Passing passwords more securely to mysql utilities on the command line

You can put the password in the my.cnf (or my.ini for Windows) and then login without specifying the password "over the wire". Obviously you want to keep that file secure, but in any decent setup, this should not be a problem.

LOGGING IN

[pol@localhost inst]$ ./bin/mysql --defaults-file=./my.cnf -S ./mysql.sock -u root  -- **NOTE: no password is needed here.**
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.6.22-log Source distribution

Copyright (c) 2000, 2014, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

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

mysql>

CONTENTS OF MY.INI OR MY.CNF

[client]
port        = 3306
socket      = /home/pol/Downloads/software/mysql/5.6/inst/mysqld.sock
host            = 127.0.0.1
user        = pol
password    = dba   --  **<<-----NOTE password in client section**

PASSWORD NOT VISIBLE

mysql> \! ps -ef |  grep mysql
pol       2173  2122  0 Apr19 pts/0    00:00:19 ./bin/mysqld --defaults-file=./my.cnf
pol       9294  2197  0 00:09 pts/1    00:00:00 ./bin/mysql --defaults-file=./my.cnf -S ./mysql.sock -u root   **NOTE - no password visible "over the wire"
pol       9360  9294  0 00:16 pts/1    00:00:00 sh -c  ps -ef |  grep mysql
pol       9362  9360  0 00:16 pts/1    00:00:00 grep mysql


Optimize INSERT performance with a UNIQUE constraint on md5(JSONB_COLUMN::text)

If the uniqueness constraint is the only issue (and I'm interested to learn more about why in the discussion above), here's an idea:

remove the uniqueness constraint
when you do reads (selects), do order by id asc limit 1 so that you ignore duplicates
have some sort of parallel process going through the table periodically and removing duplicates


You cannot just "open" a database backup. You have to restore the database. For this, you first need to install PostgreSQL. The latest version should be capable of taking backups from older versions, too. But older version may have problems with newer features in the backup. So your version should be at least as recent as the source.

How you install PostgreSQL depends on your OS that you cunningly kept a secret. In Debian it's basically a matter of:

apt-get install postgresql-8.4

(Or you take postgresql-9.1 from the backports.)

Depending on the format (there are various) you can then restore the backup. If the backup holds a complete database cluster (produced with pg_dumpall - ask your friend!), you can simply:

psql postgres -f /path/tp/file.sql

To view the data you would best use a GUI like pgAdmin. Or you learn how to use SQL. :)

For more advise you would need to provide more information. Like a short(!) sample of the backup and how it was produced.

For more about PostgreSQL start by reading the manual or one of the thousand pages on the web.

But really, your friend should just export the relevant data do a CSV file that you can open in Open Office Calc or some other spreadsheet application. Or even any text editor.


Why does this query work with a CTE, but not with a derived table?

You are missing an alias for the derived table.

Books online - Emphasis mine

  When a derived table, rowset or table-valued function, or operator clause (such as PIVOT or UNPIVOT) is used, the required table_alias at the end of the clause is the associated table name for all columns, including grouping columns, returned.


You've come across a fairly well-known problem. When SSIS is trying to import the data, it provides locale-aware conversions and the format YYYYMMDD doesn't convert nicely. You can use the "import as string and convert to actual datetime" approach you've outlined in your solution but that's slower and consumes more resources than using the native approach of telling SSIS to quit being so damn smart.

Right click on your flat file source and select the Show Advanced Editor. In the Input and Output Properties, expand Output Columns, find your column (BirthDate) and change the FastParse property from False to True.

With only that change, the package will execute successfully

Also, an excellent answer from SO on the same issue


POSTGRES declaring variable containing date array in functions

You can use ARRAY keyword:

create table tbl (dateval date[]);
insert into tbl values(array ['20180101','20180102'::date]);

Or in a block of code:

do $$
declare
    dateval date[];
begin
    dateval := array ['20180101','20180102'::date];
end;
$$;

Or cast the expression as an array of dates.

do $$
declare
    dateval date[];
begin
    dateval := '{2015-4-12, 2015-4-19}'::date[];
end;
$$;

db<>fiddle here


Performance of selecting strings: substring IN vs regular expression

The version with IN will be slightly faster, because = ANY is cheaper than ~.
The estimate suggests otherwise, but only because it estimates a different number of result rows.
You can make these queries faster with an index on the more selective condition.


How to find the count of words (all and unique) in a column row-wise?

To solve your issue I did the following:

CREATE TABLE wordcount (id SERIAL NOT NULL PRIMARY KEY, description TEXT NOT NULL);

Populate it:

INSERT INTO wordcount (description) VALUES ('What a great day');
INSERT INTO wordcount (description) VALUES ('This is a product. It is useful');

Then performed the following query (fiddle available here):

WITH arranged AS
(
  SELECT id, UNNEST(STRING_TO_ARRAY(
                   REGEXP_REPLACE(description,  '[^\w\s]', '', 'g'), ' '))
                   AS word, description
  FROM wordcount
)
SELECT a.id, COUNT(a.word), COUNT(DISTINCT(a.word)), a.description
FROM arranged a
GROUP BY a.id, a.description;

Result:

id  Word_Count  Unique_Word_Count   Description
 1           4                  4   What a great day
 2           7                  6   This is a product. It is useful

If you're new to all this, I would advise you to go through the fiddle here which shows the development of my thought processes which led to the query above.

I would advise you to put this logic into an onInsert/onUpdate trigger - it's probably fairly computationally intensive. This means the computation is only performed once. Version 12 will allow generated columns (one of the few areas where MySQL is superior to PostgreSQL) which could be a good solution.


How to handle database of multiples languages and countries

Normally I use two tables, main table has default language, just in case you forget to add corresponding values into translations tables.

create table books (
    book_id int,
    book_name varchar(200),
    description varchar(500)
)

create table books_language (
    book_id int,
    language_id vachar(10),
    book_name varchar(200),
    description varchar(500)
)

This returns all records including default language.

select book_id,
       isnull(books_language.laguage_id, 'default')
       isnull(books_language.name, books.name) as name,
       isnull(books_language.description, books.description) as description
from books
     left join books_language
     on books.book_id = books_language.book_id


How to deny access to any system user who attempts to log in without password usage?

By default ''@localhost, and other bunch of accounts exist in a freshly installed MySQL instance.

If you execute SELECT user() you may see something like this:

mysql> SELECT user();
+--------------------+
| user()             |
+--------------------+
| user-one@localhost |
+--------------------+
1 row in set (0.00 sec)

That user doesn't really exist, and it is actually mapped for authentication and authorization to:

mysql> SELECT current_user();
+----------------+
| current_user() |
+----------------+
| ''@localhost   |
+----------------+
1 row in set (0.00 sec)

In order to fix this, run the script mysql_secure_installation always after setting up a production database, and it will ask you to set a mysql root password, delete remote access and delete the passwordless default accounts. This should be part of any proper mysql installation process, and it is documented here.

Starting with 5.6 and 5.7, better security defaults are used, but only on official packages.

Remember that by default, all users have write permissions to write on test% databases. That is a very dangerous default, and should be fixed before production.


How to see where the default database is configured for a particular username or login name?

Running the following code shows the default database on ASE:

SELECT sl.name
    , sl.dbname
FROM syslogins sl
ORDER BY sl.name;


Database modelling : Creating a model for restaurant application which gives statistics

Save all of the raw ratings (i.e. individual user inputs) with the date on which the rating was recorded.

Then you can calculate all of your weekly, monthly and yearly statistics based on the raw data each night.  These rolled up statistics should also be dated (with a date range).  In this way you can also keep multiple weeks, months and years so that you can look at trends if you like.

This is often called a data warehouse.  It is a common way of pre-calculating rolled-up statistics for quick and easy reporting.

When you get past the longest of your roll-ups (e.g. 1 year) you can start to throw away your raw transactional data if you're worried about how much space it takes up.


Are Snapshot files created during MS SQL replication one time use only?

The lifecycle of snapshot files is dependent on a number of factors. Firstly, if your publications have immediate_sync enabled it means that new subscribers will use the existing snapshot to initialise the new subscription (provided the snapshot is within the retention period).

This has additional impacts as commands in the distribution DB are also retained for the retention period which can cause excess growth of that database. The snapshot remains valid until the retention period expires at which point it is removed by the distribution cleanup job.

Secondly, if you have immediate_sync disabled and you reinitialise a subscription, a new snapshot must be generated for the reinitialisation. You can reinitialise multiple subscriptions at a time, but only if they're all marked for reinitialisation prior to running the snapshot agent. Once those subscriptions marked for reinitialisation have been reinitialised, the snapshot is invalid for further use. At this point, it should be cleaned up by the distribution cleanup job.

Thirdly, if you perform a mini-snapshot due to something like adding a single article to an existing publication (as per your described scenario), that mini-snapshot is only valid for the subscribers that existed and were synchronising/synchronised at the time the snapshot was generated. Any new or expired subscriptions would require a full snapshot to reinitialise.

At all stages, the distribution cleanup job should be cleaning up expired or invalid snapshots, however, you can remove them manually if desired.


VM'd a SQL server, having trouble with external HDDs

In general, you can put TempDB anywhere you want. It could be an existing drive on the VM (not great, but OK as a stopgap measure), or a new virtual drive, or a non-local drive (again, not a great idea).
There are a lot of references on the Internet for how to relocate TempDB. And for how to start SQL if the current TempDB is not accessible. Here are some good ones:

How to Move TempDB to Another Drive & Folder
Start SQL Server without tempdb

