InnoDB temporary table location I have a busy InnoDB table that expands and contracts regularly in size on a daily basis based on it's load. Typically, we'd see the table grow to 80GB in size (50/50 data and indexes) and then shrink down to around 30GB. Unfortunately, InnoDB doesn't like freeing up space it's not using anymore which doesn't bode well for our 120GB SSD.

Typically, an optimize (or recreate for InnoDB tables) would give us a bit more breathing space. But sometimes there isn't enough room for the temporary table to be created.

Despite setting a different location for the temporary table variable in the my.cnf, the InnoDB database still insists in creating temporary tables in it's data directory, within the database folder. Is there any way I can point MySQL to a different drive when creating temporary InnoDB tables?

I'm sure it would do more damage than good, but it was suggested here that we could potentially create a symlink for the database on a larger drive, then symlink the tables to the SSD. That way, all standard operations will run on the much faster SSD and routine maintenance tasks will run on the larger, slower drive.

EDIT: I should clarify. innodb_file_per_table is set and so is tmpdir (to the larger drive), but tmpdir seems to do nothing for me.

Symlinking InnoDB is definitely not a good idea for regular maintenance of InnoDB.
I wrote posts on why not to do this:

Aug 07, 2011 : MySQL InnoDB data file directly on a disk slice (fixed-size)?
Mar 12, 2012 : How to set a specific directory location for individual MySQL database?

However, if this strictly for the purpose of shrinking a large table, I guess you can symlink the whole data directory where the temp table is to go, but you will have to personally script the shrinkage.
For example

Suppose the table to shrink is mydata.mytable
datsdir is /var/lib/mysql

Your procedure would be
STEP 01: You should create a database called mydatatemp. Do not put any data in it just yet.
STEP 02: Move the /var/lib/mysql/mydatatemp over to another disk.
STEP 03: Create a Symlink /var/lib/mysql/mydatatemp over to the folder on the other disk
STEP 04: chown -R mysql:mysql against the external folder and against the symlink.
STEP 05: Now perform the data shrinkage as follows:
CREATE TABLE mydatatemp.mytable LIKE mydata.mytable;
INSERT INTO mydatatemp.mytable SELECT * FROM mydata.mytable;
DROP TABLE mydata.mytable;
ALTER TABLE mydatatemp.mytable RENAME mydata.mytable;

To recap, do the following (assuming /tmpdata is the external disk)
STEPS 01-04
chown mysql:mysql /tmpdata
ln -s /tmpdata /var/lib/mysql/mydatatemp
chown -R mysql:mysql /var/lib/mysql/mydatatemp

then run STEP 05
CAVEAT
This would be rather high risk, but it should work. Please test this with an empty table first like this:
USE mydatatemp
CREATE TABLE mrnorm (id int not null, name varchar(20) primary key (id));
INSERT INTO mrnorm (name) VALUES ('one'),('two'),('three');
SELECT * FROM mrnorm;

If these SQL command worked, go to OS and do this:
cd /tmpdata
ls -l

You should see two files:

mrnorm.frm
mrnorm.ibd

Also, test if mrnorm can be moved
CREATE DATABASE helloworld;
ALTER TABLE mydatatemp.mrnorm RENAME helloworld.mrnorm;

Go to the OS and run
cd /tmpdata
ls -l

The table should be gone. Now, do this:
cd /var/lib/mysql/helloworld
ls -l

The table should be there
END OF TEST
Keep in mind that the rename of the table would essentially be controlled copy of the table from one disk to another disk with a proper adjusting of the data dictionary section of the system tablespace in ibdata1. This could take some time.


