Your description results in a table definition like this:

CREATE TABLE tbl (
   lap_id   serial PRIMARY KEY
 , lap_no   int NOT NULL
 , car_type enum NOT NULL
 , race_id  int NOT NULL  -- REFERENCES ...
 , UNIQUE(race_id, car_type, lap_no)
);

General solution for this class of problems

To get the longest sequence (1 result, longest of all, arbitrary pick if there are ties):

SELECT race_id, car_type, count(*) AS seq_len
FROM  (
   SELECT *, count(*) FILTER (WHERE step)
                      OVER (ORDER BY race_id, car_type, lap_no) AS grp
   FROM  (
      SELECT *, (lag(lap_no) OVER (PARTITION BY race_id, car_type ORDER BY lap_no) + 1)
                 IS DISTINCT FROM lap_no AS step
      FROM   tbl
      ) x
   ) y
GROUP  BY race_id, car_type, grp
ORDER  BY seq_len DESC
LIMIT  1;

count(*) FILTER (WHERE step) only counts TRUE (= step to next group), which results in a new number for every new group.

Related question on SO, one answer featuring a procedural solution with plpgsql:

GROUP BY and aggregate sequential numeric values

If the top requirement is performance, the plpgsql function is typically faster in this particular case because it can calculate the result in a single scan.

Faster for consecutive numbers

We can capitalize on the fact that consecutive lap_no define a sequence, for a much simpler and faster version:

SELECT race_id, car_type, count(*) AS seq_len
FROM  (
   SELECT race_id, car_type
        , row_number() OVER (PARTITION BY race_id, car_type ORDER BY lap_no) - lap_no AS grp
   FROM   tbl
   ) x
GROUP  BY race_id, car_type, grp
ORDER  BY seq_len DESC
LIMIT  1;

Consecutive laps end up in the same grp. Every missing lap results in a lower grp per partition.

This relies on (race_id, car_type, lap_no) being UNIQUE NOT NULL. NULL values or duplicates could break the logic.

Discussion of Jack's simpler alternative

@Jack's version effectively counts all laps (rows) where the previous lap_no in this race_id had the same car_type. That's simpler and faster and correct - as long as each car_type can only have one sequence per race_id.

But for a task that simple the query could be simpler, yet. It would follow logically that all lap_no per (car_type, race_id) must be in sequence, and we could just count the laps:

SELECT race_id, car_type, count(*) AS seq_len
FROM   tbl
GROUP  BY race_id, car_type
ORDER  BY seq_len DESC
LIMIT  1;

If, on the other hand, one car_type can have multiple separate sequences per race_id (and the question does not specify otherwise), Jack's version will fail.

Faster for a given race / car type

In reply to the comment / clarifications in the question: restricting the query to one given (race_id, car_type) will make it much faster, of course:

SELECT count(*) AS seq_len
FROM  (
   SELECT row_number() OVER (ORDER BY lap_no) - lap_no AS grp
   FROM   tbl
   WHERE  race_id = 1
   AND    car_type = 'red'
   ) x
GROUP  BY grp
ORDER  BY seq_len DESC
LIMIT  1;

db<>fiddle here
Old SQL Fiddle

Index

Key to top performance is a fitting index (except for the mentioned procedural solution working with a single sequential scan). A multicolumn index like this serves best:

CREATE INDEX tbl_mult_idx ON tbl (race_id, car_type, lap_no);

If your table has the UNIQUE constraint I assumed at the top, that is implemented with just this (unique) index internally, and you do not need to create another index.


Composite index performance with varchar columns

While some RDBMSs might be tuned to interact better with fixed-length strings (I seem to recall that maybe DB2/MVS did better with fixed-length strings, but I learned this in 1996 and it was mainframe DB2, so not sure if that even applied to DB2 on Unix or Windows), I would be shocked if using CHAR(50) did anything but decrease performance as compared to VARCHAR(50) given that most values are only using 20-30 bytes. Using CHAR(50) takes up more space, which reduces the number of rows that fit on the data page, which increases the number of data pages, which takes up more space in the buffer pool (i.e. RAM) when querying the data, plus takes longer to backup and restore. AND, if I am not mistaken, it will also increase the memory grants (i.e. RAM requested per query) as it will request 50 bytes per field instead of 25 (I believe it requests 50% the max size of variable length fields). For a more thorough look at the down-stream effects of using more space than you need, please see: Disk Is Cheap! ORLY?.

That being said, between VARCHAR(50) and CHAR(50), I would choose: neither ! Given the names of the columns, I am guessing that these are actually dimensions? Filtering on binary/numeric values is much faster than filtering on strings (there is one exception that I will mention in a moment). Depending on how many potential values there will be over time for clients, customers, and items, I would use either INT or, if truly needed, then BIGINT as the keys for the Client, Customer, and Item dimensions, and store those numeric key values here.

At bare minimum, I would change the collation on the VARCHAR columns to be Latin1_General_100_BIN2 since you clearly aren't needing case or accent insensitivity, else the idea of using a SHA-256 hash would not even be an option. That should at least be able to filter almost as fast as an INT or BIGINT (I say "almost" only due to, again, the effect of storing 20 - 30 byte values for 3 columns instead of 4 or 8 byte values for those same 3 columns, when accounting for millions/billions of rows).

I would strongly advise against using any type of hash value here. Not only would you be bloating the table (i.e. decreasing performance), but you would then lose any meaning of those values, and would hence either need to also store the non-hashed values for those 3 columns in the fact table (i.e. crazy) or require a lookup back to the dimension table (in which case you gained nothing over using INT or BIGINT at 4 or 8 bytes plus no calculation overhead!).


PostgreSQL: translating user-defined calculations into executable calculation in trigger

Essentially, you're asking how to implement a DSL  that generates dynamic SQL based on that proprietary non-spec'd query string '[bp2][-1]/[bp5]'. That's a massive task. I'm not going to be able to answer this question on this medium, it's far from a Q/A: "it's like how do I do this job." That said, this is how it would look.

Create a specification of what kind of features you intend to support with '[bp2][-1]/[bp5]' and '([bp1]/[wc66])*[wc100]', and the numerous other examples you've given in your questions.
Create a parser. This is an art in and of itself. You going to use regexes, a tree parser, a sax parser? What exactly do you need to meet the specification?
Use an off-the-shelf solution that can translate the parsed structure to SQL. Most languages have something that can do this, like SQL::Abstract. If not, you gotta create it.


Using `nextval` and `currval` inside one CREATE TABLE

The code you have posted works OK, and is guaranteed to insert the same IDs.

The other way of designing this is to have a separate table that stores the hierarchical relationships, but you don't really need to do so unless you have many:1 or many:many relationships.


RDBMS: Proper way to store data - comma separated variables or in different fields or tables?

Why storing data as a string a problem :

Storing multiple datums as (in this case comma separated) strings is bad practice because:

- First reason:

It breaches Codd's second rule (called the "Guaranteed Access Rule") which states that Each and every datum (atomic value) in a relational data base is guaranteed to be logically accessible by resorting to a combination of table name, primary key value and column name.

So, if you wish to refer to user02's USB lineItem, you have to do further processing beyond simply knowing the table name, the PRIMARY KEY and the column name.

From here: Among the conventional database models, the Relational Model of data has a simple, sound mathematical foundation based on the notions of set theory. And from the wiki on the Relational Model (RM) we have:

  The relational model was the first database model to be described in
  formal mathematical terms. Hierarchical and network databases existed
  before relational databases, but their specifications were relatively
  informal. After the relational model was defined, there were many
  attempts to compare and contrast the different models, and this led to
  the emergence of more rigorous descriptions of the earlier models;
  though the procedural nature of the data manipulation interfaces for
  hierarchical and network databases limited the scope for
  formalization.

So, basically, the only data model with a sound mathematical foundation is the relational one. Most relational databases use the SQL data definition and query language; these systems implement what can be regarded as an engineering approximation to the relational model. [ibid].

Codd derived his rules as a guide to a practical implementation of his relational calculus - given that it is the only model with a sound mathematical foundation, it seems that it would be a bad idea to breach any of them.

Caveat: now, if for example, you will NEVER EVER want to break out the line items into their individual components, then storing it as one "unit" would be acceptable, but I can see many instances of where you would want to split it up into its component parts (see the fifth reason below).

An example of where you might want to store data in .csv form might be storing somebody's name and title for an academic journal - it might be stored thus:

Citizen, Seán B., Prof.

and that's the only way you're ever going to print/process/transmit/store this information, then it is a datum, not comma separated variables - datum or data is very much a contextual concept.

- Second reason:

As mentioned in comments, your lineItem table isn't even in first normal form (see the chart here - Atomic columns (cells have single value). This is obviously related to the point above. Database normalisation is

  the process of structuring a relational database in accordance with a
  series of so-called normal forms in order to reduce data redundancy
  and improve data integrity.

These "forms" flowed from the RM/Relational Calculus and Codd's rules as a way of ensuring that data remains consistent, which is obviously of paramount importance in any database system - in simple terms, it's how we ensure that the definitive archetype of a given datum is stored in one place and one place only.

- Third reason:

You have no way of controlling what data is entered into that field - i.e. you have no way of controlling Declarative Referential Integrity (DRI). This means, for example, that there's nothing to stop you referring to products which don't exist (say, DVDx).

DRI is one of the most important benefits of using the RM - it means that internal data consistency can be maintained, the benefits of which you will greatly appreciate if you've ever you've had the misfortune to have worked with a system where this has broken down.

In point two, we said that Normal Forms were to ensure that the definitive archetype of a given datum is stored in one place and one place only - DRI ensures that all other references to that datum point to that one place and nowhere else.

- Fourth reason:

SQL is not designed for parsing strings - it can be done, it's just messy, time-consuming and error-prone. Many proprietary extensions have been developed by the various RDBMS providers to try and overcome this lacuna, but it's still far easier to deal with properly normalised tables (see the SQL below).

- Fifth reason:

Apart from the "theoretical" (more or less) reasons for not doing this, is the massive PRACTICAL problem of not being able to assign individual quantities and prices to items under your schema - suppose I'm doing my Christmas shopping and I wanted the new "U2 CD" for 3 of my friends who are U2 fanatics? No way of telling the system that there are 3 U2 CD's other than having a field value like this:

'"U2 CD", "U2 CD", "U2 CD" "UB40 CD", "U2 DVD", "Kingston USB 32GB"' -- note repetition of "U2 CD".

Suppose you want to know the number of USB's sold? The number of USB's per client? The number per client district/area/country - depending on the scale of your operation (see SQL below)? Suppose I want to know how much was spent on USB drives last week - absolutely no way of getting any of that information! The list goes on...

How to deal with the problem:

So, having dealt with the first part of your question, we can now progress to the second part - Should the lineItem values be linked to relational values in another table instead maybe?.

- First solution (extra fields):

Here is a further example of the problems associated with storing strings. In this case, adding fields to a given record is the solution - i.e. splitting the string into its component parts and making each one a field! Very helpful for enforcing DRI and controlling data correctness if one has reference tables for (in this case) ZIP codes, street names &c...

- Second solution (extra records - 1-to-many relationships):

In this particular case of your question, what we have here is a classic 1-many relationship - also known as parent-child where receipt is the parent and line_item is the child.

Your table structure is this:

CREATE TABLE line_item
(
  lineItem VARCHAR(2000),  -- could have a many items - need a very long string - parsing a nightmare!
  receiptID INTEGER,       -- "001" could be a string - MySQL has a zero-fill function
  customeID VARCHAR(20)     -- redundant - don't need to store it for every line_item - it corresponds to a receipt (1 customer/receipt), not a line_item!
);

