Altering Column Size On a Super Large Table

The best method is always specific to your requirements and situation. Some approaches might be faster than others but require more temporary space or disallow end users from querying the table. Based on what you've said in your question I'm going to assume the following:

Your primary concern is reducing space and you don't want to have to grow your database to perform this operation.
Azure SQL limits your options in moving the data to a new database or filegroup.
You want the table to remain online for end user queries during the maintenance operations.
The table has a clustered index index.

One approach to fix it:

Immediately make changes in the application to prevent end users from loading new data that's above your desired maximum length. No reason to let the problem get worse while you figure out what to do.
Verify that your desired maximum length will free up enough space. If a simple COUNT_BIG(*) is too slow you can use TABLESAMPLE and a bit of math to figure out how many rows you'll delete. I created a 100 GB table on my local machine and running a 1% sample query took less than a second with a cold cache. Example code below:

DROP TABLE IF EXISTS #BIG_TABLE;

CREATE TABLE #BIG_TABLE (
    ID BIGINT NOT NULL,
    FOR_U NVARCHAR(MAX),
    PRIMARY KEY (ID)
);

DECLARE @big_string NVARCHAR(MAX) = REPLICATE(CAST(N'Z' AS VARCHAR(MAX)), 200000);

INSERT INTO #BIG_TABLE WITH (TABLOCK)
SELECT TOP (250000) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) RN, @big_string -- 100k is good
FROM master..spt_values t1
CROSS JOIN master..spt_values t2
OPTION (MAXDOP 1);

CHECKPOINT;
DBCC DROPCLEANBUFFERS;

SELECT COUNT_BIG(*) total_sampled_rows
, COUNT_BIG(CASE WHEN DATALENGTH(FOR_U) > 8000 THEN 1 ELSE NULL END) sampled_rows_too_long
FROM #BIG_TABLE TABLESAMPLE (1 PERCENT);

Notice the use of DATALENGTH instead of LEN. See Datalength optimizations for LOB data types… for the reasons you might find that performs better.
Delete rows from the table in small batches. Use the clustered key to loop over all rows exactly once. If you'd like more detail here, try Take Care When Scripting Batches. Otherwise, the core idea is to increment local variables as you process the table:

DECLARE @start_id BIGINT = 1;
DECLARE @end_id BIGINT = 500;

DELETE FROM #BIG_TABLE
WHERE ID BETWEEN @start_id AND @end_id;

Once all of the deletes are done, stop and measure your progress. Is the table small enough now? You may need to run a REORGANIZE or REBUILD to fully reclaim space. I recommend REORGANIZE because it's not an all-or-nothing operation (if you cancel it you keep your progress), it won't grow your log file, and it's an online operation.
Stop here. If the problem is that the table takes up too much space then you've solved that the problem by removing unneeded rows from the table. You probably do not need to change the data type in order to reclaim space. Having an unnecessary NVARCHAR(MAX) isn't ideal, but the downsides may not justify the effort in changing the data type. If you do need to change the data type then it will be easier to do so after you've freed up some space in your database.


Restore SQL Server 2008 database onto SQL Server 2014?

Check if the SQL Server user account is able to read from "\Mylocation\DBRestores\", the security permissions from file share and file system.

To really test that it works, copy the backup to local server and run it from there.


Create new Read Only user on DB2 on the whole database

There is no read only privilege on a database in DB2. You will need to grant SELECT privileges on specific tables, preferably via a role to avoid doing this multiple times.

Create a new database role, e.g. create role readonly.
Grant SELECT privileges on the required tables to that role:
grant select on myschema.mytable to role readonly.
Create a new user in the operating system, e.g. # mkuser newuser.
Grant the role to the user: grant role readonly to user newuser.

Note that the new user will likely need to change the initial password before he or she can connect to the database.


It would be interesting to know if there are indexes on your tables (and if there are, their structure as well).  There is the slight possibility that these would help to understand the reason behind the third query being much slower than the second one.

Well, this does not answer this mystery, but apart from the above, you should omit the Supplier table from your queries.  Since you don't select any data from it, the only thing it does is multiplying the number of the rows selected (SELECT any_column FROM table1 t1, table2 t2 without further conditions on which t1 row to be joined to which t2 row produces the Cartesian product of the two tables - it can be written as table1 t1 CROSS JOIN table2 t2 in other RDBMSes, however in SQLite CROSS JOIN only fixes the order in which tables are processed).  You apparently use SELECT DISTINCT to get rid of the multiplicated results (@ypercube, thanks for the suggestion).

It would be interesting to see whether the speed difference is still there after these modifications.  If the answer is 'yes', than again I would try to swap the two parts of the WHERE clause and check the difference again.  (Well, SQLite seems to be quite smart avoiding plan gotchas like this, but you never can tell with bees.)


Informix onunload/onload to take a snapshot and then restore

First of all, Informix supports transactions if you create a logged database (WITH LOG or WITH BUFFERED LOG).  Further, it supports transactions (BEGIN) and transaction nesting with SAVEPOINT and COMMIT and ROLLBACK TO SAVEPOINT.

Using onunload and onunload might be workable, but you need to be confident you aren't using blobs (smart blobs), or UDTs (user-defined types).  I'd have to research the details of how to use them for your proposed task; it probably is fiddly.  I'm not sure that I've seriously used either command this millennium, and I didn't often use them in the last millennium either.

If you have several Informix server instances, you could consider using ER (Enterprise Replication) to make a copy of the relevant sections of the database in a separate server, and then do your experimentation on that other server.

If you have shared disks, you might be able to use an SDS (shared disk secondary) node with care.

If transactions are sufficient, I recommend using them rather than the alternatives.  Just ensure you have enough logical log space to be able to roll the transactions back (look up 'long transaction', LTXHWM and LTXEHWM).


What is the purpose of the database 'owner'?

There is some confusion out there between the database concepts of the 'dbo' (a user) and 'db_owner' (a fixed role) on one side and the instance concept of 'database owner' on the other side. The 'dbo' and 'db_owner' are often called 'database owner'. In what you're asking you are talking about the database owner as the server principal that owns the database.

The theory goes like this: anything that can be granted permissions on is a 'securable'. All securables have an owner. The owner of a securable has absolute control over the securable and cannot be denied any priviledge. Instance level securables are owned by server principals (logins). Database level securables are owned by database principals (users). Principal come in two flavor: primary (identity) and secondary (membership). Server level securables are by default owned by the currently logged primary server principal. Database level securables are owned by default by the current database principal, except for schema bound objects that by default are owned by the schema owner. All securables support the AUTHORIZATION clause at create time to enforce a different owner. ALTER AUTHORIZATION can be later used to change the owner of any securable.

Since the database is a server level securable it follows that it will be, by default, owned by the primary principal that issued the CREATE DATABASE statement. Ie. the NT login of the departed employee.

So your question is really "Why do securables need an owner?". Because the owner is the root of trust. It is the owner that grants, denies and revokes permission on the object. Can a security system be designed without owners of securables? Probably yes, but there would have to be some mechanism in place to replace the role owners play in the current model. For instance consider that dad securables have no owner (eg. instead of owning a securable, the original creator is just granted CONTROL over it) it would be possible create a securable and revoke access on it to everybody, including himself. The requirement of an owner circumvents this problem since an owner cannot lock himself out.

The little known side effect of CREATE DATABASE of creating a securable (the database) owned by original NT login has burned many before. The rules are the same for every securable, but some factors aggravate the DATABASE owner issues:

the other server level securables (endpoint, server role, login) are far seldom used, moved around etc.
database level securables usually end up by being owned by dbo (the database principal), or some other database principal, and thus the owner is contained with the database
Having the database ownership default to the NT primary principal creates a containment issue (the owner is an NT SID managed by AD and does not travel with the database files, the NT account can be thumbstoned etc etc etc)
the most important thing: the database owner has important side effects, specifically the EXECUTE AS context. This later problem is what burns most users. Since Service Broker makes extensive use of EXECUTE AS (the message delivery has an implicit EXECUTE AS context, as well as queue activation that has an explicit one) is usually  Service Broker users that discover this problem first.

BTW, Kudos for investigating and fixing your original problem :)


SQL Server 2008 R2 - Service shutdown - what with running stored procedures?

When I go to the services.msc and stop the SQL Server, the service
  will be in 'Stopping' state until @ScheduledProcedure is completed
  (8-10 mins).

What you are seeing is the default behavior when you shut down SQL Server. If you do not want want to wait you can use WITH NOWAIT switch but when SQL server starts recovery time will be longer as transactions has to roll forward and roll back.

Details here.

Unless the WITHNOWAIT option is used, SHUTDOWN shuts down SQL Server by:

Disabling logins (except for members of the sysadmin and serveradmin
fixed server roles).
Waiting for currently running Transact-SQL statements or stored
procedures to finish. To display a list of all active processes and
locks, run sp_who and sp_lock, respectively.
Inserting a checkpoint in every database.
Using the SHUTDOWN statement minimizes the amount of automatic
recovery work needed when members of the sysadmin fixed server role
restart SQL Server.

  When I shut the OS down it seems that SQL Service cancels stored
  procedure. I think that it is the case, because Stage 2 and 3 messages
  are not available in test_log table.

In this case SQL Server has no control as operating system issue a shut down process command and that is why you are seeing immediate shutdown.Transactions that were completed and written to the transaction log but not yet applied to the database, will be applied to the database. Open, unfinished, transactions are rolled back, again to ensure the database is in a consistent state.  Details about roll forward and roll back here.

Different ways to shut down SQL Server.


Nope. Unless you explicitly enabled logging within each and every SSIS package, there is no guaranteed way to know if it's being run.

You can look through SQL Agent job steps, check for xp_cmdshell calls that either use dtexec.exe or call batch scripts which then use dtexec.exe and then discover some clever sod wrote a windows service that uses the SSIS object model to run them. Or it's the unscheduled process that is only run when Accounting says the books are closed at month/quarter/year end and only then is it run.


How to pass a column name using string_split - ms sql server?

You'll need to use dynamic SQL to implement your logic, by constructing a whole new SELECT query.

For example:

DECLARE @CMD nvarchar(max), @amount NVARCHAR(50) = 'column_name1/column_name2/column_name3'
SELECT @CMD = ISNULL(@CMD + ', ', N'SELECT ') + QUOTENAME([value])
FROM STRING_SPLIT(@amount,'/')
SET @CMD = @CMD + N' FROM table1'
PRINT @CMD
EXEC(@CMD)

Its output would be:

SELECT [column_name1], [column_name2], [column_name3] FROM table1

In order to get all the values under a single column, you'll need to "UnPivot" the data. There are two main ways to do so. One is by a standard UNPIVOT command, like so:

DECLARE @CMD nvarchar(max), @amount NVARCHAR(50) = 'column_name1/column_name2/column_name3'
SELECT @CMD = ISNULL(@CMD + ', ', N'SELECT unpvt.* FROM table1 UNPIVOT (amount FOR amounttype IN ( ') + QUOTENAME([value])
FROM STRING_SPLIT(@amount,'/')
SET @CMD = @CMD + N' ) AS unpvt'
PRINT @CMD
EXEC(@CMD)

