Why and how sometimes I get the "absent table error" while there are definitely Sch-M lock?

Really interesting and tough question.  I was not able to find any official documentation of this behavior, and I suspect there may not be any (though I'd love it if someone corrects me on that!).

My research leads me to believe that it is the plan compilation step that is vulnerable to this race condition.  Note that I was able to run your test queries for an hour with no errors, but if I repeatedly kick kick off your process, I get an immediate error a portion of the time.  When it does hit an error, it always does so right away on plan compilation.  Alternatively, you can add "OPTION RECOMPILE" to the COUNT(*) in the loop, forcing a new plan to compile at each trial.  Using this approach, I see the error almost immediately every time I have run your scripts.

I was able to reproduce the error with a controlled series of steps that seems to hit the error on every trial, removing the need to set up a loop and rely on randomness.

I also threw in a potential fix (to use ALTER TABLE...SWITCH) which might be possible to try out in your production environment.  Now, on to the details!

Here are the steps to reproduce:

-- Instructions: Process each step one at a time, following any instructions in that step

-- (1) Initial setup: Clean any relics and create the test table
DBCC TRACEOFF(-1,3604,1200) WITH NO_INFOMSGS;
SET NOCOUNT ON;
IF @@TRANCOUNT > 0 ROLLBACK
IF object_id('test') IS NOT NULL
    DROP TABLE test
IF object_id('TMP_test') IS NOT NULL
    DROP TABLE TMP_test
IF object_id('test1') IS NOT NULL
    DROP TABLE test1
CREATE TABLE test(Id INT IDENTITY CONSTRAINT PK_test PRIMARY KEY)
GO
INSERT test DEFAULT VALUES
GO 2000

-- (2) Run the COUNT(*)
-- This will acquire the Sch-S lock, and we use HOLDLOCK to retain that lock.
-- This simulates a race condition where this query is running at the time the first rename occurs.
BEGIN TRANSACTION
SELECT COUNT(*) FROM Test WITH (HOLDLOCK)
GO

-- (3) In another window, run the block of code that performs the renames
-- This will be blocked, waiting for a Sch-M lock on "test" until we complete step 4 below
CREATE TABLE TMP_test(Id INT PRIMARY KEY)
INSERT TMP_test SELECT * FROM test
EXEC sp_rename 'test', 'test1'
EXEC sp_rename 'TMP_test', 'test'
EXEC sp_rename 'test1', 'TMP_test'
DROP TABLE TMP_test
GO

-- (4) Now, commit the original COUNT(*) and immediately fire off the query again
-- We use OPTION (RECOMPILE) to make sure we need to compile a new query plan
-- The COMMIT releases the Sch-S lock, allowing (3) to acquire the Sch-M lock
-- This batch will now be waiting for the Sch-S lock again, on the same object_id,
-- but that object_id will no longer point to the correct object by the time the lock
-- is acquired.
COMMIT
SELECT COUNT(*) FROM Test OPTION (RECOMPILE)
GO

Using these steps, we can dig in a little bit more to what is causing the error.  To do so, I ran a trace with the following events on: SP:StmtStarting, SP:StmtCompleted, Lock:Acquired, Lock:Released.

What I found is that the following occurs, in order (but omitting some detail in between):

The first COUNT(*) acquires a Sch-S lock
The first COUNT(*) acquires some locks on statistics (presumably to build a query plan)
The first COUNT(*) releases a Sch-S lock (end of plan compilation)
The first COUNT(*) acquires a IS lock (beginning of execution), then runs successfully
The sp_rename runs up to the point where it needs to take a Sch-M lock, then is blocked by the first COUNT(*), which is not yet committed
The first COUNT(*) is committed
The sp_renames acquires the Sch-M lock
The second COUNT(*) requests a Sch-S lock and is added to the blocking chain behind the sp_rename
The sp_renames complete (at this point, the second COUNT(*) is waiting on a Sch-S lock for an object_id that is no longer the correct object_id)
The second COUNT(*) acquires the Sch-S lock
The second COUNT(*) acquires a Sch-S lock on sys.sysschobjs, presumably as a sanity check because it has detected (or needs to confirm) that something is amiss with Sch-S lock it was granted on the object_id that used to be "test"
The Invalid object name 'Test' error is thrown

However, if a similar sequence of events occurs when a plan compilation is not needed (such as when running your loop), SQL Server is actually smart enough to detect that the object_id has changed.  I also traced that case, and I found a situation where the second COUNT(*) performed the following sequence

Acquires the Sch-S lock (on the object_id that used to be "test")
Acquires a Sch-S lock on sys.sysschobjs
Releases both of these locks
Acquires a IS lock on a different object_id; the new object_id of "test"!
Runs successfully to completion

So it does look like this adaptive logic is in place, as you hypothesized.  But it looks like it may be in place only for query execution, and not for query compilation.

As promised, here is an alternative snippet for section (3) that appears to provide a way of renaming tables that resolves the concurrency problem (at least on my machine!):

-- (3) In another window, run the block of code that performs the "renames"
-- Instead of sp_rename, we use ALTER TABLE...SWITCH
-- This appears to be a more robust way of performing the same logic
CREATE TABLE test1(Id INT PRIMARY KEY)
CREATE TABLE TMP_test(Id INT PRIMARY KEY)
INSERT TMP_test SELECT * FROM test
ALTER TABLE test SWITCH TO test1
ALTER TABLE TMP_test SWITCH TO test
ALTER TABLE test1 SWITCH TO TMP_test
DROP TABLE TMP_test
DROP TABLE test1
GO

Lastly, here is one other link that I found useful in getting my head wrapped around how to think about locking in the context of tables whose names are changing:

http://www.sqlnotes.info/2011/12/02/sp_rename-causes-lock-leaking/


Mariadb configuration is not loaded from my.cnf

I am having the same problem in ~/.my.cnf file (MySQL 5.5.5-10.2.6-MariaDB installed via Homebrew).

Apparently it is a recorded bug in both MySQL and MariaDB:

Bug #84172    The pid-file value is ignored in the /etc/my.cnf option file
https://bugs.mysql.com/bug.php?id=84172

pid_file in my.cnf ignored
https://jira.mariadb.org/browse/MDEV-8168


MsSql express export from local pc to RDS, how to transfer NDF/LDF files?

convention is that ndf is used for the 2nd & subsequent data file & ldf for the transaction log file - so YES these are included in your database backup. Also worth noting that these are just conventions and you can give the files any extension you like.


ORA-01555: snapshot too old: rollback segment number with name "" too small

Per the Oracle Docs and MetaLink support note 66431.1, the standard undo mechanism is not used by LOB segments, which have their own page versioning (controlled via PCTVERSION and the LOB_retention_clause).

"Snapshot too old" generally means that a transactionally-consistent version of your data could not be hold long enough because of lack of space. It might be that your PCTVERSION setting is too strict and effectively causes any new update to invalidate the consistent version(s).


The choice between index scan and bitmap index scan is basically decided by how many rows per data page Postgres expects to retrieve - which depends on statistics about data distribution in the table and selectivity of your query predicates. And by the expected cost of random memory access - which is defined by cost settings, most prominently random_page_cost and effective_cache_size.
If Postgres expects to find enpugh rows on the same data page, it switches to bitmap index scan, which is more efficient for this kind of physical data distribution. (If most data pages are retrieved, a lowly sequential scan is faster anyway.) So even if columns and indexes of your two tables look all the same, you may still get these different query plans depending on data distribution and selectivity of your query predicates.

Optimizing queries on a range of timestamps (one column)
"Recheck Cond:" line in query plans with a bitmap index scan

But your tables are very different to begin with. mp_physical has much wider rows, so that only few rows are located on the same data page (default 8kb). This heavily favors index scans, since bitmap index scans won't buy much (if anything) even when retrieving a higher percentage of all rows.
Also, be aware that temporary tables are not covered by autovacuum and hence not analyzed automatically. You may have to do this manually to get accurate table statistics for the query planner to work with:
ANALYZE observations_optical_temp;

Are regular VACUUM ANALYZE still recommended under 9.1?


Does latin1 have performance benefits over utf8?

Short answer:  Don't bother.

Long answer:

https://bugs.mysql.com/bug.php?id=84440 -- you may or may not save space.
InnoDB is inherently bigger than MyISAM.
Comparing characters in utf8 is slightly slower than in latin1.  But you will probably not notice.
The tiny difference between 1741668352 abd 1810874368 is probably due to the random nature of how you build one table from the other.  Or the phase of the moon, not something significant.
The 30 vs 31 comes from how InnoDB estimates things.

Do not use CHAR except for truly fixed-length strings.  Almost always they are ascii, such as country_code, postal_code, UUID, hex, md5, etc.

Get in the habit of explicit saying ascii or utf8mb4 when you create the column/table unless you have an unusual case where you need something else.


Dirty buffer pages after issuing CHECKPOINT

A manual CHECKPOINT should write all dirty pages to disk.  It does this for the database that is active for your connection, not for all databases.  Make sure the current database is the name of whatever database_id=7 is:
USE [YourDatabaseName];
GO

CHECKPOINT;
GO

SELECT *
FROM sys.dm_os_buffer_descriptors
WHERE database_id = 7 AND is_modified = 1;

See How do checkpoints work and what gets logged:

All dirty data file pages for the database are written to disk (all pages that have changed in memory since they were read from disk or since the last checkpoint), regardless of the state of the transaction that made the change.

And Database Checkpoints (SQL Server):

Name
Transact-SQL Interface
Description

Manual
CHECKPOINT [checkpoint_duration]
Issued when you execute a Transact-SQL CHECKPOINT command. The manual checkpoint occurs in the current database for your connection...

Note that a bunch of things can cause dirty pages show up in sys.dm_os_buffer_descriptors after all explicit (user initiated) write operations have completed.  Some of these could be in response to SELECT queries:

sync or async stats updates from plan compilation
query store data collection

Or due to background tasks related to specific SQL Server features:

columnstore tuple mover
deferred drop (if tables were truncated or dropped, but extents are still being deallocated in the background
Change Tracking autocleaup
PVS cleanup (if using Accelerated Database Recovery on SQL Server 2019)

That's not an exhaustive list, but gives you an idea that you might see dirty pages for many reasons beyond just inserts, updates, and deletes.


I can see what your second code is doing. You want to open 2 cursors only if certain conditions are met, but only 1 cursor if not met. IMHO that should be fine.

In fact, the book MySQL Stored Procedure Programming has this sample code on pages 110-111 under Example 5-17

DECLARE CONTINUE HANDLER FOR NOT FOUND SET l_last_customer=1;
SET l_last_customer=0;
OPEN customer_csr;
cust_loop:LOOP
    FETCH customer_crs INTO l_customer_id;
    IF l_last_customer=1 THEN LEAVE cust_loop; END IF;
    SET l_customer_count=l_customer_count+1;
    sales_block: BEGIN
        DECLARE l_last_sale INT DEFAULT 0;
        DECLARE CONTINUE HANDLER FOR NOT FOUND SET l_last_sale=1;
        OPEN sales_csr;
        sales_loop:LOOP
            FETCH sales_csr;
            IF l_last_sale=1 THEN LEAVE sales_look; END IF;
            CALL check_sale(l_sales_id);
            SET l_sales_count=l_sales_count+1
        END LOOP sales_loop;
    END sales_block;
END LOOP cust_loop;
SET l_last_customer=0;
CLOSE customer_csr;

Notice in the code the opening and closing of a cursor inside a BEGIN...END block. You should be OK with your code. If mysql complains, place your THEN code and ELSE code within BEGIN...END blocks.


It is sometimes called the "commandline mysql tool".  It is a client that comes with MySQL and MariaDB.  It is a simple but very useful way to type SQL and get it executed.  Think of it as the "shell" version of phpmyadmin or Workbench.

It does not do any monitoring, though it could be used as a way to execute SQL in a monitoring script.


MySQL InnoDB Plugin default engine type not accepting config setting

I have finally found the answer, and it was [unsurprisingly] staring me in the face the whole time...

I had inherited the system from an earlier admin, who had made a mistake in the my.cnf file putting all the InnoDB specific parameters under a [innodb] heading, instead of under the [mysqld] heading where it should be.

Why did this take so long to locate? It was assumed that the config was working correctly in the first place. [slaps head]...

Incorrect file segment

[mysqld]
# Stuff here

[innodb]
# innodb stuff here

Correct file segment

[mysqld]
# Stuff here

# innodb stuff here

And now everything works as intended.


Security implications of backing up to UNC path

We have a lot of SQL Server VMs and we've found it best to backup database to a centralized location via a Windows share and keeping our VMs slim. However, we've been backing up to the administrative Windows $ shares--which I have yet to see any administrator turn off -- though it can be done.

As for your question: usually you map a drive only for convenience sake because you want to or provides and expected benefit, but you still have to map it to a UNC name. The network packets to and from the mapped drive are still going to contain UNC information. That information doesn't get obfuscated because you mapped it to a drive letter. Actually systems that have mapped drives have been more vulnerable to trojans like Cryptolocker!

If there are legitimate data hacking concerns, just turn on backup encryption--knowing it will add some CPU cycles to the mix(and the new task of acquiring, managing, and maintaining certificates). Our network is fairly secure so we are not currently encrypting our backups--but that day is probably coming sooner rather than later. Additionally, it sounds like the questions need to be re-directed to the owners and creators of the data domain, since that's where the share resides and most of the risk exists (not to mention the UNC share name).

If the powers that be are wondering about a UNC hack, I'd have start asking questions like:

1.> "is our network in danger of being hacked or have we been hacked already? What's your confidence level for this scenario?"

2.> "are server backups currently being encrypted?"

3.> "have Windows admin/$ UNC shares turned off?"

4.> "are certificates readily available for encryption -- should we go that route?"

5.> "do we even have a  corporate certificate policy we can rely on in order to acquire certificates from a reliable source?"

6.> and my personal favorite, "do I need to air gap my SQL Server?"


Please provide some sizes.  For example, how big would BIG be when it is time to remove the rows?  How big are the other tables?

MEMORY is not necessarily better than InnoDB.  It may be slower because of table vs row locking.  It may interfere with overall performance because of taking RAM away from the buffer_pool for BIG, thereby slowing down other things.

Yes, having a "staging" table is a practical way to do certain things.  For really high-speed staging, ping-ponging between two table may be desirable.

PARTITION is unlikely to be useful in what you have described so far.

Please describe what kind of data you have and why it needs AC or ACID (if it is not obvious from the data).

Edit

Thanks.  One row a minute?  Things don't get exciting until 100 per second.  Based on that, I would expect either MySQL or Postgres to consider this to be a "tiny" database.

I recommend doing it in whatever way is easiest for you.  I do not forsee any scaling/performance problems, at least not for the near future.  (I am assuming adequate INDEXes on the tables and reasonable queries.)  I'm still vague on what the "pump" is, but it sounds unnecessary.


Is this a correct understanding of transactions?

TRUNCATE is indeed very fast, but it has to take an ACCESS EXCLUSIVE lock on the table.
For this, the query has to wait until all concurrent transactions that use the table (even if only for a SELECT!) are done. Only then can it take the lock and execute the actual truncation.
To make it faster, see that concurrent transactions are short. 16 seconds is an awfully long time for a database transaction.
To your questions:

All the transactions that started before the TRUNCATE (and that block it) will see the complete, untruncated table.
All transactions that start after the TRUNCATE will be blocked until the TRUNCATE's transaction is done, even if they only SELECT from the table.

If you TRUNCATE and INSERT in the same transaction, the ACCESS EXCLUSIVE lock is held until the whole transaction is done.
So it will be like in the previous case, but the transactions that started later will see the table filled immediately.


Filter Data to different data files on SQL server

Table By Table - You can relatively easily do this.  You need to create a filegroup, then a file in that filegroup and then you can move the tables you want to the new filegroup.  Use the CREATE INDEX syntax with DROP EXISTING=ON and specify the new filegroup.

The clustered index is the table so moving them this way moves the table.

Your alternatives are to use partitioning but that requires enterprise and you won't be able to control it the way you are discussing.

MSDN CREATE INDEX

Example for AdventureWorks2014 and the Person.Person table.

USE [master];

--Creates a new filegroup.
ALTER DATABASE [AdventureWorks2014] ADD FILEGROUP [SLOW]

--Adds a file to the filegroup...
ALTER DATABASE [AdventureWorks2014] ADD FILE ( NAME = N'AdventureWorks2014_SlowSAN'
                                , FILENAME = N'C:\SQLData\JDF2014\Data\AdventureWorks2014_DataSlow.MDF'
                                , SIZE = 524288KB
                                , FILEGROWTH = 131072KB ) TO FILEGROUP [SLOW];

USE AdventureWorks2014

--Move the clustered index (which is the table) to the new filegroup.
CREATE UNIQUE CLUSTERED INDEX PK_Person_BusinessEntityID
    ON Person.Person (BusinessEntityID)
WITH (DROP_EXISTING =  ON )
ON SLOW
GO


OpenRowSet - How to configure OLE DB Provider to be used for for distributed queries

after installation Restart agent and server services

Enable OLEDB Driver in SQL Server

EXEC sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
EXEC sp_configure 'Ad Hoc Distributed Queries', 1;
GO
RECONFIGURE;
GO


One table for all users.  Period.  End of discussion.  (This is a common question; the answer is always the same.)

When you get to a million rows, we can discuss some other issues and solve them before you embark on the road to a billion.

What is best for project X?  If it will have a billion rows, you will need a database expert to tweak whatever underlying system you are using.  Any system can do it.  But it won't be trivial.

Focus on getting to a million rows.  Reconsider all of your design decisions at that point.  Any decisions made today will not survive to 1 billion.

Would you like me to repeat myself all over again?


Recommendation for PGA_AGGREGATE_TARGET in ASMM

Setting the Initial Value for PGA_AGGREGATE_TARGET

  To set the initial value for PGA_AGGREGATE_TARGET:

  Determine how much of the total physical memory to reserve for the
  operating system and other non-Oracle applications running on the same
  system.

  For example, you might decide to reserve 20% of the total physical
  memory for the operating system and other non-Oracle applications,
  dedicating 80% of the memory on the system to the Oracle database
  instance.

  Divide the remaining available memory between the SGA and the PGA:

  For OLTP systems, the PGA memory typically makes up a small fraction
  of the available memory, leaving most of the remaining memory for the
  SGA.

  Oracle recommends initially dedicating 20% of the available memory to
  the PGA, and 80% to the SGA. Therefore, the initial value of the
  PGA_AGGREGATE_TARGET parameter for an OLTP system can be calculated
  as:

  PGA_AGGREGATE_TARGET = (total_mem * 0.8) * 0.2 where total_mem is the
  total amount of physical memory available on the system.

  For DSS systems running large, memory-intensive queries, PGA memory
  can typically use up to 70% of the available memory.

  Oracle recommends initially dedicating 50% of the available memory to
  the PGA, and 50% to the SGA. Therefore, the initial value of the
  PGA_AGGREGATE_TARGET parameter for a DSS system can be calculated as:

  PGA_AGGREGATE_TARGET = (total_mem * 0.8) * 0.5 where total_mem is the
  total amount of physical memory available on the system.

  For example, if an Oracle database instance is configured to run on a
  system with 4 GB of physical memory, and if 80% (or 3.2 GB) of the
  memory is dedicated to the Oracle database instance, then initially
  set PGA_AGGREGATE_TARGET to 640 MB for an OLTP system, or 1,600 MB for
  a DSS system.


Date calculations give different results in different environments

sqlfiddle.com might interpret returned timestamps as having a timezone.
But PostgreSQL does not store a timezone along with a timestamp.
timestamp with timezone fields are internally stored in UTC: https://www.postgresql.org/docs/11/datatype-datetime.html#DATATYPE-TIMEZONES
A timezone is not returned in this query either.
So this behavior of sqlfiddle.com is misleading.


You'll need to copy over ALL the contents of your old data dir to the new data dir.  Specifically it sounds like you did not copy the mysql folder (read mysql database) over to the new data dir.


Why this system table doesn't appear on dictionary table?

I do not know any proper explanation. It has always been like this. Maybe Oracle was lazy, forgot to update the DICTIONARY view and noone even bothered to report this. Maybe there is a real reason for this.

If you check the definiton of the view DICTIONARY (DICT is a synonym for that), you can see it is a union of 3 branches (simplified):

ALL_*, DBA_*, USER_* views
A specific list of other views
Some others views that have public synonyms created on them with a different name (like DICTIONARY and DICT)

The problem is with 2. in my opinion. These newer views do not follow the regular naming schema and they should be included in this list.

I had the same experience with views related to Data Redaction.

Views such as REDACTION_POLICIES or REDACTION_COLUMNS are also not listed in DICT/DICTIONARY.


You could use a recursive common table expression to find all the derivations from a particular starting point.

Table and Data

DROP TABLE IF EXISTS dbo.ObjectTree;

CREATE TABLE dbo.ObjectTree
(
    [object_id] integer NOT NULL
        CONSTRAINT [PK dbo.ObjectTree object_id]
        PRIMARY KEY CLUSTERED,
    [object_name] varchar(50) NOT NULL
        CONSTRAINT [UQ dbo.ObjectTree object_name]
        UNIQUE NONCLUSTERED,
    derived_from_object_id integer NULL
        CONSTRAINT [FK object_id]
        FOREIGN KEY (derived_from_object_id)
        REFERENCES dbo.ObjectTree ([object_id]),

    INDEX [IX dbo.ObjectTree derived_from_object_id] (derived_from_object_id)
);

INSERT dbo.ObjectTree
    ([object_id], [object_name], derived_from_object_id)
VALUES
    (1, 'object1', NULL),
    (2, 'object2', 1),
    (3, 'object3', 1),
    (4, 'object4', NULL),
    (5, 'object5', 3),
    (6, 'object6', 2),
    (7, 'object7', 3),
    (8, 'object8', 5);

Recursive Query

-- Where to start
DECLARE @ObjectID integer = 1;

WITH R AS
(
    -- Anchor
    SELECT
        OT.[object_id]
    FROM dbo.ObjectTree AS OT
    WHERE
        OT.[object_id] = @ObjectID

    UNION ALL

    -- Recursive
    SELECT
        OT.[object_id]
    FROM R
    JOIN dbo.ObjectTree AS OT
        ON OT.derived_from_object_id = R.[object_id]
)
SELECT
    derivations = COUNT_BIG(*)
FROM R
OPTION MAXRECURSION(0);

This returns 7 for [object_id] = 1.

Try it at db<>fiddle.

Using MAXRECURSION

The MAXRECURSION query hint can be used to prevent a poorly formed recursive CTE from entering into an infinite loop. If that is a concern for you, use a suitable maximum recursion level instead of the zero (no limit) above. The default (no hint) is 100.


How can I find out if a SQL Function can be inlined?

Since it changes the actual execution plan, you can tell from the output of EXPLAIN (ANALYZE, VERBOSE) (among others): Example:

EXPLAIN (ANALYZE, VERBOSE) SELECT my_func(col) FROM tbl;

I simplified and adapted the fiddle from my answer you are referring to:

db<>fiddle here

The first function is inlined, hence you see the inlined expression in the output:

Output: (to_char((d)::timestamp with time zone, 'MMDD'::text))::integer

The second function is not inlined, hence you see the function call in the output:

Output: f_mmdd_tc_i(d)

To be precise: this shows whether the function has been inlined in the tested query. If it has not, that does not necessarily prove it can't be (under different circumstances).
And the test is not bound to just IMMUTABLE functions in any way.


Can I get the union of the a Postgres fulltext index?

If I understand your question correctly and it isnt at all clear, you are trying to pull back information as far as what id's are connected with a value.  I don't think you can just pull it from the index in PostgreSQL because the index will not contain visibility info, and so you will have a LOT of random IO and waiting for platters to turn.

The query for your test case is:

select thing, array_agg(id) from test group by thing;

Assuming you are on a version high enough to have array_agg.

In my system (9.1) this gives me:

chris=> select thing, array_agg(id) from test group by thing;
   thing  | array_agg
 ---------+-----------
  'one'   | {1,4}
  'two'   | {2,5}
  'three' | {3}
 (3 rows)

that's what you are looking for, right?


As Joel says, you would be better off storing bookings rather than availability.  This will make any future changes to operating hours or booking conditions easier (slot length, minimum/maximum booking duration etc).  Also, you won't have to pre-populate an availability table out to some arbitrary future date.


As far as I know you don't need foreign keys to make joins.

Also, you don't even need to use the "inner/outer join" syntax to make joins!

So, following your schema:

pUserMatch  |  pGroupMatch  |  ProfileData
----------- |  -----------  |  ----------
* username
* GroupID .... GroupID
               ProfileID ....  ID
                               ParentUser

The query would be:

select u.username, u.GroupID, g.ProfileID, p.ParentUser
from pUserMatch u, pGroupMatch g, ProfileData p
where u.GroupID = g.GroupID
  and g.ProfileID = p.ID
  and usermatch.username = <"username">

Anyway the "join" syntax would be the following:

select u.username, u.GroupID, g.ProfileID, p.ParentUser
from pUserMatch u
  inner join pGroupMatch g
    on u.GroupID = g.GroupID
  inner join ProfileData p
    on g.ProfileID = p.ID
 where usermatch.username = <"username">

Use "left outer join" instead of "inner join" if you want to see all the records of the first table (i.e. pUserMatch) even if there's no the linked record on the other one (i.e. pGroupMatch).


Selecting with varbinary(max) criteria (in the where clause)

Just do

SELECT ItemId
FROM   MyTable
WHERE  Value = 0xAAFF

You are converting the varchar representation to varbinary(max) which is not correct.

SELECT CONVERT(VARBINARY(MAX), '0xAAFF') returns 0x307841414646 for me for example. It will give you a result based on the character codes in that string representation in your default collation's code page.


How to split tables without affecting all stored procedures

I may be missing something, but instead of splitting tables, have you considered parameterized filters?

  Parameterized filters allow different partitions of data to be sent to
  different Subscribers without requiring multiple publications to be
  created. For example, a table can be filtered so that data for a given
  sales representative is replicated only to that representative.
  Parameterized filters have a variety of options that allow you to
  tailor filtering to optimize performance and best match your data and
  application requirements. For more information, see Parameterized Row
  Filters.


Is there a way to see if a SQL 2012 Replication publication is marked for initilazation and a way to unmark it?

Microsoft has some documentation on Initializing and Reinitializing Transactional Replication.

Initialize a Subscription with a Snapshot

Initialize a Transactional Subscription Without a Snapshot

Reinitialize Subscriptions

Snapshot Options

From your concern over lost data, you might want to investigate the Snapshot Options. Note this option: Specify an alternate snapshot folder location instead of or in addition to the default snapshot folder location.


How Can I Convert SQL Server 2012 Version 11.0.3000.0 to SQL Server 2012 Version 11.1.3000.0?

It looks like you are already at Service Pack 1 (this is why MSSQLSERVER is checked and greyed out). 11.0.3000 and 11.1.3000 are essentially the same thing, different files just have different reasons for exposing a different number in the middle - that number is really not relevant to your problem.

So now you just need to download and install the latest CU or, if you really want CU #4 specifically, you can download that here (just keep in mind  you are missing a boatload of fixes if you limit yourself to that update).

P.S. you check the version of SQL Server by opening a query window and running:

SELECT @@VERSION;

The method you're using now checks the version of Management Studio - these are client tools that can be older or newer than the server service itself.


First metric in each time "bucket" for multiple IDs

Thanks to k_rus for suggesting to use the TimescaleDB function time_bucket(). The below query is significantly faster:
SELECT vehicle_id, submitted_at, fuel
FROM (VALUES (1), (3), (4), (34), (44), (56), (76), (79), (81), (83)) vehicle_ids(v)
         CROSS JOIN LATERAL (SELECT DISTINCT ON (vehicle_id, bucket) vehicle_id,
                                                                     time_bucket('79488 seconds',
                                                                                '2020-11-17T00:00:00'::timestamp) AS bucket,
                                                                     submitted_at,
                                                                     fuel

                             FROM vehicle_fuel
                             WHERE vehicle_id = v
                               AND submitted_at BETWEEN '2020-05-17T00:00:00'::timestamp AND '2020-11-17T00:00:00'::timestamp
                             ORDER BY vehicle_id, bucket DESC, submitted_at DESC
                             LIMIT 200) d
ORDER BY vehicle_id, submitted_at DESC;


mysqlslap chokes on strings that contain ";" delimiter?

Looking briefly at the source code, it looks like mysqlslap doesn't do any sophisticated parsing for delimiters... it's just splitting strings.

If you write each of your queries on a single line, you don't have to terminate them -- the default statement delimiter is a newline.

const char *delimiter= "\n";

With one query per line, it splits the line and executes each statement.

The ; delimiter isn't actually needed at the end of each query to make the --query file valid and the server has no problem executing the example query you provided with the semicolon removed.

You could modify the output file from mysqldump to use a different delimiter, like $$, using sed or perl and then use that delimiter in your query file also.

mysqldump [args] | perl -pe 's/;$/\n\$\$\n/g' > create.sql

edit: oops, yeah, this is really primitive... multi-characterbyte delimiters aren't really even supported.

if (user_supplied_query)
    actual_queries= parse_delimiter(tmp_string, &query_statements, delimiter[0]);


Check Constraints on SQL Server Database advisable for number range & year & month?

Yes, use check constraints

Yes, you should add check constraints in your database to ensure valid values, as well as checking in your app. The database side makes for a double-check in case your app’s code has a hole or bug. Also, you may use other apps such as db admin tools to interact with your data, so it is good to have the database enforce the rules.

This kind of data validation is exactly the purpose of the check constraints.

Check year and month too

By the way, your year and month columns should not be of data type DATE. Use a small int for them too, and add constraint checks for reasonable year values such as >2016 and for months being 1-12.

ISO 8601

Also consider storing your year-month values as a single column of type text/varchar, formatted according to the ISO 8601 standard: YYYY-MM such as 2017-03. Sorting alphabetically happens to also be chronological.

By the way, Java includes a YearMonth class to represent this kind of value. That class can generate and parse that ISO 8601 formatted string by default with its toString and parse methods.

Avoid keywords as names

Avoid using any of the over one thousand keywords and reserved words used by various  databases. Your "year" and "month" names may be problematic.

Simple solution I use is to append a trailing underscore in all my SQL names (catalog, schema, table, column, index, etc.). So, year_ and month_. The SQL spec explicitly promises that no keyword will ever have a trailing underscore.


How to update a column with values from the same newly inserted row in a trigger?

Try this

create trigger utr_ValInfo on Table1
after insert as
begin

    declare @size int = 4;

    UPDATE Table1

    SET
         Tab1_valInfo  = right('0000' + convert(varchar, i.Tab1_val1), @size)
            + '-' +
             right('0000' + convert(varchar, i.Tab1_val2), @size)

    FROM Inserted i

    WHERE
         Table1.Tab1_id= i.Tab1_id;
end;

and don't declare varchar in convert without length


How to back up an entire database to text files

The following is not a good backup solution. Unless you’re quitting your job and feel like pranking your boss on the way out the door, you shouldn’t use it. But I learned a few things and had fun writing it, hopefully other people will, too. We are going to use some ETL concepts that might actually be useful in other circumstances. The full scripts are at the bottom.
Here’s what we are going to do:

Write the Schema to its own table
Write each table in the database to its own dynamically named text file
Re-create the Schema to a new DB from the text file
Re-create & Bulk Insert each table from its text file

Before we begin:
To do the whole DB, you will need several hundred GB of storage. If you’re too chicken, you can toss in ‘top 1000’s on the script to limit the size of the .txt files, then you won’t have to explain to your boss how you filled up C:\

Enable xp_cmdshell - explained here:

https://blog.sqlauthority.com/2007/04/26/sql-server-enable-xp_cmdshell-using-sp_configure/

Write the schema to a text file

We are going to check and see if the table already exists (If we’ve run the script before). It’s easier to just drop the table every time.

    if exists (select name from sys.tables st with (Nolock) where name = 'HeaderTable')

    begin
    drop table HeaderTable
    end

    SELECT
        st.name, sc.name 'Column_Name', t.Name 'Data_type',sc.max_length 'Max_Length',
        sc.precision, sc.scale, sc.is_nullable
        into HeaderTable
        FROM
        sys.tables st
        inner join sys.columns sc on sc.object_id = st.object_id
        INNER JOIN sys.types t ON sc.user_type_id = t.user_type_id

Now, if you do a quick select * from HeaderTable, you’ll see every table, every column, its data type and max_length - everything we will need to recreate it.

Write each table in the database to its own dynamically named text file

Here’s where it starts to get cool. We are going to use a cursor to loop through sys.tables and dump each one into its own .txt.

We are going to use a bunch of variables:

@table is going into the cursor. It will hold the names of each table as we go.

@Database, @filepath, @filename, @filetype are all going to be used to build a set of dynamic SQL statements.

@sql is going to hold our final SQL commands, to put to sp_executesql.

Things get a bit tricky with the delimiters and row terminators. If you use the defaults of | and /r, you’re going to have a real hard time with the Comments table. We are going to have to use something that we know isn’t used anywhere in the StackOverflow database. You could use newid(), rocket ships & googly eyes or you could use your favorite nursery rhyme. Anything, so long as it isn’t on StackOverflow already.

Here’s the script:

    declare @table varchar(255),
    @Database varchar(255),
    @filepath varchar(255),
    @filename varchar(255),
    @filetype varchar(255),
    @sql nvarchar(max),
    @delimiter varchar(255),
    @rowterminator varchar(255)

    set @Database = 'StackOverflow'
    set @filepath = 'C:\Data\' -- fix pretty-print mishandling of not-truly escaped '
    set @filetype = '.txt'
    set @delimiter = 'WhimmyWhammyWozzle'
    set @rowterminator = 'WubaLubaDubDub'

    declare c cursor local for

    select name from sys.tables with (Nolock)

    open c

    fetch from c into @table

    while @@FETCH_STATUS = 0
    begin

    SET @filename = @table

    --output to txt
    set @sql = N'declare @bcp varchar(4000)
    set @bcp = ''bcp " select top 10000 * from ' + @table + ' " queryout '
    + @filepath +  @filename + @filetype + ' -t "' + @delimiter + '" -r "'
     + @rowterminator + '" -c -T -d ' + @Database + '''
    print @bcp

    EXECUTE master.dbo.xp_cmdshell @BCP'

    print @sql
    --exec sp_executesql @sql

    fetch next from c into @table
    end

    close c

    deallocate c

Note that I’ve put the safety on in case you just pasted it in and hit F5. Not that anyone would ever do that, right? exec sp_executesql @sql won’t run until you remove the commenters. I’ve also included a top 10000.

Go to your file path, and you should see a bunch of text files.

Go ahead and open one up and change some data. If you think opening them manually is for peasants, you can use Fart.exe to find and replace in all of the text files.

Re-Write the Header

Go ahead and create a new database.

We are going to hard code the recreation of the header table, and use it to rebuild the rest.

restore header:

    if exists (select name from sys.tables where name = 'HeaderTable')
    begin
    drop table HeaderTable
    end

    create table HeaderTable
    (Table_Name varchar(255),
    Column_Name  varchar(255),
    Data_type  varchar(255),
    Max_Length  varchar(255),
    precision  varchar(255),
    scale varchar(255),
    is_nullable  varchar(255))

And now, we are going to bulk insert our Schema into HeaderTable:

    set @sql = 'BULK INSERT HeaderTable FROM ''' + @filepath + 'HeaderTable'+ @filetype + ''' WITH (FIELDTERMINATOR = '''
     + @delimiter + ''', ROWTERMINATOR = ''' + @rowterminator + ''')'
    print @sql
    --exec sp_executesql @sql

    We will have to tidy it up a bit, to make the next steps easier:

    update HeaderTable
    set Max_Length = 'max'
    where Max_length = -1

    update HeaderTable
    set Max_Length = '(' + Max_Length + ')'

    update HeaderTable
    set Max_Length = ''
    where Data_type in ( 'int', 'bigint', 'smallint', 'tinyint',
    'date','datetime', 'uniqueidentifier', 'sysname', 'bit')

Re-create & Bulk Insert each table from its text file

And here’s where things get cool again. We are going to loop through HeaderTable and re-create each table, concatenating the Create statement with STUFF(). Don’t ask me how stuff() works - an old coworker (Mike Ignatoski) gave this to me years ago. Reliable sources say he originally got it from some guy named Solomon.

    declare @table varchar(255),
    @column_string nvarchar(max),
    @sql nvarchar(max),
    @string nvarchar(max),
    @filepath varchar(255),
    @filename varchar(255),
    @filetype varchar(255),
    @sql nvarchar(max),
    @delimiter varchar(255),
    @rowterminator varchar(255)

    set @filepath = 'C:\Data\' -- fix pretty-print mishandling of not-truly escaped '
    set @filetype = '.txt'
    set @delimiter = 'WhimmyWhammyWozzle'
    set @rowterminator = 'WubaLubaDubDub'

    declare c cursor local for

    select distinct Table_Name from HeaderTable
    where Table_Name != 'HeaderTable'

    open c
    fetch from c into @table

    while @@FETCH_STATUS = 0
    begin

    set @string = null

    set @string = (select stuff( (
    select ', ' + Column_Name + ' ' + Data_type  + Max_Length from HeaderTable
    where Table_Name = @table
    for xml path ('')),1,2,''))

    print @string

    set @sql =  ' if not exists (select top 1 name from sys.tables where name = ''' + @table + ''') begin
    create table ' + @table + ' (' + @string + ') end'

    print @sql
    exec sp_executesql @sql

    --populate the table
    set @sql = 'BULK INSERT ' + @table + ' FROM ''' + @filepath + @table + '.txt'' WITH (FIELDTERMINATOR = '''
    + @delimiter  + ''', ROWTERMINATOR = ''' + @rowterminator + ''' )'

    print @sql
    exec sp_executesql @sql

    fetch next from c into @table

    end

    close c

    deallocate c

And there you have it - your database has been restored from text files. You can consign the .bak file to the garbage pile of history! So long as you don’t have any functions, stored procedures, views, constraints or indexes.

Here are the full scripts:
Bad Idea Jeans Backup:

    declare @table varchar(255),
    @Database varchar(255),
    @filepath varchar(255),
    @filename varchar(255),
    @filetype varchar(255),
    @sql nvarchar(max),
    @delimiter varchar(255),
    @rowterminator varchar(255)

    set @Database = 'StackOverflow'
    set @filepath = 'C:\Data\' -- fix pretty-print mishandling of not-truly escaped '
    set @filetype = '.txt'
    set @delimiter = 'WhimmyWhammyWozzle'
    set @rowterminator = 'WubaLubaDubDub'

    --create database header

    if exists (select name from sys.tables st with (Nolock) where name = 'HeaderTable')

    begin
    drop table HeaderTable
    end

    SELECT
        st.name, sc.name 'Column_Name', t.Name 'Data_type',sc.max_length 'Max_Length',
        sc.precision, sc.scale, sc.is_nullable
        into HeaderTable
        FROM
        sys.tables st
        inner join sys.columns sc on sc.object_id = st.object_id
        INNER JOIN sys.types t ON sc.user_type_id = t.user_type_id

        select * from HeaderTable

    declare c cursor local for

    select name from sys.tables so with (Nolock)

    open c

    fetch from c into @table

    while @@FETCH_STATUS = 0
    begin

    SET @filename = @table

    --output to txt
    set @sql = N'declare @bcp varchar(4000)
    set @bcp = ''bcp " select top 10000 * from ' + @table + ' " queryout '
    + @filepath +  @filename + @filetype + ' -t "' + @delimiter + '" -r "'
     + @rowterminator + '" -c -T -d ' + @Database + '''
    print @bcp

    EXECUTE master.dbo.xp_cmdshell @BCP'

    print @sql
     exec sp_executesql @sql

    fetch next from c into @table
    end

    close c

    deallocate c

Bad Idea Jeans Restore:

declare @table varchar(255),
    @column_string nvarchar(max),
    @sql nvarchar(max),
    @string nvarchar(max),
    @filepath varchar(255),
    @filename varchar(255),
    @filetype varchar(255),
    @delimiter varchar(255),
    @rowterminator varchar(255)

    set @filepath = 'C:\Data\' -- fix pretty-print mishandling of not-truly escaped '
    set @filetype = '.txt'
    set @delimiter = 'WhimmyWhammyWozzle'
    set @rowterminator = 'WubaLubaDubDub'

    --restore header
    if exists (select name from sys.tables where name = 'HeaderTable')
    begin
    drop table HeaderTable
    end

    create table HeaderTable
    (Table_Name varchar(255),
    Column_Name  varchar(255),
    Data_type  varchar(255),
    Max_Length  varchar(255),
    precision  varchar(255),
    scale varchar(255),
    is_nullable  varchar(255))

    set @sql = 'BULK INSERT HeaderTable FROM ''' + @filepath + 'HeaderTable'+ @filetype + ''' WITH (FIELDTERMINATOR = '''
     + @delimiter + ''', ROWTERMINATOR = ''' + @rowterminator + ''')'
    print @sql
    exec sp_executesql @sql

    --make some changes so that we can concatenate our create tables more easily
    update HeaderTable
    set Max_Length = 'max'
    where Max_length = -1

    update HeaderTable
    set Max_Length = '(' + Max_Length + ')'

    update HeaderTable
    set Max_Length = ''
    where Data_type in ( 'int', 'bigint', 'smallint', 'tinyint',
    'date','datetime', 'uniqueidentifier', 'sysname', 'bit')

    select * from HeaderTable

    --restore DB

    declare c cursor local for

    select distinct name from sys.columns
    where name != 'HeaderTable'

    open c
    fetch from c into @table

    while @@FETCH_STATUS = 0
    begin

    set @string = null

    set @string = (select stuff( (
    select ', ' + Column_Name + ' ' + Data_type  + Max_Length from HeaderTable
    where name = @table
    for xml path ('')),1,2,''))

    print @string

    set @sql =  ' if not exists (select top 1 name from sys.tables where name = ''' + @table + ''') begin
    create table ' + @table + ' (' + @string + ') end'

    print @sql
    --exec sp_executesql @sql

    set @sql = 'BULK INSERT ' + @table + ' FROM ' + '' + @filepath + @table + '.txt'' WITH (FIELDTERMINATOR = '''
    + @delimiter  + ''', ROWTERMINATOR = ''' + @rowterminator + ''' )'

    print @sql
    --exec sp_executesql @sql

    fetch next from c into @table

    end

    close c

    deallocate c


Partitioning could quite possibly help you in this situation.  By putting the data into a partition, the database will have a smaller table for each client to deal with.  This could help performance a lot.

Here is an example of how it would help.  The data in the table would otherwise be interleaved, so different clients would have rows on a data page.  Reading the data from a single client would then "clutter up" the available page cache with records from other clients.

However, it will not help very much if all the clients are accessing data concurrently, so all are competing for available memory.  Partitioning probably will not make this worse, but it won't necessarily help.

Also, MySQL automatically partitions the indexes on the table, which is generally a good thing.

Often partitioning is applied to a date field, so older data gets put into less used partitions.  This may be a case where another field is useful.  But, if all the clients need all their data at the same time, then the partitioning may not give you much of a gain.


Optimizing MariaDB for 5-10K small databases

Your output for GLOBAL_STATUS is showing:

opened_tables 2098690

The definition for this status is defined as:

  Number of tables the server has opened...

Reference: Opened_tables (MariaDB documentation)

Your configuration for innodb_file_per_table is set to:

innodb_file_per_table   ON

And MariaDB defines this setting to be:

  If set to ON, then new InnoDB tables are created with their own InnoDB file-per-table tablespaces. If set to OFF, then new tables are created in the InnoDB system tablespace instead. Page compression is only available with file-per-table tablespaces. Note that this value is also used when a table is re-created with an ALTER TABLE which requires a table copy.

Reference: innodb_file_per_table (MariaDB documentation)

So basically, you have 2'098'690 open files on your Ubuntu Server, but your settings for innodb_open_files is set to 2000:

innodb_open_files   2000

Now there could be an issue that Ubunutu is hitting a limit or your MariaDB is in some race condition opening and closing the database/table files.

The 2 million + open files is a counter and there is a recommendation that if your counter keeps on increasing that:

  If the number of opened tables is increasing rapidly, you should look at increasing the table_open_cache value. Try to find a value that sees a slow, or possibly even no, increase in the number of opened tables.

Reference: Optimizing table_open_cache (MariaDB documentation)

Additionally you might want to have a look at your Ubunutu instance and see what limits are set for system-wide resources:

 bash#> ulimit -a

This will return a list of currently set limits. You might have to increase these limits to achieve a stable MariaDB environment. The one setting being:

-n   The maximum number of open file descriptors.

Reference: ulimit (SS64.com)

You can set this setting according to this Q&A:

How to increase max open files limit on Ubuntu 18.04? (Ask Ubuntu)


Can I add 1 primary node in existing cluster?

The architecture of an availability group is that there is one primary node that owns all databases in the availability group.  A listener has an IP address, and it would be impossible to have that IP address online on multiple nodes at once.

The only way you can use another node (NodeC) as a primary while the current node (NodeA) is also a primary is to create an additional availability group.  You could set up this new availability group so that only NodeC and NodeB are server replicas, or, you could also include NodeA as a replica as well if desired.


If you have the full and transaction log backups, you can use a log reading tool like Quest Litespeed or ApexSQL Log Reader. (Disclaimer: I used to work for Quest.) Those read the full backup, then the logs, in order to build a chain of what happened. You can search for transactions by syntax, table name, etc, and generate undo scripts.

Here's the catch, though: if it was a shared login, like a SQL login where multiple people knew the password, you're going to have a tough time pinning down who did it.


Turning multiple fields in column to row

You're looking for a pivot. Either of these queries will work: SqlFiddle

/* case */

select
      CustomerId
    , FirstName =max(case when [Key]= 'FirstName' then Value end)
    , LastName  =max(case when [Key]= 'SecondName'  then Value end)
    , Age       =max(case when [Key]= 'Age'       then Value end)
    , Gender    =max(case when [Key]= 'Gender'    then Value end)
from Customers
group by CustomerId

/* pivot */

select CustomerId, FirstName, SecondName, Age, Gender
  from (Select CustomerId, [Key], Value from Customers) c
    pivot ( max(Value)
      for [Key] in (FirstName, SecondName, Age, Gender)
    ) as p

Schema Setup for SqlFiddle:

    create table Customers (
    id int identity (1,1) not null primary key
  , CustomerID int not null
  , [Key] varchar(32) not null
  , Value varchar(32)
  )

insert into Customers (CustomerId, [Key], Value) values
    (2,'FirstName','Tim')
  , (2,'SecondName','Skold')
  , (2,'Age','48')
  , (2,'Gender','Male')
  , (3,'FirstName','Sql')
  , (3,'SecondName','Zim')
  , (3,'Age','32')
  , (3,'Gender','Male')

Links

Technet - Using PIVOT and UNPIVOT
Cross Tabs and Pivots, Part 1 - Jeff Moden


Behavior of case sensitivity of RAISERROR when run remotely

This issue may actually be related to a very old bug with SQL Server, but not directly caused by SQL Server itself.

I found this MSDN thread which describes the exact same behavior

  Please note the strangeness of this: RAISERROR won't compile (connection time out), even if it is in a comment!

This was the accepted answer

  It turns out that the network that our SQL Server is on has an intrusion prevention system.  In that system there is a filter with the following description:

  This filter detects access to the raiserror procedure on port 1433. The raiserror procedure in a Microsoft SQL Server contains a buffer overflow vulnerability, which can be remotely exploited to execute arbitrary computer code on the affected system, thus allowing an attacker to gain complete control of the server. The procedures known to be vulnerable are: xp_sprintf, raiseerror() and formatmessage(). If the overruns are exploited, the code runs in the context of a local administrator account.

There is an old MS Security Bulletin for this bug and a more recent TechNet thread describing the same issue.

It's possible there is something on our network that I am not aware of that is filtering out anything with the text raiserror in it (but doing a poor job of it since the check is case sensitive). Not that it matters since the bug was fixed.

Since this isn't recognized behavior of SQL Server then it could likely be a network configuration issue.


Help with a table having fixed number of self-referencing columns

First approach seems to be more logical.
Because ManyToOne usually stored in same table.


I cannot tell for sure, but there is a hint in the CREATE FUNCTION documentation:

  langname
  The name of the language that the function is implemented in. May be SQL, C, internal, or the name of a user-defined procedural language. See CREATE LANGUAGE for the procedural languages supported in Greenplum Database.

In turn, the referenced place says

  The PL/pgSQL language is installed by default in Greenplum Database.

Now the EXECUTE ... USING is a PL/pgSQL feature, so the only question is which PostgreSQL version it comes from.  The documentation points to 8.2 - and this way it looks like you are out of luck.

I've contacted the Greenplum support to get a definitive answer, but never got an answer.

As for working around the lack of that construct, you can obviously concatenate together any SQL string you want and execute it.  Be careful to correctly quote the passed-in values and avoid SQL injection.


Refreshing a stored generated column in Postgres

The only way is to rewrite the whole table with a no-operation update like:

UPDATE people SET first_name = first_name;

That will re-evaluate the function.

You may want to run VACUUM (FULL) on the table afterwards to reclain the wasted space.


What is the difference between SET NOEXEC ON and SET PARSEONLY ON?

The order of the tasks in executing a SQL query are parsed, compile and execute.
PARSE
SQL Server parses the syntax of a T-SQL. For a successful query execution. It should have valid syntax. It also checks for the variable declaration, query identifiers.
COMPILE
SQL Server checks for the objects, whether they exist or not. It also checks user permissions. During the compilation phase, SQL Server also optimizes the query execution and comes with an optimized execution plan using available indexes and statistics
EXECUTE
Once SQL Server completed parse and compilation, SQL Server executes query batch and returns output to the client
So if you have the following settings the engine do parse and compile tasks but not execute.
SET NOEXEC ON
SET PARSEONLY OFF


TSQL: Find Queries causing too many SQL Compilations and SQL ReCompilations seperately

I don't think you can find this by an easy way but it is possible anyway to get through this. Profiler offers many event class types that can be used in analyzing the performance of a query. Start a new Profiler session and check following events:

Performance: Performance statistics
Stored Procedures: RPC:Completed
TSQL: SQL:BatchCompleted
TSQL: SQL: BatchStarting

Check to Show all columns and select each one of the columns under Performance: Performance statistics event only. The rest of events can be left with default setting.

Next, Select Column Filters and filter by DatabaseName and/or LoginName/ApplicationName/HostName etc.., if you know them. The purpose is to limit the number of rows dispalyed in Profiler and concentrate only on your needs.

Next, press Run and let it run for a while (2-3 min as long as you need).
Analyse the results dispalyed looking primarily at: Performance statistics event.

If Performance Statistics will occur often it means that the plan of a query was cached for the first time, compiled, re-compiled or evicted from PlanCache. From my knowledge if a query does not have its query plan in Plan Cache - you will see 2 rows of PerformanceStatistics event and followed by SQL:BatchStarting, then SQL:BatchCompleted. It means that the Query Plan was first compiled, cached and then the query started and completed.

Look at following columns under Performance Statistics event:

SPID - ID of the session on which the event occurred. You can use it to identify the
       row on SQL:BatchCompleted event which will display the SQL Query text and other
       usefull information (Read/Writes, StartTime/EndTime)
Duration - Total time, in microseconds, spent during compilation.
EventSubClass - 0 = New batch SQL text that is not currently present in the cache.
                1 = Queries within a stored procedure have been compiled.
                2 = Queries within an ad hoc SQL statement have been compiled.
                3 = A cached query has been destroyed and the historical performance
                    data associated with the plan is about to be destroyed.
                4 = A cached stored procedure has been removed from the cache and the
                    historical performance data associated with it is about to be
                    destroyed.

                5 = A cached trigger has been removed from the cache and the historical
                    performance data associated with it is about to be destroyed.

Considering the EventSubClass number you can find out what happened with the Query Plan and take specific measures. Additionally you can add other columns to Stored Procedures and TSQL Event Classes if you are interseted in HostName, WindowsUser or other info from Profiler trace.
Also the trace can be stored in a SQL table making the analyse more easy and much more customizable. Here is a link describing more the Performance Statistics Event Class.


By default, the DTA doesn't recommend clustered indexes. In addition, check out the documentation for even more things it doesn't do.

If you do want recommendations for clustered indexes, click the radio button for "Do not keep any existing PDS" as shown in the below screenshot:

But be aware that it can produce some stunningly bad clustered index recommendations. The DTA isn't a replacement for basic data modeling for how to store your data.

Generally, your clustered indexes should follow the SUN-E method:

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


Display all parent records but only one record of child records of a parent record

Assuming "only the first record of those" means ordered by Id you can use rank() partitioned by ParentId and a case statement in the order by clause.

select T.Id,
       T.ParentId,
       T.Item
from (
     select T.Id,
            T.ParentId,
            T.Item,
            rank() over(partition by T.ParentId
                        order by case when T.ParentID is not null
                                   then T.Id
                                   else null
                                 end ) as rn
     from @t as T
     ) as T
where T.rn = 1;


Migrating from Oracle to MySQL via MySQL workbench: Table list is empty

Like @bauerInHsv said, Oracle has a vested interest in providing minimal support for migrating off of its enterprise offering (Oracle) and onto its open-source RDBMS (MySQL).

As an alternative to MySQL Workbench, you could use etlalchemy.

It is an open-source Python tool, that lets you migrate between any 2 relational database with 4 lines of Python.

To install:

pip install etlalchemy
# On El Capitan:
#### pip install etlalchemy --ignore-installed

To run:

from etlalchemy import ETLAlchemySource, ETLAlchemyTarget

oracle_db_source = ETLAlchemySource("oracle+cx_oracle://username:password@hostname/SID")

mysql_db_target = ETLAlchemyTarget("mysql://username:password@hostname/db_name",
                                       drop_database=True)
mysql_db_target.addSource(oracle_db_source)
mysql_db_target.migrate()

This will handle the migration in the following order:

Migrating the Schema
Migrating the Data
Migrating the Indexes
Migration the Constraints

(P.S. I wrote this tool, so feel free to reach out if you find something broken.)


One of the key differences between these platforms is that a database in MySQL is a schema in Oracle.  Since a schema in Oracle is also a user, the command you are looking for is CREATE USER.  I highly recommend that you read the Oracle Concepts Guide before continuing.  You may also find this documentation on MySQL>>Oracle migrations using SQL Developer useful.  Here is the part relevant to your situation:

  When migrating MySQL databases to Oracle, SQL Developer maps each
  MySQL database to a tablespace in Oracle. Database objects, such as
  tables, indexes and views are stored in the respective tablespaces and
  are referenced from the Oracle schema for the user that owns them.

From the error message perspective, the salient part to pull out is "ORA-01100: database already mounted".  This indicates that a database has already been created and mounted.  It may also be open.  Here is an example reproducing your issue, opening the database (which may not be necessary if it is already open) and creating a django user.

SQL> create database django;
create database django
*
ERROR at line 1:
ORA-01501: CREATE DATABASE failed
ORA-01100: database already mounted

SQL> SELECT status FROM v$instance;

STATUS
------------
MOUNTED

SQL> ALTER DATABASE OPEN;

Database altered.

SQL> SELECT status FROM v$instance;

STATUS
------------
OPEN

SQL> create database django;
create database django
*
ERROR at line 1:
ORA-01501: CREATE DATABASE failed
ORA-01100: database already mounted

SQL> CREATE USER django IDENTIFIED BY "No More ORA-001100.";

User created.

You should also assign permissions and create a django tablespace setting it as the default tablespace for django.


How to design table for role-based security with special Update actions

Just in keeping with the KISS principle, first, make a list of all actions:

create table Actions(
  ID     int auto_generate primary key,
  Name   varchar( 16 ) not null -- "Create", "Read", etc.
  ...    -- other data describing the action
);

Now create an intersection table between the user, the object and the actions. Nothing fancy, if a user is tied to an action, they can perform that action. If not, they can't.

create table UserActions(
    UserID   int not null references Users( ID ),
    ObjectID int not null references Objects( ID ),
    ActionID int not null references Actions( ID ),
    constraint PK_UserActions primary key( UserID, ObjectID, ActionID )
);

The PK prevents multiple entries of the same User/Object/Action combination. If there in an entry for a particular user and a particular object with, say, the id of READ, then that user can perform that action on that object. Otherwise, they can't. Simple and scalable. And thoroughly normalized.


Where can I find a syntax reference for Oracle SQL Developer's "Generate DB Doc" function?

Community wiki answer initially based on comments left by thatjeffsmith:

This is an exhaustive list of what it supports:

(reproduced from http://pldoc.sourceforge.net/maven-site/samples/sample1.sql)

CREATE OR REPLACE
PACKAGE CUSTOMER_DATA
IS
/**
* Project:         Test Project (PLDoc)

* Description:     Customer Data Management

* DB impact:       YES

* Commit inside:   NO

* Rollback inside: NO

* @headcom
*/

/**
* Record of customer data.
*
* @param id     customer ID
* @param name       customer name
* @param regno      registration number or SSN
* @param language   preferred language
*/
TYPE customer_type IS RECORD (
  id                        VARCHAR2(20),
  name                      VARCHAR2(100),
  regno                     VARCHAR2(50),
  language                  VARCHAR2(10)
);

/** Table of customer records. */
TYPE customer_table IS TABLE OF customer_type INDEX BY BINARY_INTEGER;

/**
* Gets customer by ID.
*
* @param p_id       customer ID
* @param r          record of customer data
* @throws no_data_found if no such customer exists
*/
PROCEDURE get_customer (
  p_id              VARCHAR2,
  customer_rec      OUT customer_type);

/**
* Searches customer by criteria.
*
* @param p_criteria record with assigned search criteria
* @param r_records  table of found customers (may be empty!)
*/
PROCEDURE get_by_criteria (
  p_criteria        customer_type,
  r_records         OUT customer_table);

/**
* Creates a customer record.
*
* @param customer_rec record of customer data
*/
PROCEDURE create_customer (
  customer_rec      customer_type);

/**
* Changes customer data.
*
* @param customer_rec record of updated customer data
*/
PROCEDURE update_customer (
  customer_rec      customer_type);

END;
/

We support everything in PLDOC - we just have a GUI vs a CLI for it. There are three code samples there, you should be able to do anything listed in those samples.


Where is SQLCMD.EXE in SQL Server 2014 LocalDB or how to install it?

I have figured out that during SQL Server LocalDB MSI installation the ODBC driver is not installed

So to use SQLCMD it is necessary to install
Microsoft® ODBC Driver 11 for SQL Server® - it is a part of ODBC driver as per here.

When I tried to install Microsoft® Command Line Utilities 11 for SQL Server® it prompted me to install the ODBC Driver first.

Then it is possible either to copy the files SQLCMD.EXE, BATCHPARSER.DLL and SQLCMD.RLL or to install Microsoft® Command Line Utilities 11 for SQL Server®.


Is there a way to backup a mysql database without having to run mysqldump?

Is there a way to run a backup of a mysql database using sql?

Short answer: No.

  ... scripts ... only have mysql user credentials, not system login credentials.

I'm pretty sure it's possible to run the mysqldump utility against a remote database.

However, I would have to ask "Why?"

Even if you could take a backup, what would you hope to do with it?

It seems to me that you don't have the tools or the credentials necessary to take proper backups (or to restore a database, which pretty much has to be done on the machine itself).  As such, I would guess that someone else is responsible for keeping these databases up and running, recoverable and well-tuned (at least I hope so, or your organisation is in Big Trouble).

Perhaps you should discuss options with them?


Three tables joined by a many-to-many relationship?

I was just going to make this a comment but it is too long to just be a comment, and I think it is one viable solution though I would say that there are many solutions and it will depend on how large the tables are going to be, how frequently they are used, how they are used, etc...as to what is going to be the best for your situation.

In this case, I think having your Type, Status and SubStatus tables all be separate and having a table that links them together, seems to make sense.

Here is the code that I put together to make that happen for your data.

create table tblType
(
    TypeID int identity(1,1) primary key clustered
    ,TypeName varchar(20)
);
GO
Create table tblStatus
(
    StatusID int identity(1,1) primary key clustered
    ,StatusName varchar(20)
);
GO
Create table tblSubStatus
(
    SubStatusID int identity(1,1) primary key clustered
    ,SubStatusName varchar(20)
);
GO
create table StatusSubStatus
(
    StatusSubStatusID int identity(1,1) primary key clustered
    ,TypeID int
    ,StatusID int
    ,SubStatusID int
);
GO
ALTER TABLE StatusSubStatus ADD CONSTRAINT fk_Type
FOREIGN KEY (TypeID) REFERENCES tblType(TypeID);
GO
ALTER TABLE StatusSubStatus ADD CONSTRAINT fk_Status
FOREIGN KEY (StatusID) REFERENCES tblStatus(StatusID);
GO
ALTER TABLE StatusSubStatus ADD CONSTRAINT fk_SubStatus
FOREIGN KEY (SubStatusID) REFERENCES StatusSubStatus(StatusSubStatusID);
GO
insert into tblType
Values ('Type 1'), ('Type2'); --id 1, 2
GO
insert into tblStatus
Values ('A'); --id 1
GO
insert into tblSubStatus
Values ('X'), ('Y'), ('Z');  --id's 1, 2, 3 respectively
GO
insert into StatusSubStatus
Values (1, 1, 1), (1, 1, 2), (2, 1, 3);
GO

The advantage to this is that you get to keep your Status/Type tables from being large.  The disadvantage is that you have a linking table that can get quite large.   But it is a clean way to get all of this stuff together cleanly.

If you wanted to see all of the data for TypeID of 1 you would just do the query

Select TypeName, StatusName, SubStatusName
from StatusSubStatus sss
join tblType tt on sss.TypeID = tt.TypeID
join tblStatus ts on sss.StatusID = ts.StatusID
join tblSubStatus tss on tss.SubStatusID = sss.SubStatusID
Where tt.TypeID = 1;

There are many solutions to making this work, this is one that I like but it will certainly depend on your situation as to whether this is the best one for you or not.


Explain
We would have to see table definition, cardinalities and the EXPLAIN output to be certain, but the reason is most likely this:
Only your spatial GiST index on a.geom can be used. The btree index is not applicable. Postgres walks through the "closest" rows until it finds the first two matching your predicate.
Normally, more restrictive conditions make queries faster, since fewer rows have to be fetched (and sorted). But not in this constellation:
Fewer qualifying rows don't help a nearest neighbour search with a small LIMIT - au contrair. There are two options:

The "nearest neighbour" search based on the GiST index - which is fast as long as some of the closest rows qualify - fastest if you remove the WHERE clause altogether, try it! It gets increasingly expensive if many rows have to be filtered. Basically: the longer your pattern the more expensive the query.

A sequential scan, which has to solve the same problem for selective conditions: filter many rows until finding some candidates. The method is faster, but the whole table has to be read, and if more than a few rows are found, the final sort by distance gets expensive.

Basically, Postgres is stuck between a rock and a hard place with this wicked query.
Fix
You would need an index on unaccent(lower(name)) which is not possible, because unaccent() is only STABLE, not IMMUTABLE. But you can work around this limitation with an IMMUTABLE function wrapper - done right:

Does PostgreSQL support “accent insensitive” collations?

The best index still seems tricky and depends on your complete situation.
Since you match left-anchored patterns, I would try a separate btree index with the text_pattern_ops operator class, using the function f_unaccent() outlined in the linked answer above:
CREATE INDEX a_name_pattern_idx ON a (f_unaccent(lower(name)) text_pattern_ops);

More:

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

Then:

Rewrite the WHERE condition to match the indexed expression
Make the wildcard to the right of the expression explicit to make it work for prepared statements.
Don't concatenation text parameters into your query to begin with, that's an invitation for sneaky errors and SQL injection. Use a prepared statement:

$sql = 'SELECT a.name, ST_AsText(a.geom), b.name AS name_b, b.id
        FROM   a
        LEFT   JOIN b ON b.id = a.b_id
        WHERE  f_unaccent(lower(a.name)) LIKE (f_unaccent(lower($1)) || $$%$$)
        ORDER  BY a.geom <-> ST_GeomFromText($$POINT($2 $3)$$, 4326)
        LIMIT  2';

$result = pg_query_params($sql, array($searched, $lat, $lon));

I use dollar-quoting to avoid escaping single quotes.
For repeated calls use pg_prepare and pg_execute. Details:

Problems with my attempt to implement an UPSERT

Or you could try a GiST index - a multicolumn, functional GiST index combining a PostGis spatial GiST index and a functional trigram GiST index. I didn't test that combination, yet.
You need to install pg_trgm first. Instructions:

How is LIKE implemented?

CREATE INDEX a_special_gist_idx ON a
USING gist (f_unaccent(lower(name)) gist_trgm_ops, geom);

I put f_unaccent(lower(name)) first because, per documentation:

A multicolumn GiST index can be used with query conditions that
involve any subset of the index's columns. Conditions on additional
columns restrict the entries returned by the index, but the condition
on the first column is the most important one for determining how much
of the index needs to be scanned. A GiST index will be relatively
ineffective if its first column has only a few distinct values, even
if there are many distinct values in additional columns.


SQL Server 2016 Express, need to add account with Administrator access to a database

If you are a Windows Administrator, follow the procedure documented here.

Or simply run this script from an elevated command prompt on the SQL Server:

net stop mssql$sqlexpress
net start mssql$sqlexpress /mSQLCMD
sqlcmd -S (local)\sqlexpress -Q "if not exists(select * from sys.server_principals where name='BUILTIN\administrators') CREATE LOGIN [BUILTIN\administrators] FROM WINDOWS;EXEC master..sp_addsrvrolemember @loginame = N'BUILTIN\administrators', @rolename = N'sysadmin'"
net stop mssql$sqlexpress
net start mssql$sqlexpress
sqlcmd -S (local)\sqlexpress -Q "if exists( select * from fn_my_permissions(NULL, 'SERVER') where permission_name = 'CONTROL SERVER') print 'You are a sysadmin.'"

Source


thanks for the reply. The migration tool generates a report. In it's report, the migration tool listed SSIS as a feature that had to be deleted, it also indicated all maintenance plans had to be deleted. SSIS afaik was not installed and did not show in the features when I checked programs and features. This confused me. I had left the maintenance plans on while running the tool since we needed them. On a hunch I deleted the maintenance plans and SSIS was no longer listed in the report as a problem feature. The maintenance plans were backups, either they used SSIS somehow even with it not installed, or the MS's migration tool was wrong. In any case, with the maintenance plans delete I no longer have the problem.


migrating from a local PostgreSQL database to Amazon Aurora

As per @Pavel Tsiukhtsiayeu blog documentation Migrating PostgreSQL standalone instance to Aurora (Amazon RDS) The idea behind migration is simple: create Aurora cluster in RDS, setup database source and target endpoints at DMS, and launch database migration task. The data migration task will migrate all data from the source database, and replicate ongoing changes to the target endpoint. All you have to do is to point your web server to the new Aurora cluster, and hot reload web server once the source and target endpoints completely in sync.

For further your reference here


Why does CCI creation on an empty table request a huge memory grant?

There's a hint about this in the documentation for CCI query performance:

Plan for enough memory to create columnstore indexes in parallel
Creating a columnstore index is by default a parallel operation unless memory is constrained. Creating the index in parallel requires more memory than creating the index serially. When there is ample memory, creating a columnstore index takes on the order of 1.5 times as long as building a B-tree on the same columns.
The memory required for creating a columnstore index depends on the number of columns, the number of string columns, the degree of parallelism (DOP), and the characteristics of the data. For example, if your table has fewer than one million rows, SQL Server will use only one thread to create the columnstore index.

That quote suggests that the number of rows in the table matters for DOP but doesn't matter for the memory grant. The memory required also depends on the number of columns so let's test with a table with fewer columns to more easily see the expected difference in memory grant. I'm using this table and index definition:
DROP TABLE IF EXISTS dbo.MY_SECOND_FACT_TABLE;

CREATE TABLE dbo.MY_SECOND_FACT_TABLE (
    ID BIGINT NOT NULL,
    COL1 BIGINT NULL
);

INSERT INTO dbo.MY_SECOND_FACT_TABLE WITH (TABLOCK) (ID, COL1)
SELECT TOP (0) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)), NULL
FROM master..spt_values t1
CROSS JOIN master..spt_values t2
OPTION (MAXDOP 1);

CREATE CLUSTERED COLUMNSTORE INDEX MY_SECOND_CCI ON dbo.MY_SECOND_FACT_TABLE WITH (MAXDOP = 1);

Below are results from a few tests:
╔══════════════════╦════════════════╦═════════════════╗
║ REQUESTED_MAXDOP ║ NUMBER_OF_ROWS ║ MEMORY_GRANT_KB ║
╠══════════════════╬════════════════╬═════════════════╣
║                1 ║              0 ║           89928 ║
║                2 ║              0 ║           89928 ║
║                1 ║        2000000 ║           89928 ║
║                2 ║        2000000 ║          179896 ║
║                1 ║        5000000 ║           89928 ║
║                2 ║        5000000 ║          179896 ║
╚══════════════════╩════════════════╩═════════════════╝

With 0 rows the DOP is downgraded to 1, but otherwise the number of rows doesn't appear to matter for the memory grant. This is undoubtedly a gross oversimplification, but perhaps SQL Server builds the CCI one rowgroup at a time per DOP and maintains the string dictionaries as it goes along. That sounds fine in practice and means that the memory grant won't scale with the number of rows in the table, but without a check for a very low number of rows SQL Server could request an excessive memory grant. The excessive memory grant wouldn't matter in most cases because the CREATE INDEX is so fast, but it can matter if SQL Server waits on the memory grant.
So now I have an idea of why the memory grant is so large, but what can be done about it? SQL Server 2014 introduced the syntax to define an index definition as part of the table definition. It is possible to write a single statement that creates both the table and the index:

INDEX index_name CLUSTERED COLUMNSTORE
Applies to: SQL Server 2014 through SQL Server 2016 and Azure SQL Database.
Specifies to store the entire table in columnar format with a clustered columnstore index. This always includes all columns in the table. The data is not sorted in alphabetical or numeric order since the rows are organized to gain columnstore compression benefits.

For the original table the syntax would look like this:
CREATE TABLE dbo.MY_FIRST_FACT_TABLE (
    ID BIGINT NOT NULL,
    COL1 BIGINT NULL,
    COL2 BIGINT NULL,
    COL3 BIGINT NULL,
    COL4 BIGINT NULL,
    COL5 BIGINT NULL,
    COL6 BIGINT NULL,
    COL7 BIGINT NULL,
    COL8 BIGINT NULL,
    COL9 BIGINT NULL,
    COL10 BIGINT NULL,
    STRING1 VARCHAR(100) NULL,
    STRING2 VARCHAR(100) NULL,
    STRING3 VARCHAR(100) NULL,
    STRING4 VARCHAR(100) NULL,
    STRING5 VARCHAR(100) NULL,
    INDEX MY_FIRST_CCI CLUSTERED COLUMNSTORE
);

A CREATE TABLE statement does not generate an actual plan so I can't immediately tell if this reduced the memory grant. I tried creating the table 1000 times while hammering the sys.dm_exec_query_memory_grants DMV but never got any results. That suggests that memory was not granted but isn't proof. I also tried extended events with query_memory_grant_usage and didn't get any results for the single CREATE TABLE statement.
Of course, the best way to test is to create the CCI the original workflow conditions with limited available memory. I did that and did not experience any memory timeouts.
I suppose it could be said that defining the CCI in the CREATE TABLE statement for a new table is a best practice. However, if your workload causes this error then changing the CREATE TABLE script probably isn't sufficient to address other problems that you'll run into, such as memory issues when you starting loading data into the CCIs.


That is not a supported syntax as a stored procedure can return multiple result sets or do other data manipulations and return nothing.

You would need to convert your stored procedure to a table valued function or insert the results into a temp table.


This may not be the most optimal way to skin the cat but when I need to combine different JSON results in a single output, I typically use the JSON_QUERY() function.
Here is one way to apply it in your case:
SELECT
  COLUMN_NAMES = JSON_QUERY(inf_sch.json_result)
, Notification = JSON_QUERY(notif  .json_result)
FROM
  (
    SELECT COLUMN_NAME
    FROM INFORMATION_SCHEMA.COLUMNS
    WHERE TABLE_NAME = 'notificacion'
    ORDER BY ORDINAL_POSITION
    FOR JSON PATH
  ) AS inf_sch (json_result)
, (
    SELECT *
    FROM dbo.Notificacion
    FOR JSON PATH
  ) AS notif (json_result)
FOR JSON
  PATH, WITHOUT_ARRAY_WRAPPER

The two derived tables are your two original queries with the ROOT() specifier stripped off.  They each produce a JSON output.  Each output is essentially injected into the outer level output using the JSON_QUERY function.  The function makes sure that its argument is injected as a JSON object rather than an nvarchar string representing one.
The WITHOUT_ARRAY_WRAPPER option at the end of the query makes sure the top-level JSON is returned as a single object rather than an array.
Again, I understand that this may not be the best method.  Perhaps a more concise and elegant way could be achieved using the AUTO option of the FOR JSON clause.  I admit I am not very familiar with it.  Hopefully someone can come along and show both of us, as well as the rest of the audience, how to successfully use it in your scenario.


Recommended settings for a small table with high update rate

Oracle 12c: Simple test case: one session inserting 256 rows:

MARK.STEWART@dev01> create table mytab (
    primary_key_column number,
    a number,
    b number,
    c number,
    d number,
    e number,
    f number
);

Table created.

MARK.STEWART@dev01>   insert into mytab (select rownum, OBJECT_ID, DATA_OBJECT_ID, NAMESPACE, DBMS_RANDOM.VALUE(1, 256), DBMS_RANDOM.VALUE(1, 256),  DBMS_RANDOM.VALUE(1, 256) from dba_objects  where rownum < 257);

256 rows created.

Elapsed: 00:00:00.77

Updated a row 1,000 times and committed after each update, took only 0.29 of a seconds:

MARK.STEWART@dev01> begin
   2      for loop_var in 1 .. 1000 loop
   3              execute immediate 'update  mytab set a= 1 where PRIMARY_KEY_COLUMN = 6';
   4              execute immediate 'commit';
   5      end loop;
   6  end;
   7  /

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.29
MARK.STEWART@dev01>

With  pctfree 99 initrans 20 it took almost twice as long (probably due to having high value for initrans): 00:00:00.65


can I get SQL Server to choose between a table and a view?

Not suggesting a better way to run your queries, but hopefully answering the question. You're asking if SQL SErver can ignore the view when you're running queries, but presumably, you're writing the query against the view. In which case, no, SQL Server can't ignore the view.

However, there is a process within the optimizer called simplification. Given enough time (more on that in a moment), SQL Server can recognize which parts of a view you're using or not using and then eliminate tables from the execution plan that are not needed to satisfy the query. But, it has to have enough time to do that. Since you're working with nested views, a major coding issue, you're not generally going to have enough time for the optimizer to get a good execution plan, let alone perform simplification and eliminate unnecessary queries.

Short answer, no. There's not short cut open to you. You need to rearchitect the queries to eliminate nested views. Any other tuning you do will be, at best, perephiral to the problems you're experiencing.


Select IDS where all values can't be found in another table

I think the sentence is quite clear, using a left join to get null USER_ID's and returning those where count = 0;

SELECT
    BOOK_ID, COUNT(USERS.USER_ID) AS USERS
FROM
    BOOKS
LEFT JOIN
    USERS
    ON USERS.USER_ID = BOOKS.USER_ID
GROUP BY
    BOOK_ID
HAVING COUNT(USERS.USER_ID) = 0;

BOOK_ID | USERS
------: | ----:
      2 |     0
      3 |     0

db<>fiddle here


When refreshing materialised views does the index automatically refresh

Yes.

A refresh changes the table storing the data that is materialized from the materialized view query.  The indexes for these tables are updated just as indexes for "normal" tables would be updated when they change.


What Problems Does an Eager Spool Indicate

I am attempting to increase performance of an UPDATE statement. I notice an Eager Spool operation in the showplan popping up.

Eager Spools may be added for a variety of reasons, including for Halloween Protection, or to optimize I/O when maintaining nonclustered indexes.

Without seeing (even a picture of) the execution plan, it is hard to be certain which of these scenarios might apply in your particular case. If data sensitivity is a concern, consider uploading an anonymized version of the plan for analysis using SentryOne Plan Explorer.

It may well be that the Eager Spool is not the thing you should be concentrating on anyway; many factors influence the actual performance of queries that change data. If you're basing your tuning efforts on the estimated percentage cost shown for the Eager Spool operator, please consider that those estimates are generated using a model that is not intended to match the capabilities of your particular hardware configuration.


The “one table per query” thing is a misnomer - the real practical advice is to figure out what you need to read, and then store data to serve that purpose

Sometimes that means fully denormalizing your data, duplicating columns between tables

Sometimes that means lookup tables that act as manual indices - whether you denormalize or use a lookup table depends on which is easier for you, the extra query or the extra storage cost. That’s a personal decision.

10,000 items in a partition is probably fine unless their large blobs (try not to hit 100mb/partition), but do try to take advantage of clustering for queries where you’ll fetch multiple rows.


How can I write an "on-update" trigger which takes a table-name as argument?

Unfortunately, googling this was fairly difficult as the data-type of OLD is data, and searching for this in Google turned up nothing usable. Additionally, finding out that data is a composite type was key to finding the solution.

The issue is twofold:

The table cannot directly be used in an INSERT statement as it is of type text. So dynamic SQL has to be used. So instead of directly writing

INSERT INTO TG_ARGV[0] VALUES OLD;

the following had to be used:

sql := format(INSERT INTO %I VALUES ...', TG_ARGV[0]);

The OLD variable is not available directly inside an EXECUTE statement. So, building from the previous bullet-point, this was not possible:

 sql := format(INSERT INTO %I VALUES OLD', TG_ARGV[0]);

instead the values from OLD had to be passed on with the USING keyword:

 sql := format('INSERT INTO %I VALUES $1.*', TG_ARGV[0]);
 EXECUTE sql USING OLD;

The final solution:

CREATE OR REPLACE FUNCTION update_trigger_func()
    RETURNS TRIGGER AS $$
    DECLARE
        sql TEXT;
    BEGIN
        NEW.updated = NOW();
        sql := format('INSERT INTO %I VALUES $1.*', TG_ARGV[0]);
        EXECUTE sql USING OLD;
        RETURN NEW;
    END;
    $$ language 'plpgsql';

This allows us to write a trigger which handles events on the data table and writes the history into history table as:

 CREATE TRIGGER data_versioning_trigger_delete
     BEFORE DELETE ON data
     FOR EACH ROW EXECUTE PROCEDURE update_trigger_func('history');


No, MongoDB don't "read whole collection to memory" (automatically).
If you don't read/write collection B, then yes, MongoDB keeps collection A in the memory.

So, basically MongoDB keeps (as long as there is enough memory to keep) all read and write data (indexes, documents) in the memory. Of course, you can "warm up" collection, by reading all documents. When memory is "full", the oldest documents are thrown away when newer data comes in.


You have two options. Option 1 is to use the system views:

select
    t.name
    ,c.column_id
    ,c.name
    ,st.name
from
    sys.tables t
    join sys.columns c on (t.object_id = c.object_id)
    join sys.types st on (c.system_type_id = st.system_type_id)
where
    c.name = ''

Option 2 is a free add on from Red Gate Software called SQL Search.


This behavior occurs due to the time to live (TTL) on the DNS records.  A client does not do a DNS lookup every time it attempts to connect to a server.  It first looks in its DNS cache to see if there is a cached entry.  You can see what is in the cache by running ipconfig /displaydns.  Once you run this you'll see the "Time To Live" property on each record.  This indicates how many more seconds the record will stay cached.

So your client will not connect to the new IP address until the old record is timed out of the cache.  If you dig into the Microsoft documentation, they recommend setting the time to live on listener DNS records to 5 minutes to minimize this delay.

Additionally, if the SQL client in use supports "multi-subnet failover", you need to enable that option.  When this option is enabled, the client will try to connect to all IP addresses that are registered for the listener name.  So for this to work nicely, you will have both IP addresses registered for the listener hostname at all times.

See SQL Server Multi-Subnet Clustering (SQL Server)


My mysql database crashed, how to recover

I included this line in my.ini according to the doc http://dev.mysql.com/doc/refman/5.6/en/forcing-innodb-recovery.html: innodb_force_recovery = 1
The server started
I created a dump of each database using mysqldump
According to http://blackbird.si/mysql-corrupted-innodb-tables-recovery-step-by-step-guide/, I ran a database check with mysqlcheck --all-databases and found the offending table.
I tried to delete that table, but it was linked with primary keys (mysql -u ... -p... -e "drop database ..."). I deleted the depending tables, but with no luck, mysqld still refused to start with innodb_force_recovery = 0

I decided to start off from scratch to be sure to get a clean database back.

I deleted everything in the data directory (keeping a backup copy).
I initialized the innodb datafiles using mysqld --initialize, according to https://dev.mysql.com/doc/refman/5.7/en/data-directory-initialization-mysqld.html
I copied the two system database files from the backup (directories mysql and performance_schema)
Now the server started again with these warnings in the log:

  [Warning] InnoDB: Cannot open table mysql/slave_master_info from the internal data dictionary of InnoDB though the .frm file for the table exists. See http://dev.mysql.com/doc/refman/5.6/en/innodb-troubleshooting.html for how you can resolve the problem.
  [Warning] Info table is not ready to be used. Table 'mysql.slave_master_info' cannot be opened.

the same for mysql/slave_relay_log_info and mysql/slave_worker_info
According to https://dev.mysql.com/doc/refman/5.6/en/innodb-troubleshooting-datadict.html, I deleted the three files mysql/slave_master_info.frm, mysql/slave_relay_log_info.frm and mysql/slave_worker_info.frm
I imported the three tables from the dump of the mysql database.

At this point I got a working mysql server with a clean database. The olny thing that remained was to import the dumps that I made previously, using mysql -u root -p... mydb < backup-mysql\mydb.sql

And thank God, after one hour of interruption, everything was working again.


Is there any performance difference between UNIQUE INDEX and INDEX with the same cardinality on MySQL?

There will be no difference for read operations, once written the entries in the index will be the same.
In theory there is a slight difference in update performance as the engine needs to enforce uniqueness in a unique index, but in reality this is one going to be at most a few CPU cycles per row difference so will be unnoticeable.
Only create a unique index for values that you know should be unique. It is quite conceivable that two items could be created at the same time (or at least within the granularity of the data type you are using for that column).


Select column from current table to a related table

Let’s join on player_id
SELECT *
  FROM teams t INNER JOIN players p ON t.player_id = p.id
 WHERE t.created_at >= DATE_SUB(CURDATE(), INTERVAL 30 DAY)
   and p.email = 'barcelonafc@outlook.com'

Be sure to verify if the results should be filtered on players added within the last 30 days or teams.


Rows missing after online conversion from MyISAM to InnoDB

Performing an ALTER to change storage engines won't make rows disappear. However, let me offer some advice since you said you're 'database noobs' in your question.

When modifying existing schema or doing anything that could affect data, here's some basic advice:

Make a backup first.
Have a change plan.
Test your plan on an offline host.
Have a test plan to compare before and after data.
Schedule and take a downtime.
Take a backup and snapshot immediately after your downtime goes into effect and you verify traffic has stopped.
If you're running MYISAM, use 'CHECK TABLE' to evaluate what you're dealing with before you ALTER.
Copy the table locally in addition to your backup, just in case.
Proceed with caution, enable "--show warnings" and other output so you have the full picture as you make your changes.
If the data is important to you, hire a DBA, even if just to consult during the migration so you have a seasoned veteran by your side.

There's probably a lot more I could get into, but the above will provide you with options when something goes wrong.

As far as your missing data/rows, there's no way to know w/o a "before/after" snapshot to compare. You can compare against your latest backup to at least verify that much.


How to fix `The table '/tmp/mysql/#sql_xxxxx' is full`?

The relevant configuration is this:

  ibtmp1:12M:autoextend:max:512M

You are restricting your temporary tables (and similarly your main innodb tablespace via innodb_data_file_path) to 512M total.  This includes implicit (disk) temporary tables that might be created by various ORDER BY / GROUP BY / DISTINCT operations.

MySQL reports this failure relative to your configured tmpdir, although it's actually using the ibtmp1 file.

Increase or remove the :max:<size> limit to resolve this.

Alternatively you might find a way to avoid these sort of temporary tables by inspecting the offending queries - possibly via adding an index, changing data types or changing the query itself among other strategies.


The UPDATE permission was denied on the object

I've set GRANT on all the securables.

You want to assign roles to write data for a specific database. When I read this correct you clicked everthing to do this.

Now I think that your user is member of the role db_datawriter and db_denydatawriter. Please remove the role db_denydatawriter and try it again.


This is a common case where autonomous transactions would be helpful. In a situation where you have a transaction the might or not fail, you'll want to log the error, but as an independent transaction, without affecting the main one.

Example from Tom Kyte:

create or replace procedure log_error( ..... )
as
   pragma autonomous_transaction;
begin
   insert into logging_table (...) values (...);
   commit;
end;

and then in your plsql code you can:

begin
    .... your code
exception
    when others then
           log_error( .... );
           RAISE;  /* this is crucial, you MUST re-raise the exception */
end;

If you need to know more about autonomous transaction, please check:
http://docs.oracle.com/cd/E25054_01/server.1111/e25789/transact.htm#i7733


"SSIS Evaluation Period Has Expired" on Dev Instance?

Run regedit and change the value of CommonFiles REG_DWORD under:

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\130\ConfigurationState

...from 1 to 3.

Run the Edition Upgrade and this will "upgrade" the components.

If that doesn't resolve the issue, then we need to make sure the client components are on the same service pack/build version as SQL Server.  If you have applied SP1 to SQL Server, check and make sure SP1 successfully installed on the client components as well.  Looking at your setup bootstrap logs will help determine that.


Why is my postmaster process (sometimes) becoming unmanageable after a WAL base restore?

In general if you are seeing problems of this sort, it may be best to take them up on the pgsql-bugs list.  People there can help figure out what information to gather to help determine what the scope of this misbehavior is and help fix it for you.

Also 8.4.11 to 8.4.12 wal restore should work just fine.

If this is only occasionally happening, I don't think your explanations get there.  It sounds like something that really could use additional troubleshooting by people who can determine if a code fix is required.


Cannot create a database connection SQL Developer

I was putting the wrong service name. You can check yours on tnsnames.ora.