What you should have is something like this (see the fiddle here - all data and tables are also given at the bottom of this answer):

CREATE TABLE line_item
(
  receipt_id INTEGER NOT NULL,
  item_id INTEGER NOT NULL,
  item_qty INTEGER NOT NULL,
  CONSTRAINT line_item_pk PRIMARY KEY (receipt_id, item_id),
  CONSTRAINT li_item_fk FOREIGN KEY (item_id) REFERENCES item (item_id),
  CONSTRAINT li_receipt_fk FOREIGN KEY (receipt_id) REFERENCES receipt (receipt_id)
);

and your data will (rather cryptically) look like this:

INSERT INTO line_item VALUES
(1, 1, 1), (1, 4, 1), (2, 2, 1), (2, 3, 1), (2, 5, 1);

The receipt_id fields and the item_id fields point to the PRIMARY KEYs of their respective tables - and there is no redundant, extraneous information in the table - no customer_id stored multiple times for example! This way of modelling allows one to write queries of the form:

SELECT
  c.customer_id, c.customer_name, c.customer_address_1,
  i.item_desc, i.item_price,
  r.receipt_id,
  li.item_id, li.item_qty
FROM
  customer c
JOIN receipt r
  ON c.customer_id = r.customer_id
JOIN line_item li
  ON r.receipt_id = li.receipt_id
JOIN item i
  ON li.item_id = i.item_id;

Result:

customer_id  customer_name  customer_address_1  item_desc          item_price   receipt_id  item_id     item_qty
1            Bill Gates     Redmond             Michael Jackson CD      1.50              1     1          1
1            Bill Gates     Redmond             U2 DVD                   5.00             1     4          1
2            Larry Ellison  Redwood Shores      U2 CD                    2.00             2     2          1
2            Larry Ellison  Redwood Shores      UB40 CD                 4.00              2     3          1
2            Larry Ellison  Redwood Shores      Kingston USB 32GB       25.00             2     5          1

See the fiddle (or below) for the all the DDL and DML! I challenge you to do this trivially with a .csv string containing your line_item products - especially in MySQL! It probably would be feasible in PostgreSQL using something like array_to_table after feeding the string into an array but I leave that as an exercise for you!

So, for a 1-many relationship, you add items to your line_item table - one item for each element in your .csv string - 1 receipt parent record can have 1 to many (possibly a very large number) of line_item children.

Now, the item table is also a parent of line_item and in its case, there can be 0 to many children if, for example, if an item hasn't sold at all there will be no references to it in the line_item table.

- Third solution (extra tables - many-to-many relationships):

There is an appropriate circumstance when "values SHOULD be linked to relational values in another table" (as you hint at in your question) and this is when there is an m-to-n relationship - otherwise know as a many-to-many relationship.

Consider the old favourite Databases-101 example of students and courses and the many courses taken by many students! See the fiddle here - I haven't populated the tables this time. I've used PostgreSQL for the fiddle (my favourite server) but a bit of tweaking will get it to work on any reasonable RDBMS.

Create tables course and student:

CREATE TABLE course
(
  course_id SERIAL,  -- INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY in MySQL dialect
  course_year SMALLINT NOT NULL,
  course_name VARCHAR (100) NOT NULL,
  CONSTRAINT course_pk PRIMARY KEY (course_id)
);

CREATE TABLE student
(
  student_id SERIAL,
  student_name VARCHAR (50),
  CONSTRAINT student_pk PRIMARY KEY (student_id)
);

