How to clear ad hoc queries from plan cache? As the title implies, I'm going to remove just ad hoc queries (not prepared queries) from plan cache in sql server 2014/2016 because it occupies more than 50% percent of my main memory. Do you have any suggestion?

Many Thanks.

So you just want to clear the Ad-hoc query plans and still dont want to clear the whole procedure cache. What you are asking is there in Clearing Your Ad-hoc SQL Plans While Keeping Your SP Plans Intact
The blog asks you to run
DBCC FREESYSTEMCACHE('SQL Plans')
As per the blog

The procedure cache actually consists of 4 distinct cache stores that hold different types of plans. Those cache stores are:

CACHESTORE_OBJCP - these are "Object Plans" - stored procedures, functions and triggers. Generally, the good stuff.
CACHESTORE_SQLCP - these are "SQL Plans" - ad-hoc SQL statements (including parameterized ones) and prepared statements. This is the
stuff that we are after.
CACHESTORE_PHDR - so-called "Bound Trees" for views, constraints and defaults. Irrelevant for the problem discussed.
CACHESTORE_XPROC - not really execution plans but rather pointers to the entry points of your extended SPs.

So you can see selectively clearing the SQLCP would remove prepared and ad-hoc plans. I tested this on my system.
Ran query
select objtype,
count(*) as number_of_plans,
sum(cast(size_in_bytes as bigint))/1024/1024 as size_in_MBs,
avg(usecounts) as avg_use_count
from sys.dm_exec_cached_plans
--where objtype='adhoc'
group by objtype

And the output was

You can see the the picture has 1264 ad-hoc plans and 69 prepared statements.
Now I selectively clear the SQLCP using DBCC FREESYSTEMCACHE('SQL Plans') and rerun the query again which gave me below output

Now you can see the ad-hoc and prepared plans are 2 and 6 respectively. While other are NOT affected.


How to improve this product rental schema? I have a rental system where each product's stock item can be rented from a location and returned to a different location (think Alamo, Hertz etc.). When saving an order, first an inventory event is saved (to lock the inventory), and then each order item is saved.

I'm curious to know how my schema can be improved to avoid denormalization. Can I somehow use the event table in a better way as a base for my order items?

Also if necessary, how should I adjust my design to avoid data inconsistency?

inventory_events
==========================
id                    (PK)
inventory_item_id     (FK)  --> inventory_items
location_id           (FK)  --> locations (Store 1, Store 2 etc.)
checked_out_flag
inventory_status_id   (FK)  --> inventory_status (rented out, stolen, on service etc.)
comment
event_date

order_items
=============================
id                       (PK)
order_id                 (FK)  --> orders
inventory_item_id        (FK)  --> inventory_items
status_id                (FK)  --> order_status (on hold, processed, prepared etc.)
unit_price
item_description
comment
deliver_at
delivery_to_address_id   (FK)  --> addresses
due_at
returned_at

When designing schemas that have a "current state" (in your case: What inventory is currently in use) and a historical log of states, it is generally a good idea to split those two concepts into two different tables.

In your case, you would have:

inventory_status
==========================
id                       (PK)
inventory_item_id        (FK)  --> inventory_items
location_id              (FK)  --> locations (Store 1, Store 2 etc.)
checked_out_flag
status_type_id           (FK)  --> status_id (rented out, stolen, on service etc.)
comment

And you will have

inventory_events
==========================
inventory_item_id          (PK, FK) --> inventory_items
location_id                (FK)     --> locations (Store 1, Store 2 etc.)
checked_out_flag
status_type_id             (FK)     --> As per above
comment
event_date                 (PK)     --> Together with the item and perhaps the status.

Whenever status (or any other thing you want to track) changes, you write a new row in inventory_events and update the row in inventory_status. You may even decided to do this with a trigger. You could argue that this is a slight break with normalisation. But until someone invents a temporal database with reasonable performance, this is the fastest and easiest way to query the database.

From your description, it is not clear to me if an inventory_item can have more than one status at the same time (for example, can a car be BOTH rented out and stolen at the same time?). If each inventory_item can only have one status, you can collapse the table inventory_item and inventory_status into one without breaking normalisation.

A design like this has the advantage that joins are clean and easy to write. You do no need to look for the latest status - as that is always present in the inventory_status table.

A lot of ERP systems handle the balance sheet in a very similar manner - with a snapshot table (the balance sheet) and a log of transactions that add up to it. With the risk of jumping to conclusion, this may be the analogy that Pieter is cleverly hinting at.


Gathering sp_BlitzFirst data into a centralized database I have setup a Central Management Server and registered all of my SQL servers (prods and devs). I want to save the results of sp_BlitzFirst from running it against all of my servers to a centralized database 'DBAtools' on my test box for later analysis. What is the most efficient method for achieving this task?

Thank you so much in advance.

There's nothing special about the data being built by sp_BlitzFirst. You can really zoom out and ask, "What's the most efficient way of moving data between any SQL Servers?"

Some of the approaches that folks have used include:

Pulling the data periodically from a central server via linked servers or openquery
Using their favorite ETL tool, like SSIS
Log shipping their management databases to a central location
Writing data movement jobs with PowerShell

There's no one right answer: it's just whatever data movement you're most comfortable with and most able to support. I don't ship a method with the First Responder Kit because frankly, I don't want to do support for this kind of thing. (It would come with all the baggage that comes with anytime people try to connect two SQL Servers to each other, especially over networks.)


Is it bad to shrink + reindex after removing a significant part of data? I have a test environment running on SQL Server 2017 Express that uses only recent data (a job removes data older than three months once per month).

The job does the following:

Using a while loop, it removed data older than three months
Shrinks the database, if its size is above some threshold
Reindexes all tables

This article describes how awful is to shrink the database due to index fragmentation and why reindexing also requires extra space.

Last run started from a 9GB database. Deleting tooks 3-4 minutes and reindexing about 30 seconds. The database was reduced to about 5GB.

Since this is a test environment, I can afford this downtime without any issues.

Question: Is it bad to shrink + reindex after removing a significant part of data?

Well, ask yourself: What is the point of shrinking, if you know the reindex (and any other data changes, really) will expand the file again after the shrink?

Even if the reindex doesn't bring it back up to 9GB, if the database is going to grow in the future, I see absolutely zero value in reclaiming some of that disk space temporarily. What will you do with that space until the database has to grown again? Lease it out and then evict them? This is like a restaurant disassembling a table every time a customer gets up and leaves, only to reassemble it when a new customer arrives.

In your situation (where you need to keep the data file under 10GB because of the license you chose and no other reason), there is no point going down to 5GB just to grow back to 9GB again. I would rather have some active monitor that warns you when the data file is getting back up into the 9GB space again (or just keep it at 9GB!) and the space used is actually approaching 9GB.

Instead of thinking about it as a "keep the last three months" requirement, think of it as "keep 9GB of data" requirement. Which you'll have to revisit as your data volumes per time period (day/week/whatever) go up anyway. What happens when you amass more than 10GB of data inside of three months?


SELECT , GROUP AND DISPLAY IN TABULAR FORM AND COUNTING INSTANCES Answer to this query was received as -

SELECT location,
   AVG(CASE WHEN qtr = 'Q1' THEN achievement END) q1-average,
   AVG(CASE WHEN qtr = 'Q2' THEN achievement END) q2-average
FROM sourcetable
GROUP BY location

Now, the problem is while counting the instances generated with the above query. The final output I require as shown below -

Can such output be generated using a single query?

SELECT location,
   AVG(CASE WHEN qtr = 'Q1' THEN achievement END) `q1-average`,
   AVG(CASE WHEN qtr = 'Q2' THEN achievement END) `q2-average`,
   COUNT(CASE WHEN achievement BETWEEN 3 AND 4 THEN 1 END) `3.0-4.0`,
   COUNT(CASE WHEN achievement BETWEEN 4 AND 5 THEN 1 END) `4.0-5.0`,
   COUNT(CASE WHEN achievement BETWEEN 5 AND 6 THEN 1 END) `5.0-6.0`,
   COUNT(CASE WHEN achievement BETWEEN 6 AND 7 THEN 1 END) `6.0-7.0`
FROM sourcetable
GROUP BY location

In that form the border value (for example, 4.0) will be calculated both in lower and in upper range. To prevent this use double compare (for example COUNT(CASE WHEN achievement >= 3 AND achievement < 4 THEN 1 END)) instead of BETWEEN.


Find highest level of a hierarchical field: with vs without CTEs note: this question has been updated to reflect that we are currently using MySQL, having done so, I would like to see a how much easier it would be if we switched to a CTE-supporting database.

I have a self-referencing table with a primary key, id and a foreign key parent_id.

+------------+--------------+------+-----+---------+----------------+
| Field      | Type         | Null | Key | Default | Extra          |
+------------+--------------+------+-----+---------+----------------+
| id         | int(11)      | NO   | PRI | NULL    | auto_increment |
| parent_id  | int(11)      | YES  |     | NULL    |                |
| name       | varchar(255) | YES  |     | NULL    |                |
| notes      | text         | YES  |     | NULL    |                |
+------------+--------------+------+-----+---------+----------------+

Given a name, how can I query the top-level parent?

Given a name, how can I query all of the id's associated with a record of name = 'foo'?

context: I am not a dba, but am planning to ask a dba to implement this type of hierarchical structure and would like to test some queries. The motivation for doing so is described by Kattge et al 2011.

Here is an example of the relationships among ids in the table:

-- -----------------------------------------------------
-- Create a new database called 'testdb'
-- -----------------------------------------------------
SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0;
SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0;
SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='TRADITIONAL';

CREATE SCHEMA IF NOT EXISTS `testdb` DEFAULT CHARACTER SET latin1 COLLATE latin1_swedish_ci ;
USE `testdb` ;

-- -----------------------------------------------------
-- Table `testdb`.`observations`
-- -----------------------------------------------------
CREATE  TABLE IF NOT EXISTS `testdb`.`observations` (
  `id` INT NOT NULL ,
  `parent_id` INT NULL ,
  `name` VARCHAR(45) NULL ,
  PRIMARY KEY (`id`) )
ENGINE = InnoDB;

SET SQL_MODE=@OLD_SQL_MODE;
SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;

-- -----------------------------------------------------
-- Add Example Data Set
-- -----------------------------------------------------

INSERT INTO observations VALUES (1,3), (2,5), (3,NULL), (4,10),
   (5,NULL), (6,1), (7,5), (8,10), (9,10), (10,3);

You definitely have to script this via MySQL Stored Procedure Language

Here is a Stored Function called GetParentIDByIDto Retrieve a ParentID given an ID to Search For

DELIMITER $$
DROP FUNCTION IF EXISTS `junk`.`GetParentIDByID` $$
CREATE FUNCTION `junk`.`GetParentIDByID` (GivenID INT) RETURNS INT
DETERMINISTIC
BEGIN
    DECLARE rv INT;

    SELECT IFNULL(parent_id,-1) INTO rv FROM
    (SELECT parent_id FROM pctable WHERE id = GivenID) A;
    RETURN rv;
END $$
DELIMITER ;

Here is a Stored Function called GetAncestryto Retrieve a List of ParentIDs starting from the First Generation all the up the hierarchy given an ID to start with:

DELIMITER $$
DROP FUNCTION IF EXISTS `junk`.`GetAncestry` $$
CREATE FUNCTION `junk`.`GetAncestry` (GivenID INT) RETURNS VARCHAR(1024)
DETERMINISTIC
BEGIN
    DECLARE rv VARCHAR(1024);
    DECLARE cm CHAR(1);
    DECLARE ch INT;

    SET rv = '';
    SET cm = '';
    SET ch = GivenID;
    WHILE ch > 0 DO
        SELECT IFNULL(parent_id,-1) INTO ch FROM
        (SELECT parent_id FROM pctable WHERE id = ch) A;
        IF ch > 0 THEN
            SET rv = CONCAT(rv,cm,ch);
            SET cm = ',';
        END IF;
    END WHILE;
    RETURN rv;
END $$
DELIMITER ;

Here is something to generate sample data:

USE junk
DROP TABLE IF EXISTS pctable;
CREATE TABLE pctable
(
    id INT NOT NULL AUTO_INCREMENT,
    parent_id INT,
    PRIMARY KEY (id)
) ENGINE=MyISAM;
INSERT INTO pctable (parent_id) VALUES (0);
INSERT INTO pctable (parent_id) SELECT parent_id+1 FROM pctable;
INSERT INTO pctable (parent_id) SELECT parent_id+2 FROM pctable;
INSERT INTO pctable (parent_id) SELECT parent_id+3 FROM pctable;
INSERT INTO pctable (parent_id) SELECT parent_id+4 FROM pctable;
INSERT INTO pctable (parent_id) SELECT parent_id+5 FROM pctable;
SELECT * FROM pctable;

Here is what it generates:

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

mysql> CREATE TABLE pctable
    -> (
    ->     id INT NOT NULL AUTO_INCREMENT,
    ->     parent_id INT,
    ->     PRIMARY KEY (id)
    -> ) ENGINE=MyISAM;
Query OK, 0 rows affected (0.05 sec)

