ERROR: currval of sequence "rss_sub_source_id_seq" is not yet defined in this session

The functions currval(regclass) and lastval() only make sense after nextval() has been used in the same session (not transaction!). Concurrency control in PostgreSQL is reason behind this behavior.
If you are just curious about the current state of the sequence (not the last serial ID actually used in the session), you can always just call nextval():
SELECT nextval(pg_get_serial_sequence('foo', 'id'));

Returns the next serial ID, which had not been used, yet. This carries the flaw of actually consuming the returned ID (which should not matter in a proper relational design). But that side effect can be avoided. In Postgres, serial and IDENTITY columns are implemented using a SEQUENCE internally, which happens to be a table-like object that can be queried like a table. If you are curious about the current state of a SEQUENCE, just use SELECT:
SELECT * FROM public.rss_sub_source_id_seq;

(Requires the SELECT privilege on the sequence, while the above mentioned sequence functions require the USAGE privilege.)
You need to know the name of the sequence for this, and you already know how to get it: pg_get_serial_sequence('rss_sub_source', 'id'). It's easy enough to guess, too. See:

PostgreSQL next value of the sequences?

For ad-hoc use, just compile the query by hand. Or you can use \gexec in psql:
SELECT 'SELECT last_value - CASE WHEN is_called THEN 0 ELSE 1 END FROM ' || pg_get_serial_sequence('b2', 'big_id')\gexec

See:

Simulate CREATE DATABASE IF NOT EXISTS for PostgreSQL?

For automated (or regular) use, you need dynamic SQL to use the text value returned by the function as SQL identifier. Here is a simple custom function to do that. Also factored in is_called:
CREATE OR REPLACE FUNCTION sequence_currval(_tbl text, _col text, OUT currval bigint)
  LANGUAGE plpgsql AS
$func$
BEGIN
  EXECUTE 'SELECT last_value - CASE WHEN is_called THEN 0 ELSE 1 END FROM ' || pg_get_serial_sequence(_tbl, _col)
  INTO currval;
END
$func$;

Call:
SELECT sequence_currval('foo', 'id');

This is safe against SQLi because pg_get_serial_sequence() returns the identifier safely double-quoted as needed.
The function returns the last value that has been consumed from this sequence. (Does not mean it made its way into the table.) The next one will be greater. (Assumes a default sequence with INCREMENT 1!)
It does not consume a serial ID and does not require that nextval() has been called in the same session.
Consider this demo:
db<>fiddle here


Drop Oracle data file with 0 bytes and in offline (needs recovery) state

You can not drop the datafile. You can move it to a better location and keep it there. If you want to get rid if it, re-create the tablespace and copy the data to the new tablespace. Drop the old tablespace and rename the new tablespace. Don't forget to give quota to your schemas similar as they have on the old tablespace.


Availability Group, asynchronous mode, manual/forced failover - handling identity overlap

Rather than trying to react to the failover on the new primary, I think it would make more sense to update your manual failover procedure to first change the AG to sync mode.

Once the AG indicates that it's synchronized, then do the failover.  This way you won't have data loss.  You can then go back to async mode after the failover is completed.

See Change the Availability Mode of an Availability Replica (SQL Server) for instructions on how to make the change prior to the failover.

Note: a sync mode AG can temporarily shift into async mode if the network gets slow between the two servers

  If primary's session-timeout period is exceeded by a secondary replica, the primary replica temporarily shifts into asynchronous-commit mode for that secondary replica. When the secondary replica reconnects with the primary replica, they resume synchronous-commit mode

But according to the failover docs, a sync mode AG guarantees zero data loss during a manual failover

  This guarantees that every transaction that was committed on a former primary database has also been committed on the new primary database


Is execution of synonyms not logged in target database?

The query is run in DatabaseA, not DatabaseB, so I'd say by design. That is, SQL Profiler captures "what is the connection database context" not "In what database is the object am I accessing"

This makes sense: each database is logically isolated from each other. Using a synonym or other cross database query is a special case because you can't ensure all databases are in synch (especially around restores) nor enforce foreign keys etc

An other example...

USE DatabaseD
GO
SELECT *
FROm
  DatabaseA.dbo.TableA A
  JOIN
  DatabaseB.dbo.TableB B ON ..
  JOIN
  DatabaseC.dbo.TableC C ON ..

It runs in DatabaseD so this is what you'd trap in profiler

Workaround? As far as I know (I haven't tried though) you can't distinguish the database of an accessed object, only of the connection.


ORA-32004: obsolete or deprecated parameter(s) specified for RDBMS instance

Well, you can hack around with an SPFILE easily enough:

SQL> create pfile='/tmp/initSID.ora' from spfile;

Now edit that ordinary text file, then

SQL> create spfile from pfile='/tmp/initSID.ora';

And you can play with the temporary one with

SQL> startup pfile='/tmp/initSID.ora';

Note tho' there is no actual problem with specifying obsolete parameters other than the message; the only issue is if you were relying on them to set some desired behavior, and they were ignored.


You may need to perform a FLUSH PRIVILEGES; before trying to create an account that was recently removed. This is the result of a very old bug in MySQL server. By flushing the privileges the system will properly “forget” that simon@localhost existed.


Do I need to disable logins while upgrading SQL Server?

SQL Server certainly doesn't require you to disable the logins in order to upgrade, but it is going to terminate any sessions that are connected because it will stop the SQL Server service.  If you are going to install a service pack, and then a CU, for example, this will result in stopping, starting, stopping, and starting the SQL Server service.  So if you are concerned about users logging in and being disappointed because their query didn't finish, then yes, you might want to disable their logins first.

You can generate a T-SQL script to do this by running a query similar to the following:

SELECT 'ALTER LOGIN [' + name + '] DISABLE'
FROM sys.server_principals WHERE TYPE = 'S'
    AND  [name] NOT IN ('sa', '##MS_PolicyTsqlExecutionLogin##',
       '##MS_PolicyEventProcessingLogin##')

You may need to add some more logins to the NOT IN list--just be sure that you don't end up disabling the login(s) you need to administer the system!

A similar script to enable the logins can be generated by replacing DISABLE in the script above with ENABLE.

If there is a poorly designed application using a database on the server, it's possible that a user could start something that doesn't get properly finished before their session is terminated by the service shutdown.  This could happen if something that should be done in one transaction is instead being done in multiple transactions.  If the first transaction succeeds, but one or more of the related transactions fail, then you might have something half-baked.  Typically this is not something to be concerned about, but just including this because it's certainly possible.


mysql_install_db: How to set the root password

Upon installation, root@localhost does not have a password.

You should be able to connect without a password by just doing this:

$ mysql -uroot

Try the following:

$ mysqladmin --no-defaults --port=3308 --user=root password 'somenewpassword'

or you can do it the stubborn way:

$ service mysql restart --skip-grant-tables --skip-networking
$ mysql -e"UPDATE mysql.user SET password=password('somenewpassword') WHERE user='root'"
$ service mysql restart
$ mysql -uroot -p

UPDATE 2013-01-02 16:47 EDT

I am sorry, I overlooked the port number.

Here is the problem: you cannot use root@localhost against a non-3306 port unless you use the TCP/IP protocol. Please try this:

$ mysqladmin --no-defaults --port=3308 --user=root --protocol=tcp password 'somenewpassword'


Why is changing data types not effecting the database size?

How are you measuring the size of your tables and indexes?
The way SQL Server works is once the database is allocated disk space, it still retains that disk space and only marks it empty / for re-use internally when data is reduced / removed until a SHRINK or similar operation occurs that forces the release of that acquired space back to the disk.
So changing data types of a fixed-width to a type that uses less space doesn't automatically trigger the release of that previously claimed space back to the disk unless you rebuild your indexes, run a SHRINK, etc.
As Martin points out in the comments, if you are using compression, then likely the used space of the BIGINT column that only held INT values was already being compressed to an equivalent space use of an INT column, which would also explain why you wouldn't see it change in size by changing data types, even if you did rebuild your indexes or ran a SHRINK operation.


Permissions Error on Member that doesn't exist

In SSMS if you open each role and find the Measures Dimension Data security section then go to the Advanced tab (if you have trouble finding it see this blog for screenshots from the same screens in SSDT-BI) do you see any role mentioning that problem measure? If that measure doesn't exist in the cube then remove it from the security role definition.

If that doesn't help you find it then right click on the database on SSMS and script the database as an Alter then search the XMLA for that measure and post back the section of XMLA where you find it.


What schema for different relation from two tables to another?

Rearranging / rephrasing your relationships a bit:

1 instructor has 0, 1 or n meeting_points.
1 meeting_point is linked to 1 instructor.

This means there is a simple 1:n (1::0-n) relationship between them. The foreign key should be in meeting_points, referencing instructors.
1 booking has 1 meeting_point.
1 meeting_point is linked to 0 or 1 booking.

This means there is a simple 1:1 (1::0-1) relationship between them. The foreign key should be in bookings, referencing meeting_points.
There are no direct relationships between instructors and bookings.

The implementing is obvious here.

Without further information about other attributes and possible unique keys, I'll use the above, adding surrogate unique keys in the first two tables (this is not needed, the surrogate keys could be replaced by other unique keys you may have identified). The bookings table does not need a surrogate anyway, as it's in a 1:1 relationship with meeting_points and the same column can be used as primary key and for the foreign key constraint:

CREATE TABLE instructors
(   instructor_id SERIAL NOT NULL,
    instructor_name TEXT NOT NULL,
    -- other attributes
    PRIMARY KEY (instructor_id)
);

CREATE TABLE meeting_points
(   meeting_point_id SERIAL NOT NULL,
    meeting_point_name TEXT NOT NULL,
    -- other attributes
    instructor_id INT NOT NULL,
    PRIMARY KEY (meeting_point_id),
    FOREIGN KEY (instructor_id)
        REFERENCES instructors (instructor_id)
);

CREATE TABLE bookings
(   booking_id INT NOT NULL,
    booking_date DATE NOT NULL,
    -- other attributes
    PRIMARY KEY (booking_id),
    FOREIGN KEY (booking_id)
        REFERENCES meeting_points (meeting_point_id)
);

Sidenote: The relationship "a meeting_point has (max) one booking" does not sound very realistic. So, if A is an instructor and M is a meeting_point, A can only make one booking in M? What if she wants to make another booking in the same point tomorrow? This would not be allowed under this design.


WE8DEC is the old DEC MCS character set which stood for Digital Equipment Corporation Multinational Character Set.  The WE8 prefix identifies this as a Western European 8-bit character set.


Having two entities that are the same except for 1 distinct attribute

If I were you, I would do something like the following:

(tl;dr) - you should have one inspection entity - they are a hair's breadth apart and in this context, essentially one and the same thing! Modifying your schema slightly makes them one and the same.

CREATE TABLE location  -- "master" room/apartment (could be called "accomomation" or similar)
(
  location_id INTEGER PRIMARY_KEY,
  location_type VARCHAR (10) NOT NULL,
  -- CHECK accommodation_type IN ('Hotel', 'Appartment'), could also have 'Hostel', 'AirBNB', 'Tent'...
  -- MySQL doesn't support `CHECK` constraints - use a lookup table!
  location_name VARCHAR (25), -- colloquial name
  location_phone VARCHAR (20) -- either the switch or owner's mobile/cell.
);

Note:

all lower case for table names - easy to read: SQL in caps, identifers in lower with underscores.
always (depending on your RDBMS - though all the major ones comply these days), give your PRIMARY KEYs, FOREIGN KEYs and UNIQUE INDEXes (i.e. all database objects) meaningful names - makes debugging much easier.
you will notice that I put in long-ish names for my fields - this is because

a) easier to debug and,
b) code/programmes spend 99% of their time in maintenance - typing a few extra characters is a small price to pay for an easier life!

