As the administrator or boss, you can create user-specific views which include only the columns you want that user to be able to view. Then grant the user access to their specific view, and not to the underlying table. If you do this, then you don't need to use column permissions in the first place, as the column access is already restricted via the views. If you accidentally include a column into the view that you didn't want to, column permissions won't save you as the view is executed as the view owner, not the view invoker, and so will offer up the columns anyway.

Or each user can create his own view for his own convenience, in which case the column privileges in the underlying table will still be enforced.

Note that many people think that 'select *' is just a crutch for development and testing, and shouldn't be used in production code anyway.


SQL Server trusted connection additional security

The feature designed to help in this scenario is Application Roles.  With an Application Role you can allow the user to connect to the target database, but not be able to run any queries until the application supplies a secret to activate the Application Role.


continuously extending a table in SQL Server

when we query sys.dm_db_index_physical_stats we see an avg_fragment_size_in_pages just under 8, namely the 8 8kB pages that can fit into one 64kB extend. A table with 50000 pages has 6300 fragments. Not very "good" page splits anymore!

If your server has a RAID disk system, or is using SAN-based storage, or has SSD storage, index fragmentation will have no appreciable effect, either way.  If you are storing the database on a single hard drive, then table fragmentation will have an effect that is most noticeable when scanning an index sequentially that spans multiple 64KB extents.  If your system is not querying large portions of the given table(s) sequentially, index fragmentation will have a low impact.

You are much more likely to see gains in performance by ensuring you have the most efficient indexes for the actual work load present on the system.  Oh, and for less than 1/5th the cost of the typical SQL Server Enterprise license you can have the highest performing PCI-attached SSD cards commonly available that can pump hundreds of thousands of I/Os per second.

Look at http://www.brentozar.com/archive/2012/08/sql-server-index-fragmentation/ for more information about the index fragmentation issue.


Chat/conversation history, entity relationship diagram

Some loose opinions which may be useful but may be completely off:

The model looks OK and should be scalable as far as I can tell.
Relational model (3NF) is the king of storage size, and data consistency. It's not the king of "easy querying". See customer_id note below.
In actual long term business, maintainability is often more important than performance.
Don't worry about partitioning too soon. You may never see this first 0.5 TB of customer data in your database. But consider making customer_id the part of PK in all tables if this is going to be a multi-tenant app. It will make scaling up (think: sharding / separating tenants) easier.
The thread ID might be as well synonymous to chat_lines.id of top level item.
tsearch is OK, but for large scale, fancy fulltext search you might need to employ some outside tools like ElasticSearch / Sphinx / etc.


DROP TABLE ... CASCADE does not drop any dependent table

The idea behind DROP CASCADE is to automatically remove the dependent objects. This is documented on the same manual page that the other answer is referring to:

CASCADE
Automatically drop objects that depend on the table (such as views).

(Emphasis mine.)
When you are dropping a table that is referenced by another table, the object that immediately depends on the table being dropped is not the other table itself but the foreign key constraint defined on it.
So, the behaviour you are observing should be expected as it is consistent with the other cases you have mentioned:

DROP TABLE ... CASCADE drops the views that directly depend on the table you are dropping.

DROP DOMAIN ... CASCADE drops the columns that directly depend on the domain you are dropping.

TRUNCATE ... CASCADE is also consistent with the explanation above because it removes rows, and the objects dependent on rows can only be other rows, including other tables' rows – that is why the referencing tables are truncated as well1.

1My only issue with the other tables' truncation is that their foreign keys may be defined on nullable columns and yet the tables are still truncated completely even if some of their rows do not reference the table(s) specified in the TRUNCATE statement. Still, even if it is an inconsistency, it is how the behaviour is documented:

CASCADE
Automatically truncate all tables that have foreign-key references to any of the named tables, or to any tables added to the group due to CASCADE.


Unable to connect to an Oracle 12c Docker container

"Semi-official" images available on hub.docker.com
docker image ls
REPOSITORY         TAG           IMAGE ID       CREATED       SIZE
gvenzl/oracle-xe   18            e1d4dae1771b   4 days ago    3.69GB
gvenzl/oracle-xe   18.4.0        e1d4dae1771b   4 days ago    3.69GB
gvenzl/oracle-xe   latest        e1d4dae1771b   4 days ago    3.69GB
gvenzl/oracle-xe   18-full       13085168429b   12 days ago   6.36GB
gvenzl/oracle-xe   18.4.0-full   13085168429b   12 days ago   6.36GB
gvenzl/oracle-xe   full          13085168429b   12 days ago   6.36GB

docker-compose.yml for persistent database.
version: '3.8'
services:
  database:
    image: gvenzl/oracle-xe:18
    container_name: oracle
    environment:
      - ORACLE_PASSWORD=Welcome_1
    volumes:
      - type: volume
        source: oradata
        target: /opt/oracle/oradata
    ports:
      - 1521:1521
    networks:
      - dbnet
    healthcheck:
      test: sql -L -S sys/Welcome_1@localhost:1521/XEPDB1 as sysdba < /dev/null |grep 'ORA-'; if [[ $$? == 1 ]]; then echo 0; else echo 1; fi
      interval: 1m
      timeout: 10s
      retries: 20
      start_period: 40s
networks:
  dbnet:
volumes:
   oradata:
     driver: local
     driver_opts:
       type: none
       device: $PWD/oradata
       o: bind

Note, make sure "oradata" is rw by oracle user inside container.
Best of luck!


SSIS Excel to SQL Server - Need to add SSIS variables into the mix

Does the Excel Connection Manager use a variable for the file name (ExcelFilePath property)?  If so, try adding a derived column with this variable as the value.  Otherwise you can add a Script Component within the Data Flow Task as follows to create a new column that will hold the file path.

Add a Script Component.  On the Input and Outputs pane, expand the Output 0 folder, select the Output Columns folder and add a new column.  Name this column as you want the column holding the file name to be named and pick the proper data type.
On the Connection Managers pane of the Script Component, add the Excel Connection Manager that is being used and provide an appropriate name for the Name property.
In the script, the following code will obtain the file name and set the new column to this value from the IDTSConnectionManager100 object.  In this example, ExcelConnection is the name that was given to the Excel Connection Manager on the Script Component editor and ExcelFilePath is the name of the new column that was added in the Data Flow.  From this point, the new column can be mapped to the corresponding column in the destination table.

IDTSConnectionManager100 ConnMnger;
ConnMnger = this.Connections.ExcelConnection;

Row.ExcelFilePath = ConnMnger.ConnectionString;


The information you provided is not sufficient to analyze the problem, so people will start guessing (BTW how have you established your theory that the problem is memory pressure?). To analyze the problem you need to enable sql_trace for both sessions, even better set event 10046 for both sessions and then compare the data.

this can be done by executing the following sql once the session has been established

alter session set events '10046 trace name context forever, level 12';

This will generate trace files. These trace files need to be analyzed either manually or with tkprof. You need to pay attention to the wait events.

Are there any other differences between the machines in terms of

Processor
Disk speeds and disk types
Network infrastructure
Other load on the machine
Number of database sessions

As these will affect the performance measurements


Unable to join on conrelid and confrelid columns in pg_constraint

The problem is that not all of "tables" presented in the pg_tables view have OID presented in the pg_class relation:

postgres=# select tablename::regclass from pg_catalog.pg_tables;
ERROR:  relation "sql_features" does not exist

You should to rewrite your query to more "low-level":

SELECT
  x.conname,
  x.contype,
  x.conrelid,
  x.confrelid,
  pk.relname,
  fk.relname
FROM pg_catalog.pg_constraint x
INNER JOIN pg_catalog.pg_class pk ON x.conrelid!=0 AND x.conrelid=pk.oid
INNER JOIN pg_catalog.pg_class fk ON x.confrelid!=0 AND x.confrelid=fk.oid
WHERE
  x.contype='f';

test


Is there an option to turn existing global variant to constant in MySQL or MariaDB?

Which values are you concerned about?

Some are GLOBAL only.  my.ini and a restart is needed to change them from outside.  Or a user with SUPER can change them from a connection.
Some are SESSION, but initialized to the corresponding GLOBAL when you connected.  You can override these with SET SESSION ....  Once you have done that, no one else can change them until you disconnect.

If you are having trouble with a user, that is a personnel problem.  Don't give him SUPER.


Is it ok to install a SSAS 2016 instance next to a SQL Server 2014 instance?

It is best to keep a server dedicated to a single version of SQL Server and/or its components. You can run into connectivity issues or have components stop working as expected due to the mismatched version of the shared components. I recently had an instance where someone upgraded the SSIS components and prevented us from opening packages properly on that box any more.

Additionally, if you can avoid it, keep SSAS and the engine separated as both do not like to share resources properly. You could end up with performance issues if you do not configure things properly.


Use NOT EXISTS:

Declare @T table (id int, [type] varchar(5))
insert into @t(id,[type]) values(1,'B'),(2,'A'),(2,'B'),(3,'A')

SELECT *
FROM @T TAB1
WHERE [TYPE] <> 'A'

UNION ALL

SELECT *
FROM @t TAB1
WHERE [type] = 'A'
    AND NOT EXISTS (
        SELECT *
        FROM @t
        WHERE id = TAB1.id
            AND [type] = 'B'
        )

The above uses UNION ALL (simple concatenation), which is typically more efficient than duplicate-removing UNION [DISTINCT]. Two sets, one with type = A and the other with type <> A cannot possibly overlap, so concatenation is safe here.

You could also express the logic as WHERE [TYPE] <> 'A' OR ([TYPE] = 'A' AND NOT EXISTS ...) but check which method gives you better performance (complex disjunctions do not always optimize well).


MySQL - UUID/created_at cursor based pagination?

WHERE   created_at <= x
  AND ( created_at < x OR uuid < y )
ORDER BY created_at DESC,
         uuid       DESC

or this equivalent:

WHERE (     created_at < x
       OR ( created_at = x AND uuid < y )
      )
ORDER BY created_at DESC,
         uuid       DESC

This technique works for any pair of columns where the first one (created_at) could have duplicates and the second is unique (uuid or id).

And this is required:

INDEX(created_at, uuid)

Note that both parts of the WHERE are DESC.  Mixing ASC and DESC will defeat the usability of the INDEX.  (MySQL 8.0 can work around that.)

Note also, that this assumes you don't care what order the rows are in when created_at is duplicated, but you do want a consistent ordering.  Note that uuid will be seemingly random, but still consistent.  With that said, id (with or without Galera) and uuid work equally well.

(UUIDs suck, but that is a different discussion.)

More on pagination without using OFFSET .


How to INSERT all entries to a map table

APPROACH #1

If you never delete from table1, table2, or table3

STEP #1

Using a Cartesian Join, generate a script that makes individual inserts

SQL="SELECT CONCAT('INSERT IGNORE INTO keymap VALUES"
SQL="${SQL} (',col1,',',col2,',',col3,');') FROM"
SQL="${SQL} (SELECT col1 FROM table1) A,"
SQL="${SQL} (SELECT col2 FROM table2) B,"
SQL="${SQL} (SELECT col3 FROM table3) C;"

