The full cost derivation logic is complex, but for the relatively simple case in the question:

Inputs

The number of times the operator is executed
This is the Estimated Number of Executions: 504
The cardinality (total number of rows) in the index
The TableCardinality property of the Index Seek operator gives this: 113,443
The number of data pages in the index: 201
This number can be obtained multiple ways, for example from sys.allocation_units:

SELECT
    AU.data_pages
FROM sys.allocation_units AS AU
JOIN sys.partitions AS P
    ON P.hobt_id = AU.container_id
WHERE
    AU.[type_desc] = N'IN_ROW_DATA'
    AND P.[object_id] = OBJECT_ID(N'Production.TransactionHistory', N'U')
    AND P.index_id =
        INDEXPROPERTY(P.[object_id], N'IX_TransactionHistory_ProductID', 'IndexID');

The density (1 / distinct values) of the index: 0.002267574
This is available in the density vector of the index statistics:

DBCC SHOW_STATISTICS
(
    N'Production.TransactionHistory',
    N'IX_TransactionHistory_ProductID'
)
WITH DENSITY_VECTOR;

Computation

-- Input numbers
DECLARE
    @Executions float = 504,
    @Density float = 0.002267574,
    @IndexDataPages float = 201,
    @Cardinality float = 113443;

-- SQL Server cost model constants
DECLARE
    @SeqIO float = 0.000740740740741,
    @RandomIO float = 0.003125,
    @CPUbase float = 0.000157,
    @CPUrow float = 0.0000011;

-- Computation
DECLARE
    @IndexPages float = CEILING(@IndexDataPages * @Density),
    @Rows float = @Cardinality * @Density,
    @Rebinds float = @Executions - 1e0;

DECLARE
    @CPU float = @CPUbase + (@Rows * @CPUrow),
    @IO float = @RandomIO + (@SeqIO * (@IndexPages - 1e0)),
    -- sample with replacement
    @PSWR float = @IndexDataPages * (1e0 - POWER(1e0 - (1e0 / @IndexDataPages), @Rebinds));

-- Cost components (no rewinds)
DECLARE
    @InitialCost float = @RandomIO + @CPUbase + @CPUrow,
    @RebindCPU float = @Rebinds * (1e0 * @CPUbase + @CPUrow),
    @RebindIO float = (1e0 / @Rows) * ((@PSWR - 1e0) * @IO);

-- Result
SELECT
    OpCost = @InitialCost + @RebindCPU + @RebindIO;

db<>fiddle


SSIS Update (Replace) record instead of append based on Primary Key

As Kin mentioned in his comment above, it is possible to accomplish what I was trying to do using the SSIS Lookup transformation component (example also provided in his comment) which answers my question.

While I will surely be using this component in the future I decided to go with a more straightforward SQL scripting method to accomplish what I needed after reading mskinner's comment (and referenced blog) which points out that using an Execute SQL task is probably going to be much faster/efficient than using the Lookup Transformation component in SSIS for what I am trying to accomplish.


Get distinct count on calculated member?

The limitation of a single distinct count per measure group is related to performance. To get both distinct count measures to show up in the same measure group, you can use the workaround suggested here by Darren Gosbell.

best of both worlds...performance benefit from each DC-measure residing in its own measure group, UX benefit from having all related measures appearing in same measure group.


sys.database_files / sys.filegroups VS sys.sysfiles / sys.sysfilegroups

Almost always, backward compatibility. Often for end user compatibility but also for internal purposes.

I'd swear I was reading an article earlier this week that mentioned one of the old views, possibly sysfiles, having been 99% removed during SQL2005 development but the final 1% proved to difficult/risky to eliminate.

As a general rule, target the most recent versions of any system tables/views in your scripts, they're more likely to still be there in subsequent releases.


How to Select Most Recently Updated Row Based on Sequence ID

;WITH x AS
(
  SELECT Person_ID, Person_Seq_ID, Person_Name, Person_Favorite_Color,
    rn = ROW_NUMBER() OVER (PARTITION BY Person_ID ORDER BY Person_Seq_ID DESC)
  FROM dbo.Person -- always use schema prefix
)
SELECT Person_ID, Person_Seq_ID, Person_Name, Person_Favorite_Color
  FROM x WHERE rn = 1
  ORDER BY Person_ID;


Why is ACCESS EXCLUSIVE LOCK necessary in PostgreSQL?

If you have a look at the documentation, you see the list of operations that obtains this type of lock:

  Acquired by the DROP TABLE, TRUNCATE, REINDEX, CLUSTER, VACUUM FULL,
  and REFRESH MATERIALIZED VIEW (without CONCURRENTLY) commands. Many
  forms of ALTER TABLE also acquire a lock at this level.

All those are physically (re)moving rows, so reading would be at least very unpredictable during these.

Talking about transactions, when you start reading from a table and another process decides slightly later to drop it, the latter must wait until the read finishes - the earlier will still get the data requested, and when that transaction commits, the second one takes the lock and drops the table.  The other way around, the read will just fail after the wait from the dropping transaction to commit, as there is nothing to read from anymore.

If PostgreSQL allowed to read from a table being dropped, that would mean two things.  One is, if I am not mistaken, that there existed a READ UNCOMMITTED isolation level.  While you can choose that, it is in fact READ COMMITTED, meaning that no transaction is able to see intermediary states of other transactions.  The other one is, as you already guessed, that possibly only a portion of the desired data is returned.  This would lead to unpredictable behaviour, and that's the last thing you want from a database system.

Moreover, CLUSTER and VACUUM FULL are moving data between two files - how could one decide in which file to chase the rows?

All in all, in some cases reading data is not so urgent.  Or in the case of DROP TABLE and TRUNCATE it is not so urgent anymore.


Homework questions like this will generally not elicit answers with code. Stack Exchange is not a place where you can come to get others to do your homework for you. However you have only asked for pointers of where to look, so to that end, I will inform you that what you are looking for is called a many-to-many relationship, and requires a third table that maps table 1 to table 2. Usually, this third table just has two columns, each containing the primary key of one of the main tables, and the primary key of this table covers both columns.

Read up:

http://en.tekstenuitleg.net/articles/software/database-design-tutorial/many-to-many.html
http://www.tomjewett.com/dbdesign/dbdesign.php?page=manymany.php


export data from SQl to text file/MongoDB

There is a tool on CodePlex you can use for this, called MSSQL to MongoDB Tool. This tool will migrate all data from MSSQL to MongoDB. It will do this without exporting to a file first.
If you want to export to file first, you are best off using the Export Data wizard which you can find in your SSMS, by right clicking your DB and choose 'Export Data' under 'Tasks'


How to restore dump from MariaDB 10.1 to MySQL 5.5

I've not tested this myself, but in the MariaDB mysqldump main page there is a paragraph about the --create-options switch which seems to indicate you can solve the problem by using the switch --skip-create-options:

  Include all MariaDB and/or MySQL specific create options in CREATE
  TABLE statements. Use --skip-create-options to disable.


If the table reportingdb.norep_zonebannertmp_bk does not exist on one of the nodes in the Cluster, this could be the source of your problem. Why? TRUNCATE TABLE is DDL. That will close the current transaction PXC has during WriteSet Operations. This cannot be rolled back. I would expect a PXC communication problem at COMMIT time because TRUNCATE TABLE causes implicit COMMIT (See my StackOverflow post from May 12, 2011). If the table reportingdb.norep_zonebannertmp_bk had existed in every node in the Cluster, then the TRUNCATE TABLE would execute without an error. You should check for any BugReports about this. You should probably submit this as a question to Percona.
There are two methods you may want to try to clear this up:
METHOD #1
You could recreate the table on the PXC (Percona XtraDB Cluster) that has the table missing

Step01) Do SHOW CREATE TABLE reportingdb.norep_zonebannertmp_bk\G on Node that still has table
Step 02) Copy and Paste that Query into /root/Fix.sql
Step 03) Change CREATE TABLE to CREATE TABLE IF NOT EXISTS in /root/Fix.sql
Step 04) Execute /root/Fix.sql on the PXC node that does not have the table

METHOD #2
You may have to perform a full SST in the event other tables are out of sync. The most aggressive way to do this is the following:
cd /var/lib/mysql
service mysql stop
rm -f /var/lib/mysql/galera.cache /var/lib/mysql/grastate.dat
service mysql start

Since there is no /var/lib/mysql/galera.cache, IST cannot be done. SST would be initiated.


I can't create relationship in my database sql server

The issue is with creating two CONSTRAINT in Comment table with ON DELETE CASCADE one for UserId and other for TopicId, which can be fixed by INSTEAD OF DELETE Trigger. Just create all the tables and add all foreign key constraint as shown.

You can add your columns as indicated with other columns and Id column can be changed from bigInt to Int.

CREATE TABLE [dbo].[User]
(
UserId BIGINT NOT NULL,
UserName VARCHAR(10),
--Other columns
CONSTRAINT PkUserId PRIMARY KEY CLUSTERED (UserId ASC)
);
GO
CREATE TABLE [dbo].[Topic]
(
TopicId BIGINT NOT NULL,
UserId BIGINT NOT NULL,
TopicData VARCHAR(20)
--Other columns
CONSTRAINT PkpostId PRIMARY KEY CLUSTERED(TopicId ASC)
);
ALTER TABLE [dbo].[Topic] WITH CHECK
ADD CONSTRAINT [FkTopicUserId] FOREIGN KEY(UserId)
REFERENCES [dbo].[User]([UserId]);
GO
CREATE TABLE [dbo].[Comment]
(
CommentId BIGINT NOT NULL,
TopicId BIGINT NOT NULL,
UserId BIGINT NOT NULL,
CommentData VARCHAR(20),
--Other columns
CONSTRAINT PkCommentId PRIMARY KEY CLUSTERED(CommentId ASC)
);

ALTER TABLE [dbo].[Comment] WITH CHECK
ADD CONSTRAINT [FkCommentTopic] FOREIGN KEY(TopicId)
REFERENCES [dbo].[Topic](TopicId);
GO
ALTER TABLE [dbo].[Comment] WITH CHECK
ADD CONSTRAINT [FkCommentUser] FOREIGN KEY(UserId)
REFERENCES [dbo].[User](UserId);

Now for Multiple delete cascade you can use INSTEAD OF DELETE Triggers as

CREATE TRIGGER [DELETE_User]
   ON [dbo].[User]
   INSTEAD OF DELETE
AS
BEGIN
 SET NOCOUNT ON;
 DELETE FROM [dbo].[Topic] WHERE UserId IN (SELECT UserId FROM DELETED)
 DELETE FROM  [dbo].[Comment] WHERE UserId IN (SELECT UserId FROM DELETED)
 DELETE FROM dbo.[User] WHERE UserId IN (SELECT UserId FROM DELETED)
END
GO
CREATE TRIGGER [DELETE_Topic]
   ON [dbo].[Topic]
   INSTEAD OF DELETE
AS
BEGIN
 SET NOCOUNT ON;
 DELETE FROM [dbo].[Topic] WHERE UserId IN (SELECT UserId FROM DELETED)
 DELETE FROM  [dbo].[Comment] WHERE UserId IN (SELECT UserId FROM DELETED)
END
GO

Let's check with some data on these the above design.

INSERT INTO [dbo].[User]
VALUES (1,'user1'),(2,'user2');
GO
INSERT INTO [dbo].[Topic]
VALUES (1,1,'POST1'), (2,1,'POST2'),(3,2,'POST3');
GO
INSERT INTO [dbo].[Comment]
VALUES  (1,1,'COmment1'), (2,1,'Comment2'),
(3,1,'Comment3'), (4,2,'Comment4'), (5,3,'Comment4');

