When to use stored procedure / user-defined function?

Postgres 11 or newer
SQL procedures ("stored procedures") that can also start and end transactions:

The manual for CREATE PROCEDURE.

2ndquadrant blog entry introducing the feature.

However, commands that are not allowed in a transaction block can not (yet) be included. Important examples CREATE DATABASE or CREATE INDEX CONCURRENTLY or VACUUM.
And procedures can only return a single result row, yet, when created with INOUT parameters.
Postgres 14 or later also allows OUT parameters.
Postgres 10 or older (original answer)
There are no "stored procedures", strictly speaking. Just functions, doing almost but not quite the same. Most importantly, functions always run inside a transaction, which matters for lock management, trapping errors, or commands that cannot be run in a transaction context (see above). Related:

In PostgreSQL, what is the difference between a "Stored Procedure" and other types of functions?

Processing data in SQL can have major benefits over retrieving all raw data into your application and doing calculations there:

What are the pros and cons of performing calculations in sql vs. in your application

The same principle can apply to bigger operations that can't be done in a single statement, not even when using "chained" commands in a (data-modifying) CTE:

Are SELECT type queries the only type that can be nested?
RETURN QUERY-Record in PostgreSQL

Depending on the use case, a function can be superior in performance, and often more convenient - especially when multiple apps access the same database. You have to chose a server-side language, most commonly SQL or PL/pgSQL:

Difference between language sql and language plpgsql in PostgreSQL functions
Function Performance

In the presence of competing concurrent transactions (multi-user environments) be brief. Only put as much into the same function as needs to be atomic. If possible, avoid long transactions with many locks. Locks are only released at the end of a transaction. Long transactions may stall concurrent access, even lead to deadlocks (which are detected and resolved by Postgres automatically, by raising an error for one or more competing transaction, which are then rolled back) ...
If you avoid those anti-patterns, server-side functions can be a great tool. For some purposes you must use functions anyway, like trigger functions.


Optimization paramters read-only mysql 5.7 database?

I can think of several things that might help.  But I can't be specific without seeing that query, plus SHOW CREATE TABLE.

If we can shrink the table/index size, there might be less I/O.  If the index involves a UUID, then I will have lots to say.

Sometimes rearranging the index(es) helps.

If you are not using InnoDB, we need to discuss that.

Let's see SHOW GLOBAL STATUS; and SHOW VARIABLES; -- there are some metrics that will say how well the Query cache is doing, info about connections, whether the buffer_pool is adequately big, plus other things that might give clues.

In general, "you can't tune your way out of a performance problem."

For "readonly", add this to the config file:

read_only = ON

but I doubt if it will impact the performance.


SQL Server Management Studio Doesn't do "Full" Backup

The issue is the defualt backup value of NOINIT
https://docs.microsoft.com/en-us/sql/t-sql/statements/backup-transact-sql?view=sql-server-2017

Media Set Options
These options operate on the media set as a whole.
{ NOINIT | INIT } Controls whether the backup operation appends to or overwrites the existing backup sets on the backup media. The default is to append to the most recent backup set on the media (NOINIT).

Go to the options tab and change the selection to overwrite, or change the SQL to INIT

Alternatively you can restore from the NOINIT backup using the directions at this post SQL Recover from .bak file with NOINIT


Dump all Views of multiple databases in SQL Server

You will certainly need the right permissions to view the views definition. This should work for you. Please note that I'm using the undocumented sp_MSforeachdb stored procedure. This means that it could change without notice so is not advisable to include as part of your production/live tsql code as it could fail. Check Aaron comment about it.

EXEC sp_MSforeachdb '
    IF DB_ID(''?'') > 4
    BEGIN
        USE [?]
        SELECT DB_NAME()
        SELECT  c.text
            FROM    sysobjects o
                    JOIN syscomments c
                        ON c.id = o.id
            WHERE   o.type = ''V'';
    END'


How do I force SQLite to materialize a subquery?

From the SQLite docs (my emphasis):

  Subqueries might also need to be materialized when they appear in the
  FROM clause of a SELECT statement. For example:

SELECT * FROM ex1 JOIN (SELECT b FROM ex2) AS t ON t.b=ex1.a;

  Depending on the query, SQLite might need to materialize the (SELECT
  b FROM ex2) subquery into a temporary table, then perform the join
  between ex1 and the temporary table. The query optimizer tries to
  avoid this by "flattening" the query. In the previous example the
  query can be flattened, and SQLite will automatically transform the
  query into

SELECT ex1.*, ex2.b FROM ex1 JOIN ex2 ON ex2.b=ex1.a;

  More complex
  queries may or may not be able to employ query flattening to avoid the
  temporary table. Whether or not the query can be flattened depends on
  such factors as whether or not the subquery or outer query contain
  aggregate functions, ORDER BY or GROUP BY clauses, LIMIT clauses, and
  so forth. The rules for when a query and cannot be flattened are very
  complex and are beyond the scope of this document.

You may be able to work around the problem with a view, but it seems that in this case at least, adding a dummy union is enough to prevent "flattening":

select op, op
from (select abs(random())%10 as op from (select 1) union all select 1 where 1=2);

6|6


Move the qnt <= 0 inside as a HAVING (not WHERE).

Use JOIN ... ON ... instead of FROM a,b WHERE a...=b...

Add the following composite indexes:

a: INDEX(type, code, color)
b: INDEX(code, color, qnt)
c: INDEX(modello, commessa, stagione)


Does MS SQL Server have generate_series function

A fairly common method of creating a series in T-SQL consists of using a CTE as the source, something like:

;WITH t AS
(
    SELECT n = v2.n * 10 + v1.n
    FROM (VALUES (0), (1), (2), (3), (4), (5), (6), (7), (8), (9)) v1(n)
    CROSS JOIN (VALUES (0), (1), (2), (3), (4), (5), (6), (7), (8), (9)) v2(n)
)
SELECT t.n
FROM t
ORDER BY T.n;

The query above will return an ordered list of values from 0 to 99.

For dates, you can implement it like this:

;WITH t AS
(
    SELECT n = v2.n * 10 + v1.n
    FROM (VALUES (0), (1), (2), (3), (4), (5), (6), (7), (8), (9)) v1(n)
    CROSS JOIN (VALUES (0), (1), (2), (3), (4), (5), (6), (7), (8), (9)) v2(n)
)
SELECT DATEADD(DAY, t.n, '2019-01-01')
FROM t
ORDER BY T.n;

Which provides a list of dates starting at January 1st, 2019, and continuing for 100 days, ending on April 10th, 2019.

There are numerous other ways of generating a set like this, including generating a "numbers" table.


How long does query optimisation take relative to the execution time?

There is no absolute number for the percentage of database time spent planning queries, it depends on your workload.

If the workload consists of analytical queries, planning time is usually insignificant in comparison to query execution time.

If the workload consists of simple OLTP queries, planning time can exceed query execution time.

I didn't read the article you quote, but the PostgreSQL optimizer is invoked once per SQL statement executed. Of course it considers several plans to find the best one.
For a simple benchmark, you can use the built-in PostgreSQL benchmark tool pgbench. While it is not a full-fledged workload simulator, it is surprisingly volatile. I'll use the default workload, which is a simple OLTP workload.
First, create a sample database with a scale factor appropriate for what you want to test (in this case, 10 million rows in the largest table):
pgbench -i -s 100 mydb

Then run the benchmark with the “simple query protocol” (one-shot queries that are planned each time they are executed):
pgbench -c 4 -T 60 mydb
[...]
tps = 3922.296560 (excluding connections establishing)

Repeat the benchmark using the extended query protocol with prepared statements, so that query plans are reused (this roughly corresponds to query execution time without planning):
pgbench -c 4 -M prepared -T 60 test
[...]
tps = 5022.185127 (excluding connections establishing)

So in this simple scenario, we got more than 25% more transactions done by excluding planning time. For more reliable values, run the test for more than 60 seconds.
Of course this number doesn't accurately measure query planning time versus query execution time, because there is still the latency of the round trip between client and server to consider. You could get better numbers by writing your own PL/pgSQL code that runs static or dynamic SQL statements in a tight loop. Since the plans of static SQL statements in PL/pgSQL statements are cached, their runtime will exclude query planning time.
You can also write your own benchmarks with pgbench; it allows you to write custom scripts and even offers a simple scripting language.


First of all you need to make sure you are auditing both successful and failed logins

  EXEC xp_instance_regwrite N'HKEY_LOCAL_MACHINE', N'Software\Microsoft\MSSQLServer\MSSQLServer', N'AuditLevel', REG_DWORD, 1

after a restart you can read the information from the SQL Server errorlog

$server = get-item SQLSERVER:\sql\localhost\default
$server.ReadErrorLog() | ? { $_.processInfo -eq 'Logon' -AND $_.Text -match 'sa'}

or grab it from the application log


The difference lies between a query and a transaction.  A transaction can contain any number of queries.  To illustrate the difference, I set up a small example:

CREATE TABLE table_to_be_updated (
    id serial PRIMARY KEY,
    other_column text,
    column_changing text
);

INSERT INTO table_to_be_updated (other_column, column_changing)
VALUES
('value', 'old_value'),
('value', 'other_value'),
('nonvalue', 'doesnt matter');

Then run two transactions concurrently (issuing the commands one by one, the middle line wants to depict the timeline):

                                | <-- BEGIN;
                                |
                                |
                                |     UPDATE table_to_be_updated
BEGIN; -----------------------> |     SET column_changing = 'new_value'
                                |     WHERE
                                |         other_column = 'value' AND
                                |         column_changing = 'old_value';
                                |
                                |
SELECT column_changing -------> |     -- update not yet committed
FROM table_to_be_updated        |
WHERE other_column = 'value';   | <-- COMMIT;
                                |
                                |
SELECT column_changing -------> |
FROM table_to_be_updated        |
WHERE other_column = 'value';   |
                                |
                                |
COMMIT; ----------------------> |

Running these in READ COMMITTED isolation level, the first query returns a row with 'old_value', while the second one shows a row with 'new_value'.  On an other run, I change the left-hand-side transaction isolation level:

SET transaction ISOLATION LEVEL REPEATABLE READ;

(The command must be the first statement in a transaction.)
Now both SELECTs return the same rowset, while a third one after committing both transactions will show the new row.


How to wait for SQL Server replication reinitialization via TSQL

The only way I can think of doing this is to slightly hack the process. It would involve the following steps:

Copy the Distribution agent job and rename it to indicate it is run
manually. You'll also need to change/disable the schedule and remove
the "-Continuous" flag from the exec command of this new job.
Create a job that does the following:

Step 1: Stops the main, continuous Ditribution job
Step 2: Executes the sp_reinitsubscription stored proc with
@invalidate_snapshot = 1
Step 3: Launches the Snapshot agent
Step 4: Checks that the Snapshot agent is complete*
Step 5: Launches the manual Distribution agent job

Create a job that checks if the manual Distribution agent job is finished

Step 1: Check if job is running.*
Step 2: Restart the main Distribution agent job

