ERROR 1067 (42000) at line 7: Invalid default value for 'LAST_UPDATED'

This error can occur when you attempt to import a file into MySQL Server 5.6.4 and earlier, when the database was created on and exported from MySQL 5.6.5 or later.

  As of MySQL 5.6.5, TIMESTAMP and DATETIME columns can be automatically initializated and updated to the current date and time (that is, the current timestamp). Before 5.6.5, this is true only for TIMESTAMP, and for at most one TIMESTAMP column per table."

  — https://dev.mysql.com/doc/refman/5.6/en/timestamp-initialization.html

It has been suggested that the workaround was to use TIMESTAMP rather than DATETIME for this column, since that appears to work, but that solution may fail for multiple reasons.  Additionally, though it gives the initial appearance of working for this specific table, MySQL Server versions before 5.6.5 have other limitations and implicit behavior for TIMESTAMP columns such that this workaround would not be usable in every case.

Notably, in all reasonably modern (5.x and up) versions of MySQL Server the TIMESTAMP and DATETIME data types are not fully interchangeable.  DATETIME columns support a broader range of values than TIMESTAMP and TIMESTAMP is subject to the database connection's notion of time zones, with automatic time zone conversions, while DATETIME is not.

Since the database was created on 5.6 or later, for optimum compatibility, you should be loading it onto a server that is also 5.6 or later.  Throughout the 5.x series, things are generally backward-compatible; that is, it is generally possible to restore a dump file on a later version of the server, but due to the introduction of new features, the reverse is not as true.

See also:

Time Zone Support
The DATE, DATETIME, and TIMESTAMP Types
Should I use DATETIME or TIMESTAMP?


How do I stop psql from displaying executed SQL?

There are two methods that can cause the query to be echoed.

With psql -E (or \set ECHO_HIDDEN) the queries that psql executes with client commands \command are echoed back at you. You can see if this is set by running \set and looking for ECHO_HIDDEN. If set, you should try running without -E, not setting the option yourself, and making sure it's not set in the ~/.psqlrc. If set the query will look like this above every client command

********* QUERY **********
query
**************************

If you see LOG:  statement:, you may have awkward configurations on the server, like

client_min_messages='LOG';
log_statement='all';

See the docs on Error Reporting and Logging for more information. You should check,

SHOW client_min_messages;
SHOW log_statement;

If set, this is an indicator that someone opened up the server-configuration file and wanted more logging but didn't know what they were doing.

In your case you have LOG:  statement: so check the server settings.


Associate BAK file with SQL Server database

This bak file is not recognized as belonging to this database

It may just be that you haven't performed a tail log backup on the target database.  But you should restore the backup and all the log files to a new database first to determine if it has the data you need.  Then you can either proceed with the restore, or manually copy the data from the new database.

  It was my assumption that I could use the LDF in conjunction with a BAK to reconstitute the database at any given point. Does it not work that way?

No.  The LDF will be overwritten in a restore.  You must move the transaction log records from the LDF to a log backup first.  That's why RESTORE over an existing database will fail if you haven't taken a Tail Log backup: otherwise you would lose data.

So before you start the restore sequence, ensure you've taken a Log Backup of the database.  This will move all the log records currently in the .LDF file into the log backup file and make them available for use in a point-in-time restore.


Reordering a postgresql timescaledb hypertable

We recommend instead to use TimescaleDB's reorder_chunk command (or a reorder policy).

CLUSTER will take an ACCESS EXCLUSIVE lock on the entire hypertable, which prevents any operations (reads or writes) from occurring during the entire cluster process (https://www.postgresql.org/docs/current/sql-cluster.html).

We re-implemented similar functionality in reorder_chunk to not acquire require such a heavy weight lock:  It operates chunk-by-chunk, and you can still read from the table/chunk while the reordering/clustering is occurring.

But to answer your other questions:

You can order in either timestamp order.  Define your index to be
clustered/reordered as having either timestamps DESC (descending) or ASC (ascending) order.
Reordering chunks can be really great for performance. You don't typically need just for timestamp if you are inserting data is loose timestamp order.
But we often see them in composites, so if you have a device &
timestamp, and you often do a query like:

  SELECT foo from table where device_id = X and timestamp > now() - interval '1 week'

then its very useful to reorder based on a composite index like (device_id, timestamp).

https://docs.timescale.com/latest/api#reorder_chunk
https://docs.timescale.com/latest/api#add_reorder_policy


Making a domain conditional on other values in the table

I wouldn't try to solve that with a DOMAIN since that cannot reference other columns. Use a CHECK constraint instead:

ALTER TABLE foo ADD CONSTRAINT foo_min_salary
CHECK (salary > 1000 OR hired < '2013-01-01' AND salary > 500);

Define the column NOT NULL in addition to disallow NULL values.
Using the IS0 8601 format for the date, which is valid with any locale and the recommended form.
Note how I inverted your logic to arrive at a simpler form.
I wrote hired < '2013-01-01' on purpose, suspecting that your phrase hired > January 1st, 2013 is off by one.
Be aware of operator precedence. AND binds before OR.

If you are building on a domain that already demands value > 500, you can simplify the CHECK constraint to:

ALTER TABLE foo ADD CONSTRAINT foo_min_salary
CHECK (salary > 1000 OR hired < '2013-01-01');


What is the best approach to run a DELETE query on 92K of 4 million rows?

92K out of 4M is still only 2.3% of your records. I'd suggest your first option, just throw what you want deleted at the database and let the database sort it out (if you give it to the database all at once, chances are it'll do it more efficiently than if you parcel it out). The only time I'd recommend chunking it is if your application is time-critical and you're worried about slowing it down (in which case the other solution is to do it off-hours).