mysql> INSERT INTO pctable (parent_id) VALUES (0);
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO pctable (parent_id) SELECT parent_id+1 FROM pctable;
Query OK, 1 row affected (0.00 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> INSERT INTO pctable (parent_id) SELECT parent_id+2 FROM pctable;
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> INSERT INTO pctable (parent_id) SELECT parent_id+3 FROM pctable;
Query OK, 4 rows affected (0.00 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql> INSERT INTO pctable (parent_id) SELECT parent_id+4 FROM pctable;
Query OK, 8 rows affected (0.01 sec)
Records: 8  Duplicates: 0  Warnings: 0

mysql> INSERT INTO pctable (parent_id) SELECT parent_id+5 FROM pctable;
Query OK, 16 rows affected (0.00 sec)
Records: 16  Duplicates: 0  Warnings: 0

mysql> SELECT * FROM pctable;
+----+-----------+
| id | parent_id |
+----+-----------+
|  1 |         0 |
|  2 |         1 |
|  3 |         2 |
|  4 |         3 |
|  5 |         3 |
|  6 |         4 |
|  7 |         5 |
|  8 |         6 |
|  9 |         4 |
| 10 |         5 |
| 11 |         6 |
| 12 |         7 |
| 13 |         7 |
| 14 |         8 |
| 15 |         9 |
| 16 |        10 |
| 17 |         5 |
| 18 |         6 |
| 19 |         7 |
| 20 |         8 |
| 21 |         8 |
| 22 |         9 |
| 23 |        10 |
| 24 |        11 |
| 25 |         9 |
| 26 |        10 |
| 27 |        11 |
| 28 |        12 |
| 29 |        12 |
| 30 |        13 |
| 31 |        14 |
| 32 |        15 |
+----+-----------+
32 rows in set (0.00 sec)

Here are what the functions generate for each value:

mysql> SELECT id,GetParentIDByID(id),GetAncestry(id) FROM pctable;
+----+---------------------+-----------------+
| id | GetParentIDByID(id) | GetAncestry(id) |
+----+---------------------+-----------------+
|  1 |                   0 |                 |
|  2 |                   1 | 1               |
|  3 |                   2 | 2,1             |
|  4 |                   3 | 3,2,1           |
|  5 |                   3 | 3,2,1           |
|  6 |                   4 | 4,3,2,1         |
|  7 |                   5 | 5,3,2,1         |
|  8 |                   6 | 6,4,3,2,1       |
|  9 |                   4 | 4,3,2,1         |
| 10 |                   5 | 5,3,2,1         |
| 11 |                   6 | 6,4,3,2,1       |
| 12 |                   7 | 7,5,3,2,1       |
| 13 |                   7 | 7,5,3,2,1       |
| 14 |                   8 | 8,6,4,3,2,1     |
| 15 |                   9 | 9,4,3,2,1       |
| 16 |                  10 | 10,5,3,2,1      |
| 17 |                   5 | 5,3,2,1         |
| 18 |                   6 | 6,4,3,2,1       |
| 19 |                   7 | 7,5,3,2,1       |
| 20 |                   8 | 8,6,4,3,2,1     |
| 21 |                   8 | 8,6,4,3,2,1     |
| 22 |                   9 | 9,4,3,2,1       |
| 23 |                  10 | 10,5,3,2,1      |
| 24 |                  11 | 11,6,4,3,2,1    |
| 25 |                   9 | 9,4,3,2,1       |
| 26 |                  10 | 10,5,3,2,1      |
| 27 |                  11 | 11,6,4,3,2,1    |
| 28 |                  12 | 12,7,5,3,2,1    |
| 29 |                  12 | 12,7,5,3,2,1    |
| 30 |                  13 | 13,7,5,3,2,1    |
| 31 |                  14 | 14,8,6,4,3,2,1  |
| 32 |                  15 | 15,9,4,3,2,1    |
+----+---------------------+-----------------+
32 rows in set (0.02 sec)

MORAL OF THE STORY : Recursive data retrieval must be scripted in MySQL

UPDATE 2011-10-24 17:17 EDT

Here is the reverse of GetAncestry. I call it GetFamilyTree.

Here is the algorithm:

Place Given ID in Queue
Loop

Dequeue into front_id
Retrieve all ids into queue_children whose parent_id = front_id
Append queue_children to retval_list (rv)
Enqueue queue_children
Repeat until queue and queue_children are concurrently empty

I believe from my Data Structures and Algorithms classes in College, this is called something like preorder/prefix tree traversal.

Here is the code:

DELIMITER $$

DROP FUNCTION IF EXISTS `junk`.`GetFamilyTree` $$
CREATE FUNCTION `junk`.`GetFamilyTree` (GivenID INT) RETURNS varchar(1024) CHARSET latin1
DETERMINISTIC
BEGIN

    DECLARE rv,q,queue,queue_children VARCHAR(1024);
    DECLARE queue_length,front_id,pos INT;

    SET rv = '';
    SET queue = GivenID;
    SET queue_length = 1;

    WHILE queue_length > 0 DO
        SET front_id = FORMAT(queue,0);
        IF queue_length = 1 THEN
            SET queue = '';
        ELSE
            SET pos = LOCATE(',',queue) + 1;
            SET q = SUBSTR(queue,pos);
            SET queue = q;
        END IF;
        SET queue_length = queue_length - 1;

        SELECT IFNULL(qc,'') INTO queue_children
        FROM (SELECT GROUP_CONCAT(id) qc
        FROM pctable WHERE parent_id = front_id) A;

        IF LENGTH(queue_children) = 0 THEN
            IF LENGTH(queue) = 0 THEN
                SET queue_length = 0;
            END IF;
        ELSE
            IF LENGTH(rv) = 0 THEN
                SET rv = queue_children;
            ELSE
                SET rv = CONCAT(rv,',',queue_children);
            END IF;
            IF LENGTH(queue) = 0 THEN
                SET queue = queue_children;
            ELSE
                SET queue = CONCAT(queue,',',queue_children);
            END IF;
            SET queue_length = LENGTH(queue) - LENGTH(REPLACE(queue,',','')) + 1;
        END IF;
    END WHILE;

    RETURN rv;

END $$

Here is what each row produces

mysql> SELECT id,parent_id,GetParentIDByID(id),GetAncestry(id),GetFamilyTree(id) FROM pctable;
+----+-----------+---------------------+-----------------+--------------------------------------------------------------------------------------+
| id | parent_id | GetParentIDByID(id) | GetAncestry(id) | GetFamilyTree(id)                                                                    |
+----+-----------+---------------------+-----------------+--------------------------------------------------------------------------------------+
|  1 |         0 |                   0 |                 | 2,3,4,5,6,9,7,10,17,8,11,18,15,22,25,12,13,19,16,23,26,14,20,21,24,27,32,28,29,30,31 |
|  2 |         1 |                   1 | 1               | 3,4,5,6,9,7,10,17,8,11,18,15,22,25,12,13,19,16,23,26,14,20,21,24,27,32,28,29,30,31   |
|  3 |         2 |                   2 | 2,1             | 4,5,6,9,7,10,17,8,11,18,15,22,25,12,13,19,16,23,26,14,20,21,24,27,32,28,29,30,31     |
|  4 |         3 |                   3 | 3,2,1           | 6,9,8,11,18,15,22,25,14,20,21,24,27,32,31                                            |
|  5 |         3 |                   3 | 3,2,1           | 7,10,17,12,13,19,16,23,26,28,29,30                                                   |
|  6 |         4 |                   4 | 4,3,2,1         | 8,11,18,14,20,21,24,27,31                                                            |
|  7 |         5 |                   5 | 5,3,2,1         | 12,13,19,28,29,30                                                                    |
|  8 |         6 |                   6 | 6,4,3,2,1       | 14,20,21,31                                                                          |
|  9 |         4 |                   4 | 4,3,2,1         | 15,22,25,32                                                                          |
| 10 |         5 |                   5 | 5,3,2,1         | 16,23,26                                                                             |
| 11 |         6 |                   6 | 6,4,3,2,1       | 24,27                                                                                |
| 12 |         7 |                   7 | 7,5,3,2,1       | 28,29                                                                                |
| 13 |         7 |                   7 | 7,5,3,2,1       | 30                                                                                |
| 14 |         8 |                   8 | 8,6,4,3,2,1     | 31                                                                                |
| 15 |         9 |                   9 | 9,4,3,2,1       | 32                                                                                |
| 16 |        10 |                  10 | 10,5,3,2,1      |                                                                                |
| 17 |         5 |                   5 | 5,3,2,1         |                                                                                |
| 18 |         6 |                   6 | 6,4,3,2,1       |                                                                                |
| 19 |         7 |                   7 | 7,5,3,2,1       |                                                                                |
| 20 |         8 |                   8 | 8,6,4,3,2,1     |                                                                                |
| 21 |         8 |                   8 | 8,6,4,3,2,1     |                                                                                |
| 22 |         9 |                   9 | 9,4,3,2,1       |                                                                                |
| 23 |        10 |                  10 | 10,5,3,2,1      |                                                                                |
| 24 |        11 |                  11 | 11,6,4,3,2,1    |                                                                                |
| 25 |         9 |                   9 | 9,4,3,2,1       |                                                                                |
| 26 |        10 |                  10 | 10,5,3,2,1      |                                                                                |
| 27 |        11 |                  11 | 11,6,4,3,2,1    |                                                                                |
| 28 |        12 |                  12 | 12,7,5,3,2,1    |                                                                                |
| 29 |        12 |                  12 | 12,7,5,3,2,1    |                                                                                |
| 30 |        13 |                  13 | 13,7,5,3,2,1    |                                                                                |
| 31 |        14 |                  14 | 14,8,6,4,3,2,1  |                                                                                |
| 32 |        15 |                  15 | 15,9,4,3,2,1    |                                                                                |
+----+-----------+---------------------+-----------------+--------------------------------------------------------------------------------------+
32 rows in set (0.04 sec)

This algorithm works cleanly provided there are no cyclic paths. If there exists any cyclic paths, you would have to add a 'visited' column to the table.

Once you add visited column, here is the algorithm blocking cyclic relationships:

Place Given ID in Queue
Mark all visited with 0
Loop

Dequeue into front_id
Retrieve all ids into queue_children whose parent_id = front_id and visited = 0
Mark all queue_children just retrieved with visited = 1
Append queue_children to retval_list (rv)
Enqueue queue_children
Repeat until queue and queue_children are concurrently empty

UPDATE 2011-10-24 17:37 EDT

I created a new table called observations and populated your sample data. I changed the stored procedures to use observations instead of pctable. Here is your output:

mysql> CREATE TABLE observations LIKE pctable;
Query OK, 0 rows affected (0.04 sec)

mysql> INSERT INTO observations VALUES (1,3), (2,5), (3,0), (4,10),(5,0),(6,1),(7,5),(8,10),(9,10),(10,3);
Query OK, 10 rows affected (0.00 sec)
Records: 10  Duplicates: 0  Warnings: 0

mysql> SELECT * FROM observations;
+----+-----------+
| id | parent_id |
+----+-----------+
|  1 |         3 |
|  2 |         5 |
|  3 |         0 |
|  4 |        10 |
|  5 |         0 |
|  6 |         1 |
|  7 |         5 |
|  8 |        10 |
|  9 |        10 |
| 10 |         3 |
+----+-----------+
10 rows in set (0.00 sec)

mysql> SELECT id,parent_id,GetParentIDByID(id),GetAncestry(id),GetFamilyTree(id) FROM observations;
+----+-----------+---------------------+-----------------+-------------------+
| id | parent_id | GetParentIDByID(id) | GetAncestry(id) | GetFamilyTree(id) |
+----+-----------+---------------------+-----------------+-------------------+
|  1 |         3 |                   3 |                 | 6                 |
|  2 |         5 |                   5 | 5               |                   |
|  3 |         0 |                   0 |                 | 1,10,6,4,8,9      |
|  4 |        10 |                  10 | 10,3            |                   |
|  5 |         0 |                   0 |                 | 2,7               |
|  6 |         1 |                   1 | 1               |                   |
|  7 |         5 |                   5 | 5               |                   |
|  8 |        10 |                  10 | 10,3            |                   |
|  9 |        10 |                  10 | 10,3            |                   |
| 10 |         3 |                   3 | 3               | 4,8,9             |
+----+-----------+---------------------+-----------------+-------------------+
10 rows in set (0.01 sec)

UPDATE 2011-10-24 18:22 EDT

I changed the code for GetAncestry. There was WHILE ch > 1 it should be WHILE ch > 0

mysql> SELECT id,parent_id,GetParentIDByID(id),GetAncestry(id),GetFamilyTree(id) FROM observations;
+----+-----------+---------------------+-----------------+-------------------+
| id | parent_id | GetParentIDByID(id) | GetAncestry(id) | GetFamilyTree(id) |
+----+-----------+---------------------+-----------------+-------------------+
|  1 |         3 |                   3 | 3               | 6                 |
|  2 |         5 |                   5 | 5               |                   |
|  3 |         0 |                   0 |                 | 1,10,6,4,8,9      |
|  4 |        10 |                  10 | 10,3            |                   |
|  5 |         0 |                   0 |                 | 2,7               |
|  6 |         1 |                   1 | 1,3             |                   |
|  7 |         5 |                   5 | 5               |                   |
|  8 |        10 |                  10 | 10,3            |                   |
|  9 |        10 |                  10 | 10,3            |                   |
| 10 |         3 |                   3 | 3               | 4,8,9             |
+----+-----------+---------------------+-----------------+-------------------+
10 rows in set (0.01 sec)

Try it now !!!


SQL server taking too much time in opening properties in SSMS I am using sql server express 2008 r2 SP2 from about 2 years. Now a days when i try to check properties of database using sql server management studio it takes too much time(25 to 30 seconds) to open properties window for any database. But other working such as connection to the server, backup of database are almost same as before.I have not changed any settings or added new large databases recently. So what may be the reason behind this slow behavior? If anyone wants extra information on this problem please let me know.

Now I'm not stating that this is the solution, but I have encountered various issues with various versions of SQL Server Management Studio if SSMS cannot retrieve Microsoft's Certificate Revocation List from the internet. When SSMS starts or runs various commands, it can display slow performance.

Solution

Downloading the MicrosoftRootAuthority.crl file from the following location and installing it into your trusted certificates, can alleviate some of the issues surrounding SSMS and slow performance.

https://crl.microsoft.com/pki/crl/products/MicrosoftRootAuthority.crl


Mysql hand machine due to high load average and CPU Mysql is used by only one application which only fire query periodically.
When i restart server,It run smooth for period of time.
and then crash the whole server.

My my.cnf file

[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
user=mysql
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
innodb_file_per_table=1
set-variable = max_connections=200
log-slow-queries=/var/lib/mysql/slow.log
safe-show-database
query_cache_size=128M
join_buffer_size=50M
tmp_table_size=128M
max_heap_table_size=128M
table_cache=10K
long_query_time =10
open_files_limit=50000
table_open_cache=20000
table_cache=20000
query_cache_limit=134217728
innodb_buffer_pool_size=900M

[mysqld_safe]

log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

From your screenshot, it seems you are running on a machine just under 4GB of RAM. You also have ~4GB of SWAP space that MySQL could use if necessary. A rough calculation of how much memory your settings will allow is ~11.5GB.

From mysqlcalculator.com:

Due to the complex nature of memory allocation, this is an estimate of a worst-case scenario only.

Out of the box, your join_buffer_size is extremely large. This is a per-session thread that can be allocated multiple times in a query:

  For a complex join between several tables for which indexes are not used, multiple join buffers might be necessary.

I recommend setting this back to the default (in 5.6, that is 256kb). If you find many queries that are starting to show 'join_buffer' in the explain plan, then you should examine them for better index usage. If that fails, you might consider increasing join_buffer_size at the SESSION level for specific queries.


can I copy-paste SQL Server 2005 Express on XP Pro or Vista machine and make it work without installer? I am having installer problems on several machines trying to install SQL Server Express 2005 Express as well as later versions (but I would be happy with 2005 anyway). However, I do have one machine where it runs fine. So, is there a way to clone the installation by copy-pasting the files and then doing some magic to make it work? So far when I put the folder into another XP Pro machine and tried launch ssmsee.exe, it didn't start - command executed in cmd quietly and nothing happened.

Is this approach in fact doable with some relevant fixes? Or is the installer the only way?

No, you need to go through the installer to get SQL Express working on the other machines.


Replay (re-execute) MySQL SELECT queries from a log file MySQL Benchmarking

I would like to evaluate the performance of a different MySQL instance by re-executing real queries from log files. I am aware of tools like mysqlslap, that produces random and autogenerated queries, but I would rather like to test the system with realistic queries.

In order to benchmark the new system, I would ideally collect all queries either using the slow log or the general log and then replay the queries to the new system.

It should be possible to filter queries, for instance I would like to replay SELECT statements only. I am wondering if there exists an actively maintained procedure or a best practice that how to achieve warm-up or benchmark functionality.

Requirements

Replay (re-execute) real queries collected from a running MySQL instance
Filter statements by statement type (INSERT, UPDATE, DELETE) and DML
Get statistics (how long did the query run before, after the migration etc)

Problem: Tools are outdated

Percona offered several tools that seemed to be designed for that purpose.    All of them have been removed from the current Percona Toolkit. These tools were:

pt-log-player
The replay option for pt-query-digest
query-playback

Potential solutions

Using a Debian Wheezy docker container gives easy access to an older Percona Toolkit version where the pt-log-player is available. Running a container with docker run -it --network="host" --name wheezy debian:wheezy /bin/bash and installing the legacy versions inside works:

apt-get update
apt-get install percona-toolkit mysql-client

I also tried percona-playback, but it does not seem to be very actively maintained either. That would be a great tool, but it is hardly documented and I could not find a method for filtering SELECT statements only, as I do not want to run other DML statements. I only saw that Percona Server offers a flag for enabling a read only option, but this is not available with vanilla MySQL or AWS RDS.

However I would rather like to use a procedure that does not rely on unmaintained tools.

Imagined Workflow

Enable slow query log or general log
Parse and analyze log file
Filter relevant queries
Re-execute queries
Compare results

Are there any tools how I could achieve this? What is the best way to filter the slow query log? How to replay the queries sanely?

I found a solution for my problem. It is a combination of Percona pt-query-digest and Percona playback.

First, I filter the slow log by database name and exclude all statements that do not start with select.

cat mysql-slow.log |  pt-query-digest --filter '(($event->{db}) =~ /my_database/) && $event->{arg} =~ m/^select/i' --output slowlog > my_database_selects.log

As output, I get a new slow log file that only contains the desired queries.

percona-playback --mysql-max-retries 1 --mysql-host example.org --mysql-port 3306 --mysql-username warmup --mysql-password S-E-C-R-E-T --ignore-row-result-diffs --query-log-file ~/my_database_selects.log

As result, I get an overview of the query execution time and a warmed up database.

Detailed Report
----------------
SELECTs  : 41666 queries (33379 faster, 8287 slower)
INSERTs  : 0 queries (0 faster, 0 slower)
UPDATEs  : 0 queries (0 faster, 0 slower)
DELETEs  : 0 queries (0 faster, 0 slower)
REPLACEs : 0 queries (0 faster, 0 slower)
DROPs    : 0 queries (0 faster, 0 slower)

Report
------
Executed 41666 queries
Spent 00:00:45.795945 executing queries versus an expected 00:02:07.966942 time.
33379 queries were quicker than expected, 8287 were slower
A total of 0 queries had errors.
Expected 79870 rows, got 79868 (a difference of 2)
Number of queries where number of rows differed: 0.

Average of 265.39 queries per connection (157 connections).

I hope this helps someone with a similar problem.

Installing Percona Playback

There is a bug in the current version that requires to change a library path.

sudo apt-get install libtbb-dev libmysqlclient-dev libboost-program-options-dev libboost-thread-dev libboost-regex-dev libboost-system-dev libboost-chrono-dev pkg-config cmake  libssl-dev
git clone https://github.com/Percona-Lab/query-playback.git
cd query-playback/

Edit the the file CMakeLists.txt  (in the directory   ~/git/query-playback/percona_playback/mysql_client/CMakeLists.txt ) and replace  find_library(MYSQL_LIB"mysqlclient_r" PATH_SUFFIXES "mysql") with find_library(MYSQL_LIB "mysqlclient" PATH_SUFFIXES "mysql") (remove the _r suffix).

mkdir build_dir
cd build_dir
cmake -DCMAKE_BUILD_TYPE=RelWithDebInfo ..
make
sudo make install

Here is a link with some background


How can I specify which MariaDB instance to launch in a Galera Cluster I have several MariaDB servers, each with multiple instances running. I'm trying to figure out how would I bootstrap a Galera cluster but specify which instance I want to launch i.e:

node1, 2, and 3 each has instances 1-4 running via mysqld_multi, each running on their own port.

I want to use instance 4 on each server for the cluster, so I shut each one down.

On my test cluster (which has a single instance of MariaDB running) bootstrapping is simple: start it on the first node with galera_new_cluster then bring up the other two servers.

How would I do the same thing but specify GNR "4" to bootstrap the cluster?

According to the output from mysqld_multi --help:

--wsrep-new-cluster  Bootstrap a cluster.

So on the node you want to bootstrap, I'm assuming you should be able to do:

mysqld_multi --wsrep-new-cluster start 4

And then start the instances on the other two the normal way, assuming all the instances have the necessary config files.


MySQL Duplicate Key Entry Error when there is not already an entry for this key Duplicate entry '35839' for key 'PRIMARY'

I am receiving the above error message suddenly on a database I keep logs in.  This is an auto-increment field which the last key for the last entry is 35838.  If I perform a search for the key 35839, no results are returned.  (Also, I'm looking at the database through phpMyAdmin and can see that the last record is 35838.)

The table structure is:

CREATE TABLE IF NOT EXISTS `raw_data` (
  `id` int(12) unsigned NOT NULL AUTO_INCREMENT,
  `oid` int(12) unsigned NOT NULL,
  `data` longtext NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `oid` (`oid`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=35839 ;

You can see that the next ID it is expecting to make is 35839.  Yet it thinks that it is already in the table...

Also, the oid that is trying to be inserted is also unique.  It is an order ID being generated by another system and is never duplicated.  Plus, I've checked to see if it is already in the database and it is also not in the database already.

Is there anything I could be overlooking as to why this would suddenly start telling me that it is trying to enter a duplicate?  It has been working for two years now.  haha~

Thanks!

Select Statement

Select * From raw_data Where id = 35839;

MySQL returned an empty result set (i.e. zero rows). (Query took 0.0012 sec)

From @ypercubeᵀᴹ's suggestion:

The table is MyISAM so this is probably a corruption issue. Try running CHECK and then REPAIR the table (taking the necessary backup before!)
Details and various options for both commands can be found in CHECK and the links from there.

I then ran Check Table raw_data Extended which resulted in:
livereps_webdb.raw_data     check   warning     1 client is using or hasn't closed the table prope...
livereps_webdb.raw_data     check   warning     Size of datafile is: 12921069568       Should be: ...
livereps_webdb.raw_data     check   error   Found 15839 keys of 15838
livereps_webdb.raw_data     check   error   Corrupt

I then ran Repair Table raw_data Extended which resulted in:
livereps_webdb.raw_data     repair  info    Found block that points outside data file at 12921...
livereps_webdb.raw_data     repair  info    Found block with too small length at 12921068496; ...
livereps_webdb.raw_data     repair  info    Found block that points outside data file at 12921...
livereps_webdb.raw_data     repair  info    Found block that points outside data file at 12921...
livereps_webdb.raw_data     repair  status  OK

This fixed the problem.


SharePoint Database Creation Failure I wonder if this question will need to be moved to the SharePoint SE site, but I believe this could be a simple a SQL Server security fix.

A little back story:
Back in April we upgraded our SharePoint SQL Server backend (mirrored) from SQL 2005 to SQL 2014.  We did this by way of mirroring from SQL 2005 (primary) to SQL 2014 (mirror).  I don't believe the security settings and logins were extracted from the old server and placed in the new.  I'll have to reach out to my infrastructure department to see if the server has been completely decommissioned.

My SharePoint Admin is trying to create a new site from the SharePoint Central Admin Site (SharePoint 2010), but gets the following error.

  Error "Directory lookup for the file "D:\Data\NewSiteDB.mdf" failed
  with the operating system error 2(The system cannot find the file
  specified.). CREATE DATABASE failed. Some file names listed could not
  be created."

He told me that he used to be able to use his Active Directory account or a SQL Server SysAdmin account to create new sites, now he can't.  I'm pretty sure that his AD account was not an admin of any kind on the SQL Servers regarding the SQL 2005 configuration.  Further, he stated that he used a SQL Server SysAdmin account (in the SQL 2014 configuration) but it still failed.

What can I do if the old server is decomm'd?
If the server isn't decomm'd, and I can access, how do I extract all the logins, permissions, SID's, etc.?
Is it possible to create a new mapping from the SharePoint Central Admin site to a new SQL/Windows login?

Let me know if you need any other info and thank you in advance for your help!

This is an operating system error, reported to the SQL Server service. The SQL Server service account, which creates the files, needs to have at least change permissions on the d:\data folder and it needs to exist.


Change only one cell value on select statement i have these db tables:
 Table Combo:
id int identity(1,1)
name varchar
duration int
type int

table related
sourcecombo int
destinationcombo int
finalCombo int

sales
id int identity(1,1)
username varchar
comboid int
saledate datetime

Now, if a combo has a duration=0, it means that the client is upgrading or downgrading its product. In the interface, I need to display the destinactioncombo value, with the condition that the sales table has one record comboid, where duration=0.But I need to display the value only at the cell that has as a primary value sourceCombo. The query I am trying to use is the following:
    if exists
(select comboid
from sales
inner join combo on sales.comboid=combo.id
where combo.duration=0 and  username=@name)
select sales.username,saledate,
(select destinationcombo from related
where FinalCombo =
(select comboid
from sales where
saledate=(select max(saledate) from
Sales where username=@name)))
as comboid from Sales where username=@name
else select  '0'

where @name is a value taken by input from the user. The result i get until now, is the following:

The problem is, that it does capture the destinationCombo as it should, but it displays it at every single row when it has to display only at the last row.
UPDATE 1
So, the steps I should follow to achieve the result are like follows:

Check the sales table if it has any combo with duration=0 for
the selected user
If there is a result, then take the most recent combo (with
duration<>0 and with most recent saledate)
After finding that combo, than calculate from relation the
destinationcombo, where FinalCombo is the combo found on step 1) and
sourcecombo is the combo found on step 2).

Let's hope this clears the question a little more

The following query matches your description from UPDATE 1 (with inline comments showing how it matches) and also yields the correct result for your SQL Fiddle (there is no longer an unwanted row for for the 2015-10-21 sale).

-- (1) Check the sales table if it has any combo with duration=0 for the selected user
DECLARE @finalComboId INT = (
    SELECT TOP 1 s.comboid
    FROM sales s
    JOIN combo c
        ON s.comboid = c.id
        AND c.duration = 0
    WHERE s.username = 'user1'
)

IF (@finalComboId IS NOT NULL)
BEGIN
    SELECT s.username, s.saledate, r.destinationcombo as comboid
    FROM (
        -- (2) If there is a result, then take the most recent combo
        -- (with duration <> 0 and with most recent saledate)
        SELECT TOP 1 c.id AS recent_comboid
        FROM combo c
        JOIN sales sc
            ON sc.comboid = c.id
            AND sc.username = 'user1'
        WHERE c.duration <> 0
        ORDER BY sc.saledate DESC
    ) rc
    -- Find all sales rows from that most recent combo
    -- (The SQL Fiddle has just one, but more generally I assume there could be multiple)
    JOIN sales s
        ON s.comboid = rc.recent_comboid
    -- (3) After finding that combo, than calculate from relation the destinationcombo...
        -- NOTE: I intrepret this as finding all sales rows from that most recent combo
    JOIN related r
        ON r.finalCombo = 3 --...where FinalCombo is the combo found on step 1)
        AND r.sourceCombo = s.comboid --...and sourcecombo is the combo found on step 2).
END
ELSE
    SELECT * FROM sales WHERE username='user1'
GO

However, there are a few things that remain confusing and leave me wondering if I am interpreting the problem correctly:

The sourcecombo field is not used in your example query, but the problem statement indicates that it is important when matching to relation.
Why does the ELSE clause select all rows from sales when the the main body is selecting only the most recent combo?
What if there is more than one combo with duration = 0? There is no guidance in the problem statement about which row should be picked.


FreeTDS TSQL connect string I am unable to connect to a MSSQL 2012 Server using FreeTDS on OSX in the terminal when I pass the -P .

Example:

V-MBP:etc vashavoc$ tsql -S SERVER_IP -p PORT -U USER -P PASSWORD
locale is "en_US.UTF-8"
locale charset is "UTF-8"
using default charset "UTF-8"
Msg 18456 (severity 14, state 1) from SERVER_IP Line 1:
"Login failed for user 'USER'."
Error 20002 (severity 9):
Adaptive Server connection failed
There was a problem connecting to the server

If I leave out the -P and force it to prompt for input, I can type/paste it in and I can connect. (No there is no typo when I try using -P.)

V-MBP:etc vashavoc$ tsql -S SERVER_IP -p PORT -U USER
Password:
locale is "en_US.UTF-8"
locale charset is "UTF-8"
using default charset "UTF-8"
1>

Seems very odd it works when I omit the password from the connect string. FreeTDS seems installed OK.

V-MBP:sh vashavoc$ tsql -C
Compile-time settings (established with the "configure" script)
                        Version: freetds v0.95.80
         freetds.conf directory: /usr/local/Cellar/freetds/0.95.80/etc
 MS db-lib source compatibility: no
    Sybase binary compatibility: no
                  Thread safety: yes
                  iconv library: yes
                    TDS version: 7.1
                          iODBC: no
                       unixodbc: no
          SSPI "trusted" logins: no
                       Kerberos: no
                        OpenSSL: yes
                         GnuTLS: no

Tried TDSVER=X.X variants before the tsql string without any change.

tsql -LH SERVER_IP

Also tried to see if it would list back its instances using the -LH, doesn't respond and times out I presume.

Am I missing anything that could get this to work? It is a server setting that is preventing it?

@Phil's suggesting of the special char causing an issue was the problem. Corrected and the connection string works fine.


Upload database records to AWS using TSQL? We have an AWS account that will be installing some software that ultimately needs a subset of our customers' data.  Our customer databases are either MS SQL Server or Sybase Anywhere.  Is there a way we can run TSQL SELECTs from our customer databases into databases on AWS?  If so, would this be resident on AWS pulling from the customer databases?  Or resident on our customer servers pushing to AWS?  If we were pulling, we would need our customers to provide us access to their IP addresses; is that insecure?

perhaps you can share what exactly the software need, so without assuming things , I would describe approaches I use at my current work.

I normally use powershell to extract the data from dbs, I use SMO [link] http://sqlmag.com/powershell/using-sql-server-management-objects-powershell

and then you can upload the output to an s3 bucket using write-s3object powershell command , this can be done from any customer environment which has access to internet, and with using the awspowershell module which you can download [link] https://aws.amazon.com/powershell/ and from a central EC2 in your aws you can read this data from s3-bucket , you can use read-s3object to get the data from the s3 bucket


How much space is one row taking up I need to find out how much disk space a single row is taking up in a table.  I can then use this as an approximate to work out how much disk space I can recover by removing old unneeded data.  Would I have to combine all of the columns in a single SELECT statement, like so?

SELECT DATALENGTH([Id]) + DATALENGTH([Column1]) + DATALENGTH([Column2]) AS 'Size'
  FROM [MyTable]

Or is there a better, more efficient way that I'm not aware of?

EDIT

I have just noticed that if a column has a NULL value, then I get NULL as the result.

How much data do you have?

Personally, I'd do one of

try it on a test system and check "before" and "after"
just remove unused data because by definition, it isn't needed

The questions is complicated when you have

variable length data: how much of the field is used on average?
page density (rows per page) may not increase therefore you save no space
indexes duplicate a column value, so you have to count some ones twice

So, I'd do it empirically rather than by calculation...


Partial backup restore of read-only file group on new database Let's say, I have a database myModel2015 that contains two file groups - Primary A and Secondary Read-Only B. Then, I am creating a partial backup of the secondary file group.

Now, let's create a new myModel database (there might be difference in the database objects in the primary file group, but the tables in the read-only file group will be the same as this in the myModel2015 database).

Is it possible to perform piecemeal restore of the Secondary Read-Only B file group but using the new database?

I think that this one covers it for you (SE Link)

He answers very succinctly, but as he says the short answer is no.   He also gives you some options that can work but none as simple as it would be if you could just restore that filegroup to a different database.


Oracle binaries query im not sure if this would be considered as a question or not. I would delete it if not counted for. But just wanted to know, where can I get Oracle 12.1.0.1 software binaries.

http://www.oracle.com/technetwork/database/enterprise-edition/downloads/database12c-linux-download-2240591.html

gives me 12.1.0.2 but what about 12.1.0.1 software?

Kindly help

The software isn't on edelivery.oracle.com either, as Oracle removed 12.1.0.1 as soon as 12.1.0.2 was released. Almost as if 12.1.0.1 was a stop-gap release.

You'll have to open a support ticket with Oracle, asking for it.

12.1.0.2 was a significant bugfix release, as well as adding lots of new features (JSON storage, rapid home provisioning) - upgrade to it ASAP if you've already got 12c in production.


Make MySQL database accessed by just one user I have MySQL server, and it have a lot of databases and users, and i need to create a user and a database for it, and only this user can access this database, even the root user can't access this database.
how can i do it

You need an 'admin' for maintenance, etc.  You don't have to call it 'root' and you don't have to let everyone know the password.

It is 'best practice' to grant user(s) access to one (maybe more) database(s).  In this context, a "user" could be a human, or the login for an app.  Example:

 GRANT ALL PRIVILEGES ON dbname.* TO username@"1.2.3.4" IDENTIFIED BY "...";

That implicitly does not give that user (from that host) access to any other databases, nor privileges such as CREATE DATABASE or GRANT.

When writing Stored Routines, carefully think about the use of SECURITY INVOKER versus SECURITY DEFINER.  INVOKER means that the routine cannot do more than what the user could manually do.  DEFINER lets the routine be more powerful.  This can be useful for providing some otherwise-restricted operation to mere users.

(Caveat:  Newer versions of MySQL have "Plugins" and "Roles" that are more involved than the old GRANT; these let you have more involved security models.)


Slow Calculated sort in MS SQL Server I have a big query (Entity Framework generated one) and I'm using a Calculated Field. Performance has already improved by adjusting the Clustered Index to include the [On] (datetime) column.

The sort on the calculated function is responsible for 100% of the performance slowness according to the Query Plan.

The function estimated the order of the item and ranks it according to the votes/comments it received and gives newer items a higher ranking. So the score changes every hour + every vote on item/comment (similar to Reddit).

In short, this is what it ranks by

CAST( [Project4].[C1] AS float) / (POWER( CAST( DATEDIFF (hour, [Project4].[On], '2018-05-01 00:00:00.0000000') AS float) + cast(2 as float(53)), cast(1.8 as float(53)))) AS [C1],

The problem is, when I sort by [On], I have immediate results. When I sort by the mentioned calculation, it takes 12-16 seconds.

The literal '2018-05-01 00:00:00.0000000' is dependent on current time.

How would I improve this?

Edit:
I didn't think it was useful to post the query, because it's created by EF and very big (also for Stackoverflow, see this pastebin )

More useful: pastetheplan

You shouldn't do this kind of sorting in SQL Server, you should do it in your application. Here's why:

When you need to order data that doesn't have an index that supports the required sort order, your query will do all the work at runtime with a Sort operator.

SELECT *
FROM dbo.Users AS u
WHERE u.Reputation < 2
ORDER BY u.Reputation;

The Sort operator will ask for memory to sort all of the columns you request by the column(s) you're ordering by (this is greatly simplified, but going deeper isn't really necessary here).

If I add an index that supports the sort order (and in this case, the WHERE clause) I'm asking for, the query plan may change to use it. This isn't guaranteed, since at some point the optimizer may decide that doing a Key Lookup isn't in its best interests.

CREATE INDEX ix_yourmom ON dbo.Users (Reputation);

But in this case, I'm returning a small enough number of rows to make the Key Lookup attractive. It has six pack abs and a cool anime haircut, or something.

Of course, if I add more complexity to the ordering, the plan goes back to the original one that scans the clustered index and uses a Sort operator to give us the ordering.

SELECT *
FROM dbo.Users AS u
WHERE u.Reputation < 2
ORDER BY CAST(u.Reputation AS FLOAT) /
POWER(CAST(DATEDIFF(HOUR, u.CreationDate, '2018-05-01 00:00:00.0000000') AS FLOAT)
+ CAST(2 AS FLOAT(53)), CAST(1.8 AS FLOAT(53)));

The way you'd normally get around this is by adding a computed column:

ALTER TABLE dbo.Users ADD RankingSort_Literal AS
CAST(Reputation AS FLOAT) /
POWER(CAST(DATEDIFF(HOUR, CreationDate, '2018-05-01 00:00:00.0000000') AS FLOAT)
+ CAST(2 AS FLOAT(53)), CAST(1.8 AS FLOAT(53)))

But since you said:

  The literal '2018-05-01 00:00:00.0000000' is dependant on current
  time.

Which makes the DATETIME literal the equivalent of calling GETDATE(), which means that your computed column wouldn't be determinstic. Having a non-deterministic computed column means you can't a) index it b) persist it.