*I initially suggested setting the job to fail if the agent job was still running and configuring retry attemps. It might be better to write a WHILE loop with a WAITFOR DELAY instead:
  WHILE EXISTS(
   SELECT
     ja.job_id,
     j.name AS job_name,
     ja.start_execution_date,
     ISNULL(last_executed_step_id,0)+1 AS current_executed_step_id,
     Js.step_name
   FROM msdb.dbo.sysjobactivity ja
   LEFT JOIN msdb.dbo.sysjobhistory jh
        ON ja.job_history_id = jh.instance_id
   JOIN msdb.dbo.sysjobs j
        ON ja.job_id = j.job_id
   JOIN msdb.dbo.sysjobsteps js
        ON ja.job_id = js.job_id
        AND ISNULL(ja.last_executed_step_id,0)+1 = js.step_id
   WHERE ja.session_id = (SELECT TOP 1 session_id
                          FROM msdb.dbo.syssessions
                          ORDER BY agent_start_date DESC)
        AND j.name = 'Snapshot or distribution agent job name'
        AND start_execution_date is not null
        AND stop_execution_date is null)
BEGIN
  --PRINT 'Waiting...'
  WAITFOR DELAY '00:05:00'
END
RETURN


Oracle database - get two rows (A,B) for every (COD) row

I solve it with UNION ALL:

with cte as
        (select rap.cod,
                case
                 when (coalesce(val1_a.v1,0)<>0 or
                       coalesce(val1_a.v2,0)<>0 or
                       coalesce(val1_a.v3,0)<>0)
                 then
                    'A'
                end
                 as cat,
               coalesce(val1_a.v1,0) as v1_a,
               coalesce(val1_a.v2,0) as v2_a,
               coalesce(val1_a.v3,0) as v3_a,
               coalesce(val1_b.v1,0) as v1_b,
               coalesce(val1_b.v2,0) as v2_b,
               coalesce(val1_b.v3,0) as v3_b
         from rap
             full outer join val1_a on val1_a.fk_rap=rap.id
             full outer join val1_b on val1_b.fk_rap=rap.id
         UNION ALL
         select rap.cod,
                case
                 when (coalesce(val1_b.v1,0)<>0 or
                       coalesce(val1_b.v2,0)<>0 or
                       coalesce(val1_b.v3,0)<>0)
                 then
                    'B'
                end
                 as cat,
               coalesce(val1_a.v1,0) as v1_a,
               coalesce(val1_a.v2,0) as v2_a,
               coalesce(val1_a.v3,0) as v3_a,
               coalesce(val1_b.v1,0) as v1_b,
               coalesce(val1_b.v2,0) as v2_b,
               coalesce(val1_b.v3,0) as v3_b
         from rap
             full outer join val1_a on val1_a.fk_rap=rap.id
             full outer join val1_b on val1_b.fk_rap=rap.id
       )
 select cod,
        cat,
        case when cat='A' then v1_a
             when cat='B' then v1_b
        end
          as v1,
        case when cat='A' then v2_a
             when cat='B' then v2_b
        end
          as v2,
        case when cat='A' then v3_a
             when cat='B' then v3_b
        end
          as v3
 from cte


Changed a user name in MySQL, but user is still able to login using previous user name

There are several possibilities:

If you edited the privileges manually, you need to execute:

FLUSH PRIVILEGES;

