Designing a database structure for companies and stock owners I'm working on a personal project where I want to make a database that holds the information for every owner of every public company. Say for example "Sears INC" and the program would get the information for every owner in "Sears INC". That is the idea. But I am having a really hard time trying to structure this database. I only have limited experience and knowledge about this subject so any guidance would be greatly appreciated.

Now I was first thinking about making a table called companies, and give them all unique ID's and make a table for every company. Then inside those tables would be all the owners with their unique ID. That would link to their information. I have tried to visualize this here:

Now I know that creating a table for every company would be tedious but I cant think of any other way of doing this. As you all know each company might have the same owner and therefore it would make sense of using Owner_ID to identify each owner.

I have all the other data in a CVS file that I could easily import in PostgreSQL after I have structured the database.

Any help would be greatly appreciated.

TL:DR; Creating a database of stock owners that will be searchable by company name, and need help to structure the database for maximum efficacy.

A person can only have one phone and one address. Same goes for the company, because I have made a script that finds the phone based on this information. The way I differentiate between knowing if a entity is a company or person is by using a column called F_Org which will be larger than 4 digits for companies. This is all handled by the script.

One of the topics that I commonly touch in my database design answers is that before (a) thinking about the technical aspects of a relational database —e.g., the declaration of tables, columns, etc.— it is highly recommendable to first (b) define all the characteristics of the business context of interest with precision. This implies identifying the entity types, their properties and every significant association that exists among them.
The group of definitions about said elements is commonly referred to as as business rules, which make up the applicable conceptual model.
Also, I esteem that there are other important subjects about (1) data management and (2) relational design and manipulation that (3) may help to clarify some aspects you brought up in the question and via comments as well. I will integrate all these points as the answer goes progressing.
Conceptual level
Let us start reevaluating the relevant conceptual level of abstraction. In this regard, it is important to formulate a series of statements that describe the busines environment. So, in your specific case, keeping the business rules deliberately as simple as possible:

A Company is primarily identified by its Id
A Company is alternately identified by its Name
A Company is established on its FoundingDate
A Person is primarily identified by his or her Id
A Person is alternately identified by the combination of his or her FirstName, LastName, GenderCode, BirthDate and BirthPlace
A Person keeps exactly-one Address
A Person uses exactly-one PhoneNumber
A Company is owned by one-to-many People
A Person owns zero-one-or-many Companies

Illustrative IDEF1X model
Then based on the business rules formulated above, one can create a deliberately and relatively simple IDEF1Xa model like the one shown in Figure 1, in order to have a graphical device that consolidates most of the significant features in a single resource:

a Integration Definition for Information Modeling (IDEF1X) is a highly recommendable data modeling technique that was established as a standard in december 1993 by the United States National Institute of Standards and Technology (NIST). It is solidly based on (a) some of the early theoretical works authored by the originator of the relational model, i.e., Dr. E. F. Codd; on (b) the Entity-Relationship view, developed by Dr. P. P. Chen; and also on (c) the Logical Database Design Technique, created by Robert G. Brown.
As demonstrated, in an IDEF1X model we can start including technical considerations like the indications of properties or attributes that must be constrained via PRIMARY, ALTERNATE and FOREIGN KEY definitions (for brevity: PK, AK and FK, respectively) at the logical level.
The last two of the business rules previously discussed show that there is a many-to-many (M:N) association or relationship between the entity types Company and Person, which reveals the existence of an associative entity type that I called CompanyOwner (which might very well be denominated CompanyShareHolder or something that fitts better the terminology used in the business domain).
One of the properties of paticular importance in the present modeling excercise is the one I named StockShareQuantity (portraying a sum of Shares), because it comes about exclusively in the context of a CompanyOwner association; in this way, it does not belong to a Person nor to a Company, but to the connection that may arise between these two separate entity types.
As specified, each CompanyOwner occurrence or instance is identified by the combination of its CompanyId and OwnerId values, so these properties are highlighted as a composite PK in the entity type depiction. The CompanyOwner.CompanyId property is distinguished with a FK that points to Company.CompanyId, while CompanyOwner.OwnerId is noted with a FK that makes a reference to Person.PersonId.
Aiming to portray a slighlty more elaborate Person entity type, I decomposed Person.Name into FirstName and LastName, and I included the columns GenderCode, BirthDate and BirthPlace. Together, the combined values of all those properties are typically used to identify a Person in certain business scenarios, but you may simply be interested in keeping track of the Person.FullName, if that meets the requirements of your data usage, so you do not have to follow the same approach concerning the particular database under discussion.
Expository logical SQL-DDL structure
Subsequently, it is relatively more easy to declare a logical structure by means of the data definition language supplied by the database management system, in this case PostgreSQL, just like exemplified below:
-- You have to determine which are the most fitting
-- data types and sizes for all the table columns
-- depending on the business context characteristics.

-- Also, you should make accurate tests to define the
-- most convenient physical implementation settings; e.g.,
-- a good INDEXing strategy based on query tendencies.

-- As one would expect, you are free to make use of
-- your preferred (or required) naming conventions.

CREATE TABLE Company ( -- Stands for an independent entity type
    CompanyId       INT       NOT NULL, -- You may like to set it with the SERIAL type to retain system-assigned and system-generated surrogate values.
    Name            TEXT      NOT NULL,
    FoundingDate    DATE      NOT NULL,
    Etcetera        TEXT      NOT NULL,
    CreatedDateTime TIMESTAMP NOT NULL DEFAULT NOW(), -- You may also configure it so that the DEFAULT value is set by the CURRENT_TIMESTAMP function, if appropriate.
    --
    CONSTRAINT Company_PK PRIMARY KEY (CompanyId),
    CONSTRAINT Company_AK UNIQUE      (Name) -- Single-colum ALTERNATE KEY.
);

CREATE TABLE Person ( -- Denotes an independent entity type.
    PersonId        INT       NOT NULL, -- You may like to set it with the SERIAL type to retain system-assigned and system-generated surrogate values.
    FirstName       TEXT      NOT NULL,
    LastName        TEXT      NOT NULL,
    GenderCode      TEXT      NOT NULL,
    BirthDate       DATE      NOT NULL,
    BirthPlace      TEXT      NOT NULL,
    Address         TEXT      NOT NULL,
    PhoneNumber     TEXT      NOT NULL,
    CreatedDateTime TIMESTAMP NOT NULL DEFAULT NOW(), -- You may also configure it so that the DEFAULT value is set by the CURRENT_TIMESTAMP function, if appropriate.
    --
    CONSTRAINT Person_PK PRIMARY KEY (PersonId),
    CONSTRAINT Person_AK UNIQUE      ( -- Composite ALTERNATE KEY.
        FirstName,
        LastName,
        GenderCode,
        BirthDate,
        BirthPlace
    )
);

CREATE TABLE CompanyOwner ( -- Represents an associative entity type or M:N association. Attaching an extra column to hold system-generated and system-assigned surrogate values to this table is superfluous.
    CompanyId          INT       NOT NULL,
    OwnerId            INT       NOT NULL,
    StockShareQuantity INT       NOT NULL,
    Etcetera           TEXT      NOT NULL,
    CreatedDateTime    TIMESTAMP NOT NULL DEFAULT NOW(), -- You may also configure it so that the DEFAULT value is set by the CURRENT_TIMESTAMP function, if appropriate.
    --
    CONSTRAINT CompanyOwner_PK          PRIMARY KEY (CompanyId, OwnerId), -- Composite PRIMARY KEY.
    CONSTRAINT CompanyOwnerToCompany_FK FOREIGN KEY (CompanyId)
        REFERENCES Company (CompanyId),
    CONSTRAINT CompanyOwnerToPerson_FK  FOREIGN KEY (OwnerId)
        REFERENCES Person (PersonId),
    CONSTRAINT StockShareQtyIsValid_CK  CHECK       (StockShareQuantity >= 0) -- Appears to be required.
);

As expounded, in this deliberately and relatively simple logical structureb (which is very similar to the one portrayed in your diagram although with a few important differences):

each base table denotes an individual entity type, which prevents ambiguities;
each column stands for a single property of the respective entity type;
a specific data type is fixed for each column in order to ensure that all the values it retains belong to a particular set (that you will have to adapt to adapt to your exact necessities, choosing the most fitting types from the options provided by PostgreSQL), be it INT, TIMESTAMP, TEXT, etc.; and
multiple constraintsc, d are set up (declaratively) in order to guarantee that the rows retained in all the tables comply with the rules determined in the conceptual model.

b I have uploaded a db<>fiddle and a SQL Fiddle, running on PostgreSQL 9.6, that contain the DDL structure and constraints I defined along with sample data so that you can test it “in action”.
c Since (i) the structure of the data —i.e. the tables, columns and types— and (ii) the constraints —e.g., PKs, FKs and CHECKs— imposed on such a structure to ensure that it accepts only valid data are (iii) two diferent —but related— factors of the configuration of a relational database, I suggest (iv) placing the structure declarations apart from the constraint declarations —though the SQL language and the dialect supplied by PostgreSQL permit declaring “in-line” column constraints, if you wish to opt for that option—. In fact, it would be even better to move the constraint definitions outside of the CREATE TABLE … (…); statements, so this point deserves thorough evaluation.
dAn exception that I make regarding the separation of structure from constraints is that I in effect fix NOT NULL in “in-line” column declarations because it is a special kind of constraint. To keep things as brief as possible, permitting NULL marks is an approach  —considered controversial on theoretical grounds— introduced by the SQL language designers to try manage the Missing Information Problem, which is a rather broad subject. NULL marks, in turn, imply dealing with another problem known as Three-Valued Logic. According to the relational theory, a table with one or more columns that enclose NULL marks (1) does not represent a mathematical relation —said marks are indicators about the absence of a value, then they are not domain values—, therefore (2) such a table is not going to “behave” as a relation when it is operated upon. Consequently, while having columns maintaining NULL markers is a possibility, I recommend that you study trustworthy material about these topics so that you can make an informed decision knowing all the implications and methods with respect to the management of Missing Data.
The Person.Address Column
In case that you are interested in manipulating certain encoded parts of the Address column, let us say PostCode, you might like to evaluate decomposing it in several columns and moving the pertinent information to a separate table that, perhaps, would be connected with the Person table via a column with a FK constraint.
Declaring a table for each Company?
You brought up the following thoughts in comments:

As I have stated the design will change because […] there will be multiple companies and people can own multiple companies and therefore show up twice. My original question was how to design this. I thought about making a table for each company which I think would be my last resort but I still have no other guidance or tips on how to do this.