Sometimes that's okay, but not when you're trying to sort data by it. There's no benefit here.

Of course, I'm saying all this with no insight into:

The rest of the query
Current indexes
The query plan
Table definition
SQL Server hardware and settings

It could be that adding memory, or changing your query to use a temp table in a stored procedure would make sense, or simply changing your query to select fewer rows via a TOP would help. I'm not sure if you're able to make any of those changes though.

Which brings me back to my original point: You should do this sorting in the application.

Hope this helps!


Postgres point() index not being used \d aggregate

              Materialized view "public.aggregate"
          Column      |            Type             | Modifiers
    ------------------+-----------------------------+-----------
     id               | integer                     |
     searchable       | text                        |
     name             | character varying(255)      |
     source_type      | character varying(255)      |
     source_id        | integer                     |
     latitude         | double precision            |
     longitude        | double precision            |
     created_at       | timestamp without time zone |
     updated_at       | timestamp without time zone |

Indexes:
    "aggregate_lat_lng_point" gist (point(latitude, longitude))
    "searchable_tsvector" gin (to_tsvector('english'::regconfig, COALESCE(searchable, ''::text)))

Query:

EXPLAIN ANALYZE SELECT name FROM aggregate
WHERE point(53.574753, -2.1) <@> point(latitude, longitude) < 100;