This is where a JOINing table (aka linking table(more formally known as an [Associative Entity`]13 - as an aside, there are 17 different names for this type of table on that page) comes in.

a given student can take many courses
a given course can have many students

So, you deal with this by creating the Associative Entity - your JOINing table:

CREATE TABLE registration
(
  reg_course_id INTEGER NOT NULL,
  reg_student_id INTEGER NOT NULL,

  CONSTRAINT reg_course_fk FOREIGN KEY (reg_course_id) REFERENCES course (course_id),
  CONSTRAINT reg_student_fk FOREIGN KEY (reg_student_id) REFERENCES student (student_id)
);

I then add a PRIMARY KEY - I kept it out of the table definition to illustrate the point, but it could (and normally would) be part of the table creation DDL.

ALTER TABLE registration
ADD CONSTRAINT registration_pk
PRIMARY KEY (reg_course_id, reg_student_id);

So now,

a given student can only be enroll in a given course once and
a given course can only have the same student enrolled once

There are many other situations where this construct is useful - basically, it's the only way to meaningfully model many real-life situations.

An example from my own career:

Think of a flight table containing a flight_id field, a list of departure and arrival airports and the relevant times and then also a crew table with crew members and a crew_id field (and other details obviously).

Having the flight_id and crew_id fields in a JOINing table proved to be very useful for the system - it really helped with scheduling and rostering which was a mess with the other system - constant conflicts in both. It takes time and experience to recognise when which schema design is suitable for which scenario, but 1-many (extra records in existing table) and many-many (extra JOINing table) is a good rule of thumb!

p.s. welcome to the forum!

_____________ Full DDL and DML _______________

Customer table:

CREATE TABLE customer  -- storing the customer_id on every line item is redundant - check out 3rd normal form
(
  customer_id INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY,
  customer_name VARCHAR (100) NOT NULL,
  customer_address_1 VARCHAR (100) NOT NULL -- can have address_1..n
  --
  -- other fields of particular interest to you
  --
);

Customer data:

INSERT INTO customer (customer_name, customer_address_1) VALUES
('Bill Gates', 'Redmond'), ('Larry Ellison', 'Redwood Shores');

item table:

CREATE TABLE item
(
  item_id INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY,
  item_code VARCHAR (25) NOT NULL UNIQUE,
  item_desc VARCHAR (200) NOT NULL,
  item_price DECIMAL(10, 2),
  item_supplier INTEGER NOT NULL -- refers to supplier table - not shown!
  --
  -- other fields of interest to you
  --
);

item data:

INSERT INTO item (item_code, item_desc, item_price, item_supplier) VALUES
('code_1', 'Michael Jackson CD', 1.5, 56), ('code_2', 'U2 CD', 2, 78), ('code_3', 'UB40 CD', 4, 67),
('code_4', 'U2 DVD', 5, 78), ('code_5', 'Kingston USB 32GB', 25, 23);

receipt table:

CREATE TABLE receipt  -- often called "orders" but receipt is OK
(
  receipt_id INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY,
  customer_id INTEGER NOT NULL,  -- refer to customer table - see below

receipt_total DECIMAL(10, 2),  -- kept updated by a trigger (not shown)
                                 -- can be calculated on the fly or
                                 -- possibly a generated field
  receipt_dt TIMESTAMP NOT NULL,  -- date and time of sale
  receipt_asst INTEGER,  -- refers to the sales assistant table - not shown
  CONSTRAINT rec_cust_fk FOREIGN KEY (customer_id) REFERENCES customer (customer_id)
);

receipt data:

INSERT INTO receipt (customer_id, receipt_total, receipt_dt, receipt_asst)
VALUES
(1, 6.5, '2020-06-03 15:23:45.123', 34),
(2, 31 , '2020-06-05 10:54:23.123', 17);

line_item table:

CREATE TABLE line_item
(
  receipt_id INTEGER NOT NULL,
  item_id INTEGER NOT NULL,
  item_qty INTEGER NOT NULL,
  CONSTRAINT line_item_pk PRIMARY KEY (receipt_id, item_id),
  CONSTRAINT li_item_fk FOREIGN KEY (item_id) REFERENCES item (item_id),
  CONSTRAINT li_receipt_fk FOREIGN KEY (receipt_id) REFERENCES receipt (receipt_id)
);

line_item data:

INSERT INTO line_item VALUES
(1, 1, 1), (1, 4, 1), (2, 2, 1), (2, 3, 1), (2, 5, 1);

Query:

SELECT
  c.customer_id, c.customer_name, c.customer_address_1,
  i.item_desc, i.item_price,
  r.receipt_id,
  li.item_id, li.item_qty
FROM
  customer c
JOIN receipt r
  ON c.customer_id = r.customer_id
JOIN line_item li
  ON r.receipt_id = li.receipt_id
JOIN item i
  ON li.item_id = i.item_id;

Result:

customer_id  customer_name  customer_address_1  item_desc          item_price   receipt_id  item_id     item_qty
1            Bill Gates     Redmond             Michael Jackson CD      1.50              1     1          1
1            Bill Gates     Redmond             U2 DVD                   5.00             1     4          1
2            Larry Ellison  Redwood Shores      U2 CD                    2.00             2     2          1
2            Larry Ellison  Redwood Shores      UB40 CD                 4.00              2     3          1
2            Larry Ellison  Redwood Shores      Kingston USB 32GB       25.00             2     5          1


How to give SQL Server Job Owner permission to Proxy a Credential

Expand SQL Agent
Expand Proxies
Find your proxy, right-click on it and choose Properties
Go to the Principals tab
Add the NT AUTHORITY\SYSTEM account

This will give that account permission to use the proxy.


"Unexpected token" in a DB2 SQL statement that runs OK in Postgres

The problem is in the FOR READ ONLY statement. DB2 implicitly add's the same effect for all nested query's, and FOR READ ONLY makes it ambiguous. However, i dunno why i getting so strange error message.


update column based on the sort order of another query

Let's start with a table, put data in it, add a sort order column

USE test
DROP TABLE IF EXISTS ordered_names;
CREATE TABLE ordered_names
(
    id int not null auto_increment,
    firstname VARCHAR(20),
    lastname VARCHAR(20),
    PRIMARY KEY (id),
    KEY name_ndx (lastname,firstname)
);
INSERT INTO ordered_names (firstname,lastname) VALUES
('pamela','edwards'),('rolando','edwards'),('diamond','edwards');
ALTER TABLE ordered_names ADD COLUMN sort_order INT DEFAULT NULL;
SELECT * FROM ordered_names;

Let's actually run that:

mysql> USE test
Database changed
mysql> DROP TABLE IF EXISTS ordered_names;
Query OK, 0 rows affected (0.00 sec)

mysql> CREATE TABLE ordered_names
    -> (
    ->     id int not null auto_increment,
    ->     firstname VARCHAR(20),
    ->     lastname VARCHAR(20),
    ->     PRIMARY KEY (id),
    ->     KEY name_ndx (lastname,firstname)
    -> );
Query OK, 0 rows affected (0.04 sec)

mysql> INSERT INTO ordered_names (firstname,lastname) VALUES
    -> ('pamela','edwards'),('rolando','edwards'),('diamond','edwards');
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> ALTER TABLE ordered_names ADD COLUMN sort_order INT DEFAULT NULL;
Query OK, 3 rows affected (0.17 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> SELECT * FROM ordered_names;
+----+-----------+----------+------------+
| id | firstname | lastname | sort_order |
+----+-----------+----------+------------+
|  1 | pamela    | edwards  |       NULL |
|  2 | rolando   | edwards  |       NULL |
|  3 | diamond   | edwards  |       NULL |
+----+-----------+----------+------------+
3 rows in set (0.00 sec)

mysql>

Next, let's populate the sort order column:

SET @x = 0;
UPDATE ordered_names SET sort_order = (@x:=@x+1) ORDER BY lastname,firstname;
SELECT * FROM ordered_names;

Let's run that:

mysql> SET @x = 0;
Query OK, 0 rows affected (0.00 sec)

mysql> UPDATE ordered_names SET sort_order = (@x:=@x+1) ORDER BY lastname,firstname;
Query OK, 3 rows affected (0.00 sec)
Rows matched: 3  Changed: 3  Warnings: 0

mysql> SELECT * FROM ordered_names;
+----+-----------+----------+------------+
| id | firstname | lastname | sort_order |
+----+-----------+----------+------------+
|  1 | pamela    | edwards  |          2 |
|  2 | rolando   | edwards  |          3 |
|  3 | diamond   | edwards  |          1 |
+----+-----------+----------+------------+
3 rows in set (0.00 sec)

mysql>

OK, good so far, let's add four(4) more people and reorder:

INSERT INTO ordered_names (firstname,lastname) VALUES
('javonne','washington'),('richard','washington'),
('carlik','washington'),('dominique','edwards');
SET @x = 0;
UPDATE ordered_names SET sort_order = (@x:=@x+1) ORDER BY lastname,firstname;
SELECT * FROM ordered_names;

Let's run that:

mysql> INSERT INTO ordered_names (firstname,lastname) VALUES
    -> ('javonne','washington'),('richard','washington'),
    -> ('carlik','washington'),('dominique','edwards');
Query OK, 4 rows affected (0.00 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql> SET @x = 0;
Query OK, 0 rows affected (0.00 sec)

mysql> UPDATE ordered_names SET sort_order = (@x:=@x+1) ORDER BY lastname,firstname;
Query OK, 6 rows affected (0.00 sec)
Rows matched: 7  Changed: 6  Warnings: 0

mysql> SELECT * FROM ordered_names;
+----+-----------+------------+------------+
| id | firstname | lastname   | sort_order |
+----+-----------+------------+------------+
|  1 | pamela    | edwards    |          3 |
|  2 | rolando   | edwards    |          4 |
|  3 | diamond   | edwards    |          1 |
|  4 | javonne   | washington |          6 |
|  5 | richard   | washington |          7 |
|  6 | carlik    | washington |          5 |
|  7 | dominique | edwards    |          2 |
+----+-----------+------------+------------+
7 rows in set (0.00 sec)

mysql>

Give it a Try !!!


Why are constraints applied in the database rather than the code?

Because:

I want all the data in the database to be subject to the same constraints, not just the new data to be subject to the constraints in the version of the code that's running today.
I want declarative constraints, not programmatic constraints.
Data in the database often outlives the code that's written to interact with it today. And that data -- not the code -- is the organisation's asset.
My code becomes much simpler when I know that all data is subject to rigorous constraints. I no longer have to consider special cases which I know that the database guarantees to be impossible.

Just some reasons that are important to me.


SQL Server 2012 - Insufficient memory to continue the execution of the program. (mscorlib)

If you need to execute sql file use sqlcmd documented in the MSDN: Run Transact-SQL Script Files Using sqlcmd

This did the trick for me:

sqlcmd -S serverName\instanceName -i script.sql  -o output.txt


From your example it seems like invoice can be replaced with a simple VIEW, which is similar to a "symlink" in nature:

CREATE VIEW invoice AS
SELECT id, account_payment_type FROM account;

All done.
Else you need to be more specific in your question. Start with providing the complete table definition for both tables and add more information what is covered in each ...


sp_settriggerorder only allows you to set first/last, nothing in between. How SQL Server will fire the middle ones (or all, if you haven't used that) is completely up to SQL Server, it may not even be deterministic, and how it will fire them the next time the table is touched is not stored anywhere. Even the documentation states that the order is "undefined"...

If the order of the trigger firing matters then I strongly recommend consolidating them and not having a bunch of separate triggers in the first place. Since the behavior is undefined, even if you observed one day that triggers were firing a,b,c - nothing would prevent SQL Server from firing them c,a,b the next day. If you have used  sp_settriggerorder to set first/last, you can see those manually using OBJECTPROPERTY as demonstrated in this answer. But if you have more than three, there is no way to predict the order of the ones you haven't marked first or last.


How do I dictate the order of preferred replica on a Always On cluster?

You can set the backup Priority to be higher on your non-DR node

By curiosity, why are you taking backup on the secondaries ?
There is some cases where your secondary could be out of sync with the primary (even if you set you AG to by Sync), for that reason, I usually prefer to have my backup done on my primary node.
Also, if you take the backup on the secondary node, are you also running checkdb on that secondary node ? (you don't want to end up doing backup of a corrupted secondary)


In the end, as some have suggested, our only option was to incrementally shrink files w/out truncate only. The cost/benefit didn't really work out, so we are living with what we have.

Thank you all for the help and suggestions.


Postgres 9.4.1 on Ubuntu 14.04.2 LTS fails to install properly

Not an elegant solution but after installing the package using apt-get (which fails creating the cluster but installs PostgreSQL), I switched to the postgres user and created the database using initdb. Then back to root, I created the cluster using the pg_createcluster command.

This moved the configurations to /etc/postgres/9.4/main and set it all up.


Took me some time to test it but this actually works.

Step 1

Add the new disks to both nodes of the SQL Server Availability group

Step 2

Stop the SQL Server service of your passive node

Step 3

Move the SQL Server database files from the old disk to the new disk

Step 4

Adjust the drive letter of the original disk to another letter using f.e. diskpart

Step 5

Rename the disk where you move the database files to to the drive letter of the original disk

For Example= Old disk D: --> renamed to F:  New Disk E:  --> renamed to D:

Step 6

Restart SQL Service on Passive node and wait untill everything is back into sync.

Step 7

Failover to the passive node & do the exact same process on your previously active node.

Downtime?

If you follow this process you will have no downtime, except for the failover of your availability group.


Awful execution plans after migrating from MySQL 5.5.11 to MariaDB 5.5.41

Well, this is a bit of voodoo/shotgun debugging, but I've got it functioning okay for the time being. I set the extended_keys option to on, and also created a covering index on the 8-million-row table in question. Now I'm getting an execution plan that's even better than the acceptable plan the old server was coming up with (which was only using Magento's default indexes).

Still, it all seems a bit hand-wavey, so I'll leave this open for now in case anybody has a better recommendation or explanation.


Does SQL Server use (unused)index stats for optimizer

Yes, statistics based on indexes can be used to help with query plan creation even if the underlying index isn't used to access data in the plan. Consider that the query optimizer may consider many different query plans and data access paths while creating a query plan. The compiled query plan may end up not using one of the indexes that was considered. That certainly doesn't mean that any query plan that benefited from the statistics of that index needs to be invalidated, right?

An example might be helpful as well. First I'll throw about 6.5 million rows into a heap:

DROP TABLE IF EXISTS dbo.A_GOOD_HEAP;

CREATE TABLE dbo.A_GOOD_HEAP (
    INDEXED_COLUMN BIGINT NULL,
    OTHER_COLUMN BIGINT NULL
);

INSERT INTO dbo.A_GOOD_HEAP WITH (TABLOCK)
SELECT CASE WHEN t.RN % 10 = 0 THEN 0 ELSE 1 END
, RN
FROM
(
    SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) RN
    FROM master..spt_values t1
    CROSS JOIN master..spt_values t2
) t;

Next I'll create an index on one of the columns and look at the histogram for the statistics object that is automatically created.

CREATE INDEX IX ON dbo.A_GOOD_HEAP (INDEXED_COLUMN);

DBCC SHOW_STATISTICS ('A_GOOD_HEAP', 'IX');

Here's the histogram:

╔══════════════╦════════════╦═════════╦═════════════════════╦════════════════╗
║ RANGE_HI_KEY ║ RANGE_ROWS ║ EQ_ROWS ║ DISTINCT_RANGE_ROWS ║ AVG_RANGE_ROWS ║
╠══════════════╬════════════╬═════════╬═════════════════════╬════════════════╣
║            0 ║          0 ║  645160 ║                   0 ║              1 ║
║            1 ║          0 ║ 5806440 ║                   0 ║              1 ║
╚══════════════╩════════════╩═════════╩═════════════════════╩════════════════╝

Based on the statistics there are 5806440 rows in the table with a value of 1 for INDEXED_COLUMN. Now consider this query:

SELECT COUNT(DISTINCT OTHER_COLUMN)
FROM dbo.A_GOOD_HEAP
WHERE INDEXED_COLUMN = 1;

The query optimizer has a few different access paths for the data. It also has a few choices for how to calculate the aggregate. One of the considerations for the picking an algorithm for the agggregate is the cardinality estimate of the data. Here's a screenshot of the query plan:

Note that the estimate matches the histogram exactly even though the index isn't used to access data. Newer versions of SQL Server show which statistics were considered during optimization in the query plan. You can see that the statistic associated with the index was used:

However, the sys.dm_db_index_usage_stats dmv doesn't report any end user activity on the index.


Postgresql INSERT triggers "0 row affected" workaround

You can try to catch the number of inserted rows using GET DIAGNOSTICS and then raise a notice/warning/whatever you prefer.  Here is a small illustration:

DO $$
    DECLARE cnt integer;
    BEGIN
        INSERT INTO b SELECT i FROM generate_series(1,9) t(i);
        GET DIAGNOSTICS cnt := ROW_COUNT;
        RAISE WARNING '% row(s) affected', cnt;
    END;
$$;
-- gives:

WARNING:  9 row(s) affected
DO


Enabling TDE without breaking existing mirror

From the looks of this article, you would probably have to break the mirror first, setup TDE, and then setup the mirror again. Not ideal, but it would work.


Create Synonym error for a user-SQL Server

A user requires not only CREATE SYNONYM statement permissions but also ALTER permissions on the schema in order to create a synonym. ALTER permission on a schema, especially dbo, should be granted with care to follow the security principle of least privilege. It would be better (and easier) if the user creates the synonym in their own schema or in a schema with an owner other than dbo so that the user is sandboxed into that schema for ALTER purposes.

If the minimally-privileged user must create the synonym in the dbo schema, consider encapsulating the CREATE SYNONYM DDL in a stored procedure and use module signing to elevate permissions. That way, a user with execute permissions on the proc can create synonyms in the dbo schema but is limited to the functionality performed by the stored procedure. Below is an example of this technique.

CREATE OR ALTER PROC dbo.CreateSynonym
      @SynonymSchemaName sysname
    , @SynonymName sysname
    , @SynonymForServerName sysname
    , @SynonymForDatabaseName sysname
    , @SynonymForSchemaName sysname
    , @SynonymForObjectName sysname
AS
DECLARE @CreateSynonymStatement nvarchar(MAX) = N'CREATE SYNONYM '
    + QUOTENAME(@SynonymSchemaName)
    + N'.'
    + QUOTENAME(@SynonymName)
    + N' FOR '
    + QUOTENAME(@SynonymForServerName)
    + N'.'
    + QUOTENAME(@SynonymForDatabaseName)
    + N'.'
    + QUOTENAME(@SynonymForSchemaName)
    + N'.'
    + QUOTENAME(@SynonymForObjectName)
    + N';'
EXEC sp_executesql @CreateSynonymStatement;
GO

--create certificate and sign proc
CREATE CERTIFICATE CreateSynonymCert
   ENCRYPTION BY PASSWORD = 'T3mP0@rypAsso0rd'
   WITH SUBJECT = 'For CREATE SYNONYM in dbo schema';
ADD SIGNATURE TO dbo.CreateSynonym BY CERTIFICATE CreateSynonymCert WITH PASSWORD = 'T3mP0@rypAsso0rd';

--private key is ephemoral for this purpose so we remove it
ALTER CERTIFICATE CreateSynonymCert REMOVE PRIVATE KEY;

--create a user from certificate with the needed permissions
CREATE USER CreateSynonymCertUser FROM CERTIFICATE CreateSynonymCert;
GRANT CREATE SYNONYM TO CreateSynonymCertUser;
GRANT ALTER ON SCHEMA::dbo TO CreateSynonymCertUser;

--We could grant execute directly to the user but a role is easier to manage
--when multiple users are involved and adheres to RBAC security principles.
CREATE ROLE CreateSynonymRole;
ALTER ROLE CreateSynonymRole ADD MEMBER YourUser;
GRANT EXECUTE ON dbo.CreateSynonym TO CreateSynonymRole;
GO

--example usage
EXECUTE AS USER = 'YourUser';
GO
EXEC dbo.CreateSynonym
      @SynonymSchemaName = N'dbo'
    , @SynonymName = N'MYSYN'
    , @SynonymForServerName  = N'SERVER01'
    , @SynonymForDatabaseName  = N'DEMODB'
    , @SynonymForSchemaName  = N'dbo'
    , @SynonymForObjectName  = N'CHILD';
GO
REVERT;
GO


Why does Mongodb production notes have no recommendations about the
  disk block size?

As per MongoDB documentation Configure Block Size in a Blockstore When you back up your deployment to a Blockstores, the Backup Daemon first takes a snapshot of the head database. It then breaks this snapshot into blocks and writes these blocks to the blockstore.

The different blockstores have different default sizes and ranges of sizes for blocks.

Blockstore Type Block Size Range    Default Size
MongoDB          64 KB to 15 MB          64 KB
S3               64 KB to 16 MB          16 MB

In general, increasing block size results in faster snapshots and restores, but requires more disk space. These competing factors should be considered when determining if you wish to tune the block size.

There are two exceptions where performance improves as the block size increases without requiring additional disk space:

Workload                      Impact
Update- and Delete-Intensive  No matter how small you make the block size, the entire block file is rewritten. Since the entire file is always rewritten, there is no difference in storage space if you change the block size.
Insert-only                   The existing blocks never change. As you increase block size, block management is simplified. This enables the best possible performance on snapshot and restore.

The range of minimum block sizes include:

64 KB
128 KB
256 KB
512 KB
1 MB
2 MB
4 MB
8 MB
15 MB (MongoDB) or 16 MB (S3)

  Does disk block size have any impact on mongodb performance in
  practice?

As per Blog from @Colt McAnlis, here  “The default block size on volumes is 4K. For throughput-oriented workloads, values of 256KB or above are recommended.”

It’s important to observe the following formula wrt disk performance:

Throughput (M/sB) = IOPS * Blocksize.

How this evens out is that IOPS and block Size tend to have an inverse relationship to each other. As block size increases, it takes longer latency to read a single block, and thus the # of IOPS decreases. Inversely, smaller block sizes yield higher IOPS.

For further your ref here


Executing sql linked server procedures through python

The error is quite clear - you can't run this SP in a transaction, Some libraries (ODBC etc) support implicit transactions (meaning every piece of SQL will be run in a transaction) so you need to dig into why these are enabled. Explained in the link below
or just add SET IMPLICIT_TRANSACTIONS OFF in the line before the SP call
https://stackoverflow.com/questions/60511929/set-set-implicit-transactions-attribute-on-off-for-a-pyodbc-connection


Reset MySQL root password in LAMPP server on Ubuntu

The skip-grant-tables solution is not a recommended one, for a couple of reasons:

It makes the database vulnerable (even with skip-networking)
It requires taking your database down twice.

A solution which requires taking the database down just once is as follows:

Create a temporary SQL text file, say /tmp/init.sql
Within this file, write:

SET PASSWORD FOR root@localhost = PASSWORD('the_new_password');
Add the following to your MySQL config file (on Ubuntu this is on /etc/mysql/my.cnf), under the [mysqld] section:

init-file=/tmp/init.sql
Restart MySQL once. The init file is read and executed upon startup. The password is reset.
Proceed to remove the init-file=/tmp/init.sql entry from my.cnf (do not forget this). Even as the server is up and running.
Remove the /tmp/init.sql file.

There are even more solutions! Please refer to a past blog post of mine. Make sure to check out comment #4 by strcmp


increment row value does not work properly with where condition

Try it this way

SELECT id, rank,
       @rownum   := IF(rank = @prevrank, @rownum, @rownum + 1) myorder,
       @prevrank := rank previousRank
  FROM
(
  SELECT mro.id, mro.rank
    FROM myRankorder mro JOIN rankGroup rg
      ON rg.id = mro.groupid
   WHERE rg.groupid = 1
   ORDER BY mro.rank DESC
) q CROSS JOIN
(
  SELECT @rownum := 0, @prevrank := NULL
) const

Output:

| ID | RANK | MYORDER | PREVIOUSRANK |
|----|------|---------|--------------|
|  3 |    7 |       1 |            7 |
|  4 |    3 |       2 |            3 |
|  1 |    2 |       3 |            2 |

Here is SQLFiddle demo


Oracle 9i historical long queries since instance startup

Dynamic performance views are not guaranteed to have all SQL information since instance startup because of the limited amount of memory.

Oracle 9i does not have AWR.

You can use Statspack, but it is not installed by default.

Install it as SYSDBA with:

@?/rdbms/admin/spcreate

Creating a snapshot (does not happen automatically, this needs to be scheduled manually or with spauto.sql):

EXECUTE statspack.snap;

Creating a report:

@?/rdbms/admin/spreport

More details can be found in the documentation following the above link.


Why are breakpoints not being hit in SQL Server Management Studio?

It is because you need to create the breakpoint using the database name and object id rather than setting a breakpoint in a random SQL file which is what you get by choosing modify.

See:

https://sqlserverfunctions.wordpress.com/2014/09/12/setting-breakpoints-in-ssms/

Ed


Per Brent Ozar Unlimited (linked below), the recommendation for SQL Server Standard 2014 is 192GB RAM. SQL Server will only use up to 128GB of it and the rest is to accommodate the overhead of OS and other processes.

This being said, 64GB may be far more than necessary to allow for the of SentryOne and the IIS application overhead.

How Much Memory Does SQL Server Need?

I believe that, for the cores, you must license each core that is allocated to the VM. You can limit the CPU's using the CPU page on the Server Properties.


Ways to clean data (trim unneccesary space & chars) - MySQL

You can use TRIM():

SELECT TRIM('  cognac    ');
-> 'cognac'

Or:

SELECT TRIM(BOTH 'x' FROM 'xxxxcognacxxxx')
-> 'cognac'

Or cascade multiple characters:

SELECT TRIM(BOTH 'c' FROM (TRIM(BOTH 'x' FROM 'xxxxcognacxxxx')))
-> 'ogna'

You can also look at this question for REGEX replace using a function: How to do a regular expression replace in MySQL?


A single statement like that works the same with MyISAM or InnoDB, with a transaction or with autocommit=ON.  It blocks enough to do the query, thereby blocking the other connection.  When finished, the other connection proceeds.  In all cases, the column is soon decremented by 11.

A third user may see the value decremented by 0 or 4 or 7 or 11.  The "very exact time" is not really possible because, at some point in the execution of each statement, a single-threaded lock is checked/set/whatever.  That is, they will be serialized, just so fast that you can't see it.

InnoDB locks only rows, not tables.  (OK, DDL statement do bolder locks.)

What gets more interesting is a transaction that modifies two things, or that takes a noticeable amount of time:

Intention Case: Single item but taking time:

BEGIN;
SELECT something;
think about it for a while
UPDATE that something;
COMMIT;

The select needs to be written thus:

SELECT something  FOR UPDATE;

This tells other connections "I intend to update the row; please don't mess me up".  (I bring up this example, because a lot of newbies miss this subtlety.)

Deadlock case:  Messing with 2 things:

BEGIN;    -- in one connection
UPDATE thing_1;
UPDATE thing_2;
COMMIT;

BEGIN;    -- in another connection, at the "exact same time"
UPDATE thing_2;
UPDATE thing_1;
COMMIT;

This is the classic example of a deadlock -- each grabs one thing and then reaches for the other thing.  Clearly it can't be made to work.  One transaction is killed; the other completes.  Hence, you must check for errors, so you can discover it.

The normal reaction to a deadlock is to replay the entire failed transaction.  By then, the other connection will not be interfering, and it should proceed without trouble.  (OK, yet another connection could create another deadlock.)

Delay Case: If the two connections grab multiple things in the same order, then one can be delayed until the other finishes.  To keep this from "waiting forever", there is a default 50-second innodb_lock_wait_timeout.  Your pair of simple UPDATEs is actually an example of this case.  One will finish promptly; the other is stalled until the first finishes.

Note how a Deadlock can (in some cases) be turned into a Delay by consistently ordering the things you touch.

autocommit=1:  With this setting and without calling BEGIN, each statement is effectively:

BEGIN;
your statement
COMMIT;

autocommit=0:  This is trouble waiting to happen.  When you perform a write query, a BEGIN is implicitly generated.  However, it is your responsibility to eventually issue COMMIT.  If you fail to do so, you will wonder why your system is hung.  (Another common newbie bug.)  My advice: "Never use =0".


Is it safe to delete the default "postgres" role?

I'm trying to understand if it's okay for me drop the "postgres" role,
  or at the least, remove it as the superuser.

Dropping the role owning the template databases and all the system tables of all databases is not possible. There is even a specific error for that, should you try to DROP ROLE postgres:

ERROR:  cannot drop role postgres because it is required by the database system

It's technically possible to remove the superuser capability to postgres, but  if there is no superuser left in the system, any administrative task that only a superuser can do will be out of reach, and that would be really inconvenient.

On the other hand, it's entirely reasonable to deny through pg_hba.conf the ability for the postgres role to connect remotely, and also to set and enforce a password for the postgres account even for local-only connections.


should I change mysql platform for higher innodb_buffer_pool_size?

With a 160 GB database, it does not sound like a 10 GB cache is overkill, but it depends to some extent on the size of the active data as well.

I suggest that you look at Rolando's post:
How large should be mysql innodb_buffer_pool_size?

In the particular case he discusses, there was too much memory allocated for the cache. However, he offers considerable detail on what to measure and how to refine the size of the cache.

Some code snippets from Rolando's post:

Compute the Cache Size:

SELECT CEILING(Total_InnoDB_Bytes*1.6/POWER(1024,3)) RIBPS FROM
(SELECT SUM(data_length+index_length) Total_InnoDB_Bytes
FROM information_schema.tables WHERE engine='InnoDB') A;

After you reset the buffer size and restart the server, give it some time, then run this to see how usage is holding up:

SELECT (PagesData*PageSize)/POWER(1024,3) DataGB FROM
(SELECT variable_value PagesData
FROM information_schema.global_status
WHERE variable_name='Innodb_buffer_pool_pages_data') A,
(SELECT variable_value PageSize
FROM information_schema.global_status
WHERE variable_name='Innodb_page_size') B;


How to create an index to be picked by query

This is the part of the execution plan where you expect an index being used:

      ->  Seq Scan on paid gap  (cost=0.00..20265.45 rows=204709 width=63) (actual time=0.024..215.813 rows=198575 loops=1)
            Filter: ((project_id = 1) AND ((country_iso_code)::text = 'gb'::text) AND ((source)::text = 'website'::text) AND (created_at <= (now())::date) AND (created_at >= ((now())::date - 100)))
            Rows Removed by Filter: 214895

What we have to look at is the number of rows this node returns (198575) and the number it does not return (Rows Removed by Filter: 214895).  This means that the conditions in your WHERE clause are not really selective - they specify nearly half of the whole table.

This is a situation where indexes seldom help - they usually said to kick in when the percentage of the returned rows is estimated to be under 10% (others say less, like 5%).

You can try the same query with a more selective set of conditions (practically, setting a narrower date range).  In this case, an index may help - the first one you list above, for example.  Depending on the distribution of the data (the different values of project_id, source, and country_iso_code, and the proportion of all rows having the specific values), a different order of the columns might be preferable (for example, created_at::date as the first column).  If the other columns are not selective enough in any case (for example, all rows have a project ID of 1), it can happen that you need created_at::date only.

At the same time, your problem seems not to be the (lack of) indexes, but that the query is sorting too much data.  This node:

->  Sort  (cost=46024.07..46535.85 rows=204709 width=63) (actual time=2284.109..2805.815 rows=198575 loops=1)
      Sort Key: key
      Sort Method: external merge  Disk: 13960kB

shows that the work_mem setting is too low - the query needs at least 13960 kB more memory for this sort.  Do a

SET work_mem TO xMB;

where x is {your current setting} + 16 MB.  This will spare you about 2 seconds.

Notes:

casting varchar to text does not help with your indexes.
Is this query just a part of a bigger picture?  If it isn't, I'd think a bit more about what I am planning to do with the nearly 100k rows returned.  It might mean a (slight) re-desing of your logic.


Transaction log space used in percent - how to clear or empty

The guide David Browne linked should explain everything you need, so I highly concur that you should read it. But the five second version is the Transaction Log will grow until a backup is taken of it to ensure the log itself is hardened (aside from the transaction being committed to disk). When the Transaction Log grows, it'll use the reserved free space it has available inside the log file until it runs out of space in the file itself, then the file itself will undergo a growth operation on the disk.
Once a growth operation occurs, the size of the log file on disk will remain that size unless an explicit shrink operation occurs, but internally on SQL Server, the space consumed by the log file is flushed and re-used when a log backup occurs. So while from the OS's perspective, the log file may be one size, the actual available reserved space inside that log file is managed by SQL Server and consumed until it runs out and needs to grow the log file again or a backup occurs to flush the used space so it can be re-used.
One last thing I meant to add, is that your stored procedure performance is almost certainly not related to how full your Transaction Log is. If you post a new question with the execution plan of your stored procedure, we likely can point you in the right direction of the actual root cause.


Backup and restore for PostgreSQL + why number of backups increased while we limit the recovery window of 7 days

Your listing of /barman/main-db-server/base suggests you are taking a backup once a minute (Why and how?  I don't know.  Your given crontab doesn't seem to launch a backup that often--but something seems to be).  None of those backups are more than 7 days old, so there is no reason for them to be deleted based on the retention_policy = RECOVERY WINDOW OF 7 days

But you should really list your backups using barman list-backup, not by manually monkeying around inside barman's workspace.


Dual management node for mysql clustering

Hmm, the two data nodes on one server means that if you lose 192.168.0.1 (node 3/node 4) you lose the whole cluster (possibly why you are experiencing the failure).

In fact the fact that you have effectively a single data node (unless I'm missing something) means you don't really have a cluster - you only have one copy of data.


Changing SQL account password in specific database

All password authentication for userid is at the server level for a login.  Therefore the LionsDB database will have users but does not have per database password for those users since the login is the authentication point.

If you have enough rights, you can change the passwords of the logins and use a different password, but that password would be the same for all databases on your server.

If you need a special login for a database, then you should create another login, e.g. LionsDB_George or some such pattern that suits you.  Then you grant that login rights to the LionsDB database.


Define the table name parameter as text and then pass a string:
create or replace function field_summaries(gap interval , _tbl text, _col text)
returns table(hour text, average numeric, minimum numeric, maximum numeric) as
$func$
begin
 RETURN QUERY
  EXECUTE format('select time_bucket($1, time)::text as hour,
                         avg(%I),
                         min(%I),
                         max(%I)
                  from %I d
                  where d in (select device_id from sensors)
                  group by hour', _col, _col, _col, _tbl)
     USING gap;
END;
$func$
language plpgsql
stable;

Then call it like this:
select *
from field_summaries('5minutes', 'm_09', 'current');


How can I verify that all possible FLOAT(53) values convert to unique BINARY(8) values?

The size, in bytes, of both BIGINT and FLOAT(53) are documented.  It would be a breaking change to make either one not convertible back and forth to BINARY(8).

The documentation you quote is about the details of the conversion, which are undocumented and subject to change.  EG the byte layout of a float(53) in this conversion could change.  So if you convert a float to binary(8), store the binary(8) in a table, upgrade SQL Server, and convert the binary(8) back to float, you might not get the same value.

These particular types have well-known byte layouts.  BIGINT is an 8-byte little endian integer, and float(53) is a standard double-precision floating point number.  And it would be pretty surprising if one of those changed.  But other types, especially CLR-based types, really might change their layout between versions.


Recover database until cancel without prompt?


You have options

Move away the backup files to a network share or a central backup server. Based on your company's retention policy, the backup can be archived to tape or just deleted (Caution !). This way if you lost the backup drive, you still have backups.
For VLDBs, you have to look into a good workable (meeting your company's RTOsand RPOs) backup (and restore) strategy.

E.g. Take weekly full backup, daily diff and transaction log backups say 15mins... This way, you wont end up taking FULL backups. You can be creative to check - how much database has changed since the last full backup before taking a diff backup.

The take away is to do proper storage planning. Enable Instant file initialization and yes do test your restores ... since a backup is no good unless you can restore it !


CTE Query to CONCAT text (over 10,000 rows)

How does this approach do in comparison?

SELECT
  SampleUserNumber,
  CombinedMessage = STUFF(
    (SELECT N' ' + SampleTextLine FROM #SampleTable
       WHERE SampleUserNumber = t.SampleUserNumber
       ORDER BY SampleLineNumber
       FOR XML PATH, TYPE).value(N'.[1]',N'nvarchar(max)'),1,1,'')
FROM #SampleTable AS t
GROUP BY SampleUserNumber
ORDER BY SampleUserNumber;

It would probably be useful for your #temp table to have a clustered index on SampleUserNumber, SampleLineNumber or at least a non-clustered on those two columns that INCLUDEs SampleTextLine.

In SQL Server 2017 you would be able to use a much more straightforward approach, almost guaranteed to be faster:

SELECT SampleUserNumber,
  CombinedMessage = STRING_AGG(CONVERT(varchar(max),SampleTextLine), ' ')
                    WITHIN GROUP (ORDER BY SampleLineNumber)
FROM #SampleTable
GROUP BY SampleUserNumber
ORDER BY SampleUserNumber;

To avoid the CONVERT you could just make your #temp table column a MAX type.


Mysql replication, allow inserts while database is disconnected

For Master-Master, use Galera or InnoDB Cluster.  The pitfalls of M-M are many and subtle.


Can't connect to local Postgres databases

If you want to run more postgres clusters on a single machine, just make sure that they have their own $PGDATA (the location where their data and config files are)
and make sure that in their postgres.conf the parameter for port differ.
port = 2345
listen_addresses='*'

the cluster can share the listen_addresses but not the port. So each must have it's own port number.


Option 2 would be the fastest. You are loading it directly, append-only. Option 3 would be similar except that you are updating data.

Option #4: Create a staging table (UNLOGGED) and COPY the CSV into that. After loading, use straight SQL or PLPGSQL to INSERT-INTO-CHILD--SELECT--FROM-. That allows fast bulk loading, with optional transformation steps after the load. If you have few indexes on the staging table (which you ideally add after the bulk load), you can have the whole ETL process work quite rapidly. Using plpgsql to load your child partition from the staging table allows gives you error-handling capabilities.


Redirecting MySQL errors and slow logs into another database

Have you ever considered converting the slow log into a MyISAM table?

As for MySQL 5.1.30, you can choose the format of the slow log and general log as either TABLE or FILE by means of the log_output option. It is also available in MySQL 5.5.

To support the TABLE option for log_output, MySQL provided two tables:

mysql.slow_log
mysql.general_log

For the sake of this question, I will discuss only mysql.slow_log

By default, this is mysql.slow_log:

mysql> show create table mysql.slow_log\G
*************************** 1. row ***************************
       Table: slow_log
Create Table: CREATE TABLE `slow_log` (
  `start_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `user_host` mediumtext NOT NULL,
  `query_time` time NOT NULL,
  `lock_time` time NOT NULL,
  `rows_sent` int(11) NOT NULL,
  `rows_examined` int(11) NOT NULL,
  `db` varchar(512) NOT NULL,
  `last_insert_id` int(11) NOT NULL,
  `insert_id` int(11) NOT NULL,
  `server_id` int(10) unsigned NOT NULL,
  `sql_text` mediumtext NOT NULL
) ENGINE=CSV DEFAULT CHARSET=utf8 COMMENT='Slow log'
1 row in set (0.10 sec)

mysql>

The slow_log table uses the CSV storage engine. Please note that a CSV table cannot be indexed. That's not very useful for doing analysis by date and time. GOOD NEWS: You can convert it to a MyISAM table.

In order to facilitate a MyISAM-based slow log, perform the following:

Step01) Make mysql.slow_log a MyISAM table

ALTER TABLE mysql.slow_log ENGINE-MyISAM;

Step02) Index to timestamp column called start_time