If you were taking 20-30% or more of the records out of the table, I'd recommend dropping indexes, doing the delete, and recreating indexes, followed by an OPTIMIZE TABLE, but for 2% of the records in the table the index rebuild overhead isn't worth it (it's faster just to keep the indexes up-to-date while deleting the records).

That said, after you've done the delete, running OPTIMIZE TABLE might not be a bad idea - don't do that during peak hours though.


Creating independent databases will create the risk that as data structures change with the application, structural changes (and any associated data migrations) must be propagated to all of the databases. This can get tricky. A school_id in the tables that need it will simplify things in the sense that you only need to maintain one database.

Multiple databases would make more sense if you are going to deploy the application to physically separate clients (such as if the application runs independently on a server in the school), but will make maintenance more difficult.

Another place where it might make sense to have a separate database for each client would be if you know there will be slightly different versions of the data structures for each client. Of course, changes to common structures would still have to be propagated to all variant databases, and then you have to ensure that common patches/upgrades to the application work properly with the variants.

In general, I'd try to keep it all in one multi-tenant database, unless there is a very good reason to split it up.


So it turns out that confluence uses a different structure to manage this data and now the discrepancies appear to be resolved.

Here is the query:

select
    User_Name,
    First_Name,
    Last_Name,
    max(Last_Login) as Last_Login
FROM
(
SELECT
    u.user_name as User_Name,
    u.first_name as First_Name,
    u.last_name Last_Name,
    ifnull(from_unixtime((cast(a.attribute_value AS UNSIGNED)/1000), '%Y-%m-%d'), '1970-01-01') AS Last_Login
FROM jira.cwd_user u
  LEFT JOIN jira.cwd_user_attributes a ON u.id = a.user_id AND a.attribute_name = 'login.lastLoginMillis'
  JOIN jira.cwd_directory d ON u.directory_id = d.id
where u.active = 1
UNION
 SELECT
    u.user_name,
    u.first_name as First_Name,
    u.last_name as Last_Name,
    date_format(l.successdate, '%Y-%m-%d') as Last_Login
  FROM confluence.logininfo l
  JOIN confluence.user_mapping m ON m.user_key = l.username
  JOIN confluence.cwd_user u ON m.username = u.user_name
  JOIN confluence.cwd_directory d ON u.directory_id = d.id and u.active = 'T'
UNION
select
    u.user_name as User_Name,
    u.first_name as First_Name,
    u.last_name Last_Name,
    ifnull(from_unixtime((cast(sa.attribute_value AS UNSIGNED)/1000), '%Y-%m-%d'), '1970-01-01') AS Last_Login
FROM stash.cwd_user u
LEFT JOIN stash.cwd_user_attribute sa ON u.id = sa.user_id AND sa.attribute_name = 'login.lastLoginMillis'
JOIN stash.cwd_directory d ON u.directory_id = d.id
where u.is_active = 'T'
) as z
group by User_Name, First_Name, Last_Name
having (max(Last_Login)) <= date_sub(now(), interval 90 day)
order by User_Name asc, Last_Login desc


MySQL - Restore 1TB database using myloader

I think it is choking on 200 threads.  Recommend not using more than twice the number of CPU cores you have.  And even that may be too high...

In particular, there is no use adding more threads after saturating the I/O.  (I am not familiar with your configuration, so I cannot be more specific.)

innodb_buffer_pool_size =235G is dangerously high for 244GB of RAM.  If there is swapping, that could be killing performance.

If you have "indexed every column", that is another performance killer for loading.  And "indexing every column" is usually useless.  Instead, discover what indexes you need, and have only them.  This often includes 'composite' indexes.

Also, multiple UNIQUE (including PRIMARY) indexes on a table is costly.


Linked Server Query is not working in SQL Agent job

When you run the SQL Server Agent job, it executes the query using the credentials of the account for which the SQL Server Agent service is running.

For linked servers and utilizing Windows Authentication, it has been my experience that it is necessary to set up the Keberos authentication for the service accounts in Active Directory, and set the Service Provider Name (SPN) by running the SETSPN command for the SQL Server service account for the SQL Server machine. See this article: https://technet.microsoft.com/en-us/library/bb735885.aspx

If you are using the SQL Authentication, I would recommend trying to log in using the account that the SQL Server Agent service uses and seeing if the query utilizing the Linked Server works correctly.

I found this code a while back, and I don't remember exactly where. So, I can't give proper credit. It should generate the code to set the SPNs if they aren't done correctly.

SET NOCOUNT ON

-- service account
DECLARE @DBEngineLogin VARCHAR(100)

EXECUTE master.dbo.xp_instance_regread
   @rootkey = N'HKEY_LOCAL_MACHINE',
   @key = N'SYSTEM\CurrentControlSet\Services\MSSQLServer',
   @value_name = N'ObjectName',
   @value = @DBEngineLogin OUTPUT

-- SELECT [DBEngineLogin] = @DBEngineLogin

DECLARE @physicalServerName varchar(128) = '%' + cast(serverproperty('ComputerNamePhysicalNetBIOS') as varchar(64))+ '%'
DECLARE @ServerName varchar(128) = '%' + cast(SERVERPROPERTY('MachineName') as varchar(64)) + '%'
DECLARE @spnCmd varchar(265)

SET @spnCmd = 'setspn -L ' + @DBEngineLogin
CREATE TABLE #spnResult (output varchar(1024) null)
INSERT #spnResult exec xp_cmdshell @spnCmd

CREATE TABLE #spnLIst (output varchar(1024) null)

INSERT #spnLIst
SELECT output as 'SPN List for Service Account' FROM #spnResult
WHERE output like @physicalServerName or output like @ServerName

Declare @NodeName VARCHAR(128)
DECLARE db_cursor CURSOR FOR
SELECT '%' + NodeName + '%' AS NodeName FROM fn_virtualservernodes()

OPEN db_cursor
FETCH NEXT FROM db_cursor INTO @NodeName

WHILE @@FETCH_STATUS = 0
BEGIN
   INSERT #spnLIst
   SELECT output as 'SPN List for Service Account' FROM #spnResult
   WHERE output like @NodeName

   FETCH NEXT FROM db_cursor INTO @NodeName
END

CLOSE db_cursor
DEALLOCATE db_cursor

SELECT DISTINCT output as CurrentSPNRegisterStatus INTO #spnListCurrent FROM #spnLIst

TRUNCATE TABLE #spnLIst

-- GET Port Number
DECLARE @PortNumber varchar(10)
SELECT @PortNumber = cast(local_tcp_port as varchar(10))
FROM sys.dm_exec_connections WHERE session_id = @@SPID

-- GET FQDN
DECLARE @Domain NVARCHAR(100)
EXEC master.dbo.xp_regread 'HKEY_LOCAL_MACHINE', 'SYSTEM\CurrentControlSet\services\Tcpip\Parameters', N'Domain',@Domain OUTPUT

INSERT #spnLIst
SELECT 'MSSQLSvc/' + cast(serverproperty('ComputerNamePhysicalNetBIOS') as varchar(64)) + ':' + @PortNumber
UNION ALL
SELECT 'MSSQLSvc/' + cast(serverproperty('ComputerNamePhysicalNetBIOS') as varchar(64)) + '.' + @Domain  + ':' + @PortNumber
UNION ALL
SELECT 'MSSQLSvc/' + cast(SERVERPROPERTY('MachineName') as varchar(64)) + ':' + @PortNumber
UNION ALL
SELECT 'MSSQLSvc/' + cast(SERVERPROPERTY('MachineName') as varchar(64)) + '.' + @Domain + ':' + @PortNumber

-- If this serve is clusterd, need to check for all Physical nodes
IF SERVERPROPERTY('IsClustered') = 1
BEGIN

   INSERT #spnLIst
   SELECT 'MSSQLSvc/' + NodeName + ':' + @PortNumber
   FROM fn_virtualservernodes()

   INSERT #spnLIst
   SELECT 'MSSQLSvc/' + NodeName + '.' + @Domain  + ':' + @PortNumber
   FROM fn_virtualservernodes()

END

IF NOT EXISTS(SELECT CurrentSPNRegisterStatus FROM #spnListCurrent)
   SELECT 'NO SPN has been registered' as CurrentSPNRegisterStatus
ELSE
   SELECT CurrentSPNRegisterStatus FROM #spnListCurrent

SELECT
   CASE
      WHEN A.CurrentSPNRegisterStatus is NULL THEN '*Missing SPN - See SPNGenerateCommandLine'
      ELSE A.CurrentSPNRegisterStatus END AS 'CurrentSPNRegisterStatus',
   CASE
      WHEN B.output IS NULL THEN '*** Review for Remove or you have multiple instance ***'
   ELSE B.output end as SuggestSPNList,
   CASE
      WHEN B.output is null THEN
          'SETSPN -D ' + A.CurrentSPNRegisterStatus + ' ' + @DBEngineLogin
   ELSE 'SETSPN –S ' + output + ' ' + @DBEngineLogin END as SPNGenerateCommandLine
FROM #spnListCurrent A
FULL OUTER JOIN #spnLIst B on REPLACE(A.CurrentSPNRegisterStatus,CHAR(9),'') = B.output
WHERE CurrentSPNRegisterStatus is NULL OR B.output IS NULL

IF @@ROWCOUNT = 0
 SELECT 'All SPN has been registered correctly. If you are running for AG Group, this script does not check so please check manually' as SPNStatus

DROP TABLE #spnResult
DROP TABLE #spnLIst
DROP TABLE #spnListCurrent
GO


sp_recompile does not recompile inline TVF. What alternatives are there other than using an ALTER statement?

Using SELECT * in production code is evil, for a number of reasons.

If you absolutely must do this, the procedure you're looking for is sp_refreshsqlmodule, which works much like the built in sp_refreshview, rather than sp_recompile.


Arrange SQL Server rows in a paired sequence

You can use this ORDER BY:

SELECT  ...
FROM ...
ORDER BY
    CASE checkpoint WHEN 'A' THEN id
                    WHEN 'B' THEN related_id
    END,
    checkpoint ;


Maximum number of fields/columns in MongoDB

This depends of name space or the storage for each name of each field, MongoDB by default only available 16mb for each documents, including name space (field in documents, database name, collection name)

Read this: limits namespaces


Oracle asm disk string profile string vs param string

asm_diskstring can be set in the ASM parameterfile and in the GPnP profile as well. Grid Infrastructure needs to know where to search for ASM disks before starting ASM and reading the ASM parameterfile, the GPnP profile makes that possible.

dsget

dsget
        Retrieves the discovery diskstring value that is
        used by the Oracle ASM Instance and its clients

Synopsis
        dsget [ --normal | --profile [ -f ] | --parameter ]

Description
        The options for the dsget command are described below.

        --normal        - Retrieves the discovery string from the Grid Plug
                          and Play (GPnP) profile and the one that is set in
                          the Oracle ASM instance. It returns one row each for
                          the profile and parameter setting. This is the
                          default setting.

        --parameter     - Retrieves the ASM_DISKSTRING parameter setting of
                          the Oracle ASM instance.

        --profile       - Retrieves the discovery string from the GPnP
                          profile.

        -f              - If -f is specified, retrieves the discovery string
                          from the local GPnP profile.  This parameter
                          can only be specified if --profile is specified.

Examples
        The following example uses dsget to retrieve the current discovery
        diskstring value from the GPnP profile and the ASM_DISKSTRING parameter.

        ASMCMD [+] > dsget
        profile: /devices/disk*
        parameter: /devices/disk*


Basically it is just for programmer convenience since you can just add additional conditions with AND... after that and it has no impact on execution time.

Check out these links to Stackoverflow:

Why would someone use WHERE 1=1 AND  in a SQL
clause?
“where 1=1”
statement

Note that WHERE 1 is identical to WHERE 1=1; both mean WHERE TRUE but the former is rejected by many database management systems as not really being boolean.


For the first question, you can't get data that isn't stored in your system. There is no way to know the date from which each row was inserted or updated if you don't have it stored somewhere.

For the second question, you can create a new column with a default constraint that will automatically set the current datetime value (if it wasn't supplied by the client), or an AFTER INSERT trigger that updates this column also. On both cases you need a column to store this value.

ALTER TABLE SchemaName.YourTable ADD CreatedDate DATETIME

Using a constraint with default value:

ALTER TABLE SchemaName.YourTable ADD CONSTRAINT DF_SchemaNameYourTable_CreatedDate
DEFAULT GETDATE() FOR CreatedDate

Using a trigger:

CREATE TRIGGER SchemaName.utrYourTableUpdateCreatedDate ON SchemaName.YourTable
    AFTER INSERT
AS
BEGIN

    UPDATE T SET
        CreatedDate = GETDATE()
    FROM
        inserted AS I -- special table that holds only new inserted rows (in this case)
        INNER JOIN SchemaName.YourTable AS T ON I.KeyColumn = T.KeyColumn

END


Multi-row insert in many to many relationship in PostgreSQL

You can obtain a Cartesian product using a LATERAL JOIN.

Have a look at Postgres docs.

select u.id, c.id
from   r_users u
left join lateral (select id from r_codes where id in (1, 2, 3, 5)) c on true
where  u.id in (1,2,4);

id | id
-: | -:
 1 |  1
 1 |  2
 1 |  3
 1 |  5
 2 |  1
 2 |  2
 2 |  3
 2 |  5
 4 |  1
 4 |  2
 4 |  3
 4 |  5

insert into user_code
select u.id, c.id
from   r_users u
left join lateral (select id from r_codes where id in (1, 2, 3, 5)) c on true
where  u.id in (1,2,4);

12 rows affected

select * from user_code;

user_id | code_id
------: | ------:
      1 |       1
      1 |       2
      1 |       3
      1 |       5
      2 |       1
      2 |       2
      2 |       3
      2 |       5
      4 |       1
      4 |       2
      4 |       3
      4 |       5

db<>fiddle here

As @Lennart has pointed out, in this case you can get the same result by using left join.

insert into user_code
select    u.id, c.id
from      r_users u
left join r_codes c
on        c.id in (1, 2, 3, 5)
where     u.id in (1,2,4);

12 rows affected

select * from user_code;

user_id | code_id
------: | ------:
      1 |       1
      1 |       2
      1 |       3
      1 |       5
      2 |       1
      2 |       2
      2 |       3
      2 |       5
      4 |       1
      4 |       2
      4 |       3
      4 |       5

db<>fiddle here


Join multiple PostgreSQL tables that have both common and individual columns

Community wiki answer:

SELECT * FROM a
NATURAL JOIN b
NATURAL JOIN c
...
NATURAL JOIN z;

...will give you a result, without any prior knowledge of column names.

I got it to work as intended by writing

SELECT * INTO new_table
FROM a NATURAL FULL OUTER JOIN b;

I needed the FULL OUTER part for the rows to actually be inserted into the new table.

This can be done for X number of tables in the following way, as described in this answer:

CREATE TABLE new_table ();
DO $$DECLARE r record;
BEGIN
    FOR r IN SELECT table_name FROM information_schema.tables
         WHERE table_schema = 'public' AND table_type = 'BASE TABLE' AND table_name != 'new_table' AND table_name != 'spatial_ref_sys'
    LOOP
        EXECUTE 'CREATE TEMP TABLE temp_table AS SELECT * FROM ' || quote_ident(r.table_name) || ' NATURAL FULL OUTER JOIN new_table';
        EXECUTE 'DROP TABLE new_table CASCADE';
        EXECUTE 'CREATE TABLE new_table AS SELECT * FROM temp_table';
        EXECUTE 'DROP TABLE temp_table CASCADE';
        EXECUTE 'DROP TABLE ' || quote_ident(r.table_name) || ' CASCADE';
    END LOOP;
END$$;


Array of one value, for every integer in another field?

Figured this out if anyone comes across this and wants the solution:

SELECT color, TRIM(TRAILING ',' FROM GROUP_CONCAT(REPEAT(CONCAT(length,','),qty) SEPARATOR '') )
FROM buyer_advert
GROUP BY color

This makes a nice array like I wanted.


Personally, I don't like MERGE because there are many unresolved bugs:

This blog post (scroll down to "Other MERGE issues")
This answer by AlexKuznetsov
These posts on Paul White's blog
I published a cautionary tip about MERGE here, including references to several bugs that haven't been addressed.
And this list may not even be exhaustive - given the number of bugs discovered by this small set of users, I have to wonder how many haven't been discovered yet?

MERGE also gives a false sense of security in terms of optimistic concurrency and race conditions. See Dan Guzman's blog post for more details.

I'm not trying to be a fear-mongerer here. But I also find the syntax unintuitive and daunting. So I would only use it in cases where it's actually needed and I can prove that I'm not affected by any of the above issues. I don't know what I would ever possibly gain from using it for an operation that could only ever end in an UPDATE anyway.

So here is how I would do it instead, using syntax that is much more familiar to me:

;WITH s AS
(
  SELECT VisitorID, UserIpAddress FROM
  (
    SELECT
      VisitorID,
      UserIpAddress,
      rn = ROW_NUMBER() OVER (PARTITION BY VisitorID ORDER BY CreateDate DESC)
    FROM dbo.VisitorSession
    WHERE UserIpAddress IS NOT NULL
    AND CreateDate > @dt
  ) AS x
  WHERE rn = 1
)
UPDATE c
  SET c.UserIpAddress = s.UserIpAddress
  FROM dbo.ShoppingCart AS c
  INNER JOIN s
  ON c.VisitorID = s.VisitorID;

You can also break this operation into chunks to reduce the impact on the transaction log which may, in turn, reduce the overall duration. I blogged about this here.

Here is how I would handle that approach:

DECLARE
  @dt DATE = DATEADD(MONTH, -6, SYSDATETIME()),
  @rc INT = 1;

WHILE @rc > 0
BEGIN

  BEGIN TRANSACTION;

  ;WITH s AS
  (
    SELECT TOP (100000) VisitorID, UserIpAddress FROM
    (
      SELECT
        VisitorID,
        UserIpAddress,
        rn = ROW_NUMBER() OVER (PARTITION BY VisitorID ORDER BY CreateDate DESC)
      FROM dbo.VisitorSession AS s
      WHERE UserIpAddress IS NOT NULL
      AND CreateDate > @dt
      AND EXISTS
      (
        SELECT 1 FROM dbo.ShoppingCart AS c
          WHERE c.VisitorID = s.VisitorID
          AND (c.UserIpAddress <> s.UserIpAddress
          OR c.UserIpAddress IS NULL)
      )
    ) AS x
    WHERE rn = 1
  )
  UPDATE c
    SET c.UserIpAddress = s.UserIpAddress
    FROM dbo.ShoppingCart AS c
    INNER JOIN s
    ON c.VisitorID = s.VisitorID;

  SET @rc = @@ROWCOUNT;

  COMMIT TRANSACTION;
END

Of course, as the blog post illustrates, you can gain just about as much time by making sure your log is big enough to handle the entire transaction without having to grow - most of the delay is likely coming from many, many autogrow operations accommodating your large transaction. Sadly until you've done the operation once it can be very difficult to try and guess how much transaction log you'll need...


Lets take your questions one by one :

  Is it possible to do the following:Have a copy of the database on the same SQL Server Instance
  Yes it is possible. You just have to restore the database main-db.bak with move.

e.g. Provided you dont have secondary data files, below will work

-- find logical names

restore filelistonly from disk = 'path\main-db.bak'
go
restore database main_db_copy
from disk = 'path\main-db.bak'
with recovery, stats = 10,
-- below logical data and log file names will be found from the filelistonly mentioned above.
move 'logicalDataFileName' to 'PhysicalPath\main-db_data.mdf',
move 'logicalLogFileName' to 'PhysicalPath\main-db_log.ldf'

  Create a new empty database

No need to first create an empty database, as the restore with move will create it.

  Restore the database from main-db.bak file and not overrite anything else that may be using the same data MDF and LDF files?

Same as above. As restore with move will not over write unless you use restore with REPLACE explicitly to overwrite.

  Or would it be easier to reproduce this database from a live running database copy so that it is not needed to restore if from backup?

Cannot understand what you mean by this ! If you want to create a new database or copy of the current database , you have to take a backup and then use restore database to restore it.

  Is it mandatory to rename the LDF and MDF files when the database is restored, can I script to automate the majority of the work?

If you want to overwrite the existing one, then no. If not and you want to create a copy of existing one, then Yes - you have to rename the mdf and ldf files.

You can fully automate the restore process. Some examples can be found here and here.


Programatically copying data from table from offline database to online database

The key concept you need to study is called "Replication".


Altering multiple tablespaces at once in Oracle

The syntax for the alter tablespace statement only supports one tablespace at a time, you can't list more than one as you're trying to.

If you're scripting that command already, simply have your script issue multiple alter tablespace statements rather than one.

If you're generating your list of tablespaces from dba_tablespaces, you can dynamically alter the tablespaces from that with something like:

begin
  for tbs in (select tablespace_name from dba_tablespaces where [your conditions])
  loop
    execute immediate 'alter tablespace '||tbs.tablespace_name||' read only';
  end loop;
end;
/


How to design SQL schema that treats historical data as first-class citizen?

Trying to do FKs will just frustrate you - you don't actually need it.

If you handle versioning by using an InsertDateTime concept, then you're basically describing a Type2 dimension as used by many data warehouses. There is quite a lot of material out there about tuning systems that sit over the top of data warehouses, but from a T-SQL perspective, consider using APPLY. Like this:

SELECT ...
FROM ... AS t
CROSS APPLY (
    SELECT TOP (1) ...
    FROM ... AS rev
    WHERE rev.UID = t.UID
    AND rev.InsertDateTime < @revdatetime
    ORDER BY rev.InsertDateTime DESC) AS something
WHERE something.IsDeleted = 'N'

This kind of construct benefits from an index on (IsDeleted, UID, InsertDateTime DESC).

Alternatively, if you want to look at the whole table as at a particular time, use ROW_NUMBER() like:

WITH numbered AS
 (  SELECT *, ROW_NUMBER() OVER (PARTITION BY UID ORDER BY InsertDateTime DESC) AS rownum
    FROM ...
    WHERE InsertDateTime < @revDateTime
),
VersionAtTime AS
(   SELECT *
    FROM numbered
    WHERE rownum = 1
    AND IsDeleted = 'N'
)
SELECT ...
FROM VersionAtTime
....

(edited from original to move the IsDeleted predicates outside the sub-queries, removing all versions at that particular time)


No index used warning in WHERE column IS NULL

You should create and index anyway.

Run the following query:

SELECT COUNT(1) rowcount,my_column FROM mTable GROUP BY my_column
UNION SELECT COUNT(1) rowcount,'Whole Table' FROM mTable;
SELECT DISTINCT my_column 'Cardinality' from mTable;

If the number of rowcount for NULL is more than 5% of Whole Table, an index might not be used be the Query Optimizer if your WHERE clause is AND my_column IS NULL or AND my_column IS NOT NULL (especially if the number of rows with NULL is about the same as the number of rows with non-NULL). If the cardinality of my_column is very low (indicating a small number of unique values) an index on my_column may never be be used at all.

That warning you mentioned may go away if you create the index on my_column, but the index may never be used if the circumstances I mentioned apply.

The index may be of value if NULL my_column is like 90% of the table.

My recommendation is not to make one at this time.


Gotchas converting latin1_swedish_ci to utf8mb4_unicode_ci

I assume you will do this for each table?
ALTER TABLE tbl CONVERT TO CHARACTER SET utf8mb4;

One potential issue involves the maximum of 767 bytes per column in an INDEX.  If you currently have a VARHAR(255) latin1 field in an index, you will need to rethink it.

Decrease it to VARCHAR(191) if you are sure that 191 will suffice (into the future)
Use a 'prefix' index:  INDEX(foo(191)).  This is often not worth doing.
DROP the index.  (But not if you need the index; let's see the SELECT that needs it.)

Edit

For shrinking to VARCHAR(191), you could write a Stored Procedure to look in information_schema to discover all the VARCHARs, and create and execute SELECT MAX(CHAR_LENGTH(col)) ... to see if any are over 191 (or even dangerously close to 191).  You might be surprised to find a few with max=255, implying that you may already have truncated some text.

The other two options won't damage data, but could have performance problems.


Oracle: Casting timestamp as date and comparing result

Casting to a DATE datatype from a TIMESTAMP only removes the fractional seconds, therefore your direct to_date('20190516', 'yyyymmdd') comparison is rightly failing. It's just a display issue that has confused you.

Definition of the Oracle DATE datatype (from here):

  Valid date range from January 1, 4712 BC to December 31, 9999 AD. The
  default format is determined explicitly by the NLS_DATE_FORMAT
  parameter or implicitly by the NLS_TERRITORY parameter. The size is
  fixed at 7 bytes. This datatype contains the datetime fields YEAR,
  MONTH, DAY, HOUR, MINUTE, and SECOND. It does not have fractional
  seconds or a time zone.

This DB Fiddle demonstrates.

These queries probably demonstrate best:

-- cast to date, and format with TO_CHAR to show that the time element still exists
select  TO_CHAR(
               cast(to_timestamp('20190516 08:00:00', 'yyyymmdd HH24:MI:SS') as date)
                ,'yyyymmdd HH24:MI:SS') from dual;

-- demonstrate that the fractional element gets removed
select cast(
         cast(to_timestamp('20190516 08:00:00.123', 'yyyymmdd HH24:MI:SS.FF')
              as date)
       as timestamp) from dual;


How to delete global variables set in PostgreSQL

This is not really a variable, but a “placeholder parameter”, and once you have set it, there is no way to “undefine” it for the lifetime of the database session.

Using it as a variable is actually abusing the concept, which is to allow specifying extension parameters in postgresql.conf even before the extension is loaded.

You could set the parameter to an empty value (or some other special value) to indicate that you want to unset it.

Alternatively, consider storing such variables in a temporary table, which will automatically be deleted as soon as the database session is done.


Count bookings and cancellations per member (select in subquery)

I hope that query is self-explaining enough.

SELECT u.name AS `name`
     , IFNULL( z.bookings, 0 ) AS bookings
     , IFNULL( q.cancellations, 0 ) AS cancellations
  FROM user AS u

  LEFT JOIN
     (
       SELECT uv.user_id AS user_id
            , COUNT(*) AS `bookings`
         FROM user_visit AS uv
        WHERE uv.type = 'BOOKED'
        GROUP BY uv.user_id
     )
     AS z
     ON z.user_id = u.id

  LEFT JOIN
     (
       SELECT uv.user_id AS user_id
            , COUNT(*) AS `cancellations`
         FROM user_visit AS uv
        WHERE uv.type = 'CANCELED'
        GROUP BY uv.user_id
     )
     AS q
     ON q.user_id = u.id
;


Failure notification with multiple profiles

SQL Server will use the default mail profile on alerts. On the otherside if you need to specify a special mailaccount or some other error handling, just create a step in your job which will be executed in the case of an error which uses sp_sendDbMail to send the alert.

If you just want to change the alert sender for ALL of your jobs, you can change it by right clicking your sql server agent, select properties, alert system and there you go. See here.


Remove a character from a records enclosed between double quotes

Well, this is really awful, but okay, if you are going to refuse to even consider better alternatives... first, create a set-based split function that will track the order of the string (your looping function is really not optimal):

CREATE FUNCTION [dbo].[SplitStrings_Ordered]
(
    @List       VARCHAR(8000),
    @Delimiter  VARCHAR(255)
)
RETURNS TABLE
AS
    RETURN (SELECT [Index] = ROW_NUMBER() OVER (ORDER BY Number), Item
    FROM (SELECT Number, Item = SUBSTRING(@List, Number,
      CHARINDEX(@Delimiter, @List + @Delimiter, Number) - Number)
     FROM (SELECT ROW_NUMBER() OVER (ORDER BY [object_id])
      FROM sys.all_columns) AS n(Number)
      WHERE Number <= CONVERT(INT, LEN(@List))
      AND SUBSTRING(@Delimiter + @List, Number, LEN(@Delimiter)) = @Delimiter
    ) AS y);

(If you have a numbers table in this database, use that instead of sys.all_columns, and add WITH SCHEMABINDING to the function definition.)

Now, let's look at a few examples of strings with commas embedded inside double quotes, and removing those before splitting and re-concatenating:

DECLARE @x TABLE(n VARCHAR(8000));

INSERT @x VALUES
('0150566115,"HEALTH 401K","IC,ON","ICON HEALTH 401K",,,1,08/21/2014'),
('0150566115,HEALTH 401K,"IC,ON","ICON HEALTH 401K",,,1,"08/21/2014"'),
('"01505,66115,","HEALTH 401K","IC,ON","ICON HEALTH 401K",,,1,08/21/2014');

;WITH x AS
(
  SELECT x.n, s.[Index], s = REPLACE(s.Item, ',',
    CASE s.[Index]%2 WHEN 0 THEN '' ELSE ',' END)
  FROM @x AS x
  CROSS APPLY dbo.SplitStrings_Ordered(x.n, '"') AS s
)
SELECT x.n, fixed = (SELECT x2.s
  FROM x AS x2
  WHERE x2.n = x.n
  ORDER BY [Index]
  FOR XML PATH, TYPE).value(N'.[1]',N'varchar(max)')
FROM x
GROUP BY x.n;

Results in the fixed column for all three strings:

0150566115,HEALTH 401K,ICON,ICON HEALTH 401K,,,1,08/21/2014
0150566115,HEALTH 401K,ICON,ICON HEALTH 401K,,,1,08/21/2014
0150566115,HEALTH 401K,ICON,ICON HEALTH 401K,,,1,08/21/2014

Now, you can feed those results back into the split function, using the comma this time, depending on your ultimate goal. The question seemed to resolve only around being able to ignore the double quotes and any commas contained only inside double-quote pairs.

For more on splitting and concatenating strings:

http://sqlperformance.com/2012/07/t-sql-queries/split-strings
http://sqlperformance.com/2012/08/t-sql-queries/splitting-strings-follow-up
http://sqlperformance.com/2012/08/t-sql-queries/splitting-strings-now-with-less-t-sql
http://sqlperformance.com/2014/08/t-sql-queries/sql-server-grouped-concatenation
http://sqlperformance.com/2014/08/t-sql-queries/sql-server-grouped-concatenation-2
https://www.sentryone.com/blog/aaronbertrand/grouped-concatenation-use-cases

For more on numbers tables and generating sets without loops:

Why should I consider using an auxiliary numbers table?
Why are numbers tables "invaluable"?
http://sqlperformance.com/generate-a-set-1
http://sqlperformance.com/generate-a-set-2
http://sqlperformance.com/generate-a-set-3


How to optimize MariaDB database when it is serving queries

Do you want to optimize MariaDB tables or do you want to run OPTIMIZE TABLE on MariaDB? Because those are 2 things that have nothing to do with each other.

Optimizing, in general, is not a process as easy as running a command- you need to analyze your server, profile your queries, change your schema, check your configuration; select, dimension and configure your hardware and os correctly, and use it efficiently from your application.

OPTIMIZE TABLE is a command that -to summarize-, for MyISAM and InnoDB MySQL/MariaDB engines, it defragments your table- in most cases with no real impact on performance, although it can save space under certain circunstances. Please do not execute optimize table regularly, it serves no purpose in most cases, while creating a lot of work for the servers (it basically recreates the table).

Online alter table is only possible on MySQL >=5.6+, and equivalent versions of MariaDB. OPTIMIZE TABLE basically runs ALTER TABLE <table> ENGINE=InnoDB for InnoDB tables, which is known to be able to be online since 5.6.17 for MySQL. I cannot say for sure for MariaDB 10+, as it will depend on the exact version, but that is as easy to check as forcing it to be online with the LOCK=NONE parameter, which would fail immediately otherwise.

If for any reason you cannot run a table defragmentation online, you can always use pt-online-schema change and perform a null operation, which will copy the new table without (almost) any locking.


Active directory automatic login to SSRS

This may be a simple matter of Internet Explorer not automatically detecting server2.newdomain.net as an "intranet" site. By default, IE will only do automatic Windows authentication in the intranet zone.

To see if this is the case, you can add "server2.newdomain.net" to the list of trusted intranet sites. Open IE, and do this:

Go to Tools, Internet Options.
Go to the Security tab, click Local intranet, and click the Sites button.
Click Advanced to open the list of sites.
Enter the full name ("server2.newdomain.net") into the appropriate box, and click Add.

If this gets automatic login working, then you'll have to decide how you want to solve the issue. You can specify a list of sites for the trusted intranet zone via Group Policy, but this prevents users from being able to add or remove anything on that particular list. If there's a cleaner way to do it, I'd be interested in knowing too, as we run into this a lot with intranet sites on various DNS domains.


How can I query for a sequence of values in a table

just 9 joins
if ID is indexed the join will be efficient
if you have gaps in ID that needs to be compressed then this does not work

select row1.*
from table row1
join table row2
      on row2.id = row1.id + 1
     and row1.value = 0
     and row2.value = 0
join table row3
      on row3.id = row1.id + 2
     and row3.value = 0
join table row4
      on row4.id = row1.id + 3
     and row4.value = 0
...
join table row10
      on row10.id = row1.id + 9
     and row10.value = 0

if no negative values

 select row1.id
   from table row1
   join table row2
         on row2.id >= row1.id
        and row2.id <= row1.id + 10
  group by row1.id
 having sum(row2.value) = 0


Intentionally corrupting an index in PostgreSQL

I think @Craig's comments are more important, addressing the intentions behind your question. In any case, to answer the question asked:

One simple way (among many others) would be to fake an IMMUTABLE function and base an index on it like outlined in this answer from yesterday:

CREATE OR REPLACE FUNCTION f_fake_immutable_ts()
  RETURNS timestamp LANGUAGE sql COST 1 IMMUTABLE AS
$$SELECT timestamp '2015-07-25 01:00'$$;

Create a partial index based on it:

CREATE INDEX tbl_created_at_idx ON tbl (tbl_id)
WHERE  created_at > f_fake_immutable_ts();

The index relies on the function to always return the same output for the same input. Changing the function (to return a different timestamp), renders the index corrupt.

There is no way to identify this index as corrupt except by testing each entry.


Change server name for SQLLocalDB instance

No, there is no way to remove the (LocalDB)\ prefix from LocalDB instance names. LocalDB, which is a user-process, operates differently than "regular" SQL Server which normally operates as an NT service. So, at least in terms of client connectivity libraries / software goes, that (LocalDB)\ prefix is actually functional: it directs the client to another, LocalDB-specific, DLL for the connectivity. That LocalDB-specific DLL handles things such as looking for the existence of a currently running process for the instance being requested, and if not present, then it will actually start that instance and then connect to it (end result being that it just works).


How to find Jobs that ran between two times

Well you could run the following query to retrieve the jobs run between two times or you could change the WHERE expression to catch jobs that ran for a certain duration.

SELECT sj.name,
       sjh.step_name,
       sjh.step_id,
       --sjh.run_status,
       STUFF(STUFF(CAST(sjh.run_date as nvarchar(10)),5,0,'.'),8,0,'.') as HistRunDate,
       STUFF(STUFF(RIGHT(REPLICATE('0', 6) +  CAST(sjh.run_time as varchar(6)), 6), 3, 0, ':'), 6, 0, ':') as HistRunTime,
       STUFF(STUFF(STUFF(RIGHT(REPLICATE('0', 8) + CAST(sjh.run_duration as varchar(8)), 8), 3, 0, ':'), 6, 0, ':'), 9, 0, ':') as HistRunDuration
       --,sjh.run_status AS JobStatus
FROM   msdb.dbo.sysjobs           AS sj
       join msdb.dbo.sysjobhistory AS sjh --- was sysjobschedule sjc
            on sjh.job_id = sj.job_id
WHERE  1=1
--AND sj.enabled = 1
------------------------------------------
-- for a certain time frame
------------------------------------------
AND ((sjh.run_date = 20170617 and sjh.run_time > 200000)
or (sjh.run_date = 20170722 and sjh.run_time < 100000))

------------------------------------------
-- between certain dates and with a long duration
------------------------------------------
--AND (sjh.run_date > 20160501) AND (sjh.run_date < 20160503)
--and sjh.run_duration > 3000

------------------------------------------
-- Job Outcome not required
------------------------------------------
--and sjh.step_name != '(Job outcome)'
--and sjh.step_id = 0

------------------------------------------
-- Find failed jobs
------------------------------------------
--and sjh.run_status != 1

ORDER BY
       sjh.run_date,
       sjh.run_time

As McNets pointed out there are various ways to retrieve the date you require. It's good to know though that the dates and times are defined as INT in the sys.jobsxxxx tables and not as dates and times as one would expect.

Reference: dbo.sysjobhistory (Transact-SQL)(Microsoft Technet)


Loading XML documents to Oracle 11g DB with control file

I think the way you'll have to do it is a Jokke suggest: instead of defining the DT datatype as TIMESTAMP WITH TIME ZONE, rather define it as VARCHAR2(29) where I think that 29 characters should be enough but you could define it wider.

Then when you extract data from the XML you'll have to convert it to TIMESTAMP WITH TIME ZONE.

However, you could also define the column as an "xsd:dateTime". See http://www.adp-gmbh.ch/xml/schema.html and http://www.w3.org/TR/xmlschema-2/#dateTime


Creating Symmetric Key from external provider

tl;dr - use CREATE CRYPTOGRAPHIC PROVIDER to create a connection inside SQL Server to your EKM/HSM hardware/software, through a vendor-provided .DLL file designed for that purpose.

You can use Azure Key Vault to protect on-premises data.  There are many steps required to enable your Azure account with Azure Key Vault, which are detailed at setup steps for extensible key management using Azure Key Vault.

The steps inside SQL Server consist of the following:

Install the SQL Server Connector for Azure Key Vault from the Microsoft download center.
Run the following code to enable EKM providers:

-- Enable advanced options.
USE master;
GO

sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO

-- Enable EKM provider
sp_configure 'EKM provider enabled', 1;
GO
RECONFIGURE;

Register the EKM module:

CREATE CRYPTOGRAPHIC PROVIDER AzureKeyVault_EKM_Prov
FROM FILE = 'C:\Program Files\SQL Server Connector for Microsoft Azure Key Vault\Microsoft.AzureKeyVaultService.EKM.dll';
GO

Setup a credential for SQL Server to connect to Azure:

USE master;
CREATE CREDENTIAL sysadmin_ekm_cred
    WITH IDENTITY = 'ContosoDevKeyVault', -- for public Azure
    -- WITH IDENTITY = 'ContosoDevKeyVault.vault.usgovcloudapi.net', -- for Azure Government
    -- WITH IDENTITY = 'ContosoDevKeyVault.vault.azure.cn', -- for Azure China
    -- WITH IDENTITY = 'ContosoDevKeyVault.vault.microsoftazure.de', -- for Azure Germany
    SECRET = 'EF5C8E094D2A4A769998D93440D8115DReplace-With-AAD-Client-Secret'
FOR CRYPTOGRAPHIC PROVIDER AzureKeyVault_EKM_Prov;

-- Add the credential to the SQL Server administrator's domain login
ALTER LOGIN [<domain>\<login>]
ADD CREDENTIAL sysadmin_ekm_cred;

Open the key vault:

CREATE ASYMMETRIC KEY CONTOSO_KEY
FROM PROVIDER [AzureKeyVault_EKM_Prov]
WITH PROVIDER_KEY_NAME = 'ContosoRSAKey0',
CREATION_DISPOSITION = OPEN_EXISTING;

You can then enable TDE by following the steps listed to use SQL Server with SQL Encryption.


Read only replica part of always on availability group for Reporting

My questions is will there be any problem if the read only replica for reporting is included as part of the always on availability group instead of setting Transaction Replication with the Listener.

Nothing you're not already dealing with such as an extra copy of the database, more networking IO, disk IO, etc., so that's kind of a moot point assuming the reporting server isn't massively underpowered for the reports you're running.

To be quite up front about it, I don't believe using transactional replication with Availability Groups is a good idea - it's technically possible, but beyond a pain to administrate over the long term unless you expect to never have a failover or swap in and out servers in the AG. The only time I'd go this route is if you only needed an extremely mall subset of data with vastly different indexes for reporting and couldn't do this on your primary. Outside of that I'd try my best to stay away from AGs + TR.


MySQL proxies_priv ignored in --skip-name-resolve mode

The warning is useful because it tells you that this proxy privileges will never be matched.
Your options are:

you delete this privilege
you change the @ part to % or to a defined ip address
you disable skip-name-resolve (which will slow down MySQL, which will now depend on the DNS resolver speed: https://dev.mysql.com/doc/refman/5.7/en/host-cache.html)

Update
Just tested MySQL 5.6 and MySQL 5.7, and it seems that even if the warning appears, localhost is replaced by 127.0.0.1 with --skip-name-resolve. A bug was opened: https://bugs.mysql.com/bug.php?id=81441
Clearly MySQL should only warn if the domain isn't localhost, as with a disabled resolver the rule won't apply at all, only @your.ip.address.number and @localhost would work.


How to know what server and port to select in MongoVue

Try using;

Server: 127.0.0.1
Port: 27017

That should work.


For SQL Server, an index entry is stored pretty much the same way as a normal record except for the row header. Also, index and record entries are never on the same page

Now, anything I add to this would be copy/paste of articles by cleverer folk than me, so I'll refer you to Paul Randal: Inside the Storage Engine: Anatomy of a record

Also, all indexes in SQL Server are B-Tree. Tables with clustered indexes in SQL Server are the same as Oracle Index Organised tables

As for a SELECT, you have to consider if an index is "covering" or not usually. This means the entire SELECT can be satisfied from the index only. Otherwise it may not be used or generate key lookups. For more, see

SQL Server Index Basics
Using Covering Indexes to Improve Query Performance


My favorite way to check these is to use Adam Machanic's excellent sp_WhoIsActive stored proc.  Here's a video on how to use it and a link to download the code:

http://www.brentozar.com/archive/2010/09/sql-server-dba-scripts-how-to-find-slow-sql-server-queries/

The catch is that you have to run it periodically to check for results.  If you want this data to be gathered for you periodically, check out Kendra Little's tutorial on capturing sp_WhoIsActive results to a table:

http://www.littlekendra.com/2011/02/01/whoisactive/

Finally, if you want something to fire whenever a query waits for async_network_io, you can use a new tool called Extended Events.  It's like debug points inside the SQL Server engine where you can make magic happen.  Frankly, it's a little painful to use right now in 2008.


Transaction on delete is committed, but for a brief time the entries can still be read?

(I can't answer your question, but maybe the following will eliminate the problem you are seeing.)

For long-running tasks (more than a few seconds), consider this technique:

SET autocommit=1;
UPDATE Tasks SET owner='me' WHERE task='use_car' AND owner IS NULL;
if ROWS_AFFECTED == 0  THEN
     Do something else, or wait, or whatever

START TRANSACTION;
...
COMMIT;

UPDATE Tasks SET owner=NULL WHERE task='use_car';

Notes:

The 'task' stuff is kept outside the transaction.
Each UPDATE is atomic, so a simple autocommit suffices.


Oh my goodness, I think the problem stems from mixing the mysql schema of different mysql versions. First of all, run this query:
desc mysql.user;

For MySQL 8.0, you get 51 columns
mysql> desc mysql.user;

Field
Type
Null
Key
Default
Extra

Host
char(255)
NO
PRI

User
char(32)
NO
PRI

Select_priv
enum('N','Y')
NO

N

Insert_priv
enum('N','Y')
NO

N

Update_priv
enum('N','Y')
NO

N

Delete_priv
enum('N','Y')
NO

N

Create_priv
enum('N','Y')
NO

N

Drop_priv
enum('N','Y')
NO

N

Reload_priv
enum('N','Y')
NO

N

Shutdown_priv
enum('N','Y')
NO

N

Process_priv
enum('N','Y')
NO

N

File_priv
enum('N','Y')
NO

N

Grant_priv
enum('N','Y')
NO

N

References_priv
enum('N','Y')
NO

N

Index_priv
enum('N','Y')
NO

N

Alter_priv
enum('N','Y')
NO

N

Show_db_priv
enum('N','Y')
NO

N

Super_priv
enum('N','Y')
NO

N

Create_tmp_table_priv
enum('N','Y')
NO

N

Lock_tables_priv
enum('N','Y')
NO

N

Execute_priv
enum('N','Y')
NO

N

Repl_slave_priv
enum('N','Y')
NO

N

Repl_client_priv
enum('N','Y')
NO

N

Create_view_priv
enum('N','Y')
NO

N

Show_view_priv
enum('N','Y')
NO

N

Create_routine_priv
enum('N','Y')
NO

N

Alter_routine_priv
enum('N','Y')
NO

N

Create_user_priv
enum('N','Y')
NO

N

Event_priv
enum('N','Y')
NO

N

Trigger_priv
enum('N','Y')
NO

N

Create_tablespace_priv
enum('N','Y')
NO

N

ssl_type
enum('','ANY','X509','SPECIFIED')
NO

ssl_cipher
blob
NO

NULL

x509_issuer
blob
NO

NULL

x509_subject
blob
NO

NULL

max_questions
int unsigned
NO

0

max_updates
int unsigned
NO

0

max_connections
int unsigned
NO

0

max_user_connections
int unsigned
NO

0

plugin
char(64)
NO

caching_sha2_password

authentication_string
text
YES

NULL

password_expired
enum('N','Y')
NO

N

password_last_changed
timestamp
YES

NULL

password_lifetime
smallint unsigned
YES

NULL

account_locked
enum('N','Y')
NO

N

Create_role_priv
enum('N','Y')
NO

N

Drop_role_priv
enum('N','Y')
NO

N

Password_reuse_history
smallint unsigned
YES

NULL

Password_reuse_time
smallint unsigned
YES

NULL

Password_require_current
enum('N','Y')
YES

NULL

User_attributes
json
YES

NULL

51 rows in set (0.00 sec)
For MySQL 5.6, you get 43 columns
mysql> desc mysql.user;
+------------------------+-----------------------------------+------+-----+---------+-------+
| Field                  | Type                              | Null | Key | Default | Extra |
+------------------------+-----------------------------------+------+-----+---------+-------+
| Host                   | char(60)                          | NO   | PRI |         |       |
| User                   | char(16)                          | NO   | PRI |         |       |
| Password               | char(41)                          | NO   |     |         |       |
| Select_priv            | enum('N','Y')                     | NO   |     | N       |       |
| Insert_priv            | enum('N','Y')                     | NO   |     | N       |       |
| Update_priv            | enum('N','Y')                     | NO   |     | N       |       |
| Delete_priv            | enum('N','Y')                     | NO   |     | N       |       |
| Create_priv            | enum('N','Y')                     | NO   |     | N       |       |
| Drop_priv              | enum('N','Y')                     | NO   |     | N       |       |
| Reload_priv            | enum('N','Y')                     | NO   |     | N       |       |
| Shutdown_priv          | enum('N','Y')                     | NO   |     | N       |       |
| Process_priv           | enum('N','Y')                     | NO   |     | N       |       |
| File_priv              | enum('N','Y')                     | NO   |     | N       |       |
| Grant_priv             | enum('N','Y')                     | NO   |     | N       |       |
| References_priv        | enum('N','Y')                     | NO   |     | N       |       |
| Index_priv             | enum('N','Y')                     | NO   |     | N       |       |
| Alter_priv             | enum('N','Y')                     | NO   |     | N       |       |
| Show_db_priv           | enum('N','Y')                     | NO   |     | N       |       |
| Super_priv             | enum('N','Y')                     | NO   |     | N       |       |
| Create_tmp_table_priv  | enum('N','Y')                     | NO   |     | N       |       |
| Lock_tables_priv       | enum('N','Y')                     | NO   |     | N       |       |
| Execute_priv           | enum('N','Y')                     | NO   |     | N       |       |
| Repl_slave_priv        | enum('N','Y')                     | NO   |     | N       |       |
| Repl_client_priv       | enum('N','Y')                     | NO   |     | N       |       |
| Create_view_priv       | enum('N','Y')                     | NO   |     | N       |       |
| Show_view_priv         | enum('N','Y')                     | NO   |     | N       |       |
| Create_routine_priv    | enum('N','Y')                     | NO   |     | N       |       |
| Alter_routine_priv     | enum('N','Y')                     | NO   |     | N       |       |
| Create_user_priv       | enum('N','Y')                     | NO   |     | N       |       |
| Event_priv             | enum('N','Y')                     | NO   |     | N       |       |
| Trigger_priv           | enum('N','Y')                     | NO   |     | N       |       |
| Create_tablespace_priv | enum('N','Y')                     | NO   |     | N       |       |
| ssl_type               | enum('','ANY','X509','SPECIFIED') | NO   |     |         |       |
| ssl_cipher             | blob                              | NO   |     | NULL    |       |
| x509_issuer            | blob                              | NO   |     | NULL    |       |
| x509_subject           | blob                              | NO   |     | NULL    |       |
| max_questions          | int(11) unsigned                  | NO   |     | 0       |       |
| max_updates            | int(11) unsigned                  | NO   |     | 0       |       |
| max_connections        | int(11) unsigned                  | NO   |     | 0       |       |
| max_user_connections   | int(11) unsigned                  | NO   |     | 0       |       |
| plugin                 | char(64)                          | YES  |     |         |       |
| authentication_string  | text                              | YES  |     | NULL    |       |
| password_expired       | enum('N','Y')                     | NO   |     | N       |       |
+------------------------+-----------------------------------+------+-----+---------+-------+
43 rows in set (0.15 sec)

For MySQL 5.5, you get 42 columns
mysql> desc mysql.user;
+------------------------+-----------------------------------+------+-----+---------+-------+
| Field                  | Type                              | Null | Key | Default | Extra |
+------------------------+-----------------------------------+------+-----+---------+-------+
| Host                   | char(60)                          | NO   | PRI |         |       |
| User                   | char(16)                          | NO   | PRI |         |       |
| Password               | char(41)                          | NO   |     |         |       |
| Select_priv            | enum('N','Y')                     | NO   |     | N       |       |
| Insert_priv            | enum('N','Y')                     | NO   |     | N       |       |
| Update_priv            | enum('N','Y')                     | NO   |     | N       |       |
| Delete_priv            | enum('N','Y')                     | NO   |     | N       |       |
| Create_priv            | enum('N','Y')                     | NO   |     | N       |       |
| Drop_priv              | enum('N','Y')                     | NO   |     | N       |       |
| Reload_priv            | enum('N','Y')                     | NO   |     | N       |       |
| Shutdown_priv          | enum('N','Y')                     | NO   |     | N       |       |
| Process_priv           | enum('N','Y')                     | NO   |     | N       |       |
| File_priv              | enum('N','Y')                     | NO   |     | N       |       |
| Grant_priv             | enum('N','Y')                     | NO   |     | N       |       |
| References_priv        | enum('N','Y')                     | NO   |     | N       |       |
| Index_priv             | enum('N','Y')                     | NO   |     | N       |       |
| Alter_priv             | enum('N','Y')                     | NO   |     | N       |       |
| Show_db_priv           | enum('N','Y')                     | NO   |     | N       |       |
| Super_priv             | enum('N','Y')                     | NO   |     | N       |       |
| Create_tmp_table_priv  | enum('N','Y')                     | NO   |     | N       |       |
| Lock_tables_priv       | enum('N','Y')                     | NO   |     | N       |       |
| Execute_priv           | enum('N','Y')                     | NO   |     | N       |       |
| Repl_slave_priv        | enum('N','Y')                     | NO   |     | N       |       |
| Repl_client_priv       | enum('N','Y')                     | NO   |     | N       |       |
| Create_view_priv       | enum('N','Y')                     | NO   |     | N       |       |
| Show_view_priv         | enum('N','Y')                     | NO   |     | N       |       |
| Create_routine_priv    | enum('N','Y')                     | NO   |     | N       |       |
| Alter_routine_priv     | enum('N','Y')                     | NO   |     | N       |       |
| Create_user_priv       | enum('N','Y')                     | NO   |     | N       |       |
| Event_priv             | enum('N','Y')                     | NO   |     | N       |       |
| Trigger_priv           | enum('N','Y')                     | NO   |     | N       |       |
| Create_tablespace_priv | enum('N','Y')                     | NO   |     | N       |       |
| ssl_type               | enum('','ANY','X509','SPECIFIED') | NO   |     |         |       |
| ssl_cipher             | blob                              | NO   |     | NULL    |       |
| x509_issuer            | blob                              | NO   |     | NULL    |       |
| x509_subject           | blob                              | NO   |     | NULL    |       |
| max_questions          | int(11) unsigned                  | NO   |     | 0       |       |
| max_updates            | int(11) unsigned                  | NO   |     | 0       |       |
| max_connections        | int(11) unsigned                  | NO   |     | 0       |       |
| max_user_connections   | int(11) unsigned                  | NO   |     | 0       |       |
| plugin                 | char(64)                          | YES  |     |         |       |
| authentication_string  | text                              | YES  |     | NULL    |       |
+------------------------+-----------------------------------+------+-----+---------+-------+
42 rows in set (0.01 sec)

mysql>

For MySQL 5.1, you get 39 columns
mysql> desc mysql.user;
+-----------------------+-----------------------------------+------+-----+---------+-------+
| Field                 | Type                              | Null | Key | Default | Extra |
+-----------------------+-----------------------------------+------+-----+---------+-------+
| Host                  | char(60)                          | NO   | PRI |         |       |
| User                  | char(16)                          | NO   | PRI |         |       |
| Password              | char(41)                          | NO   |     |         |       |
| Select_priv           | enum('N','Y')                     | NO   |     | N       |       |
| Insert_priv           | enum('N','Y')                     | NO   |     | N       |       |
| Update_priv           | enum('N','Y')                     | NO   |     | N       |       |
| Delete_priv           | enum('N','Y')                     | NO   |     | N       |       |
| Create_priv           | enum('N','Y')                     | NO   |     | N       |       |
| Drop_priv             | enum('N','Y')                     | NO   |     | N       |       |
| Reload_priv           | enum('N','Y')                     | NO   |     | N       |       |
| Shutdown_priv         | enum('N','Y')                     | NO   |     | N       |       |
| Process_priv          | enum('N','Y')                     | NO   |     | N       |       |
| File_priv             | enum('N','Y')                     | NO   |     | N       |       |
| Grant_priv            | enum('N','Y')                     | NO   |     | N       |       |
| References_priv       | enum('N','Y')                     | NO   |     | N       |       |
| Index_priv            | enum('N','Y')                     | NO   |     | N       |       |
| Alter_priv            | enum('N','Y')                     | NO   |     | N       |       |
| Show_db_priv          | enum('N','Y')                     | NO   |     | N       |       |
| Super_priv            | enum('N','Y')                     | NO   |     | N       |       |
| Create_tmp_table_priv | enum('N','Y')                     | NO   |     | N       |       |
| Lock_tables_priv      | enum('N','Y')                     | NO   |     | N       |       |
| Execute_priv          | enum('N','Y')                     | NO   |     | N       |       |
| Repl_slave_priv       | enum('N','Y')                     | NO   |     | N       |       |
| Repl_client_priv      | enum('N','Y')                     | NO   |     | N       |       |
| Create_view_priv      | enum('N','Y')                     | NO   |     | N       |       |
| Show_view_priv        | enum('N','Y')                     | NO   |     | N       |       |
| Create_routine_priv   | enum('N','Y')                     | NO   |     | N       |       |
| Alter_routine_priv    | enum('N','Y')                     | NO   |     | N       |       |
| Create_user_priv      | enum('N','Y')                     | NO   |     | N       |       |
| Event_priv            | enum('N','Y')                     | NO   |     | N       |       |
| Trigger_priv          | enum('N','Y')                     | NO   |     | N       |       |
| ssl_type              | enum('','ANY','X509','SPECIFIED') | NO   |     |         |       |
| ssl_cipher            | blob                              | NO   |     | NULL    |       |
| x509_issuer           | blob                              | NO   |     | NULL    |       |
| x509_subject          | blob                              | NO   |     | NULL    |       |
| max_questions         | int(11) unsigned                  | NO   |     | 0       |       |
| max_updates           | int(11) unsigned                  | NO   |     | 0       |       |
| max_connections       | int(11) unsigned                  | NO   |     | 0       |       |
| max_user_connections  | int(11) unsigned                  | NO   |     | 0       |       |
+-----------------------+-----------------------------------+------+-----+---------+-------+
39 rows in set (0.00 sec)

mysql>

For MySQL 5.0, you get 37 columns
mysql> desc mysql.user;
+-----------------------+-----------------------------------+------+-----+---------+-------+
| Field                 | Type                              | Null | Key | Default | Extra |
+-----------------------+-----------------------------------+------+-----+---------+-------+
| Host                  | char(60)                          | NO   | PRI |         |       |
| User                  | char(16)                          | NO   | PRI |         |       |
| Password              | char(41)                          | NO   |     |         |       |
| Select_priv           | enum('N','Y')                     | NO   |     | N       |       |
| Insert_priv           | enum('N','Y')                     | NO   |     | N       |       |
| Update_priv           | enum('N','Y')                     | NO   |     | N       |       |
| Delete_priv           | enum('N','Y')                     | NO   |     | N       |       |
| Create_priv           | enum('N','Y')                     | NO   |     | N       |       |
| Drop_priv             | enum('N','Y')                     | NO   |     | N       |       |
| Reload_priv           | enum('N','Y')                     | NO   |     | N       |       |
| Shutdown_priv         | enum('N','Y')                     | NO   |     | N       |       |
| Process_priv          | enum('N','Y')                     | NO   |     | N       |       |
| File_priv             | enum('N','Y')                     | NO   |     | N       |       |
| Grant_priv            | enum('N','Y')                     | NO   |     | N       |       |
| References_priv       | enum('N','Y')                     | NO   |     | N       |       |
| Index_priv            | enum('N','Y')                     | NO   |     | N       |       |
| Alter_priv            | enum('N','Y')                     | NO   |     | N       |       |
| Show_db_priv          | enum('N','Y')                     | NO   |     | N       |       |
| Super_priv            | enum('N','Y')                     | NO   |     | N       |       |
| Create_tmp_table_priv | enum('N','Y')                     | NO   |     | N       |       |
| Lock_tables_priv      | enum('N','Y')                     | NO   |     | N       |       |
| Execute_priv          | enum('N','Y')                     | NO   |     | N       |       |
| Repl_slave_priv       | enum('N','Y')                     | NO   |     | N       |       |
| Repl_client_priv      | enum('N','Y')                     | NO   |     | N       |       |
| Create_view_priv      | enum('N','Y')                     | NO   |     | N       |       |
| Show_view_priv        | enum('N','Y')                     | NO   |     | N       |       |
| Create_routine_priv   | enum('N','Y')                     | NO   |     | N       |       |
| Alter_routine_priv    | enum('N','Y')                     | NO   |     | N       |       |
| Create_user_priv      | enum('N','Y')                     | NO   |     | N       |       |
| ssl_type              | enum('','ANY','X509','SPECIFIED') | NO   |     |         |       |
| ssl_cipher            | blob                              | NO   |     | NULL    |       |
| x509_issuer           | blob                              | NO   |     | NULL    |       |
| x509_subject          | blob                              | NO   |     | NULL    |       |
| max_questions         | int(11) unsigned                  | NO   |     | 0       |       |
| max_updates           | int(11) unsigned                  | NO   |     | 0       |       |
| max_connections       | int(11) unsigned                  | NO   |     | 0       |       |
| max_user_connections  | int(11) unsigned                  | NO   |     | 0       |       |
+-----------------------+-----------------------------------+------+-----+---------+-------+
37 rows in set (0.01 sec)

mysql>

For MySQL 4.x, you get 31 columns
mysql> desc mysql.user;
+-----------------------+-----------------------------------+------+-----+---------+-------+
| Field                 | Type                              | Null | Key | Default | Extra |
+-----------------------+-----------------------------------+------+-----+---------+-------+
| Host                  | varchar(60) binary                |      | PRI |         |       |
| User                  | varchar(16) binary                |      | PRI |         |       |
| Password              | varchar(16) binary                |      |     |         |       |
| Select_priv           | enum('N','Y')                     |      |     | N       |       |
| Insert_priv           | enum('N','Y')                     |      |     | N       |       |
| Update_priv           | enum('N','Y')                     |      |     | N       |       |
| Delete_priv           | enum('N','Y')                     |      |     | N       |       |
| Create_priv           | enum('N','Y')                     |      |     | N       |       |
| Drop_priv             | enum('N','Y')                     |      |     | N       |       |
| Reload_priv           | enum('N','Y')                     |      |     | N       |       |
| Shutdown_priv         | enum('N','Y')                     |      |     | N       |       |
| Process_priv          | enum('N','Y')                     |      |     | N       |       |
| File_priv             | enum('N','Y')                     |      |     | N       |       |
| Grant_priv            | enum('N','Y')                     |      |     | N       |       |
| References_priv       | enum('N','Y')                     |      |     | N       |       |
| Index_priv            | enum('N','Y')                     |      |     | N       |       |
| Alter_priv            | enum('N','Y')                     |      |     | N       |       |
| Show_db_priv          | enum('N','Y')                     |      |     | N       |       |
| Super_priv            | enum('N','Y')                     |      |     | N       |       |
| Create_tmp_table_priv | enum('N','Y')                     |      |     | N       |       |
| Lock_tables_priv      | enum('N','Y')                     |      |     | N       |       |
| Execute_priv          | enum('N','Y')                     |      |     | N       |       |
| Repl_slave_priv       | enum('N','Y')                     |      |     | N       |       |
| Repl_client_priv      | enum('N','Y')                     |      |     | N       |       |
| ssl_type              | enum('','ANY','X509','SPECIFIED') |      |     |         |       |
| ssl_cipher            | blob                              |      |     |         |       |
| x509_issuer           | blob                              |      |     |         |       |
| x509_subject          | blob                              |      |     |         |       |
| max_questions         | int(11) unsigned                  |      |     | 0       |       |
| max_updates           | int(11) unsigned                  |      |     | 0       |       |
| max_connections       | int(11) unsigned                  |      |     | 0       |       |
+-----------------------+-----------------------------------+------+-----+---------+-------+
31 rows in set (0.01 sec)

Picture this scenario:

You install 5.0
You uninstall 5.0
You forgot to move /var/lib/mysql out of the way
You install 5.1

The user permissions start getting shifted. Whenever you run SHOW GRANTS;, it is hardwired to expect columns in specfic places in mysql.user (given the fact that mysql.user is a MyISAM and its ROW_FORMAT is Dynamic (Default)). It is very easy to see a user suddenly lose permissions when you reload a MySQL 5.0 version of mysql.user into a MySQL 5.1 instance.
In the future, should you ever decide to reload users into MySQL, try to dump the users to a text file using pt-show-grants rather than mysqldump.
I actually wrote my own version of pt-show-grants as follows:
mysql -hhostaddr -umyuserid -pmypassword --skip-column-names -A -e"SELECT CONCAT('SHOW GRANTS FOR ''',user,'''@''',host,''';') FROM mysql.user WHERE user<>''" | mysql -hhostaddr -umyuserid -pmypassword --skip-column-names -A | sed 's/$/;/g' > MySQLUserGrants.sql

This will produce all user grants as SQL, which is far more portable than standard mysql techniques. Simply reload the SQL script and the GRANTS are executed. Of course, such reloading is only forward compatible, never backward.


Performance difference between searching table with 350 columns and searching table with 25 columns

If someone can point me to a better, performant way of searching for a non-primary key data [...]

Add an index in your database on the column(s) you are filtering on - in this case COL_5. The resulting execution plan should be a single index seek and the performanze difference negligible (or even zero).

Also rather than getting the entire result set, just check if there is at least one resulting row. You can either add a TOP 1 to your query (or whatever SQL equivalent), or add a .setMaxResults(1).


Updating total using values from previous row - SQL Server 2016

I had to do something similar once and had a good result from using a windowing function like below:

SELECT RunningTotal = History.Total + ISNULL((SUM(History.Total) OVER
                    (PARTITION BY PartitionID
                    ORDER BY History.EffectiveDate ASC, History.HistoryID ASC
                    ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING)), 0)
FROM   History
ORDER BY History.EffectiveDate, History.HistoryID

This sums up all of the rows before the current row.

You can't update straight from that, but you could put it into a CTE or insert the data into a temp table and then update from one of those.

The performance of it isn't great, but for a one off script in a maintenance window it was good enough for us.


When to Use Functions over Stored Procedures

from a quick search i found the following article
as to the benefits of a udf vs an sps:

Procedure allows SELECT as well as DML(INSERT/UPDATE/DELETE) statement in it whereas Function allows only SELECT statement in it.

you can use this to limit the use of a query to a select statment only via the function

Procedures can't be utilized in a SELECT statement whereas Function can be embedded in a SELECT statement.
Stored Procedures cannot be used in the SQL statements anywhere in the WHERE/HAVING/SELECT section whereas Function can be.

this might have an effect on the query load because for each row returned it will execute your udf.

Functions that return tables can be treated as another rowset. This can be used in JOINs with other tables.
Inline Function can be thought of as views that take parameters and can be used in JOINs and other Rowset operations.

In my opinion, it's really a matter of the relevant use case.
You can use a function when you need to manipulate the data per row, or when you want to be on the safe side that a DML query won't run for a user that has permissions to run DML queries.


Execution plan doesn't use clustered index!

I'm using "SQL*Plus: Release 10.2.0.1.0 - Production"

Which tells us nothing about your Oracle Server version unfortunately! The bit below ("Connected to...") is about the server side

  select foo_id from foo;

The clustered index does not contain a pointer to each row of the table - instead it contains pointers to clusters (groups of rows with the same foo_id). It could in theory be useful for a query like select distinct foo_id from foo; (no idea if it actually would though), but you still need to go to the underlying table to answer select foo_id from foo;.

  So why doesn't my hint work

Because it is just a hint - the CBO is clever enough to ignore it if it means full scanning the clustered index and the table instead of just the table

Also the correct hint for a clustered scan is cluster rather than index eg /*+ CLUSTER(foo) */


Connecting to the default instance of SQL Server connects to a named instance

There can be three reasons:

SQL Aliases: either you or that  dev has an alias
Named instance running on port 1433. Connecting to the default instance means just connecting with port 1433 instead of asking the sql browser first (only applies to tcp)
Explicit port inside the connection string


According to IBM's documentation: "Typically, you need to manually perform integrity processing for a table in three situations: After loading data into a table; when altering a table by adding constraints on the table; and when altering a table to add a generated column."

That would be the why you need to do it. We use it right after we use the LOAD command as this bypasses referential integrity to load (for lack of a better word) a ton of records into tables. We end up needing to use SET INTEGRITY when we are done to have it check the integrity and verify our tables can be used.

In our case we use:

SET INTEGRITY <schema>.<table> ALLOW NO ACCESS IMMEDIATE CHECKED;

And yes, you do have to run the SET INTEGRITY command for each table that is in a pending state. (We use scripts to do it in our case to simplify things.)

For more information about the command see SET INTEGRITY for all available options.


Community wiki answer:
From CHECKDB From Every Angle: When did DBCC CHECKDB last run successfully? by Paul Randal:

One of the new features we put into SQL Server 2005 was storing the last time that DBCC CHECKDB completed successfully (called the last-known good time). It’s the time that the DBCC CHECKDB started, not completed.
What does successfully mean? This is the catch – if DBCC CHECKDB runs to completion then it considers that a successful run – EVEN if it found some corruptions. However, the last-known good time is ONLY updated if there were NO corruptions found. Slightly confusing I know.

Does seem somewhat contradictory, but I agree it sounds like this is not expected behaviour for that field to be updated if errors were reported.
On the other hand, your examples are quite minor issues, easily fixed by running DBCC UPDATEUSAGE so maybe SQL Server regards that as a successful run.


Cannot re-attach SQL Server Express database to the same server after detaching?

Can someone point me to the circumstances that could lead to this problem (database working on the very same server, then detached, not
changed (at least by me), then not being able to re-attach?

You have 2 problems:

Your 'old' server is SQL Server 2008 R2, and the new one is SQL Server 2012.   You cannot attach a database from a SQL Server 2012 instance on a SQL Server 2008R2 instance.
The limit of 10Gbs is exceeded.

What to do:

attach your database to a SQL Server 2012 (or higher) instance.
Export data from  that instance to  your SQL Server 2008 R2 instance.

Is there a way to free up space inside that .mdf file without actually attaching it?

You do not actually need it. I do not know such a way.

Or could it be feasible to take the database I already attached to the new (full) SQL Server, shrink that in size and then attach it to the SQL Server Express instance?  Would attaching the file to a full version of SQL Server change the format of said .mdf file to be unusable by SQL Server Express?

You can do it if you will work on SQL Server 2012 Express.  No - the format will not be changed.


Surrogate vs composite key in hierarchical data structure

With the 1st design, you can enforce that a Part is related (through SubItem and Type) to the same Item. With the second, you can't (through DDL alone, you need triggers as you already mentioned to enforce that or make sure all CRUD operations are done through stored procedures that take care of the restriction). Otherwise you may have a Part that is related to a SubItem and a Type that refer to different Items.

Some notes on your (1st) design:

It's not clear that you have the correct FOREIGN KEY constraints. Your requirement cannot be enforced by 3 simple FKs in Part but needs 2 composite ones:

Part
  PartId    (PK)
  ItemId    (PK, FK1, FK2)
  SubItemId (PK, FK1)
  TypeId    (PK,      FK2)
  Name

You may not need to have PartId in the PK. Is it possible to have 2 or more parts that belong to the same SubItem and same Type? If not, the unique (or primary key) constraint should be on (ItemId, SubItemId, TypeId). If yes, then it should be on (ItemId, SubItemId, TypeId, PartId), as you have it.

Regarding PartMetadata or any other table that can reference Part table, you don't have to include the whole composite key of Part. You can always add a new surrogate UNIQUE key in Part and use that instead. This is common practice I think in complex and high hierarchies of entities. We can use composite PKs and FKs up to a point. When they become too wide (4-6 columns), we can add surrogate unique keys in a few important tables and use them for referencing from lower tables in the model.


Performance difference between Text and Varchar in Mysql

The performance difference is mainly due to the fact, that text datatype columns are always copied to temporary tables on disk, because the memory engine doesn't support those types.

From the manual:

  Instances of BLOB or TEXT columns in the result of a query that is
  processed using a temporary table causes the server to use a table on
  disk rather than in memory because the MEMORY storage engine does not
  support those data types (see Section 8.4.4, “Internal Temporary Table
  Use in MySQL”). Use of disk incurs a performance penalty, so include
  BLOB or TEXT columns in the query result only if they are really
  needed.

When you don't use group by no temporary table is needed, obviously.


Oh goodness, you have a lot of questions in here. I'll try to unpack them all.

Q: What is "SQL Server in the cloud"?

The term "the cloud" is too generic - you might as well be saying "SQL Server on the network." There are lots of different ways to deploy SQL Server in the cloud, some of which function exactly like on-premises servers (it's just that someone else is managing the server for you.)

If you can, try to avoid generic sweeping statements like "SQL Server in the cloud" because you won't get good answers. The more specific your question can be, the more relevant advice you can get. (Not to mention the less downvotes, ha ha ho ho.)

Q: Is there a universal rule for clustering keys?

Generally, you want your clustering keys to follow the SUN-E principles:

Static - fields that don't change, so you don't have to update your nonclustered indexes
Unique - so SQL Server doesn't have to add a uniqueifier behind the scenes
Narrow - so lots of data isn't copied into your nonclustered indexes
Ever-increasing - creating a hotspot at the end, guaranteeing that where you're inserting will usually be in memory (although this is one rule that folks violate on purpose during extremely heavy concurrency, like tens of thousands of inserts per second)

Identity fields match all 4 (SUN-E), while GUID fields only match the SU portions. One could argue that a single GUID is kinda narrow compared to, say, multiple GUIDs chained together or an NVARCHAR(250), but it's got nothin' on the narrowness of an INT.

Q: Do data modeling guidelines change in IaaS?

Infrastructure-as-a-Service providers like Amazon EC2, Google Compute Engine, and Microsoft Azure VMs are just VMs running on someone else's computer. Data modeling rules do not change here.

Q: Do data modeling guidelines change in PaaS?

Platform-as-a-Service providers are a little different.

Amazon RDS is just SQL Server hosted and managed by someone else, so no data modeling changes required there.

Microsoft Azure SQL DB isn't exactly "just SQL Server hosted and managed by someone else" - it has some strengths and weaknesses that differ from the traditional boxed product. However, if you're only building your app for a single database in Azure SQL DB (not scaling out to multiple databases), then the conventional rules apply.

Q: When does "the cloud" affect data modeling guidelines?

When you want to scale a single application across multiple databases and/or servers. This design pattern is called sharding, splitting a single identical table (or sets of tables) across hosts.

To be clear, you can do this same design pattern on-premises - it's just more trendy these days in the cloud because it's so much easier to spin up multiple servers or databases.

You noted that your database only uses 70GB per year - that's well below the numbers where I'd even remotely consider sharding.

Q: When sharding, what data modeling concerns will I have?

First, your application has to know which shard to hit in order to get the relevant data. (You don't want the app hitting the database to discover this data for every query - after all, you'd be right back up against the scalability limits of a single database server.)

You could shard by customer location - like one shard for North America, another for Europe, another for Asia. However, your customers may move between locations, or they may have locations spread across contents.

You could shard by customer name - like A, B, C, D - but you'll have some shards that are much hotter than others due to load. (Say one of your customers runs a World Cup sale - that server might fall over while the rest are idle.) And of course, customers can change names.

You could shard by activity date - like the date of sale - but of course today's shard will be ridiculously hot compared to the other shards.

By now, you're starting to see now why your question is so broad, and so difficult to answer. Your best bet to get an actionable, useful answer will be to provide as many specifics as you can. However, since you asked for a design for 70GB of data per year, keep it simple: stick with the SUN-E principles.


Generating an expiry date based on fields in different tables

You can create a view using a JOIN statement to combine the results of two related tables. In your design, the CourseID field appears on both the tbl_courses and tbl_training tables, using this field to perform the join you can easily derive the value that you require:

CREATE VIEW [CourseDetails]
(
    [CourseName],
    [CourseDescription],
    [DateCompleted],
    [ExpiryDate]
)
AS
SELECT tbl_Courses.[CourseName]
     , tbl_Courses.[CourseDescription]
     , tbl_Training.[DateCompleted]
     , DATEADD("d", tbl_Training.[DaysToExpire], tbl_Courses.[DateCompleted]) [ExpiryDate]
  FROM tbl_Courses
 INNER JOIN tbl_Training
    ON tbl_Courses.[CourseID] = tbl_Training.[CourseID];

There is a full tutorial on how to perform this that you might find helpful.


I have heard that some folks have had success using lxc to isolate MongoDB on Linux. There are other container technologies out there, but that seems to be the most mainstream option.


Yes, it is supposed to be slow. Why?

There are multiple subqueries:

3 levels of nested derived tables
7 inline correlated subqueries that each join to a different table, using the userid which as you say is not indexed. One of the subqueries has a further nested derived table in its FROM clause.

There are WHERE conditios on un-indexed columns
There is an ORDER BY clause - it always adds one more operation
There is a DISTINCT quantifier - filtering out duplicates is a costly operation
There are some calculations being made

It really looks very bizarre. I guess it can be rewritten to a neat form using JOINs and it would run much faster. You would have to post the database structure and the result you want to achieve.


Storing no of players the last seven days in an online game

I would store the timestamp, server identifier, and number of players in just the way you thought of, deleting the oldest (if you need to) and adding new ones every 15 minutes.

Moving the server names into another "server" table and referencing the primary key in your player count table would be a more conventional normalised design.

With reference to the trigger, don't forget that you can perform both the insert and the delete in the same SQL statement with a common table expression: http://sqlfiddle.com/#!1/c8362/5


Oracle 12.2 : Is there a way to copy pdb from one server to another in production env

The hot cloning of PDBs is a new feature introduced in 12.2.

Cloning a PDB

  Cloning of a pluggable database (PDB) resolves the issue of setting
  the source system to read-only mode before creating a full or snapshot
  clone of a PDB. With this feature, you can now clone your production
  PDBs and create point-in-time copies for development or testing
  without any application outage.

  This feature eliminates the need to create application outages for the
  purpose of cloning.

So the PDB in question can be easily cloned without downtime, then the customer can send you the clone PDB.


PostgreSQL logical replication using pglogical

Tables and schema names in the subscriber node have to be the same as the provider node.
Hereunder the error messages if the names are differents between the two nodes:

>LOG:  starting apply for subscription subscription
>INFO:  initializing subscriber subscription
>INFO:  synchronizing data
>ERROR:  schema "myschema" does not exist

>LOG:  starting apply for subscription subscription
>INFO:  initializing subscriber subscription
>INFO:  synchronizing data
>ERROR:  relation "myschema.tblprod2" does not exist

Now replication working.


Restarting hot_standby replication when the *slave* fails

You must be using a replication slot.  It is causing the master to retain the log files, because it still thinks the replica will be needing them.  (That is the purpose of a replication slot).  Find the slot and delete it.  Then create a new one for the new replica you will need to seed.


Why does subquery use parallelism and join doesn't?

As already indicated in the comments it looks as though you need to update your statistics.

The estimated number of rows coming out of the join between location and testruns is hugely different between the two plans.

Join plan estimates: 1

Sub query plan estimates: 8,748

The actual number of rows coming out of the join is 14,276.

Of course it makes absolutely no intuitive sense that the join version should estimate that 3 rows should come from location and produce a single joined row whereas the sub query estimates that a single one of those rows will produce 8,748 from the same join but nonetheless I was able to reproduce this.

This seems to happen if there is no cross over between the histograms when the statistics are created. The join version assumes a single row. And the single equality seek of the sub query assumes the same estimated rows as an equality seek against an unknown variable.

The cardinality of testruns is 26244. Assuming that is populated with three distinct location ids then the following query estimates that 8,748 rows will be returned (26244/3)

declare @i int

SELECT *
FROM   testruns AS tr
WHERE  tr.location_id = @i

Given that the table locations only contains 3 rows it is easy (if we assume no foreign keys) to contrive a situation where the statistics are created and then the data is altered in a way that dramatically effects the actual number of rows returned but is insufficient to trip the auto update of stats and recompile threshold.

As SQL Server gets the number of rows coming out of that join so wrong all the other row estimates in the join plan are massively underestimated. As well as meaning that you get a serial plan the query also gets an insufficient memory grant and the sorts and hash joins spill to tempdb.

One possible scenario that reproduces the actual vs estimated rows shown in your plan is below.

CREATE TABLE location
  (
     id       INT CONSTRAINT locationpk PRIMARY KEY,
     location VARCHAR(MAX) /*From the separate filter think you are using max?*/
  )

/*Temporary ids these will be updated later*/
INSERT INTO location
VALUES      (101, 'Coventry'),
            (102, 'Nottingham'),
            (103, 'Derby')

CREATE TABLE testruns
  (
     location_id INT
  )

CREATE CLUSTERED INDEX IX ON testruns(location_id)

/*Add in 26244 rows of data split over three ids*/
INSERT INTO testruns
SELECT TOP (5984) 1
FROM   master..spt_values v1, master..spt_values v2
UNION ALL
SELECT TOP (5984) 2
FROM   master..spt_values v1, master..spt_values v2
UNION ALL
SELECT TOP (14276) 3
FROM   master..spt_values v1, master..spt_values v2

/*Create statistics. The location_id histograms don't intersect at all*/
UPDATE STATISTICS location(locationpk) WITH FULLSCAN;
UPDATE STATISTICS testruns(IX) WITH FULLSCAN;

/* UPDATE location.id. Three row update is below recompile threshold*/
UPDATE location
SET    id = id - 100

Then running the following queries gives the same estimated vs actual discrepancy

SELECT *
FROM   testruns AS tr
WHERE  tr.location_id = (SELECT id
                         FROM   location
                         WHERE  location = 'Derby')

SELECT *
FROM   testruns AS tr
       JOIN location loc
         ON tr.location_id = loc.id
WHERE  loc.location = ( 'Derby' )


Update records in Postgres from a select query

I've set up next example:

create table sports(id int, status int, createdBy int, createdDate date);
create table music(id int, status int, createdBy int, createdDate date);
insert into sports values (1, 1, 101, '20170101'), (2, 2, 101, '20170102'), (3, 1, 102, '20170103');
insert into music  values (1, 1, 101, '20170201'), (2, 2, 101, '20170202'), (3, 1, 102, '20170203');

with sel as
(
    select ttype, id, status, createdBy, createdDate
    from
        (
        (select 'sports' as ttype, id, status, createdBy, createdDate
        from   sports
        where  status = 1
        and    createdBy = 101
        order  by createdDate
        limit  1)
        union
        (select 'music' as ttype, id, status, createdBy, createdDate
        from   music
        where  status = 1
        and    createdBy = 101
        order  by createdDate
        limit  1)
        ) un
    order by 5
    limit 1
)
    , updsport as
    (
        update sports
        set    status = 5
        from   sel
        where  sports.id = sel.id
        and    sel.ttype = 'sports'
    )
    update music
    set    status = 5
    from   sel
    where  music.id = sel.id
    and    sel.ttype = 'music'
    ;

select * from sports order by id;

id | status | createdby | createddate
-: | -----: | --------: | :----------
 1 |      5 |       101 | 2017-01-01
 2 |      2 |       101 | 2017-01-02
 3 |      1 |       102 | 2017-01-03

select * from music order by id;

id | status | createdby | createddate
-: | -----: | --------: | :----------
 1 |      1 |       101 | 2017-02-01
 2 |      2 |       101 | 2017-02-02
 3 |      1 |       102 | 2017-02-03

Now try by changing createdDate on sports or music table just to change the filter criteria.

dbfiddle here


I may get dinged for this, I may not, but if you don't have time I've got a fantastic solution for you. Download Visual Studio LightSwith - you can build a database application in less than five minutes and have it deployed in less than ten - I've done it.

Make time, find the right tools, but do not give them the keys to the database.

In fact, LightSwitch is so dead simple, you could teach some other folks there how to use it at some level.