Permission denied when trying to install sp_blitz on in the master database in Amazon RDS

That error message means you don't have enough permissions to install things in the master database.

That's okay: you can install sp_Blitz (and any other stored procs) in a user database instead, and they'll work fine there.

If you run into that same error in a user database (one you created), then you'll need to check with your sysadmins about your permissions.


how to decide whether table segregation is benificial or not

Some of your premises are not necessarily true therefore could lead to false conclusion.

  The thing with segregation is that query time will surely reduce

Properly sized and configured servers with the right indexes can still serve queries from tables in the TB scale in the milliseconds range. If you do point queries (single row lookups with primary key or fully covering secondary keys) then you will find that the significant time is spent on parsing the query string and authenticate/authorise the user not the actual data fetching.

  memory allocation will increase as well

By having more tables you're certainly not going to increase the memory allocation for MySQL. Number of tables has no influence on memory usage. Discussing memory allocation is out of scope now but this may be useful to take a look: MySQL memory calculator.

  there will be an increase in data input time

The insert time could only decrease not increase by having more tables.

Insert (and update) time can be improved by having more smaller table because the B+Trees become smaller. Given which storage engine you use it also matters if you insert in primary key order or random order. In case of InnoDB if you insert in random order there is a performance penalty of constant B+Tree rebalancing. In that case smaller tables could help.

However it does involve more logic on the application side and I don't know how much overhead that would be for your developers. Table partitions could be a middle ground here.

Do you delete from these table frequently?

If you do purging whole tables (or partitions) is much easier and can be done without affecting your production queries and replication (if you have) while DELETE FROM ... WHERE ... has to lookup the rows, it involves some locking (level is dependent on storage engine dependent), etc.

In this case splitting is certainly beneficial.

Do you want to archive the data?

MyISAM tables can just be simply copied over to another mysql server and it will just work. This is a very common and easy way to archive old data to a different server.

Splitting to tables by date work quite well in this case too.

Do you change your table structure frequently?

ALTERing many smaller table is more manageable than one huge.

These are the most common situations for splitting I can think of right now.

The question is do you experience any issues right now? Is there an explicit need to fix something? Can it be done by other ways of optimization? Configuration, indexes, query tuning, etc?

Testing

You can test your query time by replaying queries from tcpdump or slowlog.

You can also enable binary logs and replay insert/updates from ther eand see how fast they finish.

This is going to be a balance between many things including operability of the system so find what's work best for your use case.

I hope this helped.


Why is below 2 query generating different execution plan in MYSQL?

The explain depends on which column you using in WHERE clause, the column which are large in size that which doesn't fit in SORT BUFFER requires filesort for sorting purpose. And other point is b.id may be primary key which is sorted by default, So it doesn't requires to do sorting & where c.table_b_id requires sorting.


SOA and Oracle cascade data between two pdb

INTRO

If you use different database accounts for "Authentication Service" and "Customer Service", you can easily control what each REST Service can see and do.

This is what database are designed to do: Control of access to data.

By levering the database's ability to secure access to data, you can use a single USER_INFO table in a single database.

Conceptual Setup

Your USER_INFO table should be treated as (java parlance) private.

Since USER_INFO is private, you do not GRANT access on that table to the accounts used by the REST services.

You use VIEWS and PACKAGES as your (java parlance) public interface to that private data.

The account for "Authentication Service" will get access to one set of VIEWS and PACKAGES.
The account for "Customer Service" will get access to another set of VIEWS and PACKAGES.

A VIEW can easily limit what columns are displayed.  In some cases, you can control what Rows can be displayed.

A PACKAGES is just a collection of function/procedures.
eg registerUser and loginNamePassword would belong to PACKAGE AUTHENTICATION_SERVICE_API

You could, almost, think of a PACKAGE as a (java parlance) class.

Your design would look like this:

ANSWERS

You are now using a single table.  This isn't a concern
You are now using a single table.  Oracle doesn't do dirty data(*).

(*) I'm ignoring the DBMS package for 2-phase commits that allows you to do that.


I'm trying to run an oracle database in virtual box under oracle linux

Had the same issue. Turned out to be a firewall issue. Here is how I turned it off:

As root in terminal:
chkconfig iptables off
chkconfig ip6tables off
service iptables stop
service ip6tables stop

Then modify /etc/selinux/config and set SELINUX=enforcing to SELINUX=disabled.

A more elegant solution is to open the 1521 port in the firewall. You can do that in the System -> Administration -> Firewall menu. Add 1521 (tcp) under "Other ports".


Read a plain language description of your data. A Product is a noun. A noun can become an entity and an entity becomes a table (or several tables). The words buy and sell are verbs, actions that take place with entities. Verbs do not become tables. A suggestion was to have a transaction table. Fine, "transaction" is a noun and the actions buy and sell would be one of its attributes.

There are many ways to design such a schema based on your specific needs, but in general you would have one Products table with a complement of supporting tables. One such supporting table could be Comments. A comment is an attribute of the Product entity but you could normalize it to a separate table if you really wanted to. You should have a valid reason for doing so, however. For example, if you want to allow a comment on every state change, that will be several comments possible for each product. First normal form. That's a valid reason. But beware. Such comments would be attributes of the state change (transaction) rather than the entity itself.

Other supporting tables would help you track state changes to your entities. One such would be Transactions which could track "Ordered", "Received/Entered to Inventory", "Sold", "Shipped" and any other state you want to track. While a state is an attribute of the Product entity, there will be several of them (even if only one is current at any given time) so that would require normalization to a separate table.

Many books have been written on database design. But for your example the biggest no-no right now is creating a table to represent actions: buy and sell in your instance. That is going to be really difficult to work with.


PostgreSQL 10 Optimizing slow query performance with aggregate function

You really need an index on the array:

create index on person using gin (tags);

It is not obvious to me that the subselect is worse than any other way of accomplishing the same thing.

Your query plan doesn't match your query text, "is_deleted" occurs in the plan but not the text.  If you want to optimize for that you could do:

create index on person using gin (tags) where not is_deleted;

How important that is depends on what fraction of your table is_deleted.

An alternative method not needing the index would be

select * from
    (select unnest(tags) as id, count(id) from person group by 1) agg
join
    tag
using (id);

But this would not return any tags which had a count of zero.  You could make the join a right join, but then reported count would be null rather than zero.


Can I get results from a Postgres query that I got disconnected from?

Unfortunately you can't. However, if the query is for changing data and there isn't any transaction started, then it will make the change. Next time start a screen or save results to a file.


Cloning the database to another server with raw data files

One can use rman to duplicate a database. Here is the Database Backup and Recovery User's Guide.

But now let's assume that you have a copies of these files that where made when the database was down and where already copied to the places of the target system.

On the target server 1 you have the same type of perating system as on the source server 1. On the target server you have the same Oracle version with the same patch level as on the source server. You can check this by setting the oracle environment and executing the command

$ORACLE_HOME/OPatch/opatch lsinventory

and compare the output of both servers.

If you don't have the right environment you can clone the Oracle software from the source server using Oracle Universal Installer.

So let's assume that you have the correct software installed and set the correct environment of the Oracle instance of the target server.

what happens if you do a startup nomount of the database in sqlplus?

sqlplus first searches for

${ORACLE_HOME}/dbs/pfile${ORACLE_SID}.ora

if such a file does not exist it searches for

 ${ORACLE_HOME}/dbs/init${ORACLE_SID}.ora

and oracle is started with the parameters given in this files. I am not 100% sure about that. Maybe sqlplus starts oracle executable and the oracle executable searches for these files. But that does not matter for our purposes.

So the parameters of these file should have sensible values. This means, the pathes in these files should point to the correct files.

If not, you have to change them. The init.ora file can be modified with a text editor. An spfile must be transformed to a init.ora file by executing

create pfile='/tmp/myfile.txt' from spfile='&absolute_spfile_path';

in sqlplus while the instance is not running. &absolute_spfile_path must be substituted by the correct path.

then you can edit the file /tmp/myfile.tx with a text editor. You can change the pathes, if an instance name is in the file it must be modified, too. Memory parameters can be adapted. Underline parameter written by the instance should be removed, e.g. '_sga_target' and similar parameters.

and then create a modified spfile by issuing

create spfile='&absolute_spfile_path' from pfile='/tmp/myfile.txt';

Before you start you can make a copy of your files so that you can start again when something went wrong.

Make a copy of the init.ora and spfile.ora, the control files, the redo log files and the datafile of th SYS, SYSAUX and undo tablespaces. the datafile of the remaining tablespaces make readonly with the appropriate Linux command(chmod) so they can't be modified by oracle. Tempfiles are not needed from the source database.

Now you can start the instance with

startup mount

check the alert log if everything is ok. If there are errors then repair tthem.

If everything is fine then mount the dtabase

alter database mount;

Now the control file is read. You can rename all files now. In the control file their old name is stored. you can see this if you do a

select name
from v$datafile;

issue an

alter database rename file '&old_name' to '&new_name';

until all datafile are removed.

The same command can be used to rename the log files.

select member
from v$logfile;

shows you the names of the redo log files in the controlfile.

You can put the tempfiles offline to avoid eror messages when openinf the database

select name
from v$tempfile;

shows you then namesof the temp files.

alter database '&tempfileName' offline;

puts the file offline.

Now you can open the database

alte database open;

Now add new temp files with correct path and size to your temp tablespaces.

alter tablespace TEMP add tempfile ...;

and then remove the old ones

alter tablespace TEMP drop tempfile ...;

If you have made the daatafiles read only then shutdown the database, make the files read/write and start the database again.

Now you are done.


Restored database copy larger than original

You may find this surprising but this is something I have come to expect often. I have mentioned in other posts how data loaded in primary key order can populate BTREE indexes in a lopsided manner:

May 01, 2014 : Why would the size of MySQL MyISAM table indexes (aka MYI file) not match after mysqldump import?
Dec 28, 2014 : What are the optimal mysqldump settings? (See the CAVEAT about --order-by-primary)

This behavior is seen for MyISAM and InnoDB alike. Don't be too alarmed by this. If the data being copied is in order by an auto-incremented primary key when dumping, the clustered index is populated in a lopsided manner from inception.
When it comes to InnoDB, bulking loading data into a table with an index will have a degree of fragmentation introduced. You just have to bite the bullet on this.
To minimize this, you should do the following for table mytable:

Copy the table schema for mytable
Create empty mytable on target server
Drop indexes for mytable on target server
Load data into mytable on target server
Create all indexes

MyISAM already has this paradigm in place for non-unique indexes when reloading MyISAM tables

ALTER TABLE mytable DISABLE KEYS;
Load the data
ALTER TABLE mytable ENABLE KEYS;