Its output would be:

SELECT unpvt.* FROM table1 UNPIVOT (amount FOR amounttype IN ( [column_name1], [column_name2], [column_name3]) AS unpvt

Another way is to use CROSS APPLY with a Values Constructor, like this:

DECLARE @CMD nvarchar(max), @amount NVARCHAR(50) = 'column_name1/column_name2/column_name3'
SELECT @CMD = ISNULL(@CMD + '), (', N'SELECT v.amount FROM table1 CROSS APPLY(VALUES(') + QUOTENAME([value])
FROM STRING_SPLIT(@amount,'/')
SET @CMD = @CMD + N')) AS v(amount)'
PRINT @CMD
EXEC(@CMD)

Its output would be:

SELECT v.amount FROM table1 CROSS APPLY(VALUES([column_name1]), ([column_name2]), ([column_name3])) AS v(amount)


How do you configure a 4-node AlwaysOn cluster to have two listeners?

is there a way to configure to have two listeners

Sort of, but it's unusual.  The more normal practice is to have a single AG Listener, with read-only routing configured.  That way everyone connects to the same hostname, but read-only workloads will disconnect and reconnect directly to an available readable replica.

  would SQL on all four nodes need to run under the same service account?

It's recommended, but not required, to use a single Group Managed Service Account for all the nodes.  If the instances have different service accounts, Kerberos won't work when connecting to the AG Listener.


Can't reduce cost of query plan and get rid of Key Lookup because of cursor

Two solutions were mentioned in the comments to your question.  Let's review both of them.

Use a READ_ONLY cursor

  As the article implies, are you able to use the STATIC and READ-ONLY arguments? – Jacob H 23 hours

That comment refers to Paul Randal's blog post Adventures in query tuning: unexpected key lookups.  This points out that a cursor type is dynamic optimistic by default.  From the Microsoft Docs, you can see that the "optimistic" part is what's causing all your problematic key lookups:

  OPTIMISTIC
  ...
  It instead uses comparisons of timestamp column values, or a checksum value if the table has no timestamp column, to determine whether the row was modified after it was read into the cursor.

So it has to do the key lookups to check and see if the rows you've read in have been modified.

The solution proposed in the blog post, and by Jacob H, is to use a more restrictive cursor type (such as READ_ONLY) in order to avoid these extra key lookups entirely.

Optimize the index

  You can improve part of the Key Lookup to support the predicate by adding Claim_Unique_No to the nonclustered index as a key column. – sp_BlitzErik 23 hours ago

The query is currently using the clustered index to do the key lookup.

This comment points out that you could add Claim_Unique_No as a key column in your existing nonclustered index (the one being used in the scan: IDX_Claim_Audit...) to support the predicate (it looks like it already supports the output list that you want).


Some slightly shorter (and more natural, IMHO) approaches:

EXEC LinkedServer.[master].sys.sp_executesql N'SELECT @@VERSION;';

Or:

EXEC('SELECT @@VERSION;') AT LinkedServer;

I prefer the sp_executesql route because:

It will still allow for strongly-typed parameters, instead of dealing with all kinds of string concatenation, escaping of single quotes, etc.
It is easy to specify a particular database in that path, instead of having to put database prefixes on all the objects referenced in the query. And yes, you can define the database dynamically:

DECLARE @db SYSNAME, @exec NVARCHAR(1024);
SET @db = N'tempdb';
SET @exec = N'LinkedServer.' + QUOTENAME(@db) + N'.sys.sp_executesql';
EXEC @exec N'SELECT @@VERSION, DB_NAME();';

And the linked server name, too, if need be:

DECLARE @srv SYSNAME, @db SYSNAME, @exec NVARCHAR(1024);
SET @srv = N'LinkedServer';
SET @db = N'tempdb';
SET @exec = QUOTENAME(@srv) + N'.' + QUOTENAME(@db) + N'.sys.sp_executesql';
EXEC @exec N'SELECT @@VERSION, DB_NAME();';


How to use document age as weigt in ts_rank

I don't even get how this code is supposed to work

You have

(EXTRACT(epoch FROM (SELECT (NOW() - INTERVAL '10 days')))/3600)::int

All that's doing is resolving to a static int. How is dividing a result set by a static int significant.

SELECT ts_rank(to_tsvector('example of document'), to_tsquery('document'))
  / 420953;


Unable to connect SQL Managed instance in the vnet

For POC, you can also try the public endpoint which is now available and can be configured through PowerShell. https://techcommunity.microsoft.com/t5/Azure-SQL-Database/Public-endpoint-for-SQL-Database-Managed-Instance-is-available/ba-p/438996


If you want shorter syntax in SQL, there is the TABLE name syntax that you can use - where name is a table name (base table or view or cte):

SELECT * FROM ((TABLE A) UNION (TABLE B)) t ;

The parentheses are not needed:

SELECT * FROM (TABLE A UNION TABLE B) t ;

and you could even write:

TABLE A UNION TABLE B ;

Regarding whether the syntax is ISO/ANSI SQL, I found this in a (copy of a) draft of SQL 2011:

6.41 <multiset value constructor>

Function

Specify construction of a multiset.

Format

<multiset value constructor> ::=
    <multiset value constructor by enumeration>
    | <multiset value constructor by query>
    | <table value constructor by query>

<multiset value constructor by enumeration> ::=
    MULTISET <left bracket or trigraph> <multiset element list> <right bracket or trigraph>

<multiset element list> ::=
    <multiset element> [ { <comma> <multiset element> }... ]

<multiset element> ::=
    <value expression>

<multiset value constructor by query> ::=
    MULTISET <table subquery>

<table value constructor by query> ::=
    TABLE <table subquery>


Why is sp_Blitz executing for more than 5 hours

In your screenshot, you're running sp_WhoIsActive. You can click on the query text in blue:

And that'll tell you which query is running slow, and might give you some insight into why. You can also read the documentation, which mentions to try @CheckUserDatabaseObjects = 0 as a parameter to go faster.


SSIS packages running forever without completing. OLEDB Source query suspended

Based upon your description my guess is that the rest of the SSIS data flow is the problem in that it can’t process rows as fast as the source query sends them. Thus SSIS applies backpressure so that you don’t get a huge amount of rows in memory in SSIS. This backpressure causes the SQL query to be suspended. Can you describe the full data flow and all the transforms/components/destinations?

I would recommend installing the free BI Developer Extensions and running the Pipeline Component Performance Breakdown feature to confirm which component is your bottleneck.


SQL Server --> Informix DB Linked Server

We had a nightmare setting something similar up, specifically with the issue of connecting to multiple Informix environments. I had several Informix instances that needed to be linked to a single SQL Server instance for warehousing purposes, and was told by our resident SME that this was impossible; only a single Informix instance could be referenced per server.

I would consistently receive the same errors as you, and nothing I was able to dredge up online proved helpful.

Ultimately, the solution had several parts, of which you've done the first few:

The correct IBM Informix ODBC Driver must be installed on the database server.
Use the SetNet32 application provided with the drivers and set up the Server Information tab appropriately. Additionally, set up a corresponding System DSN entry.
The trick with the Linked Server setup is to use the following (items in codeblocks are literal):

Provider: Microsoft OLE DB Provider for ODBC Drivers
Product Name: Ifxoledbc
Data Source: $YourSystemDSNName
Provider String

Driver = {IBM INFORMIX ODBC DRIVER}
Database = $YourDatabaseName
etc...

If you're more of a manual SQL kind of person, see below. The provider string is pulled into a variable for inline commenting purposes only, while still having executable code. Modify as needed:

DECLARE @provider NVARCHAR(4000);
SET @provider = N'Driver={IBM INFORMIX ODBC DRIVER};'
  + N'DATABASE= ;'  --Informix DB name
  + N'HOST= ;'      --Informix Hostname or IP adddress
  + N'SERVICE= ;'   --Informix service name, ex.  ifx1_tcp1
  + N'PROTOCOL= ;'  --Informix protocol, ex.  onsoctcp
  + N'SERVER= ;'    --Informix server, ex. ifx1
  + N'DB_LOCALE=en_US.819; CLIENT_LOCALE=en_US.819;'; --Change to your locale, as needed

EXEC master.dbo.sp_addlinkedserver
@server = N'UCCX', --Linked Server system name
@srvproduct=N'Ifxoledbc',
@provider=N'MSDASQL',
@datasrc=N' ', --Your System DSN Name
@provstr= @provider;

Since you're dealing with a single instance, it should be enough to simply match the values used to create your System DSN. If you need to add additional linked servers for other instances, that can be done following exactly the same format described above without additional DSN entries.

I hope that helps.


You could also write

SELECT Fname, Lname, Address
FROM   department
JOIN   employee
       ON employee.dno = department.dnumber
WHERE  department.Dname = 'Research';

which is equivalent to query two and can be a clearer form (see https://stackoverflow.com/questions/1599050/ansi-vs-non-ansi-sql-join-syntax for an answer that describes why the newer syntax is preferred). Most query optimisers will see this equivalence and perform the same operations for such a simple query, though for more complex queries this may not be the case (if there is a difference the JOIN variant is likely to be the better of the two).

Not that this is almost equivalent to the inner part of your first query, join syntax you have used is the older but equivalent one. As you are doing that in the inner query then just adding an extra filter in the outer one, a good query planner will see these queries as identical and run them exactly the same way. A bad query planner will have the engine run the inner query first then apply the extra filtering clause making it much less efficient depending on what indexes are present for it to use.

But this all depends on a few things that you should edit your question and tags to provide:

What database you are using as different query planners may not optimise the same way for a given query (add the database and version to the tags).
Also most database engines provide a way to read what the query planner is likely to do for a given query which case be useful in judging which option is most optimal - I'll not list how to do that in every database I know though, be specific about the database(s) you are working with and we can be more specific & relevant about the answers we give.
The indexes present on the relevant tables (add the index and key definitions to the question text)

Note that if you run the second query and there are two rows in department where name='Research' then you are likely to get an error as the = operator can only work on one value each side. To list people in all departments with that name use IN instead. This may change the answer to which is most efficient.


When you execute:

select total
from (SELECT regexp_substr('Espacio Asignado:358898204672: Espacio Usado:355976458989: Espacio Reclamable:2921745683:','[^:]+', 1, level) total
      FROM DUAL
      connect by regexp_substr('Espacio Asignado:358898204672: Espacio Usado:355976458989: Espacio Reclamable:2921745683:','[^:]+', 1, level) is not null)
where   REGEXP_LIKE(total, '^[[:digit:]]+$')
/

You get all the numeric values in the string.


Really poor performance when using subquery to get one value

CREATE UNIQUE INDEX sensor_values_sensor_id_timestamp_index
  ON sensor_values (sensor_id, ts);
CREATE INDEX sensor_values_sensor_id_timestamp_value_index
  ON sensor_values (sensor_id, ts, value);

You can do instead:
CREATE UNIQUE INDEX sensor_values_sensor_id_timestamp_index
  ON sensor_values (sensor_id, ts) INCLUDE (value);

This will create your unique constraint index, and store "value" in it too, since I assume you want to use that as a covering index. Drop the second index, which is now useless. You can use the reclaimed disk space to create an index on (ts) which will help your query for the last day's records.

Am I right in thinking I need a partial index for the datetime column ?

You can't create a partial index with a variable where clause like "current day".
But you can create it only on the last partition only.


Calculate difference between SUM over last 30 days and the SUM for 30 days preceding this period (days 30-60) in Postgres

Especially the CTEs might be a problem. So it needs to be rewritten anyway. And while at it, I'd rewrite this to use conditional aggregation. That should get the data in "one run", without two temporary results and a join on them.

SELECT metric_data.company_id,
       sum(CASE
             WHEN metric_data.recorded_at > current_date - INTERVAL '30 day' THEN
               1
             ELSE
               0
           END) current_jobs_posted,
       sum(CASE
             WHEN metric_data.recorded_at > current_date - INTERVAL '30 day' THEN
               1
             ELSE
               -1
           END) delta
       FROM metric_data
       WHERE metric_data.recorded_at >= current_date - INTERVAL '60 day'
       GROUP BY metric_data.company_id;

(You might want to check the comparison operators (>=, >, etc.), if I picked the ones you wanted.)

An index on recorded_at and company_id should support it.

CREATE INDEX metric_data_recorded_at_company_id
             ON metric_data(recorded_at,
                            company_id);

Like that the entire query should be answerable from the index alone, without using the table at all. But maybe you also want to try it with an index just on recorded_at alone.


How to tell if a project needs a NoSQL database solution?

How to tell if a project needs NoSQL database solution?

  I think that SQL limits define it, if project needs bigger database or bigger fields than it needs NoSQL.

  Am I right?

You are not right ;)

Choosing NoSQL over a relational database has a lot more to do with business reasons like:

do we have people on the team already familiar with NoSQL database?
will it be cheaper (in terms of hardware, licensing, support, etc)?
will it be faster (because of tooling, or the schema-less nature of document databases, etc)?

It also has a lot to do with the types of data you're storing, and your data modeling requirements.  For instance, if your application needs to store large amounts of XML or JSON data, or if every entity you save might be slightly different, you might be better off with NoSQL. But if you need to model relationships and enforce constraints, referential integrity, etc, then you would likely be better off with a relational DB.


You missed CREATE USER command after creating the login:

USE ciq;
CREATE USER [usr_saga_ciq_loader_dev] FOR LOGIN [usr_saga_ciq_loader_dev];


Application considerations when using a multi-primary innodb cluster

The short answer is no, unfortunately. Have you considered using Galera instead? It has mature synchronous replication and can virtually guarantee consistency across nodes. I would highly recommend it. This article compares what you are using now with the Galera option: https://www.percona.com/blog/2017/02/24/battle-for-synchronous-replication-in-mysql-galera-vs-group-replication/

Otherwise you would have to develop an ugly solution like you describe, and it still won't be 100% adequate in the event of a node failure.


Import Tables without Constraints from Schema Export

You are right when stating I thought that by using INCLUDE=TABLE only the tables would be included, but it includes all related objects too. This means that constraints and triggers will be created for you when you use INCLUDE=TABLE when importing.

Possible Solution
If you have the possibility to re-create the dump using the expdp command then you might want to just export the tables of the required schema and use the INCLUDE parameter.

INCLUDE
Purpose:
Enables you to filter the metadata that is exported by specifying objects and object types for the current export mode. The specified objects and all their dependent objects are exported. Grants on these objects are also exported.

exp_include.par
This is basically the expdp parameter file.
FULL=N
...
SCHEMAS=<your_schema(s)>
INCLUDE=TABLE
...

This will create a dump of the required schema and contain only tables and dependant objects.

After you have generated the required *.dmp file, import using the EXCLUDE=... option.

EXCLUDE
Purpose:
Enables you to filter the metadata that is imported by specifying objects and object types to exclude from the import job.

imp_exclude.par
This is the parameter file for the impdp.
FULL=Y
...
EXCLUDE=STATISTIC
EXCLUDE=CONSTRAINT
EXCLUDE=TRIGGER
...

This should result in the tables being imported correctly.
Reference Material

2 Data Pump Export (Oracle Docs)
3 Data Pump Import (Oracle Docs)


SQL SERVER: Header Blocker Across Multiple Databases with wait type CXCONSUMER

On further investigation I believe the update itself might be causing the locking. It is on a large table 500+ million rows. As I understand it the table will become locked as soon as SQL starts to find the join. Perhaps the way activity monitor displays the locking is a little misleading though


Create an aggregate using the range_merge(anyrange, anyrange) function.

CREATE AGGREGATE range_merge(anyrange)
(
  sfunc = range_merge,
  stype = anyrange
);

SELECT range_merge(r) FROM test_ranges;
=> '[4,45)'


What is search space when it comes to query optimization?

This search space is part of mathematical optimisation.
From Wikipedia's article on Feasible Region
(emphasis mine)

In mathematical optimization, a feasible region, feasible set, search space, or solution space is the set of all possible points (sets of values of the choice variables) of an optimization problem that satisfy the problem's constraints, potentially including inequalities, equalities, and integer constraints.1 This is the initial set of candidate solutions to the problem, before the set of candidates has been narrowed down.

From Wikipedia's article on Search Algorithm
(emphasis mine)

Classes
[...]
For virtual search spaces
[...]
This class also includes various tree search algorithms, that view the elements as vertices of a tree, and traverse that tree in some special order. Examples of the latter include the exhaustive methods such as depth-first search and breadth-first search, as well as various heuristic-based search tree pruning methods such as backtracking and branch and bound. Unlike general metaheuristics, which at best work only in a probabilistic sense, many of these tree-search methods are guaranteed to find the exact or optimal solution, if given enough time. This is called "completeness".

I'm only including this answer, because it wouldn't fit in a comment and it's a Community wiki entry which can be improved upon by the community.


PostgreSQL: changing password for a user is not working

Like willglynn said, it's probably your pg_hba.conf file.

If you have the following line:

local   all    all     peer

then change it to:

local   all    all     md5

That should then let you login with your new password (assuming that you correctly supply it) :)