read -s pw ; echo ${pw} | less
DB=mydb
echo "SET foreign_key_checks = 0;"         > All_Key_Combinations.sql
mysql -uroot -p${pw} -D${DB} -ANe"${SQL}" >> All_Key_Combinations.sql

STEP #2

Review the script and make sure it's OK

head All_Key_Combinations.sql
echo
tail All_Key_Combinations.sql
echo
sleep 15
less All_Key_Combinations.sql

STEP 3

Execute All_Key_Combinations.sql

DB=mydb
mysql -uroot -p${pw} -D${DB} < All_Key_Combinations.sql

APPROACH #2

If you ever delete from table1, table2, or table3

STEP #1

Using a Cartesian Join, generate a script that makes individual inserts

SQL="SELECT CONCAT('INSERT INTO keymap VALUES"
SQL="${SQL} (',col1,',',col2,',',col3,');') FROM"
SQL="${SQL} (SELECT col1 FROM table1) A,"
SQL="${SQL} (SELECT col2 FROM table2) B,"
SQL="${SQL} (SELECT col3 FROM table3) C;"

read -s pw ; echo ${pw} | less
DB=mydb
echo "SET foreign_key_checks = 0;"         > All_Key_Combinations.sql
echo "TRUNCATE TABLE keymap;"             >> All_Key_Combinations.sql
mysql -uroot -p${pw} -D${DB} -ANe"${SQL}" >> All_Key_Combinations.sql

STEP #2

Review the script and make sure it's OK

head All_Key_Combinations.sql
echo
tail All_Key_Combinations.sql
echo
sleep 15
less All_Key_Combinations.sql

STEP 3

Execute All_Key_Combinations.sql

DB=mydb
mysql -uroot -p${pw} -D${DB} < All_Key_Combinations.sql

GIVE IT A TRY !!!


Work queue with complex select and long processing: Ensuring concurrency

You mentioned you could not use 'locking by deletion' because the row must stay in the table. The code example below does use it, but only after creating a global queue first and populating it. It then uses the 'locking by deletion' for the ##Queue table rather than the table you're working with.