Result:

 Seq Scan on aggregate_mv  (cost=0.00..23.01 rows=172 width=516) (actual time=0.019..0.522 rows=458 loops=1)
   Filter: (('(53.574753,-2.1)'::point <@> point(latitude, longitude)) < 100::double precision)
   Rows Removed by Filter: 320
 Total runtime: 0.579 ms
(4 rows)

As you can see, the index is not being used. The table contains 525 rows, and the above query returns 195 rows. The table is a materialized view, but that shouldn't make any difference, I have other indexes which work fine. Any ideas as to why my index is not being used in the above query?

I switched to use cube based to take advantage of indexing:

CREATE INDEX aggregate_cube ON aggregate USING gist (ll_to_earth(latitude, longitude));

explain analyze select name from aggregate where earth_box(ll_to_earth(53.574753, -2.1), 100 * 1609.344) @> ll_to_earth(latitude, longitude);

 Bitmap Heap Scan on aggregate  (cost=9.06..259.53 rows=69 width=516) (actual time=0.218..0.476 rows=134 loops=1)
   Recheck Cond: ('(3779643.6387679, -143776.582140441, 5127079.5615671),(3789643.63851185, -133776.582396498, 5137079.56131104)'::cube @> (ll_to_earth(latitude, longitude))::cube)
   ->  Bitmap Index Scan on aggregate_cube  (cost=0.00..9.05 rows=69 width=0) (actual time=0.190..0.190 rows=134 loops=1)
         Index Cond: ('(3779643.6387679, -143776.582140441, 5127079.5615671),(3789643.63851185, -133776.582396498, 5137079.56131104)'::cube @> (ll_to_earth(latitude, longitude))::cube)
 Total runtime: 0.516 ms
(5 rows)


how to return all the columns not just the updated columns when update in postgresql? let's say i have this update statement
UPDATE person set age = 20,location = 'us' WHERE id = 1234

based from documentation of postgresql on UPDATE , it can only return updated fields.
but I want to get all fields regardless if it's updated or not. e.g i have name, first name, gender and where not updated in my UPDATE query.
RETURNING *

doesn't work

Your understanding of the documentation saying "you can only return columns you use" is incorrect
You can list all the columns you want to return. You can even list results of functions or use values that are nothing to do with the columns at all, like you would in a SELECT:
UPDATE person set age = age+1,location = 'us' WHERE id = 1234
RETURNING id, age, location, name, "first name", gender, "where", CONCAT("first name", ' ', name) as fullname, 1+2 as three
--                           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
--                "i have name, first name, gender and where not updated"

https://dbfiddle.uk/?rdbms=postgres_13&fiddle=5ad1bc7b45c4e73da351adedf987b0c3
And as HWNN points out, you can have RETURNING * .. check your original query for syntax errors


is-a relation(ERM) to tables i try to design a database with an is-a relation:

Vehicles have some general attributes and the SubVehiclesX have some specialized Attributes.

SubvehicleX is a Vehicle

And a Person has a Vehicle

In the VehicleNames-Table is e.g.:

ID | TableName

1 | SubVehicle1
:

Now I want to find out all Attributes of the Vehicle

  SELECT *
  FROM VEHICLE v
  LEFT JOIN (
             SELECT vn.TableName
             FROM VehicleNames vn
             WHERE vn.ID = (SELECT NameOfVehicle
                        FROM Vehicle v
                        WHERE v.ID = VARIABLE1)
            ) x on x.ID = v.Vehicle
  WHERE v.ID = VARIABLE1