Why is my DR AG database in the "recovery pending" state?

You cannot put one database into multiple availability groups. The likely cause of the recovery pending state is related to your cluster configuration. If you only have two nodes and no file share or disk witness then your cluster can sustain 0 node failures.

If you're testing by shutting down node 1 and you don't have a quorum witness (file share or disk) then node 2's cluster service will stop and the AG will not bring the databases online.

Run a cluster validation to confirm how many node failures the cluster can sustain (needs to be at least 1) and validate your quorum configuration.


How to deny access to SQL Server to certain login over SSMS, but allow over .Net SqlClient Data Provider

I think there is no reliable solution for your problem since Application Name is modifiable parameter that cam be changed by any user.

Here is how to change it within SSMS:

In Connect to Database Object dialog choose Options, open  Additional Connection Parameters and choose any name for Application Name like this:

Now sys.dm_exec_sessions DMV and Program_name() will show you what you passed in your connection string in Application Name parameter:


Oracle SQL Summing Values From Two Different Tables

You can do this using an UPDATE:

UPDATE inventory i
   SET quantity = ( SELECT s.quantity+i.quantity
                     FROM shipment s
                    WHERE s.itemname = i.itemname );

or a MERGE:

MERGE INTO inventory
USING
(
  SELECT itemname, quantity
  FROM shipment
) ship ON (ship.itemname = inventory.itemname)
WHEN MATCHED THEN UPDATE
    SET inventory.quantity = ship.quantity + inventory.quantity;

Example SQL Fiddle here if you want to play.


Oracle Package in Data Dictionary that can't be dropped