No, you should not create a table for each Company, because that would be quite suboptimal.
In that respect, it can be said that every row kept in each of the previously presented base tables is an assertion about a certain conceptual-level entity that belongs to a certain type, i.e., either a Company or a Person or a CompanyOwner.
Hence, a row about a given Company is held only once in the Company table and a row about a given Person is maintained only once in the Person table. Then, since a Person can own zero, one or more Companies, each row about an exact connection that a Person has with respect to an individual Company is enclosed only once in the CompanyOwner table.
A row in the CompanyOwner table does not contain the whole information about a Person, it only contains (1) one value in the OwnerId column —constrained as a FK— that makes a reference to (2) one value retained in the Person.PersonId column —constrained as the PK—. A certain CompanyOwner.OwnerId value may be repeated multiple times, and so may happen with a CompanyOwner.CompanyId value too, but that is not problematic. As exemplified, the possibility of repeating the same combination of (CompanyId, OwnerId) values is prevented by way of the CompanyOwner composite PK definition.
Derivable information
A set of base tables is definitely not a fixed structure it when it comes to a relational database because, apart from being susceptible to extensions and adaptations, the base tables help in deriving new tables that are not configured at design time.
Let us suposse that you have populated your database tables with the sample data via the following INSERT operations:
INSERT INTO Company
    (CompanyId, Name, FoundingDate, Etcetera)
VALUES
    (1748, 'Database Modeling Inc.', '1985-06-30', 'Foo'),
    (1750, 'Application Programming Co.', '1987-10-14', 'Bar');

INSERT INTO Person
    (PersonId, FirstName, LastName, BirthDate, BirthPlace, GenderCode, Address, PhoneNumber)
VALUES
    (1, 'Edgar', 'Codd', '1923-08-19', 'Fortuneswell, UK', 'M', 'IBM Research Laboratory K01/282, 5600 Cottle Road, San Jose, CA, USA', '01-800-17-50-17-50'),
    (2, 'Alan', 'Turing', '1912-06-23', 'Maida Vale, UK','M', 'National Physical Laboratory, Hampton Road, Teddington, TW11 0LW, England', '01-800-17-48-17-48'),
    (3, 'Grace', 'Hopper', '1906-12-09', 'New York City, USA', 'F', 'Navy’s Office of Information Systems Planning, USA.', '01-800-17-50-17-50'),
    (4, 'Diego', 'Velázquez', '1599-06-06', 'Seville, Spain', 'M', 'Palacio Real, Madrid, Spain', '01-800-17-50-17-50'),
    (5, 'Michelangelo', 'Buonarroti', '1475-03-06', 'Caprese, Italy', 'M', 'Sistine Chapel, Vatican City State', '01-800-17-50-17-50');

INSERT INTO CompanyOwner
    (CompanyId, OwnerId, StockShareQuantity, Etcetera)
VALUES
    (1748, 1, 2500, 'U'),
    (1750, 1, 2500, 'V'),
    (1750, 2, 8000, 'W'),
    (1750, 3, 3580, 'X'),
    (1748, 4, 12899, 'Y'),
    (1750, 5, 12899, 'Z');

After that, if you want to, e.g., produce a table that comprises data about the Owners of all the Companies, you can declare a VIEW like the following one:
CREATE VIEW CompanyAndOwner AS
    SELECT C.CompanyId,
           C.Name AS CompanyName,
           P.PersonId,
           P.FirstName,
           P.LastName,
           P.BirthDate,
           P.BirthPlace,
           P.GenderCode,
           P.Address,
           P.PhoneNumber,
          CO.StockShareQuantity,
           P.CreatedDateTime
        FROM Person P
        JOIN CompanyOwner CO
          ON CO.OwnerId = P.PersonId
        JOIN Company C
          ON C.CompanyId = CO.CompanyId;

Then you can express successive operationsc that SELECT directly FROM that VIEW; e.g.:
SELECT *
  FROM CompanyAndOwner
 WHERE CompanyId = 1750;

and
SELECT CompanyName,
       FirstName AS OwnerFirstName,
       LastName  AS OwnerLastName,
       StockShareQuantity
  FROM CompanyAndOwner
 WHERE CompanyId = 1750;

and
SELECT *
  FROM CompanyAndOwner
 WHERE CompanyId = 1748;

and
SELECT CompanyName,
       FirstName AS OwnerFirstName,
       LastName  AS OwnerLastName,
       StockShareQuantity
  FROM CompanyAndOwner
 WHERE CompanyId = 1748;

Or you can SELECT directly FROM a base table as well:
SELECT COUNT(OwnerId) AS OwnedCompaniesQuantity
   FROM CompanyOwner
  WHERE OwnerId = 1;

etc.
e All the data manipulation operations included here are comprised in the db<>fiddle and the SQL Fiddle previously linked to, so that you can analyze the result sets that they produce.
Company Address(es) and Phone Number(s)
In the series of deliberations that we engaged in via comments I asked you whether you interested in retaining Addresses and PhoneNumbers that belong to Companies, and your response was the following one:

A person can only have one phone and one address. Same goes for the company, because I have made a script that finds the phone based on this information. The way I differentiate between knowing if a entity is a company or person is by using a column called F_Org which will be larger than 4 digits for companies. This is all handled by the script.

I am not sure if that means that the Address(es) and Phone Number(s) of a Company are stored in an application program component (perhaps a file with records and fields, or something similar) but, if you are in fact handling that information, you should make use of the right tool for the job, i.e., include those aspects in the database structure and take advantage of the instruments provided by the database management system (i.e., PostgreSQL) so that you can administer the concerning data in an optimal manner (e.g., by virtue of logical-level operations that are based on the relational algebra and declarative constraints, supported at the physical level by a powerful set processing engine).
Feasible DDL structure extension representing a supertype-subtype relationship
Thus, in case that you determine that, in your business domain

the same Address occurrence can be kept by more than one Person and/or Company instance,

and

the same PhoneNumber occurrence can be used by more than one Person and/or Company instance,

you may like to analyze the diagrams I included in this answer for a question on a scenario that is very similar to the one under consideration (with, among other features, the Party supertype representing either the Organization or Person subtypes, People playing the Role of Owners of Organizations, and Addresses and PhoneNumbers linked with Organizations and People via the supertype), as they can be used as a reference for a tentative extension.


SQL Server 2008 on Hyper-V Virtual Machine with Multiple instances Running SQL Server 2008 instance on a Virtual Machine (Hyper-V) on a Windows 2008 R2 OS. Currently has a few production DBs. The whole VM itself is on RAID 5 volume (so are the DB files etc).

We will be adding further development and production DBs onto this VM soon. The production DB's on average are 1.5Gig in size with the largest DB less than 20Gig.

My question is: would having multiple instances make much difference in my situation. i.e.
Data separation (dev, prod)
An instance fails the others still work (not sure if applicable in a shared disk situation)

Would multiple instances be worth the overhead? i.e. more services, memory used by services

Thanks.

PS. Our disk layout is on a single disk and I understand that is not the recommended but will leave as a separate issue as I want to nut down on the multiple instance question first.

For your hardware setup you should go with a single instance of SQL Server.  Configure resource governer to identify dev and prod logins and set limits accordingly.


OpenProject pg_restore - input file does not appear to be a valid archive I'm trying to restore my OpenProject's database created with the command
sudo openproject run backup

this command created the file postgresql-dump-20190722131834.pgdump which I'm trying to restore with the command
pg_restore --host=localhost --dbname=openproject postgresql-dump-20190722131834.pgdump

but it returns me the error
pg_restore: [archiver] input file does not appear to be a valid archive

I've also tried to att the --format=c option to the pg_restore command, but the error changes to
pg_restore: [archiver] did not find magic string in file header

The script which OpenProject runs to create a PostgreSQL backup is:
dump_postgresql() {
  dst="${TARGET}/postgresql-dump-${timestamp}.pgdump"
  touch "$dst" && chmod 0640 "$dst"
  pg_dump -Fc $DATABASE_URL > $dst

  echo " done" >&2
  echo "$dst"
}

I've also noticed that pg_dump prints a lot of warnings like this:
pg_dump: [custom archiver] WARNING: ftell mismatch with expected position -- ftell used

I'm trying to restore the database on a machine which runs Ubuntu 18.04 and PostgreSQL 10.
This is the result of the command head postgresql-dump-20190722131834.pgdump (I've put it in a screenshot because there are some invalid characters which I can't copy-paste):

I'll post an answer because it might be useful to someone else.
As the screenshot in the question displays, there is an error in the /home/openproject/.postgresqlrc file on the server on which I've run the backup.
The format of that file was not correct: I've fixed it by following the format described here and when I had run the next backup I get no errors, and I was also able to restore that backup successfully.


PostgreSQL: Allow connection by host name and not IP? As background when connecting from remote (eg home mostly using JDBC) due to how the VPN works I always get a different IP address and hence I can't connect because the IP isn't listed in pg_hba.conf. Hence I tried with host name but that doesn't work either.

How can I allow access to PostgreSQL by hostname and not IP address?

EDIT:

I found the following which seems more suitable to place here than the comments:

https://www.postgresql.org/message-id/369ED3B4-CAF3-4BBB-904F-E86999249D8E@visi.com

  If a host name is specified (anything that is not an IP address range or a special key word is treated as a host name), that name is compared with the result of a reverse name resolution of the client's IP address (e.g., reverse DNS lookup, if DNS is used). Host name comparisons are case insensitive. If there is a match, then a forward name resolution (e.g., forward DNS lookup) is performed on the host name to check whether any of the addresses it resolves to are equal to the client's IP address. If both directions match, then the entry is considered to match. (The host name that is used in pg_hba.conf should be the one that address-to-name resolution of the client's IP address returns, otherwise the line won't be matched. Some host name databases allow associating an IP address with multiple host names, but the operating system will only return one host name when asked to resolve an IP address.)

So while ping with host name works, the reverse dns lookup doesn't work and hence according to above text explains why connection then fails. So it for sure seems the way the VPN works is to blame for this as nslookup indeed fails to find a name.

Quote from the manual

  address

  Specifies the client machine address(es) that this record matches. This field can contain either a host name, an IP address range, or ...

(emphasis mine)

So putting a hostname into pg_hba.conf is supported.

During the initial connection request, Postgres only sees the IP address (as part of the TCP protocol) and needs to do a lookup to resolve the IP address to a hostname. This only works if the client and the Postgres server are in the same network and are using the same DNS server(s).

My assumption is, that your VPN solution is preventing that hostname lookup to work properly.

If you always get your IP addresses from the same range, you could configure that range in pg_hba.conf rather than a single address (or hostname)


Phpmyadmin feed a timestamp column with "0000-00-00 00:00:00"? in PhpMyAdmin (OVH Mutu), a table with column type timestamp.
Impossible to put into this colum a timestamp :
Always display datetime like 2017-01-09 23:02:01 !!!
It generate several problmes : default current_timestamp give in this table "0000-00-00 00:00:00". Calculate delays is impossible.
The only way : define a int(11) type column.
An idea ?...

If you need to calculate with the timestamp field, you simply may use SELECT UNIX_TIMESTAMP(yourtimestampfield);. I decided to use this instead of the int(11) because of the built in function on update CURRENT_TIMESTAMP which shows this format 0000-00-00 00:00:00.

Also inside of mysql queries yo may use it the reverse way like UPDATE user SET logouttime = FROM_UNIXTIME(UNIX_TIMESTAMP(logintime)+3600) WHERE user_id = 12345;

Hope this helps.


getting the output of delete statement in UDF in postgres In Postgres in a user defined function I do

execute format('delete from table where session_id = %L', id);

ran without execute postgres (may) return(s) the following:

DELETE 0

Is there any way I could get this output in the user defined function? What I am looking is something like:

execute format('delete from table where session_id = %L', id) into log;

You can use the GET DIAGNOSTICS command for this purpose.  A quick example:

DO $$
DECLARE i integer;
BEGIN
    DELETE FROM event1;
    GET DIAGNOSTICS i = ROW_COUNT;
    RAISE NOTICE 'DELETE %', i;
END;
$$;

For DELETE statements, the only useful variable is ROW_COUNT, for INSERTs there is also RESULT_OID.

Set the error level after the RAISE to your choice and set the logging configuration accordingly.


How to index a composite primary key with non-sequential inserts? I have a composite primary key, comprising of a datetime, a location id and a 2 character code. This is the key that uniquely identifies each row of my table.

I know that the usage of this table will insert a week of data from a year ago, then a week of data from this year. Therefore, it's likely the table will get fragmented, as the primary key jumps back and forth.

I would like to know what sort of indexing strategies can be employed in this scenario. My gut feeling is that the default clustered index is a bad idea and I should replace that with a non-clustered index, which would make rebuilds easier.

If you are worried about fragmentation, do you have a maintenance solution on your server that addresses that such as SQL Server Maintenance Solution - Ola Hallengren?

Questions:

datetime data type is date, smalldatetime, datetime, datetime2(0-2),datetime2(3-4), datetime(5+) or?
LocationId data type is tinyint, smallint, int, bigint or ?
Character code data type is char(2), nchar(2) or?

More Questions:

How many rows are in your table?
Would you need an int or bigint for a surrogate id?
What are the other columns in your table?
Are all queries filtered by datetime, or are filtered by the locationid and/or character code?
Are you always returning the whole row, or sometimes just pieces of it?

Because most people assume the Primary Key is the Clustering Key, I am going to interpret your question as "How to index a composite clustering key with non-sequential inserts?".

The situation you are considering is related to debates like The Clustered Index Debate and Surrogate Key vs. Natural Key.

In this situation, I would want to consider the impact of a [16?] byte composite clustering key vs a 4 or 8 byte clustering key (int / bigint). My decision tree would look something like this:

Will we be using Hekaton (In-Memory OLTP)?

Yes => composite key. Run away.
No => Good call, continue...

How many rows will this table have?

Tens of millions, maybe more! => surrogate key (probably).

If the data length of each row will be variable and not narrow => surrogate key.
If the data length of the row will be fixed and narrow, and it results in optimal page usage => continue...

Less than that => continue...

How will the table be queried?

Various combinations of and not always all of datetime, location_id, character_code => surrogate key.

In this case you may want to be able to have multiple supporting indexes the combinations of datetime, location_id, character_code for your queries.
The clustering key is included in all non-clustered indexes, and the larger it is the more space/pages each index entry will require. => surrogate key.

Almost always by all three datetime, location_id, character_code or almost always by datetime or datetime, location_id;
but not by location_id without datetime and not character_code without datetime, location_id (zero or only a couple of non-clustered indexes on this table) => continue...

Will any other table reference this table?

Yes => surrogate key.
No => composite key is a reasonable option

Even if my first run through of the above decision tree leads me to a composite key, I would probably start my design using a surrogate key because it is easier to get rid of it (because it isn't being used) than to go back and add it and implement its use.

Just to clarify, I have had cases where I did find that the composite key was a better solution and did refactor the design to drop the surrogate key. I don't want to leave the impression that the surrogate key is always the better solution, even if it is a common default for many designers (including myself).

Reference:

A Simple Start – Table Creation Best Practices - Kimberly Tripp - concerning row size and page utilization
Ever-increasing clustering key – the Clustered Index Debate……….again! - Kimberly Tripp
The Clustered Index Debate Continues… - Kimberly Tripp
More considerations for the clustering key – the clustered index debate continues! - Kimberly Tripp
How much does that key cost? (plus sp_helpindex9) - Kimberly Tripp
101 Things I Wish You Knew About Sql Server - Thomas LaRock
SQL Server: Natural Key Verses Surrogate Key - Database Journal - Gregory A. Larsen
Ten Common Database Design Mistakes - Simple Talk - Louis Davidson
Hekaton (In-Memory OLTP) - dbareactions.com


How should I configure the RAID array of SSD drives on my SQL Server? I'm building a SQL Server with 48 GB RAM, 1 CPU, & 8 SATA III (6GB/s) SSD drives (128 GB Crucial m4) and an LSI MegaRAID controller (SAS 9265-8i). I expect the typical work load to be mostly reads. There will be some periods of heavier write activity (hourly data syncs w/ 3rd party data providers - nightly backups), but the I suspect the typical read/write ratio is about 90% reads/10% writes.

Option 1:
Logical Drive C: - RAID 1 (2 physical drives) - OS
Logical Drive D: - RAID 10 (6 physical drives) - DB files/logs/tempdb/backups?

OR

Option 2:
Logical Drive C: - RAID 1 (2 physical drives) - OS
Logical Drive D: - RAID 1 (2 physical drives) - Db Files
Logical Drive E: - RAID 1 (2 physical drives) - log files/backups?
Logical Drive F: - RAID 1 (2 physical drives) - tempdb

OR

Option 3:
Other suggestions?

I'm thinking option 1 would give me better performance, since all DB activity would be striped across 3 drives (and mirrored across the other 3 in the array), although option 2 seems to mimic conventional wisdom (which appears to apply more to mechanical drives than SSDs). It seems like Stack Overflow has gone with option 1.

I'm guessing with SSD's it's OK to put every thing on a single logical drive since your server is probably more CPU constrained instead of I/O constrained at that point?

Another question I have is where should I place the nightly backups? We don't want backups slowing down the rest of SQL server, and I'm guessing writing the backups the same location as the logs is a good practice because the read/write behavior in both those cases is sequential writes.

Conventional wisdom about RAID doesn't apply well to SSDs. They don't really need striping (RAID0). They are prone to failures by-design, but RAID-1 is usually not the right answer for SSD for two reasons: a) is wasteful, halves the capacity of the SSD array (and they are pricey) and 2) SSDs failure characteristics leads towards both drives in the mirror to fail at very close intervals (ie. correlated failures) and thus render the entire array useless. See Differential RAID: Rethinking RAID for SSD Reliability for a lengthier discussion. Some have recommended using Raid-6 for SSDs.

Additionally, the conventional wisdom of SQL Server file layout doesn't apply to SSDs. I would recommend you watch SQL on SSDs: Hot and Crazy Love and go over the benchmark links in this answer.


Enforce conditions on queries Currently I have a simple schema, a sites table and an items table.

Sites own many items, items has a siteId column to store the reference of the site which owns it.

I'd like to be able to enforce a condition when querying items, meaning returning an error if it is not queried with a siteId - in practice:

select * from items would fail
select * from items where siteId = 1 would succeed, albeit a site record with id 1 exists

This can easily be achieved on an application level.

I recently started investigating further into row level policies, roles, functions, etc… which I found very interesting as they enable lower level setups. That is, I'm still new about it and lack understanding/knowledge. I was wondering if the use case described above could simply be solved within SQL (via a combination of policies, functions, etc… or any other Postgres builtin tools)?

One possibility (one that you feel intuitively, apparently) is to centralize your query into a function and disable querying otherwise.

Let's say the tables are owned by a role called owner, and there is a reader to run SELECT queries against the tables.  Then there is an application role which can connect to the DB and is able to call functions.  All this can be achieved by:

-- log in as a superuser, for example
CREATE ROLE owner;
CREATE ROLE reader;
CREATE USER application WITH PASSWORD 'a';

SET ROLE TO owner;

CREATE SCHEMA data;

CREATE TABLE data.important (i_id serial PRIMARY KEY);  -- will be owned by owner

GRANT SELECT ON data.important TO reader;

At this point, whoever can assume the reader role (by being a member and/or using SET ROLE) will be able to run any SELECT against the table.  Note that application doesn't have any rights here.

Then create another schema for your functions:

CREATE SCHEMA api; -- well, you can call it something else, too

RESET ROLE; -- to switch back to your superuser self

CREATE FUNCTION api.select_important(id integer)
RETURNS data.important
LANGUAGE sql
SECURITY DEFINER -- the important bit
AS $$
SELECT * FROM data.important WHERE i_id = id;
$$;

GRANT USAGE ON SCHEMA api TO application;
GRANT EXECUTE ON FUNCTION api.select_important(integer) TO application;

The SECURITY DEFINER clause will make the function inherit the rights of the superuser to read from the table (there is no need to grant it).  Now if you log in as application, you can call the function:

\c - application
Password for user application:

SELECT * FROM api.select_important(1);
 i_id
──────

(1 row)

However, you cannot read from the table directly:

my=> SELECT * FROM data.important WHERE i_id = 1;
ERROR:  permission denied for schema data
LINE 1: SELECT * FROM data.important WHERE i_id = 1;

Note: to spare GRANTs all the time you create a table or function, you can set default privileges.  See the bottom part of an earlier answer of mine about how to do that.


Create view with dynamically selected tables - is it possible? I have subsection of tables in my database that share part of a schema and a naming scheme. I'd like to create a view that automatically gathers all of the common columns of these tables into a single place. The catch is that more of these tables may be added from time to time, and I would ideally like to have the view dynamically add these new tables to itself.

For example, say we have three tables

+-----------------+ +-----------------+ +-----------------+
|table1           | |table2           | |table3           |
+-----------------+ +-----------------+ +-----------------+
|id               | |id               | |id               |
|name             | |name             | |name             |
|unique_attribute1| |unique_attribute2| |unique_attribute3|
+-----------------+ +-----------------+ +-----------------+

If I were doing this statically, I would write the view as:

CREATE VIEW unified_table_view AS
SELECT id, name FROM
  (SELECT id, name FROM table1)
  UNION (SELECT id, name FROM table2)
  UNION (SELECT id, name FROM table3);

However, if a new table

+-----------------+
|table4           |
+-----------------+
|id               |
|name             |
|unique_attribute4|
+-----------------+

is added and I want it to be included in the view, I would have to go an manually add that to the view definition:

CREATE VIEW unified_table_view AS
SELECT id, name FROM
  SELECT id, name FROM table1
  UNION SELECT id, name FROM table2
  UNION SELECT id, name FROM table3
  UNION SELECT id, name FROM table4;

I want to know if it is possible to write the view such that when table4 is added, the view automatically incorporates it, without me having to update the view definition.

Normally if I was doing something like that, I would interrogate information_schema to construct something for EXECUTE, but that is a one-time thing and I would have to run the query again to update the view.

You cannot do that with a view, because a view is parsed when it is created, and the parse tree is stored. That will lead to an error if a table used in the view does not exist.

What you could do is to write a table function that searches for tables, constructs a query string for each and uses RETURN QUERY EXECUTE to add the results to the output. That would be equivalent to UNION ALL. If you need UNION, you'd use SELECT DISTINCT to query the table function.


How to raise_application_error beyond a when others clause Background:

I've used a few Oracle articles to develop an error package, with some procedures. One of these is Log_And_Return which is called throughout the database. I've simplified it for brevity, and it looks like this:

PROCEDURE Log_And_Return (error_name)
IS
BEGIN
    // Some code to convert error_name to error_code and error_message
    LOG (error_code, error_message);
    Raise_To_Application (error_code, error_message);
