Installing SQL Server and Visual studio side-by-side

There is actually no official guide (AFAIK) for which one you should install first but based on my personal experience here is what I recommend:

If you are just installing the management components of SQL Server, such as Management Studio and the client libraries then I recommend always installing Visual Studio first followed by the SQL Server components.
However, if you are installing the database engine itself then this should be installed first followed by Visual Studio (as SQL Server is the priority not VS).
Even when performing updates, ensure to update Visual Studio and then SQL Server.

This is because generally the components used by Visual Studio will be newer than those used by the build of SQL Server. I have found complications in the past when installing them side-by-side because sometimes they can be overwritten or updated by a Visual Studio install.

By installing Visual Studio and then SQL Server, any components that are required by SQL Server which have been updated by the Visual Studio installation are reinstalled by SQL Server itself.

That being said, with SSMS being a separate product Microsoft have been able to offer more frequent builds and problems are substantially reduced.


One approach is to create a database containing only synonyms referencing the objects in the snapshot you need for reporting. Clients can then connect to the synonym database and run queries just as if they were using the snapshot directly.

Whenever a new snapshot is created, run a script to update the synonyms with the new snapshot database name. Below is an example that recreates synonyms for all tables, views, and procedures in the snapshot. Add additional object types if needed.

USE SynonymDatabase;

DECLARE
      @SnapshotDatabaseName sysname = N'YourSnapshotDatabase'
    , @SQL nvarchar(MAX)
    , @DropSynonymsSQL nvarchar(MAX)
    , @CreateSynonymsSQL nvarchar(MAX);

--generate DROP SYNONYM script
SELECT @DropSynonymsSQL = (SELECT N'DROP SYNONYM ' + QUOTENAME(sch.name) + N'.' + QUOTENAME(syn.name) + N';
'
FROM sys.synonyms AS syn
JOIN sys.schemas AS sch ON sch.schema_id = syn.schema_id
FOR XML PATH(''), TYPE).value('(./text())[1]', 'nvarchar(MAX)');