Also, run (don't walk) to your boss and let them know that both SQL 2000 and Windows 2000 are out of support, and have been for a long time. You are open to security vulnerabilities, performance risks, and reliability concerns. Do whatever you need to to make your boss realize that you can't provide good business value on unsupported software.


On Windows, the listener is a WSFC cluster resource with a dependency on the availability group. It is hosted by the current primary AG node and will move to other nodes of the cluster when the primary role switches, providing high availability.


Why does searching for LIKE N'%�%' match any Unicode character and = N'�' match many?

How one "character" (which can be comprised of multiple Code Points: surrogate pairs, combining characters, etc) compares to another is based on a rather complex set of rules. It is so complex due to needing to account for all the various (and sometimes "wacky") rules found in all of the languages represented in the Unicode specification. This system applies to non-binary Collations for all NVARCHAR data, and for VARCHAR data that is using a Windows Collation and not a SQL Server Collation (one starting with SQL_). This system does not apply to VARCHAR data using a SQL Server Collation as those use simple mappings.

Most of the rules are defined in the Unicode Collation Algorithm (UCA). Some of those rules, and some not covered in the UCA, are:

The default ordering / weight given in the allkeys.txt file (noted below)
Which sensitivities and options are being used (e.g. is it case sensitive or insensitive?, and if sensitive, then is it upper-case first or lower-case first?)
Any locale-based overrides.
The version of the Unicode standard is being used.
The "human" factor (i.e. Unicode is a specification, not software, and is thus left up to each vendor to implement it)

I emphasized that final point regarding the human factor to hopefully make it clear that one should not expect SQL Server to always behave 100% according to the specification.

The overriding factor here is the weighting given to each Code Point, and the fact that multiple Code Points can share the same weight specification. You can find the basic weights (no locale-specific overrides) here (I believe the 100 series of Collations is Unicode v 5.0 -- informal confirmation in the comments on the Microsoft Connect item):

http://www.unicode.org/Public/UCA/5.0.0/allkeys.txt

The Code Point in question – U+FFFD – is defined as:

FFFD  ; [*0F12.0020.0002.FFFD] # REPLACEMENT CHARACTER

That notation is defined in section 9.1 Allkeys File Format of the UCA:

<entry>       := <charList> ';' <collElement>+ <eol>
<charList>    := <char>+
<collElement> := "[" <alt> <weight> "." <weight> "." <weight> ("." <weight>)? "]"
<alt>         := "*" | "."

Collation elements marked with a "*" are variable.

That last line is important as the Code Point we are looking at has a specification that indeed starts with "*". In section 3.6 Variable Weighting there are four possible behaviors defined, based on Collation configuration values that we have no direct access to (these are hard-coded into the Microsoft implementation of each Collation, such as whether case-sensitive uses lower-case first or upper-case first, a property which is different between VARCHAR data using SQL_ Collations and all other variations).

I do not have time to do the full research into which paths are taken and to infer which options are being used such that a more solid proof can be given, but it is safe to say that within each Code Point specification, whether or not something is considered "equal" is not going to always use the full specification. In this case, we have "0F12.0020.0002.FFFD" and most likely it is just levels 2 and 3 that are being used (i.e. .0020.0002.). Doing a "Count" in Notepad++ for ".0020.0002." finds 12,581 matches (including supplementary characters that we have not been dealing with yet). Doing a "Count" on "[*" returns 4049 matches. Doing a RegEx "Find" / "Count" using a pattern of \[\*\d{4}\.0020\.0002 returns 832 matches. So somewhere in this combination, plus possibly some other rules I am not seeing, plus some Microsoft-specific implementation details, is the full explanation of this behavior. And to be clear, the behavior is the same for all of the matching characters as they all match each other as they all have the same weight once the rules are applied (meaning, this question could have been asked about any one of them, not necessarily Mr. �).

You can see with the query below and changing the COLLATE clause as per the results below the query how the various sensitivities work across the two versions of Collations:

;WITH cte AS
(
  SELECT     TOP (65536) ROW_NUMBER() OVER (ORDER BY (SELECT 0)) - 1 AS [Num]
  FROM       [master].sys.columns col
  CROSS JOIN [master].sys.objects obj
)
SELECT cte.Num AS [Decimal],
       CONVERT(VARBINARY(2), cte.Num) AS [Hex],
       NCHAR(cte.Num) AS [Character]
FROM   cte
WHERE  NCHAR(cte.Num) = NCHAR(0xFFFD) COLLATE Latin1_General_100_CS_AS_WS --N'�'
ORDER BY cte.Num;

The various counts of matching characters at different collations is below.

Latin1_General_100_CS_AS_WS   =   5840
Latin1_General_100_CS_AS      =   5841 (The "extra" character is U+3000)
Latin1_General_100_CI_AS      =   5841
Latin1_General_100_CI_AI      =   6311

Latin1_General_CS_AS_WS       = 21,229
Latin1_General_CS_AS          = 21,230
Latin1_General_CI_AS          = 21,230
Latin1_General_CI_AI          = 21,537

In all of the collations listed above N'' = N'�' also evaluates to true.

UPDATE

I was able to do a little more research and here is what I found:

How it "probably" should work

Using the ICU Collation Demo, I set the locale to "en-US-u-va-posix", set the strength to "primary", checked show "sort keys", and pasted in the following 4 characters that I copied from the results of the query above (using the Latin1_General_100_CI_AI Collation):

�
Ԩ
ԩ
Ԫ

and that returns:

Ԫ
    60 2E 02 .
Ԩ
    60 7A .
ԩ
    60 7A .
�
    FF FD .

Then, check the character properties for "�" at http://unicode.org/cldr/utility/character.jsp?a=fffd and see that the level 1 sort key (i.e. FF FD) matches the "uca" property. Clicking on that "uca" property takes you to a search page – http://unicode.org/cldr/utility/list-unicodeset.jsp?a=%5B%3Auca%3DFFFD%3A%5D – showing just 1 match. And, in the allkeys.txt file, the level 1 sort weight is shown as 0F12, and there is only 1 match for that.

To make sure that we are interpreting the behavior correctly, I looked at another character: GREEK CAPITAL LETTER OMICRON WITH VARIA Ὸ at http://unicode.org/cldr/utility/character.jsp?a=1FF8 which has a "uca" (i.e. level 1 sort weight / collating element) of 5F30. Clicking on that "5F30" takes us to a search page – http://unicode.org/cldr/utility/list-unicodeset.jsp?a=%5B%3Auca%3D5F30%3A%5D – showing 30 matches, 20 of them being in the 0 - 65535 range (i.e. U+0000 - U+FFFF). Looking in the allkeys.txt file for Code Point 1FF8, we see a level 1 sort weight of 12E0. Doing a "Count" in Notepad++ on 12E0. shows 30 matches (this matches the results from Unicode.org, though it is not guaranteed to since the file is for Unicode v 5.0 and the site is using Unicode v 9.0 data).

In SQL Server, the following query returns 20 matches, same as the Unicode.org search when removing the 10 supplementary characters:

;WITH cte AS
(
  SELECT TOP (65535) ROW_NUMBER() OVER (ORDER BY (SELECT 0)) AS [Num]
  FROM   [master].sys.columns col
  CROSS JOIN [master].sys.objects obj
)
SELECT cte.Num AS [Decimal],
       CONVERT(VARCHAR(50), CONVERT(VARBINARY(2), cte.Num), 2) AS [Hex],
       NCHAR(cte.Num) AS [Character]
FROM cte
WHERE NCHAR(cte.Num) = NCHAR(0x1FF8) COLLATE Latin1_General_100_CI_AI
ORDER BY cte.Num;

And, just to be sure, going back to the ICU Collation Demo page, and replacing the characters in the "Input" box with the following 3 characters taken from the list of 20 results from SQL Server:

Ὂ

Ὸ

shows that they, indeed, all have the same 5F 30 level 1 sort weight (matching the "uca" field on the character property page).

SO, it certainly does seem as though this particular character should not match anything else.

How it actually works (at least in Microsoft-land)

Unlike within SQL Server, .NET has a means of showing the sort key for a string via the CompareInfo.GetSortKey Method. Using this method and passing in just the U+FFFD character, it returns a sort key of 0x0101010100. Then, iterating over all of the characters in the range of 0 - 65535 to see which ones had a sort key of 0x0101010100 returned 4529 matches. This does not exactly match the 5840 returned in SQL Server (when using the Latin1_General_100_CS_AS_WS Collation), but it is the closest we can get (for now) given that I am running Windows 10 and .NET Framework version 4.6.1, which uses Unicode v 6.3.0 according to the chart for the CharUnicodeInfo Class (in "Note to Callers", in the "Remarks" section). For the moment I am using a SQLCLR function and so cannot change the target Framework version. When I get a chance I will create a console app and use a target Framework version of 4.5 as that uses Unicode v 5.0, which should match the 100 series Collations.

What this test shows is that, even without the exact same number of matches between .NET and SQL Server for U+FFFD, it is pretty clear that this is not SQL Server-specific behavior, and that whether intentional or oversight with the implementation done by Microsoft, the U+FFFD character does indeed match quite a few characters, even if it shouldn't according to the Unicode specification. And, given that this character matches U+0000 (null), it is probably just an issue of missing weights.

ALSO

Regarding the difference in behavior in the = query vs the LIKE N'%�%' query, it has to do with the wildcards and the missing (I assume) weights for these (i.e. �  Ƕ  Ƿ  Ǹ) characters. If the LIKE condition is changed to being simply LIKE N'�' then it returns the same 3 rows as the = condition. If the issue with the wildcards is not due to "missing" weights (there is no 0x00 sort key returned by CompareInfo.GetSortKey, btw) then it could be due to these characters potentially having a property that allows the sort key vary based on context (i.e. surrounding characters).


Here is my favourite article for beginners: http://www.sqlmag.com/article/tsql3/t-sql-101-lesson-1

10 articles in total, you will be a pro in no time.


Simple way to export and import data in SQL Server

Right Click DatabaseName -> Go to Task -> Generate Script
One dialog is open

1). Select ALL Table & Click on Next

2). Click on Advance Select  button and select value "Data only" in Type data to Script dropdown

3). And Click Finish

4). Copy result .sql file to target machine.

5). run resultant .sql file to target machine.


SQL Server 2019 UPDATE Statement SET to function does not execute the function again for each row

I suspect this is because of a new SQL 2019 feature, scalar UDF inlining:

With this new feature, scalar UDFs are automatically transformed into scalar expressions or scalar subqueries that are substituted in the calling query in place of the UDF operator. These expressions and subqueries are then optimized. As a result, the query plan will no longer have a user-defined function operator, but its effects will be observed in the plan, like views or inline TVFs.

This can be a performance benefit, but in your case it appears to be processing the function only once, then applying the same result to every row.
The bottom of the linked article describes several ways to disable scalar UDF inlining.
You can disable it database by database:
ALTER DATABASE SCOPED CONFIGURATION SET TSQL_SCALAR_UDF_INLINING = OFF;

Or force this option query-by-query, using a hint:
OPTION (USE HINT('DISABLE_TSQL_SCALAR_UDF_INLINING'));

Or you can disable it by altering the UDF itself:
WITH INLINE = OFF


Table mysql/innodb_index_stats has length mismatch in the column name table_name. Please run mysql_upgrade

Maybe this information is useful.
https://dev.mysql.com/doc/relnotes/mysql/5.7/en/news-5-7-23.html

Note
This release includes a change to the innodb_index_stats and innodb_table_stats system tables. When upgrading to this release, be sure to run mysql_upgrade in order to include these changes.


The index you have named cat_id doesn't have any columns in it.  You must actually index something in your index.  The FOREIGN KEY () column list is also empty.  You must actual point at some foreign keys.


I suggest you read up about database normalization. Here is a good starting point: Third normal form

Definitely do not create one table per employee as management of that database structure will soon become too cumbersome and your application will need to be modified every time a new employee is hired.

I do not know what your business rules are, but assuming that Performance, Quality and Attendance all store different types of data (i.e., different column types) then they should be in separate tables. I could not guess at what primary key you should use without knowing what types of data are stored in those tables.

Can you provide more information?


Oracle 11.2 restore DB to a specific point

