CONCAT with multiple INSERT INTO ... SELECT ... WHERE I have a number (currently 6) of tables within multiple schemas, and I need to insert a number of (2000) rows into a table in each schema.

The idea is that I have the procedure below, and simply change the variable at the start (schema_name and user_id), and run for each table, rather than having to run multiple inserts, and change the schema name each time.

Eg, I'd change @schema_name = 'foo1' to @schema_name = 'foo2', and @user_id = '1', to user_id = '2', then re-run the script.

SET @schema_name = 'foo1';
SET @user_id = '1';

SET @proc = CONCAT('
INSERT INTO ' ,@schema_name, '.tbl_name (tbl_id, shortlist_id, year, created_at, updated_at) SELECT "2", "1", lookup_year, now(), now() FROM "lookup_tbl" WHERE lookup_id LIKE "' ,@user_id, '";
INSERT INTO ' ,@schema_name, '.tbl_name (tbl_id, shortlist_id, year, created_at, updated_at) SELECT "2", "2", lookup_year, now(), now() FROM "lookup_tbl" WHERE lookup_id LIKE "' ,@user_id, '";
INSERT INTO ' ,@schema_name, '.tbl_name (tbl_id, shortlist_id, year, created_at, updated_at) SELECT "2", "7", lookup_year, now(), now() FROM "lookup_tbl" WHERE lookup_id LIKE "' ,@user_id, '";
INSERT INTO ' ,@schema_name, '.tbl_name (tbl_id, shortlist_id, year, created_at, updated_at) SELECT "2", "9", lookup_year, now(), now() FROM "lookup_tbl" WHERE lookup_id LIKE "' ,@user_id, '";
INSERT INTO ' ,@schema_name, '.tbl_name (tbl_id, shortlist_id, year, created_at, updated_at) SELECT "2", "8", lookup_year, now(), now() FROM "lookup_tbl" WHERE lookup_id LIKE "' ,@user_id, '";
');

PREPARE stmt FROM @proc;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

The problem that I'm having is that I can't appear to do multiple statements within a concat.

I had this trouble before, and used INSERT INTO (...) VALUES (A, B), (C, D)...,
but I don't think this is a option with the INSERT INTO (...) SELECT ... WHERE ...

Is there an alternative that I'm unaware of?

I've used 5 rows in the example, but I've actually got 2000 rows to do it with.

This is doable. What you need is 5 SELECTs with one INSERT

SET @schema_name = 'foo1';
SET @lookup_table = 'mytable';
SET @user_id = '1';

SET @proc = CONCAT('INSERT INTO ' ,@schema_name, '.tbl_name ');
SET @proc = CONCAT(@proc,'(tbl_id, shortlist_id, year, created_at, updated_at) ');
SET @proc = CONCAT(@proc,'SELECT       "2", "1", lookup_year, now(), now() FROM ',@lookup_table,' WHERE lookup_id LIKE "' ,@user_id, '" ');
SET @proc = CONCAT(@proc,'UNION SELECT "2", "2", lookup_year, now(), now() FROM ',@lookup_table,' WHERE lookup_id LIKE "' ,@user_id, '" ');
SET @proc = CONCAT(@proc,'UNION SELECT "2", "7", lookup_year, now(), now() FROM ',@lookup_table,' WHERE lookup_id LIKE "' ,@user_id, '" ');
SET @proc = CONCAT(@proc,'UNION SELECT "2", "9", lookup_year, now(), now() FROM ',@lookup_table,' WHERE lookup_id LIKE "' ,@user_id, '" ');
SET @proc = CONCAT(@proc,'UNION SELECT "2", "8", lookup_year, now(), now() FROM ',@lookup_table,' WHERE lookup_id LIKE "' ,@user_id, '"');

PREPARE stmt FROM @proc;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;


When to use lateral joins vs correlated subqueries in Postgres? These two queries:

SELECT username, location, task, ts_next - ts AS diff
FROM (
        SELECT username, location, task, ts, (
                SELECT ts AS ts_next
                FROM switches
                WHERE username = a.username
                AND ts > a.ts
                ORDER BY ts ASC LIMIT 1
        )
        FROM switches a
        WHERE task IS NOT NULL
) c
ORDER BY username, ts
;

and

SELECT username, location, task, ts_next - ts AS diff
FROM (
        SELECT username, location, task, ts
        FROM switches
        WHERE task IS NOT NULL
) a
LEFT JOIN LATERAL (
        SELECT ts AS ts_next
        FROM switches
        WHERE username = a.username
        AND ts > a.ts
        ORDER BY ts ASC LIMIT 1
) b
ON true
ORDER BY username, ts
;

give the same results.

When and why should I use one form over the other?

(The switches table is from a time/task tracking service, where only task switches are recorded, but task durations need to be calculated.)

The query plans are different, is one better than the other?

                                          QUERY PLAN
-----------------------------------------------------------------------------------------------
 Sort  (cost=27.96..28.01 rows=19 width=37)
   Sort Key: a.username, a.ts
   ->  Seq Scan on switches a  (cost=0.00..27.55 rows=19 width=37)
         Filter: (task IS NOT NULL)
         SubPlan 1
           ->  Limit  (cost=1.38..1.38 rows=1 width=8)
                 ->  Sort  (cost=1.38..1.39 rows=4 width=8)
                       Sort Key: switches.ts
                       ->  Seq Scan on switches  (cost=0.00..1.36 rows=4 width=8)
                             Filter: ((ts > a.ts) AND ((username)::text = (a.username)::text))
(10 rows)

                                                QUERY PLAN
-----------------------------------------------------------------------------------------------------------
 Sort  (cost=28.34..28.39 rows=19 width=45)
   Sort Key: switches.username, switches.ts
   ->  Nested Loop Left Join  (cost=1.38..27.93 rows=19 width=45)
         ->  Seq Scan on switches  (cost=0.00..1.24 rows=19 width=37)
               Filter: (task IS NOT NULL)
         ->  Limit  (cost=1.38..1.38 rows=1 width=8)
               ->  Sort  (cost=1.38..1.39 rows=4 width=8)
                     Sort Key: switches_1.ts
                     ->  Seq Scan on switches switches_1  (cost=0.00..1.36 rows=4 width=8)
                           Filter: ((ts > switches.ts) AND ((username)::text = (switches.username)::text))
(10 rows)

Using the subquery version would become awkward if you wanted to return more than one column or more than one row from the innermost subquery. If you never want to do that, then I find the first form easier to read.  (Or at least it would be if you removed the outermost subquery and reformatted a bit--can't the expression and the ORDER BY be done directly in the middle query "c"?)

The difference in plan between the two is unlikely to have any practical significance.


One MySQL table for all data vs Several Different Engines MySQL Tables I did quite good research and went through some information on this regard.
But I am not satisfied yet, so preferred to ask directly about it.

I'm designing the schema of an application database, which is assumed to have tins of millions of records.

Some of these are frequently updated, and some are very rarely updated.
Hence, I decided to spread data on different tables according to the nature, and JOIN them in queries.

So, is this better or.. going with the one table (no JOINs)?

Assume it is a Rent-A-Car management system..

Here are the tables:

DESC cars
+-----------------+---------------------+------+-----+---------+-------+
| Field           | Type                | Null | Key | Default | Extra |
+-----------------+---------------------+------+-----+---------+-------+
| id              | bigint(20) unsigned | NO   | PRI | NULL    | ai    |
| brand           | varchar(40)         | NO   |     | NULL    |       |
| engine_capacity | tinyint(4)          | NO   |     | NULL    |       |
| model           | tinyint(4)          | NO   |     | NULL    |       |
| serialnumber    | bigint(20)          | NO   | UNI | NULL    |       |
+-----------------+---------------------+------+-----+---------+-------+

AND

DESC cars_data;
+---------------+----------------------+------+-----+---------+-------+
| Field         | Type                 | Null | Key | Default | Extra |
+---------------+----------------------+------+-----+---------+-------+
| carid         | bigint(20) unsigned  | NO   | PRI | NULL    | ai    |
| car_status    | enum('1','2','3')    | NO   | MUL | 1       |       |
| base_location | point                | NO   | MUL | NULL    |       |
| driver_id     | bigint(20) unsigned  | NO   | MUL | NULL    |       |
+---------------+----------------------+------+-----+---------+-------+

Both those tables, are assumed to be very rarely updated, thus I used for them MyISAM for next reasons:

Need to store Point data and use Spatial Index on them.
Saving disk space (since MyISAM tables are smaller in size).
Easier to maintain (as per my experience, is that right?).

Here is the very frequently updated table:

DESC cars_extra;
+---------------+---------------------+------+-----+---------+-------+
| Field         | Type                | Null | Key | Default | Extra |
+---------------+---------------------+------+-----+---------+-------+
| carid         | bigint(20) unsigned | NO   | PRI | NULL    | ai    |
| odometer_km   | tinyint(6) unsigned | NO   |     | NULL    |       |
| last_maint    | bigint(20) unsigned | NO   |     | NULL    |       |
| last_update   | bigint(20) unsigned | NO   |     | NULL    |       |
+---------------+---------------------+------+-----+---------+-------+

I used for last table InnoDB for one single main reason:

Avoid table-level locks during updates.

Advises, and general notes would be more than appreciated :-)

Also, are joins usually a con or pros (surly with the use of proper indexed keys).

Simply, use InnoDB for everything.  MyISAM is going away in the next release.  In almost all cases, InnoDB out-performs MyISAM.  About the only advantage with MyISAM is disk space.

With millions of rows, you will appreciate InnoDB's automatic recovery after any crash.

It is rarely wise to make two tables be 1:1.  Your suggestion for that is based on one table being constant; the other being frequently modified.  But I would suggest that "frequent" is 'many times a second', not 'a few times a day'.

Don't design the schema without sketching out the SELECTs.  You have POINT and SPATIAL, but what will you do with such?

Glancing at the tables...

Use SHOW CREATE TABLE; it is more descriptive than DESCRIBE.

Don't use BIGINT (8 bytes) unless INT (4 bytes) won't suffice.  You would need to hire nearly all the adults in the world to exceed what INT UNSIGNED can handle.

JOIN can be pro or con.  But it is rarely a deciding factor.

Use utf8mb4 from the start.  (DESCRIBE omits that bit if info.)

Use AUTO_INCREMENT only if there is not a 'reasonable' column (or combination of columns) that make up a 'natural' PRIMARY KEY.

TINYINT UNSIGNED is limited to 255, not enough for odometer.  MEDIUMINT UNSIGNED goes up to 16M.

Do not PARTITION without a good reason for it.


How to let customers use their own reporting tools on a SaaS application? We have a SaaS application that generates a lot of operational data that our customers want to report on. We have a built-in business intelligence tool, but some of our customers want to use their existing tools, like Tableau or Cognos.

Some of them have asked us to let them connect to their SQL database directly over a VPN tunnel. That works, but it will be difficult to manage if we have more than a few.

Is there any standard way to let outside reporting tools access data without opening a direct network connection to the database? By "standard" I mean something that we can implement that will be usable by a lot of common BI and reporting tools.

Do not permit ad hoc querying against the operational database, no matter if it is internally generated or external. The potential to lock data or saturate resources is too large. Client B is not going to forgive the SLA breach caused by client A submitting a malformed query then going for lunch.

Separating each customer's data from the others' is important, too. Even if the data isn't "sensitive" it is not professional and can lead to awkward conversations when A realises their outcomes are so much worse than B's. The question doesn't make clear if the source is multi-tenant.

One solution is to replicate / mirror / copy data from the operational DB to a second, separate, isolated DB. Use whatever tool your product supports. This may be an opportunity to do some transformation if required and supported by the tool. The lag between primary and secondary is to be negotiated with the customer in the light of license, set-up and support costs. Grant read-only access on this secondary to the client. Ideally the primary and secondary would be on separate boxes with a firewall between since the latter will be externally accessible. Be kind to yourself on the SLA you agree for the secondary.

If the client can accept a reporting lag on the order of hours you could simply ftp the data to them on a schedule.

Is there a better option than VPNs? I'm not a networking expert, but I'd say that unless you have a specific problem with using a VPN, go with it.


SQL Query GROUP BY JOIN I have the following tables:

CREATE TABLE `Channels` (
   `id` INT(11) AUTO_INCREMENT PRIMARY KEY,
   `title` VARCHAR(255) NOT NULL,
   `views` INT(11) NOT NULL DEFAULT 0,
) Engine = InnoDB;

INSERT INTO Channels (title, views)
VALUES ('Movies', 50030),
       ('TV shows', 51201),
       ('Howtos', 18601);

CREATE TABLE `Videos` (
   `id` BIGINT AUTO_INCREMENT PRIMARY KEY,
   `channel_id` INT(11),
   `name` VARCHAR(255) NOT NULL,
   `description` TEXT,
   `views` INT(11) NOT NULL DEFAULT 0,
   `created_at` DATETIME NOT NULL,
   `updated_at` DATETIME,
   CONSTRAINT videos_channel_id_fk
       FOREIGN KEY ( channel_id )
           REFERENCES Channels ( id )
) ENGINE=InnoDB;

INSERT INTO Videos (`channel_id`, `name`, `created_at`)
VALUES (1, 'A', '2019-10-03 00:00:12'), (1, 'B', '2019-03-01 00:12:12'),
       (1, 'C', '2019-10-01 19:12:12'), (1, 'D', '2019-10-03 01:55:55'),
       (2, 'E', '2019-10-03 01:58:00'), (2, 'F', '2019-10-01 15:11:11'),
       (2, 'G', '2019-09-09 11:11:11'), (2, 'H', '2019-08-01 12:53:10');

I'm trying to get a distinct list of channels with their most recent videos.

For example for Channel 1, it would be Video 'D' at '2019-10-03 01:55:55', for Channel 2 it would be Video 'E' at '2019-10-03 01:58:00');