I want it as dynamic as possible, so when i want a new subvehicle i only must insert a row in the VehicleNames and create a new table. I haven't to change my program code.

the part:

            (
             SELECT vn.TableName
             FROM VehicleNames vn
             WHERE vn.ID = (SELECT NameOfVehicle
                        FROM Vehicle v
                        WHERE v.ID = VARIABLE1)
            )

must be handled in the Programm code or is there an function to take the Result as a tablename?? Left Join SubVehicle1 x

Is there any other alternative to this? I find this Version isn't clean...

Denormalization to the 'Vehicle' table is still option, technically, but based on the responses in the comments I would probably sub-class it as well. So, you're at the options I mentioned in my last comment.  There's no magic bullet.  It's string concatenation for dynamic SQL or separate statements. One way or another you're going to have to change your app code.


Automatic Failover or Database Mirroring? We have a software backed with database. We want to keep database at a remote location and have it all the time available. For this task, a 5 year old project experience came into my mind. I remember, IT department over there asked me to deploy database not locally but remotely on another LAN computer. They told me, there will be two computers there but thanks to a special software, I will see only one Windows OS interface and that software will handle all sync, backup, replication .. tasks. They were calling it "virtual machine".

So with the IP they gave me, I just connected to so called virtual machine and I just deployed my SQL database there just like it is in a local machine. In summary, we need such a solution now for ourselves. I made some search on the net and I found out Automatic Failover, Database Mirroring etc. topics. But I am not sure these refer to what I described. At this point I need your help. Maybe there is a better solution after 5 year for such a task. I am not a database administrator.

I did not choose a specific database yet. Our software can connect to several number of major databases. Just seems that SQL will provide a robust solution for that. And In short, what we need is to have a database backend which will be always available to us. So  in the case of failures, it should have a system to automatically switch to working replica.

What you're describing sounds like Microsoft Sql Server HADR commonly called AlwaysOn Availability Groups. It combines traditional Windows clustering with Database Mirroring to allow you a primary replica and up to 4 (Sql Server 2012) secondary replicas.

Once set up correctly it will provide you with a single IP address and port # to use as a connection target for your application. Setting up the mirroring portion is a bit more than just deploying your database to the connection though there are ways to automate that also.

The replicas may be geographically separated as well as residing on different sub-nets and domains.


Create archive solution on seperate server We have a custom application that uses a database trigger that updates a separate "Audit" database to track any changes in data.  I'd like to come up with a way to keep another database on separate server (Archive server) that would keep older archive data. Current thought is Prod Server keeps an archive database with changes made that day/week.  Archive database pushes any changes older than a day/week to the historical archive server, then truncates all the data in the archive database (on production server) to keep inserts fast and limits the amount of data.  I'd keep the data on the archive server for a set period.  Is it possible to setup replication to keep inserting on the archive server, but not delete the data when I truncate the tables on the source database?

If you're planning on using Transactional or Merge Replication, then you won't be allowed to run a TRUNCATE command on any of your published tables in the Publisher Database. Please see the Microsoft BOL Frequently Asked Questions for Replication Administrators - Database Maintenance, specifically the first section called Why can't I run TRUNCATE TABLE on a published table?:

TRUNCATE TABLE is a DDL statement that does not log individual row deletions and does not fire DML triggers. It is not permitted because replication cannot track the changes caused by the operation: transactional replication tracks changes through the transaction log; merge replication tracks changes through DML triggers on published tables.

That being said, you can modify the properties of your published articles such that DELETE statements don't get replicated to the Subscriber Database, as detailed in this article Options to not replicate SQL Server DELETE commands. Or you can follow the second option of that article to modify the underlying stored procedure that would execute the DELETE statement, though I find the first solution cleaner.
Either of these options should help you achieve your goal.


Visual Studio DAC with Schemabound View containing hierarchyid::GetRoot() - Two part name needed? In Visual Studio, when trying to create a DAC which has a schemabound view which contains hierarchyid::GetRoot() this error is generated and the project does not build.  However, the t-sql is ok as if I create the view directly in the database it works ok.

Is there a two part name for hierarchyid to solve this?

In a DAC project is VS:

CREATE VIEW [dbo].[View1]
with schemabinding
    AS SELECT hierarchyid::GetRoot() as t

Gives:

Error   93  SQL70561: Cannot schema bind view '[dbo].[View1]' because name 'hierarchyid' is invalid for schema binding. Names must be in two-part format and an object cannot reference itself.

The answer is to add sys. in front of the hierarchyId:

sys.hierarchyid::GetRoot()


Does T-SQL MOVE actually move the database, or just copy? I'm a bit confused over the T-SQL command MOVE. I have a statement saying:

MOVE
N'Prod_Database_Data'
TO
N'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\Test_Database.mdf'
GO

The same statement is used on the log: Prod_Database_Log. I think the statement just makes a copy of the database and not actually moves the original database. Am I right or have I missed something fundamentally crucial?

There is no MOVE command.

There is the "RESTORE" command with arguments "WITH MOVE"

This deletes the old MDF and LDF files (if database already exists) and recreate the MDF/LDF files (and NDFs etc) where specified


MySQL IN() statements: Avoiding duplicate lists I need to check that two columns both appear in a list of IDs. So right now I have this:

SELECT
    `main_table`.*
FROM
    `activities` AS `main_table`
WHERE
    (actor_id IN ('10027' , '11535'))
        AND (original_actor_id IN ('10027' , '11535'))

The problem is that the list of IDs can get pretty big; into the thousands. I can't find a way without another temp table that lets me check that two columns are in a list without defining the list twice. I'm concerned that it's an excessively large query to send back and forth from my PHP web server.

You can create a temp table, insert your parameters there and then join it:

create temporary table tmp (x int);
insert into tmp(x) values (10027),(11535);
select x.*
from activities as x
join tmp as y
    on y.x = x.actor_id
join tmp as z
    on z.x = x.original_actor_id;


Backup and restore for PostgreSQL + why number of backups increased while we limit the recovery window of 7 days We are using the barman for PostgreSQL backup and restore on rhel 7.2 machines

In the barman configuration file - /etc/barman.conf we set the following

retention_policy_mode = auto
retention_policy = RECOVERY WINDOW OF 7 days

But when we go to the folder that hold all the barman backups we see more then 30 backup folders

Note - from barman.conf we set barman_home = /barman

pwd
/barman/main-db-server/base
drwxr-xr-x 3 barman barman 4096 2020 20200218T204628
drwxr-xr-x 3 barman barman 4096 2020 20200218T204702
drwxr-xr-x 3 barman barman 4096 2020 20200218T204802
drwxr-xr-x 3 barman barman 4096 2020 20200218T204901
drwxr-xr-x 3 barman barman 4096 2020 20200218T205002
drwxr-xr-x 3 barman barman 4096 2020 20200218T205102
drwxr-xr-x 3 barman barman 4096 2020 20200218T205202
drwxr-xr-x 3 barman barman 4096 2020 20200218T205302
drwxr-xr-x 3 barman barman 4096 2020 20200218T205402
drwxr-xr-x 3 barman barman 4096 2020 20200218T205502
drwxr-xr-x 3 barman barman 4096 2020 20200218T205602
drwxr-xr-x 3 barman barman 4096 2020 20200218T205702
drwxr-xr-x 3 barman barman 4096 2020 20200218T205802
drwxr-xr-x 3 barman barman 4096 2020 20200218T205902
drwxr-xr-x 3 barman barman 4096 2020 20200218T210002
drwxr-xr-x 3 barman barman 4096 2020 20200218T210102
drwxr-xr-x 3 barman barman 4096 2020 20200218T210202
drwxr-xr-x 3 barman barman 4096 2020 20200218T210302
drwxr-xr-x 3 barman barman 4096 2020 20200218T210402
drwxr-xr-x 3 barman barman 4096 2020 20200218T210502
drwxr-xr-x 3 barman barman 4096 2020 20200218T210602
drwxr-xr-x 3 barman barman 4096 2020 20200218T210702
drwxr-xr-x 3 barman barman 4096 2020 20200218T210802
drwxr-xr-x 3 barman barman 4096 2020 20200218T210902
drwxr-xr-x 3 barman barman 4096 2020 20200218T211002
drwxr-xr-x 3 barman barman 4096 2020 20200218T211102
drwxr-xr-x 3 barman barman 4096 2020 20200218T211202
drwxr-xr-x 3 barman barman 4096 2020 20200218T211302
drwxr-xr-x 3 barman barman 4096 2020 20200218T211402
drwxr-xr-x 3 barman barman 4096 2020 20200218T211503
drwxr-xr-x 3 barman barman 4096 2020 20200218T211602
drwxr-xr-x 3 barman barman 4096 2020 20200218T211702
drwxr-xr-x 3 barman barman 4096 2020 20200218T211802
drwxr-xr-x 3 barman barman 4096 2020 20200218T211902
drwxr-xr-x 3 barman barman 4096 2020 20200218T212002
drwxr-xr-x 3 barman barman 4096 2020 20200218T212102
drwxr-xr-x 3 barman barman 4096 2020 20200218T212203
drwxr-xr-x 3 barman barman 4096 2020 20200218T212302
drwxr-xr-x 3 barman barman 4096 2020 20200218T212402
drwxr-xr-x 3 barman barman 4096 2020 20200218T212502
drwxr-xr-x 3 barman barman 4096 2020 20200218T212602
drwxr-xr-x 3 barman barman 4096 2020 20200218T212702
drwxr-xr-x 3 barman barman 4096 2020 20200218T212802
drwxr-xr-x 3 barman barman 4096 2020 20200218T212902
drwxr-xr-x 3 barman barman 4096 2020 20200218T213002
drwxr-xr-x 3 barman barman 4096 2020 20200218T213102
drwxr-xr-x 3 barman barman 4096 2020 20200218T213202

In  crontab we defined to create backup each day as

30 23 * * * /usr/bin/barman backup main-db-server
* * * * * /usr/bin/barman cron

But since we limit the    retention_policy = RECOVERY WINDOW OF 7 days

From my understanding this means - limit the backup only to 7 days ,

But in spite this we get much more backups , why?

Note - the reason that we want to limit the number of backup is because we have small disk

meanwhile we delete the old backup manual

as

 barman delete  main-db-server 20191019T233002

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.


How to split numbers and text in MySQL I have searched over the internet looking for a function like REGEXP_REPLACE in Oracle, regexp_replace in PostgresSQL but I haven't find one similar in MySQL just REGEXP and RLIKE, but these operators just check whether the string matches pattern containing a regular expression not what the match is.

I have read about UDF like mysql-udf-regexp, but it doesn't work for me since I want something that I can manipulate like stored functions for faster optimization tasks or whatever conditions I want.

My questions are:

Do I have to make 2 functions if I want to delete all the alpha characters when I want just letters or vice versa?
Does anyone know any better way to approach this?

I have a rather ugly approach that will strip alphanumeric characters from a user variable

STRIPPING ALPHAS

