Relational-JSON overlapping with association

If you want simple and efficient SQL statements, don't model an entity as a JSON column. Create a regular normalized table that expresses the functional dependency between the sensor's attributes explicitly.

JSON in the database is a good thing and has its applications, but modeling foreign key relationships like the one to event is not among them.


Grant access to all objects (with a few exceptions) to a role

The sad truth is that you cannot do anything computerwise about the 'sa' activities.  This is because the 'sa' (or a member of the sysadmin server role) are defined to be able to do pretty much anything.

So, of course, you should only have  sysadmin's that you can trust to do their best to adhere to the standards for the database.  But even the best sysadmin is imperfect.  So you might set up some auditing to report on changes made in the database.

(Or, more low tech, periodically check your views for a restricted table name in a view.)

So it depends on what you are concerned about and how much effort you need to put into protecting the secure resources.

In terms of views that expose secure information, you can DENY permissions on a view.  Even if a sysadmin created the view, the DENY of the view for those without approval will prevent their access.

Perhaps any high security views could be named something like SecureViewXYZZY so as to emphasize the reason for those views and the restriction of who can use them.


MariaDB - MySQL - ALL-DB Import - Table 'user' already exists

As someone already mentioned, this has to do with the mysql.user table being changed to a view in 10.4. The problem and solution is documented on the MariaDB website in MDEV-22127.
The solution is to simply add the following two lines to the top of your all-dbs.sql dump file:
DROP TABLE IF EXISTS `mysql`.`global_priv`;
DROP VIEW IF EXISTS `mysql`.`user`;


Better Way of Releasing Massive Unused storage from Data Files to Server

Paul Randal who led the storage engine team in SQL 2005 recommended moving the data to a different filegroup.  Personally I like this approach because it caused the least amount of free space left at the end of the process.  I believe this was in his old MCM training videos.

Also having our databases in the PRIMARY filegroup led to a potential disaster when the GAM/SGAM for the primary filegroup was corrupted due to all the writes on a bad IO subsystem.  Since the DB boot page is on the primary file group and other reasons, I now always keep the PRIMARY filegroup empty to limit corruption potential to it.  Also if you do piecemeal restore and you restore the Primary filegroup, your database is technically online reducing the errors you are spitting out while the rest of the DB is up.


Best way to configure and maintain a table with many insertions and deletions that's empy on average?

In my experience with SQL Server 2016 Enterprise Edition in a similar process where a permanent table is used as a message queue - so it has a very high rate of inserts and deletes - we found rebuilding indexes every 5 to 10 minutes to be beneficial in forcing deleted record clean up.  This dramatically improved read performance on the table.
In our case, the database isolation level is read committed snapshot, and the dequeue/delete process was deleting records faster than the ghost cleanup process was clearing them out.  With high ghost record counts, selecting one record would sometimes take 1-3 seconds instead 5-10 ms because SQL was having to read past all the ghost records, indicated by a high Scan Count (with set statistics io on).
To check the number of ghost records:
SELECT version_ghost_record_count, *
FROM sys.dm_db_index_physical_stats(DB_ID(), OBJECT_ID('[transport].[BatchTable]'), NULL, NULL, 'SAMPLED')

https://docs.microsoft.com/en-us/sql/relational-databases/ghost-record-cleanup-process-guide?view=sql-server-2017#ghost-record-cleanup-task

The ghost cleanup process runs automatically on an interval (every 5 seconds for SQL Server 2012+, every 10 seconds for SQL Server 2008/2008R2) and checks to see if any pages have been marked with ghost records. If it finds any, then it goes and deletes the records that are marked for deletion, or ghosted, touching at most 10 pages with each execution.
On high-load systems with many deletes, the ghost cleanup process can cause a performance issue from keeping pages in the buffer pool and generating IO

I think the "at most 10 pages" part was our limiting factor as the high rate of deletes exceeded the amount of pages the cleanup process could touch each run.


Yes, you can drop the indexes on the subscriber if no one is using them.  Better yet, remove them from the publication article, or create a post snapshot script to drop whatever indexes you want.  Either way on next initialization, the indexes will not be created, or they will be dropped.


Trigger function taking column names as parameters to modify the row

Problem
You had to pick the spot where all possible complications come together.

SQL (or PL/pgSQL) does not allow to parameterize identifiers. You need dynamic SQL with EXECUTE for that.

But the special plpgsql variable NEW in trigger functions is not visible inside dynamic code executed with EXECUTE.

And it's further complicated by passing column names as arguments to the trigger function in CREATE TRIGGER.

And it's not enough to make the target column dynamic, you want to fetch the source value from another dynamic column of the the row.

Unless you know your way around all the involved matters, I would rather try a simpler approach. Write a separate trigger function for each trigger and assign to the target column without dynamic SQL.
Solution
That said, it can be done with one line of code - and many lines of explanation. For the original example in the question, assuming this table definition:
CREATE TABLE tbl (
  tbl_id serial PRIMARY KEY,
  geom geometry,
  coords double precision[]
);

You need the additional module hstore installed (once per database) for this to work. Alternatively you could also use an undocumented feature of json_populate_record() / jsonb_populate_record() for the same purpose:

How to set value of composite variable field using dynamic SQL

CREATE OR REPLACE FUNCTION trg_demo()
  RETURNS trigger AS
$func$
BEGIN
   EXECUTE format('SELECT ($1 #= hstore(%L, ST_SetSRID(ST_Point($1.%2$I[1], $1.%2$I[2]), 4326)::text)).*'
                , TG_ARGV[0], TG_ARGV[1])  -- target (geom), source (coords)
   USING  NEW
   INTO   NEW;
   RETURN NEW;
END
$func$ LANGUAGE plpgsql;
Trigger:
CREATE TRIGGER demo
BEFORE INSERT OR UPDATE ON tbl
FOR EACH ROW EXECUTE PROCEDURE trg_demo('geom', 'coords');

If you don't understand what's happening here, consider my advice above.
Explanation
I formatted the dynamic computation of the geometry bold to help you get a grip on this. Compare with the simple case below.
db<>fiddle here - with point instead of geometry, as PostGIS is not installed.
Old sqlfiddle.
Here is an even simpler version, just assigning the bare text value (which would require the target column geom to be type text). The simplified part in bold again:
   EXECUTE format('SELECT ($1 #= hstore(%L, $1.%I::text)).*'
                , TG_ARGV[0], TG_ARGV[1])  -- target (geom), source (coords)
   USING  NEW
   INTO   NEW;
The core feature is the hstore operator #= to (per documentation):

replace fields in record with matching values from hstore

Both target and source are columns of the new row, which complicates matters. If the source value was a constant we could simply:
NEW := NEW #= hstore(TG_ARGV[0], 'POINT(123.0 456.0)');

Related:

Assign to NEW by key in a Postgres trigger

But we need dynamic SQL to resolve the column name and fetch the source value.

Use format() to concatenate the query string safely.

TG_ARGV[0] and TG_ARGV[1] access the first two elements to the (0-based!) text array of arguments passed by CREATE TRIGGER.

%I concatenates the argument passed to format() as identifier (safe against SQL injection).

$1 references the value passed to EXECUTE in the USING clause.

We need the cast to ::text after calculating the geometry, because hstore takes text.

We need to decompose the row NEW for the assignment back, because plpgsql assigns rows as target column-by-column.


Resolve parametrized schema in PostgreSQL

The short answer is that your use of variables in that manner in the DECLARE statement isn't going to work. I think the workaround you should shoot for is to use a RECORD type, but your example doesn't really explain the end goal, so it's hard to be 100% certain.


How to use a view to span two tables that are identical

You'll need to use a union instead. If your data is indeed distinct, use a union all for better performance -- the step to eliminate duplicates is eliminated.

create view table_full as
select *
from [A].[dbo].[AUX]
union all
select *
from [B].[dbo].[AUX];


I was using sqlplus and I forgot to commit the changes. Before the commit, changes appeared only in the source database, but after I had committed them, the destination database got the new data.


How to use grouping sets in this query that shows database sizes - adding the last time it was updated

I just removed the DB_Name from Grouping

SELECT [Database Name] = DB_NAME(s.database_id),
       [Type] = CASE WHEN s.Type_Desc = 'ROWS' THEN 'Data File(s)'
                     WHEN s.Type_Desc = 'LOG'  THEN 'Log File(s)'
                     ELSE s.Type_Desc END,

       LastUpdate= case when s.type_desc ='ROWS' then NULL
                       when s.type_desc = 'LOG' then NULL
                       else
                           (select COALESCE(max(last_user_update),MAX(last_system_update))
                                         from sys.dm_db_index_usage_stats dius
                                        where dius.database_id = s.database_id)
                    end,
       [Size in MB] = CAST( ((SUM(s.Size)* 8) / 1024.00) AS DECIMAL(18,2) ),
       [Size in GB] = CAST( ((SUM(s.Size)* 8) / 1024.00/1024.00) AS DECIMAL(18,2) )

FROM   sys.master_files s
GROUP BY      GROUPING SETS
              (
                     (s.database_id, s.Type_Desc),
                     (s.database_id)
              )
ORDER BY      DB_NAME(s.database_id), s.Type_Desc DESC

output:

Database Name   Type         lastUpdate  Size in MB Size in GB
db_xxx          Log File(s)   Null            8.00     0.01
db_xxx          Data File(s)  Null            8.00     0.01
db_xxx          NULL          2019-07-14 17:27:04.473  16.00       0.02


Find next entity with ordered junction table

Your query is correct and does work. I think you can make the intent a little bit more clear by writing it in a slightly diffent way (although this is open for debate). The execution plans (on MariaDB) are exactly the same.

The comments in the middle should be explanatory:

SELECT
    sg.series_id, sg.guide_id, sg.ord
FROM
    series_guides sg
    JOIN
    (-- We get the current `series_id`, `ord`, based on `guide_id`
    SELECT
        series_id, ord
    FROM
        series_guides sg
    WHERE
        guide_id = 13
    ) AS sg0
    -- We want to be in the same series_id, but greater ord
    ON sg.series_id = sg0.series_id AND sg.ord > sg0.ord
ORDER BY
    sg.ord
LIMIT
    1 -- We just want one result
;

This will give you:

series_id | guide_id | ord
--------: | -------: | --:
        1 |        1 |   2

If you change then the guide_id to be 1, you'll get the next value as:

series_id | guide_id | ord
--------: | -------: | --:
        1 |       21 |   3

Check everything at dbfiddle here

Remarks and (simplified) table definitions:

I've defined the tables with different namings, to avoid confusions (and not to clash with the ORDER keyword, just in case):

CREATE TABLE guides
(
    guide_id integer PRIMARY KEY,
    guide_stuff varchar(100)
) ;

CREATE TABLE series
(
    series_id integer PRIMARY KEY,
    series_stuff varchar(100)
);