I am suspecting with the case sensitive name or special character(i.e. ' or white-space) included with the package name unknowingly while creating package. I tried to get object name from dictionary itself which ensures correct object name in your drop command in the following script, it can help in your context:

select
'DROP PACKAGE ' || '"' || OBJECT_NAME || '";' as "Command"
from
USER_OBJECTS
WHERE
OBJECT_NAME LIKE '%POSTING_PROCS%'
AND OBJECT_TYPE='PACKAGE';

CAUTION - REMEMBER THAT ABOVE SCRIPT GENERATES DROP COMMANDS WHICH CAN CONTAINS POSTING_PROCS AS THE PART OF OTHER PACKAGE NAME, PLEASE CHECK EACH DROP STATEMENTS BEFORE EXECUTING THEM IN YOUR SERVER.

Alternatively - You might be using client tool like TOAD, SQL Tool, SQL Developer which display your invalid object name in a GUI format. You can try to delete your object by right click on the object and follow with the drop option.


Efficiently Filter Large Set With Disjunctions

If this particular work load is the majority of queries against the table you might consider:

ALTER TABLE Ticket ADD CONSTRAINT PK_Ticket PRIMARY KEY NONCLUSTERED (TicketId);

CREATE UNIQUE CLUSTERED INDEX IX_Ticket_Covering ON Ticket (
    InsertDateTime ASC
);

Considerations:

can you use datetime2 (SQL 2008+; flexible precision)
will InsertDateTime be unique within your precision
if the times are not constrained unique sql will add a hidden uniquifier column of type int. This is added to all nonclutred index so they can reference the correct clustered record

Advantages:

Adds new rows to the end of the table
prevent writing the optional filter columns twice (once in the clustered, and once on the index leaf for the include)
majority of you time will still be on a cluster index seek with more or less filers.
then add other nonclustered index for most popular column pairs


Exchange rate table and conversion on the fly in query

How are your underlying item prices stored?

If, for example, your items are all priced/stored in EUR, then there is no need for an NxN (duplicative) currency matrix.

In this case, exchange_rate (against EUR or your base currency) can simply be a field in the currency table (vs an independent table).

Then all of your conversions for display (which are all then simply EUR->XXX) can be calculated with a single JOIN.


AlwaysOn Replicas set to manual but failing automatically

A reboot of a cluster node that has a resource group that's configured to automatically start forces the WSFC to start that resource on a surviving node, implementing HA feature of SQL Server AAG. This action also triggers SQL Server instance to promote an existing synchronous secondary replica to primary. I think you can avoid automatic failover by setting only one replica to be synchronous, however this will effectively make you lose data should you irreversibly lose the cluster node with the primary replica.


db_owner unable to drop database - Error 615, SQL Server

I can guess you have the AutoClose option for database set to True. This is the default behavior when you create a database with Express Editions.

The mentioned error can occur exactly in this case. Actually the complete error message 615 states: "Could not find database ID %d, name '%.*ls'. The database may be offline. Wait a few minutes and try again." ... So it points that database could be closed during dropping.

So, go to DB properties, switch it to False and try again dropping it or use below script before dropping

ALTER DATABASE [MyDB] SET AUTO_CLOSE OFF
GO

Many point out that is better to have AutoClose set to False. I found this article explaining a bit more about AutoClose: http://sqlmag.com/blog/worst-practice-allowing-autoclose-sql-server-databases

Small extension of the answer:

-- this works in standard SQL Server Editions, but NOT with Express Editions:
CREATE DATABASE [MyDB]
GO
DROP DATABASE [MyDB]
GO

-- this works in ALL SQL Server Editions
CREATE DATABASE [MyDB]
GO
ALTER DATABASE [MyDB] SET AUTO_CLOSE OFF
GO
DROP DATABASE [MyDB]
GO


Assign number incrementally, reset when value changes

SQL Server 2008R2 is pretty limited in its windowing functions, no lag/lead functionality, and ORDER BY can only be used with ROW_NUMBER().  So the usual techniques to solve this are not readily available.
I'm going to assume your primary key is (ScanCode,Dates), if it isn't this won't return consistent results.
SELECT
  curr.ScanCode
 ,curr.Dates
 ,curr.Flag
 ,prn = ROW_NUMBER() OVER (PARTITION BY curr.ScanCode, pr.Dates ORDER BY curr.Dates)
FROM
  @t curr
LEFT JOIN
  @t pr --Get maximum date prior to current date where Flag did not match
    ON pr.ScanCode = curr.ScanCode
        AND pr.Dates =
          (
            SELECT
              MAX(Dates)
            FROM
              @t
            WHERE
              ScanCode = curr.ScanCode
                AND Flag <> curr.Flag
                AND Dates < curr.Dates
          )

This works because the dates for a contiguous block of Flag will all have the same prior date value:
ScanCode    Dates       Flag    PriorDate
182086      2020-07-01  P       NULL
182086      2020-07-02  P       NULL
182086      2020-07-03  A       2020-07-02
182086      2020-07-04  A       2020-07-02
182086      2020-07-06  P       2020-07-04
182086      2020-07-07  P       2020-07-04
182086      2020-07-08  P       2020-07-04
182086      2020-07-09  P       2020-07-04
182086      2020-07-10  A       2020-07-09
182086      2020-07-11  A       2020-07-09
182086      2020-07-13  A       2020-07-09
182086      2020-07-14  A       2020-07-09

So if your clustered index is chosen properly, this is one table scan plus two seeks per row, and one sort for the PARTITION BY.  Not great, but even with LAG/LEAD you'd probably not create anything more efficient.
Other advice:

Dates isn't a good column name as the column stores a single value, not plural.
Additionally, it's not very descriptive of what the column represents - is this a date of a certain event?  Entry date?  If you can rename it, do.  Also, if the values will always be dates, use the DATE datatype.
The primary key in this instance really should be (ScanCode,Dates) if it isn't already.  Not only is this necessary to guarantee consistent results, but the join to get the prior value will be a seek on the clustered index.  To test, declare a primary key on your table variable and compare the execution plans with and without.


What is an effective way to wipe sensitive data on a data pages after dropping a table's column?

No, sorry, there is no feature for reinitializing empty pages or extents when they are unused.

It is true that if you take a backup and create a new database from that backup the empty extents are not backed up, so that data could not be restored to the new database. But that still leaves some old data in the extents that are still in use. See:

'Cleanse' a SQL Server database file created with Instant File Initialization enabled?

I suppose that overwriting the data first, then deleting it, might help.  But that may not be practical for many reasons. (Too much data to update, constraints, unique indexes, triggers, etc.)


What are the approaches for refreshing materialized views in Oracle, when underlying tables are updated often?

If you can, use fast refresh instead of complete refresh - it will save you a lot of compute time. you'll have to create materialized view logs on the source tables, but if you run the refreshes frequently you will keep them small and only have to deal with the incremental updates. If you're using 12.2 you can also use "real-time" materialized views, as illustrated here: https://www.youtube.com/watch?v=k5nhZfyPHdY


mysql “Lock wait timeout exceeded” two transaction with different keys indexed

If the values for the 'id' are adjacent, like '1' and '2' in your example, including the case where they are not adjacent but there are no rows in the index with values in between them, then I would suggest that you are bumping into the index gap lock... which is part of "row" locking.

InnoDB performs row-level locking in such a way that when it searches or scans a table index, it sets shared or exclusive locks on the index records it encounters. Thus, the row-level locks are actually index-record locks. In addition, a next-key lock on an index record also affects the “gap” before that index record. That is, a next-key lock is an index-record lock plus a gap lock on the gap preceding the index record. If one session has a shared or exclusive lock on record R in an index, another session cannot insert a new index record in the gap immediately before R in the index order.
— http://dev.mysql.com/doc/refman/5.6/en/innodb-record-level-locks.html

As that page suggests, a transaction isolation level of READ COMMITTED might be helpful, if that's not what you're using and if the gap locks are the issue.
It also might be contention for the primary key on the inserts.
The INNODB_LOCKS and INNODB_LOCK_WAITS tables in information_schema could provide additional diagnostic information, as might SHOW ENGINE INNODB STATUS; though it can be cryptic.


Are "column names" optimized in PostgreSQL JSONB jsonb_agg(*)?

It is not optimized.  Each column name, as a JSON key, is sent in full, multiple times.  You could try connecting over SSL and turning on compression, but for security reasons that is disabled by default in most systems, and is getting increasingly difficult to turn on.

  If the column names are really reapeated once per row, we would spend more bandwith for them, then for the values.

If that is the highest cost you have to pay for gratuitous use of JSON, then consider yourself lucky.


Controlled shutdown/startup of a cluster

Shutting down the cluster for a move is pretty straight forward.

Open the failover cluster administrator.
Using the failover cluster administrator stop the resource groups for the SQL instances
Shut down two of the nodes.
At this point the cluster will fail and the cluster service will stop.
Shutdown the final node

To bring things back online.

Startup all the servers.
Start the cluster service if it didn't start automatically
Verify that the servers can see the storage correctly.
Startup the resource groups that host the SQL instances from within the Failover cluster administrator assigning them to the correct node of the cluster as you start them.

A word of advice, make sure that EVERY cable is properly labeled at both ends before you unplug anything. I've done this kind of move a few times.  A couple of hours of labeling the cables will save you hours of pain when getting things back up and running.


How to trigger a function with DDL query?

DDL statements terminate transactions.  So your attempt with START TRANSACTION; ... COMMIT; does not work.  (MySQL 8.0 may change that.)

If you are just checking for the existence of a PRIMARY KEY, suggest this (in a Stored Procedure) (and with a pre-created DATABASE test_area):

USE test_area;
CREATE TABLE ...  -- probably need prepare+execute+deallocate_prepare
abort if errors
IF ( ( SELECT ... FROM information_schema ...
    WHERE table_schema = 'test_area' ) )
THEN
    issue error message
END IF;
DROP TABLE ...;  -- probably need prepare+execute+deallocate_prepare


Generate GRANT for all tables reference by a procedure

The error message is correct. You can't say LIKE against the output of a query unless you are somehow explicitly constraining that output to one row (and this is not what you want to do). Just perform a join... you could weed out duplicates but there's no reason to (granting to the same table twice, if possible, does not hurt anything). Also please use QUOTENAME() instead of manually adding [ and ] yourself.

DECLARE @sql nvarchar(max) = N'';

SELECT @sql += CHAR(13) + CHAR(10) + N'GRANT SELECT ON '
  + QUOTENAME(s.name) + N'.' + QUOTENAME(t.name) + N'] TO [User1];'
FROM sys.tables AS t
INNER JOIN sys.schemas AS s
ON t.schema_id = s.schema_id
INNER JOIN sys.sql_dependencies AS d
ON d.referenced_major_id = t.object_id
INNER JOIN sys.procedures AS p
ON p.[object_id] = d.[object_id]
WHERE p.name = N'myproc';

PRINT @sql;
-- EXEC sys.sp_executesql @sql;

Keep in mind that sys.sql_dependencies will not necessarily capture everything, for example if the stored procedure uses dynamic SQL, it can't parse the text to find all table references, or explore all possible permutations.


Not an answer to your exact question, but instead of translating the SQL to DAX and maintaining it in two places, you can reuse the SQL logic in your RLS implementation to materialize tables in your tabular model with the allowed (Username,BrandId) pairs, and the allowed (Username,ChannelId) pairs enumerated.

So write a view and load a table in your tabular model with the results of a query like this:

select s.BrandId,c.ChannelId,u.StaffUserName
from dbo.Brands b
cross join dbo.Channels c
cross join dbo.Staff s
cross apply   [Security].[fn_securitypredicate](s.BrandId,c.ChannelId,s.StaffUserName) sp
where sp.fn_securitypredicate = 1

Which will enumerate all the permissible combinations of (BrandId, ChannelId, StaffUserName).  Then use that new table in your DAX row filters by setting up bidirectional cross filtering between this table and Brand and this table and Channel.

David


Build a histogram of road speed based on road length by grouping road segments

Maybe the following ideas will give you a starting point for developing your queries.  We need some test data first.  The last SEGID in your question is: 4007.  In the example, we are using a table with 4500 random SEGLENGTHs, and IDs, maxspeeds etc similar to the ones you have described (in your sample table).

TEST table and data (Oracle 12c)

create table test (
  segid number generated always as identity primary key
, roadid number
, roadtype varchar2( 64 )
, seglength number ( 10, 1 )
, maxspeed number
, check ( maxspeed between 0 and 100 )
);

insert into test( roadid, roadtype, seglength, maxspeed )
select
  roadid
, case
    when roadid < 200 then 'Ave'
    when roadid between 200 and 400 then 'Hwy'
    when roadid between 401 and 700 then 'St'
    when roadid between 701 and 900 then 'Rd'
  end roadtype
, round( dbms_random.value() * 10, 2 )
, case
    when roadid < 200 then ( mod( roadid, 4 ) + 1 ) * 10
    when roadid between 200 and 400 then ( mod( roadid, 7 ) + 1 ) * 10
    when roadid between 401 and 700 then ( mod( roadid, 3 ) + 1 ) * 10
    when roadid between 701 and 900 then ( mod( roadid, 6 ) + 1 ) * 10
  end maxspeed
from
  ( select level roadid from dual connect by level <= 900 )
, ( select level segcount from dual connect by level <= 5 )
;

TEST table: first 5 rows, last 5 rows

SQL> select * from test order by segid fetch first 5 rows only;
SEGID  ROADID  ROADTYPE  SEGLENGTH  MAXSPEED
1      1       Ave       1.6        20
2      1       Ave       7.5        20
3      1       Ave       3.8        20
4      1       Ave       9.2        20
5      1       Ave       2.8        20

SQL> select * from test order by segid offset 4495 rows fetch next 5 rows only;
SEGID  ROADID  ROADTYPE  SEGLENGTH  MAXSPEED
4496   900     Rd        4.2        10
4497   900     Rd        4.2        10
4498   900     Rd        0.7        10
4499   900     Rd        7.1        10
4500   900     Rd        2          10

For dividing the SEGLENGTH into "buckets", you could use WIDTH_BUCKET() (see documentation), and use GROUP BY to find the average speed for each "bucket".

  select
    roadtype, avg( maxspeed ), max( seglength ), wb
  from
  (
      select roadtype, maxspeed, seglength
      , width_bucket(
          seglength
        , ( select min( seglength ) from test )
        , ( select max( seglength ) from test ) + 1  -- upper bucket count: exclusive!
        , 5
       ) wb
      from test
    )
  group by wb, roadtype

-- result
ROADTYPE  AVG(MAXSPEED)                              MAX(SEGLENGTH)  WB
Hwy       39.24170616113744075829383886255924170616  8.7             4
Hwy       40.70866141732283464566929133858267716535  10              5
St        18.91304347826086956521739130434782608696  8.7             4
St        19.81132075471698113207547169811320754717  10              5
Rd        34.70588235294117647058823529411764705882  6.5             3
Ave       24.49275362318840579710144927536231884058  10              5
Hwy       41.76744186046511627906976744186046511628  6.5             3
Ave       25.52995391705069124423963133640552995392  2.1             1
Ave       24.0796019900497512437810945273631840796   8.7             4
Ave       25.27272727272727272727272727272727272727  4.3             2
St        20.50632911392405063291139240506329113924  2.1             1
Rd        34.51754385964912280701754385964912280702  8.7             4
Rd        35.11811023622047244094488188976377952756  10              5
Hwy       38.68312757201646090534979423868312757202  4.3             2
Rd        35.26315789473684210526315789473684210526  2.1             1
Rd        35.52995391705069124423963133640552995392  4.3             2
Ave       25.70776255707762557077625570776255707763  6.5             3
Hwy       40.28708133971291866028708133971291866029  2.1             1
St        21.03658536585365853658536585365853658537  4.3             2
St        19.65838509316770186335403726708074534161  6.5             3

Add a PIVOT() to this query, in order to convert the ROADTYPES into columns, and use ROUND() or TRUNC() for obtaining the final values.

select msl, ave, hwy, st, rd
from (
  select
    roadtype, trunc( avg( maxspeed ) ) ams, max( seglength ) msl, wb
  from
  (
    select roadtype, maxspeed, seglength
    , width_bucket(
        seglength
      , ( select min( seglength ) from test )
      , ( select max( seglength ) from test ) + 1  -- upper bucket count: exclusive!
      , 5
     ) wb
    from test
  )
  group by wb, roadtype
) pivot  (
    avg( ams ) for roadtype in (  -- pivot() requires an aggregate function
      'Ave' as ave
    , 'Hwy' as hwy
    , 'St'  as st
    , 'Rd'  as rd
   )
) P
order by wb
;

-- result
       MSL        AVE        HWY         ST         RD
---------- ---------- ---------- ---------- ----------
       2.1         25         40         20         35
       4.3         25         38         21         35
       6.5         25         41         19         34
       8.7         24         39         18         34
        10         24         40         19         35

For getting the standard deviation values, just use STDDEV() instead of AVG().


Try the dblink. You find the description in http://www.postgresql.org/docs/current/static/contrib-dblink-function.html and http://www.postgresonline.com/journal/archives/44-Using-DbLink-to-access-other-PostgreSQL-Databases-and-Servers.html. Also a question about dblink was asked on https://stackoverflow.com/questions/3862648/how-to-use-install-dblink-in-postgresql.


Maybe you could try something like :

declare @file varchar(50)
declare @DBName varchar(50)
declare @sql varchar(200)

CREATE TABLE #tempFileTable (FName VARCHAR(8000),Depth INTEGER, Files INTEGER)

INSERT INTO #tempFileTable
EXEC xp_dirtree 'D:\', 1, 1

select @file = FName
from #tempFileTable

set @DBName = 'MyDB'

set @sql = 'RESTORE DATABASE '+@DBName+' FROM  DISK = ''D:\'+@file+''' WITH  FILE = 1'

print @sql


Do I Alter user quota in USERS tablespace?

Yes, users should not write to the system or sysaux tablespaces.  If the user is expected to have data or objects and will be copied to development instances they should have their own tablespace as it makes moving them easier.

Change your file path to what you have and execute this as a privileged user

create tablespace sales_tbs datafile '/home/oracle/databases/ora11/sales.dbf'
 size 10M autoextend on maxsize 200M extent management
 local uniform size 64K;
alter user SALES quota 50m on sales_tbs;

and to make sure all future objects go to the dedicated tablespace for sales

 alter user sales default tablespace sales_tbs temporary tablespace temp;


Use crosscheck copy before deleting:

RMAN>crosscheck copy
RMAN>delete noprompt obsolete;

Crosscheck on it's own (without copy) doesn't do anything.  You could also have done:

crosscheck backuppiece papcfat0_1_1


Would comparison of strings to a postgres 'text' type column for equality be a performance bottleneck?

Yes, text is (roughly) equivalent to varchar(max)

Comparing text values is not less efficient than comparing varchar values in Postgres as under the hood they are absolutely identical. So the efficiency of the comparison is related to the length of the values. I don't expect that to be any slower than your current implementation using varchar(max) in SQL Server.

If you want to enforce uniqueness on a column, create a unique index on it. Then you can use insert ... on conflict do nothing to efficiently insert new values and at the same time validating that they are unique.

However: there is a technical limit on how long an index entry is allowed to be which is roughly 2700 byte. You didn't mention how long your values are, but the index might not work for you then.

Consider also this answer https://dba.stackexchange.com/a/69164


Not sure what you mean by:

  the join would force the clustered key to be matched.

Non-clustered indexes include the clustered index key (needed as the unique row locator).  Consequently, a non-clustered index can cover a query as long as the query references only columns in the clustered index key, non-clustered index key columns, and included columns.

A potentially covering non-clustered index might not be used if other indexes are available to process the query more efficiently depending on the columns specified in WHERE, JOIN, or ORDER BY clauses.


How do I join two tables on two different instances of DB2?

The "federation" feature in DB2 will allow you to do what you need.

It's available on the Linux/Unix/Windows version of DB2 - not sure about the mainframe - and there are no licensing issues, it's part of the base product set.

To implement it you only need to create a few extra db objects (wrapper, server mappings, nickname) and you're more or less done.  Follow the documentation and you should be fine.

Cheers.


Delete duplicates from a table with all the columns duplicated

If you can, add an UNIQUE INDEX (or PRIMARY KEY) for [ID].

And you could change:

DELETE FROM [dbo].[monthly_prd04_ftp_list]
GO

by

TRUNCATE TABLE [dbo].[monthly_prd04_ftp_list]
GO

It's faster and it will reseed the identity column to 1 (or it initial value.)

If you don't want to use a temporary table, and there isn't FOREIGN KEYS pointing to this table, you could rename it and insert the DISTINCT values to the final table directly. Then you could DROP the old table.


Joining INTO many-to-many table (From Primary Key Table)

http://sqlfiddle.com/#!9/33b19d/3 ...

as I said in the comments - use left join instead of inner join ...

your diagram misses the mentor_types ... so I commented it out.

of course you will get 2 lines for user 1886

For your question regarding all lines of user_mentors - no its impossible if you start building the select with the users table. And as there is no mentoruser_id that points to other lines than the id==4 line in users_mentors.


You are treating it as if block reads are a subset of block hits.  That isn't correct, the two are mutually exclusive.  To get a real hit ratio, you would have to compute as  sum(hits)/sum(hits+reads). But just looking at hit ratios is pretty useless anyway, in general but especially in PostgreSQL which doesn't use DirectIO but rather sits on top of the OS's file cache.  Hit ratios are part of mythology-based administration.

For all we know, this table has only had one or two seq scan done on it (for example, the ones which were done in order to create the index in the first place) and that event is now dominating your statistics.  You could reset the statistics (or better yet just stop looking at them) to get rid of one-time events.

The columns seq_scan, seq_tup_read, idx_scan, idx_tup_fetch are occasionally (but rarely) useful in figuring out the source of a problem when a problem is known to exist. They are entirely useless when there is no problem to diagnose in the first place.

You should instead spend your time doing useful things, like setting up a slow query log ("log_min_duration_statement" or better yet auto_explain, and pg_stat_statements), setting up test/dev/QA environments, or running disaster recovery drills so you know know that your backups are working and that you know how to use them.

My favorite settings of auto_explain for new projects are:

shared_preload_libraries = 'auto_explain'
track_io_timing=on
auto_explain.log_min_duration = '50ms'
auto_explain.log_analyze=on
auto_explain.log_timing=off
auto_explain.log_buffers=on

And then increase log_min_duration if you find it is catching too many queries.

Having log_analyze=on while also having log_timing=on imposes a pretty high overhead on all your queries; so setting log_timing=off is a compromise to get as much detail as possible but with minimal overhead.


Data Lake : Data Catalog and Data Schema definitions

This has a regular structure ("schema"):

id  name   color  cost
1   dress  green    40
2   camera red     200
3   car    black 34000

Note the rigid "rows" and "columns".  The columns have specific datatypes" (string, number, etc).  Etc.

The following has some structure, but is also very open-ended and "self-defining".

{name: "dress"; size: "large"},
{name: "camera"; pixels: "12M"; zoom: "4x"},
{name: "car"; style: "sedan"},
{name: "family"; members: ["father", "mother", "children"]}

The former example is the key design of all RDBMSs.  It is the primary focus of MySQL, Postgres, etc.  It is designed and implemented for speed and scaling.  The "catalog" and "schema" define the structure separate from the data.

The latter example is less structured, less performant, but more flexible.  Perhaps this is the "data lake" you are asking about.

I suggest you focus on RDBMS and don't confuse yourself with "other" ideas.


Something is eating your Ram, possibly the mysql recover. Add more swap (dd if=/dev/zero count=1000 bs=1M of=swapfile; mkswap swapfile; swapon swapfile)  go to singleuser mode (init 1) so that nothing else is running and start your mysql recover.
Watch with top or htop for memory usage.


It looks like MS has seen this already.  Have you tried running SSMS through
Right Click - Run As Admin?

http://connect.microsoft.com/SQLServer/feedback/details/198005/not-able-to-access-the-database-model-under-the-current-security-context-microsoft-sql-server-error-916

Also are you logging in as a sysadmin to SQL Server? Do you have create DB rights?

Edit:
Just saw your original post updated.  It looks like you are restoring to the wrong version for sure.  Consider yourself lucky if that is the issue!!

Do a SELECT @@SERVERNAME.  What does it return?  Is that the right instance?  Probably not (hopefully).  Try your restore script only after SELECT @@SERVERNAME returns the right info.  Also you might want to use OSQL where you have to state the server name and creds before connecting, that way you know you're at the right one instance.


I do this a lot with my Availability Group backups (using Ola's scripts). Since you have @CopyOnly = 'Y', and you have a Secondary server set as your preferred replica for backups, Ola's script will ignore this backup on your Primary and use the Secondary server for the FULL_COPY_ONLY backups (if the script is run there).

If you were to do away with the @CopyOnly = 'Y' parameter, the scripts would take a FULL backup on your Primary server (FULLs and DIFFs are not allowed on Secondaries).

Ola's scripts are smart when it comes to Availability Groups.  If you have a preferred secondary specified, his scripts will try to use it for any backups that don't HAVE to be done on the Primary.  Because of this, I wound up creating identical backup jobs across my Primary and Secondary servers, and each one runs whichever backups are appropriate based on the current status of each server.


SQL Server on linux unable to export to bacpac

According to the official documentation for SqlPackage on 2017 CTP 2.1, the command to export data and schema is as follows:

/opt/mssql/bin/sqlpackage /a:Export /ssn:tcp:<your_server> /sdn:<your_database> /su:<username> /sp:<password> /tf:</path/to/your/file.bacpac>

Looking at your command, it appears you're missing credentials, so I'd give this a try (I've shortened the other parameters as well, and removed the TCP port):

/opt/mssql/bin/sqlpackage /a:Export /ssn:tcp:localhost /sdn:MyDB /su:<user> /sp:<password> /tf:'/media/jake/1F171B4C75D06418/MyDB.bacpac'


Applying data changes from one database to another

One way to do this is to ensure that all tables of properties, or metadata, have a date_created and date_last_modified date.  Then you can filter where the date created is in a time period and export the changes as inserts or updates.

What I do is harder. All changes to development properties are saved as a script of inserts/updates and I use a project/bug tracker to keep track of all the collection of changes.  I refresh development weekly so you see right away if you forgot something like a sequence or a grant.  A typical change could involve data inserts, package updates, grants, sequences and triggers so you have to keep it organized.

Edit: Luke asks how do you keep changes as scripts?
I do two things:

all properties and types are added by a package of procedures and functions.  This checks that the input is valid, updates any logging/audit tables and validates the changes against any business logic you may have in the database.  This is a lot of work to set up but allows you to have a consistent practice for adding new metadata.  Each time you use a procedure or function you can save your call as an sql statement.
I use Toad and this has the ability to filter a table for almost any criteria and export the results as Inserts.


Oracle 11.2.0.3 Service Name Mismatch issue

SERVICE_NAME is made up of the values db_unique_name and db_domain parameters. Here is the reference to Oracle Documentation about SERVICE_NAME. Even if you set lab3 as service name oracle automatically adds value of db_domain on it. Your service_name is 'lab3' and db_domain is 'domain.com' so you end up with the orgianl service name.
Instead you can create tns service name to point that service. Example is given below

lab3 =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = domain.com)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = lab3.domain.com)
    )
  )