SET @st='r1+o2l-3a4*n5d6o7';
SELECT (@st:=REPLACE(@st,ch,'')) FROM
(
    SELECT (x*10+y) ndx,SUBSTR(chars,x*10+y,1) ch FROM
    (SELECT 'abcdefghijklmnopqrstuvwxyz' chars) L,
    (SELECT 0 x UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4
    UNION SELECT 5 x UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) X,
    (SELECT 0 y UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4
    UNION SELECT 5 x UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) Y
    WHERE SUBSTR(chars,x*10+Y,1)<>'' ORDER BY SUBSTR(chars,x*10+y,1)
) alpha;
SELECT @st;

STRIPPING ALPHAS EXECUTED

mysql> SELECT (@st:=REPLACE(@st,ch,'')) FROM
    -> (
    ->     SELECT (x*10+y) ndx,SUBSTR(chars,x*10+y,1) ch FROM
    ->     (SELECT 'abcdefghijklmnopqrstuvwxyz' chars) L,
    ->     (SELECT 0 x UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4
    ->     UNION SELECT 5 x UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) X
    ->     (SELECT 0 y UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4
    ->     UNION SELECT 5 x UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) Y
    ->     WHERE SUBSTR(chars,x*10+Y,1)<>'' ORDER BY SUBSTR(chars,x*10+y,1)
    -> ) alpha;
+---------------------------+
| (@st:=REPLACE(@st,ch,'')) |
+---------------------------+
| r1+o2l-34*n5d6o7          |
| r1+o2l-34*n5d6o7          |
| r1+o2l-34*n5d6o7          |
| r1+o2l-34*n56o7           |
| r1+o2l-34*n56o7           |
| r1+o2l-34*n56o7           |
| r1+o2l-34*n56o7           |
| r1+o2l-34*n56o7           |
| r1+o2l-34*n56o7           |
| r1+o2l-34*n56o7           |
| r1+o2l-34*n56o7           |
| r1+o2-34*n56o7            |
| r1+o2-34*n56o7            |
| r1+o2-34*56o7             |
| r1+2-34*567               |
| r1+2-34*567               |
| r1+2-34*567               |
| 1+2-34*567                |
| 1+2-34*567                |
| 1+2-34*567                |
| 1+2-34*567                |
| 1+2-34*567                |
| 1+2-34*567                |
| 1+2-34*567                |
| 1+2-34*567                |
| 1+2-34*567                |
+---------------------------+
26 rows in set (0.00 sec)

STRIPPING ALPHAS RESULT

mysql> SELECT @st;
+------------+
| @st        |
+------------+
| 1+2-34*567 |
+------------+
1 row in set (0.00 sec)

mysql>

STRIPPING NUMERICS

SET @st='r1+o2l-3a4*n5d6o7';
SELECT (@st:=REPLACE(@st,ch,'')) FROM
(
    SELECT x+1 ndx,SUBSTR(chars,x+1,1) ch FROM
    (SELECT '0123456789' chars) L,
    (SELECT 0 x UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4
    UNION SELECT 5 x UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) X
) numerics;
SELECT @st;

STRIPPING NUMERICS EXECUTED

mysql> SET @st='r1+o2l-3a4*n5d6o7';
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT (@st:=REPLACE(@st,ch,'')) FROM
    -> (
    ->     SELECT x+1 ndx,SUBSTR(chars,x+1,1) ch FROM
    ->     (SELECT '0123456789' chars) L,
    ->     (SELECT 0 x UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4
    ->     UNION SELECT 5 x UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) X
    -> ) numerics;
+---------------------------+
| (@st:=REPLACE(@st,ch,'')) |
+---------------------------+
| r1+o2l-3a4*n5d6o7         |
| r+o2l-3a4*n5d6o7          |
| r+ol-3a4*n5d6o7           |
| r+ol-a4*n5d6o7            |
| r+ol-a*n5d6o7             |
| r+ol-a*nd6o7              |
| r+ol-a*ndo7               |
| r+ol-a*ndo                |
| r+ol-a*ndo                |
| r+ol-a*ndo                |
+---------------------------+
10 rows in set (0.00 sec)

STRIPPING NUMERICS RESULT

mysql> SELECT @st;
+------------+
| @st        |
+------------+
| r+ol-a*ndo |
+------------+
1 row in set (0.00 sec)

mysql>

STRIPPING ALPHANUMERICS

SET @st='r1+o2l-3a4*n5d6o7';
SELECT (@st:=REPLACE(@st,ch,'')) FROM
(
    SELECT (x*10+y) ndx,SUBSTR(chars,x*10+y,1) ch FROM
    (SELECT 'abcdefghijklmnopqrstuvwxyz0123456789' chars) L,
    (SELECT 0 x UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4
    UNION SELECT 5 x UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) X,
    (SELECT 0 y UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4
    UNION SELECT 5 x UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) Y
    WHERE SUBSTR(chars,x*10+Y,1)<>'' ORDER BY SUBSTR(chars,x*10+y,1)
) alphanumeric;
SELECT @st;

STRIPPING ALPHANUMERICS EXECUTED

mysql> SET @st='r1+o2l-3a4*n5d6o7';
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT (@st:=REPLACE(@st,ch,'')) FROM
    -> (
    ->     SELECT (x*10+y) ndx,SUBSTR(chars,x*10+y,1) ch FROM
    ->     (SELECT 'abcdefghijklmnopqrstuvwxyz0123456789' chars) L,
    ->     (SELECT 0 x UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4
    ->     UNION SELECT 5 x UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) X,
    ->     (SELECT 0 y UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4
    ->     UNION SELECT 5 x UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) Y
    ->     WHERE SUBSTR(chars,x*10+Y,1)<>'' ORDER BY SUBSTR(chars,x*10+y,1)
    -> ) alphanumeric;
+---------------------------+
| (@st:=REPLACE(@st,ch,'')) |
+---------------------------+
| r1+o2l-3a4*n5d6o7         |
| r+o2l-3a4*n5d6o7          |
| r+ol-3a4*n5d6o7           |
| r+ol-a4*n5d6o7            |
| r+ol-a*n5d6o7             |
| r+ol-a*nd6o7              |
| r+ol-a*ndo7               |
| r+ol-a*ndo                |
| r+ol-a*ndo                |
| r+ol-a*ndo                |
| r+ol-*ndo                 |
| r+ol-*ndo                 |
| r+ol-*ndo                 |
| r+ol-*no                  |
| r+ol-*no                  |
| r+ol-*no                  |
| r+ol-*no                  |
| r+ol-*no                  |
| r+ol-*no                  |
| r+ol-*no                  |
| r+ol-*no                  |
| r+o-*no                   |
| r+o-*no                   |
| r+o-*o                    |
| r+-*                      |
| r+-*                      |
| r+-*                      |
| +-*                       |
| +-*                       |
| +-*                       |
| +-*                       |
| +-*                       |
| +-*                       |
| +-*                       |
| +-*                       |
| +-*                       |
+---------------------------+
36 rows in set (0.00 sec)

STRIPPING ALPHANUMERICS RESULT

mysql> SELECT @st;
+------+
| @st  |
+------+
| +-*  |
+------+
1 row in set (0.00 sec)

mysql>

EPILOGUE

This SELECT query is meant to hold up to 100 characters for stripping

You can replace (SELECT 'abcdefghijklmnopqrstuvwxyz0123456789' chars) L, with any list of characters you wish to strip from a user variable.

GIVE IT A TRY !!!


SQL Server 2008R2 is pretty limited in its windowing functions, no lag/lead functionality, and ORDER BY can only be used with ROW_NUMBER().  So the usual techniques to solve this are not readily available.
I'm going to assume your primary key is (ScanCode,Dates), if it isn't this won't return consistent results.
SELECT
  curr.ScanCode
 ,curr.Dates
 ,curr.Flag
 ,prn = ROW_NUMBER() OVER (PARTITION BY curr.ScanCode, pr.Dates ORDER BY curr.Dates)
FROM
  @t curr
LEFT JOIN
  @t pr --Get maximum date prior to current date where Flag did not match
    ON pr.ScanCode = curr.ScanCode
        AND pr.Dates =
          (
            SELECT
              MAX(Dates)
            FROM
              @t
            WHERE
              ScanCode = curr.ScanCode
                AND Flag <> curr.Flag
                AND Dates < curr.Dates
          )

This works because the dates for a contiguous block of Flag will all have the same prior date value:
ScanCode    Dates       Flag    PriorDate
182086      2020-07-01  P       NULL
182086      2020-07-02  P       NULL
182086      2020-07-03  A       2020-07-02
182086      2020-07-04  A       2020-07-02
182086      2020-07-06  P       2020-07-04
182086      2020-07-07  P       2020-07-04
182086      2020-07-08  P       2020-07-04
182086      2020-07-09  P       2020-07-04
182086      2020-07-10  A       2020-07-09
182086      2020-07-11  A       2020-07-09
182086      2020-07-13  A       2020-07-09
182086      2020-07-14  A       2020-07-09

So if your clustered index is chosen properly, this is one table scan plus two seeks per row, and one sort for the PARTITION BY.  Not great, but even with LAG/LEAD you'd probably not create anything more efficient.
Other advice:

Dates isn't a good column name as the column stores a single value, not plural.
Additionally, it's not very descriptive of what the column represents - is this a date of a certain event?  Entry date?  If you can rename it, do.  Also, if the values will always be dates, use the DATE datatype.
The primary key in this instance really should be (ScanCode,Dates) if it isn't already.  Not only is this necessary to guarantee consistent results, but the join to get the prior value will be a seek on the clustered index.  To test, declare a primary key on your table variable and compare the execution plans with and without.


Where in pg_catalog is the database or catalog information stored about relations? When I query a table like information_schema.tables

SELECT
  table_catalog,
  table_schema,
  table_name
FROM information_schema.tables;

I get a result like this,

 table_catalog |    table_schema    |              table_name
---------------+--------------------+---------------------------------------
 myDB          | mySchema           | myTable

I can get table_schema, and table_name from pg_catalog.tables, but how would I get the table_catalog. Where is a the _catalog information stored in the pg_catalog schema?

You can't it's hard coded. The _catalog columns in the table schema literally cast from a call to current_database()

CAST(current_database() AS sql_identifier) AS table_catalog,

You can see them defined in the source code postgres/src/backend/catalog/information_schema.sql

The docs on information_schema_catalog_name make it explicit

  information_schema_catalog_name is a table that always contains one row and one column containing the name of the current database (current catalog, in SQL terminology).

The docs on information_schema.tables also say,

  Name of the database that contains the table (always the current database)


Table size increasing drastically in sql server When I ran:
SP_Spaceused TEST. I got this output which is very strange for 20918 rows.
Output:
name    rows    reserved    data       index_size   unused
TEST    20918   6240816 KB  6235480 KB  2304 KB     3032 KB

I Try to gess The reason.
I copy These new records to another table.
select * into TEST1 from TEST

But The result was more strange : on new table the size of these record was
name    rows    reserved    data    index_size  unused
TEST1   20918   5704 KB    5456 KB     8 KB     240 KB

I tried rebuilding the Index on TEST table, but it was not helpful.
Can someone please explain what could be the reason for this?

I bet it is a heap table. SQL server can be lousy at re-using space for deleted data in a heap. Try having it as a clustered table instead.


mysql match against boolean mode I'm using MATCH AGAINST in boolean mode to add a simple search functionality to my site.