From the very first lines in the Oracle Documentation about Flashback Database feature ( https://docs.oracle.com/cd/E11882_01/backup.112/e10643/rcmsynta023.htm#RCMRF194 ):

  This command works by undoing changes made by Oracle Database to the data files that exist when you run the command.

Flashback its mean to undo logical changes in the database. A Backup its a cloned copy of your database in a point in time. So, if you want to reproduce the exactly same environment for any subsequent tests, involving DB changes, you should go for a RMAN backup/restore technique.

Also, for the single instance DB, If it's going to be just a Test environment, you could avoid the creation of Archives and Flashback logs for saving lots of space, and create/restore from cold backups.

Scripting to recreate the DB + dp exp/imp can be another option, but its a waste of time having RMAN.


Use an IDENTITY column for the incrementing ID value, then prefix it with 11- as a computed column.

CREATE TABLE [Table2]
    (
    ID int IDENTITY(1,1),
    ID_pretty AS ('11-' + CAST(ID AS nvarchar))
    CONSTRAINT pk_ID PRIMARY KEY (ID))
GO


join the two tables which possess one to many relationship

The relationship appears to be between content_field_mem_id.field_mem_id_value and ex.mem_id
However, since content_field_mem_id.field_mem_id_value is an int and ex.mem_id is a varchar, you will need to cast/convert one of the columns as part of your join.

It would be more reliable (although slower) to convert the int column to a varchar.

You didn't say which DB technology you are using, so I can't show the exact syntax for the convert (it is very different between MS SQL/Oracle/DB2/etc).


Why does my PostgreSQL query cost so much when I tack on an additional group-by?

The reason why the third statement is slow is it has to group 2.8 million rows which cannot be done in memory any longer. So the sorting is done on disk which obviously is a lot slower:

You can see that in this line:

Sort Method: external merge  Disk: 286688kB"

The number of rows and the time it took can be seen in this line::

Sort  (...) (actual time=19110.027..22635.209 rows=2807146 loops=1)

You can also see that the first and second statements did the grouping using a hash function and both only had to process less than 2000 rows.

HashAggregate  (cost=...) (actual time=1617.381..1617.799 rows=1794 loops=1)

The memory that Postgres is allowed to use for sorting is controlled through the configuration parameter work_mem. If you increase that until the sorting can be done in memory, the performance should get better.


Found the answer.
I was Trying:

USE [FruitHistory]
GO

ALTER TABLE [dbo].[Fruit]  WITH CHECK ADD  CONSTRAINT [FK_Fruit_OnTruck] FOREIGN KEY([TruckNumber],[Vendor])
REFERENCES [dbo].[Trucks] ([TruckNumber],[Vendor])
GO

ALTER TABLE [dbo].[Fruit] CHECK CONSTRAINT [FK_Fruit_OnTruck]
GO
GO

But what actually worked was

USE [FruitHistory]
GO

ALTER TABLE [dbo].[Fruit]  WITH CHECK ADD  CONSTRAINT [FK_Fruit_OnTruck] FOREIGN KEY([Vendor],[TruckNumber])
REFERENCES [dbo].[Trucks] ([Vendor],[TruckNumber])
GO

ALTER TABLE [dbo].[Fruit] CHECK CONSTRAINT [FK_Fruit_OnTruck]
GO
GO

Not Completely sure why but reversing the columns allowed it to be set


Name of the product price, line-item database pattern?

Not every approach to common problems has a name.  Certainly at least not a commonly agreed name.  The term "design patterns" applies just as well to common database designs as it does to OOP.  I would say data modellers are a little less enamoured with the idea of design patterns than OOP programmers.

I'm not familiar with a commonly agreed name for the design approach that you're describing.  I call it transactional logging.

As an aside, the price in the sale item table is not technically redundant, even if the price hasn't changed.  The price in the product table is the current book price, the price in the sale item table is the actual price charged.  There is no way of knowing for sure what price was actually charged from looking at book price.  Even if you have a book price history table it might have been possible, depending on your system's rules, for the price to be overridden manually.  The two data attributes are semantically different, even if they happen to be coincidentally equal for some period of time.


Search 200M documents of text by keyword in text

Just think about your requirements and what you're asking and if an answer can even been given here. Wikipedia has 5.3 M documents. You're talking about a load on that scale. And you want to know whether or not you can query it in 0.1 sec? You're going to have provide the sample text. I'm all about making sample data, but how am I even going to generate sample data for this question/

What you want is enterprise Full-Text Search (FTS): PostgreSQL has that. Whether or not it will perform as well as you intend, I have no idea. I've used it for reasonably large projects, it's worked fantastically.

  would be great to find text with part of the word: hous would find house, housing etc.

It also supports :* which does what you want. so just use hous:*

It is indexable using a GIST index. All of that is in the docs.

{
  id: 1,
  reference_id: 1,
  text: 'text of unique. 10-1000 words each ',
}

So just

CREATE TABLE foo (
  id            int PRIMARY KEY,
  reference_id  int, -- no idea what this is
  document      text,
);
CREATE INDEX ON foo USING GIST (to_tsvector(document));


SQL Server query performance affected by rebuilding an un-fragmented index

It sounds like the rebuild of the index is a red herring, especially given it was non-clustered. The rebuild is more likely to be invalidating a bad query plan in cache, and on re-run the good query plan is generated and used again.

Captue the query plan when the performance is bad and then after the rebuild and compare them, I suspect you will see a different plan.

That would be my first step - e.g. demonstrate what changes to the plan due to the index rebuild, or prove there is no change on the plan.


Oracle, Cursor_Sharing = Force and Queries with literal comparisons forcing FTS

You can not correct a bad implemented application from the database side in such a way that it will always work as expected. With cursor sharing the database tries to prevent parsing. This works when the application uses bind variables when working on item/person level and literals when working on group/class level.
You could try cursor_sharing force. This could correct some queries but at the same time hurt others.
You could try sql plan management but in my experience, when you really need it, it fails. Especially with literal littered applications. Sometimes you have luck but don't underestimate the amount of work you need to put in them. Auto sampling of plans is not the best idea here ....


Use a LEFT JOIN and check for null values. Something like:

SELECT profile.* FROM user_profile profile
 LEFT JOIN user_friend friend1 ON friend1.user_profile1_id = user_profile_id
 LEFT JOIN user_friend friend2 ON friend2.user_profile2_id = user_profile_id
 WHERE friend1.user_profile1_id IS NULL AND friend2.user_profile2_id IS NULL

Here's a link to the various joins


No. There  is no possibility to pass for operating system request not to cache file reads for IO done for specific query. You can reduce sysctl parameter vm.swappiness to 1 to make cache recovery more aggressive, but that affects all IO caching in the server.


Try to Restart Oracle Service by following Commands

net stop oracleserviceORCL;
net start oracleserviceORCL;

and then start listener first, if it is not already started by this command.

lsnrctl start listener;

and update me if it help...


SQL hourly data aggregation in postgresql

select
  date_trunc('hour', t - interval '1 minute') as interv_start,
  date_trunc('hour', t - interval '1 minute')  + interval '1 hours' as interv_end,
 sum(v)
  from myt
    group by date_trunc('hour', t - interval '1 minute')
order by interv_start

see sqlfiddle

As for the index: you could try a function index on date_trunc('hour', t - interval '1 minute') but I'm not sure postgresql can use it.


MySQL Timezone Resets after exiting server

Abstract taken from another Stack Overflow Answer - Adjusting for the
  default time-zone setting on RDS

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

$ rds-describe-db-parameters default | grep "time_zone"
DBPARAMETER  default_time_zone                                                                   engine-default  string   static   false

To set the global value via SET GLOBAL you need to have the SUPER privilege which is not granted to you as a RDS user.

The only way to set the time_zone is on a per-connection basis

mysql> SET time_zone = timezone;

On my machines I've tried US/Eastern successfully but I got a quite old generation running.

To determine the timezones you have available log into your box

mysql -h yourboxhost.rds.amazonaws.com -u <youruser> -p

and type

mysql> SELECT * FROM mysql.time_zone_name;

You should get a list of installed and valid timezone names you can set on your instance

+----------------------------------------+--------------+
| Name                                   | Time_zone_id |
+----------------------------------------+--------------+
| Africa/Abidjan                         |            1 |
| Africa/Accra                           |            2 |
| Africa/Addis_Ababa                     |            3 |
| Africa/Algiers                         |            4 |
| Africa/Asmara                          |            5 |
| Africa/Asmera                          |            6 |
| Africa/Bamako                          |            7 |
| Africa/Bangui                          |            8 |
| Africa/Banjul                          |            9 |
| Africa/Bissau                          |           10 |
| Africa/Blantyre                        |           11 |
| Africa/Brazzaville                     |           12 |
| Africa/Bujumbura                       |           13 |
| Africa/Cairo                           |           14 |
etc...

You have to set the time_zone each time you connect to your database server

For example if you use the php Mysqli extension you can do this

$mysqli = mysqli_init();
mysqli_options($mysqli,MYSQLI_INIT_COMMAND,"SET time_zone = 'Africa/Brazzaville'" );
mysqli_real_connect($mysqli,$host, $user, $pass,$dbName) or die ('Unable to connect');

Otherwise just manually ( in terms of let your database connector do it ) execute the SET time_zone = '<YOUR_DESIRED_TIMEZONE>' Query right after you've connected to your database


How many pool connection size should be set for a 1000 users Jira system?

The answer depends on how many cores you have on your database system, how many concurrent I/O requests it can handle and what the ratio of active versus idle time is while the application is using a connection.

I would start with a maximum of 20 sessions.


Use serial ID for multiple columns and return it from INSERT

Syntax errors

Immediate causes for the error:

WHERE  id = select currval('mytable_id_seq');

And a missing semicolon at the end of the DO statement:

...
$do$;

Better query

On a closer look, this does not seem to be another case of UPSERT.
It would seem one of the inserted columns needs to depend on multiple input values in combination with the freshly retrieved value for a serial ID. Try this largely simplified statement:

INSERT INTO mytable (id, name, uniquecallid)
SELECT t.id
     , t.name
     , CASE WHEN t.uniquecallid <> '' THEN t.uniquecallid
            ELSE COALESCE(t.callidpad, '') || t.id END

FROM (
   SELECT nextval('mytable_id_seq') AS id
        , '@name'::text             AS name         -- provide values here, once
        , '@UniqueCallId'::text     AS uniquecallid
        , '@CallIdPad'::text        AS callidpad
   ) t
RETURNING id;

An explicit cast to text (or your actual undisclosed data type(s)) is needed.

This is equivalent to the presented code, just with legal syntax and much faster - unless there are triggers or non-standard default values you did not declare ...

Simpler with currval()

On an even closer look, this can be simplified further:

INSERT INTO mytable (name, uniquecallid)
SELECT '@name'::text
     , COALESACE( NULLIF('@UniqueCallId'::text, '')
                , COALESCE('@CallIdPad'::text, '') || currval('mytable_id_seq'))
RETURNING id;

Detailed explanation:

PostgreSQL next value of the sequences?


Comment out the left join to AppHisTeams as you do not use that table.

Creating indexes tends to be an art, you should not just create indexes. You may be able to adjust existing ones.

You should index AppHisScanCodes as it is not using one, maybe on scanid and include [Description] or use scanid as the clustered index.

Can you change the left joins to inner joins? Especially to AppHisActivigity, 30,000,000+ rows, as you are only using it to get to AppHisTeams.

Is MOdate a date field or is it a number?

Can you inner join to AppHisOutput, 20,000,000+ rows?

Look at the existing index IX_AppHis_ABR. Can you add the fields TitleNumber, AppType or IntakeDate with an include statement if they are not in the index already?

Add an index to AppHisOutput on MODate. Index on abr include modate.


You can use the aggregate function array_agg() for this purpose.

select price, type, array_agg(id) ids
from   tbl
group by price, type

price | type  | ids
----: | :---- | :------
 1.00 | fruit | {1,2,3}
 2.00 | veg   | {4,5,6}

dbfiddle here


Why is my query suddenly slower than it was yesterday?

Dear [your name here]!

Oh no, I'm sorry to hear that! Let's start with some basics to get you fixed up in a jiffy.

The thing you're running into is called Parameter Sniffing

It's a way out wiggy weird problem. The name rolls right off the tongue. Like the German word for squirrel.

And it's usually your friend.

When a query hits your server, a plan has to be compiled. To save time and resources later, an execution plan is cached based on the estimated rows that parameter will cause your code to process and return.

The easiest way to picture this going bad is to imagine a stored procedure that needs to count things from two lopsided populations.

For example:

People wearing CrossFit shirts who aren't injured: Zero
People wearing CrossFit shirts who wince when they wince: All

Obviously, one execution of that code would have to do a lot more work than another, and the query plans you'd want to do totally different amounts of work would look totally different.

What am I up against?

This is a genuinely difficult problem to find, test, and fix.

It's hard to find because it doesn't happen consistently
It's hard to test because you need to know which parameters cause different plans
It's hard to fix because sometimes it requires query and index tuning
It's hard to fix because you may not be able to change queries or indexes
It's hard to fix because even if you change queries or indexes, it might still come back

Quick Fixes

Sometimes, all you need is a little clarity. Or rather, your plan cache does.

If it's a stored procedure

Try running EXEC sys.sp_recompile @objname = N'schema.procname'. That'll cause the procedure to recompile a new plan the next time it runs.

What this won't fix:

Processes currently running it.

What this doesn't guarantee:

The next process that runs after recompiling will use a parameter that gives you a good plan.

You can also point sp_recompile at a table or view, but be forewarned that all code that touches that table or view will recompile. This could make the problem a whole lot harder.

If it's a parameterized query

Your job is a little more difficult. You'll need to track down the SQL Handle. You don't want to free the entire plan cache -- just like using sp_recompile against a table or view, you could trigger (ha ha ha) a whole bunch of unintended consequences.

The easiest way to figure that command out is to run sp_BlitzWho*! There's a column called "fix parameter sniffing" that has a command to remove a single plan from the cache. This has the same drawbacks as recompile, though.

What this won't fix:

Processes currently running it.

What this doesn't guarantee:

The next process that runs after recompiling will use a parameter that gives you a good plan.

I still need help!

We're going to need the following things:

The good query plan, if possible
The bad query plan
The parameters used
The query in question
Table and index definitions

Getting the query plans and query

If the query is running, you can use sp_BlitzWho* or sp_WhoIsActive to capture currently executing queries.

EXEC sp_BlitzWho;

EXEC sp_WhoIsActive @get_plans = 1;

If the query isn't currently executing, you can check for it in the plan cache, using sp_BlitzCache*.

If you're on SQL Server 2016+, and have Query Store turned on, you can use sp_BlitzQueryStore*.

EXEC dbo.sp_BlitzCache @StoredProcName = 'Your Mom';

EXEC dbo.sp_BlitzQueryStore @StoredProcName = 'Your Mom';

These will help you track down cached version(s) of your Stored Procedure. If it's just parameterized code, your search is a little more difficult. This may help, though:

EXEC dbo.sp_BlitzCache @QueryFilter = 'statement';

You should see fairly similar output from any of those. Again, the query plan inviting cool blue clicky column is your friend.

The easiest way to share plans is using Paste The Plan*, or dumping the XML into pastebin. To get that, click on either one of those inviting blue clicky columns. Your query plan should appear in a new SSMS tab.

If you're touchy about sharing your company's code and query, you can use Sentry One's free Plan Explorer tool to anonymize your plan. Keep in mind, this makes getting help harder -- anonymized code is a lot harder to read and figure out.

All of these tools we talked about should return the Query Text. You don't need to do anything else here.

Getting the parameter(s) is a little more difficult. If you're using Plan Explorer, there's a tab at the bottom that lists them all out for you.

If you're using sp_BlitzCache*, there's a clickable column which gives you the execution statement for stored procedures.

Getting the table and index definitions

You can easily right click in SSMS to script things out.

If you want to get everything in one shot, sp_BlitzIndex* can help if you point it directly at a table.

EXEC dbo.sp_BlitzIndex @DatabaseName = 'StackOverflow2010',
                       @SchemaName = 'dbo',
                       @TableName = 'Users';

This will give you the table definition (though not as a create statement), and create statements for all your indexes.

Collecting and adding this information to your question should get people enough information to help, or point you in the right direction.

I wanna do it myself!

Well, cool. I'm happy for you. You crazy person.

There are a lot of ways people think they "fix" parameter sniffing:

Recompile hints
Optimize for unknown
Optimize for a value

But these really just disable parameter sniffing in different ways. That's not to say they can't solve the problem, they just don't really get to the root cause.

That's because getting to the root cause is usually kind of difficult. You have to look for those pesky "plan quality issues".

Starting with the fast vs slow plans, look for differences like:

Indexes used
Join order
Serial vs Parallel

Also look for different operators that make your code sensitive to parameter sniffing:

Lookups
Sorts
Join type
Memory grants (and by extension, spills)
Spools

Don't get too wrapped up in seek vs scan, index fragmentation, or any of the cargo cult-y stuff people hem and haw about.

Usually, there's a pretty basic indexing problem. Sometimes the code needs a little rewriting.

If you want to learn more about parameter sniffing:

Slow in the Application, Fast in SSMS? - Erland Sommarskog
Troubleshooting Parameter Sniffing 1, 2, 3 - Tara Kizer
Why You’re Tuning Stored Procedures Wrong (the Problem with Local Variables) - Kendra Little
How to Use Parameters Like a Pro and Boost Performance - Guy Glantser
Parameter Sniffing, Embedding, and the RECOMPILE Options - Paul White

If you're reading this, and you think I missed a link or helpful tool, leave a comment. I'll do my best to keep this up to date.


Maximum overall size of MariaDB binary logs

max_binlog_size
With regards to max_binlog_size, what you are asking is not possible. Why ?
On May 05, 2017, I answered the question MySQL - Binary Log File size growing more than the maximum limit. I mentioned the following from the MySQL Documentation on max_binlog_size:

A transaction is written in one chunk to the binary log, so it is never split between several binary logs. Therefore, if you have big transactions, you might see binary log files larger than max_binlog_size.

That fact that your binary logs are bigger than max_binlog_size just indicates that you are writing too many changes per single transaction.
expire_logs_days
With regards to expire_logs_days, the granularity is in days. There is nothing you can do with that.
If you wish to squeeze the number of binlogs down, you may have do one of two things:
SUGGESTION #1 : Scan in the binlog index file, find the last 10 binlogs, get the first binlog of those 10, and run something like:
PURGE BINARY LOGS TO 'whatever-binlog.002316';

SUGGESTION #2 : Write a cronjob that will keep the last 18 hours of binary logs:
PURGE BINARY LOGS BEFORE (NOW() - INTERVAL 18 HOUR);

GIVE IT A TRY !!!


Why am I getting `Impossible WHERE noticed after reading const tables` in explain query?

You are getting the message

  Impossible WHERE noticed after reading const tables

This is documented in the page you already linked.

  MySQL has read all const (and system) tables and notice that the WHERE clause is always false

const tables are defined as

  The table has at most one matching row, which is read at the start of
  the query.  ... const is used when you compare all parts of a PRIMARY KEY or UNIQUE
  index to constant values.

You have a UNIQUE KEY on  (fromid,toid). The query on WHERE fromid=78 AND toid=60 can be satisfied by reading this unique index. From the message you are getting this must return no results.

Similarly the query WHERE fromid=60 and toid=78 AND (is_approved='s'  OR is_approved='f' OR is_approved='t') can also use this index to locate the row of interest (though it still has a residual predicate to evaluate were any row to match).

Your other query is different

SELECT rid
FROM   relationship
WHERE  fromid = 60
       AND toid = 78
       AND is_approved = 's'
        OR is_approved = 'f'
        OR is_approved = 't'

AND has a higher precedence than Or, so this is the same as

SELECT rid
FROM   relationship
WHERE  ( ( fromid = 60 ) AND ( toid = 78 ) AND ( is_approved = 's' ) )
        OR ( is_approved = 'f' )
        OR ( is_approved = 't' )

This can no longer use that index and has different semantics in that it will return any rows where is_approved IN ('f','t') irrespective of what the values in the other columns are.


How to get the ID of the conflicting row in upsert?

This will work (as far as I tested) in all 3 cases, if the to-be-inserted values are all new or all already in the table or a mix:

WITH
  val (name) AS
    ( VALUES                          -- rows to be inserted
        ('foo'),
        ('bar'),
        ('zzz')
    ),
  ins AS
    ( INSERT INTO
        tag (name)
      SELECT name FROM val
      ON CONFLICT (name) DO NOTHING
      RETURNING id, name              -- only the inserted ones
    )
SELECT COALESCE(ins.id, tag.id) AS id,
       val.name
FROM val
  LEFT JOIN ins ON ins.name = val.name
  LEFT JOIN tag ON tag.name = val.name ;

There's probably some other ways to do this, perhaps without using the new ON CONFLICT syntax.


It Requires ALTER TRACE permission on the server.

Note :- This feature will be removed in a future version of Microsoft SQL Server (2014 above). Avoid using this feature in new development work.


Which grants are needed to access a table?

There is no simple list of necessary and sufficient grants, because there are multiple paths that can be used.  CONNECT on the database and USAGE on a schema are often done through "PUBLIC" rather than to individual roles, for example.

Newly created databases will have CONNECT privilege for PUBLIC by default, so unless you have gone out of your way to remove it no explicit grant is needed in that case.  When you create a database, it comes with a schema named "public" which also has USAGE granted to PUBLIC, but schemas you create manually do not come with that privilege.


Actually this error is a bit confusing and that's the reason you are asking these questions.

Correct statement would be:

DECLARE @UniqueRequests int;
SET @UniqueRequests = (SELECT COUNT(*) FROM (SELECT DISTINCT sender_id, send_to_id FROM friend_request) friends);

I did testing on some other table with same concept and you may see the result as below:

Answer to these questions are as below:

  What is the correct statement?

DECLARE @UniqueRequests int;
SET @UniqueRequests = (SELECT COUNT(*) FROM (SELECT DISTINCT sender_id, send_to_id FROM friend_request) friends);

  It seems that we must use parentheses around a SELECT when assigninig a variable. That is to say, we need to write SET
  @MyVariable = (SELECT some_expression); Why is that so?

This is because you are setting the value, and you can't assign keyword and other strings to an int, rather you want to assign the value which means query has to run first and that result you want to assign to this particular integer variable.

Alternatively if you wish to only set the value, you can use below:

DECLARE @UniqueRequests int;
select @UniqueRequests = COUNT(*) FROM CommandLog
print @UniqueRequests

  Is there any documentation page on the use of parentheses in SQL Server in general?

You may refer this from Micrososoft, this thread and this link.

Hope this helps.


When is the best time to use RMAN vs when to use Data Pump?

The rman utility is for making a physical backup of the database that can be used to restore the database back to a consistent image for the entire database generally at the point of failure when combined with the archived redo log backups.  Datapump is for making logical point in time copies of data.  Forward recovery of the data to a point in time more current than the time of the export is not provided.  In an export row data may be at different points in time for different tables resulting in some minor data inconsistencies upon import completion.  Export may be good enough to use as a backup for test databases.  Both rman backups and export should probably be ran on production databases.


Table contains multiple names for same company

In this case I'd create a new table called CompanyAlias that will contain all possible aliases that you get for all companies. A possible structure would be:

CompanyAlias:

AliasId - int - Primary Key - some generated id
Alias - varchar(100) - the name of the alias
CompanyID - int - Foreign Key - references the original table of companies (in case you don't have one, I believe you should create one table with all unique companies and any other details that you might get)
any other column you'd like to have (description, creator..etc)

A query that will bring all the transactions for the main company of a given alias could look like:

declare @alias varchar(100);
set @alias = 'MicroS';

Select t.TransactionId, t.Alias, c.Company
from Transaction t
join CompanyAlias ca on ca.Alias = t.Alias
join Company c on c.CompanyId = ca.CompanyId
where t.Alias in
(
    Select ca.Alias
    from CompanyAlias ca
    where ca.CompanyId =
    (
        Select ca.CompanyId
        from CompanyAlias ca
        where ca.Alias = @alias
    )
);

Where the tables would be:

Transaction - contains monetary transactions (should contain company alias used by client)
CompanyAlias - defined earlier
Company - defined by you to have unique companies and their main details

PS: in case the SELECT statement is too bulky (written in notepad), I want to mention that I wanted to:

get the CompanyId of the searched alias (the one that you specify in the search form)
get all the aliases of the mentioned CompanyId
get all the transactions of the mentioned aliases


Lets first look at few of the output terms to understand better.

numYields

The number of times the operation yielded to allow other operations to complete. Typically, operations yield when they need access to data that MongoDB has not yet fully read into memory. This allows other operations that have data in memory to complete while MongoDB reads in data for the yielding operation.
Does a read or write operation ever yield the lock?

reslen

The length in bytes of the operation’s result document. A large responseLength can affect performance. To limit the size of the result document for a query operation, you can use any of the following:

Projections
The limit() method
The batchSize() method

acquireCount

Number of times the operation acquired the lock in the specified mode.

How do you troubleshoot this thing?

I am taking a guess here as I cannot see your query plan. I suggest using explain method and looking at the plan. Especially focusing on index usage, scan/seek.

See this answer about high number of yields.

Monitor your instance for some key matrix which will help you tune your long running queries.  In your case I would especially focus on index misses.

mongotop - shows how much time was spend reading or writing each
collection over the last second
mongostat - brilliant live debug tool, gives a view on all your
connected MongoDB instances


What learning resources are there for part-time DBAs?

If your going to be "The DBA" (part or full time) you should learn as much as you can before the former DBA leaves. I think people in this situation should talk to anyone who knows about the database (the data flow, the design choices, common task, etc.), it is very hard to try to learn the db even if documentation exists.

Also, the person should have something like a dba profile, If you don't like it you would be doing something that brings no excitement and you will have no satisfaction from your work.


Does "Synchronous-Commit Availability Mode" ensure consistency between Replicas?

SYNCHRONIZED state only ensures that the writes are hardened by the secondary (log written to disk). It says nothing about them being applied (data changed).

  can I expect consistent results to be returned from both replicas, every time?

Yes. The reads are consistent, always. But keep in mind that in relational parlance consistency (ACID) has a different meaning from the distributed (CAP) consistency. You are not guaranteed to read the most recent consistent state. Particularly, you are not guaranteed to read your own committed writes. And the reads from each replica, while each being consistent, may not match.


Mongo DB Replica set Stuck at RECOVERING state

The easy, albeit a bit unsecure way

Stop the first secondary
Delete the content of it's dbpath
Restart the secondary
Wait for it to catch up with the primary
Repeat process with the second secondary

This is a bit unsecure as it is unknown why the secondaries entered the Recovering state.

The more secure, but also more intrusive way

As above, but stop your application during the process. This prevents the possibility that your application is inserting more data than the secondaries are able to replicate. However, the problem may occur during production.

The most secure, but also most intrusive way

Shut down the whole replica set
Remove the content of dbpath on both secondaries
Copy the content of dbpath to both secondaries' dbpath
Start the old primary.
Start one of the old secondaries.
Wait until a new primary is elected.
Start the remaining secondary.

Some notes:

Use MMS. It's free, it's easy to set up and it gives you good information about your replica set. Try to keep the value for "replication lag" around 0, and take all means necessary that your replication lag is never greater than the "replication oplog window".

Always make sure you have a 1Gb network and a (sorry) shitload of RAM. The more, the better. Additional rule of thumb: rather half the RAM and SSDs than double the RAM and no SSDs (with RAM remaining within reasonable limits).

Disclaimer: Always make a backup of production data before fiddling with it.


Pagination - Text comparison with greater than and less than with DESC

I don't think just placing an arbitrary value would work, since you have to put a larger if not possibly the largest value.

Perhaps pass in a boolean value with an OR operand (WHERE someflag OR last_name < @key), than way you do not have to pick arbitrary lowest value and largest values.

Otherwise if you can only control what is passed and not the WHERE operation, you'll have to use whatever the max value is for the column based on encoding.


Function without parameters, and calling a function inside

CREATE TABLE orders (
    id INT NOT NULL,
    code INT NOT NULL,
    service_id INT NOT NULL,
    descript CHARACTER VARYING(50),
    creation TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    status_date TIMESTAMP,
    created_user INT
    );

INSERT INTO orders
(id, code, service_id, descript, creation, status_date, created_user)
VALUES
(100,2394,558151,'Requested not today','2019-06-16 11:12','2019-06-18 14:08',1),
(100,2394,558151,'Fulfilled blue squit','2018-06-16 11:12','2018-06-18 14:08',1),
(100,2394,558151,'Requested blue kit','2019-06-16 11:12','2019-06-18 14:08',1),
(100,2395,558152,'Requested pony','2019-06-16 11:12','2019-06-18 14:08',1),
(100,2396,558153,'Requested sauron','2019-06-16 11:12','2019-06-18 14:08',1),
(100,2394,558151,'Requested I''m tired','2019-06-16 11:12','2019-06-18 14:08',1);

CREATE FUNCTION function_A(in numeric, in int,out noorder int)
    AS $$ SELECT COUNT(*) FROM orders WHERE EXTRACT(YEAR FROM  "status_date") = $1 AND "code" = $2 $$
    LANGUAGE SQL;

CREATE FUNCTION function_B(in numeric,out noorderyear int)
    AS $$ SELECT COUNT(*) FROM orders WHERE EXTRACT(YEAR FROM  "status_date") = $1  $$
    LANGUAGE SQL;

CREATE OR REPLACE FUNCTION summary_orders()
RETURNS TABLE (retyear numeric, order_desc TEXT, percentage numeric) AS $$
DECLARE
    retyear numeric DEFAULT 0;
    descrip TeXT DEFAULT 0;
    percent numeric DEFAULT 0;
BEGIN
    RETURN
        QUERY SELECT CAST(EXTRACT(year FROM status_date) as numeric) teayear,
                MIN(descript) as descrip,
                ROUND(Max(function_A(CAST(EXTRACT(year FROM status_date) as numeric),code))::decimal / function_B(CAST(EXTRACT(year FROM status_date) as numeric)),2) as percent
        FROM orders o
        GROUP BY teayear
        ORDER BY teayear ASC;
END;
$$
LANGUAGE plpgsql;

SELECT * FROM summary_orders()

retyear | order_desc           | percentage
------: | :------------------- | ---------:
   2018 | Fulfilled blue squit |       1.00
   2019 | Requested blue kit   |       0.60

db<>fiddle here


Should the Redo log on a production MySQL server be configured to another location?

Yes, that would be a good idea. I have mentioned a FaceBook Engineer's Blog about doing that, along with putting the DoubleWrite Buffer and Tablespace Files in other places, in my other posts

Feb 24, 2012 : Tuning dedicated Percona Server with XtraDB for OLTP (Also See Switching Gears TO RAID)
Feb 06, 2014 : MySQL on SSD - what are the disadvantages?
Jan 27, 2015 : what would be the primary disadvantages of splitting my tables into table spaces and/or separate schemata

RAID5 ??? OMG, NO !!!!!
Please do not put anything InnoDB-related in RAID5

Feb 14, 2012 : Is raid 5 suitable for a mysql installation?
Mar 24, 2013 : Innodb table is taking randomly long time to execute the insert query and in show processlist showing as queryend/updating

Since you will need the the redo logs only in the event of crash recovery, I would go with RAID10 SAS Drives for the redo logs because they are sequentially written files. I would shy away from RAID1 because you want full redundancy of disk in the event of a disk crash. RAID5 does plenty of parity checking which can inhibit sequential writes.


MySQL, comparison between, tables with not all records common, on 2 time intervals

Algorythm:

SELECT product_name,
       COALESCE(SUM(CASE WHEN `date` BETWEEN '2017-10-1' AND '2017-10-31'
                         THEN volume END), 'no sale') AS volume2017,
       COALESCE(SUM(CASE WHEN `date` BETWEEN '2018-10-1' AND '2018-10-31'
                         THEN volume END), 'no sale') AS volume2018,
       COALESCE(SUM(CASE WHEN `date` BETWEEN '2017-10-1' AND '2017-10-31'
                         THEN margin END), 'no sale') AS margin2017,
       COALESCE(SUM(CASE WHEN `date` BETWEEN '2018-10-1' AND '2018-10-31'
                         THEN margin END), 'no sale') AS margin 2018
FROM datatable
GROUP BY product_name
/* WHERE `date` BETWEEN '2017-10-1' AND '2017-10-31'
      OR `date` BETWEEN '2018-10-1' AND '2018-10-31' */


How to resolve TNS error in Oracle 11g when connect with Navicate)

Today i solve my problem connection error oracle 11g with navicate

i just change the port number and my TESTDB is connected.

so if any body have same issue kindly check your DB port.


PG: How to show total count for this query if result is 0

Then do it slightly different, use the (more or less) complementary of COALESCE, which is NULLIF:

SELECT
    NULLIF(books_count, 0) AS books_count,
    NULLIF(movies_count, 0) AS movies_count
FROM
    ( -- Original, slightly simplified query
    SELECT
        SUM(CASE WHEN _type = 'book'  THEN 1 END) AS books_count,
        SUM(CASE WHEN _type = 'movie' THEN 1 END) AS movies_count
    FROM
        selected_media
    WHERE
        subscriber_id = 1234
    ) AS s1 ;

dbfiddle here

If you don't want to have a NULL value when the result is zero, but another one, you can use:

SELECT
    CASE
    WHEN books_count <> 0 THEN books_count
    ELSE (another_value_or_expression_for_books)
    END  AS books_count,
    CASE
    WHEN movies_count <> 0 THEN movies_count
    ELSE (another_value_or_expression_for_movies)
    END  AS movies_count
FROM
    ( -- Original, slightly simplified query
    SELECT
        SUM(CASE WHEN _type = 'book'  THEN 1 END) AS books_count,
        SUM(CASE WHEN _type = 'movie' THEN 1 END) AS movies_count
    FROM
        selected_media
    WHERE
        subscriber_id = 1234
    ) AS s1 ;

You can also use count(case ...) instead of SUM, and, in latest versions of PostgreSQL, you can use a FILTER, instead of CASE:

SELECT
    NULLIF(books_count, 0) AS books_count,
    NULLIF(movies_count, 0) AS movies_count
FROM
    (SELECT
        count(*) FILTER (WHERE _type = 'book') AS books_count,
        count(*) FILTER (WHERE _type = 'movie') AS movies_count
    FROM
        selected_media
    WHERE
        subscriber_id = 1234
    ) AS s1 ;

dbfiddle here

If you need the fastest possible speed, and depending on the number of _types, and the number of elements of each type, you could use some partial indexes:

CREATE INDEX idx_books ON selected_media (subscriber_id) WHERE _type='book' ;
CREATE INDEX idx_movies ON selected_media (subscriber_id) WHERE _type='movie' ;

And then perform your query as two subqueries:

SELECT
    nullif( (select count(*) from selected_media where _type='book'
             and subscriber_id = 1234),
           0) AS count_books,
    nullif( (select count(*) from selected_media where _type='movie'
             and subscriber_id = 1234),
           0) AS count_movies ;

This will give you a query plan doing two index only scans most of the time.

| QUERY PLAN                                                                                |
| :------------------------------------------------------------------------------------------------------------------------------------------------------------ |
| Result  (cost=164.10..164.12 rows=1 width=16) (actual time=1.491..1.492 rows=1 loops=1)                                                                       |
|   InitPlan 1 (returns $0)                                                                                |
|     ->  Aggregate  (cost=159.94..159.95 rows=1 width=8) (actual time=1.485..1.485 rows=1 loops=1)                                                             |
|           ->  Index Only Scan using idx_books on selected_media  (cost=0.28..150.36 rows=3833 width=0) (actual time=0.016..1.108 rows=3974 loops=1)           |
|                 Index Cond: (subscriber_id = 1234)                                                                                |
|                 Heap Fetches: 3974                                                                                |
|   InitPlan 2 (returns $1)                                                                                |
|     ->  Aggregate  (cost=4.15..4.16 rows=1 width=8) (actual time=0.002..0.002 rows=1 loops=1)                                                                 |
|           ->  Index Only Scan using idx_movies on selected_media selected_media_1  (cost=0.12..4.14 rows=1 width=0) (actual time=0.001..0.001 rows=0 loops=1) |
|                 Index Cond: (subscriber_id = 1234)                                                                                |
|                 Heap Fetches: 0                                                                                |
| Planning time: 0.094 ms                                                                                |
| Execution time: 1.514 ms                                                                                |

dbfiddle here

If you have very many _type, or you want your query to work with any possible _type (and not two specifically chosen) your second choice would be to have the following index:

CREATE INDEX idx_subscriber_type ON selected_media (subscriber_id, _type) ;

... then, the previous query will execute using this index

| QUERY PLAN                                                                                |
| :------------------------------------------------------------------------------------------------------------------------------------------------- |
| Result  (cost=1643.07..1643.09 rows=1 width=16) (actual time=0.679..0.680 rows=1 loops=1)                                                          |
|   InitPlan 1 (returns $0)                                                                                |
|     ->  Aggregate  (cost=821.53..821.54 rows=1 width=8) (actual time=0.670..0.671 rows=1 loops=1)                                                  |
|           ->  Bitmap Heap Scan on selected_media  (cost=29.98..819.19 rows=933 width=0) (actual time=0.170..0.573 rows=992 loops=1)                |
|                 Recheck Cond: ((subscriber_id = 1234) AND (_type = 'book'::text))                                                                  |
|                 Heap Blocks: exact=547                                                                                |
|                 ->  Bitmap Index Scan on idx_subscriber_type  (cost=0.00..29.75 rows=933 width=0) (actual time=0.113..0.113 rows=992 loops=1)      |
|                       Index Cond: ((subscriber_id = 1234) AND (_type = 'book'::text))                                                              |
|   InitPlan 2 (returns $1)                                                                                |
|     ->  Aggregate  (cost=821.53..821.54 rows=1 width=8) (actual time=0.006..0.006 rows=1 loops=1)                                                  |
|           ->  Bitmap Heap Scan on selected_media selected_media_1  (cost=29.98..819.19 rows=933 width=0) (actual time=0.005..0.005 rows=0 loops=1) |
|                 Recheck Cond: ((subscriber_id = 1234) AND (_type = 'movie'::text))                                                                 |
|                 ->  Bitmap Index Scan on idx_subscriber_type  (cost=0.00..29.75 rows=933 width=0) (actual time=0.005..0.005 rows=0 loops=1)        |
|                       Index Cond: ((subscriber_id = 1234) AND (_type = 'movie'::text))                                                             |
| Planning time: 0.077 ms                                                                                |
| Execution time: 0.706 ms                                                                                |

dbfiddle here


You can't "see" the SQL Server from within MySQL like you can in the reverse direction with a linked server.

The only way I can think of doing this is to create a MySQL trigger that calls a User Defined Function that executes an external application and passes the new data to it. The external application would have to connect to the SQL Server database and push the data to it.

DELIMITER @@

CREATE TRIGGER Test_Trigger
AFTER INSERT ON MyTable
FOR EACH ROW
BEGIN

DECLARE cmd CHAR(255);
DECLARE result int(10);
SET cmd=CONCAT('/usr/bin/php ', 'home/sync/send_to_sql_server.php f1=', NEW.f1, ' f2=', NEW.f2);
SET result = sys_exec(cmd);

END;
@@
DELIMITER ;


How to satisfy 2NF and what are superkeys? - Understanding the algorithm for database normalization in general

to satisfy 2NF, it is so that all data/columns need to depend on the
  whole/entire primary key

That's incorrect. A relation R is in second normal if every non-prime attribute of R is fully dependent on each candidate key of R. In the case of studentid -> username and username -> studentid both username and studentid are prime attributes (attributes that are included in a candidate key). So those dependencies are not a violation of 2NF and Student_subject does satisfy 2NF with respect to those dependencies. As already pointed out, Normal Forms apply to relations, not to FDs.


You could create your own aggregate to append arrays:
CREATE AGGREGATE array_append_agg(anyarray) (
   SFUNC = pg_catalog.array_cat,
   STYPE = pg_catalog.anyarray
);

Then use that aggregate function in your outer query.


Typically caused by:

Bug 18921384 - DBA_TABLESPACE_USAGE_METRICS VIEW DOES NOT RETURN ROWS WHEN DB_FILES LIMIT IS REACHED

To verify:

show parameter db_files

select count(*) from v$datafile;

Then increase db_files if needed or upgrade to a newer version where it is fixed.

Another possible cause:

Bug 16317020 - Records missing from dba_tablespace_usage_metrics (Doc ID 16317020.8)

Also fixed in newer version, and one-off patch is also available.


NOTE : This solution is in SQL SERVER

You can do this using while loop.

Here is the query that will do

DECLARE @cnt INT = 1

WHILE @cnt > 0
BEGIN
   update TOP (1) account
  set account.proxy_id = (
      select top 1 proxy.id
      from proxy order by (
        select count(account.id)
        from account
        where account.proxy_id = proxy.id and account.enabled = 1
      )
  ) where account.proxy_id is null
   SET @cnt = (select count(id) from account where proxy_id is null and enabled = 1)
END;

In this query first we count all account for proxy.id in proxy table and select the proxy.id for lowest count.

Then we set that id to user who is enabled and does not have any proxy_id

UPDATE :

In POSTGRESQL you can achieve this by

DO $$DECLARE r int;
BEGIN
r=1;
    while r > 0
    LOOP
        update account set proxy_id = (
            select proxy.id from proxy order by (
                select count(account.id)
                from account
                where account.proxy_id = proxy.id and account.enabled = '1'
            ) limit 1
        ) where id = (
                select id
                from account
                where proxy_id is null and enabled ='1'
                order by id limit 1
        );
        r = (select count(id) from account where proxy_id is null and enabled ='1');
    END LOOP;
END$$;


SQL Server Replication Alternative Software

It's highly unlikely you'll find a better merge replication solution than what's built-in.

Merge replication in particular is hard to implement.

I would suggest that if you're having issues with the current setup breaking, take it up with Microsoft. They should be very motivated to help you find the root cause of the problem so their customers are happy, and not looking for other solutions like you're asking for here.


Fetching Data from joining table for any one from condition satisfied

Try it:

SELECT *
FROM myactivities_4
LEFT JOIN claims2 ON claims2.activityid = myactivities_4.activityid
WHERE myactivities_4.activityid NOT IN (
    SELECT activityid
    FROM claims2
    WHERE claim_status NOT IN ('Submitted', 'Approved','Rejected')
)


what will happen if MongoDB primary host is down and secondary is out of sync. will we lose transaction?

When you managed to get that A running, it will roll-back all those transactions what are not on B, because now B have newer transactions and it's Primary. That you get when you "force" Secondary to be primary against replica sets will.


Try this:

select osd.UserId, osd.OfferId, ou.OfferName
  from dbo.offersentdetail as osd
    inner join (
      select OfferId = Offer1Id, OfferName, OfferExpiryDate
        from dbo.[Offer1]
      union all
      select OfferId = Offer2Id, OfferName, OfferExpiryDate
        from dbo.[Offer2]
        ) as ou
    on osd.OfferId = ou.OfferId


Insert Missing Date row for each group With & W/O using CTE

To get each day in the range:

DECLARE @StartDate date = '20180924', -- stay away from m/d/y!
        @EndDate   date = '20180928';

;WITH n(n) AS
(
  SELECT 0 UNION ALL SELECT n + 1 FROM n
  WHERE n < DATEDIFF(DAY, @StartDate, @EndDate)
), d(d) AS
(
  SELECT DATEADD(DAY, n, @StartDate) FROM n
)
SELECT d FROM d
OPTION (MAXRECURSION 0);

This returns:

d
----------
2018-09-24
2018-09-25
2018-09-26
2018-09-27
2018-09-28

Now to produce a row for every possible meeting room on every date, you cross join against the distinct set of meeting rooms present within the range, and then left outer join that set against your data (I'm not sure why you are inserting your date range into a permanent table, this isn't necessary and it kills concurrency).

DECLARE @StartDate date = '20180924',
        @EndDate   date = '20180928';

;WITH n(n) AS
(
  SELECT 0 UNION ALL SELECT n + 1 FROM n
  WHERE n < DATEDIFF(DAY, @StartDate, @EndDate)
), d(d) AS
(
  SELECT DATEADD(DAY, n, @StartDate) FROM n
),
roomdata AS
(
  SELECT MeetRoom, StartDT, status, minsused
    FROM dbo.tblBI
    WHERE StartDT >= @StartDate
    AND StartDT < DATEADD(DAY, 1, @EndDate)
),
rooms AS
(
  SELECT DISTINCT MeetRoom FROM roomdata
)
SELECT r.MeetRoom, d.d, status = COALESCE(rd.status, 'FC'),
  minsused = COALESCE(rd.minsused, 1440)
FROM d
CROSS JOIN rooms AS r
LEFT OUTER JOIN roomdata AS rd
ON r.MeetRoom = rd.MeetRoom
AND rd.StartDT >= d.d
AND rd.StartDT < DATEADD(DAY, 1, d.d)
ORDER BY r.MeetRoom, d.d
OPTION (MAXRECURSION 0);

Currently this returns the rows that exist and a row for each combination that doesn't exist. If what you want is just the set of rows that don't exist, then just add a WHERE clause before the ORDER BY:

...
AND rd.StartDT < DATEADD(DAY, 1, d.d)
WHERE rd.status IS NULL
ORDER BY r.MeetRoom, d.d;


Max Workers available and yet pending task high

Your numbers don't totally make sense.  In particular, the value for "workersCreated" should not be higher than the value for "maxWorkers."  Here's an example of a normal-looking entry:

<queryProcessing maxWorkers="576"
                 workersCreated="86"
                 workersIdle="46"
                 tasksCompletedWithinInterval="950"
                 pendingTasks="0"
                 oldestPendingTaskWaitingTime="0"
                 hasUnresolvableDeadlockOccurred="0"
                 hasDeadlockedSchedulersOccurred="0"
                 trackingNonYieldingScheduler="0x0">

In your case, you've hit the max worker threads limit (as evidenced by the THREADPOOL waits in sys.dm_os_wait_stats).  Investigating those is a pretty big task, and probably tough to cover in this Q&A format.  This is what's causing tasks to be pending though.  Generally, you'll need to

Fix blocking issues (if that's what's causing threads to tie up schedulers)
Reduce parallelism (by reducing MAXDOP or CTFP) - this is kind of a band-aid fix
Change the app to run less queries at a time

Based on other questions you've asked on the site, I imagine that blocking and long-running transactions are the reason you're hitting this.  You'll need to continue to work with the vendor.

By the way, you can double check that the "max worker threads" setting hasn't been modified by running:

EXEC sp_configure @configname = 'max worker threads';

It should show the run_value as 0 if it hasn't been changed.


First, let's note that in this case there are three keys, not only one:

AB
BD
BC

This means that all the attributes are primes, so the relation is by definition already in Third Normal Form.

For the BCNF, you have described the “analysis algorithm”, which is the algorithm presented in every good book on databases. But, with this example, you have also discovered that in normalizing, you can have different results, that can depend for instance on the order in which the dependencies that violates the normal form are chosen (not in this case, but possible in other cases with this same algorithm), or by using a different algorithm (there are others, not interesting from a practical point of view).

In your example, the second decomposition, which is in BCNF as you correctly stated, cannot be produced by the analysis algorithm: there is nothing wrong about it, in general there is no guarantee that this algorithm will produce the “best” decomposition (for instance a decomposition with a minimum number of relations), and, if this is an exercise, you have done it correctly!

What instead is interesting to note, also from a practical point of view, is if the decomposition satisfies all the important properties, i.e. if it preserves the dependencies of the original schema (since the decomposition produced by the algorithm is instead guarantee to be always a lossless decomposition).

And you can see that both the decompositions fail under this aspect. The decomposition in R2(B, C), R3(C, D), R4(D, A) does not preserve the dependency A B → C, while the decomposition R1(A, B, C), R2(C, D) does not preserve the dependency D → A. This means that in practice both of them reduce the redundancies and other anomalies of the data, at the expense of losing an important contraint over them.

So, knowing that the relation is already in 3NF, one could be satisfied by this result and leave the relation in this form. This means the preservation of the dependencies, together with a limited amount of redundancy, which in practice can be tolerated.


normal Linux user for oracle database dumps

You can create a custom directory object for this user's use. Don't use the system-level directory created by default. Just make sure that both the user and Oracle will have read/write permissions to it. Also make sure the user has execute permissions on $ORACLE_HOME/bin, and a database account with read/write access to the directory object and whatever you want to import/export.
Be very careful with the individual permissions you grant to the database user. If this isn't just for an automated interface, but for an actual non-privileged user/person, then I would push back hard, as there are serious security implications:

In general, unprivileged users should not have login access to the database server such as is required to use data pump in the first place. Only sys admins and DBAs should be able to log directly in to the database server.
Using datapump to import objects or data for other schemas than the one actually running the import requires elevated database privileges that normal users shouldn't have. The ability to perform full imports, for example, allows the executing user to create other users, modify system settings, and generally make a hash out of everything if they don't know what they're doing.


I have run truncate table but it is still showing table occupying space?

Tables with Engine=InnoDB take a minimum of one block -- 16KB.

mysql> CREATE TABLE minsz (x int) ENGINE=InnoDB;
Query OK, 0 rows affected (0.17 sec)

mysql> SHOW TABLE STATUS LIKE 'minsz'\G
*************************** 1. row ***************************
           Name: minsz
         Engine: InnoDB
        Version: 10
     Row_format: Compact
           Rows: 0
 Avg_row_length: 0
    Data_length: 16384   <-- Brand new table, still empty, yet 16KB
Max_data_length: 0
   Index_length: 0
...

If I had added an secondary index (not PRIMARY KEY), Index_length would also be one block.  (Heidi would probably show 32KiB.)

I might add a hundred rows before the table and/or its index expands beyond 16KB.


According to your table structure, you only have a PRIMARY KEY on build_id.

Thus, there is no index that can be utilized to solve this query. I suspect you are very new to indexes; having an index on some column doesn't mean all queries will run faster. You need an index that matches you query.

For example, in your case, an index on user_id or an index on country_id or an index on share or an index on mode would work; Indexes on combinations of the above, like INDEX (user_id, country_id) would do well, too.

Which should you choose? One that would significantly limit the number of rows to "search by hand" (so to speak), and one that would do well for your other common queries.

My recommendation to you is to study how indexes work in relational databases.


Application Continuity on Single instance

Application Continuity is not limited to RAC. It works with Data Guard as well. For example an in-flight transaction can be replayed after a switchover on the new primary (former standby). With a single instance primary and standby, this requires the Active Data Guard option to be licensed.

Application Continuity

  Application Continuity is supported for Oracle Data Guard switchovers
  to physical standby databases. It is also supported for fast-start
  failover to physical standbys in maximum availability data protection
  mode. Note that primary and standby databases must be licensed for
  Oracle RAC or Oracle Active Data Guard in order to use Application
  Continuity.


Use a view to join tables or a stored procedure for a better execution plan?

Which option from above will perform better?

Best case, both will produce exactly the same execution plan, with the same runtime performance.

This can require some careful design and some fairly advanced skills, as Rob Farley mentions in his answer. Rob also has a blog post describing the core issue, and it is also discussed in one of his chapters from the SQL Server MVP Deep Dives Volume 1 book.

  Which option from above is considered best practice?

This is likely more a matter of opinion that an established best practice either way, but experienced database professionals tend to limit their use of views to scenarios where they offer real and significant benefits. The more complex a view becomes, (especially if views start to become stacked or layered) the harder it is to avoid undesirable side-effects and the more likely it becomes to encounter optimizer limitations.

The query may be easier to read and maintain if it references a view, but "separation of concerns" is not something you will find database professionals discussing when choosing between a view or writing the underlying query out in a stored procedure. There are many established practices and patterns in programming that do not translate well to database work.

Always bear in mind that a (non-indexed/materialized) view is simply a stored query definition. It is expanded into the referencing query exactly as if copy-and-pasted in place manually before query compilation and optimization begins.

You did not ask for a comprehensive critique of the benefits and drawbacks of using views (which would likely be too broad a question anyway) so I will not attempt to offer one. My final word of caution is that even simple views that optimize well today (perhaps making use of Rob's idea) can easily be modified (often in an innocuous-looking way) so that queries referencing them suddenly encounter an optimizer limitation and a performance cliff.


Need to delete consecutive, unchanged records


No matter what you choose, you should pick a convention and stick to it.

Here are my recommendations

BOOL/BOOLEAN

If the data values are truly binary, use the BOOL/BOOLEAN data type.

CHAR/VARCHAR

YES/NO works well when you have lazy UI developers that want to show the result with out translating it.  The single character value ( Y/N ) works well also.

The downside: Multinational applications will still have to translate the text.  (eg はい/いいえ )

NUMBER(1)

For databases that do not support bool/boolean I recommend using a NUMBER(1).  This way, you can tell the UI developers that they will need to translate it into to the proper language.  Make sure you document what 1 and 0 mean.

enum

I do like the idea of using an enum to store the values.  This would allow you to store a greater range (eg somewhatReflective, mostlyReflective)

Proper datatype

I'm assuming the question is about data types whose values can be represented as binary.  (eg true/false)  This section is here to remind readers:  Always pick the proper data type for your column (eg DATE for dates, etc.)

The albedo of an object (relfectiveness) is measured as a dimensionless NUMBER whose value is BETWEEN 0 AND 1.  I would not use a BOOLEAN data type for the reflective column.


Importing hundreds of CSV's into postgres (non-sequential naming)

If the CSV files are in the same directory, a global import can be scripted from  Ubuntu with a bash script like:
cd /path/to/csv-directory
for file in *.csv
do
  psql (add connection options here) << EOF
\copy MyTableName from $file (format csv)
EOF
done


Relation does not exist while scanning schema

Use relid instead of relname:

SELECT relname                                 AS tablename
     , to_char(seq_scan  , '999,999,999,999')  AS total_seqscan
     , to_char(idx_scan  , '999,999,999,999')  AS total_indexscan
     , to_char(n_live_tup, '999,999,999,999')  AS tablerows
     , pg_size_pretty(pg_relation_size(relid)) AS tablesize
FROM   pg_stat_all_tables
WHERE  schemaname = 'loan'
AND    50 * seq_scan > COALESCE(idx_scan, 0) -- more then 2%
AND    n_live_tup > 10000
AND    pg_relation_size(relid) > 5000000;

Why?

Assuming you do not have the schema loan in your search_path, Postgres is unable to resolve the cast mv_transaction_view::regclass - looking up unqualified tablenames only in the schemas listed in the current search_path. Hence the error:

 ERROR: relation "mv_transaction_view" does not exist

Materialized views are also listed among "tables" in the view pg_stat_all_tables, that's not the problem.

The missing schema in the search_path is also not the problem. Just the messenger in a manner of speaking. The problem is a bug in your query. Imagine a table loan.tbl and a search_path of public, loan. Your query would just work fine. Or would it?
Now imagine an innocent (or malevolent) user creating a table public.tbl. Then you run your query again ...

Using relid instead is the safe solution. You don't even need to cast since that's the OID of a table, exactly what pg_relation_size() expects. (The cast oid -> regclass is implicit.)

Details:

How to check if a table exists in a given schema
How does the search_path influence identifier resolution and the “current schema”

Also note COALESCE(idx_scan, 0):
idx_scan can be NULL, and you wouldn't wanna miss tables with all sequential scans.

The manual on pg_stat_all_tables.


You need to extract the value as text which is done using the ->> operator (-> returns a json value).
SELECT *
FROM job_request_table p
  cross join json_array_elements(p.data_meta) as d(element)
where d.element ->> 'data_type' = 'Insurance';

However, given your sample data, this would result in the error "cannot extract elements from an object" as the content of data_meta is not an array.
If you want to test if a JSON value contains a specific key/value pair, use @> - but that can only be used with jsonb, so you need to cast the column:
SELECT *
FROM job_request_table p
where data_meta::jsonb @> '{"data_type": "Insurance"}';

You can use this also to check inside the array in source_data (but again a cast to jsonb is neeed):
where source_data::jsonb @> '[{"vendor": "ABC"}]'

In the long run, I would recommend to change both columns to jsonb


SQL Server 2008 R2 Database - Why is the database not using free space before growing again?

A couple of things worth noting here.

First, the database will grow whenever it needs the space to complete a transaction. I wrote about this here. If you performing maintenance on your database (say, rebuilding indexes) and you hit a large enough table then you could see a growth event happen as a result, even though it appears you have a large amount of free space.

Second, you can examine you default trace files in order to find out when your autogrowth events are happening. Often times you will find that the timing of the growth events coincides with maintenance, or perhaps a batch load of some kind.

HTH


Full backup of Simple recovery model - how much log is backed up if there is an open transaction

In a FULL backup, how much LOG information is recorded if there is an open transaction?

Answer to your question is in This Blog by Paul Randal

  When the FULL backup is restored, does it ROLLBACK the actions by the OPEN transaction?

Yep if the transaction has not committed when the full backup completes it will be rolled back when the backup is restored, simply, because SQL Server does not have idea about "state of transaction" when the backup finished before the transaction committed/completed. Please read below for more details

Understanding Backups in SQL Server

  If so, why does FULL backup even bother to backup the LOG for an OPEN transaction when it is still open after the backup is finished (on a SIMPLE recovery model though).

As stated above because it does not have any idea about the state of transaction, like whether it will commit or rollback and since transactions in SQL Server are first written in transaction log before changes are made to pages the backup so it does its job of backing up enough transaction logs so that if transaction commits it can bring database to consistent state after backup is restored and if transaction does not commits before backup is finished the backups takes it as uncommitted and when it will be restored their would be no information about changed made by that transaction in the restored database.

Read The First Myth pointed out by Paul Randal

  If I kill the spid from SQL Server, does it need more LOG space to ROLLBACK? We are already out of space here !!

Yes it does needs space in transaction log file to rollback. Rollback is mostly single threaded and undoes what the transaction has done.

Rollback: What happens when you kill a session


How can I extract the perfmon counter "Memory: Pages/Sec" using t-sql?

Following @Srutzky's example of just looking at page faults/sec (not pages/sec), you can do this:

DECLARE @before BIGINT, @after BIGINT;

SELECT @before = page_fault_count FROM sys.dm_os_process_memory;

WAITFOR DELAY '00:00:10';

SELECT @after = page_fault_count FROM sys.dm_os_process_memory;

SELECT PageFaultsPerSec = (1.0*@after - @before)/10.0;

It is likely that you can just have Ignite poll for the following metric:

SELECT page_fault_count FROM sys.dm_os_process_memory

And then set up alerts based on thresholds or deltas. But I still recommend you contact them to find out how to best implement a custom metric based on a column from an arbitrary DMV, which might be different from the perf counters DMV.


Convert string to number from xml at server side

cast relies on your session's NLS settings to convert between datatypes.

You should use to_number with an explicit number format model, something like:

SELECT to_number(nvl(trim(replace(xmltype(t.sce_msg).extract('//RoofInsulationCSV/Total/text()', 'xmlns="http://INT010.GPA.Schemas.External.GPA_AllFile').getstringval(),'.',',')),0), '999999999,99')
FROM        ta_main.serv_cons_event t
INNER JOIN  ta_main.event e
      ON    t.sce_evt_id_s = e.evt_id_s
WHERE       e.evt_typ_id_c IN ('INV', 'ACC');

You'll have to play around with the format model to fit your expected data.


Convert rows to columns in SSIS. Unpivot?

Yes the Unpivot Transformation should work, as long as the number of input columns does not vary.

Looking at your example, it looks like [Column 2] (e.g. WAREHOUSE ID) should be set as the Pivot Key Value.  You can ignore any output columns that you dont want to output e.g. the Pivot Key Value Column Name.


Partition : understanding files and file groups

There shouldn't be a performance gain by the third option over the second. When you think about it, it's almost the same as having all your user objects in the PRIMARY filegroup. One performance advantage you can achieve with the second option is to flag the older historical filegroups as read-only once you've confirm no additional data modifications are expected, indexes are rebuilt and statistics updated. Once read-only has been implemented, locking doesn't become an issue when querying data in those filegroups.


The roles in Oracle Database have no owner as well as directories have no owner.

When the user with CREATE ROLE or SYSDBAprivilege creates the role, he or she is automatically assigned that role WITH ADMIN OPTION which means that the user can subsequently grant that role to other users even if the privilege CREATE ROLE is revoked from the user.

You can see what roles are assigned to which users querying DBA_ROLE_PRIVS view, or USER_ROLE_PRIVS if you want to know what roles are assigned to the current user.

Also, a user cannot create a role  if the role with the same name already exists. However you can audit role creation and granting. You can do that with AUDIT ROLE statement (if it was for some reason disabled with NOAUDIT ROLE), and then querying DBA_AUDIT_TRAIL view.


There is this KB article, which lists the required updates you need to install https://support.microsoft.com/en-us/kb/3135244 , which also lists which updates are needed on the client machines trying to connect to a TLS 1.2 enabled SQL Server.

Also, read http://blogs.sqlsentry.com/aaronbertrand/tls-1-2-support-read-first/ , which lists known problems and caveats. It states

  It seems straightforward, but as of today, not all builds will enable you to rush out and convert to TLS 1.2 exclusively


Exporting data from Microsoft Access table file subsets to plain text

Microsoft Access does not know how you want the files exported when you use the standard "export" text function.

Here is a trivial piece of code that you can add to a Module, and run from a Command Button on a form, or directly from the Immediate Window:

Public Sub ExportTables(ByVal DestinationPath As String)

    Dim a As Long

    For a = 0 To CurrentDb.TableDefs.Count - 1
        If Not (CurrentDb.TableDefs(a).Name Like "MSys*") Then
            DoCmd.TransferText AcTextTransferType.acExportDelim _
                , _
                , CurrentDb.TableDefs(a).Name, DestinationPath & "\" & CurrentDb.TableDefs(a).Name & ".csv" _
                , True
        End If
    Next a

End Sub

Add a Module to your Access database, and paste in the code above.  Press "CTRL-G", enter ExportTables "C:\temp", then press Enter - this will export all tables in the database into C:\TEMP in comma-separated-values format (.CSV) ready for import into Excel or other databases.


Amazon RDS - Can't connect to MySQL database server?

1) Make sure that RDS instance has access to ec2 instance from where you are trying to connect

2) From ec2 instance connect to Amazon RDS instance using telnet
$telnet