I've tried doing a self join like this,

SELECT v1.id, v1.channel_id, v1.name FROM Videos AS v1
LEFT JOIN Videos AS v2 ON v1.channel_id = v2.channel_id AND v1.created_at>v2.created_at
WHERE v1.created_at IS NULL

but my Video id's never match up with the latest video. i.e. I'll end up with video id: 1 where I want Video id: 4.

WITH cte AS (SELECT *,
                    ROW_NUMBER() OVER (PARTITION BY channel_id
                                       ORDER BY created_at DESC) rn
             FROM Videos)
SELECT *
FROM Channels c
LEFT JOIN cte ON c.id = cte.channel_id AND cte.rn = 1

fiddle


Need help to design model with multiple rights For a website, I have multiple elements that need to be moderated by many different users.
Until now, I have always used Role-Based Access Control (RBAC) model but I want a more flexible model. I would like to be able to assign some user with some specific rights on particular ressources and after be able to remove/add rights as I want.

For example an element would be a blog with articles. There is the author of the articles who has full rights on his article but I would like to be able to add another user for that article with update right on it. And that for all elements in my website.

Here is what I have:

Elements are 'page', 'article', 'anotherElement'. 'Ownership' is the table where each user has what he can do. 'Right' is the table with specific right (read, write, ...). 'Category' is not important, only to subclass my elements.

Is here a better way to do this? The problem will be the size of table 'ownership' after a certain time.

Since my question I have now a great concept.
Security, rights and access are handled by an ownership table.
Each row contains :

booleans access for create, delete, read & update
Link to table Element wich contains all manageable elements in my website
Link to table OwnershipType wich tells me if it's an access on ownership, for all ressources or only a specific ressource ID
Link to table User
An ID in case OwnershipType is only a specific ressource

To manage groups, I didn't want to create more tables, what I found clever was to consider a group as a user and assign it some ownerships. To add a user to a group, I have a Parent table wich link a user to another (in this case that user is in fact a 'group'). It explains why I have a UserType table. It tells me if it's a group or a normal user.
In my code I just have to check if a user has a parent and if so add its ownership to the user's ones.
That concept let me having some global ownership on many user who are linked to a group and still have really specific ownership on certain user.
If people are interested I can explain how I implemented that concept in my website, it's quite simple and clean.
My model :


Mysql: slave read binlog from local storage I am running master-slave replication, Can I manually move binlog files from the master server to the slave and make the slave read them locally?

I am willing to do so because I am running out of space on the master storage. and the number of binlog files is huge.

You don't need all binary logs from the master. Just the ones that your slave hasn't read yet.

On your slave you can do show slave status\G. Look for the lines Master_Log_File and      Relay_Master_Log_File. The first is the binary log file, that your master is using, the second is the log file that your slave is currently reading.

You can then delete all binary logs on the master, that the slave has already read.
For example, Relay_Master_Log_File is mysql-bin.011096, then you could execute the following statement on the master:

purge binary logs to 'mysql-bin.011096';

This is described in more detail here.

You can also configure your master to hold less binary logs by configuring the expire_logs_days variable. Read more about it here.

I assume this already solves your storage problems.

UPDATE based on comment: You can simply copy the binary logs to the slave. Remember the position you already have from the show slave status output. Stop the slave. Then execute the binary logs on the slave with

# mysqlbinlog binlog-file1 binlog-file2 --start-position=x | mysql

Note that the --start-position parameter is only applied to the first binlog file you specify as parameter. Make sure to specify the right one.

Then read the last position from the last binary log with

# mysqlbinlog binlog-file99 | tail

and change the coordinates with

mysql> change master to master_log_file='...', ...

to the appropriate position and start the slave again.

Read more about this here (I guess)


Bizarre density results in sampled statistics A NC-index gets a totally different statistical distribution when estimated with sampling vs fullscan; the sampled one having a bizarre density vector. This results in poor execution plans.

I have a table of ~27M rows, with a non null FK-column supported by a nonclustered index. The table is clustered on its primary key. Both columns are varchar.

A fullscan statistics update for our FK-column gives a normal looking density vector:

All density Average Length  Columns
6,181983E-08    45,99747    INSTANCEELEMENTID
3,615442E-08    95,26874    INSTANCEELEMENTID, ID

That is, we're expected to read about 1.7 rows for each distinct INSTANCELEMENTID we are joining with.

A typical bin from the histogram looks like this:

RANGE_HI_KEY    RANGE_ROWS  EQ_ROWS DISTINCT_RANGE_ROWS AVG_RANGE_ROWS
FOOBAR          133053      10      71366               1,679318

However, if we do a sampled update (using the default sample number which is 230k rows for this table) things take a turn to the bizarre:

4,773657E-06    45,99596    INSTANCEELEMENTID
3,702179E-08    95,30183    INSTANCEELEMENTID, ID

The density on INSTANCEELEMENTID is now two orders of magnitude larger. (The density for both columns however has been estimated to a quite acceptable value).

A typical bin from the histogram now looks like this;

RANGE_HI_KEY    RANGE_ROWS  EQ_ROWS     DISTINCT_RANGE_ROWS AVG_RANGE_ROWS
FOOBAR          143870,4    766,2573    1247                115,3596
ZOTZOT          131560,7    1           969                 135,7092

which is a completely different distribution. Note that the INSTANCEELEMENTID with the highest number of associated IDs has 12, the most common number is 1. It's also very strange that some bins get EQ_ROWS = 1, this happens to about 10% of the bins.

There is no "unlucky" draw of strange rows that could contribute to this.

Am I reading the histogram correctly? Doesn't it look like the sampling has somehow scaled EQ_ROWS, DISTINCT_RANGE_ROWS and AVG_RANGE_ROWS incorrectly?

The table is, as far as I can tell, unskewed. I've tried to emulate the sampler by estimating values myself with tablesample. Counting these results in a normal way gives results that agree with the fullscan version, not the sampler.

Furthermore, I've not been able to reproduce this behaviour on clustered indices.

I've narrowed this down to this to reproduce:

CREATE TABLE F_VAL (
    id varchar(100) primary key,
    num_l_val int not null
)

set nocount on

declare @rowlimit integer = 20000000;

The table must be sufficiently large for this to be observed. I've seen this with uniqueidentifer and varchar(100) but not int.

declare @i integer = 1;

declare @r float = rand()

while @i < @rowlimit
begin
set @r = rand()
insert f_val (id,num_l_val)
values (
   cast(@i as varchar(100)) + REPLICATE('f', 40 - len(@i)),
   case when @r > 0.8 then 4 when @r > 0.5 then 3 when @r > 0.4 then 2 else 1 end
)
  set @i = @i + 1

end

create table k_val (
 id int identity primary key,
 f_val varchar(100) not null,
)

insert into k_val(f_val)
select id from F_VAL
union all select id from f_val where num_l_val - 1 = 1
union all select id from f_val where num_l_val - 2 = 1
union all select id from f_val where num_l_val - 3 = 1
order by id

create nonclustered index IX_K_VAL_F_VAL  ON K_VAL (F_VAL)

update statistics K_VAL(IX_K_VAL_F_VAL)
dbcc show_statistics (k_val,IX_k_VAL_F_VAL)

update statistics K_VAL(IX_K_VAL_F_VAL) WITH FULLSCAN
dbcc show_statistics (k_val,IX_k_VAL_F_VAL)

Compare the two statistics; the one with sampling is now representing a total different density vector and the histogram bins are off. Note that the table is not skewed.

Using int as datatype does not cause this, does SQL Server not examine the entire datapoint when using varchar?

It's worthwhile to mention that the problem seems to scale, increasing sample rate helps.

I've seen this same density problem on some of the nonclustered indexes on the largest databases that I have access to. First I'll start with a few observations that I've made about histograms and density calculations:

SQL Server is able to use the primary key on the table to infer something about the density of both columns. This means that the density that includes the PK columns will usually be very accurate.
The density calculation for the first column in the statistics is consistent with the histogram. If the histogram does not model the data well then the density may be off.
To create the histogram the StatMan function makes inferences about the data that is missing. The behavior can change depending on the data type of the column.

For one way to look at the problem, suppose that you sample 100 rows from a 10000 row table and you get 100 distinct values. One guess at what the rest of the data in the table is that there are 10000 unique values. Another guess is that there are 100 distinct values but each of the values are repeated 100 times. The second guess may seem unreasonable to you, which I will agree with. However, how do you balance the two approaches when the sampled data comes back unevenly distributed? There is some set of algorithms developed for this by Microsoft contained in the StatMan function. The algorithms may not work for all data disruptions and all sample levels.

Let's go through a relatively simple example. I'm going to use VARCHAR columns like in your table to see some of the same behavior. However, I'll just add one skewed value to the table. I'm testing against SQL Server 2016 SP1. Start with 100k rows with 100k unique values for the FK column:

DROP TABLE IF EXISTS X_STATS_SMALL;

CREATE TABLE X_STATS_SMALL (
ID VARCHAR(10) NOT NULL,
FK VARCHAR(10) NOT NULL,
PADDING VARCHAR(900) NOT NULL,
PRIMARY KEY (ID)
);
-- insert 100k rows
INSERT INTO X_STATS_SMALL WITH (TABLOCK)
SELECT N, N, REPLICATE('Z', 900)
FROM dbo.GetNums(100000);