Then you can use this tnsname(lab3).


Set statistics time showing different elapsed_time than dm_exec_query_stats view

What version of SQL Server are you using?  This is/was a known issue and was fixed in SQL 2008 SP1 but wasn't ported back to SQL 2005.

http://connect.microsoft.com/SQLServer/feedback/details/487838/cpu-time-sys-dm-exec-query-stats-vs-set-statistics-time-on


Adding new key(s) to enable partitioning, INSERT performance?

Your one select needs this "compound" index.

INDEX(DeviceID, Date)

Do you have other queries?

But your question was about PARTITIONing.  No advantage, based on what you have said so far.


Yep, it's really strange why SEC_CASE_SENSITIVE_LOGON should not be used for this if it's especially designed for the purpose.
I can suggest in this case maybe just use some external authorization method like Oracle Identity Management, e.g.. It's much more complex and require additional infrastructure installed/configured and maintained, but users' credentials will be stored and controlled (including case-sensitive passwords) outside of the database and becomes independent on the database parameter value.


Manual failover error, not in correct state

Your secondary database needs to be in a "Restoring..." state.  Can you verify that?  When you restore the database on the secondary server, it needs to be with NORECOVERY to put it in this state.


PostgreSQL does not support = or LIKE on COLLATE. This is because internally index ordering uses = and so even if the collation returns that they're equal PostgreSQL falls back to binary equal. This is documented,

  Note that while this system allows creating collations that “ignore case” or “ignore accents” or similar (using the ks key), PostgreSQL does not at the moment allow such collations to act in a truly case- or accent-insensitive manner. Any strings that compare equal according to the collation but are not byte-wise equal will be sorted according to their byte values.