If telnet fails means connection to MySQL port is not possible.


Can converting MyISAM to InnoDB cause problems?

Yes, it can cause a number of troubles.  However, troubles are rare.

All the 'troubles' I know of are listed here.

(Do not change tables in the databases mysql or information_schema or performance_schema; that will cause serious trouble.)


How to customize constraint violation messages?

This has been asked in Postgres Forum: Custom Constraint Violation Errors

and after some discussion:

  ...
  So am I got that right, that If I want to have eg custom error messages
  for a foreign
  key violation, I need to replace all fkeys by trigger functions?!

  Michael Musenbrock

answered there:

    The short answer is, while it is possible to custom make your
    own constraint triggers that emulate foreign keys, don't do it.  This
    would be a maintenance nightmare.

    I'd be more maintainable to catch these errors in your client
    application.  Here you would reword these error messages according the
    business rules of your client application.

    Richard Broersma Jr.

with what I think would work best:

      It's not hard to create:

CONSTRAINT some_constraint_name FOREIGN KEY col REFERENCES blah(id)

      ... then in the app, match "some_constraint_name" and map it to a suitable error. That's what I do and it works very well for all constraint types, not just foreign key constraints.

      --
      Craig Ringer


How to improve performance to 12million rows table?

I think I have been using that table.  It is clunky when you want to see states/provinces and other things like that.
Sure break out a country if that is all you need.  But don't plan on breaking out all ~250 countries into separate tables (plus continents, etc).
VIEWs are not performance enhancers.  They can hide the clumsy nature a table like that one.  (Especially due to the fcode checks.)
This may help:
INDEX(fcode, country_code)

WHERE feature_code LIKE 'PCL%' AND ...
WHERE feature_code = 'ADM1' AND country_code = 'ES'

If you would care to provide the desired queries (not the views) and the desired table(s), I may be able to provide more suggestions.
Lat/lng searches
Lat/lng needs more work than simply a composite index.  Suggest you start with a "bounding box" and these two composite indexes:
INDEX(lat, lng),
INDEX(lng, lat)

If such searches are not fast enough, then look at more complex methods in http://mysql.rjweb.org/doc.php/find_nearest_in_mysql