CREATE INDEX IX_X_STATS_SMALL ON X_STATS_SMALL (FK);

-- get sampled stats
UPDATE STATISTICS X_STATS_SMALL IX_X_STATS_SMALL;

Here are some samples from the statistics:

╔═════════════╦════════════════╦═════════╗
║ All density ║ Average Length ║ Columns ║
╠═════════════╬════════════════╬═════════╣
║ 1.00001E-05 ║ 4.888205       ║ FK      ║
║ 1.00001E-05 ║ 9.77641        ║ FK, ID  ║
╚═════════════╩════════════════╩═════════╝

╔══════════════╦════════════╦═════════╦═════════════════════╦════════════════╗
║ RANGE_HI_KEY ║ RANGE_ROWS ║ EQ_ROWS ║ DISTINCT_RANGE_ROWS ║ AVG_RANGE_ROWS ║
╠══════════════╬════════════╬═════════╬═════════════════════╬════════════════╣
║ 1005         ║ 0          ║ 1       ║ 0                   ║ 1              ║
║ 10648        ║ 665.0898   ║ 1       ║ 664                 ║ 1.002173       ║
║ 10968        ║ 431.6008   ║ 1       ║ 432                 ║ 1              ║
║ 11182        ║ 290.0924   ║ 1       ║ 290                 ║ 1              ║
║ 1207         ║ 445.7517   ║ 1       ║ 446                 ║ 1              ║
║ ...          ║ ...        ║ ...     ║ ...                 ║ ...            ║
║ 99989        ║ 318.3941   ║ 1       ║ 318                 ║ 1              ║
╚══════════════╩════════════╩═════════╩═════════════════════╩════════════════╝

For evenly distributed data with one unique value per row we get an accurate density, even with a VARCHAR histogram column and a sample size of 14294 rows.

Now let's add a skewed value and update statistics again:

-- add 70k rows with a FK value of '35000'
INSERT INTO X_STATS_SMALL WITH (TABLOCK)
SELECT N + 100000 , '35000',  REPLICATE('Z', 900)
FROM dbo.GetNums(70000);

UPDATE STATISTICS X_STATS_SMALL IX_X_STATS_SMALL;

With a sample size of 17010 rows the density of the first column is smaller than it should be:

╔══════════════╦════════════════╦═════════╗
║ All density  ║ Average Length ║ Columns ║
╠══════════════╬════════════════╬═════════╣
║ 6.811061E-05 ║ 4.935802       ║ FK      ║
║ 5.882353E-06 ║ 10.28007       ║ FK, ID  ║
╚══════════════╩════════════════╩═════════╝

╔══════════════╦════════════╦══════════╦═════════════════════╦════════════════╗
║ RANGE_HI_KEY ║ RANGE_ROWS ║ EQ_ROWS  ║ DISTINCT_RANGE_ROWS ║ AVG_RANGE_ROWS ║
╠══════════════╬════════════╬══════════╬═════════════════════╬════════════════╣
║ 10039        ║ 0          ║ 1        ║ 0                   ║ 1              ║
║ 10978        ║ 956.9945   ║ 1        ║ 138                 ║ 6.954391       ║
║ 11472        ║ 621.0283   ║ 1        ║ 89                  ║ 6.941863       ║
║ 1179         ║ 315.6046   ║ 1        ║ 46                  ║ 6.907561       ║
║ 11909        ║ 91.62713   ║ 1        ║ 14                  ║ 6.74198        ║
║ ...          ║ ...        ║ ...      ║ ...                 ║ ...            ║
║ 35000        ║ 376.6893   ║ 69195.05 ║ 54                  ║ 6.918834       ║
║ ...          ║ ...        ║ ...      ║ ...                 ║ ...            ║
║ 99966        ║ 325.7854   ║ 1        ║ 47                  ║ 6.909731       ║
╚══════════════╩════════════╩══════════╩═════════════════════╩════════════════╝

It's surprising that the AVG_RANGE_ROWS is pretty uniform for all steps at around 6.9, even for buckets of keys for which the sample couldn't have found duplicate values. I don't know why this is. The most likely explanation is that the algorithm used to guess at the missing pages does not do well with this data distribution and sample size.

As stated earlier, it is possible to calculate the density for the FK column using the histogram. The sum of the DISTINCT_RANGE_ROWS values for all steps is 14497. There are 179 histogram steps so the density should be about 1 / (179 + 14497) = 0.00006813845 which is pretty close to the reported value.

Testing with a larger table can show how the problem can get worse as the table gets bigger. This time we'll start with 1 M rows:

DROP TABLE IF EXISTS X_STATS_LARGE;

CREATE TABLE X_STATS_LARGE (
ID VARCHAR(10) NOT NULL,
FK VARCHAR(10) NOT NULL,
PADDING VARCHAR(900) NOT NULL,
PRIMARY KEY (ID));

INSERT INTO X_STATS_LARGE WITH (TABLOCK)
SELECT N, N, REPLICATE('Z', 900)
FROM dbo.Getnums(1000000);

CREATE INDEX IX_X_STATS_LARGE ON X_STATS_LARGE (FK);

-- get sampled stats
UPDATE STATISTICS X_STATS_LARGE IX_X_STATS_LARGE;

The statistics object isn't interesting yet. Density for FK is 1.025289E-06 which is close to exact (1.0E-06).

Now let's add a skewed value and update statistics again:

INSERT INTO X_STATS_LARGE WITH (TABLOCK)
SELECT N + 1000000 , '350000',  REPLICATE('Z', 900)
FROM dbo.Getnums(700000);

UPDATE STATISTICS X_STATS_LARGE IX_X_STATS_LARGE;

With a sample size of 45627 rows the density of the first column is worse than before:

╔══════════════╦════════════════╦═════════╗
║ All density  ║ Average Length ║ Columns ║
╠══════════════╬════════════════╬═════════╣
║ 2.60051E-05  ║ 5.93563        ║ FK      ║
║ 5.932542E-07 ║ 12.28485       ║ FK, ID  ║
╚══════════════╩════════════════╩═════════╝

╔══════════════╦════════════╦═════════╦═════════════════════╦════════════════╗
║ RANGE_HI_KEY ║ RANGE_ROWS ║ EQ_ROWS ║ DISTINCT_RANGE_ROWS ║ AVG_RANGE_ROWS ║
╠══════════════╬════════════╬═════════╬═════════════════════╬════════════════╣
║ 100023       ║ 0          ║ 1       ║ 0                   ║ 1              ║
║ 107142       ║ 8008.354   ║ 1       ║ 306                 ║ 26.17787       ║
║ 110529       ║ 4361.357   ║ 1       ║ 168                 ║ 26.02392       ║
║ 114558       ║ 3722.193   ║ 1       ║ 143                 ║ 26.01217       ║
║ 116696       ║ 2556.658   ║ 1       ║ 98                  ║ 25.97568       ║
║ ...          ║ ...        ║ ...     ║ ...                 ║ ...            ║
║ 350000       ║ 5000.522   ║ 700435  ║ 192                 ║ 26.03268       ║
║ ...          ║ ...        ║ ...     ║ ...                 ║ ...            ║
║ 999956       ║ 2406.266   ║ 1       ║ 93                  ║ 25.96841       ║
╚══════════════╩════════════╩═════════╩═════════════════════╩════════════════╝

AVG_RANGE_ROWS is up to 26. Interestingly, if I change the sample size to 170100 rows (10X the other table) then the average value for AVG_RANGE_ROWS is again right around 6.9. As your table gets bigger SQL Server will pick a smaller sample size which means that it needs to make guesses about a larger percentage of pages in the table. This can exaggerate statistics problems for certain kinds of data skew.

In conclusion, it's important to remember that SQL Server does not calculate the density like this:

SELECT COUNT(DISTINCT FK) * 1700000. / COUNT(*) -- 1071198.9 distinct values for one run
FROM X_STATS_LARGE TABLESAMPLE (45627 ROWS);

Which for some data distributions will be very accurate. Instead it uses undocumented algorithms. In your question you said that your data wasn't skewed, but the INSTANCEELEMENTID value with the highest number of associated IDs has 12 and the most common number is 1. For the purposes of the algorithms used by Statman that could be skewed.

At that point there's nothing that you can do about it except for gathering stats with a higher sample rate. One common strategy is to gather statistics with FULLSCAN and NORECOMPUTE. You can refresh the stats with a job on whatever interval makes sense for your rate of data change. In my experience a FULLSCAN update isn't as bad as most people think, especially against an index. SQL Server can just scan the entire index instead of the entire table (as it would do for a rowstore table against an unindexed column). Additionally, in SQL Serer 2014 only FULLSCAN stats updates are done in parallel, so a FULLSCAN update can finish faster than some sampled updates.


Should I store data pre-ordered rather than ordering on the fly? I'm using MySQL and I'm wondering if it's a good strategy to presort my data so when a user accessed the information, it's not having to sort it on the fly?

Basically, I have an HTML table with is being populated with paginated data from the database, this is ordered by a particular column and can sometimes be a little sluggish - I was thinking about reordering the table on a nightly basis so the order by can be removed from the query.

Is this general practice or should I avoid this?

Update

My query is as follows:

'select keyword, position, impressions, clicks, ctr
 from keywords where profile_id=%s
 order by impressions desc limit %s, %s', (profile_id, start, end))

My table looks like this:

+---------------------+---------------+------+-----+---------+----------------+
| Field               | Type          | Null | Key | Default | Extra          |
+---------------------+---------------+------+-----+---------+----------------+
| id                  | bigint(20)    | NO   | PRI | NULL    | auto_increment |
| profile_id          | int(11)       | YES  | MUL | NULL    |                |
| landing_page_id     | int(11)       | YES  | MUL | NULL    |                |
| keyword             | varchar(2083) | YES  |     | NULL    |                |
| position            | int(11)       | YES  | MUL | NULL    |                |
| impressions         | int(11)       | YES  | MUL | NULL    |                |
| ctr                 | float         | YES  | MUL | NULL    |                |
| clicks              | int(11)       | YES  | MUL | NULL    |                |
| unique_key          | varchar(200)  | YES  | UNI | NULL    |                |
| position_30_days    | int(11)       | YES  |     | NULL    |                |
| impressions_30_days | int(11)       | YES  |     | NULL    |                |
| clicks_30_days      | int(11)       | YES  |     | NULL    |                |
| ctr_30_days         | float         | YES  |     | NULL    |                |
| position_60_days    | int(11)       | YES  |     | NULL    |                |
| impressions_60_days | int(11)       | YES  |     | NULL    |                |
| clicks_60_days      | int(11)       | YES  |     | NULL    |                |
| ctr_60_days         | float         | YES  |     | NULL    |                |
| position_90_days    | int(11)       | YES  |     | NULL    |                |
| impressions_90_days | int(11)       | YES  |     | NULL    |                |
| clicks_90_days      | int(11)       | YES  |     | NULL    |                |
| ctr_90_days         | float         | YES  |     | NULL    |                |
+---------------------+---------------+------+-----+---------+----------------+