Mysql Range partition I have the next table structure:

 CREATE TABLE `sat_msgPos` (
  `idOwnApp` mediumint(11) NOT NULL DEFAULT '0',
  `idMsg` bigint(11) unsigned NOT NULL AUTO_INCREMENT,
  `idVeh` mediumint(11) NOT NULL DEFAULT '0',
  `idProviderComm` mediumint(11) DEFAULT NULL,
  `txMsg` varchar(255) DEFAULT NULL,
  `tmMsg` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  `amLat` double DEFAULT NULL,
  `amLong` double DEFAULT NULL,
  `tmUpdate` datetime DEFAULT NULL,
  `idMsgBlobParent` int(11) DEFAULT NULL,
  `txDescPos` varchar(255) DEFAULT NULL,
  `idMsgVpr` int(11) DEFAULT NULL,
  `amSpeedCurr` float DEFAULT NULL,
  `idSts` char(1) DEFAULT NULL,
  `idTrigger` tinyint(3) unsigned DEFAULT NULL,
  `amDistTot` mediumint(9) DEFAULT NULL,
  `amEngMinsTot` float DEFAULT NULL,
  `inException` tinyint(2) DEFAULT '0',
  PRIMARY KEY (`idMsg`,`idVeh`,`tmMsg`),
  KEY `X1` (`idMsgBlobParent`),
  KEY `X2` (`idMsgVpr`),
  KEY `X3` (`tmMsg`),
  KEY `X4` (`idVeh`,`tmMsg`,`idTrigger`)
) ENGINE=InnoDB AUTO_INCREMENT=2102445247 DEFAULT CHARSET=utf8
/*!50500 PARTITION BY RANGE  COLUMNS(tmMsg)
(PARTITION a9 VALUES LESS THAN ('2017-02-01') ENGINE = InnoDB,
 PARTITION a10 VALUES LESS THAN ('2017-03-01') ENGINE = InnoDB,
 PARTITION a11 VALUES LESS THAN ('2017-04-01') ENGINE = InnoDB,
 PARTITION a12 VALUES LESS THAN ('2017-05-01') ENGINE = InnoDB,
 PARTITION a13 VALUES LESS THAN ('2017-06-01') ENGINE = InnoDB,
 PARTITION a14 VALUES LESS THAN ('2017-07-01') ENGINE = InnoDB,
 PARTITION b1 VALUES LESS THAN ('2017-08-01') ENGINE = InnoDB,
 PARTITION b2 VALUES LESS THAN ('2017-09-01') ENGINE = InnoDB,
 PARTITION b3 VALUES LESS THAN ('2017-10-01') ENGINE = InnoDB,
 PARTITION b4 VALUES LESS THAN ('2017-11-01') ENGINE = InnoDB,
 PARTITION b5 VALUES LESS THAN ('2017-12-01') ENGINE = InnoDB,
 PARTITION b6 VALUES LESS THAN ('2018-01-01') ENGINE = InnoDB,
 PARTITION b7 VALUES LESS THAN ('2018-02-01') ENGINE = InnoDB,
 PARTITION b8 VALUES LESS THAN ('2018-03-01') ENGINE = InnoDB,
 PARTITION b9 VALUES LESS THAN ('2018-04-01') ENGINE = InnoDB,
 PARTITION b10 VALUES LESS THAN ('2018-05-01') ENGINE = InnoDB,
 PARTITION b11 VALUES LESS THAN ('2018-06-01') ENGINE = InnoDB,
 PARTITION b12 VALUES LESS THAN ('2018-07-01') ENGINE = InnoDB,
 PARTITION b13 VALUES LESS THAN (MAXVALUE) ENGINE = InnoDB)

I try to reorganize the b13 partition with following command

ALTER TABLE sat_msgPos REORGANIZE PARTITION b13 INTO(PARTITION c1 VALUES LESS THAN ('2018-08-01'), PARTITION c2 VALUES LESS THAN ('2018-09-01'));

I am receiving following error:

"ERROR 1520 (HY000): Reorganize of range partitions cannot change total ranges except for last partition where it can extend the range
"

How can i split b13 into 2 new partitons without lose the data?

Thanks a lot!

Think of it this way -- REORGANIZE PARTITION reads the source partition(s) and creates the destination partition(s), putting each row wherever it belongs. If the source range and destination range are identical (but broken up differently), then nothing can get lost.

Some comments on the schema:

PRIMARY KEY (`idMsg`,`idVeh`,`tmMsg`),

idMsg is unique; tmMsg needed to be added because it is the "partition key".  But is there any reason to include idVeh?  (There may be, bot note each of your 4 secondary keys will include all 3 of these columns -- somewhat bulky.)

KEY `X3` (`tmMsg`),

It is usually useless to have the partition key at the start of an index.  (Again, you may have a good reason; I am suggesting that this might be a useless index.)

KEY `X4` (`idVeh`,`tmMsg`,`idTrigger`)

Unless you are using = when testing tmMsg, this index is unlikely to get to the idTrigger column.  (Again, I am asking you to justify it; there could be a SELECT that shines because of this index; but it seems unlikely.)

`amLat` double DEFAULT NULL,
`amLong` double DEFAULT NULL,

Are you tracking fleas on dogs?  16 significant digits is overkill.  See here.

About the only benefit in PARTITION by a time is to purge old data. (DROP PARTITION is a lot less invasive than DELETE.)  If you think you are getting some other performance benefit, let me know; I may argue with you.


Keeping an up to date copy of a SQL Server database on another server We outsourced some of our business functions to another company. That company makes use of the same software as we do and we would like to keep a update to date copy of their database on our servers for monitoring purposes.

Just to clarify what I mean by having an an update to date copy we don't mind if the database is only updated or synchronized once a day.

Last thing to keep in mind is that the connection between the companies is a 10 Mbps connection.

We are both running Microsoft SQL Server 2008 R2 (SP2) - 10.50.4000.0 (X64).

How would you tackle this?

Edit

I don't expect the database to grow to more than 8GB in the next 2-3 years.

Just to clarify what I mean by having an an update to date copy we don't mind if the database is only updated or synchronized once a day.

Depending on the database size, I would use backup with compression and restore. You can easily automate this process.

Just take full backup with compression once every night, upload it to FTP (probably secure) and have a process on your end to download it and restore the database (overwriting the previous copy).

There are other methods like using SSIS - if you want selected tables only or using BCP OUT and BCP IN - provided you have same schema on your side.

Dont get into the hassle of implementing replication as making schema changes is a big headache + you have to generate snapshot and depending on the size of your database and network, it will have a performance impact on the source server.


Index Maintenance on busy OLTP database I have a database where constant read writes happening all the time. Searches are being done with wild card entries. The server is on SQL 2016 standard edition.
There is no budget for enterprise nor any intention to not using the wild card searches, and there is no maintenance window.
Since wildcard searches are being made indexes are also of no use as it is doing a full table scan and thus creates locking.
I am using Ola Hallengren script for indexing but the indexes maintenance is taking 10-12 hours to complete for a 300gb database as it busy 24/7.Below is the script
EXECUTE dbo.IndexOptimize
@Databases = 'user_databases',
@FragmentationLow = NULL,
@FragmentationMedium = 'INDEX_REORGANIZE,INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE',
@FragmentationHigh = 'INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE',
@FragmentationLevel1 = 5,
@FragmentationLevel2 = 30,
@UpdateStatistics = 'ALL',
@OnlyModifiedStatistics = 'Y'
This has become a frustrating issue and I am about to quit. Please advise what are my options here.

Don't do index defrag is one option. Did you measure the improvements from defrag in the first place?
HQISILON Doesn't say anything to me, your disk subsystem is one aspect. But also the query load, the execution plans etc. Best is to have something load to measure what you actually gain.
A parallel work should be to investigate whether you have index that don't help you. Sys.dm_db_index_usage_stats can be useful here.
If blocking is your issue then consider doing reorg instead of rebuild. I.e., change the parameters to the proc since you now do rebuild if > 30%. Reorg is more online-ish than rebuild (in offline mode).


MySQL version confusion How is this possible:

I installed mysql-5.6.11-osx10.7-x86.dmg which is supposed to be version Mac OS X ver. 10.7 (x86, 32-bit), DMG Archive (32 bit!)

But upon SHOW VARIABLES LIKE "%version%"; I get the following:

mysql> SHOW VARIABLES LIKE "%version%";
+-------------------------+------------------------------+
| Variable_name           | Value                        |
+-------------------------+------------------------------+
| innodb_version          | 5.6.11                       |
| protocol_version        | 10                           |
| slave_type_conversions  |                              |
| version                 | 5.6.11                       |
| version_comment         | MySQL Community Server (GPL) |
| version_compile_machine | x86_64                       |
| version_compile_os      | osx10.7                      |
+-------------------------+------------------------------+
7 rows in set (0,00 sec)

Why x86_64 ?

Amendment:

Now it becomes weird:
I uninstalled MySQL with the help of https://stackoverflow.com/questions/1436425/how-do-you-uninstall-mysql-from-mac-os-x

Then I installed mysql-5.5.31-osx10.6-x86_64 from http://dev.mysql.com/downloads/mysql/5.5.html#downloads
It is clearly sitting in /usr/bin/local/mysql-5.5.31-osx10.6-x86_64/ (note the 64 at the end).

Now this:

mysqld  Ver 5.5.31 for osx10.6 on i386 (MySQL Community Server (GPL))

and:

mysql  Ver 14.14 Distrib 5.5.31, for osx10.6 (i386) using readline 5.1

and this:

mysql> SHOW VARIABLES LIKE "%version_compile%";
+-------------------------+---------+
| Variable_name           | Value   |
+-------------------------+---------+
| version_compile_machine | i386    |
| version_compile_os      | osx10.6 |
+-------------------------+---------+
2 rows in set (0.00 sec)

How can this be: 32 bit architecture after (obviously) downloading and installing 32 bit?
I am feeling lost.

Let's analyze the string '5.6.11-osx10.7-x86'.
5.6.11 is the MySQL version.
osx is the platform.
10.7 is the version of the MacOS X package for the 5.6.11 MySQL version.

That package was compiled on a x86_64 machine.


Determine 3rd Friday of each month I need to determine the dates which are the "3rd Friday of each month" for a date range of "1.1.1996 - 30.8.2014" in SQL Server.

I expect I should use a combination of DENSE_RANK() and PARTITION BY() to set "rank = 3". However, I am new to SQL and unable to find the correct code.

Given:

Friday is called "Friday"
The 3rd Friday of the month will always
fall from 15th-21st of the month

select thedate
from yourtable
where datename(weekday, thedate) = 'Friday'
and datepart(day, thedate)>=15 and datepart(day, thedate)<=21;

You could also use weekday with datepart(), but it's more readable with a name IMO. String comparisons will obviously be slower though.


Select items that have never been ordered using the MINUS operator I have the following two tables:

ITEM (Item#, Item_Name, Unit_Price)

ORDER_ITEM (Order#, Item#, Qty)

Now, how do I list the items that have never been ordered by using the MINUS operator?

How about:

select item# from item
minus
select item# from ORDER_ITEM


How can I see if the data in a SQL Server table is page-compressed? This is a follow-on question to a question which I asked yesterday: Can I bulk insert into an empty page-compressed table and get full compression? The answer to that question (paraphrased from Randi Vertongen's excellent answer) is yes, but it requires that the bulk insert take a table-level lock; otherwise, the bulk insert takes a row-level lock and performs only row data compression. This raises the question: how can I tell afterward what compression is applied?

Here are the steps to create row-compressed data in a theoretically-page-compressed table:
1. Create a table with DATA_COMPRESSION=PAGE and do not use sp_tableoption to turn on the "table lock on bulk load" option for this table.
2. Use bcp to bulk insert the data from a flat file into the new table, but without specifying the -h TABLOCK option to lock the table.

The result is a table in which the data is compressed on the row level (smaller than an uncompressed table but larger than a page-compressed table), but inspecting the sys.allocation_units catalog table shows the data compression as page.

The question

When the table's data allocation is for page compression as in this scenario, what can I do to find out if the data in that table is page-compressed?

To see whether or not data pages are indeed currently "PAGE" compressed, you can use the undocumented DMF sys.dm_db_database_page_allocations(). The is_page_compressed field contains the info you are looking for. You will need to use the DETAILED mode (i.e. 5th parameter) or else the values in that field will all be NULL.

To be clear (based on the wording of the question being, "what can I do to find out if the data in that table is page-compressed?"), this is not an all-or-none issue: page compression is applied per each data page, hence you can have none that are compressed, all being compressed, or any combination in between. So, you need to look at all of the pages. And no, you cannot necessarily assume that a single non-page compressed page indicates that you need to REBUILD because a non-filled page will not compress.

For example:

SELECT [is_page_compressed]
FROM   sys.dm_db_database_page_allocations(DB_ID(), OBJECT_ID(N'dbo.CompressedHeap'),
                                           0, 1, 'DETAILED');

The following shows that the data pages are not initially page compressed, but they are after the REBUILD operation:

USE [tempdb];

-- DROP TABLE dbo.CompressedHeap;
CREATE TABLE dbo.CompressedHeap
(
  ID INT IDENTITY(1, 1) NOT NULL,
  String sysname,
  [MaxLength] SMALLINT,
  [Type] VARCHAR(5)
) WITH (DATA_COMPRESSION = PAGE);

INSERT INTO dbo.CompressedHeap ([String], [MaxLength], [Type])
  SELECT col.[name], col.[max_length], obj.[type]
  FROM   master.sys.columns col
  CROSS JOIN master.sys.objects obj;

SELECT [is_page_compressed], *
FROM   sys.dm_db_database_page_allocations(DB_ID(), OBJECT_ID(N'dbo.CompressedHeap'),
                                           0, 1, 'DETAILED')
WHERE  [is_iam_page] = 0
AND    [is_allocated] = 1;
-- 394 pages

ALTER TABLE dbo.CompressedHeap REBUILD;

SELECT [is_page_compressed], *
FROM   sys.dm_db_database_page_allocations(DB_ID(), OBJECT_ID(N'dbo.CompressedHeap'),
                                           0, 1, 'DETAILED')
WHERE  [is_iam_page] = 0
AND    [is_allocated] = 1;
-- 179 pages


Can this query be simplified? Calculate cumulative length of segments and collapse coordinates into linestring I have a road_vertices table:

create table road_vertices
(
road_id number,
vertex_index number,
x number,
y number
);

insert into road_vertices values ('100',1,0,5);
insert into road_vertices values ('100',2,10,10);
insert into road_vertices values ('100',3,30,0);
insert into road_vertices values ('100',4,50,10);
insert into road_vertices values ('100',5,60,10);

select * from road_vertices;

   ROAD_ID VERTEX_INDEX             X          Y
---------- --------------- ---------- ----------
       100 1                        0          5
       100 2                       10         10
       100 3                       30          0
       100 4                       50         10
       100 5                       60         10

I need to:

Calculate the cumulative length of line segments (as shown in the grey text in the image above).
Collapse the coordinates and cumulative lengths into a linestring.

This is the end-goal:

ROAD_ID     LINESTRING
----------------------------------------------------------------------------
100         LINESTRING M ( 0 5 0, 10 10 11.18, 30 0 33.54, 50 10 55.9, 60 10 65.9)

I've figured out a way to do it:

--Step #3: Collapse the coordinates and cumulative lengths into a linestring
SELECT
    ROAD_ID,
    'LINESTRING M ( ' || LISTAGG(CUMULATIVE_LENGTH, ', ')
        WITHIN GROUP (ORDER BY VERTEX_INDEX) || ')'  AS LINESTRING
FROM
    (
    --Step #2: Calculate each line segment's length using the Pythagorean theorem, and add together to get cumulative length
    SELECT
        ROAD_ID,
        VERTEX_INDEX,
        X || ' '  || Y || ' ' || ROUND(SUM(NVL(SQRT(POWER((X - PREV_X),2) + POWER((Y - PREV_Y),2)),0))
            OVER (PARTITION BY ROAD_ID ORDER BY ROAD_ID,VERTEX_INDEX),2)
            AS CUMULATIVE_LENGTH
    FROM
        (
        --Step #1: Get the previous X and previous Y for Step #2's Pythagorean theorem calculation
        SELECT
            ROAD_ID,
            VERTEX_INDEX,
            ROUND(X,2) AS X,
            ROUND(Y,2) AS Y,
            LAG (X,1) OVER (PARTITION BY ROAD_ID ORDER BY VERTEX_INDEX) AS PREV_X,
            LAG (Y,1) OVER (PARTITION BY ROAD_ID ORDER BY VERTEX_INDEX) AS PREV_Y
        FROM
            INFRASTR.ROAD_VERTICES
        )
    )
GROUP BY
    ROAD_ID;

However, this solution is quite complicated. Can it simplified/improved?

A combination of a function and a query/view may be another option.  The function fulfils your first requirement: " Calculate each line segment's length ... the portions of lines between vertices." (The function will need exception handling and testing!)

-- -----------------------------------------------------------------------------
--   function: calculate the segment length
-- -----------------------------------------------------------------------------
create or replace function seglength(
  x_ number
, oldx_ number
, y_ number
, oldy_ number
)
return number as
begin
  if oldx_ = 0 or oldy_ = 0 then  -- vertex_index 1, no "previous"/old values
    return 0;
  else
    return round(
        sqrt(
          power( ( x_ - oldx_ ), 2 )
        + power( ( y_ - oldy_) , 2 )
        )
      , 2
    );
  end if;
end seglength;
/

Then, we can use a modified version of your original query, like so:

select
  d.roadid
, 'LINESTRING M ( '
  || listagg( ( round(x,2) || ' '  || round(y,2) || ' '
           || seglength(x, d.old_x, y, d.old_y) ) , ', ' )
     within group ( order by d.vertexindex )
  || ')' linestring
from (
  select
    roadid
  , vertexindex
  , x
  , y
  , case
      when vertexindex = 1 then 0 -- zero instead of NULL
      else ( lag (x,1) over ( partition by roadid order by vertexindex ) )
    end old_x
  , case
      when vertexindex = 1 then 0
      else ( lag (y,1) over ( partition by roadid order by vertexindex ) )
    end old_y
  from rdvx
  ) d
group by d.roadid;

output:

500100  LINESTRING M ( 670113.32 4863724.94 0, 670122.42 4863728.94 9.94, 670259.91 4863776.23 145.39)
507200  LINESTRING M ( 670147.94 4863628.42 0, 670158.74 4863632.98 11.72, 670298.55 4863680.65 147.72)

Note: the last values in the "LINESTRING" are smaller than the ones in your question.  Can it be the case that your original query actually calculates the distance between the vertices 1 and 3?  My understanding is that the "SEGMENT LENGTHS" are supposed to be: distance v1-v1 ie 0, distance v1-v2, distance v2-v3. dbfiddle here

UPDATE

Function:

create or replace function rlength(
  x number
, prev_x number
, y number
, prev_y number
)
return number as
begin
  if prev_x is null or prev_y is null then
    return 0 ;
  else
    return round(
      sqrt(
        power( ( x - prev_x ), 2 )
      + power( ( y - prev_y ), 2 )
      )
    , 2
    );
  end if;
end rlength;
/

Query:

with roads_ as (
  select
    road_id
  , vertex_index
  , round( x, 2 ) x
  , round( y, 2 ) y
  , sum ( rlen ) over ( partition by road_id order by road_id, vertex_index )  clength
  from (
    select
      road_id
    , vertex_index
    , x
    , y
    , rlength(
        x
      , lag( x,1 ) over ( partition by road_id order by vertex_index )
      , y
      , lag( y,1 ) over ( partition by road_id order by vertex_index )
      ) rlen
    from road_vertices
    )
)
select
   road_id
, 'LINESTRING M ( '
  || listagg( x || ' ' || y || ' ' || clength , ', ' )
     within group ( order by vertex_index )
  || ' )' linestring
from roads_
group by road_id;

Test data:

create table road_vertices
(
road_id number,
vertex_index number,
x number,
y number
);

begin
  insert into road_vertices values ('100',1,0,5);
  insert into road_vertices values ('100',2,10,10);
  insert into road_vertices values ('100',3,30,0);
  insert into road_vertices values ('100',4,50,10);
  insert into road_vertices values ('100',5,60,10);
end;
/

Output:

ROAD_ID  LINESTRING
100      LINESTRING M ( 0 5 0, 10 10 11.18, 30 0 33.54, 50 10 55.9, 60 10 65.9 )


MySQL clusters - sync a table I've been developing a software that runs on hardware and now I need to synchronise DB on them, but only partly - several tables using MySQL Clusters(don't matter which style), each of hardware can act as a node, and we have virtual one as NDB_MGM.
For ex, I have two tables in DB: users and rules.
Users should stay unique to each node, but rules should be synced.

My question is: is it even possible?

Yes, this is not difficult: Just create the users table using a different storage engine than NDB.

  It is possible to create tables using other storage engines (such as
  InnoDB or MyISAM) on a MySQL server being used with an NDB Cluster,
  but since these tables do not use NDB, they do not participate in
  clustering; each such table is strictly local to the individual MySQL
  server instance on which it is created.

Source: Appendix A.20 MySQL 5.7 FAQ: NDB Cluster


How to use postgresql variable in where clause # It works!
select * from device where device.user_id in (select user_id from user);

# It doesn't work!
select user_id into test from user;
select * from device where device.user_id in test;

Why does the first one work but second?

If I want to use a variable in where clause like the above, how can I do that?

Actually select user_id from user will result in an error, because user is a reserved keyword (function) that returns the current user - and that does not have a column named user_id. See here: https://rextester.com/EPWT79333

The reason why it works in the sub-select of the first query, is that any column not available in the tables of the sub-query will resolve to a column from the outer query.

So the query:

select *
from device
where device.user_id in (select user_id from user);

Is actually the same as:

select *
from device
where device.user_id = user_id;

If you do have a table named user you have to quote it, to make clear that you mean the table, not the built-in function: "user"

So the correct form for the first query is:

select *
from device
where device.user_id in (select user_id from "user");


Restore one table from SQL Server data file (.mdf) I stupidly deleted most of the contents of a table in my database. I have a backup of the server which has the .mdf on it.

I assume I can make a new db and attach the backup .mdf. Then I can select the records from the backup database and insert them into my live database.

Is there an easier way to do this? Or is that the only way?

Thanks

Yes, the easiest way is to attach the MDF (if you can). Not all abandoned MDF files are attachable, it depends on how they were detached and what state the server was in when that happened. The proper way to take a backup is BACKUP DATABASE.

There isn't a way that I know of to extract only a single table from a detached MDF file, unless you're really, really comfortable with a hex editor.

So I think you are on the right track - attach your MDF as a different database name, and extract the data from the one table you need. Shark provided the syntax you'll need. However, again, a disk image does not necessarily leave an MDF in a usable state, so without a proper backup, all I can do is cross my fingers for you.


Restoring multiple snapshots with SQL Server 2008 I noticed I cannot have multiple snapshots of a SQL Server database and then restore from one of them (without I believe, deleting all but the one I want to restore).

Is there a way to keep multiple snapshots of the same database and be able to restore to any of them?

Or is the only way either buying some 3rd party software which does this, or
 taking full backups of each version of the database?

DB snapshots work on the basis of copy-on-write. Read operations of data that is unchanged since the snapshot was taken are fulfilled by pages from the original database. If the data has been changed, the original version of the data is copied to the snapshot so future queries will use pages in the snapshot. When you have multiple snapshots, there is a potentially intricate collection of pages in each snapshot depending on how much write activity took place and how much of that was on the same data. You could end up with many generations of data where each generation resides in one of the snapshots.

This means all snapshots are individually, tightly-coupled with the original database. If you restore the original database using one of the snapshots, that effectively breaks the coupling for all other snapshots hence it isn't allowed.

Third party solutions from storage vendors keep copies of the data around by retaining and tracking blocks that each snapshot is dependent on. That means you could end up consuming storage space many times the size of the original database depending on how many snapshots you have and restores you make. It gets expensive and very complicated quickly.

If you're using multiple snapshots to get different point-in-time data, suggest you consider temporal tables in SQL Server 2016.


Scheduling queries to run when sql server is not busy Is it possible to schedule some queries not for specific times but to run when sql server is not busy?

Is there any way to calculate the business of the sql server?

An Idle condition is defined at the SQL Agent properties, Advanced

https://msdn.microsoft.com/en-us/library/ms189065.aspx

The select it in the schedule type for the job as Rafael said in his comment


Error on Check Contraint that is on a Column with a Default Constraint Inspired by this article, I wanted to test the idea before I considered implementing it.

However, I get this error

  Msg 547, Level 16, State 0, Line 14 The INSERT statement conflicted
  with the CHECK constraint "CK__DateCreated2". The conflict occurred in
  database "db_name", table "dbo.t2", column 'DateCreated'. The
  statement has been terminated.

OK, i know what the error means but how do I get around it.

Here's my sample data with DDL

DROP TABLE IF EXISTS t2;
CREATE TABLE t2 (
     Id             INT IDENTITY
    ,Col1           NVARCHAR (15)
    ,DateCreated    DATETIME2 CONSTRAINT DEF__DateCreated2 DEFAULT (GETUTCDATE())
);
ALTER TABLE t2 WITH CHECK ADD CONSTRAINT CK__DateCreated2 CHECK ( DateCreated = GETUTCDATE()) ;

This INSERT results in the error

;WITH Numbers (Number) AS
(   SELECT ROW_NUMBER() OVER(ORDER BY N1.N) - 1
    FROM (VALUES (1),(1),(1),(1),(1),(1),(1),(1),(1),(1)) N1(N)
    CROSS JOIN (VALUES (1),(1),(1),(1),(1),(1),(1),(1),(1),(1)) N2 (N)
    CROSS JOIN (VALUES (1),(1),(1),(1),(1),(1),(1),(1),(1),(1)) N3 (N)
    CROSS JOIN (VALUES (1),(1),(1),(1),(1),(1),(1),(1),(1),(1)) N4 (N)
)
INSERT INTO dbo.t2
(Col1,DateCreated)
SELECT 'Col' + CONVERT(NVARCHAR(50), M.Number), GETUTCDATE()
FROM Numbers M

so does this

;WITH Numbers (Number) AS
(   SELECT ROW_NUMBER() OVER(ORDER BY N1.N) - 1
    FROM (VALUES (1),(1),(1),(1),(1),(1),(1),(1),(1),(1)) N1(N)
    CROSS JOIN (VALUES (1),(1),(1),(1),(1),(1),(1),(1),(1),(1)) N2 (N)
    CROSS JOIN (VALUES (1),(1),(1),(1),(1),(1),(1),(1),(1),(1)) N3 (N)
    CROSS JOIN (VALUES (1),(1),(1),(1),(1),(1),(1),(1),(1),(1)) N4 (N)
)
INSERT INTO dbo.t2
(Col1)
SELECT 'Col' + CONVERT(NVARCHAR(50), M.Number) FROM Numbers M

and this

DECLARE @UTCNow DATETIME2 = GETUTCDATE() ;

;WITH Numbers (Number) AS
(   SELECT ROW_NUMBER() OVER(ORDER BY N1.N) - 1
    FROM (VALUES (1),(1),(1),(1),(1),(1),(1),(1),(1),(1)) N1(N)
    CROSS JOIN (VALUES (1),(1),(1),(1),(1),(1),(1),(1),(1),(1)) N2 (N)
    CROSS JOIN (VALUES (1),(1),(1),(1),(1),(1),(1),(1),(1),(1)) N3 (N)
    CROSS JOIN (VALUES (1),(1),(1),(1),(1),(1),(1),(1),(1),(1)) N4 (N)
)
INSERT INTO dbo.t2
(Col1,DateCreated)
SELECT 'Col' + CONVERT(NVARCHAR(50), M.Number), @UTCNow
FROM Numbers M

To be honest, I think you are in for a rough ride here. I see several issues with how this will pan out.

The SELECT (the subselect for the INSERT) will resolve GETUTCDATE to something which later will be used in the INSERT part. The CHECK constraint is evaluated row by row, and since the value from GETUTCDATE will be "old", then you get the error.
I can't say why this fails for sure, but I'm speculating that the default value it captured momentarily a very short time before the check constraint is evaluated. Again, not the same value and hence the error.
Here it is pretty obvious that you first capture the date into a variable, which at time of INSERT will be old.

I changed the data type to datetime2(0) and the check constraint to  CHECK ( DateCreated = CAST(GETUTCDATE() AS datetime2(0)) and I didn't get any errors. But who knows if you happen to pass a one-second boundary and it then will fail. Also, that resolution might not cut it for you.

Another issue that I see that that a subsequent UPDATE of a row might re-trigger that check constraint to be evaluated (even if you don't modify that column) and that UPDATE will fail even though the data is "correct".

I would investigate either protect from UPDATE using permissions or having a trigger.


Load index to cache is not using memory available in the key_buffer_size variable Good afternoon folks,
I have a problem when trying to load the indexes from the MyISAM tables into MySQL memory, even though there is space available in the key_buffer_size variable, it stops loading even though there are still indexes to be loaded.
Has anyone had this problem or know what I can do to solve it?
The server that is running the database is a windows server 2012 (¯_(ツ)_/¯ - and don't want to switch to linux server), MySQL is version 5.5.62 and in this instance I have 1210 databases with the structure of the tables in MyISAM.
Below is the configuration file (my.ini):

  [mysqld]
   port=3306
   basedir="C:/Program Files/MySQL/MySQL Server 5.5/"
   datadir="D:/MySQL/Data/"
   character-set-server=latin1
   default-storage-engine=myisam
   sql-mode="STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"
   max_connections=4500
   query_cache_size=48M
   query_cache_type=1
   tmp_table_size = 1G
   max_heap_table_size = 256M
   thread_cache_size=1300

   key_buffer_size=62G
   read_buffer_size = 200M
   read_rnd_buffer_size = 100M
   sort_buffer_size = 1G
   myisam_sort_buffer_size = 4096M

  skip-innodb
  max_allowed_packet=16M
  interactive_timeout=300
  wait_timeout=1700
  max_sort_length=4096
  max_connect_errors=1000000
  table_open_cache=13999
  table_definition_cache=4096
  log-warnings
  long_query_time=10
  open_files_limit=65535
  slow-query-log
  slow_query_log_file = "D:/MySQL/Data/slowlog.txt"
  log_output = TABLE
  connect_timeout=30
  net_read_timeout=65
  net_write_timeout=65
  tmpdir="D:/MySQL/Temp/"

Since there is still a lot of memory available for the cache and most of the scripts are data queries

You have a pretty strange buffer configuration:
max_connections=4500
. . . . .
key_buffer_size=62G
read_buffer_size = 200M
read_rnd_buffer_size = 100M
sort_buffer_size = 1G
join_buffer_size = ???

While key_buffer_size is a global value for all MyISAM indexes across all the databases, (read|read_rnd|sort|join)_buffer_size are per-connection values. That amounts are reserved for each client attached to the mysqld service. The overall RAM consumption is calculated as:
(read_bs + read_rnd_bs + sort_bs + join_bs) * max_connection + key_buffer_size

In your case it seems to be at least 6TB in total that is too optimistic.
First you have to lower your buffer sizes to the reasonable values - 2-8MB each. Second you have to install mysqltuner.pl - a neat tool that analyze both config and status variables and propose a number of reasonable advices.
Third you have to perform a system monitoring for slow queries and on-disk temporary tables and tune your mysql accordingly to your certain needs.


Where does kerberos tool need running and settings configured? I am reading this article on how to link 2 sql server instances using kerberos: https://www.sqlshack.com/how-to-link-two-sql-server-instances-with-kerberos/
I am not able to understand which computer the settings need to be made on. Is it on both instances?

You want to have SPNs in place for both servers. And you'll need the delegation settings in place for the first server (Server A in the linked article). Specifically, for the service account that runs the SQL Engine service on Server A.
Your title asks about where the tool needs to be run from. The Kerberos Configuration Manager can be run from anywhere, but the changes it will help you make are to Active Directory (AD) objects, so you'll need to run it under an account that has enough rights to make the AD changes. (Typically, you'd use a domain administrator account for this.)


Replication testing is a Materialized view Refresh equivalent of continious update? In my database I have the following a materialized view named materialized_view_test and the data are being feeded from 3 tables:

select
 *
from
 a_table join b_table on b_table.a_id=a_table.id
 join c_table on b_table.c_id=c_table.id

As you can see the b_table is a pivot table for a_table and c_table. Also my database for testing purpoces has a master-slave replication with the slave to be on a hot stanby replication mode.

I try to stress my database in order to investigate which configuration on the following parameters is the optimal:

max_standby_streaming_delay
max_standby_streaming_delay
hot_standby_feedback

So I continiously run the following queries on my database over an infinite loop (for convenience lets name the test as test1):

Set 1:

DELETE FROM b_table where a_id=12 and c_id=33
REFRESH MATERIALIZED VIEW materialized_view_test;
VACUUM (VERBOSE) materialized_view_test

Set 2:

INSERT INTO b_table(a_id,b_id) VALUES (12,33);
REFRESH MATERIALIZED VIEW materialized_view_test;
VACUUM (VERBOSE) materialized_view_test

And I toggle between 2 sets into my database by checking a counter whether is odd or even, using a custom script. Afterwads, I read from the materialized view in a purpocely non-optimal written query in order to cause a load:

select * FROM (
  SELECT * FROM (
   SELECT *, pg_sleep(5) FROM b_table join b_table as b_table12 on btable.b_id=b_table12.b_id
   ) as dummy
) as dummy2

But in my database I rearely do any deletes. So I though if I was continiously toggling a boolean flag over an infinite loop in one of my tables (for convenience lets name the test as test2):

UPDATE a_table set some_flag = NOT some_flag where a_id=12;
VACUUM (VERBOSE) materialized_view_test;

And then continiously sellecting over it in a infinite loop:

select * FROM (
  SELECT * FROM (
   SELECT *, pg_sleep(5) FROM a_table join a_table as b_table12 on btable.a_id=b_table12.a_id
   ) as dummy
) as dummy2

So does in my situation the test1 can have same results and effects as running into test2 or I need both tests in order to test my replication schema.

For most practical purposes, UPDATE is pretty much the same as DELETE + INSERT in PostgreSQL. The difference between your tests is that test1 refreshes the materialized view twice and runs one more VACUUM.

Refreshing a materialized view is something entirely different. It will effectively drop the materialized view and re-create it from scratch. If you use REFRESH MATERIALIZED VIEW CONCURRENTLY, you will see activity similar to INSERT and DELETE.

max_standby_archive_delay does not apply to streaming replication at all.
max_standby_streaming_delay governs if there will be a delay in applying data changes on the standby server in the case of replication conflicts.

Both your tests will eventually generate replication conflicts as soon as autovacuum removes old row versions from a_table or b_table, but only if hot_standby_feedback = off. If hot_standby_feedback = on, you will not see a replication conflict until a random autovacuum run truncates empty pages off the end of one of the tables, which requires a short ACCESS EXCLUSIVE lock.

You could reliably trigger a replication conflict by selecting from the materialized view on the standby while it is being refreshed on the primary, because that takes an ACCESS EXCLUSIVE lock on the materialized view.
hot_standby_feedback: If set to on, then if either of your tests runs long enough while the query on the standby is running, you will slowly see the tables grow and get bloated, because old row versions won't get removed on the primary.

If set to off you will get replication conflicts as soon as one of the tables is vacuumed.


Data Migration or Sharded Cluster to new Cluster We have Sharded Mongo Cluster. However some documents are sharded and others are unsharded.

We are planning to migrate these databases to new cluster. Please update if there is any process or steps to migrate the data from one sharded cluster to the other.

We have two secondaries and 1 primary for each shard,MongoS,3 Config Servers,2 Arbitors. I understood that copydb can not be used on the sharded collection. Please suggest if there is any other way.

Migrating an existing sharded cluster to new hardware

If you want to migrate a sharded cluster to new hardware, there is a tutorial in the MongoDB manual: Migrate a Sharded Cluster to Different Hardware

Migrating data in an existing sharded cluster to a new cluster

There is no officially supported process to migrate documents between two different MongoDB clusters (at least as at MongoDB 2.6), but you do have a few options:

A typical approach is via backup & restore of the sharded cluster, but that involves some down time or a custom script to sync up changes that occurred while you were loading data into the new cluster.
The team at Mailbox has open sourced a set of Python scripts called Hydra which specifically aim to enable live migration of sharded  collections. For more background details see: Scaling MongoDB at Mailbox.

I haven't personally tried Hydra (and there is currently a caveat in the Github repo that the authors only tested with MongoDB 2.2.3 on Ubuntu 12.04), however it seems like a promising approach.


How to find duplicate digits in the middle of similar numbers? In my table, a column has the following values:

1234567890
2086567845
    ^^^^ duplicate digits

So the above values have 4 digits in the middle which are duplicates.

How could I find these automatically?

Here is one method for doing that.  It assumes that the strings are all the same length and you really want to find all the cases where the middle string is duplicated.

Finds all duplicate "middle", update the CTE_Split to use your actual table and column names as appropriate.

;WITH CTE_Split AS
    (
    SELECT ID
        , LEFT(ValueString, 4) AS PreFix
        , SUBSTRING(ValueString, 5, 4) AS Middle
        , RIGHT(ValueString, 2) AS Ending
    FROM (VALUES (1, '1234567890')
                , (2, '2086567845')
                ) AS P (ID, ValueString)
    )
, CTE_Duplicate AS
    (
    SELECT ID
        , Prefix
        , Middle
        , Ending
        , RN = ROW_NUMBER() OVER (PARTITION BY Middle ORDER BY ID)
    FROM CTE_Split
    )
SELECT ID, Prefix, Middle, Ending, RN
FROM CTE_Duplicate
WHERE Middle IN (   SELECT Middle
                    FROM CTE_Duplicate
                    WHERE RN >= 2
                    )


I don't want to have the error when I drop a table although the table doesn't exist I am using Oracle 12c and I am not interesting to have an error while droping my table 'CONTINENT' in case it doesn't exist.

I did this

set echo on

set serveroutput on
alter session set current_schema=WORK_ODI;
set verify off
set pause off
--
WHENEVER SQLERROR CONTINUE

drop table  CONTINENT;

COMMIT;

EXIT;

but it coudn't skipe the error when the table doesn't exist, how can I fix this problem please

Unfortunately Oracle does not have anything like for example IF EXISTS in MySQL. You can do the above in a PL/SQL block, catch the exception, then do nothing with it.

begin
  execute immediate 'drop table continent';
  exception when others then null;
end;
/


Slow query when adding additional where clause Here's my setup (Postgres 9.3)

Posts

project_id

Messages

post_id
kind
updated_at

I'm trying to get the most recently updated 100 messages that belong to posts (on a specific project) that are a specific kind. My query looks like:

SELECT "messages".* FROM "messages"
INNER JOIN "posts" ON "posts"."id" = "messages"."post_id"
WHERE "posts"."project_id" = '418fdd03-ab90-4efd-b04d-5d5563d58972' AND "messages"."kind" IN (10, 11, 12)
ORDER BY updated_at DESC LIMIT 100 OFFSET 0;

Limit  (cost=0.17..2372.06 rows=100 width=648) (actual time=17.731..308.603 rows=100 loops=1)
 ->  Nested Loop  (cost=0.17..44164.77 rows=1862 width=648) (actual time=17.730..308.559 rows=100 loops=1)
       ->  Index Scan Backward using index_messages_on_updated_at on messages  (cost=0.08..20924.10 rows=151337 width=648) (actual time=0.034..93.817 rows=83481 loops=1)
             Filter: (kind = ANY ('{10,11,12}'::integer[]))
             Rows Removed by Filter: 130238
       ->  Index Scan using posts_pkey on posts  (cost=0.08..0.15 rows=1 width=16) (actual time=0.002..0.002 rows=0 loops=83481)
             Index Cond: (id = messages.post_id)
             Filter: (project_id = '418fdd03-ab90-4efd-b04d-5d5563d58972'::uuid)
             Rows Removed by Filter: 1
Total runtime: 308.660 ms

If I run without the kind clause:

SELECT "messages".* FROM "messages"
INNER JOIN "posts" ON "posts"."id" = "messages"."post_id"
WHERE "posts"."project_id" = '418fdd03-ab90-4efd-b04d-5d5563d58972'
ORDER BY updated_at DESC LIMIT 100 OFFSET 0;

Limit  (cost=0.17..1320.02 rows=100 width=648) (actual time=0.501..23.613 rows=100 loops=1)
 ->  Nested Loop  (cost=0.17..69371.34 rows=5256 width=648) (actual time=0.500..23.583 rows=100 loops=1)
       ->  Index Scan Backward using index_messages_on_updated_at on messages  (cost=0.08..20603.62 rows=427305 width=648) (actual time=0.010..3.256 rows=7893 loops=1)
       ->  Index Scan using posts_pkey on posts  (cost=0.08..0.11 rows=1 width=16) (actual time=0.002..0.002 rows=0 loops=7893)
             Index Cond: (id = messages.post_id)
             Filter: (project_id = '418fdd03-ab90-4efd-b04d-5d5563d58972'::uuid)
             Rows Removed by Filter: 1
Total runtime: 23.667 ms

I think the main slowdown is that the majority of the rows are not of the kind I'm looking for. I tried adding a partial index on kind, but that didn't have any effect.

CREATE INDEX index_messages_on_kind ON messages USING btree (kind) WHERE (kind = ANY (ARRAY[10, 11, 12]));

Any ideas to speed this query up?

Edit:

Indexes that currently exist:

Posts

index on project_id and updated_at

Messages

indexes on post_id, updated_at and partial index on kind IN (10, 11, 12)

@Joishi already provided an explanation for what you saw.

Here is a solution to make it fast.

Your query (unchanged) after trimming some noise:

SELECT m.*
FROM   posts    p
JOIN   messages m ON m.post_id = p.id
WHERE  p.project_id = '418fdd03-ab90-4efd-b04d-5d5563d58972'
AND    m.kind = ANY ('{10,11,12}')
ORDER  BY m.updated_at DESC
LIMIT  100
OFFSET 0;

1. Multicolumn index on posts

This is only a minor improvement and not strictly necessary. Should give you an Index Only Scan instead of an Index Scan. Saves the lookup on the heap (the table)

 CREATE UNIQUE INDEX index_posts ON posts (project_id, id);

UNIQUE is also not necessary, but it better documents that the index is, in fact, unique.

2. Partial multicolumn index on messages done right

This is the major part.

Use the same predicate in the partial index and in the query. Postgres only applies very rudimentary tests to determine whether an partial index is applicable. Else, the cost would get out of hand quickly.
Since kind IN (10, 11, 12) is rewritten to kind = ANY ('{10,11,12}'::integer[]) internally, it should still work in this case, but it's better to be clear.
It would be useless to make kind the indexed column here. It's only relevant in the predicate, not as column to retrieve from the index. You need an equality check on post_id (so it comes first) and a range check on  updated_at (so it comes after that). Details:

Multicolumn index and performance

DESC is not strictly necessary, but still better here.

-- DROP INDEX index_messages_on_kind;
CREATE INDEX index_messages_on_kind ON messages (post_id, updated_at DESC)
WHERE kind = ANY ('{10,11,12}');

Then run ANALYZE on both tables and try EXPLAIN ANALYZE again. Are we fast, yet?


What type of locks does BEGIN TRANSACTION cause against a specific table that's being read from? In the following example, what kinds of locks are generated from the explicit transaction block that wraps the SELECT INTO query?

BEGIN TRANSACTION T1

SELECT Field1, Field2, Field3
INTO TableB
FROM TableA
WHERE Field3 > Value1

COMMIT TRANSACTION T1

Notes:

1) TableA has about 100 billion rows, but the filter causes the query to return only about 5 million rows.

2) Field3 is a clustered index on TableA, and the generated query plan is doing a clustered index seek.

3) The isolation level in my scenario is the default (READ COMMITTED).

Do explicit transaction blocks like this potentially cause more locking (or a higher chance of lock escalation) against the table then if the query wasn't wrapped in an explicit transaction?

5 million rows is a lot, theoretically it would cause shared lock escalation for TableA to table-level lock in any case (explicit or implicit transaction)

But you can find this out experimentally, you just need to setup Extended Events session and monitor for "lock_escalation" event

Then run your query in explicit and implicit transaction and check out XE session logs to see the lock_escalation events


Inserting rows into other table whilst preserving IDENTITY I'm performing ETL logic on a SQL Server table.  I'm going to be synchronizing data from one table to another.  For all of the records that need to be added to the target table based on the source table, I'm doing an insert on those rows in the target table.  The schema defines one of the columns as an identity column.  So SQL Server auto increments the id for new rows that are inserted.  Because I'm moving existing ids, I need to remove the identity, insert those rows, then re-apply the identity, then reset the seed so it matches the source table.  How can this be done programmatically?

Can I remove the identity declaration from an existing column?
Can I mark an existing column as an identity?
How can I reset the seed for an identity column?

While it doesn't automatically prevent duplicates, you can disable the identity temporarily using the following, and then you would likely just want to set the identity seed to the highest value in the table:

 SET IDENTITY_INSERT dbo.tablename ON;

 INSERT ...

 SET IDENTITY_INSERT dbo.tablename OFF;

 DECLARE @sql NVARCHAR(MAX);

 SELECT @sql = N'DBCC CHECKIDENT(''dbo.tablename'', RESEED, '
   + RTRIM(MAX(id_column_name)) + ');' FROM dbo.tablename;

 EXEC sp_executesql @sql;

I'm not sure what your best course of action would be to correct duplicates. If you insert 1000 new rows after reseeding, it is likely that the source system will generate new identity values that will conflict. What you might consider doing is simply setting one of the identity values to generate numbers well above the range that the other table won't ever get to (say 1 billion). You can still use IDENTITY_INSERT to merge, but there will never be a conflict. This also makes it very easy to determine whether a row was generated locally or imported.


Enable cache on AWS RDS for MySQL I just upgraded my server to AWS, and I am using RDS for database. But here, it looks like the same query is taking such a long time as compared to my old server.

I was researching and found that there is RDS cache issue. It every time calls opening table and follow all the steps and in my server it was using cache so it was not opening tables every time. I am using MySQL on RDS.

I have hundreds of thousands of records in one one table. Can someone please help me to get good performance on RDS?

You can tune performance using Parameter Groups. Some (unfortunately, not all) variables can be configured there.

Also, if you used AWS Database Migration make sure you have all the keys you need because they're not transferred by default.


If the SIDs for ##certificate-based## SQL Server Logins don't match across replicas in an Availability Group, will this be a problem on failover? I am reading mixed messages about server level object syncing between replicas in an Availability Group.

As I understand it, server-level objects must be kept in sync (aside: then why isn't it part of AG by default?). For example, if a Login (identified by SID) does not exist on a Secondary replica (regardless of username and password) and the system fails over to that replica, then the corresponding principal will fail to authenticate.

All well and good - keeping these objects in sync is a task to be done. But what about certificate-based Logins? I read these are for internal system use only, so I would expect the DB engine to maintain SIDs across replicas if that's required. I have a case with three nodes; the third being read-only. I see the Primary's SIDs for logins such as ##MS_SQLAuthenticatorCertificate## do not exist on either of the other nodes. However on those other nodes, there exists a login with the same name and a different SID, but that SID is the same between those two nodes, viz:

SQL01 (Primary) SID = ABC
SQL02 (Secondary) SID = DEF
SQL03 (Read-only) SID = DEF

Is this of concern? Will a failover work despite these Logins having mismatching SIDs between some replicas?

Is this of concern? Will a fail-over work despite these Logins having mismatching SIDs between some replicas?

I don't think this is a concern with fail-over due to following facts:

The fail-over basically targets user databases based on database mirroring endpoint
CONNECT permissions on the ENDPOINT required to user defined LOGIN or SERVICE ACCOUNT (not for server principals named like ##%).

As you mentioned, fundamentally, the SID is the correlation between Login and User in a database level. Since the server level metadata sync is out of scope in availability groups, we must maintain Server Level login's SID same in all the replicas (SQL Instances), so that it would match with User SID in secondary copy of the database.

When fail-over happens there is no failure of application connectivity as the login SID = user SID, eventually application gets the same permissions on the database. Otherwise users in database become Orphaned

Following are some points that indicates why we do not have to concern on server principals that are named '##%':

These are created during SQL Server installation, we do not have control over these to manage SID
These are only internal purpose, meaning only exists in system databases, doesn't have any relation with user database principals (sys.database_principals)
There is no issue with user DB connection, since any application usually doesn't have any relation with the principals that are named like '##%', and the principals (##%) not existed in user database.
Finally, our aim is to match Server principal's SID = database principal's SID within each replica. For the principals that are named like ##% are by default and always maintains same SID within replica which you can verify with following query:

select name, sid from sys.server_principals where name like '##%'

select name, sid from master.sys.database_principals where name like '##%'
select name, sid from msdb.sys.database_principals where name like '##%'

  As I understand it, server-level objects must be kept in sync (aside: then why isn't it part of AG by default?).

You're right, it's limitation of availability group which clearly stated here.., in this case contained database feature would be a solution to persists the authentication with database itself.

Not only logins, but also there are server level metadata aspects that need to be manged outside the availability group as required.


Question regarding PSQLException: User was holding shared buffer pin for too long I recently encountered a strange error that I don't quite understand what could cause it:

caused by org.postgresql.util.PSQLException: ERROR: canceling statement due to conflict with recovery Detail: User was holding shared buffer pin for too long.

I have a fixed timeout 10s set for each query, so any long run query should get terminated and the error message I expected to see User query get cancelled by user request. However, I see this sometimes, but the above as well which is really confusing. Furthermore, it only show up when I see high lag from our RDS replica, then resolve itself after 5, 10 minutes.

I tried to reproduce this issue locally but no luck. Any thought would be greatly appreciated!

If the replay process is already behind by more than max_standby_streaming_delay (or max_standby_archive_delay), then it will immediately terminate anything which is in its way.  If it is behind by less, then it is willing to wait only until it reaches that delay.  In other words, the max_standby_streaming_delay sets a grace period for the entire system, not for each individual action within the system.

So if max_standby_streaming_delay is set to 30 seconds, but the replay process is already 28 seconds behind, then "too long" to hold a buffer pin could be 2 seconds.  If is already more than 30 seconds behind, then a few nanoseconds could be "too long"--just long enough to detect the blockage and then send, receive, and process a signal.


How to store likes and comments of posts in database? I am building a social networking website, so I wanted to know how the database structure for storing likes and comments of a post.
I have created a table post for storing posts but I want to know how to store "likes" and "comments" so that I can show who are the people who liked your post and what are the comments of the post.
Do I need to create a table for likes which stores user id of every user who liked the post and post_id as foreign key, but this may be very large table as it will create a new row for every user who liked the post. For example if one post has 1000 likes then it will create 1000 rows for the same post in "like" table. The same thing happens with comments as well.
Please suggest me the table structure for post, like and comments. Thanks!

Doing it the way you suggested is the right way to keep track of likes. A separate table with user_id and post_id both of which are FK and these 2 FK combined can serve as PK

2 columns both of which are integers(or bigint) is not a problem to any RDMBS.

In fact, 2 integer columns should be the least of your concerns. Because integers are one of the most performant datatypes, and occupy very little space if compared to, for example, nvarchar.

I think this SO post might help you as well https://stackoverflow.com/questions/11808680/database-design-table-of-likes


SQL Server not using available memory SQL Server 2008 R2 Standard Edition (64-bit) on Windows 2008R2 Enterprise

Server has over 300 GB of memory but total memory use in control panel never goes over 86 GB
Have SQL configured to use the maximum amount of memory
Even under heavy use - CPU over 80% for minutes
Dedicated SQL Server
Several large databases
One heavily used table has an index size alone of over 10 GB

Set the service account to hold locks in memory

Is that normal?

What can I test?

Can I get SQL Server to use more memory?

Standard Edition is limited to a 64 GB buffer pool.

To use all the available memory of the server, you'll need to upgrade to either Enterprise or Datacenter Edition, or scale out to multiple instances of Standard Edition.


MS SQL rolling grouped sum for every new data entry I am using MS SQL 2016, and I have a table which looks something like that (simplified)

 Object      Date         Amount  Owner  TypeOwner
  O1        20180101        10      A     X
  O2        20180101        50      B     Y
  O1        20180101        30      C     X
  O1        20180203        25      B     Y
  O2        20180203        20      B     Y
  O1        20180206        15      A     X
  O2        20180206        20      A     X

Simply put, owners declare how much of an object they have, at a date they choose. What I am looking for is reconstruct the history of ownership of the objects with respect to a column like TypeOwner, i.e. each time a new data point is available for a owner, recompute the current ownership. So the output table should look something like

Object      Date         Total  TypeOwner
  O1        20180101        40      X
  O2        20180101        50      Y

  O1        20180203        25      Y
  O2        20180203        20      Y
  O1        20180103        40      X

  O1        20180206        45      X
  O2        20180206        20      X
  O1        20180206        25      Y
  O2        20180206        20      Y

Basically each time there is a new entry in the table, it should look at all the most recent entries for any other owners and recompute a grouped sum per Object, TypeOwner.

How I obtain the result table:

As of 2018-01-01, we know that A owns 10 O1, B owns 50 O2 and C owns 30 O1. So group X (A and C) owns 40 O1 and group Y (B) owns 50 O2. That's the first two lines of the results.
On 2018-02-03, we have new data for B. This overrides the past entry for that owner. So as of this date A still owns 10 O1, C still owns 30 O1 and now B owns 25 O1 and 20 O2. So group X (A and C) owns 40 O1, group Y (B) owns 25 O1 and 20 O2.
On 2018-02-06, we have new data for A. This overrides the past entry for that owner. As of this date, A now owns 15 O1 and 20 O2, C still owns 30 O1 (2018-01-01), and B still owns 25 O1 and 20 O2 (2018-02-03). Once again group X (A and C) owns 45 O1 and 20 O2, group Y (B) owns 25 O1 and 20 O2

I tried using various techniques like inner join/cross apply to select last records up to some date, but I am not sure what would be te most efficient solution here. The real table is on thousands of objects and owners, hence any speed gain would be great.

Thank you

This will get you the desired result set.
It works by 1) creating the missing [object, date, owner] row combinations and then 2) doing a "data smear" to carry the most recent non-NULL amount forward. And 3) filter out any rows that did not have a previous amount to pull from.

IF OBJECT_ID('tempdb..#TestData', 'U') IS NOT NULL
DROP TABLE #TestData;

CREATE TABLE #TestData (
    Object CHAR(2) NOT NULL,
    [Date] DATE NOT NULL,
    Amount INT NOT NULL,
    Owner CHAR(1) NOT NULL,
    TypeOwner CHAR(1) NOT NULL
    );
INSERT #TestData (Object, Date, Amount, owner, TypeOwner) VALUES
  ('01', '20180101', 10, 'A', 'X'),
  ('02', '20180101', 50, 'B', 'Y'),
  ('01', '20180101', 30, 'C', 'X'),
  ('01', '20180203', 25, 'B', 'Y'),
  ('02', '20180203', 20, 'B', 'Y'),
  ('01', '20180206', 15, 'A', 'X'),
  ('02', '20180206', 20, 'A', 'X');

--SELECT * FROM #TestData;

WITH
    cte_cross_values AS (   --
        SELECT
            o.object,
            d.date,
            ot.owner,
            ot.typeowner
        FROM (
            VALUES ('01'),('02') ) o (object)
            CROSS APPLY ( VALUES ('2018-01-01'),('2018-02-03'),('2018-02-06') ) d (date)
            CROSS APPLY ( VALUES ('A','X'),('c','X'),('B','Y') ) ot (owner, typeowner)
        ),
    cte_rolling_amtount AS (
        SELECT
            cv.object,
            cv.date,
            RollingAmount = CONVERT(INT, SUBSTRING(MAX(bv.bin_val) OVER (PARTITION BY cv.owner, cv.typeowner, cv.object ORDER BY cv.date), 4, 4)),
            cv.owner,
            cv.typeowner
        FROM
            cte_cross_values cv
            LEFT JOIN #TestData td
                ON cv.date = td.Date
                AND cv.object = td.Object
                AND cv.owner = td.Owner
                AND cv.typeowner = td.TypeOwner
            CROSS APPLY ( VALUES (CONVERT(BINARY(3), td.Date) + CONVERT(BINARY(4), td.Amount)) ) bv (bin_val)
        )

SELECT
    ra.object,
    ra.date,
    amount = SUM(ra.RollingAmount),
    ra.typeowner
FROM
    cte_rolling_amtount ra
WHERE
    ra.RollingAmount IS NOT NULL
GROUP BY
    ra.object,
    ra.date,
    ra.typeowner
ORDER BY
    ra.date,
    ra.typeowner,
    ra.object;

Results:

object date             amount      typeowner
------ ----------       ----------- ---------
01     2018-01-01       40          X
02     2018-01-01       50          Y
01     2018-02-03       40          X
01     2018-02-03       25          Y
02     2018-02-03       20          Y
01     2018-02-06       45          X
02     2018-02-06       20          X
01     2018-02-06       25          Y
02     2018-02-06       20          Y


Update many rows in a table with a single statement? What is the easiest way to update many rows in a table? I have a csv file that looks like this:

|primary_key |value|
|          1 |  xyz|
|          2 |  abc|
|          3 |  def|
...

Rows with these primary keys already exist in the target table

I would like to update the target table with these values. Is there syntax so that I can write something like:

update mytable set value = ('xyz', 'abc', 'def') where primary key = (1,2,3);

Looking through the MySQL Update Reference, this site (MySQL - csv update), SO (update multiple rows, multiple db updates, update multiple rows), I suspect that the answer is "no", but I'd like to confirm that this is true.

First here is sample data

mysql> drop table if exists mytable;
Query OK, 0 rows affected (0.03 sec)

mysql> create table mytable
    -> (
    ->     id int not null,
    ->     value VARCHAR(255),
    ->     primary key (id)
    -> );
Query OK, 0 rows affected (0.06 sec)

mysql> insert into mytable (id) values (1),(2),(3);
Query OK, 3 rows affected (0.06 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> select * from mytable;
+----+-------+
| id | value |
+----+-------+
|  1 | NULL  |
|  2 | NULL  |
|  3 | NULL  |
+----+-------+
3 rows in set (0.00 sec)

mysql>

Here is the new query

update mytable A inner join
(
    SELECT 1 id,'xyz' value UNION
    SELECT 2   ,'abc'       UNION
    SELECT 3   ,'def'

) B USING (id)
SET A.value = B.value;

Here is the new query executed

mysql> update mytable A inner join
    -> (
    ->     SELECT 1 id,'xyz' value UNION
    ->     SELECT 2   ,'abc'       UNION
    ->     SELECT 3   ,'def'
    -> ) B USING (id)
    -> SET A.value = B.value;
Query OK, 0 rows affected (0.06 sec)
Rows matched: 3  Changed: 0  Warnings: 0

mysql> select * from mytable;
+----+-------+
| id | value |
+----+-------+
|  1 | xyz   |
|  2 | abc   |
|  3 | def   |
+----+-------+
3 rows in set (0.00 sec)

mysql>


Getting error on a particular query on a table in Postgres I am getting the following error on one query in Postgres:

select * from sampletable where sample_id = 1234;

Error is :

ERROR: could not access status of transaction <some-id>
Detail: CLOG segment 42 does not exist: No such file or directory.

But when I am trying to run other queries on the same table, it works fine.
Eg.

select * from sampletable where sample_id = 4321;

So, its happening for a particular value of a sample_id only.

What could be the cause of it and how can I fix it? Any suggestions.

CLOG is the PostgreSQL Commit Log Manager (CLog). It's telling you here that the record of transactions is fuxed. This may have been caused from any kind of corruption. I would back up the database before attempting anything. There is one suggestion the lists,

  There is a commit log corruption in your database. You need to make the
  files with right size (256K of zeroes). A suitable dd from /dev/zero will
  accomplish this on modern Unixen (ie, anything that has /dev/zero). Filling
  of those files with zeroes nothing but  "all those old transactions are
  aborted".

  The safest way to resolve the issue by using the below command:-

dd if=/dev/zero of=Transaction_missing_file_of pg_clog bs=256K count=1

  (To make the uncommitted record as they haven't been committed).

This is followed up with the amusing reply,

  Just for the record, playing with pg_clog files like this is an
  insanely unsafe thing to do. Notably it will make all aborted
  transactions in that range become committed, leading to all sorts of
  fun. This is a desperate measures only action and not one that looks
  immediately related to the problem.

I would try backing up, upgrading to PostgreSQL 11, running VACUUM FULL, and restarting before I messed around with this in 9.6. But I would give it a shot if that failed.


Foreign key part of composite primary key of new table I have tried searching loads but I have not received a concrete answer to my problem.
Suppose I have Table 'A' with primary key as Apk.

This Apk is a foreign key in Table 'B'. In Table 'B' I have also another column Bcol.

Now for 'B' can I have a composite primary key of 'Bcol' and 'Apk': PRIMARY KEY(Bcol, Apk)? Is it advisable?

so basically you have something like this (except I renamed your FK table to C since it was already pulling B from somewhere else):

CREATE TABLE IF NOT EXISTS `table_a` (
  `apk` int(10) NOT NULL AUTO_INCREMENT,
  `name` varchar(20) NOT NULL,
  `time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`apk`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;

CREATE TABLE IF NOT EXISTS `table_b` (
  `bcol` varchar(3) NOT NULL,
  `Location` varchar(20) NOT NULL,
  `Time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`bcol`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE IF NOT EXISTS `fk_table_c` (
  `apk` int(10) NOT NULL,
  `bcol` varchar(3) NOT NULL,
  `SomeOtherValue` varchar(20) NOT NULL,
  PRIMARY KEY (`apk`,`bcol`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

ALTER TABLE `fk_table_c`
  ADD CONSTRAINT `fk_table_c_ibfk_2` FOREIGN KEY (`bcol`) REFERENCES `table_b` (`bcol`) ON DELETE CASCADE ON UPDATE CASCADE,
  ADD CONSTRAINT `fk_table_c_ibfk_1` FOREIGN KEY (`apk`) REFERENCES `table_a` (`apk`) ON DELETE CASCADE ON UPDATE CASCADE;

this is completely normal and how you would form many to many relationships between 2 entities. You may wish to have some other auto generated unique identifier as the primary key but its a good idea to have the 2 FKs as a combined unique key to prevent duplicating rows of the same combinations


How to break out of error loop in transactional replication? I was running some simple tests on a publisher and subscriber on the same instance:

I inserted 50,000 rows into a published article and the data was
correctly pushed to the subscriber.
I inadvertently deleted the 49,985th record on the subscriber (but
didn't realize it at the time)
I deleted 50,000 rows from the published article

As I was watching the size of the subscriber table, I noticed it would drop down in row count almost to zero and then go back to 50,000. Deletes would start running again, the row count would drop, and then it back to 50,000. This kept happening over and over.

I ran a standard trace and saw all the deletes running normally. After it was almost complete, this statement appeared:

IF @@TRANCOUNT > 0 ROLLBACK

Then I modified the trace to incude error messages and saw this:

The row was not found at the Subscriber when applying the replicated command.

So it looks like when the distribution agent is executing all the deletes one-by-one, the process is still included in a transaction (the original DELETE was one line). If there is an error, it performs a ROLLBACK and then starts over.

My questions:

Is there a standard way to break out of the loop?
Would it be acceptable to recreate the missing row so the process
can complete?
What do you do if this happens on production? I guess that's not
really a question. But this seems like a simple error could have
some pretty serious complications unless replication is monitored
very carefully.

The error is caused by the code in the custom delete stored procedure for each article:

create procedure [dbo].[sp_MSdel_dboChild]
        @pkc1 int
as
begin
    delete [dbo].[Child]
where [ChildID] = @pkc1
if @@rowcount = 0
    if @@microsoftversion>0x07320000
        exec sp_MSreplraiserror 20598
end

If no row is deleted, a special procedure is called and the transaction is rolled back.

In order to avoid this, I've created a post-Snapshot script that alters the all delete / update replication stored procedures and logs the error to a table instead.

CREATE procedure [dbo].[sp_MSdel_dboChild]
        @pkc1 int
as
begin
    delete [dbo].[Child]
where [ChildID] = @pkc1
if @@rowcount = 0
    if @@microsoftversion>0x07320000
        insert into repl_errors (database_name, table_name, pkey_col, pkey_val)
        select dbo.fn_GetDB(APP_NAME()), 'Child', 'ChildID', @pkc1
end


How can Cumulative Updates to SQL Server 2019 be Slipstreamed into a Single Installer? I'd like to take the latest version of SQL Server (SQL Standard, 15.0.2000.5), and apply the latest CU to it so that I have a single installer, thus reducing the size of the resulting package. The custom installer that I'm developing packages the SQL Server installer, and thus reducing the size by several hundred MB is important.

In previous versions of SQL Server, it was possible to create a single merged media output folder using the /PCUSOURCE and /CUSOURCE parameters, however this seems to have been removed in SQL Server 2014 (See "Slipstream Functionality" here). The alternative provided is to use the /UpdateSource flag, however this approach only allows for the simultaneous application of patches at the time of installation (by bundling the CU media with the original installation media). Further, the /ACTION=PrepareImage facilitates a SysPrep installation, not a slipstreamed update to the install media.

So, is there a way to update the installation media of SQL Server Standard 2019 with CU patches such that there's a single (reduced-size) installation media bundle?

You can't do this, unfortunately.
As I wrote in my blog post here, you must:

Download and extract the installation media to a directory;
Add the patches to an updates subdirectory (yep, all of them).

As you've discovered, you use the /UpdateEnabled and /UpdateSource switches when it comes to doing the installation.
The one small mercy is that CUs are cumulative, so you only need the latest one in the updates subdirectory.


What is the `TABLE example` syntax called? In PostgreSQL, I've been using syntax like
TABLE example1
UNION ALL
TABLE example2

which I understand to be equivalent to
SELECT * FROM example1
UNION ALL
SELECT * FROM example2

But a colleague asked me questions about this, and I can't find any PostgreSQL docs on this syntax. ("TABLE" isn't a helpful search phrase.) Where can I find documentation about it?
Is it ANSI SQL standard?

EDIT: It currently apparently even confuses StackExchanges's syntax highlighting.

It's documented with the SELECT statement and it's called "TABLE Command" there.

In the SQL standard it's called an "explicit table":

  The <explicit table>

     TABLE <table or query name>

  is equivalent to the

     ( SELECT * FROM <table or query name> )

This seems to be part of the standard at least since SQL 1992


the role of " Intent lock " in transaction and concurrency According to the book Microsoft Sql server 2012, Chapter 13 (Transactions and concurrency):

  If an exclusive page lock is held in one page of a table, another
  process can not get even a shared table lock for that table. This
  hierarchy is protected using intent locks. A process acquiring an
  exclusive page lock, update page lock or intent exclusive page lock
  first acquires an intent exclusive lock on that table. This intent
  exclusive table lock prevents another process from acquiring the
  shared table lock on that table.

And according to another paragraph of this book:

  ... lock compatibility is an issue only when the locks affect the same
  object. For example two or more process can hold exclusive page locks
  simultaneously, as long as the locks are on different pages.

I think these two paragraphs are in contradiction with each other, because in the first one it is said that if there is an exclusive lock on a page a table, other type of locks can't be granted on the whole table to other processes In the second paragraph it is said that it can be granted if it is on a different page.

Which one is true?

It is not that simple to understand without making your hands dirty and digging much deeper into the SQL Server locking. And yes, you must take into account the locking escalation as it is a completely different approach and while sometimes could be very useful, it has its own negative consequences

I would suggest reading All about locking in SQL Server where you can find a lot of details, explained in a simple and concise manner, that allows even the beginners to process this rather complex thing like SQL locking


Can I grant privileges to a role without specifying an object? I am trying to create a role to simplify granting a set of privileges that I often give to users. I am wondering if there is any way to grant these privileges on only the objects owned by their schema without knowing what the schema name will be ahead of time?

For instance, I want to assign the role student_access to the users JOE and MARY without having to specify what tables or procedures they have these privileges on. They should have all privileges that the role grants on all objects owned by their schema.

Thanks.

The system privileges that relate to the object types (CREATE TABLE, VIEW, ...) can be divided in two groups:

Those that lack the keyword ANY are only effective on the user's own schema.
Those with the keyword ANY (DROP ANY TABLE, EXECUTE ANY PROCEDURE, ...) are effective on all schemas. Consequently, you should restrict the ANY privileges to administrative users.

If you grant an object type privilege (without ANY) such as CREATE TABLE to a role, all users of this role will be able to assert this privilege on their own schema. Consequently you can grant these privileges to a role and the users will inherit such rights (on their own schema) when they are granted the role.


Sync data in SQL Server 2014 with Oracle database12c I have installed Oracle database 12c enterprise on my laptop.
How can I implement data synchronization between Oracle 12c and SQL Server 2014 that whenever an INSERT,UPDATE,DELETE or ... happend on SQL Server tables I can have it on my Oracle database automatically in the shortest time it is possible?

You have a few options:

Transactional replication: this has been around for quite some time
and works nicely within its narrowly defined scenario.
Unfortunately, the use of non-SQL Server subscribers is deprecated.
Change data capture + SSIS: this is the Microsoft recommended solution. It's a bit more involved than replication (to me anyway) but it's also more flexible. You can do some really powerful things with SSIS as the data mover, if you ever need it. Basic concepts described here with a detailed example here.
Oracle solutions: you can also do it from the other end. Oracle has similar capabilities as SQL Server including their GoldenGate software. However, I believe it's extra $$$ for license. I don't think you'll need to tread this path but calling it out for completeness.


How to avoid implicit conversion when using XQuery value() method in TSQL? I have the following xml that I am parsing using TSQL - XQuery value method.
<Root>
  <FooAudit>
    <BarIntField>..</BarIntField>
    <BarDateTimeField>..</BarDateTimeField>
    <BarVarcharField>...</BarVarcharField>
    <BarFloatField>...</BarFloatField>
  </FooAudit>
</Root>

However, every time I invoke .value() it results in an implicit conversion.  How can I get rid of this implicit conversion since this logic will be called thousands of times per minute?
SET NOCOUNT ON

DECLARE @BarIntField INT;
DECLARE @BarDateTimeField DATETIME;
DECLARE @BarVarcharField VARCHAR(25);
DECLARE @BarFloatField FLOAT;

--Test with non null values
SET @BarIntField = 123;
SET @BarDateTimeField = GETUTCDATE();
SET @BarVarcharField = 'My Text';
SET @BarFloatField = 2.3;

--Test with null values
/*
SET @BarIntField = NULL;
SET @BarDateTimeField = NULL;
SET @BarVarcharField = NULL;
SET @BarFloatField = NULL;
*/