If guide_id must be UNIQUE (and NOT NULL, or otherwise, it doesn't make any sense) it's already a candidate key. Being the simplest candidate key, it's the one I'd use as primary key:

CREATE TABLE series_guides
(
    series_id integer NOT NULL REFERENCES series(series_id),
    -- If this is unique, make it just the primary key. It's simpler
    guide_id integer PRIMARY KEY REFERENCES guides(guide_id),
    ord integer NOT NULL,
    -- This covering index will prove useful
    UNIQUE (guide_id, series_id, ord)
) ;

Probably, you would want to have also another restriction: UNIQUE (guide_id, ord) as well, to avoid situations where your order might not we well defined.


SELECT *
FROM   table
WHERE  id IN ('x', 'y')
AND    (id='x' OR NOT EXISTS(SELECT id FROM table WHERE id='x'))

should do the trick (caveat: I'm an MSSQL guy and haven't tried that in mySQL, but I suspect that syntax is accepted just fine there too) though the efficiency will depend on how many rows matching the first filtering clause there are (for every row matching id='x' OR id='y' he subquery in the EXISTS part will probably be run unless the query planner is bright enough to see that it depends on nothing in the outer query so can be run once and the result reused - if you are only expecting at most two (which with a column name like id I suspect is the case) this should not be an issue)


Sqlite - Limit Characters for different parts of one String with Check

So i figured out how to do exactly what i wanted to do.
I used substrings and limited the characters for each individual part of the EMail.

CREATE TABLE User(
EMail varchar NOT NULL COLLATE NOCASE check( (substr(EMail, INSTR(EMail,'.')+1) NOT GLOB '*[^A-Za-z]*')
AND (substr(EMail,1, INSTR(EMail,'@')-1) NOT GLOB '*[^A-Za-z0-9]*')
AND (substr(EMail, INSTR(EMail,'@')+1, ((INSTR(EMail,'.')-1) - (INSTR(EMail,'@')+1))+1 ) NOT GLOB '*[^A-Za-z0-9]*')
AND EMAIL LIKE '%_@%_.%_')


How to use SQL Web Service Endpoints for TSQL

The MSDN documentation for CREATE ENDPOINT notes the following:

  Native XML Web Services (SOAP/HTTP endpoints) was removed in SQL Server 2012.

So, unless you are using an unsupported version of SQL Server prior to SQL Server 2012, you cannot create an endpoint for http or xml.

If you are considering create a T-SQL endpoint, every SQL Server already has a T-SQL endpoint built in by default and there is absolutely no need to create another one. By default, the T-SQL endpoint listens on port 1433, although that is configurable.


If you have one of the system administrators or database administrators create a read/write share on the server and grant your domain account access to the share, then you should be able to copy the data up and then use BULK INSERT because it would run in the context of the SQL Server service account which would be able to see the path.  Alternatively, you could make a request that they place the data file on the server for you and give you the local path which you would then use in your BULK INSERT script.

You could also use the SQL Server Import/Export wizard to create a connection to your file as the source on your workstation then create a connection to the server as the destination and import the file that way.  You can get to the Import/Export wizard by right clicking the database in Object Explorer and selecting Tasks > Import Data.


How can I see the plan for ONE statement in a stored procedure?

I don't think there's a way to do that in SSMS.

You should use Sentry One Plan Explorer for this.  It's a free tool that provides an alternative way of viewing SQL Server execution plans.

I've run this code on my local copy of the Stack Overflow 2010 sample database:

CREATE PROCEDURE #sp_TestQuery
AS
SELECT COUNT_BIG(*) FROM dbo.Users u WHERE u.Id > 10;
SELECT MAX(p.CommentCount) FROM dbo.Posts p WHERE p.Id > 1000;
SELECT MIN(v.BountyAmount) FROM dbo.Votes v WHERE v.Id > 100000;
GO

EXEC #sp_TestQuery;

Running the EXEC... line produces the problem you mentioned in SSMS:

One option I have here is to at least resize the different panes, by dragging the border between them.  If I wanted to mostly see the middle query:

But that's still not ideal.  The default view in Plan Explorer shows me a list of statements at the top, and I can click on each statement to view the graphical plan below it.  Here's the middle query in Plan Explorer:


Query to get data from 2 different tables is taking long time

Rewrite your query such a way:

SELECT Column1,
       Column2,
       etc..
FROM Db1.dbo.Tb1 b
WHERE EXISTS (
        SELECT TOP (1) 1
        FROM Db2.dbo.Tb2 s
        WHERE s.Id2 <> ''
            AND b.Id1 IN (s.Column3, s.Column4)
    )


You can more or less estimate that but you cannot be 100% sure.

A piece of advice I can give you is to change your storage engine to wiredTiger. I had 20.0GB of disk used by a mongodb instance using the MMAPv2 (which was the default storage engine before Mongodb 3.2). When I converted that to use wiredTiger my disk space dropped to unbelievable 1.9GB.

That might help you for a while.


Based on the comment thread, the first step is to update the system. That is, install Windows updates, patch the Sql Server and update all the device drivers and firmwares. On virtual machines (which you seem to have), the VM guest drivers and the host itself should be updated, but that's usually not DBA's domain.

Lack of patching is often a red flag about more widespread infrastructure problems. Please make sure that the system is fine in other ways. That is, you have a working restore plan [1], service accounts, passwords and user rights are documented and so on.

To illustrate the patching effect, SentryOne has a handy list about the number of fixes included in service packs and cumulative updates. Just SP2 fixes some 133 issues, of which "only" 53 are public ones. Reading through all the release notes about if the problem is mentioned is going to take a while.

As a side note: Microsoft's support policy is restrictive. One needs quite strong a business case (read: lots of money is at stake) if unpatched software problems are looked into. The advice usually is akin to "install all the patches and see if problem occurs again".

[1]: "Working" means you have tested recently enough that the backups can be restored and contain the stuff they should. It's all too common that backup process looks successful, but doesn't quite do what was intended.


Optimise a LATERAL JOIN query on a big table

Preliminary notes

You are using odd data types. character(24)? char(n) is an outdated type and almost always the wrong choice. You have indexes on person_id and join on it repeatedly. integer would be much more efficient for multiple reasons. (Or bigint, if you plan to burn more than 2 billion rows over the lifetime of the table.) Related:

Would index lookup be noticeably faster with char vs varchar when all values are 36 chars

LIKE is pointless without wildcards. Use = instead. Faster.
x.location_host LIKE '2015.testonline.ca'
x.location_host = '2015.testonline.ca'
Use count(e1.*) or count(*) instead of adding a dummy column with the value 1 for each subquery. (Except for the last (e3), where you don't need any actual data.)
You're inconsistent in sometimes casting the string literal to timestamp and sometimes not (timestamp '2016-04-30 23:59:59.999'). Either it makes sense, then do it all the time, or it doesn't, then don't do it.
It doesn't. When compared to a timestamp column, a string literal is coerced to timestamp anyway. So you don't need an explicit cast.
The Postgres data type timestamp has up to 6 fractional digits. Your BETWEEN expressions leave corner cases. I replaced them with less error-prone expressions.

Indexes

Important: to optimize performance create multicolumn indexes.
For the first subquery hp:

CREATE INDEX event_pg_location_host_timestamp__idx
ON event_pg (location_host, timestamp_);

Or, if you can get index-only scans out of it, append person_id to the index:

CREATE INDEX event_pg_location_host_timestamp__person_id_idx
ON event_pg (location_host, timestamp_, person_id);

For very large time ranges spanning most or all of the table, this index should be preferable - it also supports the hlp subquery, so create it either way:

CREATE INDEX event_pg_location_host_person_id_timestamp__idx
ON event_pg (location_host, person_id, timestamp_);

For tnk:

CREATE INDEX event_pg_location_fragment_timestamp__idx
ON event_pg (location_fragment, person_id, timestamp_);

Optimized with partial indexes

If your predicates on location_host and location_fragment are constants, we can use much cheaper partial indexes instead, especially since your location_* columns seem big:

CREATE INDEX event_pg_hp_person_id_ts_idx ON event_pg (person_id, timestamp_)
WHERE  location_host = '2015.testonline.ca';

CREATE INDEX event_pg_hlp_person_id_ts_idx ON event_pg (person_id, timestamp_)
WHERE  location_host = 'helpcentre.testonline.ca';

CREATE INDEX event_pg_tnk_person_id_ts_idx ON event_pg (person_id, timestamp_)
WHERE  location_fragment = '/file/thank-you';

Consider:

Multicolumn index and performance
Is a composite index also good for queries on the first field?

Again, all of these indexes are substantially smaller and faster with integer or bigint for person_id.

Generally, you need to ANALYZE the table after creating a new index - or wait till autovacuum kicks in to do it for you.

Index that is not used, yet influences query
PostgreSQL partial index unused when created on a table with existing data

To get index-only scans, your table has to be VACUUM'ed enough. Test immediately after VACUUM as proof of concept. Read the linked Postgres Wiki page for details if you are unfamiliar with index-only scans.

Basic Query

Implementing what I discussed. Query for small ranges (few rows per person_id):

SELECT count(*)::int           AS view_homepage
     , count(hlp.hlp_ts)::int AS use_help
     , count(tnk.yes)::int     AS thank_you
FROM  (
   SELECT DISTINCT ON (person_id)
          person_id, timestamp_ AS hp_ts
   FROM   event_pg
   WHERE  timestamp_ >= '2016-04-23'
   AND    timestamp_ <  '2016-05-01'
   AND    location_host = '2015.testonline.ca'
   ORDER  BY person_id, timestamp_
   ) hp
LEFT JOIN LATERAL (
   SELECT timestamp_ AS hlp_ts
   FROM   event_pg y
   WHERE  y.person_id = hp.person_id
   AND    timestamp_ >= hp.hp_ts
   AND    timestamp_ <  '2016-05-01'
   AND    location_host = 'helpcentre.testonline.ca'
   ORDER  BY timestamp_
   LIMIT  1
   ) hlp ON true
LEFT JOIN LATERAL (
   SELECT true AS yes                   -- we only need existence
   FROM   event_pg z
   WHERE  z.person_id = hp.person_id    -- we can use hp here
   AND    location_fragment = '/file/thank-you'
   AND    timestamp_ >= hlp.hlp_ts      -- this introduces dependency on hlp anyways.
   AND    timestamp_ <  '2016-05-01'
   ORDER  BY timestamp_
   LIMIT  1
   ) tnk ON true;

DISTINCT ON is often cheaper for few rows per person_id. Detailed explanation:

Select first row in each GROUP BY group?

If you have many rows per person_id (more likely for bigger time ranges), the recursive CTE discussed in this answer in chapter 1a can be (much) faster:

Optimize GROUP BY query to retrieve latest record per user

See it integrated below.

Optimize & automate best query

It's the old conundrum: one query technique is best for a smaller set, another for a larger set. In your particular case we have a very good indicator from the start - the length of the given time period - which we can use to decide.

We wrap it all in a PL/pgSQL function. My implementation switches from DISTINCT ON to rCTE when the given time period is longer than a set threshold:

CREATE OR REPLACE FUNCTION f_my_counts(_ts_low_inc timestamp, _ts_hi_excl timestamp)
  RETURNS TABLE (view_homepage int, use_help int, thank_you int) AS
$func$
BEGIN

CASE
WHEN _ts_hi_excl <= _ts_low_inc THEN
   RAISE EXCEPTION 'Timestamp _ts_hi_excl (1st param) must be later than _ts_low_inc!';

WHEN _ts_hi_excl - _ts_low_inc < interval '10 days' THEN  -- example value !!!
-- DISTINCT ON for few rows per person_id
   RETURN QUERY
   WITH hp AS (
      SELECT DISTINCT ON (person_id)
             person_id, timestamp_ AS hp_ts
      FROM   event_pg
      WHERE  timestamp_ >= _ts_low_inc
      AND    timestamp_ <  _ts_hi_excl
      AND    location_host = '2015.testonline.ca'
      ORDER  BY person_id, timestamp_
      )
    , hlp AS (
      SELECT hp.person_id, hlp.hlp_ts
      FROM   hp
      CROSS  JOIN LATERAL (
         SELECT timestamp_ AS hlp_ts
         FROM   event_pg
         WHERE  person_id = hp.person_id
         AND    timestamp_ >= hp.hp_ts
         AND    timestamp_ < _ts_hi_excl
         AND    location_host = 'helpcentre.testonline.ca'  -- match partial idx
         ORDER  BY timestamp_
         LIMIT  1
         ) hlp
      )
   SELECT (SELECT count(*)::int FROM hp)   -- AS view_homepage
        , (SELECT count(*)::int FROM hlp)  -- AS use_help
        , (SELECT count(*)::int            -- AS thank_you
           FROM   hlp
           CROSS  JOIN LATERAL (
              SELECT 1                     -- we only care for existence
              FROM   event_pg
              WHERE  person_id = hlp.person_id
              AND    location_fragment = '/file/thank-you'
              AND    timestamp_ >= hlp.hlp_ts
              AND    timestamp_ < _ts_hi_excl
              ORDER  BY timestamp_
              LIMIT  1
              ) tnk
           );

ELSE
-- rCTE for many rows per person_id
   RETURN QUERY
   WITH RECURSIVE hp AS (
      (  -- parentheses required
      SELECT person_id, timestamp_ AS hp_ts
      FROM   event_pg
      WHERE  timestamp_ >= _ts_low_inc
      AND    timestamp_ <  _ts_hi_excl
      AND    location_host = '2015.testonline.ca'  -- match partial idx
      ORDER  BY person_id, timestamp_
      LIMIT  1
      )
      UNION ALL
      SELECT x.*
      FROM   hp, LATERAL (
         SELECT person_id, timestamp_ AS hp_ts
         FROM   event_pg
         WHERE  person_id  > hp.person_id  -- lateral reference
         AND    timestamp_ >= _ts_low_inc  -- repeat conditions
         AND    timestamp_ <  _ts_hi_excl
         AND    location_host = '2015.testonline.ca'  -- match partial idx
         ORDER  BY person_id, timestamp_
         LIMIT  1
         ) x
      )
    , hlp AS (
      SELECT hp.person_id, hlp.hlp_ts
      FROM   hp
      CROSS  JOIN LATERAL (
         SELECT timestamp_ AS hlp_ts
         FROM   event_pg y
         WHERE  y.person_id = hp.person_id
         AND    location_host = 'helpcentre.testonline.ca'  -- match partial idx
         AND    timestamp_ >= hp.hp_ts
         AND    timestamp_ < _ts_hi_excl
         ORDER  BY timestamp_
         LIMIT  1
         ) hlp
      )
   SELECT (SELECT count(*)::int FROM hp)   -- AS view_homepage
        , (SELECT count(*)::int FROM hlp)  -- AS use_help
        , (SELECT count(*)::int            -- AS thank_you
           FROM   hlp
           CROSS  JOIN LATERAL (
              SELECT 1                     -- we only care for existence
              FROM   event_pg
              WHERE  person_id = hlp.person_id
              AND    location_fragment = '/file/thank-you'
              AND    timestamp_ >= hlp.hlp_ts
              AND    timestamp_ < _ts_hi_excl
              ORDER  BY timestamp_
              LIMIT  1
              ) tnk
           );
END CASE;

END
$func$  LANGUAGE plpgsql STABLE STRICT;

Call:

SELECT * FROM f_my_counts('2016-01-23', '2016-05-01');

The rCTE works with a CTE by definition. I also slipped in CTEs for the DISTINCT ON query (like I discussed with @Lennart in the comments), which allows us to use CROSS JOIN instead of LEFT JOIN to reduce the set with each step, since we can count each CTE separately. This has effects working in opposite directions:

On the one had we reduce the number of rows which should make the third join cheaper.
On the other hand we introduce overhead for the CTEs and need considerably more RAM, which may be particularly important for big queries like yours.

You'll have to test which outweighs the other.


Using Oracle Directories names as same of path

Just because you are able to do something, it does not necessarily mean it is a good idea.

[oracle@o73 ~]$ sqlplus / as sysdba

SQL*Plus: Release 18.0.0.0.0 Production on Thu Apr 5 16:20:15 2018
Version 18.1.0.0.0

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

Connected to:
Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production
Version 18.1.0.0.0

SQL> create directory "/home/oracle" as '/home/oracle';

Directory created.

SQL> Disconnected from Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production
Version 18.1.0.0.0
[oracle@o73 ~]$ expdp \'/ as sysdba\' directory=\\\"/home/oracle\\\" schemas=bp

Export: Release 18.0.0.0.0 - Production on Thu Apr 5 16:20:33 2018
Version 18.1.0.0.0

Copyright (c) 1982, 2018, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production
Starting "SYS"."SYS_EXPORT_SCHEMA_04":  "/******** AS SYSDBA" directory=\"/home/oracle\" schemas=bp
Processing object type SCHEMA_EXPORT/USER
Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
Processing object type SCHEMA_EXPORT/ROLE_GRANT
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Master table "SYS"."SYS_EXPORT_SCHEMA_04" successfully loaded/unloaded
******************************************************************************
Dump file set for SYS.SYS_EXPORT_SCHEMA_04 is:
  /home/oracle/expdat.dmp
Job "SYS"."SYS_EXPORT_SCHEMA_04" successfully completed at Thu Apr 5 16:20:53 2018 elapsed 0 00:00:18


I would spend sometime over at the Teradata Developer Exchange, Teradata Questions (currently disabled), and the Teradata Forum. There is a wealth of information to be gleaned from these sites.

There is an Express version of Teradata that can be downloaded from here. This will allow you the opportunity to experiment with Teradata on your own computer. The VMware edition will probably provide you the most flexibility from a DBA perspective. The Windows version is great for developers who want to improve their understanding of SQL with Teradata.

Of course there are also questions about Teradata on Stack Overflow using the teradata tag.


dba_data_files does not include:

tempfiles
controlfiles
spfiles
redo logs
archived logs
backups/copies
ADVM volumes (ACFS)
OCR
reserved space for ASM metadata

Include v$asm_alias and v$asm_file in your queries, or just simply list the files in the diskgroup (asmcmd, ls).


MongoDB hidden node still receiving connections

Yes, it is normal behavior. Those connections are from other mongod nodes, because every node "watches" every other node to see who is reachable. This because election must be started even in the situation where secondary node disappear from replica set.


SQL Server Management Studio DAC connection error

TcpDynamicPorts in regedit was set to 59036

Your instance is named and is configured to use dynamic ports.
The default instance is listening on 1433 for all the connections and on 1434 for DAC, your port for DAC is still unknown but you can find it in error log:

xp_readerrorlog 0,1,N'Dedicated admin connection'

Then you need to check that it's open in your firewall and you can connect specifying directly this port:

myPCname,port


No, it's not possible You will need the MYD file. The MYI file only stores the indexes and does not contain any data.


Fastest way to move Collection from Sharded MongoDB Cluster to a another Sharded MongoDB Cluster

As per MongoDB documentation  Create Chunks in a Sharded Cluster

  If you want to ingest a large volume of data into a cluster that is
  unbalanced, or where the ingestion of data will lead to data
  imbalance, such as with monotonically increasing or decreasing shard
  keys. Pre-splitting the chunks of an empty sharded collection can help
  with the throughput in these cases.

EXAMPLE

To create chunks for documents in the myapp.users collection using the email field as the shard key, use the following operation in the mongo shell:

for ( var x=97; x<97+26; x++ ){
    for ( var y=97; y<97+26; y+=6 ) {
        var prefix = String.fromCharCode(x) + String.fromCharCode(y);
        db.adminCommand( { split: "myapp.users", middle: { email : prefix } } );
    }
}

This assumes a collection size of 100 million documents.


How can I Export Warnings generated by HeidiSQL


How to update a huge MySQL table from a text file

since you are using TSV file you can use the following command to import the data:

LOAD DATA INFILE 'data.tsv' INTO TABLE database_name.my_table;

references:
http://dev.mysql.com/doc/refman/5.1/en/load-data.html

http://forums.devshed.com/mysql-help-4/importing-tab-separated-file-tsv-table-853669.html

http://dev.mysql.com/doc/refman/5.1/en/loading-tables.html


You need to do your aggregation separately in a CTE or derived table. Here's a CTE version that should work for you.

WITH    t1
          AS ( SELECT y.CS, COUNT(y.projs) AS records
                FROM es AS y
                GROUP BY y.CS)
     UPDATE x
        SET x.totalval = t1.records
        FROM Prs AS x
        LEFT JOIN t1
        ON  t1.CS = x.CS


I've created a Self Certificate, installed it to SQL server and
  encrypted the connection using it, and even installed it on my laptop
  (which is off our Domain).

By "Self Certificate" do you mean a self-signed certificate created on the machine or a certificate created by your private Certificate Authority on your domain (probably domain controller)?

I'm assuming you mean a certificate issued by your internal CA, so you need to make sure you install the public key for your CA into the Trusted Root Certification Authorities store on your laptop so that the certificate identity can be validated by the third party (your internal CA). In a domain-joined machine, this is done automatically, but as your laptop is not domain-joined it may not have the internal CA as a trusted CA.

Also, since you're running SSMS as a domain user, ensure the SQL Server certificate is installed in the local computer certificate store and not the current user store, otherwise the runas user won't see the SQL Server certificate.

Lastly, validate the certificate meets the requirements defined by Microsoft here, and make sure that you've configured SQL Server to force encryption in SQL Server Configuration Manager.

Once you've done this, connect to SQL from either the machine or a workstation that is domain-joined and validate that your connection is encrypted:

SELECT encrypt_option FROM sys.dm_exec_connections WHERE session_id = @@SPID

This should return true if encrypted or false if unencrypted. Once you've confirmed encryption works from domain-joined machines, test from your non-domain laptop.


Adding COMPRESSION_DELAY to an existing COLUMNSTORE INDEX

An existing columnstore index can be altered as such:

ALTER INDEX ColumnstoreIndexName ON SchemaName.ObjectName
SET (COMPRESSION_DELAY  = x Minutes);

As found in the documentation on ALTER INDEX

ALTER INDEX { index_name | ALL } ON <object>
{
...
    | SET ( <set_index_option> [ ,...n ] )

<set_index_option>::=
{
      ALLOW_ROW_LOCKS = { ON | OFF }
    | ALLOW_PAGE_LOCKS = { ON | OFF }
    | OPTIMIZE_FOR_SEQUENTIAL_KEY = { ON | OFF}
    | IGNORE_DUP_KEY = { ON | OFF }
    | STATISTICS_NORECOMPUTE = { ON | OFF }
    | COMPRESSION_DELAY= {0 | delay [Minutes]}
}


Fast copy/rename database, not mysqldump

It sounds like you have a lot of indexes and/or you have not set innodb_buffer_pool_size is too small on your testing server.  If you have "indexed every column", repent!

A way to get a full disk copy instantly, regardless of its size, is to use LVM.  You would have the database sitting there, then you would take a "snapshot" and use the snapshot.  Eventually would would toss the snapshot.


Quality of Microsft's SQL Server Training/Certification

If your company is willing to pay, I would suggest you to have a subscription for Pluralsight. Its worth for your company or as an individual as well its worth taking it.

(I am speaking from my experience, since I have the subscription and its worth quality investment).

It has courses from SQLSkills team and other people which teaches you a lot about SQLServer - internals, XEvents, Query plans, troubleshooting methodology, powershell, etc.

As a side note, certification will give you a level of confidence to you and your employer, what matters is hands on experience.


Arguments for MongoDB backup with OpsManager or mongodump?

It might not be a full answer (as others will have valid opinion) but I am ok with not getting credit.

Argument in favor of OpsManager:

Continuous backup, you can recover up to the last committed transaction.
You can set your retention one time and do not have to worry about it.
You can automatically make multiple copy of your backup for redundancy.
Restoring non production environment (DEV/QA/INT) in matter on minutes.
mongodump excludes the content of the local database in its output which is problematic for sharded cluster and replica set.
mongodump only captures the documents in the database in its backup data and does not include index data. mongorestore or mongod must then rebuild the indexes after restoring data.
mongodump can adversely affect performance of the mongod. If your
data is larger than system memory, the mongodump will push the
working set out of memory.
Mongodump is not suitable for large databases. This is from official documentation.

  The mongodump and mongorestore utilities work with BSON data dumps,
  and are useful for creating backups of small deployments. For
  resilient and non-disruptive backups, use a file system or block-level
  disk snapshot function, such as the methods described in the MongoDB
  Backup Methods document.

  Because mongodump and mongorestore operate by interacting with a
  running mongod instance, they can impact the performance of your
  running database. Not only do the tools create traffic for a running
  database instance, they also force the database to read all data
  through memory. When MongoDB reads infrequently used data, it can
  evict more frequently accessed data, causing a deterioration in
  performance for the database’s regular workload.


Does visual studio (2015 or above) needs to be installed in a server in order to transfer/create SSIS packages there?

Visual Studio does not need to be installed on the SQL Server.  SSIS packages built with SQL Server Data Tools can be exported to .ispac files, which can then be deployed to servers using SQL Server Management Studio.

See Deploy Integration Services (SSIS) Projects and Packages for more details.

One thing to note is that I've found that deploying .ispac files to SQL Server 2016 servers requires SSMS 2016 for some packages.  Packages deployed with later versions of SSMS end up with versioning errors (Source - ODBC is not compatible with this version of the DataFlow).


How to force subscriber to have foreign key WITH CHECK?

I know by default SQL Server replication adds WITH NOCHECK and NOT FOR REPLICATION

Not quite.  By default they are CHECK or NOCKECK to match the constraint on the publisher, but set to NOT FOR REPLICATION, so updates from the distribution agent do not fire the constraint.

You can choose

1) Whether or not the constraints are copied in the first place in the article properties, by setting or 0x200 flag (2nd byte, 2nd bit) in the schema_option bitmask in sp_addarticle

  0x200 Replicates foreign key constraints. If the referenced table is
  not part of a publication, all foreign key constraints on a published
  table are not replicated

2) Whether they are created with the NOT FOR REPLICATION option at the subscriber, by setting or clearing the 0x20000 flag (3rd byte, 2nd bit)

  0x20000   Replicates FOREIGN KEY constraints as NOT FOR REPLICATION so
  that the constraints are not enforced during synchronization.

By default this nibble is 3, as the 0x10000 flag that controls check constraints is also set.  So set this nibble to 0x10000 to create FK constraints without the NOT FOR REPLICATION option and to 0x00000 to create both FK constraints and check constraints without the NOT FOR REPLICATION option.

See generally Control Behavior of Triggers and Constraints in Synchronization


Select Which Database Mail Profile is used in SQL Server 2008 R2

Since "operators" are part of SQL Server Agent, I'm guessing it'll use whatever mail profile you've configured that to use.

To review/change it, right click SQL Server Agent in Management Studio, and choose Properties. Go to the "Alert System" page, and look for the "Mail profile" drop-down menu near the top. You may also have to check "Enable mail profile", and set "Mail system" to "Database Mail" if you haven't already.


Postgresql 10 There is no unique or exclusion constraint matching the ON CONFLICT specification

ERROR: there is no unique or exclusion constraint matching the ON CONFLICT specification in

This because dg_fkey is a column referenced in a FOREIGN KEY CONSTRAINT and not an index. In fact, if you want that to be faster, you may consider additionally adding an index. From the docs on ON CONFLICT

You probably want something like this,

CREATE UNIQUE INDEX asdf ON pdpc.collection(dg_fkey);

Or add it the the ddl,

CREATE TABLE pdpc.collection
(
    dg_fkey bigint UNIQUE

Then your upsert will work.

Also never use double-quotes on identifiers, that's a horrible practice in Pg


Data duplication as your are proposing is certainly not illegal.  You can do what you like with your data.

You need to weigh the benefits of having the statistic statically stored and using up storage space, or having to calculate it each time.


Easiest method to retrieve the content of BLOBs from a Sybase databse

For example, I have a table which contains a column whose format is BLOB. The result I would like to get is to extract the content of the BLOB into CSV files.
What is the most elegant method to do this?

BCP OUT will be your best choice.

Make sure your database has BLOB data (Image and/or TEXT datatypes). Also, select into should be set to TRUE for the database.
Check the MAX length of the BLOB data using select max(datalength(column_name)) from table_name. Alternatively, you can use this utility to do the same thing programatically.
Use the above max datalength as -T parameter in BCP OUT.

-T text_or_image_size
allows you to specify, in bytes, the maximum length of text or image data that Adaptive Server sends. The default is 32K. If a text or an image field is larger than the value of -T or the default, bcp does not send the overflow.

Is there a Sybase function to perform this? Or an existing software?

SybaseASE does not have a native function. Also, I am not aware of an existing software (there might be, but I did not have a need to use them for getting BLOB data out of my sybaseASE).


Given the error message, this is most probably happening under WSL (Windows Subsystem for Linux).

There have been recent changes in PostgreSQL related to fsync that turn out to be problematic with WSL. The specific PANIC error mentioned and a candidate solution is discussed in detail here:

WSL (windows subsystem on linux) users will need to turn fsync off as of 11.2

Except it's not just 11.2 because the changes were made in other supported versions like the 10.7 you're probably using if you're up to date in the 10.x branch.

Note that the workaround suggested in the subject, turning fsync to off, is not for production use. It's only okay if you're playing around with Postgres on WSL, and don't really care about the durability of your data. Some say that WSL itself is not for production use anyway.

As you're using PostgreSQL 10, it's possible that this problem will be mitigated in the next bugfix release, version 10.8. Two fixes have been committed in the 10.x branch last February that look like they might address the issue.


Delete multiple rows in one MySQL statement

The multi-table delete syntax goes like this:

DELETE [LOW_PRIORITY] [QUICK] [IGNORE]
    tbl_name[.*] [, tbl_name[.*]] ...
    FROM table_references
    [WHERE where_condition]

or like this:

DELETE [LOW_PRIORITY] [QUICK] [IGNORE]
    FROM tbl_name[.*] [, tbl_name[.*]] ...
    USING table_references
    [WHERE where_condition]

Source: MySQL documentation.

The documentation also points out in the Multi-Table Deletes section:

  Table aliases in a multiple-table DELETE should be declared only in the table_references part of the statement.

That is what is wrong with your second example in particular. Instead of

DELETE FROM adv_equip t1, adv_players t2
USING t1, t2
...

it should be

DELETE FROM t1, t2
USING adv_equip t1, adv_players t2
...

Ypercubeᵀᴹ has raised a few good points in the comments. Since one of the conditions in your WHERE clause is effectively a joining condition, you might want to express the join more explicitly in your statement:

DELETE FROM
  t1, t2
USING
  adv_equip AS t1 INNER JOIN adv_players AS t2
  ON t1.session = t2.session
WHERE
  t1.session = '9746qbcinll2edfbmkfn316lu0'
;

A join, however, comes more naturally as part of a FROM clause, so you might find the first variation of MySQL's multi-table DELETE extension more consistent with an equivalent SELECT. This is how it would look in your case:

DELETE
  t1, t2
FROM
  adv_equip AS t1 INNER JOIN adv_players AS t2
  ON t1.session = t2.session
WHERE
  t1.session = '9746qbcinll2edfbmkfn316lu0'
;

I would probably also prefer specifying .* after the target names in the DELETE clause with that syntax:

DELETE
  t1.*, t2.*
...

That, in my opinion, would more clearly be conveying the intention: delete rows rather than the tables themselves.

And last, but not least, point from the comments is that if only one of the tables has the matching rows, your DELETE statement will fail to delete any rows at all. That is very unlike two separate statements each deleting from one table at a time and making sure that in the end neither has the specified rows. Depending on the intended outcome, therefore, you might prefer two DELETE statements to one.


Why do string functions on SQL based database servers start at position 1 instead of 0?

Considering that there is no zeroeth position in a string outside of computers, shouldn't the question really be: why are strings 0-based in some of the more common programming languages? (I am not sure about the statement of "nearly every programming language" since there are many more languages than most people are aware of)

Strings in C and other languages are simply an array of characters (i.e. char[]) that are null-terminated. This is why you can reference individual characters by using the index notation (i.e. stringVariable[index]). Variables are an address to a location in memory. The index is the offset to the starting address of the array. So when thinking in terms of strings being an array, it makes sense enough to interact with them in a 0-based fashion because it is at least consistent even if sometimes a little awkward.

Why is this different in SQL? I would guess that it has to do with SQL being more about physical storage than memory allocation. While some RDBMS's do support arrays (such as PostgreSQL), that is not standard. SQL is also a high-level declarative language that hides the operational specifics of what the query engine is really doing, and so the concepts of addresses and pointers just isn't there. Hence it doesn't really make sense to think in terms of 0-based indexes when working with SQL.

As another poster notes, the source of zero-based indexing is addressing. The first address in any data block ends in zero (whether it occupies the last digit in physical memory or not). And it's not just computers - the address of the first house on your neighborhood block likely is a number like 300 - not 301.

When programming iterative functions where moduli are used (to make something happen every 5 iterations, etc.) it's handy - and faster - to work with zero-based arrays.

Please also see:

Why are zero-based arrays the norm?
Zero-based numbering


Update a table after insertion to another table in PostgreSQL?

The cleanest solution is to remove the redundant number_of_cars column completely. Your solution (as well as many related ideas floating around) are not safe against concurrent write access.

Instead, create a VIEW (or a MATERIALIZED VIEW to optimize read performance) like:

CREATE VIEW person_cars AS
SELECT *
FROM  (
   SELECT owner AS personid, count(*) AS number_of_cars
   FROM   car
   GROUP  BY 1
   ) c
JOIN person p USING (personid);

Or you could have a custom materialized view where you only update persons that had actual changes. Related example:

Refresh materalized view incrementally in PostgreSQL

If you insist on your original idea (and concurrent write access is not an issue), you could use a trigger solution. Basic example:

How do I make a trigger to update a column in another table?

You need to cover all possible changes: INSERT, UPDATE, DELETE on either table. @bgiles added more considerations.


Is it safe to move GPT formatted mdf file to MBR formatted disk?

In general SQL Server will not care that the database files are on a GPT or MBR formatted drive. You could use could use a floppy drive if you could find one big enough to hold the database (ouch on performance though! )

The only time it will be an issue is, as @James-Jenkins mentioned, if the size of your database files exceed the limits of what am MBR formatted drive can handle. This is 2TB. For a GPT drive it's 9.4ZB (9400 Terabytes!). Windows restricts this to 256TB.

If the MBR drive is running on a slow interface (USB1?) then you may experience IO performance issues but it shouldn't cause SQL Server to not run.

Investigate Windows system and application Logs in event Event Viewer to see if there are any messages related to the drive or the IO subsystem.

Check the disk using a testing tool to see if it's dying. If it's having problems you don't want to put your database on it.


How to find Linear interpolation without using pivot and unpivot in SQL Server 2008

Sample data

CREATE TABLE #Data
(
    id integer NOT NULL PRIMARY KEY,
    x integer NOT NULL,
    val float NOT NULL
);

INSERT #Data
    (id, x, val)
VALUES
    (1, 1, 8.18),
    (2, 3, 8.35),
    (3, 5, 8.51),
    (4, 7, 8.52),
    (5, 10, 8.54),
    (6, 15, 8.57),
    (7, 1, 8.35),
    (8, 3, 8.60),
    (9, 5, 8.70),
    (10, 7, 8.75),
    (11, 10, 8.76),
    (12, 15, 8.82),
    (13, 1, 8.55),
    (14, 3, 8.80),
    (15, 5, 8.91),
    (16, 7, 8.95),
    (17, 10, 8.96),
    (18, 15, 9.04),
    (19, 1, 8.75),
    (20, 3, 9.04),
    (21, 5, 9.15),
    (22, 7, 9.21),
    (23, 10, 9.24),
    (24, 15, 9.27);

Pivot

The first step is to pivot the data we have:

DECLARE @Pivoted table
(
    x integer NOT NULL PRIMARY KEY,
    val1 float NULL,
    val2 float NULL,
    val3 float NULL,
    val4 float NULL
);

-- Pivot the data we have
INSERT @Pivoted
    (x, val1, val2, val3, val4)
SELECT
    N.x,
    val1 = MAX(CASE WHEN N.rn = 1 THEN N.val ELSE 0 END),
    val2 = MAX(CASE WHEN N.rn = 2 THEN N.val ELSE 0 END),
    val3 = MAX(CASE WHEN N.rn = 3 THEN N.val ELSE 0 END),
    val4 = MAX(CASE WHEN N.rn = 4 THEN N.val ELSE 0 END)
FROM
(
    SELECT
        D.x,
        D.val,
        rn = ROW_NUMBER() OVER (
            PARTITION BY D.x
            ORDER BY D.id)
    FROM #Data AS D
) AS N
GROUP BY N.x;

Next we need to know the range of values in the table so we can fill in the blanks:

DECLARE
    @Min integer,
    @Max integer;

-- Range of values
SELECT
    @Min = PMin.x,
    @Max = PMax.x
FROM (SELECT TOP (1) P.x FROM @Pivoted AS P ORDER BY P.x ASC) AS PMin
CROSS JOIN (SELECT TOP (1) P.x FROM @Pivoted AS P ORDER BY P.x DESC) AS PMax;

Now we can find the missing rows, and the real rows on either side (for interpolation):

DECLARE @Missing table
(
    x integer NOT NULL PRIMARY KEY,
    prev_row integer NULL,
    next_row integer NULL
);

-- Find missing rows
INSERT @Missing
    (x, prev_row, next_row)
SELECT
    Missing.number,
    PrevRow.x,
    NextRow.x
FROM
(
    -- All rows that ought to be present
    SELECT SV.number
    FROM master.dbo.spt_values AS SV
    WHERE
        SV.[type] = N'P'
        AND SV.number BETWEEN @Min AND @Max
    EXCEPT
    -- Those that are present
    SELECT P.x
    FROM @Pivoted AS P
) AS Missing
CROSS APPLY
(
    -- Find the previous real row
    SELECT TOP (1) P.x
    FROM @Pivoted AS P
    WHERE P.x < Missing.number
    ORDER BY P.x DESC
) AS PrevRow
CROSS APPLY
(
    -- Find the next real row
    SELECT TOP (1) P.x
    FROM @Pivoted AS P
    WHERE P.x > Missing.number
    ORDER BY P.x ASC
) AS NextRow;

Finally, we compute interpolated values for the missing rows, and return the results:

-- Results
SELECT
    Result.x,
    val1 = ROUND(Result.val1, 3),
    val2 = ROUND(Result.val2, 3),
    val3 = ROUND(Result.val3, 3),
    val4 = ROUND(Result.val4, 3)
FROM
(
    -- Rows that exist
    SELECT
        P.x,
        P.val1,
        P.val2,
        P.val3,
        P.val4
    FROM @Pivoted AS P

    -- Plus
    UNION ALL

    -- Interpolated rows
    SELECT
        M.x,
        val1 = PrevRow.val1 + (Scale.factor * (NextRow.val1 - PrevRow.val1)),
        val2 = PrevRow.val2 + (Scale.factor * (NextRow.val2 - PrevRow.val2)),
        val3 = PrevRow.val3 + (Scale.factor * (NextRow.val3 - PrevRow.val3)),
        val4 = PrevRow.val4 + (Scale.factor * (NextRow.val4 - PrevRow.val4))
    FROM @Missing AS M
    JOIN @Pivoted AS PrevRow
        ON PrevRow.x = M.prev_row
    JOIN @Pivoted AS NextRow
        ON NextRow.x = M.next_row
    CROSS APPLY
    (
        VALUES(CONVERT(float, M.x - M.prev_row) / CONVERT(float, M.next_row - M.prev_row))
    ) AS Scale (factor)
) AS Result
ORDER BY
    Result.x ASC;

Results

╔════╦═══════╦═══════╦═══════╦═══════╗
║ x  ║ val1  ║ val2  ║ val3  ║ val4  ║
╠════╬═══════╬═══════╬═══════╬═══════╣
║  1 ║ 8.18  ║ 8.35  ║ 8.55  ║ 8.75  ║
║  2 ║ 8.265 ║ 8.475 ║ 8.675 ║ 8.895 ║
║  3 ║ 8.35  ║ 8.6   ║ 8.8   ║ 9.04  ║
║  4 ║ 8.43  ║ 8.65  ║ 8.855 ║ 9.095 ║
║  5 ║ 8.51  ║ 8.7   ║ 8.91  ║ 9.15  ║
║  6 ║ 8.515 ║ 8.725 ║ 8.93  ║ 9.18  ║
║  7 ║ 8.52  ║ 8.75  ║ 8.95  ║ 9.21  ║
║  8 ║ 8.527 ║ 8.753 ║ 8.953 ║ 9.22  ║
║  9 ║ 8.533 ║ 8.757 ║ 8.957 ║ 9.23  ║
║ 10 ║ 8.54  ║ 8.76  ║ 8.96  ║ 9.24  ║
║ 11 ║ 8.546 ║ 8.772 ║ 8.976 ║ 9.246 ║
║ 12 ║ 8.552 ║ 8.784 ║ 8.992 ║ 9.252 ║
║ 13 ║ 8.558 ║ 8.796 ║ 9.008 ║ 9.258 ║
║ 14 ║ 8.564 ║ 8.808 ║ 9.024 ║ 9.264 ║
║ 15 ║ 8.57  ║ 8.82  ║ 9.04  ║ 9.27  ║
╚════╩═══════╩═══════╩═══════╩═══════╝

These numbers are rounded to three decimal places using SQL Server's default rounding. If you need to mimic e.g. Excel rounding, you would need to use a function that implements that specific algorithm.

Tested on SQL Server 2008 R2 with compatibility level 80, as indicated in your previous questions.

db<>fiddle (SQL Server 2012, earliest version available)


So this was an oddity and I have yet to get down to the nuts and bolts. In a nutshell I enabled MARS on sql server connections a while back to fix an issue I was having with IoC container connections.

This fixed an exception that was similar to "You must close the current command yaddayaddayadda". However, Multiple Active Result Sets seems to lead to a concurrency issue in my case. I actually fixed the original issue that should have been fixed instead of enabling MARS. The fix was to implicitly create a new Data Context per call.


How expensive is a SELECT that returns an empty result set?

That is about 7 requests per second if the rate is flat over the 24 hour period, which shouldn't have a noticeable impact on a decently specced SQL Server machine. If they are far more bunched then they may be more of a problem.

I would be inclined to investigate where the requests are coming from. If they don't do anything useful then they may indicate a bug in the application.

SELECT <something> FROM <table> WHERE <column>=<value_that_doesn't_exist> is not going to tax the query planner or engine really, the work top bring up and tear down a connection will be more arduous to the SQL infrastructure than the work to lookup a cached query plan and read a couple of pages to see the row does not exist.

Assuming that the Id column is the table's clustering key there will be at most a couple of page reads and they will be from RAM as they are not ever going to leave the buffer cache due to how frequently they are accessed. If that column isn't the clustering key then add another page or two to those read (again, almost certainly from RAM not disk). If the column isn't indexed at all (I'd be surprised if that is the case given it is called "Id") then things are potentially more problematical as there will be a table scan involved.


Can't restore master/slave replication from Percona backup (slave server)

I found the solution here
https://www.percona.com/doc/percona-xtrabackup/2.3/howtos/setting_up_replication.html
I was missing the xtrabackup --move-back command


Naming question for functions: implicit or explicit naming of grouping in function name

Where are these functions being called? Are they being called often? How complex is the logic, does it even warrant creating a new database object?

They are very verbose, and the new ones match that style so I guess they're ok. I would prefer to start with Get then I know right away its more of a 'getter' than a subroutine.

I would also pick more terse names, though I have a weakness for clutter free code.


Database file paths for Named Instances in AAG's

I have configured a 2 node AAG setup with named instances say N1 and N2.

I wouldn't use named instances, default instances will cause less issues in the long run. We'll get to one of those issues...

  During the AAG creation and database seeding from N1 , I got an error saying that N2 did not have any folders/path similar to N1 and I could not continue.

Correct. Since you're using named instances, the installation directory names will be different which is why you're receiving the error. In order for the databases to seed they must have identical folder structures.

  So I created another Directory path for the database files with the same name as that on Node 1. Will this work?

If the folders are identical, then yes. You'll also need to put the proper security on the new folders.


"All joins can be rewritten to sub queries", is this statement true?

As far as I know, a JOIN [INNER JOIN], from the theoretical point of view, is a projection of a CROSS JOIN (every combination of two tables). If you can get a cross join using a subquery and apply any function on it, then you have a perfect substitute. I think you can always transform:

SELECT T1.A, T2.B
FROM T1
JOIN T2
ON condition(T1.C, T2.D)

into:

SELECT T1.A, T2.B
FROM T1
WHERE condition(T1.C, ALL(SELECT T2.D FROM T2))

A similar conclusion can be reached with LEFT [OUTER] JOINS and RIGHT [OUTER] JOINS (the other types supported on MySQL), by including the NULL value:

SELECT T1.A, T2.B
FROM T1
WHERE condition(T1.C, ALL(SELECT T2.D FROM T2) UNION SELECT NULL)


CATCH block is triggered when it should not

It looks like the pre SQL Server 2012 behaviour is a bug.

I infer this from this Connect article:

https://connect.microsoft.com/SQLServer/feedback/details/712003/sql-server-2012-error-in-exception-handling-mechanism

I could be wrong of course...


MYSQL Stored procedure to fetch entire row from a table

You basically don't need a procedure for that.
Procedures get interesting with CURSOR and LOOP when you need "normal" programming besides SQL to makes some thong much more complex
DROP procedure IF EXISTS `proc_get Student`;

DELIMITER $$
CREATE PROCEDURE `proc_get Student` ( IN _student_no BIGINT)
BEGIN
    SELECT * FROM Fetch_Marks WHERE Student_No  = _student_no;
END$$

DELIMITER ;

But I am guessing this is some kind of homework, the variable _student_no is as it is because you have so a optical marker that it is a locally defined variable
Here's another example:
In this, we are adding a second IN variable and in which both are checked if they are NULL or NOT and then selects the wanted data
DROP procedure IF EXISTS `proc_get Student`;

DELIMITER $$
CREATE DEFINER=`root`@`%` PROCEDURE `proc_get Student`( IN _student_no BIGINT,IN _roll_no BIGINT)
BEGIN
    IF ( _student_no IS NOT NULL AND _roll_no IS NULL) THEN
        SELECT * FROM Fetch_Marks WHERE Student_No  = _student_no;
    END IF;
END$$

DELIMITER ;


ORA-01034: ORACLE not available in oracle 11g

Run sqlplus / as sysdba then issue command startup; to start the database.

Also on Linux with this error, you can check first if Oracle background processes are up with ps aux | grep pmon. Pmon is process monitor, or check some other background process. If they are not running then the database is not running.


Database is not accessible as transaction log file growing to full

First things first, if you need to kill the active user(s) immediately and make sure no more connections are made, you'll want to put the DB in single user mode.

ALTER DATABASE MyDatabase SET SINGLE_USER WITH ROLLBACK IMMEDIATE

Second you'll need to address the SUSPECT mode and hope it's an easy to fix file access issue.  Try these suggestions to fix your SUSPECT mode

Once that is fixed, do what you need to do with the database - CheckDB, backup, etc.  By keeping it in single user mode other users, including applications and any other user but you, will not be able to access the database.

To put it back in multi user mode use

ALTER DATABASE MyDatabase SET MULTI_USER

EDIT - I should say this does not resolve why you have an ever growing transaction log.  If it is of concern, you should try to resolve that prior to killing an active transaction with sp_whoisactive, monitoring tools, etc.  But don't let the log fill the drive or it will kill the transaction anyway and roll it back.


Estimate amount of downtime from RDS backup

Usually, RDS backups don't cause downtime. Similar to EBS backup, the I/O needs to freeze the DB for a split fo the second to get a consistent LVM snapshot. After that, it is irrelevant how long the backup takes. Alternatively, you may consider taking the snapshot on the Read Replica to avoid the impact on Master.

  During the automatic backup window, storage I/O might be suspended
  briefly while the backup process initializes (typically under a few
  seconds). You might experience elevated latencies for a few minutes
  during backups for Multi-AZ deployments. For MariaDB, MySQL, Oracle,
  and PostgreSQL, I/O activity is not suspended on your primary during
  backup for Multi-AZ deployments, because the backup is taken from the
  standby. For SQL Server, I/O activity is suspended briefly during
  backup for Multi-AZ deployments."
  https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/USER_WorkingWithAutomatedBackups.html

Since you've mentioned that you need to perform RDS Maintenance after the backup.
you may want to check out how to avoid downtime altogether during the RDS maintenance.
https://workmarket.tech/zero-downtime-maintenances-on-mysql-rds-ba13b51103c2

Disclosure: I work as a Lead DBA for WorkMarket.


Copy Postgres databases with indexes between instances

If you want to copy a complete PostgreSQL database within its cluster, the fastest method is to use it as TEMPLATE in a CREATE DATABASE statement. I quote the manual:

  By default, the new database will be created by cloning the standard
  system database template1. A different template can be specified by
  writing TEMPLATE name. In particular, by writing TEMPLATE template0,
  you can create a virgin database containing only the standard objects
  predefined by your version of PostgreSQL. This is useful if you wish
  to avoid copying any installation-local objects that might have been
  added to template1.

CREATE DATABASE db_copy TEMPLATE db_org;

This effectively copies underlying files around like you tried manually. Except that is sets everything up to work correctly.

You may want to clean up your original before you do (depends):

VACUUM FULL ANALYZE;


Windows Applicationlog ID 102: SQLServerAgent service successfully stopped.
Windows Systemlog ID 7036: SQLServerAgent service successfully stopped.
SQL Server Logs: Event ID: [098] SQLServerAgent service successfully stopped.

It doesn't show who did it. I think you need to configure auditing or tracing for this. For tracing there is a Security Event called Audit Server Starts and Stops.


Pros and Cons of building a relationship model where all table ids are just "_id"?

There's no weighty reason to do one or the other.  Both styles are common and work fine.
AFAIK:
Pro: You always know how to join, as foreign keys use the same column name in each table, and join queries are easier to validate.
Con: You can't declare a supertype in your app that has the primary key as a property.  IE it's a relatively common pattern in .NET to have
public abstract class Entity
{
    public int Id { get; set; }
}

as a common base type for your entitites.
Some may argue that you shouldn't be adding synthetic keys to tables unless there's an actual need, but that's really a different issue.


MYSQL after insert trigger is not running

Mostly just a few little mistakes, the biggest of which is the ambiguous declaration of category_id.

SELECT category_id from models
WHERE id = model_id
INTO category_id;

This statement is selecting value of the DECLARE'd category_id ( which is the DEFAULT, null ), into the DECLARE'd category_id.  This renders the following branches unreachable, as they will always evaluate as false.  To eliminate this ambiguity, you can alias your table.

SELECT  m.category_id
FROM    models m
WHERE   m.id = model_id
INTO    category_id;

The lesson here is to always alias your tables.  As per natural law, for every opinion there is an equal and opposite opinion, but in this case, I'm right.  Alias your tables, all the time, every time.  It drastically improves readability and serves to avoid little bugs like this one.

The next issue with the trigger is a discrepancy between what you have said ( insert a value into the asset_type column for each row ) and what you've coded ( insert a row into the assets table for each row ).

IF (category_prefix IS NOT NULL) THEN
  INSERT INTO assets
  (asset_type)
  VALUES (category_prefix);
END IF;

This statement says to insert the [ (null), (null), category_prefix ] tuple into the asset table.  In addition to likely not being the actual intent, as your trigger is an after-insert trigger, if the trigger were to actually perform this operation it would infinitely loop.  What you need to do is to set the value of the NEW column:

IF ( category_prefix IS NOT NULL )
THEN
  SET NEW.asset_type = category_prefix;
END IF;

However, MySQL will also complain about this because you can't update a NEW column in an after-insert trigger, as the write has already completed: It's already done.  To make this work, it's necessary to change the trigger from an after-insert to a before-insert.

CREATE TRIGGER asset_prefix_before_insert
  BEFORE INSERT ON assets

With those changes, you may end up with something that looks a little like the following:

DELIMITER //
CREATE TRIGGER asset_prefix_before_insert
  BEFORE INSERT ON assets
  FOR EACH ROW
BEGIN

  DECLARE model_id INT DEFAULT NULL;
  DECLARE category_id INT DEFAULT NULL;
  DECLARE category_prefix VARCHAR( 6 );

  SELECT  NEW.model_id INTO model_id;

  IF ( model_id IS NOT NULL )
  THEN
    SELECT  m.category_id
    FROM    models m
    WHERE   m.id = model_id
    INTO    category_id;
  END IF;

  IF ( category_id IS NOT NULL )
  THEN
    SELECT  c.cat_prefix
    FROM    categories c
    WHERE   c.id = category_id
    INTO    category_prefix;
  END IF;

  IF ( category_prefix IS NOT NULL )
  THEN
    SET NEW.asset_type = category_prefix;
  END IF;

END; //

INSERT  INTO categories ( id, cat_prefix )
VALUES  ( 1, 'x' ),
        ( 2, 'y' ),
        ( 3, 'z' );

INSERT  INTO models ( id, category_id )
VALUES  ( 1, 1 ),
        ( 2, 2 ),
        ( 3, 3 );

INSERT  INTO assets ( id, model_id )
VALUES  ( 1, 1 ),
        ( 2, 2 ),
        ( 3, 3 );

SELECT  *
FROM    assets;


What is preventing ORA-00376 errors appearing in the alert log?

Oracle provides the following information for this error message:

ORA-00376 file string cannot be read at this time
Cause: An attempt was made to read from a file that is not readable. Most likely the file is offline.
Action: Check the state of the file. Bring it online.

Their logic for not including this error message into the alert log is related to the fact that the alert log is to show messages received by the background processes that make up the instance. User session error messages could indicate larger problems with the instance, but all user session error messages are not indicators of instance problems. (ORA-00001, for example)


Change referenced index for a Foreign Key

Well, after continue searching I found this article

  Unlike a normal query, it won't pick up a new index due to statistics being updated, a new index being created, or even a server being rebooted. The only way I'm aware of to have a FK bind to a different index is to drop and recreate the FK, letting it automatically select the index with no options to control it manually.

Whereupon, unless someone can say otherwise, I will have to look for a time window to perform this task.

Thanks


Since Char 12 and 13 seem to be hours, with SQL Server 2012, you can build a new time using this part and 0 for missing parts:

  TIMEFROMPARTS ( hour, minute, seconds, fractions, precision )

Select TIMEFROMPARTS(SUBSTRING([Dato Time], 12, 13), 0, 0, 0, 0)

MSDN: TIMEFROMPARTS (Transact-SQL)

With SQL Server prior to 2012, you can use such queries:

Select DATEADD(HOUR, Cast(SUBSTRING(@x, 12, 13) as int), CAST('00:00' as time))
Select Cast(SUBSTRING(@x, 12, 13)+':00' as time)


Replace the server name in email sender address when sending email from SQL Server 2008

Try creating a new database mail profile and database mail account. Put the email address you desire as the name of the new mail account. Then use the profile when calling the stored procedure to send email:
EXEC msdb.dbo.sp_send_dbmail
    @profile_name = "DB Mail Profile"

Cheers!


Amazon RDS for MySQL vs installing MySQL on an Amazon EC2 instance

I'm a big AWS fan in general... but RDS, not so much.

@RolandoMySQLDBA has pointed out are some pretty good points against it.

The only advantage I see in RDS compared to MySQL on EC2 are the ability to do point and click snapshots, clones, and point-in-time recovery, but these are not nearly sufficient to make up for the loss of control and flexibility and they most certainly don't justify the price being higher.  RDS is sexy in some ways, but you can't ultimately trust what you can't ultimately fix, because you can't get to all the moving parts.

I don't like not having the SUPER privilege.  I don't like not being able to tail the error log.  I don't like not being able to run "top" or "iostat" on my database server to see how the cores and drives are enjoying the load.  I don't like not having access to the federated storage engine.  I don't like the thought of paying for a hot standyby (multi-AZ) backup master machine that I can't even leverage as a read replica.  Sure, there are perfectly reasonable explanations why all of these constraints have to be in place for MySQL to be successfully packaged and sold as RDBMS-in-a-box.  The only thing is, RDBMS-in-a-box "solves" a whole series of problems I don't have... and gets in my way, causing new problems.

But the absolute deal-breaker for me with RDS is the complete lack of access to the binary logs and replication.  Binlogs, especially row-based, are a fantastic recovery tool for minor disasters, but they are of no help to you if you can't access them.  Want to configure an on-premise server at your office as a read-replica of your production database in RDS?  Something to take local backups from, do reporting, have on hand for disaster recovery should something unthinkable happen to your data that lives in RDS?  That's an idea that is simultaneously obvious and brilliant.

Oops, sorry, direct access to replication is not available.  Sure, you can pay for read replicas... but only as other RDS instances.  Not a value proposition in my book.

Update: One Significant Change in RDS for MySQL 5.6

I still prefer running my own server (even in EC2) as opposed to running RDS for a number of reasons, including the lack of support for User-Defined Functions, the inability to use the Federated Storage Engine, and the inability to have the one extra connection available for emergency access... however...

Amazon has made a significant change in MySQL 5.6 for RDS that eliminates one of my major objections -- perhaps my largest objection: the binary logs are now accessible and you can run a non-RDS instance as a slave, or connect other utilities to the server that read the binlog stream.

http://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/MySQL.Procedural.Exporting.NonRDSRepl.html

Officially, the documentation indicates that they are exposing this so that you can set up a slave for the purpose of doing a live migration -- you synchronize the foreign future master server from the existing RDS instance using mysqldump, then connect it to RDS as a slave to get a live feed of updates via the replication stream until your application is migrated to the new master -- but unofficially, you can do this on an ongoing basis as long as you don't expect them to support you... which, to me, seems reasonable.

This was confirmed in a recent Webinar, in a conversation that begins at around 56:45:

  "You can keep it in a replicated state indefinitely...

  "...as long as you take the responsibility to maintain the replication..."

  "We are not preventing you from doing ongoing replication if that's what you want."

This new capability was enough for me to drop my blanket objection to using RDS in our public-facing website-backing MySQL instances, where we don't use FEDERATED or some of the other things as much or at all.

So I'm still not in favor of it, but I'm no longer against it, since having a live stream of the binary logs puts me ultimately back in control of the data in real time and the responsibility for ensuring that no transactions are lost in a catastrophic outage is back with me, because I, as the DBA, am back in control -- which is exactly how I want it.  Having a third party vendor to point fingers at, or file a lawsuit against, or whatever, doesn't get your lost data back if it disappears down a black hole inside a black box.

Management seems to like the "idea" of RDS and doesn't object to the cost difference, so we are now launching all new web sites with RDS behind them.

The point and click point-in-time recovery, I admit, is a nice feature in RDS... it doesn't alter or disrupt your existing machine -- instead, it fires up an entirely new instance, using the backup that was closest-in-time to the selected point in time, and then applies the necessary binlogs to bring that new machine forward to the point in time that you've specified.

Related to this, but in the other direction, it's also possible, now, to use a similar strategy to migrate a live MySQL database into RDS... you can connect an RDS master (presumably, typically, this would be a newly-deployed instance) as a slave of an existing system so that the RDS instance has the live version of the data at the time you migrate into it.  Unlike access to the RDS binlogs for outward replication, which only works in 5.6, the inward replication is supported in RDS beginning with 5.5.33 and 5.6.13.


Without the DDL and sample (raw) data, and not sure how you go from the first result set having all cnam = "foo foo" to the second result set having CNAM = "foo" or "foo2" ...

You could start with something like ...

select cnam,
       count(disposition) as TOTAL,
       sum(case when disposition = 'ANSWERED' then 1 else 0 end) as COMPLETED,
       sum(case when disposition = 'NO ANSWER' then 1 when disposition = 'BUSY' then 1 else 0 end) as MISSED,
       SEC_TO_TIME(sum(billsec)) as "Total DURATION",
       SEC_TO_TIME(avg(billsec)) as "AVG DURATION"

from   (SELECT SUBSTRING(channel,1, LOCATE("-",channel, LENGTH(channel)-8)-1) AS chan1,
               cnam,
               duration,
               disposition

        FROM   asteriskcdrdb.cdr

        WHERE  calldate >= '2017-12-08 00:00:00'
        AND    calldate <= '2017-12-08 23:59:59'
        AND    (duration-billsec) >=0
        HAVING chan1 LIKE 'SIP/3%'
            or chan1 IN ('SIP/2000','SIP/2016','SIP/2046')
       ) dt

group by cnam
order by cnam asc

... keep in mind that I'm not 100% sure of your logic (eg, should total/avg duration be based only on COMPLETED data?) so I mostly cut-n-pasted what you had originally.

OK, thinking about this a bit more ...

The derived table isn't really doing anything other than filtering out rows from the base table, which means the derived table could be eliminated.

One possible flattening of the query (using RDFozz's modified WHERE clause):

select cnam,
       count(disposition) as TOTAL,
       sum(case when disposition = 'ANSWERED'  then 1 else 0 end) as COMPLETED,
       sum(case when disposition = 'NO ANSWER' then 1 when disposition = 'BUSY' then 1 else 0 end) as MISSED,
       SEC_TO_TIME(sum(billsec)) as "Total DURATION",
       SEC_TO_TIME(avg(billsec)) as "AVG DURATION"

FROM   asteriskcdrdb.cdr

WHERE  calldate >= '2017-12-08 00:00:00'
AND    calldate <= '2017-12-08 23:59:59'
AND    (duration-billsec) >=0
AND    (   channel LIKE 'SIP/3%'
        OR channel LIKE 'SIP/2000-%'
        OR channel LIKE 'SIP/2016-%'
        OR channel LIKE 'SIP/2046-%')

group by cnam
order by cnam asc


Reference outer variables in nested select in bigquery vs mysql

Try removing the GROUP BY from the main query and converting the join to PRFXad_reports to an EXISTS subquery:

SELECT
    ads.ad_id,
    ( SELECT count(*)
      FROM `PRFXvisits` AS v
      INNER JOIN `PRFXvisit_data` AS vd
      ON vd.visit_id = v.id
      WHERE v.date_time >= @xday AND v.date_time < @xdaytomorrow
      AND ads.id = vd.ad_id
    ) AS clicks
FROM `PRFXads` AS ads
WHERE ads.is_archive = 0
  AND EXISTS
      ( SELECT 1
        FROM `PRFXad_reports` AS r
        WHERE r.ad_id = ads.id
          AND r.rdate = @xday
      )
ORDER BY ads.ad_id ;


How is IF NOT EXISTS SELECT THEN INSERT faster than UNIQUE index?

FINAL UPDATE:

It's the INSERT that's really slowing things down.

When a Unique index is in place, with every new record you add SQL has to check if the value already exists. As the table grows, the amount of cross-referencing increases. A non-unique index will require no cross-referencing so performance is constant.

Unique indexes are usually faster for SELECT statements but that comes at a cost when updating a table.

Below is why the SELECT can sometimes be slower on a Unique Index

I've partially recreated your situation to the point where I think it is down to combination of Parameter sniffing and SQL prefers using the NON-UNIQUE index on a HEAP.

Set up 2 test tables, one of them is a heap (just like your table).

CREATE TABLE dbo.TEST1(ID VARCHAR(255) NOT NULL,TXT1 VARCHAR(255) NOT NULL,TXT2 VARCHAR(255) NOT NULL)
CREATE TABLE dbo.TEST2(ID VARCHAR(255) NOT NULL,TXT1 VARCHAR(255) NOT NULL,TXT2 VARCHAR(255) NOT NULL)
GO
INSERT INTO dbo.TEST1 VALUES(NEWID(),NEWID(),NEWID())
GO 30000

INSERT INTO dbo.TEST2
SELECT * FROM dbo.TEST1
GO

CREATE CLUSTERED INDEX cidx ON dbo.TEST1 (ID)
CREATE INDEX idx_nu ON dbo.TEST1 (ID)
CREATE UNIQUE INDEX idx_u ON dbo.TEST1 (ID)

CREATE INDEX idx_nu ON dbo.TEST2 (ID)
CREATE UNIQUE INDEX idx_u ON dbo.TEST2 (ID)

Review the footprint of the Indexes, on the HEAP the UNIQUE index has a smaller footprint than the NON-UNIQUE index. (perhaps the pages of the NON-UNIQUE index contain extra - possibly useful - information)(note: after running the above code multiple times the page count doesn't differ, probably due to caching, alter the "GO 30000" to rectify the issue.)

SELECT
    s.name AS SchemaName,
    t.name AS TableName,
    i.name AS IndexName,
    p.row_count,
    SUM (p.used_page_count) as used_pages_count,
    SUM (CASE
            WHEN (i.index_id < 2) THEN (in_row_data_page_count + lob_used_page_count + row_overflow_used_page_count)
            ELSE lob_used_page_count + row_overflow_used_page_count
        END) as pages
FROM
    sys.dm_db_partition_stats  AS p
        JOIN sys.tables AS t
            ON
            p.object_id = t.object_id
        JOIN sys.indexes AS i
            ON
            i.[object_id] = t.[object_id]
            AND
            p.index_id = i.index_id
        JOIN sys.schemas AS s
            ON
            t.schema_id = s.schema_id
WHERE
    t.name IN ('TEST1','TEST2')
GROUP BY
    s.name
    ,t.name
    ,i.name
    ,p.row_count

Now query the tables with literals and variables.

--SCAN of the UNIQUE index
DECLARE @account_id VARCHAR(255) = (SELECT TOP 1 ID FROM dbo.TEST2 WHERE ID like '%A%')

--Parameter Sniffing kicks in --The optimiser doesn't know the value of @account_id

--SEEK of the CLUSTERED index
DECLARE @ID1 VARCHAR(255)  = (SELECT TOP 1 ID FROM dbo.TEST1 WHERE ID = @account_id)

--SEEK of the NON UNIQUE index
DECLARE @ID2 VARCHAR(255)  = (SELECT TOP 1 ID FROM dbo.TEST2 WHERE ID = @account_id)

For some reason SQL prefers the NON UNIQUE index on a HEAP when performing SEEK operations.

Here's what I think is going on. When the Non-Unique index has more Pages then the corresponding Histogram in the STATS has more STEPS, run below code.

DBCC SHOW_STATISTICS ( 'TEST2' , 'idx_nu' )
DBCC SHOW_STATISTICS ( 'TEST2' , 'idx_u' )

The additional STEPS create a more granular view of the underlying Index, so the Optimiser (knowing that the EQ_ROWS is always 1) is getting a better Cardinality Estimate from the Non-Unique index.


If you are concerned about the size you can do a binary lookup:

North = 1
Middle = 2
South = 4
Est = 8
Ovest = 16
Mountainous = 32
Hilly = 64
Flat = 128

Of course, then you have to write a function which translates your number into readable results.


Maximum or suggested number of bound query-parameters in InnoDB (MySQL)?

With that many values you are likely to run into estimation errors during query plan generation. This will lead to it choosing worse (slower-performing) plans than it might otherwise.

With that many values I would suggest INSERTing them into an actual table, with index(es), which is JOINed to in the query. Logically the two are the same. Practically, aesthetically and performance wise it is likely to be better, even with the additional writes.


Problems connecting remotely to a RAC DB via sqlplus

Remote SYSDBA login uses password file authentication. A 11g RAC database uses local password files on each node in the local filesystem ($ORACLE_HOME/dbs). When you grant SYSDBA to a user, or change the password of this user, the password file is automatically updated on the node where the command was executed, but not on the other nodes. You need to manually copy the updated password file to the other nodes as well, otherwise you will not be able to log in to the other nodes.

Starting with 12c, the database password file can be stored in ASM, and it is not needed to perform such manual maintenance steps anymore.


how to select first 9 numbers from other table from database using sql query?

If I understand correctly your requirement, this should do the trick:

WITH MAINFILE (facno) AS (
    SELECT '010000101000001'
),
GLMAST (fglcode) AS (
    SELECT '010000101'
)
SELECT MAINFILE.facno --,GLMAST.fglhead
FROM MAINFILE
INNER JOIN GLMAST
    ON MAINFILE.facno LIKE GLMAST.fglcode + '%'
    --and GLMAST.fglhead='" & ComboBox2.Text & "' "

Please note that the predicate on GLMAST.fglhead is prone to SQL injection. I strongly suggest to rewrite this code in a more secure way.


You cannot do that. Use one of the other formats; ideally the custom format, which is automatically compressed.


If I kill a looping INSERT query, will it rollback all inserts since the loop started or only the last iteration?

Do all inserts or only the current one rollback if the following loop query was killed in the middle of looping?

If the session has started a transaction before the loop, then all statements will be rolled back.  But by default each statement runs in a separate transaction, and only the currently-running INSERT would be rolled back.


Assuming from your code snippet that the displayed JSON is the value of a key "productCategories" in a jsonb column object_data of your table tbl, you can achieve your desired sort order like this:

SELECT t.*
FROM   tbl t
LEFT   JOIN LATERAL (
   SELECT elem->>'priority' AS prio
   FROM   jsonb_array_elements(t.object_data->'productCategories') a(elem)
   WHERE  elem->>'categoryName' = 'Trucks'  -- your category here
   ) a ON true                              -- preserve all rows from t
ORDER  BY prio;                             -- NULL values are sorted last

Related answers with detailed explanation:

How to sort objects in an array inside a json or jsonb value by a property of the objects?
Customize jsonb key sort order involving arrays
What is the difference between LATERAL and a subquery in PostgreSQL?
PostgreSQL sort by datetime asc, null first?


If your archive table does not.

Have enabled triggers defined on it.
Participate on either side of a FOREIGN KEY constraint.
Have CHECK constraints or enabled rules.

You could also do it in one statenent.

DELETE FROM source_table
OUTPUT deleted.Foo,
       deleted.Bar,
       SYSUTCDATETIME()
INTO archive_table(Foo, Bar, archived)
WHERE  Foo = 1;

This will either succeed or fail as a unit and also avoids possible race conditions with rows being added in between the INSERT to the archive and DELETE (though your WHERE clause may well make this extremely unlikely anyway).


Quickly updating table from large tsv, retaining a timestamp

It's 4 statements:

CREATE TABLE Staging ...;  (and a DROP or TRUNCATE)
LOAD DATA ... INTO Staging ...;
INSERT INTO Real (...)  SELECT ... FROM Staging LEFT JOIN Real ...;
UPDATE Real JOIN Staging ON ... SET ...;

The LOAD is very fast for getting the TSV/CSV into a table.  staging is purely for this periodic 'update' process.

The SELECT finds the 'new' rows and feeds them to the INSERT to do the rest add new rows.

The UPDATE is to update all the rows for which you have new data, and to update imported.

If uid is AUTO_INCREMENT, I deliberately avoided INSERT ... ON DUPLICATE KEY UPDATE ... because the latter would 'burn' ids, thereby threatening to eventually overflow the range of uid.

For more discussion, see my blog on ingestion .  That section of the blog details how to augment a Normalization table with one INSERT and one UPDATE.  Your case is very similar.  The rest of the blog focuses on 'continual' ingestion, unlike your 'periodic' ingestion.

If your incremental load is bigger than a million rows, there are other aspects we need to discuss.


How I should design tables for transaction (payment) for banking?

i think there are a lot of different solutions in the field.
There are different workflows on wirecard, Computop, Saferpay and so on.
Basicly i prefer these steps.

Store the initial Step (init payment) with timestamp, useragend... (Table A)
Put this id to the Payment-Request to the prowider as an optional field.
Often you get an response to yor success-url and to a notify-url. I fetch the full response and store it as a json string ( for debug ) (Table B)
Update Table A with the encrypted data from the Response ( SUCCESS|FAIL ... )
If success, update the shopping basket
If you have problems, you need as much information as you can have. Specially for seamless payment.


I'm looking for an open source app to backup sql server / mariadb / postgres.

One app?  Singular?
You won't find one.
Every DBMS does things ever so slightly differently from all the others.  Most significantly, each and every one has its own utilities for backing up and restoring its databases.
There is no "one size fits all" solution that will do everything.
As the Database Administrator, you have to know and understand each one of them.

I want to schedule backups with the app.

Scheduling needs a scheduling program.  Almost any one will do.
Backing up each DBMS needs the utilities for that DBMS.
Restoring any database needs the utilities for that DBMS and is, usually, a manual activity.


Storage and presentation of database data across multiple languages

Well, a lot of this depends on exactly what data elements need to be translated, and at the moment I think you might be focusing too much on technical specifics instead of where the need truly lies.

Given this:

  ASP.Net uses resource files to provide the interface in the local language

what elements need to be translated in the database?

Application / System lookup values? Probably not these since the UI is handling that via resource files.
User data? No, this wouldn't make sense. If someone fills out a form, they don't expect it to magically appear in 20 different languages.
Customer configured lookup values? Possibly, but they would be the ones supplying the translations.

Can you provide 1 or 2 examples of something that would need this structure? It seems that this structure is what would be used if one were not using resource files.

The only globalization issue I can see here is how to handle sorting and comparison according to each user's preferred language, given that many cultures have different rules, even if sharing many / most of the characters. In this respect, you will need to make use of Dynamic SQL because unfortunately you cannot specify collations dynamically (please vote for my suggestion to allow for this: Allow collation set by variable when using the COLLATE clause (at least in expressions) ). But for now (or possibly forever given the likelihood of that request being implemented :-(  ), you will need to attach the COLLATE keyword to relevant parts of queries (WHERE / GROUP BY / ORDER BY / etc) based on the current user's language. So one person might get French_100_CI_AS and someone else might get Latin1_General_100_CI_AS, and so on. This doesn't require multiple fields.

The only thing that different columns for different languages would get you is the ability to index the different collations (i.e. locales). So if there is a place that really needs performance, you can consider specifying the primary language as the collation of the column, and then create non-persisted computed columns that simply return the main column along with a different collation, and then index the non-persisted computed column(s).

If you feel that your app really will need to store multiple languages worth of a single item that would not be handled by the resource files, then please provide at least one, if not two examples. But even if there are some, I can't see how it would ever be all, or even most, columns. Things like customer names don't change based on what language they come from. I mean, I did adopt a Spanish form of my name for Spanish class in Junior High and High School, but that was for class. If I become a customer of a company in a Spanish-speaking country, I will still give them my real name. And the name of my street, city, and state won't change either.


Access - SQL Server: cannot generate SSPI context

Why not have both?

That login screen you're seeing is SQL server rejecting the login parameters.

Create a OU and label it something like SQLAccess. Add all the appropriate users to the OU.
From there goto SSMS, add the OU to the appropriate logins.
Set your permissions
Create a DSN(Or use current). When it says use Network ID for authentication, make sure it's checked/bubbled. It uses the current user's credentials.

With the above steps done, when your enduser opens the access file, SQL will shake behind the scenes and thus allow your custom login screen to pop... Only showing 1 login screen. Also should a user ever leave the company, just remove them from the OU and you're golden(or deactivate the account)

In theory you could develop a DSN for every specific users and specify the login with a SQL login but that is tedious, unless of course you do a universal login. I don't recommend it for obvious reasons.


Anything that you do here will impact performance somewhere.  But if it's a requirement then it's a requirement.  The methods you've laid out in your question are the accepted, low impact methods of getting that.

You can mitigate the large log files by rolling over to a new file more frequently.  By default it does that on every server restart, but you can add an agent job to run sp_cycle_errorlog every day, week or whatever.  Then change the number of files to retain (limited to a max of 99).

--May need to adjust if running an instance.
EXEC xp_instance_regwrite N'HKEY_LOCAL_MACHINE', N'Software\Microsoft\MSSQLServer\MSSQLServer', N'NumErrorLogs', REG_DWORD, 90

But that just tells you that the user in question logged on.  If you need to do more with that information such as track what they did, then you will need to turn on Auditing (recommended if it's a legal requirement such as HIPAA/SOX; although only available in Enterprise) or you can just use Extended Events.  Extended Events are as lightweight as you can get with tracking statements against the database.

There is one other option and that's a logon trigger, but I would strongly advise NOT to follow that route.  If the logon trigger messes up for whatever reason then you could prevent anyone from logging into the database.  But it only gives you slightly more information than you would get from turning on successful and failed login monitoring.


Are views harmful for performance in PostgreSQL?

The book is wrong.

Selecting from a view is exactly as fast or slow as running the underlying SQL statement – you can easily check that using explain analyze.

The Postgres optimizer (and the optimizer for many other modern DBMSes) will be able to push down predicates on the view into the actual view statement – provided this is a simple statement (again, this can be verified using explain analyze).

The "bad reputation" regarding performance stems – I think – from when you overuse views and start building views that use views that use views. Very often that results in statements that do too much compared to a statement that was hand-tailored without the views e.g. because some intermediate tables wouldn't be needed. In almost all cases the optimizer is not smart enough to remove those unneeded tables/joins or to push down predicates over multiple levels of views (this is true for other DBMSes as well).


How to migrate from a MySQL master-slave setup to a master-master setup without downtime

On a basic level, yes. Just set up the current Master like you would any normal slave database.

However, there are some things to consider first.

Is the second slave set to READ-ONLY, as you want to avoid writing to both masters at once.

You probably want to set auto_increment_increment=2 and auto_increment_offset=1 on the first master, and auto_increment_increment=2 and auto_increment_offset=2 on the second master.

This prevents the two masters from creating the same auto_increment primary keys.

You also want to make sure you have no replication filters (binlog-do-db, replicate-do-db etc) set up as they can cause problems too).


SQL Server 2012 Standard Edition Disaster Recovery Options?

Could you please let me know the best option to proceed and least complexity to maintain

Logshipping is your best option for Disaster Recovery since you are on standard edition.

Make sure you test the disaster recovery - simulate it so that you are confident when a real disaster strikes.

Note: In my company, we use LogShipping as a primary DR solution involving 2500 databases globally and it works perfectly fine and fulfills our business continuity goal (it worked during Hurricane Sandy as well). We automate most of the things and test(simulate) every quarter per individual database and yearly the entire data center. THis gives us a huge confidence when doing an actual failover during a disaster.

Check out the things to keep in mind when going for Logshipping :

  Log shipping is a low-cost (poor-man) solution that provides disaster recovery protection at the database level. A log shipping configuration includes one primary server and one or more secondary servers. A database is backed up and restored from the primary server to the secondary server. At regularly scheduled intervals, a transaction log backup and restore is performed at the primary and secondary server to keep the servers in sync.

Also, Kendra Little has an excellent post on Log Shipping Magic: Using A Differential Backup to Refresh a Delayed Secondary

Note: As per your question, it seems to me that you are mixing up HA and DR. They are different when it comes to RTO and RPO vs availability of your application.