(DESCRIBE is not as descriptive as SHOW CREATE TABLE; we can't see what indexes you have.)

That one SELECT would benefit from this 'composite' index:

INDEX(profile_id, impressions) -- in that order.

Do you have a keyword that is 2083 characters long?  If not, why have such a big VARCHAR?

Why have both unique_key and id?  Is unique_key some form of UUID?  They are notoriously inefficient when the table gets huge.

LIMIT ?, ? ... ($start, $end) -- The two numbers in LIMIT are start and count, not end.

By using the index, above, and changing to "remember where you left off", you can make the ORDER BY...LIMIT work a lot faster.  Details .  This suggestion, if practical for your application, will be faster (at least after the first 'page') than your original question about ordering the data could ever be!  Why?  Because OFFSET (the first number in LIMIT) requires work.  My blog show how to get rid of that work.

More

When you could have multiple rows with the same value, and you need to be deterministic in ordering:

ORDER BY profile_id DESC, impression DESC, id DESC)
INDEX   (profile_id,      impression,      id)

Notes:

In the ORDER BY, all the items are in the same direction (DESC is usually what is wanted).
Mixing ASC and DESC prevents use of the index (until 8.0).
Since you are looking for a single profile_id, ASC and DESC on it have identical effect.

To deal with a 'compound' $leftoff, let's look at the above example.  After assuming that profile_id is constant, we want to somehow remember where you left off as a pair of $impression, $id, then do

WHERE   impression <= $impression
  AND ( impression <  $impression OR id < $id )

alternatively (and it is unclear whether these optimize differently in different versions of mysql):

WHERE ( impression = $impression AND id < $id
     OR impression < $impression )


MySQL configuration to connect to multiple local applications I've installed FreeRADIUS, MySQL and FreeRADIUS management (a web-based app) on the same machine, with both FreeRADIUS and FreeRADIUS management using same user to connect MySQL.
My problem is when I make a report from FreeRADIUS managment, it makes MySQL busy and FreeRADIUS can not respond to the request (out of service) until my report query has finished.
My question is: How both applications (FreeRADIUS & FreeRADIUS management) run queries on MySQL at same time.

I'm using MyISAM and below is the result of SHOW PROCESSLIST;

These are the queries run during SHOW PROCESSLIST;

SELECT count( DISTINCT (username) ) , `nasipaddress` FROM `radacct`
WHERE (
   acctstarttime < 'First period'
   AND acctstoptime > 'First period'
   AND acctstoptime <'Second period'
   )
OR (
    acctstarttime > 'First period'
    AND acctstoptime < 'Second period'
   )
OR (
  acctstarttime < 'First period'
  AND acctstoptime > 'Second period'
 )
OR (
  acctstarttime > 'First period'
  AND acctstarttime < 'Second period'
  AND acctstoptime > 'Second period'
 )
OR (
    acctstarttime < 'First period'
    AND acctstoptime IS NULL
    )
OR (
  acctstarttime > 'First period'
  AND acctstarttime <'Second period'
  AND acctstoptime IS NULL
 )
GROUP BY `nasipaddress`

-----------------------------------------
SELECT SUM(AcctSessionTime), SUM(AcctOutputOctets), SUM(AcctInputOctets)
FROM radacct
WHERE username='xxx'
-----------------------------------------

UPDATE radacct
      SET
     nasipaddress = '192.168.1.2',
         framedipaddress = '10.10.10.2',
         acctsessiontime     = '200',
         acctinputoctets     = '8263'
         acctoutputoctets    = '310'
      WHERE acctsessionid = 'FD324A12'
      AND username        = 'yyy'
      AND nasipaddress    = '192.168.1.2'"

Not really an answer, but too big for a comment. This is quite a specialised question and would probably (IMHO) be better off asked on the FreeRadius mailing lists.

I looked here and found this

  FreeRADIUS is a high-performance and highly configurable RADIUS
  server. It supports many database back-ends such as flat-text files,
  SQL, LDAP, Perl, Python, etc.

The problem with many systems which support multiple back-ends is that they end up not being optimised for any of them - it's possible that something in their code conflicts with MySQL's way of doing things. And I'm not even sure what a "Perl, Python, etc." back-end is - drivers?

From here, it appears to me that the default database is PostgreSQL and it's possible that scenarios which work with PostgreSQL either don't work or work poorly with MySQL. So, my second suggestion is to try that RDBMS (which, in any case, is superior to MySQL IMHO).

[EDIT] in response to the OP's queries.

Just out of curiosity - what are the response times of those queries when run separately? Second, the issue appears to be a lock on the radacct table. This is a problem with MyISAM tables which your system uses.

Could you take a dump of your schema using mysqldump, change ENGINE=MyISAM to ENGINE=InnoDB,

sed -i 's/ENGINE=MyISAM/ENGINE=InnoDB/g' your_schema_dump.sql

load the edited dump file and rerun the system to the point where you are having the problem? There is very little to be said these days for running a system using MyISAM tables.


How to compare current record with previous records Is there a way to group by and compare current record to previous records when reading from a table.  My table looks something like below.

ID    PASSED    LEVEL    CREATEDATE
100   1         2        2016-01-10 07:35:02.123
100   1         2        2016-01-10 07:05:33.135
100   0         2        2016-01-09 08:35:29.123
100   1         2        2016-01-08 09:35:30.123
100   1         1        2016-01-07 11:35:31.123
101   1         1        2016-01-10 08:45:12.123
102   1         1        2016-01-10 09:45:22.222
102   1         1        2016-01-09 08:15:29.123
103   0         1        2016-01-10 07:35:02.123
103   1         2        2016-01-09 06:35:29.023

Ideally, I need to return a BIT value for each ID in the table.

The condition of the BIT value is to return a 1 for each ID when PASSED is 1 and LEVELS are different by CREATEDATE DESC order.

So in my example table the BIT value for

ID 100 would return 1 because PASSED is 1 on the first and fourth record (the level changes on the fourth record).
ID 101 would return a BIT value of 0 because there is only 1 PASSED record with a value of 1.
ID 102 would return a BIT value of 0 because the LEVELs are the same for both records even though PASSED was 1 on both records.
ID 103 would return 0 because both records did not PASS even though LEVELs are different.

Hoping a query could be structured in such a way that the response would be like this:

ID    STATUS_PASSED
100   1
101   0
102   0
103   0

Thanks for any help provided.

This seemed like a relational division problem to me:

DECLARE @results TABLE ( ID INT NOT NULL, PASSED BIT NOT NULL, LEVEL INT NOT NULL, CREATEDATE DATE NOT NULL, PRIMARY KEY ( ID, LEVEL, PASSED, CREATEDATE ) )
DECLARE @levels TABLE ( LEVEL INT PRIMARY KEY )

INSERT INTO @levels
VALUES ( 1 ), ( 2 ) --, ( 3 )
INSERT INTO @results
VALUES
    ( 100, 1, 2, '1 Oct 2016' ),
    ( 100, 0, 2, '1 Sep 2016' ),
    ( 100, 1, 2, '1 Aug 2016' ),
    ( 100, 1, 1, '1 Jul 2016' ),
    ( 101, 1, 1, '1 Oct 2016' ),
    ( 102, 1, 1, '1 Oct 2016' ),
    ( 102, 1, 1, '1 Sep 2016' ),
    ( 103, 0, 1, '1 Oct 2016' ),
    ( 103, 1, 2, '1 Sep 2016' )
    ,
    ( 200, 0, 1, '1 Sep 2016' ),        -- No pass at all
    ( 201, 1, 1, '1 Sep 2016' ),        -- 1 pass at level 1 only
    ( 202, 1, 1, '1 Sep 2016' ),        -- 1 pass at each level
    ( 202, 1, 2, '1 Sep 2016' ),        -- 1 pass at each level
    ( 203, 1, 2, '1 Sep 2016' ),        -- 1 pass at level 2 only
    ( 204, 0, 1, '1 Sep 2016' ),        -- No pass at either level
    ( 204, 0, 2, '1 Sep 2016' )         -- No pass at either level

    SELECT ID,
        CASE
            WHEN SUM( CAST( PASSED AS INT ) ) >= COUNT( DISTINCT LEVEL )
              AND COUNT( DISTINCT LEVEL ) = ( SELECT COUNT( DISTINCT LEVEL ) FROM @levels)
            THEN 1
            ELSE 0
        END overallPass
    FROM @results
    GROUP BY ID

I have not included the CREATEDATE column in the calculation as it doesn't seem to add a lot.  Also, does it really make sense for someone to pass a course at the same level twice (as per ID 100 in your sample data)?  Does the order in which they pass the courses really matter?  If so, then this could cause a bug in my code where Sum of PASSED >= COUNT ( DISTINCT LEVEL ).  Pre-aggregating the data would solve this, eg

SELECT ID,
    CASE
        WHEN SUM( CAST( PASSED AS INT ) ) >= COUNT( DISTINCT LEVEL )
          AND COUNT( DISTINCT LEVEL ) = ( SELECT COUNT( DISTINCT LEVEL ) FROM @levels)
        THEN 1
        ELSE 0
    END overallPass
FROM ( SELECT ID, LEVEL, MAX( CAST( PASSED AS INT ) ) PASSED FROM @results GROUP BY ID, LEVEL ) x
GROUP BY ID

If the date really is important, please post back and we'll need a slightly more complicated query, basically the PASSED status associated with the max date per ID.

HTH


Query last time a view, function or sp was last used/read I use sys.dm_db_index_usage_stats.last_user_seek, sys.dm_db_index_usage_stats.last_user_scan and sys.dm_db_index_usage_stats.last_user_lookup to query last time a table was read.

For views, functions and stored procedures I'm using sys.objects but it doesn't have any field for read datetime.

Is there any system table where I can see the last time these types of objects were read/used?

My objective is to find objects that aren't being used and possibly drop them.

Unless you have a server side trace running or XEvent running, there is no guaranteed way of telling a particular object is used or not. Some objects might be referenced by a report that is ran once a year by your CIO or business user. So unless you have a concrete data for your business life cycle, there is always a room for error.

I would suggest that once you identify possible candidates for removal, do not drop them, instead rename them and after a year or so, drop them.

When using DMVs you need to log them to a persisted table as DMV data get flushed out due to instance restart, certain sp_configure changes, etc.

I have answered a similar question here that will get you started.


Limiting read access in postgres to few rows We have a requirement where we want to limit the maximum no. of rows that a select query can return when its done using a particular login. So basically we would like to append a limit clause to any select query fired by a particular user.

Is this possible in PostgreSQL? If so, how can this be done?

You can define a function that returns required limit for some users. The function below set limit for users joe and ben.

Note that limit null is equivalent to limit all, so all other users will have no limits.

create or replace function set_limit()
returns integer language sql as $$
    select case current_user
        when 'joe' then 100
        when 'ben' then 200
        else null
    end
$$;

select * from some_table limit set_limit();

Edit:

  So basically we would like to append a limit clause to any select query fired by a particular user.

Once function set_limit() is created you can append limit set_limit() to any select query.


Is Ola Hallengrens Stored Procedure what we need and safe to use I stumbled across a stored procedure and wanted to clarify a few things first as I’ve been told it needs “rigorous review before being executed on any server”, and “testing before we run it on a production server”.

We need to know and understand what the SP does at each stage to ensure no security risks are being introduced and that there is no potential for a data breach or destabilisation of the environment.

Below is our current SP which runs on a Sunday night but within the day the gains are lost and fragmentation is up to 99% on a large number of tables

Additional NONE of our tables have a fill factor at all and all set to zero (equivalent to 100), wondered if this matters

Forgive the email I literally am very new to the Developer world with no prior experience of any DBA related issues.

Our current rebuild / reorganise code :