DECLARE @payload XML;

SET @payload =
(
    SELECT @BarIntField AS BarIntField,
           @BarDateTimeField AS BarDateTimeField,
           @BarVarcharField AS BarVarcharField,
           @BarFloatField AS BarFloatField
    FOR XML PATH('FooAudit'), ROOT('Root'), TYPE
);

--Implicit conversion here
DECLARE @parsedIntValue AS INT = @payload.value('(/Root/FooAudit/BarIntField/text())[1]', 'int');
PRINT @parsedIntValue;

--Implicit conversion here
DECLARE @parsedDateTimeValue AS DATETIME = @payload.value('(/Root/FooAudit/BarDateTimeField/text())[1]', 'DATETIME');
PRINT @parsedDateTimeValue;

--Implicit conversion here
DECLARE @parsedVarcharValue AS VARCHAR(25) = @payload.value('(/Root/FooAudit/BarVarcharField/text())[1]', 'VARCHAR(25)');
PRINT @parsedVarcharValue;

--Implicit conversion here
DECLARE @parsedFloatValue AS FLOAT = @payload.value('(/Root/FooAudit/BarFloatField/text())[1]', 'FLOAT');
PRINT @parsedFloatValue;

Here is the plan.

You can ignore the implicit conversion warnings, they are irrelevant here.  But parsing XML is not free, and you're doing it the best way possible in TSQL.
If you really need to improve the XML parsing, you'd have to try SQLCLR and the low-level XmlReader, or a pre-compiled XmlSerializer.


