Are you using Oracle 7? If not, please make sure to always use current versions of documentation. Oracle is already on 12C, and while some concepts still the same, a huge part of features has changed.

You actually won't get column size. This is why it is only an "estimate". You need to have an idea of what you are going to need, and then you can use the package dbms_space with the create_table_cost procedure to get an idea of space required.

For example:

I need to create a table with 3 columns (val1, val2, val3), and I know each one is going to take up 4bytes (3 of literals, like abc, and the leading byte that varchar2 takes). I also estimate that this table will have approximately 1000 rows. Now just think about cartesian graph:

row_count
^
|
|
|
|-----------> avg_row_len

and use the following to estimate the size:

 set serveroutputon
    DECLARE
      estimated_used_size   NUMBER;
      allocated  NUMBER;
    BEGIN
        DBMS_SPACE.create_table_cost (
        tablespace_name => 'USERS',
        avg_row_size    => 12,
        row_count       => 1000,
        pct_free        => 10,
        used_bytes      => estimated_used_size,
        alloc_bytes     => allocated);

      DBMS_OUTPUT.put_line ('the table (TS=USERS): will use =' || estimated_used_size ||' bytes  to be allocated =' ||  allocated);
end;
/

Regarding your last question, to estimate the size of an existing table, you can check the view dba_segments (also to query index and other objects size).

Example:

SQL> SELECT bytes/1024/1024 "Size in MB" FROM dba_segments WHERE segment_name = 'TABLE_NAME_HERE' AND segment_type = 'TABLE';


The key words here are:

"heavily updated"
"in the table for 2-3 hours".

Point 1. is indication for a lower fill factor, while 2. is the opposite. It helps performance if multiple row versions are stored on the same data page. H.O.T. updates would achieve that. Read here or here. They need some wiggle room on the data page - like dead tuples or space reserved by a fillfactor < 100. But they can only do their thing, if no index involves any of the updated columns, which should be true for your case.