--generate CREATE SYNONYM script
SET @SQL = N'USE ' + QUOTENAME(@SnapshotDatabaseName) + N';
SELECT @CreateSynonymsSQL = (SELECT N''CREATE SYNONYM '' + QUOTENAME(sch.name) + N''.'' + QUOTENAME(obj.name) +
    N'' FOR '' + QUOTENAME(@SnapshotDatabaseName) + N''.'' + QUOTENAME(sch.name) + N''.'' + QUOTENAME(obj.name) + N'';
''
FROM sys.objects AS obj
JOIN sys.schemas AS sch ON sch.schema_id = obj.schema_id
WHERE
    obj.type IN(''U'', ''V'', ''P'')
    AND obj.is_ms_shipped = 0
FOR XML PATH(''''), TYPE).value(''(./text())[1]'', ''nvarchar(MAX)'');';
EXECUTE sp_executesql
      @SQL
    , N'@SnapshotDatabaseName sysname, @CreateSynonymsSQL nvarchar(MAX) OUTPUT'
    , @SnapshotDatabaseName = @SnapshotDatabaseName
    , @CreateSynonymsSQL = @CreateSynonymsSQL OUTPUT;

BEGIN TRY
    BEGIN TRAN;
    EXEC sp_executesql @DropSynonymsSQL;
    EXEC sp_executesql @CreateSynonymsSQL;
    COMMIT;
END TRY
BEGIN CATCH
    IF @@TRANCOUNT > 0 ROLLBACK;
    THROW;
END CATCH;
GO

From Aaron:
The job that creates the snapshot could store the new database name in a table, and SSRS can use that data source to dynamically create its connection. This example is slightly different, but same general concept applies

https://www.mssqltips.com/sqlservertip/4302/implement-dynamic-data-sources-in-sql-server-reporting-services/


How to index/optimize sum of two columns in PostgreSQL?

If you have used the same TTL for every record you can avoid functional indexing by simple moving the TTL to the right side of comparison (thanks to jjanes for correction):

DELETE
  FROM temporarydata
 WHERE id IN ( SELECT id
                 FROM temporarydata
                WHERE created <= 1553755330 - ttl
                LIMIT 1000
             )

Optimizer will calculate the difference only once and then will use it against the indexed created column.

If you need the different TTL for different records you can store not the created -- ttl pair but precalculated created -- expires one

And you can use functional indexing:

CREATE INDEX expires ON temporarydata ((created+ttl));


Data Warehouse - Slowly Changing Dimensions with Many to Many Relationships

Does DimCategory have any Type 2 attributes that you are tracking?

If it is not, then you would only need to add a new record to your bridge table based on whenever a Type 2 attribute in DimPerson changes since you will be inserting a new record in DimPerson with a new surrogate key. You would then need to add or update your bridge table with this new surrogate key for whichever categories are assigned.

However, if an attribute on DimCategory changes, you would just update that record and there would be no change in the surrogate key so the bridge table would be unchanged.

Now if you want to track things like when a Category assignment to a Person changes as a Type 2 attribute, then you would need to build the Effective Start and End Dates in your bridge table to be able to tell at what point in time a Person's Categories change.


Window function with order by returns strange results

That's expected.

An aggregate function used as a window function together with an order by acts as a "rolling" aggregate, i.e. it only considers values up to the "current rows" based on the order by expression.

Quote from the manual

  There is another important concept associated with window functions: for each row, there is a set of rows within its partition called its window frame. Some window functions act only on the rows of the window frame, rather than of the whole partition. By default, if ORDER BY is supplied then the frame consists of all rows from the start of the partition up through the current row, plus any following rows that are equal to the current row according to the ORDER BY clause. When ORDER BY is omitted the default frame consists of all rows in the partition.

(Emphasis mine)

For a more detailed explanation see this answer


It is slow because the table is big, and it performs lots of IO when reading sparse data. The index on w2 would be used to tell which rows will be read from the base table, which is huge. And since the rows in that table are not in order- physically that are fragmented, there will be probably one disk access per row (This is to read the value of w1)
To make this query faster, create a composite index on (w2, w1). This way, the data is only read from the index file, which is much smaller in size, and the required data is adjacent.
Note please that if it is not a frequent query, it is an overhead to have this index.


How To Import Binary Dump File To SQL Server

The problem of tango ward is that he tries to pass full backup filee as a script file to sqlcmd.
I understood it as he wrote the error is

  Could not find stored procedure 'TAPE'.

The first bytes of a backup file even if it's disk file are 'TAPE':

To make a simplest restore from the bak file without restoring it to another database and moving the files you still can use sqlcmd, but you should pass your file as a backup file location, i.e. use this command:

restore database MyDB from disk = '/home/user1/Downloads/myspecialpresentforyou'

Here is simple example of doing it in sqlcmd:

First I launch sqlcmd (without parameters I'll connect to my
default instance with win authentication, but you should use your
sa user and password as you did)
Once launched sqlcmd you'll see 1> it's a prompt to enter your
command, now enter there RESTORE...(the whole command, see above) and
after entering it (press ENTER) you'll see 2>
Now enter GO to send your command

Here is how I restored my database abc from full backup Z:\Anna\backups\default\abc_full.bak

If you restore to another database you should first know logical file names using restore filelistonly and then move the files to new destination.

If it's your case you'd better use SSMS to see these files as sqlcmd output can be combersome if your db has many files.

Here the example in SSMS:


PostgreSQL dump distinct column without memory consumption

Solution suggested by jjanes worked, run set enable_hashagg=off in psql terminal


The .pdb files are detailed information for use in debugging or reporting errors in the executables in MySQL. If you decide you don't need this functionality, you can delete the files


Schema and Query design for table with multiple languages

Your data model is good.

The only addition I'd make is to add a primary key to the “language” tables that consists of (*_id, language). Every table should have a primary key, and there should be an index on the foreign key column.

Another thing you could do is to use an enum type or a lookup table for the language rather than using text. That avoids typos.


Restore backup in order to write datafile sequentially in disk

A restore will not correct the page fragmentation.  The best option would be to take an outage and defrag the entire database using DBCC INDEXDEFRAG or DBCC DBREINDEX.  For the OS file fragmentation if you simply detach the database, move the files to a different physical drive and then move it back you should be good.


How to specify authentication database and target database separately on mongodb connection uri?

Yes.. Easily!

mongo "mongodb://test:testpwd@localhost/test?authSource=admin"
MongoDB shell version v3.4.6
connecting to: mongodb://test:testpwd@localhost/test?authSource=admin
MongoDB server version: 3.4.6
MongoDB> db
test
MongoDB>

You put your destination DB at end of "base" string and add authentication db to parameter authSource


ORA-00905: missing keyword when trying to create a synonym

As the documentation says implicitly, you can create synonyms in the current DB only. So the first @PACOMNET in the synonym name is unnecessary (and causing the error). You should instead connect to the PACOMNET database and run

CREATE SYNONYM B2H.BILLING_USER_ACTIVITY
FOR B2H.BILLING_USER_ACTIVITY@PACOMNET.US.ORACLE.COM;


No buffer space available corresponds to a network error where the client is not enable to allocate the resources to initiate a connection.

Generally, this means that there are other programs holding too many connection handles (sockets).

According to a post on IBM developerWorks:

  This error can occur on Windows, particularly around socket
  operations. The error is translated from the Winsock error code
  WSAENOBUFS, 10055. The most common cause of this error is that Windows
  is configured for the default maximum of 5,000 in-use ports. This can
  be monitored by watching netstat or perfmon and can be changed with
  the MaxUserPort registry parameter.

  A more advanced cause for this error is non-paged pool exhaustion[...]

Uninstalling software will solve the problem for good only if that software was responsible for consuming too many simultaneous connections in the first place. Otherwise, getting back some of these resources may happen as a side-effect of say, rebooting, or just waiting a bit so that TCP connections in TIME_WAIT state disappear, or individually terminating programs that held these connections open.


Microsoft SQL Server equivalent to Oracle Enterprise Manger Top Activity Interface?

I think what you are looking for is Query Store, but this is available only for SQL Server 2016 or higher.

For SQL Server 2014 or lower, you can set up an Extended Events (XEvents) session to capture the queries running longer than X amount of time. XEvents are really easy to use and quite intuitive, the only thing you should pay attention to is how much data you are capturing as it can have some impact on performance or fill up the disk quickly. The only draw back of XEvents is that it will not provide you with a chart.


Products with different prices in an order

Below is my take on it:

Have two separate price columns, once for $ and the other for €. If in some stage in the future you will start taking payments in more currencies, you will likely use one of the prices as the base price and do a currency conversion calculation.
Depending on the complexity of the rules you have two choices:

a. Add "bundles" and "bundled products" tables which will store information about discount prices and what product combinations are eligible.
Pros:
You can easily implement functionality to manage bundles in the front end.
Cons:
Complex ad-hoc rules cannot be implemented this way.
b. Implement order price calculation rules in code (either database side or front end side, which will calculate final order price based on the products added.
Pros:
This approach allows for any number of complex rules specified.
Cons:
Requires program changes to change the rules.
The rules are not transparent to administrators / customers.


Why is this query throwing an "Invalid Column Name" error?

Try replacing the double quotes with single quotes?


Should i separate ContactInfo (sub-entity) from MyTable (main-entity) or Not?

I do not know exactly what you mean by 'updated many times' but I presume that you mean that the same ContactInfo can be present in multiple MyTable entries. In this case I would split it but put the ContactId in the MyTable entity. This way you are sure that all ContactInfo is the same everywhere it is used in MyTable.

The rule is that you put all fields that are identified by a single primary key in a separate entity. This way you minimize the number of updates when one of the fields that 'belong' to this primary key changes.

If 1 ContactInfo entry has only 1 MyTable entry and 1 MyTable entry has only 1 ContactInfo entry then the separation is not necessary. The only advantages are:

If the length of an entry is big there is less chance that an entry does not fit in the physical block any more and needs to be 'moved' to an other physical block by the RDBMS.
When you rarely need to select the ContactInfo and MyTable at the same time that (with a lot of rows) the fetch in of the rows goes faster since more entries will fit in a physical block.


how to combine multiple timestamps and keep the most recent one?

You need the greatest() function:

SELECT greatest(now(),
                now() + interval '1 minute',
                CURRENT_DATE::timestamptz,
                NULL);

           greatest
───────────────────────────────
 2018-04-17 19:40:04.568827+02

About NULLs:

  NULL values in the list are ignored. The result will be NULL only if all the expressions evaluate to NULL.

Please note that comparing timestamps (especially timestamps with time zones) to simple date values sometimes result in surprises.  I always try to use something like date_trunc('day', '2018-01-05'::date) to get a type that can be compared cleanly.


Find minimum record based on two fields, self joins vs. derived tables

You can use window functions for this:

WITH ct AS
  ( SELECT
        ThingID,
        Attribute1,
        Attribute2,
        Attribute3,
        Rn = ROW_NUMBER() OVER (PARTITION BY ThingID
                                ORDER BY DateID, SequenceID)
    FROM dbo.Fact
  )
SELECT
    ThingID,
    Attribute1,
    Attribute2,
    Attribute3
FROM ct
WHERE Rn = 1 ;

and an index on (ThingID, DateID, SequenceID).


Testing postgresql 9.4 streaming replication

Simply put, if the slave can be started up, both contents of master and slave are perfectly same.

I'll explain the details.
The slave is restored from the base backup which is the physical database back up of the master by replaying WAL log (transaction log).  So, the slave is a perfect replica of master and the difference between master and slave never occurs if slave can be ran.  In other words, if there are any differences between master and slave, the slave cannot be ran.

The simplest test is to create a small table at the master and to confirm the table at the slave, which is mentioned at that document.

Master:

postgres=# create table tbl (data text); insert into tbl values ('test');

Slave:

postgres=# select * from tbl;

If you can find the new table at the slave, the databases of slave is a perfect replica of master data.

Additionally,  this is a very rare case,  but the piece of  data of slave may be corrupted  by a failure of the copy tool.
If you want to avoid this failure, you should do VACUUM FULL statement for all tables at the master server.
If you do VACUUM FULL, all tables will be reconstructed at the slave as well as the master. Therefore, the corrupted data of the slave will be restored. (It's a dirty trick.)


UPDATE the records in oracle SQL with joinings and where exists query

Usually, when you need to join multiple tables in an UPDATE, MERGE is the solution.

merge into address a1 using
 (
   select
    p.address_fk,
    ta2.tempaddress1
  from
    premise p
    join account ac on (ac.id = p.account_fk)
    join tempaddresstable ta2  on (ac.ext_ref = ta2.tempextref)
 ) ta1
 on (a1.id = ta1.address_fk)
when matched then update set a1.address1 = ta1.tempaddress1;


Just execute on shell "use dbname"
When you insert the first document on any collection you will see the db with "show dbs"


InnoDB: Error: pthread_create returned 12

$ /usr/local/mysql/bin/perror 12
OS error code  12:  Cannot allocate memory

Sure, you have memory now but MySQL isn't running now.

Remove those multi-gigabyte configuration entries and verify that you can start the server without them.  I suspect read_buffer_size may be the one that's getting you but they are all unnecessarily large for a new server.  The warnings you're seeing for max_open_files and table_cache suggest that you are trying to tweak MySQL for performance by setting large values elsewhere in your config, too.

That's not going to do what you expect.  When it comes to buffer and cache sizes, more is not always better and often it is worse.  Comment out your custom settings and start with the defaults unless you have a specific reason not to... and when you do make changes, don't change more than one thing at a time unless the changes are directly related to each other.


Intermittent failures to connect to 127.0.0.1

Since this question is already quite old, I won't bother asking you to clarify the problem, and will instead try to focus on providing some things that might be worthwhile looking at, that some future visitor might find helpful.

Microsoft .Net Framework 4.5 and 4.5.1 have a known issue with connections to localhost
It's possible your server is suffering from thread-starvation.  Confirm the maximum number of expected simultaneous connections, including pooled connections that might not be actively performing tasks is below the maximum number of threads for your server.
Confirm there are no network-related filters in place that might be dropping connections.  Running netsh winsock show catalog will show the base service providers and any layered service providers that might be interfering with connectivity.  Particularly look for anti-virus or firewall apps.
Check network utilization, and if necessary tune networking parameters for performance.
If SQL Server is running in a virtual machine, check the host server to ensure a memory reservation has been set that prevents memory being swapped out (VMware, Hyper-V).  Ensure a CPU reservation has been set to prevent the virtual machine being starved of processing power.


It looks like your PHP is invalid. Shouldn't the second line be more like?

$sel_query="Select * from details where account_id=".$_SESSION['admin_id']." ORDER BY time DESC";


It really is a question of how your new data fits into the current model.

Option 1 - Same Data, need multiple associations

If your new "product category" data belongs to the same scope then you should put it into the question_codes table. And by scope, I mean would it look OK to see all of the data in question_codes table in a single drop down list in an application?

If you need to associate multiple rows of question_codes to questionnaire you should create an intermediate table between the two tables. This will require a migration of the existing associations into the new table.

    create table question_codes (
      code_id number,
      code_name varchar2(25),
      code_desc varchar2(25)
    );

    create table questionnaire (
       qst_id number
       /* , question_type number -- remove this column */
    );

    create table questionnaire_codes (
      qst_id number,
      code_id number
    );

    ALTER TABLE question_codes ADD (
      CONSTRAINT question_codes_PK PRIMARY KEY (code_id)
    );

    ALTER TABLE QUESTIONNAIRE ADD (
      CONSTRAINT QUESTIONNAIRE_PK PRIMARY KEY (QST_ID)
    );

    ALTER TABLE questionnaire_codes ADD (
      CONSTRAINT questionnaire_codes_PK PRIMARY KEY (qst_id, code_id),
      CONSTRAINT FK_ques_codes_question_codes FOREIGN KEY(code_id)
        REFERENCES question_codes (code_id),
      CONSTRAINT FK_quest_codes_questionnaire FOREIGN KEY(qst_id)
        REFERENCES questionnaire (qst_id)
    );

    insert into question_codes values (1,'QST1','Is Tangible');
    insert into question_codes values (2,'QST2','Is Perishable');
    insert into question_codes values (3,'QST3','Exported?');
    insert into question_codes values (4,'QSC1','Vendor Rating');
    insert into question_codes values (5,'QSC2','Vendor Payment');
    insert into questionnaire values (1);
    insert into questionnaire values (2);
    insert into questionnaire values (3);
    insert into questionnaire_codes values (1, 1);
    insert into questionnaire_codes values (1, 3);
    insert into questionnaire_codes values (2, 2);
    insert into questionnaire_codes values (3, 3);
    insert into questionnaire_codes values (3, 4);
    insert into questionnaire_codes values (3, 5);

    commit;

Option 2 - Different data

If your new "product category" data is a bit different than the existing data in question codes, you should create a new look-up table. Meaning, you would expect to see two separate drop down boxes in an application for questionnaire.

Associate the new look-up table with the questionnaire table.

    create table question_code (
      code_id number,
      code_name varchar2(25),
      code_desc varchar2(25)
    );

    create table question_category (
      cat_id number,
      cat_name varchar2(25),
      cat_desc varchar2(25)
    );

    create table questionnaire (
       qst_id number,
       code_id number,
       cat_id number
    );

    ALTER TABLE question_code ADD (
      CONSTRAINT question_codes_PK PRIMARY KEY (code_id)
    );

    ALTER TABLE question_category ADD (
      CONSTRAINT question_category_PK PRIMARY KEY (cat_id)
    );

    ALTER TABLE questionnaire ADD (
      CONSTRAINT QUESTIONNAIRE_PK PRIMARY KEY (QST_ID),
      CONSTRAINT FK_quest_question_codes FOREIGN KEY(code_id)
        REFERENCES question_code (code_id),
      CONSTRAINT FK_quest_question_category FOREIGN KEY(cat_id)
        REFERENCES question_category (cat_id)
    );

    insert into question_code values (1,'QST1','Is Tangible');
    insert into question_code values (2,'QST2','Is Perishable');
    insert into question_code values (3,'QST3','Exported?');
    insert into question_category values (1,'QSC1','Vendor Rating');
    insert into question_category values (2,'QSC2','Vendor Payment');
    insert into questionnaire values (1, 1, 2);
    insert into questionnaire values (2, 2, 2);
    insert into questionnaire values (3, 3, 1);

    commit;

Avoid Option 3 - Common lookup table

You can create a common lookup table that many tables can use at the expense of data integrity. If you go this route, you will have more bugs in your data and applications. Avoid this unless you have a time-to-market issue and plan to refactor after you get your angle investors.

    create table code_category (
      code_category number,
      description varchar2(25)
    );

    create table question_code (
      code_id number,
      code_category number,
      code_name varchar2(25),
      code_desc varchar2(25)
    );

    create table questionnaire (
       qst_id number,
       vendor_code_id number,
       type_code_id number
    );

    ALTER TABLE question_code ADD (
      CONSTRAINT question_codes_PK PRIMARY KEY (code_id)
    );

    ALTER TABLE code_category ADD (
      CONSTRAINT code_category_PK PRIMARY KEY (code_category)
    );

    ALTER TABLE questionnaire ADD (
      CONSTRAINT QUESTIONNAIRE_PK PRIMARY KEY (QST_ID),
      CONSTRAINT FK_quest_vendor_codes FOREIGN KEY(vendor_code_id)
        REFERENCES question_code (code_id),
      CONSTRAINT FK_quest_type_code FOREIGN KEY(type_code_id)
        REFERENCES question_code (code_id)
    );

    insert into code_category values (1, 'Question Types');
    insert into code_category values (2, 'Vendor Types');
    insert into question_code values (1, 1, 'QST1','Is Tangible');
    insert into question_code values (2, 1, 'QST2','Is Perishable');
    insert into question_code values (3, 1, 'QST3','Exported?');
    insert into question_code values (4, 2, 'QSC1','Vendor Rating');
    insert into question_code values (5, 2, 'QSC2','Vendor Payment');
    insert into questionnaire values (1, 1, 4);
    insert into questionnaire values (2, 2, 4);
    insert into questionnaire values (3, 3, 5);

    commit;

Excuse me while I take a shower after writing that last example.


How to insert multiple returns of the select statement?

Since you don't appear to have any common information in these two tables, I'd recommend doing a distinct cross apply:

INSERT INTO ClassList (StudentID, FacultyID, ModifiedDate)
SELECT DISTINCT student.studentID
    ,faculty.FacultyID
    ,getdate()
FROM Student
CROSS APPLY faculty


Shrink Transaction Log While Using AlwaysOn Availability Group 2014

When you use AlwaysOn Availability Group, even if you take a proper backup the log file might grow larger and the log drive might get full over time. In order to maintain proper (shrink) log file size you can use the following technique.

On the AlwaysOn configuration, change the backup priority options to primary replica/server.
Since the databases are by default in a full recovery mode, take at least one a full and one transaction log backup.
Shrink the log files of all databases on primary replica. This will truncate the empty the log drives on all availability replicas.
Finally, schedule a job to take appropriate backup on a regular basis. This will keep you log file on the right size.
I hope this will help!


Dynamically generate batched data for import

The actual rate of ingest is going to be based on the number of spindles (physical hard disks) behind the RAID that holds your data files and log files.

A decent RAID setup should grant you the ability to achieve ~1 M rows/sec or higher.

Your algorithm is another major bottle neck.

Method 1

You can skip the create file step by making Python connect to the database directly.

I don't know if there is a Timescale DB driver for Python.

For speed:

Use BIND variables
Cache your Statement Handle
If the driver supports it, use BIND Arrays.

Make sure the "number of rows per BIND Array" and "number of groups between COMMIT" are variables in your program.  You'll want to play with these to find the sweet spot for your system.  That is: You will need to run benchmarks to find the best values for your setup.

The insert_vendor_list in this example uses the BIND Array technique.

Method 2

Sometimes, you want to review the data before it is generated.  In that case, you want to create a .csv file, not an .sql file full of INSERT statements.  Each of those INSERT statements will need to be hard parsed.

The RDBMS databases I have played with come with specialized applications that can ingest a CSV at (or near) the maximum rate (as defined by your RAID setup).

Notes on Speed Performance

Depending on what you are really testing, you should disable/remove all indexes and constraints on the target table prior to ingesting the data.  Then, create them afterwards.


What is the replacement for EXEC sp_dboption  in SQL Server 2012?

Most of the options are in sys.databases


This is a very open question, with lots of considerations to be made and lots of choices that are more "opinions" than "facts". So, I won't try to provide an answer, but to point out some of the big differences and things to conisder.

If your only need is to store key/value pairs (page_id, body), a key/value datastore is a good match for your need. You can use Riak, Oracle NoSQL and several others (you can take a look at the Wikipedia for a longer list). You get very fast access to your bodies (or titles); normally you also get some easy sharding and replication (i.e.: MongoDB), which allows for really good horizontal scalability if you ever need really very huge amounts of data.

What you don't get is an easy way to join data and query it. If one day you decide to have a list of (for instance) article authors, or article comments, or whatever, the key-value store might not be that comfortable any more.

As a rule of thumb, key-value stores or document stores require that you plan very well in advance how your data is going to be used, because the structure of your documents will have to reflect this usage. A relational database is based on structured tables where you store information which can be searched and joined easily.

The majority of contemporary SQL databases allow for some combinations that can be thought as "the best of both worlds", mixing SQL with JSON (or documents). For instance, you can check PostgreSQL hstore, which gives you a key-value store within a SQL database, or use JSON with MySQL, PostgreSQL, Oracle, SQL Server, and others.

You can check previous questions such as Use SQL or NoSQL?, or Should I use SQL or NoSQL for this specific design (surveys)?.

I'd also recommend SQL vs NoSQL: The Differences


Difference between host '*' and host '%'

Per create user % has a wildcard meaning. _ also matches a single character like the like expression.
* has no meaning and was probably put there by someone not familiar with what they are doing. Recommend dropping this use.


How can I prevent UNPIVOT from being transformed into UNION ALL?

Try the following Oracle optimizer hint:

NO_EXPAND

This hint disables OR-expansion. OR-expansion transforms combined OR-conditions or IN-lists in the where clause into a compound query with the union all.

We don't have your SQL so this is a guess but seems to a viable option.

As a second option, since you are in a development environment, you can try the Oracle optimizer hint:

NO_QUERY_TRANSFORMATION

but this hint disables all query transformations that the optimizer can perform other than transformations that the optimizer always can apply.


Migrate SQL Server database with instance level logins

There are some fantastic Powershell scripts located at dbatools.

You could use Copy-SqlDatabase to migrate your databases.

Then, you could take advantage of Copy-SqlLogin by generating the Powershell commands via T-SQL script for the logins you're interested in.

select 'Copy-SqlLogin -Source sqlserver -Destination sqlcluster -Logins ' + name + '' from sys.database_principals where type_desc='sql_user'

After migration, make sure you take care of orphan users.  You could use something like this:

declare @cmd varchar(max)=''
select
    @Cmd=@cmd + 'ALTER USER ' + dp.name + ' WITH LOGIN = ' + dp.name + ';' + char(10)
from
    sys.database_principals dp left join
    master.sys.server_principals sp on
        sp.name=dp.name
where
    dp.type='s' and
    dp.default_schema_name<>dp.name and
    sp.name is not null
order by dp.name
--print @Cmd
exec (@cmd)


This may have been an existing problem with the database you backed up.

Do you need to preserve the existing Service Broker conversations?  Messages?  Broker Identifier?

If you don't need the Service Broker messages, or to preserve any of its conversations or place in a distributed broker topology, you might end all the conversations WITH CLEANUP, or ALTER DATABASE ... SET NEW_BROKER.

If you do need to fix this while preserving the other conversations and messages, you would probably need to open a support case, as there are really only a handful of people who can advise you on this.


Selecting a proper data warehouse and/or big data approach

I think you should be thinking about what your end state is going to be.

I.e: "What is the requirement"?

The requirements should be driving the solution. Not the other way around.

What does the business want?
What reports do you need? What outputs?
Will your reporting solution benefit from aggregation? Would an OLAP cube be of benefit? Will you be transforming or denormalising the data?
Will your DW be fed from one system or many? If many: will it be application agnostic?
What display tools and technologies are expected? Any compatibility issues that might limit platform selection?
How long do your current reports take? And how fast do they need to be?. How fast is fast enough? What changed?
Can you produce the reports you need now with the data you have?
Are conventional reports still acceptable? Or does the business now need/want dynamic interactive tools? Reports? Dashboard? Exploration tools? Mobile?
How recent must the data be? Is yesterdays data processed overnight acceptable? Or do they need near real time?
Do they want analytical or operational data? What happened over the last 12 months or what happened this morning?

Once you have a clear requirement, it will allow you to shortlist viable solutions. Within your shortlist you can start to express preferences and pros and cons for the evaluation process.


Find and remove gaps in sequence across two different columns

You can store pre-calculated gaps, and use constraints to make sure that your pre-calcualted data is always up-to-date:

Here is the table and the first interval

CREATE TABLE dbo.IntegerSettings(SettingID INT NOT NULL,

  IntValue INT NOT NULL,

  StartedAt DATETIME NOT NULL,

  FinishedAt DATETIME NOT NULL,

  PreviousFinishedAt DATETIME NULL,

  CONSTRAINT PK_IntegerSettings_SettingID_FinishedAt PRIMARY KEY(SettingID, FinishedAt),

  CONSTRAINT UNQ_IntegerSettings_SettingID_PreviousFinishedAt UNIQUE(SettingID, PreviousFinishedAt),

  CONSTRAINT FK_IntegerSettings_SettingID_PreviousFinishedAt

    FOREIGN KEY(SettingID, PreviousFinishedAt)

    REFERENCES dbo.IntegerSettings(SettingID, FinishedAt),

  CONSTRAINT CHK_IntegerSettings_PreviousFinishedAt_NotAfter_StartedAt CHECK(PreviousFinishedAt <= StartedAt),

  CONSTRAINT CHK_IntegerSettings_StartedAt_Before_FinishedAt CHECK(StartedAt < FinishedAt)

);

GO

INSERT INTO dbo.IntegerSettings(SettingID, IntValue, StartedAt, FinishedAt, PreviousFinishedAt)

  VALUES(1, 1, '20070101', '20070103', NULL);

It has five constraints which work together to implement the business rule. Let me demonstrate how the more complex ones work. Of course, some constraints are simple and as such do not need any explanations.

**

There can be only one first interval for a setting

**

The constraint UNQ_IntegerSettings_SettingID_PreviousFinishedAt ensures exactly that. The first interval does not have a previous one, which means that PreviousFinishedAt IS NULL. The UNIQUE constraint  guarantees that there can be only one such row per setting. See for yourself:

INSERT INTO dbo.IntegerSettings(SettingID, IntValue, StartedAt, FinishedAt, PreviousFinishedAt)

  VALUES(1, 1, '20070104', '20070105', NULL);

/*

Server: Msg 2627, Level 14, State 2, Line 1

Violation of UNIQUE KEY constraint 'UNQ_IntegerSettings_SettingID_PreviousFinishedAt'. Cannot insert duplicate key in object 'dbo.IntegerSettings'.

The statement has been terminated.

*/

**

Next window must begin after the end of the previous one.

**

The constraint CHK_IntegerSettings_PreviousFinishedAt_NotAfter_StartedAt guarantees exactly that. See for yourself:

INSERT INTO dbo.IntegerSettings(SettingID, IntValue, StartedAt, FinishedAt, PreviousFinishedAt)

  VALUES(1, 2, '20070104', '20070109', '20070105')

/*

Server: Msg 547, Level 16, State 1, Line 1

INSERT statement conflicted with TABLE CHECK constraint 'CHK_IntegerSettings_PreviousFinishedAt_NotAfter_StartedAt'. The conflict occurred in database 'RiskCenter', table 'IntegerSettings'.

The statement has been terminated.

*/

**

Two different windows cannot refer to one and the same window as
their previous one.

**

Again, the same constraint UNQ_IntegerSettings_SettingID_PreviousFinishedAt guarantees precisely that, as demonstrated below:

INSERT INTO dbo.IntegerSettings(SettingID, IntValue, StartedAt, FinishedAt, PreviousFinishedAt)

  VALUES(1, 3, '20070104', '20070115', '20070103')

Msg 2627, Level 14, State 1, Line 1

Violation of UNIQUE KEY constraint 'UNQ_IntegerSettings_SettingID_PreviousFinishedAt'. Cannot insert duplicate key in object 'dbo.IntegerSettings'.

The statement has been terminated.

This means that there can be no overlaps.

As you have seen, for every time window, there can be at most one preceding it, and at most one following it. The following interval cannot begin before the end of its previous one. Together these two statements mean that there can be no overlaps.

**

Working with gaps.

**

You can prohibit gaps altogether, just replace the following constraint:

  CONSTRAINT CHK_IntegerSettings_PreviousFinishedAt_NotAfter_StartedAt CHECK(PreviousFinishedAt <= StartedAt),

With a stricter one, as follows:

 CONSTRAINT CHK_IntegerSettings_PreviousFinishedAt_EqualTo_StartedAt CHECK(PreviousFinishedAt = StartedAt),

But if you allow gaps, the query to retrieve them is very simple and performant, as follows:

SELECT PreviousFinishedAt AS GapStart, StartedAt AS GapEnd
  FROM dbo.IntegerSettings
  WHERE StartedAt > PreviousFinishedAt;


SELECT DISTINCT is sometimes an indication that someone has 'fixed' a query that returns duplicate rows in a naive way. This (mis-)use is probably more common among those with relatively little database experience.

That said, some people (Chris Date, for example) argue that DISTINCT should be the default qualifier for SELECT; we should have to write SELECT ALL to return a non-relational result (relations do not contain duplicate tuples by definition).

My view is that there is nothing inherently wrong with SELECT DISTINCT. It can be the most natural way to express a query. If the execution plan and performance is acceptable, I see no sensible reason to mandate that it should never be used.

At the same time, it is clearly wrong to simply add DISTINCT at the top level of a complex query to eliminate duplicates at a late stage, when the query should be simply written more efficiently.


what does I/O affinity automatically do when cpu affinity is manually set

Been a long time since I have played around with this setting.

The point of setting up CPU affinity is so you can manually load balance the CPUs and make sure certain task always have resources and do not compete with each other system loads.

If the affinity is set to automatic MSSQL CPU scheduler will pick a CPU for the load.  This has the advantage that if a CPU becomes loaded and other CPUs are unloaded it can switch to an unloaded CPU

But if the affinity is set MSSQL scheduler will not switch the load to another process unless its in the affinity list.

IO and CPU both have an affinity list and can be controlled independently.  Setting one to Automatic and the other to a specific list means the MSSQL scheduler can move one around automatically

Key point do not set both OS affinity and MSSQL it can cause interesting problems

FROM the help files

  When specifying the affinity I/O mask option, you must use it in connection
  with the affinity mask configuration option. Do not enable the same CPU in
  both the affinity mask switch and the affinity I/O mask option. The bits
  corresponding to each CPU should be in one of these three states:

  0 in both the affinity mask option and the affinity I/O mask option.

  1 in the affinity mask option and 0 in the affinity I/O mask option.

  0 in the affinity mask option and 1 in the affinity I/O mask option.

It also suggested by MS and many others not to configure this unless there is a clear need to exclude CPU's and/or manually load balance resources.


Stack dumps when turning on Temporal Table SYSTEM_VERSIONING

This might be your problem:

A dump file is generated when you enable system-versioning on a table in SQL Server 2016

Try installing CU1 of Sql 2016 SP1


How to create Clustered and non clustered index in Oracle?

Does Clustered index exist in Oracle database? since I read in some blogs

Yes there is.
It is called "index organized table" (IOT) - which in my opinion is the better name as it makes it absolutely clear that the index and the table are the same physical thing (which is the reason why we can have only one clustered index in SQL Server)

If yes, please let me know the SQL statement to create a cluster index.

There is no such thing as create clustered index in Oracle.
To create an index organized table, you use the create table statement with the organization index option.
In Oracle you usually use IOTs for very narrow tables. Very often for tables that only consist of the primary key columns (e.g. m:n mapping tables), e.g.
create table assignment
(
   person_id  integer not null,
   job_id     integer not null,
   primary key (person_id, job_id)
)
organization index;

You can create IOTs with more column, in that case the you need to define the non-pk columns as "included" columns. E.g. if the assignment table should have additional columns, like start and end date that are not part of the primary key:
create table assignment
(
   person_id   integer not null,
   job_id      integer not null,
   start_date  date,
   end_date    date,
   primary key (person_id, job_id)
)
organization index
including start_date
overflow storage (initial 4k);

See the manual for more details and examples: https://docs.oracle.com/database/121/SQLRF/statements_7002.htm#i2153235

Somewhat unrelated, but maybe interesting anyway:
An interesting blog post that questions SQL Server's (and MySQL's) behaviour of using a clustered index as the default when creating a table:
Unreasonable Defaults: Primary Key as Clustering Key
Coming from an Oracle background I wholeheartedly agree with that.


Shared Location Options in MS SQL Server Data Migration Assistant

That is for backups that can be accessed by both source and Target servers.

Think of it Just like logshipping has a backup share that is accessed by primary backup job and secondary copy jobs.


You could simplify your life with a shell script. Here is one I created for Linux, it is based on the mysqlcheck application

This script will check all tables in all databases except mysql and information_schema

vi check_all_databases.sh

#!/bin/bash
USER=root
Port=3306
PASSWD=password

DBS="$(/usr/bin/mysql --connect_timeout 10 -u $USER -P $Port -p$PASSWD -Bse 'SHOW DATABASES')"
for db in $DBS
        do
                if [ "$db" != "mysql" ] && [ "$db" != "information_schema" ];
                then
                        /usr/bin/mysqlcheck -u $USER -p$PASSWD -c $db
                fi
        done

Save the file

\wq

Then execute it

sh check_all_databases.sh

dbt2.customer                                      OK
dbt2.district                                      OK
dbt2.history                                       OK
dbt2.item                                          OK
dbt2.new_order                                     OK
dbt2.order_line                                    OK
dbt2.orders                                        OK

For Windows you would use a batch script or vbs to do the same thing.

The other option would be just to run the mysqlcheck application for the one database you are interested in checking.

/path/to/mysqlcheck -u username -pPASSWORD -c database_name

mysqlcheck will be in the directory you installed your mysql binary files in.

In Linux, you can locate it with the which command

which mysqlcheck


What do you call a relationship between two entities without a foreign key?

It is still a foreign key, because it's a feature of your data. However, it is not enforced by a referential integrity constraint.

In my view this would mean that the foreign key would be present in the logical model ERD, but absent in the physical model.


How to do a case sensitive search in WHERE clause?

by default, MySQL does not consider the case of the strings

This is not quite true.  Whenever you create database in MySQL, the database/schema has a character set and a collation.  Each character set has a default collation; see here for more information.

The default collation for character set latin1, which is latin1_swedish_ci, happens to be case-insensitive.

You can choose a case-sensitive collation, for example latin1_general_cs (MySQL grammar):

CREATE SCHEMA IF NOT EXISTS `myschema`
DEFAULT CHARACTER SET latin1
COLLATE latin1_general_cs ;

This has an effect on things like grouping and equality.  For example,

create table casetable (
  id int primary key,
  thing varchar(50)
);

select * from casetable;
+----+-------+
| id | thing |
+----+-------+
|  3 | abc   |
|  4 | ABC   |
|  5 | aBc   |
|  6 | abC   |
+----+-------+

In a case-sensitive database, we get:

select thing, count(*) from casetable group by thing;
+-------+----------+
| thing | count(*) |
+-------+----------+
| ABC   |        1 |
| aBc   |        1 |
| abC   |        1 |
| abc   |        1 |
+-------+----------+

select * from casetable where thing = "abc";
+----+-------+
| id | thing |
+----+-------+
|  3 | abc   |
+----+-------+

While in a case-insensitive database, we get:

select thing, count(*) from casetable group by thing;
+-------+----------+
| thing | count(*) |
+-------+----------+
| abc   |        4 |
+-------+----------+

select * from casetable where thing = "abc";
+----+-------+
| id | thing |
+----+-------+
|  3 | abc   |
|  4 | ABC   |
|  5 | aBc   |
|  6 | abC   |
+----+-------+

Note that you can also change the collation from within a query.  For example, in the case-sensitive database, I can do

select * from casetable where thing collate latin1_swedish_ci = "abc";
+----+-------+
| id | thing |
+----+-------+
|  3 | abc   |
|  4 | ABC   |
|  5 | aBc   |
|  6 | abC   |
+----+-------+


What things we should consider when configuring Cassandra Cluster?

what basic things we should consider when are we going to configure Cassandra cluster in on or multiple database centers?

This is a bit of an open-ended question, but I build multi-DC Cassandra deployments on a fairly regular basis, so I'll try to provide some insight here.

The first thing to consider, is what you hope to achieve by building a presence in both data centers.  The main reasons are data locality and disaster recovery.

Data locality is in reference to the application Cassandra is serving.  So if an application team is deploying to two DCs, they're going to want their data nearby.  Disaster recovery is good to have if you lose an entire DC.  This allows you to easily and quickly rebuild another logical or physical DC.

Another point to consider is whether or not the data centers are hosted by the same provider.  Assuming a cloud deployment, you'll want to understand the network between the cloud regions, what your bandwidth is, and how much it'll cost to use.  Obviously, things will be easier if your nodes can easily communicate to each other cross-DC when they need to.

If the data center providers are different, you're going to want be very sure about how consistent that network connection is.  In this scenario, you're going to want to set the phi_convict (default 8) to a higher value.  That will give your nodes a little more leniency in marking nodes as up or down, when the network becomes unstable.

You will also want to set the replication strategy on the system_auth table to use the NetworkTopologyStrategy, and make sure it has an appropriate number of replicas in each DC.  Otherwise, your authorization requests will be jumping DCs.


How can I restore a 10.91G table in Oracle Express?

The space a table takes up and the space it needs can differ.

I'd recommend using impdp with the sqlfile option to put the create table script into a file. Edit that to try to maximise the use of space (set pctfree to 0). Remove any primary key or unique constraint, as any index will eat into your available storage.

The INITIAL will be the amount of space initially allocated. Start with whatever is in the sqlfile output and nudge the value down until the create statement succeeds. Then use impdp with TABLE_EXISTS_ACTION=TRUNCATE so it will insert the data into the already created table.

Cross your fingers that there's enough room. If not, you can look at applying a WHERE clause as part of the impdp and split the data into two.


Use a BEFORE trigger instead, and set the updated column assigning a value to NEW.updated (this would be a simple assignment, not an UPDATE).  This way you won't trigger additional UPDATEs.

Your trigger body will simply look like

SET NEW.updated = NOW()

I usually use AFTER triggers only for modifying other tables, BEFORE for modifying the new (or updated) row, or suppress DELETE.


Silent truncation of last character when using SSIS with Oracle

It looks like the problem was caused by a data flow task where I had two different data flows running in parallel. The same source tables were involved in both data sources but with different destinations. When I broke the task up into two tasks running one after the other, the truncation problem seems to have gone away.


Measuring latency of a long-distance SQL connection

Just to clarify some math here:

360 queries per "property"
60 properties * 360 = 21,600 queries
3 minutes in duration is about 120 queries per second

So in your best case, you're averaging 120 queries per second throughput.

After moving the SQL Server to somewhere that has a ping latency of 65ms, you're averaging 7 queries per second.

Your best bet is to

measure wait statistics while this query is running

If you don't have a monitoring tool, a simple way to do this is running sp_BlitzFirst, an open source tool developed by Brent Ozar Unlimited.  It'll take a snapshot of your wait stats (among other things), wait 5 seconds, and then take another snapshot, and show you a summary of the biggest issues
You can also see the wait stats since the server started using a query like the one in Paul Randal's blog post: Wait statistics, or please tell me where it hurts

Once you've got those results, examine the highest wait stats, and use the SQL Skills wait stats library to dig into what they mean

Based on your description, it's likely that you'll see ASYNC_NETWORK_IO waits.  This can indicate slow network transfer.  But usually it means that the client application is doing row-by-row processing, and SQL Server is waiting to send rows (when it really wants to fire them over the network as fast as possible).

On a related note - in general, it would be far more effective to try to pull larger "groups" of data - rather than running 21,000 queries, and thus making 21,000 round trips over the (potentially slow) network.  This is called a "chatty" interface or API, and is generally considered to be an anti-pattern.

Consider that 65ms * 21,600 queries is 1,404,000 ms, AKA over 23 minutes of overhead.  That's a big chunk of overheard that could be removed simply by limiting the number of roundtrips.


postgres system catalog query columns from table

Just select from information_schema.columns instead.

SELECT table_catalog, table_schema, table_name, data_type
FROM information_schema.tables
WHERE table_schema='{schema}';

FOR GLORY

For whatever reason, if you can't query information_schema.columns (which would otherwise indicate something is funky to me). We can reverse engineer the catalogs efficiently with psql -E with a user that can. Then run the appropriate client (\) command in psql that shows you what you want, like \d schema. And copy out the parts of the exported query you need..

For me the final column in that is something like

SELECT a.attname,
  pg_catalog.format_type(a.atttypid, a.atttypmod),
  (SELECT substring(pg_catalog.pg_get_expr(d.adbin, d.adrelid) for 128)
   FROM pg_catalog.pg_attrdef d
   WHERE d.adrelid = a.attrelid AND d.adnum = a.attnum AND a.atthasdef),
  a.attnotnull, a.attnum,
  (SELECT c.collname FROM pg_catalog.pg_collation c, pg_catalog.pg_type t
   WHERE c.oid = a.attcollation AND t.oid = a.atttypid AND a.attcollation <> t.typcollation) AS attcollation,
  NULL AS indexdef,
  NULL AS attfdwoptions
FROM pg_catalog.pg_attribute a
WHERE a.attrelid = '1024334' AND a.attnum > 0 AND NOT a.attisdropped
ORDER BY a.attnum;

Now I just need to get all the attrelid's for the schema. Running a quick \d asdofkodskf I see,

SELECT c.oid,
  n.nspname,
  c.relname
FROM pg_catalog.pg_class c
     LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
WHERE c.relname ~ '^(asdofkodskf)$'
  AND pg_catalog.pg_table_is_visible(c.oid)
ORDER BY 2, 3;

We actually want n.nspname not c.relname

So given my version of psql, the official method of querying the catalog directly would be...

SELECT c.oid,
  n.nspname,
  c.relname, t.*
FROM pg_catalog.pg_class c
LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
CROSS JOIN LATERAL (
  SELECT a.attname,
    pg_catalog.format_type(a.atttypid, a.atttypmod),
    (
      SELECT substring(pg_catalog.pg_get_expr(d.adbin, d.adrelid) for 128)
      FROM pg_catalog.pg_attrdef d
      WHERE d.adrelid = a.attrelid
        AND d.adnum = a.attnum
        AND a.atthasdef
    ),
    a.attnotnull, a.attnum,
    (
      SELECT c.collname
      FROM
        pg_catalog.pg_collation c,
        pg_catalog.pg_type t
      WHERE c.oid = a.attcollation
        AND t.oid = a.atttypid
        AND a.attcollation <> t.typcollation
    ) AS attcollation
  FROM pg_catalog.pg_attribute a
  WHERE a.attrelid = c.oid
    AND a.attnum > 0
    AND NOT a.attisdropped
) AS t
WHERE n.nspname ~ '^(public)$'  -- YOUR SCHEMA HERE
AND pg_catalog.pg_table_is_visible(c.oid);

Excerpt (without all the columns or all the rows) (relname is table, attname is column)

   oid   | nspname |           relname           |       attname        |       format_type
llation
---------+---------+-----------------------------+----------------------+-------------------------
--------
 1024242 | public  | spatial_ref_sys_pkey        | srid                 | integer
 1045853 | public  | product_discount_qty_excl   | qty                  | int4range
 1024334 | public  | valid_detail                | valid                | boolean
 1024334 | public  | valid_detail                | reason               | character varying
 1024334 | public  | valid_detail                | location             | geometry
 1045847 | public  | product_discount            | pid                  | integer
 1045847 | public  | product_discount            | qty                  | int4range
 1045847 | public  | product_discount            | percent_modifier     | real
 1024569 | public  | geography_columns           | f_table_catalog      | name


Incorrect behaviour of SQL Server 2016 with memory-optimized tables

Yes it is a bug, which seems to only affect table variables, with a bw-tree index access method, and an uncorrelated self-join.

Simplified repro using DELETE:

CREATE TYPE dbo.IN_MEMORY_TABLE_TYPE AS TABLE
(
    col integer NOT NULL INDEX i NONCLUSTERED (col)
)
WITH (MEMORY_OPTIMIZED = ON);
GO
DECLARE @T AS dbo.IN_MEMORY_TABLE_TYPE;

INSERT @T (col)
VALUES (1), (2), (3), (4), (5);

DELETE T
FROM @T AS T
WHERE EXISTS
(
    SELECT 1
    FROM @T AS T2
    WHERE T2.col = 1 -- Vary this number 1-5
);

SELECT T.col FROM @T AS T;
GO
DROP TYPE dbo.IN_MEMORY_TABLE_TYPE;

Note in the above plan the search for rows to delete terminates earlier than expected (only two rows are read from the scan). Halloween Protection is generally correctly handled for In-Memory OLTP, there just seems to be a specific issue with the combination of factors mentioned above.

This bug is fixed in SQL Server 2016 SP1 CU5 and SQL Server 2017 CU1:

FIX: Incorrect behavior when you use memory-optimized tables with "where exists" statement in SQL Server 2016 or 2017


Excel spreadsheet into a MySQL database tables

I did not look in detail at the data since I belief you can normalize it yourself, but from what I've grasped out of your posting I would say you are doing everything right.

One warning though: Likely you will encounter a lot of existing inconsistencies in the excel sheets. You must fix those in order to import and normalize the data correctly. This constitutes a high initial effort.

The alternative (keep the data in the form it is) instead will rise the maintenance costs of the application.

In the long run I claim that the additional initial costs will be less than the regular addition in maintenance cost.


Pervasive linked server provider missing

You will need to set up a System DSN in order to connect to it using the ODBC drivers from SSMS/SQL Server - simple installing the drivers is not enough. I had  to do the same in order to connect to an older instance that our company uses. Here is an excellent link that will help guide you through the process:

http://discoverysoftware.com/support/kb/acorn-docs/bridges-acorn-docs/odbc-bridges/odbc-set-up-pervasive-sql/

Once you have set up the DSN within the System Data Sources you will be able to create a linked server to it.


How prepare and transfer database from mysql 5.7 to 8.0.19 since mysql_upgrade has been removed

Do a logical dump with mysqldump instead of physical backup with xtrabackup or similar tool.


I think you need to output your results to a file and bring them back in. Something like this should help get you close to what you are looking for:

:setvar MyDir "C:\scripts"
:OUT $(MyDir)\test.txt
PRINT ':SETVAR ServerName ''' + @@SERVERNAME + ''''
GO
:OUT stdout
:r $(MyDir)\test.txt
GO
SELECT $(ServerName)

You can see more examples here.


Your left outer join looks to be condensing your 100,000 rows in tblstatistics into approximately 2,643:

2   DERIVED tblstatistics   ALL NULL    NULL    NULL    NULL    *2643*    Using temporary; Using filesort

and then it places that grouping in into a temporary table. At this point, it applies the aggregate functions (SUM, AVG, etc).

Unfortunately, since you are not limiting your tblstatistics with a WHERE statement, this is probably the best you can do.

Overall, the only limiting you do is on ps.active=1. This is probably a really low cardinality column, and if it's an index, it provides no benefit as MySQL will ignore it anyway.


Unlocking sample accounts like HR in Oracle 19c/18c/12c

Edit tnsnames.ora  file located at %db_home%\network\admin

Add TNS ORCLPDB like below-highlighted block

Open CMD as Administrator and execute below command

C:\>lsnrctl reload

Open CMD and execute SQLPlus with below command to login as SYSDBA

C:\WINDOWS\system32> sqplus / as sysdba

Run below SQL commands to Unlock your Schema

  ALTER SESSION SET container = ORCLPDB;

Check if the pluggable database is opened ?

SELECT name, open_mode FROM v$pdbs;

  To open your pluggable database Skip If Already open

ALTER PLUGGABLE DATABASE open;

Unlock HR user

ALTER USER hr IDENTIFIED BY <your_password> ACCOUNT UNLOCK;
conn
username hr@orclpdb
password <your_password>
Connected.

Connected means you can access HR schema lets start

  Now you will be able to access HR schema

select * from tab;


Synchronizing two MySQL Server both serving clients after reconnect

If you set them up with dual-master replication, they will be continually in sync.  But don't write to both at the same time.


Your new design will make it easier to query across all of the tables without the need to know which tables to union when you write a query.

I'm not sure if you need to worry about the partitioning aspect. It sounds OK, but I've never worked with something like this on this scale (not in MySQL).

If you are worried about the partitioning, you could stay with your current solution, but instead of a new table for every year+month, have 12 tables, one for each month, for all years. At least your unions would be constant.


SET SESSION variables apply only to the current connection.
SET GLOBAL variables apply as the default when a connection is made.
SESSION does not need root access; GLOBAL does.
Be careful what you set it to:

Let's say you find that the default is

mysql> SELECT @@sql_mode;
+--------------------------------+
| @@sql_mode                     |
+--------------------------------+
| STRICT_ALL_TABLES,NO_ZERO_DATE |
+--------------------------------+

Blindly setting it will turn of those two while setting the one you want:

mysql> set session sql_mode = 'ONLY_FULL_GROUP_BY';
mysql> SELECT @@sql_mode;
+--------------------+
| @@sql_mode         |
+--------------------+
| ONLY_FULL_GROUP_BY |
+--------------------+

So, build the full string:

mysql> SET sql_mode = 'NO_ZERO_DATE,STRICT_ALL_TABLES,ONLY_FULL_GROUP_BY';

mysql> SELECT @@sql_mode;
+---------------------------------------------------+
| @@sql_mode                                        |
+---------------------------------------------------+
| ONLY_FULL_GROUP_BY,STRICT_ALL_TABLES,NO_ZERO_DATE |
+---------------------------------------------------+

Addenda

If you want to add/subtract a mode without knowing what mode(s) are already set, this seems to work:

Turn on (for session only):

SET @@sql_mode = CONCAT(@@sql_mode, ',', 'ONLY_FULL_GROUP_BY');

Turn off (for session only):

SET @@sql_mode =
        REPLACE(
        REPLACE(
        REPLACE(@@sql_mode, 'ONLY_FULL_GROUP_BY,', ''),
                           ',ONLY_FULL_GROUP_BY', ''),
                            'ONLY_FULL_GROUP_BY', '');

Note carefully the commas.


Estimating the size (of the records) and the overhead in Postgres

Functions that give the size of columns, tables, and indexes are documented in the manual: http://www.postgresql.org/docs/9.4/static/functions-admin.html

There is no function to calculate the size of an entire record (while there is a function to know the storage cost of an individual data value (pg_column_size)), since records are in general of variable length (sometimes they are compressed), so I think you have two possibilities, either perform a catalog query to sum up all the sizes of the columns of a table, or simply get the size of a populated table and divide by the numbers of records, so to have an average size for record.


How to optimise read/write performance a MySQL table with many rows?

As you need to perform a read,search operation faster you can use the MyISAM as the table engine if the table will not have the High write's in future.

When you will use the MyISAM Engine for fast read/search for this table ,you need to set the Key_Buffer_size to some appropriate value depending upon the index size of your MyISAM tables and also the amount of available RAM.

As the Table having two columns id and domain name, so you can use the FULL TEXT index on domain name so it will make searching fast on the column domain name.

Look the structure which I have Created

mysql> show create table domain\G
*************************** 1. row ***************************
       Table: domain
Create Table: CREATE TABLE `domain` (
  `id` int(10) NOT NULL AUTO_INCREMENT,
  `domain_name` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`),
  FULLTEXT KEY `domain_name` (`domain_name`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

Restore the data in table and find out index_length for MyISAM table and set the Key_Buffer_size accordingly.

See How to set Key Buffer Size.


If possible. What you need to do is create public synonyms for the tables, grant GRANT SELECT to the user who performs the query.

CREATE PUBLIC SYNONYM app_table1 FOR schema1.app_table1;

CREATE PUBLIC SYNONYM app_table2 FOR schema2.app_table2;

GRANT SELECT ON app_table1 TO app_user;

GRANT SELECT ON app_table2 TO app_user;

Your query will be like this:

Select * from app_table1 ap1 inner join app_table2 ap2


Use SQL Server Database Mail with Amazon SNS

I have been away from SQL Server for over a decade but will try to answer this based on my familiarity with what SNS is(n't).

SNS is able to fan-out a notification to multiple destinations over a number of outbound transports:

email (raw, message body only)
email (json, with metadata about the SNS message in addition to the body)
http or https to a web endpoint
SMS to a mobile device
SQS, publishing the message to Amazon Simple Queue Service queue(s)

However, it only has one inbound transport: a web API, so it isn't exactly an email distribution management platform though it does have portions of that functionality.  As such, you wouldn't be able to submit messages directly into SNS from an SMTP client.

If there is a mechanism by which you could publish the notifications with an http client within SQL Server, then you could potentially wire this in... but otherwise, you'd need to find or write an SMTP-to-SNS gateway.

Such a "gateway" would emulate an SMTP server on the front-end and an SNS API client on the back-end, to bridge this gap.  I've written one of these in Perl as a proof-of-concept, but it's not in production yet and it has a lot of hard-coded proprietary assumptions, so wouldn't be particularly useful outside of my specific environment -- but is was not particularly difficult to implement.  The message ID returned by SNS is handed back to the SMTP client as the SMTP transaction id.  (This implementation assumes the message is being submitted by a smart host, and puts the burden on the SMTP client, returning a 4XX error if for any reason the SNS API interaction fails -- accepting emails from SQL Server, you might need to shift that burden onto the gateway.)

(SNS can also send addressible push notifications to mobile applications, but this is a very different application of SNS, and uses a different API, but also doesn't accept inbound SMTP).


Cannot see Green Button with white arrow in object explorer; DTC config problems?

Your best bet is to download DTCPing (http://www.microsoft.com/en-us/download/details.aspx?id=2868) and run it from both machines.  If I recall correctly, the servers initially talk over the RPC port (135) and then negotiate a random, high port (above 1024) to continue the transaction on.  There is a registry entry that will allow you to limit the ports RPC chooses, but it will apply to all RPC calls not just DTC calls.  http://support.microsoft.com/kb/908472


From High Performance MySQL Third Edition,

  Loose Index Scans
  MySQL has historically been unable to do loose index scans, which scan noncontiguous ranges of an index. MySQL’s index scans generally require a defined start point and a defined end point in the index, even if only a few noncontiguous rows in the middle are really desired for the query. MySQL will scan the entire range of rows within these
  end points.

A loose index scan is simply a scan that skips over part of the index. How does it do that? Assuming an index on (a,b) a loose index scan allows you to query for specific values of b without scanning all values of b. It does this by starting at the beginning of the range of b searching, and then skipping backward to find a new a. Compare this with a (tight) index scan where the whole index is scanned and there is no backtracking.

Backtracking in this context usually means moving up on a tree (back) to the previous position.

  I was reading that MySQL can not use an index on 2 range conditions due to not having a "loose index scan"

I don't think that was ever true. It (most RDBMS) can use an index, but without an implementation of a loose index scan they would have had to scan the entire index, as in an Index-only scan. This saves having to visit the table. This may or may not be the desirable action (in the cases where it would have to visit the table anyway), when the selectivity is low, or when the index is close enough to the size of the table that it simply doesn't matter.

See also

MySQL 8: Group by Optimization - Loose Index Scan
PostgreSQL Method (with a Recursive CTE)

Footnotes

I'm not sure whether the book's statement about MySQL is still accurate as the book was printed in 2012 and covers MySQL 5.5.


It looks like one (or more) of the shared memory segments already exists; there wasn't a clean shutdown.  Use onclean — it should clean up for you.

When you restart, consider using 'verbose' mode: oninit -v.  It will report what it is doing until the server is up and running.


Don't RESEED tables on Query Store Clean

Assuming you're using the command ALTER DATABASE [YourDatabaseName] SET QUERY_STORE CLEAR to "clean the query store," I decided to see what queries are run behind the scenes when that command executes.

To that end, I set up a basic Extended Events session by using the "Query Detail Tracking" XE template that's included with SSMS.  This captures several events that should cover most queries running on a system:

error_reported
module_end
rpc_completed
sp_statement_completed
sql_batch_completed
sql_statement_completed

By default it excludes queries running in the context of the system databases (database_id > 4), which is fine for this test since query store lives within the user database where it's enabled.

After starting that XE session, I ran the ALTER DATABASE...SET QUERY_STORE CLEAR command, and the following queries were picked up by the session:

TRUNCATE table sys.plan_persist_runtime_stats;
TRUNCATE table sys.plan_persist_runtime_stats_interval;
TRUNCATE table sys.plan_persist_plan;
TRUNCATE table sys.plan_persist_query;
TRUNCATE table sys.plan_persist_query_text;

Adding the optional "ALL" parameter after "CLEAR" ran this additional statement:

TRUNCATE table sys.plan_persist_context_settings;

You may notice that these are the base tables for the Query Store catalog views.

TRUNCATE TABLE resets identity values:

  If the table contains an identity column, the counter for that column is reset to the seed value defined for the column. If no seed was defined, the default value 1 is used. To retain the identity counter, use DELETE instead.

So this behavior is "by design" - you can't depend on the identity values in the query store tables across "query store clear" operations.

What problem are you solving by clearing the query store?  Perhaps we could suggest a different option if you provide that context in a new question.

Some potential options for removing some queries without truncating the tables are found here:

Best Practice with the Query Store - Keep the most relevant data in Query Store

Configure time-based policy to activate auto-cleanup
Activate size-based cleanup policy
Configure Query Capture Mode to Auto

The first two options cause rows in the Query Store to be deleted by id based on their age and / or how expensive the queries were.

The second option limits the amount of data being added to the Query Store in the first place.


mysql picasa like photo tags store and query

If the number of tags allowed will never be greater than 10, then the simplest solution is to have ten 80-character fields attached to the photo table, named tag1, tag2,...

This is a very simple way to do it, but could lead to problems. If you want to have more or less tags in the future (and I see this sort of thing happen very often), you'll have to change the table structure. You also have to think of what happens when a user adds some tags, then removes one, then adds a different one: how do you decide what value to drop into which tag_n fields?

Another approach would be to normalize the tags and have a separate tags table, and then a tags_photos table to keep track of which tags have been applied to a specific photo.

This is slightly more complicated in terms of structures, but if you choose later to allow more tags, nothing needs to be done and if you decide to reduce the number of allowed tags, old photos could be permitted to keep their larger number of tags. You also remove redundancy by moving the tags to a separate table and referencing them by a tag_id.

Personally, I prefer the second approach. It's cleaner and more flexible.


Design: Fitting variable hierarchy of text files into a relational db

You could store the text file data in a table like this:

config_files
------------
  ID (PK)
  name
  parent_dir_id (FK to config_dirs.id)
  content

config_dirs
-----------
  ID (PK)
  name
  original_user (key to the table that stores the original user data)
  parent_dir_id (key to config_dirs.ID, needs to be nullable to indicate a root directory)

Populating this structure, you could do something like this:

config_dirs
-----------
ID    | name       | parent_dir_id | original_user
------+------------+---------------+--------------
1     | conf       | (null)        | Toby Derius
2     | users      | 1             | Toby Derius
3     | admin      | 2             | Toby Derius
4     | bob        | 2             | Toby Derius

config_files
------------
ID    | name       | parent_dir_id   | content
------+------------+-----------------+--------
1     |general.cfg | 1               | This is the general config...
2     |admin.cfg   | 3               | admin's config...

This could allow you to query the directory structure and the file contents for specific users. Storing large amounts of plain text is not always a great idea, in terms of performance, so you might also want to consider storing the contents in a separate table file_contents and then referencing the file_contents from config_files. Also look into fill-text search engines.


MySQL is pretty slow after 50-100k rows whats happen?

This is what happened:

UUID VARCHAR(36)

UUIDs (GUIDs, MD5s, etc) are very random.  That is, if you can't keep the entire table (or index) cached in RAM, the 'next' lookup is likely to cause a disk hit.  And disk hits are a big factor (think 90%) of the cost of performing queries.

More discussion here.  That includes a few partial remedies.

More importantly

WHERE LOWER(ma.aliasTitle) = LOWER(manufacturer)

has terrible performance because it hides an indexable column inside a function (LOWER()).  Instead, make sure those two columns have a collation ending with _ci (as in "Case Insensitive").  Then simply do

WHERE ma.aliasTitle = manufacturer

and have

INDEX(aliasTitle)
INDEX(manufacturerTitle)
INDEX(distributorSKU)

If you run into some index limit size, please provide MySQL version and SHOW CREATE TABLE.  Meanwhile, distributorTitle VARCHAR(256) is likely to cause trouble.  Changing to (255) may work; (191) may be needed for 5.5 or 5.6; beware of truncating strings.

One more thing -- When you CREATE the procedure, be sure to have established the collation for the connection that is consistent with the columns.  For example, if you are using CHARACTER SET utf8 COLLATE utf8_general_ci for the columns, then you could do it this way:

SET NAMES utf8 COLLATE utf8_general_ci;
DELIMITER //
CREATE ...
//
DELIMITER ;


Postgres: multiple selects that exclude results from previous ones

I'm stumped myself.  Is this what you're looking for?

select * from (
  select id, name, acnt, bcnt, ccnt,
         row_number()
         over (partition by id order by (acnt+bcnt+ccnt)) RN
    from (
      select w.id, w.name,
        (select count(*)
           from modes m, mode_connections mc,
                connections c
           where m.name = 'A'
             and m.id = mc.mode_id
             and mc.connection_id = c.id
             and c.widget_id = w.id )  ACNT,
        (select count(*)
           from modes m, mode_connections mc,
                connections c
           where m.name = 'B'
             and m.id = mc.mode_id
             and mc.connection_id = c.id
             and c.widget_id = w.id )  BCNT,
        (select count(*)
           from modes m, mode_connections mc,
                connections c
           where m.name = 'C'
             and m.id = mc.mode_id
             and mc.connection_id = c.id
             and c.widget_id = w.id )  CCNT
         from widgets w
    )
    where acnt >= 2 and bcnt >= 2 and ccnt >= 1
) where RN = 1


mongo db-Either all host names in a replica set configuration must be localhost references, or none must be; found 1 out of 2

You can have all replica set nodes in the same physical computer (localhost), but in different ports! (node1:27017, node2:27027, ...)
You CANNNOT have different RS nodes:

at one machine at same port (f.ex. 27017)
different machine and bind to localhost address

So, you need to build your replica set using IP-addresses.


How to determine if SQL Server will not execute parts of the script in parallel?

GO

is a batch terminator in SSMS. Every time you have a GO (or a ;) you have told SQL Server that the batch just ended. So you are sending your commands in batches, and the batches are executed after another - not at the same time.

SO in this case they are all executed serially. Really anytime you run commands they are executed serially in one connection.

The bigger question in my mind is - Why are you doing Shrinkfile? I hope this isn't a production database? This is not a best practice operation to regularly be running at all, as this link describes.

Preview: Shrinking your data files shouldn't be needed - unless you just cleared a ton of space you'll never grow back into, are having serious disk capacity issues or are doing something in dev - and even in these situations, there may be alternatives that are better for you.

Shrinking means introducing index fragmentation. It means some expensive IO operations and there is a good chance you'll be growing back into the space eventually anyway if your database is still live and in use with inserts and updates.


How to search records based on date range for other matching data

Try ans use EXISTS to get all records where another (or the same for that matter) exists with the same app ID, a stage of 'D1' and the start date or the end date in march.

Assuming you're on Oralce, this could look like this:

SELECT *
       FROM elbat t1
       WHERE EXISTS (SELECT *
                            FROM elbat t2
                            WHERE t2.app_no = t1.app_no
                                  AND stage = 'D1'
                                  AND (t2.start_date >= to_date('2019-03-01', 'yyyy-mm-dd')
                                       AND t2.start_date < to_date('2019-04-01', 'yyyy-mm-dd')
                                        OR t2.end_date >= to_date('2019-03-01', 'yyyy-mm-dd')
                                           AND t2.end_date < to_date('2019-04-01', 'yyyy-mm-dd')));


Can't install a OLE DB Provider for IBM DB2 ISeries on SQL Servers higher than 2008 version

If you want to use the Microsoft provider, you'd need to use the Microsoft provider's name and not IBM's provider name.

The IBM provider is/was part of Client Access for Windows.  You don't mention what version you are trying to install, but the latest is v7r1 and it supports up to Windows 8.1.

IBM has replaced client Access for Windows with the new Access Client Solution (ACS).  ACS is split into two packages, an OS independent package and a OS dependent one.

The OLEDB driver is part of the ACS Windows Application Package.

Both the Client Access for Windows and the ACS Windows application package are licensed software and available only through IBM's Entitled Software Support website (ESS).  You'll need your IBM i folks to download it for you, or help you get access.


Assuming you are on SQL Server 2016 or greater, you can use the JSON_VALUE function to parse out an individual value:

SELECT  JSON_VALUE(@JSON,'$.ErpOrderResponses[0].OrderNumber');

If you are going to be processing more than one row, I recommend you use OPENJSON for better performance:

SELECT
    *
FROM
    OPENJSON(@JSON,'$.ErpOrderResponses')
    WITH (OrderSubmitStatus varchar(20),OrderNumber bigint);

Documentation: https://docs.microsoft.com/en-us/sql/t-sql/functions/openjson-transact-sql?view=sql-server-2017


Error: Invalid data directory

This happens when pg_wrapper is unable to figure out the data directory from the configuration. pg_wrapper is the layer on top of postgres that can juggle between several live PostgreSQL instances on debian-based systems. As installed by the postgresql-client package, psql is a link to pg_wrapper on such systems:

/usr/bin/psql -> ../share/postgresql-common/pg_wrapper

Since you mention slightly modifying postgresql.conf, make sure you didn't change the permissions inside /etc/postgresql/, including postgresql.conf.
That looks like the most plausible change that would immediately lead to the mentioned error. The next one would be that your changes are syntactically wrong, in a way that pg_wrapper would no longer be able to parse the file.


Import .frm and .ibd files into a new instance of MySQL

It should work copying the whole data directory to the target machine given the following conditions:

You must have the whole data directory (ibdata1 and iblogfiles included).
Try to use the same my.ini file.
The OS where the source DB files were running must be the same as of the target OS.
Try to use MySQL 5.5.x. If you need to upgrade to 5.7 you must first install 5.6.x and run mysql_upgrade script, install 5.7.x and run again mysql_upgrade script, OR install 5.5.x, run a mysqldump, and then importing the dumpfile on 5.7.x.

If you don't have the ibdata1 and iblogfiles then you must apply the procedure of the thread that you shared, scripting as Rick suggested.


Query to get total time logged in per week

There's probably a more elegant solution but using your criteria from your comment to @Erik that the logout will be recorded this should get you what you need, or very close:

DECLARE @UserID sysname;
DECLARE @LastUserID sysname;
DECLARE @Time datetime;
DECLARE @EventType varchar(10);
DECLARE @CurrentSessionRowID int;

CREATE TABLE #results (
        ID int IDENTITY
               PRIMARY KEY
      , UserID sysname
      , LoginTime datetime
      , LogoutTime datetime
      , [Duration(Hours)] AS DATEDIFF(mi, LoginTime, LogoutTime) / 60.  --  datediff by minutes so partial hours can be displayed in the final results
       );

DECLARE userSessions CURSOR FAST_FORWARD
FOR
        --  find all the user sessions in the order they were input for each user
        SELECT  t.UserID
              , t.[Time]
              , t.EventType
        FROM    dbo.test AS t
        ORDER BY t.UserID
              , t.ID;

OPEN userSessions;
FETCH NEXT FROM userSessions INTO @UserID, @Time, @EventType;
SET @LastUserID = @UserID;

WHILE @@FETCH_STATUS = 0
      BEGIN
            --  same user, determine if this is a login or logout and record it in the #results table
            IF @UserID = @LastUserID
               BEGIN
                     IF @EventType = 'login'
                        BEGIN
                              INSERT    INTO #results
                                        (UserID, LoginTime)
                              VALUES    (@UserID, @Time);
                              SET @CurrentSessionRowID = SCOPE_IDENTITY();

                        END;
                     IF @EventType = 'logout'
                        BEGIN
                              UPDATE    #results
                              SET       LogoutTime = @Time
                              WHERE     ID = @CurrentSessionRowID;
                        END;

               END;
            ELSE
               BEGIN
                     -- new user, make sure the first record is login and start recording sessions
                     IF @EventType = 'login'
                        BEGIN
                              INSERT    INTO #results
                                        (UserID, LoginTime)
                              VALUES    (@UserID, @Time);
                              SET @CurrentSessionRowID = SCOPE_IDENTITY();

                        END;
               END;

            SET @LastUserID = @UserID;
            FETCH NEXT FROM userSessions INTO @UserID, @Time, @EventType;
      END;
DEALLOCATE userSessions;

--  Query the results however you want with grouping and aggregates
SELECT  *
FROM    #results AS r;

DROP TABLE #results;


Is there anyway to find the closest matching JSON entry inside a database

Just a rough sketch on how you could do that with Postgres arrays.

There are two array operators that you might be interested in. The "overlaps" operator that returns true if some of the elements are the same and the "contains" operator that checks if all elements of one array are contained in the other - that would be your 100% match:

Assuming this table:

create table idlist (ids int[]);

You can create an index on that column:

create index on idlist using gin (ids);

A query to find the 100% match looks like this:

select *
from idlist
where ids @> array[1,2,3,4,5,6]

That will return only rows where ids contains all the values 1,2,3,4,5,6 (but it might contain more).

On a table with 2 million rows and arrays from 5 to 100 elements this takes about 20ms on a pretty old test server (the actual runtime depends on the total amount of matches found)

The query to find rows that contain at least one of the values of the parameter uses the && operator and is substantially slower.

select *
from idlist
where ids && array[1,2,3,4,5,6];

That takes about 300ms on my test server. But it also returns 500.000 rows! If the condition only returns very few rows (less than thousand) then obviously this is going to be a lot faster.

How you proceed from there is a matter on how restrictive the conditions are that you use. If the && operator only returns a few rows (not half a million) then maybe processing them in your application might be more efficient.

Getting the information if the input parameter is completely contained, could be done like this:

select ids,
       ids @> array[1,2,3,4,5,6] as full_match
from idlist
where ids && array[1,2,3,4,5,6];

Another option would be to write your own function that calculates the match, or maybe use the intarray extension to efficiently calculate the intersection between two arrays.


How much will computer hardware affect the speed of a "load data local infile" query?

I would like to recommend something with regard to LOAD DATA INFILE:

You need to increase bulk_insert_buffer_size (such as 256M) because a special tree structure is made to accommodate the loading of the data. Even with LOAD DATA INFILE, the tree structure is used when loading data into non-empty tables.

According to the MySQL Docs on bulk_insert_buffer_size:

  MyISAM uses a special tree-like cache to make bulk inserts faster for
  INSERT ... SELECT, INSERT ... VALUES (...), (...), ..., and LOAD DATA
  INFILE when adding data to nonempty tables. This variable limits the
  size of the cache tree in bytes per thread. Setting it to 0 disables
  this optimization. The default value is 8MB.


Please do this after denying access to other database .. i think newely created user's default database is MASTER you need to give him newley created db as default database. you can do this by following steps

1) go to SECURITY >> LOGINS >> Select USER >> RIGHT CLICK on it >> Select PROPERTIES >> Select you newley created Database in DEFAULT DATABASE

Please see below screenshot for the reference


Resolving Queries into a List of `object name.field`

Oracle 12c provides the procedure DBMS_UTILITY.EXPAND_SQL_TEXT.

EXPAND_SQL_TEXT Procedure

If you have a query, it can expand its text to involve th base tables and its columns.

Example:

SQL> create table t1 (C1 number, c2 number);

Table created.

SQL> create view v1 as select * from t1;

View created.

SQL> create or replace view v1 as select c1 as c3, c2 as c4 from t1;

View created.

SQL> create or replace view v2 as select * from t1;

View created.

To find the tables and columns used for select * from v2:

SET SERVEROUTPUT ON
DECLARE
  l_clob CLOB;
BEGIN
  DBMS_UTILITY.expand_sql_text (
    input_sql_text  => 'SELECT * FROM v2',
    output_sql_text => l_clob
  );

  DBMS_OUTPUT.put_line(l_clob);
END;
/

Output is:

SELECT "A1"."C1" "C1","A1"."C2" "C2" FROM  (SELECT "A2"."C1" "C1","A2"."C2" "C2"
FROM SYS."T1" "A2") "A1"

The rest is up to you though.