END Log_And_Return;
// In reality, this procedure takes parameters which determine whether it will log and/or raise.

The Log procedure does as it says and the Raise_To_Application procedure simply raises an application error.

Problem:

I have a procedure, which performs a query, e.g. fetching a customer record. If this query fails, it's a big problem. So I could do this:

BEGIN
    SELECT *something*
    FROM *some table*
    WHERE *some field* = *some user input*

EXCEPTION
WHEN NO_DATA_FOUND THEN
    ERR.Log_And_Return('unknown_id');
WHEN OTHERS THEN
    ERR.Log_And_Return('unknown_error');
END;

So I'm displaying a specific error message if no data is found, or a generic one is another problem has occurred.

Now, let's say I amend the code:

BEGIN
    SELECT *something* INTO *some variable*
    FROM *some table*
    WHERE *some field* = *user id*
    Call_Another_Procedure(*user id*, *some variable*)

EXCEPTION
WHEN NO_DATA_FOUND THEN
    ERR.Log_And_Return('unknown_id');
WHEN OTHERS THEN
    ERR.Log_And_Return('unknown_error');
END;

Now, after the select query, I'm calling a new procedure. Inside it, I'm doing a few things, including an update statement, like so:

// bunch of logic

BEGIN
    UPDATE *another table*
    SET *some field* = *some value*
    WHERE *some field* = *variable passed into method*
EXCEPTION
WHEN NO_DATA_FOUND THEN
    Err.Log_And_Return('some_error')
END;

Question:

If the no_data_found error is thrown, Log_And_Return will log the problem and raise an application error... which will then be caught by the when others clause in the parent procedure - which will return the wrong message to the user.

What is the workaround to this?

One workaround I had considered, and I've no idea if this is recommended or not, would be to wrap every stored procedure with a BEGIN END EXCEPTION block, where every procedure had a when others block that just logged and re raised the most recent error (using SQLCODE). Then, in my application layer I could specify that if the error is between -20000 and -20999, show it along with its message, otherwise show a generic message (and the DBA can find out what happened in the database by looking at the log table, along with a full stacktrace).

Or do I simply get rid of ALL when others blocks and let the application layer deal with any of these unknown exceptions?

There are a variety of approaches you could take. You should pick the one you feel is most suitable for your application.

Include no WHEN OTHERS exception handlers and let the raw Oracle errors propagate to your users.
Include WHEN OTHERS exception handlers in the top-level blocks.
Include WHEN OTHERS exception handlers in all/most blocks

The first option requires no effort on your part, but can expose users to very unfriendly error messages. They can also expose details about the database which could be of use to hackers, etc.

The second option require some effort. You can use the built-in packages, such as DBMS_UTILITY.FORMAT_ERROR_BACKTRACE to identify where an exception occurred, which can be included in any logging processes you may have. You can convert the raw Oracle errors into application specific ones.

The third option requires most effort. However, as well as the above, you can also log items such as variable values to help you identify why an error occurred, not just where it occurred. Using some common exception handling routines -as you seem to be doing - will reduce the effort required for this approach.

WHEN OTHERS THEN
   exception_pkg.handler('<variable_values_of_interest>')
   RAISE;

where you pass the variables values to be logged via a standard process before re-raising the exception.

You should always re-raise an exception in a WHEN OTHERS block, either the original exception or one with an application specific message - via RAISE_APPLICATION_ERROR.


How to read all metadata without select permissions to actual data? I need to create a user account that can read the metadata for all objects from information_schema