Never use keywords (e.g. DATE) as table or column names! Makes life hell with quoted identifiers &c.
always try to reduce (within reason) the number of points in your schema where NULLs can be entered. It makes logic easier and can help the optimiser! Most of my column definitions can be followed with NOT NULL below!
you might also want to check out this site for other ideas. My thoughts are only a "first pass".

CREATE TABLE hotel
(
  hotel_id INTEGER PRIMARY KEY,
  hotel_name VARCHAR (50) NOT NULL,  -- full business name
  hotel_address INTEGER, -- FK reference to address table?
);

CREATE TABLE room
(
  room_id INTEGER PRIMARY KEY, -- as opposed to room_no - room numbers in hotels can change, renovations, buidling works!
  location_id INTEGER, -- FK to location. Also `UNIQUE INDEX` to prevent dups.
  hotel_id INTEGER, -- FK to hotel
  room_no INTEGER,
  room_rate INTEGER -- FLOAT/DECIMAL/whatever
);

CREATE TABLE apartment
(
  apartment_id INTEGER PRIMARY KEY
  location_id INTEGER, -- FK to location! Also, `UNIQUE INDEX` to prevent dups!
  apartment_address INTEGER, -- FK to address table
  apartment_room_count INTEGER,
  apartment_rate INTEGER -- FLOAT, DECIMAL...
);

CREATE TABLE location_rating
(
  location_rating_id INTEGER NOT NULL PRIMARY KEY,  -- if possible give your PK a meaningful name.
  accomodation_rating_text VARCHAR (20) NOT NULL
);

INSERT INTO accomodation_rating
VALUES
(1, 'Call the police!'),
(2, 'Totally unaccptable'),
(3, 'Very poor'),
(4, 'Poor'),
(5, 'Acceptable'),
(6, 'Reasonable'),
(7, 'Good'),
(8, 'Very good'),
(9, 'Excellent'),
(10, 'Kiss the cleaners!');

CREATE TABLE inspection
(
  inspection_id INTEGER PRIMARY KEY,
  accommodation_id  INTEGER, -- FK to accomodation
  inspection_date DATE, -- more meaningful than just date - anyway, NEVER call a table or field by a KEYWORD!
  inspection_rating INTEGER, -- FK to your accomodation_rating table - NEVER allow people to write free text if possible!

  -- NOW, no need for hotelNo or apartmentNo - plus no NULLs!
);


pg_upgrade --link fails: `directory "/..." already in use as a tablespace`

"PG_10_201707211" must be a left over directory from your aborted attempt at running pg_upgrade without the --link flag.  You can delete it, or out of an abundance of caution move it to one of your other partitions.  Renaming it the way you describe wouldn't accomplish much and would be very confusing if you have more problems in the future which require more investigation.


The NOTICE means that textdoc contains at least one word within it that is more than 2047 characters.  The definition of a word depends on what configuration you use for your ts_config.  Words are generally strings of characters separated by spaces or certain punctuation. Hyphenated words will generally be emitted as each separate word, plus the entire hyphenated phrase as an extra "word", at least with the ts_configs that I am familiar with. You can use the ts_debug function to find out how it actually breaks up the string emitted by your specific converter using your chosen ts_config.

select alias, description, token from mytable cross join ts_debug('english',textdoc)
    where length(token)>2047;

(On second thought, ts_lexize may be better than ts_debug, but I am not as familiar with it.)

Regarding the ILIKE query, your query is returning 2/3 of the rows in the table.  Any index, whether pg_trgm-based or tsvector-based, is unlikely to help you in that situation.


This has happened to me before when the InteractiveHeight was set to 0. The query only returned a few thousand rows, but since there was no paging in the report, SSRS was trying to put everything on a single page. Eventually I would hit the SSRS timeout before it reached the rendering phase, and have 0 rows returned.

If that doesn't apply to your situation, please check out this link for some additional troubleshooting ideas: http://technet.microsoft.com/en-us/library/bb522806(v=sql.105).aspx#Process


How to Import a BACPAC file to Azure SQL and Overwrite Existing Database?

You are correct.  In Azure you cannot restore on an existing database.

You have to restore with a different name.
Delete old database
Rename new one to old database name.

You have few ways to restore from .BACPAC file.

You can do it directly from your on prem .BACPAC location by using
.\sqlpackage.exe command line tool.

.\sqlpackage.exe /a:Import /sf:C:\filename.bacpac /tsn:ServerName.database.windows.net /tdn:destinationDBName `
 /tu:adminaccountName@serverName /tp:$credentialPW
You can also use the copy that you uploaded in your storage account.

$ResourceGroupName = "RGName"
$ServerName = 'ServerName'
$DatabaseName = "DestinationDBName"

$StorageName = "StorageAccountName"
$StorageKeyType = "StorageAccessKey"
$StorageUri = "http://$StorageName.blob.core.windows.net/swwstoragecontainer/BackpacFileName.bacpac"
$StorageKey = "***********************************"

$credential = Get-Credential

$importRequest = New-AzureRmSqlDatabaseImport -ResourceGroupName $ResourceGroupName -ServerName $ServerName
 -DatabaseName $DatabaseName -StorageKeytype $StorageKeyType -StorageKey $StorageKey
 -StorageUri $StorageUri -AdministratorLogin $credential.UserName -AdministratorLoginPassword $credential.Password `
 -Edition Standard -ServiceObjectiveName S0 -DatabaseMaxSizeBytes 50000

Get-AzureRmSqlDatabaseImportExportStatus -OperationStatusLink $importRequest.OperationStatusLink
In portal you can directly import the .BACPAC file into your server as a database.


Restoring data from dumped sql file gives: mysql: unknown option '--innodb_file_per_table

Problem solved:

It should be an unknown option as I've put it below [mysql] not [mysqld]. I put all the parameters below [mysqld] and I went through the process again and now it works just fine. ibd files are in their respective database folder.


I need to run VACUUM FULL with no available disk space

Since you don't have enough space to run a vacumm or rebuild, you can always rebuild your postgresql databases by restoring them.  Restoring the databases, tables, indexes will free up space and defragment.  Afterwards, you can setup automated maintenance to vacumm your databases on a regular basis.

1 Backup all of the databases on your postgresql server

You will want to backup all of your databases to a partition that has enough space. If you were on Linux, you can use gzip to further compress the backup to save space

su - postgres
pg_dumpall | gzip -9 > /some/partition/all.dbs.out.gz

2 Backup your configuration files