Another important factor here would be the tuple size (in comparison to your page size (which is most commonly 8 kb). More details in this related answer:

Measure the size of a PostgreSQL table row

If the tuple size is 4 kb or more, reducing the fill factor would be futile, since there can never be more than one tuple on a data page. You might as well leave it at 100 (which is the default anyway). However, some data types are "toasted" and stored out-of-line if they exceed a size limit, so tuples requiring that much in the main relation fork are rare.

Whatever you do, VACUUM will be run often. And that's generally a good thing, I wouldn't worry about that. You create lots of dead tuples. VACUUM identifies dead rows that are not visible to any open transaction any more. The manual:

  The standard form of VACUUM removes dead row versions in tables and
  indexes and marks the space available for future reuse.

Bold emphasis mine.
You can play with per-table settings for autovacuum to trigger it less (or more) often for this table only:

  The default thresholds and scale factors are taken from
  postgresql.conf, but it is possible to override them on a table-by-table basis;

Bold emphasis mine. In particular with autovacuum_vacuum_threshold and autovacuum_vacuum_scale_factor. Running VACUUM a lot might actually be a good idea, instead of a very low fillfacter. That depends on access patterns. If all tuples live, say, 3 hours and each is updated several times, I would still lower the fillfactor to something like 50. You'll have to test and find the sweet spot.

Alternatives

All this aside, since your data seem to be volatile to begin with: use an UNLOGGED table:

  Data written to unlogged tables is not written to the write-ahead log
  (see Chapter 29), which makes them considerably faster than
  ordinary tables. However, they are not crash-safe: an unlogged
  table is automatically truncated after a crash or unclean shutdown.
  The contents of an unlogged table are also not replicated to standby servers.

Bold emphasis mine. Don't use this if your server might crash and you still need the data afterwards. But if we are talking about session data for web applications, this might be an acceptable price to pay.

Or, even more radical: Use a key-value store like Redis if you can do without the features and security provided by an RDBMS altogether.


Stop PostgreSQL from logging statements on error

The amount of details logged is controlled by log_error_verbosity. Setting it to TERSE will exclude DETAIL, HINT, QUERY, and CONTEXT error information:

ALTER DATABASE db_name SET log_error_verbosity to 'TERSE';


How to validate DB Users/logins across Availability Group by script

You can use this addin (AG Addin) for SMSS, it copies:

User-defined Server Roles
Server Logins and Permissions
Linked Servers
SQL Server Agent Operators
SQL Server Agent Alerts
SQL Server Agent Jobs


Database restore using SSMS leaves source database in restoring state

This is "normal" behaviour in SSMS if you check the box "Take tail-log backup before restore" and the box "Leave source database in the restoring state"

It may not be intuitive and it's probably not the best decision ever to check those boxes by default but you have to un-check the box "leave source database in restoring state" to avoid that behaviour:

So it's not a bug, it's standard SSMS behaviour that changed in a more recent version (I believe those options appeared in 2012 but I'm not sure).

If you want to avoid strange or buggy behaviour in SSMS your best option would be to perform your restores using T-SQL scripts so you have something reproducible even when you change your SSMS version.

See this connect item which clearly states this is by design.


Backup MS SQL AG on a non-production environment

So as a preface for this answer, please consider the following points:

If your system is meant to be HIGHLY AVAILABLE, what are your plans for inevitable down-time and data loss? Will the time taken to recover this environment mean it will simply be faster to keep backups?
If your system is simply a copy of production, why are you not mirroring your backup jobs on that system?

The above may highlight why you do in fact want to set up proper backup jobs. This said, here's a really bad idea™.

BACKUP LOG [DBName] TO DISK = N'nul';
BACKUP DATABASE [DBName] TO DISK = N'nul';

This uses the nul device to stream a backup into nothingness. It should take a similar time to how long the read phase of the backup takes.

N.B. The above should not go anywhere near any production systems - you could end up losing data, and your job. The backups taken using this code (and therefore your backup chain) are not recoverable in any way.


Equivalent to Azure SQL Threat Detection for on-premise database

Azure Security Center already monitors the security posture of non-Azure computers by on boarding VMs and computers running on-premises by simply installing the Microsoft Monitoring Agent on these machines. Currently this offering includes OS Advanced Threat Detection using behavioral analytics and machine learning to identify attacks and zero-day exploits on the Windows Operation System layer. As part of our road-map we plan to add SQL Advanced Threat Detection, which will detect anomalous database activities indicating unusual and potentially harmful attempts to breach or exploit SQL severs.

https://docs.microsoft.com/en-us/azure/security-center/security-center-onboarding


Multi part identifier in Microsoft SQL Server

As you say the query doesn't make any sense.

Probably just a parser bug in 6.5. There have been a few similar other issues ORDER BY nonsensealias.somecolumn worked until 2005 with the SQL Server 2000 behavior being

  Column names in the ORDER BY clause are resolved to columns listed in
  the select list, regardless if they are qualified. For example, the
  following query executes without error:

USE pubs
SELECT au_fname AS 'FName',
  au_lname AS 'LName'
FROM authors a
ORDER BY a.LName

  SQL Server ignores the qualifier a in the ORDER BY clause and resolves
  the column name LName to the select list.

and there is still a similar issue for INSERT statements where the following syntax is accepted.

CREATE TABLE nisse(hult int NOT NULL)
go
INSERT nisse(ab.xx.hult)
VALUES(12)
go
DROP TABLE nisse


How to Skip Existing Data While Restoring mysqldump File?

I have four(4) suggestions:
SUGGESTION #1 : Recreate dump with INSERT IGNORE
Use --insert-ignore

Write INSERT IGNORE statements rather than INSERT statements.

What happens on reload ???

New data gets inserted.
Already existing primary keys are skipped

SUGGESTION #2 : Change INSERT to INSERT IGNORE on reload
Instead of loading normally
mysql -u root -p database_name < database_name.sql

just pipe the dump into sed and reload like this:
cat database_name.sql|sed 's/^INSERT/INSERT IGNORE/'|mysql -u root -p database_name

SUGGESTION #3 : Recreate dump with REPLACE INTO
Use --replace

Write REPLACE statements rather than INSERT statements.

What happens on reload ???

New data gets inserted.
Already existing primary keys will be deleted and re-inserted

SUGGESTION #4 : Change INSERT INTO to REPLACE INTO on reload
Instead of loading normally
mysql -u root -p database_name < database_name.sql

just pipe the dump into sed and reload like this:
cat database_name.sql|sed 's/^INSERT INTO/REPLACE INTO/'|mysql -u root -p database_name

SUMMARY

Use suggestions 1 or 3 if you can recreate the dump
Use suggestions 2 or 4 if you cannot recreate the dump

GIVE IT A TRY !!!


You might try scripting it out to see if you get a different behaviour.  This simple example runs in under a minute on my local SQL 2005 instance:

USE master
GO

SET NOCOUNT ON
GO

IF EXISTS ( SELECT * FROM sys.databases WHERE name = 'fullTextDemo3' )
BEGIN
    ALTER DATABASE fullTextDemo3 SET SINGLE_USER WITH ROLLBACK IMMEDIATE
    DROP DATABASE fullTextDemo3
END
GO

IF NOT EXISTS ( SELECT * FROM sys.databases WHERE name = 'fullTextDemo3' )
CREATE DATABASE [fullTextDemo3]
GO

ALTER DATABASE fullTextDemo3 SET RECOVERY SIMPLE
GO

USE fullTextDemo3
GO

CREATE TABLE dbo.nearnessTest (
    rowId           INT IDENTITY,
    keywords        VARCHAR(MAX) NOT NULL,
    notes           VARCHAR(100) NOT NULL,

    CONSTRAINT PK_nearnessTest PRIMARY KEY ( rowId )
)
GO

INSERT dbo.nearnessTest
--SELECT TOP 270000 m1.[text], ''
SELECT m1.[text], ''
FROM sys.messages m1
--  CROSS JOIN sys.messages m2
WHERE m1.language_id = 1033
  --AND m2.language_id = 1033
GO

-- Create the catalog
IF NOT EXISTS ( SELECT * FROM sys.fulltext_catalogs WHERE name = N'ftc_List3' )
CREATE FULLTEXT CATALOG ftc_List3
GO

-- Create the full-text index
CREATE FULLTEXT INDEX ON dbo.nearnessTest (keywords) KEY INDEX PK_nearnessTest ON ftc_List3 WITH CHANGE_TRACKING MANUAL -- CHANGE_TRACKING OFF, NO POPULATION
GO

SELECT 'before' ft, * FROM sys.fulltext_indexes
GO

DECLARE @i INT
SET @i = 0

WHILE EXISTS ( SELECT * FROM sys.fulltext_indexes WHERE has_crawl_completed = 0 )
BEGIN

        SELECT outstanding_batch_count, *
        FROM sys.dm_fts_index_population
        WHERE database_id = DB_ID()

        --SELECT *
        --FROM sys.dm_fts_outstanding_batches
        --WHERE database_id = DB_ID()

    WAITFOR DELAY '00:00:05'

    SET @i = @i + 1
    IF @i > 60 BEGIN RAISERROR( 'Too many loops!', 16, 1 ) BREAK END

END

SELECT 'after' ft, * FROM sys.fulltext_indexes
GO

SELECT *
FROM dbo.nearnessTest
WHERE CONTAINS( keywords, 'error' )

What happens when you issue an explicit start for the index population, eg

ALTER FULLTEXT INDEX ON dbo.yourTable START FULL POPULATION;

Run these summary functions

SELECT FULLTEXTSERVICEPROPERTY ( 'IsFulltextInstalled' ) IsFulltextInstalled
SELECT DATABASEPROPERTY('fullTextDemo3','IsFulltextEnabled') IsFulltextEnabled
SELECT FULLTEXTCATALOGPROPERTY('ftc_List3', 'AccentSensitivity ') AS AccentSensitivity
SELECT FULLTEXTCATALOGPROPERTY('ftc_List3', 'IndexSize ') AS IndexSize
SELECT FULLTEXTCATALOGPROPERTY('ftc_List3', 'ItemCount ') AS ItemCount
SELECT FULLTEXTCATALOGPROPERTY('ftc_List3', 'LogSize ') AS LogSize
SELECT FULLTEXTCATALOGPROPERTY('ftc_List3', 'MergeStatus ') AS MergeStatus
SELECT FULLTEXTCATALOGPROPERTY('ftc_List3', 'PopulateCompletionAge ') AS PopulateCompletionAge
SELECT FULLTEXTCATALOGPROPERTY('ftc_List3', 'PopulateStatus ') AS PopulateStatus
SELECT FULLTEXTCATALOGPROPERTY('ftc_List3', 'UniqueKeyCount ') AS UniqueKeyCount
SELECT FULLTEXTCATALOGPROPERTY('ftc_List3', 'ImportStatus ') AS ImportStatus


SQL not engaging parallelism for extremely large query

What's weird is while experimenting, we even dropped the threshold back down to the default value of 5 seconds, and it still won't run parallel. What could be preventing it?

There are many reasons that the optimizer will not (or cannot) produce a parallel plan. Broadly:

Obvious causes, such as the configuration setting max degree of parallelism being set to one, running under a Resource Governor workload group with MAX_DOP = 1, or having only one logical processor available to SQL Server
Parallelism-inhibiting operations

There are many operation that prevent parallelism, either because they make no sense in a parallel plan, or because the product just does not support them (yet).  Some examples (not exhaustive!) of things that force the whole plan to be serial:

Modifying the contents of a table variable (reading is fine)
Any T-SQL scalar function (which are evil anyway)
CLR scalar functions marked as performing data access (normal ones are fine)
Some intrinsic functions including OBJECT_NAME, ENCYPTBYCERT, and IDENT_CURRENT
Fast Forward cursors
System table access (e.g. sys.tables)

Any reference to a table (or view) with a computed column that uses a non-inline T-SQL scalar function will result in a serial plan, even if the that column is not referenced in the query.
Cardinality estimation errors

If there is nothing that absolutely prevents parallelism in the target query, the optimizer may still choose a serial alternative if it has a lower estimated cost. This can be caused by incorrect cardinality estimation.
Costing model limitations

SQL Server uses a model to estimate the runtime cost of each operator in a query plan. This model may incorrect cost a serial plan cheaper than the parallel alternative. The optimizer always chooses the cheapest-looking option it considers.

When SQL Server costs a parallel plan, it generally reduces the CPU cost for a parallel iterator by the a factor equal to the expected runtime DOP. Plans with Nested Loops joins can be a special problem, because the inner side almost always runs multiple threads serially. The parallelism icons are still present, but they indicate that there are DOP independent serial threads.

The distinction is perhaps a subtle one, but it (a) explains why operators that normally force a serial zone can run 'in parallel' on the inner side of a loops join; and (b) the optimizer does not reduce the CPU costs on the inner side by the estimated runtime DOP. This puts nested loops at a disadvantage when it comes to parallelism costing, compared with Hash and Merge Joins, and can explain why the optimizer may fail to choose a parallel Nested Loops plan.
Optimizer code path issues

The optimizer may not get as far as evaluating a parallel plan at all.  One way this can occur is if the final plan is found during the Trivial Plan stage.  If a Trivial Plan is possible, and the resulting cost is less than the configured cost threshold for parallelism, the full optimization stages are skipped and a serial plan is returned immediately.

Queries that pass Trivial Plan may still terminate optimization early (before parallelism is considered), either with a Good Enough Plan Found message, or a Time Out. Both of these are heuristics to prevent the optimizer spending more time optimizing than it stands to gain by reducing estimated execution time (cost).

Testing

There is no supported way to require a parallel plan but there are a couple of undocumented tricks (not suitable for production). One is to temporarily set the CPU weighting used during optimization much higher, and the other is to set trace flag 8649. For SQL Server 2016 SP1 CU2 and later, the undocumented query hint OPTION (USE HINT ('ENABLE_PARALLEL_PLAN_PREFERENCE')) performs the same function as TF 8649, but without the need for admin permissions.

The plan produced might not be one the optimizer would normally consider, but you may be able to capture it and use it in a plan guide in production after careful testing and review.

For more information, see my article Forcing a Parallel Query Execution Plan and Non Parallelizable operations in SQL Server by Simon Sabin.


What table should I host data linking two objects from separate tables?

If Users can only be members of one group, you need only store a GroupId in the Users table. If not, the pattern you are looking for is commonly known as a "Join" table, containing only foreign keys and perhaps a generated primary key.

If you have primary key UserId (uniquely identifies a user record) for the Users table and GroupId for the Groups table, you can create a third table:

CREATE TABLE [dbo].[GroupUsers](
    [UserId] [int] NOT NULL,
    [GroupId] [int] NOT NULL
) ON [PRIMARY]

GO

ALTER TABLE [dbo].[GroupUsers]  WITH CHECK ADD  CONSTRAINT [FK_GroupUsers_Groups] FOREIGN KEY([GroupId])
REFERENCES [dbo].[Groups] ([GroupId])
GO

ALTER TABLE [dbo].[GroupUsers] CHECK CONSTRAINT [FK_GroupUsers_Groups]
GO

ALTER TABLE [dbo].[GroupUsers]  WITH CHECK ADD  CONSTRAINT [FK_GroupUsers_Users] FOREIGN KEY([UserId])
REFERENCES [dbo].[Users] ([UserId])
GO

ALTER TABLE [dbo].[GroupUsers] CHECK CONSTRAINT [FK_GroupUsers_Users]
GO

This design allows for one-to-one, one-to-many and many-to-many relationships. In practice, its performance and scalability are excellent, as long as the tables are properly indexed.

To get all users in a group given its GroupId:

SELECT *
FROM Users u
INNER JOIN GroupUsers gu
ON u.UserId = gu.UserId
WHERE gu.GroupId = @GroupId

Many times the relationships need to be traversed across the foreign key table. For instance, to select the groups a user belongs to, given their username (assuming there is a Username field in Users):

SELECT g.*
FROM Groups g
INNER JOIN GroupUsers gu
ON g.GroupId = gu.GroupId
INNER JOIN Users u
ON gu.UserId = u.UserId
WHERE u.Username = @Username

I usually use views to simplify queries that need this sort of traversal.


MINUS is a name for the "set difference" operator that is used only by Oracle.

SQL Server uses the (standard) name, EXCEPT, for this operator. Replacing the MINUS with EXCEPT will solve the issue:

SELECT Customer_Name
FROM Customer
WHERE Customer_No
  IN
  (SELECT Customer_No FROM Customer
   EXCEPT
   SELECT Customer_No FROM DVD_Purchase
  ) ;

By the way, since the external query and the subquery reference the same table+column (Customer . Customer_No) you could use NOT IN without EXCEPT. Assuming that the columns involved (Customer_No) are not nullable, this is equivalent:

SELECT Customer_Name
FROM Customer
WHERE Customer_No
  NOT IN
  (SELECT Customer_No FROM DVD_Purchase
  ) ;


A. Oracle searches for a init.ora to determine the configuration
parameters

without a parameterfile, you will get an LRM-00109, ORA-01078 error, and nothing gets started

E. oracle starts an instance

we could argue what exactly starting an instance means, I have just simply put it here because this is the "exact" message that appears first when starting an instance

C. memory for the SGA gets allocated and the background processes that are necessairy get started

background processes like LGWR, DBWR, CKPT, SMON, PMON start in NOMOUNT, way before doing anything with control files...

F. oracle searches for the control files with the parameter CONTROL_FILES

thats the next one when going to MOUNT

D. Oracle searches the database files and redo log files

now tell me, how could the database perform an instance recovery without knowing where the datafiles and redo logs are

B. The proces SMON sometimes executes an instance-recovery

finally an instance recovery is performed, and database gets opened


XMLTABLE solution without getting right parenthesis missing error

You might have wanted this:

WITH XML_DATA AS
(SELECT XMLTYPE('<?xml version="1.0" encoding="UTF-8"?>
<response>
    <phone>
        <number>859111</number>
        <contactModes>
            <mode>AUTO_DIAL</mode>
            <mode>AUTO_TEXT</mode>
            <mode>MANUAL_DIAL</mode>
        </contactModes>
        <contactableTimeRange>
            <day1>
                <startTime>08:00:00</startTime>
              <endTime>20:00:00</endTime>
            </day1>
            <day2>
                <startTime>08:00:00</startTime>
                <endTime>20:00:00</endTime>
            </day2>
        </contactableTimeRange>
    </phone>
</response>') doc
FROM DUAL)
SELECT X.phone
,x2."MODE"
,To_Number (SubStr (x3.Day, 4)) Day
,x3.starttime
,x3.endtime
/*,x4.* */
FROM XML_DATA XD,
XMLTABLE('/response/phone' PASSING XD.DOC
     COLUMNS phone  VARCHAR2(36) PATH 'number',
            CONTACT_MODES XMLTYPE PATH 'contactModes',
            CONTACT_TIMES XMLTYPE PATH 'contactableTimeRange')  X
   , XMLTABLE('/contactModes/mode'
   passing x.contact_modes
   COLUMNS "MODE" VARCHAR2(10) Path '.' ) AS  x2
   ,XMLTABLE('/contactableTimeRange/*' PASSING X.CONTACT_TIMES
            COLUMNS Day VARCHAR2(10) PATH 'name()',
                    STARTTIME VARCHAR2(10) PATH 'startTime',
                    ENDTIME VARCHAR2(10) PATH 'endTime') AS X3

Which produces:

    PHONE                                MODE              DAY STARTTIME  ENDTIME
    ------------------------------------ ---------- ---------- ---------- --------
    859111                               AUTO_DIAL           1 08:00:00   20:00:00
    859111                               AUTO_DIAL           2 08:00:00   20:00:00
    859111                               AUTO_TEXT           1 08:00:00   20:00:00
    859111                               AUTO_TEXT           2 08:00:00   20:00:00
    859111                               MANUAL_DIA          1 08:00:00   20:00:00
    859111                               MANUAL_DIA          2 08:00:00   20:00:00


Log to disk all queries that waited for a lock over a period of time

You can do this by setting long_query_time as follows:
If you want it done permanently set it in mysql.cnf in the [server] section.
long_query_time=123.123 (down to millisecond precision)
From here - you can also set it dynamically.
Issue the command (from the mysql CLI (Command Line Interface) client):
SHOW VARIABLES LIKE '%long%'

or similar and set it. You can find out and/or set the destination file by issuing;
SHOW VARIABLES LIKE '%slow%'

Check out the db<>fiddle here!


How to collapse rows with individual, contiguous dates into single rows with date range start and end

WITH
cte1 AS ( SELECT Id, xDate, Value,
                 CASE DATEDIFF( DAY, LAG(xDate) OVER (PARTITION BY Id ORDER BY xDate), xDate )
                 WHEN 1
                 THEN 0
                 ELSE 1 END delta
          FROM test ),
cte2 AS ( SELECT Id, xDate, Value, SUM(delta) OVER (ORDER BY Id, xDate) grp
          FROM cte1 )
SELECT Id, MIN(xDate) startDate, MAX(xDate) endDate, SUM(Value) sumValue
FROM cte2
GROUP BY id, grp;

fiddle


How can I reference column aliases from a projection inside the projection?

The usual way to cope with that is to use a derived table (aka sub-query):

select x, y, x/y as markup
from (
  select sellprice as x, cost as y
  from mytable
) as t;


The problem is apparently not related to just SQL 2012; it has been reported on older versions also.

One guess is that your copy of SQL Server is probably corrupt. You could try downloading from a different source and checking if the issue repeats. The new SQL Server download might have all the required files in good condition, and save you the headache of any issues down the line. Of course, you should uninstall everything before attempting a fresh install from a different source.

You mentioned that it was Enterprise Edition? Assuming that it's a licensed copy,  you can also call Microsoft Support and ask them for assistance.


Power BI on-prem data gateway for Azure devops pipeline?

I have succeeded in getting the Azure devops pipeline to create releases and deploy to on-prem resources (sql servers) .

The overall solution was indeed to install and configure a self-hosted windows agent for azure pipeline. This agent vm has the redgate powershell components installed on it.

Some interesting hurdles that had to be overcome :

1) when running the .\config.cmd to install and configure the agent, it has to be run in a regular powershell cmd shell. ISE doesn't work.

2) copy and paste didn't work to get my PAT into the powershell window when running config.cmd. I actually had to type it in.

I'd love to see the MS docs get cleaned up a bit. They were pretty unclear in relation to what I saw on my screen in .visualstudio.com

Cheers.


Compression in MongoDb 3.2 does not work

--version 3.4.10 Enterprise

compression will not change the avgObjSize. It will just improve the compression ratio saving disk space; I tested it for a collection (documents: 11553423 and avgObjSize= 6182) worth 21GB  with snappy, when same created with zlib, size of collection reduced to 12GB (avgObjSize= 6182).


Is there a way to "SELECT and TRUNCATE" without losing new data?

There is more than one possibility here.

One is what Peter Eisentraut suggested in the post you linked, using a writable CTE which deletes the rows from the table which will be processed or inserted elsewhere in the same query.

This approach may be viable with small datasets.  However, there are chances that some table bloat will occur with time, or concurrent inserts and deletes clash on the disk I/O level.

Another way to solve this is using transaction blocks, as you also suggested.  If there are open transactions (that do the inserts, for example) on new, the TRUNCATE command will wait until these are closed, then it will remove everything.  This means that the problem you anticipate really exists :)