Now check with cascade delete.

SELECT UserId,UserName FROM [dbo].[User];
SELECT TopicId, UserId FROM [dbo].[Topic];
SELECT CommentId,TopicId FROM [dbo].[Comment];
GO
DELETE FROM [dbo].[Topic] WHERE TopicId =1;
GO
SELECT UserId,UserName FROM [dbo].[User];
SELECT TopicId, UserId FROM [dbo].[Topic];
SELECT CommentId,TopicId FROM [dbo].[Comment];
GO
DELETE FROM [dbo].[User] WHERE UserId = 2;
GO
SELECT UserId,UserName FROM [dbo].[User];
SELECT TopicId, UserId FROM [dbo].[Topic];
SELECT CommentId,TopicId FROM [dbo].[Comment];


What's the easiest way to re-format CSV exports from MySQL into Excel/ flat-file fields?

This will generate a CSV per lead_id in order of field_number.

SELECT lead_id, lead_id, GROUP_CONCAT(value)
FROM test_table
GROUP BY lead_id
ORDER BY field_number
INTO OUTFILE '/tmp/result.txt'
  FELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' LINES TERMINATED BY '\n';

There are 2 lead_id columns to match your output. MySQL doesn't have ROW_NUMBER() to generate an arbitrary sequence.

I have found that the following works better to order the fields properly:

SELECT lead_id,
    GROUP_CONCAT(value
        ORDER BY field_number)
    FROM exp_wp_rg_lead_detail
    GROUP BY lead_id


Compare two tables and find dates present in one and missing in another

As far as you have not provided an expected result I've used this:

emp_id | 01/01/2017 | 02/01/2017 | 03/01/2017 | 04/01/2017 | 05/01/2017 | 06/01/2017 | 07/01/2017
:----- | ---------: | ---------: | ---------: | ---------: | ---------: | ---------: | ---------:
001    |          1 |          1 |          1 |          1 |          0 |          1 |          1
002    |          1 |          1 |          0 |          0 |          1 |          1 |          1
003    |          1 |          0 |          1 |          1 |          1 |          1 |          1

dbfiddle here

Where 1 means attendance and 0 not attendance.

I've used next script to dynamically generate the pivot table.

DECLARE   @SQLQuery AS NVARCHAR(MAX) = '';
DECLARE   @PivotColumns AS NVARCHAR(MAX) = null;

select  @PivotColumns = COALESCE(@PivotColumns + ',','')  + '[' + convert(varchar(20),cal_dates,103) + ']'
FROM    (select cal_dates from tbl_month) m;

SELECT @SQLQuery =
'with allDates as
 (
 select      te.emp_id, tm.cal_dates
 from        tbl_month tm
 cross apply (select distinct emp_id
              from tbl_employees) te
 )
 select    emp_id,' + @PivotColumns +
 'from      (select    allDates.emp_id, convert(varchar(20), allDates.cal_dates, 103) as cal_dates,
                       case when te.attendance_date is null then 0 else 1 end as Assist
                       from      allDates
                       left join tbl_employees te
                       on        te.emp_id = allDates.emp_id
                       and       te.attendance_date = allDates.cal_dates) source
PIVOT
(sum(Assist) FOR cal_dates IN (' + @PivotColumns + ')) piv'

exec (@SQLQuery)

First CTE generates all possible dates using tbl_month, you should set some filter (date between MinDate and MaxDate) or it will use the whole calendar table.

with allDates as
(
select      te.emp_id, tm.cal_dates
from        tbl_month tm
cross apply (select distinct emp_id
             from tbl_employees) te
)

Data source

Using a LEFT JOIN with all possible dates, I've set a mark (0/1) depending on employees attendance.

select    allDates.emp_id, convert(varchar(20), allDates.cal_dates, 103) as cal_dates,
          case when te.attendance_date is null then 0 else 1 end as Assist
from      allDates
left join tbl_employees te
on        te.emp_id = allDates.emp_id
and       te.attendance_date = allDates.cal_dates

Pivot columns

Keep in mind I've used CONVERT(varchar(20),cal_dates,103), as a result you get a date format as 'DD/MM/YYYY'. If you want to change it, remember to change both, pivot columns generator and data source select.

No attendance only

If you want to fetch 'Non attendance' only, you can filter data source on this way:

select    allDates.emp_id, convert(varchar(20), allDates.cal_dates, 103) as cal_dates,
          case when te.attendance_date is null then 0 else 1 end as Assist
from      allDates
left join tbl_employees te
on        te.emp_id = allDates.emp_id
and       te.attendance_date = allDates.cal_dates
and       te.attendance_date is null

Change dates by employess

Once you have defined the dynamic query, you can easily change final output, and interchange dates by employees.

DECLARE   @SQLQuery AS NVARCHAR(MAX) = '';
DECLARE   @PivotColumns AS NVARCHAR(MAX) = null;

select  @PivotColumns = COALESCE(@PivotColumns + ',','')  + '[' + emp_id + ']'
FROM    (select distinct emp_id from tbl_employees) m;

SELECT @SQLQuery =
'with allDates as
 (
 select      te.emp_id, tm.cal_dates
 from        tbl_month tm
 cross apply (select distinct emp_id
              from tbl_employees) te
 )
 select    cal_dates,' + @PivotColumns +
 'from      (select    allDates.emp_id, convert(varchar(20), allDates.cal_dates, 103) as cal_dates,
                       case when te.attendance_date is null then 0 else 1 end as Assist
             from      allDates
             left join tbl_employees te
             on        te.emp_id = allDates.emp_id
             and       te.attendance_date = allDates.cal_dates) source
  PIVOT
  (sum(Assist) FOR emp_id IN (' + @PivotColumns + ')) piv'

exec (@SQLQuery)

This is the result:

cal_dates  | 001 | 002 | 003
:--------- | --: | --: | --:
01/01/2017 |   1 |   1 |   1
02/01/2017 |   1 |   1 |   0
03/01/2017 |   1 |   0 |   1
04/01/2017 |   1 |   0 |   1
05/01/2017 |   0 |   1 |   1
06/01/2017 |   1 |   1 |   1
07/01/2017 |   1 |   1 |   1

dbfiddle here


Microsoft has done this with the next version of SQL Server, codename "Denali", as well as SQL CE 4. Check out the OFFSET & FETCH parts of the ORDER BY clause here: http://msdn.microsoft.com/en-us/library/ms188385(v=sql.110).aspx


How can I convert a string to a double precision in PostgreSQL?

double is not a postgres data type:

select cast('324342' as double);

ERROR:  type "double" does not exist
LINE 1: select cast('324342' as double);
                                ^

but double precision is:

select cast('132342' as double precision);

| float8 |
| :----- |
| 132342 |

So try:

update points set latitude2 = cast(latitude as double precision) ;

if you prefer, you can use float * instead, because according to the docs:

  float with no precision specified is taken to mean double precision

select cast('132342' as float);

| float8 |
| :----- |
| 132342 |

db<>fiddle here

* or float(n) where 25<=n<=53 because that is also taken to mean double precision


Should I add my index after the table is populated or before (MySQL. Aurora)

Have the primary key in place, but not the secondary keys.  When finished loading, add all the secondary keys in a single ALTER statement.

Caveats:

I have not done that particular task, so I dont speak from experience.
There have been changes over the years in how ALTER works.  Doing multiple alters at once used to be optimal.  But with online/instant/etc ALTERs, it is hard to say.  Still, the current ALTER .. ADD INDEX.., even though it comes back instantly, has a lot of work to do afterwards.
If the PRIMARY KEY in not in place to start with, it will have to reorganize the data.  However, this aspect may depend on whether the source data is in PK-order.


It does not solve the problem as such but you should be able to bypass it by adding the skip-external-locking to my.cnf under [mysqld]

Should at least get the server starting while you find out the root of the problem.

https://dev.mysql.com/doc/refman/5.7/en/external-locking.html


SQLServer: "order by" clause causing excessive locking

As it turns out, adding an index on the column that I'm sorting by fixes the problem.


SQL Server Management Studio closes automatically

Try uninstall/reinstall. You have a corrupt installation here.


PROXYSQL configuration over mysql galera replication

Yes, I would think so. In the ProxySQL blog post here there is a reference infrastructure which has multiple ProxySQL instances connected to the same set of database nodes. It also says:

  We usually recommend to setup ProxySQL instances on the application boxes for availability reasons

That said, for a Galera cluster, I'm unsure whether the ProxySQL instances agree between themselves which Galera node is the "master" and which are the "slaves" - this is especially relevant if you're using read-write splitting, which is something you may want to do with a Galera cluster in order to avoid certain deadlocks not seen with ordinary MySQL configurations. (See also: Galera’s big gotcha for MySQL users.)


How to call an Oracle Function with multi out Parameters though LINKSERVER

You can try same trick as mentonied at this answer on StackOverflow.

In your case SQL statement string for EXECUTE must look like this:

DECLARE
  vRetValue number;
  vErrorCode varchar2(100);
  vErrorDescription varchar2(4000);
  vRetCursor sys_refcursor;
BEGIN
  vRetValue := package_name.function_name(?,?,?,?,?,?,?,?,?,?,?,vErrorCode, vErrorDescription);
  open vRetCursor for select vRetValue, vErrorCode, vErrorDescription from dual;
  ? := vRetCursor;
END;

And call to this code like this:

DECLARE @RET_CURSOR CURSOR;

EXECUTE (
  'DECLARE '+
  '  vRetValue number;  '+
  '  vErrorCode varchar2(100); '+
  '  vErrorDescription varchar2(4000); '+
  '  vRetCursor sys_refcursor; '+
  'BEGIN '+
  '  vRetValue := package_name.function_name(?,?,?,?,?,?,?,?,?,?,?,vErrorCode, vErrorDescription); '+
  '  open vRetCursor for select vRetValue, vErrorCode, vErrorDescription from dual; '+
  '  ? := vRetCursor; '+
  'END;' ,
  @USER_NAME, @FIRST_NAME, @MIDDLE_NAME, @LAST_NAME, @DATE_OF_BIRTH,
  @MOTHER_MAIDEN_NAME, @MOBILE_NO, @P_NATIONAL_ID, @NATIONAL_ID_EXP_DT,
  @PASSPORT_NO, @PPT_EXP_DATE,
  @RET_CURSOR OUTPUT
) AT linkserver_name;

Sorry, I can't check the syntax, but hope that principle is clear enough.

Another thing is the code above is a workaround and must be used only if code with multiple output parameters won't work at all.
Most probable source of such behavior is a bugs or version incompatibility of MS OLE DB Provider for Oracle or Oracle client.


Need urgent help with recovering data from dropped database

Hire a professional.

If this is important production data that needs to be recovered quickly, your best strategy is to get an expert in as soon as possible to get the work done.

The work you will be focusing on is creating processes and procedures to make sure this doesn't happen in the future. And convincing management that the likely hood of it happen again based on those processes and procedures are as low as possible.


SQL Server full text search query takes way too long to execute

In place of your query, try this:

SELECT * FROM dbo.Analysis
where FREETEXT(dbo.Analysis, 'historic year-on-year exchange rates forecast
yearon-year exchange rates');

or this:

SELECT * FROM  dbo.Analysis
where CONTAINS(dbo.Analysis, "historic year-on-year exchange rates forecast
year-on-year exchange rates");

Also, the hyphens may be causing problems.  Try:

  year on year

or

  yearonyear

in place of the hyphens.

Lastly, did you build and enable a stop list?


Foreign Key Constraint Set Back to Trusted

Will the optimizer recognize the change and consider them before using cached query plans for re-run transactions? Put another way, do I need to consider removing any query plans?

Yes, the optimizer should recompile the plans since changes to an underlying table are one of the triggers that result in a plan recompilation. Since checking a constraint involves an ALTER TABLE command,

ALTER TABLE [dbo].[table] CHECK CONSTRAINT [fk_table]

this should qualify it as a valid reason to update the plans, according to this TechNet article (from 2008 R2 but should stand true for current versions as well) which specifies that a ALTER TABLE or ALTER VIEW command will qualify.

  Is it safe to say this is a situation where SQL Server has been
  rebuilding the index (we do have this turned on), and maintaining the
  index throughout inserts, updates, and deletes, but simply not using
  the foreign key?

Correct. As you've observed the optimizer ignores the foreign key when creating query plans - no operation involving the table will make use of the foreign key while it is untrusted, whether that is a rebuild or an insert, update, etc.


MySQL - alter huge table without slowing down server

Upgrade to InnoDB.  Tips and caveats

Upgrade to at least 5.6

After those, you can do most ALTERs with almost no downtime.  See ALGORITHM=INPLACE.


check out this one: https://stackoverflow.com/questions/2281890/can-i-create-view-with-parameter-in-mysql

ot you can crate sp for this select
 http://dev.mysql.com/doc/refman/5.0/en/create-procedure.html


SELECT with lots of left join is very slow in SQL Server 2014

It may be to do with the new cardinality estimator.  Try running the query with this disabled.  Also try the FORCE ORDER hint, eg

...your query
OPTION ( QUERYTRACEON 9481 ) -- turn off new cardinality estimator

...your query
OPTION ( FORCE ORDER )

I would also ask what foreign keys, indexes and constraints do you have in place?


Is a single batch request equivalent to a single tsql request?

As per the comment, then no - it's not a single statement.
A batch can cover multiple individual T-SQL statements in a single batch.


MySQL next entry after certain condition

Well, MySQL isn't a specialty of mine, so there may be a better way of doing this. However, after checking to see how/if MySQL implemented various concepts (like SQL Server's ROW_NUMBER or LEAD functions), this appears to work.

This uses a trick, where the value of a variable is updated with each row, in sequence, and both the old and new values of the variable are included as columns. This lets us pick up the location from the previous row in the current row. (Note that I am not familiar enough with MySQL to know if this is a deliberate feature; in SQL Server, something similar works - but is not documented as a feature, and is generally thought of as something that could stop working with the next version.)

With that trick, we:

Limit our records to those that have one of the two locations we're interested in;
add the previous row's location to the current row; and
select the desired fields from the rows where the previous row's location is different from that of the current row.

Here's the code:

CREATE TABLE WhenWhatWhere (`when` datetime, `code` int, `location` char(1), CONSTRAINT PRIMARY KEY (`code`,`when`));

INSERT INTO WhenWhatWhere
VALUES ('2017-01-05 12:54', 102263, 'X')
      ,('2017-02-02 8:42', 102263, 'Y')
      ,('2017-03-02 12:04', 102263, 'Z')
      ,('2017-03-06 13:34', 102263, 'Y')
      ,('2017-03-07 7:57', 102263, 'Z')
      ,('2017-03-07 18:37', 102263, 'Y')
      ,('2017-03-09 17:47', 102263, 'Z')
      ,('2017-03-13 7:56', 102263, 'Y')
      ,('2017-03-21 11:44', 102263, 'Z')
      ,('2017-03-22 20:28', 102263, 'Y')
      ,('2017-03-24 13:07', 102263, 'X')
      ,('2017-03-25 14:01', 102263, 'Y')
      ,('2017-03-26 8:14', 102263, 'Z')
      ,('2017-03-27 9:52', 102263, 'Y')
      ,('2017-04-03 10:02', 102263, 'X')
;

SET @rownum = 0;
SET @loc1 = 'X';
SET @loc2 = 'Y';
SET @lastloc = '';

--
-- Select only the rows where the location changed from the previous row.
SELECT `when`, `code`, `location`
  FROM ( -- Add the location from the previous row; must be sorted to work
        SELECT `when`, `code`, @lastloc `lastloc`, (@lastloc := `location`) `location`
          FROM ( -- Select just the rows that deal with our two locations
                SELECT `when`, `code`, `location`
                  FROM WhenWhatWhere
                 WHERE `location` = @loc1 OR `location` = @loc2
                  -- add additional restrictions (like date range or specific code) here
               ) loc
         ORDER BY `when`
       ) comploc
 WHERE `lastloc` <> `location`
 ORDER BY `when`
;

It worked when I ran it at sqlfiddle.com.


A key word is missing. It must be:

ALTER DEFAULT PRIVILEGES FOR ROLE new_user IN SCHEMA public GRANT...

The documentation:

ALTER DEFAULT PRIVILEGES
    [ FOR { ROLE | USER } target_role [, ...] ]

One of both ({ ROLE | USER }) is required.

Note that the particular command does not make any sense to begin with because (per documentation):

  There is no need to grant privileges to the owner of an object
  (usually the user that created it), as the owner has all privileges by default.

If you want that to grant permissions for objects that a different user created, like postgres, then use:

And you probably wouldn't want to restrict that to a particular schema.

ALTER DEFAULT PRIVILEGES FOR ROLE postgres
GRANT ALL ON TABLES TO new_user;

And you typically want to grant privileges on sequences as well

ALTER DEFAULT PRIVILEGES FOR ROLE postgres
GRANT ALL ON SEQUENCES TO new_user;


SELECT hours, SUM(CASE WHEN branchid = 'A'
                       THEN total
                       ELSE 0 END) A, SUM(CASE WHEN branchid = 'B'
                                               THEN total
                                               ELSE 0 END) B
FROM output
GROUP BY hours;

or
SELECT hours, SUM(CASE WHEN branchid = 'A'
                       THEN total
                       ELSE 0 END) A, SUM(CASE WHEN branchid = 'B'
                                               THEN total
                                               ELSE 0 END) B
FROM (SELECT 0 hours UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4) hours_table
LEFT JOIN output USING (hours)
GROUP BY hours;

fiddle


Do you prefer to store confirm password property in database?

Maybe I misunderstood your question, but let's consider a signup form. It is customary to ask for password confirmation, then when the user submits the form, the backend checks that both password fields do match. If there was some issue like incomplete data, you show a message back to the user.
Otherwise, if no other errors were found, you proceed to save the record to the DB - the password of course should be hashed and not stored in plain text.
But until the form is submitted without errors, you save nothing to the DB. At least I don't see why you would.
You can save session state though, that's what cookies are for.
To sum up, only one field is need for the password (in hashed form). The password confirmation field is simply to minimize the risk of incorrect input, but it's no longer useful after signup has completed.


As user1786423 said in your question's comment, you will probably have a User table, an Aircraft table and a table for each type of measurement.

As a minimum, User would have an id, say idUser. I'd imagine there would be more static data about a user in there as well.

As a minimum, Aircraft would have an id, say idAircraft, and idUser. idUser would reference User.idUser. I'd imagine there would be more static data about an aircraft in there as well.

The AirspeedMeasurement table would have, based on your descriptions, columns like idAirspeedMeasurement, idAircraft, time, airSpeed. idAircraft would reference Aircraft.idAircraft.

You may want to have a parent table for all of the measurements. For example, an AircraftMeasurement table would have the columns idAircraftMeasurement, idAircraft, time. Then, the child tables would have a primary key that references idAircraftMeasurement, and wouldn't have to each store idAircraft and time.

An ER diagram would look something like this:

Some sample data:

In this example Aircraft 1 records PlaneSpeedMeasurements and WingTiltMeasurements, whilst Aircraft 2 only records PlaneSpeedMeasurements.

Let us know if something is unclear : ]


Get the data from the table in the form of fieldname and fieldvalue in SQL Server

UNPIVOT can help you with this.

Try something like

SELECT employeenumber, fieldname,  fieldvalue
FROM tblmployee e
UNPIVOT(
    FieldValue
    FOR FieldName IN (Gender, JoinDate, MobileNo)
) U

Hope that helps


It sounds like, with your scenario, the easiest way would be to grab a backup of the database and restore it side-by-side your current database.  You need to ensure that this backup contains the deleted records.  Then simply do an INSERT INTO ... SELECT FROM ... to get the deleted records from the old database (via a backup/restored version) into your current.

If you don't have a backup of the database containing all of the data, you may be out of luck here.


Can we capture only Slave_IO_Running in SHOW SLAVE STATUS in MySQL

Executing

mysql> SHOW STATUS LIKE 'Slave_running';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Slave_running | ON    |
+---------------+-------+
1 row in set (0.04 sec)

From the manual:

  This is ON if this server is a replication slave that is connected to a replication master, and both the I/O and SQL threads are running; otherwise, it is OFF.

Caveat: If you're running MySQL prior to version 5.1.46 or 5.5.4, it's possible the slave IO thread could not be running, and the status variable indicate the slave is running. (MySQL Bug 51089)

As of MySQL 5.7, the above solution will not work by default. This is because many of the status variables from information_schema are being moved into performance_schema:

  As of MySQL 5.7.6, the Performance Schema also contains system and status variable tables (see Section 23.9.13, “Performance Schema System Variable Tables”, and Section 23.9.14, “Performance Schema Status Variable Tables”). The Performance Schema tables are intended to replace the INFORMATION_SCHEMA tables, which are deprecated as of MySQL 5.7.6 and will be removed in a future MySQL release.
  [src]

and

  These Slave_xxx status variables become unavailable through SHOW STATUS:

  Slave_heartbeat_period
  Slave_last_heartbeat
  Slave_received_heartbeats
  Slave_retried_transactions
  Slave_running

  Applications that use these status variables should be revised to obtain this information using the replication-related Performance Schema tables.

There are two options to get the slave_running status outside of SHOW SLAVE STATUS

The preferred method, as mentioned by the doc, is to update the application to pull from replication-connection-status.SERVICE_STATE and replication-applier-status.SERVICE_STATE in performance_schema:

  // this is the SLAVE IO THREAD status
  SELECT SERVICE_STATE FROM performance_schema.replication_connection_status;

  // this is the SLAVE SQL THREAD status
  SELECT SERVICE_STATE FROM performance_schema.replication_applier_status;

NOTE: This is for single master replication. Multi-master replication should be in replication_applier_coordinator.SERVICE_STATE

The second option is to turn on show_compatibility_56. This is a temporary fix to get you by without modifying the application, but will go away in a future release.


SentryOne Plan Explorer (free) - analyze execution plans in a way you could never do with Management Studio alone (I also wrote a demo kit here)
SSMS Tools Pack (free) - lots of valuable features here - snippets, connection color coding that works, CRUD generation, and query execution history


Databases lost after Postgres upgrade from 11 to 12

I followed the comment from @Colin 't Hart and this solved the problem. With pg_upgradecluster -v 12 11 main /srv/postgres/data/12 the upgrade was done. I changed the port to 5432 and restarted it. Now my applications are using the version 12 database.


We have been using the approach in your posted link since first reading about it in Sept 2017.  Here is a link with comments from MS SQL Architect Conor Cunningham about the need to re-index in Azure SQL Database  Link to Conor Cunningham comments.


This was tested using PostgreSQL 9.1

This is assuming that you only want to be able to manage tables, indexes and other objects from within one database.

You will need to first create your user and database as postgres or a user that has the right to create logins and databases

When you create that user, you can make him the owner of the newly created database.  As the owner of the database, that user can drop and create tables as well as other objects within that database.

I have created four different sections.