USE master
GO

    -- 1. Create a Stored Procedure that creates the Queue and populates it based on the criteria you have.
    --      (this doesn't have to be a Stored Procedure, but can also be run via some other method to populate the queue.)
    --      (also update 'DatabaseName.SchemaName.the_table' to be the name of your table.)

    IF EXISTS (SELECT * FROM master.dbo.sysobjects o WHERE o.xtype IN ('P') AND o.id = object_id('master.dbo.CreateQueue'))
    DROP PROC CreateQueue
    GO

    CREATE PROCEDURE dbo.CreateQueue
    AS
    BEGIN
        IF OBJECT_ID('Tempdb.dbo.##Queue') IS NOT NULL
        DROP TABLE ##Queue

        SELECT * INTO ##Queue FROM DatabaseName.SchemaName.the_table WHERE <replace with your criteria>
    END
    GO

    -- 2. Execute the SP above or use the code some other way to create the ##Queue

    -- 3. Copy the code below and run it via multiple processes if you like, as there should not be concurrency issues.
    WHILE 1 = 1
        BEGIN
            DELETE TOP ( 1 )
                    ##Queue WITH ( READPAST )
            OUTPUT  Deleted.*
                    INTO #RowToProcess
            IF @@ROWCOUNT > 0
                BEGIN
                    --Place logic here to work with the row...
                    DELETE  FROM #RowToProcess
                END
            ELSE
                BREAK
        END


TLS 1.2 breaks SQL Server linked servers

Are you familiar with using any tools such as netmon, wireshark, or netsh?  You might want to do a capture as you replicate this issue.  Something that sticks out in my mind is Server B might be trying to hit something external to the mix (i.e. Certificate Revocation List CRL check) and that call is failing.  A CRL check takes place on the client side of the equation - in this case Server B is the client.  If you are able to run a trace you probably want to filter for tcp resets where the client (Server B) is involved.  In wireshark a filter for this would look like tcp.flags.reset == 1 and ip.addr == 10.X.X.X where 10.X.X.X would be the ip address of Server B.


SQL Server AAGs/Possible Endpoint contention/Synchronous replication to WAN Secondary

Few things that I learned from having a similar setup with my old company. We had 53 AOAGs with many DBs in each AG.

TCP Endpoint will not be a contention. (remember that you can have only one endpoint per server instance).Even with our setup having MPLS network (NY and LD) and a dedicated VPN between (NY and HK), we ran the above setup and we were fine.
with such amount of DBs, your availability group DMV queries might be slow. So make sure you use temp tables.
Since you are running SQL Server 2016, Always On Availability Groups are Turbocharged along with better AlwaysOn Parallel Compression / Improved Algorithms
Make sure you offload read work by utilizing Round-robin load balancing in readable secondaries.
Make sure you are on latest 2016 SP2 service pack.

As always, do some benchmark and testing and have a baseline to compare. This will give you confidence in presenting to management that the consolidation project indeed helped !


Installing 32bit SSRS on 64bit Windows/IIS

You can fix the problem by following the steps described in the link below:

http://msdn.microsoft.com/en-us/library/ms143293(v=sql.90).aspx

In this link different scenarios including yours have been discussed.
Hope it helps.


Nonclustered Index still doing index scan

Try to create this index:

CREATE NONCLUSTERED INDEX idx_dmcasarms_GradeSubmissionList_RecordIDEdpCode
ON [dbo].GradeSubmissionList(EdpCode, DateSubmitted)
INCLUDE (FinalApprovedRejectedBy, FinalAction, FinalActionDate, FinalActionReason );

Index Seek  will be used only if conditions from WHERE or ON clauses are the first in the list of columns of the Index. Also, this fields must be selective which means that the condition must filter just a little percent or rows from a table.

Also, you don't need to include column that is part of the clustered index into list of columns of a nonclustered index, because Nonclustered indexes always contain the clustered index columns if a clustered index is defined on the table.

Actually, this is very complex subject. To know more about using indexes in Sql Server you can read this article SQL Server Index Design Guide


Select only the last record for multiple values

give your example data this should do it.

SELECT name, max(f_time) AS f_time
FROM sessions
WHERE name IN ('abc','def','xyz')
GROUP BY name
ORDER BY name;

If you need to pick the f_time according to some other column it gets messy.

In Postgresql you can use the distinct on SQL extension.

SELECT DISTNICT ON (name)
    name, f_time
FROM sessions
WHERE name IN ('abc','def','xyz')
ORDER BY name, id desc;

i think there's also a way using window functions


Programmatically find indexes that cannot be rebuilt online

I use the following to detect LOBs:

SET @LOBCount = (
    SELECT COUNT(*) FROM (
        select t.name as TableName, i.name as IndexName, c.name as ColumnName, ty.name, ic.is_included_column
            from [' + @DatabaseName + '].sys.indexes i
            INNER JOIN [' + @DatabaseName + '].sys.tables t ON t.object_id = i.object_id
            inner join [' + @DatabaseName + '].sys.index_columns ic on i.object_id = ic.object_id and i.index_id = ic.index_id
            inner join [' + @DatabaseName + '].sys.columns c on ic.object_id = c.object_id and ic.column_id = c.column_id
            INNER JOIN [' + @DatabaseName + '].sys.types ty ON c.system_type_id = ty.system_type_id
            where
                t.name = ''' + @TableName + '''
                AND i.name = ''' + @IndexName + '''
                AND (
                    ty.name IN (''text'', ''ntext'', ''image'')
                    or
                    (
                        ty.name IN (''varchar'', ''nvarchar'', ''varbinary'', ''xml'')
                        AND c.max_length = -1
                    )
                )
        ) as x
    )';

Obviously, this is a code fragment from a much larger stored proc that we use to rebuild and/or reorganize indexes.  I would recommend you look at Ola Hallengren's scripts at:  http://ola.hallengren.com/


Find material for improvement in Microsoft BI

The book "The Data Warehouse Toolkit" by Kimball is a must for any fledging BI developer, IMO.


how to mongorestore mongoDB 4.2 to documentDB 4.0?

This happened because AWS DocumentDB is only mongo 4.0 compatible and I was trying to restore from mongo 4.2, to solve this I just used AWS DMS as it was free, process is simple and there are many tutorials available from AWS and as well as other people on youtube


PostgresQL Delete Trigger does not delete row

Inheritance could be a useful tool, but for now it has its many flaws, as you noticed.
So you should dismiss it, if the system don't react to the way you think

create table test
(
    id                  serial primary key,
    name                varchar(128) not null
);

create table test_archive
(
    id                  serial primary key,
    name                varchar(128) not null
);

CREATE OR REPLACE FUNCTION archive_record()
RETURNS TRIGGER AS $$
BEGIN
    RAISE NOTICE 'Running trigger';
    EXECUTE format('INSERT INTO %I.%I SELECT $1.*', TG_TABLE_SCHEMA, (TG_TABLE_NAME || '_archive'))
    USING OLD;
    RETURN NULL;
END;
$$ LANGUAGE PLPGSQL;

CREATE TRIGGER delete_test
    AFTER DELETE
    ON test
    FOR EACH ROW
    EXECUTE PROCEDURE archive_record();

insert into test values (1,'test1'),(2,'test2')

2 rows affected

DELETE FROM test WHERE id = 1

1 rows affected

SELECT * FROM test_archive

id | name
-: | :----
 1 | test1

SELECT * FROM test

id | name
-: | :----
 2 | test2

db<>fiddle here


You did not specify your RDBMS (SQL Server, Oracle, etc.), but if it supports Common Table Expressions, you could encapsulate your main select logic in a CTE and select/sum from it.  Here is a solution using SQL Server.

Declare @T table (A int, B int, C int)
insert into @T(A,B,C) values(null,2,null)

;WITH _cte
AS (
    SELECT CASE
            WHEN A IS NULL
                THEN 1
            ELSE 0
            END AS [A]
        ,CASE
            WHEN B IS NULL
                THEN 1
            ELSE 0
            END AS [B]
        ,CASE
            WHEN C IS NULL
                THEN 1
            ELSE 0
            END AS [C]
    FROM @T
    )
SELECT A + B + C as Results
FROM _cte

Results
2

As a comment pointed out, you don't need a common table expression.  A derived table will also serve the purpose

Declare @T table (A int, B int, C int)
insert into @T(A,B,C) values(null,2,null)
SELECT A + B + C AS Results
FROM (
    SELECT CASE
            WHEN A IS NULL
                THEN 1
            ELSE 0
            END AS [A]
        ,CASE
            WHEN B IS NULL
                THEN 1
            ELSE 0
            END AS [B]
        ,CASE
            WHEN C IS NULL
                THEN 1
            ELSE 0
            END AS [C]
    FROM @T
    ) a


The data itself is a very simple data problem, and any modern database system will work just fine (though I recommend staying away from MS Access just because it's antiquated as a database system nowadays).
The actual problem you're trying to solve is a UI one, which you'll want to likely provide a rich text editor for the end users to type their instructions (ProcessDescription) into. This can be accomplished easily with any desktop or web application, which there are a multitude of rich text editors out there for (dependent on whichever front-end / back-end stack you choose). The rich text editor will usually store the formatting of the text with special code (most times HTML tags or something similar are used) behind the scenes from the end user, which will be completely digestible by your database without any special extra work needed to store it and recall it in the same format.


There are advantages of using Linux as you base for mysql, as others have already covered, but from a performance point of view for many applications you will not see the difference particularly.

While Linux would be my default recommendation too make sure you consider your experience (and that of the people you can immediately call upon if something goes wrong). If you are familiar with Windows but have little sysadmin experience with Linux, go with Windows. Having the knowledge to fix certain things when they go wrong may prove much more important than a performance gain while things are going right, and if got know Windows like the back of your hand you may be able to tune it to the point where it performs better than a similar Linux install that you might produce. Likewise if you have a preference for or more experience with Linux go with that for the same reasons.


PostgreSQL FDW: is it necessary to specify sever and user for every link to a database on the same server (with identical user privileges)?

Yes.  You can't use a foreign server without creating it.  And you would have to create one for each database you want to link to, as dbname can only be specified at the foreign server level, not at the level of individual foreign tables.  And each server needs its own user mapping, they can't be shared.


Your assumption is correct with one comment about sort order. Make sure your query sort order matches your index sort order otherwise index will not be used. Read more details here.

Consider a collection events that contains documents with the fields username and date. Applications can issue queries that return results sorted first by ascending username values and then by descending (i.e. more recent to last) date values, such as:

db.events.find().sort( { username: 1, date: -1 } )

or queries that return results sorted first by descending username values and then by ascending date values, such as:

db.events.find().sort( { username: -1, date: 1 } )

The following index can support both these sort operations:

db.events.createIndex( { "username" : 1, "date" : -1 } )

However, the above index cannot support sorting by ascending username values and then by ascending date values, such as the following:

db.events.find().sort( { username: 1, date: 1 } )


Java Spring application Database triggers auditing - how to provide the right user that made the change

Unless you create a different database user for each application user -so it is available with the user() or current_user() functions:

mysql> SELECT user();
+----------------+
| user()         |
+----------------+
| root@localhost |
+----------------+
1 row in set (0.00 sec)

mysql> SELECT current_user();
+----------------+
| current_user() |
+----------------+
| root@localhost |
+----------------+
1 row in set (0.00 sec)

it is not possible to know this at MySQL level unless it is provided by the application.

You could use SQL comments on application code:

/* user=jynus */ INSERT INTO test values (1);

Which can later be processed using the slow log, if active, or some of the auditing plugins. Please note that the mysql command-line client application deletes comments before sending them to the server.

The only way you can log it using triggers with a single user that I can now think is setting a variable at the start of the connection:

mysql> SET @user := 'jynus';
Query OK, 0 rows affected (0.01 sec)

mysql> CREATE TABLE test (i int PRIMARY KEY);
Query OK, 0 rows affected (0.04 sec)

mysql> CREATE TABLE log (id serial, i int, user varchar(100));
Query OK, 0 rows affected (0.06 sec)

mysql> CREATE TRIGGER test_AI AFTER INSERT ON test FOR EACH ROW
       INSERT INTO log (i, user) VALUES (NEW.i, @user);
Query OK, 0 rows affected (0.02 sec)

mysql> INSERT INTO test VALUES (5);
Query OK, 1 row affected (0.00 sec)

mysql> SELECT * FROM log;
+----+------+-------+
| id | i    | user  |
+----+------+-------+
|  1 |    5 | jynus |
+----+------+-------+
1 row in set (0.00 sec)


Why is my TRY CATCH block in SQL Server immediately exiting on error completely ignoring the remaining code in the block, including my error handling?

This is actually far more straightforward than I thought. Assuming that all of the variables used above are declared elsewhere, the simple answer as to why the catch isn't working is that it is working, but it has syntax errors internally that result in a return value of NULL.

Consider this line:

SET @errorstring = @errorstring + 'Database: ' + @name + '  Error: ' + @errormessage + char(10) + char(10);

Unless you declared @errorstring like this:

DECLARE @errorstring varchar(255) = ''

Or assigned @errorstring as some value, the variable is currently set to NULL. And, by default, concatenating anything to NULL returns... NULL.

Changing the code here to

SET @errorstring = 'Database: ' + @name + '  Error: ' + @errormessage + char(10) + char(10);

Sets the variable to the correct value.


Script to install Oracle client 12c2 on Redhat linux 7.5

That will not work, that is not how you execute commands as an other user. + Only root can run passwd username, not even oracle user can run passwd oracle.

sudo su oracle; echo Nbst@AWS01 | passwd oracle --stdin
export ORACLE_BASE=/opt/app/oracle
export ORACLE_HOME=/opt/app/oracle/product/12.2.0/client_1
/tmp/client/runInstaller -silent -responseFile /tmp/client/client_install.rsp
exit

Use something like this:

echo Nbst@AWS01 | passwd oracle --stdin
sudo su oracle -c "export ORACLE_BASE=/opt/app/oracle;
export ORACLE_HOME=/opt/app/oracle/product/12.2.0/client_1;
/tmp/client/runInstaller -silent -responseFile /tmp/client/client_install.rsp"


Restrict range of dynamic ports available to SQL Server

Here's the start of a PowerShell solution as I mentioned in the comments:

$instanceId = "<Instance ID>";
$registryKey = "HKLM:\SOFTWARE\Microsoft\Microsoft SQL Server\$instanceId\MSSQLServer\SuperSocketNetLib\TCP\IPAll"
$portNumber = (Get-Item $registryKey).GetValue("TcpDynamicPorts");

netsh advfirewall firewall add rule action=allow localport=$portNumber protocol=TCP dir=in name="SQL Server Database Engine ($instanceId)"

I'm not sure if this is going to be appropriate for the exact situation, but it might help someone out there.


Can I query from two databases if my web application is connected to one database?

You need to add DB2 as a linked server on your DB1 instance.
Once created, then you can specify [LinkedServer].[Database].[schema].[Table] to query the objects on that server. Other than linked servers, there is no other way for a connection to discover and connect to another server(even if you have access) for security reasons. If you create a linked server, you are specifying a connection that server DB1 can establish and query DB2, all from BD1, not from the client.

Hope that helps.
How to create a linked server in MSSQL


What could cause the wrong ID to be inserted?

Doing dbcc checkident('dbo.table',reseed,0) will cause the next entry in a newly created / truncated table to have 0 as the identity.

        CREATE TABLE TestIdent
        (
            ID INT NOT NULL CONSTRAINT PK_TestIdent PRIMARY KEY CLUSTERED IDENTITY(1,1)
            , SomeText nvarchar(255)
        );

        dbcc checkident('dbo.TestIdent',reseed,0)

        INSERT INTO TestIdent VALUES ('Test');

        SELECT * FROM dbo.TestIdent;

This causes the next identity entered to be 10:

        TRUNCATE TABLE dbo.TestIdent;

        DBCC CHECKIDENT('dbo.TestIdent',reseed,10)

        INSERT INTO TestIdent VALUES ('Test');

        SELECT * FROM dbo.TestIdent;


How can a group track database schema changes?

just a couple of minutes ago I was checking this: A table that should exist in all projects with a database, seems simple enough to put in practice, check it out:

  It’s called schema_version (or migrations, or whatever suits you) and its purpose is to keep track of structural or data changes to the database.
  A possible structure (example in MySQL) is:

create table schema_version (
     `when` timestamp not null default CURRENT_TIMESTAMP,
     `key` varchar(256) not null,
     `extra` varchar(256),
     primary key (`key`)
) ENGINE=InnoDB;

  insert into schema_version(key, extra) values ('001', 'schema version');

  Whether you add this table from the beggining of the project or just after you’ve deployed the first version to a staging or production server is up to you.

  Whenever you need to execute an SQL script to change the database structure or perform a data migration you should be adding a row in that table as well. And do that via an insert statement at the begining or end of that script (which is committed to the project’s code repository).

  …


What is more efficient, a where clause or a join with million plus row tables?

For modern RDBMS there is no difference between "explicit JOIN" and "JOIN-in-the-WHERE" (if all JOINS are INNER) regards performance and query plan.

The explicit JOIN syntax is clearer and less ambiguous (see links below)

Now, the JOIN-before-WHERE is logical processing not actual processing and the modern optimisers are clever enough to realise this.

Your problem here is most likely indexing.

Please show us all indexes and keys on these tables. And the query plans

Note: this question would have been close on StackOverflow for being a duplicate by now... COUNT(1) vs COUNT(*) is another busted myth too.

https://stackoverflow.com/questions/5654278/sql-join-is-there-a-difference-between-using-on-or-where/5654338#5654338
https://stackoverflow.com/questions/3003457/count-vs-countcolumn-name-which-is-more-correct/3003533#3003533
https://stackoverflow.com/questions/1221559/count-vs-count1/1221649#1221649


Does this design satisfy 3NF, and can any improvements be made?

The normal forms apply to individual tables. Dependencies are semantic; they depend on what the data means. Normalizing by relying on column names alone is not very reliable.

TblClient is in 5NF.
TblEmployee might have a transitive dependency and be only in 2NF, if EmployeeNm->EmployeeRole.
TblItem might have a transitive dependency and be only in 2NF, if (ItemType, ItemDesc)->Size, or if ItemDesc->Size.
TblProject might have a transitive dependency and be only in 2NF, if (ProjectSite, EstBeginDt)->EstCompleteDt, or if (ProjectSite, EstBeginDt)->anything else.

Every one of these tables almost certainly needs an additional unique constraint on one or more columns. Otherwise you're likely to end up with data that looks like this.

ItemID  ItemType  ItemDesc                Size
--
245     Flange    Engine mounting flange  13mm
246     Flange    Engine mounting flange  13mm
247     Flange    Engine mounting flange  13mm
451     Flange    Engine mounting flange  13mm
457     Flange    Engine mounting flange  13mm
683     Flange    Engine mounting flange  13mm

That particular table needs an additional unique constraint on either {ItemType, ItemDesc} or on {ItemType, ItemDesc, Size}.

Reaching 3NF for every table doesn't necessarily mean you have a good design. You might well have missed critical requirements.  If you missed a critical requirement, you're not likely to discover it by normalizing to 3NF or 5NF.

Whoever taught you to name all tables "Tbl"-something or "Lk"-something or "Xref"-something did you no favors. It's likely that person didn't understand logical data independence, and it's likely you don't understand it either. That's a big deal, because it's one of the main features of the relational model, and SQL supports it fairly well. Name tables for what they are, not for how you intend to use them today.


Scripting the setting up of a MySQL database - best practices?

Should I be splitting up do_everything.sql into different .sql files according to logical chunks? If so, would I then have a single master shell script that executes all of them?

IMHO, yes, you should split up your do_everything.sql script into smaller logical chunks - modularisation is one of the key tools that we software engineers have at our disposal to help organise our work, and this will lead to fewer bugs and fewer mistakes. You can test module by module rather than having to always deal with a behemoth (or "juggernaut" as someone from the Indian subcontinent might say! :-) ).

If I keep the same workflow, am I following best practices?

See my remarks about modularisation above - I believe that it would be considered best practice to break your script up into logical parts - I used to always do it table by table - that way it was easy to see where any problems arose, and the "sub-scripts" could be either tested one by one or the master script could be checked.

You asked in comments:

Can I modularize the scripts and run them from a single "master" .sql file? If so, having trouble looking up the command to do so.

It's very simple - you simply use the SOURCEkeyword (i.e. SOURCE /path/to/file.sql) in your scripts - you can nest this to many levels (but not too many).

What I typically did was have a master_script.sql which was really just a "skeleton" - it was just filled with SOURCE statements calling other scripts. Some of those might themselves have had SOURCE statements (within reason). I found this worked very well.


Estimated Execution Plan SQL Server Sort?

Group by Abandoned_ID, WorkType_ID , Site_Type_ID, time_id

One way to implement a group by is to sort the input. Stream Aggregate Showplan Operator:

  The Stream Aggregate operator requires input ordered by the columns within its groups. The optimizer will use a Sort operator prior to this operator if the data is not already sorted due to a prior Sort operator or due to an ordered index seek or scan.

QED.

Further reading: Craig Freedman blog entry on Stream Aggregate and the alternative Hash Aggregate.


Not sure, why this is marked "Active yesterday". Once I'm here…

If it needs to be done (which it shouldn't, if proper database design can be applied), this could be a way:

SELECT
  Users.userID
  , Users.name AS userName
  , Groups.uids
  , Groups.name AS groupName
FROM Users
INNER JOIN Groups
  ON INSTR(TRANSLATE(Groups.uids, '()', ',,'), ',' || TO_CHAR(Users.userid) || ',') > 0
;

It turns the parentheses around the list of group ids into commas to simplify the comparison against the comma padded userIDs. The latter is to prevent, e.g., 123 to match 12.

See it in action: SQL Fiddle

Please comment, if and as this requires adjustment / further detail.


mysql trigger on update with insert statement

I think that you don't need a trigger at all and - based on your comments - you want an INSERT ... ON DUPLICATE KEY UPDATE. If there is a UNIQUE constraint on (col2), this will work:

INSERT INTO table_name
  (col1, col2)
VALUES
  (@col1, @col2)                  -- the new values here
ON DUPLICATE KEY UPDATE
  col1 = col1 + VALUES(col1) ;    -- update the old value by adding the new one


2017-08-16 03:44:34 GMT LOG:  received fast shutdown request

Someone did a pg_ctl -m fast stop then pg_ctl start, either manually or via the Windows service controller (services.msc, net service, ...).

If it wasn't you, it was a scheduled job, automatic update for some tool related to PostgreSQL from some unofficial 3rd party, etc. Because unless your system got hit by the most mystically lucky cosmic ray in history, that's what happened.

There's no way to restart PostgreSQL from within the PostgreSQL SQL environment (a SQL connection via PgAdmin, psql, etc). You could do it by sending a signal from an untrusted procedural language like plpythonu or plperlu if you're connected as the database superuser and these languages are installed. But otherwise it'd be done at the command-shell level with pg_ctl, by signalling the postmaster process, etc.


You could make (ParentId,FavoriteChildId) a FK on Parent and get most of this.  However in SQL Server there's no practical way to make the relationship required, so you must allow a parent to have no favorite child, or else you wouldn't be able to insert a new parent without disabling the FK.

You can add a trigger to prevent later setting FavoriteChildId to null, but you must allow it at least in the case where a Parent has a single Child, as there would otherwise be no way to delete that Child.  So I'm not sure it's worth bothering with the Trigger.  EG:

--alter table Parent
--drop constraint fk_Parent_favorite_child
--go
--drop table if exists Child
--drop table if exists Parent

go

CREATE TABLE Parent
(
   ParentID int NOT NULL PRIMARY KEY,
   ParentName varchar(50) NULL,
   FavoriteChildId int null
)

CREATE TABLE Child
(
  ParentID int NOT NULL REFERENCES Parent (ParentID),
  ChildID int UNIQUE NOT NULL,
  constraint pk_child primary key (ParentID, ChildID),
  ChildName varchar(50) NULL
)

alter table Parent
add constraint fk_Parent_favorite_child
foreign key (ParentId,FavoriteChildId)
  references Child (ParentId,ChildId)
on delete set null

go
create or alter trigger tg_ensure_favorite_child
  on parent after update
as
begin
   if exists
   (
       select *
       from inserted i
       where i.FavoriteChildId is null
         and (select count(*) from child where ParentID = i.ParentId  ) > 1
    )
    begin
      throw 50001, 'Cannot set ChildID null for Parent with more than one Child.', 10
    end
end

go

insert into Parent(ParentID,ParentName) values (1,'Parent1')
insert into Child(ParentId, ChildId, ChildName) values (1,1,'Child1')
insert into Child(ParentId, ChildId, ChildName) values (1,2,'Child2')
update Parent set FavoriteChildId = 2 where ParentId = 1

go

update parent set FavoriteChildId = null where ParentId = 1 -- Fails
--Msg 50001, Level 16, State 10, Procedure tg_ensure_favorite_child, Line 13 [Batch Start Line 45]
--cannot set ChildID null for Parent with Children

select FavoriteChildId from Parent where ParentID = 1
--FavoriteChildId
-----------------
--2

--(1 row affected)
go

delete from Child where ParentId = 1 and ChildID = 1

go

update parent set FavoriteChildId = null where ParentId = 1 -- Succeeds


Is it good practice to have relationship between the tenant table and all other tables in a multi tenant application?

Is it a good idea to create a relationship between Tenant and all other tables?

Yes. Absolutely yes, you should have a relationship between them physically (it is already there logically).

This way I could use cascade delete to delete a tenant by simply removing a single record from the tenants table.

Er, um, not so sure about this one. Depending on the size / complexity of your schema, you might not be able to do this anyway due to potential circular references. I would do cascading delete FKs for subsets of tables that together act as a particular feature or property. But I don't see this as a good thing for doing over the entire schema, even if you don't get circular reference errors. Still, adding ON DELETE CASCADE to subsets of tables will at least reduce the total number of delete statements (although, you should already be doing the DELETEs in a transaction, so transactionally this won't really be any different; it will be fewer DELETE statements for you to code / maintain, though the main benefit will be helping to ensure integrity within those subsets).

For more info, please see my answer, here on DBA.SE, to the following question:
Composite Primary Key in multi-tenant SQL Server database


Is there a way to get the last NDF file READ/Access date / time?

Another option is to use sysinternals process utilities especially :

make sure you use the filter button to filter out unwanted things :-)

Process Explorer
Find out what files, registry keys and other objects processes have open, which DLLs they have loaded, and more. This uniquely powerful utility will even show you who owns each process.

or

Process Monitor
Monitor file system, Registry, process, thread and DLL activity in real-time.


Copy database, problem with not existing principal

The principal in the error you are seeing (MVGSQL01\itsac) is a Windows login from the computer MVGSQL01 and can't be created in SQL Server because it doesn't exist on the destination computer.

You would need to create a new Windows login on the computer you are copying your database to.

Then in SSMS using a new query window, you would run these two commands to add the login and then the database user for your new login

USE [master]
GO
CREATE LOGIN [NEWCOMPUTERNAME\itsac] FROM WINDOWS WITH DEFAULT_DATABASE=[mydatabase]
GO

USE [mydatabase]

CREATE USER [NEWCOMPUTERNAME\itsac] FOR LOGIN [NEWCOMPUTERNAME\itsac]
GO

Note: Since the computer name of the principal (login) is different though, you will probably get the same error. Another option would be to remove the login (MVGSQL01\itsac) from SQL Server on MVGSQL01 temporarily and then copy the database to your laptop.

There is a stored procedure called sp_help_revlogin http://support.microsoft.com/kb/918992 that can be used to backup and or transfer your logins.

Updated 7/15/2013

In SSMS, when you launch the SQL Server Copy Database Wizard, you will eventually get to the Select Server Objects Screen, if you are copying to or from a remote server.

From there you can select which users you want or don't want to copy for the database you wish to copy.

And Then you get the Logins screen. Click on Copy only the selected logins and then select the logins you wish to copy. In your case, you will be excluding MVGSQL01\itsac.


PostgreSQL update join vs SQL Server update join

But running in Postgres, the query is glacially slow.

UPDATE foo
SET    bar = t2.bar
FROM   foo t1
JOIN   foo2 t2 ON t1.id = t2.id;

There is no join condition between foo and t1, the implicit CROSS JOIN forces a Cartesian product, i.e. O(N²) (!) update operations instead of just O(N). And the result is non-deterministic nonsense. The effect also becomes apparent in the query plan: rows=338326628280 instead of rows=581621 (Also: both plans were produced off slightly different tables, but that seems irrelevant to the question.)
Could be fixed by adding a join condition like:
UPDATE foo
SET    bar = t2.bar
FROM   foo t1
JOIN   foo2 t2 ON t1.id = t2.id
WHERE  foo.id = t1.id;  -- !

Well, technically, a WHERE condition, but all the same.
But that's just putting lipstick on a pig. While id is the PK column of each table, that's just adding noise. Use the command you already found instead:
UPDATE foo
SET    bar = t2.bar
FROM   foo2 t2
WHERE  foo.id = t2.id;

The manual advises for the FROM clause of UPDATE:

Do not repeat the target table as a from_item unless you intend a self-join (in which case it must appear with an alias in the from_item).

And:

When a FROM clause is present, what essentially happens is that the
target table is joined to the tables mentioned in the from_item list,
and each output row of the join represents an update operation for the
target table. When using FROM you should ensure that the join
produces at most one output row for each row to be modified. In other
words, a target row shouldn't join to more than one row from the other
table(s). If it does, then only one of the join rows will be used to
update the target row, but which one will be used is not readily predictable.

Such a self-join makes sense (or is even necessary!) if you need a LEFT [OUTER] JOIN to additional table(s). Sadly, there is no provision in SQL to say "FROM LEFT" in an UPDATE. Example:

Nullify column in update if subquery returns empty


The question to answer first is, are any of the possible children bounded or unbounded sets of data? The next question is, how are you going to be requesting the data? Another question would be, how often will the data be written?

One of the things you'll learn quickly with MongoDB is the answer to any schema questions is almost always "It depends". It depends on the answers to the questions I asked above and a few more. To get you on your way though, MongoDB has some great documentation on the subject of hierarchies. This gets you part of the way to your solution.

Then you have to understand your data access patterns and when to normalize or denormalize data. Something to help you make that decision can be read in this great blog.

Sorry to leave you with a not so cut and dry answer. Yes, MongoDB can take care of hierarchical data. How should it be done? It depends. Will it be faster than MySQL, it depends. Can it be faster than MySQL? Most definitely.

Scott


SQL Server 2008 R2 on Windows 7 x64 Professional - what are the downsides?

Your going to run into occasional memory and thread handling issues.  I run 2008 r2 Developer (which is basically enterprise) on Win7 Prof (64x) on a development machine* for writing code.  I do not know the exact reason but SQL acts funky sometimes and you need to either restart service or box.  Ex. No queries will be running and cpu will be pinned at 50% with SQL proc that if you check with ProcExplorer isn't doing anything. If this is something you can live with doing every so often then maybe you can get away with it.  On the other hand if you need high up-time then spend the money on server edition.

*I run like this because Visual Studio runs like crap on server and I like a portable compact dev setup when I'm on site.


Does having auto_increment ID degrade performance compared to no auto_increment?

After statically looking at the MySQL (5.7.28) code it makes no difference. Auto_increment locks within innodb only happen if fetching a new auto_increment value (via here), or altering the table to have a different auto_increment value.

Its current dormant status is not causing any impact to you.


Duration how long a door is open, but same status records can be present more than once

IMHO this question can be solved using a classic GROUPING AND WINDOWS solution.

Reset point

According to your question:

  I want the earliest OPEN record, compared to the nearest CLOSED record.

Two or more contiguous CLOSED records, require two different groups.

I've a set a reset point when:

Last StatusDoor IS NULL (First record)
Last StatusDoor is CLOSED and current StatusDoor is CLOSED. (Nearest CLOSED record).
Current StatusDoor is OPEN and last StatusDoor is CLOSED (New Open/Closed event).

In addition, I've added a MinMaxOrder just to ensure OPEN events can be fetched before CLOSED events.

WITH ResetPoint AS
(
    SELECT DoorName, [StatusDoor], EventEndDate,
           CASE WHEN ([StatusDoor] = 'CLOSED'
                     AND  COALESCE(LAG(StatusDoor) OVER (PARTITION BY DoorName ORDER BY EventEndDate), 'CLOSED') = 'CLOSED')
                     OR
                     ([StatusDoor] = 'OPEN'
                     AND  COALESCE(LAG(StatusDoor) OVER (PARTITION BY DoorName ORDER BY EventEndDate), 'CLOSED') = 'CLOSED')
                THEN 1 END IsReset
    FROM   #DeviceMessages
)
   , SetGroup AS
   (
        SELECT DoorName, [StatusDoor], EventEndDate,
               CASE WHEN StatusDoor = 'OPEN' THEN 0 ELSE 1 END MinMaxOrder,
               COUNT(IsReset) OVER (PARTITION BY DoorName ORDER BY [EventEndDate]) grp
        FROM   ResetPoint
    )
SELECT * FROM SetGroup;
GO

DoorName | StatusDoor | EventEndDate        | MinMaxOrder | grp
:------- | :--------- | :------------------ | ----------: | --:
Door 001 | OPEN       | 23/03/2017 16:50:42 |           0 |   1
Door 001 | CLOSED     | 23/03/2017 16:54:15 |           1 |   1
Door 001 | CLOSED     | 23/03/2017 17:53:12 |           1 |   2
Door 001 | OPEN       | 23/03/2017 18:22:37 |           0 |   3
Door 001 | CLOSED     | 23/03/2017 18:28:01 |           1 |   3
Door 001 | CLOSED     | 23/03/2017 18:28:01 |           1 |   4
Door 001 | OPEN       | 23/03/2017 18:29:40 |           0 |   5
Door 001 | OPEN       | 23/03/2017 18:29:40 |           0 |   5
Door 001 | CLOSED     | 23/03/2017 18:34:53 |           1 |   5
Door 001 | CLOSED     | 23/03/2017 18:34:53 |           1 |   6
Door 001 | OPEN       | 23/03/2017 18:42:56 |           0 |   7
Door 001 | OPEN       | 23/03/2017 18:42:56 |           0 |   7
Door 001 | CLOSED     | 23/03/2017 18:46:32 |           1 |   7

As far as each CLOSED event produces a new group, I can get MIN and MAX event of each group, and return only those groups with more than one record.

--= Set a reset point each time the door opens and last event is a closed event
--
WITH ResetPoint AS
(
    SELECT DoorName, [StatusDoor], EventEndDate,
           CASE WHEN ([StatusDoor] = 'CLOSED'
                     AND  COALESCE(LAG(StatusDoor) OVER (PARTITION BY DoorName ORDER BY EventEndDate), 'CLOSED') = 'CLOSED')
                     OR
                     ([StatusDoor] = 'OPEN'
                     AND  COALESCE(LAG(StatusDoor) OVER (PARTITION BY DoorName ORDER BY EventEndDate), 'CLOSED') = 'CLOSED')
                THEN 1 END IsReset
    FROM   #DeviceMessages
)
   --= Set a group for each reset point, and establish specific order as 0='OPEN', 1='CLOSED'
   --
   , SetGroup AS
   (
        SELECT DoorName, [StatusDoor], EventEndDate,
               CASE WHEN StatusDoor = 'OPEN' THEN 0 ELSE 1 END MinMaxOrder,
               COUNT(IsReset) OVER (PARTITION BY DoorName ORDER BY [EventEndDate]) grp
        FROM   ResetPoint
    )
    --= Get max and min event date avoiding single CLOSED events.
    --
    SELECT   DoorName,
             MIN(EventEndDate) DoorOpen,
             MAX(EventEndDate) DoorClosed,
             DATEDIFF(SECOND, MIN(EventEndDate), MAX(EventEndDate)) DurationOpenSec
    FROM     SetGroup
    GROUP BY DoorName, grp
    HAVING   COUNT(*) > 1
GO

DoorName | DoorOpen            | DoorClosed          | DurationOpenSec
:------- | :------------------ | :------------------ | --------------:
Door 001 | 23/03/2017 16:50:42 | 23/03/2017 16:54:15 |             213
Door 001 | 23/03/2017 18:22:37 | 23/03/2017 18:28:01 |             324
Door 001 | 23/03/2017 18:29:40 | 23/03/2017 18:34:53 |             313
Door 001 | 23/03/2017 18:42:56 | 23/03/2017 18:46:32 |             216

dbfiddle here


Single or multiple non-clustered indexes when pairing with a composite primary key

This is a strange one as [UserId] [int] IDENTITY(1,1) NOT NULL is unique.

There could be a case to use [UserId] alone as the PK.  You will get less fragmentation compared to a composite clustered index TenantId, UserId.

I get you plan to use TenantId, UserId in all queries but you don't need to.  UserID will uniquely identify each user.

If you have reports by TenantId you can include that in the tables and put a non-clustered index on it.

As far as email I would just put a non-clustered unique index on it. You can include the other fields if you like but it is just picking up data from a single row so it is not really necessary.  Right now there is nothing to prevent duplicate email.

Could argue repeating TenantId in all tables is not 3NF as it can be derived from  UserId.

I personally would not repeat TenantId and have a view for each table that brings it in. I get you don't want to do it that way but it is still my answer.

select t.*, u.tenant
  from table t
  join [user] u
    on u.userid = t.userid

User is not a good name for a table as it is a keyword.


How should we handle rows which won't be queried once they are old in PostgreSQL?

Partition and/or use partial indexes, so you only have an index for the part you care about
No, AFAIK there are no correlation hints in Pg yet. Pity.
Depends a lot on the type of queries, but it can be worth doing, especially if you can do it by partitioning.

Partition

This is a classic use for table partitioning. Partition the table into two (or more) parts, one with the hot data and one with the old archival data. Index anything you need to on the hot table, and use fewer indexes to save space and save insert time on the cold table.

With constraint exclusion enabled, Pg will automatically scan only new_table when your queries match the partitioning constraint.

If your queries don't all use the same constraint you can either add a where test that does use the constraint, or just query the new_table partition directly.

Partial indexes

Alternately, you can create partial indexes that only cover the ranges you're interested in. Again, these will only work if your queries use constraints Pg can recognise as matching the partial index. In your case you'd have to create and drop them over time to keep your time range relevant, since you can't index on a dynamic expression like WHERE (some_field > current_timestamp - INTERVAL '2' week).


Contrary to popular misconceptions, "materializing" has nothing to do with writing anything to disk or a "storage" layer. During the process of executing a query you have two discrete concepts,

Pipelining
Materializing

The basic idea here is that given something like a WHERE clause you can either

Add selectivity in the retrieval,
Add selectivity at the end of the pipeline,

The process is stateless. Ultimately a pipeline gets constructed and somewhere in that pipeline the magic happens (with the general rule being the earlier the better).

Let's look at a sort, how do you sort 1,5,3 in a pipeline?  You can't. That means if the table isn't clustered by that field, your only option is to

Declare a thing "result set"
Finish writing that "result set"
Process it (sort in this case)
Move on

That "thing", more typically a "materialized result set" is a "relation" and it's typically itself modeled like any other table subject to all of the same operations.

Materialization can be a result of the demands of the problem (like sorting), a shortage of resources, or because of planner limitations -- in PostgreSQL a CTE is an optimization fence. That means optimizations on the outside of the CTE can not be pushed down into the CTE. Why? Because at the point the CTE is done: it's results are in a buffer somewhere (or on disk), the step of pipelining is over.

Real World Validation

PostgreSQL, I would urge you to search through the whole codebase here for "materialization". It's used frequently. You can see one of the popular cases in nodeMaterial.c where a call is made to tuplestore_begin_heap to get a place to store a result set
MySQL uses it in the context of semijoin, subquery, aggregate, and window function code. Further they have a hint MATERIALIZATION which is documented as such,

  The optimizer uses materialization to enable more efficient subquery processing. Materialization speeds up query execution by generating a subquery result as a temporary table, normally in memory. The first time MySQL needs the subquery result, it materializes that result into a temporary table. Any subsequent time the result is needed, MySQL refers again to the temporary table. The optimizer may index the table with a hash index to make lookups fast and inexpensive. The index contains unique values to eliminate duplicates and make the table smaller.

The video in question.

I would advising ignoring this section of the video. He defines "materialization" as the step from "physical pages to storage devices". This is not a definition in any practice for that term.

For the purposes of a DBA, "materialization" means what I said above.
For the purposes of chip design and kernel design, there is a difference between Physical Pages and Virtual Pages.

Physical Pages are in hardware and contiguous. A "page" is one unit of CPU-accessible memory (4096 bytes on x86). It's called a "Physical Page" because you often can't access it directly, and it's not exposed to you at any level.
A logical page is one unit of accessible memory in software that the kernel can get access to.

In the x86 platform for instance, paging is turned in in Long Mode (x86_64) and you can't access Physical Memory without going through a "Logical Page." He calls the process of resolution from Logical Pages to Physical Pages "devirtualization". I've never heard of that and likely neither has he simply because it's a function of the microcode on the CPU and that's all proprietary so naming individual processes that it performs is somewhat useless. For the same reason "materialization" seems equally useless, and it's even more confusing. If you know the physical page how could you not know the storage device? If it's physical, it's not abstracted further. What does the logical<->physical mapping mean in this scheme? It's foreign to me anyway, but maybe it's Oracle parlance or something.

Chosen Answer

  In database research the term "materialization" denotes any form of data storage, i.e. any operation that actually sets some bytes on any storage layer eventually. Examples include a deep copy, memory allocation, replication, materialized views (rather than dynamic views), intra-pipeline materializations, but also any form of (partial) copies along the storage hierarchy.

That is untrue in any research or database parlance and I would challenge the statement that all uses of malloc, memcpy or as the author goes on to say fork with (or without) COW as being "Materialization". They may be a "part" of materialization if they otherwise refer to the mechanism I describe above.


Single listener for multiple Oracle homes?

Question: Is it problematic to use a single listener for multiple
  Oracle homes?

No.  There really is no valid reason to run multiple Listeners.

You just need to make sure each database "registers" with that one Listener.

  I get that it's a single point of failure, but is there
  also a performance hit?

I'd be more concern with your single node databases being a "single point of failure" before I worry about the Listener as a "single point of failure".

  Something else I'm not thinking of?

K.I.S.S.:  Just use a single Listener.
The Listener does not have to be on the same server as the database
I believe you can use Oracle Grid to run a collection of Listeners.

  Question: Is there some other way I can run a listener out of each of
  the new Oracle homes using the same port? Again, we're not using
  virtual IPs, so I'm stuck with new host "foonew" + port 1521.

No.  This is an OS thing, not a Listener thing.


SQL Server Command Line install - Default database directories

Yes you can do this. Best way to do is use the configuration.ini file and customise it as per your use.

For system datafiles you will need to edit this portion of the file :
INSTALLSQLDATADIR="S:"

ABove example is where my system datafiles go into.. (S: )


Use persistent connections, cpu will drop dramatically. Then check slow queries.


You should use len() instead of datalength() to get the number of characters in Value. They are different because Value is a double byte string (nvarchar).

CREATE TABLE #tmp (Value NVARCHAR(255))
INSERT INTO #tmp (Value) VALUES ('L1 [ABC]'),('    L2 [DEFGH]'),('        L3 [E]')

SELECT
Value + SPACE(50 - DATALENGTH(Value)) + 'Description text'
FROM #tmp

SELECT
Value + SPACE(50 - LEN(Value)) + 'Description text'
FROM #tmp

Result:

----------------------------------------------------------------------------
L1 [ABC]                                  Description text
    L2 [DEFGH]                      Description text
        L3 [E]                      Description text

(3 row(s) affected)

----------------------------------------------------------------------------
L1 [ABC]                                          Description text
    L2 [DEFGH]                                    Description text
        L3 [E]                                    Description text


How do I simultaneously update unique keys in PostgreSQL?

Define the constraint as deferrable:
CREATE TABLE widget (
  id serial PRIMARY KEY,
  name text NOT NULL,
  ordinal int NOT NULL UNIQUE DEFERRABLE
);

Then you can update it in one statement:
update widget
  set ordinal = t.new_ordinal
from (
  values (1, 3), (3,1)
) as t(id, new_ordinal)
where t.id = widget.id


Dropped database still shows but causes errors PostgreSQL

I would guess you had open connections / transactions while you dropped the db initially and the DROP is stalled somehow.

You can get more info on databases with statistics views. For instance:

SELECT * FROM pg_stat_database WHERE datname = 'mixmobi_master';

In case you find open backend processes, you might want to shut them down like this:

SELECT pg_terminate_backend(procpid)
FROM   pg_stat_activity
WHERE  datname = 'mixmobi_master';

More about that in the manual here.

Find a safe method to drop a database under load at this related answer:

Force drop db while others may be connected


When do Azure SQL Database index rebuilds occur?

When do index rebuilds occur in Azure SQL? Are they automatic or manually selected in a maintenance window?

The index rebuilds, at the time of writing the answer, in SQL Server is not automatic, I hope you are not mixing it with automatic index tuning. There is no "SQL Server agent" in Azure SQL database you have the option to schedule jobs via Elastic Job Agent to run Ola Hallengren Index maintenance job for index rebuild/reorganize and stats update, you can also use your personal index maintenance script. Please note Elastic Job agent is still in preview and requires the Azure SQL database to be on size S0 and above. Read more about Database elastic jobs

You also have option to create Azure automation account for job scheduling


How do I set a geography point to be the North Pole?

Try this instead, with (or without) decimal points

DECLARE @a geography
SET @a=geography::STPointFromText('POINT(0.0 90.0)', 4326)

if this works - then something wrong with the ITEMS table


I don't think this syntax will work:

IN (`a`)

Instead, you probably need to use CONCAT, etc to construct the SELECT, then PREPARE, EXECUTE, and DEALLOCATE it.

But, I suggest you back up -- Try the SELECTs by hand until you have some confidence that they will deliver the desired results.  Only then try to turn it into a Stored Procedure.


SQL Server Licensing for training, small scale development

What I would do in your case is the following:

Download HyperV and install on your machine, details here: https://blogs.msdn.microsoft.com/virtual_pc_guy/2010/07/07/installing-windows-virtual-pc-on-windows-7-home-editions/
Download Windows Server 2016 evaluation edition here:
https://www.microsoft.com/en-us/evalcenter/evaluate-sql-server-2016
Set up a Virtual Machine using HyperV (this is a good skill to have anyway and is really easy to learn. Search online for a tutorial).
Install the Windows Server 2016 on the VM.
Download SQL Server 2016 developer edition.
Install Developer Edition on your new VM with Server 2016 and SQL Server 2016 and enjoy the benefits of the latest release of both products.

Remember afterwards that there is a bit of firewall configuration if you want to install SSMS on your local PC and connect to your SQL Server 2016 VM (also look this up on the web). SSMS is compatible with Windows 7 still, so you are OK to install locally and connect it to your SQL Server 2016 VM.

You may also require quite a bit of RAM etc for this approach. I had a 2011 laptop with 16GB of RAM and a SSD drive running 5 VMs on HyperV a while ago and it ran OK. Your mileage will be different depending on your PC specs.


As a BI consultant, my view on datawarehousing is that it provides (primarily) non-technical users with an easily accessible set of facts and dimensions.

Often, you'll see the following features in a data warehouse:

denormalized dimensions,
simplified fact tables and measures,
pre-aggregated balances,
pre-accumulated amounts over some time dimension,
often with some business logic (distributions, calculations, statistics) already applied to the data,
dimension data organized as a hierarchy where originally wasn't,
etc

All of this serves to simplify the understanding of, and accessibility to, the business data for people who aren't too comfortable writing SQL queries or using pivot table tools. Some of the greatest pitfalls in data warehousing and BI involve users not understanding the structure or meaning of the data, producing incorrect results and subsequently not trusting the BI solution.


You can do a AGEfrom you MIN date

CREATE tABLE "table1" (date_column timestamp)

INSERT INTO "table1" VALUES ( NOW())

SELECT AGE(MIN(date_column)) FROM "table1"

| age              |
| :--------------- |
| -12:07:06.199423 |

db<>fiddle here


Are there any things to consider before granting VIEW SERVER STATE and VIEW DEFINITION to Developers?

VIEW DEFINITION is a database permission.  There is a corresponding server permission VIEW ANY DEFINITION you can add.  If you want grant some logins access to view server state and any all object metadata, you can do it like this:
create server role developers
grant view any definition to developers
grant connect any database to developers
grant view server state to developers

You can grant SHOWPLAN for each database, or grant ALTER TRACE at the server level.  ALTER TRACE also allows the user to create SQL Traces which you might not want to allow developers to do on a production server.  This is all documented under Permissions.


Transform SELECT query to DELETE to remove extra taxonomy entries

If I unserstand the task correctly:

Delete "wrong" records

DELETE t1.*
FROM wp_term_relationships t1
WHERE t1.term_taxonomy_id IN (`none`,'low','medium','high')
  AND EXISTS ( SELECT NULL
               FROM wp_term_relationships t2
               WHERE t2.term_taxonomy_id IN ('none','low','medium','high')
                 AND t1.object_id = t2.object_id
                 AND t1.term_order > t2.term_order )

Forbid "wrong" records insertion:

ALTER TABLE wp_term_relationships
ADD COLUMN term_taxonomy_check VARCHAR(8)
           AS (CASE WHEN term_taxonomy_id IN (`none`,'low','medium','high')
                    THEN term_taxonomy_id END),
UNIQUE INDEX idx_term_taxonomy_check (object_id, term_taxonomy_check);


How to stop a PostgreSQL database with pg_ctl?

I had to change owner of those files to user postgres and then shut it down via that user:

$ sudo chown -R postgres .
$ sudo su
# su postgres
$ pg_ctl stop -D /Library/PostgreSQL/9.6/data
waiting for server to shut down.... done
server stopped


Copy table with separate sequences for serial columns

To create a copy as close as possible you must use INCLUDING ALL with CREATE TABLE .. (LIKE ..) since there can be any number of columns with defaults that you obviously want to copy.
You just want serial columns to get their own, independent sequence, which makes a lot of sense and probably should be the default behavior. For now, this should do the job:
Function to copy any given table
Copies any given table (must exist) with new given name and independent serial columns (if any).
Data is not included, it's trivial to copy that, too.
CREATE OR REPLACE FUNCTION f_copy_tbl(_tbl regclass, _newtbl text)
  RETURNS void AS
$func$
DECLARE
   _sql text;
BEGIN

-- Copy table
EXECUTE format('CREATE TABLE %I (LIKE %s INCLUDING ALL);', _newtbl, _tbl);

-- Fix serial columns, if any
SELECT INTO _sql
       string_agg('CREATE SEQUENCE ' || seq, E';\n') || E';\n'
    || string_agg(format('ALTER SEQUENCE %s OWNED BY %I.%I'
                        , seq, _newtbl, a.attname), E';\n') || E';\n'
    || 'ALTER TABLE ' || quote_ident(_newtbl) || E'\n  '
    || string_agg(format($$ALTER %I SET DEFAULT nextval('%s'::regclass)$$
                                 , a.attname, seq), E'\n, ')
FROM   pg_attribute  a
JOIN   pg_attrdef    ad ON ad.adrelid = a.attrelid
                       AND ad.adnum   = a.attnum
     , quote_ident(_newtbl || '_' || a.attname || '_seq') AS seq  -- new seq name
WHERE  a.attrelid = _tbl
AND    a.attnum > 0
AND    NOT a.attisdropped
AND    a.atttypid = ANY ('{int,int8,int2}'::regtype[])
AND    ad.adsrc = 'nextval('''
         || (pg_get_serial_sequence (a.attrelid::regclass::text, a.attname))::regclass
         || '''::regclass)'
;

IF _sql IS NOT NULL THEN
   EXECUTE _sql;
END IF;

END
$func$  LANGUAGE plpgsql VOLATILE;

Call:
SELECT f_copy_tbl('tbl', 'tbl1');

Produces and executes SQL code of the form:
CREATE TABLE tbl1 (LIKE tbl INCLUDING ALL);
-- only if there are serial columns:
CREATE SEQUENCE tbl1_tbl_id_seq;     -- one line per serial type ..
CREATE SEQUENCE "tbl1_Odd_COL_seq";  -- .. two in this example
ALTER SEQUENCE tbl1_tbl_id_seq OWNED BY tbl1.tbl_id;
ALTER SEQUENCE "tbl1_Odd_COL_seq" OWNED BY tbl1."Odd_COL";
ALTER TABLE tbl1
  ALTER tbl_id SET DEFAULT nextval('tbl1_tbl_id_seq'::regclass)
, ALTER "Odd_COL" SET DEFAULT nextval('"tbl1_Odd_COL_seq"'::regclass);

Only serial columns get their own sequence. Other column default are copied unchanged - including nextval() from a sequence that is not owned by the column or differs in any way from a serial.

The function is safe against SQL injection and should work with arbitrary table and column names.

SQL Fiddle.
It would be a useful option for the LIKE feature to create separate sequences for serial columns. You may want to post a friendly feature request to pgsql-general.


The name of your index suggests, that the index is on (status, created), correct? Then it's exactly what you mentioned.

An index works best, when it's as selective as possible, or in better words:

The closer the result of number of distinct values / total number of rows is to 1, the better is the index.

When you have very few distinct values, it's often cheaper to just scan the entire table, than to look at the index and then find the corresponding entry in the table. That's most probably the reason, the index isn't used. The fact, that you also filter by date, doesn't help much, because you have created as second column in the index.

The order of the columns in a combined index is very important. Always put the more selective columns first. In your case it might help (very much), if your index would be (created, status) instead (depending on how much rows per day you have and how much rows in total).


MySQL Job failed to start after adding "log = /var/log/mysql/mysql.log" to my.cnf

Just change this

log = /var/log/mysql/mysql.log

to this

general_log_file  = /var/log/mysql/mysql.log


Switching SQL Server service credentials to AD account

Use SQL Server Configuration Manager to change the service account used by the SQL Server services.  That will ensure any necessary changes are made to permissions required by that account.  See the MIcrosoft Docs for precise details.
Regardless of what you set the service account to, any local rights should be applied to NT SERVICE\MSSQLSERVER, as this is the per-service Virtual Account, and even after you change the service account, the SQL Server will retain any rights granted to it.

To pursue best practice for SQL Service accounts, I’m working through changing the SQL service account to be AD accounts for our existing SQL servers.

This is only a best-practice if you are using a Managed Service Account or Group Managed Service account, and only if

You've got a cluster and need Kerberos authentication, or

You've got multiple services on the SQL Server and and need to differentiate domain permissions between them.

Otherwise leave the default Virtual Account account and grant any domain permissions for accessing external resources (like file shares) to the machine account (eg MyDomain\MyServer$).
See

Managed service accounts, group managed service accounts, and virtual
accounts are designed to provide crucial applications such as SQL
Server with the isolation of their own accounts, while eliminating the
need for an administrator to manually administer the Service Principal
Name (SPN) and credentials for these accounts. These make long term
management of service account users, passwords and SPNs much easier.

https://docs.microsoft.com/en-us/sql/database-engine/configure-windows/configure-windows-service-accounts-and-permissions?view=sql-server-ver15#New_Accounts


How to index a composite primary key with non-sequential inserts?

If you are worried about fragmentation, do you have a maintenance solution on your server that addresses that such as SQL Server Maintenance Solution - Ola Hallengren?

Questions:

datetime data type is date, smalldatetime, datetime, datetime2(0-2),datetime2(3-4), datetime(5+) or?
LocationId data type is tinyint, smallint, int, bigint or ?
Character code data type is char(2), nchar(2) or?

More Questions:

How many rows are in your table?
Would you need an int or bigint for a surrogate id?
What are the other columns in your table?
Are all queries filtered by datetime, or are filtered by the locationid and/or character code?
Are you always returning the whole row, or sometimes just pieces of it?

Because most people assume the Primary Key is the Clustering Key, I am going to interpret your question as "How to index a composite clustering key with non-sequential inserts?".

The situation you are considering is related to debates like The Clustered Index Debate and Surrogate Key vs. Natural Key.

In this situation, I would want to consider the impact of a [16?] byte composite clustering key vs a 4 or 8 byte clustering key (int / bigint). My decision tree would look something like this:

Will we be using Hekaton (In-Memory OLTP)?

Yes => composite key. Run away.
No => Good call, continue...

How many rows will this table have?

Tens of millions, maybe more! => surrogate key (probably).

If the data length of each row will be variable and not narrow => surrogate key.
If the data length of the row will be fixed and narrow, and it results in optimal page usage => continue...

Less than that => continue...

How will the table be queried?

Various combinations of and not always all of datetime, location_id, character_code => surrogate key.

In this case you may want to be able to have multiple supporting indexes the combinations of datetime, location_id, character_code for your queries.
The clustering key is included in all non-clustered indexes, and the larger it is the more space/pages each index entry will require. => surrogate key.

Almost always by all three datetime, location_id, character_code or almost always by datetime or datetime, location_id;
but not by location_id without datetime and not character_code without datetime, location_id (zero or only a couple of non-clustered indexes on this table) => continue...

Will any other table reference this table?

Yes => surrogate key.
No => composite key is a reasonable option

Even if my first run through of the above decision tree leads me to a composite key, I would probably start my design using a surrogate key because it is easier to get rid of it (because it isn't being used) than to go back and add it and implement its use.

Just to clarify, I have had cases where I did find that the composite key was a better solution and did refactor the design to drop the surrogate key. I don't want to leave the impression that the surrogate key is always the better solution, even if it is a common default for many designers (including myself).

Reference:

A Simple Start – Table Creation Best Practices - Kimberly Tripp - concerning row size and page utilization
Ever-increasing clustering key – the Clustered Index Debate……….again! - Kimberly Tripp
The Clustered Index Debate Continues… - Kimberly Tripp
More considerations for the clustering key – the clustered index debate continues! - Kimberly Tripp
How much does that key cost? (plus sp_helpindex9) - Kimberly Tripp
101 Things I Wish You Knew About Sql Server - Thomas LaRock
SQL Server: Natural Key Verses Surrogate Key - Database Journal - Gregory A. Larsen
Ten Common Database Design Mistakes - Simple Talk - Louis Davidson
Hekaton (In-Memory OLTP) - dbareactions.com


Generating sequences partitioned or grouped by field(s)

Ultimately, the best solution will depend on the specific details of what you need, and how various solution perform in your environment.

First: using a SEQUENCE or a table of virtual "sequences" will result in behavior closer to ROW_NUMBER than to DENSE_RANK - with DENSE_RANK, if the value you're ordering on (say, a record creation date) is the same for three records in your partition, they'd all get the same rank, but they'd each get a unique row number.

Second, even using a SEQUENCE, you cannot guarantee that there will be no gaps in your numbering. If you grab the next value from the sequence, but end up rolling back the transaction, the sequence number is still consumed.

An additional concern in your situation is managing the sequences. You would presumably need one sequence for each group you created. Creating and using the sequences would require dynamic SQL. It might not be vulnerable to SQL injection (if all the dynamic values are system-generated rather than user-input-generated), but this could still be a concern for some.

Also, you'd need a way to ensure that the Grouping values were properly tied to the sequences. If those values are user-facing, then someone will want to change one at some point - if the sequence names depend on that user-facing value, then the original sequence would be lost after a change, and a new one would presumably have to be started (or, you'd have to include code to update the sequence name after a change). More practically, you might need a table with a Grouping ID, and the name of the corresponding SEQUENCE.

Using a table of "virtual sequences" lets you avoid some of these problems, but can create another, much larger one - blocking. If you include retrieving the next value from the virtual sequence and updating it in the transaction to create your record, then, if the transaction is rolled back, then so is the sequence number change; however, you can't add another record to the table until that first transaction is completed. I worked with a table like that, and almost every deadlock we encountered came back to that table. And, if you grab the next ID outside the transaction where you insert a new record, then you're back to maybe having gaps in the ID range.

Of course, you should consider that gaps can happen naturally, as well - whenever a record is deleted, there will be a gap. Are those gaps tolerated/desired, or does the business owner of the process really want to see 1, 2, 3, 4, 5, even if the rows are actually 1, 3, 4, 6, 7 (because 2 and 5 were deleted)?

So, work out what the actual reasoning behind the requirement is. If there is an absolute need to have the report show sequences with no gaps whatsoever, then ROW_NUMBER (or, possibly, if you want the same value for ties, DENSE_RANK) is probably the best solution available to you, and I'd check on just how much of a performance hit it causes. If some gaps (especially for deleted rows) should be retained, you almost have to go with one of the options you suggested above. If rollbacks are rare in your application (and I've seen some where that's true), a SEQUENCE is probably your best bet, performance wise. The "virtual sequence" table has to be handled very carefully, or it can be a major performance bottleneck; but, it can work (I should note that most of those deadlock issues I mentioned earlier occurred in jobs processing bulk data inserts, not in normal user activity in the application, and additional routines to grab a range of values instead of one at a time were created - however, using those increased the likelihood of a skipped values again).

Make sure the business owner requesting this understands that providing permanent sequence values with no gaps can have significant impact on performance, etc., and see if the requirement is really a must-have, or a nice-to-have (or, actually requires ROW_NUMBER or DENSE_RANK).


Once the database is detached you will probably not be able to attach the data files.  Since the file operation that occurred is an almost guaranteed data corruption I think your best option here is to perform a restore from a native SQL server backup or export of some kind, or recover any snapshots you might have taken of the environment.


Identity column re-seed: when it is necessary?

That is, when table row is deleted, it's PK must be reused in subsequent inserts.

What universe is your lecturer from??

That is grossly inefficient.  If you try to do that, you will cut your performance prospects down by a factor of 10.

If you need gapless numbers for auditing reasons, build them explicitly, not directly from database tools.  And never delete rows, but flag them as "deleted".  This will add to the messiness of queries, since they will have to ignore such rows.

In MySQL, InnoDB requires the existence of a unique PRIMARY KEY for each table.  But that is the extent of the requirement.  The key can even be a string.

Gaps are a convenience to the users and DBAs, not an inconvenience.

I can think of one case where gapless would be convenient -- chunking into groups of 100 rows at a time.  But there is a simple workaround using LIMIT 100,1.

Gaps have zero impact on performance.  That includes non-numeric indexes.  And non-unique indexes.  And composite indexes.

Sure, you can run out of ids.  I think I have seen it happen twice in nearly 2 decades of using MySQL.  I may as well worry about being struck by an asteroid.  It's low on my things-that-keep-me-awake-at-night list.

Gaps occur from (at least):  INSERT IGNORE, IODKU, REPLACE, DELETE, ROLLBACK (explicit, or due to crash), Multi-master replication (including Galera and Group Replication).  Do you really want to come up with workarounds for those?!

Feel free to have us sanity-check anything else that lecturer says that is suspicious.


How do you convert IBM DB2's recursive increment to MySQL version?

Recursive SQL Constructs of this nature do not exist in MySQL.

I know something like ths can be done in SQL Server 2005.

The only WITH operator in MySQL is the WITH ROLLUP modifier clause in GROUP BY functions.


Truncating and inserting in a safe transaction on MySQL

Since TRUNCATE TABLE does an implicit commit, don't use it.

As long as the table does not have foreign keys, you can do it quickly like this:

CREATE TABLE mytable_new LIKE mytable;
RENAME TABLE mytable TO mytable_old,mytable_new TO mytable;
INSERT INTO mytable ... ;

If the INSERT succeeds, then run this

DROP TABLE mytable_old;

If the INSERT rolls back, just revert back

RENAME TABLE mytable TO mytable_zap,mytable_old TO mytable;
DROP TABLE mytable_zap;

Give it a Try !!!


how to execute sql inside a column in postgres

You don't put the condition inside the procedure, you call a procedure only when the condition is true. The calling context should look something like

SELECT id, name, rs.*
FROM tests AS t
CROSS JOIN LATERAL myDynamic3ColExec(sql) AS rs
WHERE EXISTS (
      SELECT 1
      FROM     prod.listing
      WHERE    t.name = listing.source_system_name
      AND      source_record_status = 'active'
      AND      modified_on BETWEEN '2001-06-29'::TIMESTAMP AND                        ('2017-06-29'::TIMESTAMP - interval '24 hours')
      GROUP BY source_system_name
      HAVING Count(*) BETWEEN 500 AND 2000
);

Now you have to create a type, and create a function that returns the type given a sql statement as text.

CREATE TYPE mytype AS ( sid int, name text, sql text );

CREATE FUNCTION myDynamic3ColExec(sql text)
RETURNS setof mytype
AS $$
  RETURN QUERY EXECUTE sql;
$$ LANGUAGE plpgsql
VOLATILE;


In case the reasoning behind lower-case names wasn't clear, here's why: Postgres automatically and invisibly folds names down. So, all of these names are the same:

user_platform

USER_PLATFORM

UsEr_PlAtFoRm

They're all user_platform internally. Unless you put them in double-quotes:

"UserPlatform" is "UserPlatform". But to use this field or table, you would then need to put the name in double quotes everywhere you use it. It's a total pain. So, it's pretty standard to see names like

user_platform

With that said, as long as you don't use the double-quotes, you can write the name

User_Platform

if you find it easier to read.

As far as the naming convention goes, yes, pick one, stick to it and document your rules. You get 64 characters to work with (by default), which I haven't found to be an issue, except sometimes for index names.

For linking tables, whatever you like is fine. Linking users and platforms?

user_platform

user_link_platform

whatever you prefer.

As far as the singular vs. plural argument, it's a matter of taste. Some people care. A lot. The gutters run red with the blood of the unrighteous! I say do whatever you like. I'm of the make-table-names-singular school, but mostly because it's a habit that I picked up long ago.

Ah, one thing about the double-quotes...I do use them. We ship Postgres data to a visualization/analytics program called Domo. For that, it's nicer to have column (attribute) names in MixedCase. For that, I create views with "MixedCase" column aliases so that the data exports with a "MixedCase" name. This is a slightly exotic scenario, but maybe you'll run into something similar one day. Normally, I'd change the tables client-side, but the views were the easier solution.

Bonus tip! If you haven't discovered it already, Postgres text fields are case-sensitive. If you don't like that, you can:

Write your queries to deal with the data case-blindly. Every. Single. Query.
Use the standard citext extension. (Case Insensitive Text.)
Wait for PG12 to ship with case-insensitive collations. (Haven't tried it out yet.)

Forgot to mention...Postgres has unusually good support for commenting on system elements, including tables:

https://www.postgresql.org/docs/current/sql-comment.html

Someone has to look for and read your comments, of course, but it's another place that you can provide context. Even if that context is nothing but a link to a wiki you maintain, etc.


Is a temporary table a good solution for importing temporary data into a database

I see 2 ways to do this, your temporary table idea or running them through one at a time.  Which way to go depends on how many records we are talking about.  If its 3 or 4 names at a time then running them individually may be faster but if we are talking larger than 20 or so a temp table would be the way to go.

Overall the best way depends on your requirements.  I use temporary tables for things like this all the time, they are quick and easy and definitely not a "Wrong" choice.

My personal choice would be to write a program to have some one else do it but if that is not an option we see where we go.


How to Count Rows in a Separate Table Using a Foreign Key

A join of some type between the two tables is needed - and then group by customers.customer_id. Then to get results for customers with no orders, one way would be to use a LEFT join:

SELECT
    C.CUSTOMER_ID,
    COUNT(O.ORDER_ID) AS TotalOrders
FROM
        CUSTOMERS  C
    LEFT JOIN
        ORDERINFO  O
    ON
        O.CUSTOMER_ID = C.CUSTOMER_ID
GROUP BY
    C.CUSTOMER_ID ;


How to find the erroneous code failing the SSDT publish?

I would use RAISERROR WITH NOWAIT in the post-deployment script at the start of each block being executed. This way you should be able to see how far the deployment got before getting to the failure point and hopefully have narrowed it down to a particular script.

How I tend to structure my SSDT projects is that I have a single post-deployment script and then include other files using :r and separate them with GO's. This then leaves me with the ideal place to insert the RAISERRORS to pick up on progress. Alternatively you should stick the RAISERROR at the top of each included file.

https://docs.microsoft.com/en-us/sql/t-sql/language-elements/raiserror-transact-sql?view=sql-server-2017 key point in the link being:

  RAISERROR can be used as an alternative to PRINT to return messages to calling applications. RAISERROR supports character substitution similar to the functionality of the printf function in the C standard library, while the Transact-SQL PRINT statement does not. The PRINT statement is not affected by TRY blocks, while a RAISERROR run with a severity of 11 to 19 in a TRY block transfers control to the associated CATCH block. Specify a severity of 10 or lower to use RAISERROR to return a message from a TRY block without invoking the CATCH block.

Sorry - this is assuming the error is within your post-deployment script. After re-reading I've realised your error seems to be prior to that.


Fetch 10 paginated ranked posts of each category

Like Akina suggested, add row_number() and filter on the next query level:

SELECT ranking, id, full_count
FROM  (
   SELECT dense_rank() OVER w  AS ranking
        , p.id
        , count(*)     OVER () AS full_count
        , row_number() OVER w  AS rn
   FROM   p
   -- JOIN   c ON c.id = p.cid
   WINDOW w AS (PARTITION BY p.cid ORDER BY p.score DESC NULLS LAST)
   ) sub
WHERE rn BETWEEN 21 AND 30;

This return row 21 to 30 per window (per pid) - no rows if there are less than 21.

A subquery is typically faster than a CTE in Postgres (at least up to version 11). Only use CTEs where needed.

Also, if referential integrity is enforced with a foreign key constraint, the join to c adds nothing but cost. Drop it.

And since we are reusing the same window definition twice, a WINDOW clause avoids spelling it out repeatedly (no effect on performance, though).

That full_count looks odd. If you need the full count per window, make that count(*) OVER w as well.

Alternative for few groups

For a small number of groups (few distinct pid), a LATERAL join with index support is typically faster:

SELECT p.*
FROM   c
CROSS  JOIN LATERAL (
   SELECT dense_rank() OVER (ORDER BY p.score DESC NULLS LAST) AS ranking
        , p.id
        , count(*) OVER () AS full_count_per_window
   FROM   p
   WHERE  p.cid = c.id
   ORDER  BY p.score DESC NULLS LAST
   OFFSET 20
   LIMIT  10
   ) p
-- WHERE c.* ... -- some filter on c?

This time with a full count per window.
"Index support" would ideally be:

CREATE INDEX ON p (pid, score DESC NULLS LAST);

score DESC NULLS LAST is the perfect inversion of score ASC, so a simpler index on (pid, score) is also applicable using index scan backwards. See:

PostgreSQL sort by datetime asc, null first?

About that LATERAL join:

Optimize GROUP BY query to retrieve latest record per user
What is the difference between LATERAL and a subquery in PostgreSQL?

Generally, OFFSET is a poor tools for pagination. See:

What is the recommended way to join junction tables for efficient ordering/pagination?
Efficient pagination for big tables


Remove duplicates and connect relations to the first one

Well,
Apologies to all those SQL guru's - this might be a simplistic answer.

Since the product table will only have one record per product you don't need the ID column and can just use the 'sku' as the key.

Insert into that table a distinct list of the products
 e.g

INSERT INTO products (sku,ProductName)
SELECT DISTINCT (sku,ProductName)
FROM OldProductTable

That's your product table sorted.

How can you tell from your original data which price is the latest price? You have potentially two prices it could be. Lets presume the latest ID is the latest price.

Now you need to create a price table (as you have done) and create your foreign keys on it. You need some way of telling which is the current price though. One way is to give each record an iteration column, so the highest value in it is the latest price. Before you scream - this is a bit rubbish but is the only way I can see it fitting your data.

INSERT INTO ProductPrices (sku, PriceOld, PriceNew, iteration)
SELECT sku, PriceOld, PriceNew, ID
FROM OldProductTable

The primary key is sku and iteration. You want the latest price you just select the product and return the record with the highest iteration.

A better way to do it would be to have the concept of recording the price that was valid  date range:

Product ID,Price,StartDate,EndDate

Live prices will not have an end date (null). When you want to change a price you simply update the end date and add a new record with the start date being the date the new price comes in to effect.

That's just me though! Be happy to see other peoples views.


MS-SQL-Server 2019 is slower than SQL-Server 2008

From the query plan you provided it runs a minute:

But it compiles 33 minutes (1992254 ms ~= 1992 s ~= 60 * 33 = 33 minutes):

So basically you had a problem on 2008 already. It's just exaggerated in 2019. Never experienced such an extreme case in my life, but I would try to simplify the views you are using and drop not used indexes on underlying tables. Or maybe split the query: first select view into temp table and then use the temp table in your original query.


table_name.ibd file size is increasing ( MySQL 5.7.7 Enterprise) ( Innodb Engines)

Now that you have posted the top tablespace file sizes, here is my attempt to answer your questions:

Yes, regardless of what value you have for innodb_file_per_table the files ibdata & ib_logfile will be there. There can be multiple ibdata files depending on how you configure innodb_data_file_path. ib_logfile(s) can also be multiple depending on the value you use for innodb_log_files_in_group. To calculate how much size for log files is good for you, have a look at How to calculate a good innodb log file size

If your ib_logfiles are GBs, it will best to recalculate the size you really need (see link above) and set the size accordingly. ibdata* files are difficult to resize (the only way I know is to mysqldump the whole DB and reimport in a new installation).

You can calculate free tablespace that can be compacted by using this query:

SELECT table_name, (data_free)/power(1024,2) free_space_mb
FROM information_schema.tables
WHERE table_schema='[yourdb]'
ORDER BY free_space_mb DESC;

If you find any tables that have GBs worth of free space, then running OPTIMIZE on such tables should reduce the on-disk tablespace size.

If you have bigger disk/volume available that you want to take your datafiles to, use the following as a guide:

Stop MySQL service
Copy contents of your current data directory (usually /var/lib/mysql on CentOS/RHEL) to the new location
Make sure all folders and files on the new location where mysql files have been copied to, have owner AND group set to "mysql".
Edit /etc/my.cnf, set datadir to the new location where you copied mysql files to
Restart mysql service