I can access the information_schema with any user but only for tables that the user has select privileges. Is there any role (like Oracle's SELECT_CATALOG_ROLE) that I need?

The possible solution depends on exactly which objects you are interested in.

Tables

The way I see is half suggested by the documentation:

  Only those tables and views are shown that the current user has access to (by way of being the owner or having some privilege).

So, you can set your information_schema_reader role as owner of everything, revoking the privileges (SELECT, INSERT, UPDATE, DELETE, TRUNCATE, REFERENCES, TRIGGER).  The backside is:

  The right to drop an object, or to alter its definition in any way, is not treated as a grantable privilege; it is inherent in the owner, and cannot be granted or revoked.

So, this is possibly not what you want - the role cannot see the data but can drop the table.

On the other hand, in the above privilege list there is one which seems really innocent (and can be used for checking data in tables only in a very cumbersome way): REFERENCES.  You can try to grant this to an otherwise powerless user.

Other objects

Unfortunately, not every object type has such 'weak' privileges.  A function, for example, is either executable or not by a user - there is no other privilege that can be granted.  Depending on your needs (the exact list of types you want to 'hide'), this may be acceptable.


Issues with uninstalling and re-installing SQL Server 2008 R2 I have installed SQL Server 2008 R2, and was able to login and also create a database and everything.

When I was facing some problems with connecting my .NET project with SQL Server, I created some new accounts in SQL Server.

For some reason I uninstalled it. But later when I re-installed, I was not able to login with the "SA" account. I still see a few of the other user accounts that I created earlier.

Is there any way to completely uninstall SQL Server 2008 R2 and successfully install new copy and login?

Log in with your Windows account and enable the SA login again.  You'll probably need to configure the SQL Server for mixed mode authentication as well.

If you decide that you do want to reinstall SQL, after uninstalling it you'll need to remove the database files then you can reinstall.  SQL leaves the databases alone when you uninstall so that you don't loose any accounts or data when reinstalling.


How to Select the First (or last) entry for a specific day I have a table "AttendanceRecords" with entries like this

EntryID UUID PRIMARY KEY,
StafID UUID REFERENCES staffmembers(ID) NOT NULL,
ArrivalTime TIMESTAMP NOT NULL,
DepartureTime TIMESTAMP

For one function I want to find the first and last entry for a specified staff member on a specified date.  To Clarify, First would be as per

SELECT MIN(ArrivalTime)
FROM AttendanceREcords
WHERE StaffID = 'xxxxx';

But I'm looking for how to compose a query that will do

SELECT EntryID
FROM AttendanceREcords
WHERE StaffID = 'xxxxx'
AND ArrivalTime = MIN(ArrivalTime);

On a related report I would like to find all the entries where the arrival-times are on a specified date, eg sorted by StaffID

What would the queries look like for these (Using Postgres 9.3)

You can't use an aggregate function in a condition, you need to use a sub-select

SELECT EntryID
FROM AttendanceREcords
WHERE StaffID = 'xxxxx'
AND ArrivalTime = (select min(ArrivalTime)
                   from AttendanceREcords
                   where StaffID = 'xxxxx');

But this can be done more efficiently by using a LIMIT clause:

SELECT EntryID
FROM AttendanceREcords
WHERE StaffID = 'xxxxx'
ORDER BY arrivalTime
LIMIT 1

There is a difference between the two statements: if more than one row has the same minimum arrival time, the first one will return all of them, the second one only one row.

Another alternative that is usually more efficient that a sub-select is using a window function:

SELECT EntryID
FROM (
  SELECT EntryID,
         dense_rank() over (order by arrivalTime) as rnk
  FROM AttendanceREcords
  WHERE StaffID = 'xxxxx'
) t
where rnk = 1;

By changing the order by arrivalTime you can select the first or the last.

If you want the first and last in a single query you can do something like this:

SELECT EntryID,
       arrivalTime
FROM (
  SELECT EntryID,
         min(arrivalTime) as min_time,
         min(arrivalTime) as max_time,
  FROM AttendanceREcords
  WHERE StaffID = 'xxxxx'
) t
where arrival_time = min_time
   or arrival_time = max_time;


What are the downsides of using varbinary[max]? I want to set up a table to store waveform data from an oscilloscope. The table would have a column of varbinary[max] because the waveform data from the scope is returned as a binary sequence that ranges from 1,400 bytes to 14,000,000 bytes. I would have a half dozen more int and small varchar for parsing information. So each row in the table will represent all information related to this waveform.

I am a complete noob to working with databases. I just set this one up and created my insert queries. I have no idea how using such a large type may affect responsiveness, speed, or anything else.

I can say that I will never want to use this varbinary column to do any filtering or searching. This table is mostly for record storage.

[MAX] columns are to be considered like BLOBs (Binary Large Objects) and are physically stored on another "page".
This means that practically, they are like on another table, seamlessly linked to the one with the rest of the data.
One of the main limitations is that you can't index the [MAX] column.

I don't see any issue in you doing things as you suggest.


SQL Server 2016, temporal tables and compressed indexes We use temporal tables in our database, and I have just run Brent Ozar's sp_BlitzIndex on it with @mode=0 (urgent issues only).

It has come up with 33 issues, all of which are "Abnormal Psychology: Compressed indexes", and they are all on the temporal tables e.g.

  Schema.TableName.ix_IndexName (1). COMPRESSION:   Partition 1 uses PAGE
      [CX] [2 KEYS] SysEndTime {datetime2 8}, SysStartTime {datetime2 8}

I am pretty sure we didn't actively choose to turn compression on for these indexes, and I think it is the default behaviour for system generated history tables. But should I be worried? sp_BlitzIndex thinks I should be...

What are the recommended best practices? Thanks in advance.

This is default behaviour for temporal tables, the history tables are PAGE compressed by default. This is done out of space & performance considerations, compression will reduce the amount of data that has to be processed, both on disk & memory. This will have a trade-off in extra CPU cycles as SQL Server has to decompress the data. So unless you are under CPU pressure, compression is usually a good thing as your disk IO & memory usage improve.

More information on temporal tables: Temporal Table Considerations and Limitations

For what sp_BlitzIndex page says about it:

  Abnormal psychology findings aren’t necessarily problems– just look at
  this as a big FYI. Some implementations of indexes may mean that the
  database can only be restored in a certain edition of SQL Server, or
  they may change performance of some operations. Which apply to you?
  Check out details on your finding below.

So as stated above, this does not mean it is a problem. For the reasons described above.


How to get Current Shift? Question

How can I retrieve the current shift record based on getdate() and which lies between the existing ShiftStartTime and ShiftEndTime as per the defined table and data?

It is possible get shift if the time reaches 12.00 AM?

Table

create table ShiftMaster
(
ShiftID char(4),
ShiftName char(7),
ShiftDescripiton varchar(20),
ShiftStartTime time,
ShiftEndTime time
)

Data

insert into ShiftMaster
values
('SHF1', 'Shift A', 'First Shift', '07:15:01', '15:45:00'),
('SHF2', 'Shift B', 'Second Shift', '15:45:01', '00:15:00'),
('SHF3', 'Shift C', 'Third Shift', '00:15:01', '07:15:00')

Original Data (Screenshot)

What I Have Tried

 SELECT * FROM VHASM2.ShiftMaster
 WHERE FORMAT(getdate(),'HH:mm:ss') between ShiftStartTime and ShiftEndTime;

dbfiddle and Alternate Statement

select *
from ShiftMaster
where
'' + cast(datepart(hh, getdate()) as char(2)) + ':' + cast(datepart(n, getdate()) as char(2)) + ':' + cast(datepart(ss, getdate()) as char(2)) + '' >= ShiftStartTime
and
'' + cast(datepart(hh, getdate()) as char(2)) + ':' + cast(datepart(n, getdate()) as char(2)) + ':' + cast(datepart(ss, getdate()) as char(2)) + '' <= ShiftEndTime

The dbfiddle so far.

You can separate the logic into 3 parts:

first, for "normal" shifts, that don't include midnight
and second and third for shifts that have midnight.

The query:

-- time now
declare @tn as time = getdate() ;

select *
from ShiftMaster
where ShiftStartTime <= @tn and @tn < ShiftEndTime
   or ShiftEndTime < ShiftStartTime and @tn < ShiftEndTime
   or ShiftEndTime < ShiftStartTime and ShiftStartTime <= @tn
  ;

Test in dbfiddle.uk.

Please also note that I used closed-open intervals and comparisons (avoiding the evil BETWEEN*), so the shift ('00:15:00', '07:15:00') stands for any time just of after 00:15 and before 07:15 (but not exactly 07:15). So every point in time should be in one and only one shift, assuming that you define the shifts correctly.

insert into ShiftMaster
values
('SHF1', 'Shift A', 'First Shift',
             '07:15:00', '15:45:00'),   -- notice the starts
('SHF2', 'Shift B', 'Second Shift',
             '15:45:00', '00:15:00'),   -- and the ends
('SHF3', 'Shift C', 'Third Shift',
             '00:15:00', '07:15:00') ;

With your method, there would be gaps of 1 second between shifts.

*: More info and detailed explanation about why to avoid BETWEEN with datetime types on the excellent blog post by Aaron Bertrand: What do BETWEEN and the devil have in common?.


Optimal Hardware for > 30K Requests / min? We have some performance issues with one of our Test-Machines a MySQL 5.7.21 Standalone Server.

We have: 8 CPUs 16GB RAM (buffer_pool_size 5.6 G) and need to handle > 30 K Requests / minute.

When we run our tests the CPU workload raised to 100% and we got several issues (long query run time etc...)

What is your hardware recommendation for this workload?
ty in advance br Andreas

500 inserts per second is definitely into the area where out-of-the-box MySQL begins to have trouble.

Some things to do:

Don't trust benchmarks; use your code.
Combine one-row INSERTs into transactions.  (Much of what is limiting you to 500qps is the transaction overhead.
"Batch" inserts -- 100-1000 rows per INSERT statement gives you 10x performance improvement.
Use LOAD DATA -- also 10x (maybe more).
Switch to SSDs -- perhaps 10x faster than spinning drives.
innodb_flush_log_at_trx_commit=2 -- much faster, but less secure in a crash.
Decrease the number of indexes, especially UNIQUE, on the table.
Do not use UUIDs.
Gather data some other way (eg, MyISAM table with no indexes), then batch INSERT...SELECT....  This has the added advantage of giving you a change to efficiently do ETL transformations.
My High Speed Ingestion technique.  It involves ping-ponging between two staging tables and handles various variations (multiple connections, etc)


Renaming multiple databases on a MySQL slave I have 4 mysql databases that I'm replicating from a master to a slave.  In the past, I have renamed one of them because the slave is also a dev server where it has a dev version of that schema with the same name as prod, to make things easier for devs for various reasons.  Replication currently goes like this:

db1 => db1_prod
db2 => db2
db3 => db3
db4 => db4

(mind you, these aren't the actual names).

This has worked for us in the past because db1 is actually a very unique name & we took care of the renaming by piping the dump through sed, such as:

mysqldump --master-data --databases db1 db2 db3 db4 | sed 's/db1/db1_prod/g;' | pv | pbzip2 -p2 > mysqldump.sql.bz2

Then on the slave we have the rename directives in my.cnf:

replicate-rewrite-db='db1->db1_prod'

This has always worked due to db1's actual unique name.  However, my testing has shown that it will not work for the other DB names, because some of the strings actually exist in tables or rows.  What we want to do is rename them all to _prod:

db1 => db1_prod
db2 => db2_prod
db3 => db3_prod
db4 => db4_prod

I've also tried:

s/`db1`/`db1_prod`/g

But when I grep'd the resulting file, it still got more hits than I expected, though I can't say exactly what because the output is a bunch of very long rows, but I know it's more than just the DB name.

Best thing I can think of right now is to dump one at a time without the --databases flag in mysqldump, then specify manually which DB schema to restore it to, but I'm not sure how to do that while keeping replication running if I'm having to reset the binlog position on the slave for each DB restore.

I did search around a bit & saw this post:  Restore mysql database with different name, but my use case is a little different since I have multiple DBs to consider.

Is there a better/easier way to do what I'm trying to do?

Edit:

I guess I could open the dump file up & edit the 3 lines for each DB each time I need to restore:

-- Current Database: `db1`
--

CREATE DATABASE /*!32312 IF NOT EXISTS*/ `db1` /*!40100 DEFAULT CHARACTER SET latin1 */;

USE `db1`;

and edit those 3 lines for each DB.

MySQL Slave is used as Development Server! Better not to.
Otherwise, let the replication happen with the same db / table names. And let the db used for development be named differently - which you can handle in the program code itself (one dbname for dev and another for production). You can have an environment section which names the db and several other required too for dev and production.
If at all you prefer this...assuming that the dbname changes and not table names...create individual mysqldump per db and then mysql insert each dump separately into your preferred db.


Missing DB on linked server So there is a linked server. However, I am not currently able to view all databases associated with that linked server from within SSMS after expanding the catalog dropdown. It is my understanding that this may be a permissions issue. However, I am unsure as to which permissions I need to grant in order to interact with tat db from the linked server.

Any ideas?

After executing the following command from the linked server:

select * from [<server>].[<database>].[dbo].table1

I receive the following error message:

  The OLE DB provider "SQLNCLI10" for linked server "" does not
  contain the table """."dbo"."table1"". The table either does
  not exist or the current user does not have permissions on that table.

TLDR;

Problem: I'm not sure how to get one of the databases associated with my linked server to show up.!

This is due to the account used for Linked Servers not being added to the security of the database trying to be accessed.

On the databases that are shown in the Linked Servers container, you will also find the account (back on the original db server) added to the Security section with Read access. Add that account to the newly created db (catalog in this case) on the original server and then refresh the linked servers on the target db server. Your catalog will now show.


MySQL subqueries that use range based on values of main queries don't use indices properly I think I've isolated a problem that has been affecting many of my queries lately.
And would like some help to figure out a solution for this.

Ok so my findings are that a normal query that runs very fast using like a couple of rows can actually use indexes improperly when used in a subquery which is based on values from the main query.

Lets take an example:

DROP TEMPORARY TABLE IF EXISTS Person;
DROP TEMPORARY TABLE IF EXISTS CofeeBreaks;
CREATE TEMPORARY TABLE IF NOT EXISTS Person
(
    `person_id` INT(11) AUTO_INCREMENT,
    `age` INT,
    `lastCofee` DATETIME,
    KEY `idkey` (`person_id`) USING BTREE,
    KEY `datekey` (`lastCofee`) USING BTREE
) ENGINE = MEMORY;
CREATE TEMPORARY TABLE IF NOT EXISTS CofeeBreaks
(
    `id` INT(11) AUTO_INCREMENT,
    `cofeeBreakStart` DATETIME,
    `cofeeBreakEnd` DATETIME,
    KEY `brekIdKey`(`id`) USING BTREE
) ENGINE = MEMORY;
INSERT INTO Person (age, lastCofee) VALUES (24, '2013-03-27 14:45:34');
INSERT INTO Person (age, lastCofee) VALUES (34, '2013-03-27 14:46:38');
INSERT INTO Person (age, lastCofee) VALUES (26, '2013-03-27 15:25:24');
INSERT INTO Person (age, lastCofee) VALUES (28, '2013-03-27 16:33:54');
INSERT INTO Person (age, lastCofee) VALUES (46, '2013-03-27 17:11:03');
INSERT INTO CofeeBreaks (cofeeBreakStart, cofeeBreakEnd) VALUES ('2013-03-27 15:11:03', '2013-03-27 17:25:24');
INSERT INTO CofeeBreaks (cofeeBreakStart, cofeeBreakEnd) VALUES ('2013-03-27 14:45:34', '2013-03-27 15:25:24');
INSERT INTO CofeeBreaks (cofeeBreakStart, cofeeBreakEnd) VALUES ('2013-03-27 17:11:03', '2013-03-27 17:11:03');
SELECT * FROM Person WHERE lastCofee BETWEEN '2013-03-27 15:11:03' AND '2013-03-27 17:11:03';
SELECT
    *,
    (SELECT AVG(Person.age) FROM Person WHERE Person.lastCofee BETWEEN CofeeBreaks.cofeeBreakStart AND CofeeBreaks.cofeeBreakEnd) AS averageAge
FROM CofeeBreaks;

So the explain results for the first select are as follow:

1   SIMPLE  Person  range   datekey datekey 9       1   Using where

But the second query doesn't use the index properly in the subquery and analyses more rows than necessary:

id  select_type         table   type    possible_keys   key key_len ref rows
1   PRIMARY CofeeBreaks ALL                                               3
2   DEPENDENT SUBQUERY  Person  ALL       datekey                         5

As we can see the subquery needs to analyse all rows in the person table when none of the cofeebreaks ranges surrounds all of the 5 persons.

The way I've been fixing the performance issues in a very busy database is by doing a select into a temporary table and than looping the rows of that table and updating it with the aggregates that i need. This is obviously ugly code and shouldn't be built that way.
But I really haven't found a way to optimize queries for this kind of subqueries and I've tried a lot of possible ways to do this without success on the optimization.

Thanks in advance for any inputs.

This small data sample doesn't serve to illustrate that the behavior you are attempting to identify exists.  Indeed, I've tested it on a larger data set and it did use the Index (using MySQL 5.5.30).

The problem is that when the optimizer determines that using an index would result in an inordinately large number of matches -- compared to the total number of rows in the table -- it won't use an index, because that could actually perform worse than simply scanning the whole table, and it will exhibit exactly the behavior this example illustrates... it knows the index is a candidate, but it chooses not to use it.

But I would suggest the problem lies in the fact that you're using a subquery in a place where a subquery isn't really necessary or called for.

I rewrote this as a join, because, from what I can tell, this is what you're asking the database to do: join each row in Person to the matching row(s) in CofeeBreaks where that person took their break during that window, and average the ages of the attendees.

I also built this here on SQL Fiddle.  Removed TEMPORARY from the table definitions because the Fiddle doesn't seem to support them properly (because it probably uses a connection pool).

SELECT cb.id,
       cb.cofeeBreakStart,
       cb.cofeeBreakEnd,
       avg(p.age)
  FROM CofeeBreaks cb
  JOIN Person p ON p.lastCofee BETWEEN cb.cofeeBreakStart AND cb.cofeeBreakEnd
 GROUP BY cb.id;

EXPLAIN SELECT on this subquery shows that the index is being used, even on this small data set... although if you change that to a LEFT JOIN (which would show all coffee breaks even if nobody took that particular break, while the JOIN only includes breaks where at least one person did), the index shows up as being a candidate, but doesn't get used... again, likely because of the cost, and this behavior would likely be different with a larger data set.

The LEFT JOIN version of the query would produce identical results to your subquery regardless of the table data, while the JOIN version only produces identical results if every CofeeBreak had at least one person taking that break, which in your sample data, it does.

But using the indexes or not, a correlated subquery will not usually scale as well as a join.

http://dev.mysql.com/doc/refman/5.5/en/rewriting-subqueries.html


Can't restore from mysql dump file I used mysqldump command to export/dump .sql file with rows like:

String cmd1 = mysqldump + " -h" + host + " -u" + username + " -p" + password + " " + "--compact --no-create-info " + "--where=ID=" + "'5b4ed36d-6152-4bfd-ad06-666f781e8bdc'" + " " + database + " " + "EMPLOYEE" + " " + "--result-file=" + "C:\\employee.sql";

This is the result of above command in a employee.sql file:

INSERT INTO `employee` VALUES ('Anette','Busch','2014-08-12','admin',' ','FALSE');

Now, I want to import the values from empolyee.sql file using command:

String cmd =  mysql + " --user=" + username + " --password=" + password +  " -e" + " source " + restoreFile;

The problem is I can't execute import command. I'm using the same import command for whole database, that works fine, but not in this case.

Thanks a lot for your guidance.

The problem is that you forgot to put double quotes around the -e parameter

String cmd =  mysql + " --user=" + username + " --password=" + password +  " -e\"" + "source " + restoreFile + "\"";

An alternative would be to redirect the file into the  mysql client

String cmd =  mysql + " --user=" + username + " --password=" + password +  " < " + restoreFile;

I also just noticed that you forgot to set the target database

String cmd =  mysql + " --user=" + username + " --password=" + password +  " -D" + mydb + "-e\"" + "source " + restoreFile + "\"";

String cmd =  mysql + " --user=" + username + " --password=" + password +  " -D" + mydb + "  < " + restoreFile;

Give it a Try !!!


Understanding Sort Estimates with TOP(variable) I have the below query and I am trying to understand the sort estimate when I use TOP with a variable.

PLAN

create table justint ( c1 int )

insert into justint
select top 1000000 row_number() over(order by t1.number) as N
from   master..spt_values t1
cross join master..spt_values t2

declare  @MaxRowsToReturn    INT     = 500
SELECT  top (@MaxRowsToReturn) c1
FROM justint WITH (NOLOCK)
where  c1 >= 600000
ORDER BY c1 ASC

So how does an estimate of 404986 going into the sort operator comes out as an estimate of 100?  Is it just an arbitrary number since it cant sniff the variable in the TOP operator?

This would seem to be an issue with parameter sniffing (or lack thereof in this case).

The optimizer has to choose the best plan for the batch, in this case without knowing how many rows the user is going to need from the TOP operator. The key here is that in your case the @Maxrowstoreturn is set during the batch in which it is used, meaning the engine needs to create the entire plan before that variable is initialized or used. As for why the number 100 is used, it's likely an arbitrary decision by the developers as there is no way to provide a solid estimate in this situation anyway.

If we run the following code, this corrects the estimate, as SQL is able to use the input parameter to create a more accurate execution plan:

create table justint ( c1 int )
go

insert into justint
select top 1000000 row_number() over(order by t1.number) as N
from   master..spt_values t1
cross join master..spt_values t2
go

update statistics justint with fullscan;
go

create procedure #tmpproc

(@Maxrowstoreturn int)

 AS
begin

SELECT  top (@MaxRowsToReturn) c1
FROM justint WITH (NOLOCK)
--where  c1 >= 600000
ORDER BY c1 ASC
end
go

exec #tmpproc 10000

drop table justint


How locks in Postgres behave when quitting the JVM abnormally I have a table 'car', as follows

                 Table "public.car"
  Column  |  Type  | Collation | Nullable | Default
----------+--------+-----------+----------+---------
 car_no   | bigint |           | not null |
 car_name | text   |           |          |
Indexes:
    "car_pkey" PRIMARY KEY, btree (car_no)

Table content:

 car_no |  car_name
--------+------------
      1 | first_car
      2 | second_car

I have a JAVA code, which locks a row in database and quits abnormally as follows,

public static void main(String[] args) throws SQLException {
        Connection connection =
                DriverManager.getConnection("jdbc:postgresql://localhost:5432/postgres", "root", "root");

        // begin transaction
        connection.setAutoCommit(false);

        System.out.println("waiting for lock "+new Date(System.currentTimeMillis()));
        // getting lock
        connection.createStatement().executeQuery("select * from car where car_no=2 for update");
        System.out.println("lock acquired "+new Date(System.currentTimeMillis()));
        System.out.println("press enter to quit abnormally [System.exit(1)]:");
        Scanner scanner = new Scanner(System.in);
        scanner.nextLine();
        System.out.println("terminating execution "+new Date(System.currentTimeMillis()));

        // quiting JVM abnormally
        System.exit(1);

        System.out.println("this doesn't prints");
}

When i executed the code snippet concurrently (two times), the first execution takes the row lock (waiting for System.exit), the second execution waits for the row lock. When first one quits abnormally, the second execution got the required lock. I wonder what happened to locks acquired by first execution, are they returned to database since abnormal termination or something else. I need some insights on row locking during these kind of scenario. Does it depends on transaction isolation level?

The answer depends on a few things and my Java knowledge isn't up-to-date regarding session handling.

However, the lock isn't held by the database, the lock is held by a session.

This means if the session no longer exists, then the lock is removed from the database object (table, row, ...).

If you were to run the Java code on a different server/computer, then you might observe a delay before the second message is displayed. This will be because the JDBC connection could be using different parameters.

socketTimeout

  The timeout value used for socket read operations. If reading from the server takes longer than this value, the connection is closed. This can be used as both a brute force global query timeout and a method of detecting network problems. The timeout is specified in seconds and a value of zero means that it is disabled.

Answering Your Question

  I need some insights on row locking during these kind of scenario. Does it depends on transaction isolation level?

Session terminated = Lock removed

If you remove the for update part, then the second statement would be able to read the data at the same time. As soon as you are expecting to update something, then you will have a ROW EXCLUSIVE lock.

  The commands UPDATE, DELETE, and INSERT acquire this lock mode on the target table (in addition to ACCESS SHARE locks on any other referenced tables). In general, this lock mode will be acquired by any command that modifies data in a table.

and

  Conflicts with the SHARE, SHARE ROW EXCLUSIVE, EXCLUSIVE, and ACCESS EXCLUSIVE lock modes.

Reference: 13.3. Explicit Locking (PostgreSQL 9.4 Documentation)

Further Reading/Reference Material

PostgreSQL Concurrency Model

  PostgreSQL provides a rich set of tools for developers to manage concurrent access to data. Internally, data consistency is maintained by using a multiversion model (Multiversion Concurrency Control, MVCC). This means that each SQL statement sees a snapshot of data (a database version) as it was some time ago, regardless of the current state of the underlying data. This prevents statements from viewing inconsistent data produced by concurrent transactions performing updates on the same data rows, providing transaction isolation for each database session. MVCC, by eschewing the locking methodologies of traditional database systems, minimizes lock contention in order to allow for reasonable performance in multiuser environments.

Reference: Chapter 13. Concurrency Control - 13.1. Introduction (PostgreSQL 9.4 Documentation)

PostgreSQL JDBC Driver

  PostgreSQL™ provides a type 4 JDBC driver. Type 4 indicates that the driver is written in Pure Java, and communicates in the database system's own network protocol. Because of this, the driver is platform independent; once compiled, the driver can be used on any system.

Reference: The PostgreSQL JDBC Interface- Chapter 1. Introduction (PostgreSQL JDBC Driver Documentation)


Installing Oracle 11g on Ubuntu 11.10 Does anyone know to how to install Oracle 11g on Ubuntu 11.10?

AskUbuntu.com has this question: How to install Oracle 11g?.

Oracle Database 11g and Ubuntu 11.10 Oneiric Ocelot has some good information.

Here are some obligatory links to the relevant 11g pdf documentation.

Database Installation Guide for Linux
Database Quick Installation Guide for Linux x86
Database Quick Installation Guide for Linux x86-64
Release Notes for Linux


SQL perf issue - configure server correctly first or troubleshoot issues one by one? This is for SQL Server 2012.

I am  troubleshooting overall slow performance on a Dynamics AX environment. Everything is just slow - app itself, reports, ad-hoc, etc.  Right off the bat, I can see that none of the best practices for SQL Server have been configured - MAXDOP, tempdb, trace flags specific for Dynamics, and index/fragmentation maintenance.  I am pretty sure there are inefficient queries, missing or non-optimal indexes, etc.

I spent a little bit of time looking at wait stats, but now I'm wondering if I should first apply all the recommended best practices (industry-standard best practices for DynamicsAX), then troubleshoot what is slow, or dig into each issue one by one, and address it as it comes?  I know some will say, don't start flipping switches until you know for sure it needs to be flipped.

How would you approach this kind of scenario?

There are a couple of things to say about this, Dynamics AX performance is not necessarily always caused by SQL Server slowness, especially if you say "everything is slow".

When faced with performance issues in Dynamics AX I tend to use the following approach:

First start with reviewing resources on all servers involved (cpu pressure, memory pressure, page life expectancy,...)

Every AOS, Client & Citrix need at least 4 cores
AX is very sensitive to CPU latency, make sure your CPU's are set to high performance in the BIOS. If you say everything is slow, even simple forms my gut feeling says this isn't the case.
Perform the standard  checks such as disk latency and the likes, not only on the SQL Server but on all components involved.

Then verify if all the SQL Server settings are correct and according to best practices

Correct trace flags
Correct index/stats maintenance
MaxDOP
max memory settings
...

Review all the relevant database properties if they are set according to best practices

auto update statistics

Review the database design by reviewing missing indexes and slow queries

use known DMV queries (I tend to like the Glenn Berry scripts)
log dmv data using DynamicsPerf If you contact support this will be the first tool they'll have you install
log slow queries to the SQL Statement trace log so you can relate them to the call stack in code

review known issues in the application set up (number sequences, database log, ...)

look at the top 10 issues discovered by the Premier Field Engineers

Once you have a setup that works acceptably and some processes remain slow, use the Trace Parser to have exact timings on every piece of code and query that's being run during the process. This should help you either solve or at least explain why the remaining process is slow.


after deleting 100 millions of records in postgresql free space doesn't change Recently I configured netflow for monitoring traffic of my company. I use pmacct to collect netflow packets and PostgreSQL for storing them.
I only keep netflow records for a week and after that every day I delete all records that older than 7 days.
this script runs every day with cronjob:
date >> /root/log/cronlog

du -h /var/lib/postgresql/data/base/ >> /root/log/cronlog

psql -U pmacct -d pmacct_switch -c "DELETE FROM acct_v9 WHERE stamp_updated < NOW() - INTERVAL '7 days';" >> /root/log/cronlog
psql -U pmacct -d pmacct_switch -c "VACUUM;" >> /root/log/cronlog

du -h /var/lib/postgresql/data/base/ >> /root/log/cronlog

date >> /root/log/cronlog

and this is the cronlog file:
Wed Jul 8 01:00:02 +0430 20207.4M

/var/lib/postgresql/data/base/17.5M

/var/lib/postgresql/data/base/1299480G

/var/lib/postgresql/data/base/167654.0K

/var/lib/postgresql/data/base/pgsql_tmp7.4M

/var/lib/postgresql/data/base/129936.5M

/var/lib/postgresql/data/base/1638480G

/var/lib/postgresql/data/base/

DELETE 100424891
VACUUM

7.4M

/var/lib/postgresql/data/base/17.5M

/var/lib/postgresql/data/base/1299481G

/var/lib/postgresql/data/base/167654.0K

/var/lib/postgresql/data/base/pgsql_tmp7.4M

/var/lib/postgresql/data/base/129936.5M

/var/lib/postgresql/data/base/1638481G
/var/lib/postgresql/data/base/Wed Jul 8 02:29:33 +0430 2020

Ss you see after deleting 100 millions of records free space doesn't increase!
So, what's the solution?

The documentation describes that well:

The standard form of VACUUM removes dead row versions in tables and indexes and marks the space available for future reuse. However, it will not return the space to the operating system, except in the special case where one or more pages at the end of a table become entirely free and an exclusive table lock can be easily obtained. In contrast, VACUUM FULL actively compacts tables by writing a complete new version of the table file with no dead space. This minimizes the size of the table, but can take a long time. It also requires extra disk space for the new copy of the table, until the operation completes.

For an application where you have to delete lots of rows regularly, you might consider partitioning. If you can partition the table in a way so that you can drop partitions rather than running DELETE, you won't have no bloat problem any more.


What's the risk of keeping a table with row size > 8126 in MySQL 5.7? I just upgraded from MySQL 5.6 to 5.7, and had the bad surprise to discover that a WordPress plugin table contains a ridiculous 438 fields, and exceeds the row size limit of 8126 bytes.

Here is the warning I got when running mysql_upgrade:

  Row size too large (> 8126). Changing some columns to TEXT or BLOB or using ROW_FORMAT=DYNAMIC or ROW_FORMAT=COMPRESSED may help. In current row format, BLOB prefix of 768 bytes is stored inline.

This was just a warning though, and the table is still there. I can't create a new table with the same structure, but interestingly enough, I can keep the old table and use it (at least, SELECT from it).

I've read RolandoMySQLDBA's and Bill Karwin's answers on similar topics, and no solution can help me:

changing to ROW_FORMAT=DYNAMIC is not enough, as the table is full of inline UTF-8 VARCHAR fields that alone exceed the max size
modifying the structure of the table is out of the question, as it's part of a third-party software, and that would be like opening a can of worms

I've reported the error to the plugin developers and hope they'll fix the table soon, but in the meantime, I'm stuck with this outlaw table.

So the questions are:

How can MySQL work with a table that does not respect the InnoDB storage rules, when it does not allow to create such a table?
Is there any risk to keep the table in its current state?
Are there limitations on the type of queries I can run on this table?
If I replicate this server to another MySQL 5.7 server, will the replication fail because of this table?

I upgraded from MySQL 5.6. I did try DYNAMIC and COMPRESSED formats, but no luck. I calculated the size of the integer + varchar fields, they're totaling 17,059 bytes, so I have no chance to succeed this way.

The plugin is called Photo Gallery and the faulty table is wp_bwg_theme.

SET GLOBAL innodb_file_format=Barracuda;
SET GLOBAL innodb_file_per_table=ON;
ALTER TABLE tbl ROW_FORMAT=DYNAMIC;  -- Or COMPRESSED

That is what was needed in 5.6, I think.  Since Barracuda and file_per_table are defaults in 5.7, most of this goes away.  However, the upgrade may have left the table Antelope and/or not file_per_table.

So, I recommend throwing those 3 commands and see if it "fixes" your problem for good.  (Yeah, keep some kind of backup somewhere first.)

You mentioned utf8; perhaps you want utf8mb4, too?

ALTER TABLE tbl CONVERT TO utf8mb4;

(I don't now if replication will be a problem.  Presumably after the above changes it won't be a problem on a 5.7 Slave.)

Edit

But... Being the default (for 5.7) does not mean that you got those values for an existing file after an upgrade.

Look in information_schema.INNODB_SYS_TABLES to see what the file_format and row_format are.  The column space indicates file_per_table if the value is > 0.

A drastic way to deal with the problem:  Change the InnoDB page size.  The default is 16KB, but 32KB is possible.  If I am not mistaken, all tables on the system have to be converted to the new page size, so it is a non-trivial undertaking, and may have other ramifications.

The handling of VARCHAR in DYNAMIC/COMPRESSED

When is the string stored in the main part of the record, versus stored in some other block?

Small size (string is < 40 bytes):  The entire string is stored with the record.  2+N bytes.
Medium size:  Either 'small' or 'large', depending on "record not too big".
Large size: 2+20 bytes in record (length+pointer); entire text stored separately.

"record too big":  When the row is too long, the longest columns are stored in off-page storage.  That is, some times a column will be entirely off-page, sometimes on-page, depending on the makeup of the entire row.

VARCHAR and TEXT and BLOB are handled the same for DYNAMIC or COMPRESSED.

The DYNAMIC format is based on the idea that if a portion of a long data value is stored off-page, it is usually most efficient to store all of the value off-page ("all or none").

COMPRESSED = DYNAMIC + compression. KEY_BLOCK_SIZE controls how much column data is stored in the clustered index, and how much is placed on overflow pages.

5.7 reference and older, more detailed, discussion; and other pages.


Function that loops through array parameter values to build multiple WHERE clauses (postgres 11.4) I have been iterating on an idea presented by Rob Conery in his excellent post to generate monthly reports using PostgreSQL Views.
My version required the View to be refactored into a Function as I needed to utilize input parameters. I recently received a request to add filtering so that specific products and locations could be searched as well, but I found myself executing this function N number of times, which caused a significant performance bottleneck. I figured having these conditions in one query would alleviate these performance issues.
I made a bit of progress after following the (very well-written) answers to some questions here, but I am still stuck wrapping my head around how to generate  WHERE clauses for each input array element.
Essentially my desired "output" SQL would look something like this:
select sum(total) as total_activity,
  count(1) as sales_event_count,
  created_at::date as sales_event_date,
  date_part('year',created_at at time zone 'hst') as year,
  date_part('quarter',created_at at time zone 'hst') as quarter,
  date_part('month',created_at at time zone 'hst') as month,
  date_part('day',created_at at time zone 'hst') as day
from locations loc
left outer join sales_events se ON loc.id = se.location_id
left outer join junction_products jp ON jp.sales_event_id = se.id
left outer join products p ON p.id = jp.product_id
where (p.sku = '12345' and p.manufacturer = 'CompanyA' and location_id = 'LocationA') or
      (p.sku = '09876' and p.manufacturer = 'CompanyA' and location_id = 'LocationA') or
      (p.sku = '10293' and p.manufacturer = 'CompanyB' and location_id = 'LocationA')
group by se.created_at
order by se.created_at

Here are some example pages that I have explored to help tackle this problem:

Pass multiple sets or arrays of values to a function
How to select multiple values into an array and loop through? (postgres 9.3)
Selecting from multidimensional array parameter in Postgres

After picking and choosing from each of these, I have come up with the following:
create type product_type as(sku character varying(100), manufacturer character varying(200))

create or replace function find_sales_location_activity(
  _products_arr product_type[],
  _location_id bigint
)
returns table (total_activity bigint, sales_event_count bigint, sales_event_date date, "year" double precision, quarter double precision, "month" double precision, "day" double precision) as
$func$
select sum(total) as total_activity,
  count(1) as sales_event_count,
  created_at::date as sales_event_date,
  date_part('year',created_at at time zone 'hst') as year,
  date_part('quarter',created_at at time zone 'hst') as quarter,
  date_part('month',created_at at time zone 'hst') as month,
  date_part('day',created_at at time zone 'hst') as day
from locations loc
left outer join sales_events se ON loc.id = se.location_id
left outer join junction_products jp ON jp.sales_event_id = se.id
left outer join products p ON p.id = jp.product_id
where (p.sku = $1[1][1] and p.manufacturer = $1[1][2] and location_id = $2) or
      (p.sku = $1[2][1] and p.manufacturer = $1[2][2] and location_id = $2) or
      (p.sku = $1[3][1] and p.manufacturer = $1[3][2] and location_id = $2)
group by se.created_at
order by se.created_at
$func$
language sql;

...but obviously this isn't looping over anything. I have experimented with replacing the FROM locations loc clause with FROM generate_subscripts($1, 1) and attempting to loop through that way, but replacing the table name causes my left outer join's to fail.
Clearly I'm a bit out of my depths here, but I'd really, really appreciate it if anyone could lead me in the right direction. Thanks in advance!

The parameters in the array may be transformed into a table
to be be joined against the rest. This transformation is
done with the unnest function and the WHERE conditions can then be expressed as
JOIN clauses.
This could also be done by joining with generate_subscripts($1, 1) but that leads to more complicated syntax for no apparent benefit.
select ...<same as your query>...
from locations loc
left outer join sales_events se ON loc.id = se.location_id
left outer join junction_products jp ON jp.sales_event_id = se.id
left outer join products p ON p.id = jp.product_id
join unnest($1) params ON (p.sku=params.sku AND p.manufacturer=params.manufacturer)
WHERE location_id = $2
group by se.created_at
order by se.created_at

Note: this query leaves the left outer join as they are in the original query because the question is not about them, but it seems they should be inner joins instead.
The reason is that WHERE clauses like  p.sku = '12345' and p.manufacturer = 'CompanyA'
imply that these columns in p cannot be NULL, but that negates the reason why you'd use an outer join versus an inner join, which is having NULL columns added for non-matching rows from each table at the right side of each left join.
In short, if you're not sure why this query uses left outer join, consider replacing them  with join.


How to get all connected values as a single column I'm trying to use CTE recursion.
This is my scenario SQL Fiddle.
It is a simple tree structure:

CREATE TABLE [dbo].[AL](
    [IdAL] [int] IDENTITY(1,1) NOT NULL,
    [ID1] [int] NOT NULL,
    [ID2] [int] NOT NULL,
 CONSTRAINT [PK_AL] PRIMARY KEY CLUSTERED
    (
      [IdAL] ASC
    ) WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF,
            ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY] ;

INSERT INTO AL (ID1, ID2)
VALUES (1092, 284917),
       (1092, 248957),
       (1092, 789475),
       (   1, 789475),
       (   1,      2),
       (   3,      2),
       (   6,      4) ;

Simply put, the user gives me an id (e.g. 1092). I would recursively run all rows in the table and do the merge of the id (ID1 + ID2) starting with the id provided by the user.

I would like to get something like this (in just one column called ID):

ID
1092,
284917,
248957,
789475,
1,
2,
3

Note that id 4 and 6 are not in the result list because there is no node that points to these ids.

this is my solution. As I mentioned in a previous comment, it's not the prettiest solution stylistically, but it works.

DECLARE @Id int = 1092

DECLARE @i                      INT = 0,
    @IdToProcess    INT = 0

DECLARE @t TABLE    (   Id  INT,
                    Processed   BIT
                )

INSERT INTO @t (Id, Processed)
VALUES (@Id, 0)

WHILE   (1 = 1 OR @i <= 100)
BEGIN
 SET @i = @i +1
SET @IdToProcess = 0

SELECT  @IdToProcess = MIN(Id)
FROM    @t
WHERE   Processed = 0

SET @IdToProcess = ISNULL(@IdToProcess, 0)

IF @IdToProcess <= 0
BEGIN
    BREAK
END

INSERT INTO @t (Id, Processed)
SELECT  AL.Id2, 0
FROM    dbo.AL AL
WHERE   AL.Id1 = @IdToProcess
        AND NOT EXISTS (SELECT 1 FROM @t T WHERE T.Id = AL.Id2)
UNION
SELECT  AL.Id1, 0
FROM    dbo.AL AL
WHERE   AL.Id2 = @IdToProcess
        AND NOT EXISTS (SELECT 1 FROM @t T WHERE T.Id = AL.Id1)

UPDATE  @t
SET     Processed = 1
WHERE   Id = @IdToProcess
END

SELECT * FROM @T


Including count of a result in the main query? Currently we execute two queries to get the count and result with a pagination filter. While we can easily combine both of these into a single network call (using sql seprators), is there a way to do this in a single query following the DRY principle?

-- count
SELECT COUNT(*) FROM table;

-- result with pagination
SELECT *
FROM (SELECT ROW_NUMBER() OVER (ORDER BY tbl.idn) AS row, * FROM tbl)_tbl
WHERE row >= 1 AND row <= 10;

It is really challenging to maintain both queries - single query reduces the maintenance overhead.

You can try create a stored procedure and call it from the application.

CREATE PROCEDURE [dbo].[getPage]
AS
BEGIN
    DECLARE @cnt INT = (SELECT COUNT(*) FROM table)

    SELECT *, [cnt] = @cnt
    FROM (SELECT ROW_NUMBER() OVER (ORDER BY tbl.idn) AS row, * FROM tbl)_tbl
    WHERE row >= 1 AND row <= 10;
END

Or you can use subquery:

CREATE PROCEDURE [dbo].[getPage]
AS
BEGIN
    SELECT *, [cnt] = (SELECT COUNT(*) FROM table)
    FROM (SELECT ROW_NUMBER() OVER (ORDER BY tbl.idn) AS row, * FROM tbl)_tbl
    WHERE row >= 1 AND row <= 10;
END

Edit.

Even better you can use OFFSET FETCH clause for paging puprose. You will get much cleaner code and higher performance (no window function here). For example somthing like this:

CREATE PROCEDURE [dbo].[getPage]
AS
BEGIN
    DECLARE @cnt INT = (SELECT COUNT(*) FROM table)

    SELECT *, [cnt] = @cnt
    FROM tbl
    ORDER BY idn
    OFFSET 0 ROWS FETCH NEXT 10 ROWS ONLY;
END

Or with subquery:

CREATE PROCEDURE [dbo].[getPage]
AS
BEGIN
    SELECT *, [cnt] = (SELECT COUNT(*) FROM table)
    FROM tbl
    ORDER BY idn
    OFFSET 0 ROWS FETCH NEXT 10 ROWS ONLY;
END

There is no significant diffrence between a subquery and a variable approach. It's more about the notation. SQL Server does almost the same work (additional "nocost" Nested Loop). In this particular sitiation I prefer to avoid window function (possible Table Spool - additional impact on tempdb).


sqlite - Distinguish a column with the same value by range of id and count this range, take the higher one I have a small sqlite table

CREATE TABLE IF NOT EXISTS intersection (
    cnt NUMERIC NOT NULL,
    sweep NUMERIC)

from what i want to get the maximum value of "cnt" and later on the average of "sweep" depending on the range/amount of distinct "cnt"-values.

Since sqlite only has this rowid option i use it within my query:

SELECT rowid, cnt, sweep FROM intersection
WHERE cnt IN (SELECT MAX(cnt) FROM intersection)

This returns:

+--------+------+-------+
| rowid  | cnt  | sweep |
| 106    | 7    | 21.07 |
| 107    | 7    | 21.17 |
| 108    | 7    | 21.27 |
| 109    | 7    | 21.37 |
| 110    | 7    | 21.47 |
| 111    | 7    | 21.57 |
| 112    | 7    | 21.67 |
| 113    | 7    | 21.78 |---split here
| 152    | 7    | 25.7  |
| 153    | 7    | 25.8  |
| 154    | 7    | 25.9  |
| 155    | 7    | 26    |
| 156    | 7    | 26.1  |
| 157    | 7    | 26.2  |
| 158    | 7    | 26.31 |
| 159    | 7    | 26.41 |
| 160    | 7    | 26.51 |
| 161    | 7    | 26.61 |
+--------+------+-------+

I need both "cnt"-ranges but the higher count of 7s (from id 152 downwards) first. So i would have to split this result according to the gap in id and proceed with my calculations.

I googled a lot and also looked here on stackexchange, this is one partial solution i found so far

SELECT f.rowid, f.cnt, f.sweep FROM intersection f
    WHERE cnt IN (SELECT MAX(cnt) FROM intersection)
    AND NOT EXISTS (
        SELECT * FROM intersection s
        WHERE s.cnt = f.cnt
        AND s.rowid = f.rowid+1)--or -1

It returns, depending on +1 or -1, the lower or upper boundary of id but with that i can't compute the average of sweep for the given range.

  Finally i need a query that looks for the highest "cnt"-value, the highest count of one range of (in this case 7s) split by the gap between the id's of these two groups.

I have no idea how to get there since i'm not that experienced but willing to learn a lot, so a little advice would be greatly appreciated!

Wanted output:

+-------+-----------+
| count | avg_sweep |
| 8     | 21.42     |
| 10    | 26.15     |
+-------+-----------+

For each lower boundary, search the matching upper boundary:

WITH uppers(rowid) AS (
  SELECT f.rowid FROM intersection f
  WHERE cnt = (SELECT MAX(cnt) FROM intersection)
  AND NOT EXISTS (
      SELECT * FROM intersection s
      WHERE s.cnt = f.cnt
      AND s.rowid = f.rowid+1)
)
SELECT f.rowid AS lb,
       (SELECT u.rowid FROM uppers u
        WHERE u.rowid >= f.rowid
        ORDER BY u.rowid ASC LIMIT 1
       ) ub
FROM intersection f
WHERE cnt = (SELECT MAX(cnt) FROM intersection)
AND NOT EXISTS (
    SELECT * FROM intersection s
    WHERE s.cnt = f.cnt
    AND s.rowid = f.rowid-1);

lb  ub
--- ---
106 113
152 161

These values can then be used for BETWEEN:

WITH uppers(rowid) AS (
  SELECT f.rowid FROM intersection f
  WHERE cnt = (SELECT MAX(cnt) FROM intersection)
  AND NOT EXISTS (
      SELECT * FROM intersection s
      WHERE s.cnt = f.cnt
      AND s.rowid = f.rowid+1)
),
bounds(lb, ub) AS (
  SELECT f.rowid,
         (SELECT u.rowid FROM uppers u
          WHERE u.rowid >= f.rowid
          ORDER BY u.rowid ASC LIMIT 1)
  FROM intersection f
  WHERE cnt = (SELECT MAX(cnt) FROM intersection)
  AND NOT EXISTS (
      SELECT * FROM intersection s
      WHERE s.cnt = f.cnt
      AND s.rowid = f.rowid-1)
)
SELECT (SELECT count(*) FROM intersection
        WHERE rowid BETWEEN bounds.lb AND bounds.ub
       ) AS count,
       (SELECT avg(sweep) FROM intersection
        WHERE rowid BETWEEN bounds.lb AND bounds.ub
       ) AS avg_sweep
FROM bounds;


Get the windows account which runs the query? I'm using sql server 2008 r2.

I logged in to ssms which in turn connects to a remote sql server machine.

im writing a query which writes a file.

I need to know - which windows permissions I should grant to c:\myfolder

is there any select query which can provide me the windows account who is actually finally writes  the file ?

It depends, and I'm afraid Martin give you only part of the answer. The credential used to access the file is different whether you logged in with SQL login or a Windows login.

For SQL logins (ie. user and password based connection string) the credential used is either the SQL server process account of the login proxy credential account, if one is set up:

  The information stored in a credential enables a user who has
  connected to SQL Server by way of SQL Server Authentication to access
  resources outside the server instance. When the external resource is
  Windows, the user is authenticated as the Windows user specified in
  the credential.

For Windows logins (ie. Integrated Security or SSPI connection string) the engine will always impersonate the caller before accessing outside resources. Which implies that the access to the shared folder will occur under the impersonated context of the original Windows login. Which in turns implies 'double-hop' delegation will occur and the authentication with the shared file will be subject to the domain policies on constraining delegation. And all domains that give 1 cent on security will have constrained delegation restriction in place and will require you to set up the SQL Server service account to be enabled for constrained delegation.


Use a Derived Table/CTE to get a single ID for "approved"/"not approved" and LEFT JOIN it two times to Employees:

SELECT TaskID
   ,MAX(CASE WHEN IsApprove = 1 THEN EmployeePermission end) AS Approved_ID
   ,MIN(CASE WHEN IsApprove = 0 THEN EmployeePermission end) AS Not_Approved_ID
FROM "Employee Approval"
GROUP BY TaskID


Redshift table occupies exponentially more disk space in production cluster Production Cluster details:

Node Type dc1.8xlarge
Nodes 25
2.56TB SSD storage per node

Test Cluster details:

Node Type ds2.xlarge
Nodes 6
2TB HDD storage per node

When same table with exactly same DDL & encoding is unloaded and copied from production cluster to test cluster, its disk footprint reduces exponentially. This has been tested with multiple tables with different distribution styles and sort key patterns.

Example:
Table A (No sort key, DISTSYLE EVEN) - Size in production: 60GB; Size in test: 0.6 GB

Table B (Sort key, DISTSTYLE KEY) - Size in production: 96GB 100% sorted; Size in test: 1.4 GB 100% sorted

Any ideas what can result in this discrepancy? I have read most of redshift forums but not able to find a reason for this issue. I am using the admin view v_space_used_per_tbl (provided by AWS) for calculating size of the table.

Found this in AWS documentation, which describes this behavior clearly.

The default amount of disk storage space allocated to two tables residing on different clusters can vary significantly, even when the tables are created using identical data definition language (DDL) statements and contain the same number of rows.

Because the block size in RedShift is 1MB, all columns will take up 1MB per column at a minimum. On top of this if the DISTSTYLE is EVEN it will be closer to one block per slice in the database. Since there is no way to tweak the block size in RedShift there is no way to reduce the size of an empty table since the table's size follows below formula:

For tables created using the KEY or EVEN distribution style:

Minimum Table Size = block_size (1MB) * (number_of_user_columns + 3 system columns) * number_of_populated_slices * number_of_table_segments.

For tables created using the ALL distribution style:

Minimum Table Size = block_size (1MB) * (number_of_user_columns + 3 system columns) * number_of_cluster_nodes * number_of_table_segments

https://aws.amazon.com/premiumsupport/knowledge-center/redshift-cluster-storage-space/