ALTER TABLE mysql.slow_log ADD INDEX (start_time);

Step03) Add the following lines to the my.cnf (or my.ini)

[mysqld]
slow-query-log
log-output=TABLE

Step04) Restart MySQL

For Linux

service mysql restart

or for Windows

net stop mysql
net start mysql

Step05) Run the following command in MySQL

mysql> SHOW VARIABLES LIKE 'long_query_time';

By default long_query_time is 10 seconds

Step06) Run this command

mysql> SELECT SLEEP(15);

If long_query_time is 10 seconds, this SELECT should deliberately be placed in mysql.slow_log.

Step07) Run this command

mysql> SELECT sql_text FROM mysql.slow_log;

If you see any row in the table with the SELECT SLEEP(15);, CONGRATULATIONS !!!

You can now run SELECT queries against mysql.slow_log to copy some or all of the rows to other tables or other databases.

I have posted past answers in conjunction with this subject:

Mar 30, 2012 : MySQL slow log configuration
Nov 18, 2011 : Create a custom log table in MySQL
Jun 30, 2011 : How do I output MySQL logs to syslog?

UPDATE 2012-11-12 17:00 EDT

In all fairness, the question did ask about error logs. I did not explicitly include anything about it. I will include some code that addresses how to

Let's say the error log is defined on any DB server as follows:

[mysqld]
log-error=/var/log/mysqld.log

You could create a script like this:

ERROR_LOG=/var/log/mysqld.log
LOG_LINES=/tmp/general-log-lines.txt
ERROR_FOLDER=<folder where to copy logs>
ERROR_TARGET=${ERROR_FOLDER}/`hostname`-DB-Errors.txt
NEWCOUNT=`wc -l < ${ERROR_LOG}`
if [ -f ${LOG_LINES} ]
then
    OLDCOUNT=`cat ${LOG_LINES} | awk '{print $1}'`
    if [ ${OLDCOUNT} -lt ${NEWCOUNT} ]
    then
        (( DIFF = NEWCOUNT - OLDCOUNT ))
        tail -${DIFF} < ${ERROR_LOG} >> ${ERROR_TARGET}
        echo ${NEWCOUNT} > ${LOG_LINES}
    fi
fi

or if you want to collect and reset the error log without a mysql restart (linux only) you can use this (This technique could never be done in Windows because the Windows OS locks the error log file from external writing):

ERROR_LOG=/var/log/mysqld.log
LOG_LINES=/tmp/general-log-lines.txt
ERROR_FOLDER=<folder where to copy logs>
ERROR_TARGET=${ERROR_FOLDER}/`hostname`-DB-Errors.txt
NEWCOUNT=`wc -l < ${ERROR_LOG}`
if [ -f ${LOG_LINES} ]
then
    OLDCOUNT=`cat ${LOG_LINES} | awk '{print $1}'`
    if [ ${OLDCOUNT} -lt ${NEWCOUNT} ]
    then
        (( DIFF = NEWCOUNT - OLDCOUNT ))
        tail -${DIFF} < ${ERROR_LOG} >> ${ERROR_TARGET}
        echo -n > ${ERROR_LOG}
        echo 0 > ${LOG_LINES}
    fi