InnoDB does not :-(
CONCLUSION
Your main concern should be the content of data.
You should run pt-table-checksum for the table for both source and target servers. Even with mismatches here, do not worry too much with VARCHAR columns (See my post Native checksum table says there's a difference, pt-table-checksum does not).
You could also just dump the data in a specific into CSV files on source and target servers and compare the text just to inspect content.
In closing, even though the table is small, once the copy is done and the table is not corrupt, try what you reasonably can to defragment your tables and just live with it.


In postgresql.conf, does the order of the listed parameters matter?

If you have several times the same parameter in postgresql.conf, the last value will override the previous ones.
I normally advise keeping the postgresql.conf file as short as possible to prevent confusion. See here.
My guess is that you misspelled something on the line you added. To be sure, you can look at Postgres logfiles which should tell you exactly what was wrong. (see here how to find your Postgres logfile.)


Static archive database: SQL Server can't shrink it and leaves ~400G of unallocated space

There's 2 potential things at play here.  Shrink's algo takes the last page and moves it to the first empty spot, this creates massive fragmentation and could also create forwarding records.  The better idea is to rebuild your database on a different filegroup using a clustered index that's already defined pre data population.

The other item could be a ghost clean up bug that was around in 2008.  I don't know what version you're running, but if it is 2008, ensure you are fully up to date on patches.

https://codingsight.com/moving-existing-table-from-primary-filegroup-to-different-filegroup/


Designing a Products database: One Products table or many separated by vendor?

I would go with number 1. Having a separate table for each vendor or each product type will be a nightmare when trying to run reports that need data from multiple product tables. You don't want to have to create a new table each time you add a vendor or product type.
I understand your inclination coming from a spreadsheet, but with the ability to query the records easily, separating the products into multiple tables is not the way to go.

  I am inclined to say that there is a downside because products of one category will have many NULLs in attributes meant for other categories, but I'm not sure if this is actually a negative.

To prevent this, attributes should be in a separate table.

For example, a partial table definition may be as follows:

products
-- id
-- name

product_attributes
--product_id
--name
--value

attributes.product_id is a foreign key of products.id.

To further normalize it you could define the attributes in a separate table and have an attribute_id field instead of attribute_name:

    attributes
    --id
    --name

    product_attributes
    --attribute_id (foreign key of attributes.id)
    --value

(edited for spelling)


MySQL Conditional Counter Based Other Columns?

SELECT t.*,
CASE WHEN CONCAT(t.CustomerName, CHAR(0), t.PurchaseDate) = @group
     THEN @num:=@num+1
     ELSE @num:=1
     END PurchaseNumberForDate,
@group:=CONCAT(t.CustomerName, CHAR(0), t.PurchaseDate) groupname
FROM datatable t, (SELECT @group:='', @num:=0) vars
ORDER BY t.CustomerName, t.PurchaseDate

Variant without additional field:

SELECT t.*,
CASE WHEN CONCAT(t.CustomerName, CHAR(0), t.PurchaseDate) = @group
     THEN @num:=@num+1
     ELSE @num:=1
     END + CONCAT(CHAR(0), (@group:=CONCAT(t.CustomerName, CHAR(0), t.PurchaseDate))) PurchaseNumberForDate
FROM datatable t, (SELECT @group:='', @num:=0) vars
ORDER BY t.CustomerName, t.PurchaseDate;

fiddle


Database Design Techniques, Notations and Terminology

There are basically just two things here:

Your data model, which is the structure of your entities, attributes and relationships.
The graphical representation of your data model.

Your data model may be high level (entities/relationships only) or lower level (add in attributes with details of data types) and it may be logical or physical.  It all depends on what stage of your design process you're at.  The point of a data model is to record your database design, which is just an expression of your business requirements for persisting data.

Crows foot and ERD etc. are just graphical conventions for showing your data model pictorially.  The great thing about standards is that there's so many to choose from, and this is also true of graphical conventions.  Read this wiki article for a sampling.

Which conventions you choose may depend on what you need to communicate about your model, or it might just depend on what conventions make sense to you.

Don't get too wound up about all the different terminology.  It's all just your database design and how you choose to record and communicate it.


Check whether a key value is referenced in any related table

Given this data:

USE tempdb;
GO

CREATE TABLE dbo.Books
(
  BookID int PRIMARY KEY,
  title varchar(32)
);
-- insert 3 rows
INSERT dbo.Books(BookID, title) VALUES(1,'no relations'),
    (2,'one relation'),(3,'all relations');

CREATE TABLE dbo.OverdueBooks
(
  BookID int NOT NULL FOREIGN KEY REFERENCES dbo.Books(BookID)
);
-- insert 1 row
INSERT dbo.OverdueBooks(BookID) VALUES(2);

CREATE TABLE dbo.OneStarBooks
(
  BookID int NOT NULL FOREIGN KEY REFERENCES dbo.Books(BookID)
);
-- insert 2 rows
INSERT dbo.OneStarBooks(BookID) VALUES(2),(3);

You can avoid knowing all of the tables and columns that reference the parent by building dynamic SQL from the metadata about the foreign keys:

-- all you need to identify is the table / column that is the parent:
DECLARE @parent nvarchar(800) = N'dbo.Books.BookID';

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

SELECT @sql += N'
UNION ALL SELECT ' + QUOTENAME(c.name) + N' AS id FROM '
 + QUOTENAME(s.name) + N'.' + QUOTENAME(o.name) + N' WHERE '
 + QUOTENAME(c.name) + N' = ' + PARSENAME(@parent,2) + N'.' + PARSENAME(@parent,1)
FROM sys.foreign_keys AS fk
INNER JOIN sys.foreign_key_columns AS fkc
ON fk.object_id = fkc.constraint_object_id
INNER JOIN sys.columns AS c
ON fkc.parent_object_id = c.object_id
AND fkc.parent_column_id = c.column_id
INNER JOIN sys.objects AS o
ON c.object_id = o.object_id
INNER JOIN sys.schemas AS s
ON o.schema_id = s.schema_id
WHERE fk.referenced_object_id = OBJECT_ID(PARSENAME(@parent,3)+N'.'+PARSENAME(@parent,2));

SET @sql = N'SELECT Books.BookID, Books.Title,
  IsReferenced = MIN(CASE WHEN x.id IS NOT NULL THEN ''True'' ELSE ''False'' END)
  FROM dbo.Books
  OUTER APPLY
  (
  ' + STUFF(@sql,1,CHARINDEX(N'SELECT',@sql)-1,N'') + N'
  ) AS x(id)
GROUP BY Books.BookID, Books.Title;';

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

When you're happy with the PRINT output (if you have tons of foreign keys, it may get truncated; see this tip for ideas), uncomment the EXEC. My print output looked like this:

SELECT Books.BookID, Books.Title,
  IsReferenced = MIN(CASE WHEN x.id IS NOT NULL THEN 'True' ELSE 'False' END)
  FROM dbo.Books
  OUTER APPLY
  (
  SELECT [BookID] AS id FROM [dbo].[OverdueBooks] WHERE [BookID] = Books.BookID
UNION ALL SELECT [BookID] AS id FROM [dbo].[OneStarBooks] WHERE [BookID] = Books.BookID
  ) AS x(id)
GROUP BY Books.BookID, Books.Title;

And when I ran that I got the expected:

BookID  Title          IsReferenced
------  -------------  ------------
1       no relations   False
2       one relation   True
3       all relations  True

Don't forget to clean up tempdb:

DROP TABLE dbo.OneStarBooks, dbo.OverdueBooks, dbo.Books;


Sounds like you've got some sort of corruption problem on the disk which the SQL Agent logs get written to.  I'd check the disk for corruption, as well as the C drive in case it's talking about the application log which the SQL Agent also writes to.


Oracle driver and language specific chars

Indeed, the OLEDB provider from Mircrosoft is deprecated for ages and you should not use it anymore. Certainly it can have problems when you work with Unicode.

Use the OLEDB Provider from Oracle. After installing it, it should be sufficient to modify just the connection string from connectString = "Provider=MSDAORA;Data Source=... to connectString = "Provider=OraOLEDB.Oracle;Data Source=....

OLEDB Provider from Oracle always use UTF-16 (see OraOLEDB Provider Specific Features), so german "Umlaute" should be no problem.

Note provider msdaora also uses local Oracle Client installation (like OraOLEDB.Oracle), you cannot use it without any Oracle Client installation.

In order to verify properly what is stored at database level try

select DUMP(c.custname, 1016) FROM ...


What's the advantage of thread pool in MySQL/MariaDB?

The main advantage of the thread pool is that it generally limits the number of running operations to the value of thread_pool_size which is generally set to the number of CPUs on the server or how many MySQL will be using.  It prevents context switching and contention issues with other internal structures.  It is particularly helpful when there are many more queries to run than there are available CPUs.

Limiting connections with max-connections would limit the number of running queries by limiting the number of connections but any connections over that limit are denied with an error.  When using the thread pool more connections are allowed but the queries are queued by the thread pool.  Another thing to consider is that connections aren't always running queries so limiting the connections does not effectively limit running queries.

To the point about the thread cache, it does help when using thread per connection but the thread pool does its own thing with reusing threads.

max-connections can still be used to limit the connections but as more of a safeguard with a value of something like 10000 rather than a limiter for the amount of work for the server.

For example if you have 100 clients that want to run queries with a desired limit of 10 queries running concurrently, setting the connections limit to 10 will only allow 10 client to connect with the others being rejected with an error.  There would be at most 10 queries running at the same time but it is likely that on average there will be less than 10 queries running at a time.  Without limiting the connections, if all 100 of the clients wanted to run a query at the same time, there would be 100 threads executing at the same time which could cause contention issues leading to less throughput than with 10 connections.

If you were to use a thread pool size of 10 instead, all 100 clients would be able to connect and run their queries.  If all of the clients try to run a query at the same time, the thread pool will queue the queries and generally execute 10 in parallel which can have a higher throughput than the 100 concurrent queries in the previous example.


How crash recovery process works in SQL Server?

The Comparison of RECOVERY and NORECOVERY section of the RESTORE Statements doc should help you with that question:

Rollback is controlled by the RESTORE statement through the [ RECOVERY
| NORECOVERY ] options:

NORECOVERY specifies that rollback doesn't occur. This allows    rollforward to continue with the next statement in the sequence.
In this case, the restore sequence can restore other backups and roll    them forward.

RECOVERY (the default) indicates that rollback should be performed    after rollforward is completed for the current backup.
Recovering the database requires that the entire set of data being    restored (the rollforward set) is consistent with the
database. If    the rollforward set has not been rolled forward far
enough to be    consistent with the database and RECOVERY is
specified, the Database    Engine issues an error.


How I can flatten a count table in postgresql that contains only 2 columns with crosstab?

Use conditional aggregation:
select count(*) filter (where category = 'category1') as category1,
       count(*) filter (where category = 'category2') as category2,
       count(*) filter (where category = 'category3') as category3
from the_table;


Restoring backup from mysqldump does not remove additions

mysqldump has an --add-drop-database option that will put those in the file for you.

It will add this, before each database in the file:

/*!40000 DROP DATABASE IF EXISTS `accounting`*/;

For the benefit of anyone unfamiliar with magic comments, this isn't a comment.  It's a statement that will be executed by MySQL 4.00.00 and higher, disguised as a comment for backwards-compatibility.

And, of course, this assumes your database, like mine, is called "accounting."

Note, though, that for --add-drop-database to work, you need to specify the database(s) to back up using either --all-databases or the --databases YOUR_DB_NAME command line option to mysqldump.  You "can" make a backup of a single database with just the database name on the command line by itself, without the --databases option preceding it... that also sort of works but is really not a good way to go, unless you intend to restore the backup into a different database name than the one you got it from, which, thanks to the way that option works, you might do even if it wasn't your plan.

Using --databases YOUR_DB_NAME or --all-databases automatically adds CREATE DATABASE and USE your_db_name; statements to the top of each schema in the file.  This means you don't have to specify it when you're restoring, and if you forget or get it wrong, the data still goes to the right place -- the "right" place being the place the file says it should go.

The --add-drop-database option adds the DROP statement to clear the way for the new one, immediately before the CREATE and USE statements.


PostgreSQL 9.1 runs into inconsistent data, but pg_dump corrects the problem?

Might be index corruption - assuming you have an index on sal_id.

If so, REINDEX TABLE salXXXX; (or just REINDEX INDEX index_name would do the same trick as your dump/restore cycle, which also recreates all indexes from scratch.

The manual advises:

  Note that while this method does not require locking out other
  clients, it might still be wise to prevent other users from connecting
  to the damaged database until repairs have been completed.

Generally, consider upgrading to a current version of Postgres. Version 9.1 has just reached its EOL in Sept. 2016. There have been many related updates since, might very well fix the problem for good.


Integrating large PostgreSQL table as partition for a new table

Proceed like this:

Don't create a primary key constraint on the partitioned table for now.
Rather, create unique indexes on the new partitions that will later become partitions of the primary key index.

As soon as the large table is old enough that you don't need the data any more, drop it.

Then you can create a primary key on the partitioned table that will use the already existing unique indexes as partitions.


Keyword Search a single SQL Server table for multiple search terms and create a new table from results?

Your search:

SELECT col
  FROM dbo.existing_table
  WHERE col LIKE '%keyword1%'
     OR col LIKE '%keyword2%'
     ...;

To put the results into a new table:

SELECT col
  INTO dbo.new_table_name
  FROM dbo.existing_table
  WHERE col LIKE '%keyword1%'
     OR col LIKE '%keyword2%'
     ...;


SQL Server Management Studio cannot Connect to Always On Availability Listener

When you configure your listener to use a non-default port (any port other than 1433), then you need to specify the port number in the server name when connecting in order to connect successfully.

This is detailed in the documentation here.


This specific case (the exists predicate) is a bit of an outlier as far as correlated subqueries go.  ish...

Some correlated subqueries are certainly evaluated once for every row in the outer query, but I don't think you can apply that across the board, and I specifically think that's a statement that cannot be applied with authority to the exists predicate.

The SQL example below this paragraph is highly contrived, but nevertheless illustrates an "evaluated once for each row in the outer query" correlated subquery. It's "highly contrived" because a straight relational join would be a much better solution, and in this simple case the join is super easy to write.

select
    team_name
    , (
       select league_name
       from leagues
       where leagues.league_id = teams.league_id
    ) as league_name
from teams

In this instance, each row in the result set is going to contain the name of the league that the team belongs to, because of the correlated subquery. And the correlated subquery is probably going to be run once for each row in the outer query. Maybe.

The thing is, the database's query optimizer might rewrite this as a more efficient join, depending on the database. So even this simple example might not be executed "once per row in the outer query."

Either way, the subquery is dependent on the values returned from the table in the outer query, and that makes it a correlated subquery--because of the correlation with the outer table.

Part of the point of SQL in general is that the specific implementation is not important to you.

You should think in terms of sets of data (think set arithmetic), not in terms of iterations and comparisons per iteration.

The exists predicate doesn't make you any promises about the particular implementation. In general, exists is supposed to take advantage of table statistics and indexes in such a way that it produces a more efficient query plan than many other approaches will (and more efficient than you're likely to come up with on your own).

The actual query that ends up being run is going to be some kind of join, or multiple joins. But you don't need to get worked up about that. That's why your exists clause contains select * instead of identifying specific fields.

The query plan produced by the database engine will figure out which fields it wants to use, which key or keys and which index or indexes matter, etc.

So your exists example is still a correlated subquery, despite the underlying implementation, because the inner query refers to and is logically dependent upon the table in the outer query.

SELECT DISTINCT store_type FROM stores
WHERE EXISTS
    (
    SELECT * FROM cities_stores
    WHERE cities_stores.store_type = stores.store_type
    );


updating 1st 5 digit of a column value in sql

Something like this? If value consists of digits only, replace first 5 characters with 1s; else, do nothing.

SQL> with test (col) as
  2    (select '999999999' from dual union all
  3     select '123456789' from dual union all
  4     select 'MIS'       from dual union all
  5     select 'ab12345cc' from dual
  6    )
  7  select col,
  8    case when regexp_like(col, '^\d+$') then 11111 || substr(col, 6)
  9         else col
 10    end result
 11  from test;

COL       RESULT
--------- ---------------------
999999999 111119999
123456789 111116789
MIS       MIS
ab12345cc ab12345cc

SQL>


How to retain the current value of a sequence when copying a database in SQL Server

You can get the current seed values by running:

SELECT
   s.name as [schema],
   t.name as [table],
   IDENT_CURRENT( s.name + '.' +t.name )  NextIdentValue,
   'DBCC CHECKIDENT ('+char(39)+s.name+'.' +t.name+char(39)+',RESEED,'+ CAST(IDENT_CURRENT( s.name + '.' +t.name ) as varchar(50))+ '); ' as cmdSQL
FROM sys.schemas AS s
INNER JOIN sys.tables AS t
  ON s.[schema_id] = t.[schema_id]
WHERE EXISTS
(
  SELECT 1 FROM sys.identity_columns
    WHERE [object_id] = t.[object_id]
);

Run the cmdSQL script column on the target system.

Depending on your requirements, you can store the output to a table/file and then execute the synchronization script after restore.


Error Correction: Conversion Failed when converting the nvarchar value 'ALPHANUMERICVALUE' to data type int

The problem seems to be on the Coalesce's.

  Returns the data type of expression with the highest data type
  precedence. If all expressions are nonnullable, the result is typed as
  nonnullable.

Source

For example:

select coalesce('101R',0)

Gives the error:

Msg 245, Level 16, State 1, Line 1
Conversion failed when converting the varchar value '101R' to data type int.

This works fine:

select coalesce('101R','0')

Result:

(No column name)
101R

If the lag function on e.accountingcode can't be NULL, then you could use ISNULL(), but you should look into the differnces with coalesce.

  Returns the same type as check_expression. If a literal NULL is
  provided as check_expression, returns the datatype of the
  replacement_value. If a literal NULL is provided as check_expression
  and no replacement_value is provided, returns an int.

Source

Or in other words, if the first value is not a NULL value, return that datatype, if it is NULL and a replacement value, in our case 0 is passed,  then it will be that datatype. If all are NULL then it will be an integer.

Solution #1, changing 0 to '0'

SELECT
   e.accountingcode AS [Vehicle],
   Concat(e.year + ' ', e.make + ' ', e.model + ' ', Cast( e.[Description] AS VARCHAR(80))) AS NAME,
   Iif(cff.stringtype = '', cff1.stringtype, cff.stringtype) AS 'Operator',
   fta.fluid,
   so.odometer,
   CASE
      WHEN
         COALESCE(Lag(e.accountingcode) OVER ( partition BY e.accountingcode
ORDER BY
   e.accountingcode, so.odometer), '0') <> e.accountingcode
THEN
   0
ELSE
   Lag(so.odometer) OVER ( partition BY e.accountingcode
ORDER BY
   e.accountingcode, so.odometer)
   END
   AS 'Previous Odometer', so.odometer - Iif((
   CASE
      WHEN
         COALESCE(Lag(e.accountingcode) OVER ( partition BY e.accountingcode
ORDER BY
   e.accountingcode, so.odometer), '0') <> e.accountingcode
THEN
   0
ELSE
   Lag(so.odometer) OVER ( partition BY e.accountingcode
ORDER BY
   e.accountingcode, so.odometer)
   END
) = 0, so.odometer,
   CASE
      WHEN
         COALESCE( Lag(e.accountingcode) OVER ( partition BY e.accountingcode
ORDER BY
   e.accountingcode, so.odometer), '0') <> e.accountingcode
THEN
   0
ELSE
   Lag(so.odometer) OVER ( partition BY e.accountingcode
ORDER BY
   e.accountingcode, so.odometer)
   END
) AS 'Miles Traveled', sh.hour,
   CASE
      WHEN
         COALESCE(Lag(e.accountingcode) OVER ( partition BY e.accountingcode
ORDER BY
   e.accountingcode, sh.hour), '0') <> e.accountingcode
THEN
   0
ELSE
   Lag(sh.hour) OVER ( partition BY e.accountingcode
ORDER BY
   e.accountingcode, sh.hour)
   END
   AS 'Previous Hours', sh.hour - Iif((
   CASE
      WHEN
         COALESCE(Lag(e.accountingcode) OVER ( partition BY e.accountingcode
ORDER BY
   e.accountingcode, sh.hour), '0') <> e.accountingcode
THEN
   0
ELSE
   Lag(sh.hour) OVER ( partition BY e.accountingcode
ORDER BY
   e.accountingcode, sh.hour)
   END
) = 0, sh.hour,
   CASE
      WHEN
         COALESCE(Lag(e.accountingcode) OVER ( partition BY e.accountingcode
ORDER BY
   e.accountingcode, sh.hour), '0') <> e.accountingcode
THEN
   0
ELSE
   Lag(sh.hour) OVER ( partition BY e.accountingcode
ORDER BY
   e.accountingcode, sh.hour)
   END
) AS 'Hours Used' , fta.fifototalcost / fta.totalgallons AS [Avg Price], fta.totalgallons

Solution #2, changing coalesce to ISNULL()

Educate yourself on the differences with coalesce, for example only do this when the LAG() function on e.accountingcode can't return NULL, which kind of defeats the purpose of the isnull

SELECT
   e.accountingcode AS [Vehicle],
   Concat(e.year + ' ', e.make + ' ', e.model + ' ', Cast( e.[Description] AS VARCHAR(80))) AS NAME,
   Iif(cff.stringtype = '', cff1.stringtype, cff.stringtype) AS 'Operator',
   fta.fluid,
   so.odometer,
   CASE
      WHEN
         ISNULL(Lag(e.accountingcode) OVER ( partition BY e.accountingcode
ORDER BY
   e.accountingcode, so.odometer), 0) <> e.accountingcode
THEN
   0
ELSE
   Lag(so.odometer) OVER ( partition BY e.accountingcode
ORDER BY
   e.accountingcode, so.odometer)
   END
   AS 'Previous Odometer', so.odometer - Iif((
   CASE
      WHEN
         ISNULL(Lag(e.accountingcode) OVER ( partition BY e.accountingcode
ORDER BY
   e.accountingcode, so.odometer), 0) <> e.accountingcode
THEN
   0
ELSE
   Lag(so.odometer) OVER ( partition BY e.accountingcode
ORDER BY
   e.accountingcode, so.odometer)
   END
) = 0, so.odometer,
   CASE
      WHEN
         ISNULL( Lag(e.accountingcode) OVER ( partition BY e.accountingcode
ORDER BY
   e.accountingcode, so.odometer), 0) <> e.accountingcode
THEN
   0
ELSE
   Lag(so.odometer) OVER ( partition BY e.accountingcode
ORDER BY
   e.accountingcode, so.odometer)
   END
) AS 'Miles Traveled', sh.hour,
   CASE
      WHEN
         ISNULL(Lag(e.accountingcode) OVER ( partition BY e.accountingcode
ORDER BY
   e.accountingcode, sh.hour), 0) <> e.accountingcode
THEN
   0
ELSE
   Lag(sh.hour) OVER ( partition BY e.accountingcode
ORDER BY
   e.accountingcode, sh.hour)
   END
   AS 'Previous Hours', sh.hour - Iif((
   CASE
      WHEN
         ISNULL(Lag(e.accountingcode) OVER ( partition BY e.accountingcode
ORDER BY
   e.accountingcode, sh.hour), 0) <> e.accountingcode
THEN
   0
ELSE
   Lag(sh.hour) OVER ( partition BY e.accountingcode
ORDER BY
   e.accountingcode, sh.hour)
   END
) = 0, sh.hour,
   CASE
      WHEN
         ISNULL(Lag(e.accountingcode) OVER ( partition BY e.accountingcode
ORDER BY
   e.accountingcode, sh.hour), 0) <> e.accountingcode
THEN
   0
ELSE
   Lag(sh.hour) OVER ( partition BY e.accountingcode
ORDER BY
   e.accountingcode, sh.hour)
   END
) AS 'Hours Used' , fta.fifototalcost / fta.totalgallons AS [Avg Price], fta.totalgallons


MySQL Installation error: Unable to update security settings

Problem was with root access and firewall.

To fix this I:

Ran it on a machine with full root acces
Added all MySQL items to
firewall list and ran reconfiguration of the MySQL

Ability to access root is important. I tried repeating this on a machine were I do not have root access and failed on every attempt


How to get average of views before clicking on advertisement?

Ok, it take me some time but I think it works well:

select clickcume nr_clicks, avg(view_b) avg_view_before from(
select userid, clickcume, max(viewcume) view_b from(
select a.*, b.time_v, b.viewcume  from
(
  select userid, time as time_c,
         count(*) over (partition by userid order by time) as clickcume
  from views) a join
(
  select userid, time as time_v,
         count(*) over (partition by userid order by time) as viewcume
  from clicks) b
  on (a.userid=b.userid)
where time_c>=time_v ) c
group by userid, clickcume ) d
group by clickcume


Does ORDER BY COUNT(*) DESC suffer a penalty?

The optimizer might build a hash table -- key == values of col, value = count.  Or it might extract all the col values.  But in either case, it will have to do some kind of sort at some point.

When everything fits in RAM, the cost of the sort is minimal.  As a rule of thumb, when the GROUP BY and the ORDER BY are different, then there will be at least one sort, sometimes two.

The only optimization you can do for that query is INDEX(col).  Then, at least, it is working with the index BTree, and the values of col are known to be sorted.  Hence it walks through the index counting up the counts.  Still there is a sort at the end.

As for whether DESC costs anything versus ASC, yes and no.  In the B+Trees that InnoDB uses, it is slightly more efficient to walk forward (ASC) than backward (DESC).  But that is not relevant here.  The sort for the ORDER BY does not use a BTree if it is in RAM -- Instead it is a Quicksort (or something).

Or is this what you are asking?

  SELECT col, COUNT(*)
    FROM tbl
GROUP BY col
ORDER BY COUNT(*) DESC

versus

  SELECT col, COUNT(*)
    FROM tbl
GROUP BY col
-- no ORDER BY

There definitely is an extra sort.  Without the ORDER BY (ASC or DESC), you will probably get the output sorted by col.  In fact, until a recent version, you were guaranteed to get it sorted by the GROUP BY.  This was an extension to the standard, that has been removed.


Temporarily disable checking of constraints

In order to get your new narrower index used for foreign key constraint validation you will need to drop and re-create the foreign keys.

You will then see in sys.foreign_keys that they are bound to the better index and it will use that for validation.

NB: This isn't documented anywhere but is definitely the way it appears to work!


Issue migrating from SQL Server 2008 R2 to SQL Server 2017 Standard (on Linux)

There is a similar, but not exactly the same, question here:

Azure SQL Data Migration Assistant is Stuck on "Migrate Data"

However, it does explain the limitation you're hitting:

  The Extract service connects to a database, reads all of its objects and their properties, and then creates an in-memory model of the database.

If the database you're trying to migrate has an exceptional number of schema objects, this in-memory DAC model potentially overwhelms the client application / system (Data Migration Assistant running on your PC in this case), resulting in the OutOfMemoryException.

Your next best bet would be to use tried and true approaches like backup / restore in order to migrate to the new machine.


does the mere existence of a primary key increase query speed for large data sets?

Having a primary key per se will not speed up queries. Primary key constraints are usually accompanied by a unique index. If this index matches query predicates or join conditions then those queries are likely to run faster.


Informix: is it necessary to update statistics on views?

There's no need to update statistics on a view in an Informix database.  Indeed, AFAIK, there's no way to do so; statistics are not stored for views.


How to know when/if I have too many indexes?

Your load profile is the most important factor in answering this question.

If your load is read-intensive, you want indexes to satisfy your heaviest or most frequent queries.
If your load is write-intensive, index carefully. Index to satisfy a seek an UPDATE needs, for example, as well as your one or two most expensive SELECTs.
If your load is an OLAP one, index sparingly since you will be scanning the target tables anyway.

How do you know you have too many indexes?

When you can see that some of them are not used by any queries.
A frequent DELETE, UPDATE or INSERT shows a query plan that involves several expensive index changes (i.e. a nonclustered index insert, update, or delete). Use your judgement to determine if the penalty on those DML statements is worth the gain you are getting from the indexes that have to be updated.


@Arun Raut, As you said that you want to know that memory (RAM) allocation in PostgreSQL.

What processes use much RAM and why?

Shared memory is accessible by all postgres server processes.

Normally the most part of it is shared_buffers. As per PostgreSQL BOL Here  PostgreSQL suggest to use 25% of RAM, though often less values are used.

The wal_buffer are normally much smaller, 1/32 of shared_buffer is default. Anyway, you are allowed to set it to arbitrarily large value.

The amount of memory used for table and advisory locks is

About 270 * max_locks_per_transaction
* (max_connections + max_prepared_transactions) bytes

you are probably safe, unless you are doing something tricky using lots advisory locks and increase
max_locks_per_transaction to really large values

Same for max_pred_locks_per_transaction - predicate locks are used only for non-default transaction isolation levels, make sure not to increase this setting too much.

No more than autovaccum_max_workers workers, each uses
maintenance_work_mem or autovaccum_work_mem of RAM.

Ideally, your tables are not too large and your RAM is not too small , so you can afford setting autovaccum_work_mem to reflect your smallest table size.

Practically, you will autovaccum_work_mem to cover all the small tables in your DB, whatever that means.

Backends and their bgworkers are the most important , as there might be quite a few of them, namely max_connections and max_workers.

The work_mem parameter limits the amount of RAM used per operation, i.e. per execution plan node, not per statement.

For your further ref Here, Here and https://stackoverflow.com/questions/30328861/most-impactful-postgres-settings-to-tweak-when-host-has-lots-of-free-ram


SQL Query- field calculation based on date

One solution to this problem would be to create a static calendar table. In the scope of your problem, this table would be important because there are holidays that must be considered when calculating the week's goals, as it is not fair to compare the targets an entire week with a partial week (holidays).

The downside of these static tables is proper management and organization of the calendar for the New Year's and holidays change. It is very important to put reminders in this routine to minimize the chance of a possible future problem.

Consider this risk for the proposed solution.


Mongodb automatic Replica Set creation with JS driver

The method rs.initiate can only be used in the MongoDB shell, for drivers you have to use Database Commands.

For example:

myMongoClientVariable.db("myDB").admin().command( {
    replSetInitiate : {
        _id : "myReplicaSet",
        members : [
            {_id : 0, host : "host0"},
            {_id : 1, host : "host1"},
            {_id : 2, host : "host2"}
        ]
    }
} );


Using Python to insert JSON into PostgreSQL

There's no need to have python parse the json; just pass through the file contents, retrieved with .read(), to cursor.execute() as a parameter (& make sure to wrap in in a tuple, as shown below). And don't forget connection.commit() or you'll never actually see the inserted data as it'll be rolled back when the connection closes after python finishes running.

import psycopg2
connection = psycopg2.connect(connection_string)
cursor = connection.cursor()
cursor.execute("set search_path to public")

with open('data.json') as file:
    # change json.load(file) to file.read()
    data = file.read()

# Just put a placeholder %s instead of using {} and .format().
query_sql = """
insert into json_table select * from
json_populate_recordset(NULL::json_table, %s);
"""

# change .execute(query_sql) to .execute(query_sql, (data,))
cursor.execute(query_sql, (data,))
# Add a commit on the connection.
connection.commit()


SSDT: Create User in a database not being part of the project

The action described in the question should not be a problem. I was able to create a Post Deployment script and add the following code:

USE [tempdb];
GO
PRINT DB_NAME();

IF (USER_ID(N'g') IS NULL)
BEGIN
    PRINT 'Creating user [g]...';
    CREATE USER [g] WITHOUT LOGIN;
END;
ELSE
BEGIN
    PRINT 'Dropping user [g]...';
    DROP USER [g];
END;

It builds and publishes with no errors, and executes the commands as expected.

Make sure that your SQL script is truly a Post Deployment script. If it is set to Build Script then it probably will get those errors. Look at the properties of the script and make sure that Build Action is set to PostDeploy.

And please make sure that you are using the latest update of SSDT. The most recent version can be found here:

Download Latest SQL Server Data Tools


Below is the script as per your need

SELECT h.SERVER AS [Server Name]
  ,j.NAME AS [Job Name]
  ,CONVERT(TIME, a.start_execution_date) AS [Normal Start Time]
  ,CONVERT(TIME, a.last_executed_step_date) AS [Normal End Time]
  ,CONVERT(TIME, a.run_requested_date) AS [Actual Start Time]
  ,CONVERT(TIME, a.stop_execution_date) AS [Actual End Time]
  ,h.run_time
FROM msdb.dbo.sysjobhistory AS h
INNER JOIN msdb.dbo.sysjobs AS j ON h.job_id = j.job_id
INNER JOIN msdb.dbo.sysjobactivity AS a ON h.job_id = a.job_id
WHERE h.run_date = (
      SELECT CONVERT(VARCHAR, GETDATE(), 112)
      )
  AND H.run_time >= (
      SELECT REPLACE(REPLACE(CONVERT(TIME(0), CONVERT(TIME, DATEADD(HOUR, - 1, GETDATE()))), '.', ''), ':', '')
      )
  AND h.step_id = 1
  AND a.start_execution_date IS NOT NULL
  AND a.last_executed_step_date IS NOT NULL
  AND a.run_requested_date IS NOT NULL
  AND a.stop_execution_date IS NOT NULL


Perhaps you could create an SSIS script process to dynamically build the insert commands and execute them.

--Demo setup
drop table if exists MappingTable;
create table MappingTable
    ([StageTble] varchar(9), [StageColumn] varchar(9), [RealTable] varchar(8), [RealCol] varchar(6), [OrdinalPosition] int)
;

INSERT INTO MappingTable
    ([StageTble], [StageColumn], [RealTable], [RealCol], [OrdinalPosition])
VALUES
    ('tblStage1', 'Field_ABC', 'tblDest1', 'FldABC', 1),
    ('tblStage1', 'Field_ZXC', 'tblDest1', 'FldZXC', 2),
    ('tblStage1', 'Field_QWE', 'tblDest1', 'FldQWE', 3),
    ('tblStage2', 'Field_PLM', 'tblDest2', 'FldPLM', 1),
    ('tblStage2', 'Field_WER', 'tblDest2', 'FldWER', 2),
    ('tblStage2', 'Field_YTE', 'tblDest2', 'FldYTE', 3)
;

SSIS script task

--SSIS script task
--NOTE: the actual EXECUTE sp_executesql @cmd is commented out
Declare @Cmd nvarchar(max)
;with DistinctTables as
(
select distinct [RealTable], [StageTble] from MappingTable
),
InsertCommands as
(
select *,
'insert into ' + [RealTable] + '(' +
STUFF((
        SELECT ',' + [RealCol]
        FROM MappingTable t1
        where t1.RealTable = drt.RealTable
        ORDER BY OrdinalPosition
        FOR XML PATH(''), TYPE).value('.', 'VARCHAR(MAX)'),1,1,'')
+ ')' +
' select ' +
STUFF((
        SELECT ',' + [StageColumn]
        FROM MappingTable t1
        where t1.RealTable = drt.RealTable
        ORDER BY OrdinalPosition
        FOR XML PATH(''), TYPE).value('.', 'VARCHAR(MAX)'),1,1,'')
+ ' from ' + [StageTble]  as InsertCommand
 from DistinctTables drt
)
select @cmd =
STUFF((
        SELECT ';' + char(10) + [InsertCommand]
        FROM InsertCommands
        FOR XML PATH(''), TYPE).value('.', 'VARCHAR(MAX)'),1,1,'')
+';' from InsertCommands ic

print @cmd
--EXECUTE sp_executesql @cmd

Produces the following dynamic SQL

insert into tblDest1(FldABC,FldZXC,FldQWE) select Field_ABC,Field_ZXC,Field_QWE from tblStage1;
insert into tblDest2(FldPLM,FldWER,FldYTE) select Field_PLM,Field_WER,Field_YTE from tblStage2;


Saving LZW encoded data into a mySQL database

You most likely have codepage issues here, where some parts of the process is interpreting the binary data as text and performing some sort of conversion instead of leaving it well alone. This may not be in your database, it could also be in the server-side code that is performing the inserts/updates and selects, or it could even by your browser that is interfering at one end of the process - so this question may be better suited to StackOverflow. Whether the question belongs here on over on SO, you need to give a lot more detail to get a good answer. Extra detail that would be useful includes:

What datatype(s) are you using to store the information? mySQL has specific types for binary data which have no collation so you should be using them not normal text types - if you are not then this is most likely the cause of the problem you are experiencing.
What language and database bindings are you using for the server-side code?
How are you getting the data into the database? Fully manufactured (ad-hoc) SQL? A parametrised statement? Stored procedure call?
How are you getting the data out again?
How is the data being corrupted? Extra characters added? Characters dropped? Changes to specific characters? Almost complete bejigglement of the data? Providing an example of the input data and the corrupt output may help if you can create a small example (adding massive amounts of data to your question won't be a popular action).

One option to remove the need to worry about such codepage issues is to keep your data in plain ASCII which should result in clean passage in, through & out of almost anything. You can do this by encoding your data using base64 immediately after compression and decoding it immediately before decompression. This has one obvious significant disadvantage though: you are going to increase the size of the stored data by 33%. If you are getting a large compression rate from the input data this may not be a massive issue (if you are getting compressed data at about 25% of the original then you are still getting ~33% after base64).


SQL Server 2012 - The application has failed to start because its side-biy-side configuration is incorrect

Open an elevated command prompt (Run As Admin)
Type sfc /scannow

The sfc /scannow command will scan all protected system files, and replace corrupted files with a cached copy that is located in a compressed folder at %WinDir%\System32\dllcache. The %WinDir% placeholder represents the Windows operating system folder.


Reset mongo cache memory for test perfomance - windows

stop mongod
sh -c "sync; echo 3 > /proc/sys/vm/drop_caches"
start mongod

stop mongod: Stops the database service. sh -c "sync; echo 3 > /proc/sys/vm/drop_caches": write any data stored in memory to disk. start mongod: Start the database again.


Is it possible to set auto-increment-increment by 10 for only one table?

According to the MySQL documentation, you can't set custom auto-increment value for a specific table.

  It is not possible to restrict the effects of these two variables to a single table; these variables control the behavior of all AUTO_INCREMENT columns in all tables on the MySQL server.

However, this value has both global and session level effect. You may set it for session level and can perform required tests. And the changes will not persist after restarting your database server.

Reference:
auto_increment_increment


Informix v12 substitute for query using multiple with statements

I might be wrong but give a shot

SELECT
bear.wrk_id,
bear.vct_id,
bear.bearing,
bear.bearing_datum,
dist.dist_equipment_type,
bear.bear_equipment_type,
dist.distance,
bear.surveyed_class AS bear_surveyed_class,
bear.geodetic_class AS bear_geodetic_class,
bear.adopt_bear_wrk_id,
bear.bear_adopt_plan,
dist.surveyed_class AS dist_surveyed_class,
dist.geodetic_class AS dist_geodetic_class,
dist.adopt_dist_wrk_id,
dist.adopt_dist_wrk_id,
bear.cos_id AS cos_id,
bear.ref_datetime,
bear.stp_id_local AS bear_stp_id_local,
bear.stp_id_remote AS bear_stp_id_remote,
dist.stp_id_local AS dist_stp_id_local,
dist.stp_id_remote AS dist_stp_id_remote
(
SELECT
lsetup.wrk_id,
lsetup.equipment_type AS bear_equipment_type,
lobservation.id AS obs_id,
lobservation.obt_sub_type,
lobservation.cos_id,
lobservation.vct_id,
lobservation.ref_datetime,
lobservation.surveyed_class,
lobservation.geodetic_class,
lobservation.value_1 AS bearing,
lobservation.value_2 AS bear1,
lobservation.value_3 AS bear2,
lobservation.value_1 as dd,
lobservation.stp_id_local,
lobservation.stp_id_remote,
ladoption.factor_1 AS bear_corr,
ladoption.sur_wrk_id_orig AS adopt_bear_wrk_id,
lcsystem.name AS bearing_datum,
concat(TRIM( TRAILING ' ' FROM CAST (lsurvey.dataset_series AS TEXT)) , ' ' , lsurvey.dataset_id) AS bear_adopt_plan
FROM crs_observation lobservation
INNER JOIN crs_setup lsetup
ON (lobservation.stp_id_local = lsetup.id AND lobservation.obt_sub_type = 'BEAR' AND lobservation.surveyed_class <> 'PSED' )
INNER JOIN crs_work lwork
ON (lsetup.wrk_id = lwork.id AND lwork.status <> 'REQU')
LEFT JOIN crs_adoption ladoption
ON (lobservation.id = ladoption.obn_id_new)
LEFT JOIN crs_coordinate_sys lcsystem
ON (lobservation.cos_id = lcsystem.id)
 LEFT JOIN crs_survey lsurvey ON
ladoption.sur_wrk_id_orig = lsurvey.wrk_id
WHERE lwork.id = 1672933 AND lobservation.obt_sub_type = 'BEAR' AND lobservation.surveyed_class <> 'PSED' bear
FULL OUTER JOIN
  (
    SELECT
lwork.id AS wrk_id,
lobservation.id AS obs_id,
lobservation.obt_sub_type,
lobservation.cos_id,
lobservation.vct_id,
lobservation.ref_datetime,
lobservation.surveyed_class,
lobservation.geodetic_class,
lobservation.value_1 AS distance,
lobservation.value_2 AS dist2,
lobservation.value_3 AS dist3,
ladoption.factor_1 AS dist_corr,
ladoption.sur_wrk_id_orig AS adopt_dist_wrk_id,
lobservation.stp_id_local,
lobservation.stp_id_remote,
lsetup.equipment_type AS dist_equipment_type,
concat(TRIM( TRAILING ' ' FROM CAST (lsurvey.dataset_series AS TEXT)) , ' ' , lsurvey.dataset_id) AS dist_adopt_plan
FROM crs_observation lobservation
INNER JOIN crs_setup lsetup
ON (lobservation.stp_id_local = lsetup.id)
INNER JOIN crs_work lwork
ON (lsetup.wrk_id = lwork.id AND lwork.status <> 'REQU')
LEFT JOIN crs_adoption ladoption
ON (lobservation.id = ladoption.obn_id_new)
LEFT JOIN crs_survey lsurvey ON
ladoption.sur_wrk_id_orig = lsurvey.wrk_id
WHERE  lwork.id = 1672933 AND lobservation.obt_sub_type = 'SLDI'  AND lobservation.surveyed_class <> 'PSED') DIST
ON (bear.vct_id =  dist.vct_id AND bear.wrk_id = dist.wrk_id AND bear.cos_id = dist.cos_id)
)


Copy Specific Columns from DB 1 To DB 2 on MongoDB

You can do projection with query. So, db.collection.find({},{c:1,e:1}) and then insert that to second DB.

Inside the one DB you can do it with use of aggregation with project and out stages.

Have you consider to use replica set and secondary read?


Impact of moving the ldf and mdf files temporarily for few hours

You should don't have any issues I have done this many times before with no big issues.

One thing to consider is that if you have binaries in  E drive, you may have some issues, otherwise should be ok to go.

From what I remember in the past you should check OS privileges, once  I had issues with the SQL Server account access to the drives.

Another tip: If you plan to go with the move of the datafiles, you may want to try to shrink the tlog files before put offline, this way you will save some MB/GB and copy time will be faster.


MySQL: How does AES_ENCRYPT turn the password into a key?

This really isn't a mysql question, more of a "how does encryption work" question.  If you have some time to kill you might want to watch this series

http://www.youtube.com/watch?v=dlS5_qfxAss&feature=results_main&playnext=1&list=PLDCC9179604F435AB

Here's what I felt was a good explaination of password strength I came across a while back

http://www.reddit.com/r/programming/comments/9pu3k/how_long_would_it_take_to_bruteforce_crack_an_aes/c0dv7sx

Remember though, the theoretical security of your password is moot if it's stored in source code that is not secure (world readable, ending up in a .mysql_history when working from the commandline, etc).


What are the pros/cons of between these 2 database design options where the join table can have 2 or 3 foreign keys?

It's hard to answer that question without knowing what you are trying to model and the queries that are relevant to your problem domain. I recommend that you trace out some queries and see how that design works.   Absent any other information, I would tend towards the 3 column option because the interpretation of a label depends on the property and you probably will do the query "give me all the labels associated with a user."
I think the most important question you should ask yourself are all the labels unrelated. If you are storing user preferences for an application, I can see the utility of your data model. On the other hand, if you want to store postal addresses, email address, access permissions, etc, then a different design is appropriate. Generic designs like this often shift data management and manipulation out of the DBMS into your application. Tables are cheap and I would recommend having tables of related facts/attributes (e.g. POSTAL_ADDRESS).
Revised answer based on edits:
First, never store an age in a database. Age depends on the current date.  You should store a date and calculate the age (you can use a virtual column to do that).
Second, I suggest you identify the types of things you want to store in the database. Bicycles and car are types of vehicles. I would recommend a VEHICLE table that contains the facts (attributes, property). You can then store properties relevant to bicycles in a BICYCLE_ATTRIBUTE (or whatever name you prefer) table. Do this for all the types of data you want to store and try to make groups that related. Postal address might be a group and education might be another.
You should try to avoid a completely generic database because you will end up creating a DBMS in your application and the underlying DBMS has been relegated to a fancy file system.


How to drop materialized view logs if underlying log table ($MLOG_...) is dropped?

Ok since there are no responses here.

I don't know if the problem was to do with the underlying tables or something else, we bounced the instance anyway. I also recreated the views using a SQL script (just create one for another table and use it as a template) and it seemed to work ok.


Note that in PostgreSQL a user and a role are the same thing. The difference is that a user can log in and a role cannot. So, to answer your question you need to decide if you want crm_usr to be able to login or not.

The approach that I try to take is to have two (or more) roles-- one for owning (i.e. xyz_owner) the objects and one or more (xyz_user) for performing the DML (selects, inserts, updates, etc.) on those objects. xyz_user gets granted to the actual users of the database and xyz_owner doesn't get granted to anyone.


Why should I install both 32 and 64 bits Oracle provider for the same machine?

As alluded to in the question you linked to, some SQL Server tools/applications are 32-bit only. The main SQL Server application (sqlservr.exe) is 64-bit (32-bit may still be available as well, not certain about that for the latest versions); however, some of the supporting tools, perhaps most notable SQL Server Management Studio (ssms.exe), are only available as 32-bit applications.

If you want to install both versions, this should be sufficient reason.

If you want to find out if you can avoid installing the 64-bit version, all I can suggest is uninstalling that version from your development environment, and confirming whether or not everything still works as expected (including SSMS, and any other applications you use). If everything works fine, you can probably skip installing the 64-bit version in production. If you have problems, assume you need both versions.


upgrade mysql 5.1 to 5.5 on Ubuntu 11.10

I'm not sure about your package management issues but wanted to point out that the docs Say that you should completely reload your data using mysqldump when upgrading from a previous minor version.  So once you have your complete mysqldump w/ --all-databases --events --routines, be sure to import that into the new binaries.


Query to check if job already belongs to jobserver in MSSQL

Found my solution.

IF NOT EXISTS(SELECT * FROM msdb.dbo.sysjobservers WHERE (job_id = @myJobId AND server_id = 0))


Postgres table size doubles are transaction completes

I see nothing unexpected there except that the VACUUM FULL ANALYZE took that long if you version is 9.0 or higher.

If you want to avoid the extreme bloat when updating every row in a table, update in smaller chunks and run VACUUM (not FULL) between the updates.
If you want to understand why this behaves this way, read up on the MVCC implementation in PostgreSQL.
If you want to eliminate bloat faster in pre-9.0 databases, use CLUSTER.


PostgreSQL is open source and you can use on almost any Linux/UNIX-like machine, plus Windows and Mac OS X. See the buildfarm for a list of known-working configurations. Precompiled binary installers and/or packages are available for popular platforms. I had some problems compiling it on AIX, but it was because I used gcc instead of IBM's XLC.

The minimum RAM you will need is 32MB, and you'll need least 50KB of disk space. As with any other database everything depends of the queries you are going to do, how many users you will have, what your performance requirements are, etc.

If you can, try to divide your DB between different physical hard drives (eg WAL on one, main tables + indexes on another), tune postgresql.conf to fit your machine, etc.

I recommend that you to read Greg Smith's book PostgreSQL High Performance.


Error 1239: Incorrect foreign key definition

Syntax error:

alter table invoice add constraint CUS_CODE
    foreign key (CUS_CODE) references customer.CUS_CODE;

Correct syntax:

alter table INVOICE add constraint CUS_CODE
    foreign key (CUS_CODE) references CUSTOMER(CUS_CODE);

customer.CUS_CODE is treated as dbname.tablename.  What is needed is tablename(columnname) or dbname.tablename(columnname)


Accumulate values into an array with Postgres

If your PostggreSQL version is 9.4 or later use FILTER clause:

select
    array_agg(my_table.price) filter(where my_table.property_type = 'FLAT' ) as Flat_Current_Asking_Price,
    array_agg(my_table.price) filter(where my_table.property_type = 'SEMIDETACHED') as Semidetached_Current_Asking_Price
from
    my_table;


Make sure SQL Server Service is running
If a named instance, make sure SQL Server browser service is running
Make sure SQL Server is configured to allow remote connections
Examine the SQL Server error log for messages confirming that SQL is listening on the  expected network interfaces and ports
Test server connectivity with PING from the client machine
Test port connectivity using TELNET or PowerShell to the server and port (from step 4) from the client machine.  For example

a. TELNET  1433

b. PowerShell:

1433 | % { echo ((new-object Net.Sockets.TcpClient).Connect("YourServerName",$_)) "server listening on TCP port $_" }

Check firewall settings if step 5 or 6 connectivity test fails

For further read below forum discussing the same which would be helpful to you for troubleshooting :

https://stackoverflow.com/questions/18060667/cannot-connect-to-server-a-network-related-or-instance-specific-error


Use this one command:

RENAME TABLE foo TO foo_old, foo_new To foo;

It is an atomic operation: both tables are locked together (and for a very short time), so any access occurs either before or after the RENAME.


Find transactions that are filling up the version store

It doesn't really make sense to track version store by session, or by transaction, or by query. If two different users are making use of the same version of a row/table, who owns it?

You can track this by object, though, which can help you narrow down which modules are causing the churn. Have a look at sys.dm_tran_top_version_generators:

USE [your database];
GO
SELECT obj =
  QUOTENAME(OBJECT_SCHEMA_NAME(p.object_id))
  + '.' + QUOTENAME(OBJECT_NAME(p.object_id)),
  vs.aggregated_record_length_in_bytes
FROM sys.dm_tran_top_version_generators AS vs
INNER JOIN sys.partitions AS p
ON vs.rowset_id = p.hobt_id
WHERE vs.database_id = DB_ID()
AND p.index_id IN (0,1);

And on SQL Server 2008+, you can also figure out which modules reference these tables by adding sys.dm_sql_referencing_entities:

SELECT
  obj = QUOTENAME(OBJECT_SCHEMA_NAME(p.object_id))
  + '.' + QUOTENAME(OBJECT_NAME(p.object_id)),
  referenced_by = QUOTENAME(r.referencing_schema_name)
  + '.' + QUOTENAME(r.referencing_entity_name),
  vs.aggregated_record_length_in_bytes AS size
FROM sys.dm_tran_top_version_generators AS vs
INNER JOIN sys.partitions AS p
ON vs.rowset_id = p.hobt_id
CROSS APPLY sys.dm_sql_referencing_entities
(
  QUOTENAME(OBJECT_SCHEMA_NAME(p.object_id))
  + '.' + QUOTENAME(OBJECT_NAME(p.object_id)), 'OBJECT'
) AS r
WHERE vs.database_id = DB_ID()
AND p.index_id IN (0,1)
ORDER BY size DESC, referenced_by;

This assumes that none of the version store could be created by ad hoc queries. However, it doesn't tell you which of those modules could be causing it - hopefully the naming scheme is logical and helps you narrow it down a bit.

(On 2005 you might be able to go through sysdepends and other old-style dependency views but I'm not 100% sure how reliable that would be.)


SQL Server changes execution plan - Part 2

The most likely explanation is that your sessions have different settings. SQL Server has various session settings that can affect the execution plan selected (and the results!)
The values for these settings can depend on how you connect to SQL Server, since different tools set the options different ways when they connect, and some (like SQL Server Management Studio) allow you to override the defaults as well.
For example:

The image above is reproduced from Erland Sommarskog's definitive article on this topic:
Slow in the Application, Fast in SSMS? Understanding Performance Mysteries
The whole thing is well worth reading, but you should definitely read the section titled, "The Default Settings"
If you make sure all the settings have the same value on all connections, you should get the same execution plans.
For maximum compatibility with features like indexed views, you should ensure your settings are as follows:

Many of these settings are maintained for backward compatibility only. It is strongly recommended you set them as shown in the table above, or use a tool that sets them the right way automatically.
Books Online references:

SET Statements (Transact-SQL)
SET ANSI_NULLS (Transact-SQL)
SET ANSI_PADDING (Transact-SQL)
SET ANSI_WARNINGS (Transact-SQL)
SET ARITHABORT (Transact-SQL)
SET CONCAT_NULL_YIELDS_NULL (Transact-SQL)
SET NUMERIC_ROUNDABORT (Transact-SQL)
SET QUOTED_IDENTIFIER (Transact-SQL)
Create Indexed Views

Update after plans were provided
The slow plan includes:
CardinalityEstimationModelVersion="70"

...whereas the fast plan says:
CardinalityEstimationModelVersion="120"

So the explanation is that one of you is using the original cardinality estimator, and other is using the new SQL Server 2014 CE. The difference in estimated row counts is enough for the new CE to choose a parallel execution plan. Under the original CE, the estimated cost for the serial plan is below the cost threshold for parallelism.
As to why different estimators are being used, I would guess that you have different context databases when the statements are run. One where the compatibility level of the database defaults to the new CE, and one where the original CE is used. The database you are "in" when the query executes determines the CE model, not the database(s) used in the query.
For example, you may have different default databases associated with your logins. If you USE Klasje; before running the statements, both connections should use the same CE model.
Final update: it turned out the target database was indeed set to an older compatibility level. Running the query with master as the context database produced the better plan. Be aware that changing to use the new CE for all queries may cause regressions. You will need to test your workload before changing the database compatibility level in production.


Why can't we specify group functions in Oracle in the where clause?

AVG and other aggregate functions work on sets of data.  The WHERE cause does not have access to the entire set, only to data for the row it is operating on.  If you created your own AVG function (as a normal function and not a custom aggregate function) it would only be passed one ID value when called from the WHERE clause not the entire set of ID values.

Mezmo's solution will give you your expected results, but if you want to avoid two full table scans (assuming no indexes) you can use a windowing function like this:

SELECT * FROM (SELECT AVG(id) OVER () avgid, t.* FROM tab1 t) WHERE id > avgid;


Create a default value for session valid_until MySQL

From the manual:

  With one exception, the default value must be a constant; it cannot be
  a function or an expression. This means, for example, that you cannot
  set the default for a date column to be the value of a function such
  as NOW() or CURRENT_DATE. The exception is that you can specify
  CURRENT_TIMESTAMP as the default for TIMESTAMP and DATETIME columns.

The way to workaround this is to use a trigger.

CREATE TRIGGER before_insert_on_sessions BEFORE INSERT ON `sessions`
FOR EACH ROW SET new.valid_until = IFNULL(new.valid_until,DATE_ADD(NOW(), INTERVAL 1 DAY));


Change:

MAX(created)

with a CASE expression:

MAX(CASE WHEN created <= '2015-12-31' THEN created ELSE NULL END)

The ELSE NULL is not needed as it is the default. Now, if you want to return that '2015-12-31 in case no date is before the threshold date, we can use ISNULL() or COALESCE:

COALESCE( MAX(CASE WHEN created <= '2015-12-31' THEN created END), '2015-12-31' )

or, if in that case, you want the next available date, even if it bigger than the threshold date:

COALESCE( MAX(CASE WHEN created <= '2015-12-31' THEN created END), MIN(created) )

Note: the answer assumes that created is a DATE (or a DATETIME that doesn't have a time part). If it does have time parts, I suggest you replace the use of <= with < (i.e. replacing the <= '2015-12-31' with < '2016-01-01'). See a detailed explanation of the problem with inclusive bounds at Aaron Bertrand's blog post: What do BETWEEN and the devil have in common?


Too much memory is being used during backup

MongoDB will eventually use all free memory, always! Of course in the situation where data and it indexes are smaller than free memory, mongod cannot fill whole memory.

Now, when you do backups, mongod reads all needed data (for those backups) to the memory and let it (the data) be there as long as it has free space. When there is no free memory anymore and mongod need to read something that is not already in the memory, oldest data is thrown away.


"Invalid option" while creating a tablespace

The autoextend option belongs to the datafile specification (one per datafile), not to the tablespace itself, and the uniform option is part of the extent management clause, so you've got a mixup there.

Try:

CREATE TABLESPACE ACME_DATA
DATAFILE
  '/oracle/d02/acme/ACME_DATA_01.dbf' SIZE 40M AUTOEXTEND ON NEXT 2M MAXSIZE 100M
EXTENT MANAGEMENT
  LOCAL UNIFORM SIZE 128K
SEGMENT SPACE MANAGEMENT
  AUTO;

(Note: size units are K, M, G, not KB or similar.)


Keywords and reserved words: What's a poor boy to do?

The best answer is what @akuzminksy mentioned: escape the fields.
Since you indicate doing so would be prohibitive in terms of amount of work required, there might be another solution: a pre-parse query rewrite plugin

A preparse query rewrite plugin has these characteristics:

The plugin enables rewriting of SQL statements arriving at the server before the server processes them.

The plugin receives a statement string and may return a different string.

Of course, that requires upgrading to 5.7! And the effort required to build this custom plugin might also be prohibitive.
Other tools that offer query rewriting capabilities are:

ProxySQL
MaxScale
ScaleArc

Since these tools are proxies, all rewriting is done 'preparse'.
Disclaimer: I make no claims that this will actually work, but is the only path forward I see besides code overhaul.


MySQL replication master-slave with 2 or more users in master

Not ON ., rather ON dbname.* (good idea for applications) or ON *.* (might be appropriate for admin)


Is it possible to list all user that accessed to specific table

You can do this with auditing, but it incurs a performance overhead and does not work retrospectively.

Read the section Verifying Security Access with Auditing in the Oracle® Database Security Guide 11g Release 2 (11.2).

I'll add that in the majority of cases, somebody in the organisation thinks that this type of auditing is required, but more often than not it is just a misguided requirement.


Getting total number of payments and percentage

SELECT pdate,
       COUNT(status) total,
       SUM(status='successful') successful,
       SUM(status='failed') failed,
       SUM(status='refunded') refunded,
       SUM(status='successful')/COUNT(status)*100 successful_percent,
       SUM(status='failed')/COUNT(status)*100 failed_percent,
       SUM(status='refunded')/COUNT(status)*100 refunded_percent
FROM payments
GROUP BY pdate


The "typical" method for accomplishing this type of design is something like this:

You could use pivot functionality to show the data in a table format, similar to your example.  In SQL Server this might look something like:

SELECT *
FROM (
    SELECT names.MilestoneName
        , FY = CONVERT(varchar(4), YEAR(dates.MilestoneStartDate))
                + '-' + CONVERT(varchar(4), YEAR(dates.MilestoneEndDate))
        , [Money Spent(Budgeted)] = CONVERT(varchar(20), details.MoneySpent)
                + ' (' + CONVERT(varchar(20), details.MoneyBudgeted) + ')'
    FROM dbo.MilestoneDetails details
        INNER JOIN dbo.MilestoneDates dates
            ON details.MilestoneDateID = dates.MilestoneDateID
        INNER JOIN dbo.MilestoneNames names
            ON details.MilestoneID = names.MilestoneID
) src
PIVOT (
    MAX(src.[Money Spent(Budgeted)])
    FOR
        src.FY IN (
            [2015-2016]
            , [2016-2017]
            , [2017-2018]
        )
    ) pvt

╔═══════════════╦══════════════════════╦══════════════════════╗
║ MilestoneName ║ 2015-2016            ║ 2016-2017            ║
╠═══════════════╬══════════════════════╬══════════════════════╣
║ Construction  ║ 400.0000 (900.0000)  ║ 700.0000 (1200.0000) ║
║ Security      ║ 500.0000 (1000.0000) ║ 800.0000 (1300.0000) ║
║ Travel        ║ 600.0000 (1100.0000) ║ 900.0000 (1400.0000) ║
╚═══════════════╩══════════════════════╩══════════════════════╝

This is the code to create the tables, etc, if anyone is interested:

IF OBJECT_ID(N'dbo.MilestoneDetails', N'U') IS NOT NULL
DROP TABLE dbo.MilestoneDetails;
IF OBJECT_ID(N'dbo.MilestoneDates', N'U') IS NOT NULL
DROP TABLE dbo.MilestoneDates;
IF OBJECT_ID(N'dbo.MilestoneNames', N'U') IS NOT NULL
DROP TABLE dbo.MilestoneNames;

CREATE TABLE dbo.MilestoneDates
(
    MilestoneDateID int NOT NULL IDENTITY(1,1)
        CONSTRAINT PK_MilestoneDates
        PRIMARY KEY CLUSTERED
    , MilestoneStartDate datetime NOT NULL
    , MilestoneEndDate datetime NOT NULL
);

CREATE TABLE dbo.MilestoneNames
(
    MilestoneID int NOT NULL IDENTITY(1,1)
        CONSTRAINT PK_MilestoneNames
        PRIMARY KEY CLUSTERED
    , MilestoneName varchar(48) NOT NULL
);

CREATE TABLE dbo.MilestoneDetails(
    MilestoneID int NOT NULL
    , MilestoneDateID int NOT NULL
    , MoneySpent decimal(18, 4) NOT NULL
    , MoneyBudgeted decimal(18, 4) NOT NULL
    , CONSTRAINT PK_MilestoneDetails
        PRIMARY KEY CLUSTERED
        (MilestoneID ASC, MilestoneDateID ASC)
);

ALTER TABLE dbo.MilestoneDetails  WITH CHECK
ADD  CONSTRAINT FK_MilestoneDetails_MilestoneDates FOREIGN KEY(MilestoneDateID)
REFERENCES dbo.MilestoneDates (MilestoneDateID)

ALTER TABLE dbo.MilestoneDetails CHECK CONSTRAINT FK_MilestoneDetails_MilestoneDates

ALTER TABLE dbo.MilestoneDetails  WITH CHECK
ADD  CONSTRAINT FK_MilestoneDetails_MilestoneNames FOREIGN KEY(MilestoneID)
REFERENCES dbo.MilestoneNames (MilestoneID)

ALTER TABLE dbo.MilestoneDetails CHECK CONSTRAINT FK_MilestoneDetails_MilestoneNames

INSERT INTO dbo.MilestoneNames (MilestoneName)
VALUES ('Construction')
    , ('Security')
    , ('Travel');

INSERT INTO dbo.MilestoneDates (MilestoneStartDate, MilestoneEndDate)
VALUES (N'2015-01-01T00:00:00', N'2016-01-01T00:00:00')
    , (N'2016-01-01T00:00:00', N'2017-01-01T00:00:00')

INSERT INTO dbo.MilestoneDetails (MilestoneID, MilestoneDateID, MoneyBudgeted, MoneySpent)
VALUES (1, 1, 900, 400)
    , (2, 1, 1000, 500)
    , (3, 1, 1100, 600)
    , (1, 2, 1200, 700)
    , (2, 2, 1300, 800)
    , (3, 2, 1400, 900);


Can this query be optimised with indexes, or should I change my schema?

I think this request is fully optimized on a SQL point of view. Meanwhile, you may can increase performance by choosing a particular type of index depending on your data (for example BINARY for IDs), check this documentation for more details.

Moreover you have to keep in mind that the innoDB engine in MySQL (if you're using it) may be slow on a big amout of data.


Create a PostgreSQL constraint to prevent unique combination rows

You can use an exclusion constraint with btree_gist,

-- This is needed
CREATE EXTENSION btree_gist;

Then we add a constraint that says:

"We can't have 2 rows that have the same name and different is_active":

ALTER TABLE table_name
  ADD CONSTRAINT only_one_is_active_value_per_name
    EXCLUDE  USING gist
    ( name WITH =,
      is_active WITH <>      -- if boolean, use instead:
                             -- (is_active::int) WITH <>
    );

Some notes:

is_active can be integer or boolean, makes no difference for the exclusion constraint. (actually it does, if the column is boolean you need to use (is_active::int) WITH <>.)
Rows where name or is_active is null will be ignored by the constraint and thus allowed.
The constraint makes sense only if the table has more columns. Otherwise, if the table has only these 2 columns, a UNIQUE constraint on (name) alone would be easier and more appropriate. I don't see any reason for storing multiple identical rows.
The design violates 2NF. While the exclusion constraint will save us from update anomalies, it may not from performance issues. If you have for example 1000 rows with name = 'A' and you want to to update is_active status from 0 to 3, all 1000 will have to be updated. You should examine whether normalizing the design would be more efficient. (Normalizing meaning in this case to remove is_active status from the table and add a 2-column table with name, is_active and a unique constraint on (name). If is_active is boolean, it could be totally stripped and the extra table just a single column table, storing only the "active" names.)


Reasons against having "concrete" values in own table with only an ID column and the value itself, and composing bigger objects of IDs

OK, I won't rant about what is 'wrong' in your schema.  I will provide an efficient way to batch the normalization for a batch insert.

I will point out two common flaws when "over-normalizing":

Don't do it if the id is bigger, on average, than the data.  Note: INT is 4 bytes; BIGINT is 8.
Don't normalize "continuous" values -- anything numeric or date-related.  It makes it very costly to do a range test.

OTOH, 100K rows/year is rather small.  If they come in evenly it is a rate of about 5 inserts (plus 30 normalizations) per hour.

If they come in batches, see http://mysql.rjweb.org/doc.php/staging_table#normalization

One thing of note:  Things like INSERT IGNORE will "burn" ids.  That is, an AUTO_INCREMENT id will be bumped even if it is not needed.  The code in that link avoids such.


Grant execute on xp_logevent to sa using sa user to do it

How can I grant execute permissions to sa user?

You don't need to grant execute anything to sa as sa is in the sysadmin server role and can pretty much do anything. Hence, if you are getting an error executing xp_logevent, then you aren't logging in as sa. Is the connection string using context connection = true; or a regular / external connection? If using context connection = true; then the permissions are based on whoever is executing the SQLCLR stored procedure. If using a regular connection, the permissions are based on the logon account for the SQL Server NT Service (MSSQLSERVER / SQLEXPRESS if it is a default instance) by default, else it can be the permissions of whoever is executing the SQLCLR stored procedure if code has been added to do impersonation.

So, how is the SqlConnection being made? The answer to that question will help determine the appropriate fix. If this SQLCLR stored procedure is to be called by anyone (meaning, Users who are not necessarily in the db_owner database role or the sysadmin server role), then you can do the following to grant this permission to the smallest scope possible.

In the database that contains the SQLCLR stored procedure:

Create an Asymmetric Key (see CREATE ASYMMETRIC KEY)
Create a User from that Asymmetric Key (see CREATE USER)
Add this new User to the db_owner Database Role
Create a T-SQL Stored Procedure (maybe call it dbo.LogEvent) that accepts the two parameters you are passing in (@errorNumber and @message) and simply executes EXEC xp_logevent @errorNumber, @message, informational;
Grant EXECUTE on this new Stored Procedure to any Users and/or Database Roles that will be calling the SQLCLR Stored Procedure
Sign this new Stored Procedure with the same Asymmetric Key used to create the new User (see ADD SIGNATURE)
Update your SQLCLR code to execute this new T-SQL Stored Procedure (i.e. dbo.LogEvent) instead of xp_logevent

You can then change your using statement to have new SqlCommand("dbo.LogEvent", connection)
then change cmd.CommandType = CommandType.Text; to be cmd.CommandType = CommandType.StoredProcedure;
remove the const string executeLog line

Other notes:

Please do not use AddWithValue as there are issues with it. Instead, just create the SqlParameter with an explicitly set datatype and then add it to the SqlCommand.SqlParameters collection.
No need to call ExecuteScalar if you are not capturing something from a result set. Instead, call ExecuteNonQuery


Passing each value from a temp table to a scalar function and sum values

It is fairly straightforward with scalar functions, although your description is very confusing and hard to follow with regard to function names and parameter names. Anyway, when you have a scalar function and a table of values that you want to pass as arguments to the function one row at a time, you can go like this:

SELECT
  dbo.function_scalar(Amount, CurrencyId, 'EU')
FROM
  @GetTotalPaymentAmount
;

That will give you a column of values each of which is the result for a corresponding row of the table. At this point you can specify Amount and CurrencyId to return them along with the results to verify the results match the input values.

Now, as I have understood from your description, the results will be amounts in the same currency. You can get the total amount by aggregating the column returned by the function, for which you simply need to apply SUM() to the function result:

SELECT
  SUM(dbo.function_scalar(Amount, CurrencyId, 'EU'))
FROM
  @GetTotalPaymentAmount
;

That is basically it. If you need to return the result as a single-row single-column dataset or otherwise use it as a derived table in another query, you will probably want to assign an alias to the aggregated column by changing the SUM line to this:

  TotalAmount = SUM(...)

or this:

  SUM(...) AS TotalAmount

You can also just store the result in a variable if necessary:

  @TotalAmount = SUM(...)


Coding an accounting database from scratch?

I have used almost all accounting software, currently use and modify Sql-Ledger - and so turning my database client's workflow system into a full, double-entry accounting system was so easy it was almost a relief; client had been using my system to bill their own (news-copy) clients because their internal itemisation of services spoke their own language whereas no accounting package would. Their accounts section then had to go and reproduce it all again externally for book keeping.

Finally the client's accountant and I sat down and exchanged notes. I modified the application within a week. It has never broken, produced a bug, or failed. It does all their accounts and no none else's. It tracks assets, cash flow, everything except payroll - which they didn't need.

Every business of any size knows, you just can't get anything off the shelf that works.

My advice is don't listen to the doom-and-gloom advice of anybody who doesn't understand both database schema and accounting. This combination seems to be just unusual enough to have software vendors rubbing their hands with glee, issuing warnings of dire consequences to would-be developers, spending most of their time organising vendor-lockin components to their software, or arrangements with large accounting franchises. And, like successful snake-oil vendors, they laugh all the way to the bank.


Assuming this is the store database for a Retail POS and not your main AX database you should be fine running it on Express Edition. Since the store database only contains a subset the data necessary for the store to function without a permanent connection to the main system, and transactions should only be kept for the time needed to allow the system to sync to the main database these store databases are usually fairly small.

However, your estimates are way off (now that you posted the actual plan) in this database, which could explain why your query runs better on another POS (assuming you mean that is in a different store/location with another database behind it) if that database happens to have better statistics.

Please see AX Retail: Retail store maintenance and SQL Express to see how you can schedule maintenance with SQLCMD, but I would suggest you use Ola Hallengren's scripts instead, the FAQ explains how to set it up on a SQL Server Express instance.

I also think you have customized the logisticselectronicaddress table, so you might want to review the indexing on that table, or post the table definition here so we can help you.


How do I add read/write permissions on a directory for an SQL Server account

The rights need to be provided to the SQL Server Service Account, which is typically an Active Directory account setup specifically for SQL Server to use.   Microsoft has a great Docs page that describes Service Accounts for SQL Server.

Check with SQL Server Configuration Manager to determine the name of that account.

Assign rights to the temp folder for that account.

In the image above, you can see SQL Server Express is running under the NT AUTHORITY\NetworkService account - any account that begins with NT AUTHORITY is an account local to the machine running SQL Server.


You can use hstore for use EAV. hstore is available from pgsql 8.4. If you have installed json you can then use function hstore_to_json(hstore)

select hstore_to_json('"a key"=>1, b=>t, c=>null, d=>12345, e=>012345, f=>1.234, g=>2.345e+4')

this will output:

{"a key": "1", "b": "t", "c": null, "d": "12345", "e": "012345", "f": "1.234", "g": "2.345e+4"}


How do I select by a JSONB array containing at least one element within a range?

You need to use a sub-query that unnests all array elements together with an EXISTS predicate:

select p.*
from products p
where exists (select *
              from jsonb_array_elements(p.properties->'weight') as t(weight)
              where t.weight::int <@ range(50, 150));


Any potential cons associated with enabling backup checksums when performing backups?

Yes, and you can verify that on the Backup Checksums doc:

Due to the overhead verifying and generating backup checksums, using
backup checksums poses a potential performance impact. Both the
workload and the backup throughput may be affected. Therefore, using
backup checksums is optional. When deciding to generate checksums
during a backup, carefully monitor the CPU overhead incurred as well
as the impact on any concurrent workload on the system.

But I advise you not to discard the checksum benefits just because of the cost it implies. Check the documentation to better understand the checksum and then decide whether to use it or not.
That same doc states that

after encountering a page checksum error, a BACKUP or RESTORE
operation fails and a RESTORE VERIFYONLY operation continues.

As you can see, when using checksum it's possible to find out that your backup is not reliable while taking it and not when you need to restore it (you don't wanna find yourself in this situation).

The reason checksums are not enabled by default in the backup stored
procedure DatabaseBackup is just to follow the defaults for the backup
command. I strongly recommend you to use checksums. @CheckSum = 'Y' is
also added in the jobs that comes with the installation script
MaintenanceSolution.sql. – Ola
Hallengren


Shrink my database log file, make sense?

If you're really strapped for disk space, you can of course shrink your database log file.

Switch your recovery model to SIMPLE and then shrink the database file using the DBCC SHRINKFILE('logfilename') command.

If you're running a production system, make sure that you switch your recovery model back to FULL immediately after shrinking the log file, and run a full database backup, in order to get a new point in time recovery point. You can then schedule your log file backups to run every few hours ( depending on your need ).


Report Builder Indicator showing incorrect symbol

Well, as it turns out, someone changed the type of the field in SharePoint without documenting it and instead of yes/no, it was returning true/false and causing an error to show in place of an indicator.


SQL Server - Very high disk activity after running query?

I wanted to post the solution that apparently worked for my case. I took down the app for maintenance last weekend and did a rebuild of all the indexes of the database (through the SMSS UI) followed by a statistics update and I'm happy to say we didn't experience the issue anymore since then.

I had rebuilt statistics before this and it didn't help and if the problems were related to index fragmentation then the queries should be slow all the time, but for some reason, it worked in my case.

Thank you, everybody, for your time and suggestions!


Query extremely slow when joining 2 views

You have a performance spool that feeds your nested loops operator (non-apply)

...with very low estimates.

Now, for each value in the top(outer) input, the table spool will produce a rebind. No rewinds are possible for performance spools on non apply nested loop joins.

  ...The lazy table spool is populated once during the first loop
  iteration. The spool rewinds its contents for each subsequent
  iteration of the join. With nested loops join, the inner side of the
  join is a static set of rows because the join predicate is on the join
  itself. The static inner-side row set can therefore be cached and
  reused multiple times via the spool. A nested loops join performance
  spool never rebinds....

Source: Paul White on Nested Loops Joins and Performance Spool

The low estimates result into the actual situation below:

You can validate the high rowcount of the spool output by multiplying both inputs (of the compute scalar & the stream aggregate).

515 728 (outer input) * 168 (inner input) = 86 642 304

Solutions

The first thing to fix would be fixing why the estimates are so low.

You could try updating the statistics of the table referenced in the view.

But it appears that you are doing a LIKE comparison between dbo.TblOnCall and the tables feeding the spool operator.

That can also be seen on the NL operator that gets the data from the spool.

(EXPR 1137 = the outer input, UNION = the inner input).

For a longterm solution,

I would fix this LIKE comparison and any other parts that will impact your estimations gravely.

We would have to see the views to give the most correct query recommendations.

For a temporary quick fix, you can disable the spool with a trace flag or a query hint.

  Performance spools can be disabled with lightly-documented trace flag
  8690, or the documented query hint NO_PERFORMANCE_SPOOL on SQL Server
  2016 or later.
  Source

An example of implementing this is running

DBCC TRACEON(8690,-1) (will be removed on instance restart)

Or better, running the query with OPTION(QUERYTRACEON 8690)

 SELECT
        OCCoverID as StaffID, FullName, SUM(OCWeight) AS Num
    FROM vuOCStaffAbsentExpandedMini
        INNER JOIN vuStaff ON OCCoverID = StaffID
    WHERE
        SADate = '2019-10-4'
        AND MIDent = 506
        AND SASupplyID IS NULL
        AND CoverStaffSupplyID IS NULL
        AND StaffMIDent <> 1
    GROUP BY
        OCCoverID, FullName
OPTION(QUERYTRACEON 8690);


MySQL: What happen when an executing sql's client is terminated?

I believe that killing the client won't be noticed by the server until the server sends data to the client.  That is, the server will finish the query.

COMMIT happens in different ways.

BEGIN; UPDATE...; <client dies> -- has not gotten to COMMIT, so no commit.
autocommit=OFF ... UPDATE ... -- same as above
autocommit=ON ... UPDATE...; <updated finished, so committed> (I think)


Created a Materialized View in ORACLE which won't refresh

We normally use jobs to automate MV refreshes, so you don't have to do them manually or on demand.

For example, if you want your MV to refresh every 4 hours, then you would submit a job like this.  Connect as the schema owner of the MV.

VARIABLE jobno NUMBER
BEGIN
   DBMS_JOB.SUBMIT
      (job  => :jobno,
      what => 'begin DBMS_MVIEW.REFRESH(''UPDATE_NBOE'',''C''); end;',
      next_date => SYSDATE,
      interval => 'trunc(SYSDATE+1) + 04/24');
   COMMIT;
END;
/
PRINT jobno

The C denotes a complete refresh and you can use F for a fast refresh or check the docs for other possible parameters.  I don't know why you chose atomic_refresh_test; this does not seem to be something you need.

Sometimes the jobs break and you would have to run the job again after refreshing the MV manually.  This you can check from DBA_JOBS.  This is code that checks for broken MV jobs and outputs the commands to fix them. Connect as the owner of the MV then run this code:

set serveroutput on size 1000000
declare
v_mview VARCHAR2(30);
v_run  VARCHAR2(100);
v_job number;
cursor user_mviews_c is
select mview_name from user_mviews ;
cursor user_jobs_c is
select job from user_jobs ;
begin
open user_mviews_c;
loop
   fetch user_mviews_c into v_mview;
   exit when user_mviews_c%notfound;
   v_run := 'exec DBMS_MVIEW.REFRESH ('''||v_mview||''',''C'');';
   dbms_output.put_line(v_run);
end loop;
close user_mviews_c;
open user_jobs_c;
loop
   fetch user_jobs_c into v_job;
   exit when user_jobs_c%notfound;
   v_run := 'exec DBMS_JOB.RUN ('||v_job||');';
   dbms_output.put_line(v_run);
end loop;
close user_jobs_c;
end;
/