PostgreSQL also doesn't support Unicode collation in character classes.


Don't worry about extents and pages.

A Full backup contains all the data pages.  The pages are not written to the backup at a single point-in-time.  So a Full also contains the log records needed to redo/undo the changes from the earliest page to latest transaction committed during the backup.

A differential backup omits all pages not changed since the last Full backup.

A log backup contains all log records since the last log backup.


DELETE from Linked Server table using OPENQUERY and dynamic criteria

In my experience, DML statements over Linked Servers is sllooowww (I believe that is the proper, technical spelling of this particular type of slowness ;-).

I found that the following setup was mucho faster:

Create a stored procedure on the remote server:

Call it something like RemoteTable_DeleteByEntryIDList
It should accept an NVARCHAR(MAX) parameter called @EntryIDs
The format of the data for that parameter will be a list of EntryIDs in XML
The parameter cannot be of type XML as that is not valid for passing over a Linked Server
The proc will do something along the lines of:

CREATE TABLE #TempIDs ([EntryID] INT NOT NULL);

;WITH cte AS
(
    SELECT CONVERT(XML, @EntryIDs) AS [Data]
)
INSERT INTO #TempIDs ([EntryID])
  SELECT tmp.[EntryID]
  FROM   cte
  CROSS APPLY (SELECT tab.col.value('./EntryID[1]', 'INT') AS [EntryID]
               FROM   cte.[Data].nodes('/row') tab(col)
              ) tmp;

-- optional: test to see if it helps or hurts
-- ALTER TABLE #TempIDs
--   ADD CONSTRAINT [PK_#TempIDs]
--   PRIMARY KEY CLUSTERED (EntryID ASC)
--   WITH FILLFACTOR = 100;

DELETE rt
FROM   RemoteTable rt
INNER JOIN #TempIDs tmp
        ON tmp.[EntryID] = rt.[EntryID];

Update your local stored proc to do something like:

DECLARE @IDsToDelete NVARCHAR(MAX);

SET @IDsToDelete = (
  SELECT EntryID
  FROM   dbo.LocalTable
  FOR XML RAW
);

EXEC [MyLinkedServer].[DatbaseName].[SchemaName].[RemoteTable_DeleteByEntryIDList]
     @EntryIDs = @IDsToDelete;


Do we have any File Table / File Stream like support in any other database than MS SQL Server?

The alternative implementation in the past was to store the files on a shared location and store their location's reference in the database in a table.
Here are the current options for modern DBMS:

Oracle PL/SQL - Loading documents and other file data into the Oracle Database: Full Text or Binary Data with BFiles or External Tables might be closest to what your use case is.

MySQL: BLOB Storage: Seems like there's not much alternative in MySQL (aside from storing the reference to the file's location).

PostgreSQL: Storing Binary Data & Are there performance issues storing files in PostgreSQL?: Can use the bytea data type or the Large Object feature which stores the data in dedicated table.

MongoDB: BSON Types & GridFS: BSON data types can store data up to 16 MB in size, after that GridFS is the solution for larger storage (especially for binary data).

SQLite: Can we store .doc .pdf and .jpg files in SQLite Database (Android)?
 & Datatypes in SQLite Version 3: BLOBs are the go-to in SQLite.

MariaDB: LOAD_FILE: There doesn't seem to be a lot of support in MariaDB at all, but in general they also use BLOB storage have a LOAD_FILE function.

AWS: SimpleDB: While this isn't achievable via Docker, just for reference for anyone adamant about storing files in the database, their SimpleDB (among some of their other services) offer ways to somewhat store files in the database and use meta-data to tag what kind of file they are (typically leveraging S3 buckets).

Here's also an oldie but goodie on the pros and cons of storing large files in the database.


How can one optimize a query involving a sort and a LIKE?

Your index attempts are still futile, since the predicate

u."name" LIKE 'garr%'

is obviously the most selective one but cannot use a basic (default) b-tree index unless you are operating with the "C" locale (which is uncommon).

SELECT *
FROM   "Users"
WHERE  name LIKE 'garr%'
AND    id <> 2449214
AND    hellbanned IS NULL
AND    "hellbanPostsAfterDate" IS NULL
ORDER  BY "followerCount" DESC NULLS LAST
LIMIT  3;

There are several options (also depending on the missing table definition and other information), this index should work:

CREATE INDEX "Users_whatever_idx"
ON     "Users" (name text_pattern_ops, "followerCount" DESC NULLS LAST)
WHERE  hellbanned IS NULL
AND   "hellbanPostsAfterDate" IS NULL;

The conditions on hellbanned and "hellbanPostsAfterDate" are only useful if they exclude more than a few rows. Else, a complete index might be the better choice, since it can be used for more different queries.

Also, there is a chance the second column in the index "followerCount" won't help, since there are typically multiple different matches for the first and Postgres has to sort after all. Test a couple of typical queries with and without the second column in the index.

Details for indexes and pattern matching, including an explanation for the operator class text_pattern_ops:

Pattern matching with LIKE, SIMILAR TO or regular expressions in PostgreSQL

Aside: My standing advise is to use unquoted, legal, lower-case identifiers exclusively in Postgres.


MariaDB - cannot set max_connections and wait_timeout through my.cnf

wait_timeout is a screwball one:

"On thread startup, the session wait_timeout value is initialized from the global wait_timeout value or from the global interactive_timeout value, depending on the type of client (as defined by the CLIENT_INTERACTIVE connect option to mysql_real_connect()). See also interactive_timeout. "

max_connections claims to have an upper bound of 100000, but I would say that even 10000 is unreasonably large.  Does it work to put max_connections=1000 in my.cnf?

You understand that you need to, after changing my.cnf,

restart mysqld
disconnect and reconnect to see the change
use SHOW GLOBAL, not SHOW, which defaults to SHOW SESSION.

The interaction between GLOBAL and SESSION (for both VARIABLES and STATUS) varies with the setting.  For many, not all things, session is initialized to global when you login.  And wait_timeout is probably the quirkiest.

Warning

If you had 100000 connections each running complex SELECTs needing, say, 3 tmp tables, then you might need 100000 * 3 * 256M = 76.8TB of RAM to handle the tmp table!  ( 256M = min(tmp_table_size, max_heap_table_size) ).  This is a strong reason not to set all those things so high.


How to check the last time a table has been modified?

Well this turned out to be quite an interesting side project. There are a couple of ways that this can be accomplished, and Rolando shared four common methods on this site back in 2014. That said, something told me that there had to be a better way ...
You're right that looking at UPDATE_TIME in information_schema is not enough, because this value is not updated when the base tables are InnoDB. The only reliable way to determine if an InnoDB table has been modified — to the best of my knowledge — is to query the file system. However, like you said, it's not a great idea to grant access to the MySQL data directory to just anybody.
So, with this in mind, if a database is small, consisting of perhaps a dozen or so tables, the fastest way to accomplish the goal would be to use a trigger that updates a specific table, let's call it TableTracker, to say that an INSERT, UPDATE, or DELETE was committed as a given time. However, this is not particularly scalable if a database consists of hundreds of tables, nor does it take into account new tables that are created unless people remember to also include the necessary trigger. Looking at this logically, we must query the file system. It's the only way to automate a lot of the potential problems away.
And here's a possible way of doing it:

Create a TableTracker table in a MySQL database somewhere
Write a shell script that will query the file system and record last modification times to TableTracker
Automate it in a cron job

