Your query is missing AND C.Accommodation_ID = B.Accommodation_ID

If you are just getting started with SQL then you might consider building simple queries like this in the Access query designer and then switch to SQL view to see what the query designer has generated. In your case we have

and Access generates the following SQL (which I've indented for clarity)

SELECT
    Instance.Description,
    City.CityName,
    Accommodation.CategoryType
FROM
    (
        City
        INNER JOIN
        Instance
            ON City.City_ID = Instance.City_ID
    )
    INNER JOIN
    Accommodation
        ON Instance.Accommodation_ID = Accommodation.Accommodation_ID
WHERE (((City.CityName)="Melbourne") AND ((Accommodation.CategoryType)="Hotel"));

Note that the query uses INNER JOIN syntax, which is generally preferred over the older method of putting the join conditions in the WHERE clause.


Decode URL encoded characters in inline table valued function?

While I'm not sure about the performance for you particular needs, here is an example of using a TranslationTable CTE along with a recursive CTE which will find and replace values in the incoming string to the TVF.

DROP FUNCTION IF EXISTS dbo.itvfURLDecode
go
CREATE FUNCTION dbo.itvfURLDecode
(
    @StringValue VARCHAR(MAX)
)
RETURNS TABLE

AS
RETURN (

--Common Table Expression for Translation
WITH TranslationTable
AS (
    SELECT x.FindValue,x.ReplaceValue,ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS rn
    FROM (SELECT NULL AS a) a
    CROSS APPLY (
        --list of Find/Replace values
        VALUES ('%2B','+')
            ,('%20',' ')
            ,('%3C','<')
            ,('%3E','>')
            ,('%3F','?')
            ,('%25','%')
        ) x(FindValue, Replacevalue)
    )
--Recursive CTE to loop through the TranslationTable and replace FindValue with ReplaceValue
,RecursiveCte as
(
SELECT @StringValue AS StrValue
    ,(
        SELECT count(*)
        FROM TranslationTable
        ) AS cnt

UNION ALL

SELECT replace(StrValue, tt.FindValue, tt.Replacevalue)
    ,cnt - 1
FROM RecursiveCte
JOIN TranslationTable tt
    ON tt.rn = cnt )

SELECT StrValue
    ,cnt
FROM RecursiveCte where cnt = 0
    )
go

--Verify translation
SELECT *
FROM dbo.itvfURLDecode('This%2Bis%20a%20test%3C%3E%20ok%3F%20100%25')
OPTION (MAXRECURSION 32767) -- Don't forget to use the maxrecursion option!

| StrValue                  | cnt |
|---------------------------|-----|
| This+is a test<> ok? 100% | 0   |

You can actually see the progression of the recursive CTE if you comment out the --where cnt = 0 towards the bottom of the TVF

| StrValue                                    | cnt |
|---------------------------------------------|-----|
| This%2Bis%20a%20test%3C%3E%20ok%3F%20100%25 | 6   |
| This%2Bis%20a%20test%3C%3E%20ok%3F%20100%   | 5   |
| This%2Bis%20a%20test%3C%3E%20ok?%20100%     | 4   |
| This%2Bis%20a%20test%3C>%20ok?%20100%       | 3   |
| This%2Bis%20a%20test<>%20ok?%20100%         | 2   |
| This%2Bis a test<> ok? 100%                 | 1   |
| This+is a test<> ok? 100%                   | 0   |


Upgrade all MySQL columns, tables, and databases from utf8mb3 to utf8mb4

Here is a query what will generate all the necessary update statements.

It temporarily disables foreign key checks so that the update statements succeed.
It updates the default character set for each database that needs it.
It updates the default character set of each table:

If it doesn't have the right default character set.
If it contains text columns that are not the right character set.
The same ALTER TABLE ... CONVERT statement both updates the table default and all the columns within the table.

SELECT
    /* Disable foreign key checks temporily to be able to make these changes */
    'SET FOREIGN_KEY_CHECKS = 0;' AS alter_statement
UNION SELECT
    /* Alter the default character set of each database */
    CONCAT('ALTER DATABASE `', SCHEMA_NAME,'` CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;') AS alter_statement
FROM
    information_schema.SCHEMATA
WHERE
    DEFAULT_CHARACTER_SET_NAME!='utf8mb4' AND
    SCHEMA_NAME NOT IN('mysql','information_schema','performance_schema','sys')
UNION SELECT
    /* Alter the default character set of each table .
      This also converts all text columns in the table,
      So there is no need to have a statement to alter each
      column individually */
    DISTINCT CONCAT('ALTER TABLE `', TABLE_SCHEMA,'`.`',TABLE_NAME, '` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;') AS alter_statement
FROM
(
    SELECT
        /* Find all tables with a text column that isn't utf8mb4 */
        TABLE_SCHEMA, TABLE_NAME
    FROM
        information_schema.COLUMNS
    WHERE
        TABLE_SCHEMA NOT IN('mysql','information_schema','performance_schema','sys') AND
        CHARACTER_SET_NAME IS NOT NULL AND
        CHARACTER_SET_NAME!='utf8mb4'
    UNION SELECT
        /* Also find all tables that don't have the correct default character set */
        TABLE_SCHEMA, TABLE_NAME
    FROM
        information_schema.TABLES AS T
    JOIN
        information_schema.`COLLATION_CHARACTER_SET_APPLICABILITY` AS C ON C.collation_name = T.table_collation
    WHERE
        CHARACTER_SET_NAME!='utf8mb4' AND
        TABLE_SCHEMA NOT IN('mysql','information_schema','performance_schema','sys')
) AS TABLE_UPDATES
UNION SELECT
    /* Re-enable forign key checks */
    'SET FOREIGN_KEY_CHECKS = 1;' AS alter_statement
;

When run, it should generate output like:

SET FOREIGN_KEY_CHECKS = 0;
ALTER DATABASE `mydb` CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
ALTER TABLE `mydb`.`mytable` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
SET FOREIGN_KEY_CHECKS = 1;

Sources:

MySQL command querying all MyISAM database -- For how to query the information_schema to create UPDATE statements.
How to change collation of database, table, column? - Stack Overflow -- For the update statements.
How to convert an entire MySQL database characterset and collation to UTF-8? - Stack Overflow -- For the comment about disabling foreign key checks to be able to make these changes.
How do I see what character set a MySQL database / table / column is? - Stack Overflow -- For how to join TABLES and COLLATION_CHARACTER_SET_APPLICABILITY


Deploying Ola Hallengrens scripts - Index Optimization

The parameters will depend on what tasks you want, how often and when you want to run them. This is really up to you.
The default location is master db, you can put them elsewhere. But i dont see much benefit in doing that.
Run the script to create the command log table first.
The command log table can be used by the scripts to log events.
Availability groups are supported.

Most of these questions are answered in Ola's FAQ. I highly reccomend you read it.

Personally, I find the full maintenance solution is too much. I prefer to just call the tasks I want using the examples provided.

But it's worth running it in dev as it will give you working examples of how to create the agent jobs. There is no "right answer" with this stuff, you really need to experiment and select what is right for you.

One thing I struggled with is that if you want to back up to a network address you need to grant permission to the account that SQL Server uses. Therefore it should be a domain account.


Merge Replicate, reinitialise with a different part of the data

Please follow the steps in Create a Snapshot for a Merge Publication with Parameterized Filters to generate a new snapshot.  You must first generate a standard snapshot, then generate a Subscriber-specific partitioned snapshot.  After generating the snapshots, mark the Subscriber for reinitialization and start the Merge Agent to pull down the schema and data for the filtered subscription.


Can I emulate Oracle's autonomous transaction in MySQL?

If you have access to the Aria engine or another non-transactional engine, then yes.
Very short proof of concept, tested on MariaDB 10.5.4:
CREATE TABLE test_aria_engine ( a int, b varchar(1000) )
ENGINE=Aria DEFAULT CHARSET=utf8 PAGE_CHECKSUM=1 TRANSACTIONAL=1 TABLE_CHECKSUM=1
COMMENT='test-aria_engine with transactional on 1'

The transactional in Aria is not for real transactions, it is only last operation log, still good in case of server crash the data is consistent.
Now create an InnoDB table:
CREATE TABLE test_innodb_engine ( a int , b varchar(1000) )

And test:
BEGIN TRANSACTION;

-- insert in aria table
INSERT INTO test_aria_engine ( a, b) VALUES (4 , 'ddd');

-- insert in innodb table
INSERT INTO test_innodb_engine( a, b) VALUES ( 4 , 'dddd');

-- rollback
ROLLBACK;

After the ROLLBACK has been initiated let's check the table contents of the Aria and InnoDB tables:
SELECT * FROM test_aria_engine;

|a  |b  |
|---|---|
|4  |ddd|

So, ROLLBACK cannot rollback ARIA engine
SELECT * FROM test_innodb_table;

No result.

So in the InnoDB table ROLLBACK has done its job.
This shows how to avoid transaction logic in MariaDB or using a similar engine like MyISAM in MySQL.


Does MS provide a way to query a SSDT project

YES! You can query the output to an SSDT project called a dacpac using the DacFx.

The easiest way is to grab this sample project from microsoft (https://github.com/microsoft/DACExtensions/tree/master/DacFxStronglyTypedModel) and build the "DacExtensions" dll then reference that from your own code (c#, powershell etc.)

When you use the DacExtensions you can then you can ask for a list of tables or foreign keys, have a look at this:

https://stackoverflow.com/questions/40747082/finding-data-type-information-for-certain-objects-in-dacfx-api

The question shows how to get the TSQLModel and the answer has how to get a list of tables.

Good luck!


Is there a way to define macros/alias in psql?

\set fullexplain EXPLAIN (ANALYZE, BUFFERS, VERBOSE)

:fullexplain SELECT * FROM pg_stat;.

Probably not the most elegant thing I admit.

Also you could put this in .psqlrc.


Is connection string's default database used if queries are run against other databases?

Different performance of the same query typically indicates different execution plans.  The database context generally won't yield different plans (and performance) but there are some cases where it could.

The default collation of the current database context determines the collation of literals and parameters.  Below is an example that shows a slightly different execution plan when the query differs only by database context:

CREATE DATABASE TestDatabase
    COLLATE Latin1_General_CI_AS;
GO
CREATE DATABASE TestDatabase_SC
    COLLATE Latin1_General_100_CI_AS_SC;
GO

USE TestDatabase;
GO

CREATE TABLE dbo.TestTable(
    Col1 varchar(10) CONSTRAINT PK_TestTable PRIMARY KEY
    );
INSERT INTO dbo.TestTable VALUES('A');
GO

--query plan in TestDatabase context shows seek predicate: Seek Keys[1]: Prefix: [TestDatabase].[dbo].[TestTable].Col1 = Scalar Operator([@1])
SELECT Col1 FROM TestDatabase.dbo.TestTable WHERE col1 = 'A';
GO

USE TestDatabase_SC;
GO

--query plan in TestDatabase_SC context shows seek predicate: Seek Keys[1]: Prefix: [TestDatabase].[dbo].[TestTable].Col1 = Scalar Operator(CONVERT_IMPLICIT(varchar(8000),[@1],0))
SELECT Col1 FROM TestDatabase.dbo.TestTable WHERE col1 = 'A';
GO

I wouldn't expect a significant performance difference here because SQL Server coerces a sargable expression even though the literal and column data types differ.

The current database context also determines SET options used when not explicitly set by the client API or SET statement.  This may result in different execution plans, especially for legacy APIs.


Performance of query with a range condition and order by

I would say give that covering index a try (lset, frequency, word), but you did not give much information. Please post how many rows does your table have, how large in bytes it is, how many maximum rows are you expecting to get back from your query, what's the cardinality of your data?


Oracle implicit parameter casting - why doesn't it always work? (ORA-06502 error)

Interesting question - at first I would have said an overflow, but checking, that's ORA-01426 and if it wasn't a number, ORA-01722. Is it just datatype NUMBER which defaults to NUMBER(38)?

But this can be diagnosed with a trace to see what's exactly going on. If you can identify the session, or can modify the connection code, you can do:

SQL> alter session set events='6502 trace name errorstack level 12';

Session altered.

SQL> create or replace function fn_add(p1 number, p2 number) return number
  2  as
  3  v1 number(1);
  4  begin
  5  v1 := p1 + p2;
  6  return v1;
  7  end;
  8  /

Function created.

SQL> select fn_add('10', '1') from dual;
select fn_add('10', '1') from dual
       *
ERROR at line 1:
ORA-06502: PL/SQL: numeric or value error: number precision too large

Now looking in the tracefile:

---- Error Stack Dump -----
ORA-06502: PL/SQL: numeric or value error: number precision too large
----- Current SQL Statement for this session (sql_id=a36psfjj50bpv) -----
select fn_add('10', '1') from dual

That should at least get you started on root cause analysis. But be warned that these trace files can get very large, very quickly!


MongoDB hashtable namespace index max chain reached error after machine crash

OK I found my own answer.

Because the journal file seems corrupted, this solution will abandon all changes in the journal file so the database might be restored to a slightly earlier state before the crash

According to MongoDB documentation,

  At default intervals of 60 seconds, MongoDB asks the operating system to flush the shared view to disk.

the period of losing data should be within less than 60 seconds before crash by default.

The steps are as follows:

Turn off journal in the config. For me it's in /etc/mongod.conf and change journal=true to journal=false.
In the MongDB data directory (e.g. /var/lib/mongodb/, or for me it's /edx/var/mongo/mongodb/), remove both journal folder and mongod.lock file.
Run mongod with --repair option like sudo -u mongodb mongod -f /etc/mongod.conf --repair and wait. This operation may need a lot of disk space.
Turn on journal to journal=true as opposite to step 1, and start the server normally. E.g. sudo service mongod start.


Limit CPU usage without Resource Governor

You can use Windows System Resource Manager (WSRM), which is a feature in Windows Server (not sure of minimum version, but definitely 2008 R2+).

This will allow you to control the amount of CPU used by a process, so if you hadn't already separated out the rogue application to its own SQL Server instance, you would have had do that anyway.

At that point you can set whatever MAXDOP you want, as the process will never exceed the maximum limits you set in WSRM.

Setting the CPU affinity mask is an option. You'd want to completely isolate the application onto its own set of cores to eliminate contention (note: watch your NUMA nodes). If you have those extra cores available, then go for it, but I'd prefer the WSRM solution because when that application is idle, all the CPUs can be used by other applications.


A funny thing about SID_BINARY() - it outputs binary(28) in this case, whereas CREATE LOGIN expects binary(16). As Solomon points out, and adding here so there is no ambiguity, this means there is no way to create a SQL Server SID based on - or that can map to - the Windows identifier you posted in your question. You want CREATE LOGIN ... FROM Windows for that.

Your first problem, though, is the syntax error. The reason you're getting that is because you can't use expressions in a CREATE LOGIN statement, and it doesn't take variables either, so you need to construct dynamic SQL.

I tried to use all 28 bytes as follows:

DECLARE @sid_in varchar(100);
SET @sid_in = 'S-1-5-21-408552231-458724953-3089381293-513';

DECLARE @sql nvarchar(max) = N'CREATE LOGIN [Test] WITH PASSWORD = N''foo'',
  SID = ' + CONVERT(varchar(64), SID_BINARY(@sid_in), 1) + N';';

PRINT @sql;

Yielded:

CREATE LOGIN [Test] WITH PASSWORD = N'foo',
  SID = 0x01050000000000051500000027035A185996571BAD3724B801020000;

Of course, executing that SQL yielded an error, as I expected:

  Msg 15419, Level 16, State 1
  Supplied parameter sid should be binary(16).

So then I wondered if truncating to 16 bytes would work. I thought this was incompatible because losing 12 bytes should be a problem:

DECLARE @sid_in varchar(100), @sid_out varchar(100);
SET @sid_in = 'S-1-5-21-408552231-458724953-3089381293-513';

SELECT original = SID_BINARY(@sid_in);
SELECT trimmed  = CONVERT(binary(16), SID_BINARY(@sid_in));

Results:

original
----------------------------------------------------------
0x01050000000000051500000027035A185996571BAD3724B801020000

trimmed
0x01050000000000051500000027035A18

But then I tried it, and it (kind of) worked. Here is an example:

DECLARE @sid_in varchar(100);
SET @sid_in = 'S-1-5-21-408552231-458724953-3089381293-513';

DECLARE @sql nvarchar(max) = N'CREATE LOGIN [Test] WITH PASSWORD = N''foo'',
  SID = ' + CONVERT(varchar(64), CONVERT(binary(16),SID_BINARY(@sid_in)), 1) + N';';

EXEC sys.sp_executesql @sql;

And to prove that trimming those 12 bytes off has little effect:

DECLARE @sid_in varchar(100);
SET @sid_in = 'S-1-5-21-408552231-458724953-3089381293-513';

SELECT SUSER_SNAME(CONVERT(binary(16),SID_BINARY(@sid_in)));

Results:

----
Test

This just means that you can write your code in such a way that you can still get to the right login given the Windows S-1... identifier. But there is a side effect of this trimming that may or may not be important to you. It is impossible to get back to the original SID you passed in because some of that data is gone. So if you create the functions @eckes points out above, you will find slightly different output than you would expect:

SELECT name, sid, ws1 = dbo.fn_SIDToString(sid)
  FROM sys.server_principals
  WHERE name = N'Test';

Results:

name    sid                                   ws1
----    ----------------------------------    ------------------
Test    0x01050000000000051500000027035A18    S-1-5-21-408552231

That may or may not be acceptable. I do not know enough about Windows identifiers or about exactly why you want to create a SQL auth user that is appears on first glance to be mapped to one to know if this will meet your needs. But it gets past both the syntax error and the 28/16 issue.

Function I used, included here in case the link dies:

CREATE FUNCTION [dbo].[fn_SIDToString]
(
  @BinSID AS VARBINARY(100)
)
RETURNS VARCHAR(100)
AS BEGIN

  IF LEN(@BinSID) % 4 <> 0 RETURN(NULL)

  DECLARE @StringSID VARCHAR(100)
  DECLARE @i AS INT
  DECLARE @j AS INT

  SELECT @StringSID = 'S-'
     + CONVERT(VARCHAR, CONVERT(INT, CONVERT(VARBINARY, SUBSTRING(@BinSID, 1, 1))))
  SELECT @StringSID = @StringSID + '-'
     + CONVERT(VARCHAR, CONVERT(INT, CONVERT(VARBINARY, SUBSTRING(@BinSID, 3, 6))))

  SET @j = 9
  SET @i = LEN(@BinSID)

  WHILE @j < @i
  BEGIN
    DECLARE @val BINARY(4)
    SELECT @val = SUBSTRING(@BinSID, @j, 4)
    SELECT @StringSID = @StringSID + '-'
      + CONVERT(VARCHAR, CONVERT(BIGINT, CONVERT(VARBINARY, REVERSE(CONVERT(VARBINARY, @val)))))
    SET @j = @j + 4
  END
  RETURN ( @StringSID )
END


How to join sparse events for individual users on a "filled in" date ranges in Redshift?

Non-optimized solution:

WITH RECURSIVE
cte1 AS ( SELECT MIN(created_at) "date"
          FROM test
          UNION ALL
          SELECT ("date" + INTERVAL '1 DAY')::DATE
          FROM cte1
          WHERE "date" < ( SELECT MAX(created_at)
                           FROM test ) ),
cte2 AS ( SELECT DISTINCT account_id
          FROM test ),
cte3 AS ( SELECT cte1."date", cte2.account_id, MAX(test.created_at) created_at
          FROM cte1
          CROSS JOIN cte2
          LEFT JOIN test ON cte1."date" >= test.created_at
                        AND cte2.account_id = test.account_id
          GROUP BY cte1."date", cte2.account_id )
SELECT cte3."date", cte3.account_id "user", test.plan_name
FROM cte3
LEFT JOIN test ON cte3.account_id = test.account_id
              AND cte3.created_at = test.created_at
ORDER BY 2, 1

fiddle


Using CONVERT TO CHARACTER SET for changing only collation?

There are a few options for managing character set conversions. As you have found, there is the alter table option, and you can use the character set clause or the collate clause or both in your statements.

Other options include changing the character set and collation for the whole database (which I can see you don't want to do just yet).

Or there's a tool in the free and open source Percona Toolkit that is very popular with developers, pt-online-schema-change, that helps you manage such migrations for tables with a primary key.

David Berube, a consultant independent from Percona, wrote a detailed blog post on our community blog about some of the problems that you can encounter when changing character sets and collations. You can read that at https://www.percona.com/community-blog/2018/06/12/character-sets-migrating-utf8mb4-pt_online_schema_change/

The potential pitfalls included database version compatibility, application "expectations", key length management (the length could change), and the fact that you could see false positives i.e. a column that says it's latin1 may not be!

This time on the Percona website, there are a number of blog posts and also a free webinar Troubleshooting Issues with MySQL Character Sets https://www.percona.com/resources/webinars/troubleshooting-issues-mysql-character-sets

Hope some of these will help you work out the best way to approach your scenarios.

--
Disclosure: I work for Percona.


SQLNCLI10.x vs 11.x when connecting SSIS 2008R2 to a SQL Server 2012 database

According to this and this, there are upgrades to the sqlcmd.exe utility that I am assuming are reflective of underlying changes in the libraries. For instance,

  Beginning in the ODBC Driver 11 for SQL Server, there is no limit on the amount of data that retrieved in a single column when –y0 is specified.

I realize that this says the ODBC driver, but according to another link (that I can't post because of reputation limits), the SQLNCLI is some kind of wrapper for the ODBC drivers.

  The SQL Server Native Client...contains the SQL Server ODBC driver for Linux and  ODBC/ OLE DB provider for Windows ,  supporting native connectivity to Microsoft SQL Server.

From my limited searching, there don't appear to be any issues with using 10 to connect to a later sql server instance, but if there are SQL server features from that instance that you're taking advantage of, and you need access to them from your packages, then you're going to want to upgrade.

I can't speculate on performance, and would recommend benchmarking the two methods on your dev box to see what the differences are.

You are certainly losing functionality. I can't find any release notes that would indicate what functionality, per se, but the links above seem to hint at their being sql server specific functionality that is included in the NCLI APIs.


Where clause in select does not work with Inequality of guid

Your logic is wrong ;-) Assume two tables:

CREATE TABLE T1 ( x int not null );
INSERT INTO T1 (x) VALUES (1),(2),(3),(4);

CREATE TABLE T2 ( x int not null );
INSERT INTO T2 (x) VALUES (1),(12),(13),(14);

Your first example will return 1 row:

SELECT *
FROM T1
JOIN T2
    ON T1.x = T2.x

1  1

whereas your second example will return 4*4 - 1 = 15 rows. If you think about it, in a set of 4x4=16 rows where the predicate is true for 1 row, how many rows if the predicate false for?

What you probably are looking for is MINUS or EXCEPT (not sure what it is called in sql-server):

SELECT * from test2.dbo.new_services
EXCEPT
SELECT * from MSCRM.dbo.new_services

or possibly the other way around (not sure which table is which)

Edit:
As pointed out in the comments MySQL (not sure of latest version), does not support the basic set operations INTERSECT and EXCEPT. EXCEPT is SET MINUS and can be implemented using NOT EXISTS or NOT IN:

SELECT t1.* FROM T
WHERE NOT EXISTS (
    SELECT 1 FROM T2 WHERE T1.... = T2... AND ...
);

Another option is using an OUTER JOIN.

Worth noting is that the above corresponds to EXCEPT ALL since any duplicates in T1 is preserved. For a true EXCEPT operator, DISTINCT should be used:

SELECT DISTINCT t1.* FROM T
WHERE NOT EXISTS (
    SELECT 1 FROM T2 WHERE T1.... = T2... AND ...
);

I will not delve into the shadowlands of null, and the consequences it has for logical equality between expressions

INTERSECT on the other hand corresponds closely to multiplication. We can mimic that with the IN or EXISTS predicate in the same manner as above.


Max Degree of Parallelism for SQL Server 2008

This should be set with respect to your system baseline, you have to balance it with your CPU + COST Threshold for parallelism.

for example, i was using the configuration of maxdop=0 when i realized that i have an high number of worker threads. once i reset it to 5 and cost threshold to 50 everything was fine.


Use a table valued function (TVF).
CREATE FUNCTION dbo.TVF_func (@_timestamp DATETIME2)
RETURNS TABLE AS
RETURN
SELECT T.*
  FROM [tests]
  FOR SYSTEM_TIME AS OF @_timestamp T;

The TVF can then be used anywhere a view can be used.
SELECT *
  FROM dbo.TVF_func('2020-10-31') T
  JOIN [etc...]

For views with complex logic where you typically select a subset of the output based on a date variable, you can get a massive speed boost from rewriting the view as a TVF instead of SELECT * FROM view V WHERE V.date = @date.


You could change the query slightly so that the exclusion is done within the join:

SELECT DISTINCT aj2.article_id, aj2.tag_id
  FROM article_tag_join AS aj
  JOIN article_tag_join AS aj2
    ON aj.tag_id = aj2.tag_id
   AND aj.article_id <> aj2.article_id
 WHERE aj.article_id = 75;

This might offer better performance, but it is always worth comparing it against your existing query. If I have two queries that result in the same execution plan, I pick the one that is easiest to maintain.


Export data from InterBase / Firebird database without credentials

The second error cannot attach to password database, that is given when the username and password are provided, is due to the fact that the security database couldn't be opened.

On macOS this file is located under:

/Library/Frameworks/Firebird.framework/Resources/English.lproj/var/security2.fdb

and is owned by the firebird user by default. This causes problems when the administrative tools (isql, gsec, gfix, etc.) are ran as a different user.

One way to solve this issue is to run those command as the firebird user. Another is to change the permissions of the security database:

chmod o+r /Library/Frameworks/Firebird.framework/Resources/English.lproj/var/security2.fdb

This solved the issue and data could be accessed using the default sysdba/masterkey credentials. These are not stored in the GDB file, but in the mentioned security2.fdb file, which explains why the default credentials can be used.


There are several legitimate reasons not to actually create a FOREIGN KEY constraint on a foreign key relationship:

In some cases, you may not be able to - for example, if the other table is in another database.
If your database was migrated from another environment, you may already be maintaining the relationship via triggers. In the best of all possible worlds, you'd switch to an actual foreign key constraint; however, when you have something that's already working, making changes that don't add anything of immediate and obvious benefit is a hard sell with your bosses.
Due to complex business rules, you already have to maintain the relationship with a trigger. You might be able to have the foreign key relationship defined as well, but having it defined in part in two different places is not necessarily a good idea.
Extremely frequent data load tasks that can be done faster/easier without foreign keys being on. It's possible to add code to a script to drop and recreate your foreign keys (or disable and enable, whichever), but if this is happening on a daily basis, and is the source for most of your data, there may not be much of a point in actually having the foreign keys.

Note: In all the above cases, I would have something in place (usually triggers) to maintain the foreign key relationship properly.

There are also less legitimate reasons not to have actual foreign key constraints in place:

The database was migrated from an older system that did not have foreign keys, or where using them was impractical;
The system's developer didn't know what they were;
The system's developer did know what they were, but didn't understand how to implement them properly, and found that they caused errors (because the system didn't bother to create ways to add new values to the other table, etc.).

In the first of these cases, other mechanisms (triggers, application code, etc.) may be in place already to maintain relational integrity, and adding the foreign key could again leave you with two different mechanisms in use at once, which can make things very messy.

In the latter two cases, the biggest impediment to adding the foreign key constraints immediately is that you will start having errors until you've ferreted out and fixed all the places where the original developer may have been ignoring nor bypassing the relational integrity.


Is there any way to revoke drop permission from table in oracle as admin?

There is no drop table privilege.

Users always have full permissions on their own objects, even if they didn't create those objects.

sqlplus kjohnston

SQL> create user testuser;
SQL> grant create session to testuser;
SQL> create table testuser.testtab (x varchar2(1));
SQL> exit;

sqlplus testuser
SQL> drop table testtab;
table dropped

However, there is drop any table privilege for dropping tables in other schemas:

sqlplus kjohnston
SQL> grant create any table to testuser;
SQL> exit;

sqlplus testuser
SQL> create table kjohnston.testtab (x varchar2(1));
table created
SQL> drop table kjohnston.testtab;
ORA-01031: insufficient privileges
SQL> exit;

sqlplus kjohnston
SQL> grant drop any table to testuser;
SQL> exit;

sqlplus testuser;
SQL> drop table kjohnston.testtab;
table dropped

For this reason, it is considered best practice to separate accounts that will be logging in to the database (actual users, website logins, etc...) from users that own the objects (in which case, consider not even granting create session privilege to these users so that they can't login to the database).


Ignore a database in an Availability group when using @AvailabilityGroups = instead of @Databases with ola-hallengren backup solution

You can't use both the @Databases and @AvailabilityGroups parameter. If you try, you'll get this error:

  Msg 50000, Level 16, State 1, Procedure DatabaseBackup, Line 633
  [Batch Start Line 0] You can only specify one of the parameters
  @Databases and @AvailabilityGroups.

For your condition, you'd want to only use @Databases with something like

@Databases = 'DB1, DB2'
@Databases = 'AVAILABILITY_GROUP_DATABASES, -DB1'


"ERROR: timestamp out of range" converting epoch stored as bigint to a timestamp

When I paste the value 1527012834506374 into  https://www.epochconverter.com/ I see the warning:

  Assuming that this timestamp is in microseconds (1/1,000,000 second):

Postgres' to_timestamp() assumes an epoch with seconds, not microseconds, so you need to use:

select to_timestamp(1527012834506374 / 1000000.0)


mysql database under symlinked directory: ERROR 1018 (HY000): Can't read dir of './/' (errno: 13)

It turns out that the ubuntu mysql package uses apparmor.  I found a github blog entry (no longer available a/o Oct 2019) describing my scenario and the steps to fix it, summarized below:

  Here's the gotcha: Ubuntu comes with AppArmor. AppArmor's security model is to bind access control attributes to programs rather than to users. In my /etc/apparmor.d/usr.sbin.mysqld file I've made these changes:

 # I disabled these two lines;
 # Comments indicate you don't need to
 /var/lib/mysql/ r,
 /var/lib/mysql/** rwk,
 # Added these two lines
 /example_ssd_partition/mysql/data r,
 /example_ssd_partition/mysql/data/** rwk,


Doubts about Converting MyISAM to InnoDB (master/slave and more)

ANSWERS TO YOUR QUESTIONS

  1) Since I have a Master/Slave structure, do I need to manage this and change the Slave also? Or any structural changes will directly affect the Slave?

I would recommend you change the Master to all InnoDB and leave the Slave all MyISAM

You will definitely need to make configuration changes since InnoDB has more a lot more moving parts in memory and disk.

  2) Related to n.1, do I have to stop the slave before the conversion?

You can disable binary logging in your session and convert all tables on the Master without replicating any of it to the Slave. Therefore, you do not need to run STOP SLAVE;

  3) I'm using MySQL 5.5.32 (on ubuntu 12.04). Should I update MySQL (to 5.6.*) before the conversion?

You need to upgrade either way. Why? I wrote a post describing how Oracle had a security path installed in MySQL 5.5.40 and 5.6.21 (See my post Find MySQL Patch Level)

If you want to remain with MySQL 5.5, upgrade to MySQL 5.5.40 or greater
If you want to upgrade to MySQL 5.6, upgrade to MySQL 5.6.21 or greater

  4) I use only stored procedure to run queries. Is there something I need to change for the new engine?

No changes for stored procedures are necessary

I have discussed this before. Here a two of my posts where I describe having a Master and Slave with different engines:

Jan 15, 2013 : Using MyISAM for reading and InnoDB for writing data

Accepted Answer from Shlomi Noach
My Answer

Aug 14, 2012 Can I have an InnoDB master and MyISAM slaves with Full-Text for searching?

CONVERSION SCRIPT

Create a script that has these instructions. It will convert everything to InnoDB without replicating:

CONVERT_SCRIPT=/tmp/MassConvertRowFormat.sql
MYSQL_USER=root
MYSQL_PASS=rootpassword
MYSQL_CONN="-u${MYSQL_USER} -p${MYSQL_PASS}"
#
# Query to Create SQL Script
# that Converts All Your MyISAM tables to InnoDB
#
SQLSTMT="SELECT CONCAT('ALTER TABLE ',table_schema,'.',table_name', ENGINE=InnoDB;')"
SQLSTMT="${SQLSTMT} FROM information_schema.tables WHERE engine='MyISAM' AND"
SQLSTMT="${SQLSTMT} table_schema NOT IN ('information_schema','mysql','performance_schema')"
SQLSTMT="${SQLSTMT} ORDER BY data_length"
#
# 1. Disable the Binary Logging of Your Session
# 2. Append storage engine conversion statements
# 3. Execute the Script
#
echo "SET SQL_LOG_BIN = 0;" > ${CONVERT_SCRIPT}
mysql ${MYSQL_CONN} -ANe"${SQLSTMT}" >> ${CONVERT_SCRIPT}
mysql ${MYSQL_CONN} < ${CONVERT_SCRIPT}

NOTE: Please do your homework and make changes to the InnoDB settings and log files before doing this conversion. Without do so, some have experienced slow performance because of the default settings of InnoDB

MY RECOMMENDED OLDER POSTS

Here are my other posts on how and why to tune InnoDB after (or before) converting from MyISAM

Aug 17, 2012 : When to switch from MyISAM to InnoDB?
Sep 26, 2012 : Choosing MyISAM over InnoDB for these project requirements; and long term options
Apr 11, 2014 : MySQL - Best long read query solution within a replication based system
Apr 14, 2011 : What are the main differences between InnoDB and MyISAM?


Restoring a SQL Server 2012 database in high availability

Listen to your adviser.  By restoring a backup, you are essentially replacing the database schema and data.  You will need to turn synchronization off, remove the DB from HA and perform the restore on the primary and replica, leaving the replica version in a restoring state by using WITH NORECOVERY.  Once your backup is in place, put the DB back into HA and start synchronization again.

HA is very similar to mirroring and uses similar technology, just not nearly as finicky.  You will want to treat your HA DBs similarly as well.

Code would be similar to the following:

--on primary

ALTER AVAILABILITY GROUP MyAG REMOVE DATABASE AdventureWorks2012;

--on primary

RESTORE DATABASE AdventureWorks2012
   FROM AdventureWorksBackups
   WITH NORECOVERY,
      MOVE 'AdventureWorks2012_Data' TO
'C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\Data\NewAdvWorks.mdf',
      MOVE 'AdventureWorks2012_Log'
TO 'C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\Data\NewAdvWorks.

RESTORE LOG AdventureWorks2012
   FROM AdventureWorksBackups
   WITH RECOVERY;

--on secondary

RESTORE DATABASE AdventureWorks2012
   FROM AdventureWorksBackups
   WITH NORECOVERY,
      MOVE 'AdventureWorks2012_Data' TO
'C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\Data\NewAdvWorks.mdf',
      MOVE 'AdventureWorks2012_Log'
TO 'C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\Data\NewAdvWorks.ldf';

RESTORE LOG AdventureWorks2012
   FROM AdventureWorksBackups
   WITH NORECOVERY;

--on primary

ALTER AVAILABILITY GROUP MyAG ADD DATABASE AdventureWorks2012;

--on secondary

ALTER DATABASE AdventureWorks2012 SET HADR AVAILABILITY GROUP = MyAG;


PROPOSED QUERY

select SEC_TO_TIME(IF(WorkSec<0,UNIX_TIMESTAMP(),0)+WorkSec) WorkTime
from (select SUM(UNIX_TIMESTAMP(concat(a_date,' ',a_time))*
IF(a_status='IN',-1,1)) WorkSec
from worktable where emp_id = 2) A;

SAMPLE DATA

mysql> use test
Database changed
mysql> drop table if exists worktable;
Query OK, 0 rows affected (0.13 sec)

mysql> create table worktable
    -> (a_id int not null auto_increment,
    -> emp_id int not null,a_date date,
    -> a_time time,a_status varchar(45),
    -> primary key (a_id),key (emp_id));
Query OK, 0 rows affected (0.52 sec)

mysql> insert into worktable
    -> (emp_id,a_date,a_time,a_status) values
    -> (2,'2014-04-01','09:02:00','IN'),
    -> (2,'2014-04-01','13:00:00','OUT'),
    -> (2,'2014-04-01','16:30:00','IN'),
    -> (2,'2014-04-01','21:30:00','OUT');
Query OK, 4 rows affected (0.03 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql> select * from worktable;
+------+--------+------------+----------+----------+
| a_id | emp_id | a_date     | a_time   | a_status |
+------+--------+------------+----------+----------+
|    1 |      2 | 2014-04-01 | 09:02:00 | IN       |
|    2 |      2 | 2014-04-01 | 13:00:00 | OUT      |
|    3 |      2 | 2014-04-01 | 16:30:00 | IN       |
|    4 |      2 | 2014-04-01 | 21:30:00 | OUT      |
+------+--------+------------+----------+----------+
4 rows in set (0.00 sec)

mysql>

RUN PROPOSED QUERY FOR EMPLOYEE 2 (WHO IS OUT)

mysql> select SEC_TO_TIME(IF(WorkSec<0,UNIX_TIMESTAMP(),0)+WorkSec) WorkTime
    -> from (select SUM(UNIX_TIMESTAMP(concat(a_date,' ',a_time))*
    -> IF(a_status='IN',-1,1)) WorkSec
    -> from worktable where emp_id = 2) A;
+-----------------+
| WorkTime        |
+-----------------+
| 08:58:00.000000 |
+-----------------+
1 row in set (0.00 sec)

mysql>

BRING EMPLOYEE 2 IN TO WORK TODAY

mysql> insert into worktable
    -> (emp_id,a_date,a_time,a_status) values
    -> (2,'2014-04-02','09:02:00','IN');
Query OK, 1 row affected (0.06 sec)

mysql> select * from worktable;
+------+--------+------------+----------+----------+
| a_id | emp_id | a_date     | a_time   | a_status |
+------+--------+------------+----------+----------+
|    1 |      2 | 2014-04-01 | 09:02:00 | IN       |
|    2 |      2 | 2014-04-01 | 13:00:00 | OUT      |
|    3 |      2 | 2014-04-01 | 16:30:00 | IN       |
|    4 |      2 | 2014-04-01 | 21:30:00 | OUT      |
|    5 |      2 | 2014-04-02 | 09:02:00 | IN       |
+------+--------+------------+----------+----------+
5 rows in set (0.00 sec)

mysql>

RUN PROPOSED QUERY FOR EMPLOYEE 2 (WHO IS IN TODAY)

mysql> select now();
+---------------------+
| now()               |
+---------------------+
| 2014-04-02 10:20:49 |
+---------------------+
1 row in set (0.00 sec)

mysql> select SEC_TO_TIME(IF(WorkSec<0,UNIX_TIMESTAMP(),0)+WorkSec) WorkTime
    -> from (select SUM(UNIX_TIMESTAMP(concat(a_date,' ',a_time))*
    -> IF(a_status='IN',-1,1)) WorkSec
    -> from worktable where emp_id = 2) A;
+-----------------+
| WorkTime        |
+-----------------+
| 10:16:51.000000 |
+-----------------+
1 row in set (0.00 sec)

mysql>

EPILOGUE

When an employee is out, the WorkTime is evidently a static datetime value.

When an employee is in, the WorkTime is evidently a moving datetime value.

I will leave it to you to tweek the query to set it for a day, a week, for a group of employees, or whatever you boundaries you need.

GIVE IT A TRY !!!


What is the equivalent of MS Access InStr function in SQL Server 2008?

InStr is pretty similar to CHARINDEX. So, your query can be written as:

SELECT  PartID,
        [Description],
        [Service],
        CHARINDEX(' ',[Description]) Expr1,
        LEFT([Description],CHARINDEX(' ',[Description])) Part
FROM TI
WHERE PartID IS NOT NULL
AND [Service] = 'JTP';


PostgreSQL: how can I create an index for querying variable combinations of booleans

I've found a solution by casting the bools to ints and indexing using a bloom index. The query now executes in 27ms using a bitmap index scan compared to the previous 100ms of the sequential scan.
The index:
CREATE INDEX bloom_idx ON person USING bloom (
    CAST(color_is_red as int), CAST(color_is_green as int), CAST(color_is_blue as int), CAST(color_is_yellow as int),
    CAST(seeks_red as int), CAST(seeks_green as int), CAST(seeks_blue as int), CAST(seeks_yellow as int)

);

The query:
explain (analyze, buffers, format text)
SELECT id
FROM person
WHERE CAST(color_is_red as int) = 0
  AND CAST(color_is_blue as int) = 0
  AND CAST(seeks_red as int) = 1
  AND
    (
        CAST(seeks_creative as int) = 1
        OR CAST(seeks_technical as int) = 1
    )
limit 200;

The explain output:
https://explain.depesz.com/s/piUf
"Limit  (cost=20348.00..20352.04 rows=1 width=4) (actual time=27.660..27.824 rows=200 loops=1)"
"  Buffers: shared hit=1998"
"  ->  Bitmap Heap Scan on person  (cost=20348.00..20352.04 rows=1 width=4) (actual time=27.658..27.803 rows=200 loops=1)"
"        Recheck Cond: (((color_is_red)::integer = 0) AND ((color_is_blue)::integer = 0) AND ((seeks_red)::integer = 1))"
"        Rows Removed by Index Recheck: 58"
"        Filter: (((seeks_creative)::integer = 1) OR ((seeks_technical)::integer = 1))"
"        Rows Removed by Filter: 72"
"        Heap Blocks: exact=37"
"        Buffers: shared hit=1998"
"        ->  Bitmap Index Scan on bloom_idx_no_skills  (cost=0.00..20348.00 rows=1 width=0) (actual time=24.281..24.281 rows=156588 loops=1)"
"              Index Cond: (((color_is_red)::integer = 0) AND ((color_is_blue)::integer = 0) AND ((seeks_red)::integer = 1))"
"              Buffers: shared hit=1961"
"Planning Time: 0.458 ms"
"Execution Time: 27.905 ms"

(Querying a table of 1 million randomly generated rows)


How can I UPDATE multiple ROWs in a Single Query with multiple WHERE clauses?

Remarks
It is possible to update rows based on some condition. It is also possible to update multiple tables in one statement in MySQL.
Whether the latter is a good idea is debatable, though. The target tables would be joined together for the update, and when I say "joined", I mean it in a broader sense: you do not have to specify a joining condition, in which case theirs would be a cross join. In a cross join, when at least one of the tables has more than one row, the other table will inevitably have its rows duplicated in the joined set. If both have multiple rows, both will have them multiplied. Somewhat counter-intuitively, MySQL will still update each affected row just once, yet I would refrain from multi-table updates in such scenarios, even if solely because of the counter-intuitiveness.
Method 1
Anyway, moving on to your specific example, there is indeed no joining condition, only a filter on each table. You can specify those filters in the WHERE clause of the UPDATE. Now in order to select which value to update each column with, you can use a CASE expression. This is what the complete UPDATE statement might look like:
UPDATE
  A, B
SET
  A.col1 = 'abc',
  A.col2 = 'xyz',
  B.col1 = CASE B.col3
             WHEN '1' THEN 'a'
             WHEN '2' THEN 'b'
             WHEN '3' THEN 'c'
           END,
  B.col2 = CASE B.col3
             WHEN '1' THEN 'x'
             WHEN '2' THEN 'y'
             WHEN '3' THEN 'z'
           END
WHERE
  A.col3 = '1'
  AND B.col3 IN ('1', '2', '3')
;

You can see that you have to repeat the same set of conditions in a CASE expression both for B.col1 and for B.col2. Is there a way to avoid that?
Method 2
Yes, there is. You can arrange the target values for B.col1 and B.col2 as well as the filtering values for B.col3 as a derived table and join it to B in the UPDATE clause, like this:
UPDATE
  A,
  B
    INNER JOIN
    (
      SELECT 'a' AS col1, 'x' AS col2, '1' AS col3
      UNION ALL
      SELECT 'b', 'y', '2'
      UNION ALL
      SELECT 'c', 'z', '3'
    ) AS fltr ON B.col3 = fltr.col3
SET
  A.col1 = 'abc',
  A.col2 = 'xyz',
  B.col1 = fltr.col1,
  B.col2 = fltr.col2
WHERE
  A.col3 = '1'
;

The join is also acting as a filter for B, so you can omit the one in the WHERE clause.
You can find a demo for each method at  db<>fiddle:

Method 1
Method 2

Better way
Finally, as have been remarked both at the beginning of this post and in the comments, you can have a separate UPDATE statement for each table. The result would be clear in intention both to the reader of your script and to the database engine. A simpler script enables the latter to have more options for optimisation.
Use either of the methods above for the table B update, but do both tables separately:
UPDATE
  A
SET
  A.col1 = 'abc',
  A.col2 = 'xyz'
WHERE
  A.col3 = '1'
;

UPDATE
  B
    INNER JOIN
    (
      SELECT 'a' AS col1, 'x' AS col2, '1' AS col3
      UNION ALL
      SELECT 'b', 'y', '2'
      UNION ALL
      SELECT 'c', 'z', '3'
    ) AS fltr ON B.col3 = fltr.col3
SET
  B.col1 = fltr.col1,
  B.col2 = fltr.col2
;

There is also another reason for splitting the updates in separate statements. Since for a single UPDATE statement the tables need to be joined, it is important that both tables have rows intended for the update. If one table has no matching rows, then, even if the other does, neither will be updated. This is because an empty set cross-joined to a non-empty set still results in an empty set.
So, the single UPDATE statement would have no rows to work with if at least one table had no rows matching the condition(s). That would not happen with separate UPDATEs, because each would work with its own table regardless of the contents of the other, therefore, absence of rows in one table would not affect the update of the other.


To which phase of database design is Database Denormalization formally placed

DB design is rather a cyclic process than linear so we can come back to the logical design after the physical phase again and again.


Replicating contained SQL databases to customer in near real-time

Interesting problem to solve. I don't have a lot of familiarity with contained databases but after some quick research I understand their general purpose and the challenges you're facing.
The few ideas that come to mind are possibly looking into Log Shipping or Database Mirroring. You might be able to schedule more frequent data synchronizations with one of these methodologies than routinely sending a BACPAC file.
Finally, it looks like that Contained Databases do support Availability Groups but I'm not sure if that'll be of much help since AlwaysOn Availability Groups require a Windows Failover Cluster to be setup between the servers of the Availability Group, which I'm assuming is not conducive to your business scenario.

After some more brainstorming, research, and re-reading what you wrote, there may be a way to leverage a cloud provider like Azure, as you mentioned. This DBA.StackExchange answer gives a few options for moving data into an Azure instance. Additionally it reminded me of SSIS which you should be able to leverage to synchronize data from your instance within your DMZ to an external server (whether that's in Azure or on-prem).


Will creating a view on a MySQL replication slave break the replication?

This will work fine.  The only caveat is that if you, in the future, create a view on the master with the same view name, in the same schema, replication will stop due to the conflict.

Another potentially useful thing you can do -- quite safely -- on a slave server is adding indexes to base tables to help your reporting queries, even if the same index wouldn't be needed or justified on the master.  Of course, you should not add unique indexes or foreign keys directly to the slave, since these could cause conflicts and don't really make sense when not on the master ...but ordinary single and multi-column indexes are also safe to add directly to a slave.


Count days in date range but only for a specific year within the date range

Assuming your table has a StartDate and EndDate field, you can use a CASE statement to calculate the days difference between the end date and either 01/01/2019 or the start date of the range.
SELECT ID, StartDate, EndDate,
  (CASE
    WHEN StartDate < '2019-01-01' THEN DATEDIFF(DAY, '2019-01-01', EndDate)
    ELSE DATEDIFF(DAY, StartDate, EndDate)
  END)+1 AS DaysInRange
FROM Dates

db<>fiddle


Postgres: Difference between Tuples inserted and Live tuples

This nomenclature all comes down to knowing about MVCC, or Multi-Version Concurrency Control, which is how PostgreSQL handles transactions and concurrency.

When you update or delete a row, doesn't actually remove the row. When you do an UPDATE or DELETE, the row isn't actually physically deleted. For a DELETE, the database simply marks the row as unavailable for future transactions, and for UPDATE, under the hood it's a combined INSERT then DELETE, where the previous version of the row is marked unavailable. These new versions of rows are generally referred to as the "live" rows, and the older versions are referred to as "dead" rows.

The statistics that you are looking at, where it shows 213,324,422 inserted tuples are the number of new data inserts that has been done on that table. The 124,510,280 live tuples are the number of tuples that are up to date in the table and available to be read or modified in transactions. The 3,087,919 dead tuples are the number of tuples that have been changed and are unavailable to be used in future transactions. The autovacuum daemon, or a manual vacuum will eventually come along and mark the space of those "dead" tuples available for future use, which means that new INSERTS can overwrite the data in them.

Side note, since these are just statistics that are gathered by the statistics collector, they aren't going to be exact, but they're good enough to give you an idea of how active the table is, and how much bloat there is (live vs dead tuples), and how well the autovacuum daemon is keeping up with your workload.

Hopefully this makes it clearer. You can read more about this in detail in the PostgreSQL Manual in the chapter on Concurrency Control


migrating from one SQL cluster to another cluster

Rename New Server

Is ServerA\reports running existing software that will break if the name changes? If not, after decommissioning Server1\inst1 you could rename the entire failover cluster instance and network name to reflect the decommissioned instance.

Alias

You can create an alias in SQL Server Configuration Manager on the server that is running SSRS with the alias name Server1\inst1 pointing to ServerA\reports. SSRS should be able to use this to connect with reports.

Shared Data Source

Alternatively, if you alter your reports to use a single shared data source per database you can have a single data source for each in SSRS that you can update. I do this to alter between a primary and backup set of reporting data as a form of local redundancy.

Edit: Reference for creating an alias: https://msdn.microsoft.com/en-us/library/ms190445.aspx

Note that you'll need to create the alias on the 'client' server rather than the server hosting SQL, so in your case on the server running SSRS where the connection is initiated from.


Reference:

Transactional Articles - Specify How Changes Are Propagated
sp_addarticle (Transact-SQL)
Transactional Replication insert only new Records

Yes it is possible with transactional replication.  I am assuming initially you want all data to be replicated to secondary. For that you will do a reinitialize and snapshot. From there on if your article is set up I mentioned below only new inserts will be replicated. For update you can do the same if you chose to.

When using sp_addarticle set @del_cmd  value to NONE.

If you do it from GUI it will look something like this.


Is it possible to dynamically retrieve the number of columns in a view in Oracle?

To spool the columns of the V$ views in Oracle you can use:

select owner, table_name, column_name, data_type, column_id
from dba_tab_columns c
where table_name = 'V_$PARAMETER'
order by owner, table_name, column_id


Is there any way to get the optimization time in MySQL?


Proper way to export database to other servers

If you want the schema and the data, the best and safest way is a copy_only backup. Don't like the backup history that comes along in the MSDB tables? Ok, delete it, then start a new backup chain by performing a full backup once the copy is restored on the new server.

If you just want schema, there are plenty of options. I prefer RedGate's SQL Compare, but there are plenty of alternative tools, including SSDT which (I think with the most recent update) has schema compare functionality.


SQL Server FullText searching on text that is slightly obfuscated before insert

The full text catalog will only reflect the contents of a table's data.

Since it sounds like you're not using full text search yet, and you just want to start, you basically have 2 ways to do this:

Option 1: add columns with the de-obfuscated data, and use full text search on
those. Say you have a column dbo.Customers.SavedText that is obfuscated - you add a dbo.Customers.SavedTextClear column with the original text. You can populate it and keep it up to date with triggers on insert and update. (Triggers usually have a bad reputation - rightfully so, you wanna minimize the work performed in them - but you asked for a solution that didn't require modifying the original queries inserting/updating into the table, and this is what it looks like. When you tie my hands, my artwork isn't quite as good, ha ha ho ho.)

Option 2: do full text search in something like Elasticsearch. Build an ETL process that exports the fields you want to search, de-obfuscates them, and puts them in a database that's tuned for full text search, like Elastic. You can read about how Stack Overflow approached this problem, although when you read it, I bet modifying your original queries is probably going to sound like a better solution. The only reason I mention it is that if you really need to scale out, this can be a less expensive approach long term.


Why provide database name when starting postgresql base backup

Because you must connect to a specific DB in all cases. In psql if you don't specify a database it tries to connect, at least in *nix (any Unix like OS), to a database with the same name as the user in the operating system. Not sure Windows, but I would think it probably behaves the same.

The authentication mechanism doesn't know, or care, what operation you are about to do. It just needs to verify:

Does the user exist?
Did we get proper password for the user (unless you are setup with trust connections)?
Is the user trying to connect to a database that exists and it has rights to connect?


Drive with ldf files failed and some databases left in recovery pending status

You have two options (if restoring from backup isn't an option), and both of them require the database to be attached - your problems are even more serious if you database is no attached (that's the reason why SQL Server prevents you from detaching. Lucky you - older versions didn't look after you like this.

You can either run CHECKDB and allow data loss, or you can ALTER DATABASE REBUILD LOG - but REBUILD LOG is iffy and might not work if you database is in pending recovery anyway.

You need to run CHECKDB. The syntax is as follows:

ALTER DATABASE [fluffedDB] SET EMERGENCY;
ALTER DATABASE [fluffedDB] SET SINGLE_USER;

DBCC CHECKDB ([fluffedDB], REPAIR_ALLOW_DATA_LOSS) WITH ALL_ERRORMSGS, NO_INFOMSGS;

Warning: You will get data loss, and none of this is guaranteed.
Read this SQL Skills post by Paul Randal about EMERGENCY-mode repair

Assumption:
The logical disk where the log file is has been repaired/restored.

Query: Why are restoring from backups not viable? In any environment where you even remotely care about the data, always have a backup, as a restore from backup is always usually the easiest way to solve problems like this.


Partitioning a table will boost the performance?

Does partitioning boost INSERTs and SELECTs Performance

It depends.  See the PARTITION section of http://mysql.rjweb.org/doc.php/ricksrots

  How much time the ALTER take as it is a huge amount of Data(Any way to minimize the Alter time)?

ALTER (usually) takes a time proportional to the data size.  Hour(s) for 28GB.  OTOH, ALTER PARTITION may be much faster.  DROP PARTITION is virtually instantaneous, hence useful for purging 'old' data.  Be more specific about your ALTER question.

  Do I need to use the field created in the queries so that the optimizer can use the partition?

The rules on indexes may seem screwball.  Let's see the CREATE TABLE and the SELECT(s).

  Are there any limitation's or risks by partioning this table?

No more than 1024 partitions, but 100 is 'too many', in my opinion.  The biggest risk is that you will do all the work to do PARTITIONing, only to get no performance boost.

  Is there any way to ALTER the table without downtime?

It depends -- be specific about the ALTER.


SQL Cluster failover doesn't work after applying SQL Server 2016 Service Pack 2

Actually, it looks like storage issue. I would recommend you check S2D storage. This and this might help you.
Assuming you already run SQL Cluster on top of S2D, what is the number of nodes?

If it is a 2-node S2D cluster, I suggest you consider to move to AlwaysOn Availability Groups where is no need in shared storage or Failover Cluster Instances but on top of StarWind VSAN Free. AlwaysOn Availability Groups provides you with active-passive application level replication. StarWind will do active-active block-level replication featuring SQL instance failover between nodes.


How do I handle a database for a form who's structure will likely change in the future?

My approach would be to make the table with a VARCHAR(MAX) field for the questions, and another field for the answers, and just dump each into those fields. That table can have date and version information too to make things easier. Everything should be structured within the varchar field, so it can be parsed out later.

A second approach that a co-worker of mine took was to have a table of just questions, and then a table for the form that links the questions to a certain form. Then have a table for responses, which would only link a person's response to a question.

So the forms table would have a series of records that link form 127 to questions 5,3,24,58. The response table would link person 626's responses to those questions, with a date. The worst part of it would be having to make cross joins just to see a single person's responses to a single form, but that's what views were made for.


How does the SQL Spec specify literals with a decimal should be typed?

Both are correct and compliant on storage from literals. There are two types of numeric literals in the spec's eyes,

<exact numeric literal>
<approximate numeric literals> floating point, triggered by the use of Scientific Notation (with E or e)

For exact numeric literals (ENL),

  22) The declared type of an  ENL is an implementation-defined exact numeric type whose scale is the number of <digit>s to the right of the <period>. There shall be an exact numeric type capable of representing the value of ENL exactly.

For approximate numeric literals (ANL),

  23) The declared type of an <approximate numeric literal> ANL is an implementation-defined approximate numeric type. The value of ANL shall not be greater than the maximum value nor less than the minimum value that can be represented by the approximate numeric types.

Meaning the type merely has to be able to hold the literal, the rest is left to the implementation.

Taken from the SQL 2011n spec

See also

Does MySQL or MariaDB support double literals (double precision)?


Group activities in activity feed by users and products

You need to group users by activity and product in the first step, and then group products by the groups of users.
I use ORDER BY with GROUP_CONCAT(), so that the aggregated usernames are concatenated in the same order.

SELECT x.usernames
      ,x.activity
      ,GROUP_CONCAT(p.product_name ORDER BY p.product_name) AS product_names
FROM   products p
JOIN  (
   SELECT a.activity
         ,a.productId
         ,GROUP_CONCAT(u.name ORDER BY u.name) AS usernames
   FROM   activities a
   LEFT   JOIN users u ON u.id = a.userId
   GROUP  BY 1, 2
   ) x ON x.productId = p.id
GROUP  BY 1, 2
ORDER  BY 1, 2;

-> sqlfiddle

Returns:

USERNAMES  | ACTIVITY   | PRODUCT_NAMES
-----------+------------+------------------------------
John       | Bought     | The Dark Knight Rises
John,Peter | Bought     | Batman Begins,The Dark Knight


Need advice on Primary and Clustering Keys of a table

First I'm assuming that both ID and Type are INT columns.  Second that this is going to be a somewhat active table with a fair number of inserts (and possibly deletes). And last but not least that you aren't going to be pulling a "range" of IDs.  For example you don't need to look at ID 10000-15000.

If so I would create a third column that is an INT identity column.  Make the Primary/Clustered key on it.  Then create a unique key on ID + Type.  By making the new column the clustered key you will avoid any fragmentation problems caused by inserting into the middle of pages and speed up your inserts.  The unique key ID+Type will be used for your queries and will be small enough (8 bytes) that you shouldn't see any issues there.

Last but not least since you are trying to put a full text index on the table you will need a single column, non-null, unique key preferably an integer.  The new column will fit that bill admirably.  (And of course your ID + Type combo wouldn't work.)


SSRS CROSS Domain Reports Server Authentication

I don't recommend it but

You have two options:

Connect using a domain username & password from network A (everyone
in network B connecting as DOMAINA/userx)
Allowing anonymous access.  (this is inherently dangerous and you would want at a bare minimum to pass something to all of your reports as part of your process for security authentication and probably change the default structure for uploading http://local/reportserver might become http://local/rs)


What are the problems with sharing an Access database through online storage service, like Dropbox?

To put it simply - it would work if only one of you were editing the database at a time. If both of you were to edit the database, it could corrupt the file, discard each other changes or cause sync issues.

By the way, have you considered Google Docs?


When should run Analyze table statement in mysql

I just implemented a service at my company to run ANALYZE TABLE regularly on InnoDB tables.
The reason I did this is that we were affected by this bug: InnoDB persistent statistics not persistent enough
.
When using InnoDB persistent statistics, automatic table statistics are refreshed after about 10% of the rows are modified. There's a counter that keeps track of this, but the counter itself is not persistent; it starts over at 0 if the MySQL Server restarts.
We have some tables that are very large, with hundreds of millions of rows. We also restart MySQL Server regularly, because we stay up to date faithfully on MySQL updates and OS updates. The combination of these two things causes us to do restarts before the 10% of rows are modified, and therefore the InnoDB table stats never update.
So I created a service that visits every database replicaset every day. It checks the table mysql.innodb_table_stats and specifically the last_update column. This will tell me how long it has been since the table stats were updated.
If that value is more than 90 days old for a given table, I definitely need to run ANALYZE TABLE.
Otherwise I do a random sampling and pick 10% of the tables to analyze. This should result in every table getting analyzed on average every ten days.
Naturally by checking mysql.innodb_table_stats, only InnoDB tables are considered, but we made a policy that we only support InnoDB tables in our company.


Get time from Unix milliseconds timestamp

Universal solution (for datetime >= '2001-09-09 04:46:40' - see comments).

SELECT FROM_UNIXTIME(@time * POWER(10, 9 - FLOOR(LOG10(@time))));

@time variable contains unix timestamp (any accuracy). For example, when it is 1556948248123, it is normalized to 1556948248.123 by the FROM_UNIXTIME's argument expression, and the result will be 2015-11-13 19:08:01.123000. The same result will be obtained when the variable is 1556948248.123 or, for example, 1.556948248123.

This variable is a placeholder - it can be fieldname or number literal.

You may create your own UDF if needed:

CREATE FUNCTION my_from_unixtime(arg DECIMAL(65, 30))
RETURNS DATETIME(6)
DETERMINISTIC
NO SQL
RETURN FROM_UNIXTIME(arg * POWER(10, 9 - FLOOR(LOG10(arg))));


How to filter hstore where values are not null in a trigger?

With only built-in tools of Postgres and the additional module hstore, without involving jsonb (as requested):
audit_row.row_data = hstore(
   ARRAY (
      SELECT ARRAY[key, value]
      FROM   each(hstore(NEW.*) - excluded_cols)
      WHERE  value IS NOT NULL
      )
   );

One of the overloaded variants of the hstore() functions takes a two-dimensional array.
Alternatively, you could eliminate key names listed in excluded_cols in the WHERE clause (while processing unnested keys anyway):
audit_row.row_data = hstore(
   ARRAY (
      SELECT ARRAY[key, value]
      FROM   each(hstore(NEW.*))
      WHERE  value IS NOT NULL
      AND    key <> ANY (excluded_cols)
      )
   );

Might be a bit faster, not sure. Probably not much difference.
Also not sure how jsonbandjsonb_strip_nulls()` (suggested in comments) perform in comparison. Can you report back with a performance comparison?


How to create a database user for oracle 11g on windows 8?

In Windows 8 use shortcut to start command line option (Windows Key+R). It will open "Run" terminal. Then type keyword "cmd". It will start your command line then type "sqlplus" it will start sqlplus for interacting with your Oracle database. Use your SYS user with login credential for entering in sqlplus. After login into sqlplus, create your user using commands.


I have lowered the chunksize to 1 MB:

 { "_id" : "chunksize", "value" : 1 }

I inserted now about 10.000 documents.

If I look at the sh.status(), I see this:

Shard shard0000 at 192.168.212.182:27018
 data : 1.33Mb docs : 5761 chunks : 4
 estimated data per chunk : 343kb
 estimated docs per chunk : 1440

Shard shard0001 at 192.168.212.106:27018
 data : 1017kb docs : 4249 chunks : 3
 estimated data per chunk : 339kb
 estimated docs per chunk : 1416

Totals
 data : 2.33Mb docs : 10010 chunks : 7
 Shard shard0000 contains 57.41% data, 57.55% docs in cluster, avg obj size on shard : 243b
 Shard shard0001 contains 42.58% data, 42.44% docs in cluster, avg obj size on shard : 245b

Also I run the scripts AllChunkInfo("Vision_1282.Group"), with this result:

mongos> AllChunkInfo("Vision_1282.Group")
ChunkID,Shard,ChunkSize,ObjectsInChunk
Vision_1282.Group-groepNr_MinKeyvalId_MinKey,shard0001,83504,301
Vision_1282.Group-groepNr_"1"valId_"2000",shard0001,1920,8
Vision_1282.Group-groepNr_"1"valId_"2002",shard0001,974144,4012
Vision_1282.Group-groepNr_"2"valId_"1",shard0000,524384,2138
Vision_1282.Group-groepNr_"3"valId_"4",shard0000,498704,2031
Vision_1282.Group-groepNr_"4"valId_"4",shard0000,292800,1220
Vision_1282.Group-groepNr_"4"valId_"6",shard0000,113520,473
***********Summary Chunk Information***********
Total Chunks: 7
Average Chunk Size (bytes): 355568
Empty Chunks: 0
Average Chunk Size (non-empty): 355568

So you are right, the records were to small and the chunksize was to big.

Tx.


SQL encrypted connection home experiment

Maybe you have set up 'trust server certificate' option in the client SSMS. With this option the client accept the certificate of the server as is, without verofying it with the private key stored in the certificate repository.


Repeating the above comment to provide an "answer" for this question:
The config file /etc/percona-server.conf.d/mysqld.cnf was not being included in the standard "global config" file, commonly known at /etc/my.cnf. @OP added !includedir /etc/percona-server.conf.d to the end of the global config and then the changes were visible upon restart of MySQL.


I think they should stay on the same table !
 If you have lots of row(questions) you can use this column(type) to partition your table by it as i think you might have 3-4 distinct values(select,text,checkbox) !

 This would make your query faster by doing this !

  As for the naming of your type column, yes they look better as number and save you some space on you database as Int comparisons are faster than varchar comparisons, for the simple fact that ints take up much less space than varchars.


T-SQL query to show table definition of sys.time_zone_info

sys.time_zone_info is a system view with the definition:

CREATE VIEW sys.time_zone_info AS
    SELECT  name,
            current_utc_offset,
            is_currently_dst
    FROM OPENROWSET(TABLE TIME_ZONE_INFO, SYSUTCDATETIME())

The OPENROWSET call is a wrapper over an internal structure. You cannot get information about it directly.

The columns returned by the view are shown in the documentation

+--------------------+--------------+--------------------------------------------------------------------------------------------------------------------------------+
|    Column name     |  Data type   |                                                          Description                                                           |
+--------------------+--------------+--------------------------------------------------------------------------------------------------------------------------------+
| name               | sysname      | Name of the time zone in Windows standard format. For example, Cen. Australia Standard Time or Central European Standard Time. |
| current_utc_offset | nvarchar(12) | Current offset to UTC. For example, +01:00 or -07:00.                                                                          |
| is_currently_dst   | bit          | True if currently observing daylight savings time.                                                                             |
+--------------------+--------------+--------------------------------------------------------------------------------------------------------------------------------+

and from nchar and nvarchar (Transact-SQL):

  sysname is a system-supplied user-defined data type that is functionally equivalent to nvarchar(128), except that it is not nullable. sysname is used to reference database object names.

You can also obtain the information you want from the system catalogue views, for example:

SELECT
    AC.column_id,
    AC.[name],
    data_type = T.[name],
    AC.max_length,
    AC.[precision],
    AC.scale,
    AC.collation_name,
    AC.is_nullable
FROM sys.all_objects AS AO
JOIN sys.all_columns AS AC
    ON AC.[object_id] = AO.[object_id]
JOIN sys.types AS T
    ON T.user_type_id = AC.user_type_id
WHERE
    AO.[object_id] = OBJECT_ID(N'sys.time_zone_info');

db<>fiddle demo


Why does MySQL fail to use an index effectively when LIMIT offset is large?

Yes, the large offset is a problem. MySQL does early row lookups, which do not allow throwing data away as easy as one imagines. MySQL retrieves the whole records as it processes the database and each record may be of variable length. Throwing rows away afterwards becomes as cumbersome as throwing each single row away. It's not like "throw away x*y bytes".

There are tricks to force MySQL into late row lookup, which means process the index and do all operations concerning that, than retrieve the row. This offers the possibility to throw rows away much faster.

See

this stackoverflow thread for an easy answer
or this page for in-depth info.


Creating a table if it doesn't exist (old versions of PostgreSQL)

The actual solution very much depends on your exact situation.  If you can use a pl/pgsql function, you can check first for the existence of the table:

IF NOT EXISTS (SELECT 1 FROM pg_class WHERE relname = 'my_table')
THEN
    CREATE TEMPORARY TABLE my_table ...
END IF;

You could also try to match the schema name (which will be something like pg_temp*) by joining the pg_namespace table to pg_class.  Or use the pg_tables view, which can be a bit easier.


Adding unsigned 256 bit integers in PostgreSQL

Using pg-bignum

The pg-bignum library does what you want. I just patched it to accept hex input.

Download the library. Run

make
sudo make install

Now, run

CREATE EXTENSION bignum;

Now you can take a 256 bit number like this

59 51 a1 20 2e 65 62 26 ab f3 af e6 93 16 db 1c
3b 37 24 47 75 32 72 95 79 e9 d7 fa 38 12 c0 0e

And feed it to bn_hex_in(),

SELECT bn_in_hex('5951a1202e656226abf3afe69316db1c3b3724477532729579e9d7fa3812c00e');
                                   bn_in_hex
-------------------------------------------------------------------------------
 40400070184923956455012790534029088594440775740385060328118871212830923866126
(1 row)

You can confirm that with Wolfram Alpha

Size advantages

Numeric is pretty big, from the docs

  Numeric values are physically stored without any extra leading or trailing zeroes. Thus, the declared precision and scale of a column are maximums, not fixed allocations. (In this sense the numeric type is more akin to varchar(n) than to char(n).) The actual storage requirement is two bytes for each group of four decimal digits, plus three to eight bytes overhead.

The above example in numeric is 46 bytes (you can check with ::text::numeric), as a bignum it's 36.


Windows Authentication for SQL Server Linked Servers

The only way you can pass Windows Credentials between servers to my knowledge is via delegation as you've already taken a look at.  If there's truly no way to get delegation working in your environment, an alternative to delegation is to fall back to mapping your Domain accounts to SQL authenticated accounts on the remote server that are setup with permissions that are equivalent to your needs.  The downside here is any query executed remotely will be logged to the SQL Authentication account and not your AD Account, so audits won't be as transparent unless you take pains to map a one-to-one SQL Authenticated account to AD Account.

To map AD accounts to remote SQL Authenticated accounts, you first need to create the SQL Authenticated accounts on the remote server and then configure local logins to map to them on your linked server.  If you're happy to use the GUI, you can find this window under Server Objects → Linked Servers → Linked Server Name → Properties → Security Page:

If you prefer TSQL instead, you can perform this same functionality via the sp_addlinkedsrvlogin stored procedure.

Final Note Here: Because you don't have delegation setup, you'll want to leave the Impersonate checkbox unchecked or if you're using TSQL, the @useself parameter should be passed in as FALSE otherwise it will try to pass through authentication which won't work in your scenario.


Does composite cluster index affect performance of seek/scan non-cluster index which is part of the composite?

First off, don't mix "primary key" and "clustered index". One is "how to identify a row", the other is "on-disk structure"

Then, you don't need an index on Division by itself because it is the left hand column of the clustered index. However, it won't be covering (see links below)

And for each point in turn:

this table doesn't know that "Brcode is unique for all division". If it is, then brCode should be first and unique and the clustered index by itself
a search on TrDate won't benefit from the clustered index
it depends on selectivity, statistics, how many columns are required. It may seek with a key lookup or it may scan the clustered index (= a table scan) or scan/seek the index on TrDate

Finally, I'd suggest some background reading:

SQL Server Index Basics
Using Covering Indexes to Improve Query Performance


mongo 3.6 replication and write concern timed out

"waiting for replication timed out"  is not a Error , this is a warning msg that your document has not been written to majority of your replica server. but this going to be replicate.

what you can do is along with W:majority configure one more parameter wtimeout:5000 , with this you can overcome of this warning messages.

""Do i just capture it and continue on? or do I need to reprocess some records?""
You can set w:1 for specific process for example a bulk write.


How to avoid conditional join in order to write a tsql view

It seems like to me that the query you have could be rewritten as:

SELECT R.RoleName, ds.SetID, --T2 Columns
CS.Company, R.RoleName, cs.SetID --T1 Columns
FROM Roles r
    INNER JOIN DefaultSet ds
        on r.RoleName = ds.RoleName
    LEFT OUTER JOIN CompanySet CS
        ON CS/RoleName = R.RoleName
WHERE CS.Company = 'Acme Inc'

If that is the case, than you would be able to create a view like:

CREATE VIEW ViewName
AS
BEGIN
    SELECT R.RoleName,
    ds.SetID,
    CS.Company --,
    --R.RoleName, --Don't Want To duplicate The Column In The View
    --cs.SetID --Don't Want To duplicate The Column In The View
    FROM Roles r
        INNER JOIN DefaultSet ds
            on r.RoleName = ds.RoleName
        LEFT OUTER JOIN CompanySet CS
            ON CS/RoleName = R.RoleName
END

Then you would simply be able to query the Company column like normal.

SELECT *
FROM ViewName
WHERE Company = 'Acme Inc'

Hopefully that helps and I didn't miss-read the question you asked.


How do you copy a TDE-encrypted SQL Server database using T-SQL programatically?

Using just T-SQL?  Probably but not easily.  A much better solution would be to simply put the certificate for the database on the destination server so that you can restore the database as needed to the destination server.

Given that your database has TDE enabled, I assume that there's PII in there that you have to keep encrypted.  By moving it to another server without encryption you've probably just broken your data encryption policy.


From a terminology perspective, there is a Windows Server Failover Cluster (WSFC), and a SQL Server Failover Cluster Instances (FCI). There's also an Availability Group (AG). AGs and FCIs are built on top of a WSFC. AGs and FCIs can be combined or separate. I try to avoid saying "Cluster" and use these acronyms to avoid ambiguity.

From your description, it sounds like you have an AG. An AG is a database-level of HA/DR. Anything and everything within the database is kept in sync between the replicas on your AG. For a visual, look at things in Management Studio, and use Object Explorer to drill-down to see what is contained within the database, and what is outside the database.

The sysadmin role is a server role, so therefore that permission is not kept in sync between replicas. Additionally, the server login is also outside the database, and not automatically synced. You will need to create logins and grant server-level permissions separately on every instance that is part of your AG.

Logins, server-level permissions, linked servers, SQL Server Agent Jobs, backup history, (among other things) are all stored in the master and msdb system databases, which are not part of the AG, and therefore not kept in sync between instances. You'll need to keep this in mind when you're building a solution using Availability Groups.


NOTE: I'm assuming that you are using SQL Server, from the type names listed, as you've not specified anything else.

You should allow for non-ANSI characters in names by using NVARCHAR instead of VARCHAR though of course to properly support extended character sets the rest of your stack might need work also...
You should allow for significantly longer names then 20 characters. Don't use MAX due to potential indexing and other performance concerns, as you are likely to want to search and/or sort by names.
You might need to extend the VAT number column a bit too, depending on the territories that you need to support. See https://en.wikipedia.org/wiki/VAT_identification_number for a list of formats a number of which are longer then 11.
I would be inclined to widen phone number too.
Spaces, punctuation, and anything else that might require escaping, are a bed idea in column names. I would stick with letters, numbers, and underscore.

What is "client name" used for and how (if it does) does it related to the other name columns? There maybe specific advice about that depending on how it is intended to be used.


You are missing the schema name in your query. It should be [LINKED_SERVER].[DB_NAME].[SCHEMA_NAME].[OBJECT_NAME]. So in your case, [12.34.56.78].TESTDB.[HERE SHOULD BE YOUR MISSING SCHEMA].test_table

It's a public IP address. Hopefully it is a firewall's address that routes the traffic to a SQL Server! If it has a DNS name assigned to it, then you could use it. Or you could create a SYNONYM and forget about typing this long name.

CREATE SYNONYM [schema].[synonym_name] FOR [LINKED_SERVER].[DB_NAME].[SCHEMA_NAME].[OBJECT_NAME]


Convert rows into columns using Pivot in SQL

Using your test data

--Setup Demo Data

drop table if exists #t
Create Table  #T  (
    DateValue Date
    ,Actual_Prod int
    ,Forecast_Prod int
    ,MRB_Parts int
    )

INSERT INTO #T (DateValue,Actual_Prod,Forecast_Prod,MRB_Parts)
VALUES ('01-04-18',1,2,2)
    ,('02-04-18',3,2,null)
    ,('03-04-18',0,3,null)
    ,('04-04-18',1,4,null)
    ,('05-04-18',1,null,null);

;

I used a common table expression to union the following

with UnionedData as
(
Select ''Actual_Prod'' as Col_Label,DateValue,Actual_Prod as DataValue from #T
union all
Select ''ForeCast_Prod'' as Col_Label,DateValue,Forecast_Prod as DataValue from #T
union all
Select ''MRB_Parts'' as Col_Label,DateValue,MRB_Parts as DataValue from #T
)

which results in

| Col_Label     | DateValue  | DataValue |
|---------------|------------|-----------|
| Actual_Prod   | 2018-01-04 | 1         |
| Actual_Prod   | 2018-02-04 | 3         |
| Actual_Prod   | 2018-03-04 | 0         |
| Actual_Prod   | 2018-04-04 | 1         |
| Actual_Prod   | 2018-05-04 | 1         |
| ForeCast_Prod | 2018-01-04 | 2         |
| ForeCast_Prod | 2018-02-04 | 2         |
| ForeCast_Prod | 2018-03-04 | 3         |
| ForeCast_Prod | 2018-04-04 | 4         |
| ForeCast_Prod | 2018-05-04 | NULL      |
| MRB_Parts     | 2018-01-04 | 2         |
| MRB_Parts     | 2018-02-04 | NULL      |
| MRB_Parts     | 2018-03-04 | NULL      |
| MRB_Parts     | 2018-04-04 | NULL      |
| MRB_Parts     | 2018-05-04 | NULL      |

and then utilized that data in a dynamic PIVOT to produce the final result.
I also took the liberty of replacing null values with zeros.

Here is the full solution:

--Setup Demo Data

drop table if exists #t
Create Table  #T  (
    DateValue Date
    ,Actual_Prod int
    ,Forecast_Prod int
    ,MRB_Parts int
    )

INSERT INTO #T (DateValue,Actual_Prod,Forecast_Prod,MRB_Parts)
VALUES ('01-04-18',1,2,2)
    ,('02-04-18',3,2,null)
    ,('03-04-18',0,3,null)
    ,('04-04-18',1,4,null)
    ,('05-04-18',1,null,null);

;

--The actual query
DECLARE @sql AS NVARCHAR(2000);
DECLARE @col AS NVARCHAR(2000);
DECLARE @colCoalesceNull AS NVARCHAR(2000);

--Create dynamic column names for PIVOT
SELECT @col = Coalesce(@col + ', ', '') + QUOTENAME(PvtColumnName)
    ,@colCoalesceNull = Coalesce(@colCoalesceNull + ', ', '') + 'coalesce(' + QUOTENAME(PvtColumnName) + ',0) as ' + QUOTENAME(PvtColumnName)
FROM (
    SELECT DISTINCT CONVERT(VARCHAR(10), [DateValue], 121) AS PvtColumnName
    FROM #T
    ) AS SampleTable
ORDER BY PvtColumnName;

PRINT @col
PRINT @colcoalescenull

SET @sql = N'
with UnionedData as
(
Select ''Actual_Prod'' as Col_Label,DateValue,Actual_Prod as DataValue from #T
union all
Select ''ForeCast_Prod'' as Col_Label,DateValue,Forecast_Prod as DataValue from #T
union all
Select ''MRB_Parts'' as Col_Label,DateValue,MRB_Parts as DataValue from #T
)
,cte_SampleTable as
(
select
Col_Label
,CONVERT(VARCHAR(10), [DateValue],121)
        AS PvtColumnName
,DataValue
from UnionedData

)
SELECT Col_Label, ' + @colCoalesceNull + 'FROM cte_SampleTable
    PIVOT(Min(DataValue)
    FOR PvtColumnName IN (' + @col + ')) AS PivotSampleTable';
print @sql
EXEC sp_executesql @sql;

| Col_Label     | 2018-01-04 | 2018-02-04 | 2018-03-04 | 2018-04-04 | 2018-05-04 |
|---------------|------------|------------|------------|------------|------------|
| Actual_Prod   | 1          | 3          | 0          | 1          | 1          |
| ForeCast_Prod | 2          | 2          | 3          | 4          | 0          |
| MRB_Parts     | 2          | 0          | 0          | 0          | 0          |


How to enable remote access to wamp mysql

When you grant access to the MySQL instance to a user account you have to ensure you are allowing access from the remote host.

GRANT ALL PRIVILEGES ON *.* TO 'user'@'x.x.x.x';

In your example the x.x.x.x should be the IP address of the application server or if the server has DNS resolution it can be the hostname (A record or CName) of the application server.

E.g.

Given the following values:

APP Server ....: 192.168.10.12
APP Host Name .: myappserver.domain.com

DB Server .....: 192.168.10.25
DB Host Name ..: mydbserver.domain.com

...your statements should be:

GRANT ALL PRIVILEGES ON *.* TO 'user'@'192.168.10.12';

or:

GRANT ALL PRIVILEGES ON *.* TO 'user'@'myappserver.domain.com';

You are granting access to a user from a host/ip_address (myappserver) to the MySQL instance (mydbserver).

Then you have to ensure that you can TELNET into the MySQL server from the application server on port 3306 (default; or the port you have configured MySQL to listen on). On App Server:

bash> telnet mydbserver 3306

You should be greeted by the following if the firewall is open:

Connected to mydbserver.
Escape character is '^]'.
[

If not you may have a firewall issue. Ensure that the firewall is open from myappserver to mydbserver and port 3306 (default).


repair_allow_data_loss is the minimum repair level for the errors found by DBCC CHECKDB

As the option you used states...you lost data with the repair

REPAIR_ALLOW_DATA_LOSS

In your case you lost index 0, in-row data.  The repair completed successfully, but that doesn't make your lost data come back.  About the only scenario of corruption that is fixable without data loss using the option is if all of the corruption was on a non-clustered (not in-row) data.

If you have a previous backup, you may be able to restore the damaged pages. It depends upon your backups not having the same corruption for the affected pages, which is highly likely if you weren't regularly running checkdb, didn't use checksum page verification, and the with checksum option for the backups.

Review the table involved and try to figure out if you can reconstruct otherwise what was lost.  Otherwise, good luck and you can try a page restore, see:

How to Restore a Page in SQL Server Standard and Enterprise Edition by Jes Schultz Borland


SQL Server High CPU usage and RESOURCE_SEMAPHORE waits

What are your thoughts and experience on above ?

Why would a statement inside SP that usually completes within 10 ms for months, starts to experience RESOURCE_SEMAPHORE wait and complete in 4000-8000 ms ?

CPU pressure caused by bad plans.  You should track and manage plan stability with Query Store, as well as investigating bad plans and remediating with additional index and statistics, and perhaps changes to the queries.

Can constant RESOURCE_SEMAPHORE waits and Memory Grants Pending cause pressure on CPU just to allocate workspace memory for queries?

No, it's the other way around.  Bad plans are resource intensive, driving both large memory allocations and CPU use.


Recreate is the better option, because:

You end up with a script to create the db
It's dirt simple.
You don't have to take the complexity of your schema in to account.
SQL Server will be more than happy to create the script for you.

The other option is to write something that looks through the schema, identifies tables with foreign key constraints so they can be dropped, drops them, then finds the identities and reseeds them, then puts the FKs back

Or if you are a masochist, you could write (well try to) something to use the dependency order

Both of options 2 and 3 are high maintenance.


How to best store Google ngrams in a database?

There were so many changes I would have to make to my first answer I start this one !!!

USE test
DROP TABLE IF EXISTS ngram_key;
DROP TABLE IF EXISTS ngram_rec;
DROP TABLE IF EXISTS ngram_blk;
CREATE TABLE ngram_key
(
    NGRAM_ID UNSIGNED BIGINT NOT NULL AUTO_INCREMENT,
    NGRAM VARCHAR(64) NOT NULL,
    PRIMARY KEY (NGRAM),
    KEY (NGRAM_ID)
) ENGINE=MyISAM ROW_FORMAT=FIXED PARTITION BY KEY(NGRAM) PARTITIONS 256;
CREATE TABLE ngram_rec
(
    NGRAM_ID UNSIGNED BIGINT NOT NULL,
    YR SMALLINT NOT NULL,
    MC SMALLINT NOT NULL,
    PC SMALLINT NOT NULL,
    VC SMALLINT NOT NULL,
    PRIMARY KEY (NGRAM_ID,YR)
) ENGINE=MyISAM ROW_FORMAT=FIXED;
CREATE TABLE ngram_blk
(
    NGRAM VARCHAR(64) NOT NULL,
    YR SMALLINT NOT NULL,
    MC SMALLINT NOT NULL,
    PC SMALLINT NOT NULL,
    VC SMALLINT NOT NULL
) ENGINE=BLACKHOLE;
DELIMITER $$
CREATE TRIGGER populate_ngram AFTER INSERT ON ngram_blk FOR EACH ROW
BEGIN
    DECLARE NEW_ID BIGINT;

    INSERT IGNORE INTO ngram_key (NGRAM) VALUES (NEW.NGRAM);
    SELECT NGRAM_ID INTO NEW_ID FROM ngram_key WHERE NGRAM=NEW.NGRAM;
    INSERT IGNORE INTO ngram_rec VALUES (NEW_ID,NEW.YR,NEW.MC,NEW.PC,NEW.VC);
END; $$
DELIMITER ;
INSERT INTO ngram_blk VALUES
('rolando',1965,31,29,85),
('pamela',1971,33,21,86),
('dominique',1996,30,18,87),
('diamond',1998,13,28,88),
('rolando edwards',1965,31,29,85),
('pamela edwards',1971,33,21,86),
('dominique edwards',1996,30,18,87),
('diamond edwards',1998,13,28,88),
('rolando angel edwards',1965,31,29,85),
('pamela claricia edwards',1971,33,21,86),
('dominique sharlisee edwards',1996,30,18,87),
('diamond ashley edwards',1998,13,28,88);
UPDATE ngram_rec SET yr=yr+1,mc=mc+30,pc=pc+30,vc=vc+30;
INSERT INTO ngram_blk VALUES
('rolando',1965,31,29,85),
('pamela',1971,33,21,86),
('dominique',1996,30,18,87),
('diamond',1998,13,28,88),
('rolando edwards',1965,31,29,85),
('pamela edwards',1971,33,21,86),
('dominique edwards',1996,30,18,87),
('diamond edwards',1998,13,28,88),
('rolando angel edwards',1965,31,29,85),
('pamela claricia edwards',1971,33,21,86),
('dominique sharlisee edwards',1996,30,18,87),
('diamond ashley edwards',1998,13,28,88);
UPDATE ngram_rec SET yr=yr+1,mc=mc+30,pc=pc+30;
INSERT INTO ngram_blk VALUES
('rolando',1965,31,29,85),
('pamela',1971,33,21,86),
('dominique',1996,30,18,87),
('diamond',1998,13,28,88),
('rolando edwards',1965,31,29,85),
('pamela edwards',1971,33,21,86),
('dominique edwards',1996,30,18,87),
('diamond edwards',1998,13,28,88),
('rolando angel edwards',1965,31,29,85),
('pamela claricia edwards',1971,33,21,86),
('dominique sharlisee edwards',1996,30,18,87),
('diamond ashley edwards',1998,13,28,88);
UPDATE ngram_rec SET yr=yr+1,mc=mc+30;
SELECT * FROM ngram_key;
SELECT * FROM ngram_rec;
SELECT A.ngram NGram,B.yr Year,B.mc Matches,B.pc Pages,B.vc Volumes FROM
ngram_key A,ngram_rec B
WHERE A.ngram='rolando angel edwards'
AND A.ngram_id=B.ngram_id;

Much smaller tables for year info but much bigger keys to preserve the original ngram. I also increased the amount of test data. You can cut and paste this directly into MySQL.

CAVEAT

Simply remove ROW_FORMAT and it becomes dymanic and compress the ngram_key tables a lot smaller.

DiskSpace Metrics

nrgram_rec has 17 bytes per row
8 bytes for ngram_id (max unsigned value 18446744073709551615 [2^64 - 1])
8 bytes for 4 smallints (2 bytes each)
1 byte MyISAM internal delete flag

Index Entry for ngram_rec = 10 bytes (8 (ngram_id) + 2 (yr))

47 million rows X 17 bytes per row = 0799 million bytes = 761.98577 MB
47 million rows X 12 bytes per row = 0564 million bytes = 537.85231 MB
47 million rows X 29 bytes per row = 1363 million bytes = 1.269393 GB

5 billion rows X 17 bytes per row = 085 billion bytes = 079.1624 GB
5 billion rows X 12 bytes per row = 060 billion bytes = 055.8793 GB
5 billion rows X 29 bytes per row = 145 billion bytes = 135.0417 GB

ngram_key has 73 bytes
64 bytes for ngram (ROW_FORMAT=FIXED set varchar to char)
8 bytes for ngram_id
1 byte MyISAM internal delete flag

2 Index Entries for ngram_key = 64 bytes + 8 bytes = 72 bytes

47 million rows X 073 bytes per row = 3431 million bytes = 3.1954 GB
47 million rows X 072 bytes per row = 3384 million bytes = 3.1515 GB
47 million rows X 145 bytes per row = 6815 million bytes = 6.3469 GB

5 billion rows X 073 bytes per row = 365 billion bytes = 339.9327 GB
5 billion rows X 072 bytes per row = 360 billion bytes = 335.2761 GB
5 billion rows X 145 bytes per row = 725 billion bytes = 675.2088 GB


Error when I run sp_BlitzCache specifying an output db, schema, and table

The comments are right, but I figured I'd chime in. That is fixed in the latest release

Full disclosure: I am owned and operated by Brent Ozar Unlimited, and contributed both the bug and the bug fix to this code.

On my birthday.


You should avoid duplicating rows in a database design.  You have a many-to-many relationship between events and users, so you will need the "connection" table between them to do that. There will be a one event to many connection relationship and one user to many connection relationship.
The connection table will have FK columns to both event and user, plus any attribute that is useful about that connection (e.g. visibility indicator, date accepted). Make the two FK columns be a unique constraint to make sure there are no duplicates.
You will need another table, perhaps named USER_EVENT_COMMENT, that will also FK to the user and event tables. You can either use an incrementing as a PK or combine the two FK columns with something else (perhaps a date) to make a composite PK. I think the auto increment approach is cleaner.
As to restricting the visibility of the comments, that depends on how you access the database from your application. If all access to the database is the same identity, then your application will need to enforce visibility. If you access with different identities, you can enforce visibility in the database.
As for size, a few billion rows is no big deal.  The proper indexes and thoughtful query construction (use bind variables) and you should be fine.


SQL Server 2008R2 - Replication - Log Files / Indexes

but wouldn't they therefore create the same level of fragmentation on each server?

No. Fragmentation is physical, replication is logical. The same sequence of insert/delete/update may produce different physical indexes, but transnational replication may not even produce the same sequence of insert/deletes/updates. Even with non-filtered publications, the replication agent can fail, rollback and retry when applying the changes.

  surely if the log has recorded and performed the same actions due to the replication they would be similar

Not at all. The database activity may extend well beyond the area covered by replication (in other words, the database may contain more than just the subscription articles). But, more importantly, the log structure is shaped more by backup schedule and log retention than by activity. Read more here.


Connection error while installing mongodb on fedora 28

As it turns out, Fedora treats mongo and mongod as two separate packages. While on most distributions you can install both services in a single bundle, on Fedora you have to install them separately.
sudo dnf install mongodb mongodb-server
Then you can start the service with mongod.


Queries that exceed the number of second set by long_query_time land in the slow query log.
If you want to parse and examine the mysqld-slow.log file, please use mysqldumpslow.
If you want better ways to examine slow queries, you may want to apply one of the following suggestions:
SUGGESTION #1
Keep in mind that mysqld-slow.log contains slow queries that have finished. It will not record long running queries that are in progress. If you would like to catch queries in the act of running long before they are recorded in the slow query log, please look over my past posts on how to set that up in Query Profiles

Jul 26, 2012 : What is running right now?
Dec 28, 2011 : Is there a tool like Microsoft's "SQL Server Profiler" for MySQL?

SUGGESTION #2
If you are using MySQL 5.1+, did you know that you could record the slow log into a MyISAM table? That way, you can query the MyISAM table rather than parsing a large text file.
I have past posts on how to set that up:

Mar 30, 2012 : MySQL slow log configuration
Nov 18, 2011 : Create a custom log table in MySQL


Why does 'sysfiles1' show up in the sys.objects catalog view in SQL Server 2008?

There are lots of them

SELECT * FROM sys.objects where name LIKE 'sys%'

These really are objects in your databases
Internally, other useful objects like sys.objects don't actually exist in your database.