cp /path/to/postgresql/data_directory/*.conf /some/partition/

3 Stop Postgresql

pg_ctl -D /path/to/postgresql/data_directory stop

4 erase the contents of the data directory

rm -Rf /path/to/postgresql/data_directory/*

5 Run initdb to reinitalize your data directory

initdb -D /path/to/postgresql/data_directory

6 Restore configuration files

cp /some/partition/*.conf /path/to/postgresql/data_directory/*.conf

7 Start Postgresql

pg_ctl -D /path/to/postgresql/data_directory start

8 Restore the dump of all the databases you made

gunzip /some/partition/all.dbs.out.gz
psql -f /some/partition/all.dbs.out


Getting the next bigserial value in PostgreSQL

I want to know what the id of the next row I insert will be. How can I tell this, without inserting a row?

This is exactly what nextval() does. But I can't think of any useful thing to do with this value, if you are not inserting into the table. It will be the id value of a row that may never be inserted:

  Important: To avoid blocking concurrent transactions that obtain numbers from the same sequence, a nextval operation is never rolled back; that is, once a value has been fetched it is considered used, even if the transaction that did the nextval later aborts. This means that aborted transactions might leave unused "holes" in the sequence of assigned values.

As stated above, the value returned by nextval() is hardly ever useful, unless you insert into the table. But if you are inserting some rows and you simply want the id values of the serial, you can simply use the RETURNING clause in the INSERT:

insert into t (col1, col2, ...)
values ( (value_a_col1, value_a_col2, ...),
         (value_b_col1, value_b_col2, ...),
         ...
       )
returning t_id ;

See an example at SQLfiddle.

Some more complicated cases - like when the ids are needed for inserting into more tables, related through foreign keys, or when an UPSERT is need - can be written in one statement, using RETURNING and data-modifying CTEs.
Note that UPSERT has been implemented in 9.5, with the INSERT ... ON CONFLICT DO NOTHING/UPDATE construct.
Examples below:

How to Choose Between VALUES and SELECT for INSERT?
Idiomatic way to implement UPSERT in PostgreSQL
Inserting into related tables
PostgreSQL multi INSERT…RETURNING with multiple columns
Bulk insert in multiple tables

If you want the id values to be used in different columns, see this:

Reference value of serial column in another column during same INSERT


NDB may be the right solution for you.  Or...

Sharding is splitting up the data -- some rows go here; some go there.  NDB does sharding, and it also does redundancy.  That is, the rows that are going 'here' can be put on 2 servers for redundancy.  So think through whether you want both, and how many servers you want to put into the system.

You have not mentioned anything about running out of CPU or I/O horsepower on your small machine.  16GB/1TB/8-core machines are about the minimum you can buy today; that give you a lot of growth over what you have.

Also, keep in mind that there is a lot of complexity in building and maintaining NDB.  Plus code changes may be necessary.

"2gb ram 2CPUs 40GB" -- Do you run a junk yard?


Logon procedure instead of logon triggers

There is no reliable way to do this, unfortunately. The things you want to go by is set in the connection string and, hence, can be spoofed. Here's an example what I did in SSMS:

I execute:

SELECT session_id, program_name
FROM sys.dm_exec_sessions
WHERE is_user_process = 1

With the result:

session_id  program_name
51  MyCRM
52  MyCRM
53  MyCRM
54  MyCRM
55  MyCRM
56  SQLServerCEIP


dbvisualizer: number of rows limited by maxrows

Max rows is specified just above the SQL Commander tab in the second field from the right.   On the Object View tab it is at the bottom left of the results window.  For a single table query I generally use the Object View window.  It is easy to setup filters in the Data sub-tab.

Setting this value too high will result in a crash when you run out of memory.

Is there a reason you want more than 1000 records returned?  That's a lot of data to scroll through.


How to create database level counter (identify field)?

Sounds like Sequences, brought in with SQL 2012

Info:
https://msdn.microsoft.com/en-us/library/ff878058.aspx
Create Sequence syntax:
https://msdn.microsoft.com/en-us/library/ff878091.aspx
Nice SimpleTalk article on them:
https://www.simple-talk.com/sql/learn-sql-server/sql-server-sequence-basics/


What's the difference between --read-only and just permit SELECT queries?

To the user, probably no difference.

To the system integrity, --read-only prevents you from accidentally writing to a Slave, thereby messing up the Replication integrity.

Aurora has no control (I assume) over whether you grant write permission and happen to let them hit a Slave with an UPDATE.  This is an easy, simple, prevention.


You can't change the secondary database in any way.
This is normal - the secondary database is supposed to be a backup of the main database, so it's not possible to add stuff that's not in the main database. That's why it's in read-only mode.

It's fine to use the secondary database for reporting, to reduce the load on the primary server (see Using Secondary Servers for Query Processing).
But if you need any special indices for that, you have to add them in the primary database and wait until they are log-shipped to the secondary database.

As jchelad already said in his answer, one disadvantage of using the secondary database for reporting is that users are disconnected whenever a log backup is restored.
It's possible to change this, so that restoring is paused until nobody is connected to the database.

Quote from the above link:

  There are two options for configuration when you place the secondary
  database in standby mode:

  You can choose to have database users disconnected when transaction log backups are being restored. If you choose this option, users will
  be disconnected from the database each time the log shipping restore
  job attempts to restore a transaction log to the secondary database.
  Disconnection will happen on the schedule you set for the restore job.
  You can choose not to disconnect users. In this case, the restore job cannot restore transaction log backups to the secondary database
  if there are users connected to that database. Transaction log backups
  will accumulate until there are no user connections to the database.

But I wouldn't recommend using the second option.
We are using logshipping at work, and we are using the secondary database for reporting.

We decided to choose the first option (users are disconnected when a backup is restored) because having a backup is more important than being able to run queries all the time.

We didn't like the second option (only restore if no one is connected to the database) because in case the main server dies, we don't want to find out that the secondary database wasn't restored in the last few hours because the salespeople have been running queries all day.

We "educated" our reporting users that there is a short time every 15 minutes where they can't run queries, and they accept that.
(the alternative would be to run the queries on a copy of the main database from yesterday evening, but they prefer data from today, so they have to live with the "15 minute break")


Create a view that dynamically UNIONs new tables

You can prepare the SQL statement using a cursor to build the SQL string, and execute it using sp_executesql.

DECLARE @TableName nvarchar(400)
DECLARE @DynSQL nvarchar(MAX)
Set @DynSQL = ''

DECLARE cursor1 CURSOR FOR
    select name
    from sys.tables
    where name like 'DDOS_WAF_ACCOUNT_%'

OPEN cursor1

FETCH NEXT FROM cursor1 INTO @TableName

WHILE @@FETCH_STATUS = 0
BEGIN
    -- Add the select code.
    Set @DynSQL = @DynSQL + 'Select * from ' + @TableName
    FETCH NEXT FROM cursor1
    INTO @TableName

    -- If the loop continues, add the UNION ALL statement.
    If @@FETCH_STATUS = 0
    BEGIN
        Set @DynSQL = @DynSQL + ' UNION ALL '
    END

END

CLOSE cursor1
DEALLOCATE cursor1

Print @DynSQL
exec sp_executesql @DynSQL

You can use text mode and see the query that it is building, thanks to the print statement near the end.


SQL Server 2008 R2 - Moving nodes in mirrored environment

You are correct..

Install SQL Server on the new nodes
Use SP_HELP_REVLOGIN to copy the user account to the secondary node
Take a full backup and a single log backup of the primary server and restore on the secondary with no_recovery
Break the mirror and remove the secondaries from the primary server
Create the endpoints with TSQL or use the Mirroring wizard to configure them and endpoint security
start mirroring


Why does constructing this jsonb_array from two jsonb objects fail?

You have some characters after the "k2" in first query. And because of that it is complainig about invalid syntax.

select *,ascii(c) from unnest(regexp_split_to_array('''{"k1":"v1","k2":"v2"}''','')) as c;
 c | ascii
---+-------
 ' |    39
 { |   123
 " |    34
 k |   107
 1 |    49
 " |    34
 : |    58
 " |    34
 v |   118
 1 |    49
 " |    34
 , |    44
 " |    34
 k |   107
 2 |    50
 " |    34
   |  8204
   |  8203
 : |    58
 " |    34
 v |   118
 2 |    50
 " |    34
 } |   125
 ' |    39

If you run your first query like this, it will work:

SELECT *
FROM ( VALUES
  (jsonb_build_array(
    '{"k1":"v1","k2":"v2"}'::jsonb,
    '{"k1":"v1","k2":"v2"}'::jsonb
  ))
) AS t(x);


Working with SybaseASE since last 7 years, I can tell that the newer versions of sybaseASE relies on MDA tables (just like SQLServer DMVs). The tools out there for SybaseASE monitoring are pretty expensive.

I my company, I have build reporting on the top of MDA tables. The prettiness of the graphs you have linked comes at cost.

As a cheap alternative (It is a modest 6.99 U$K per core ) would be to use ASEProfiler or DBACockpit (sybase 16 and up).

As for the configuration settings, each environment is different, so the configuration settings will be different. Start with the common ones and monitor them sp_monitorconfig 'all', null, 'full' .. esp the reuse count should never be more than 0.

Data cache
procedure cache
max memory
partition the data cache
have multiple data cache e.g. 2K, 8K or 16K.
Online engines (sybase 15.7 and up)
max lava operators

All above settings can be tuned using sp_sysmon.


When SQL Server uses Multi Page Allocations

In SQL Server 2008 R2 and earlier, the following allocations come from the MemToLeave (MTL) area:

Linked servers
XML documents
Extended stored procedures
sp_OACreate calls
Query plans that exceed 8KB
SQLCLR
Backups that use a large value for MAXTRANSFERSIZE
Using a network packet size over 8192 bytes

Information extracted from this article by Microsoft support engineer Amit Banerjee.

SQL Server 2012 uses the Any Size Page Allocator.

Further information is included in this Microsoft Knowledge Base article.

Multi-page allocations (MPA) also come from the MTL area, before SQL Server 2012. Whether an allocation made by SQL Server is made via the MPA or single page allocator (SPA) depends purely on the size of the allocation request. If it is 8KB or less, it is allocated via SPA and stolen from the buffer pool. If it is 8KB or more, MPA is used.

  I know data page is always 8K so is there a situation where data/index page make use of MPA?

No, data and index pages are always cached in the buffer pool.

  Is there a way to confirm that memory allocation for the execution plan is using multi page allocation?

Cached plans have a complex structure. Individual allocations within that structure are generally 8KB allocations, but certain queries (generally those that contain long IN lists or many parameters to a stored procedure call) can require an MPA allocation. These are comparatively rare. It is possible to query SQL Server DMVs to determine the split between SPA and MPA for a cached plan.


How to allow users to run a function, but not access objects the function is accessing

Yes that's possible. Create the function with the user owning all those tables and use the SECURITY DEFINER modifier.

  SECURITY DEFINER specifies that the function is to be executed with the privileges of the user that created it.

create or replace function foo()
  returns void
as
$body$
...
$body$
language plpgsql
security definer;

Also see the example in the manual:
http://www.postgresql.org/docs/current/static/sql-createfunction.html#SQL-CREATEFUNCTION-SECURITY


desing the database for survey app with various types of answers

I think you should have the Survey table as it will be much easier to add it now than later on when your app is live.

The second model is much better as it provides the flexibility to have multiple possible answers and user answers for a single question in a normalised fashion. Having a column for each answer is not a good idea.


It depends (as with all interesting questions).  Much of the disagreement on this point tends to come from people talking past each other because they have different concerns, different assumptions, or different time horizons.

On the one hand, a consistently high CPU load is a problem for most systems because it indicates that you likely have relatively little room for growth before CPU becomes your bottleneck and before your CPU run queue starts growing exponentially.  If my system is constantly running at 65% CPU and I'm adding users and functionality every month or expecting that end-of-month/ end-of-quarter/ some other event is going to periodically cause load to increase 20 or 25%, I'm uncomfortably close to exhausting my available CPU and causing my query response times to soar as everyone ends up queued waiting for CPU.  Since adding CPU to a machine is typically difficult-- unless you're running in a virtual environment, it almost always involves buying a brand new server-- an impending CPU bottleneck is generally particularly concerning.

On the other hand, a consistently high CPU load indicates that right now you're probably using your resources effectively.  If you're building a data warehouse, you'd ideally be close to maxing out both CPU and available I/O during your nightly loads, for example, balancing the amount of parallelism you use to get the most efficient loads.  If you assume that tomorrow's load is going to be basically identical to today's load, you'd welcome a high CPU load.  After all, it's not like you can bank your unused CPU cycles for a rainy day.

As with any metric, it's also important to have real benchmarks to compare to.  Unfortunately, lots of shops don't know what their performance profile is when things are running smoothly.  No one calls the DBA to let them know that the system is performing perfectly and under the appropriate amount of load to let her know to capture a baseline.  If you have a real baseline from when the system was performing well under a similar amount of load and that baseline had high CPU consumption, it's safe to assume that your system functions well at relatively high CPU load.

Without those real baselines, though, many DBAs have developed rules of thumb that (often inaccurately) give them some context for what an average system looks like and will compare that average to the current load.  That sort of intuition is better than nothing when you don't have real data to compare against but it often fails when looking at specific systems.  If you look at the body mass index (BMI) of a population, it's pretty good at determining which populations are under- and over-nourished.  If you look at the BMI of specific individuals, however, the metric is much less useful-- professional athletes will almost all be "overweight" or "obese" when looking at BMI despite being in excellent shape.  A DBA's (or a monitoring tool's) rule of thumb is like the BMI in that it's reasonably accurate for a population but much less accurate for an individual.


I fixed the problem by adding this correct line on the listener.ora file:

VALID_NODE_CHECKING_REGISTRATION_LISTENER=OFF

now the listener is starting.


Using "Where clause" In OPENROWSET as variable

I've solved my own problem by rewriting my code like this:

DECLARE @date DATETIME

SET @date = getdate()

DECLARE @SQL NVARCHAR(MAX) = ' SELECT * ,' + QUOTENAME(@ExcelName, '''') + ' AS ExcelName ,' + QUOTENAME(@date, '''') + ' AS TimeStamp
    INTO #TTT
     from openrowset(''' + @Provider + ''',''Excel 8.0;Database=' + @FileName + ';hdr=' + @Header + '' + ''',
                     ''select * from [' + @SheetName + '$]'')' + @WhereClause + ';
     insert into ' + @TableName + '
     select *
     from #TTT
     drop Table #TTT'

PRINT @SQL

EXEC (@SQL)


What happens when changing column type DATETIME to TIMESTAMP in MySQL?

Here is a test script:

use test
drop table if exists felipe_table;
create table felipe_table
(
    id int not null auto_increment,
    dt datetime not null,
    name varchar(25),
    primary key (id)
);
insert into felipe_table (dt,name) values
(NOW() + INTERVAL FLOOR(RAND()*10) DAY,'rolando'),
(NOW() + INTERVAL FLOOR(RAND()*100) DAY,'pamela'),
(NOW() + INTERVAL FLOOR(RAND()*1000) DAY,'dominique'),
(NOW() + INTERVAL FLOOR(RAND()*10000) DAY,'diamond');
select * from felipe_table;
SHOW CREATE TABLE felipe_table\G
ALTER TABLE felipe_table MODIFY dt TIMESTAMP;
select * from felipe_table;
SHOW CREATE TABLE felipe_table\G

Here is its execution (using MySQL 5.5.12-log for Windows):

mysql> use test
Database changed
mysql> drop table if exists felipe_table;
Query OK, 0 rows affected (0.03 sec)

mysql> create table felipe_table
    -> (
    ->     id int not null auto_increment,
    ->     dt datetime not null,
    ->     name varchar(25),
    ->     primary key (id)
    -> );
Query OK, 0 rows affected (0.11 sec)

mysql> insert into felipe_table (dt,name) values
    -> (NOW() + INTERVAL FLOOR(RAND()*10) DAY,'rolando'),
    -> (NOW() + INTERVAL FLOOR(RAND()*100) DAY,'pamela'),
    -> (NOW() + INTERVAL FLOOR(RAND()*1000) DAY,'dominique'),
    -> (NOW() + INTERVAL FLOOR(RAND()*10000) DAY,'diamond');
Query OK, 4 rows affected (0.13 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql> select * from felipe_table;
+----+---------------------+-----------+
| id | dt                  | name      |
+----+---------------------+-----------+
|  1 | 2013-02-16 09:01:21 | rolando   |
|  2 | 2013-02-09 09:01:21 | pamela    |
|  3 | 2014-09-07 09:01:21 | dominique |
|  4 | 2036-03-07 09:01:21 | diamond   |
+----+---------------------+-----------+
4 rows in set (0.00 sec)

mysql> SHOW CREATE TABLE felipe_table\G
*************************** 1. row ***************************
       Table: felipe_table
Create Table: CREATE TABLE `felipe_table` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `dt` datetime NOT NULL,
  `name` varchar(25) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

mysql> ALTER TABLE felipe_table MODIFY dt TIMESTAMP;
Query OK, 4 rows affected (0.31 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql> select * from felipe_table;
+----+---------------------+-----------+
| id | dt                  | name      |
+----+---------------------+-----------+
|  1 | 2013-02-16 09:01:21 | rolando   |
|  2 | 2013-02-09 09:01:21 | pamela    |
|  3 | 2014-09-07 09:01:21 | dominique |
|  4 | 2036-03-07 09:01:21 | diamond   |
+----+---------------------+-----------+
4 rows in set (0.00 sec)

mysql> SHOW CREATE TABLE felipe_table\G
*************************** 1. row ***************************
       Table: felipe_table
Create Table: CREATE TABLE `felipe_table` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `dt` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `name` varchar(25) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

mysql>

Looks like it works. If you do not trust this small sample, then make a copy of the table and perform this with the copy.

Give it a Try !!!


Clustered Index / Index Oriented Table performance in join, worse than nonclustered?

You are correct Erik.
In case a large portion of the leaf level of the clustered index needs to be read, the size of the other columns affects the amount of data that needs to be read, since the leaf level of a clustered index contains the table pages.

Nonclustered indexes contain the clustered index values for the ability to perform a lookup when a column that is not in the nonclustered index needs to be fetched. The optimizer can leverage this in order to fetch the clustered index values from there when it decided it's cheaper to do that.


Query to select specific columns based on current date

If you were able to start with a new table I would use this structure to store the data:

CREATE TABLE mrtg (
    city varchar(30)
    , logdate date
    , value int
)

This way you can store data for any time period and work out the days needed from the date.

Since you can't create that we'll have to see if we can modify what you've got in a query.
I've had a play and this will get the data into a table format that I believe would be easier to work with:

select data.*
  from mrtg
  left join (
    select city, 1 as day, day1 as datavalues from mrtg
    union all
    select city, 2 as day, day2 from mrtg
    union all
    select city, 3 as day, day3 from mrtg
    #....
    #union all
    #select city, 4 as day, day4 from mrtg
  ) data
  on mrtg.city = data.city
  where data.day <= DAYOFMONTH(STR_TO_DATE('2019-08-02','%Y-%m-%d'))

You'll have to add as many union all elements as you have columns but then you can filter on the number of days you want. You will now be able to GROUP BY and perform any calculations you need to.

select data.city, sum(data.datavalues) as TotalToDate
  from mrtg
  left join (
    select city, 1 as day, day1 as datavalues from mrtg
    union all
    select city, 2 as day, day2 from mrtg
    union all
    select city, 3 as day, day3 from mrtg
    #....
    #union all
    #select city, 4 as day, day4 from mrtg
  ) data
  on mrtg.city = data.city
  where data.day <= DAYOFMONTH(STR_TO_DATE('2019-08-02','%Y-%m-%d'))
  GROUP BY city

For keeping your main queries cleaner and smaller I'd recommend creating a VIEW for the subquery with all of the UNION ALL statements in.


RANGE clause error within window function

PostgreSQL doesn't support the RANGE clause yet. So even if you'd got the syntax right, it would've just failed with:

regress=> SELECT row_number() OVER (RANGE BETWEEN CURRENT ROW AND 42 FOLLOWING);
ERROR:  RANGE FOLLOWING is only supported with UNBOUNDED
LINE 1: SELECT row_number() OVER (RANGE BETWEEN CURRENT ROW AND 42 F...

To work around this, you can do a left outer join on generate_series(first_day, last_day, INTERVAL '1' DAY) then use a ROWS based window. That way a row exists for every day. It's a whole lot less efficient but unless you feel like getting your hands dirty in PostgreSQL's source code, it's probably the only option.


How can i use the same query for all codes?

It sounds like you want something like this:

SELECT invent.code_product,products.supp,products.price,
       MAX(products.date) AS Date
FROM products
LEFT JOIN list_product ON products.supp = list_product.supp
LEFT JOIN invent on list_product.id_product = invent.fk_product
Group by  invent.code_product,products.supp,products.price
Order by  products.date

To fully understand your comments on Date and Hour, it would be good to see your data definition and some representative data.


While the ASYNC_NETWORK_IO wait type is usually a consequence of the application design, the network issues can also cause the stockpiling of the ASYNC_NETWORK_IO wait type.

It is advised to check the following, regarding the network issues:

Check network bandwidth between the SQL Server and client
Make sure that all network components between the SQL Server instance and the client, such as routers, switches, cables are properly configured, fully functional and dimensioned according to required bandwidth
Review the Batch requests per second counter values, as this could often indicate the reason for high ASYNC_NETWORK_IO waits
Make sure that Auto Negotiate of the NIC is detecting the network bandwidth properly

Also -checking the NIC bandwidth utilization is prudent, even often overlooked.

Please, take a look at the Reducing SQL Server ASYNC_NETWORK_IO wait type aticle, to get more details on interpreting high ASYNC_NETWORK_IO wait type values.


Advice on indexing a search table, should I or shouldn't I?

You should use only InnoDB.  Period, Full Stop.

MyISAM is going away.  MyISAM is rarely faster than InnoDB.

With 5.6, you get FULLTEXT indexing in InnoDB.  With 5.7, you get SPATIAL indexing.  So, upgrade if you need to.


You don't need the temp table:

You can do this in a single statement:

with temp_x (id, id2, data, created_at, updated_at) AS (
   SELECT *
   FROM set_gernating_function(p)
), deleted as (
  DELETE FROM x
  WHERE NOT EXISTS (SELECT 1
                    FROM temp_x
                    WHERE temp_x.id = x.id)
    AND id2=p.id2
)
-- UPSERT by (id, id2)
INSERT INTO x
SELECT *
FROM temp_x z
  ON CONFLICT(id, id2) DO UPDATE
    SET updated_at = excluded.updated_at,
        data = excluded.data;


Synchronise two databases (but not really)

Yes, but by using tricks. MySQL has statement based replication, which causes the same SQL to be executed on the target as on the source. So the question is, can you write a SQL statement that does what you want? The answer is yes if you can exclude the rows in the target somehow. One way to do this would be a column that is 0 on all the sources, but which you update to 1 on the target by some mechanism (batch job, trigger, etc). Then when you execute the DELETE on the source WHERE ... AND is_target=0 the statement will do nothing when it is replicated.

There is no way that I am aware of to simply have the target system filter out deletes (this can be done with with Shareplex so I assume GoldenGate too, but that costs money).


Unable to connect to oracle database. Getting ORA12505 error

This: The listener supports no services.

Your database is not started, or it is registered into a different listener.

Assuming you have the simplest default configuration, just start your database with oracle user:

$ . oraenv
ORACLE_SID = [oracle] ? orcl
...
$ sqlplus / as sysdba
SQL> startup

Do not forget case sensitivity when providing the value for ORACLE_SID.


You need to escape the single quotes in the parameters, like:

exec
('
bulk insert weblog from '''+@datapath+'access1.log''
with
(
  firstrow = 1,
  FIELDTERMINATOR =''  '',
  ROWTERMINATOR=''\n'',
  MAXERRORS=99999999

)
');

Use the PRINT command to see what the command contains prior to EXECing it:

DECLARE @datapath VARCHAR(260);
SET @datapath = 'C:\some\path';
PRINT
('
bulk insert weblog from ''' + @datapath + '\access1.log''
with
(
  firstrow = 1,
  FIELDTERMINATOR =''  '',
  ROWTERMINATOR=''\n'',
  MAXERRORS=99999999
)
');

This provides the following output:

bulk insert weblog from 'C:\some\path\access1.log'
with
(
  firstrow = 1,
  FIELDTERMINATOR ='  ',
  ROWTERMINATOR='\n',
  MAXERRORS=99999999
)

The query pattern you are using is referred to as "dynamic SQL" and can be very problematic, especially for beginners, due to the problems with single quotes.  Try running the following:

SELECT 'this is a string';
GO

DECLARE @somevar VARCHAR(50);
SET @somevar = 'test';
SELECT 'this is a ' + @somevar + ' test';
GO

DECLARE @somevar VARCHAR(50);
SET @somevar = 'test';
SELECT 'this is a '' + @somevar + '' test';
GO

At first glance, it might appear the third option should print something like:

  this is a 'test' test

However, the output is:

Note the third result includes the name of the variable, instead of the content of the variable.  In order to make the result correct, we need to write the statement like:

DECLARE @somevar VARCHAR(50);
SET @somevar = 'test';
SELECT 'this is a ''' + @somevar + ''' test';
GO

Notice the + @somevar + is now wrapped with three single quotes.  This is equivalent to writing, and is indeed shorthand for, this:

DECLARE @somevar VARCHAR(50);
SET @somevar = 'test';
SELECT 'this is a ' + CHAR(39) + @somevar + CHAR(39) + ' test';
GO

Please refer to Erland Sommarskog's article on Dynamic SQL for the last and definitive word on how to use this powerful, yet sometimes confusing, feature.

Also, please be aware that dynamic SQL, although powerful and irreplaceable, can be a security nightmare through something known as SQL Injection.  Please take a look at this technet page for details about how to identify and mitigate that.  Also, you may want to search Duck Duck Go for "Little bobby tables", which takes you to this:


Performance tuning a cascading delete with many foreign keys

To answer your main question directly, the sorts are there to present rows to update operators (performing deletions in this case) in index key order. The principle at work here is that sorting on the keys will promote sequential access to the index.

This can be a good optimization, though the details depend on your hardware, how likely the affected pages are to be in memory, and whether the sorts can complete within the memory allocated to them. When the optimizer decides the cost of sorting will be paid back by the increased efficiencies associated with sequential index access, it sets a property DMLRequestSort on the update operator:

The optimizer may also decide to split the update into separate operators to maintain the clustered index (or heap) and then the nonclustered indexes. often, it will decide to sort more than once - first for the clustered index keys, and then again for the nonclustered index(es). Again, where sorting is considered optimal, each index update operator will have the DMLRequestSort property set to true.

All that said, the things I would fix first would be to eliminate the index scans where the join operator they feed is a nested loops join, and to remove the eager index spools, which are inserting rows into an empty index every time the query is executed. An eager index spool is often the clearest possible sign that you are missing a useful permanent index. The seek predicate in the index spool operator identifies the keys the optimizer would like an index on.

Examples of tables that are missing a nonclustered index (requiring an eager index spool) are:

child6gc8Selections
gc9s
child7s
gc6s

Examples of tables that are currently being scanned below a nested loops join are:

child1
parentObjectMessages
child8s
child7s
child6s
child5s
child4s
child3s
child2s

Taking the example shown above, the Clustered Index Scan has an output list of Id, parentObjectId, the Nested Loops Join predicate is child7s.parentObjectId = parentObject.Id, and the join output column list is child7s.Id.

From that information, it seems a good access method (index) on child7s for this part of the query would be keyed on parentObjectId with Id as an included column. You should be able to work out how best to work this into your existing indexing strategy.

The following are examples of tables where the optimizer is currently choosing a hash join. I would check tables like this to ensure that is a reasonable access method:

child6gc8Selections
gc2s
gc5s
gc6Properties

The table child2bigChild also participates in a merge join where an explicit sort is necessary. Again, I would check to see if this sort could be avoided.

Once the basic indexing issues are resolved, we can look at other optimizations if necessary.


If you are upgrading in place, you shouldn't need to transfer any databases; the instance will retain information on databases.

If you are installing a new named instance or installing it fresh on a different server, you have two major options:  deatch-and-attach, and backup-and-restore.

The detach-attach option is pretty quick:  detach the current database using sp_detach_db or in SSMS, right-click on the database and go to Tasks --> Detach.  Then, you can cut and paste the database files to their new location.  On the new instance, use sp_attach_db or in SSMS, right-click the Databases folder and select "Attach..." from the menu options.

The backup and restore option is pretty simple:  take a backup of the current database.  Then, copy that backup someplace where the new instance can see it, and restore from that backup.  You'll have a copy of the current database on the 2008 instance, as well as on the 2005 instance.

Here are some considerations:

If you need the 2005 database up during this time, use backup-and-restore.
The larger the database, the more likely you'd want to detach and attach, especially if this is staying on the same server and you're under a time crunch.  Taking backups and restoring from backups can take longer than shuffling bits across the WAN (or, best-case, updating a file pointer in Windows).  It also will mean more disk space in use, as backups take up some non-zero amount of space.
If you are currently using replication, you can't detach until you disable publishing (as the BOL link above indicates).
If you currently have any snapshots of the database, you can't detach until you drop all snapshots.  If you don't want to drop those snapshots, go with the backup and restore method.


Best practices on dealing with devs that want to use XP_CMDSHELL to BCP OUT

There are two things at play here

Data security
Server health

For 1, management has to be okay with any developer walking out the door with whatever data they export to a text file. This might be sensitive PII, it might be protected IP, or just customer lists that a competitor would want.

For 2, any one of these developers that has sysadmin can change settings, shut down the server, take backups, run DBCC commands, and a whole bunch of other stuff. If management is going to let them do that, then management and the developers have to be on the on call rotation to fix things when a developer with sysadmin messes something up.

Also, xp_cmdshell doesn't just write files out, it can interact with the file system, executables, and nearly anything else you can dream of. You could use it to map a network drive to a DropBox folder and... Well, you get the idea.

BCP doesn't necessarily need xp_cmdshell to work. Maybe time to talk about the process behind that.

  Permissions

  A bcp out operation requires SELECT permission on the
  source table.

  A bcp in operation minimally requires SELECT/INSERT permissions on the
  target table. In addition, ALTER TABLE permission is required if any
  of the following is true:

Hope this helps!


How to extend oracle database to a newly added hard disk

As beginner, I strongly recommend to start working with the classical way on filesystems.
Then you must be aware, where do you need more capacity within your database.
Ensure, that the new disk is available on OS. Then go ahead with following hints and steps:
(Very high level, simple and the classic way)

Users can create objects (Tables, Indexes, Views, Procedures, Sequence, ...)
Objects, which are storing any kind of data are segments, e.g. tables/indexes.
Each table is always stored within only one Tablespace.
Users can locate different tables/indexes etc. into different Tablespaces.
Tablespaces consist of one or more datafiles.
A DBA can add as many datafiles to a tablespace as he likes (ALTER TABLESPACE ADD DATAFILE).
Each new datafile enlarges the capacity of only this tablespace.

To answer your question, you need to know, which tablespace needs the additional space. Then you can add a new datafile with command above (there are tons of examples) and locate the datafile to the mountpoint of your new disk.
If you are new, you should start with a database on standard filesystems and play around with datafiles. Other options like ASM are not a good starting point for beginners, if you want to learn.


PostgreSQL GiST compress skeleton when no compression required

I think this would actually work, but I would follow the same format used in the docs for consistency on the decompress version that doesn't require compression,

PG_FUNCTION_INFO_V1(my_decompress);

Datum
my_decompress(PG_FUNCTION_ARGS)
{
    PG_RETURN_POINTER(PG_GETARG_POINTER(0));
}

My assumption here is that because you don't actually need to initialize a new GIST entry to return for Pg to write it disk, and because you're just using binary stuff, you can use one function for both

PG_FUNCTION_INFO_V1(id);

Datum
id(PG_FUNCTION_ARGS)
{
    PG_RETURN_POINTER(PG_GETARG_POINTER(0));
}

But you may have to DETOAST too which the docs eerily don't mention.


It's not cross-joined because the pipelined function is row-dependent; you can't cross-join row 1's values from the pipelined function to row 2's values because row 1's pipelined function values don't exist anymore.

If the pipelined function weren't row-dependent - if it went off of a static value - I suspect it would cross-join.  (Try it and see!)


How to remove NULL values in multidimensional array?

You can do it the slow and verbose way:

SELECT name, description, odobj, defval, datatypeid
     , CASE WHEN arrayofdatatypevalues = '{{NULL,NULL,NULL,NULL,NULL}}'::text[]
            THEN '{}' ELSE arrayofdatatypevalues END AS arrayofdatatypevalues
FROM  (
   -- your org. query here
   ) sub;

My educated guess, though: you don't actually want FULL [OUTER] JOIN to begin with. Looks like a LEFT [OUTER] JOIN to me. (Basics in the manual here.)

...
FROM   public.datatype dt
LEFT   JOIN datatypevalue dtv USING (datatypeid)
...

If so, transform it into into a LATERAL subquery or just a correlated subquery:

SELECT dt.name, dt.description, dt.odobj, dt.defval, dt.datatypeid
     , ARRAY(
          SELECT ARRAY[dtv.description::text,dtv.odobj::text,dtv.name::text,dtv.datatypevalueid::text,dtv.datatypeid::text]
          FROM   public.datatypevalue dtv
          WHERE  dtv.datatypeid = dt.datatypeid
          ) AS arrayofdatatypevalues
FROM   public.datatype dt
WHERE  dt.name <> ALL ('{Integer,String,Boolean}'::text[]);

Returns {} instead of {{NULL,NULL,NULL,NULL,NULL}} out of the box if no matching row is found in table datatypevalue. But still returns {{NULL,NULL,NULL,NULL,NULL}} for a single rows with all retrieved columns holding an actual NULL value, which can be a very important distinction to make. (Probably not possible with your setup - another guess in want of actual table definitions.)

The need for GROUP BY goes away.
While being at, use a faster ARRAY constructor instead of array_agg(). See:

Why is array_agg() slower than the non-aggregate ARRAY() constructor?

db<>fiddle here for Postgres 12.


Pushing or appending to JSON array in PostgreSQL 9.4

In pre-9.5 you can use json_array_elements, and array_to_json(array_agg()), like this.

SELECT array_to_json(array_agg(x))
FROM (
  SELECT jsonb_array_elements('[1,2,3]'::jsonb)
  UNION ALL SELECT '4'::jsonb
) AS t(x);

You can also write this using the ARRAY constructor like this..

SELECT array_to_json(ARRAY(
  SELECT jsonb_array_elements('[1,2,3]'::jsonb)
  UNION ALL SELECT '4'::jsonb
));


Why doesn't first execution plan use RowCount Spool?

This is just a cost based decision.

There are roughly 2,000 rows in the table for all v values from 0 to 4 inclusive and no other values of v.

The semi join can stop requesting rows after the first one is returned so SQL Server assumes it will only need to read 5 rows before it finds the first one matching v=4.

When you add having count(*) > 0 it now needs to read the whole table to get the COUNT(*) and can't stop after the first matching row is found. With v=40 this doesn't exist in the data at all but SQL server again needs to read the whole table to be sure of this.

The cost of reading the whole table for every outer row vs just reading a few rows from it is sufficient to make the plan with the row count spool optimisation attractive.

You can try the following with v=4 and v=40 and diff the results in a text diff tool.

SELECT v
FROM   dbo.t
WHERE  EXISTS (SELECT 1 FROM dbo.t WHERE v = 40)
OPTION (RECOMPILE, QUERYTRACEON 3604, QUERYTRACEON 8615);

The results of the final memo are very comparable in terms of options explored but a different final result was chosen on cost grounds.

The plan with the spool chose PhyOp_LoopsJoinx_jtLeftSemi 6.2 13.1 and without it was PhyOp_LoopsJoinx_jtLeftSemi 6.2 8.2.

6.2 is the clustered index scan that is the same on both plans.

8.2 is the scan with filter executed 10,000 times.

13.1 is the spool built from executing the scan with filter once.

Those parts of the memo are copied below. When v=4 the cost of the spool is 1.0043 but the cost without the spool is lower (at 0.867567)

v=40

8.2 PhyOp_Filter 7.1 4.0  Cost(RowGoal 0,ReW 9999,ReB 0,Dist 0,Total 0)= 158.804 (Distance = 1)
13.1 PhyOp_Spool 8.4  Cost(RowGoal 0,ReW 9999,ReB 0,Dist 0,Total 0)= 1.03564 (Distance = 2)

v=4

8.2 PhyOp_Filter 7.1 4.0  Cost(RowGoal 1,ReW 9999,ReB 0,Dist 0,Total 0)= 0.867567 (Distance = 1)
13.1 PhyOp_Spool 8.4  Cost(RowGoal 1,ReW 9999,ReB 0,Dist 0,Total 0)= 1.0043 (Distance = 2)

The hint OPTION (NO_PERFORMANCE_SPOOL) removes the row count spool from the v=40 plan for comparison purposes.

I was expecting that USE HINT ('DISABLE_OPTIMIZER_ROWGOAL') would do the opposite for the v=4 plan but despite group 8.2 now being costed in the memo at 158.804 it still gets selected and the overall plan is costed at only 0.939204 as the anti semi join costing itself still scales down the costs to reflect that it will only require one row from that input. Moreover with this hint in place there is no PhyOp_Spool option available in the final memo anyway.

Paul White points out in the comments that the performance spool can be achieved with the use of OPTION (QUERYTRACEON 8691).

If the following batch is run then the second query should show up as being more than 50% of the cost of the batch (i.e. more expensive according to SQL Server's cost model)

declare @v int;
select @v = v from dbo.t where exists(select 1 from dbo.t where v = 4)
select @v = v from dbo.t where exists(select 1 from dbo.t where v = 4) OPTION (QUERYTRACEON 8691);


First of all in this scenario you have two remote MySQL server you need to have two connections.
MySQL does not support such a query wherein it connects to other server and get details, in my understanding no other RDBMS does this kind of thing.

INSERT [LOW_PRIORITY | HIGH_PRIORITY] [IGNORE]
    [INTO] tbl_name [(col_name,...)]
    SELECT ...
    [ ON DUPLICATE KEY UPDATE col_name=expr, ... ]

Conclusion:
NO. You can't write such query on Two Mysql servers.


Find the last visit by teacher and show the rest of columns in MySql

You need to join all the teacher's last visit_time value against the whole visits table

SELECT B.* FROM
(
    SELECT teacher,MAX(visit_time) visit_time
    FROM visits GROUP BY teacher
) A INNER JOIN visits B USING (teacher,visit_time);

Based on this query, you should also add the following index

ALTER TABLE visits ADD INDEX teacher_visit_time_ndx (teacher,visit_time);

Give it a Try !!!


(A pseudo-answer, since it has been several weeks.)

Libraries tend to work across a wide range of server versions.  I cannot address the specifics here, but suspect there would not be an issue.


How to get data from three different tables

This query:

Uses ANSI JOIN (LEFT, INNER, ...)
Uses LEFT JOIN for each table in order to count Sessions IPs without Input or Downloads
Uses DISTINCT for each COUNT in order to remove duplicates added by the JOIN between tables
Counts values for totals and counts ids for unique counts

Query:

SELECT s.ip
    , COUNT(DISTINCT s.id)
    , COUNT(DISTINCT i.id) as TotalInputs
    , COUNT(DISTINCT i.input) as UniqInputs
    , COUNT(DISTINCT d.id) as files
    , COUNT(DISTINCT d.shasum) as Uniqfiles
FROM sessions s
LEFT JOIN input i
    ON s.id = i.session
LEFT JOIN downloads d
    ON s.id = d.session
GROUP BY s.ip;

SQL Fiddle

Output:

ip  | COUNT(DISTINCT s.id)  | TotalInputs   | UniqInputs    | files | Uniqfiles
IP2 |       1               |       3       |   2           | 1     |   1
IP3 |       3               |       5       |   4           | 3     |   2


Replicate update statement as Insert in Mysql 5.6

How about using INSERT ... ON DUPLICATE KEY UPDATE or REPLACE INTO on master itself?


Replication fails because of a non-existent foreign key

Sorry for waking up this dead thread, but I hate it when questions remains inconclusive.
Anyway, I had the same problem. Apparently SQL server replication saves foreign keys in "dbo.MSsavedforeignkeys", which is where I found my blocker FK. A quick delete dbo.MSsavedforeignkeys where constraint_name = N'FK_TableOnlyInDestDB_MyReplicatedTable' solved my problem. Of course, there may be more "best practice" ways of cleaning up after failed replications.


Database suggestion for a social network/ knowledge base community?

Consider also that there is no reason why you can't use a relational database for some things and the nosql database for other things.


The first looks like it is for RHEL5 and the second for RHEL6 (and clones) - judging by the filenames for the downloads:


SSIS Package Execution From Visual Studio Unit Test

First thing write another package that does a simple file copy task for example and see if that takes a long time using your same call method. If it does then perhaps your call method could be improved. I suspect it will be quick and the package code is where the problem is, not how its called.


Try like below, it will help you...

CREATE TABLE `read`
  (`order` MEDIUMINT NOT NULL AUTO_INCREMENT,
  `title` VARCHAR(50) NOT NULL,
  `url` VARCHAR(50) NOT NULL,
  PRIMARY KEY(`order`)
)DEFAULT CHARSET=UTF8;


Review Technical ETL process for knowledge management

Kimball Group books are great source of DW knowledge and ETL information in particular.

http://www.kimballgroup.com/html/booksMDWTtools.html

Follow this link, click Tools and Utilities, scroll to Chapter 7 — Design and Develop the ETL System.

Download Sample SSIS packages contains full solutions for Adventure Works.


Is there a workaround for lack of support of cascade triggers with MySQL?

The workaround, if that's the right word for it, seems like it would be to make the same changes to the child table in an AFTER UPDATE trigger on the parent table, containing the same logic as the BEFORE UPDATE trigger on the child table, wrapped inside a test to only do anything if the primary key in the parent table has actually changed, which would have caused a cascaded update.

DELIMITER $$
CREATE TRIGGER parent_after_update AFTER UPDATE ON parent_table FOR EACH ROW
BEGIN

# not using null-safe <=> comparison since PK cannot be null

IF NEW.primary_key != OLD.primary_key THEN

  UPDATE child_table ct
     SET /* the same logic that's in the before update trigger on the child table */
   WHERE ct.parent_primary_key = NEW.primary_key;