The column to be searched is varchar, with no full text index. The column value can be alphanumeric, all letters or all numbers, as well as certain combinations of both (eg. ABC-123').

There seems to be a few problems. First, the search doesn't work when there's a hyphen (like ABC-123). Second, it will not find partial matches. For a value of 856059, the searches look like this:

MATCH (field) AGAINST ('+856049' IN BOOLEAN MODE) // finds the record
MATCH (field) AGAINST ('+85604' IN BOOLEAN MODE) // does not find the record
MATCH (field) AGAINST ('+8560' IN BOOLEAN MODE) // does not find the record

I'm not sure why this is working this way. For various reasons I can't add a fulltext index to the column and use the "default" match against mode.

You may need to try some wildcard stuff

MATCH (field) AGAINST ('+85604*' IN BOOLEAN MODE)
MATCH (field) AGAINST ('+8560*' IN BOOLEAN MODE)

The MySQL Documentation says on '*':

  *

  The asterisk serves as the truncation (or wildcard) operator. Unlike
  the other operators, it is appended to the word to be affected. Words
  match if they begin with the word preceding the * operator.

  If a word is specified with the truncation operator, it is not
  stripped from a boolean query, even if it is too short or a stopword.
  Whether a word is too short is determined from the
  innodb_ft_min_token_size setting for InnoDB tables, or ft_min_word_len
  for MyISAM tables. The wildcarded word is considered as a prefix that
  must be present at the start of one or more words. If the minimum word
  length is 4, a search for '+word +the*' could return fewer rows than a
  search for '+word +the', because the second query ignores the
  too-short search term the.

Here is an example from the Documentation:

  'apple*'

  Find rows that contain words such as “apple”, “apples”, “applesauce”,
  or “applet”.

For more information, see MySQL Documentation on Fine Tuning Your Searches


script for restore of postgres database localy from dump given on stdin - ambiguous redirect Runing this command:

sshpass  -p "pass" ssh  x@1.2.3.4 "pg_dump -U foo some_db"

runs perfectly fine. It returns on stdandard output the dump of db.

Then I would like to restore new_db based on dump redirected to stdin

 psql -U foo -d new_db < `sshpass  -p "pass" ssh  x@1.2.3.4 "pg_dump -U foo some_db"`

I got error: ambiguous redirect

I suggest this :

sshpass  -p "pass" ssh  x@1.2.3.4 "pg_dump -U foo some_db" | psql -U foo -d new_db

This will pipe ssh standard output into psql standard input.


How can I remove locks on an object? One of my queries is running in to a Row Lock Contention. I've tracked down which table is causing that - so now what's the next step ? Which session is causing this table to get locked ? How do I remove the lock ?

I'm using Oracle 10g

Look in DBA_BLOCKERS and DBA_WAITERS. If you don't have these views, as SYS run @?/rdbms/admin/catblock.

You can remove the lock by killing the session, but you will then need to wait for Oracle, specifically PMON, to roll back that session's outstanding work.


How to get different "modules" from modules table? I have "moduller" table in that table keeping modules of system with function name. Two information in one field. Like this:

Slug field mean is like this: "MODULE_NAME/function_name"

I want to get the list:
 1. admin
 2. ajax
 3. analysisusers
 4. bonus

How to get i'm only different modules names like above?

Find the position of the / character, take everything left of it, then do DISTINCT

SELECT DISTINCT LEFT(slug,LOCATE('/',slug) - 1) module_name
FROM moduller WHERE LOCATE('/',slug) > 0;


Database snapshot update I m a new in SQL. I have one question where if we update, i mean add or delete data at source database, is that the data will be update in database snapshot also. Please enlighten me.

No, that's what snapshots are for: a static copy of the database at a specific instant. If you update the base database, the updated data pages are moved from the base database's data files to the the snapshots data files, with a "copy on write" operation, so that the snapshot always contains the same version of the data as when it was created.


Shared entities across multiple databases I'm currently working on an application with a master database and individual user databases where all user input data resides. These individual databases are exactly the same. master connects the dots and links the users to their database.

Today, we're looking into sharing a specific user entity in the way that other users should be able to access the other.

Say we have the table comments. Each comment have a primary key that may or may not exist in other user databases. What I'm thinking is to have a relation tracking table in master that basically says "this user have access to this user's comments".

The conceptual problem I'm facing is the union part, where multiple users have access to one users comments. Given that database names are pattern based user_id.comments. I just don't see how it would be possible to union all comments across multiple databases.

Scenario;

master.comments_relation
user_1   user_2
user_1   user_3
user_2   user_1

user_1.comments
1   "comment from user 1"
2   "another comment from user 1"

user_2.comments
1   "comment from user 2"
2   "another comment from user 2"

user_3.comments
1   "comment from user 3"
2   "another comment from user 3"

What I want to be able to output now as "user_1"
1   "comment from user 1"
1   "comment from user 2"
2   "another comment from user 1"
2   "another comment from user 2"

This is a totally new thing for me and I have no idea on how this is supposed to work, and the primary keys are conflicting. Is it better to move all comments to master database, and skip the multi-database approach, having shared entities in the same database?

One instance of MySQL can have one or more databases.  Each database can have one or more tables.  It sounds like the info belongs "together", so one database makes sense.  It is almost always a "bad" idea to have "one database (or table) per user/product/company/item/etc".

This forum is littered with discussions of why "one per" is bad.


MySQL: How to determine a value type? I am looking for some function that will return the type of given value/result like TYPE(value). Similar as for charset/collation for which there are functions CHARSET(value)//COLLATION(VALUE).

I know how to query a column type. But I am looking really for a function applicable to any value/result:

select CONCAT('hello', 5)
select IF(1, 'hello', 5) as v1, IF(0, 'hello', 5) as v2
(select 'hello' AS v) union all (select '5')
select 4.0 AS v

Does MySQL have any function to display the type?

A partial answer:

$ mysql -u root --column-type-info -v
Welcome to the MySQL monitor.  Commands end with ; or \g.
...

Note the extra parameters.  Now I will issue just one simple command; see what it comes back with:

mysql> SELECT * FROM things;
--------------
SELECT * FROM things
--------------

ERROR 1046 (3D000): No database selected
mysql> use try;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> SELECT * FROM things;
--------------
SELECT * FROM things
--------------

Field   1:  `thing_id`
Catalog:    `def`
Database:   `try`
Table:      `things`
Org_table:  `things`
Type:       LONG
Collation:  binary (63)
Length:     11
Max_length: 1
Decimals:   0
Flags:      NOT_NULL PRI_KEY AUTO_INCREMENT NUM PART_KEY

Field   2:  `name`
Catalog:    `def`
Database:   `try`
Table:      `things`
Org_table:  `things`
Type:       VAR_STRING
Collation:  utf8_general_ci (33)
Length:     96
Max_length: 7
Decimals:   0
Flags:      NOT_NULL

+----------+---------+
| thing_id | name    |
+----------+---------+
|        2 | thing 2 |
|        3 | thing 3 |
|        4 | thing 1 |
+----------+---------+
3 rows in set (0.00 sec)

mysql>

I don't think there is a way to get the 'type' of an expression.


Optimization: Increase Innodb_pool_size useful? We run an AWS RDS instance with 8vCPUs and 64GB memory.
All tables are using InnoDB engine.

I have kept the default setup of innodb_pool_size to be half the memory.

What I see is that almost 30Gb of memory is used and no read I/O operation (except for some spikes).

Freeable memory:

Read IO:

I've seen some great blog post, answers (like this one How large should be mysql innodb_buffer_pool_size?) about tuning MySQL.

Would it be beneficial to increase the innodb_pool_size to something like 3/4 of the memory? As I don't see read IO.

Also, but I don't know if that is related: what can explain this huge storage drop in a couple of minutes? We don't store large objects, BLOBs, etc.

Free Storage:

It's good you don't see a lot of reads, ideally it should be zero if available memory permits.

When InnoDB needs a page it checks the buffer pool. If the page is in the buffer pool - done. If not - InnoDB will read it from the disk and cache it in the buffer pool. So, if the data set fits into the buffer pool there will be eventually no reads from disk.

If you already get no reads further buffer pool increase won't increase performance.

The storage drop - I don't know RDS is a black box, who know what's going on there.


SQL Server AlwaysOn: Lost of heartbeat and connection with secondary replica Virtual Enviroment description:

Hypervisor: VMWare

WSFC composed by two nodes with Windows server 2016 Standard

SQL Server AlwaysOn with synchronous replica and automatic failover

SQL Server 2014 (SP3-CU2) 12.0.6214.1

Following the cluster thresholds:
CrossSubnetDelay          : 4000
CrossSubnetThreshold      : 40
PlumbAllCrossSubnetRoutes : 0
SameSubnetDelay           : 2000
SameSubnetThreshold       : 20

AG properties:
LeaseTimeout: 20000
FailureConditionLevel: 3
HealthCheckTimeout: 30000
VerboseLogging: 0

Issue:
RHS.exe process on primary node lost heartbeat with availability group and it initiated the failover. Immediately after the lost of heartbeat, primary node loses the connection with the secondary node and automatic failover fail. Shortly after, hearbeat with primary node works again and primary node takes over the resources again.
Log Details
In chronological order:

Primary node randomly shows signs of disconnection with the secondary replica already in the days leading up to failover:
...AlwaysOn Availability Groups connection with secondary database terminated for primary database 'nameofDB'..
The primary node storage randomly shows signs of distress already in the days leading up to failover and just before failover:
...Long Sync IO: ... IOs in nonpreemptive mode longer than 1000ms
FlushCache cleaned up 146070 bufs with 20273 writes in 80387ms...
SQL Server has encountered 4 occourence(s) of I/O request taking longer than 15 seconds to complete...
RHS.exe lost heartbeat with AG and WSFC send failover request to AG:
[hadrag] Failure detected, diagnostics heartbeat is lost
The local replica of availability group ... is preparing to transition to the resolving role..
Shortly after the failover request, the primary replica loses connection with the secondary:
...AlwaysOn Availability Groups connection with secondary database terminated for primary database 'nameofDB'..
The listener stops working and there is a checkpoint failure on a specific database:
One or more recovery units belonging to database .. failed to generate a checkpoint
Eleven seconds after the attempted failover, heartbeat works again and primary replica
takes over the resources

Questions
Million dollar questions:

why heartbeat was lost?
why connection with secondary replica was lost ?
Any workaround ? increase lease timeout, performance of storage or RAM and CPU can help?

I understand that it is difficult to answer but it can help to only have a strategy or hypotheses that can help me find the problem.
EDIT
I found an interesting example of extended event able to trace the duration of all synchronization steps on tigerteam github. The problem is that it can degrade performance and I haven't found a doc reference for analysing the values shows by extended events. Any idea or suggest for troubleshooting?

why heartbeat was lost?
why connection with secondary replica was lost ?

Depending on which instance of DHS.exe it could be Network Problems or a failure in the local SQL Server instance.

Any workaround ? increase lease timeout, performance of storage or RAM and CPU can help?

Double-check your cluster quorum so that the primary node does not need to communicate with any far-off resources to maintain cluster quorum.
You also have a storage problem that needs to be addressed, but if you use iSCSI or other network-based storage, that might have the same root cause.