To reload the privileges cache on memory (needed to speedup the checks on every query).
If you edited the privileges manually, you may have omitted an entry. Do not trust the SHOW GRANTS FOR, because it may omit the privileges edited directly on tables. Make sure that there are no user_dev not only on the user table, but on all these.
The fact that there is no user_dev user literally does not mean that a user_dev user cannot login. I will explain myself. The user that you submit is compared against the user privileges tables, so user_dev (let's say that is on localhost) matches:

'user_dev'@'localhost'
'user%'@'127.0.0.1'
''@'%'
'%'@'localhost'

In fact, by default, anonymous users from localhost can login, and all users can read and write on the test database. Run mysql_secure_installation to avoid that.


Is there a way to recover a dropped mysql database?

If you act fast there is a high chance to get your database back. The chance is higher for InnoDB, for MyISAM it's non-zero, but close.

The matter is when MySQL executes DROP TABLE or DROP DATABASE (which is essentially the same) InnoDB doesn't wipe the data out. Pages with the data are still on disk.

Depending on innodb_file_per_table setting the recovery process differs. If innodb_file_per_table is OFF (default up until 5.5) then the dropped table remains in ibdata1. If innodb_file_per_table is ON (default as of 5.5) then the dropped table was in the respective .ibd file. MySQL removes this file when drops the table.

The very first thing to do is to stop any possible writes so your table isn't overwritten. If innodb_file_per_table is OFF it's enough to stop MySQL (kill -9 is even better, but make sure you kill safe_mysqld first). If innodb_file_per_table is ON then umount partition where MySQL stores its data. If the datadir is on the root partition I recommend to shut down server or at least take an image of the disk. Let me repeat, the goal is to prevent overwriting dropped table by MySQL or operating system.

There is a tool that allows to work with InnoDB pages at low level,
TwinDB data recovery toolkit. I will use it to illustrate undrop recovery.

You need to take the media with dropped table (either ibdata1 or disk image) and find InnoDB pages on it. stream_parser tool from the toolkit does it.

./stream_parser -f /path/to/disk/image

It will scan the file, find the InnoDB pages and sort them by type and index_id. index_id is an identifier that InnoDB uses to refer to an index. A table is stored in index PRIMARY. To find what index_id is your dropped table you need to recover InnoDB dictionary.

The InnoDB dictionary is stored in ibdat1 file. You need to scan ibdata1 file the same way as above:

./stream_parser -f /var/lib/mysql/ibdata1

Now you need to get records from the InnoDB dictionary tables SYS_TABLES and SYS_INDEXES(let's say your table is sakila.actor):

./c_parser -4Df pages-ibdata1/FIL_PAGE_INDEX/0000000000000001.page -t dictionary/SYS_TABLES.sql | grep sakila/actor
000000000B28  2A000001430D4D  SYS_TABLES  "sakila/actor"  158  4  1 0   0   ""  0

158 is table_id, remember it.

./c_parser -4Df pages-ibdata1/FIL_PAGE_INDEX/0000000000000003.page -t dictionary/SYS_INDEXES.sql | grep 158
000000000B28    2A000001430BCA  SYS_INDEXES     158     376     "PRIMARY"       1       3       0       4294967295
000000000B28    2A000001430C3C  SYS_INDEXES     158     377     "idx\_actor\_last\_name"        1       0       0       4294967295

So, index_id of your dropped table(sakila.actor) is 376.

Now you can fetch records of the dropped table from InnoDB index_id 376.
You need to have the table structure of the dropped table, exactly CREATE TABLE statement which the table was created with. Where you can get it? Either from old backup, or from elsewhere. It's also possible to recover the structure from the InnoDB dictionary, but I won't cover it in this answer. Let's just assume you have it.

./c_parser -6f pages-ibdata1/FIL_PAGE_INDEX/0000000000000376.page -t actor.sql > dump.tsv 2> load_cmd.sql

c_parser outputs records as tab-separated dump to stdout. The dump can be loaded with LOAD DATA command. c_parser prints it to stderr.

See more details in posts:

Recover after DROP TABLE, innodb_file_per_table is ON
Recover after DROP TABLE, innodb_file_per_table is OFF
Recover InnoDB dictionary


At the moment for security issues I just changed auth true in the mongod.conf, created a new user for my test DB and changed the port to be something different than 27017.

  First question: There is something else to do to enhance security?

The MongoDB documentation has a Security Checklist and information on best practices. Aside from enabling authentication you will want to limit network exposure with proper firewall configuration and ensure you are running with secure configuration options (eg. HTTP and REST interfaces disabled). See the checklist for full details.

Changing the port from the default doesn't really enhance security, as that is trivial to discover with a port scanner. You want to have proper network security so potential attackers don't even get as far as connecting.

  Regarding the backup, I have seen different blogs but I didn't understand few things:

  Do Mongo create an automatic file for the backup (something called BSON file)?

No, MongoDB does not create backups by default. Recommended backup methods are described in the MongoDB manual. I would consider the MongoDB manual the definitive reference source as it is kept current with product changes. Blog articles may be outdated or contain incomplete/incorrect advice.

  Is it a good idea to backup the DB every time a value changed?

This will depend on your backup strategy. In the general sense, you would not be taking a full backup everything a single value changes using command-line tools like mongodump.

However, a typical deployment uses a replica set which provides for data redundancy and failover across multiple servers. Replication is based on an oplog (operations log) which contains a rolling history of all changes to data that gets replicated to all members of the replica set. Administrative tools like the MongoDB Management Service (MMS) can also use the oplog to create continuous backups and do point-in-time restores.


Maybe you should install SQL Search

I think you will find it as a 'Table-valued function'  (a function that returns a table )


Automatically keep synchronized copy of database

From everything you describe, I would think you would be better served by using Replication rather than Synchronization.

Replication allows you to have additional objects on the subscriber database that are not affected by the replication.

Your scenario fits a classic snapshot replication topology. I use that for reporting all time. Once it is setup it is fairly painless to replicate at whatever frequency suits you.

Here is a good article to cover setting it up: http://www.sqlservercentral.com/articles/Replication/snapshotreplication/819/


Sum of values each hour at half past (that hour)

Given the data:

CREATE TABLE #Demo
(
    Val integer NOT NULL,
    TS datetime2(3) NOT NULL
);

INSERT #Demo
    (Val, TS)
VALUES
(1, '2019-08-12 00:00:00.013'),
(3, '2019-08-12 00:10:00.013'),
(2, '2019-08-12 00:20:00.013'),
(2, '2019-08-12 00:30:00.013'),
(0, '2019-08-12 00:40:00.013'),
(0, '2019-08-12 00:50:00.013'),
(1, '2019-08-12 01:00:00.013'),
(7, '2019-08-12 01:10:00.013'),
(0, '2019-08-12 01:20:00.013'),
(1, '2019-08-12 01:30:00.013'),
(2, '2019-08-12 01:40:00.013'),
(0, '2019-08-12 01:50:00.013'),
(2, '2019-08-12 02:00:00.013'),
(0, '2019-08-12 02:10:00.013'),
(0, '2019-08-12 02:20:00.013'),
(0, '2019-08-12 02:30:00.013'),
(1, '2019-08-12 02:40:00.013'),
(0, '2019-08-12 02:50:00.013'),
(4, '2019-08-12 03:00:00.013'),
(3, '2019-08-12 03:20:00.013'),
(1, '2019-08-12 03:30:00.013');

One approach is:

Calculate the number of minutes from a fixed date (with no time component)
Add 30 minutes to each time to align half-hour slots to hours
Round to the hour (divide then multiply minutes by 60 using integer math)
Subtract the 30 minutes
Add the minutes to the same fixed date

This is shown in the code below:

SELECT
    TS = CONVERT(char(19), Timeslot.TS, 120),
    Val = SUM(D.Val)
FROM #Demo AS D
CROSS JOIN (VALUES(CONVERT(datetime2(3), '20190101', 112))) AS Base(FixedDate)
CROSS APPLY(VALUES((DATEDIFF(MINUTE, Base.FixedDate, D.TS) + 30) / 60 * 60)) AS Slot(InMins)
CROSS APPLY(VALUES(DATEADD(MINUTE, Slot.InMins - 30, Base.FixedDate))) AS Timeslot(TS)
GROUP BY
    Timeslot.TS
ORDER BY
    Timeslot.TS;

Output:

╔═════════════════════╦═════╗
║         TS          ║ Val ║
╠═════════════════════╬═════╣
║ 2019-08-11 23:30:00 ║   6 ║
║ 2019-08-12 00:30:00 ║  10 ║
║ 2019-08-12 01:30:00 ║   5 ║
║ 2019-08-12 02:30:00 ║   8 ║
║ 2019-08-12 03:30:00 ║   1 ║
╚═════════════════════╩═════╝

db<>fiddle demo

This produces a couple of extra periods compared with the output shown in the question, but it nevertheless looks correct to me.

If you prefer the (somewhat slow) FORMAT function, the following is equivalent:

SELECT
    TS = Slot.TS,
    Val = SUM(D.Val)
FROM #Demo AS D
CROSS APPLY
(
    VALUES
    (
        -- Remove 30 minutes
        DATEADD(MINUTE, -30,
            CONVERT(datetime2(3),
                FORMAT(
                    -- Add 30 minutes
                    DATEADD(MINUTE, 30, D.TS),
                    -- Truncate to hour
                    N'yyyy-MM-dd HH:00:00',
                    N'en-us'
                ), 120
            )
        )
    )
) AS Slot (TS)
GROUP BY
    Slot.TS
ORDER BY
    Slot.TS;


Like @ypercubeᵀᴹ noticed and suggested:

SELECT column_name
FROM information_schema.columns
WHERE table_schema = 'mydatabase'
  AND table_name='users';

does the trick


Select Statement with RegEx Replace - DB2 9.7

You could use the the

  replace

function like this:

select replace('Text   with  random      spaces', '  ', ' ')
  from yourtable

The second argument shows two spaces and the third is a single blank.

EDIT:

For multiple blanks this sould work - natively:

SELECT c1, length(c1), xmlquery('fn:replace($c,"[ ]+", " ")'  PASSING c1 AS "c")
  FROM test1


sp_sequence_get_range equivalent in Oracle

Just alter then increment the sequence.

For example, to increment by 1000:

ALTER SEQUENCE yoursequence INCREMENT BY 1000;

You then have to SELECT the NEXTVAL from the sequence and re-alter it to increment by the usual value to restore "normal" operation.

To check the current value without incrementing:

SELECT yoursequence.CURRVAL FROM DUAL;

If you need to decrease the sequence, just ALTER SEQUENCE by a negative amount (very intuitive!).


Plan A: (see answer by @hmikael)

Plan B: Change innodb_page_size to 32K.  However, this is a non-trivial task since it applies to all InnoDB tables.  Doing so would change the limit to ~16KB.

(Note: going to page size of 64KB won't get past 16KB limit per row.)

Plan C: Switch to MyISAM.  This is not advised for various reasons.

Plan D: build a "parallel table" or "vertically partition".  That is, split off some of the columns into another table.  Suggest moving rarely-used and/or bulky columns into the new table.  Both tables would have the same PRIMARY KEY (though at most one would say AUTO_INCREMENT).

Plan E: Shrink datatypes where practical.  That is, don't blindly use VARCHAR(255).  (This will also help with performance in a few situations.)  Don't use a 4-byte INT for a true/false flag; use a 1-byte TINYINT.  Etc.

Plan F: Normalize common strings.  (A 3-byte MEDIUMINT UNSIGNED is usually a lot smaller than a string.)

Plan G: Don't splay an array across columns.

(Some "Plans" can be used together.)


Informix trigger to delete records before insert

If you follow the syntax diagram(s) for CREATE TRIGGER, you find that the action clause needs either BEFORE, FOR EACH ROW or AFTER as a prefix before the action verbiage, and the triggered action segment shows you need parentheses around the triggered action(s).  Hence:

CREATE TRIGGER systables_growth_clnup
    INSERT ON systables_growth
    BEFORE (DELETE FROM systables_growth
            WHERE date_added < (CURRENT - 15 UNITS DAY)
           );


What factors will cause SQL Server to report different logical read counts for the same query in the absence of data writes?

For a heap table, there's no mechanism I know of that would cause this.

I notice that the author's reported logical reads only vary when compression was used. This suggests that the table rebuild was included on each execution. The rebuild statement does not specify MAXDOP = 1, so a parallel plan would have been generated. The way rows are distributed across threads in the rebuild at runtime is not deterministic (it depends on timing) so the final structure of the heap would vary. This is the only way I have been able to reproduce the published results.

For a clustered table (or a seek/scan of an index structure in general), the logical reads can vary due to read-ahead activity. Read-ahead reads pages from the upper levels of the b-tree to identify pages to read in advance of the scan, resulting in a varying number of 'extra' logical reads, depending on timing and the characteristics of the storage system. Disabling read-ahead with global trace flag 652 can be used to demonstrate this.

I mention the read-ahead aspect only for completeness, since the table in question is a heap. Heap scans do use read-ahead, but this is driven from the IAM pages. IAM pages do not form part of the main table structure (unlike the upper levels of a b-tree) so these extra reads are not reported by STATISTICS IO.


SQL substract values from same column different rows

retrieve balance quantity(qty) per each batchno or drugid

SELECT batchno, -- or drugid
       SUM(CASE type WHEN 'i' THEN qty  -- inward
                     WHEN 'o' THEN -qty -- outward
                     ELSE 0             -- unknown
           END) balance
FROM stock_table
GROUP BY batchno -- or drugid


I've changed LOCK_ESCALATION from default table to auto and now they
  do not block each other, but still I don't understand why server
  escalates lock in this case.

This is specifically called out in the SQL Server Books Online CREATE TABLE topic.  The note states:

  After you create a partitioned table, consider setting the
  LOCK_ESCALATION option for the table to AUTO. This can improve
  concurrency by enabling locks to escalate to partition (HoBT) level
  instead of the table.

The default table will escalate to the table level, which includes all partitions.

You could also improve both concurrency and performance of this query by adding Callid to the clustered index key and specify unique. This would touch only the single row needed by the query instead of scanning all rows in the partition with an exclusive lock.


I think this could help you.

I create this structure:

Employee
- EmpID (PK)
- EmpName
- TKID

Domain
- DomID (PK)
- DomName

Project
- ProjectID (PK)
- ProjectName
- ProjectStart
- ProjectEnd
- DomID (Asuming your projects are for Domains)

ProjectType
- ProTypeID (PK)
- ProTypeName

RTS
- ProjectID (PK)
- EmpID (PK)
- ProTypeID (PK)
- Active (PK)
- DateStart (PK)
- DateEnd

I fill the tables with the information you post on the screenshot (Only the RTS information is from 2015-06-01 to 2015-06-09).

Employees:

Domains:

Projects:

ProjectTypes:

RTS:

Query:

        SELECT
        E.EmpID,
        E.EmpName,
        E.TKID,
        D.DomName,
        P.ProjectName,
        PT.ProTypeName,
        ROUND(IFNULL((COUNT(DISTINCT CASE WHEN RTS.DateStart BETWEEN '2015-06-01' AND '2015-06-05' && RTS.Active IS TRUE THEN RTS.DateStart END)/5),0.0),1) AS '2015_06_[01-05]',
        ROUND(IFNULL((COUNT(DISTINCT CASE WHEN RTS.DateStart BETWEEN '2015-06-08' AND '2015-06-13' && RTS.Active IS TRUE THEN RTS.DateStart END)/5),0.0),1) AS '2015_06_[08-12]'
    FROM Employe as E
    LEFT JOIN RTS on (RTS.EmpID = E.EmpID)
    LEFT JOIN Project as P ON (P.ProjectID = RTS.ProjectID)
    LEFT JOIN Domain as D ON (D.DomID = P.DomID)
    LEFT JOIN ProjectType as PT ON (PT.ProTypeID = RTS.ProTypeID)
    GROUP BY E.EmpID;

Result:

The only problem with this is that you have to put the week date parameters you want to calculate. You can make a Stored Procedure that could read eStartDate and eEndDate variables and calculate the weeks between those dates, and all you have to do is make a Dynamic Query that concatenate every week between the parameters.

Example:

CALL SP_RTS('2015-06-01','2015-06-31',1);

Between 2015-06-01 and 2015-06-31 exists 4 weeks monday-friday, you'll need to concatenate 4 columns with those dates. I could help you but I dont know if my post if what are you looking for, if it is, let me know.


It has something to do with item 6 being after item 7 which is in another group. Because of that it is seen as a new count in my query, resulting in 5.  Is this time with id=6 correct? can it be after id=7?

The query has gotten so complex that I cannot explain, or it too late already (), which is not good, but ….

The next one seems to produce the correct results (with the 9 records):

WITH ADates AS (
    SELECT
          Id
        , SharedId
        , TheDateTime
    FROM (VALUES
        (CAST(1 AS int), CAST(1 AS int), CAST('2019-01-01 01:01:00.00' AS datetime2(7))),
        (2, 1, '2019-01-01 01:01:33.00'),
        (3, 1, '2019-01-01 01:02:00.00'),
        (4, 1, '2019-01-01 01:02:01.00'),
        (5, 1, '2019-01-01 01:02:04.00'),
        (6, 1, '2019-01-01 01:06:15.00'),
        (7, 2, '2019-01-01 01:06:00.00'),
        (9, 1, '2019-01-01 01:02:31.00'),
        (8, 2, '2019-01-01 01:06:45.00')
    ) X (Id, SharedId, TheDateTime)
),
TMPADates AS (
SELECT
    Id,
    SharedId,
    TheDateTime,
    --DATEADD(S, 30, TheDateTime ) TheDateTime30,
    ISNULL((SELECT MIN(TheDateTime)
            FROM ADates t2
            WHERE t2.TheDateTime BETWEEN DATEADD(s,-30,ADates.TheDateTime) AND ADates.TheDateTime
              and ADates.SharedId=t2.SharedId),TheDateTime) ingroup
FROM ADates
),
TMPAdates2 AS (
SELECT
    Id,
    SharedId,
    TheDateTime,
    ingroup,
    -- ISNULL(LAG(ingroup) OVER (ORDER BY SharedId,ingroup DESC),ingroup) as ingroup2
    ISNULL(LAG(ingroup) OVER (PARTITION BY SharedId ORDER BY SharedId,ingroup DESC),ingroup) as ingroup2
FROM TMPADates
)
SELECT
    SharedId,  COUNT(DISTINCT ingroup2) As Count
FROM TMPADates2
GROUP BY  SharedId


Find points within a distance using MySQL

Using GIS

Assuming MySQL 8+

SELECT u.*,
  ST_Distance(
    ST_SRID(Point(65.3234, 78.3232),4326),
    ST_SRID(Point(u.lon,u.lat),4326)
  )
FROM Users AS u
WHERE ST_Intersects(
  ST_Buffer(ST_SRID(Point(65.3234, 78.3232),4326), 5000),
  ST_SRID(Point(u.lon,u.lat),4326)
);

If you're not using MySQL 8+ you can't use ST_SRID(geom,SRID). Also, if you store GEOMETERY types on the table, rather than silly lat and long columns, this looks a lot cleaner.

SELECT u.*,
  ST_Distance(
    ST_SRID(Point(65.3234, 78.3232),4326),
    u.geom
  )
FROM Users AS u
WHERE ST_Intersects(
  ST_Buffer(ST_SRID(Point(65.3234, 78.3232),4326), 5000),
  u.geom
);


How to include categories for which there are no values in an ssrs chart

The best solution I've found to this type of issue is to create a date dimension table that shows various information for dates- in this case it could simply be date and month, but other implementations I've seen include whether or not a particular day is a holiday or a day in which employees will work.

Then, apply the date criteria to this dimension table and left join the results onto it-

CREATE TABLE date_dim (Date_eval, Month)

...

SELECT  [t.DataPoint], [dd.Month]
FROM   date_dim dd
LEFT JOIN Table t
   ON t.[When] = dd.Date_Eval
WHERE
    Date_eval >= DATEADD(MONTH, -7, @CurrentMonth)
    Date_eval < @CurrentMonth

The downside of this approach is that you have to populate a table with data and that if you use a date dimension attribute that can change, such as whether or not employees work a certain day, the table is difficult to maintain automatically.

The upside is that you have a framework to build reports like this on, and one that can be reused as often as you need it.


There are a multitude of factors that go into determining which Teradata platform and the configuration of the platform that will suite your needs. Teradata has spent untold amounts of money on intellectual property and decades of experience working with potential customers to help them properly size a configuration that not only meets the immediate needs of a customer but provides them capacity for which the environment can adequately grow and evolve.

I would strongly suggest you reach out to Teradata directly and engage them in a pre-sales capacity if your company is considering their technology to meet the needs of your data warehouse environment.

For a sandbox environment, you could may be able to get away with using the one terabyte version of Teradata Express on an adequately sized server or consider using Amazon EC2 to stand up a instance of Teradata to complete a proof of concept. It should be noted that either of these options should not be used to gauge the performance of a production environment for service level agreements but whether or not the technology will accomplish what you are trying to do.


SQL Server Error 18483 - When Setting Up SQL Distribution

The SQL Server was probably renamed in the past.  Here's a better article from Microsoft on how to change the server names:

http://msdn.microsoft.com/en-us/library/ms143799.aspx


Continuously arriving data with fast filtering on date in PostgreSQL

As @a_horse_with_no_name suggested, you could do partitioning of the tables, which seems ideal for what you are describing. Something like pg_partman would help significantly with the process:

https://github.com/keithf4/pg_partman

You could also try an unlogged table, with a job to load the table in between bursts, something like this:

http://theplateisbad.blogspot.com/2015/08/row-row-row-your-boat.html

But in the end, I think that partitioning is the best way to go here, so that you are writing to a smaller table with many other sibling tables, the table inheritance is hidden behind PostgreSQL's partitioning machinery, and entire partitions can be eliminated via constraint exclusion depending on timestamp ranges.


SQL - prevent a function from being executed in a short period of time

You can prevent a stored procedure from running more often than every "x" seconds using a table to keep track of the last run-time of the given procedure.

Create a table to store the last-run-time:

CREATE TABLE dbo.ProcControl
(
    ProcName sysname NOT NULL
        CONSTRAINT PK_ProcControl
        PRIMARY KEY
        CLUSTERED
    , LastExecution datetime NOT NULL
);
INSERT INTO dbo.ProcControl (ProcName, LastExecution)
VALUES (N'dbo.TestProc', '2017-01-01 00:00:00')
GO

Create a test stored procedure:

CREATE PROCEDURE dbo.TestProc
AS
BEGIN
    SET NOCOUNT ON;
    IF NOT EXISTS (
        SELECT 1
        FROM dbo.ProcControl pc
        WHERE pc.ProcName = N'dbo.TestProc'
            AND pc.LastExecution >= DATEADD(SECOND, -5, GETDATE())
            )
    BEGIN
        /*
            run the code you want to run at most every 5 seconds.
        */
        SELECT Result = 'This is a result'
            , [Timestamp] = GETDATE();
        UPDATE dbo.ProcControl
        SET LastExecution = GETDATE()
        WHERE ProcName = N'dbo.TestProc';
    END
END
GO

Here, we run the proc twice in quick succession; the first iteration returns a valid result, the second iteration does not.  Then we wait for 6 seconds, and run the procedure again, which in-turn returns a result :

DECLARE @returnVal int;
EXEC @returnVal = dbo.TestProc;
PRINT @returnVal;
EXEC @returnVal = dbo.TestProc;
PRINT @returnVal;
WAITFOR DELAY '00:00:06';
EXEC @returnVal = dbo.TestProc;
PRINT @returnVal;

The results:

Cleanup:

IF OBJECT_ID(N'dbo.ProcControl', N'U') IS NOT NULL
DROP TABLE dbo.ProcControl;
IF OBJECT_ID(N'dbo.TestProc', N'P') IS NOT NULL
DROP PROCEDURE dbo.TestProc;
GO

Would I suggest doing this?  Absolutely not!

What if you have two customers using the site at the same time?  The first customer will get results, the second customer may not.  Also, writing into the dbo.ProcControl table unnecessarily may reduce performance, and might become a central bottleneck.

Re-architect your website to use caching and only make calls to the database when data is needed from it.


Display user-defined types and their details

Does this get you started?

SELECT n.nspname AS schema,
        pg_catalog.format_type ( t.oid, NULL ) AS name,
        t.typname AS internal_name,
        CASE
            WHEN t.typrelid != 0
            THEN CAST ( 'tuple' AS pg_catalog.text )
            WHEN t.typlen < 0
            THEN CAST ( 'var' AS pg_catalog.text )
            ELSE CAST ( t.typlen AS pg_catalog.text )
        END AS size,
        pg_catalog.array_to_string (
            ARRAY( SELECT e.enumlabel
                    FROM pg_catalog.pg_enum e
                    WHERE e.enumtypid = t.oid
                    ORDER BY e.oid ), E'\n'
            ) AS elements,
        pg_catalog.obj_description ( t.oid, 'pg_type' ) AS description
    FROM pg_catalog.pg_type t
    LEFT JOIN pg_catalog.pg_namespace n
        ON n.oid = t.typnamespace
    WHERE ( t.typrelid = 0
            OR ( SELECT c.relkind = 'c'
                    FROM pg_catalog.pg_class c
                    WHERE c.oid = t.typrelid
                )
        )
        AND NOT EXISTS
            ( SELECT 1
                FROM pg_catalog.pg_type el
                WHERE el.oid = t.typelem
                    AND el.typarray = t.oid
            )
        AND n.nspname <> 'pg_catalog'
        AND n.nspname <> 'information_schema'
        AND pg_catalog.pg_type_is_visible ( t.oid )
    ORDER BY 1, 2;

In psql you can \set ECHO_HIDDEN on to make psql show you the queries
used to generate the output of the \d... commands. I've found these queries
to be very useful as a starting point when digging metadata out of databases.

Update: 2019-12-16

For composite types, the columns metadata can be determined using something like the following:

WITH types AS (
    SELECT n.nspname,
            pg_catalog.format_type ( t.oid, NULL ) AS obj_name,
            CASE
                WHEN t.typrelid != 0 THEN CAST ( 'tuple' AS pg_catalog.text )
                WHEN t.typlen < 0 THEN CAST ( 'var' AS pg_catalog.text )
                ELSE CAST ( t.typlen AS pg_catalog.text )
                END AS obj_type,
            coalesce ( pg_catalog.obj_description ( t.oid, 'pg_type' ), '' ) AS description
        FROM pg_catalog.pg_type t
        JOIN pg_catalog.pg_namespace n
            ON n.oid = t.typnamespace
        WHERE ( t.typrelid = 0
                OR ( SELECT c.relkind = 'c'
                        FROM pg_catalog.pg_class c
                        WHERE c.oid = t.typrelid ) )
            AND NOT EXISTS (
                    SELECT 1
                        FROM pg_catalog.pg_type el
                        WHERE el.oid = t.typelem
                        AND el.typarray = t.oid )
            AND n.nspname <> 'pg_catalog'
            AND n.nspname <> 'information_schema'
            AND n.nspname !~ '^pg_toast'
),
cols AS (
    SELECT n.nspname::text AS schema_name,
            pg_catalog.format_type ( t.oid, NULL ) AS obj_name,
            a.attname::text AS column_name,
            pg_catalog.format_type ( a.atttypid, a.atttypmod ) AS data_type,
            a.attnotnull AS is_required,
            a.attnum AS ordinal_position,
            pg_catalog.col_description ( a.attrelid, a.attnum ) AS description
        FROM pg_catalog.pg_attribute a
        JOIN pg_catalog.pg_type t
            ON a.attrelid = t.typrelid
        JOIN pg_catalog.pg_namespace n
            ON ( n.oid = t.typnamespace )
        JOIN types
            ON ( types.nspname = n.nspname
                AND types.obj_name = pg_catalog.format_type ( t.oid, NULL ) )
        WHERE a.attnum > 0
            AND NOT a.attisdropped
)
SELECT cols.schema_name,
        cols.obj_name,
        cols.column_name,
        cols.data_type,
        cols.ordinal_position,
        cols.is_required,
        coalesce ( cols.description, '' ) AS description
    FROM cols
    ORDER BY cols.schema_name,
            cols.obj_name,
            cols.ordinal_position ;


Change the system wide default for maxrecursion

If your queries have a common shape, you might be able to add the required maxrecursion hint using one or more plan guides.

There can be a knack to getting them right. If you add specific query details to your question, we might be able to work that out for you. Typically, you would trace the SQL actually hitting the server, or obtain a parameterized form using the built-in procedure sys.sp_get_query_template, and then create a TEMPLATE and/or OBJECT/SQL plan guide.

See the documentation for further information:

Plan Guides
Create a New Plan Guide
Create a Plan Guide for Parameterized Queries
Specify Query Parameterization Behavior by Using Plan Guides
Apply a Fixed Query Plan to a Plan Guide
Attach Query Hints to a Plan Guide
View Plan Guide Properties
Use SQL Server Profiler to Create and Test Plan Guides
Validate Plan Guides After Upgrade

Plan guides will need to be revalidated whenever the application code changes, and when SQL Server is patched or upgraded. This should just be part of your normal testing cycle.

Note that plan guide validation using sys.fn_validate_plan_guide may incorrectly report a failure if the guided statement references a temporary table. See this question:

Plan guide validation with fn_validate_plan_guide gives false positives

The Plan Guide Successful and Plan Guide Unsuccessful Profiler and Extended Events classes can also be used to monitor plan guide applications.

Connect was retired before the product improvement suggestion Allow MAXRECURSION limit values other than 100 for views and UDFs by Steve Kass was implemented. If you would like to take it up with Microsoft now, see the options at SQL Server help and feedback.


Query tables with 400 million records fast

Will partitioning the table by YEAR help improve performance or partitioning only helps in data management?

Yes. If your queries have date filters, Partition Elimination will allow queries to ignore some partitions.

How to convert Index Scan to Seek (majority of reports using different columns, filters and aggregation)

You don't really want that.  With a clever combination of indexes, filtered indexes, indexes with included columns, and indexed views it's possible to provide optimized query plans for many different query types.  But it's expensive, time-consuming, and and typically fails to account for 100% of the queries.  It's better to be able to support scanning, which brings us to:

Any other feature in SQL Server 2019 which can be used when queries with such big data can return records faster.

Yes.  Absolutely.  For large tables with a variety of queries that scan and aggregate large numbers of rows, you should use a Columnstore design instead of the default row-oriented Clustered Index or Heap.
To switch to columnar storage, just create a Clustered Columnstore index on the table.  It's designed for exactly this scenario, providing:

The fastest scanning speed.
The highest compression.
Queries can scan only the columns they need.
Data is stored and fetched from disk in larger chunks.
Unused columns are not fetched from disk or cached in RAM.
Can be combined with non-clustered indexes and partitioning if needed.

And once you can scan fast enough, you don't really need a lot of other indexes.


Return true if any value in an specific key inside a jsonb array exists

I think you can get it by using jsonb_array_elements function and the ->> operator.

create table test (facility int, data jsonb);

insert into test values
(1, '[{ "id" : "1", "count" : "4", "severity" : "bad" }, { "id" : "3", "count": "4", "severity" : "good"}]'),
(2, '[{ "id" : "2", "count" : "4", "severity" : "good" }, { "id" : "3", "count": "4", "severity" : "good"}]'),
(3, '[{ "id" : "3", "count" : "4", "severity" : "good" }, { "id" : "3", "count": "4", "severity" : "bad"}]');

SELECT t1.facility, obj->>'severity' severity
FROM   (SELECT facility, jsonb_array_elements(data) obj
        FROM   test) t1
WHERE  obj->>'severity' = 'bad'

facility | severity
-------: | :-------
       1 | bad
       3 | bad

dbfiddle here

In case there is more than one "severity" : "bad" for the same facility you could avoid duplicates using DISTINCT.

create table test (facility int, data jsonb);

insert into test values
(1, '[{ "id" : "1", "count" : "4", "severity" : "bad" }, { "id" : "3", "count": "4", "severity" : "good"}]'),
(2, '[{ "id" : "2", "count" : "4", "severity" : "good" }, { "id" : "3", "count": "4", "severity" : "good"}]'),
(3, '[{ "id" : "3", "count" : "4", "severity" : "bad" }, { "id" : "3", "count": "4", "severity" : "bad"}]');

SELECT distinct t1.facility, obj->>'severity' severity
FROM   (SELECT facility, jsonb_array_elements(data) obj
        FROM   test) t1
WHERE  obj->>'severity' = 'bad'
ORDER BY t1.facility

facility | severity
-------: | :-------
       1 | bad
       3 | bad

dbfiddle here


MongoDB - Clarification on choosing randomly distributed shard key

While it's hard to say for certain without full context, I'm assuming its referring to the need to keep the working set in memory.

A randomly distributed shard key would distribute the workload across an entire index, meaning that the entire index would need to be fit in memory to efficiently handle the workload. Performance would deteriorate once the size of this index on a shard grows larger than RAM, as the index on the shard key would need to be page faulting data in and out of memory.

In contrast, a non-random shard key may have a "hot" subset that handles most of the working set. For example, consider a website where only newer "posts" by users are frequently accessed and older "posts" are rarely accessed. While the indexes on "posts" may be larger than available memory, only subsets of the indexes may need to fit in memory, reducing memory pressure and the potential of page faults.


If a query reads more pages than the InnoDB buffer can hold, are all the previous pages evicted?

According to MySQL 5.7 Reference Manual - The InnoDB Buffer Pool

InnoDB manages the buffer pool as a list, using a variation of the least recently used (LRU) algorithm. When room is needed to add a new page to the pool, InnoDB evicts the least recently used page and adds the new page to the middle of the list. This “midpoint insertion strategy” treats the list as two sublists:

At the head, a sublist of “new” (or “young”) pages that were accessed recently.

At the tail, a sublist of “old” pages that were accessed less recently.

This algorithm keeps pages that are heavily used by queries in the new sublist. The old sublist contains less-used pages; these pages are candidates for eviction.

(emphasis mine)
I think this is the answer you're looking for. Not all pages are evicted, they're evicted one at at a time, and only of the old ones will be evicted each time. Obviously, if queries need twice (or more) of the available pool, eventually all pages might be evicted, depending on how frequently they're used.
I'd say that all pages near the root of B+trees (used for indices and the way tables are clustered) will be used more frequently than the rest, and so, they're unlikely to ever be evicted. However, some situations where there are just lots of full table scans, that (probably) pass through pages just once, might end up having all your hot pages overwhelmed and evicted. I don't think this is likely, but theoretically possible.


SQL Server: Does the sysadmin role encompass all the other roles?

You do not need to add any server roles to a new user, those are optional (although it is best to leave the public role checked).

The sysadmin role encompasses all other roles, and is deity level access within SQL Server, granting you the rights and permissions to do whatever you want with the instance.


PostgreSQL suitabilty of Hash Index on PK and FK

Hash indexes do not currently support unique indexes, and so not PK or FK either.  Maybe in a future version...


SQL Server columnstore metadata (pushdown) filter

The performance optimization that you are describing is known as rowgroup elimination. You can get rowgroup elimination with both queries in both SQL Server 2016 and SQL Server 2014 but SQL Server 2014 has additional restrictions.

Let's test in SQL Server 2016 first. The following query finishes very quickly:

select *
from generate_series
where c = 100
OPTION (MAXDOP 1);

SQL Server 2016 offers additional diagnostic information about rowgroup elimination through SET STATISTICS IO ON; so let's take advantage of that:

  Table 'generate_series'. Segment reads 1, segment skipped 68.

  SQL Server Execution Times: CPU time = 0 ms,  elapsed time = 9 ms.

This query is also fast:

select *
from generate_series
where c in (select c from #tmp)
OPTION (MAXDOP 8);

Statistics output:

  Table 'generate_series'. Segment reads 1, segment skipped 68.

  SQL Server Execution Times: CPU time = 0 ms,  elapsed time = 11 ms.

This query is fast as well:

select *
from generate_series
where c in (select c from #tmp)
OPTION (MAXDOP 1);

  Table 'generate_series'. Segment reads 1, segment skipped 68.

  SQL Server Execution Times: CPU time = 0 ms,  elapsed time = 5 ms.

For each query SQL Server is able to skip 68 rowgroups. It only reads data from the rowgroup that contains an ID of 100. In the plan for the final query we can see that an optimized bitmap was applied:

That bitmap allows rowgroup elimination through a join.

In SQL Server 2014 I get similar performance results for the queries except for the last one that has a join and MAXDOP of 1. That one takes 21 seconds to run. Here is the query plan:

There is no bitmap and the scan isn't running in batch mode. We send back all 67 million rows to the hash join.

In SQL Server 2014 serial plans are not eligible for batch mode. If your query is running in serial for some reason that could explain the poor performance that you experienced. Note that the query that directly filters against c = 100 also runs in row mode but it still finishes quickly.


Issue connecting to remote SQL Server database through VPN

I'm speculating that since you were given RDP access and a username/account to the database server, the security credentials in question are Active Directory credentials.

DBeaver on Linux likely cannot use AD credentials (unless the environment taps into AD for authentication on Linux, in which case this isn't your issue).  I don't know if trying to authenticate via SQL Authentication using AD Credentials will throw the error you've provided, but it may be the cause.  I don't use DBeaver, so again this is just speculation.

HeidiSQL requires that you check the box next to Use Windows authentication as shown below:

Your screenshot covers up this area of the window so I haven't an idea if you're using this option or not.  Regardless, the caveat here is that that the Windows machine you are running HeidiSQL on has to be on the domain in question, and you need to have logged into said machine using the RDP credentials provided to you.  Basically, if you're using a Windows machine from DomainA, VPN into DomainB, and then run HeidiSQL and check this box, you're still going to get an error authenticating because it's using the authentication token created by the account used to log into the machine on DomainA and not the account needed to log into DomainB.

Again, the errors you're showing don't necessarily mean this is your issue, but it could be one reason why you're not able to connect remotely.

To start troubleshooting differently, I suggest you RDP to the server with a client that supports NLA/TLS connections (e.g. authentication occurs before an RDP session is opened) and see if you can log into SQL Server locally on the server.  If you can log in locally, start checking other potential issues for remote connection issues and go from there.

If you provide more context in your question, I'll be happy to modify my answer accordingly as well.


How to adjust a hierarchical query in Oracle so then the parent and the id can come in sequence?

Create sample data:

create table t1
(
  config_id number,
  config_info_id number,
  parent number
);

insert into t1 values (1, 1, null);
insert into t1 values (16, 3983, null);
insert into t1 values (16, 3984, null);
insert into t1 values (16, 3985, null);
insert into t1 values (16, 3986, 3985);
insert into t1 values (16, 3987, 3985);
insert into t1 values (16, 3988, null);
insert into t1 values (16, 3989, 3988);
insert into t1 values (16, 3990, 3988);
commit;

Generate new id values for CONFIG_INFO_ID where config_id = 16:

with new_ids as
(
  select rownum as new_id, config_info_id from
  (
    select config_info_id from t1
    where config_id = 16
    order by config_info_id
  )
)
select * from new_ids
;

    NEW_ID  CONFIG_INFO_ID
---------- ---------------
         1            3983
         2            3984
         3            3985
         4            3986
         5            3987
         6            3988
         7            3989
         8            3990

Replace the original CONFIG_INFO_ID and parent values with the generated values:

with new_ids as
(
  select rownum as new_id, config_info_id from
  (
    select config_info_id from t1
    where config_id = 16
    order by config_info_id
  )
)
select
  ci.CONFIG_ID,
  n1.new_id as cfg_info_id,
  n2.new_id  AS CFG_PARENT,
  level AS CFG_LEVEL
from
  t1 ci
  join new_ids n1 on (ci.config_info_id = n1.config_info_id)
  left join new_ids n2 on (ci.parent = n2.config_info_id)
where config_id = 16
start with ci.parent is null
connect by prior ci.config_info_id = ci.parent
;

 CONFIG_ID  CFG_INFO_ID CFG_PARENT  CFG_LEVEL
---------- ------------ ---------- ----------
        16            1     (null)          1
        16            2     (null)          1
        16            3     (null)          1
        16            4          3          2
        16            5          3          2
        16            6     (null)          1
        16            7          6          2
        16            8          6          2


Why does SQL Server truncate my nvarchar(max) string in an expression?

It is truncating because the replacement_value parameter is being converted to the same data type as the check_expression.

From https://msdn.microsoft.com/en-us/library/ms184325.aspx

ISNULL ( check_expression , replacement_value )

  replacement_value must be of a type that is implicitly convertible to
  the type of check_expresssion.

  Return Types Returns the same type as check_expression. If a literal
  NULL is provided as check_expression, returns the datatype of the
  replacement_value. If a literal NULL is provided as check_expression
  and no replacement_value is provided, returns an int.


What interval does SQL Server Profiler's "duration" refer to?

Duration from SQL Server 2005 traces is stored in microseconds (one-millionth of a second) while Duration from SQL Server 2000 is stored in milliseconds (one-thousandth of a second). CPU is always stored in milliseconds.

The typical cause for long durations with no work is blocking: someone else has a lock on a row.

To find out why queries are waiting, skip Profiler and head to Adam Machanic's sp_WhoIsActive. It shows you what queries are running right now, and the wait_info column tells you what they're waiting on (and for how long). If you see LCK*, then it's blocking.


MySQL, RAM disk for tmpdir - how to figure out what size it should be?

This is a really interesting question.

You could check the

SHOW GLOBAL STATUS like 'Created_tmp_disk_tables';

But that only gives you the overall number of tables created (or the delta, if you subtract that value between two points of time), but not the actual size needed.

You could go the "logical" wayin MySQL 5.6, configuring performance_schema in order to log a certain amount of queries, enough to check how many temporary tables are being created. You can find those on

performance_schema.events_statements_history/events_statements_history_long

On the CREATED_TMP_DISK_TABLES and CREATED_TMP_TABLES. That will give you more information about the type of temporary tables created, but still no information on the actual number or rows written or the size at a moment in time.

I would probably check it the physical way (assuming a UNIX-like OS), by something like this:

sudo lsof -s | awk '{if ($1 == "mysqld" && $9 ~ /\/tmp/) size+=$7} END {print size}'

Checking the total size of files that the mysqld process has open on /tmp along time may give you a better indication, assuming you monitor that at your peak time, or a significant amount of time.


Implenting Min Max memory configuration in Alwayson

The current setting is set to the default max value and that is something not which I want.

You should change your max memory setting away from the default. Its a dynamic setting that does not require a restart of sql server service. Bear in mind that running reconfigure it will flush out the plan cache.

Refer to my answer here for more details.

  Each of the server has 24gb of memory. The total size of the databases on each node is 105gb

Start with 21GB as max memory provided you are running only SQL Server (no SSIS, SSRS, SSAS,etc). Leave 3 GB for OS.

Depending on the database activity, the RAM (24GB) seems to be low. You should look out for low memory notifications to see if server is having memory pressure.


'utf-8' codec can't decode byte ERROR when querying data

I fixed the problem adding character_set 'UTF-8' when creating foreign server.
Example:

CREATE SERVER myserver
FOREIGN DATA WRAPPER tds_fdw
OPTIONS (servername 'localhost', port '1501',msg_handler 'notice', database 'mydb', character_set 'UTF-8');


How can I Insert when not in table and when max timestamp T-SQL

I used a CTE with just one column that is the maxtime from tb2. Then join on the CTE and reference it when checking for if its the max or not.

WITH aggregateTime (maxTime) AS (
SELECT MAX(STime) AS maxTime
FROM tb2
)
INSERT INTO db.dbo.tb1
SELECT *
FROM tb2
INNER JOIN aggregateTime ON 1=1
WHERE tb2.STime = aggregateTime.maxTime AND EMPNO NOT IN (SELECT EMPNO FROM tb1);


this runs in MySQL as in Oracle 18c
So you must have somewhere a typo in your code

CREATE TABLE Department (
    Name varchar(255) NOT NULL PRIMARY KEY
);

✓

CREATE TABLE Person (
    SSN CHAR(9) NOT NULL PRIMARY KEY,
    LastName varchar(255),
    FirstName varchar(255)
);

✓

CREATE TABLE Professor (
    SSN CHAR(9),
    PRIMARY KEY(SSN),
    FOREIGN KEY (SSN) REFERENCES Person(SSN)
);

✓

CREATE TABLE WorksFOR (
    SSN CHAR(9),
    Salary float NOT NULL,
    DepName varchar(255),
    CONSTRAINT WorksFor_Salary CHECK (Salary>=0),
    CONSTRAINT WorksFor_pk PRIMARY KEY(SSN,DepName),
    CONSTRAINT WorksFor_ssn_fk FOREIGN KEY(SSN) REFERENCES Professor(SSN),
    CONSTRAINT WorksFor_dep_fk FOREIGN KEY(DepName) REFERENCES Department(Name)
);

✓

CREATE TABLE Course (
    CourseDepartment varchar(255) NOT NULL,
    CourseNumber varchar(8) NOT NULL,
    CONSTRAINT Course_pk PRIMARY KEY(CourseDepartment,CourseNumber),
    FOREIGN KEY(CourseDepartment) REFERENCES Department(Name)
);

✓

CREATE TABLE CanTeach (
    SSN CHAR(9) NOT NULL,
    Department varchar(255) NOT NULL,
    CourseNum varchar(8) NOT NULL,
    CONSTRAINT CanTeach_pk PRIMARY KEY(SSN,Department,CourseNum),
    FOREIGN KEY(SSN,Department) REFERENCES WorksFOR(SSN,DepName),
    FOREIGN KEY(Department,CourseNum) REFERENCES Course(CourseDepartment,CourseNumber)
);

✓

db<>fiddle here


If you are only accessing your database, it is highly unlikely that deadlocks you are experiencing are caused by other customers that happen to have their database on the same server - they'd have to be intentionally crossing database boundaries with transactions, and they'd most certainly not have the ability to do that. However we need a lot more information about what kind of deadlocks you are seeing, as they could involve system objects, etc. Are the objects involved only in your database? Have you looked at the deadlock graphs at all, or are you just responding to "deadlock victim" messages? Lots of info in this StackOverflow question about interpreting deadlocks, but you need to capture the graph using Profiler or some kind of monitoring tool (for which you may need to ask the host's assistance).


Should I just rely on Foreign Key Constraints for error handling?

Yes, this is a waste of time at least when your stored procedure is just the INSERT statement that may cause the foreign key violation. Where a foreign key is in place, validation of any attempted FK value is made against the reference table by the database engine itself.

If the value does not exist in the reference table, you will both receive an exception and have your INSERT statement aborted.

So, this would be perfectly enough:

CREATE PROC spCreateChildFoo
    @ForeignKey_Id int,
    @Attribute varchar(50)
AS

INSERT INTO ChildTable (ForeignKey_Id, Attribute)
VALUES (@ForeignKey_Id, @Attribute);

On the other hand, if your procedure is more than a single INSERT statement and you would like to abort the entire procedure on an invalid insert at its beginning, then preliminarily checking the FK value may not be entirely pointless. However, valid FK values would still be causing double lookups, and that is neither good nor necessary. To achieve the same effect of aborting the entire procedure, you could just put your procedure's body into a TRY/CATCH block:

CREATE PROC spCreateChildFoo
    @ForeignKey_Id int,
    @Attribute varchar(50)
AS

BEGIN TRY
    INSERT INTO ChildTable (ForeignKey_Id, Attribute)
    VALUES (@ForeignKey_Id, @Attribute);

    ... -- other statements
END TRY
BEGIN CATCH
    RAISERROR ('The input ForeignKey_Id does not exist', 16, 1);
    -- or you could catch ERROR_NUMBER(), ERROR_MESSAGE() etc.
    -- and re-raise the original exception
END CATCH;

More info:

TRY...CATCH (Transact-SQL)
ERROR_LINE (Transact-SQL)
ERROR_NUMBER (Transact-SQL)
ERROR_MESSAGE (Transact-SQL)
ERROR_PROCEDURE (Transact-SQL)
ERROR_SEVERITY (Transact-SQL)
ERROR_STATE (Transact-SQL)


I think you should give a try for TimescaleDB. It is a Postgres extension for huge tables. Splitting table by key in 1000 sections gives you tens of millions records in each section. It will work fast without indexes at all.


How does PostgreSQL handle Checkpoints in the middle of a WAL-enabled backup?

You asked:

how postgreSQL will handle the recovery with a pg_data content containing some files which are inconsistent.

pg_start_backup() ensure the data file is at least as new as the checkpoint. On recovery, the logs are applied.
If the data is old, the log will update it..
If the data is new, the log will have same content. There is no hurt writing it again.
The data are never newer then the log, because the logs are write ahead (WAL).

You asked:

... xfs-freeze ...

xfs-freeze is alike to pg_start_backup(), it don't take a snapshot. You need a volume manager to do that.

You asked:

... why do create tablespace & create database statements are unsupported if the WAL can replay everything?

It is supported, just some little gotcha.  See http://www.postgresql.org/docs/8.1/static/backup-online.html :

23.3.5. Caveats
CREATE TABLESPACE commands are WAL-logged with the literal absolute
path, and will therefore be replayed as tablespace creations with the
same absolute path. This might be undesirable if the log is being
replayed on a different machine. It can be dangerous even if the log
is being replayed on the same machine, but into a new data directory:
the replay will still overwrite the contents of the original
tablespace. To avoid potential gotchas of this sort, the best practice
is to take a new base backup after creating or dropping tablespaces.


MySQL #1005 - Can't create table 'startet' (errno: 150)

You need a key on passering.starttid in order to link it as a foreign key.

Like this:

   CREATE TABLE passering (
     avstand INT NOT NULL,
     starttid INT NOT NULL,
     slutttid INT NOT NULL,
     passeringspunkt INT NOT NULL,
     høydeforskjell VARCHAR(25) NULL,
     bilde VARCHAR(25) NOT NULL,
     mellomtid VARCHAR(25) NOT NULL,
     PRIMARY KEY (slutttid, starttid),
     KEY (starttid)
     );


Is it necessary for rownum = 1 with Query.createQuery().getSingleResult()?

The only difference between the two queries is that the 2nd will always return 1 row. Even if the table is empty or just has 0 rows with reference=X, the result will be 1 row with null in the min(date) column.

The first query will return no row in this case. The and rownum=1 is useless and does not affect anything for the specific query.

Test at SQLfiddle.


Change Data Capture in MySQL/PHP by writing to a MySQL table (rather than streaming to Kafka/Rabbit/etc)?

The most obvious solution to this would perhaps be to use triggers. You can have triggers that fire before/after inserts, deletes and updates on the table(s) in question, and then log the changes in your special table, including a time stamp and what type of write operation it was.

Another approach could be to use an audit plugin - MariaDB's audit plugin works with older versions of MySQL at least up to 5.5. MySQL has Enterprise Audit, which presumably has a proprietary licence. These plugins will log to a file, but you can set up a cron job or similar to load that into a database table.

(With MariaDB 10.3+ you would be able to use system-versioned tables which would allow you to store all changes to rows.)


Increasing max server memory to deal with System.OutOfMemoryException on SQL Server

The System.OutOfMemoryException error that you are seeing means there was a problem with SQL Server Management Studio, not with the amount of memory on the server itself. One easy way to trigger this error is to simply run a SELECT query on a large table. If the table is too large then the results grid will take up too much memory and you'll see the error.

It's not clear to me why you would experience this error when importing data, but raising max server memory is not the right fix. I recommend trying BULK INSERT after setting the BATCHSIZE, KILOBYTES_PER_BATCH, or ROW_PER_BATCH arguments. That will limit the amount of data sent in a single transaction. You could also try importing the data through bcp. You don't even need SSMS open for that.


Should I be alarmed by this NO JOIN PREDICATE warning?

Because we know that l.id = '732820' and ls.id = l.id then SQL Server derives that ls.id = '732820'

i.e.

FROM   db2.dbo.VIEW ls
       JOIN db1.dbo.table l
         ON ls.id = l.id
WHERE  l.id = '732820'

is the same as

  ( /*...*/ FROM   db2.dbo.VIEW ls WHERE id = '732820'  )
   CROSS JOIN
  ( /*...*/  FROM   db1.dbo.table l WHERE id = '732820'  )

This rewrite is not bad for performance .

  This derivation is a good thing.  It allows SQL Server to filter out
  rows ... earlier than would otherwise be possible.


how can I ensure database connectivity on a spotty network? (and allow for entirely offline connectivity)

You have come across the first fallacy of distributed computing: the network is reliable.  I think no matter how much time and money your company invests in improving the network, there will always be outages.

I recommend designing the application/system such that it does not rely on the network to be up to function correctly.  What I think you are asking for is a way for the logging to eventually make it to the centralized SQL Server database.  Your proposed "store and forward" mechanism is a great way to accomplish this.

I would look at using a message queuing system such as MSMQ that supports local storage (no network dependency on a central bus/broker).  Frameworks such as NServiceBus can assist with this and abstract much of the low layer implementation details.  This will help avoid writing the replication, error handling, etc. logic yourself.

From the client app's perspective, instead of writing the log data directly to the remote or local database, it would simply send a WriteLogToDatabaseCommand message.  The messaging system running on the local system would persist this in its own storage and then try to forward it to the centralized logging server.  It keeps the local copy until it successfully forwards it to the receiver.  There would be a receiver process running on the logging server that handles the message by writing the log data to the centralized SQL database.

The same approach can be used for more than just "fire and forget" type of logging information.  However, for application data, you would have to design the whole system to work in an asynchronous fashion.  In that case, you may also want to research command/query responsibility segregation (CQRS).  Since the central state is not known by the remote clients, they must be able to handle that.  The updates (Commands) must always succeed, even if that means being more flexible than traditional thinking (e.g. an order is placed but the item is out of stock - still accept the order and say it's on backorder vs. stopping the order from being placed in the first place).

For more information on this approach, I recommend reading:

https://particular.net/blog/the-network-is-reliable
https://www.enterpriseintegrationpatterns.com/patterns/messaging/Introduction.html


Oracle program "lsnrctl" is missing after the installation of 32bit Oracle client

There doesn't appear to be a problem.

The listener is a process that generally runs on the database server, not on the client machine.  Doing a client-only install will not install a listener.  If you want to have a listener on your machine, you'd need to do a database install (though you can do a software-only install rather than actually creating a database).

If you're just trying to connect to a remote database, however, you'd use the listener that is (presumably) running on the database server.  It's theoretically possible to install a listener on one machine for a database running on another machine but that isn't particularly common.  It would be extremely, extremely unusual to install a listener on the client machine that listens for connections to a database on a remote server.


Use SSIS to get measures from a tabular model

You cannot get the value of a measure without an evaluation context. You can however execute a DAX query to fetch the data that you need. Once you have defined your connection manager, instead of choosing Table or View as your data access mode, choose SQL Command. You can then specify the DAX Query which will evaluate the measure within your defined context. After you have defined your query you can click on the Preview button to see the data that will be returned.

You can find a step by step guide on how to do this here.


InnoDB tables cannot be copied the same way that MyISAM tables can.

Just copying the .frm and .ibd files from one location to another is asking for trouble. Copying the .frm and .ibd file of an InnoDB table is only good if and only if you can guarantee that the tablespace id of the .ibd file matches exactly with the tablespace id entry in the metdata of the ibdata1 file.

I wrote two posts in DBA StackExchange about this tablespace id concept

Table compression in InnoDB? (under the heading 'Restoring Databases')
How to Recover an InnoDB table whose files were moved around

Here is excellent link on how to reattach any .ibd file to ibdata1 in the event of mismatched tablespace ids : http://www.chriscalender.com/?tag=innodb-error-tablespace-id-in-file. After reading this, you should come to the immediate realization that copying .ibd files is just plain crazy.

You could apply the suggestions from the Chris Calendar link, or you could go back to the old installation of mysql, startup up mysql, and then mysqldump the ddms database. Then, import that mysqldump into your new mysql instance. Trust me, this would be far easier.


Database mirroring - link failure between principal and witness

The problem is that the principal became disconnected from the witness server before it went offline. In this case, the witness cannot agree that the principal is offline because it was disconnected while the principal was still connected. As far as the witness knows, the principal may still be up.

In this scenario, it would require that you force service on the mirror to bring it online using ALTER DATABASE ... SET PARTNER FORCE_SERVICE_ALLOW_DATA_LOSS.


A simple list of name/address pairs with an index on each would be sufficient if absolutely all you needed to do was lookup one from the other. Unfortunately you will find that the DNS protocol has larger requirements than that so if you intending to interact with the public domain name system you have much more work ahead of you. See http://en.wikipedia.org/wiki/Domain_Name_System for a quick primer and a list of the relevant RFCs (which are essentially the specification you'd be expected to work to as a participant in the public DNS infrastructure (though remember that many features are optional: you may chose to implement an authoritative service only so not need to worry about recursive queries (except knowing the right error response to give when you receive one).

There are a couple of F/OSS DNS servers that use a relational database back-end instead of bind-style zone files, perhaps you could look at their structures as a hint (though if you are building a commercial solution be careful of licensing issues: for instance you might find looking at GPL covered code to be a bad idea if you intend to distribute the results of your project in a non-GPL compatible manner):
MyDNS: http://mydns.bboy.net/
Power DNS: http://en.wikipedia.org/wiki/PowerDNS


querying a table of events for reporting

I'm not sure how you're going to be reporting this data back to said farmer, here's a quick way of doing what you're requesting:

declare @test table (
    sd datetime,
    id int,
    st nvarchar(100)
);

insert @test (sd,id,st) values ('05/25/2012',1,'Shipment to Customer');
insert @test (sd,id,st) values ('05/26/2012',1,'Shipment to Customer');
insert @test (sd,id,st) values ('05/30/2012',1,'Shipment to Customer');
insert @test (sd,id,st) values ('05/26/2012',2,'Shipment to Customer');
insert @test (sd,id,st) values ('05/30/2012',3,'Shipment to Customer');
insert @test (sd,id,st) values ('05/14/2012',2,'Shipment to Customer');
insert @test (sd,id,st) values ('05/30/2012',5,'Shipment to Customer');

select
   id
   , count(*) ShippedById
from @test
where sd >= '05/25/2012' and sd <= '05/28/2012'
group by id
compute sum(count(*));

(with emphasis on the select statement)

You will get the number of times a peachid shipped, by id and then you'll get a second set that gives you the total number of peaches shipped.

If you're going to be using SSRS, you can take out the compute clause and then use SSRS to sum the results of ShippedById. Such a solution would also work with Excel.


There is an equivalent of login.sql on PostgresSQL?

Yes, there is. It's stored in ~/.psqlrc

More details in the manual: http://www.postgresql.org/docs/current/static/app-psql.html#AEN85369


PostgreSQL query with max and min date plus associated id per row

You can calculate this in one step with window functions:

CREATE OR REPLACE VIEW daily_trans AS
SELECT DISTINCT
       trans_date
     , first_value(trans_time) OVER w AS first_time
     , first_value(id)         OVER w AS first_id
     , last_value(trans_time)  OVER w AS last_time
     , last_value(id)          OVER w AS last_id
     , calculate_status(min(trans_time) OVER w
                      , max(trans_time) OVER w) AS status
FROM   trans
WINDOW w AS (PARTITION BY trans_date ORDER BY trans_time, id
             ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)
ORDER  BY 1;

I added id as secondary ORDER column in the window clause, to make the sort order stable in case of identical times per day.

If you are not familiar with window functions, be sure to read this chapter of the manual.

ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING is a not so commonly used clause to adjust the window frame, because we want last_value() to always return the last value of the whole partition (the date), not the last value up to the current (including peers) according to the sort order.

How you to combine DISTINCT with window functions:

PostgreSQL: running count of rows for a query 'by minute'


Change value lower_case_table_names on a running docker container

2 options;

Build the container and ADD or COPY a my.cnf file into place with your desired options.
Bind mount a config file into the container as part of the run command

I think there’s possibly a 3rd but I’m not in the position to test but you can possibly add startup options to the docker run command e.g.

docker run -d {other necessary options} mysql/mysql-server:latest —lower-case-table-names


Unfortunately using application pool identities between two different servers is not possible. App Pool identities are located on their host machines only - SQL server will be unaware of the application pool accounts - so you cannot add them explicitly to logon to the SQL server. They do however run as the Local Machine account over the network - in a domain environment you could give the application server's machine account permissions to the SQL server.

If you have a domain they could be joined to you could run the application pools under a domain identity; granting the domain account access to the SQL server.

This is my preferred method for a dev environment: Enable the "Local Account Token Filter Policy" using group policy. Cross create your accounts on both servers with same username/passwords. Run the application pools under the created accounts; you can grant the same account access on the SQL server; since the account exists on both servers you shouldn't have any issues.


Cluster instances use registry keys checkpointing to ensure that all nodes in the cluster share the same configuration. Checkpointing works while the resource (the SQL Server Service in this case) is online. When the resource is offline, checkpointing doesn't happen.

In the context of your issue, just change the port from the configuration manager on the active node while the service is running.

As an alternative, you can edit the contents of the checkpoint file directly, as described in the section "Method 1" in this page.

As far as removing/adding checkpoints with PowerShell is concerned, the syntax is straightforward:

Get-ClusterCheckpoint #lists all checkpoints

#remove checkpoint
Remove-ClusterCheckpoint -ResourceName "SQL Network Name (InstanceName)" -RegistryCheckpoint "SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL11.InstanceId\MSSQLServer"

#add checkpoint
Add-ClusterCheckpoint -ResourceName "SQL Network Name (InstanceName)" -RegistryCheckpoint "SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL11.InstanceId\MSSQLServer"


Script for migrating server role permissions - SQL Server

In this case the issue was not with the join but with the text concatenation.

The following should work

SELECT ' GRANT ' + SPerm.permission_name COLLATE Latin1_General_CI_AS + ' TO '  + SP.name -- + ' '' ' AS Commands_To_Execute
FROM sys.server_principals SP
  JOIN sys.server_permissions SPerm
    ON SP.principal_id = SPerm.grantee_principal_id
    where sp.type='R' and sp.name!='public'

As an aside, I would strongly recommend looking at the Copy-DBAServerRole Powershell cmdlet in DBATools which is a Powershell module - it does the heavy lifting for you in jobs like these.


I was able to get all the properties scripted out.

Open SSMS
Right click on the job
Click properties
Use script button on the top

I created a test job with many features.

This is what I get when I scripted out.

USE [msdb]
GO
EXEC msdb.dbo.sp_update_alert @name=N'Blocked Tasks > 5',
        @message_id=0,
        @severity=0,
        @enabled=1,
        @delay_between_responses=183,
        @include_event_description_in=3,
        @database_name=N'',
        @notification_message=N'This is a test',
        @event_description_keyword=N'',
        @performance_condition=N'Workload Group Stats|Blocked tasks|default|>|5',
        @wmi_namespace=N'',
        @wmi_query=N'',
        @job_id=N'b97f9d4d-a22d-4745-9d19-9fadd168b4b1'
GO
EXEC msdb.dbo.sp_update_notification
      @alert_name=N'Blocked Tasks > 5',
      @operator_name=N'sysadmin',
      @notification_method = 1
GO

I ran an extended event trace to see what get called. I changed the select statement to select * to see all the values.

EXEC Sp_executesql
N'  create table #tmp_sp_help_alert  (id int null, name nvarchar(128) null,  event_source nvarchar(100) null, event_category_id int null, event_id int null, message_id int null,  severity int null, enabled tinyint null, delay_between_responses int null, last_occurrence_date int null, last_occurrence_time int null, last_response_date int null, last_response_time int null, notification_message nvarchar(512) null, include_event_description tinyint null, database_name nvarchar(128) null, event_description_keyword nvarchar(100) null, occurrence_count int null, count_reset_date int null, count_reset_time int null, job_id uniqueidentifier null, job_name nvarchar(128) null, has_notification int null, flags int null, performance_condition nvarchar(512) null, category_name nvarchar(128) null,  wmi_namespace nvarchar(max) null,  wmi_query nvarchar(max) null, type int null)   insert into #tmp_sp_help_alert exec msdb.dbo.sp_help_alert

SELECT * FROM #tmp_sp_help_alert AS tsha WHERE (tsha.name=@_msparam_0)   drop
  table #tmp_sp_help_alert',
    N'@_msparam_0 nvarchar(4000)',
    @_msparam_0=N'Blocked Tasks > 5'


Encryption is a huge topic. There are lots of caveats, and most of those are about business requirements, not about technology.

Start by taking a step back and look at the big picture. Pop the why? stack until the business requirements are clear. Then start formulating requirements about what data needs to be protected and what kind of attacks are expected. Only after then start exploring what encryption would be suitable and what wouldn't.

Who or what would attack the database? Rogue admin? Oops, data-at-rest encryption doesn't help much. Network sniffer? Hope you are using TLS between application and SQL Server. SQL injection? Something else?

What would happen in case of data breach? Vandalism? Lawsuits? Loss of reputation? Heist of Bitcoins? Authoritarian government hangs dissidents?

Some domains, like health care and banking, often have strict laws about data protection. US, EU and rest of the world have their own quirks.

As practical issues, key management is among the most complex one. Do you have a key management process? Without one, managing certificates and encryption keys is going to be painful. Restoring old backups and server migrations are suddenly extra complex, as backup itself isn't enough.

If working on Enterprise Edition or Developer Edition, TDE is relatively simple to set up.

Column encryption is suitable for hiding a subset of data. An example about securing national id numbers.

Always Encrypted moves the crypto to application layer. Good solution for some cases, abysmal for the rest.

Dynamic data masking isn't actually encryption, but suitable for many cases in which confidential data must be partly exposed.


Can I open a SQL Server 2008 R2 database in SQL Server 2008 R2 Express?

Not if you used any persisted Enterprise features. If you used any of such there will be an entry in sys.dm_db_persisted_sku_features and when trying to open/attach/restore this database on any edition lower than Enterprise you will gen an exception and the database won't open. The list possible of persisted SKU features are:

  Compression. Indicates that at least one table or index uses data
  compression or the vardecimal storage format. To enable a database to
  be moved to an edition of SQL Server other than Enterprise or
  Developer, use the ALTER TABLE or ALTER INDEX statement to remove data
  compression. To remove vardecimal storage format, use the
  sp_tableoption statement.

  Partitioning. Indicates that the database
  contains partitioned tables, partitioned indexes, partition schemes,
  or partition functions. To enable a database to be moved to an edition
  of SQL Server other than Enterprise or Developer, it is insufficient
  to modify the table to be on a single partition. You must remove the
  partitioned table. If the table contains data, use SWITCH PARTITION to
  convert each partition into a nonpartitioned table. Then delete the
  partitioned table, the partition scheme, and the partition function.

  TransparentDataEncryption. Indicates that a database is encrypted by
  using transparent data encryption. To remove transparent data
  encryption, use the ALTER DATABASE statement. For more information,
  see Transparent Data Encryption (TDE).

  ChangeCapture. Indicates that a
  database has change data capture enabled. To remove change data
  capture, use the sys.sp_cdc_disable_db stored procedure.

In addition, for Express edition specifically, the database must be smaller than the maximum database size supported by Express Editions (10Gb). There could be complications if you used any of the service pack specific options like sp_db_increased_partitions but I doubt that's the case.

If your database is clean of all of the above then Express will open it w/o problems. If the source is a Standard Edition then for sure it won't have any of the persisted Enterprise features and the only issue is size.

You need to make sure the SQL Server is the same major release (SQL Server 2008R2, not SQL Server 2008). The patching level (service pack and cumulative updates applied) does not matter as the database format cannot change between minor releases, but it would be recommended to have the Express patched to the latest SP and have the latest CU applied.


You could try using MySQL's mysqldump utility.

There is an option to make mysqldump export in XML

[redwards@lw-lts-155 ~]$ mysqldump --help | grep xml
  -X, --xml           Dump a database as well formed XML.

In your case, you could dump the table mydb.mytable like this:

mysqldump --xml mydb mytable > mytable.xml

EXAMPLE

For the given table

mysql> show create table groupby\G
*************************** 1. row ***************************
       Table: groupby
Create Table: CREATE TABLE `groupby` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `num` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=17 DEFAULT CHARSET=latin1
1 row in set (0.01 sec)

mysql> select * from groupby;
+----+------------+
| id | num        |
+----+------------+
|  1 |  360370418 |
|  2 |  429187192 |
|  3 | 1064824747 |
|  4 | 1328623077 |
|  5 |  740702064 |
|  6 | 1071610692 |
|  7 |  428008187 |
|  8 |  279178462 |
|  9 |  360370418 |
| 10 |  429187192 |
| 11 | 1064824747 |
| 12 | 1328623077 |
| 13 |  740702064 |
| 14 | 1071610692 |
| 15 |  428008187 |
| 16 |  279178462 |
+----+------------+
16 rows in set (0.00 sec)

mysql>

just mysqldump to a textfile.

Here is what the XML actually looks like

<?xml version="1.0"?>
<mysqldump xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
<database name="eggyal">
        <table_structure name="groupby">
                <field Field="id" Type="int(11)" Null="NO" Key="PRI" Extra="auto_increment" Comment="" />
                <field Field="num" Type="int(11)" Null="YES" Key="" Extra="" Comment="" />
                <key Table="groupby" Non_unique="0" Key_name="PRIMARY" Seq_in_index="1" Column_name="id" Collation="A" Cardinality="16" Null="" Index_type="BTREE" Comment="" Index_comment="" />
                <options Name="groupby" Engine="MyISAM" Version="10" Row_format="Fixed" Rows="16" Avg_row_length="9" Data_length="144" Max_data_length="2533274790395903" Index_length="2048" Data_free="0" Auto_increment="17" Create_time="2012-11-26 22:39:22" Update_time="2012-11-26 22:39:22" Collation="latin1_swedish_ci" Create_options="" Comment="" />
        </table_structure>
        <table_data name="groupby">
        <row>
                <field name="id">1</field>
                <field name="num">360370418</field>
        </row>
        <row>
                <field name="id">2</field>
                <field name="num">429187192</field>
        </row>
        <row>
                <field name="id">3</field>
                <field name="num">1064824747</field>
        </row>
        <row>
                <field name="id">4</field>
                <field name="num">1328623077</field>
        </row>
        <row>
                <field name="id">5</field>
                <field name="num">740702064</field>
        </row>
        <row>
                <field name="id">6</field>
                <field name="num">1071610692</field>
        </row>
        <row>
                <field name="id">7</field>
                <field name="num">428008187</field>
        </row>
        <row>
                <field name="id">8</field>
                <field name="num">279178462</field>
        </row>
        <row>
                <field name="id">9</field>
                <field name="num">360370418</field>
        </row>
        <row>
                <field name="id">10</field>
                <field name="num">429187192</field>
        </row>
        <row>
                <field name="id">11</field>
                <field name="num">1064824747</field>
        </row>
        <row>
                <field name="id">12</field>
                <field name="num">1328623077</field>
        </row>
        <row>
                <field name="id">13</field>
                <field name="num">740702064</field>
        </row>
        <row>
                <field name="id">14</field>
                <field name="num">1071610692</field>
        </row>
        <row>
                <field name="id">15</field>
                <field name="num">428008187</field>
        </row>
        <row>
                <field name="id">16</field>
                <field name="num">279178462</field>
        </row>
        </table_data>
</database>
</mysqldump>

As for the DTD itself, you should look around StackOverflow and ServerFault

You could also XML dump just the structure using --no-data option

mysqldump --xml --no-data mydb mytable > mytable.xml

which produces

<?xml version="1.0"?>
<mysqldump xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
<database name="eggyal">
        <table_structure name="groupby">
                <field Field="id" Type="int(11)" Null="NO" Key="PRI" Extra="auto_increment" Comment="" />
                <field Field="num" Type="int(11)" Null="YES" Key="" Extra="" Comment="" />
                <key Table="groupby" Non_unique="0" Key_name="PRIMARY" Seq_in_index="1" Column_name="id" Collation="A" Cardinality="16" Null="" Index_type="BTREE" Comment="" Index_comment="" />
                <options Name="groupby" Engine="MyISAM" Version="10" Row_format="Fixed" Rows="16" Avg_row_length="9" Data_length="144" Max_data_length="2533274790395903" Index_length="2048" Data_free="0" Auto_increment="17" Create_time="2012-11-26 22:39:22" Update_time="2012-11-26 22:39:22" Collation="latin1_swedish_ci" Create_options="" Comment="" />
        </table_structure>
</database>
</mysqldump>

Take that output and scult it into a DTD


How to display data from table 1 separately, and combined with matching rows from table 2

Your question is a little confusing (in particular around the joins), does this look something like what you are looking for?

CREATE TABLE T1 (i1 INT, x CHAR(1));
CREATE TABLE T2 (i2 INT, c1 INT, c2 CHAR(1));

INSERT INTO T1 (i1, x) VALUES
(1,'a'),
(2,'b'),
(3,'c'),
(4,'d');

INSERT INTO T2 (i2, c1, c2) VALUES
(1,1,''),
(2,2,'g'),
(3,3,'h'),
(4,3,'i');
GO

CREATE VIEW TestView
AS
WITH cteInitialSet AS (
    SELECT i1, NULL AS i2, x, NULL AS c1, NULL AS c2 FROM dbo.T1
    UNION ALL
    SELECT T1.i1, T2.i2, T1.x, T2.c1, T2.c2
    FROM dbo.T1
        JOIN dbo.T2 ON T1.i1 = T2.c1
        )

SELECT ROW_NUMBER() OVER (ORDER BY i1, i2) AS i,
    i1 ,
    i2 ,
    x ,
    c1 ,
    c2
FROM cteInitialSet
GO

SELECT * FROM dbo.TestView;


How to difference a column name on the select statement which derive from diferent ancestor tables?

Something along these lines. I need to link to the same table more than once so I need to give unambiguous table aliases. I favor minimal character aliases but you'll be needing to reference the doctor person, DP, or the patient person, PP.
SELECT
    DP.Name AS DOCTORNAME
,   PP.Name AS PATIENTNAME
FROM
    Register AS R
    INNER JOIN
        Doctor AS D
        ON R.Doctor_idDoctor = D.idDoctor
    INNER JOIN
        Person AS DP
        ON D.Person_idPerson = DP.idPerson
    INNER JOIN
        Patient AS P
        ON R.Patient_idPatient = P.idPatient
    INNER JOIN
        Person AS PP
        ON P.Person_idPerson = PP.idPerson;


Oracle hierarchical query question (start with ... connect by ... )

There would have to be data indicating that the row is both a child of row A and a child of row B.  This can be done, but does not seem like it would be typical.  Think of an employee.  Most employees only have one direct managers.  In a simple design an employee could have two managers, but it would require multiple employee records.  For example:

--drop table e;
create table e (id number, name varchar2(100), manager number);
insert into e values (1,'Amos',null);
insert into e values (2,'Bob',1);
insert into e values (3,'Cindy',1);
insert into e values (4,'Dave',2);
insert into e values (4,'Dave',3);
select substr(name,1,7) name, substr(prior name,1,7) Manager
   from e start with id=1 connect by prior id=manager order by name;


Do you guys think this is a feasible model?

No.

  Is there a better approach to this?

Yes. Use PostgreSQL, and PostGIS. Instead of storing towns, store your points as WGS84 coordinates. You can use PostGIS to resolve the towns or addresses to WGS84 coordinates. You can connect them self and find the shortest path, or... you can use load the OSM data and have a Google Maps-like product.

See also,

MySQL spatial comparison with PostGIS

MariaDB has a graph backend OQGRAPH that may also assist you, but I wouldn't touch it with a 10 foot pole. Database Administrators has one question on it. Outside I've jokes, I've never seen it mentioned.


Get the start and end dates of the previous month the query is run with PostgreSQL/intervals

Assuming you want to apply a condition on a column in the table you can use:
where timestamp_column >= date_trunc('month', current_timestamp) - interval '1 month'
  and timestamp_column <  date_trunc('month', current_timestamp)

date_trunc('month', current_timestamp) - interval '1 month' gives you the start of the previous month. In March this would be 2021-02-01
date_trunc('month', current_timestamp) gives you the start of "this month" so in March this would be 2021-03-1 as the comparison for the upper limit is done using < it will include everything on the last day of February (including 23:59:59.9999999 which your desired condition would not include).