END IF;
END $$
DELIMITER ;

If primary_key on parent_table just now changed, then by definition, all of the rows child_table with the new value in their parent_primary_key column must have just been updated via a cascaded update, because until that value exists in parent_table.primary_key, there could not have been any rows already existing with the new value in child_table.parent_primary_key ... until the parent table's primary_key was updated just now, those rows could not possibly have existed, since they would have violated the referential constraint of the foreign key (referencing a nonexistent value).

Of course, I'm using "primary key" here when in reality the foreign key could reference any superkey of the parent table.

However, this calls into question what you're really using the cascaded updates to accomplish in the first place and whether that application of cascaded updates constitutes a sound design.  You seem to be deriving another column from the value, and storing that derived column, and both of these seem like less-than-ideal practices.

Normally, a cascaded update, at least in principle if not in fact, seems like something that is not a change to the data, as an update changing the child table directly would be.  "These" child records reference "that" row in the parent table.  After a cascaded update, they still do, and nothing has changed, only the key is different.


MS SQL Server: Transactional replication not working but no errors are present

I'm still not sure what the issue was because nothing indicated a problem, but the fix ended up being to just generate DROP and CREATE scripts on the Transactional Replication Publication using SQL Server Management Studio and running them. After which, I had to reinitialize and generate a new snapshot since it was a new Publication technically.
I guess the old turn off / turn back on again trick for the win.