PostgreSQL is on the same server as the application.
PostgreSQL is running on a distant server.
Locking example.
Disconnect users

1. PostgreSQL server is on the same machine as the application

Just make sure that the users can connect locally.  trust means you don't need a password, or you can use md5 to require one.

cat /path/to/postgresql/data/pg_hba.conf

# IPv4 local connections:
local   all             all                                     trust
host    all             all             127.0.0.1/32            trust

Change to postgres user

su - postgres

connect to PostgreSQL

Now create the user and the database

-bash-3.2$ psql -d template1

template1=# CREATE ROLE myuser WITH LOGIN PASSWORD 'test123';
CREATE ROLE
template1=# CREATE DATABASE mydatabase WITH OWNER myuser;
CREATE DATABASE
template1-# \q

Connect to postgresql as your user

-bash-3.2$ ./psql -d mydatabase -U myuser

mydatabase=> create table mytable (t int not null);
CREATE TABLE

mydatabase=> \dt
         List of relations
 Schema |  Name   | Type  | Owner
--------+---------+-------+--------
 public | mytable | table | myuser

mydatabase=> drop table mytable;
DROP TABLE

2. PostgreSQL server is on a different machine than that of the application

Edit the authorization file on the remote PostgreSQL server.  For users connecting remotely, its better to require a password.

Authorization File

vi /path/to/postgresql/data/pg_hba.conf

Add in the ip address of the server that will be connecting to this PostgreSQL server.

# IPv4 local connections:
host    all             all     ip.address.application_server/32     md5

Edit postgresql.conf file on the remote PostgreSQL server.  You need to make sure that PostgreSQL server is listening on some ip address

vi /path/to/postgresql/data/postgresql.conf
#------------------------------------------------------------------------------
# CONNECTIONS AND AUTHENTICATION
#------------------------------------------------------------------------------

# - Connection Settings -

listen_addresses = '*'          # list on all ip addresses of the server;
# or
listen_addresses = 'ip.address.psql.server'          # listen to one ip address;

Reload PostgreSQL

/etc/init.d/postgresql reload
#or
./pgctl reload -s -D /path/to/postgresql/data
server signaled

Connecting to PostgreSQL From Application server

-bash-3.2$  ./psql -h remote.ip.address.psql -U superuser -d template1
Password for user superuser:

template1=# CREATE ROLE myuser WITH LOGIN PASSWORD 'test123';
CREATE ROLE
template1=# CREATE DATABASE mydatabase WITH OWNER myuser;
CREATE DATABASE
template1-# \q

Connect to distant PostgreSQL server as new user

-bash-3.2$ ./psql -h remote.ip.address.psql -U myuser -d mydatabase
Password for user myuser:

mydatabase=> create table mytable (t int not null);
CREATE TABLE

mydatabase=> \dt
         List of relations
 Schema |  Name   | Type  | Owner
--------+---------+-------+--------
 public | mytable | table | myuser

mydatabase=> drop table mytable;
DROP TABLE

3. Locking example

http://www.postgresql.org/docs/current/interactive/sql-lock.html

Unless I'm mistaken, locking occurs at the table and/or row level unless you can put the database into read-only or standby.

Connected as myuser

mydatabase=> BEGIN WORK;
BEGIN
mydatabase=> LOCK TABLE mytable IN SHARE ROW EXCLUSIVE MODE;
LOCK TABLE
mydatabase=> SELECT t FROM MYTABLE ;
 t
---
(0 rows)

mydatabase=> INSERT INTO mytable VALUES (1);
INSERT 0 1
mydatabase=> COMMIT WORK;
COMMIT

4. Disconnect users

Find all active connections

SELECT usename, procpid, datname FROM pg_stat_activity;

Output:

test=# select usename, procpid, datname from pg_stat_activity;
 usename  | procpid | datname
----------+---------+---------
 test     |    4083 | test

Now that you have the procpid, you can terminate the connection.  You must be a superuser to run the next command.

SELECT pg_terminate_backend(4083) FROM pg_stat_activity WHERE datname = 'test';

Then if you want to prevent other normal users from reconnecting you can limit the number of connections to 0.  You can run this command as the db owner

ALTER DATABASE test WITH connection limit 0;

If a user attempts to reconnect, he will get this message

  test=> \dt
  FATAL:  terminating connection due to administrator command server
  closed the connection unexpectedly    This probably means the server
  terminated abnormally     before or while processing the request. The
  connection to the server was lost. Attempting reset: Failed.

On the otherhand, postgres would still be able to connect.

or

Disallow all connections, even postgres.  You have to be a superuser to run this.  I found this command on another post related to a similar question : Force drop db while others may be connected.

UPDATE pg_database SET datallowconn = 'false' WHERE datname = 'test';


Couldn't open extension control file plpython3u.control : No such file or directory

The package to install is postgresql-plpython3.

By way of dependencies, this will install postgresql-plpython3-9.1, which provides these files:

$ dpkg -L postgresql-plpython3-9.1
/.
/usr
/usr/share
/usr/share/doc
/usr/share/doc/postgresql-plpython3-9.1
/usr/share/doc/postgresql-plpython3-9.1/copyright
/usr/share/postgresql
/usr/share/postgresql/9.1
/usr/share/postgresql/9.1/extension
/usr/share/postgresql/9.1/extension/plpython3u--1.0.sql
/usr/share/postgresql/9.1/extension/plpython3u.control
/usr/share/postgresql/9.1/extension/plpython3u--unpackaged--1.0.sql
/usr/lib
/usr/lib/postgresql
/usr/lib/postgresql/9.1
/usr/lib/postgresql/9.1/lib
/usr/lib/postgresql/9.1/lib/plpython3.so
/usr/share/doc/postgresql-plpython3-9.1/changelog.Debian.gz

and then you can do:

postgres=# create extension plpython3u;
CREATE EXTENSION


While I agree with @Kin about data types, I don't think this warning is as troublesome as you think. You're performing grouped concatenation, which is going to be orders of magnitude more expensive than any conversions anyway (and as Daniel said, unless your catalog views are massive - as in larger than physical memory - it is unlikely to affect anything in terms of estimation either).

I would write the query this way, taking care to never use varchar strings (which also means stop leaving out the N prefix on string literals), and also ensuring you use statement terminators:

DECLARE @NameColumns nvarchar(max); -- why 1024 when you use max below?

SET @NameColumns = STUFF(
  (SELECT   N',Test.' + name AS [text()] FROM
    (
      SELECT c.name FROM sys.columns AS c
        INNER JOIN sys.tables AS t
          ON t.object_id = c.object_id
        WHERE t.name = N'Test'
        AND   c.name LIKE N'Name_%'
    ) AS D FOR XML PATH(N''),
    TYPE).value(N'.[1]', N'nvarchar(max)'), 1, 1, N'');

SELECT @NameColumns;

Even still, I don't believe there is any way to avoid the implicit convert without changing the output by avoiding TYPE/value() as Mikael suggested. I would say worry about this when you can actually prove that this has some material impact on the performance of the query. In my tests, the two different forms performed the same (we're talking sub-10ms every time), but of course if I have a table named Sales & Stuff then it becomes Sales &amp; Stuff without the implicit convert.


I build indexed views in SQL Server all the time to tune existing products. The optimizer is smart enough to use the index if you are utilizing the appropriate columns.

Using your example, it looks like you created the view but did not actually create an index upon it.

if object_id(N'mytable1') is not null
drop table mytable1
if object_id(N'mytable2') is not null
drop table mytable2
go

Create table mytable1 (ID int identity(1,1), Name1 nvarchar(100))
GO
Create table mytable2 (ID int identity(1,1), Name2 nvarchar(100))
GO

insert into mytable1 values ('steve')
insert into mytable1 values ('jack')
insert into mytable1 values ('mike')
insert into mytable1 values ('ralph')
insert into mytable1 values ('simon')

insert into mytable2 values ('smith')
insert into mytable2 values ('jackson')
insert into mytable2 values ('mikaelson')
insert into mytable2 values ('montalvo')
insert into mytable2 values ('singer')
go

if object_id(N'myview') is not null
drop view myview
go

Create view myview
with schemabinding
as
select a.id, a.name1, b.name2
from dbo.mytable1 a
join dbo.mytable2 b on a.Id = b.Id
GO

select a.name1, b.name2
from mytable1 a join mytable2 b on a.Id = b.Id
GO

Since there is no index on this view, we scan on the base tables:

But once we add an index, the optimizer can use it:

CREATE UNIQUE CLUSTERED INDEX [ix_cl_names] ON [myview]
(
    [name1] ASC,
    [name2] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO

This appropriately used the view:

  I can't change all my SQL scripts to select from the view instead of tables. I want to create indexed views and force SQL Server to get data from them instead of tables.

There is no hint or other method to force SQL Server to use an indexed view when it is not referenced in the query.

Additional information (from Geoff Patterson)

One extra point is that while the optimizer can, in Enterprise Edition only, use the indexed view in this case, it may make sense to directly reference the view using the NOEXPAND hint if you need to be 100% sure of the view index being used or if you ever want it to be used in Standard Edition.

I've frequently seen queries even in Enterprise Edition where the optimizer does not pick up on the fact that the view index can be used unless NOEXPAND is used. It's more common with complex queries, but can happen with simple queries also.

Paul White has one of the better articles I've read exploring the nuances of NOEXPAND; beyond just usage of the view index, the hint can also impact things like whether statistics are automatically created on the indexed view and cardinality estimates for the plan.

And from Zane: As a side note, be careful with indexed views as like any other index it will add to your update, insert, and delete times.


What to do with duplicate lookup information

This type of thing should be handled by the ETL process that brings the data into the data warehouse. In fact, this process is the T in ETL.

What you need to do first is define the logical key column(s) of the tables, so the business meaning of the rows can be equated between the databases. A multi-column key as you propose would complicate matters, and really doesn't solve the problem.

For this example, I would define CustomerState as the logical key column in the dimension, and when the separate tables are merged together, this column would be unique in the result, with new CustomerStateId values assigned. This ensures the dimension primary key is as narrow as possible, which will carry through to the fact tables and make them as narrow as possible as well.

The ETL process might do something like this (assuming the CustomerStateId column of the target table is an IDENTITY column):

MERGE INTO [dbo].[CustomerState] tgt
    USING [Staging].[CustomerState] src ON src.CustomerState = tgt.CustomerState
    WHEN NOT MATCHED BY TARGET THEN
        INSERT (CustomerState) VALUES (src.CustomerState);

(The reason I used MERGE instead of INSERT is that in other dimensions you may need to handle doing updates as well; not in this case as there are no other columns.)

Then, the fact table loading process would use a lookup mechanism (Lookup Transformation in SSIS) to go from the CustomerState logical value to the newly-assigned CustomerStateid value generated by the above statement.


MongoDB multi-datacenter deployment and shard keys

Yes, your question is quite loaded...
1.1 Yes, every shard should have RS
1.2 Every RS have it's own master.
2.1. Only if you do slave reads. Normally read is done from primary. It will not affect replication directly. Only write of data, when write concern is majority.
2.2. If you do slave read AND you connect to specific instance, then data is read from that instance.
2.3. Yes you can do that.
3.1. NO.
3.2. NO.
You can have (of course) 2 shards in US-West and 2 shards in US-East... You just have then 4 shard cluster what uses compound sharding key {region:1,foo:1}, but every shard have different data. You just need to set chunk limits by hand (to have that foo < 0 and foo >= 0) and disable balancing between shards... End result may not be the best available, because of uneven distribution of data.
Best possible sharding key distributes written data evenly to every shard, so there is no need for balancing between shards and queries never need to read from multiple shards. With "bad" key, you are writing all data to one shard and then balancing is moving that data to other shards (later).


Well you can try to run a trace (if it's possible for you). But you can also try to just write it to your SQL Server Agent Job History instead.

I tried to write a little bit of code which can be added as a first step in front of all other steps. It will simply write a line "Executed from [hostname]".

I couldn't test it, due to the fact that I have just one instance on this machine, and I normally use it for trainings and won't misconfigure it just to have another instance. And it's late too. :-)

You can try this code:

DECLARE @host_name nvarchar(255)

SELECT @host_name = host_name
FROM sys.dm_exec_sessions AS ses
LEFT JOIN sys.servers as ser
        ON ses.host_name = ser.name
WHERE session_id = @@SPID -- only current session
    AND ser.name IS NULL -- only local connections

-- remote call
IF @host_name IS NULL BEGIN
    DECLARE @sql nvarchar(max), @server_name nvarchar(max),
            @ParmDefinition nvarchar(max) = N'@hn nvarchar(255) OUTPUT'

    SELECT @server_name = QUOTENAME(ser.name)
    FROM sys.dm_exec_sessions AS ses
    LEFT JOIN sys.servers as ser
            ON ses.host_name = ser.name
    WHERE session_id = @@SPID -- only current session
        AND ser.name IS NOT NULL -- only remote connections

    SET @sql = N'
        SELECT @hn= host_name
        FROM sys.dm_exec_requests AS ses
        WHERE wait_resource LIKE = N''%'+@@SERVERNAME+'%'+CONVERT(nvarchar(max),@@SPID) +N'%'''

    -- Encapsulate EXEC, to run it dynamically on another server
    EXEC (N'DECLARE @host_name nvarchar(255);
            EXEC '+@server_name+'.master.dbo.sp_executesql @sql = N'''+@sql+''',
                @ParmDefinition = N'''+@ParmDefinition+''',
                @hn = @host_name OUTPUT;
            PRINT(N''Executed at ''+@host_name)')

END
-- local call
ELSE BEGIN
    PRINT(N'Executed from '+@host_name)
END

Well, what does it do? It tries to check if the session is a local one or a session from a linked server. If it's a local one, it will just print out the host_name from the caller. If the caller is another server, it will take a look at the other server and find the SPID which executes the current session and will print the host_name from the remote server.

I don't know if it work that way, it need some test. Alongside with that it can be extended to look dynamically to the next server if it's a remote call too. (e.g. ServerA calls ServerB calls ServerC calls ServerD (which is the entry point)).

Hopefully it will help you and give you a working base.


Report on data in SQL database that does not meet certain rules

If you're going to maintain some sort of flag indicating which rows have problems (which makes sense if you have a lot of rows, 99.9%+ of which will be fine), then I'd use a trigger to check for the conditions. That way there's one check, only done when a row is actually added or changed.


Customize jsonb key sort order involving arrays

First off, your question as well as your column name "key" are misleading. The column key does not contain any JSON keys, only values. Else we could use the function jsonb_object_keys(jsonb) to extract keys, but that's not so.

Assuming all your JSON arrays are either empty or hold integer numbers as demonstrated. And the scalar values (non-arrays) are also integer.

Your basic sort order would work with Postgres integer (or numeric) arrays. I use this little helper function to convert jsonb arrays to Postgres int[]:

CREATE OR REPLACE FUNCTION jsonb_arr2int_arr(_js jsonb)
   RETURNS int[] LANGUAGE sql IMMUTABLE AS
'SELECT ARRAY(SELECT j::int FROM jsonb_array_elements_text(_js) j)';

Explanation:

How to turn JSON array into Postgres array?

Then add jsonb_typeof(jsonb) to arrive at:

SELECT key
FROM   t2
ORDER  BY key <> '[]'             -- special case for empty array
        , jsonb_typeof(key) DESC  -- 'number' before 'array'
        , CASE jsonb_typeof(key)  -- sort arrays as converted int[]
            WHEN 'array'  THEN jsonb_arr2int_arr(key)
            WHEN 'number' THEN ARRAY[key::text::int]
          END;

Produces the desired result exactly.

Why?

The manual for jsonb explains:

  The btree ordering for jsonb datums is seldom of great interest, but
  for completeness it is:

  Object > Array > Boolean > Number > String > Null
  Object with n pairs > object with n - 1 pairs
   Array with n elements > array with n - 1 elements

  Objects with equal numbers of pairs are compared in the order:

key-1, value-1, key-2 ...

  Note that object keys are compared in their storage order; in
  particular, since shorter keys are stored before longer keys, this can
  lead to results that might be unintuitive, such as:

{ "aa": 1, "c": 1} > {"b": 1, "d": 1}

  Similarly, arrays with equal numbers of elements are compared in the
  order:

element-1, element-2 ...

Bold emphasis mine.
That's why jsonb '[2]' < jsonb '[1, 2]'.
But Postgres arrays just sort element-by-element: '{2}'::int[] > '{1, 2}' - exactly what you were looking for.


MariaDB throws error with documented 'alter user' ... 'password expire' option

Ugh, as noted, I am using 10.3.20, and these options are not support until the 10.4 series. I had assumed Ubuntu 19 was rather up-to-date. I also assumed percona's output would be more universal. Oh well.


Can listen_addresses really be set to a list?

Yes, listen_addresses can be set to a list of addresses on the local host to bind to for listening.

In your example:

  listen_addresses = '192.168.0.191, localhost'

If the local machine has IP 192.168.0.192, you should specify that IP, not the remote host 192.168.0.191 IP. PostgreSQL cannot bind to the IP address of a remote host.

You're not saying "who is allowed to connect", you're saying "which interfaces should PostgreSQL accept connections on". The "who's allowed to connect" bit is next, and is configured in pg_hba.conf.

So: Try '192.168.0.192, localhost'. Or just *, since you probably actually want to listen on all network interfaces.


Remote PostgreSQL DB with local buffer ... how could it look like

I think installing Postgres on the RPi is overkill.
Buffering the sensor data in a plain text file should be good enough. The process could look something like:

send the sensor data to a text file (append mode)
Have a cron job that runs every x minutes. This will rename the text file as a first step. This makes 1. create a new text file while you process the "old" one
Send the contents of the text file to your Postgres server using copy ... from stdin inside a transaction. If this fails (connection problem), retry until successful.
Remove the file that was just sent to the server. Alternatively keep it as an archive/backup - which would require creating a filename in 2. that is unique, e.g. by appending a timestamp.

Using copy .. from stdin is way more efficient than running multiple single-row INSERT statement. And still more efficient than running a single multi-row INSERT statement. Additionally the file then also acts as the buffer you want.


database is not responding for hours trying to import a 2mb csv with MariaDB

Either there is a bug, or the system is ferociously swapping.

Do not blindly increase settings.  How much RAM do you have?  It sounds like you have over-committed RAM and are in the throws of heavy swapping.

innodb_buffer_pool_size = 2650M  -- OK if you have at least 6GB of RAM
innodb_log_buffer_size = 512M    -- go back to default
bulk_insert_buffer_size = 2048M  -- go back to default

All of these could be allocated simultaneously, adding up to 7GB, which would be tight in 8GB of RAM.  If you had multiple connections, running, the last one could be allocated multiple times, thereby needing even more RAM.

As a rule, anything named "buffer" or "cache" does not need to be set to huge values.  Large values help, but there are "diminishing returns".  When large values lead to swapping, performance suffers severely.

These may be directly relevant to loading a CSV file:

max_allowed_packet = 1M  -- change to 4M (due to the nature of your data)
net_buffer_length = 8M
read_buffer_size = 5M
bulk_insert_buffer_size = 2048M  -- go back to default (8M)

If you expect 1.8MB of spaces to be put into varchar(255), well, it will be truncated.

Which Engine is the table?


Since you say you can change the original table:

Alter original table, adding new column batch_id (default NULL).
Run the following

DECLARE @rowCount int = 1
       ,@batchId  int = 1
;

WHILE @rowCount > 0
BEGIN
    UPDATE TOP (50) myTable
       SET batch_id = @batchId
     WHERE batch_id IS NULL
    ;
    SELECT @rowCount = @@ROWCOUNT
          ,@batch_id = @batch_id + 1
    ;
END;

Then, simply create new tables myTable_batch_x, where x is 1 through @batchId - 1, and assigned records based on the stored batch IDs.

If you can't modify the original table, but have space to make a copy of it, create a copy with the new column, and proceed as above.

Alternately: add a my_row_id column (integer) to the original table, as an IDENTITY column:

ALTER TABLE myTable
  ADD COLUMN my_row_id int IDENTITY(1,1) NOT NULL;

This will create the new column, and automatically populate it with sequential integers starting from one.

Using the new value, assign rows to batches (1-50, 51-100, etc.). To get the rows that go into a given batch:

SELECT *  -- or, actual column list - you can exclude my_row_id that way
  FROM myTable
 WHERE (((my_row_id - 1) / 50) + 1) = @batch_no
;

Since my_row_id is an integer, if we divide it by another integer any fractional result will be dropped. So, 0/50 = 0, ..., 49/50 = 0, 50/50 = 1, .... To include my_row_id = 50 in batch 1, we simply subtract 1 from my_row_id. And, to start with batch_no 1 instead of 0, we add one to get our batch_no.

NOTE: As noted in the comments, you can simplify the code slightly. If you use IDENTITY(50,0) when adding my_row_id, then the WHERE clause could be:

 WHERE (my_row_id / 50) = @batch_no

The first 50 rows would be numbered 50-99, and this equation would evaluate to 1 for them; for the next fifty rows (100-149), it evaluates to 2; and so on.


You'll probably end up with something looking a little like this:

This may be a little more involved than you first expected, but I'll take you through the whys and hows of it all ( doing this is generally much easier than explaining it, by the way ), starting with identifying objects.  Using "Noun-Verb" analysis, which is a fairly common OOAD technique, we start picking apart your requirements:
Right away, it becomes evident that at least Employee, Position / Job and Skill are needed.  Further along, a need for Department and Skill Group emerges, then finally Skill Level.  From here, things like Employee Surname are noted as well.
Some liberties are then taken here ( which is not abnormal in any way, considering the quality of the list of requirements you'd normally be extracting from a client ), but the noun list to be used for the general structure, in no particular order, is as follows:

Department ( D )
Department Code ( DC )
Department Name ( DN )
Position ( P )
Position Code ( PC )
Position Title ( PT )
Skill Group ( G )
Skill Group Code ( GC )
Skill Group Name ( GN )
Skill ( S )
Skill Code ( SC )
Skill Name ( SN )
Employee ( E )
Employee Code ( EC )
Employee First Name ( EF )
Employee Surname ( EL )
Level ( L )
Level Code ( LC )
Level Name ( LN )

Using these nouns, on a relatively simple level, relationships between them can be established using "is-a / has-a," that is, "Noun A is a Noun B" or "Noun A has a Noun B."  This is particularly useful for classifying our nouns into two categories, being an "attribute", an atomic entity, later called a column, or a "container," which is a collection, later called a table.  During the first round of this "is-a / has-a," the goal is generally to simplify the list, effectively removing the attributes from the picture, grouping them to their containers.
The naming convention used above goes a fairly long way to making this process simple.  For instance, an Employee ( E ) has an Employee First Name ( EF ) and an Employee Last Name ( EL ), forming one-to-one relationships between Employee ( E ) and the Employee First Name ( EF ) and Employee Last Name ( EL ) entities.  In this way, Employee ( E ) can be classified as a container, whereas Employee First Name ( EF ) and Employee Last Name ( EL ) become attributes.  Focusing on this reduction of nouns via one-to-one relationships, we can reduce the list above to the following, where the notation ( A: B, C ) means that entity A is a "container" with "attributes" B and C.  It follows that:

Department ( D: DC, DN )
Position ( P: PC, PT )
Skill Group ( G: GC, GN )
Skill ( S: SC, SN )
Employee ( E: EC, EF, EL )
Level ( L: LC, LN )

With this reduction, the one-to-many relationships can now be considered.  A Department ( D ) may have any ( a pluralized has a ) number of Positions ( P ) available, implicitly indicating a one-to-many relationship.  Extending the above notation, in addition to the attribute list, we can now add "compound" attributes.  More simply, a compound attribute is basically a container treated as an attribute.  This can be written as ( P: PC, PT, ( D: DC, DN ) ), which begins to highlight the places where foreign keys will materialize.  Similarly, Skill Group ( G ) may refer to a number of differing Skills ( S ).

( P: PC, PT, ( D: DC, DN ) )
( S: SC, SN, ( G: GC, GN ) )

The many-to-many relationships can then be addressed.  Position ( P ) may require any number of Skills ( S ), each with it's own Skill Level ( L ).  Conversely, a specific Skill ( S ) can be required for any number of Positions ( P ), at any particular Skill Level ( L ).  These associations manifest as a ( is a ) Position Skill ( PS ), effectively a "surrogate" container, having a compound attribute of Skill Level ( L ).  To use the phrasing above, each of these newly established Position Skills ( PS ) has a required Skill Level ( L ).  To maintain this many-to-one relationship between Position Skill ( PS ) and Level ( L ), a unique key is to be placed on the foreign keys Position ( P ) and Skill ( S ).

( PS: ( { U1 }( P: PC, PT, ( D: DC, DN ) ), { U1 }( S: SC, SN, ( G: GC, GN ) ), ( L: LC, LN ) )

A quick note: The { Un } is used to indicate both attributes ( themselves containers ) are part of a single unique key.  If the attributes were independently unique, we could use { U1 } and { U2 }.
Next, an Employee ( E ) can occupy any number of Positions ( P ), indicating that there is a Employee Position noun to be added to our list.  This particular relationship manifests over some period of time, thus having ( has a ) Start Date ( T1 ) and End Date ( T2 ).  However, there is no way to assert that a normally part-time Employee ( E ) could not be serving under two separate Positions ( P ) in a full-time manner simultaneously in the interim before a new hire, nor that a previous Employee ( E ) could be re-hired or otherwise return to a Position ( P ) previously held by that Employee ( E ).  This dilemma results in a true many-to-many relationship, with Start Date ( T1 ) and End Date ( T2 ) as attributes.  As Start Date ( T1 ) and End Date ( T2 ) are not functionally dependent on anything other than the relationship between Employee ( E ) and Position ( P ), this structure is not in violation of 3NF.

( EP: ( E: EC, EF, EL ), ( P: PC, PT, ( D: DC, DN ) ), T1, T2 )

The final piece comes in the form of the relationship between an Employee's ( E ) specific term of employment, Employee Position ( EP ), the relevant Position Skills ( PS ) and the quality at which those specific Position Skills ( PS ) are realized, or simply, the achieved Level ( L ).  Much like the relationship established between Position ( P ) and Skill ( S ), there is a Employee Position Position Skill ( EPPS ) which has a compound attribute of Level ( L ) in a many-to-one relationship.  This again requires a unique key, leaving our noun list ( now expanded ) in the following state:

( EPPS:
    { U2 }( EP:
        ( E: EC, EF, EL ),
        ( P: PC, PT, ( D:
            DC, DN ) ),
        T1, T2 ),
    { U2 }( PS:
        ( { U1 }( P:
            PC, PT, ( D:
                DC, DN ) ),
        { U1 }( S:
            SC, SN, ( G:
                GC, GN ) ),
        ( L:
            LC, LN ) ),
    ( L:
        LC, LN ) )

That, of course, is a bit on the crazy side to read, so we extract the definitions we've reduced the problem to in an actionable way.  Here, I've added additional unique keys simply for the purpose of completeness.  Note that there are no soon-to-be tables which contain more than one independently unique key, so { U1 } is used throughout, per table:

( D: { U1 }DC, DN )
( P: { U1 }D, { U1 }PC, PN )
( G: { U1 }GC, GN )
( S: { U1 }G, { U1 }SC, SN )
( L: { U1 }LC, LN )
( PS: { U1 }P, { U1 }S, L )
( E: { U1 }EC, EF, EL )
( EP: { U1 }E, { U1 }P, T1, T2 }
( EPPS: { U1 }EP, { U1 }PS, L }


How to convert rows values into different columns based on the values in a Column in SQL Server 2012

This is a typical case of a Pivot Table.

You can do it in standard SQL using the following query:

SELECT
    VendorID,
    max(case when OPTFIELD = 'CHANNELAP'  then VALUE end) AS level_2,
    max(case when OPTFIELD = 'CHANNELAP'  then VDESC end) AS level_2_desc,
    max(case when OPTFIELD = 'CUSTGROUP'  then VALUE end) AS level_3,
    max(case when OPTFIELD = 'CUSTGROUP'  then VDESC end) AS level_3_desc,
    max(case when OPTFIELD = 'SUBCHANNEL' then VALUE end) AS level_4,
    max(case when OPTFIELD = 'SUBCHANNEL' then VDESC end) AS level_4_desc
FROM
    t
GROUP BY
    VendorID
ORDER BY
    VendorID ;

Assumption: (VendorID, OPTFIELD) is UNIQUE (and probably, the PRIMARY KEY of the table). This way, the max function will actually take the only actual value.

MS SQL server also has a specific PIVOT statement, but the syntax to use is a bit awkard when you want to have two different columns from each row:

SELECT
    VendorID,
    [CHANNELAP]   AS level_2,
    [dCHANNELAP]  AS level_2_desc,
    [CUSTGROUP]   AS level_3,
    [dCUSTGROUP]  AS level_3_desc,
    [SUBCHANNEL]  AS level_4,
    [dSUBCHANNEL] AS level_4_desc
FROM
(
   SELECT
       VendorID, OPTFIELD, [VALUE]
   FROM
       t
   UNION
   SELECT
       VendorID, 'd' + OPTFIELD, [VDESC]
   FROM
       t
) AS SourceTable
PIVOT
(
    max(VALUE)
    FOR OPTFIELD IN (CHANNELAP, CUSTGROUP, SUBCHANNEL, dCHANNELAP, dCUSTGROUP, dSUBCHANNEL)
) AS PivotTable;

dbfiddle here

NOTE: I am not sure which of the queries is more efficient. You should experiment yourself with your actual data. The PIVOT syntax of MS SQL Server is probably more efficient when you're working with just one column to pivot. But your case is not exactly the one this statement was intended for (AFAIK).

Check also

Convert Rows to columns using 'Pivot' in SQL Server, for the "typical" PIVOT case;
PIVOT on two or more fields in SQL Server, although the case is not exactly yours.
TSQL PIVOT multiple columns, which is very close to your case, and offers a slightly different alternative


Shrink logs in between user transaction in SQL Server

Don't shrink the log file - just ask yourself if you'll need to recover to part way through the process, and use this to decide whether to use Full or Simple.

If you're going with Simple, then checkpointing regularly will be enough. Leave the log file large and then consider what to do with it after the whole process. If you shrink it each time it'll only grow for the next iteration.

If you're going with Full, then do a lot of backups, figuring out how to get them off somewhere. Again, leave the log file big and figure out if you need to shrink it at the very end.

Edit based on comments below: making sure the database being checkpointed is the same one as has the growing log file is important too. :)


Operand data type nvarchar is invalid for multiply operator error

Some of your fields are text (char) fields, you should use CAST/CONVERT or TRY_CONVERT before to use it on numeric operations.

CREATE TABLE TEST (A VARCHAR(10), B VARCHAR(10), C DECIMAL(18,2));
INSERT INTO TEST VALUES
('10.2', '20.2', 5.5),
('5.2', '7.4', 10.0);
GO

2 rows affected

SELECT SUM(A * C) F1,
       SUM(B),
       SUM(C)
FROM   TEST;
GO

Msg 8117 Level 16 State 1 Line 2
Operand data type varchar is invalid for sum operator.

SELECT SUM(TRY_CONVERT(DECIMAL(18,2), A) * C) F1,
       SUM(TRY_CONVERT(DECIMAL(18,2), B)) F2,
       SUM(C) F3
FROM   TEST;
GO

F1       | F2    | F3
:------- | :---- | :----
108.1000 | 27.60 | 15.50

dbfiddle here


prevent MySQL Workbench to add index for foreignkey constraint when it is first part of primary

That's currently a limitation of MySQL Workbench and has been mentioned recently already on Stackoverflow (can't find the question right now, sorry). MySQL Workbench doesn't use the special case where a foreign key uses columns that start the PK in that table. Please file a feature request at http://bugs.mysql.com if you think it would be worth implementing it.


I have setup with FCI+AG solution but with independent disks

The FCI doesn't work without shared storage, so I'm going to assume you meant to say, "I have setup with FCI+AG solution but with asymmetric storage".

  There is no listener, and the applications are connecting to the FCI. Is this setup even reasonable to use?

The no listener part is going to severely impact transparent DR, but this setup is possible although not wise. Is it reasonable to use? Sure, just not in its current form - which is mostly due to the connecting through the FCI name and not a listener. Everything else is fine.

  The only option I see here is manual fail over.

The AG will be manual failover, yes. However, there is a huge asterisk here which is that whenever AGs are used in conjunction with FCIs, there is no automatic failover at the AG level. The automatic failover will happen at the FCI level and the only failover method for AGs would be manual.

  If I need to make any changes, what can be done?

What kind of changes are you looking to make? Personally, at the minimum I'd setup a listener and have the applications use that. Otherwise it's going to be an even more painful AG failover experience.

  Can I add a listener to the AG because both the nodes are in sync?

Adding a listener does not require the AGs to be set to a specific availability mode. AGs can have 0 or more listeners (only one available to be added via the GUI) and be in any availability mode.

  From a storage perspective was this supposed to have a Shared SAN instead of independent disks?

The FCIs are using shared storage... it's a requirement. It's not a requirement to have shared storage for AGs.


Why does the MS SQL Server change tracking table store all DML operations that occurred within the transactiton?

Change Tracking keeps watch over every single DML operation; it has no concept of a transaction. So if you change a row 5 times, whether in a transaction or not, you're going to get 5 rows. From Microsoft's documentation:

  In change tracking, the tracking mechanism involves synchronous tracking of changes in line with DML operations so that change information is available immediately.

It doesn't contain any more complicated logic than that (like only recording the net change). You can of course determine net changes by using the Change Tracking functions instead of querying the CT table directly. You can manually collapse duplicate rows in the change tracking table by grouping in your query, or maybe Change Data Capture might be more what you're after?

If you want something that is transaction-aware and only records net changes, you can look at Temporal Tables. You're going to have to move to a version of SQL Server produced this decade, though (2016 or 2017).


I wanted to just run a SELECT statement against a table to see the
  value.

  Question

  Is there anything I can do at all?

To find your permissions on the object you can use the function sys.fn_my_permissions

Just run this query to figure out your permissions on that table:

 use your_db;
 select *
 from sys.fn_my_permissions ('your_table_name', 'object')

If you have no SELECT permission on the object, maybe at least you have SELECT permission on subset of its columns.

In any case you can check your database permissions this way:

 use your_db;
 select *
 from sys.fn_my_permissions (null, 'database')

If you are fortunate and see CONTROL permission among them, there is a possibility to re-grant missing permission to you.

It can be even this situation: you are db_owner of this database, but someone used explicit DENY on this table to your user.

Some db_owners underestimate the power of being db_owner, so don't give up at the first

  The SELECT permission was denied on ...

and continue to explore what permissions you have on the server and in the database of interest.

First, check out your server level permissions like this:

select *
from sys.fn_my_permissions(null, 'server');

I think you have nothing interesting at the server level, at least from what you said it seems you lack VIEW ANY DEFINITION permission and of course you are not sysadmin.

Then check your database roles like this:

select *
from sys. user_token;

This gives you not only the roles you are member of, but it gives you all the users under which you can reach this database.
It can be usefull in case you are Windows login that is a member of many Windows groups mapped to this database, because simple select user
will return your win account (we are talking about Windows Authentication now).

I think this is all I can suggest you in your situation. Explore what permissions you have, what roles you are member of, and then may be it will be possible to gain the access on your table

Second supplementary part

And now I want to give my version of how it can be that OP sees only sa login
while he is not sa and don't see any other login while logged in as a Windows principal

Yes it's true that compatibility view syslogins does not show all security principals: at least it does not show fixed server roles.
But it has no problem with Windows principals, I controlled it on SQL Server 2014 as well.

So how it can be possible that OP sees only sa when querying syslogins logged in as Windows principal? Why doesn't he see his own win login, be it win account or just a win group, BUILTIN\Users or smth like this?

The simple answer is maybe OP has mistaken and did see another login but told us he was seeing only sa

The other and the only answer that I can give is this:
there is simply NO WIN USER mapped to this server

This situation can be reproduced only on versions 2005/2008/2008 R2 by granting CONNECT SQL to public role

So here is the scenario:
We are on SQL Server 2008 R2 and someone granted CONNECT SQL to public server role.

Well, in this case ALL THE ACTIVE DIRECTORY can log on to SQL Server without having no corresponding login at all!

Here in the picture the Windows principal hp2\Mary was successfully logged on my local instance (instance need not to be local, it's just a repro on my notebook) without having any corresponding login at all.

Mary is not a member of BUILTIN\Administrators (maybe it was even better drop that login at all to have purer repro), and what she see as logins is only sa.

And executing OP's code she's got only sa as well

Just to prove that there is no fake and my local 2008 R2 instance is full of logins and does not have only sa I attach the whole screenshot of my notebook with 2 SSMS the first one started under sysadmin Anna and another one under login without permissions and even without mapping: Mary

UPDATE
I figured out another situation that can lead to what OP was seeing:

CONTAINED DATABASE

Starting with 2012, database can be configured as CONTAINED.

In this case database, not a server, can authenticate a user.
The user will see only the database where it was created and (surprise!) syslogins view, that in my opinion should be unaccessible at all to the user,
still shows sa principal. Of course it shows no win login of a user as it just does not exists at all


Read from partition in local cassandra node only

You can do it as following (classes names, etc. are for driver 3.x, could be slightly different in 4.x):

Identify what token ranges are handled by specific node - you can obtain this information from Metadata class as cluster.getMetadata().getTokenRanges(keyspace, host) (see doc);
for every token range repeat:

generate the query like select * from table where token(part_keys) > rangeStart  AND token(part_keys) <= rangeEnd (but this may not work all the time, as you need to handle cases when node's token range is split between end of token ring and begin of token ring - see linked code)
create an instance of Statement for each query (for example, as SimpleStatement), and set consistency level  to LOCAL_ONE and host to which query should be sent (via setHost function)
perform query using the execute or executeAsync functions and process data

Source code that performs a full cluster scan could be found here - you can reuse pieces of it regarding generation of queries on token ranges, etc.


Here is one of the ways to do it!

First you need to convert the t2 table as columns provided you are certain about the values of the game.

SELECT
    max(CASE WHEN game = 'A' THEN game END) GAME1,
    max(CASE WHEN game = 'B' THEN game END) GAME2,
    ID
FROM T2 GROUP BY ID

This will give you something like

 game1 game2   ID
  A      B    1001
  A     NULL  1002

Now join this with your t1.

SELECT t1.id, t1.name, S1.GAME1, COALESCE(S1.GAME2, '') FROM t1
    JOIN (SELECT
                max(CASE WHEN game = 'A' THEN game END) GAME1,
                max(CASE when game= 'B' THEN game END) GAME2,
                ID FROM T2 GROUP BY ID) S1
        ON t1.id = S1.ID

Keep in mind that this will be more lengthy when the game types increase.


how to insert values in a table with foreign key field

There is no information on the definition of your table (what foreign key?) but based on your SQL you should use a select to enter it:

insert into ques_bank
    (q_no, uid, question)
select null, uid, 'jdkjfsd dkskdsn skdjsd' from user_info where username ='more';

or:

insert into ques_bank
    (uid, question)
select uid, 'jdkjfsd dkskdsn skdjsd' from user_info where username ='more';


Try this then instead:

select
  k.location,
  sum((case when k.pet = 'Cat' then 1 else 0 end)) as cats,
  sum((case when k.pet = 'Dog' then 1 else 0 end)) as dogs,
from pet k, location lo where k.location= lo.id and k.pet in ('Cat', 'Dog')
group by k.location
;

I am not sure what you need location lo for, seems you have all the necessary information in pet k.


Does SELECT INTO Reserve the #Object Name in TempDB Prior to Runtime?

This has nothing to do with object name reservations in TempDB or anything to do with runtime. This is simply the parser not able to follow logic or code paths that assures that your code couldn't possibly try to create that table twice. Notice that you get the exact same (non-runtime!) error if you just click the Parse button (Ctrl+F5). Basically, if you have this:

IF 1=1
  CREATE TABLE #foo(id1 INT);
ELSE
  CREATE TABLE #foo(id2 INT);

The parser sees this:

  CREATE TABLE #foo(id1 INT);
  CREATE TABLE #foo(id2 INT);

Why does it not work this way for actual tables, including actual user tables created in TempDB (note that it isn't database-specific, either)? The only answer I can suggest is that the parser has a different set of rules for #temp tables (there are a lot of other differences, too). If you want more specific reasons, you'll need to open a case with Microsoft and see if they'll give you any further details. My guess is you will be told: "this is the way it works."

Some more info in these answers:

Is it "legal" to CREATE and DROP #SomeTable more than once?
Can I recreate a temp table after dropping it?


Renaming multiple databases on a MySQL slave

MySQL Slave is used as Development Server! Better not to.
Otherwise, let the replication happen with the same db / table names. And let the db used for development be named differently - which you can handle in the program code itself (one dbname for dev and another for production). You can have an environment section which names the db and several other required too for dev and production.
If at all you prefer this...assuming that the dbname changes and not table names...create individual mysqldump per db and then mysql insert each dump separately into your preferred db.


fn_dblog not showing up deleted table data

From Thomas LaRock at http://logicalread.solarwinds.com/sql-server-dbcc-log-command-tl01/#.VXbj_DYpDVk

"The fn_dblog() function (formerly known as the DBCC command) is one of several undocumented functions for SQL Server; it allows you to view the transaction log records in the active part of the transaction log file for the current database."

The fact that you can no longer find your data suggests that the desired part of the log is no longer in the active part of the transaction log file.

One way to get these 4 rows back would be to restore a copy of the database to a temporary database name (e.g. DbName_Disaster) at a point in time that contained these 4 rows. Then insert the 4 rows back into your production database and drop the DbName_Disaster database.

Or you could look for some database recovery tool that fits your need.  The prices and features vary.


Incremental backup in postgresql in windows

Yes, it is right that the incremental backups are not possible in postgresql.

I don't know if there is an alternative for pg_rman in windows but you can achieve the incremental backup in postgres with the continuous archiving. Which can be later restored with PITR (point in time recovery)

Details on:
https://www.postgresql.org/docs/current/static/continuous-archiving.html


Use MySQL to match input against saved queries

Invert.  Hmmm...  Indexing is about inverting.  Let's see if we can apply that concept here.

I see two types of tests in your example -- "set" operations (color) and "range" operations (size).

Suppose we could build the following into some kind of table?

blue  --> Bob, Alice
red   --> Bob
green -->      Alice, Tom

size <= 5 -->      Alice
     5-7  --> Bob, Alice
     7-10 --> Bob, Alice, Tom
     >10  --> Bob, Alice

Range.  I have a hammer; I want your problem to be a nail.  My hammer is a way to index ip-address ranges for very efficient lookup.  It is so efficient that the "size" test becomes a single hit to the table that will find the set of persons that match.  This, in my mind, solves the "range" part of your problem.

Set.  This part is simply to map one color to a collection of persons.

Collection of persions

Plan A:  the datatype SET is limited to 64 persons.  Not acceptable for "tens of thousands".

Plan B:  a BLOB of bits, each bit representing one person.  This has the distinct advantage of AND and OR now become bitwise operations.  Drawback:  bitwise operations only work with SET and BIGINT.  That is, until MySQL 8.0, when it works on BLOB.  Also, I have implemented bitwise operations on `BLOB.  Another issue:  the bit strings may be very sparse?  So, Plan B is a "maybe".

Plan C:  an ordinary table:  (blue,Bob),(blue,Alice),(red,Bob), ...  Plan C may be the best.  Necessarily, the PRIMARY KEY should be (color, person) to take advantage of 'clustering'.

References

Efficient IP address lookup - for your ranges

Unique Users - someday I may publish bitwise manipulations on BLOB here.

Bottom line.  I have given you some food for thought.  And there is a size limit on Answers in this venue.  And this is a bigger task than I am willing to do for free.


How to Reduce Mysql Size After Dropping Table

You seem to have leftover some temporary file of the ARCHIVE engine: https://dev.mysql.com/doc/refman/8.0/en/archive-storage-engine.html

This engine is barely supported, and I would not be surprised that is has bugs. Assuming there is no .arz file, and that no client is accesing it, it should be safe to drop. One recommendation I always follow is to rename or backup any db-related file before removing it. That will give you an extra guarantee in the worst case possible.

As an extra comment- avoid archive engine at all costs- if you need a compressed table you can use InnoDB, TokuDB or RocksDB.


Considering the symptomp of gradual degradation after each batch I would guess is caused by DELETE leaving behind ghosted records, see SQL Server “empty table” is slow after deleting all (12 million) records? Subsequent deletes have to skip over all those ghosts, until they are reclaimed. For a one time operation it would make more sense to copy the data to be kept into a new table, then use partition switch tricks to switch it in instead of the old data, then truncate the old data. If you can't do ALTER TABLE .. SWITCH you can use the old sp_rename trick, but bear in mind that sp_rename does not preserve security permissions etc on the table (it is a different table with the same name).

If this deletion is to be done regularly, then you may look into a sliding window scenario.


MySQL switch between data directories for individual databases

Rather than retype a lot information, here are some posts I made in the DBA StackExchange before on how to move MyISAM tables to different folder and disks

Dynamically changeable database
Can I specify a different disk for a specific MySQL table?
Mail alert from MySQL when databases are edited (I suggested creating a MyISAM-based general log that would stored on another disk. I did this for a client long ago and collected over 125GB of log info)


Compare two columns in two databases to insert the content of a third column into a forth one

It's not clear what's the link between your 2 tables.

If the link between your tables is through Code and Code2:

UPDATE T2 SET
    ID2 = T1.ID
FROM
    b.dbo.table1 AS T1
    INNER JOIN w.dbo.table2 AS T2 ON T1.Code = T2.Code2

If the link is through ID and the comparison by Code:

UPDATE T2 SET
    ID2 = T1.ID
FROM
    b.dbo.table1 AS T1
    INNER JOIN w.dbo.table2 AS T2 ON T1.ID = T2.ID
WHERE
    T1.Code = T2.Code2

I'm assuming that you meant an UPDATE of the column ID2 when the codes match and not creating a new row just with the ID2 value (which would require an INSERT operation).


The query in the linked answer is a "hack" as the OP there clearly describes in a comment and can stop working any time with a MySQL upgrade as the exact behaviour of session variables is not documented.

Aside from that, the question there was about finding all ancestors, not all descendants. In adjacency lists, a node has maximum one parent but possibly many children. So this query/hack will not work in your case.

The Adjacency List model is the simplest of all hierarchy models in SQL. The other 3 (Nested Sets, Evaluated Path, Closure Table) are more complicated because - in a way - store redundant information. In exchange, some queries are far more easier to write. In any of the other 3 models, the solution would be really simple and wouldn't need a recursive query.

So, it seems you have two options:

either evaluate your decision to use the Adjacency List model and use a different one (or a combination of models)
write a procedure / function that solves the issue. @RolandoDBA has an old answer on this site, that provides several procedures that solve this (and related) problems. I'm not sure how efficient they are but they surely work:
Find highest level of a hierarchical field: with vs without CTEs.

You'll find more useful information in the links:

Trees and Other Hierarchies in MySQL, in Get It Done With MySQL 5&6, Chapter 20. Copyright © Peter Brawley and Arthur Fuller 2011.
EXPLAIN EXTENDED: Hierarchical queries in MySQL, by Quassnoi (yes, it's the same one that posted the query you tried.) In his blog, he has several hierarchical queries that can find ancestors, descendants, etc.
Models for hierarchical data, for MySQL, by Bill Karwin. A slideshare describing the pros and cons of various hierarchical models in MYSQL. Slightly skewed in favour or the Closure Table.


Unknown number of potential values in a table from a foreign table

Every character has one and only one race, right? So it seems like that field should be on the Character table: TableA(?) Each character could have multiple classes so that should probably be in a one-to-many relationship in the CharacterClass table which has the primary key of both Character and Class tables.

As for the Proficiencies (TableB), each character can have zero to many proficiencies so they must be stored in their own table, but if you want to produce a 'report' which shows all the proficiencies as a delimited list, then you could do it like this:

Select c.id,
    proficiencies = Stuff((Select ',' + proficiency
                             From #character_proficiency As cp
                             Where cp.character_id = c.Id
                             For XML Path ('')), 1, 1, '')
  From #character As c;


This will do what you want. If there is no row with check_type = 'scheduled' in the specified interval, it will return no rows at all. If you want all the rows instead, it will have to be slightly modified:

WITH find_schedule AS
  ( SELECT created_at
    FROM project_check_history
    WHERE check_type = 'scheduled'
      AND checked_at <= '2016-09-26'
    ORDER BY checked_at DESC
    LIMIT 1
  )
SELECT  -- DISTINCT
    created_at
FROM project_check_history
WHERE checked_at <= '2016-09-26'
  AND checked_at >= (TABLE first_schedule) ;


SSIS: How do I create tables using Foreach Loop Container?

Set the Foreach Loop as a Foreach File Enumerator pointed at your .sql files
Create a variable and map the Fully qualified file name to it
Set the Execute SQL Task's SQLSourceType to File connection, and create a new file connection for it
Set the Connection String expression on the file connection to your file name variable


From Erwin's suggestion above about using a tstzrange exclusion constraint, this seems to have the desired effect:

Firstly, as the admin user postgres I had to execute this CREATE EXTENSION btree_gist on the target database (PostgreSQL 9.2.3 on OpenBSD).

Then as the database user:

CREATE TABLE subscribed_dns_domain
(
  subscription integer NOT NULL,
  dns_domain integer NOT NULL,
  active_period tstzrange NOT NULL DEFAULT tstzrange(now(), NULL::timestamp with time zone, '[)'::text),
  customer_add_reference character varying(40),
  customer_delete_reference character varying(40),
  CONSTRAINT subscribed_dns_domain_pk PRIMARY KEY (subscription, dns_domain, active_period),
  CONSTRAINT subscribed_dns_domain_dns_domain_fk FOREIGN KEY (dns_domain)
      REFERENCES dns_domain (id) MATCH SIMPLE
      ON UPDATE CASCADE ON DELETE RESTRICT,
  CONSTRAINT subscribed_dns_domain_subscription_fk FOREIGN KEY (subscription)
      REFERENCES subscription (id) MATCH SIMPLE
      ON UPDATE CASCADE ON DELETE RESTRICT,
  CONSTRAINT subscribed_dns_domain_single_active_ek EXCLUDE
  USING gist (dns_domain WITH =, active_period WITH &&)
)

New subscriptions to domains are easily added:

INSERT INTO subscribed_dns_domain (subscription, dns_domain) VALUES (2, 4);
INSERT INTO subscribed_dns_domain (subscription, dns_domain) VALUES (2, 5);
INSERT INTO subscribed_dns_domain (subscription, dns_domain) VALUES (3, 6);

With it baulking on duplicate domain names with overlapping subscription time ranges (as desired):

INSERT INTO subscribed_dns_domain (subscription, dns_domain) VALUES (2, 6);

ERROR:  conflicting key value violates exclusion constraint "subscribed_dns_domain_single_active_ek"
DETAIL:  Key (dns_domain, active_period)=(6, ["2013-10-10 20:43:55.891139+01",)) conflicts with existing key (dns_domain, active_period)=(6, ["2013-10-10 20:11:48.54009+01",)).
********** Error **********

Subscriptions are easily ended:

UPDATE subscribed_dns_domain
SET
    active_period = tstzrange(lower(active_period), now())
WHERE
    subscription = 2 AND
    dns_domain = 4 AND
    upper(active_period) IS NULL

Active subscriptions are easily found:

SELECT
    subscription,
    dns_domain
FROM
    subscribed_dns_domain
WHERE
    upper(active_period) IS NULL

 subscription | dns_domain
--------------+------------
            2 |          5
            2 |          6
(2 rows)

As is historical data:

SELECT
    subscription,
    dns_domain,
    lower(active_period) AS added_at,
    upper(active_period) AS deleted_at
FROM
    subscribed_dns_domain
WHERE
    upper(active_period) IS NOT NULL

 subscription | dns_domain |           added_at            |          deleted_at
--------------+------------+-------------------------------+-------------------------------
            3 |          6 | 2013-10-10 20:10:14.269551+01 | 2013-10-10 20:11:33.467619+01
            2 |          4 | 2013-10-10 20:10:09.611487+01 | 2013-10-10 20:48:15.64681+01
(2 rows)


Lets call them A (the master) and B (the slave).

  1) who will be the master and who will be the slave when master fails?

When master (A) fails, you'll have only a slave (B) working, trying to connect to its master - which will not be responding. The slave will still be able to accept read but not write requests.

You need to "promote" the slave (B) from its "slave" status to "master", in order to have a database that accepts write requests.

  1b) who will be the master and who will be the slave when master fails - and I promote the slave?

You will have a single master (B), no slave.

  2) when slave becomes as a master how much time it will take to get it back fail-over master as slave?

You will need to setup a new slave (C) or use the old master (A) as a slave of the new master (B). Use pg_rewind, rsync or pg_basebackup if you want to reuse the old master as a new slave. With pg_rewind or rsync you can be back up and running with a new slave very quickly (seconds possibly, if automated), or within minutes if done manually.


The catalog view sys.sql_expression_dependencies gets you close. For example:

SELECT
  [A view] = s.name + '.' + v.name, 'references',
  [B object] = d.referenced_database_name + '.'
             + d.referenced_schema_name + '.'
             + d.referenced_entity_name
FROM DatabaseA.sys.views AS v
INNER JOIN DatabaseA.sys.schemas AS s
ON v.[schema_id] = s.[schema_id]
INNER JOIN DatabaseA.sys.sql_expression_dependencies AS d
ON v.[object_id] = d.[referencing_id]
WHERE d.referenced_database_name = N'DatabaseB';

It does not provide column names, however.

Unfortunately other catalog views like sql_dependencies are unreliable for this kind of thing, since they don't follow dependencies outside the scope of the current database. And it's deprecated anyway. However there are more reliable views, like sys.dm_sql_referenced_entities:

SELECT
  [A view] = s.name + '.' + v.name, 'references',
  [B column] = r.referenced_database_name + '.'
             + r.referenced_schema_name + '.'
             + r.referenced_entity_name + '.'
             + r.referenced_minor_name
FROM DatabaseA.sys.views AS v
INNER JOIN DatabaseA.sys.schemas AS s
ON v.[schema_id] = s.[schema_id]
CROSS APPLY DatabaseA.sys.dm_sql_referenced_entities
(
  QUOTENAME(s.name) + '.' + QUOTENAME(v.name), N'OBJECT'
) AS r
WHERE r.referenced_minor_id > 0
AND r.referenced_database_name = N'DatabaseB';

Some other links that might help:

Understanding SQL Dependencies
Troubleshooting SQL Dependencies

Also understand that dependencies are hard, they've changed the approach several times since SQL Server 2000, and we've had a long history of employing workarounds to ensure that the information is current and consistent (but still tend to fail on a regular basis).


query processor ran out of internal resources and could not produce a query plan

Look for queries with very long IN lists, a large number of UNIONs, or a large number of nested subqueries. These are the most common causes of this particular error message in my experience.

Occasionally the issue can be resolved by applying a product update (service pack or cumulative update) or enabling a supported trace flag, but more often the fundamental issue is the unusual SQL generated by some tools or data abstraction layers. The latter will require application changes, unfortunately.

Enabling documented trace flags 4102, 4118, 4122 (or the covering 4199) may also avoid the issue you are seeing. Review the documentation to see if they address the root cause in your case:

Microsoft Knowledge Base article for TF 4122
Microsoft Knowledge Base article for TF 4102, 4118
Microsoft Knowledge Base article for TF 4199


If an index hasn't been touched at all since start-up, then it isn't in the result from sys.dm_db_index_usage_stats. Doing an outer join to that DMV means columns you pick from it will be NULL. As for those that are 0, they have been touched according to sys.dm_db_index_usage_stats, since it does return a row for that index.

You should be able to verify doing a SELECT * FROM sys.dm_db_index_usage_stats without involving sys.indexes and see that for "the NULLs" the index isn't retured and for "the 0's" the index is returned and potentially some system scans etc.


Functions cannot return data to a client so I can't think of any use of SET NOCOUNT ON. If you do try to add it a function you'll get an error. SQL Server considers it to cause a side effect:

  function_body

  Specifies that a series of Transact-SQL statements, which together do not produce a side effect such as modifying a table, define the value of the function.

For example, the follow code throws an error:

CREATE FUNCTION X_JRO_TEST()
RETURNS INT
AS
BEGIN
SET NOCOUNT ON;
RETURN 1;
END;

  Msg 443, Level 16, State 15, Procedure X_JRO_TEST, Line 5

  Invalid use of a side-effecting operator 'SET OPTION ON' within a function.


Migrating a tablespace to a different database on the same ASM

Transportable tablespaces and asmcmd cp command should do the trick.

Prepare transportable tablespaces metadata set.
Copy expdp file to location accessible by impdp from destination database.
asmcmd cp datafiles to new location.
Import TT metadata set.

If your ASM diskgroup uses some storage device with several disks then you should be able to improve performance by copying several datafiles in parallel.