The TableTracker table does not need to be in the same database (or databases) that is being tracked, but it does need to exist. Here's a very rough table:
DROP TABLE IF EXISTS `TableTracker`;
CREATE TABLE IF NOT EXISTS `TableTracker` (
    `table_name`    varchar(120)    NOT NULL,
    `created_at`    timestamp       NOT NULL    DEFAULT CURRENT_TIMESTAMP,
    `updated_at`    timestamp       NOT NULL    DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (`table_name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

Now we need a shell script. This was written and tested on Ubuntu Sever 20.04, though it should work on any modern Linux platform:
#!/bin/bash
srcpath="/etc/mysql/my_dbname/*.ibd"
username="watcher"
password="superSecretPassword!123"
dbname="my_dbname"

sql=""
sql+="INSERT INTO TableTracker (table_name, updated_at) "
sql+="SELECT tmp.table_name, tmp.updated_at as updated_at "
sql+="FROM ("

for name in $srcpath; do
    ts=$(date -r $name "+%Y-%m-%d %H:%M:%S")
    fname="${name##*/}"

    tbl="SELECT '${fname%.*}' as table_name, '$ts' as updated_at UNION ALL "
    sql=$sql$tbl
done

sql+="SELECT '' as table_name, '2000-01-01 00:00:00' as updated_at) tmp "
sql+="WHERE tmp.updated_at > '2000-01-01 12:00:00' "
sql+="ON DUPLICATE KEY UPDATE updated_at = tmp.updated_at;"

# Record the Updates to MySQL
mysql -u $username -p$password $dbname <<< $sql

echo "All Done!"

Remember to set the file as executable with chmod +x.
From there, you can throw this file into a cron job and have it run as often as you'd like.
Notes:

this crude tool could be set up to watch multiple databases
this tool could be set up to watch any database file, not just InnoDB files
if this is running on the main database server, then no MySQL password will need to exist in the file, as root in Ubuntu can (usually) access root on MySQL directly
there is no error checking, so be sure to clean up the shell script if this is used in a production environment


Does PostgreSQL rewrite JOINs with OR's into UNIONs?

I'm afraid there is no good solution for you now, other than rewrite into a UNION (or denormalize/refactor the data).

There is a proposal to add automatic conversion of ORs to UNIONs but it needs more testing and reviews to get it into v11 of PostgreSQL, but I have verified that it does work for a case just like yours.


Log File Size and dbcc sqlperf (logspace)

The space used will decrease like that for different reasons based on the recovery model of the database named XYZ.
First of all, the transaction log is "circular," meaning that portions of it (virtual log files or VLFs for short) can be reused once they are no longer needed.  Marking a VLF for reuse is called "clearing" or "truncating" the transaction log.
If XYZ is in the Simple recovery model, then VLFs will be marked as resuable whenever a checkpoint operation occurs in the database (be it manual, or one of the automated checkpoints that occurs periodically).
If it's in the Full recovery model, VLFs can be reused once the information they contains has been backed up in a log backup.
It's likely one of those two things happening periodically that's causing space used to go down.
If VLFs aren't cleared fast enough, the log file will grow per your configuration / expectations (and File Size will change to the increased size).

You should check out the MS Docs page on SQL Server Transaction Log Architecture and Management Guide for detailed information on how the transaction log works.


Unable to ping/connect to azure sql server from sql server management studio

The problem was with our company firewall. The port 1433 was blocked on the firewall. After removing the outgoing and incoming restrictions on firewall the SMSS started working.


is it relatively safe to union two tables with UUID PK?

I guess your concern is that the UUIDs could no longer be unique after the UNION.
That is of course theoretically possible, but the danger is no bigger that if you use version-4 UUIDs as primary keys.
In practice, you don't have to worry about that. Wikipedia describes it well:

with [...] random version-4 UUIDs, collisions can occur even without implementation problems, albeit with a probability so small that it can normally be ignored. This probability can be computed precisely based on analysis of the birthday problem.
For example, the number of random version-4 UUIDs which need to be generated in order to have a 50% probability of at least one collision is 2.71 quintillion, computed as follows:

This number is equivalent to generating 1 billion UUIDs per second for about 85 years. A file containing this many UUIDs, at 16 bytes per UUID, would be about 45 exabytes.

If you paranoid enough to be afraid of that, you shouldn't use version-4 UUIDs at all, but use a different, collision-free UUID algorithm like version 1 or 2.


LEFT JOIN with WHERE (Microsoft Access SQL)

In your query left join is performed only using only the condition a.ID=b.ID and then results are filtered based on condition b.lang='de'. You need to add both conditions on the left join:

SELECT a.ID, a.job, b.job
FROM a LEFT JOIN b
ON (a.ID=b.ID and b.lang='de')


When changing the default port number from 5432 to 5332, why do I get a unix domain socket error?

You may have changed the port for the PostgreSQL database, but not the client.
Try this:

Update your PostgreSQL configuration to use port 5332 again
Test this by passing the port number when trying to connect:
psql -p 5332

If everything works, change the default port for the command-line client like this:
PGPORT=5332; export PGPORT

Now all calls to psql will act as if it has been invoked with the -p 5332 command-line option.


Setting connection collation in master for slave replication

Find all the Stored Routines; capture the ability to re-CREATE them.
Change the DEFAULT COLLATION for each DATABASE that had such routines.
DROP those routines.
Re-CREATE them.
SHOW CREATE PROCEDURE (etc) to verify that the collation is compatible with 5.7.


How do I unload data from a database to a flat file?

Right click on the database, under "Tasks" look for "Export Data", here the selected database is preconfigured as "Data Source" on the next screen you define the "Destination", change this to Flat File Destination and go from there. Later on you can use this as a template for SSIS if you need to do it often.


PostgreSQL indeterminate ordering of results

That's because the SQL standard doesn't require PostgreSQL to return rows in any particular order without an ORDER BY clause. It can return them however it feels like. See the manual for SELECT. In particular:

  If ORDER BY is not given, the rows are returned in whatever order the system finds fastest to produce.

and

  When using LIMIT, it is a good idea to use an ORDER BY clause that constrains the result rows into a unique order. Otherwise you will get an unpredictable subset of the query's rows — you might be asking for the tenth through twentieth rows, but tenth through twentieth in what ordering? You don't know what ordering unless you specify ORDER BY.

This makes LIMIT and/or OFFSET without an ORDER BY unsafe in almost all cases. It's permitted in case you've forced the ordering in an inner query, as a bit of a performance hack, but it's rarely a good idea.

You must add an ORDER BY clause to make your statement deterministic.

In this case, with the DISTINCT there it's probably doing hash aggregation - which tends to return results in an inconsistent order from execution to execution. Even if you were doing an index scan that usually returns results in order, you cannot assume that it will unless you tell PostgreSQL you require the rows in a particular order.

This is closely related to people who ask how to select rows in the "natural" order of a table; tables and result sets don't have a natural order. They're sets not lists. If you want an ordering you must specify one on an operation by operation basis.


Usually yes, when foreign keys are involved, you have to create them in the right order.

When you are sure, however, that your data is consistent, you can do it like this:

mysql> USE your_schema;
mysql> SET FOREIGN_KEY_CHECKS=0;
mysql> SOURCE /path/to/your/dump/file1.sql
mysql> SOURCE /path/to/your/dump/file2.sql
mysql> # ...
mysql> SET FOREIGN_KEY_CHECKS=1;

Or on the command line:

shell> mysql -uuser your_schema -e "SET GLOBAL FOREIGN_KEY_CHECKS=0;"
shell> cat file1.sql file2.sql | mysql -uuser your_schema
shell> mysql -uuser your_schema -e "SET GLOBAL FOREIGN_KEY_CHECKS=1;"


This would probably need to be a UNION query, as in:

SELECT 'a'
  , COUNT(a.time)
FROM
  tbl1 AS a
WHERE
  a.time BETWEEN '2018-03-07 18:32:55' AND '2018-03-07 20:46:55'

UNION ALL

SELECT 'b'
  , COUNT(b.time)
FROM
  tbl2 AS b
WHERE
  b.time BETWEEN '2018-03-07 18:32:55' AND '2018-03-07 20:46:55'

UNION ALL

SELECT 'c'
  , COUNT(c.time)
FROM
  tbl3 AS c
WHERE
  c.time BETWEEN '2018-03-07 18:32:55' AND '2018-03-07 20:46:55'

The output would look like:

╔══════╦══════╗
║ col1 ║ col2 ║
╠══════╬══════╣
║ a    ║   10 ║
║ b    ║    3 ║
║ c    ║   14 ║
╚══════╩══════╝


I can't reproduce the problem.

$ mysql -u root -p
Enter password:

Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 54
Server version: 10.0.23-MariaDB mariadb.org binary distribution

Copyright (c) 2000, 2015, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]> DROP DATABASE IF EXISTS `database_name`;
Query OK, 4 rows affected (0.01 sec)

MariaDB [(none)]> DROP ROLE `client`;
Query OK, 0 rows affected (0.00 sec)

MariaDB [(none)]> DROP USER 'john'@'%';
Query OK, 0 rows affected (0.00 sec)

MariaDB [(none)]> CREATE USER 'john'@'%';
Query OK, 0 rows affected (0.00 sec)

MariaDB [(none)]> CREATE ROLE `client`;
Query OK, 0 rows affected (0.00 sec)

MariaDB [(none)]> GRANT `client` TO 'john'@'%';
Query OK, 0 rows affected (0.00 sec)

MariaDB [(none)]> CREATE DATABASE `database_name`;
Query OK, 1 row affected (0.00 sec)

MariaDB [(none)]> CREATE TABLE `database_name`.`table1`(`col0` INT);
Query OK, 0 rows affected (0.00 sec)

MariaDB [(none)]> CREATE TABLE `database_name`.`table2`(`col0` INT);
Query OK, 0 rows affected (0.00 sec)

MariaDB [(none)]> CREATE TABLE `database_name`.`table3`(`col0` INT);
Query OK, 0 rows affected (0.00 sec)

MariaDB [(none)]> CREATE TABLE `database_name`.`table4`(`col0` INT);
Query OK, 0 rows affected (0.00 sec)

MariaDB [(none)]> GRANT INSERT ON `database_name`.`table1` TO 'client';
Query OK, 0 rows affected (0.00 sec)

MariaDB [(none)]> GRANT SELECT ON `database_name`.`table2` TO 'client';
Query OK, 0 rows affected (0.00 sec)

MariaDB [(none)]> GRANT SELECT ON `database_name`.`table3` TO 'client';
Query OK, 0 rows affected (0.00 sec)

MariaDB [(none)]> GRANT INSERT ON `database_name`.`table4` TO 'client';
Query OK, 0 rows affected (0.00 sec)

MariaDB [(none)]> SHOW GRANTS FOR 'john'@'%';
+----------------------------------+
| Grants for john@%                |
+----------------------------------+
| GRANT client TO 'john'@'%'       |
| GRANT USAGE ON *.* TO 'john'@'%' |
+----------------------------------+
2 rows in set (0.00 sec)

MariaDB [(none)]> SHOW GRANTS FOR `client`;
+------------------------------------------------------+
| Grants for client                                    |
+------------------------------------------------------+
| GRANT USAGE ON *.* TO 'client'                       |
| GRANT SELECT ON `database_name`.`table3` TO 'client' |
| GRANT SELECT ON `database_name`.`table2` TO 'client' |
| GRANT INSERT ON `database_name`.`table1` TO 'client' |
| GRANT INSERT ON `database_name`.`table4` TO 'client' |
+------------------------------------------------------+
5 rows in set (0.00 sec)

MariaDB [(none)]> exit
Bye

$ mysql -u john -p
Enter password:

Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 56
Server version: 10.0.23-MariaDB mariadb.org binary distribution

Copyright (c) 2000, 2015, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]> SHOW DATABASES;
+--------------------+
| Database           |
+--------------------+
| information_schema |
+--------------------+
1 row in set (0.00 sec)

MariaDB [(none)]> SET ROLE `client`;
Query OK, 0 rows affected (0.00 sec)

MariaDB [(none)]> SHOW DATABASES;
+--------------------+
| Database           |
+--------------------+
| database_name      |
| information_schema |
+--------------------+
2 rows in set (0.00 sec)

MariaDB [(none)]> SELECT `col0` FROM `database_name`.`table1`;
ERROR 1142 (42000): SELECT command denied to user 'john'@'localhost' for table 'table1'

MariaDB [(none)]> SELECT `col0` FROM `database_name`.`table2`;
Empty set (0.00 sec)

UPDATE

With a username longer than 6 characters, I can reproduce the problem, however, you can access the tables.

$ mysql -u root -p
Enter password:

MariaDB [(none)]> CREATE USER 'usertestjohn'@'%';
Query OK, 0 rows affected (0.00 sec)

MariaDB [(none)]> GRANT `client` TO 'usertestjohn'@'%';
Query OK, 0 rows affected (0.00 sec)

MariaDB [(none)]> exit
Bye

$ mysql -u usertestjohn -p
Enter password:

MariaDB [(none)]> SHOW DATABASES;
+--------------------+
| Database           |
+--------------------+
| information_schema |
+--------------------+
1 row in set (0.00 sec)

MariaDB [(none)]> SET ROLE `client`;
Query OK, 0 rows affected (0.00 sec)

MariaDB [(none)]> SHOW DATABASES;
+--------------------+
| Database           |
+--------------------+
| information_schema |
+--------------------+
1 row in set (0.00 sec)

MariaDB [(none)]> SELECT `col0` FROM `database_name`.`table2`;
Empty set (0.00 sec)

Bug reported to MariaDB: Roles and Users longer than 6 characters.


Restore bak file to smaller mdf and ldf database files

The backup contains two pieces of information:

the database files layout and metadata, ie. RESTORE FILELISTONLY ...
the actual data

The actual data in the backup is those 1.8 GB. But the metadata describes the layout of files as on the original production server. While it looks like compression, is not actual compression. Is just metadata vs. content. the problem is that during RESTORE the reporting machine creates a layout as described in the metadata, therefore 114.6GB + 24.8GB. Now for the real question: can you restore it w/o recreating the original DB file layout? No. However, there is another solutions to your problem: bring the original DB back in check. Shrinking the Transaction Log covers the steps you need to do, since the log is your major pain point. Next I would deploy log shipping instead of backup/restore.


Error while installing Oracle 11g in Ubuntu 18.04

Looking at the Oracle provided fixup scripts, I got the answer. The main script is orarun.sh which runs on bash.
So the following command does the trick!
PS: Remember, you need to be login as root.

bash ./orarun.sh fixup.response fixup.enable .


High Availability Error 35250 and “An error occurred while receiving data: '10054(An existing connection was forcibly closed by the remote host.)

After too many tries and reading lots of blogs about SQL Server error 35250, I can't solve this error.

Finally, I tried IP address in Endpoint URL and I successfully configured Always On availability group. Problem in FQDN name.

To use an IP address in Endpoint URL: First check below services are started. If not, then start on all replicas and DC:

1. DNS Client
2. Function Discovery Resource Publication
3. SSDP Discover
4. UPnP Device Host

Above services help to discover all nodes in the network. Then check into network for your all node are shown over there. If not then follow these steps:
Go to Network and sharing center; click on change advanced sharing settings; and turn on network discovery.

TCP://10.10.10.530:5022


Answer to your first question: Each file contains info for the table

cars.frm has the table structure
cars.MYD has the table data
cars.MYI has the table indexes

Given this layout, the cars table uses the storage engine MyISAM.

Answer to your second question: Yes, provided the following circumstances

every table uses the storage engine MyISAM
no INSERTs, UPDATEs, or DELETEs are running
DB Connections using TCP/IP are disabled

To check to see if all the tables are MyISAM, run this query:

SELECT COUNT(1) TableCount,engine
FROM information_schema.tables
WHERE table_schema='db_test_1'
GROUP BY engine;

You should only see just MyISAM. If you see other storage engines, you cannot just copy.

If your root@localhost password is mypass, here is all you need to to safely move all the tables regardless of storage engine:

# USERPASS="-uroot -pmypass"
# service mysql restart --skip-networking
# mysql ${USERPASS} -A -e"CREATE DATABASE db_test_2"
# mysqldump ${USERPASS} --routines --triggers db_test_1 | mysql ${USERPASS} -A -Ddb_test_2
# service mysql restart

If the data is huge, do this instead:

# USERPASS="-uroot -pmypass"
# service mysql restart --skip-networking
# mysql ${USERPASS} -A -e"CREATE DATABASE db_test_2"
# mysqldump ${USERPASS} --routines --triggers db_test_1 > /root/MyData.sql
# mysql ${USERPASS} -A -Ddb_test_2 < /root/MyData.sql
# service mysql restart

Give it a Try !!!


PostgreSQL network compression for result set?

This part of the documentation describes how you can do it by first setting up an SSH tunnel (outside of Postgres) and then connecting to that. If you add the -C flag to the ssh command, it should enable compression.

https://www.postgresql.org/docs/current/static/ssh-tunnels.html


Improve speed of index rebuild on SQL server

Achieving optimal import performance in this scenario requires three things:

Minimally-logged base table inserts
Minimally-logged nonclustered index builds
Avoiding physical reads

Minimal Logging

Achieving minimally-logged inserts to an empty clustered table without nonclustered indexes requires:

Using either the SIMPLE or BULK_LOGGED database recovery models
Specifying a table lock and ordered input (e.g. TABLOCK and ORDER hints)

Side note:

  It is also possible to achieve minimally-logged inserts to a clustered
  table that has nonclustered indexes provided trace flag 610 is
  enabled. Whether nonclustered index inserts are minimally-logged or
  not depends on the query plan selected by the query optimizer.

  If the query plan uses a separate iterator for the nonclustered index,
  and the iterator has the DMLRequestSort property set to true, the nonclustered index inserts will be minimally logged, provided the
  other conditions mentioned previously are met.

Building nonclustered indexes separately

The advantages of doing this are:

Clustered index inserts can be minimally-logged without enabling TF 610
CREATE INDEX is minimally logged if the recovery model is not FULL

Avoiding physical reads

Ideally, the data to be imported will be stored on a separate machine, or at least on separate physical storage from that used to host the database.

The database server should have enough memory to hold the largest base table in cache, with enough left over for sorting operations necessary when building nonclustered indexes.

A good pattern is to fast-load the base table (minimally-logged clustered index load) and then to build all nonclustered indexes for that table while its data pages are still cached.

The question outlines a process whereby base tables are loaded first, and then nonclustered indexes built. The cursor definition does not use an ORDER BY clause to at least group nonclustered index builds on the same table together.

The likely result is that data pages for different tables are repeatedly read into cache and then discarded as nonclustered indexes are built in a non-deterministic order.

The cost of repeated physical reads completely dominates the benefits of minimal logging gained by building nonclustered indexes separately. This explains why you found that loading tables with existing indexes is faster (because all nonclustered indexes for a given table are maintained before moving on to the next table).

Summary

The import process should be reworked to bulk-load one table at a time. This means loading the table and building all nonclustered indexes before moving on to the next one. The SQL Server instance should have enough memory available to hold the largest table and perform the largest nonclustered index sort at the same time.

You could also try enabling TF 610 before loading the data into tables with nonclustered indexes already in place. This is not usually as fast as the previous method, but it may be fast enough.

See the following for more information:

The Data Loading Performance Guide

Operations That Can Be Minimally Logged


It's a good practice to delimit all identifiers. In this particular case, you said the PK_dbo prefix was actually part of the index name. Therefore, the name has to be delimited appropriately, or it cannot be parsed unambiguously.

Also, what's actually being renamed is an index, not an object.

This should work after filling in the blanks:

sp_rename '[<schema>].[<table>].[PK_dbo.ContactSessions]', 'ContactSessions_old', 'INDEX';

I wouldn't use CREATE INDEX ... WITH(DROP_EXISTING = ON) for this because it will physically recreate the index, which is unnecessary in this scenario. This construct is best used for when the definition of the index needs to change while preserving the name.


MySQL - Truncated incorrect datetime value

In would recommend
  SELECT CONVERT( '2020-06-01T09:38:08+02:00' , DATETIME)

STR_TO_DATE does the trick, but is usually slow.
You can run such a Query to test the performance using the column name instead of the text
 CREATE TABLE t2 (tz TIMESTAMP) SELECT CONVERT( '2020-06-01T09:38:08+02:00' , DATETIME)


How to raise_application_error beyond a when others clause

There are a variety of approaches you could take. You should pick the one you feel is most suitable for your application.

Include no WHEN OTHERS exception handlers and let the raw Oracle errors propagate to your users.
Include WHEN OTHERS exception handlers in the top-level blocks.
Include WHEN OTHERS exception handlers in all/most blocks

The first option requires no effort on your part, but can expose users to very unfriendly error messages. They can also expose details about the database which could be of use to hackers, etc.

The second option require some effort. You can use the built-in packages, such as DBMS_UTILITY.FORMAT_ERROR_BACKTRACE to identify where an exception occurred, which can be included in any logging processes you may have. You can convert the raw Oracle errors into application specific ones.

The third option requires most effort. However, as well as the above, you can also log items such as variable values to help you identify why an error occurred, not just where it occurred. Using some common exception handling routines -as you seem to be doing - will reduce the effort required for this approach.

WHEN OTHERS THEN
   exception_pkg.handler('<variable_values_of_interest>')
   RAISE;

where you pass the variables values to be logged via a standard process before re-raising the exception.

You should always re-raise an exception in a WHEN OTHERS block, either the original exception or one with an application specific message - via RAISE_APPLICATION_ERROR.


What's the most efficient way to batch UPDATE queries in MySQL?

Since you're using InnoDB tables, the most obvious optimization would be to group multiple UPDATEs into a transaction.

With InnoDB, being a transactional engine, you pay not just for the UPDATE itself, but also for all the transactional overhead: managing the transaction buffer, transaction log, flushing the log to disk.

If you are logically comfortable with the idea, try and group 100-1000 UPDATEs at a time, each time wrapped like this:

START TRANSACTION;
UPDATE ...
UPDATE ...
UPDATE ...
UPDATE ...
COMMIT;

Possible downsides:

One error will collapse the entire transaction (but would be easily fixed in code)
You might wait for a long time to accumulate your 1000 UPDATEs, so you might also want to have some timeout
More complexity on your application code.