After a point-in-time restore, is there any way I can get the transactions during restore time?

In Azure SQL DB today, the answer is "no" - at least not the way you are asking the question.  You can't go hack the log since it is a managed service and it (the log format) can change over time and thus is not exposed to you.  Instead, you can achieve the same end by setting up active geo-dr for the database.  You let it sync up and you can break the connection when you want so that you have an exact copy with all of the most recent transactions at the point where you want to have it.


Allow another user to access my Oracle table

Has he tried "SELECT COUNT(*) FROM smahala.fishtable;"? If you grant select on a table in one schema to another schema that user should be able to access it by including the schema name in the query. However, if NOAA is using virtual private databases, then there may be other obstacles that prevent you from sharing data. If there isn't much data, you can try to export to a flat file and have the other person import the flat file. but this should probably get resolved by your internal DBA's. Since they know how the database is configured. You might also want to verify that you are using the same Oracle database.


Edit: sorry for not returning with a solution for this case.
The MS support suggested me change the language of user from us_english to NONE. The last response from the support was ... "Probably a memory leak when the user store token, which is 8 kb, needs to be created to store a certain word due to the default language setting. To have more details and confirm what word is causing this high memory usage, these other collections would be necessary. "
As I had no problem after the adjustment, it was not possible to capture any new dump and therefore the solution may have occurred due to another scenario including the applications that are used on this server. Thanks Brent Ozar by the hint