fi

Place this script on every DB server. Setup a crontab for this script to go off every 5 minutes. Make sure ERROR_FOLDER is set to a common shared folder.

UPDATE 2012-11-12 17:24 EDT

For clarification, running MySQL FLUSH LOGS; is insufficient because...

For all text-based logs, FLUSH LOGS; closes and reopens. No rotation is provided by mysqld
For binary logs, FLUSH LOGS; will...

Close current binary log
Rotate to a new binary log
Open new binary log

For MySQL 5.5, FLUSH ERROR LOGS; closes and reopens the error log. No rotation is provided

This is why some scripting is necessary


MIN(+ - schema_id) is parsed as MIN( + (- schema_id) ) which is the same as MIN(- schema_id), so it's (mathematicaly)* the same as -MAX(schema_id). Likewise for the MAX.

*: There are some edge cases when the MIN/MAX(-x) might raise an error while the - MAX/MIN(x) will not (because the negation is applied in all values of the column vs only to the MAX/MIN value).


I agree with the comment about table definitions, but in a few moments you will see this does not depend on the tables.

You use SELECT from derived table cdr for JOIN and for WHERE

JOIN customers c ON c.id = cdr.dcustomer
   LEFT JOIN hub.hours h ON HOUR(cdr.`start`) = h.idhour

    WHERE (c.parent = _ID or cdr.dcustomer = _ID or c.parent IN
        (SELECT id FROM customers WHERE parent = _ID))

Derived tables do not have indexes and so these operations always will be full-scan. It may take 6 seconds. That is not too much.

The other JOIN uses derived columns:

FROM `cdrs`
    JOIN tariffs t ON (LEFT(`cdrs`.`cnumber`,7) = t.numberrange OR LEFT(`cdrs`.`cnumber`,8) = t.numberrange)

This is not the best idea.

Again, group by does not use indexed data:

GROUP BY h.idhour, cdr.innumber

cdr - does not have indexes.

Possibly faster will be to store intermediate data in a table with indexes.


SQL Server - Recursive "find all FK connections for ID through the entire DB" query

How about implementing a child/parent relationship in a table including the hierarchical structure. You could fill that iteratively for every table in your DB, if you add a parent you'd update all its children's hierarchical structure.

To find specific results for a certain table you'd then simply have to select all records that contain that table in its hierarchy.

(Hierachies in your example would look something like this:

/H/B
/H/G/B
/H/B/A
/H/G/B/A
...
)


You can use REPLICATE:

SELECT 'A' + REPLICATE('a', 1000) + 'ha!';


Maximum Attachment Size in database mail

You will have to change the allowed Attachment file size by going to the following location:

Go to SQL Server Management Studio -->  Management -->  Database Mail

This will fire up the Configuration Wizard.

Click Next --> Select View or change system parameters --> click Next.

Now change the Maximum File Size (Bytes) property and click Next, then click Finish to save your changes.


How to configure DBeaver to save files without a BOM for UTF files?

Using sed

You can always cut out three bytes (the bom) with sed

sed -i '1 s/^\xef\xbb\xbf//' *.txt

That'll actually do it in place.


Manage max table storage space in case of excess data (size in GB)

There isn't a standard option to do this, but you can achieve this several ways.

create a separate db and move your log tables there. set a max file size.
create a trigger which checks row count or table size and limits inserts when value exceeded and/or deletes old data.
create a foreign key constraint on an id column pointing to a table with a set number of rows. When your log table reaches the maximum it will stop.
add a check constraint.


Error: /var/lib/postgresql/9.3/main is not accessible or does not exist

If you suspect there's something wrong with our database (cluster), make a copy of your database directory before you continue. Just to be sure. On Ubuntu the default directory would be: /var/lib/postgresql/9.3/main/ - but your installation may differ.

Actually, your command should work (in default Ubuntu installations), because the service command starts the postgres server process with appropriate privileges (as system user postgres)

sudo service postgresql start

Translates to something like:

sudo -u postgres /usr/lib/postgresql/9.3/bin/pg_ctl -D /var/lib/postgresql/9.3/main/ -o "-c config_file=/etc/postgresql/9.3/main/postgresql.conf" start

That starts all db clusters you may have. To start a particular one, use the wrapper pg_ctlcluster:

sudo -u postgres pg_ctlcluster 9.3 main start

To get the status of all currently installed db clusters use pg_lsclusters:

sudo -u postgres pg_lsclusters

You must be aware that the data directory is not the same as the config directory in default Debian or Ubuntu Postgres installations. For Postgres 9.3 that would be:

data dir: /var/lib/postgresql/9.3/main/
config dir: /etc/postgresql/9.3/main/

You can check the /etc/postgresql/9.3/main/postgrsql.conf for the actual data dir. It's the line starting with

data_directory =

The error message indicates that Postgres tries to start with the default data dir, but it is not there or the privileges have been changed. Did you check?

Or are you running a non-default installation?


Linked Server works on SSMS and isn't found on SSDT

Could you share the linked object name along with the query?
Sometimes the problem is on the FROM part. There are a lot of situations where you can get this error, and many of them are covered on StackOverflow. However, one case which might especially be encountered on shared servers is simply that the database name has a period (dot) in it. For example, if its name is mysite.com_DB. This will automatically cause the problem.

The solution, if you can't rename the database, is to encapsulate the DB name in square brackets, so for example:

mysite.com_DB.Table_name

Will turn into:

[mysite.com_DB].Table_name


MySQL user is not expired but asking for Password RESET

The password is expired -- implicitly -- after 360 days -- in MySQL Server 5.7 versions prior to 5.7.11.

  From MySQL 5.7.4 to 5.7.10, the default default_password_lifetime  value is 360 (passwords must be changed approximately once per year). For those versions, be aware that, if you make no changes to the default_password_lifetime variable or to individual user accounts, all user passwords will expire after 360 days, and all user accounts will start running in restricted mode when this happens. Clients (which are effectively users) connecting to the server will then get an error indicating that the password must be changed: ERROR 1820 (HY000): You must reset your password using ALTER USER statement before executing this statement.

  https://dev.mysql.com/doc/refman/5.7/en/password-expiration-policy.html

Temporary workaround should be this:

mysql> SET GLOBAL DEFAULT_PASSWORD_LIFETIME = 0;

Better, add to the mysqld section of my.cnf:

default_password_lifetime = 0

...or another appropriate value for your site, then restart the server.

Even better still... upgrade.  There were numerous bugs in early GA versions of 5.7, particularly 5.7.9, which was the first GA version of the release series.

Of course, you should be able to modify this account using ALTER USER ... PASSWORD EXPIRE NEVER which is apparently not the same as the NULL value shown in the table.


Couchbase Ghost entry - Have it in view, not in data itself

What is interested I have changed the view, to show me the data itself and the data was OK...

Well, I have Failed Over each server one by one, and made a FULL REBALANCE after the failure.
After I have finished with the last 4th server, the ghost record disappeared.


Upgrading 5.6 to 5.7 without losing any data

The upgrade path is MySQL 5.6 -> MySQL 5.7

see Upgrading MySQL


How can I partitioning oracle table when the table still working?

Just copy your table with a new name and apply the partioning,
drop the original table and rename the copied file to the original table.


Your error here is

  server not found

This is because you used @srvproduct=N'SQL Server', in this case you should use SQL Server's pc name in case of default instance, i.e. this connection string would be correct in case of default instance:

EXEC master.dbo.sp_addlinkedserver @server = N'my_pc_name', @srvproduct=N'SQL Server'

or use this one in case of named instance:

EXEC master.dbo.sp_addlinkedserver @server = N'my_pc_name\inst_name', @srvproduct=N'SQL Server'