Consider the following:

BEGIN;

LOCK new; -- takes an exclusive lock

INSERT INTO staging_table SELECT * FROM new;

TRUNCATE new;

COMMIT;

In this case, the exclusive lock will make all concurrent INSERT statements wait until the COMMIT.  As the INSERT in this block should be relatively fast, the waiting time won't be terribly long, while your existing data is safely moved to an other table.  Then comes the TRUNCATE, finally the COMMIT will release the lock and the waiting inserts can be done.

This way you can move the data processing from new to the newly introduced table.


Optimizing a four-year-old MySQL database

I want to optimize the database as I think in these years many records
  have been deleted and many added.

You have some other reason for wanting to fiddle with a database that is "running well"?

Good reasons might include:

The backups are getting unmanageably large
The customer has mentioned a specific area of performance they want improved

If you don't have a good reason, then my advice would be to do nothing


How to find similar records using multidimensional array?

Just focusing on the database and ignoring the statistics issues, and assuming the 5 days you use is a design decision and not just ephemeral example, and that we can ignore anything with an incomplete record, then you can do it like this:

Set up with random data:

create table movie as select x as movie_id, y as release_day, floor(random()*1000)::int as showtime_count, floor(random()*100000)::int as ticket_sold_count
from generate_series(1,1000000) f(x), generate_series(1,6) g(y);

denormalize and index:

create materialized view release as
    select movie_id,
           cube(
              array_agg(showtime_count order by release_day)||
              array_agg(ticket_sold_count order by release_day)
            ) as datavector
    from movie where
        release_day <=5
    group by movie_id
    having count(release_day)=5 and
           count(distinct release_day)=5;

create index on release using gist (datavector);

And use:

explain (analyze,buffers) select * from release order by datavector <-> '(230, 16, 178, 507, 366, 8798, 4724, 1736, 5765, 808)' limit 1;
                                                                   QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=0.41..0.59 rows=1 width=44) (actual time=1.800..1.802 rows=1 loops=1)
   Buffers: shared hit=95
   ->  Index Scan using release_datavector_idx on release  (cost=0.41..176884.39 rows=999999 width=44) (actual time=1.798..1.798 rows=1 loops=1)
         Order By: (datavector <-> '(230, 16, 178, 507, 366, 8798, 4724, 1736, 5765, 808)'::cube)
         Buffers: shared hit=95
 Planning Time: 0.091 ms
 Execution Time: 1.841 ms


I've encountered this issue before and in previous cases for us the solution was to develop a system of ETL processes which bring the information over into the warehouse or reporting database. What we did was design the process to run near continuously and at the head of each iteration we would grab the current maximum datetime stamp of each table. We would then process each record that was generated or updated between the last timestamp and the current one we just retrieved then repeat this process constantly. There is a chance with this for partial data, but with the process running continuously any partial data would be resolved within a few minutes.


Joining and filtering two sets of the same table

Assuming:

p_id is unique
'last purchase' is defined by highest p_id

SQL Fiddle

MySQL 5.5.32 Schema Setup:

create table t(p_id integer, item_id integer, user_id integer, cnt integer);

insert into t(p_id, item_id, user_id, cnt) values(1,4,1,22);
insert into t(p_id, item_id, user_id, cnt) values(2,4,2,1);
insert into t(p_id, item_id, user_id, cnt) values(3,1,1,1);
insert into t(p_id, item_id, user_id, cnt) values(4,0,3,1);
insert into t(p_id, item_id, user_id, cnt) values(5,3,1,182);
insert into t(p_id, item_id, user_id, cnt) values(6,0,4,1);
insert into t(p_id, item_id, user_id, cnt) values(7,3,2,7);
insert into t(p_id, item_id, user_id, cnt) values(8,3,2,14);

Query 1:

select *
from( select *
      from t tt
      where user_id=1 and p_id=( select max(p_id)
                                 from t
                                 where user_id=1 and item_id=tt.item_id )
      union all
      select *
      from t tt
      where user_id=2 and p_id=( select max(p_id)
                                 from t
                                 where user_id=2 and item_id=tt.item_id ) ) u
where item_id in(select item_id from t where user_id=1)
      and item_id in(select item_id from t where user_id=2)

Results:

| P_ID | ITEM_ID | USER_ID | CNT |
|------|---------|---------|-----|
|    1 |       4 |       1 |  22 |
|    5 |       3 |       1 | 182 |
|    2 |       4 |       2 |   1 |
|    8 |       3 |       2 |  14 |


You could use a script component which is supported in 2016 and previous versions.

Make sure that when you add the component you select "source" as the type and not destination or transformation.

Then you add a reference to System.Web.Extensions.dll to the task and write some code to consume the webservice.

This blog post looks like it documents the process nicely, but the key is that you consume the json and override the CreateNewOutputRows method to send whatever you are receiving to the task's output. The output can be connected to any SSIS component accepting a data flow input.

If you insist on using a source component there is JSONSource on codeplex but they don't support SQL 2016 yet, but I don't think it's necessary to add a JSON component if you are only consuming a single service and it's not really complicated to implement yourself.


Problem structuring database for an assessment rubric app

So you'll have students with an primary id student_id. Assume courses have a primary id course_id.

So each course will have its own assessment as per the first column in the rubric.

 create table courses_info (
   course_id int unsigned not null,
   assessment_id int unsigned not null,
   assessment varchar(80) not null,
   primary key (course_id, assessment_id)
 )

Student assessments take student_id, course_id, assessment_id and store an assessment_result (assumed to be uniform over the entire institution):

 create table student_assessment (
   course_id int unsigned not null,
   assessment_id int unsigned not null,
   student_id int unsigned not null,
   assessment_result enum('Emergent',..., 'Excellent'),
   primary key (course_id, assessment_id, student_id)
 )

The rubric is the mapping between an assessment_id for each course_id with the learning_outcome being the element in the lookup table:

 create table rubric (
   course_id int unsigned not null,
   assessment_id int unsigned not null,
   assessment_result enum('Emergent',..., 'Excellent'),
   learning_outcomes text,
   primary key (course_id, assessment_id, assessment_result)
 )

To get the results you query and JOIN on the required columns:


Mapping Fields based on unknown relationship

You can use FOR XML PATH to help construct a dynamic query:

-- Generate a list of field names to be used in the SELECT statement
DECLARE @Sql VARCHAR(MAX) =
(
    SELECT '[' + FieldNumber + '] AS [' + FieldName + '],'
    FROM Datas
    ORDER BY FieldName DESC -- Sample column ordering
    FOR XML PATH('')
)

-- Generate a SELECT query; make sure to remove the comma after the last field
SET @Sql = 'SELECT ' + SUBSTRING(@Sql,1, LEN(@Sql) - 1) + ' FROM dbo.ScannedDataCapture'

-- Show SQL before execution
PRINT @Sql
EXEC sp_sqlexec @Sql


IDERA suggests to alter procedures in tempdb

There's no harm in doing it, but it'll just go away next time the service is restarted. If it's something that really needs to be done, you'd have to alter the code that is creating the procedure, wherever that lives. It could be they are in the model database.


Efficient way to handle multiple CASE statements in SELECT

I'm assuming that you have appropriate indexes on the tables in the subqueries. I mocked up some quick test data and put 10 million rows in table A. I wasn't game to create 30 tables so I just created 3 for the CASE expression. I think that 3 is enough to show the general principles.

DROP TABLE IF EXISTS dbo.B;
CREATE TABLE dbo.B (
    ClientID INT NOT NULL,
    Id VARCHAR(20) NOT NULL,
    [Value] VARCHAR(100),
    PRIMARY KEY (ClientID, Id)
);