Mysql query - find out 1000th row from a table for each user

To get all the (1000th) rows, using the primary key of the table:

SELECT du.user_id
     , ua.*                               -- any other column you need
FROM
    ( SELECT DISTINCT user_id
      FROM user_activity
    ) AS du
  LEFT JOIN
    user_activity AS ua
      ON ua.pk =                          -- the PRIMARY KEY of the table
         ( SELECT ai.pk
           FROM user_activity AS ai
           WHERE ai.user_id = du.user_id
           ORDER BY created_at
               LIMIT 1 OFFSET 999
         ) ;

or using the (user_id, created_at) combination:

SELECT du.user_id
     , ua.*
FROM
    ( SELECT DISTINCT user_id
      FROM user_activity
    ) AS du
  LEFT JOIN
    user_activity AS ua
      ON  ua.user_id = du.user_id
      AND ua.created_at =
         ( SELECT ai.created_at
           FROM user_activity AS ai
           WHERE ai.user_id = du.user_id
           ORDER BY created_at
               LIMIT 1 OFFSET 999
         ) ;

I guess the number of rows in the table per user is high, so an index on (user_id, created_at, pk) would be good for efficiency.


Complex key constraint: allow only if uuid is new or another column matches

This is a normalisation issue as timestamp is functionally dependent on non_unique_id.

So there should be another table with primary key non_unique_id and the corresponding timestamp.

In SQL Server/Azure SQL Database you can enforce this with an indexed view though.

Create a view with schema binding and definition

SELECT non_unique_id,
       timestamp,
       COUNT_BIG(non_unique_id) AS Count
FROM   dbo.YourTable
GROUP  BY non_unique_id,
          timestamp

and then put a unique clustered index on column non_unique_id in the view.


Ok so this is what I would recommend:

Use a custom aggregate for a moving average.  the aggregate would use essentially an array to store values and return the average of the values in the array.   New values would be appended to the end, and old values dropped off.  One could use array_agg with unnest(), limit, and offset to do the work.
Use a windowing function to tie the moving average to the row.
Do a calculation between values in the above to generate your pattern....
Create another aggregate which gives you a moving pattern returning an array
Write an operator matching the array and use a windowing function call to tack this on to the returned record

I do think that the above can all be done in SQL with SQL-language functions.  It is, however, way too complicated to even get into sample code here.  However I can't help but wonder whether PL/R might not be helpful here as well.