If you want to use any other name, even just IP (or IP,port in case of named instance, use @provider=N'SQLNCLI':

for default instance

EXEC master.dbo.sp_addlinkedserver @server = N'my_linked_server_name_as_I_want_it', @srvproduct=N'', @provider=N'SQLNCLI', @datasrc=N'10.237.69.11'

for named instance:

EXEC master.dbo.sp_addlinkedserver @server = N'my_linked_server_name_as_I_want_it', @srvproduct=N'', @provider=N'SQLNCLI', @datasrc=N'10.237.69.11,your_port'

If you want to use GUI (SSMS), or change server name to your pc name, or use another radio button position, for ex. SQL Native Client (other than SQL Server)


The default trace does not capture this information. The rename operation is performed using sp_rename and the default trace captures CREATE, MODIFY, DELETE operations of objects (as in ALTER TABLE, CREATE TABLE, DROP TABLE) which doesn't occur when this procedure is run.

If you want to audit this action, you can create a trace for SQL:BatchCompleted events and filter the TextData looking for sp_rename.

As an FYI, here is the list of events captured by the default trace:

EventID  Event_Description
-------  ----------------------------------------------
18       Audit Server Starts And Stops
20       Audit Login Failed
22       ErrorLog
46       Object:Created
47       Object:Deleted
55       Hash Warning
69       Sort Warnings
79       Missing Column Statistics
80       Missing Join Predicate
81       Server Memory Change
92       Data File Auto Grow
93       Log File Auto Grow
94       Data File Auto Shrink
95       Log File Auto Shrink
102      Audit Database Scope GDR Event
103      Audit Schema Object GDR Event
104      Audit Addlogin Event
105      Audit Login GDR Event
106      Audit Login Change Property Event
108      Audit Add Login to Server Role Event
109      Audit Add DB User Event
110      Audit Add Member to DB Role Event
111      Audit Add Role Event
115      Audit Backup/Restore Event
116      Audit DBCC Event
117      Audit Change Audit Event
152      Audit Change Database Owner
153      Audit Schema Object Take Ownership Event
155      FT:Crawl Started
156      FT:Crawl Stopped
164      Object:Altered
167      Database Mirroring State Change
175      Audit Server Alter Trace Event
218      Plan Guide Unsuccessful


The immediate issue is that you are appending the formatted date string and .csv' to the end of the query string, rather than to the end of the file path as I am assuming is the intention.

It seems to me your CONCAT expression should actually go like this:

CONCAT("SELECT ... INTO OUTFILE 'E:/Data/exports/DailyExport/myfilename_"
  , DATE_FORMAT( NOW(), '%Y%m%d')
  , ".csv'
    FIELDS TERMINATED BY ','
    ..."
)

Note that there's no ' at the end of the E:/Data/exports/DailyExport/myfilename_, because the date is appended at that point, followed by .csv' which completes the name.

Once you have fixed that, you are going to face another issue, which will be caused by the ENCLOSED BY line of your dynamic query. The ' character must be escaped when itself enclosed in '. So, it should be either:

...
ENCLOSED BY ''''
...

or

...
ENCLOSED BY '\''
...


You asked for ideas--here's mine.

We had similar issues. On SQL Servers where the issue exists try changing the SQL Server service account to a Windows account instead of a built-in account, restart SQL Server, and retry the query.

If you're running these queries with SQL Agent, you may need to change the SQL Agent account to an actual Windows account.

My thought is that build-in accounts can't access Active Directory properly.


how to associate columns between differnt tables

While a better solution might exist, a simple way would be to have a Questions table with QuestionId as the primary key, QuestionText column, and an QuestionType column so your consuming application can render the appropriate control (e.g. Q1 likely should be a radio button vs Q2 which would be a checkbox).
And have an Answers table with an AnswerId primary key column, the QuestionId it belongs to as a foreign key reference, and an AnswerText column so it can hold any data type of answer. It may be helpful to also have a column called AnswerDataType so you can dynamically cast the AnswerText to the underlying data type when needed.


Botched migration from 9.4 to 9.5 and I'm not sure how to fix it

Since you had 2 instances installed, the first (I guess 9.4) was using the default 5432 port so the 2nd got another port (probably 5433). When you uninstalled the first instance, the 2nd doesn't know anything about it and still uses the 5433 port.

You can use pg_lsclusters to find what instances have been installed, and some more details like whether the are running or not, their ports, their data directories, etc. Example:

ian@jupiter:~$ pg_lsclusters
Ver Cluster Port Status Owner    Data directory                 Log file
9.3 main    5437 online postgres /var/lib/postgresql/9.3/main   /var/log/postgresql/postgresql-9.3-main.log
9.3 test93  5438 down   postgres /var/lib/postgresql/9.3/test93 /var/log/postgresql/postgresql-9.3-test93.log
9.4 main    5435 online postgres /var/lib/postgresql/9.4/main   /var/log/postgresql/postgresql-9.4-main.log
9.5 main    5432 online postgres /var/lib/postgresql/9.5/main   /var/log/postgresql/postgresql-9.5-main.log
9.5 repl    5439 online postgres /var/lib/postgresql/9.5/repl   /var/log/postgresql/postgresql-9.5-repl.log
9.5 stage   5433 online postgres /var/lib/postgresql/9.5/stage  /var/log/postgresql/postgresql-9.5-stage.log
9.6 main    5434 online postgres /var/lib/postgresql/9.6/main   pg_log/postgresql-%Y-%m-%d_%H%M.log
9.6 repl    5440 online postgres /var/lib/postgresql/9.6/repl   /var/log/postgresql/postgresql-9.6-repl.log
10  main    5441 online postgres /var/lib/postgresql/10/main    /var/log/postgresql/postgresql-10-main.log
ian@jupiter:~$

So the solution is pretty simple. Tell pgAdmin to connect to port 5433 or whichever port your 9.5 has been setup to listen to.

Alternatively, change the configuration of your 9.5 (second and now only one) instance to use port 5432. You need to edit the postgresql.conf file and restart it.


Dynamic form builder database optimization Postgresql

If I had more reputation, I would add a comment asking you to describe your indexes. As it is, I'll assume you don't have indexes since you didn't mention them.

First, consider using EXPLAIN on your queries to analyze the decisions the planner makes. 1.5m rows shouldn't take 20 seconds with any sort of reasonable combination of hardware and indexing. See the documentation of the EXPLAIN modifier to get started with that. You should include (ANALYZE on) to see the cost breakdown and timing info from actually running the query, and more importantly the individual processes that go into each stage.

Second, a schema like this would benefit not only from indexes, but from some limited use of multi-column indexes. (See CREATE INDEX documentation.) Consider creating indexes on:

form_fields.formid, form_fields.id
form_submits.formid, form_submits.id

and others. It depends highly on what your code is doing. Duplicate the database to another server and create different configurations of indexes and run your code against it. (EXPLAIN (ANALYZE on) again here!) Multi-column indexes are great because they help the planner check multiple filtering/sorting criteria in one pass of the index.

Third, to address your last points, I'm not sure that pagination or moving to a non-relational model will help. The fact that you're using foreign keys actually points to a strongly relational set of data. You can, of course, try MongoDB for free and see what the performance looks like.


SQL Server alter table to change TEXT to NVARCHAR(MAX) greatly increasing database size

I expect this post will be of help to you.

http://geekswithblogs.net/johnsPerfBlog/archive/2008/04/16/ntext-vs-nvarcharmax-in-sql-2005.aspx

Key facts:

By default TEXT and NTEXT stores the text value in the LOB structure
By Default NVARCHAR(MAX) stores the text value in the table structure (Unless it is over 8000 bytes)
When you alter the column from the TEXT/NTEXT to NVARCHAR(MAX), the way the data is stored is not changed, it updates the table metadata only.  The data structure is only changed the next time the value is changed.  This can be done immediately by running something like this:

  update mytable set mycolumn1 = mycolumn1

If you use the default table option setting for NVARCHAR(MAX), then the data in your table will be larger.

- You will need to look at your table option settings and environment before changing the setting to what suits your needs.
Your table size will eventually shrink, if you follow your alter table statement with the update table statement.

In short, if you run the update statement, forcing the storage of the data structure to be changed, your database size will be smaller, as expected.

EDIT: As you have mentioned TEXT and not NTEXT, your gain in space would be less obvious than you might think.  NTEXT takes double the amount of space as what TEXT does, but at the same time, you should expect NVARCHAR(MAX) to take up around half the space as what NTEXT does.  By my calculation, you would see little change from your original database size.

  Special credit to http://www.douglubey.com/


SQL query to get monthly sales per product, including products with no sales

See if this is what you're looking for (adjust as needed):

set nocount on

--Declare a months table (or use a Date Dimension table)
Declare @Months Table (MonthNumber tinyint, MonthName varchar(20))
insert into @Months (MonthNumber,MonthName) values
(1,'January'),
(2,'February'),
(3,'March'),
(4,'April'),
(5,'May'),
(6,'June'),
(7,'July'),
(8,'August'),
(9,'September'),
(10,'October'),
(11,'November'),
(12,'December')

--Declare a products table
Declare @Products table (Id int, ProductName Varchar(20))
insert into @Products (Id, ProductName)
values(1,'Widgets'),(2,'Thingamabob')

Declare @Sales table (product_id int, [date] Date, qty int, amount decimal(11,2))
insert into @Sales (product_id, [date], qty, amount)
values(1,'2017-01-01',1,100.00),(2,'2017-02-01',1,200.00),(2,'2017-06-01',2,300)

--Select Months and CROSS JOIN to Products, then
--LEFT JOIN to Sales.  Use ISNULL to return zeros
--for months/products with no sales
SELECT m.MONTHNUMBER
    ,m.MonthName
    ,P.ProductName
    ,ISNULL(SUM(QTY), 0) AS QTY
    ,ISNULL(SUM(AMOUNT), 0) AS AMOUNT
FROM @Months m
CROSS JOIN @Products p
LEFT JOIN @Sales s ON DatePart(MONTH, [date]) = M.MonthNumber
    AND S.product_id = P.ID
GROUP BY m.MonthNumber
    ,m.MonthName
    ,P.ProductName
ORDER BY M.MonthNumber
    ,m.MonthName
    ,P.ProductName

| MONTHNUMBER | MonthName | ProductName | QTY | AMOUNT |
|-------------|-----------|-------------|-----|--------|
| 1           | January   | Thingamabob | 0   | 0.00   |
| 1           | January   | Widgets     | 1   | 100.00 |
| 2           | February  | Thingamabob | 1   | 200.00 |
| 2           | February  | Widgets     | 0   | 0.00   |
| 3           | March     | Thingamabob | 0   | 0.00   |
| 3           | March     | Widgets     | 0   | 0.00   |
| 4           | April     | Thingamabob | 0   | 0.00   |
| 4           | April     | Widgets     | 0   | 0.00   |
| 5           | May       | Thingamabob | 0   | 0.00   |
| 5           | May       | Widgets     | 0   | 0.00   |
| 6           | June      | Thingamabob | 2   | 300.00 |
| 6           | June      | Widgets     | 0   | 0.00   |
| 7           | July      | Thingamabob | 0   | 0.00   |
| 7           | July      | Widgets     | 0   | 0.00   |
| 8           | August    | Thingamabob | 0   | 0.00   |
| 8           | August    | Widgets     | 0   | 0.00   |
| 9           | September | Thingamabob | 0   | 0.00   |
| 9           | September | Widgets     | 0   | 0.00   |
| 10          | October   | Thingamabob | 0   | 0.00   |
| 10          | October   | Widgets     | 0   | 0.00   |
| 11          | November  | Thingamabob | 0   | 0.00   |
| 11          | November  | Widgets     | 0   | 0.00   |
| 12          | December  | Thingamabob | 0   | 0.00   |
| 12          | December  | Widgets     | 0   | 0.00   |

Note: The OP did not indicate they wanted a solution that takes the year into consideration, so my solution does not deal with the notion of a year when aggregating the data.  If the month is January (regardless of year), the counts go in the January bucket.

UPDATE: To clarify a bit further, I want a row for each possible month/product tuple. If there was no sales for a given month/product it should show zero qty/sales


How to create an index to speed up an aggregate LIKE query on an expression?

There is no index support for LIKE / ILIKE in PostgreSQL 8.4 - except for left anchored search terms.

Since PostgreSQL 9.1 the additional module pg_trgm provides operator classes for GIN and GiST trigram indices supporting LIKE / ILIKE or regular expressions (operators ~ and friends). Install once per database:

CREATE EXTENSION pg_trgm;

Example GIN index:

CREATE INDEX tbl_col_gin_trgm_idx ON tbl USING gin (col gin_trgm_ops);

Related:

How is LIKE implemented?
Pattern matching with LIKE, SIMILAR TO or regular expressions in PostgreSQL


Identifying elements in SSIS package built in MS Visual Studio 2008

The fx glyph indicates that an Expression exists on the item in reference. The first picture indicates there is a an expression on the Precedent Constraint between two items. Whether they're backed by Variables or hard coded won't be known until you explore.
The blue color I think is that the preceding task must finish (Completion) - failure or success does not matter. The red dashed line coming in from offscreen is the Error/Failure path from a Task.
The Dashed line indicates that there is a Logical OR condition between all the elements that feed into the icon offscreen. A solid line will indicate a Logical AND condition.
Double click the blue precedent constraint and you'll see something like this

You can also right click on anything and select properties. An element like a Precedent Constraint will look like this

More complex tasks will have the Expressions in their own section i.e.

All that said, you should be able to add the package to an existing SSIS project and Visual Studio will upgrade it to the current revision. There can be hiccups with things like script components which are usually resolvable by opening up the script and rebuilding. Other things, like custom third party components, need to be updated to bind to the current version of SQL Server binaries.


How are you connecting?  Is the client on the same machine?  Same client?  You seem to be coming in from a different machine.  % is the world; you do not want root to get in that way.

If this is a Cloud setup, you were probably told what IP address to come in from, and that may be the only way to come in.

We need to know who set up your access and the details (except password).


How to improve this query so it'll run quicker

You can try this:

SELECT m.vlanID,
        v.vlan_name,
        m.interfaceName,
        count(1) as mac,
         n.nd_name,
         p.interfaceDescription,
         p.interfacePortType
    FROM  macs m
    INNER JOIN network_devices n
        ON n.nd_ip_address = m.ipAddr
    INNER JOIN vlans v
        ON m.vlanID = v.vlan_id
    INNER JOIN ports p
        ON p.vlanID = m.vlanID
    GROUP BY m.vlanID, v.vlan_name, m.interfaceName, n.nd_name, p.interfaceDescription, p.interfacePortType

A few things about the changes:

The cartesian join to inner join switch is purely cosmetic, unless the mysql implementation you're using doesn't support the cartesian inner join syntax, which would be very odd.
Removing the Order By clause should provide a performance bump, but depending on the way you're using the results may not be useful.
If m.macAddress is sometimes null, count(1) != count(m.macAddress)

These small optimizations stated, there is not a whole lot to optimize about this query itself. There is something about the context of the query that is causing the exceptional runtime.

Indexes on n.nd_ip_address, m.ipAddr,m.vlanID, v.vlan_id, and p.vlanID
would certainly help substantially. If you have to run the query multiple times, it would be well worth the time invested in creating the indexes even on temporary data.

It's even likely that re-creating the indexes every time the query runs would cause the overall runtime to be much lower.


Oracle 11g Rman restore with another database name from backup in windows platform

By default, the controlfile is backed up with your data. This is turned on or off by this command:

CONFIGURE CONTROLFILE AUTOBACKUP ON;

But as a matter of habit, I usually follow my data backups with:

backup current controlfile ;
backup spfile ;

I've had a great deal of trouble using duplicate database on Windows. Typically, I run into NTS permissions failures for the target login, which prevents the auxiliary db from creating. My efforts to troubleshoot these errors... well, I gave up.

This has led me to drafting a "playbook" document with all of the various OS and RMAN commands needed to manually restore a database to a new server with a new instance name. This also lets me tweak things to the new host with more control.

These scripts were tested in my Oracle 12.1 Windows environment, but may not be ideal for yours. I know RMAN is more willing to run SQL statements in 12c than in 11g, for example. Hopefully they help.

These scripts were created over several trial and error efforts, based on this link for the restore and this for changing the SID.
The steps are:

Make sure everything is backed up.

I like to get a fresh copy of the spfile and controlfile, as it makes it easier later.

backup device type disk incremental level = 0 section size 1g database plus archivelog;
backup current controlfile tag='Controlfile Level 0';
backup spfile tag='SPFile Level 0';

If your old and new servers do not use the same file paths for Oracle data, then run this:

select '  Set Newname For Datafile ' || file# || ' to ''' || name || ''';' from v$datafile;
select '  Alter Database Rename File ''' || Member || ''' To ''' || Member || ''';' from v$logfile;

take the output and change the paths / filenames as needed to match your new environment.

Copy backups from old to new host

Optional but I like the RMAN backup files to be on the box I'm restoring to. So log into the new server, and copy the full backups, archivelogs, and autobackup folders from the old server's fast recovery area. Note that you only need to go back to the most recent full backup, no need to get weeks worth of files.

Stage the new instance

On the new host, create your data folder, your fast recovery folder, and your admin folder. Make sure Oracle is installed and the Oracle user accounts have full control of these folders.

Create an empty instance, with the same name as the old:

oradim -new -sid <<old sid name>>

Set ORACLE_SID=<<old sid name>>
RMAN target /

Use RMAN to recover the SPFILE and Control Files.
inside RMAN, set the DBID to match the old instance's DB ID, and the file names and paths to match your environment:

SET DBID=1234567;
startup nomount;
restore spfile to 'C:\Oracle\Product\12.1.0\dbhome_1\database\init<<Old sid>>/ora' from 'f:\fast_recovery_area\<<old sid>>\BackupSet\2017_07_17\O1_MF_NNSNF_SPFILE_LEVEL_0_DOSTOJT8_.BKP';
create pfile='c:\oracle\product\12.1.0\dbhome_1\Database\pfile<<old sid>>.ora' from spfile='c:\oracle\product\12.1.0\dbhome_1\Database\Init<<old sid>>.ora';
shutdown immediate;

Do not close RMAN yet. Edit the pfile from the previous step in NotePad or NotePad++. Adjust the paths and memory requirements as needed to reflect the new environment. Don't change the name of the database, but change the folder paths to the new instance name.

Go back to your RMAN session and restore the control files. Again, fix paths as needed.

startup nomount pfile = 'c:\oracle\product\12.1.0\dbhome_1\Database\pfile<<old sid>>.ora’;
restore controlfile from 'F:\fast_recovery\<<old sid>>\BACKUPSET\2017_07_05\O1_MF_NCNNF_CONTROLFILE_LEVEL_0_DOSTO715_.BKP';
Alter Database Mount;

Restore your database files

Still in RMAN, inventory the backup files you copied over.

 catalog start with 'f:\fast_recovery_Area\<<old sid>>';

Now move your redo log files to their new folders in RMAN. You generated these scripts earlier.

Alter Database Rename File 'O:\ORADATA\<<old sid>>\REDO03.LOG' To 'f:\ORADATA\<<NEW sid>>\REDO03.LOG';
Alter Database Rename File 'O:\ORADATA\<<old sid>>\REDO02.LOG' To 'f:\ORADATA\<<new sid>>\REDO02.LOG';
Alter Database Rename File 'O:\ORADATA\<<old sid>>\REDO01.LOG' To 'f:\ORADATA\<<NEW Sid>>\REDO01.LOG';

And now we do the restore and recover, substitute the NewName scripts created earlier.

run {
    Set Newname For Datafile 1 to 'f:\ORADATA\<<new sid>>\SYSTEM01.DBF';
    Set Newname For Datafile 3 to 'f:\ORADATA\<<new sid>>\SYSAUX01.DBF';
    Set Newname For Datafile 5 to 'f:\ORADATA\<<new sid>>\UNDOTBS01.DBF';
    Set Newname For Datafile 6 to 'f:\ORADATA\<<new sid>>\USERS01.DBF';

    RESTORE DATABASE;
    SWITCH DATAFILE ALL;
    RECOVER DATABASE;
}

Once RMAN finishes the base restore and recovery, create a new SPFile.

create spfile from pfile = 'c:\oracle\product\12.1.0\dbhome_1\Database\pfile<<OLD sid>>.ora’;
alter database open resetlogs;

Oracle does not backup or restore TEMP tablespace. This will prevent the full recovery from succeeding. Create a new temp tablespace, drop the old, and then rebuild the original one.

CREATE TEMPORARY TABLESPACE TEMP_NEW TEMPFILE 'f:\OraData\<<NEW Sid>>\TempNew1.dbf' SIZE 5m autoextend on next 10m maxsize unlimited;
ALTER DATABASE DEFAULT TEMPORARY TABLESPACE TEMP_NEW;
DROP TABLESPACE TEMP including contents;
CREATE TEMPORARY TABLESPACE TEMP TEMPFILE 'f:\OraData\<<NEW Sid>>\Temp01.dbf' SIZE 50m autoextend on next 10m maxsize unlimited;
ALTER DATABASE DEFAULT TEMPORARY TABLESPACE TEMP;
DROP TABLESPACE TEMP_NEW including contents and datafiles;

Change the instance name

At this point, Oracle should be running a fully restored copy of your database. But you want a new name. In RMAN:

Shutdown immediate;
Startup mount;

Don't close RMAN, but from a new CMD window running as administrator:

 set ORACLE_SID=<<old sid>>
 NID target=/ DBNAME=<<new sid>>

Note the DBID this gives you, as you'll need it later for disaster recovery (notice that was our first RMAN command at the top).

Back in the RMAN window:

Shutdown Immediate;
Startup Mount;
Alter System Set DB_NAME=<<New sid>> scope=SPFILE;
Shutdown Immediate;
exit;

Start up your new DB!

From the other CMD window, create a new password file, and recreate the Windows services. Note, this utility does not use quotes around the path, unlike most cases in Oracle.

orapwd file=c:\oracle\product\12.1.0\dbhome_1\Database\pwd<<new SID>>.ora password=<SYS pwd> entries=25
oradim -delete -sid <<old sid>>
oradim -new -sid <<new sid>> -intpwd <SYS pwd> -startmode a

Start the database.

Set Oracle_SID=<<new sid>>
lsnrctl reload
sqlplus / as sysdba
Startup Mount;
Alter Database Open ResetLogs;

Back up your new database

At this point, your DB has no backups. Perform an RMAN backup before you do any work in this DB.


Query Processing at backend (I/O on disk)

Pretty much a WAG because you haven't shown EXPLAIN (BUFFERS, OUTPUT) data, but:

select id from demo_bbb b; -- database query time: ~26ms

If you're on 9.2 or newer this probably did an index-only scan on the unique index used to implement the primary key constraint. Given the latter times that's the most likely case; if it'd done a seqscan (as it would on older versions) you'd have got faster results from later queries.

select column_10 from demo_bbb b ;  -- database query time: 46ms, 48ms, 51ms
select column_05 from demo_bbb b ;  -- database query time: 37ms, 38 ms, 45ms
select * from demo_bbb b ;          -- database query time: 28ms, 32ms, 37ms

These are likely to be doing seqscans, and possibly out-of-line TOAST fetching.

The different times will be partly due to caching effects, possibly partly offset by the need to read in more out-of-line TOAST data when you select more columns.

PostgreSQL uses two levels of caching - the operating system's disk cache, and its own shared_buffers. Both are block caches. It does not have a query result cache.

For more information see the PostgreSQL manual and the existing resources about PostgreSQL benchmarking, tuning, caching behaviour, etc.


Having multiple ResultSets in parallel on same connection.. Will it cause any problem? [Mysql/Postgres]

SQL is not made for parallelism.  Adding such in your code will only make things more complex, and possibly slower.

For now, focus on writing non-parallel code.  When finished, you may need to ask the question of "how can I make this faster", with the sub-question of "would parallelism help".  The former question may involve indexes, reformulation of the query, merging two queries into one, etc.  Parallelism (in the application) is only rarely the 'right' way to approach SQL work.


This is an overly broad question but SSAS has security features that allow you to shield data for certain users.

You should look into Roles and Permissions.

Since you are talking about shielding costs you probably just want to limit access to certain measures so you're probably looking for Grant custom access to cell data which limit access to the values:

  Cell security is used to allow or deny access to measure data within a
  cube.  Cell permissions apply to data inside the cell, and not to its
  metadata. Notice how the cell is still visible in the results of a
  query, displaying a value of #N/A instead of the actual cell value.

If you want to hide the fact that the measure exists you need to look at Grant custom access to dimension data

  In Grant custom access to cell data (Analysis Services), it was
  explained that fully hiding all visual aspects of a measure, and not
  just its cell data, requires permissions on dimension members. This
  section explains how to deny access to the object metadata of a
  measure.


Naming a second additional column referencing another table column?

You may consider that the breaking of the convention is actually indicative of an error or inefficiency in the design:

For example,

I would have a lookup table for this instead of two occurences of the same foreign key field in a table.

Product Table
------------
Id
Description

Product_Currencies Table
------------
Product_Id
Currencies_Id
Condition_Id

Condition Table
-----------------------
Id
Description

Currencies
------------
Id
Code
Description

In effect, the Product_Currencies table formalizes the relationship :
Given this condition, use this currency for this product.

The condition table might contain values like:

Condition
ID     Description
----------------
1      Internal
2      External

The naming convention being broken may be a symptom of a problematic design- many conventions are partially intended to cause issues when violated.

Having two Currencies_Code in one table implies conditional use, which may imply another relation is necessary to describe it.

If you must have an answer to the original question:

It's totally and entirely dependent on who enforces and/or codifies your standard.


Access 2007 Work in a separate file using ACCDE

Yes, you can (and you should!!) split your application into two Access databases:

A backend containing all the tables.
Put this somewhere on a central network share.
A frontend containing code, forms, records and so on...basically everything except the actual tables (which are in the backend).
You can link the tables from the backend here, and each user should have his own copy of the frontend on his local machine.
It's quite easy to deploy the frontend in a way that it auto-updates each time a user runs it.

If you split your app this way, you can just deploy it now and your users can start using it.
Later, you can modify the forms and reports (or add new ones) and just deploy a new version of the frontend to your users, without them even really noticing it.