What interval does SQL Server Profiler's "duration" refer to? I've been having strange things on a production server. Clients (all on the local LAN) are getting timeout errors on queries that should have no problem at all.

it happens during times of low CPU usage (<1%)
and when there is low hard drive I/O (<1%)

I spent an afternoon watching traces live in SQL Server Profiler, and the absolute epitome of the non-sense was the batch:

SELECT GETDATE() As ServerDate

Where profiler shows the numbers:

CPU: 0
Reads: 0
Writes: 0
Duration: 17,130 ms
StartTime: 2017-02-28 15:00:20.777
EndTime: 2017-02-28 15:00:37.907

What can possibly be going on in this database engine that perfectly benign queries take too long?

anti-virus software?
a VMWare hypervisor that doesn't schedule the virtual machine for 17 seconds?
a kernel model stall for 17 seconds that doesn't show up in Task Manager?

Because even if the server were busy, a query that takes:

zero cpu
zero reads
zero writes
and has no side effect on any pages in memory

should not take 17 seconds to complete.

Duration

The only way forward is to try to figure out what duration means.

What does duration mean? Is it:

Start: after entire batch has been received by the database engine
End: after the query is complete

or is it:

Start: after entire batch has been received by the database engine
End: after the last byte has been sent to the client

or is it:

Start: after entire batch has been received by the database engine
End: after the last byte has been received by the client

(but then how does SQL Server know when the last byte has been "received" by the client).

Because given that:

there's nothing wrong on the server
there's nothing wrong on the database
there's nothing wrong on the client

there must be something wrong in between. So I need to know exactly what conceptual events SQL Server uses to denote the start and end.

Locks

Duration from SQL Server 2005 traces is stored (i.e. when you save the trace to a table or file) in microseconds (one-millionth of a second)
Duration in Profiler is presented (i.e. on screen and in a screenshot) in milliseconds (one-thousandth of a second)

Of course, milliseconds vs microseconds was not my question.

Given that the only lock that getdate() takes is:

Acquire: S (shared) lock on D (database)
Release: S (shared) lock on D (database)

If the issues were due to locking:

can anyone suggest why acquiring a shared lock on a database might take 17 seconds
and if it were due to a 17 second delay acquiring a shared database lock
why other batches can continue to acquire/release shared database locks without a stall?
and if the stall was due to taking 17 seconds to acquire a lock
what I can do about it?

That also wasn't my question. My question is tautological.

Tautology

My question as written is pretty simple:

  What does duration mean?

Of course that's trivial to answer:

  duration is EndTime - StartTime.

Which means I'm actually asking (without being pedantic):