INSERT INTO B VALUES (100, '1', 'TABLE B');

DROP TABLE IF EXISTS dbo.C;
CREATE TABLE dbo.C (
    ClientID INT NOT NULL,
    Id VARCHAR(20) NOT NULL,
    [Value] VARCHAR(100),
    PRIMARY KEY (ClientID, Id)
);

INSERT INTO C VALUES (100, '2', 'TABLE C');

DROP TABLE IF EXISTS dbo.D;
CREATE TABLE dbo.D (
    ClientID INT NOT NULL,
    Id VARCHAR(20) NOT NULL,
    [Value] VARCHAR(100),
    PRIMARY KEY (ClientID, Id)
);

INSERT INTO D VALUES (100, '3', 'TABLE D');

DROP TABLE IF EXISTS dbo.A;
CREATE TABLE dbo.A (
    column1 VARCHAR(20) NOT NULL
);

INSERT INTO dbo.A WITH (TABLOCK)
SELECT CAST(1 + t.RN % 3 AS VARCHAR(20))
FROM
(
    SELECT TOP (5000000) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) RN
    FROM master..spt_values t1
    CROSS JOIN master..spt_values t2
) t;

I disabled result sets and ran the following query in SSMS:

SELECT A.column1
FROM A;

It took around 0.723 seconds. I'm doing pretty unscientific tests because I don't know anything your data. In any case, with serial queries we can't expect a better result than 0.7 seconds. That's our baseline.

The most efficient way to write this query is without joins at all. The key is that the CASE expression is only ever going to return 3 (or 30) unique values if it finds a match. You can save off the results into local variables and just use those in the query. The query below finishes in around 1.044 seconds:

DECLARE @B_VALUE VARCHAR(100) = (select value from B where B.clientId=100 and B.Id = '1');
DECLARE @C_VALUE VARCHAR(100) = (select value from C where C.clientId=100 and C.Id = '2');
DECLARE @D_VALUE VARCHAR(100) = (select value from D where D.clientId=100 and D.Id = '3');

SELECT
(case when A.column1='1' then @B_VALUE
      when A.column1='2' then @C_VALUE
      when A.column1='3' then @D_VALUE
      -- omitted other columns
else column1 end)
FROM A;

The plan is very simple:

For another option you can write the query with joins (where we can rewrite the CASE expression in a more compact form, using COALESCE(). This finished in around 2.314 seconds:

SELECT
    COALESCE(B.column1, C.column1, D.column1, -- omitted other columns
             A.column1)
    -- (case A.column1
    --    when '1' then B.value
    --    when '2' then C.value
    --    when '3' then D.value
    --    -- omitted other columns
    -- else A.column1 end)
FROM A
LEFT JOIN B ON B.clientId=100 and B.Id = '1'
LEFT JOIN C ON C.clientId=100 and C.Id = '2'
LEFT JOIN D ON D.clientId=100 and D.Id = '3';

Here's the plan:

You can get a nearly identical runtime and query plan by writing the query like this:

SELECT
(case A.column1
   when '1' then (select value from B where B.clientId=100 and '1'=B.Id)
   when '2' then (select value from C where C.clientId=100 and '2'=C.Id)
   when '3' then (select value from D where D.clientId=100 and '3'=D.Id)
-- omitted other columns
else column1 end)
FROM A;

The original query in the question has an issue: SQL Server is doing a useless sort before the nested loop join. That query finishes in around 5.838 seconds on my machine.

Trace flag 8690 eliminates the sort as well as the table spools. The query runs in about 7.479 seconds with trace flag 8690, so I suppose that the spools are helpful for this query.


100,000 tenants, so we're anticipating average of 20,000 companies per tenant and 40,000 people per tenant. Hosted in Azure using SQL Azure Federation services

You do realize that your number comes up to 2 billion entries?

I'm afraid the answer is that there are several ways to accomplish what you want to achieve.  You can have a master table with your company information and then sub tables containing the users under each company.  Keep in mind on incoming queries, the more joins you have to make, the longer the response time.

This is a massive design effort that will not be easily answered in this format and should be reviewed by a design team in several disciplines.

I would recommend splitting them into different logical groups either by tenant, company etc.... The main reason being any DUI ( Delete, Update, Insert) will lock the records as it runs which can potentially cause problems


In SQL Server, I would write the following query:

SELECT *
FROM dbo.driver d
    LEFT JOIN dbo.driver_has_device dhd ON d.id = dhd.driver_id
WHERE dhd.driver_id IS NULL;

Essentially, this returns all rows from drivers that do not have a corresponding row in driver_has_device.


To create a SPN for a SQL FCI, use the FQDN of the FCI instance. For example, if the FCI name is "SQLFCI1" on the contoso domain and it listens on port 22000 with domain account SQLSvcAcct then the spn would be: setspn -s MSSQLSvc/SQLFCI1.contoso.com:22000 Contoso\SQLSvcAcct

If you don't want to deal with doing this by hand there is a great tool provided by Microsoft for this.


How does R-Tree outperform B-Tree for simple check if a point is within a rectangle

R-tree structure works in a way that two nearby points are "closer" in the R-tree index - because both coordinates and both with same weight are used to decide where (in the index) a new point is to be placed.

So, it's easy to identify points that are "near" a fixed point - meaning points that have both coordinates near the fixed point coordinates.

B-tree indexes on the other way work differently. Even if you have a compound index, say (Latitude, Longitude), where both coordinates are included the index, they do not have the same weight. In other words, two points with different Latitude and the same exact Longitude will be very far apart (in the index structure). Much more apart than two points with same Latitude and different Longitude.

So, searching for nearby points (with the above (Lat, Long) index) could possibly find fast nearby points with the exact same Latitude. But for all the other points, it will not be very efficient.

As an example, consider you are looking for places nearby a village in Africa - a village that has the same Latitude with London. Your query would spend lots of time calculating the distances between your village and all the (millions) points in UK that have the same (or almost the same) latitude with it. And then reject those points because they are too far apart.

You might think then that two B-tree indices, one on (Latitude) and one on (Longitude) would be fine for such queries. After all, why shouldn't both indices be used in a query that has a:

WHERE Latitude  BETWEEN @LatStart  AND @LatEnd
  AND Longitude BETWEEN @LongStart AND @LongEnd

I think that DBMS cannot use two (B-tree) indices like that in a query (MySQL cannot AFAIK). Even if it could though, it wouldn't be very good, preformance-wise, as both conditions may not narrow the search much. So, after these two index scans, a million rows found that match one condition would have to be merged with a million rows from the other one. And this merge will probably require sorting as well - which will kill performance if the subresults are big.

In general, any query that has more than one range condition on the same table is difficult to be optimized by B-tree indices. (for other DBMS that have other types of indices, like Hash or Bitmap, I can't speak about).

That's why R-trees and other indices that perform well in spatial searches were invented in the first place. They kind of "interleave" both coordinates (or all 3 or 4 in higher dimensional indices) in the index structure and no coordinate has higher weight than the others.


I think there would need to be a MemberPhone table, since the description mentions storing multiple phones per member
I would create tables to store the possible values for for BicycleMake, Bicycle (which contains an attribute linking to a model), BicycleColor, and BicycleSize. Given these tables, there wouldn't need to be a reference to the BicycleMake in Bicycle.
I feel like the Total Rental Length attribute doesn't need to be stored, since this could be derived from the pick-up time and drop-off time. Or better yet, the drop-off time doesn't need to be stored, since it can be derived from the pickup time and total length (DateTime + int is smaller than DateTime + DateTime).
How were you planning on storing the sponsorships?
I suspect that subscription information would need to be stored, such as when a subscription was purchased, and what type it is (monthly, yearly, daily, ...). If this does need to be stored, then it's possible that the membership status can be derived from the subscription information, if the membership status is simply whether the member has an active subscription or not. I would ask questions about this...
Bicycles should have an attribute for what terminal it's currently stored in (perhaps a null-value meaning it's not currently in a Terminal). Otherwise, how do you know which terminal a bicycle is in if it has yet to be rented?
I think the Terminal_Info table is unnecessary, since the number of bicycles at a terminal is duplicating data.
I feel like the GPS tracking number would need to be stored for the bicycles. I would ask questions for this...


credentials file for running mysql related jobs via script?

It would be better to store the credentials in a config file, and load them as an extra-defaults file:

$ mysqldump --defaults-extra-file=mycred.cnf ...

The config file is the same format as /etc/my.cnf or ~/.my.cnf

$ cat mycred.cnf
[mysqldump]
user = myuser
password = xyzzy

MySQL 5.6 also introduces encrypted storage of credentials. You can set up an encrypted file ~/.mylogin.cnf:

$ mysql_config_editor set --login-path=dump --host=localhost --user=root --password
Enter password:

Then you can dump using that login path

$ mysqldump --login-path=dump ...


SQL Server - How to achieve READCOMMITED and NOLOCK at the same time?

What you're looking for is an optimistic isolation level, like Snapshot Isolation, or Read Committed Snapshot Isolation.

Code example:

USE Crap;

CREATE TABLE dbo.users (id INT, username NVARCHAR(40));

INSERT dbo.users ( id, username )
VALUES ( 1, N'Jimbo' )

/*To turn on Snapshot*/
ALTER DATABASE Crap SET ALLOW_SNAPSHOT_ISOLATION ON;

/*To turn on RCSI*/
ALTER DATABASE Crap SET READ_COMMITTED_SNAPSHOT ON;

UPDATE dbo.users
SET username = 'Dimbo'
WHERE id = 1;

/*Snapshot needs this, RCSI doesn't*/
SET TRANSACTION ISOLATION LEVEL SNAPSHOT
SELECT *
FROM dbo.users AS u
WHERE u.id = 1;

Things to be aware of:

Row versioning uses space in tempdb (except SQL Server 2019 when Accelerated Database Recovery is configured - versions are stored with the user database, either in-row or in the Persisted Version Store)
There can be race conditions where you depend on locking for queueing

Differences

One important difference between Snapshot Isolation and RCSI is inside transactions:

Under Snapshot Isolation, BEGIN TRAN marks the point when all queries inside the transaction will read from the version store.
Under RCSI, each statement after BEGIN TRAN will read the version store as of when the statement executes.

Another difference is that Snapshot Isolation can be applied to modification queries, where RCSI can't. More precisely, SI detects write conflicts and rolls one of the conflicting transactions back automatically. Updates under RCSI do not use row versions when locating data to update, but this only applies to the target table. Other tables in the same delete or update statement, including additional references to the target table, will continue to use row versions.


The question I would ask is whether the direct relationship between Anthology and Composer is "important" to the system?  There are all kinds of incidental relationships between tangible things that are recorded in any system.  However, only certain of these are important for the purposes of the system itself.  These are the ones that belong in a relational schema.

If anthologies will always be made up of compositions, and compositions will always have composers, then you can always derive the relationship between anthologies and composers using a query.  If you did it that way there would be no risk of inconsistent relationships.

This model would look like this:

You would have table definitions something like this:

create table Composer
( composer_id int IDENTITY
, composer_name nvarchar(50)
, biography nvarchar(1000) null
, other_composer_info nvarchar(1000) null
, constraint pk_composer primary key(composer_id)
);

create table Composition
( composition_id int IDENTITY
, composer_id int
, composition_name nvarchar(50)
, composed_on datetime
, constraint pk_composition primary key(composition_id)
, constraint fk_composition_composer
    foreign key (composer_id) references Composer(composer_id)
);

create table Anthology
( anthology_id int IDENTITY
, anthology_name nvarchar(50)
, constraint pk_anthology primary key (anthology_id)
);

create table AnthologyItem
( anthology_id int
, composition_id int
, constraint pk_anthology_item primary key
   (anthology_id, composition_id)
, constraint fk_item_anthology (anthology_id)
   references Anthology(anthology_id)
, constraint fk_item_composition (composition_id)
   references Composition(composition_id)
);

The good thing about this is that it has no redundant data to get out of synch.  The problem is that it doesn't quite fit Shannon's requirement that an anthology be about one composer and that all of the compositions in that anthology must be from the same composer.

Unfortunately, this is not an easy problem to solve with declarative referential constraints.  Declarative constraints are great for making sure that everything within a row makes sense.  What they aren't built to do is enforce rules between rows.

There is a declarative way to solve this problem, but it involves a trade-off that many people wouldn't like, because it smells an awful lot like violating normalization.  Some people would argue (Mike Sherril comes to mind) that this solution doesn't literally violate normalization rules, but people who are less well attuned to the actual rules of normalization will probably look at this solution with skepticism.

So what is this controversial solution?  It looks like this:

Note that the primary keys of some of these tables have been modified.  Here is the SQL DDL for the solution: (You need to scroll it to the bottom to see the magic.)

create table Composer
( composer_id int IDENTITY
, composer_name nvarchar(50)
, biography nvarchar(1000) null
, other_composer_info nvarchar(1000) null
, constraint pk_composer primary key(composer_id)
);

create table Composition
( composition_id int IDENTITY
, composer_id int
, composition_name nvarchar(50)
, composed_on datetime
, constraint pk_composition
    primary key(composer_id, composition_id) -- NOTE CHANGE!
, constraint fk_composition_composer
    foreign key (composer_id) references Composer(composer_id)
);

create table Anthology
( anthology_id int IDENTITY
, composer_id int                            -- THIS IS NEW!
, anthology_name nvarchar(50)
, constraint pk_anthology
    primary key (composer_id, anthology_id)  -- THIS IS DIFFERENT
);

create table AnthologyItem
( composer_id int                            -- THIS IS NEW!
, anthology_id int
, composition_id int
, constraint pk_anthology_item primary key   -- THIS HAS CHANGED.
   (composer_id, anthology_id, composition_id)
, constraint fk_item_anthology
    foreign key (composer_id, anthology_id)
    references Anthology(composer_id, anthology_id)
, constraint fk_item_composition
    foreign key (composer_id, composition_id)
    references Composition(composer_id, composition_id)
);

Note that the way this works is you have to impose a single composer on an anthology by making the composer part of the anthology's primary key.  You do the same thing with composition.  Then, when you create an intersection between composition and anthology, you have the composer ID twice.  You can then use a check constraint to declaratively enforce that compositions and anthologies have not only a single composer, but the same one.

NOTE: I'm not saying you should do this, I'm just saying you could do this. YMMV etc.


Postgres: Preventing objects with same name

That's like asking Microsoft or GNU.org to remove PATH from the command line. Ain't gonna happen.

Use fully qualified names or always run ALTER ROLE <your_login_role> SET search_path TO a,b,c; at the beginning of your session. I'm not sure if you can run this with psqlrc but you could try.

You can view your search_path with show search_path

You could write a cron or pgagent job to run often and check information_schema for views and tables with the same name


If a user is created in a primary postgres database, will the user be copied to a follower?

If you're doing WAL streaming, you're dealing with physical replication. That means your secondary data cluster is a total clone of the primary.

Your data cluster is made of files. With physical replication your files will be totally identical bit by bit between the primary and the secondary node.

So yes, roles, permission, etc are replicated.


type recognition with MonetDB, *create* tables from CSVs

Indeed, the COPY INTO requires a CREATE TABLE beforehand. As of right now, there is no generic way to infer a schema. The main issue here is the massive diversity of CSV encodings.

That being said, the MonetDB.R connector does support schema "guessing" through the monetdb.read.csv function.

Also, I have been working on a Python script to automate the CSV loading task. The script (importer.py) is available from my CWI page. It works as follows:

./importer.py -h
usage: importer.py [-h] [--database DATABASE] [--port PORT] [--user USER]
                   [--password PASSWORD] [--header] [--yes]
                   files [files ...]

A "smarter" CSV loader for MonetDB, v.0.3, hannes@cwi.nl, 2014-05

positional arguments:
  files                One or many CSV files to be imported

optional arguments:
  -h, --help           show this help message and exit
  --database DATABASE  Database name to connect to
  --port PORT          MonetDB TCP port, defaults to 50000
  --user USER          MonetDB username, defaults to "monetdb"
  --password PASSWORD  MonetDB password, defaults to "monetdb"
  --header             set if given CSV file has a header in the first line
  --yes                if set, assume Yes on all questions

Usage example:

./importer.py --database acs ~/Desktop/adult.csv
Now probing '/ufs/hannes/Desktop/adult.csv':

OK, I am going to run the following commands on your DB acs:

CREATE TABLE adult5 (C1 DOUBLE, C2 STRING, C3 STRING, C4 STRING, C5 STRING, C6 STRING, C7 STRING, C8 STRING, C9 STRING, C10 STRING, C11 DOUBLE, C12 STRING, C13 BIGINT);
COPY 35469 OFFSET 1 RECORDS INTO adult5 FROM '/ufs/hannes/Desktop/adult.csv' USING DELIMITERS ',','\n','"' NULL AS '' LOCKED;

Proceed? [Y/n] y
imported 30162 rows from /ufs/hannes/Desktop/adult.csv in 3 second(s)

If there are any problems, please let me know.


MySQL views performance for alias purposes

YMMV.  Views sometimes fail to do the same optimizations, leading to slower execution than performing the SELECT directly.  Since your case is straight forward, probably you will incur no noticeable overhead.

INSERTs through a VIEW have limitations; sounds like you won't hit them.  Read the manual.

JOINs is a place where the optimizer may drop the ball; suggest you experiment offline with that, before committing to make the change.


SQL Query for monitoring exceptions between parent/child table

Test Data

CREATE TABLE dbo.Article([Article Title] NVARCHAR(50),
                         [Article Number] INT,
                         [Article Status] NVARCHAR(20));

INSERT INTO dbo.Article([Article Title],
                        [Article Number],
                        [Article Status])
VALUES
('Expense Report',1,'Active'),
('Inbox Monitor',2,'Active'),
('Daily finance report',3,'Active'),
('Order Audit',4,'Active'),
('Order Tracking',5,'Inactive');

CREATE TABLE dbo.Process([Process Title] nvarchar(50),
                         [Article Number] int,
                         [Process Status] nvarchar(20));

INSERT INTO  dbo.Process([Process Title],
                         [Article Number],
                         [Process Status])
VALUES
('Expense Report CPA',1,'Active'),
('Expense Report BG',1,'Active'),
('Inbox Monitor db',2,'Active'),
('Weekly finance report CPA',3,'Inactive'),
('Weekly finance report BG',3,'Inactive'),
('Order Audit for xyz',4,'Active'),
('Order Audit for abc',4,'Inactive'),
('Order Tracking',5,'Inactive');

Query

SELECT *
FROM dbo.Article A
WHERE [Article Status] = 'Active'
AND NOT EXISTS
(SELECT * FROM dbo.Process P WHERE  P.[Article Number] = A.[Article Number]
                                    AND P.[Process Status] != 'Inactive');

Result

Article Title         Article Number    Article Status
Daily finance report  3                 Active


How can I normalize this schema about students and courses further?

I would not think that credits and time would really have anything to do with each other. One is essentially a count of how many credits they have accumulated, and the other relates to their status as a student. As far as the degree program and start date, those are also just data points to keep in the student's record.
For more info on this topic, this article helped me.
https://www.lifewire.com/transitive-dependency-1019760

In your course table, I would use professor_id to indicate who is teaching it, and have that table hold the faculty department information. I am a bit confused on what you are calling 'time.' Is it related to a specific class, or a student, or a course?

If you would like, I can help walk through your Entity Relationship Diagram. I have found the rubber duck method really helps clear things up.


How to speed up MongoDB chunk moving between shards

Things like how many shards we have, which database are sharded, and the configuration setting of your shard cluster. But one of the most important pieces of information that the config servers hold, is the mapping of the chunks to shards.

By default MongoDB takes ChunkSize=64 MB

By default , MongoDB takes 64 megabytes as the default chunk size. That means that if a chunk is about 64 megabytes, or within 64 megatons range , it will be split.

1MB<=chunkSize<=1024MB

We can define a chunk size between the values of one megabyte and 1024 and one gigabyte. The chunk size is configurable during run time. So if we decide to change a chunk size, we can easily do so. But before we go in changing your chunk size.

for further your ref here and here


Building an bridge table from existing one with the use of the SELECT INTO

Instead of use a CURSOR I'd suggest you to use an iterative solution using WHILE.

CREATE TABLE MEASURES (id int, id_column1 int, id_column2 int, id_column3 int, stuff varchar(20));
INSERT INTO MEASURES VALUES
(1, 2017, 2024, 1452, 'red'),
(2, 4125, NULL, 5234, 'blue'),
(3, NULL, 5234, 7234, 'pink');
GO

CREATE TABLE BRIDGE (id int, idc int);
GO

DECLARE @idColumn INT = 1;
DECLARE @cmd VARCHAR(MAX) = '';

WHILE @idColumn <= 3
BEGIN
    SET @CMD =   'INSERT INTO BRIDGE'
               + ' SELECT id, id_column'
               + CAST(@idColumn AS VARCHAR(10))
               + ' FROM MEASURES'
               -- uncomment to remove NULL values
               --+ ' WHERE id_column'
               --+ + CAST(@idColumn AS VARCHAR(10))
               --+ ' IS NOT NULL;';

    EXEC (@CMD);

    SET @idColumn = @idColumn + 1;

    -- uncomment to check SQL statements
    --SELECT @CMD;
END

SELECT * FROM BRIDGE ORDER BY id;
GO

id |  idc
-: | ---:
 1 | 2017
 1 | 2024
 1 | 1452
 2 | 5234
 2 | null
 2 | 4125
 3 | null
 3 | 5234
 3 | 7234

dbfiddle here


What does pg_stat_activity.xact_start mean for autovacuum threads?

The xact_start field should be before or equal to query_start, covering the entire duration of the vacuuming of the table.

Your vacuum is slow because your setting of vacuum_cost_limit is very conservative.  It is throttling itself, not blocking on other things.


Cannot remove char 0x0000 from NVARCHAR column

A bug in the REPLACE function? Or in SQL Server in general? I'm not so sure about that. The only "issue" here is merely not fully understanding how string comparisons are handled.

Collations define how certain characters will compare against other characters. Sometimes there are rules about certain combinations of characters equating to one or more other characters. And there are rules about characters such as 0x00 (null) and 0x20 (space) equating to each other or other characters. And, to make life more interesting, there are a few nuances specific to VARCHAR data using a SQL Server Collation (i.e. one starting with SQL_), as the following example shows:

SELECT REPLACE('VARCHAR with SQL_Latin1_General_CP1_CI_AS'+CHAR(0)+'Matches', CHAR(0),
               ': ' COLLATE SQL_Latin1_General_CP1_CI_AS);
SELECT REPLACE(N'NVARCHAR with SQL_Latin1_General_CP1_CI_AS'+NCHAR(0)+N'Matches', NCHAR(0),
               N': ' COLLATE SQL_Latin1_General_CP1_CI_AS);
SELECT REPLACE('VARCHAR with Latin1_General_100_CI_AS'+CHAR(0)+'Matches', CHAR(0),
               ': ' COLLATE Latin1_General_100_CI_AS);
SELECT REPLACE(N'NVARCHAR with Latin1_General_100_CI_AS'+NCHAR(0)+N'Matches', NCHAR(0),
               N': ' COLLATE Latin1_General_100_CI_AS);

Returns:

VARCHAR with SQL_Latin1_General_CP1_CI_AS: Matches

NVARCHAR with SQL_Latin1_General_CP1_CI_AS

VARCHAR with Latin1_General_100_CI_AS

NVARCHAR with Latin1_General_100_CI_AS

So let's take a look at this behavior using queries based on the ones found in @Max's answer. I added the N prefix to the string literals and to the CHAR(0), and I also added an extra NCHAR(0) just to make the next part easier to see. And I added queries to show the actual Code Points being used (to prove that the 0x0000 values are really in there, and a call to REPLACE() to see if that really has a bug in it).

DECLARE @Data NVARCHAR(255);
SELECT @Data = N'this is' + NCHAR(0) + N'a test' + NCHAR(0) + N'of null';

SELECT @Data;
SELECT CONVERT(VARBINARY(50), @Data);
SELECT REPLACE(@Data, NCHAR(0), N'~');

  this is

  0x7400680069007300200069007300000061002000740065007300740000006F00660020006E0075006C006C00

  this is

The first result shows that the string ends just after the "is" due to (null) termination. The second result shows the underlying codes, and I emphasized the two instances of the 0x0000 character. The third result shows that the REPLACE function doesn't seem to match the 0x0000 character to the NCHAR(0) that is passed in.

But should we be expecting that NCHAR(0) would be matching here? We can effectively disable all of the equivalence rules that are usually applied to string comparisons by forcing a binary collation. We will use a _BIN2 collation since the _BIN collations are deprecated and shouldn't be used unless you have a specific need for them.

Add the following query to the set above and re-run the batch.

SELECT REPLACE(@Data, NCHAR(0) COLLATE Latin1_General_100_BIN2, N'~');

You should get the following additional result:

  this is~a test~of null

So the REPLACE function does in fact work, and this was tested on both SQL Server 2008 R2, SP3 as well as SQL Server 2012 SP2.

Ok, so that only addressed the issue of REPLACE not working with NCHAR(0), but did not address NCHAR(0) equating to a space (i.e. NCHAR(32) or NCHAR(0x20)).

Now we will use an adaptation of the main query from @Max's answer. I again added an extra NCHAR(0) to the test string (really just replaced the space at position 8 with it), and I added the Code Point of the matching character to the RAISERROR message.

SET NOCOUNT ON;
GO
DECLARE @Data NVARCHAR(255);
SELECT @Data = N'this is' + NCHAR(0) + N'a test' + NCHAR(0) + N'of null';

DECLARE @i INT,
        @CodePoint INT;
SET @i = 1;

WHILE @i < LEN(@Data)
BEGIN
    IF SUBSTRING(@Data, @i, 1) = NCHAR(0) --COLLATE Latin1_General_100_BIN2
    BEGIN
        SET @CodePoint = UNICODE(SUBSTRING(@Data, @i, 1));
        RAISERROR (N'Found a NULL char (Code Point = %d) at position: %d',
                   10, 1, @CodePoint, @i) WITH NOWAIT;
    END;
    SET @i = @i + 1;
END;

This query (with the COLLATE clause still commented out) will return:

Found a NULL char (Code Point = 32) at position: 5
Found a NULL char (Code Point = 0) at position: 8
Found a NULL char (Code Point = 32) at position: 10
Found a NULL char (Code Point = 0) at position: 15
Found a NULL char (Code Point = 32) at position: 18

These are the same positions reported in @Max's test, but now it shows what Code Point it is matching in each case. And yes, it is equating to both 32 and 0.

Now, uncomment the COLLATE clause and re-run it. It will return:

Found a NULL char (Code Point = 0) at position: 8
Found a NULL char (Code Point = 0) at position: 15

Another way to accomplish this, and without using the COLLATE clause, is to change the IF statement to be:

IF ( UNICODE(SUBSTRING(@Data, @i, 1)) = 0 )

Of course, neither of these two fixes -- the WHILE loop using either the COLLATE clause or the UNICODE() function -- are needed to address the original problem of removing the 0x0000 characters from the input data since the simple REPLACE (using the COLLATE clause) handles that.

Summary:

If you are wanting to replace / remove characters from a string, there is no need to use a loop. The REPLACE function works just fine as long as you specify a _BIN2 collation for at least one of the 3 input parameters via the COLLATE keyword (and technically it doesn't matter which binary collation since binary collations only compare the numeric Code Point values).
If needing to test for a particular Code Point (such as in a loop as shown above), then it is probably fastest to use the UNICODE() function since that just reports what values are actually there. This should be faster than using the COLLATE keyword since that has to do more work.
If needing to test for a series of Code Points / characters, then use the COLLATE keyword, specifying a _BIN2 binary collation.


How do I create logins for all database users in a database that was just restored for a different server?

Google for sp_help_revlogin and find the version for your version of SQL (the SQL 2008 version works on SQL 2008 and up).  Install this sp on the old server.  Run sp_help_revlogin on the old server.  Take the output and put it in a query window on the new server, and run the query to create all the logins.


Optimal database structure for fast inserts

The problem with MySQL's InnoDB engine is that the storage architecture is essentially using index-organized tables, which will unfortunately experience performance issues for large numbers of inserts when the target table is large.  The only case in which this can be mitigated is if you have sequential inserts, that is, the primary key of the set of inserts follows a sequence.

For high ingestion rates of data, different technologies are recommended, such as Cassandra which uses a log-structured merge tree, or in your case, I might recommend TokuDB, which has been acquired by Percona, and which uses a technology called a fractal tree index, which is essentially creative way to cache, pre-sort, and batch insert into your index-organized table (of course, there's much more to it than that, but no need to go into the details here.)

Long story short, if you have moderately high ingestion rates, you need a heap organized table structure implemented by databases like Oracle and PostgreSQL, and if you have very high ingestion rates, you need to use a database like Cassandra or TokuDB.


MongoDB permission to view collections not working

You need to give that "readWriteAnyDatabase" role to the user for "admin" database.

use admin
db.createUser({user:"test", pwd:"test", roles: ["readWriteAnyDatabase"]})

after that you can login with that user and see all collections

mongo -u test -p test --authenticationDatabase admin opt
Mongo> db
opt
Mongo> show collections
test_collection
Mongo>


Is it possible to join these both tables?

you can combine them like this, as long as your subse3lect only has onbe row and 1 coumn
SELECT u.*,h.*,d.* ,
(SELECT count(id) FROM OHS
WHERE Category = 'POLICIES'
  AND ID NOT IN
  (
    SELECT OHSID
    FROM User_OHS
    WHERE UserID=u.ID AND Type='POLICIES'
  )) as countr
FROM User u
LEFT JOIN HealthDec h ON u.ID = h.EmpId
LEFT JOIN Document d On u.ID = d.UserID;


Grouping records based on intervals of time

select dateadd(minute, 1+datediff(minute, 0, CaptureTime), 0),
       sum(SnapShotValue)
from YourTable
group by dateadd(minute, 1+datediff(minute, 0, CaptureTime), 0)

SE-Data

datediff(minute, 0, CaptureTime) gives you the number of minutes since 1900-01-01T00:00:00.

dateadd(minute, 1+datediff(minute, 0, CaptureTime), 0) adds the number of minutes since 1900-01-01T00:00:00 to 1900-01-01T00:00:00 ending up with a datetime with only minutes.

The 1+ is there because you wanted the next minute.

To do the same with a 5 minute interval you need to do some calculations. Divide the minutes with 5 and multiply with 5 gives you the minutes rounded down to a 5 minute precision. This works because the result of an integer division in SQL Server is an integer.

dateadd(minute, 5 + (datediff(minute, 0, CaptureTime) / 5) * 5, 0)


If your intention is that you want to have multiple equivalent units of measure, all of which share the identical nutrition information, then you should flip things around and make nutrition_facts either a child of food directly.  The serving_sizes would then be a child of your nutrition facts, not of food (except as a grand child through nutrition facts.  Something like this:

food -+-----< nutrition_facts -+-----< serving_sizes

This would let you have multiple sets of nutrition facts, say for "box of cookies" and "two cookies" each stored only once, with these units of measure stored as either cookie units or equivalent grams.

If you only want to have one set of nutrition facts... such as would appear on a nutrition label, then you have a 1:1 between food and nutrition facts, which gives you the option of moving the columns from nutrition_facts into the food table as an alternative design.  This may or may not appeal to you, depending on your design inklings.


Postgres, two tables use the same sequence, how to delete one?

You were probably using a serial data type.

PostgreSQL documentation says this is equivalent to the following:

CREATE SEQUENCE tablename_colname_seq;
CREATE TABLE tablename (
    colname integer NOT NULL DEFAULT nextval('tablename_colname_seq')
);
ALTER SEQUENCE tablename_colname_seq OWNED BY tablename.colname;

Note the latest statement.

Based on https://www.postgresql.org/docs/current/sql-altersequence.html you will need to remove the "ownership" dependency:

  Specifying OWNED BY NONE removes any existing association, making the sequence “free-standing”.

Which in your case means:

ALTER SEQUENCE mytable_ident_seq OWNED BY NONE;

Or you may need to reassociate it with the proper table, using:

ALTER SEQUENCE mytable_ident_seq OWNED BY table_name.column_name;

After which you should be able to delete the table without consequences for the sequence.


does merging tables always increases performance?

Without any knowledge about the workload of the database, is almost impossible to answer to a question like this. How often those “extra” data will be used? What are the most common queries? How frequent they are? and how much it is important the speed of the execution?

The general advice is always the best one for me: make things as simple as possible, than optimize if you need to.

So my advice is: create a single table, look at the performances, and only if those performances are not adequate to your objectives, split the table.


grouping rows of common values to create a new 'group id' for each set

Use DENSE_RANK function:
select
    anchor, sum(anchor) as "sum", grp  as "group"
from
    (select
         anchor, dense_rank() over (order by anchor) as grp
     from
         t) t1
group by
    anchor, grp
order by
    grp;

anchor | sum | group
-----: | --: | ----:
     2 |   4 |     1
     3 |   9 |     2

db<>fiddle here
More examples about DENSE_RANK here.
Even though it is preferable to add a new question when there is a significant change, this is the answer at your edited question.
select anchor,
       sum(anchor) as "sum",
       grp as "group"
from (
      select anchor, dt, tm,
             sum(rst) over (order by dt, tm) as grp
      from (
            select anchor, dt, tm,
                   case when coalesce(lag(anchor)
                                      over (order by dt, tm), 0) <> anchor
                        then 1 end as rst
            from   t
           ) t1
     ) t2
group by anchor, grp
order by grp;

anchor | sum | group
-----: | --: | ----:
     2 |   4 |     1
     3 |   9 |     2
     2 |   4 |     3

db<>fiddle here


Can MariaDB or MySQL cast as bigint, getting an error: ... right syntax to use near 'bigint)'?

Silently? Let's try:

CREATE TABLE g
   AS SELECT CAST('2147483699' AS int);

MariaDB 10.1.32, default sql_mode (non-strict)

  Query OK, 1 row affected, 1 warning (0.19 sec) Records: 1  Duplicates:
  0  Warnings: 1

And the warning is:

  | Warning | 1264 | Out of range value for column 'CAST('2147483699' AS int)' at row 1 |

Yes, selecting from the table is interesting, but not surprising given the warning.

(I don't have a MariaDB 10.2 instance at the moment, but testing on dbfiddle.uk indicates it too fails, though again not silently.)

MariaDB 10.3.6, default sql_mode (strict)

No warnings.

select * from g;

Gives:

+---------------------------+
| CAST('2147483699' AS int) |
+---------------------------+
|                2147483699 |
+---------------------------+
1 row in set (0.000 sec)

And the column is indeed created as bigint(10).

MySQL 8.0, both the default strict and non-strict sql_mode

  ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'int)' at line 2

Computer says no.

Conclusion

Can MariaDB or MySQL cast as bigint? As we have seen, yes, MariaDB 10.3 can (though it's not GA yet ... but surely any day now it will be). However, it can only cast as bigint implicitly. You can't explicitly cast as bigint, just like you can't cast as varchar (but see MDEV-11283), text, float, tinyint etcetera. There are only a limited number of datatypes you can cast to. This is not well documented, but CAST appears to support only the same datatypes as listed for the CONVERT function.


VARCHAR2 should perhaps be DT_WSTR when working with SSIS against a Oracle Database?

Looks like I need to be careful to specify my column length in CHAR. For example, Instead of VARCHAR2(255), I need to ensure it is VARCHAR2(255 CHAR).  If I do this, SSIS sees it as DT_WSTR.


User and Schema Recreated during Stored Procedure Creation

This behavior is noted in the "Remarks" section of the CREATE SCHEMA documentation, under "Implicit Schema and User Creation" (emphasis added to highlight what you are experiencing):

  In some cases a user can use a database without having a database user account (a database principal in the database). This can happen in the following situations:

  A login has CONTROL SERVER privileges.
  A Windows user does not have an individual database user account (a database principal in the database), but accesses a database as a member of a Windows group which has a database user account (a database principal for the Windows group).

  When a user without a database user account creates an object without specifying an existing schema, a database principal and default schema will be automatically created in the database for that user. The created database principal and schema will have the same name as the name that user used when connecting to SQL Server (the SQL Server authentication login name or the Windows user name).

  This behavior is necessary to allow users that are based on Windows groups to create and own objects. However it can result in the unintentional creation of schemas and users. To avoid implicitly creating users and schemas, whenever possible explicitly create database principals and assign a default schema. Or explicitly state an existing schema when creating objects in a database, using two or three-part object names.


Database Normal Form (financial statement)

My team once had to build a database for reporting financials with alternative time windows, including fiscal years, fiscal quarters, and fiscal months.  The relationship between dates and these units was documented, but really messy.  So here is what we did.

We created a table, call it Almanac, with one row per date.  (In reality, we had one row per work shift, but I'm leaving out that complexity for the sake of clarity).  We wrote an Almanac generating program that applied the strange rules for fiscal years, quarters and months to each date over a ten year period.  The PK for the Almanac table was the Date, although we couldn't use the name Date because it was a keyword.  The other columns were items like the fiscal year, the fiscal quarter, and flags like "Company Holiday". Notice that this program had one output, but no inputs.

Once the almanac was built, joining any other table with the almanac over a date field made it trivial to slice and dice by fiscal time periods, regardless of how obscure the rules were.  This worked like a charm.

(If you must know, the Almanac generator was written in COBOL+SQL.  This was a long, long, time ago, when Oracle was very young.)


How to run specific version (8.4, 9.1) of postgresql pg_* command (e.g., pg_dump)

You are on Ubuntu and obviously have Martin Pitt's pg_wrapper installed (judging from pg_ctlcluster) that is provided by the package postgresql-common and comes with the standard Debian packages. I use the same on Debian.

On a Linux system, run which in the shell to see which executable is actually picked:

postgres@db:~$ which pg_dump
/usr/bin/pg_dump
postgres@db:~$ ls -l /usr/bin/pg_dump
lrwxrwxrwx 1 root root 37  4. Jun 18:57 /usr/bin/pg_dump -> ../share/postgresql-common/pg_wrapper

pg_dump is actually a symlink to pg_wrapper, which dynamically picks the appropriate version of the client program for the db cluster you run pg_dump with. I quote the man page of pg_wrapper:

  This program is run only as a link to names which correspond to PostgreSQL programs in /usr/lib/postgresql/version/bin. It determines
  the configured cluster and
         database for the user and calls the appropriate version of the desired program to connect to that cluster and database, supplying any
  specified options to that command.

   The target cluster is selected by the following means, in descending order of precedence:
   1.  explicit specification with the --cluster option
   2.  explicit specification with the PGCLUSTER environment variable
   3.  matching entry in ~/.postgresqlrc (see postgresqlrc(5)), if that file exists
   4.  matching entry in /etc/postgresql-common/user_clusters (see user_clusters(5)), if that file exists
   5.  If only one local cluster exists, that one will be selected.
   6.  If several local clusters exist, the one listening on the default port 5432 will be selected.

   If none of these rules match, pg_wrapper aborts with an error.

IOW, the right version should be picked automagically - unless you screwed up your installation somehow. You can always add the option --cluster to be specific.


Why do I have to select from the dual table?

Strictly, yes, the FROM clause of a SELECT statement is not optional.  The syntax for SQL-99 details the basic SELECT statment, and the FROM clause doesn't have any square brackets around it.  That indicates the standard considers it non-optional:

SELECT [ DISTINCT | ALL ]
{Column expression [ AS name ]} [ ,... ] | *
FROM <Table reference> [ {,<Table reference>} ... ]
[ WHERE search condition ]
[ GROUP BY Columns [ HAVING condition ] ]
[ORDER BY {col_name | expr | position} [ASC | DESC],...]
[LIMIT {[offset,] row_count | row_count OFFSET offset}]
[PROCEDURE procedure_name(argument_list)]
[INTO OUTFILE 'file_name' export_options |
 INTO DUMPFILE 'file_name' |
 INTO var_name [, var_name]]
[FOR UPDATE | LOCK IN SHARE MODE]

In actual use, programmers and DBAs often find it useful to do things other than manipulate data in tables or manipulate tables and data structures.  This type of thing is largely beyond the scope of the SQL standard, which is concerned with the data features more than the nuts and bolts of specific implementations.  Whether we want to run SELECT getdate() or SELECT 1 or SELECT DB_NAME() (or whatever your dialect prefers), we don't actually want data from a table.

Oracle chooses to solve the standard and implementation discrepancy using a dummy table with the following effective definition:

CREATE TABLE DUAL (
  DUMMY CHAR(1)
  )

INSERT INTO DUAL (DUMMY) VALUES ('X')

Other RDBMSes essentially assume that a dummy table is used if no FROM is specified.

The history of the DUAL table is on Wikipedia:

  The DUAL table was created by Charles Weiss of Oracle corporation to
  provide a table for joining in internal views:

    I created the DUAL table as an underlying object in the Oracle Data
    Dictionary. It was never meant to be seen itself, but instead used
    inside a view that was expected to be queried. The idea was that you
    could do a JOIN to the DUAL table and create two rows in the result
    for every one row in your table. Then, by using GROUP BY, the
    resulting join could be summarized to show the amount of storage for
    the DATA extent and for the INDEX extent(s). The name, DUAL, seemed
    apt for the process of creating a pair of rows from just one.

  The original DUAL table had two rows in it (hence its name), but
  subsequently it only had one row.


Setting up sp_AllNightLog using Azure Blob Storage

Brent Ozar's sp_AllNightLog uses Ola Hallengren's backup script.

  Inside each of those Agent jobs, they call sp_AllNightLog @Backup = 1, which loops through the table to find databases that need to be backed up, then call Ola Hallengren’s DatabaseBackup stored procedure.

So you just need to configure Ola's script to write to Azure Blob Storage.

  Striping of backups to Azure Blob Storage

  The SQLCAT - team has a blog post about how you can optimize performance when backing up to Azure Blob Storage. You can stripe the backups to multiple files, and use the options MAXTRANSFERSIZE, and BLOCKSIZE.

EXECUTE dbo.DatabaseBackup @Databases = 'USER_DATABASES',
@URL = 'https://myaccount.blob.core.windows.net/mycontainer',
@BackupType = 'FULL',
@Compress = 'Y',
@Verify = 'Y',
@NumberOfFiles = 8,
@MaxTransferSize = 4194304,
@BlockSize = 65536

This is the important part:

@URL = 'https://myaccount.blob.core.windows.net/mycontainer'


Postgres - Select all if given parameter is null

Use the placeholder for strings:
select * from tbl where type LIKE Coalesce(@param, '%');


How to return JSON objects in tabular format using T-SQL?

You don't need to split the data out into separate tables and re-join for this. Simply use OPENJSON to decompose the JSON into a table and use PIVOT to shift the grade point data into columns.

The below example code can be seen in action here.

Setup example data:

CREATE TABLE DataTable
(
  Id INT,
  GroupId INT,
  Week VARCHAR(25),
  GroupData VARCHAR(MAX)
)

INSERT INTO DataTable
VALUES (1, 3840, 'Week 1', '[ { "StudentName": "Barry", "GradePoints": 100 }, { "StudentName": "Kelly", "GradePoints": 98 }, { "StudentName": "Jenny", "GradePoints": 100 } ]'),
  (2, 3840, 'Week 2', '[ { "StudentName": "Barry", "GradePoints": 95 }, { "StudentName": "Kelly", "GradePoints": 87 }, { "StudentName": "Jenny", "GradePoints": 99 } ]')

Query the data:

SELECT
 GroupId, StudentName, p.[Week 1], p.[Week 2]
FROM
(
 SELECT GroupId, Week, StudentName, GradePoints
 FROM DataTable
 CROSS APPLY OPENJSON ([GroupData])
 WITH (
  StudentName VARCHAR(25) '$.StudentName',
  GradePoints VARCHAR(25) '$.GradePoints'
 )
) src
PIVOT
(
  MAX(GradePoints) FOR [Week] IN ([Week 1], [Week 2])
) p

Results:

GroupId     StudentName     Week 1  Week 2
------------------------------------------
3840        Barry           100     95
3840        Jenny           100     99
3840        Kelly           98      87


Oracle 10g Database Locking Session Issue

You can change distributed_lock_timeout at the instance level alter system set distributed_lock_timeout=<sec> scope=spfile;
But what you are describing is a session modifiable parameter in 11g called ddl_lock_timeout and I’m not aware of anything that replicates that in 10g. The fact that it runs as expected in one environment and not the other means you might want to pull a list of parameter settings between the two and see what’s different.


SQL Server Change Tracking and Replication

My understanding of Change Tracking is that it is per database transaction. This means that all changes that are part of the same transaction get the same SYS_CHANGE_VERSION.

I also understood that the SYS_CHANGE_VERSION is set during the commit of the transaction, so numbers are a) always ascending and b) may be interleaved. You should always process the records in transaction completion order.

See Obtaining Changes by Using the Change Tracking Functions for confirmation that VERSION is ever increasing.


Move DB/LOG files - Detach/Attach - Problem

The correct (and preferred) way in my opinion should be

Use alter database....MODIFY FILE - This will update system catalog.
Offline the database
Physically move the files to the new location that you specified in step 1.
Online the database

I have written a script to rename a database - db name and the files -logical and physical and you can use that logic to adjust it as per your requirements.

Note: Before doing any detach/attach, you should take a full backup of the database. See Bad Habits: Using MDF/LDF Files .. from Aaron Bertrand.


Check constraint that exactly one in group has bit column set as true

You can define a filtered index on the table which will do this for you, there is no need to use a check constraint or an additional table for this.

CREATE UNIQUE INDEX IX_Address_UniquePrimaryAddress
ON dbo.Address(CustomerId)
WHERE IsPrimary = 1;

Here is a good link of filtered indexes that should help you:

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


Since I happened to be researching this myself, here's a summary of what I found.

According to a 2015 blog post from the MySQL dev team, the main advantages of mysqlpump are that it can use multiple threads in parallel to speed up the dumping and that it doesn't share mysqldump's backwards compatibility requirements, which should open the door for further improvements.

However, the post cautions that mysqlpump is "currently unsafe as a general purpose backup replacement" because individual threads may generate their sections of the dump from different states of the database. It doesn't mention any plans to deprecate mysqldump.

Giuseppe Maxia played around with mysqlpump in 2015 and found the dumping speed-up to be fairly small. He appreciated the new ability to select precisely which objects to dump, but noted that loading the dump back in can only be done single-threaded and that (unlike mysqldump) dumps can only be loaded back into databases with the same name.


Handling NULLs is tricky, sure. You could get around needing two indexes with either a computed column, or by updating the NULLs in your current column to some canary value.
ALTER TABLE dbo.MyTable ADD foofighter AS ISNULL(foo, 'canarystring');

CREATE INDEX fooey ON dbo.MyTable (bar) INCLUDE(foofighter)
WHERE foofighter IN ('string01', 'string02', 'canarystring');

Adding non-persisted computed columns has fewer locking implications up front, and you can still index them any which way makes you happy.


An efficient database design for recording complex contact/configurator form

You have three questions here.

Storing multiple rows (of components) per form submission. This will happen in any process flow of a manufacturer. It is possible and you shall normalize by adding more rows per form (even for a component).

Secondly, each screw / component having its own set of (duplicate) fields can be handled simply by making those components refer to the existing data itself just by adding another sub-item id as required.

And for your main question of "how should I design my db tables", it is better if you could present a design and your expected bottle necks / issues / hurdles / etc. for which we shall suggest solutions.


postgres service suddenly not working after the computer crashed while working

When I faced this message, I checked the postgresstartup.log, it showed that the data directory was having permissions as 777,After I changed to 700 , the postgresql started working

sudo su - postgres

chmod 700 -R <postgresql_Data_Directory>

exit

/etc/init.d/postgresql start

/etc/init.d/postgresql status


If you are using a 'literal date' you need to type the format differently.  If you are using a variable or field you will need to use substring and parse into the correct format.

I have a DateTime2 field in a table and the following works, similar to your query but formatted differently:

SELECT * FROM Table_1 where CreatedOn='2016-05-19T00:00:00.0000000';


How can I quantify the business value of normalization?

The actual costs are difficult to quantify as they are often diffused.  Often they appear as slower transactions.  For a system used rarely, this may not be significant.  For a system used for an hour a day, that is taking twice as long as is required the cost would be about 100 hrs a year per person using the system.  Slower transactions may also have higher error rates due to users being distracted or interrupted while processing transactions.

Failure to normalize the data often results in in consistent data through-out the application.  Updates to some records may be missed, or incorrect records may be updated.  Inconsistent data can have serious business and/or legal implications.   Such problems can reduce the reputation of the business.

Updates to non-normalized records may take many times longer than with normalized data. (That is time someone is waiting for the system rather than moving to the next task.).  In some cases the update will fail, or lock the table for excessively long periods of time.


Is there a way to determine what kind of data is sitting in the Redo Queue for an AlwaysOn AG Secondary Replica?

The short answer is no, there is not a DMV that contains this information.  It would be a significant amount of overhead for SQL Server to track what table was associated with each log record that had been received but not committed.

As you can read in Reading the transaction log in SQL Server – from hacks to solutions, you can read the log records with a function, but it's certainly not something that you would want to do in production just to have some interesting information.  Doing something like this on every log record in real time would add a tremendous amount of I/O load on a busy server, and you wouldn't want it to be done unless it solved a significant problem.

Although the type of information you're looking for would be interesting, you might get a better answer if you can state why you want to see this information--there may be a better solution.


Upgrading 8.1 to 9.3 - using statement log and WAL to check compatibility?

WAL vs statement log ordering

  Do the statements in the log file appear in the same order as they do in the WAL file?

That doesn't really make sense.

Statements do not appear in the WAL at all. Their effects do, in terms of transaction begins/commits/rollbacks, changes to table heap and index blocks, etc, but the actual statement is never recorded in the WAL at all.

Many queries run concurrently on most systems. The write-ahead log contains the effects of these queries mixed together in the order in which they did the work. So the queries are effectively all in the WAL at the same time.

PostgreSQL logs queries in the order they're received for parsing and execution. Because the operating system scheduler controls when each PostgreSQL backend runs this isn't necessarily the exact same order each query begins executing, and it's certainly not the same order each query finishes execution.

About all you can do is set a log_line_prefix that records the transaction IDs associated with statements, then look for those transaction IDs in the corresponding WAL entries. To do that you have to parse the disk block structure to find the xmin or xmax fields on changed tuples; a tool like pg_xlogdump will help you with this.

All in all I don't expect you to gain much from trying to match the WAL against the query log.

Replaying statements to compare results

  Pick a good sample of the SELECT commands from the logs, replay them to both servers, capture the output on files, compare the files. This would take care of "output" compatibility.

This would work if your application only ever uses runs one query at a time, i.e. it only uses one connection.

Almost all real world applications do many concurrent writes. You can't simply replay the query log to replicate the effect of concurrent write operations - especially in read committed transaction isolation, where one transaction's commit can affect the results of other concurrent transactions.

If you're logging transaction boundaries and transaction IDs with an appropriate log_line_prefix you can probably reassemble an interleaved ordering of all individual statements that get run by each session and use a custom tool to replay them in that exact order. It won't be perfect though, as individual statements can be subject to race conditions with each other.

Take the simple example:

INSERT INTO mytable (id, somevalue)
SELECT 1, 'blah'
WHERE NOT EXISTS (SELECT id FROM mytable WHERE id = 1);

If you run this twice at the same time from two different connections the effects can vary. They'll change based on details of exactly when each started, and on operating system scheduler timing decisions. Possible outcomes include:

The first inserts a row and returns INSERT 1, the second inserts no row and returns INSERT 0. Or vice versa.
The first inserts a row and returns INSERT 1. The second attempts to insert the same row, because the SELECT did not find any row with id=1, and fails with a duplicate key error.

So as you can see, it's just not possible to replay the exact state of one database onto another using just the query log. That's before considering things like the effect of rollbacks on sequences, volatile functions, functions using timestamps, etc, too.

Limitations of comparing input and output

Some app bugs won't be detected by the approach you describe.

For example, it's entirely normal for rows to be inserted in different orders into a table based on small details of timing, differences in plan choices, etc. A correct application won't mind this, because it either doesn't care about the row order, or or specifies a specific order by clause when it does care. However, in reality some apps try to treat tables as if they have some kind of innate order and suddenly get very upset if the table is not "in order". To PostgreSQL the table is just a heap with no innate order at all, it just happened to be returning the rows in the order the app was expecting because that was fastest. So if your app is relying on behaviour like this you won't notice the problem with either of your proposed tests.

If you attempt to compare results of replay without sorting the results then everything will appear to mismatch because of those minor timing and plan differences. If you do sort the results though, you might not notice cases where the app's making unsafe assumptions about ordering that it gets away with on 8.1 and not on 9.3.

Another case is the change of bytea_output default format. Your app's inserts will produce the same results, but selecting the data will return it in a different format now. If your app doesn't understand \xDEADBEEF hex format bytea it might try to interpret that as an octal bytea . You'd only notice the problem if you went to save it back to the DB and it got mangled, or if you got an error in the app as a result of the misinterpreted bytea data.

So how do I check compatibility?

If your application doesn't have a good unit and integration testing framework, now is the time to add one.

Read the release notes for each major PostgreSQL release and look for specific compatibility challenges in your queries, like missing from clauses, implicit casts to/from text, etc.

Manually test every part of the app you can against a dump of the old DB (taken with 9.3's pg_dump) that's applied to a test copy of the new DB.

You should also simply review your queries to look for likely issues. Some things, like a LIMIT clause without a corresponding ORDER BY, are pre-existing bugs in the app code that it might've got away with before, and might still get away with in 9.3 ... or might not. PostgreSQL tries to detect obvious bugs, but there are legitimate cases where you might use a LIMIT without an ORDER BY, so in this case and many related ones it can't warn you in case that's really what you intended.

The biggest problems you're going to face are going to be:

Switch to standard_conforming_strings = on changing handling of escapes in strings
Removal of implicit from clause causing previously valid-looking but nonstandard and potentially unsafe queries to fail;
Removal of implicit casts to text causing previously valid-looking (but probably buggy) queries to fail;
Change of default bytea_output (if you use bytea fields)