/****** Object:  StoredProcedure [dbo].[IndexMaintenance_sp]    Script Date: 25/05/2018 13:05:28 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[IndexMaintenance_sp] @log_only BIT = 1
AS
BEGIN
    DECLARE @tableName VARCHAR(800)
        ,@indexName VARCHAR(800)
        ,@fragmentation FLOAT
        ,@indexId INT = 0
        ,@prevTempIndexId INT = 0
        ,@pageCount INT = 0
        ,@tableId INT
        ,@tempIndexId INT = 0;

    CREATE TABLE #tempIndexes (
        id INT IDENTITY
        ,objectId INT
        ,name VARCHAR(max)
        ,indexId INT
        );

    INSERT INTO #tempIndexes (
        objectId
        ,name
        ,indexId
        )
    SELECT i.object_id
        ,i.name AS Name
        ,i.index_id
    FROM [sys].indexes AS i
    WHERE i.name IS NOT NULL
        AND i.object_id IS NOT NULL;

    WHILE 1 = 1
    BEGIN
        SELECT @tempIndexId = id
            ,@indexId = indexId
            ,@tableId = objectId
        FROM #tempIndexes
        WHERE id = (
                SELECT min(id)
                FROM #tempIndexes
                WHERE id > @prevTempIndexId
                );

        IF @tempIndexId = @prevTempIndexId
            BREAK;

        SET @prevTempIndexId = @tempIndexId;
        SET @indexName = NULL;

        SELECT TOP 1 @tableId = s.object_id
            ,@tableName = OBJECT_NAME(s.object_id)
            ,@indexName = i.name
            ,/*s.index_id,*/ @pageCount = s.page_count /*, s.index_type_desc*/
            ,@fragmentation = s.avg_fragmentation_in_percent /*, s.fragment_count */
        FROM [sys].[dm_db_index_physical_stats](DB_ID(), @tableId, @indexId, NULL, NULL) AS s
        INNER JOIN [sys].indexes AS i ON i.object_id = s.object_id
            AND i.index_id = s.index_id
        WHERE avg_fragmentation_in_percent > 5.0
            AND s.object_id IS NOT NULL
            AND i.name IS NOT NULL;

        IF @indexName IS NULL
            CONTINUE;

        INSERT INTO [ServerHealth].[dbo].[__tblIndexFragmentation] (
            checkDate
            ,databaseId
            ,databaseName
            ,objectId
            ,objectName
            ,indexid
            ,indexName
            ,indexPages
            ,indexFragmentation
            )
        VALUES (
            GetDate()
            ,DB_ID()
            ,DB_NAME(DB_ID())
            ,@tableId
            ,@tableName
            ,@indexId
            ,@indexName
            ,@pageCount
            ,@fragmentation
            );

        IF @pageCount < 1000
            CONTINUE;

        DECLARE @sql NVARCHAR(2000);

        SET @sql = 'ALTER INDEX [' + @indexName + '] ON [dbo].[' + @tableName;

        IF @fragmentation < 30
        BEGIN
            SET @sql = @sql + '] REORGANIZE;';
        END
        ELSE
        BEGIN
            SET @sql = @sql + '] REBUILD;';
        END

        IF @log_only = 0
        BEGIN
            EXEC (@sql);
        END
    END
END
GO

To answer your question, yes Ola Hallengrens scripts are highly respected and safe to use. As much as any 3rd party tools can be. How you choose to use (or mis-use) them are up to you. Make sure you have the current version.

The best advice i could give you if you unsure is to test them yourself somewhere safe and form your own opinion.

Take the time to read the guide and understand the default settings ( They are a good starting point) and then test them yourself.

Although Olas scripts are awesome. They're not a silver bullet. If you have a table hitting 99% fragmentation in one day you have something else going on. Are you shrinking your db daily?


Foreign key without referenced columns specification I noticed in the SQL Server documentation that the list of referenced columns is not a required parameter of a foreign key constraint:

<column_constraint> ::=
    [ CONSTRAINT constraint_name ]
    {     { PRIMARY KEY | UNIQUE }
            (...)

      | [ FOREIGN KEY ]
            REFERENCES [ schema_name . ] referenced_table_name [ ( ref_column ) ]
            [ ON DELETE { NO ACTION | CASCADE | SET NULL | SET DEFAULT } ]
            [ ON UPDATE { NO ACTION | CASCADE | SET NULL | SET DEFAULT } ]
            [ NOT FOR REPLICATION ]

      | CHECK (...)
    }

If I omit the ( ref_column ) part, it seems to reference the primary key of the referenced_table_name. That is most convenient. However, I cannot find any specification of this behavior so I am cautious to use it.

Does anybody know whether it is specified anywhere?

The behaviour is not explicitly mentioned in any of the official SQL Server documentation I am familiar with, but the 1992 Draft SQL Standard (section 11.8.2.b) does say:

  If the <referenced table and columns> does not specify a <reference column list>, then the table descriptor of the referenced table shall include a unique constraint that specifies PRIMARY KEY. Let referenced columns be the column or columns identified by the unique columns in that unique constraint and let referenced column be one such column. The <referenced table and columns> shall be considered to implicitly specify a <reference column list> that is identical to that <unique column list>.

Translated, this means an implicit foreign key does reference the primary key of the referenced table. As others have mentioned in comments to the question, it is probably best to be explicit about the relationship though.


Are Views optimized when I add a WHERE clause to them? Does it make a difference if you filter a View inside or outside the View?

For example, is there any difference between these two queries?

SELECT Id
FROM MyTable
WHERE SomeColumn = 1

Or

SELECT Id
FROM MyView
WHERE SomeColumn = 1

And MyView is defined as

SELECT Id, SomeColumn
FROM MyTable

And is the answer any different if the source table is located on a Linked Server?

I'm asking because I have to query a large table (44mil rows) twice from a linked server, and get an aggregate of the results. I want to know if I should create two views to access the data, one for each query, or if I can get away with a single view and a WHERE clause.

You should see absolutely no difference in the plans or the performance between these two choices. When the view is queried, it is expanded out to a query against the base table, which means the same seek or scan will be used.

Now, depending on the data type and selectivity of MyColumn, if you wanted to create a filtered index on the base table (when you move to SQL Server 2008+), you might get better performance, but this again will not be different via the view or without.


Need a solution to store cheaply a big MongoDB collection We have the typical problem of Twitter's timeline tweets, or Facebook profile's news:
we need to show the users only the new information (storing them in a fast db), and put all the old ones somewhere else (but able to load them on request in a reasonable time).

The reason for moving the old ones is both economic (we don't need and don't want tens of GB accessed rarely occupying space on a fast production db) and for performance (tens of GB of extra information gives much overhead for indexes and queries).

Now we have all the information, recent and old, on a MongoDB hosted on MongoHQ. Currently everything is on its own specific database, that contains only one big collection.
I would like to have only the most recent ones in the fast production db, and to take the older ones, such as the data older than let's say one month, and moving them in another database, or to another storage. I would set up a nightly job that will do the stuff so to maintain only recent data on the MongoDB.
I don't want to manage the database by myself now, so I was looking for an hosted solution.

I think we have two options for putting old data:

storing the data on another db hosted in the cloud, cheaper than the Mongo hosting we currently use as primary db, such as Amazon's DynamoDB, or Google Big Query. Going for this would be to transform our MongoDB collection to be saved in another format, and then to transform it back when we need to read it again
setting up "our" server, buying for example a package for MongoDB hosted on EC2. Going for this means adding simply another db to our infrastructure, but it is a quite expensive solution as those services provide high performances database, and we don't need that.

I think that (2) is the easy solution to implement, but (1) would be much cheaper giving that the data will be rarely accessed and the new db would be used much as a storage facility.
In the next months, we will definitely go for (2), as if data and usage increase I think that the best option would be to manage most of the things by ourselves instead of relying to hosting on PaaS or SaaS, but now this solution seems quite expensive.

Do you have any suggestion for a situation like this? Is there a third way that I haven't considered?

Short answer - this is a tricky proposition and your available options really depend on how much effort you wish to put in versus the cost of your option 2 (which I will talk about below).  Having never done it, I can't speak to the ease (or lack thereof) of using another cloud based solution for storing the data elsewhere, but I can comment on some of the options on the MongoDB side.

First up, in terms of putting in the effort, a slight twist on your 2 proposals:

Create a less expensive instance in the cloud
Implement aging/removal on the live database (either manually, or with TTL or capped collections)
Use a tailable cursor to pull all operations out of your oplog and insert into your new instance(s).  Alternatively you could look at the Mongo Connector for this functionality to avoid writing it yourself, but that will not work out of the box with TTL (TTL deletes are replicated from the primary and would need to be filtered out manually)

Caveats:

Even though it might be less expensive, it still has to be able to keep up with your operations - you do not want to have it lag behind and end up missing data
This is essentially doing a form of replication yourself, it's not officially supported anywhere - you have to support this yourself
You will need to come up with your own method to detect if this is broken so you can stop deletes, recover from failure, and your own method to restore data etc.
Manual deletes, or TTL based deletes will eventually lead to fragmentation and inefficient re-use of space, so you will probably need to regularly compact and/or repair to reclaim space

The above is usually too much effort/hassle and too much of an unknown from a supportability perspective unless you plan to run this way long term and are very comfortable supporting such a customized solution.

Your option 2, is often far easier and there are ways to save on costs.  You can, for example, have a hidden secondary that is different from the rest to reduce costs.  Imagine this kind of set up:

Primary, Secondary - both equivalent spec, enough memory to hold working set for live site, generally expensive - uses capped collections to manage data storage
Hidden Secondary - less expensive spec (RAM, slower disk), never takes live traffic, keeps all data, perhaps no indexes also - good source for snapshot backups too usually

To get the hidden secondary set up correctly will take some work.  You need to configure it to not build indexes, you need to pre-create any Capped collections without the capped configuration so they do not delete data (they will then continue to grow).


Formatting T-SQL in SSMS 2012 According to this Microsoft document:

http://msdn.microsoft.com/en-us/library/ms174205.aspx

I am supposed to be able to use ctrl+K then ctrl+D to format my SQL documents in SQL Server Management Studio 2012 but when I use that combo I get the error:

  The key combination (Ctrl+K, Ctrl+D) is bound to command (Format
  Document) which is not currently available.

I am trying to make modifications to an existing SQL document that has no formatting to it at all which makes it extremely difficult to read. Does anyone know how to make the Format Document command available so I can have SQL format this code for me?

The keyboard combination you're after (Ctrl+K, Ctrl+D) is for "formatting" - but not to the extent you seem to expect. It is not a prettifier, it is just used to insert correct spacing and tabs as found in Tools > Options >  Text Editor > Transact-SQL > General/Tabs - so if you highlight a portion of text and hit the keyboard combination, it is supposed to convert tabs to 4 spaces (if you've selected insert spaces), apply the type of indenting you've specified, etc.
This option is NOT intended to make code more readable - this is not functionality that Management Studio currently offers natively. Though there are several 3rd party options - some are external to Management Studio, like:

Simple-Talk's SQL Prettifier
Poor SQL (which Thomas suggested)

And there are add-ins as well for various levels of formatting assistance within the editor:

Red-Gate's SQL Prompt
SSMS Tools Pack
ApexSQL's Refactor
SSMSBoost
dbForge SQL Complete (which Robert suggested)

Now, the reason you are getting the message...

The key combination (Ctrl+K, Ctrl+D) is bound to command (Format Document) which is not currently available.

...is because SSMS has mapped that key combination to a different context. The way you should be able to "fix" this - again, it still wouldn't do what you want it to do, even if the "fix" worked - is by doing the following:

Go to Tools > Options > Environment > Keyboard

Place your cursor into the Press shortcut keys: box

Hit Ctrl+K, Ctrl+D

Change the Shortcut currently used by: drop-down from DataWarehouse Designer to Text Editor

Press OK

Now, this is supposed to map the keyboard combination to the text editor, but Management Studio reverts it after hitting OK (you will continue to get the error message. So, I think the problem is that the documentation believes this functionality exists, but Management Studio knows better and simply doesn't offer it (and Microsoft will likely write this off as a bug in the documentation, and correct it, rather than a shortcoming in the tool). There may be hope in the future but, for now, this is a known and largely ignored issue. You'll note that the Formatting tab that the documentation refers to is simply not present (though it is there for XML, where the keyboard combination does work). The documentation should probably state:

Applies the indenting and space formatting for the language specified on the Formatting pane of the language in the Text Editor section of the Options dialog. Available only in the text editor and only for certain languages.

Another way to get an idea of what type of formatting options SSMS offers natively is to go to Tools > Customize > Commands > Edit > Add Command... > Format and look at the list of possible commands there. Nothing that indicates there is any knowledge of the actual language, so it wouldn't know where to insert line breaks or add additional indents or assist with parentheses etc.

If you want language-specific formatting to make existing T-SQL code more readable, you won't get much from SSMS, and you'll need to seek other options.


Dumping and importing with backslash Background

I have a MySQL database, from which I run mysqldump for backups.

In this database, I have some fields that will contain some backslashes, and when dumping this, it will contain something like

INSERT INTO table1 (col1, col2) VALUES (1, '\frac{1}{2}');

(For reference, this is TeX, used in this case for typesetting mathematics.)

Problem

Now, when I import this backup with

mysql -u username -p database_name < file.sql

the backslash in the col2 value disappears. Likely, instead of inserting it, it's being used to escape the following character.

Question

Is there a way to insert this while maintaining the backslash?

My idea so far is to make a script that plows through the file and replaces every occurrence of \ with \\. Is this reliable?

I would assume that someone already thought of this problem and maybe created some way of dealing with it.

Any help appreciated!

If you use mysqldump to export your data it will take care of this for you. For example:

mysql> select * from table1;
+------+-------------+
| id   | mystr       |
+------+-------------+
|    1 | \frac{1}{2} |
+------+-------------+

mysqldump -uroot -p database1 table1 > table1.sql

cat table1.sql | grep VALUES
INSERT INTO `table1` VALUES (1,'\\frac{1}{2}');

notice the escaped backslash? and:

mysql -uroot -p database1 < table1.sql

mysql -uroot -p database1 -e "select * from table1"
Enter password:
+------+-------------+
| id   | mystr       |
+------+-------------+
|    1 | \frac{1}{2} |
+------+-------------+


Retrieve data from two tables where table 2 has multiple entries for one value I am looking for the best way to create a SELECT statement to pull data from 2 tables. Example below is the table structures:

Table 1           | Table 2
------------------+----------------------------------
Table1.Customerid |
                  | Table2.Customerid | Table2.Userid
                  | Table2.Customerid | Table2.Userid

Basically, I have in table 2, multiple records showing assignment of multiple userids to one customerid. On the page this data displays, I'm getting duplicate Customer IDs because there are multiple entries for that customer id in table 2.

How do I display the customer ID only once even if there are multiple entries in table 2?

Here is a sample of the query currently being executed with an additional search parameter in place for the company name:

SELECT '' as campaigndaysremaining, COUNT(*) OVER () AS totalrecordcount, *
FROM customers C
LEFT OUTER JOIN customer_users CU on C.customerid = CU.customerid
WHERE C.customerid > 0
AND (C.company like '%blue%' OR c.altcompanyname like '%blue%')
AND status = 0
GROUP BY
    c.customerid,c.company,c.firstname,C.lastname,C.address1,C.address2,
    C.city,C.state,C.zip,C.country,C.phone,C.fax,C.cellphone,
    C.reference1,C.reference2,C.groupid,C.email,C.residential,C.status,
    C.datecreated,C.campaigndays, C.campaignstartdate, C.campaignenddate,
    C.lastupdate, C.lastupdateuser, C.campaignactive, C.customerstatus,
    C.regionid, C.categoryid,C.viewed,C.altcompanyname,C.url,cu.id,
    cu.customerid,C.lockaccount,cu.userid, cu.request, cu.regionid,
    cu.primaryrep, cu.dateadded
ORDER BY C.company ASC

What I am looking for is this; no matter how many entries any customerid has in the customer_users table, I only want the distinct customer id's to be returned from the query. When a user searches the customers table, I don't want duplicate records in my gridview just because there are multiple users assigned to the customer.

Provided customer_users has ID and the order needed is customers.customerid , customer_users.ID you may ask sql server to show NULL in all but first row in a partition. Adjust ordering as needed.

SELECT '' AS campaigndaysremaining, COUNT(*) OVER () AS totalrecordcount, *,
  [show CustomerId]=CASE ROW_NUMBER() OVER (PARTITION BY C.customerid ORDER BY CU.ID) WHEN 1 THEN C.customerid END
FROM customers C
LEFT OUTER JOIN customer_users CU ON C.customerid = CU.customerid
WHERE C.customerid > 0
ORDER BY C.customerid , CU.ID

Getting only one user is a bit different task. First option is OUTER APPLY to get this only user

SELECT '' as campaigndaysremaining, *
FROM customers C
OUTER APPLY (
  SELECT TOP(1) cu.userid, cu.request, cu.regionid, cu.primaryrep, cu.dateadded ,
     COUNT(*) OVER () AS totalrecordcount
  FROM customer_users CU
  WHERE C.customerid = CU.customerid
  ORDER BY cu.userid -- change ordering as needed
) CU
WHERE C.customerid > 0 AND (C.company like '%blue%' OR c.altcompanyname like '%blue%') AND status = 0
ORDER BY C.company ASC


EXEC master.dbo.xp_cmdshell 'The user name or password is incorrect' I am trying to move a file from a network drive to another server and when I execute this statement. I get an output of

  The user name or password is incorrect

DECLARE @SourceFile AS VARCHAR(500);
DECLARE @DestinationFile AS VARCHAR(500);
DECLARE @Cmd AS VARCHAR(500);

SET @SourceFile='\\SHDVNFDBV1\shared-backup\netFORUM_Archive.bak';
SET @DestinationFile ='J:\netFORUM_Archive_TEST.bak';

SET @Cmd = 'COPY ' + @SourceFile + ' ' + @DestinationFile;

EXEC master.dbo.xp_cmdshell @Cmd

You should check if you have permissions or not

exec xp_cmdshell 'net use \\SHDVNFDBV1\shared-backup\  /USER:domain\username Passw0rd1'

Do not copy to root directory as @AaronBertrand mentioned.

As a side note, why are you using SQL Server to do filesystem tasks? This should be done by PowerShell e.g. Copy-Item c:\scripts\test.txt c:\test


SQLDeveloper set default linesize As per title, how to set Oracle SQL Developer default linesize so that I don't have to set it every time I open the program?

Create an SQL file with the content that sets the desired linesize, e.g:

login.sql

set linesize 200

Then in SQL Developer, go to Tools/Preferences/Database, and set this file at Filename for connection startup script.


How will you map the permission control between two entities? I have the following entities: Producer, Consumer.

A Producer produces Food for the Consumer.
A Consumer consumes Food produced by the Producer.

Now I am tasked to design a schema which maps which consumers can eat food produced by each producer.

As an example, I have Producers p1, p2, p3 and Consumers c1, c2, c3.

   c1  c2  c3
p1  N   Y   N
p2  Y   N   N
p3  N   N   Y

where N = No and Y = Yes

Keep in mind that

the number of consumers and producers will keep growing.
the default state of whether a consumer can eat food from a new producer is No

How should I define this schema?

One approach I thought of is to create a table

producer_consumer_allowed (producer_id, consumer_id).

A consumer is allowed to consume food from a producer if an entry for both this producer and consumer exist in this table.

Whenever we add a new producer, we do not need to do anything since the default state of consumers-producers is No.

Whenever we add consumers, we also do not need to add entires into this table since the default state is also No.


Remove a node from Failover Cluster We had 5 SQL Server 2012 servers configured with AlwaysOn.
A few days ago we dismissed 2 of them, we removed them from the availability group and then stopped SQL services. We need to reuse these servers for other purposes.

I need to know how to remove them also from the Windows Failover Cluster on which AlwaysOn is based.

Is it enough to click on the nodes and press "stop cluster service" and then "evict"?

Or there is something to do also with the SQL server installation?

Please note that the two servers will be formatted, so local SQL installation can be destroyed.

Removing SQL Server from failover cluster - you have to run the setup.exe and in maintenance you have to select remove node.

Then removing the node from windows cluster, I would suggest you use PowerShell

Remove-ClusterNode –Name node4 #change your node name here

  The Remove-ClusterNode cmdlet removes a node from a failover cluster. After the node is removed, the node no longer functions as part of the cluster unless the node is added back to the cluster. Removing a node is also called evicting a node from the cluster.

The above commmand will ask your confirmation if you want to really evict the node or not -- unless you use use /force switch.


Running a SQL Server query with as little impact as possible I'm looking to create some basic scripts that are aimed at running on production systems.

I want them to run with as little impact as possible.

They shouldn't affect performance whenever possible, and should be safe to run.

So far I've done this by:

Keeping them small
Keeping them simple
Using WITH (nolock)
Using OPTION (MAXDOP 1)

Is there anything else I could add to limit their impact?

An example of a script I'm looking into is:

SELECT
    cast(SQLProcessUtilization as nvarchar) AS [SQL Server Process CPU Utilization],
    cast(SystemIdle as nvarchar) AS [System Idle Process],
    100 - SystemIdle - SQLProcessUtilization AS [Other Process CPU Utilization],
    DATEADD(ms, -1 * (@ts_now - [timestamp]), GETDATE()) AS [Event Time]
FROM
    (SELECT
         record.value('(./Record/@id)[1]', 'int') AS record_id,
         record.value('(./Record/SchedulerMonitorEvent/SystemHealth/SystemIdle)[1]', 'int') AS [SystemIdle],
         record.value('(./Record/SchedulerMonitorEvent/SystemHealth/ProcessUtilization)[1]', 'int') AS [SQLProcessUtilization], [timestamp]
     FROM
         (SELECT
              [timestamp], CONVERT(xml, record) AS [record]
          FROM
              sys.dm_os_ring_buffers WITH (nolock)
          WHERE
              (ring_buffer_type = N'RING_BUFFER_SCHEDULER_MONITOR'
              AND record LIKE '%<SystemHealth>%')
         ) AS x
      ) AS y
ORDER BY
    [Event Time] asc;

Most of these scripts will be used to allow our first line help desk to gather information in a consistent way when requested.

It rather depends on what the scripts are intended to do...

If you can cope with the occasional dirty read or unrepeatable read issue (or they are truly simple enough that these are unlikely/impossible), also set your transaction handling to the most lenient mode possible with SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED. This is actually equivalent to WITH(NOLOCK) but covers the entire connection going forward instead of just the one table. There is also SET TRANSACTION ISOLATION LEVEL SNAPSHOT which generally stops your query from blocking other concurrent transactions without the risk of dirty or unrepeatable reads, but that does have some potential IO impact on tempdb.

If your queries are making any updates to the database then note that WITH(NOLOCK) support is going to be removed from UPDATE and DELETE statements at some point (see https://technet.microsoft.com/en-us/library/ms187373.aspx).


postgresql being slow on count distinct for dates I have a very simple, but very big, table.
Its schema is like this

(yadda int, yadda1 int, yaddate date, ... other stuff).

Now, yaddate has an index by itself and it is also in other indexes together with other columns (eg. (yadda1, date)).

The table itself is some 100M rows.

When I run

  select distinct date from mybigtable;

the time needed to get the list is in the range of 200 seconds.
Explain Analyze tells me it's doing a seq scan and I don't understand why, since I the index is there.

First thing I am trying is reindex on the date only column index.

Am I doing something wrong?
Since obviously there's something I am missing about seq and index scan, can someone shed some light?
How can I make that query faster?

TIA.

There is a trick with distinct to get it fast using index, that you can try.
It involves creating a function looking like that:

CREATE OR REPLACE FUNCTION small_distinct(IN tablename character varying, IN fieldname character varying, IN sample anyelement DEFAULT '1800-01-01'::date)
  RETURNS SETOF anyelement AS
$BODY$
BEGIN
   EXECUTE 'SELECT '||fieldName||' FROM '||tableName||' ORDER BY '||fieldName
      ||' LIMIT 1'  INTO result;
   WHILE result IS NOT NULL LOOP
      RETURN NEXT;
      EXECUTE 'SELECT '||fieldName||' FROM '||tableName
         ||' WHERE '||fieldName||' > $1 ORDER BY ' || fieldName || ' LIMIT 1'
         INTO result USING result;
   END LOOP;
END;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100
  ROWS 1000;

Then create an index on the column you want to count distinct, and select small_distinct('yourtable', 'yaddate'); should return you the distinct values you want, without the need to read the table.

Try it, be beware, I'm not sure it will work right out of the box, as I quickly adapted it from a varchar function.


Backup database and clean old backup question Right now we have a maintenance plan which looks like this:

backup all databases
clean old backups (> 24 hours)

This job runs every day.
Every now and then this script crashes during backup step so we are left with all old backup files.

We want to rewrite this maintenance plan so it runs like this:

backup database 1
clean old backup for database 1
backup database 2
clean old backup for database 2
etc...

For the first we can use built in actions.
Is there any simple approach for the last method described? Or do we have to script it completly?

We don't want to add all databases manually because then we have extra maintenance work everytime somebody adds a new database which also results in potenitially unbackuped databases.

You should probably move from maintenance plans to SQL Agent Jobs and leverage a script like those written by http://ola.hallengren.com/.  You would create a Job Step for each database.  It will give you the flexibility that you are looking for.

UPDATE: My info above was incorrect. You can use the Ola.Hallengren.com script to do what you are looking for without having to add a job step for each database.  After reviewing the script info I saw this:

From: http://ola.hallengren.com/sql-server-backup.html

  CleanupTime

  Specify the time, in hours, after which the backup files are deleted.
  If no time is specified, then no backup files are deleted.

  Backup files are deleted after each database is backed up and
  verified. Backup files are deleted only if the backup and verification
  of the database were successful.

  DatabaseBackup has a check to verify that transaction log backups that
  are newer than the most recent full or differential backup are not
  deleted. This is to guarantee that you can always perform a
  point-in-time restore.


High Available PostgreSQL with Patroni and Replication I want to build a highly available PostgreSQL cluster using patroni with etcd. I want to have 3 server (1 master and 2 slaves) in my cluster, so if something goes wrong one of the slaves automatically takes over the master role. Only the master server should accept write operators, the slaves are only for safety and read operations.
On the other hand i also need approximately 10 servers with an up-to-date copy of the data on the cluster.
These servers are only there to read data and none of them ever can be promoted to a master server. They shouldn't even be part of the high available cluster.
They are just supposed to get the data using streaming replication etc.
Is such a setup even possible? Can i use replication to other servers when they are part of a patroni cluster?

Only the master server should accept write operators

This is guaranteed by patroni for almost all cases. Essentially, you need to be malicious to provoke a "split-brain" scenario where more than one database accepts writes in a patroni cluster. There are several layers of security against this scenario built into patroni.

On the other hand i also need approximately 10 servers with an up-to-date copy of the data on the cluster. These servers are only there to read data and none of them ever can be promoted to a master server.

This is simple and straigh-forward to do in patroni:
In the configuration file for each of these nodes you can enable a special "tag", nofailover: true, so these database instances will never be considered during leader election, even thought they are in the same patroni cluster. The advantage of doing this is that patroni still takes care of all management, by creating replication slots, running pg_basebackup, keeping the configuration in sync etc.
https://patroni.readthedocs.io/en/latest/SETTINGS.html?highlight=nofailover#tags
In the end, if you prefer not having these replicas directly in your patroni cluster, you can always set up additional streaming replication independently, ontop of what patroni configures, though I think this only makes life more difficult...


Postgres latest sequence id and uncommitted writes Hoping someone could clarify a section of this article "scalable-incremental-data-aggregation" and the corresponding question.
Assume Postgres 10 and up.

In Postgres 10, you can use the pg_sequence_last_value function to check the most recently issued sequence number.  However, it would not be safe to simply aggregate all events up to the most recent sequence value. There might still be in-progress writes to the events table that were assigned lower sequence values, but are not yet visible when the aggregation runs.

Are there alternatives to pg_sequence_last_value that guarantee the correct seq id?

In a table (call it events) with a BIGSERIAL id, is there a preferred way to fetch the latest seq id for aggregation purposes? It's fine if there are pending inserts in a different transaction (they'll have higher sequence ids) and be picked up on subsequent runs.
The goal is to use a seq id to run aggregation (incremental rollups) on already committed inserts.
Are the following three methods guaranteed to return the latest seq id for committed inserts and avoid the pitfall mentioned in the article relating to pg_sequence_last_value?
Are there other techniques one might suggest?
SELECT max(id) FROM events;

SELECT last_value FROM pg_sequences WHERE sequencename='events_id_seq';

SELECT id FROM events ORDER BY id DESC LIMIT 1;

Assume read committed isolation level

select max(id) is the most reliable way.
It's also quite fast if you have a b-tree index on id (performance is virtually independent of the size of the table).


Why is to_char left padding with spaces? When ever I use 099 indicating 3 digits 0-padded, I'm getting spaces on the left side.

SELECT '>' || to_char(1, '099') || '<';
 ?column?
----------
 > 001<
(1 row)

Why is to_char left padding here? Why are there leading spaces?

You can see that in a simpler test case here

SELECT '>' || to_char(1, '0') || '<';
 ?column?
----------
 > 1<
(1 row)

This is because, as @Abelisto said, the space is reserved for the sign glyph,

SELECT '>' || to_char(-1, '0') || '<';
 ?column?
----------
 >-1<
(1 row)

You can suppress the sign using FM, from the docs

Modifier Description Example
FM       prefix      fill mode (suppress leading zeroes and padding blanks) FM9999

So what you want is

SELECT '>' || to_char(1, 'FM099') || '<';

The zeros aren't suppressed when you demand them with FM0


Database structure for users, events and event lists I am creating a web service for managing events. The service will have users, who are listed in one database, called Users. It will also have events, which are listed in another database, called Events.

Each event will have a list of invited users, which is variable in length. Each invited user has various properties, including if they accepted the invitation, and if they attended.

I plan to use SQL databases; either SQLite, MySQL or PostgreSQL. However, this could be changed if that was advised.

My question is how should I store the invited user info for each event? The requirements are:

I can look up the users invited to an event and the events a user is invited to equally easily.
The solution is scalable, i.e. if I end up having millions of users and millions of events then the approach can cope with that. Note that each event will only ever have a small number of guests e.g. 2-1000.
The solution is efficient, in access time, computational resources and storage requirements.

Some options I have thought of:

For each event guest list, store the user IDs and other properties as a blob, or a blob per property, in the Events database. The issue here is that the number of guests varies. Fixing the length would mean wasting space or limiting the max guest length size. Also, this approach doesn't allow a fast look up of events a user is invited to.
For each user, store the list of events they've been invited to, plus the other properties, as a blob in the Users database. Again, the issues are that the number of events varies, and this approach doesn't allow a fast look up of users invited to an event.
Combination of 1 and 2. This introduces redundancy, and the possibility of errors occurring when keeping the two databases in sync.
Separate SQLite database for each event user list. I suspect this will be very inefficient both storagewise and computationally. Also it doesn't allow the events a user is invited to to be computed easily.
Another database which stores user-event items, one for each user invited to each event. This must allow fast filtering by user or event. I'm new to databases so don't know if this can be achieved in an SQL database, or requires another type, and if so, what.

Comments on these options, and any further suggestions, are gratefully received.

Based on your comment above, it sounds like when you said everything was stored in separate "databases", you actually meant to say "tables". I'm going to work with that assumption...

I'd suggest a simple table that stores who's been invited to which event, and what their response was.

invitations
-----------
  id (primary key for the table)
  user_ID (foreign key to users.ID)
  event_ID (foreign key to events.ID)
  response (could be a simple string, or an ID to a table of possible responses, such as "attending", "not attending", "unsure"...)
  date_sent (a date showing when the invitation was sent)
  respond_by_date (date that a user must response by)
  mode_sent (was the invitation sent by email, text message, other electronic message, regular mail, semaphore flags, etc...)

The above structure will keep track of which user was invited to which event. It will also keep track of the user's response. Other fields such as date_sent aren't necessary, but could be useful for other purposes. Data for this might look like:

invitations
-----------
id   |  user_id  | event_id   | response  |  date_sent  | respond_by_date  | mode_sent
--------------------------------------------------------------------------------------
1    | 123       | 5          | attending |  2014-12-01 | 2014-12-05       | email
2    | 456       | 5          | not attnd |  2014-12-01 | 2014-12-05       | email
3    | 456       | 7          | attend+1  |  2015-01-01 | 2014-12-02       | semaphore flags

(This assumes that the user and event tables already exist)

Would this work for your purposes?


SQL server is there recursive reserve word? Is there any reserve word 'RECURSIVE' while using CTE query. For example,

WITH RECURSIVE node_rec AS (
   (SELECT 1 AS depth, ARRAY[node] AS path, *
    FROM   nodes
    WHERE  parent IS NULL
    LIMIT  10
   )
    UNION ALL
    SELECT r.depth + 1, r.path || n.node, n.*
    FROM   node_rec r
    JOIN   nodes    n ON n.parent = r.node
    WHERE  r.depth < 4
)

How to achieve ?

Ms Sql recursive CTE syntax has no RECURSIVE keyword See
https://technet.microsoft.com/en-us/library/ms186243(v=sql.105).aspx
Ie. CTE of the form

WITH cte_name ( column_name [,...n] )
AS
(
–- Anchor member
UNION ALL
–- Recursive member
)

is recursive when Recursive member references  cte_name. For example

WITH node_rec AS (
    SELECT TOP(10) 1 AS depth, node AS path, *
    FROM   nodes
    WHERE  parent IS NULL
    UNION ALL
    SELECT r.depth + 1, r.path +'/'+ n.node, n.*
    FROM   node_rec r
    JOIN   nodes n ON n.parent = r.node
    WHERE  r.depth < 4
)


MySQL Explain gives different result for same query, query performance changes I've been having some performance issues on my production server. The main issue, I think is the table pricelist_entries, which has approximately 6 million rows. Basically its holds all of the products available. The query below usually takes a second to execute but ever since the table pricelist_entries grew past 3 million rows the execution time has started to increase. The weird thing is that sometimes the same query will take 10 seconds, other times 1 second. First I thought that maybe its the query cache but I disabled that and I still get the same variating result. What I can see from the two explains is that when its fast the query only accesses about 5000 rows while when its slow it seem to do an almost full table scan. I can't figure out why this happens. Any help is appreciated.

Thanks for confirming that innodb_buffer_pool_size is OK.

Here are some ideas; I don't know which will be best:

Plan A

pricelist:  INDEX(pCurrent, tagged_to)
materials:  INDEX(companyID, article_nr_lev)

Plan B

SELECT STRAIGHT_JOIN ...
    FROM pricelist ...    -- first
    JOIN pricelist_entries ...
    LEFT JOIN materials

Please provide SHOW CREATE TABLE for each table; there could be some more clues there.

More

materials.article_nr_lev = pricelist_entries.peArtnr -- Whenever JOINing, the datatypes must be the same (or 'close').  Those two value are probably close enough -- same collation (mandatory) and both VARCHAR.  But they are of different lengths; I think that is 'close enough'.  However, do you sometimes need 255 characters, and sometimes need only 50?  For various subtle reasons (having to do with the execution of queries), it is better to keep VARCHAR sizes smaller (yet large enough to be safe).  I suspect the 255 could be changed to 50 without harm.

Also you have a lot of 4-byte INT fields that could probably be turned into smaller fields (eg, 2-byte SMALLINT UNSIGNED).  Shrinking table sizes often leads to improved performance, albeit minor.

category int(11) NOT NULL COMMENT '0 - Inget val 1 - Alkov 2 - ... -- Consider using ENUM.  That would avoid remembering the mapping.  (And take 1 byte instead of 4.)

Be careful about CHARCTER SET -- some columns are dec8, some are utf8.  The may be issues with comparisons, conversions, etc.  (I haven't noticed any problems with the query in question.)


MySQL Data Synchronization I have a user who will be away from the office for a while, but still wants to be able to insert a load of records to our MySQL database. The user will have a laptop, but no reliable internet connection so no VPN etc.

I'm aware that MySQL has a Master/Slave Replication feature, but unless I've misunderstood, this only provides read-only synchronization for the slaves.

I'm thinking that I can take a snapshot of the master database and copy it to a local server on the user's laptop. From this point onwards the local and master database will of course be out-of-sync. However, because the user only intends to add records while away and not delete or update records I'm thinking, perhaps naively, that a re-sync should be relatively easy to do with some SQL scripting.

Is this the right way to go? Are there any hidden dangers to be aware of?

As long as the traveling user will be adding, updating, and deleting records and can guarantee that no one else is touching in the master, there is something you can try.

What you want to do is setup MySQL Circular Replication (Master/Master) between Master and LapTop. Once you have this esatblished and the time has come for the traveling user (TVU) to leave for the week, here is what to do:

Step 1 : Run STOP SLAVE; on the Master

Step 2 : Run STOP SLAVE; on the LapTop

Step 3 : TVU inserts, updates, and deletes on the LapTop

Step 4 : Make sure TVU does not delete or update data that the Master will have worked on during the week.

Step 5 : When the TVU returns, run START SLAVE; on the Master to reconnect the Master to the LapTop. All inserts, updates, and deletes that occurred on the LapTop during the week  will transmit over to the Master. Run SHOW SLAVE STATUS\G every couple of minutes on the Master and look for Seconds_Behind_Master going to 0. When it does, the data from the LapTop will be sync'd over to the Master.

Step 6 : (OPTIONAL) Run START SLAVE; on the LapTop to bring all the inserts, updates, and deletes the Master did during the week. Run SHOW SLAVE STATUS\G every couple of minutes on the LapTop and look for Seconds_Behind_Master going to 0. When it does, the data from the Master will be sync'd over to the LapTop.

The only real danger is if you delete or update data on the LapTop that still needs to be on the Master. If you forget that, step 5 will make that data change or disappear unintentionally. As long as you work with a new set of data on the LapTop only, there should be no problem.

You should test this between a DevServer and a LapTop you are using for Development only.

UPDATE 2011-07-24 21:42 EDT

Here is another thing you can try:

Step 1 : Setup mysql on the LapTop with no initial data on the laptop and no binary logging

Step 2 : mysqldump the data out of the Master and into the LapTop

Step 3 : Add this to my.ini on the LapTop

[mysqld]
log-bin=mysql-bin

Step 4 : Activate binary logging without a mysql restart on the LapTop

SET GLOBAL SQL_LOG_BIN = 0;

or just restart mysql

Step 5 : Perform inserts, updates, and deletes on the LapTop (Make sure they do not conflict with needed data on the Master)

Step 6 : run this

mysqlbinlog mysql-bin.0* > ThisWeeksChanges.sql

Step 7 : Run this on the Master

mysql> source ThisWeeksChanges.sql


Understanding Transaction Recovery In the following reference, there is an algorithm outlining a transaction recovery algorithm using redo and undo. The algorithm is as follows:

UNDO and REDO: lists of transactions

UNDO = all transactions running at the last checkpoint
REDO = empty

For each entry in the log, starting at the last checkpoint
  If a BEGIN TRANSACTION entry is found for T
    Add T to UNDO
  If a COMMIT entry is found for T
    Move T from UNDO to REDO

It then gives the example:

but this is clearly wrong?? Since T2 has been committed, T must be in the REDO list. Am I missing something?

If you look throught the slides from beginning to end you will find that the explanation is pretty accurate.

The recovery of the database starts.

The depicted slide is showing you the situation as it is at the time when the vertical axis is at the checkpoint.

At that time T2 and T3 are put into the UNDO queue.
Moving forward (in the reference) the examined time (vertical axis) moves forward and reaches the BEGIN of T4. The transaction T4 is put into the UNDO queue.
Moving forward (in the reference) the examined time (vertical axis) moves forward and reaches the BEGIN of T5. The transaction T5 is put into the UNDO queue.

  You now have all four transactions T1, T2, T3, T4 in the UNDO queue.

Moving further forward (in the reference) the examined time (vertical axis) moves forward and reaches the END of T2. The transaction T2 is put into the REDO queue.
Moving forward (in the reference) the examined time (vertical axis) moves forward and reaches the END of T4. The transaction T4 is put into the REDO queue.

  The transactions T3 and T5 are in the UNDO queue.
  The transactions T2 and T4 are in the REDO queue.

Moving even further forward (in the reference) the examined time (vertical axis) moves forward and reaches the Failure.

  T3 and T5 are UNDO-ne and T2 and T4 are REDO-ne (or reapplied).

The database is now in a consistent state.


General purpose view for last value in a group I am trying to create a view to show the last value in the group.
Looking for a more general purpose query.

CREATE TABLE a (
   id INTEGER,
   seq INTEGER,
   val VARCHAR2(16),
   PRIMARY KEY(id,seq)
);

-- View 1
CREATE VIEW v1 AS SELECT a.*
 FROM
  a,
  (SELECT id, MAX(seq) from a GROUP BY id) b
 WHERE a.id=b.id AND a.seq=b.seq

-- View 2
CREATE VIEW v2 AS SELECT a.*
 FROM
  a
 WHERE a.seq=(SELECT seq from a b WHERE a.id=b.id ORDER BY seq DESC LIMIT 1)

View 1 perform better when I want to list all records.

View 2 perform better when I do  SELECT * FROM v2 WHERE id=?

Because this view is to be given to outsiders I want this to be as general purpose as possible. Is there any way to tell PostgreSQL these two query is equivalent and let it pick them automagically?

This form with DISTINCT ON is typically faster than either v1 or v2:

CREATE VIEW v3 AS
SELECT DISTINCT ON (id) a.*
FROM   a
ORDER  BY id, seq DESC NULLS LAST;

You get sorted output (by id) on top of it, which is typically a welcome side effect.
Minor difference: this form includes a row for ids with only NULL values in seq. This, too, would be a welcome effect, typically. And irrelevant if seq is defined NOT NULL.

If performance is essential I strongly suggest to create a matching (!) multicolumn index of the form:

CREATE INDEX a_id_seq_idx ON a (id, seq DESC NULLS LAST);

A lot more details and more alternatives under this related question on SO:
Select first row in each GROUP BY group?

Switch query depending on input

If you are interested in a switching technique per se, rather, you could create a table function instead of the view which takes an id as parameter.

CREATE OR REPLACE FUNCTION f_a(_id int DEFAULT NULL)
  RETURNS SETOF a AS
$func$
BEGIN

IF _id IS NULL THEN
    RETURN QUERY
    SELECT ... ;
ELSE
    RETURN QUERY
    SELECT ...
   WHERE  id = _id;
END IF;

END
$func$  LANGUAGE plpgsql;

Call:

SELECT * FROM f_a();     -- for the whole table
SELECT * FROM f_a(123);  -- for a particular id

Could be improved with more parameters.

This approach is perfect for simple calls. Even saves some planning overhead and is typically faster than raw SQL this way. Be advised, though, that a plpgsql function poses as optimization barrier. The body of the function cannot be inlined or optimized in the context of a bigger query (like a view or a simple SQL function could be). More in this related answer:
PostgreSQL Stored Procedure Performance


Subquery returned more than 1 value. please help. i beg for mercy This is may table. In one column there are daily dates and in the other is a classification.

I use this query to count how many registers are in a month and which ones are C or D, but the subquery can only return one value . How can i fix it?

select FORMAT(fecha, 'yyyy-MM') as FECHAS, COUNT(*) as TOTAL,
(select count(tipo) from prueba where tipo = 'C' group by FORMAT(fecha, 'yyyy-MM')) as CREDITO,
(select count(tipo) from prueba where tipo = 'D' group by FORMAT(fecha, 'yyyy-MM')) as DEBITO
from prueba
where fecha >= '01-08-2014'
group by FORMAT(fecha, 'yyyy-MM')

You didn't specify which database you were using, but in most databases, you can use a CASE expression inside the COUNT aggregate function to accomplish your goal:

select FORMAT(fecha, 'yyyy-MM') as FECHAS,
       COUNT(*) as TOTAL,
       COUNT(CASE WHEN tipo = 'C' THEN 'X' END) as CREDITO,
       COUNT(CASE WHEN tipo = 'D' THEN 'X' END) as DEBITO
from PRUEBA
where fecha >= '01-08-2014'
group by FORMAT(fecha, 'yyyy-MM')


LEFT JOIN performance for WHERE on secondary table I've been developing for years and through out the carrier I've been using only LEFT JOIN as it provides a more logical sense of mapping my one to many data.

However recently I ran across a performance thought:

Say I have table user with millions of rows and table gender with only 2 rows

user                                gender
=====================               ================
id name      genderId               id gender show
=====================               ================
1  Sam       1                      1  Male   0
2  Samantha  2                      2  Female 1
3  Another millions of records

If I'm doing

select * from user u left join gender g on u.genderId = g.id where g.show = 1;

Does SQL engine joins the whole million user records first, then scan the million rows of records for join projected value?

Or is the engine smart enough to query and join only the rows with correct gender? Or is RIGHT JOIN the correct method to provide best performance?

If the behavior is dependent on engine type, let's talk about MySQL InnoDB.

Remember how SQL works: construct a set of tuples (rows) based on the given tables and joins, then filter those based on any where clause.
Because you have a where condition on the "right" table, your outer join is effectively converted into an inner join.

To retain the "outer-ness" of the join, put the filter condition into the join construct, rather than in the where clause:

select *
from      user u
left join gender g
     on  u.genderId = g.id
     and g.show = 1
[where ...]