Exception Details: System.Data.SqlClient.SqlException: Login failed for user

One of our user from another domain (...)

To authenticate against a resource in another domain, either there must be a domain trust established (ask your Windows sysadmins), or pass-through authentication must be used (very likely not in this case). This is true for Windows in general, not just SQL Server.

  Do I need to setup my SQL Server 2008 Enterprise in order to allow domain user (...)

If there isn't an existing Windows user or group login for the user, then yes, you'll need to create a new login.

  Kindly note that there might be more than 50 domain users that may required to access the web application and therefore it would be inefficient to add each domain user to the database's user rights

I agree -- with this type of scenario, I would recommend setting up a group in AD, and then creating a Windows group login in SQL Server. This way, access is controlled by AD group membership, and you don't have to mess around with changing logins in SQL Server once it's set up.


In normal circumstances the JOIN is the structure that would be recommended for performance reasons. However, it looks like you're attempting some kind of search functionality.
Btree indexes don't work well with the kind of filtering you're implementing:

WHERE LOWER(b.comment) LIKE '%home%'

Using a function on the data (LOWER) will render any index in place useless as well as the wildcard filtering %string%. The query will likely run okay on small tables, but will suffer the longer it gets more rows that are scanned. This access type is eased with Fulltext indexing and relative matching rather than table scanning.

You might also try using a dedicated search product with inverted indexing like Elasticsearch, Solr or Sphinx.


Does it make sense to create a new database for every user? (MySQL)

Humour form for begin:

  if You are plan have 10M active users, which will chat, send messages
  and pay for You at least couple of dollars per month - You must open
  Your own hosting provider, rather than try to use anybody.

If talking seriously:

Size of database limited only by plan (mean cost) - so 2-3Gb it just for smallest plans
10M users - most important, how many of them active and what approximate but realistic data they will generate
what historic period they really need? as example - store 1 week history in active database and all other in archive instance
and 10M separate databases - will be exactly killer of Your server and application (and by resource usage, and by code complicity and by logic)


What's the optimal server configuration for fastest inserting in a MySql database in this scenario?

There are two major things you can change. One involves the table and one involves a config setting.
TABLE CHANGE
You can increase SELECT and INSERT speed by changing the MyISAM table's row format to FIXED. In order to do do, you would run
ALTER TABLE mydb.mytable ROW_FORMAT=Fixed;

There is a drawback to this: This could potentially increase the table size 80-100%
I have discussed changing MyISAM row format before

May 10, 2011 : What is the performance impact of using CHAR vs VARCHAR on a fixed-size field?
Aug 12, 2011 : Which DBMS is good for super-fast reads and a simple data structure?
Jul 06, 2012 : MySQL table with 100,000 records queried often
Sep 26, 2012 : Choosing MyISAM over InnoDB for these project requirements; and long term options

CONFIG SETTING
MyISAM supports a special protocol called a concurrent insert. It works by appending new rows to a MyISAM as long as there are no gaps as a result of DELETEs.
Here is some good news: you can disable the gap check for concurrent insert
[mysqld]
concurrent_insert = 2

According to the MySQL Documentation on concurrent_insert, here is what happens for value 2:

Enables concurrent inserts for all MyISAM tables, even those that have holes. For a table with a hole, new rows are inserted at the end of the table if it is in use by another thread. Otherwise, MySQL acquires a normal write lock and inserts the row into the hole.

If the table is being heavily read, new INSERTs just get appended. When you are the only DB connection with a file handle to the table, then a normal gap check is done.
See the MySQL Documentation on Concurrent Inserts
EPILOGUE
These two changes combined may produce surprising performance results. Why? Using ROW_FORMAT=Dynamic would trigger a search for a gap large enough to hold a new row. If the table has ROW_FORMAT=Fixed, all gaps are the same size. A new row insertion would be as quick as possible because the first gap row searched is used immediately for the insertion.


I can't say anything about cPanel and phpmyadmin but In general i can do it by writing a simple script.

I have written a shell script for you

#!/bin/bash

# mysql credential
user="root"
pass="root"

# list of all databases
all_dbs="$(mysql -u $user -p$pass -Bse 'show databases')"

for db in $all_dbs
     do
        if test $db != "information_schema"
            then if test $db != "mysql"
            then mysql -u$user -p$pass $db -sN -e "UPDATE wp_users SET user_pass = MD5('somepassword') WHERE user_login ='admin' LIMIT 1;"
        fi
    fi
     done


DB2: Error when using NOT EXISTS with SYSIBM.DUAL in a batch query

This question is somewhat old, so you may have already figured out your problem, but if I may offer an alternative...

It may be better to use a MERGE statement in this situation (click the link for the publib entry):

MERGE INTO mySchema.myTable tab USING (
        VALUES ('abc', '123')
    ) AS merge (C1, C2)
    ON  tab.column1 = merge.C1
    AND tab.column2 = merge.C2
    WHEN MATCHED THEN
        IGNORE
    WHEN NOT MATCHED THEN
        INSERT (column1, column2)
        VALUES (merge.C1, merge.C2)

This will take the columns in the "fake" table merge, and compare them using the keys in the ON clause, and if there is not a match, then it will use the INSERT statement.  If there is a match, that row will just be ignored.

However, the availability of MERGE depends on your platform.  I'm fairly certain MERGE has been in Linux/Unix/Windows DB2 since v8 (although, you can only use PREPARE'd merges since 9.7), and it was added in z/OS DB2 in v9.1.  I don't know about the other platforms (AS/400, etc.).


Is MySQL trigger for 100 Millions daily updates can impact data base cluster performance?

It is true that triggers incur some cost to the database. You should think about it in terms of the number of index writes.
When you insert or update a table without triggers, you can estimate the cost by counting the number of indexes, plus the primary key, which is the clustered index. You have nine indexes, so this is 9+1 = 10 writes per insert or update.
If you also have a trigger, the execution of trigger code is a small overhead, but it is more important to count the cost of the index writes by statements in the trigger. The index writes are more costly, compared to the trigger code itself.
So if your insert/update spawns a trigger that writes to another table, the cost will be the 10 index writes for the original table, plus as many index writes as the number of indexes in the additional table. Suppose you have one more index in that second table, it would then be 11 index writes, for a total of 21. So it is slightly more than double the cost.
My knowledge of Aurora is that it disables the InnoDB change buffer, because of the distributed storage. This means that all index writes must be merged at the time of your insert/update.
If I were working on your database, I would just add an index to the original table, using pt-online-schema-change. This allows clients to read and write the table while the index is being built. We use this at my company, and we do hundreds of schema changes per week for large tables, with no downtime.
Pt-online-schema-change uses triggers while it's building the index, but those triggers are dropped after this change is done. Also note that if you have long-running transactions against the table, it will conflict with pt-online-schema-change, because it needs a brief exclusive metadata lock at the start and the end of its work.


Problem with dropping materialized views and mv logs taking a long time (or never) to complete

You can do:

truncate table STAGING_OWNER.CONTRACT_SUMMARY_MV_FR ;

This simply will flush the MV. Then:

dbms_mview.refresh('STAGING_OWNER.CONTRACT_SUMMARY_MV_FR',method=>'C',atomic_refresh=>FALSE);

this will speed up mview rebuild. If you still experience any troubles you must trace event 10053 to understand why refresh is taking so much time.


Is there a better alternative than LIKE for this query?

"Better" in what sense?

The LIKE version is sargable as you have no leading wildcards and can use index seeks so that is better in that respect.

In this case you could also consider

SELECT * FROM
TableA
WHERE acccode LIKE '1[3-6]1%'
      OR acccode LIKE '121-111%'

As a simplification though the fully expanded version might be more efficient.

If you have no useful index you might prefer just using

SELECT * FROM
TableA
WHERE LEFT(acccode,3) IN ('131','141','151','161')
      OR acccode LIKE '121-111%'

Either way it looks as though there is some meaning buried in acccode that might be best storing in a separate column.


Does dumping/restoring do the same as "vacuum full"?

Yes, dumping and restoring the database will have the same effect as VACUUM (FULL). But having to do either of these every day is painful.

You have too little storage for your database. Artificially keeping it compact conserves space and speeds up queries, but it may slow down data modifications. The proper solution is to increase your storage and give your database some “breathing space”. That will make your problems go away, and probably reorganization like this is unnecessary.


Overhead of decomposing complex query into multiple simple queries?

As a general rule of thumb, you should give the database as much information as possible about the task that you're implementing. How does this apply to your scenarios?

Scenario 1 (INSERT .. SELECT)

The database knows that you're about to bulk-move a whole set of data from one table or from one derived table to another. It can optimise the execution given:

The cardinality of the bulk operation
The various constraints / triggers that will be invoked
The amount of storage that needs to be reserved in the new table
etc etc.

Scenario 2 (SELECT, and the N x INSERT)

The database has no clue about the various prepared statements that you are going to send after the SELECT. Even if it were smart enough to collect long-term statistics and heuristics about what's going to come after your SELECT, it would be unwise to assume anything about the subsequent load. So, effectively, this scenario is most often much much worse than the other.

There are some remarks to place, though:

Even if you have a good application-design reason to partition INSERTs like that, you should then probably batch-send them to the database
One good reason to partition INSERTs is the fact that you can have more fine-grained control over transaction lengths. It is generally not good to insert millions of records in a long-running transaction with logging turned on. So, either turn off logging, or commit after N inserts

Conclusion

The above are remarks regarding your two specific scenarios. Real-world scenarios aren't that simple, but you've also stated that in the question. The point I'm trying to make is that in many cases, you should let the database perform bulk data operations, because that's what it's very good at.


How to manually invalidate a pull merge replication snapshot from the publisher

I looked in to the declaration of sp_changemergepublication and found out the way it invalidates the snapshot is a simple query to update the table sysmergepublications

if (@property like 'ftp%' OR @property in ('sync_mode', 'snapshot_in_defaultfolder',
        'alt_snapshot_folder', 'pre_snapshot_script', 'post_snapshot_script','compress_snapshot','keep_partition_changes',
        'use_partition_groups', 'publication_compatibility_level'))
and @snapshot_ready = 1
begin
    if @force_invalidate_snapshot = 0 and @snapshot_ready = 1
    begin
        raiserror(20607, 16, -1)
        goto UNDO
    end
    update dbo.sysmergepublications set snapshot_ready=2 where pubid=@pubid and snapshot_ready=1
    if @@ERROR<>0   GOTO UNDO
end

So if you want to invalidate all subscriptions and snapshots just run these two lines.

update dbo.sysmergepublications set snapshot_ready = 2 where snapshot_ready = 1
exec sp_reinitmergesubscription @upload_first = 'true'


Here is an example of forcing a plan for a statement inside a procedure using a plan guide:

USE tempdb;
GO

CREATE PROCEDURE dbo.test
AS
BEGIN

    SET NOCOUNT ON;

    SELECT svp.name AS login_name, dbp.name AS user_name
    FROM sys.database_principals AS dbp
    INNER JOIN sys.server_principals AS svp
        ON dbp.sid = svp.sid;

END
GO