during what stage of query execution is StartTime timestamp recorded?
during what stage of query execution is EndTime timestamp recorded?

If I can get an answer to that, my question next will be to find all events that go in between.

Bonus Chatter

It might be helpful to see a timeline of all events during select getdate():

Acquire Shared Database lock
Release Shared Database lock
SP:CacheMiss
SQL:BatchStarting
SQL:StmtStarting
SQL:StmtCompleted
SQL:BatchCompleted

Of course, this is just one, non-repeatable, random, example of queries that take an inordinate amount of time to run.

Bonus Reading

SQL Server insert randomly takes 10+ seconds to complete?  (closed by grumpy people)
SQL Profiler CPU / duration units (it's 17,000 ms)
View and Analyze Traces with SQL Server Profiler

Duration from SQL Server 2005 traces is stored in microseconds (one-millionth of a second) while Duration from SQL Server 2000 is stored in milliseconds (one-thousandth of a second). CPU is always stored in milliseconds.

The typical cause for long durations with no work is blocking: someone else has a lock on a row.

To find out why queries are waiting, skip Profiler and head to Adam Machanic's sp_WhoIsActive. It shows you what queries are running right now, and the wait_info column tells you what they're waiting on (and for how long). If you see LCK*, then it's blocking.


Why is there such a huge performance difference in these two join statements? I have a query which i first wrote as (query has been simplified a bit) :

select ROW_NUMBER() OVER (ORDER BY c.CategoryTypeID), c.name, count(p.id) from category c
inner join product p on p.indID = c.id or p.catid = c.id and p.deleted = 1 and p.statusid = 1
group by c.name

This took 8 seconds.

If i changed it to the following it runs in under 1 second:

   select ROW_NUMBER() OVER (ORDER BY c.CategoryTypeID), c.name, count(p.id) from category c
   inner join product p on p.indID = c.id or p.catid = c.id
   where p.deleted = 0 and p.statusid =1
group by c.name

I'd like to understand why there's such a huge difference in performance. Not only is the performance worse but it seems to be ignoring my conditional statements on the first sql query it's counting records regardless of what p.deleted or p.statusid is

What I am not understanding about how joins work? This is being run on mssql 2012

The OR operator in your first statement is making the output of both queries totally different.  If you put brackets around the two items that are separated by the OR statement you will see the results are the same.

SELECT ROW_NUMBER() OVER (ORDER BY c.CategoryTypeID), c.name, count(p.id)
FROM category c
    INNER JOIN product p on (p.indID = c.id or p.catid = c.id)
        and p.deleted = 1
        and p.statusid = 1
GROUP BY c.name

OR is evaluated after AND...


remove duplicate rows in mysql table that does not contain primary key I have a table item having just one column name:-

name
-----
toys
shirt
mobile
Shirt
speaker
Toys
....
....

I am trying to delete duplicate rows except one duplicated record with the help of below query

DELETE FROM
(
SELECT `name`,`rownumber` FROM (SELECT GREATEST(0,@num := IF(`name` = @NAME,
@num + 1, 1),LEAST(1, LENGTH(@NAME := `name`))) AS rownumber,`name` FROM
item ORDER BY `name`) AS result1
) AS result
WHERE rownumber >1

The Inner query (Starts from SELECT name and ends with result1) returns individual auto incremented number for each repeating group but when i run entire query then it does not work.

I would like to know what changes i need to make in the inner query (contained in round braces) to delete duplicate rows.

In the spirit of @yercube's answer, I have an answer that has an added twist.

CREATE TABLE stage
(
    id int not null auto_increment,
    name varchar(20),
    primary key (id)
);
CREATE TABLE stage2 LIKE stage;
INSERT INTO stage (name) SELECT name FROM item;
INSERT INTO stage2 (id) SELECT min_id FROM
(SELECT MIN(id) min_id,name FROM stage GROUP BY name) A;
UPDATE stage2 A INNER JOIN stage B USING (id) SET A.name=B.name;
TRUNCATE TABLE item;
INSERT INTO item (name) SELECT name FROM stage2;
DROP TABLE stage;
DROP TABLE stage2;

This will load stage2 with the first occurrence of each name from item, zap the item table, and load the unique occurrences back.

If you look back in @yercube's answer and compare it to my answer, his is much more simplistic because

@yercube uses one temp table, while I use two
I had to create a column for iteration control, @yercube did not need to
@yercube has fewer steps
both answers achieve the same thing

I do not expect my answer to be accepted. The sole purpose of my answer was demonstrate that other answers lose the concise clarity needed to solve your problem. Again, hats off to @yercube.


Can I get OSQL if I install SQL Server Client Tools? We have some legacy .bat scripts full of osql commands to existing SQL Server instances / dbs on our network.

We've tried running the .bat scripts from our app server (Win Server 2008) but get the error:
 "osql.exe' is not recognized as an internal or external command"

We are hoping we don't need to install a full-blown SQL Server to get access to osql.

I've heard you can get it from the Client Tool installation, and no license required as long as connecting to licensed sql servers. (which we would be).

Can anyone confirm?
Are "Client Tools" part of the installation discs?
Is this a viable solution?

Thanks for your help folks!

The main recommendations appear to be:

If you have the install discs / files, then you should be able to install just the Client Tools.
Download SQL Server 2012 (SP1) Express (I would try the "SQL Server Management Studio Express (Tools only)" version first)
Check out: http://www.mssqltips.com/sqlservertip/1807/sql-server-2008-client-tools-installation/
If you already have a machine with OSQL installed, you might be able to copy just the following two files to the new server (place them in the same directory) and have it work:

C:\Program Files\Microsoft SQL Server\110\Tools\Binn\OSQL.EXE
C:\Program Files\Microsoft SQL Server\110\Tools\Binn\Resources\1033\osql.rll

As far as licensing goes, I found the following file:

C:\Program Files\Microsoft SQL Server\110\Tools\Binn\Resources\1033\license_SQLCMD.txt

That has the following snippet:

  MICROSOFT SOFTWARE LICENSE TERMS MICROSOFT SQL SERVER 2012 COMMAND
  LINE UTILITIES
   ...
  1.    INSTALLATION AND USE RIGHTS. You may install and use any number of copies of the software on your devices to design, develop and test
  your programs.

Note:
OSQL has been superseded by SQLCMD. When you run OSQL it will display the following message:

  Note: osql does not support all features of SQL Server 2012.
  Use sqlcmd instead. See SQL Server Books Online for details.

However, if your existing code expects OSQL then you would need to test SQLCMD with your code first to make sure that any behavior changes between OSQL and SQLCMD do not break your code.


SELECT query on unique number field returns no rows This is a cross-post of an issue I field on the GORM Github repo. I am unsure if the issue is with Postgres 11 or with GORM.

See: https://github.com/jinzhu/gorm/issues/2872

I have a table containing an ID as primary key and only one more column (mega_herz) which is a numeric(7,3). The numeric field also has a unique constraint on it. When I do the following query from pgadmin4 or psql I get one row as a response:

SELECT *
FROM "ttnmapper_frequencies"
WHERE ("ttnmapper_frequencies"."mega_herz" = 868.3)
ORDER BY "ttnmapper_frequencies"."id" ASC
LIMIT 1

But when I do the same query via GORM it does not return any results, and when I try and do an insert it fails:

(/home/jpmeijers/go/src/ttnmapper-postgres-insert-raw/main.go:342)
[2020-02-03 14:52:28]  [3.07ms]
SELECT *
FROM "ttnmapper_frequencies"
WHERE ("ttnmapper_frequencies"."mega_herz" = 868.3)
ORDER BY "ttnmapper_frequencies"."id" ASC
LIMIT 1
[0 rows affected or returned ]

(/home/jpmeijers/go/src/ttnmapper-postgres-insert-raw/main.go:342)
[2020-02-03 14:52:28]  [2.10ms]
INSERT  INTO "ttnmapper_frequencies"
("mega_herz")
VALUES
(868.3)
RETURNING "ttnmapper_frequencies"."id"
[0 rows affected or returned ]

(/home/jpmeijers/go/src/ttnmapper-postgres-insert-raw/main.go:345)
[2020-02-03 14:52:28]  pq: duplicate key value violates unique constraint "ttnmapper_frequencies_mega_herz_key"

Why would the select query return no result? Should I specify the number with three decimals in the where clause?

Update 2020-02-09:

The log output from Postgres shows the following:

2020-02-09 05:43:45.679 UTC [59458] ttnmapper@ttnmapper LOG:  execute <unnamed>: SELECT * FROM "ttnmapper_frequencies"  WHERE ("ttnmapper_frequencies"."mega_herz" = $1) ORDER BY "ttnmapper_frequencies"."id" ASC LIMIT 1
2020-02-09 05:43:45.679 UTC [59458] ttnmapper@ttnmapper DETAIL:  parameters: $1 = '868.2999877929688'
2020-02-09 05:43:45.688 UTC [59458] ttnmapper@ttnmapper LOG:  statement: BEGIN READ WRITE
2020-02-09 05:43:45.689 UTC [59458] ttnmapper@ttnmapper LOG:  execute <unnamed>: INSERT  INTO "ttnmapper_frequencies" ("mega_herz") VALUES ($1) RETURNING "ttnmapper_frequencies"."id"
2020-02-09 05:43:45.689 UTC [59458] ttnmapper@ttnmapper DETAIL:  parameters: $1 = '868.2999877929688'
2020-02-09 05:43:45.704 UTC [59458] ttnmapper@ttnmapper ERROR:  duplicate key value violates unique constraint "ttnmapper_frequencies_mega_herz_key"
2020-02-09 05:43:45.704 UTC [59458] ttnmapper@ttnmapper DETAIL:  Key (mega_herz)=(868.300) already exists.

The query

SELECT * FROM "ttnmapper_frequencies"  WHERE ("ttnmapper_frequencies"."mega_herz" = 868.2999877929688) ORDER BY "ttnmapper_frequencies"."id" ASC LIMIT 1

returns no results.

But however the query

SELECT * FROM "ttnmapper_frequencies"  WHERE ("ttnmapper_frequencies"."mega_herz" = 868.3) ORDER BY "ttnmapper_frequencies"."id" ASC LIMIT 1

returns a single row.

I therefore have to assume Postgres does not automatically round parameters for select queries. What would be the best solution for this?

The problem was solved when I changed the MegaHerz field's data type in the golang struct definition from a float64 to a decimal.Decimal.

type Frequency struct {
    ID          uint
    MegaHerz    decimal.Decimal `gorm:"unique;not null;type:numeric(7,3)"`
    Packets     []Packet
}

You do however need to include a third party library for the decimal.Decimal fixed point type.

import "github.com/shopspring/decimal"


Why won't Oracle alter the size of a column that is used for sub partitioning? I am trying to resize a varchar2 column, when I get an ORA-14265 error:

ORA-14265: data type or length of a table subpartitioning column may not be changed

With Oracle providing the unhelpful:

// *Cause:  User issued ALTER TABLE statement attempting to modify
//          data type and/or length of a column used to subpartition the
//          table named in ALTER TABLE statement, which is illegal
// *Action: Avoid modifying data type and/or length of table
//          subpartitioning column(s)

So I get that Oracle doesn't want me to alter the column. I presume the hash used for the subpartition would be broken by this. How do I get around this though?
The only suggestion I have found so far that seems plausible is to rename the existing table, and recreate the original with updated schema, then select the data from into the recreated table.
I don't particularly like this idea, for reasons of effort and likelihood of human error:

We have lots of partitions, and behind the scenes I'd have to manually ensure the new table matched up identically, so that supporting maintenance scripts could continue along nicely.
Then there is disk space. We keep 9 rolling months of data, and I don't have room to suddenly double up on this one table.

Any suggestions would be much appreciated. I hope I'm just missing something easy. Also, if anyone wants to let me know why oracle can't rearrange the data on disk and create a new subpartition hash automagically that would be great.

The solution is dbms_redefinition package. Basically redefition will move, online, to a new table (called interim table), then move all dependent objets like indexes and last exchange the interim table with the original one.

What you have to do is:

Create the interim table withe correct column definition
Run redefinition
Drop the old table

Use dbms_redefinition in the following way:

-- DETERMINE IF THE ORIGINAL TABLE CAN BE REDEFINED ONLINE
BEGIN
DBMS_REDEFINITION.CAN_REDEF_TABLE('SCHEMA','YOURTABLE', DBMS_REDEFINITION.CONS_USE_ROWID);
END;
/

-- BEGIN THE REDEFINITION
BEGIN
DBMS_REDEFINITION.START_REDEF_TABLE(
UNAME => 'SCHEMA',
ORIG_TABLE => 'YOURTABLE',
INT_TABLE => 'INTERIM_YOURTABLE'
OPTIONS_FLAG => DBMS_REDEFINITION.CONS_USE_ROWID);
END;
/

-- IF THE TABLE HAD DEPENDENCIES (INDEXES ... CONSTRAINTS ... TRIGGERS)
-- THIS WOULD BE THE POINT AT WHICH THEY WOULD HAVE BEEN COPIED
DECLARE
error_count pls_integer := 0;
BEGIN
DBMS_REDEFINITION.COPY_TABLE_DEPENDENTS('SCHEMA', 'YOURTABLE', 'INTERIM_YOURTABLE',
dbms_redefinition.cons_orig_params, TRUE, TRUE, TRUE, FALSE, error_count);
DBMS_OUTPUT.PUT_LINE('errors := ' || TO_CHAR(error_count));
END;
/

-- FINISH THE REDEFINITION
exec DBMS_REDEFINITION.FINISH_REDEF_TABLE('SCHEMA','YOURTABLE','INTERIM_YOURTABLE');

If you run redefinition with SYSTEM user there is no problem. If you want to execute it with a less privileged user you have to trick some privileges in order to get it working. Privileges required are:

Execute privilege to DBMS_REDEFINITION
Create any table
Alter any table
Drop any table
Lock any table
Select any table

Tables with the following characteristics cannot be redefined online:

[9.0.1]Tables with no primary keys
Tables that have materialized view logs defined on them
[9i] Tables that are materialized view container tables and AQ tables
[10g] Tables that are replicated in an n-way master configuration can be redefined, but horizontal subsetting (subset of rows in the table), vertical subsetting (subset of columns in the table), and column transformations are not allowed.
The overflow table of an IOT table
Tables with fine-grained access control (row-level security)
Tables with BFILE columns
Tables with LONG columns can be redefined online, but those columns must be converted to CLOBS. Also, LONG RAW columns must be converted to BLOBS. Tables with LOB columns are acceptable.
Tables in the SYS and SYSTEM schema
Temporary tables

Other restrictions:

A subset of rows in the table
Only simple deterministic expressions, sequences, and SYSDATE can be used when mapping the columns in the interim table to those of the original table. For example, subqueries are not allowed.
If new columns are being added with no column mappings, then they must not be declared NOT NULL until the redefinition is complete.
There cannot be any referential constraints between the table being redefined and the interim table.
Table redefinition cannot be done NOLOGGING.
[10g] For materialized view logs and queue tables, online redefinition is restricted to changes in physical properties.
You cannot convert a nested table to a VARRAY.