-- Use Ctrl+M to capture the execution plan
-- You will see loop joins used
EXEC dbo.test;

Now generate the execution plan for this statement with a forced join strategy:

SELECT svp.name AS login_name, dbp.name AS user_name
FROM sys.database_principals AS dbp
INNER JOIN sys.server_principals AS svp
    ON dbp.sid = svp.sid
OPTION (HASH JOIN);

Copy the execution plan XML and paste it to the query window. Replace any occurence of ' (single quote) with '''' (four single quotes).

Your final parameter for the sp_create_plan_guide procedure will be in the form:

@xmlPlan = 'OPTION (USE PLAN N''your plan goes here'')'

Now you can see why four quotes: escape once to put the XML inside a string literal, escape twice to put the XML inside a string literal inside another string literal.

Make sure that the XML plan is pasted right after OPTION (USE PLAN N'' : any whitespace or newline will make it invalid.

DECLARE @planXML nvarchar(max) = 'OPTION (USE PLAN N''your modified XML plan here'')';

EXEC sp_create_plan_guide
    @name = 'UseHash',
    @stmt = 'SELECT svp.name AS login_name, dbp.name AS user_name
    FROM sys.database_principals AS dbp
    INNER JOIN sys.server_principals AS svp
        ON dbp.sid = svp.sid;',
    @type = 'OBJECT',
    @module_or_batch = 'dbo.test',
    @hints = @planXML
GO

-- Use Ctrl+M to capture the execution plan
-- You will see hash joins used
EXEC dbo.test;


Postgresql: Pattern match against array elements?

You can accomplish what you're asking for by doing something like this.

I created a table and data to help better illustrate what I'm doing.

CREATE TABLE logs (id serial NOT NULL PRIMARY KEY, lognum int, flightnum int);

INSERT INTO logs (lognum, flightnum) VALUES (1,6);
INSERT INTO logs (lognum, flightnum) VALUES (1,7);
INSERT INTO logs (lognum, flightnum) VALUES (1,8);
INSERT INTO logs (lognum, flightnum) VALUES (2,80);
INSERT INTO logs (lognum, flightnum) VALUES (3,12);
INSERT INTO logs (lognum, flightnum) VALUES (4,8008);

postgres@[local]:5432:postgres:=# SELECT * FROM logs;
 id | lognum | flightnum
----+--------+-----------
 13 |      1 |         6
 14 |      1 |         7
 15 |      1 |         8
 16 |      2 |        80
 17 |      3 |        12
 18 |      4 |      8008
(6 rows)

Time: 0.188 ms
postgres@[local]:5432:postgres:=#

Note that 1, 2, and 4 have flights with 8, 80, and 8008.

Now, using this query, find the lognums with 8 as a flightnum. This takes your original query with an array_agg, wraps another query around it to generates subscripts for each member in the array, which can be arbitrarily large. Finally, an outer query wraps that, which uses the generated subscript to allow you to do a comparison against each of the members of the flightnums array to see if they are LIKE '8%'.

SELECT lognum FROM (
                      SELECT lognum,
                      flightnums,
                      generate_subscripts(flightnums, 1) AS s
                      FROM (
                             SELECT lognum,
                                    array_agg(flightnum) AS flightnums
                                    FROM logs
                                         GROUP BY lognum
                           ) AS t1
                   ) AS t2
            WHERE flightnums[s]::text LIKE '8%' ORDER BY lognum;

Which gives you the following output

postgres@[local]:5432:postgres:=# SELECT lognum FROM (SELECT lognum, flightnums, generate_subscripts(flightnums, 1) AS s FROM ( SELECT lognum, array_agg(flightnum) AS flightnums FROM logs GROUP BY lognum) AS t1) AS t2 WHERE flightnums[s]::text LIKE '8%' ORDER BY lognum;
 lognum
--------
      1
      2
      4
(3 rows)

Time: 0.338 ms
postgres@[local]:5432:postgres:=#

As would be expected from the data above.

For further array manipulation needs, I would recommend reading up on their chapter in the PostgreSQL documentation arrays


Does TRUNCATE TABLE affects isolation level in Sql Server?

TRUNCATE table will take a SCH-M lock on the table.

Even at READ UNCOMMITTED level the SELECT query will need to take a SCH-S lock. This won't be possible until the TRUNCATE transaction has ended and the  conflicting SCH-M lock has been released.


Select data from another database instance on the same server in sql server

If the database that you are trying to connect to is on a different instance then I would recommend setting up a linked server which you can then use to obtain the data. This will allow you to explicitly use credentials which exist on the instance you want to connect to - it also keeps you query relatively simple by only needing to adjust to using a four-part name to reference the table.

-- create a linked server
USE [master]
GO
EXEC master.dbo.sp_addlinkedserver
     @server = '.\INSTANCE_NAME',
     @srvproduct=N'SQL Server' ;
GO
-- add a login to the linked server
EXEC master.dbo.sp_addlinkedsrvlogin
     @rmtsrvname = '.\INSTANCE_NAME',
     @locallogin = NULL,
     @useself = N'False',
     @rmtuser = 'user_name_from_other_instance',
     @rmtpassword = 'password_for_remote_user';
GO
-- now you can query the server
SELECT *
  FROM [.\INSTANCE_NAME].master.sys.databases;
GO

There are more options available for these stored procedures and you can also perform the same action using SQL Server Management Studio. I have included some links to the documentation below for you:

Linked Servers

Create Linked Servers

sp_addlinkedserver

sp_addlinkedserverlogin


When should we use table partitioning in Postgresql?

No, there is no real row number threshold. If you only have queries that select rows by primary key, the size of the table doesn't really matter.
Partitioning is also primarily a management tool to quickly remove no longer needed rows, not so much a performance tool.
It can be used to improve performance, but only if you have queries that only need a (small) subset of all the rows. If all queries (or at least all performance critical one) do contain the partitioning key, then partitioning can help with performance.
You also need to choose the partitioning key based on then number of partitions this results in. With Postgres 12 or later "thousands" of partitions are feasible (I have heard of users using ~20000 partitions successfully, but I think that's already a stretch). An excessive amount of partitions will most probably be not practical as it will make the planning of the queries a lot slower.
You should also take the fact into account, that a partitioned tables is limited in what the primary key can be - it has to include the partition key. So if you have foreign keys referencing the partitioned table, this might get complicated.


Sql 2016 STD SP1 Always On with DB Mirroring

Since only 1 dB is allowed for always on I have to setup other 4 db’s in db mirroring.

That makes absolutely no sense as they work basically the same way. Yes, there are some very real differences between them, but if someone told me "this works for mirroring but can't be used in availability groups" I'd ask why. the capture mechanism is roughly the same, the transfer mechanism is roughly the same, and the apply mechanism is roughly the same. When I say "roughly" I mean they are not 100% identical.

I'd put them all in AGs.

  My always on endpoint is already using port 5022. Is there a place in db mirroring where I can change 5022 to let’s say 7022?

No, because there can only be a single mirroring endpoint which is what both availability groups and mirroring uses. The reason that it's called a "mirroring" endpoint and not an "availability group" endpoint is two fold. First, mirroring came first. Second, if the name was changed it could cause a compatibility issue with older applications and code where the keyword would change, so they won't change it due to backwards compatibility.

I'd be happy to understand why someone is saying that a database can belong to mirroring but not belong to an availability group.


How to switch Quiescing to Normal Mode in Oracle?

You are mixing up quiescing a replication master group and quiescing a database — these are different concepts:

ALTER SYSTEM QUIESCE

Use the QUIESCE RESTRICTED and UNQUIESCE clauses to put the database in and take it out of the quiesced state. This state enables database administrators to perform administrative operations that cannot be safely performed in the presence of concurrent transactions, queries, or PL/SQL operations.

Master Group Quiesce

At times, you must stop all replication activity for a master group so that you can perform certain administrative tasks on the master group. For example, you must stop all replication activity for a master group to add a new master group object. Stopping all replication activity for a master group is called quiescing the group. When a master group is quiesced, users cannot issue DML statements on any of the objects in the master group. Also, all deferred transactions must be propagated before you can quiesce a master group. Users can continue to query the tables in a quiesced master group.

After quiescing a replication master group, RESUME_MASTER_ACTIVITY resumes normal replication activity.


If you are going to select some data inside a procedure then it needs to go into something.  You have not shown us the complete error message but I suspect it is

PLS-00428: an INTO clause is expected in this SELECT statement

Revise your code to provide a target of the same type as the data selected. I removed the commit as is not necessary unless you UPDATE/INSERT/DELETE something after changing it. Something like

    CREATE OR REPLACE PROCEDURE JAKARTA
    as
    --declare a type with all the same columns as the karyawan table
        TYPE karyawanType IS RECORD (
            id karyawan.id%type,
            alamat karyawan.alamat%type,
        );
    --declare a record of the type you created
    v_record karyawanType;

    BEGIN
    SELECT *
    INTO v_record
    FROM
    karyawan
    WHERE
    alamat = 'Jakarta';

    END jakarta;
    /


How can I select the next available row id of varchar type in MySQL?

SELECT id AS next_id FROM your_table WHERE id > @current_id ORDER BY id LIMIT 1

Breaking that down:

SELECT id AS next_id FROM your_table

that bit is hopefully obvious

WHERE id > @current_id

you don't want the current value or anything before it

ORDER BY id

in the output, list earlier ID values first

LIMIT 1

we only want one of them so no point going further once one has been ready from the table.

As an aside: is there a any particular reason why you are using a character field as the ID instead of a numeric one which would be more efficient?


SQL Server DROP Database which is in a long roll back process

You're just going to need to wait, unless you want to risk corrupting other databases by, say, yanking the power cord out of the wall. You can set your other user databases offline before attempting such a stunt, but good luck with system databases.

For a rollback you should be able to guesstimate how much longer you'll need to wait by running this query:

SELECT
  percent_complete,
  [est_finish] = DATEADD(MILLISECOND,[estimated_completion_time],CURRENT_TIMESTAMP)
FROM sys.dm_exec_requests
WHERE command LIKE N'%ROLLBACK'; -- could be KILLED/ROLLBACK

I blogged about this here (and it includes a much more exhaustive query):

When will my backup / restore / index reorganize finish?

If you know what operation is rolling back, you may also be able to infer some guesses on progress by looking at the columns reads,writes and logical_reads - but don't spend too much time on that, since I/O is what percent_complete is based on, so you'll probably come to roughly the same conclusion.

Still, this is just information that will help you set expectations about how long you need to wait. There isn't a very safe way to cancel a rollback.


INSERT OR REPLACE INTO + SELECT as source in Oracle

Table1: A (DATA1, DATA2, DATA3)
Table2: B (DATA1, DATA2, DATA3)

Assumption: First fieldName of both table is primary key

MERGE INTO A TA
    USING (SELECT * FROM B) TB
    ON (TA.DATA1 = TB.DATA1)
    WHEN NOT MATCHED THEN INSERT (TA.DATA1, TA.DATA2, TA.DATA3)
      VALUES (TB.DATA1, TB.DATA2, TB.DATA3);