Why is an empty rowset not equivalent to null when assigning a query result to a variable and how best to work around? I have a cursor. Within my loop I select a single column and assign the result to a variable. When the select returns an empty rowset, nothing is assigned to the variable. In particular, the variable retains its current value which, in my case, is the value it held on the last iteration of the loop.

The following code illustrates this.

declare @testA int;
declare @testB int;

set @testA = 1;
set @testB = 2;

select @testA =         id from (values (3)) as testtable(id) where testtable.id = 4 ; -- empty rowset
select @testB = (select id from (values (5)) as testtable(id) where testtable.id = 6); -- empty rowset

select @testA as testA, @testB as testB;

@testA retained its value (1) while @testB was updated to null.

The only difference between the two assignment attempts was that in case B a subquery was used (via the addition of the parentheses and an additional select keyword).

This says to me a subquery (which always returns a single value) represents an empty rowset as null in that return value, but attempting to select a single column from an empty rowset does not return null. Rather, it kind of says "I have no value, so there is nothing to assign".

My understanding of the definition of null is that it represents an "undefined" value.

If I am saying "select 'id' where these conditions are met" and there are no 'id' values meeting such conditions, does this not imply the 'id' value I want is undefined?

This says to me a subquery (which always returns a single value) represents an empty rowset as null in that return value, ...

A subquery used in a context that expects a single, scalar value needs to return a single, scalar value. Such contexts are when used as an expression or with operators that expect a single, scalar value. When no scalar value exists, it returns NULL for precisely the reason you thought: it means "unknown". Think of a subquery in these contexts as being a replacement for a scalar UDF. It will always return a single something (T-SQL does not allow for a return type of void). So, what you have is effectively: select @testB = dbo.Function(6); assuming that "dbo.Function" is defined as:

CREATE FUNCTION dbo.Function (@Input INT)
RETURNS INT
AS
BEGIN
  DECLARE @Temp INT;
  SELECT @Temp = TestTable.[id]
  FROM   (VALUES (5)) TestTable([id])
  WHERE  TestTable.[id] = @Input;

  RETURN @Temp;
END;

More examples:

SELECT 1 AS [one], (SELECT 2 WHERE 1 = 0) AS [nothing];
-- 1   NULL

and:

DECLARE @MoreThanOneRow INT;

SELECT @MoreThanOneRow = (SELECT [object_id] FROM sys.objects);
/*
Msg 512, Level 16, State 1, Line XXXXX
Subquery returned more than 1 value. This is not permitted when the subquery
  follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
*/

and:

DECLARE @MoreThanOneColumn INT;

SELECT @MoreThanOneColumn = (SELECT TOP (1) [object_id], [schema_id] FROM sys.objects);
/*
Msg 116, Level 16, State 1, Line XXXXX
Only one expression can be specified in the select list when the
  subquery is not introduced with EXISTS.
*/

  ... but attempting to select a single column from an empty rowset does not return null. Rather, it kind of says "I have no value, so there is nothing to assign".

I wouldn't think of an empty result set in terms of "I have no value, so there is nothing to assign". Instead, think of it in terms of: an assignment will be performed for every row returned, and no rows returned means that there is no action to take. It might be a subtle difference, but the focus really needs to be on "rows = actions" more so than "returned value is something that can be assigned".

This distinction is not so subtle when:

You are executing a T-SQL scalar UDF in a query and passing in a column. Assuming we are not dealing with Scalar UDF inlining (starting in SQL Server 2019), then scalar UDFs are executed per each row, hence the performance issues associated with them and the impetus for the new feature in SQL Server 2019 to inline UDFs (if they meet the requirements).
You are setting a value like you are showing for select @testA = id from .... The assignment will happen per each and every row returned. And the final value of the variable will be the last row returned. That can be predictable if you use an ORDER BY, or you can live life on the edge and go for unpredictable results by not using an ORDER BY. For example:

DECLARE @Temp INT;

SELECT @Temp = tab.col
FROM   (VALUES (1), (3), (4), (2)) tab(col);

SELECT @Temp AS [NotOrdered];
-- 2

SELECT @Temp = tab.col
FROM   (VALUES (1), (3), (4), (2)) tab(col)
ORDER BY tab.col;

SELECT @Temp AS [Ordered];
-- 4

Another way to look at this is that you are using a result set as a loop:

-- pseudo-code
while (ResultSet.GetNextRow())
{
    set @testA = ResultSet.GetColumnValue("id");
}

If the first call to ResultSet.GetNextRow() returns false (due to no rows in the result set), then the loop is never entered and the assignment never happens.
If there are multiple rows in the result set, then the assignment happens for each and every row returned, and in the order that they are returned.

In fact, this is how string concatenation works when done via result set (please note: as Martin Smith pointed out in this Stack Overflow answer, string concatenation done in this manner is not guaranteed to produce the expected result, even if most of the time it does; however, that does not take away from this method proving my point here):

DECLARE @List NVARCHAR(MAX) = N'**';

SELECT @List = @List + N', ' + QUOTENAME([name])
FROM   sys.schemas
WHERE  1 = 0
ORDER BY [name];

SELECT @List AS [NoChange];
-- **

SELECT @List = @List + N', ' + QUOTENAME([name])
FROM   sys.schemas
ORDER BY [name];

SELECT TRIM(N'*, ' FROM @List) AS [SQL2017orNewer];
--SELECT SUBSTRING(@List, 5, 80000) AS [PreSQL2017];

I even made use of this behavior to generate a complex T-SQL script:

Uppercase All String Columns (and in a Single Query!)


Ola Hallengren with database mirroring I am about to setup Ola Hallengrens database maintenance plan. We have our database mirrored and I was just wanting to know if I need to run the script on both my Sql Server instances or only the primary?

I recommend that you have the jobs enabled and scheduled on both servers and with the same configurations. Databases that are in a mirror role will automatically be skipped. There are checks for that in the script.


PIVOT data type conversion error How do I name the columns of a PIVOT table to something that is not a number?

Is it a requirement that the names of the spreading columns be a number?

I'm working my way thought the book Querying Microsoft SQL Server 2012 in preparation for the 70-461 exam. I am currently on the lesson for PIVOT tables (Chapter 5, Lesson 2, Page 163). The following SQL executes successfully.

WITH Pivot234
AS
(
select  ss.companyname,                         --Grouping column
        datepart(dw, so.orderdate) as DayNumber, --Spreading column
        so.freight                              --Aggregation Column
FROM Sales.Orders so
LEFT OUTER JOIN sales.Shippers ss ON so.shipperid = ss.shipperid
)

SELECT  companyname, [1],[2],[3],[4],[5],[6],[7]
FROM Pivot234
PIVOT (AVG(FREIGHT)
       FOR DayNumber IN ([1],[2],[3],[4],[5],[6],[7])
       ) AS p;

I want the name of my attributes to be the days of the week not numbers. When I change the numbers in the PIVOT table operator to the days of the week I get the following...

WITH Pivot234
AS
(
select  ss.companyname,                         --Grouping column
        datepart(dw, so.orderdate) as DayNumber, --Spreading column
        so.freight                              --Aggregation Column
FROM Sales.Orders so
LEFT OUTER JOIN sales.Shippers ss ON so.shipperid = ss.shipperid
)

SELECT  companyname, [Mon],[Tues],[Wed],[Thurs],[Fri],[Sat],[Sun]
FROM Pivot234
PIVOT (AVG(FREIGHT)
       FOR DayNumber IN ([Mon],[Tues],[Wed],[Thurs],[Fri],[Sat],[Sun])
       ) AS p;

The second SQL query has the following error...

  Msg 8114, Level 16, State 1, Line 36
  Error converting data type nvarchar to int.
  Msg 473, Level 16, State 1, Line 36
  The incorrect value "Mon" is supplied in the PIVOT operator.

  Line 36 is FOR DayNumber IN ([Mon],[Tues],[Wed],[Thurs],[Fri],[Sat],[Sun])

What am I not understanding about PIVOT tables?

Alias the columns in the select. The values in the In (...) part of the Pivot need to be the exact field values observed in the data.

Select CompanyName, [1] As Mon, [2] As Tues, [3] As Wed...
  From Pivot234
  Pivot (Avg(Freight) For DayNumber In ([1], [2], [3], [4], [5], [6], [7])) As p;

  Can you explain why it is necessary to alias the spreading columns? Why can I not just simply state the columns as I did in the PIVOT table operator and in the SELECT statement?

The Pivot(Sum([ValueField]) For [CategoryField] In ('Category A', 'Category B' ... )) syntax is looking for the literal values that you supply in the In () to be in the [CategoryField]. It isn't mapping number 1 to position 1, it is searching the field for those exact values.


SQL Server restore differential after restoring full backup Here is the situation: I had a full backup, and a differential backup. I have restored the full backup into the database. But accidentally lost that backup file.

Now I would like to apply the differential backup. Normally you must restore from both the full backup, and the differential, without opening the DB for use. However, we have opened the DB, but not made any updates.

We have made a NEW full backup, which should be identical the the original full backup, except for at least the timestamp. That is, SQL Server detects that the differential is not taken from exactly that DB with that full backup.

Is there any way to trick SQL Server into allowing a differential to work with with new full backup? (I realize it is risky, but the data is important and willing to risk some errors.)

Is there any other way to get the updates out of the differential backup so they could be applied manually? Is there any "decoder" for a differential backup file? Any special recovery tools for this kind of situation?

No, sorry. Once the log chain was broken the following differential cannot be restored because there are at least a few transactions that would be lost.  You may feel that those 'unimportant' changes could just be ignored. But SQL Server insists on a complete restore chain.

Though there are certainly some companies that will try to reconstitute a database for you. Not for free, of course.


Do I need a composite primary key in a junction table? I have a junction table like this:

CREATE TABLE `entitytagjunction` (
     `EntityId` bigint(20) NOT NULL,
     `TagId` bigint(20) NOT NULL,

     PRIMARY KEY (`EntityId`,`TagId`),
     KEY `IX_EntityId` (`EntityId`) USING HASH,
     KEY `IX_TagId` (`TagId`) USING HASH,

     CONSTRAINT `FK_EntityTagJunction_Entities_EntityId`
        FOREIGN KEY (`EntityId`) REFERENCES `entities` (`Id`)
        ON DELETE CASCADE ON UPDATE CASCADE,
     CONSTRAINT `FK_EntityTagJunction_Tags_TagId`
        FOREIGN KEY (`TagId`) REFERENCES `tags` (`Id`)
        ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

I have EntityId and TagId as composite primary key to ensure uniqueness, and SQL Server and MySql will both use the primary key as clusteredId.

As far as I could read from the other posts, Guid is not good to be a primary key because the randomness make fragments for clusteredIds, poor for performance if the table consist of millions of rows. Apparently the combination of EntityId and TagId has some randomness. Will this have impact on performance when using them as a composite primary key and clusteredId by default? or should I add an int column as a surrogate primary key though it will not be used in applications?

You should use the combination of EntityId and TagId as composite primary key, here is why:

This will enforce uniqueness, that you will have to do even if you add a surrogate key.
These columns are what you will base most if not all of your queries on this table, so they should be indexed anyway
Adding a surrogate key will not have any benefit from the performance perspective.

btw, I don't know about MySql, but in Sql Server, a primary key is not necessarily the clustered index. It is set as the clustered index by default, but you can change the clustered index to any other index if you need.


Where is this Query Coming From? I have enabled Query Store on SQL 2017, and I'm seeing at query that's occurring frequently doing a SELECT * on a specific table.

I would like to narrow down where this request is coming from and see if we can find a better way than doing the SELECT *.

I have a query id, of course, from Query Store. I've also recently asked my developers to include Application Name in their connection strings, which many have done.

Is there a way (perhaps using a DMV, for example) that I can find out the Application Name associated with this query?

Query the query store DMVs for the query hash, then use that query hash in an Extended Events session to capture the pertinent details.

This should get you there:

SELECT qsq.query_hash
    , qsqt.query_sql_text
FROM sys.query_store_query qsq
    INNER JOIN sys.query_store_query_text qsqt ON qsq.query_text_id = qsqt.query_text_id
WHERE qsqt.query_sql_text LIKE '%some-pertinent-SQL%';

In the query above, I have LIKE '%some-pertinent-SQL%' - this limits the results of the query to match the target your looking for - you need to replace the some-pertinent-SQL with a unique piece of your target query.

Use the value from the query_hash column returned above in the Extended Events session definition:

CREATE EVENT SESSION [t] ON SERVER
ADD EVENT sqlserver.sql_statement_completed(
    ACTION(sqlserver.client_app_name
    , sqlserver.client_hostname
    , sqlserver.nt_username
    , sqlserver.plan_handle
    , sqlserver.query_hash)
    WHERE ([sqlserver].[query_hash]=(0x00000000))
    )
ADD TARGET package0.ring_buffer
WITH (STARTUP_STATE=OFF);
GO

Replace the 0x00000000 with the actual query hash value.

Start the Extended Events session with:

ALTER EVENT SESSION [t] ON SERVER
STATE = START;

Right-click the Extended Events session in Object Explorer to see details about the client hostname, username, etc., for queries as they happen.

To test this, I executed the following code on my SQL Server 2016 test instance:

CREATE DATABASE QueryStoreTest
ON PRIMARY
(
      NAME = QueryStoreTest_primary
    , FILENAME = 'C:\temp\QueryStoreTest_primary.mdf'
    , SIZE = 100MB
    , FILEGROWTH = 100MB
    , MAXSIZE = 1000MB
)
LOG ON
(
      NAME = QueryStoreTest_log
    , FILENAME = 'C:\temp\QueryStoreTest_log.ldf'
    , SIZE = 100MB
    , FILEGROWTH = 100MB
    , MAXSIZE = 1000MB
);
GO
ALTER DATABASE QueryStoreTest
SET QUERY_STORE = ON;

Here, I'll create a couple of objects in the QueryStoreTest database, and a test query:

USE QueryStoreTest;
CREATE TABLE dbo.qst
(
    id int NOT NULL
        PRIMARY KEY CLUSTERED
        IDENTITY(1,1)
);

CREATE TABLE dbo.qst2
(
    id int NOT NULL
        PRIMARY KEY CLUSTERED
        IDENTITY(1,1)
);

SELECT *
/*  test query store query  */
FROM dbo.qst
    LEFT MERGE JOIN dbo.qst2 on qst.id = qst2.id;

Here, I'll get the query hash from the query store:

SELECT qsq.query_hash
    , qsqt.query_sql_text
FROM sys.query_store_query qsq
    INNER JOIN sys.query_store_query_text qsqt ON qsq.query_text_id = qsqt.query_text_id
WHERE qsqt.query_sql_text LIKE '%test query store query%';

Results:

╔════════════════════╦════════════════════════════════════════════════════════╗
║     query_hash     ║                     query_sql_text                     ║
╠════════════════════╬════════════════════════════════════════════════════════╣
║ 0x3A0100223AD74766 ║ SELECT *  /*  test query store query  */  FROM dbo.qst ║
║                    ║  LEFT MERGE JOIN dbo.qst2 on qst.id = qst2.id          ║
╚════════════════════╩════════════════════════════════════════════════════════╝

Now, I'll create the Extended Events Session:

CREATE EVENT SESSION [t] ON SERVER
ADD EVENT sqlserver.sql_statement_completed
(
    ACTION (
          sqlserver.client_app_name
        , sqlserver.client_hostname
        , sqlserver.nt_username
        , sqlserver.plan_handle
        , sqlserver.query_hash
    )
    WHERE ([sqlserver].[query_hash]=(0x3A0100223AD74766))
)
ADD TARGET package0.ring_buffer
WITH (STARTUP_STATE=OFF)
GO

And start the session:

ALTER EVENT SESSION [t] ON SERVER
STATE = START;

Now, if I run the test query and look at the live data from the session, I see:

Cleanup:

USE master;
GO
ALTER EVENT SESSION [t] ON SERVER
STATE = STOP;
GO
DROP EVENT SESSION [t] ON SERVER;
GO
ALTER DATABASE QueryStoreTest SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
DROP DATABASE QueryStoreTest;


What is the limit on the number of INSERT statements you can paste into SSMS and run? I am currently creating a stored procedure that populates a table with a list of insert statements so that my DBAs can export the contents of the table to a text file and then paste the statements into SSMS and run (it is to copy data from test environment to live enviroment - I am constricted by the fact the 2 environments cannot see each other, bcp is not configured on the environment

My question is that the table I am going to export could have up to 2,000,000 insert statements. Is there a limit to what can be pasted in and run?

bcp is not configured on the environment

BCP is an exe that comes with SQL Server. The default path is C:\Program Files\Microsoft SQL Server\100\Tools\Binn\bcp.exe

You can refer to my BCP and BULK INSERT script here.

  I am going to export could have up to 2,000,000 insert statements.

SSMS wont be able to support this. You have to use SQLCMD.

Highly suggest you to use BCP or SSIS to do the task.


high PAGEIOLATCH_SH waits on server EDIT:

I know this question is closed, but I hope it will help someone.

The problem is on our disks ( cluster ).

Using PerfMon, I could create some counters ( disk reads and writes ) and the reads counter is fixed on 100%.

I am seeing the following wait in sp_WhoIsActive:

PAGEIOLATCH_SH:Database_Name:1(*)

I see some posts here, saying this warning is because of high I/O. SQL Server Books Online defines the SQL wait type PAGEIOLATCH_SH as:

  Occurs when a task is waiting on a latch for a buffer that is in an
  I/O request. The latch request is in Shared mode.

And this:

  A user process will request some data which is not currently in the
  buffer cache. At that  point – SQL Server will attempt to allocate a
  buffer page – and while the data is moved from disk to buffer cache an
  exclusive PAGEIOLATCH_EX is created on the buffer. At the same time a
  PAGEIOLATCH_SH is created on the buffer from the user process perspective.

I've created index and stats, and also used the SQL Server Profiler to help me.

Is there any way to improve the query?  How can I improve the handling of lots of ANDs?

We have been having this problem for about a week, and I can't figure out what to do.

SELECT TOP 30 codCliente
FROM (
    SELECT t1.CodCliente
        , codcampo
        , valor
        , t1.chavealeat
    FROM tblCliente AS t1 WITH(NOLOCK)
        INNER JOIN tblClienteDetalhe AS t2 WITH (NOLOCK)
            ON t1.codcliente = t2.codcliente
            AND CodCampo IN (-1, 4)
    WHERE codStatus IN(0)
        AND t1.ChavePeriodo < getdate()
        AND t1.CodStatusLigacao = 0
        AND EXISTS (
            SELECT codcliente
            FROM tblclientedetalhe WITH (NOLOCK)
            WHERE codcampo = 3
                AND valor = '2'
                AND codcliente = t1.codcliente
            )
        AND EXISTS (
            SELECT codcliente
            FROM tblclientedetalhe WITH (NOLOCK)
            WHERE codcampo = 6
                AND Convert(DateTime,Valor) BETWEEN '2015-03-01' AND '2015-03-31'
                AND DateDiff(day,Valor,GetDate()) > 15
                AND codcliente = t1.codcliente
            )
        AND NOT EXISTS (
            SELECT 0
            FROM tblPesquisa WITH (NOLOCK)
            WHERE tblPesquisa.CodCliente = t1.CodCliente
            )
        AND EXISTS (
            SELECT codcliente
            FROM tblclientedetalhe WITH (NOLOCK)
            WHERE codcampo = 4
                AND valor = '161'
                AND codcliente = t1.codcliente
            )
    ) AS Cliente
PIVOT (
    MAX(Valor)
    FOR codCampo in ([4])
    ) AS PivotTable
WHERE ([4] = '161')
ORDER BY chavealeat;

EDIT1:

As i said bellow in comments, there are other queries with this Wait Status.
I'm waiting for a job to finish to be sure it's not the problem.

This may not directly answer your question, however you have multiple potential issues in your query, that are immediately visible to me.

AND Convert(DateTime,Valor) BETWEEN '2015-03-01' AND '2015-03-31'
AND DateDiff(day,Valor,GetDate()) > 15

Why are you doing CONVERT(DateTime, Valor) ?  Is Valor not a DateTime type in the table?  If not, you're doing that wrong.
Running a function on a column (DateDiff(day, Valor, GetDate())) and comparing that to a value makes it non-SARGable.  Try this, instead:

AND Valor > DATEADD(DAY, -15, GETDATE())
BETWEEN (x and y) may not function the way you think it does.  You're better off to use the following construct since it explicitly defines the precise date range you desire.

WHERE Valor >= '2015-03-01T00:00:00' AND Valor < '2015-04-01T00:00:00'
WITH (NOLOCK) is NOT a go-faster button!  See http://blogs.sqlsentry.com/aaronbertrand/bad-habits-nolock-everywhere/ and http://sqlperformance.com/2015/04/t-sql-queries/the-read-uncommitted-isolation-level for excellent details about why that is a non-starter most of the time.


How to find mongo document by ObjectID age I have a collection of documents I'd like to pull a subset created after a certain point in time.  I understand the timestamp of creation is encoded in each documents ObjectID (assuming they are auto generated).  I see the ObjectId has a getTimestamp method that returns that portion of the ObjectID as an ISOdate.

I'm not very fluent in mongo and am having trouble constructing this seemingly simple query.

For bonus points,  once I figure out the "where clause", if you will, I'm wanting to select a single field from the documents using mongodump or what ever else might be available to export the results to a text file via a mongo shell.

The general approach was to fabricate an ObjectID with the time stamp prefix of what I wanted.  The general query looked like

db.mycol.find({_id: {$gte:ObjectId("50b7feb00000000000000000")} }, {fieldIWant:1})

The corresponding mongoexport cmd for a json output file was

 mongoexport -d mydb -c mycol -q '{_id: {$gte:ObjectId("50b7feb00000000000000000")} }' -f 'fieldIWant' > myFile.json


join_buffer_size causes high cpu load I am playing and testing with my.cnf and at the moment I have these server specs:
12 CPU vCore
500 GB SSD
24 GB RAM
1 GB Swap
and this is the input of my my.cnf:
# GENERAL #
# user                           = mysql
# default-storage-engine         = InnoDB

# DATA STORAGE #
# datadir                        = /var/lib/mysql/

#TIMEOUTS#
wait_timeout                    = 600

#NO DNS#
# skip-name-resolve

# MyISAM #
key-buffer-size                = 32M
myisam-recover                 = FORCE,BACKUP

# SAFETY #
max_connections = 250
# max-allowed-packet             = 16M
# max-connect-errors             = 1000000

# CACHES AND LIMITS #
tmp_table_size = 512M
max_heap_table_size = 512M
join_buffer_size = 384K
query_cache_size = 0
query_cache_type = 0
# thread-cache-size              = 32
# open-files-limit               = 65535
# table-definition-cache         = 4096
# table-open-cache               = 1024

table_definition_cache = -1
performance_schema = ON

# INNODB #
innodb_lock_wait_timeout = 600
innodb_flush_method = O_DIRECT
innodb_buffer_pool_size = 10G
innodb_log_file_size = 1G
innodb_log_buffer_size = 64M
innodb_flush_log_at_trx_commit = 2
innodb_stats_on_metadata = 0
innodb_buffer_pool_instances = 10
innodb_file_per_table = 1

#LOG QUERIES#
# long_query_time                = 3
# log_slow_queries               = 1
# slow_query_log_file            = /var/lib/mysql/mysql-slow.log

[mysqld]
# Skip reverse DNS lookup of clients
# skip-name-resolve

I just made the exprience, that as soon as join_buffer_size exceeds 512k, suddenly the CPU load goes up to 100%. How can this be explained?

Please try the experiment again; this time capture these for the two values of join_buffer_size:
 EXPLAIN SELECT ... (including the query)
 how long the query took
 SHOW CREATE TABLE ...  -- of reach table in the query

Meanwhile, I will guess at this explanation.  The Optimizer has several ways it might perform a query.

Ignore the join_buffer and use lookups (indexed or not) for joining.
Use the join_buffer:  Load the second table completely into a hash in RAM, then reach into it for lookups.

The potential problem include

The Optimizer may not have the "right" statistics for deciding between the two choices.  (There is no reliable way to fix this for all cases.)
There may be a LIMIT or something else that makes loading the entire table the "wrong" approach.
Once the join_buffer is loaded, the rest of the query is CPU-bound.  When not using the join_buffer, the query may be I/O-bound and is likely to take longer (elapsed time).  (Hence my request for the timings.)
Etc.


how can I insert a long string into a blob using an insert statement? ORA-06502: PL/SQL: numeric or value error: raw variable length too long
  ORA-06512: at "SYS.UTL_RAW", line 224

the datatype is BLOB

here's my insert statement

insert into system_value (type, version, id, date_created, date_modified, value )
values ( 0, 0, 0, ${liquibase.db.datatype.timestamp.value}, ${liquibase.db.datatype.timestamp.value},
    utl_raw.cast_to_raw('...really long string'))

It looks to me like the string you're passing in is too long for the UTL_RAW procedure you're trying to call. From the Oracle documentation:

  There is an error if the sum of the lengths of the inputs exceeds the maximum allowable length for a RAW, which is 32767 bytes.

found here: http://docs.oracle.com/cd/E11882_01/appdev.112/e40758/u_raw.htm#ARPLS072

I think you may need to utilize the DBMS_LOB package. Documentation found here: http://docs.oracle.com/cd/B28359_01/appdev.111/b28419/d_lob.htm#BABDDFDH

HTH.


Insert Row With Foreign Key Equal to ID - Circular Reference In MySQL database I have an events table which has a circular reference to itself with the following columns.

id (PK) | user_id (FK) | event_id (FK) | title

Each new row inserted has a circular reference to another row in the table. The problem is how do I create the first event for each user?

I have tried manually inserting the IDs so i know it is possible to have an event reference itself. For example if i send an explicit query with id = 1, user_id = 1, event_id = 1, title = test then the row is added fine and then adding other events for that user is then straightforward.

The problem is that this solution is only appropriate for testing. In production, when a user wants to create their first event, how can I construct a query so it sets the event_id to the same value as the new auto increment value on the id column?

To clarify, the first event for a user will throw a foriegn key constraint error as I do not know the event_id that should be set.

As requested

CREATE TABLE `events` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `user_id` int(10) unsigned NOT NULL,
  `event_id` int(10) unsigned NOT NULL,
  `title` varchar(200) NOT NULL,
  PRIMARY KEY
    (`id`),
  UNIQUE KEY `events_event_id_title_unique`
    (`event_id`,`title`),
  KEY `events_user_id_foreign`
    (`user_id`),
  CONSTRAINT `events_event_id_foreign`
    FOREIGN KEY (`event_id`)
    REFERENCES `events` (`id`) ON DELETE CASCADE,
  CONSTRAINT `events_user_id_foreign`
    FOREIGN KEY (`user_id`)
    REFERENCES `users` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB
  AUTO_INCREMENT=14
  DEFAULT CHARSET=utf8 ;

Solution 1:

Make the event_id nullable.

ALTER TABLE events
  MODIFY COLUMN event_id int unsigned NULL ;

This will allow you to insert rows which reference nothing (NULL):

INSERT INTO events
  (user_id, event_id, title)
VALUES
  (a_valid_user_id, NULL, 'test event 1') ;

And to see events without a "parent" event:

SELECT *
FROM events
WHERE event_id IS NULL ;

Test at SQL-Fiddle-1

(Parenthesis)

Unless you have some braindead ORM that requires all tables to have a column named id, it's better to have names that describe what they represent. I'd rename id to event_id and event_id to parent_event_id:

CREATE TABLE events (
  event_id int(10) unsigned NOT NULL AUTO_INCREMENT,
  user_id int(10) unsigned NOT NULL,
  parent_event_id int(10) unsigned NULL,
  title varchar(200) NOT NULL,
  PRIMARY KEY (event_id),
  --- ...
) ;


Oracle 11g PGA advice view is empty when it shouldn't I have the following situation in an Oracle 11g server (it's part of a RAC). PGA_AGGREGATE_TARGET is set, STATISTICS_LEVEL is TYPICAL, and yet V$PGA_TARGET_ADVICE shows up empty. Why does this happen?

Also, the 'maximum PGA allocated' parameter, being more than PGA_AGGREGATE_TARGET, seems to indicate there's room for tuning. Correct?

SQL> show parameter STATISTICS_LEVEL

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
statistics_level                     string      TYPICAL

SQL> select * from V$PGA_TARGET_ADVICE;

no rows selected

SQL> show parameter PGA_AGGREGATE_TARGET

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
pga_aggregate_target                 big integer 23G

SQL> select * from V$PGASTAT where name='maximum PGA allocated';

NAME                                                                  VALUE UNIT
---------------------------------------------------------------- ---------- ------------
maximum PGA allocated                                            3.6542E+10 bytes

SQL>

Bug 11872813 - V$PGA_TARGET_ADVICE VIEW IS EMPTY.

  After setting PGA_AGGREGATE_TARGET > approximately 12G the
  V$PGA_TARGET_ADVICE view is empty.
  This issue will be fixed in version 12.1 and a fix is expected to be included  in patch set
  11.2.0.4.

  Apply Patch 11872813


Authentication type differences in SQL Server I want to know what is the difference between authentication types(NONE, WINDOWS, INSTANCE) in
sys.database_principals table.
I can't find clear explanation in documentation.

Thanks

instance means this is a sql login, while windows means this is Windows login, and none means it does not need authentication (such as database role).

BTW, the two columns are in sys.database_principals


Why did creating this new index improve performance so much when existing index included all columns in new index? I have Log and LogItem tables; I'm writing a query to grab some data from both. There are thousands of Logs and each Log can have up to 125 LogItems

The query in question is complicated so I'm skipping it (if someone thinks it's important I can probably post it), but when I ran SSMS Estimated Query plan, it told me a new Non-Clustered index would improve performance up to 100%.

Existing Index: Non-clustered
Key Colums (LogItem): ParentLogID, DateModified, Name, DatabaseModified

Query Plan Recommendation
CREATE NONCLUSTERED INDEX [LogReportIndex]
ON [dbo].[LogItem] ([ParentLogID],[DatabaseModified])

Just for fun, I created this new index and ran the query and much to my surprise, it now takes ~1 second for my query to run, when before it was 10+ seconds.

I assumed that my existing index would cover this new query, so my question is why did creating a new index on the only columns used in my new query improve performance? Should I have an index for each unique combination of columns used in my where clauses?

note: I don't think this is because the SQL Server is caching my results, I ran the query about 25-30 times before I created the index and it consistantly took 10-15 seconds, after the index it is now consistantly ~1 or less.

Order of columns in an index is important. If filtering requires column 1 and 4 from index, the index is not going to help. It's only useful when filtering by the first N consecutive columns.

This is because index is a tree. You can't efficiently select all nodes of the tree where column3 = something, because they are scattered all other the place, belonging to different values of column1 and column2. But if you know column1 and column2 as well, locating the right branch in the tree is a no brainer.


SQL Server differential backup LSN values I'm looking at restoring databases on SQL Server and I'm not understanding something related to the LSNs. Here is a screenshot from the Restore Database dialog in Management Studio for a sample database I created.

You'll notice that there are two differential backups. If each differential backup contains the data for the transactions of all the previous differential backups, why would the FirstLSN of the second differential backup (22000000041800004) be higher than the FirstLSN of the first differential backup (22000000039800037)? If it covers the same transactions why wouldn't the LSNs overlap?

If you look at Microsoft's documentation on this it states

  For data and differential backups, the first LSN identifies the earliest log record that is needed to perform recovery with this backup.

So if I am going to recover the database using the second differential backup, wouldn't it stand to reason that the second differential backup's FirstLSN would start where the full backup left off (22000000038800001)? Restoring the second differential would apply all the transactions that occurred after the full backup was created wouldn't it?

I'm not understanding how the system accounts for the LSN gap between the full backup and the second differential.

why would the FirstLSN of the second differential backup
  (22000000041800004) be higher than the FirstLSN of the first
  differential backup (22000000039800037)?

When dealing with Data (Full, file, etc) or differential version of said backups the FirstLSN and LastLSN actually mean something different than is being inferred.

FirstLSN when dealing with the differentials means that's the LSN which is needed in order start recovery with that backup (meaning that's where it's going to start the process, at that lsn).

LastLSN when dealing with the differentials means that's the LSN at which any subsequent restore operations (in this case, your log backups) need to start. If you wanted to apply any extra logs, for example, the log backup that can first be applied needs to hold the LastLSN value in it in order to be applied.

However, this definition changes when dealing with the log backups which would be your original thought on the matter.

Edit: Just found this, which may help.

Edit: Updated question so updating answer.

  So if I am going to recover the database using the second differential
  backup, wouldn't it stand to reason that the second differential
  backup's FirstLSN would start where the full backup left off
  (22000000038800001)?

It's a differential, so it only holds what little log it needs to be transactionally consistent. It wouldn't have to be that, as that's not the firstLSN where it is starting recovery. This differential could be from a full backup base taken 1.5 years ago (please, please don't let it go that long between fulls) and would not have any LSNs in most likely the last 1.5 years in it. It'll have all of the changed extents, but no log transactions (again, it'll have a tiny amount to be consistent). It's just telling you that it's going to start it's recovery process at that LSN.

  Restoring the second differential would apply all the transactions
  that occurred after the full backup was created wouldn't it?

It would restore ALL of the extents that were changed since the last full backup. These may or may not reflect actual changes. For example, a large insert statement that put 1000 rows into an empty table that was subsequently rolled back. The extents allocated to that table, while nothing would be in there since it was rolled back, will still be apart of the differential as they were changed. All the differential does (well, not all but mostly) is look a the DCM and backup those extents, figure out how much log it needs and presto you have a differential backup. So it won't apply the transactions (as in replaying them) but it will hold whatever their consequences are.

  I'm not understanding how the system accounts for the LSN gap between
  the full backup and the second differential.

It's not really a gap, it holds everything that has changed since that time (not as transactions but as what extents were changed) and then a little log. The "gap" is the changed extents part and what "makes up for it" is the little bit of log in the differential backup to make it consistent.

  But why do full and differential backups need to store the LSN values?

They need to know information about themselves as well. For example, assume that a full backup was lost but you have a differential based on the newer full backup and you have an older full backup. You could restore the original old backup, logs, then the differential when you finally got to where it needs to be and continue on... but how would you know where that needed to be if you didn't store the values somewhere? How would we as admins know what order items came in if we didn't have these values?


Your query as it is will always return 0 rows . PROF field cannot be equal to 3 different values at the same time .
You need something similar to

select dno
from employe
where prof in ('Ingenieur','Technicien','Vendeur')
group by dno
having count(distinct prof) =3 ;

There are couple more ways to get what you want, you may try them and find which one has better performance .


Redundant FK on parent relationship is denormalization? Let's say I have this database

client
| id | status |

sale
| id | client_id | date |

sale_item
| id | sale_id | *client_id* |

The *client_id* column is redundant, because we can JOIN and get client from sale_item rows. However, if I'd often need to list sales by client, that JOIN could be expensive.

Is this considered denormalization? Or does it have another technical name?

In my understanding the decision to keep *client_id* is about space x speed. Consistency problems introduced by common denormalization examples doesn't apply here because id will never change neither be deleted (if we need to "remove" we can just set status=0).

Yes, it is denormalization. Any time you're copying data from one location in a database to another location (where the data could easily be obtained from its original location), it's denormalization by definition.

And, improving performance is a perfectly good reason to denormalize. That said, unless you have a huge number of clients, or the client_id is the only data from the sale table that you would normally need, then it shouldn't be a major concern. It's entirely reasonable to cause this particular instance of denormalization "cascading key structure", or anything else appropriate. Doesn't mean it's not also denormalization.

And, with respect to not having consistency issues - never say never....


SQL Server 2005 get execution plan of a overnight stored procedure There is a overnight job runs long time. I have used profiler to get the trace and identified a statement (in a stored procedure) that takes most of execution time. Now I need to see the execution plan of the statement so I can see what I can do. But I can't rerun the query since it uses a temp table created in the proc. And the logic of the proc is rather complex and very hard to reproduce (involving getting data from file system and data exchanging with Oracle database via linked server). I am not saying reproducing is not possible but would be rather time consuming. So is there a way I can see the execution plan of the proc or the statement in particular?

So is there a way I can see the execution plan of the proc or the statement in particular?

You have to refer to DMV's for extracting such information. sys.dm_exec_cached_plans, sys.dm_exec_sql_text and sys.dm_exec_query_plan are the ones to look into.

Especially sys.dm_exec_query_plan DMF will return the plan for a given batch or procedure (along with the "subplans" for each query comprising the batch).

Note: Restarting sql server will flush out all dmv data.

Example :

-- Do not run it in PROD !!!!
-- free up the procedure cache for TESTING ONLY !!
dbcc freeproccache

-- create an SP with different queries
create procedure usp_Kin_Test
as
select name, create_date from sys.objects
where type = 'P'
order by create_date desc

select name, OBJECT_ID from sys.procedures
order by name

-- execute the query
exec usp_Kin_Test

--- Check the DMV's for cached query plan

SELECT deqp.dbid
    ,deqp.objectid
    ,CAST(detqp.query_plan AS XML) AS singleStatementPlan
    ,deqp.query_plan AS batch_query_plan
    ,ROW_NUMBER() OVER (
        ORDER BY Statement_Start_offset
        ) AS query_position
    ,CASE
        WHEN deqs.statement_start_offset = 0
            AND deqs.statement_end_offset = - 1
            THEN '-- see objectText column--'
        ELSE '-- query --' + CHAR(13) + CHAR(10) + SUBSTRING(execText.TEXT, deqs.statement_start_offset / 2, (
                    (
                        CASE
                            WHEN deqs.statement_end_offset = - 1
                                THEN DATALENGTH(execText.TEXT)
                            ELSE deqs.statement_end_offset
                            END
                        ) - deqs.statement_start_offset
                    ) / 2)
        END AS queryText
FROM sys.dm_exec_query_stats deqs
CROSS APPLY sys.dm_exec_text_query_plan(deqs.plan_handle, deqs.statement_start_offset, deqs.statement_end_offset) AS detqp
CROSS APPLY sys.dm_exec_query_plan(deqs.plan_handle) AS deqp
CROSS APPLY sys.dm_exec_sql_text(deqs.plan_handle) AS execText
WHERE execText.TEXT LIKE '%usp_Kin_Test%' -- CHANGE here for your stored procedure !!

Note : You can do many cool things by digging into Plan cache as described by Jonathan here. Also refer to DMVs for Query Plan Metadata


Stale monitoring errors? After setting a cluster with 3 shards, 3 config server with 2 mongos.

I see these errors in the monitoring agents    Failure during discovery.

What do they mean I tried to see it on the internet but no luck.

The answer came in with adding a documents into the seetings collection inside the config database.

syntax:

use config

show collections

db.settings.insert({ "_id" : "chunksize", "value" : NumberLong(64) })

db.setting.find()           ---#to check

This worked for me.


Change the same field from all the tables from a particular database Suppose I have a database sitedb and in that I have 20 tables and a primary key(say uid) for one table is used in other tables as dependencies.
uid is of type int with length 10. Lets say that the number of entries in the table is full and I need to increase the capacity of the uid to the length 11.
Is it right to do so? What will happen to the related tables and how will it affect the relation?
What if I change the value of uid in all the tables to length 11. Is there a tool which will allow me to check and change the value of every related uid in all the tables in the database? Updates?

Modify column by name(s):
you can also use any SQL Where, such as column_name in (...) or using other columns in
 information_schema.columns table.

select  concat('alter table ',table_schema,'.',table_name,' change column ... ;') from information_schema.columns  where column_name='r_id'

use group_concat or similar and just copy/paste.


Postgesql non-deterministic order of result with LIMIT and ORDER BY I understand that Postgresql may return results in non-deterministic order when the query parameters are non-unique. This is especially a problem when we use LIMIT since this may return entirely different results. So the suggestion for safety is to use ORDER BY with LIMIT.

However, what if we use ORDER BY on an non-unique column name? Will this produce a deterministic result or no? Let me demonstrate with an example:

We have the following data:

id | name | index
1    foo     5
1    bar     2
1    test    5

If I query SELECT * FROM <table> WHERE id = 1 ORDER BY index LIMIT 1;. Will this produce the same result every time?

If there are multiple rows with the lowest value for index, then no, there is no guarantee you will always get the same row. If you only specify ordering for a column that contains duplicates, then the database does not have to provide anything more than any row that meets the criteria.

Same would be true if you were to say ORDER BY index DESC - which row with 5 would you expect? Why?

In order to force determinism / predictability, add a tie-breaker on a column that is unique (or at least unique within the same values of index). With your sample data, you could say:

ORDER BY index, name LIMIT 1;

But you haven’t shared enough about your table structure and constraints to get a more specific suggestion than that.


History of actions that occured on a table in SQL Server I am running a shell script that runs insert, update, delete queries on a table in an SQL Server DB. Is there a way to get a history of all transactions that happened on the table to validate that they occurred as expected. That is the number of updates, inserts and deletes that happened. And any errors that could have occurred.

Here's some out-of-the-box features of SQL Server that can help you accomplish your goal. You'll need to proactively enable / set one of them up before they collect the data you need.
For reference:

Triggers - Fire whenever data changes in a table, can implement logic similar to being in the context of a stored procedure.

Temporal Tables - System versioned copies of the user defined tables that track changes.

Change Tracking - Automated tracking of changes for your user defined tables.

Change Data Capture - Keeps track of DML changes to the specified user defined tables.

Audit - Automatically tracks a multitude of actions at the server level.


Postgres WAL logs-- point in time recovery with (time) gaps in the logs? I'm setting up continuous archiving/ point in time recovery on Postgres and I've noticed some odd behavior.  Here's the sequence of events:

Save a base backup
Archive WAL logs during normal DB operation
Turn off the database for a little while
Turn on database
Archive WAL logs during normal DB operation
Run a complete restore

I expected to recover all the data, including that which occurred after the database downtime.

What I'm observing is that only data from before the downtime is being restored.  In fact, Postgres never even asks for/looks for the WAL logs from after the downtime.

(To be clear, there is no missing data, just a period of time in which the DB was not running and thus no WAL logs were shipping.)

Is this the intended behavior?  I was unable to find any info on the specific logic by which Postgres decides which WAL logs to apply.

If so, does that mean a new base backup should be taken after any DB downtime?

PostgreSQL does not care when the WAL logs are generated, it just reads them sequentially on restore/recovery.

If the WAL logs were properly sent after the restart of the database, they should be restored as well.

However, if there is even a single WAL file missing for some reason (in this example, I'd guess that'd be either the last segment before the shutdown or one or more of the first ones from after the startup), which is typically either an incorrectly configured archive_command (missing error checks for example) or simply a broken archive server, the restore process cannot go past that point.


How to avoid Table Lock Escalation? I've got a task to update 5 million rows in a production table, without locking down the whole table for extended time
So, I used approach that helped me before many times - updating top (N) rows at a time with 1-N second interval between chunks
This time started with update top (1000) rows at a time, monitoring the Extended Events session for lock_escalation events in the process
lock_escalation showed up during each update operation, so I started lowering row count per chunk 1000 -> 500 -> 200 -> 100 -> 50 rows and so on down to 1
Before (not with this table, and for delete operations - not update), lowering row count to 200 or 100, helped to get rid of lock_escalation events
But this time, even with 1 row per 1 update operation, table lock_escalation still shows up. Duration of each update operation is about the same, regardless if its 1 row or 1000 rows at a time
How to get rid of table lock escalations in my case ?
@@TRANCOUNT is zero
Extended event:

Code:
set nocount on

declare
    @ChunkSize              int = 1000,                         -- count rows to remove in 1 chunk
    @TimeBetweenChunks      char(8) = '00:00:01',               -- interval between chunks

    @Start                  datetime,
    @End                    datetime,
    @Diff                   int,

    @MessageText            varchar(500),

    @counter                int = 1,
    @RowCount               int = 1,
    @TotalRowsToUpdate      bigint,
    @TotalRowsLeft          bigint

-- total row count to update
set @TotalRowsToUpdate = (select count(*)
                            from [Table1]
                                join [Table2] on
                                    btid = tBtID
                            where   btStatusID = 81)

set @TotalRowsLeft = @TotalRowsToUpdate
set @MessageText = 'Total Rows to Update = ' + cast(@TotalRowsLeft as varchar) raiserror (@MessageText,0,1) with nowait
print ''

-- begin cycle
while @RowCount > 0 begin

    set @Start = getdate()

    -- update packages
    update top (@ChunkSize) bti
        set btstatusid = 154,
            btType = 1
    from [Table1] bti
        join [Table2] on
            btid = tBtID
    where   btStatusID = 81

    set @RowCount = @@ROWCOUNT

    -- measure time
    set @End = getdate()
    set @Diff = datediff(ms,@Start,@End)

    set @TotalRowsLeft = @TotalRowsLeft - @RowCount
    set @MessageText = cast(@counter as varchar) + ' - Updated ' + cast(@RowCount as varchar) + ' rows in ' + cast(@Diff as varchar) + ' milliseconds - total ' + cast(@TotalRowsLeft as varchar) + ' rows left...'

    -- print progress message
    raiserror (@MessageText,0,1) with nowait

    set @counter += 1

    WAITFOR DELAY @TimeBetweenChunks

end

Plan:
https://www.brentozar.com/pastetheplan/?id=SyozGWMLw

If we look at the actual plan, the current query is reading too much data from the table to be updated.  This is from the index seek on BoxTrackInfo:

This is an index seek on btid for each row that comes out of the scan of BlueTrackEvents.  Update locks are acquired as btStatusID is checked to see if the row qualifies for the update.  Only 1,401 rows qualify for the update, but many more locks are taken in the process - resulting in lock escalation to the table level.
You really want a different plan shape - to seek into the BoxTrackInfo table on btStatusID and then join to BlueTrackEvents, which should acquire significantly less locks.  To that end, adding an index like this should help:
CREATE NONCLUSTERED INDEX IX_btStatusID
ON dbo.BoxTrackInfo (btStatusID)
INCLUDE (btType);

This should more efficiently locate qualifying rows, hopefully allowing the update to complete without lock escalation.

As a side note, the current execution plan validates the foreign key constraint on btStatusID using a merge semi-join:

This is probably not a big deal in your case, since there are only 267 rows in the LBoxTrackStatus table.  If that table were larger, you might consider adding a LOOP JOIN or FAST 1 hint to the query in order to get nested loops FK validation.  See this post for details:
Why am I getting a snapshot isolation issue on INSERT?


Is updating with value using a subquery bad in performance? I have made an update query in the following form:

update table_a
set some_column = (
    select very_complicated_expression_involving_multiple_columns_in_table_b
    from table_b
    where table_a.id = table_b.id
)

If table_a is large, will the database engine loop the select query for each row in table_a? Should I rewrite this to an update from statement?

Assuming Sql Server as your DBMS:

The following two statements are NOT equivalent:

update table_a --will update some_column to null when no match to table_b on id
set some_column = (
    select very_complicated_expression_involving_multiple_columns_in_table_b
    from table_b
    where table_a.id = table_b.id
)

UPDATE a --will not update table_a rows that don't match table_b on id
SET a.some_column = very_complicated_expression_involving_multiple_columns_in_table_b
FROM table_a a
JOIN table_b b ON b.id = a.id

The first UPDATE will update some_column to NULL if there is no match between table_a and table_b on id.

The second UPDATE won't update any table_a rows where there is no match between table_a and table_b on id.

Having said that, and back to your original question about performance, I would think the optimizer would NOT choose any kind of LOOP JOIN when there are so many rows involved in the update (assuming your statistics are up to date).  Evaluating different estimated execution plans for different flavors of the update will help you decide which is the most efficient.

There are other things that also need to be considered when updating a large table.

An update against a large table will generated a lot of transaction log entries.  You should consider breaking up the update into smaller chunks to reduce the possibility of running out of log space.  If you're running in FULL recovery mode, make sure you are taking log backups in a timely manner.  You would need to determine a method of identifying rows that have already been updated in previous runs.  Break large delete operations into chunks is a post about breaking up large deletes, but I think the idea can be applied to updates.
If some_column is referenced in any indexes on table_a, you should consider dropping/disabling them.  Naturally, they will have to be rebuilt afterwards.
Check for any UPDATE triggers on table_a that might impact performance.


Downgrade database from SQL Server 2012 to 2008 Hi guys got a problem with a database.

I've been asked to perform some indexing and delete queries on a live database, so first of I wanted to get the database to our dev server so I can test out some things before I commit to live.

Unfortunately the dev is SQL Server 2008 and the live is SQL Server 2012. So I've had to try and downgrade the database.

Database is a lovely 33gig. and I've started to use the generate script wizard to create a 2008 version.

Problem is it gets so far then errors on random tables. So far I have no idea what to do to solve the issue. In the advanced settings of the wizard I select schema and data (yup going to be huge)

Error is due to my pc running out of disk space. I have a problem, how do I recreate this DB in 2008 that's both fast and easy? generating the script like that hasn't worked.

Open to ideas guys!

One option is third party tools: Red Gate's SQL Compare and SQL Data compare are very good for this sort of thing, and what we use internally. They will let you handle the data table by table, so that might help keep size down. Not sure if they any upper limits on size.


"Set Password" with a limited user I'm using MySQL 5.6. I want to have a limited service-user which is able to create/modify the users of my databases.

Now my problem is that I can quite figure out what privileges my service-user needs to perform all the user administration tasks (create user, grant/revoke db privileges, set password)

If I give him the global "create user" and "reload" privileges, he can't use "set password" [= MySQL Error: 1044 (Access denied)]
If I give him "select", "insert", "update" on 'mysql'.'user' and 'mysql'.'db', he can't use "set password" [= MySQL Error: 1044 (Access denied)]
If I give him "select", "insert", "update" on 'mysql'.*, he CAN use "set password"

I'd like to understand why this is happening, and how to achieve my approach 1 or 2. I don't want to use approach 3.

The "set password" equivalent

UPDATE mysql.user
SET Password=PASSWORD('newpass')
WHERE User='bob'
AND Host='%';

...works with my privileges 1 and 2.

Can somebody help me out?

The problem I see with what you propose is that a user who has the ability to grant privileges to other users... is, almost by definition, not a "limited" user.

You can't grant privileges at all without the GRANT privilege, and even with that, you can't grant a specific privilege that you don't possess ... unless you have permission to manipulate the grant tables directly, in which case, you're not exactly a limited user either.

But, aha, here's your workaround.

Stored procedures run with the credentials of the user who defined them or as the explicitly-specified definer.  (You have to have SUPER to specify somebody else as DEFINER.)  Any user with the EXECUTE privilege on a stored procedure can execute the procedure, and the procedure essentially escalates their privilege level while it is running.

If you wrap your administrative operations in (well-written) procedures, then you don't have to actually give the limited user permission to do anything, other than run the procedures.

DELIMITER $$

DROP PROCEDURE IF EXISTS `mysql`.`change_password` $$
-- root@localhost is an example; use an appropriate local user that has the
-- permissions that need to be available for the operation to succeed
CREATE DEFINER='root'@'localhost' PROCEDURE `mysql`.`change_password` (
  IN dirty_user VARCHAR(16),
  IN dirty_host VARCHAR(40),
  IN dirty_password VARCHAR(41))
BEGIN

  DECLARE encrypted_password TINYTEXT DEFAULT PASSWORD(dirty_password);
  DECLARE clean_user TINYTEXT DEFAULT NULL;
  DECLARE clean_host TINYTEXT DEFAULT NULL;

  SELECT user, host
    FROM mysql.user
   WHERE user = dirty_user
     AND host = dirty_host
    INTO clean_user, clean_host;

  IF clean_user IS NULL OR clean_host IS NULL THEN
    SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'user/host provided does not exist';
  END IF;

  SET @_sql = CONCAT_WS('\'','SET PASSWORD FOR ',clean_user,'@',clean_host,' = ',encrypted_password,'');

  PREPARE stmt FROM @_sql;
  EXECUTE stmt;
  DEALLOCATE PREPARE stmt;
  SET @_sql = NULL;

END $$

DELIMITER ;

GRANT EXECUTE ON PROCEDURE mysql.change_password TO 'limited'@'%';

The 'limited'@'%' user can now change passwords for other users even though they don't have permission to do it themselves, by using CALL mysql.change_password('user','host','password');.

This user does not have permissions themselves, but does have execute on the proc; they can't do it directly:

mysql> set password for 'wombat'@'localhost' = PASSWORD('secret');
ERROR 1044 (42000): Access denied for user 'limited'@'%' to database 'mysql'

... but they can do it this way.  Note that "0 rows affected" is not a meaningful value.

mysql> call mysql.change_password('wombat','localhost','secret');

Query OK, 0 rows affected (0.00 sec)

Notes:

String-concatenated queries with input parameters are unacceptable, but the SET PASSWORD statement does not accept ? positional-parameters, so there's no choice here.  To sanitize the inputs, I take care of the password by encrypting it in advance, outside the prepared statement, and concatenated the encrypted password there; I've sanitized the user and host by actually selecting them from the mysql.user table and using the values I've selected, also outside the prepared statement.  If they aren't found, we can't set their password, so we throw an exception using SIGNAL.  If they are found, we concatenate the fetched values into the prepared statement, sanitizing them by this mechanism.  It's still theoretically possible that bad data in the mysql.user table could render the quoting of the prepared statement invalid, but if you have bad data in the mysql.user table, then you have 2 problems (1 problem in addition to this one).
Any explicit GRANT EXECUTE you've given at the procedure level disappears from the mysql.procs_priv table if you drop and redefine the procedure, so if you make changes to the procedure, you have to re-grant the privileges to the limited user.
You need MySQL 5.5+ for the SIGNAL statement to be valid.  Below 5.5 you can replace it with a hack, something like CALL mysql.`change_password(): the user/host provided`; (note the backticks) which will throw a not quite as pretty, but still serviceable message complaining that the bogus procedure name quoted in the backticks... does not exist.  Heh... ERROR 1305 (42000): PROCEDURE mysql.change_password(): the user/host provided does not exist


SQL Server Select line where jobCategoryKey EXISTS once for one candidateKey I'm blocked with an SQL Query that I have to made on SQL Server 2014.

Here is my table TR_Candidate_To_Jobs_Category.

CREATE TABLE [dbo].[TR_Candidate_To_Jobs_Category]
       ( [candidateKey] [int] NOT NULL,
         [jobCategoryKey] [int] NOT NULL,
          CONSTRAINT [PK_TR_Candidate_To_Jobs_Category] PRIMARY KEY CLUSTERED
            ( [candidateKey] ASC, [jobCategoryKey] ASC )
            WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
       ) ON [PRIMARY]

INSERT [dbo].[TR_Candidate_To_Jobs_Category] ([candidateKey], [jobCategoryKey]) VALUES (2, 223)
INSERT [dbo].[TR_Candidate_To_Jobs_Category] ([candidateKey], [jobCategoryKey]) VALUES (2, 1245)
INSERT [dbo].[TR_Candidate_To_Jobs_Category] ([candidateKey], [jobCategoryKey]) VALUES (2, 225)
INSERT [dbo].[TR_Candidate_To_Jobs_Category] ([candidateKey], [jobCategoryKey]) VALUES (5, 137)
INSERT [dbo].[TR_Candidate_To_Jobs_Category] ([candidateKey], [jobCategoryKey]) VALUES (5, 142)
INSERT [dbo].[TR_Candidate_To_Jobs_Category] ([candidateKey], [jobCategoryKey]) VALUES (5, 143)
INSERT [dbo].[TR_Candidate_To_Jobs_Category] ([candidateKey], [jobCategoryKey]) VALUES (6, 1245)
INSERT [dbo].[TR_Candidate_To_Jobs_Category] ([candidateKey], [jobCategoryKey]) VALUES (6, 223)
INSERT [dbo].[TR_Candidate_To_Jobs_Category] ([candidateKey], [jobCategoryKey]) VALUES (6, 225)

I have to find all the candidates which have the jobCategoryKey = 1245 and only this one. So they may not have one or more other jobCategoryKey or zero jobCategoryKey.

I tried with HAVING, GROUP BY and so on, but I didn't find the right solution.

All the proposed solutions using COUNT may prove to be inefficient as the aggregates will most likely need to be computed for the entire set, unless the optimizer will be able to push the predicate down. There is a more natural solution which IMHO is much clearer, and typically more efficient. It involves wording the question in negative form:

  "Show me all candidates for category 1245, who have no other
  association with any other category".

This translates to SQL immediately:

SELECT *
FROM Candidates_to_Jobs AS C1
WHERE jobCategoryKey = 1245
      AND
      NOT EXISTS (
                  SELECT NULL
                  FROM Candidates_to_Jobs AS C2
                  WHERE C1.CandidateKey = C2.CandidateKey
                        AND C2.jobCategoryKey <> 1245
                 );


datetime fractional seconds I am reading the description of the datetime datatype from Sybase ASE 15.7 documentation:

  datetime columns hold dates between January 1, 1753 and December 31, 9999. datetime values are accurate to 1/300 second on platforms that support this level of granularity.

I find the above very confusing. First of all, this is the only data type for which the caveat "on platforms that support this level of granularity" is added. What exactly does this mean and how do I find if my platform is one of those that "support this level of granularity"?

Moreover, it is not clear to me what being able to accurately store 1/300ths of a second effectively means. I am accessing the database using JDBC for which the only plausible type is java.sql.Timestamp. This type allows me to retrieve up to the precision of nanoseconds. But given that division by 300 requires infinite digits in the decimal system in the general case, effectively a fractional number of nanoseconds (with infinite decimal digits) are needed to hold a value expressed as 1/300 of a second. So, this means that I am not able to obtain the value stored in the server without losing some precision, however negligible.

Finally, when I execute the following query:

SELECT convert(char(32), submission_date, 139) FROM some..table

I see values like the following:

Jan  6 2014 12:36:12.420000
Sep 12 2013 13:44:57.100000
Sep 10 2014 13:47:02.240000
Sep 10 2014 13:47:07.850000
Sep 10 2014 13:47:13.346000
Sep 10 2014 13:47:19.033000
Sep 10 2014 13:47:24.533000
Sep 10 2014 13:47:30.030000
Sep 10 2014 13:47:35.636000
Sep 10 2014 13:47:41.136000
Sep 10 2014 13:47:46.750000
Sep 10 2014 13:47:52.240000
Sep 25 2014 09:01:18.426000

These values seem to indicate that only whole thousandths of a second are kept (not 1/300ths of a second - which would require a fractional number of thousandths). If it were the case that the server internally stores the values "accurately to 1/300 second" I would expect the conversion to a decimal notation to use all available decimal places (except for the edge cases of 3/300 secs, 30/300 secs, 150/300 secs and a few others which do not require an infinite amount of digits in the decimal system).

datetime in Adaptive Server Enterprise (and SQL Server, since they share a common-code-base that included the datetime type) is stored using 8 bytes.  4 bytes for the date, and 4 bytes for the time.  You can see this by looking at the binary version of a datetime:

SELECT CONVERT(varbinary(8), CONVERT(datetime, '1753-01-01T00:00:00.000'), 0)
SELECT CONVERT(varbinary(8), CONVERT(datetime, '1900-01-01T00:00:00.000'), 0)
SELECT CONVERT(varbinary(8), CONVERT(datetime, '1900-01-02T00:00:00.000'), 0)
SELECT CONVERT(varbinary(8), CONVERT(datetime, '3000-12-31T23:59:59.997'), 0)

The four binary values are:

0xFFFF2E4600000000
0x0000000000000000
0x0000000100000000
0x000622D3018B81FF

Take the following, which shows where the 1/300 of a second comes into play:

SELECT CONVERT(varbinary(8), CONVERT(datetime, '1900-01-01T00:00:00.000'), 0)
SELECT CONVERT(varbinary(8), CONVERT(datetime, '1900-01-02T00:00:00.000'), 0)
SELECT CONVERT(varbinary(8), CONVERT(datetime, '1900-01-02T00:00:00.003'), 0)

The difference between the 2nd and 3rd values, is one:

0x0000000000000000
0x0000000100000000
0x0000000100000001

So dates are stored in the most significant 4 bytes; and times are stored in the least significant 4 bytes.  Although moving to a precision greater than 3 milliseconds (1/300 of a second) would be possible in 4 bytes of storage; that is all the precision that is actually used.

In SQL Server, you can use a datetime2(7) data type to get precision down to 7 digits, with an accuracy of 100ns:

SELECT CONVERT(varbinary(16), CONVERT(datetime2(7), '1900-01-01T00:00:00.0000000'), 0)
SELECT CONVERT(varbinary(16), CONVERT(datetime2(7), '1900-01-01T00:00:00.0000001'), 0)
SELECT CONVERT(varbinary(16), CONVERT(datetime2(7), '1900-01-01T00:00:00.0000002'), 0)

The storage of these values are slightly different, however you can still see the binary value incrementing:

0x0700000000005B950A
0x0701000000005B950A
0x0702000000005B950A

I'm using the Sybase ISQL client; Sybase CTISQL Utility/15.7/P-EBF20996 SP100/DRV.15.7.0.10

As an aside, a small difference exists between how Adaptive Server Enterprise (ASE) and SQL Server round datetime values.  In SQL Server, milliseconds are rounded to 0.000, 0.003, and 0.007, whereas in ASE they are rounded to 0.000, 0.003, and 0.006 - why there is a difference is not documented as far as I can tell.  You can see this on ASE by running this query:

SELECT CONVERT(varchar(50), CONVERT(datetime, N'2017-01-01T23:59:59.997'), 139);

Which returns:

Jan  1 2017 23:59:59.996000

Whereas on SQL Server, the equivalent code, SELECT CONVERT(varchar(50), CONVERT(datetime, N'2017-01-01T23:59:59.997'), 109); , returns:

Jan  1 2017 11:59:59:997PM


Conditional insert vs UNIQUE constraint https://stackoverflow.com/questions/4069718/postgres-insert-if-does-not-exist-already

I would like to check if something exists before inserting it, what should I rely on?

UNIQUE constraint or INSERT .. INTO .. WHERE NOT EXISTS .. or maybe both? Is there any other solution?

Is it possible to check if something exists in a separate query and inserting in a separate query in the way that it's atomic? Putting it in a transaction scope maybe?

It depends on what you are needing from the solution. If you want to 100% prevent duplicate errors you go with a constraint. If it is to minimize duplicate rows and prevent errors from happening you go with the conditional insert. If you need to prevent duplicate rows and the errors from attempting to insert a duplicate row you go with both.

If you are depending on the conditional insert you can get a duplicate row if someone is doing an insert and they do not add the condition as expected.

If you just use the constraint you will get errors that could be prevented (sometimes you want to know of the error) when attempting to insert the duplicate record.


Efficiently finding the ultimate parent in multi-level one-to-many tables Let's say I have a database with multi-level one-to-many tables (a contrived example):

CREATE TABLE users (
  id SERIAL PRIMARY KEY
);

CREATE TABLE properties (
  id SERIAL PRIMARY KEY,
  user_id INTEGER NOT NULL REFERENCES users(id)
);

CREATE TABLE rooms (
  id SERIAL PRIMARY KEY,
  property_id INTEGER NOT NULL REFERENCES properties(id)
)

CREATE TABLE rates (
  id SERIAL PRIMARY KEY,
  price INTEGER NOT NULL,
  room_id INTEGER NOT NULL REFERENCES rooms(id)
)

Let's say a user wants to update the price in rates table but I want to make sure that he is allowed to do so - i.e. the rate belongs to a room that itself belongs to a property that the user owns.

What would be the best/efficient way to do so? Is there a way to add a "deduced" user_id column to the rates table that is automatically populated as the rows are added to the rates table so later I can use it in a query like:

UPDATE rates SET price=100 WHERE (id=30) AND (user_id=1)`

I simplified a bit by allowing only one price for a given room in the rates table.

CREATE TABLE users (
  user_id INTEGER NOT NULL
, CONSTRAINT pk_users PRIMARY KEY (user_id)
);

CREATE TABLE properties (
  property_id INTEGER NOT NULL
, user_id     INTEGER NOT NULL
, CONSTRAINT pk_prop PRIMARY KEY (property_id)
, CONSTRAINT fk1_prop FOREIGN KEY (user_id)
                 REFERENCES users (user_id)
);

CREATE TABLE rooms (
  room_id     INTEGER NOT NULL
, property_id INTEGER NOT NULL
, CONSTRAINT pk_rooms PRIMARY KEY (room_id)
, CONSTRAINT fk1_rooms FOREIGN KEY (property_id)
             REFERENCES properties (property_id)
);

CREATE TABLE rates (
  room_id INTEGER NOT NULL
, price   INTEGER NOT NULL
, CONSTRAINT pk_rates PRIMARY KEY (room_id)
, CONSTRAINT fk1_rates FOREIGN KEY (room_id)
                  REFERENCES rooms (room_id)
);

Some data to test

INSERT INTO users (user_id)
VALUES (1), (2), (3) ;

INSERT INTO properties (property_id, user_id)
VALUES
(11, 1), (12, 1), (13, 1),
(21, 2), (22, 2),
(31, 3) ;

INSERT INTO rooms (room_id, property_id)
VALUES
(1101,11), (1102,11),
(1201,12), (1202,12),
(1301,13), (1302,13),
(2101,21), (2102,21),
(2201,22), (2202,22), (2203,22),
(3101,31), (3102,31), (3103,31) ;

INSERT INTO rates (room_id, price)
VALUES
(1101,110), (1102,110),
(1201,120), (1202,120),
(1301,130), (1302,130),
(2101,210), (2102,210),
(2201,220), (2202,220), (2203,220),
(3101,310), (3102,310), (3103,310) ;

Update price for room_id = 2102 to 100, if and only if it belongs to user_id = 2.

UPDATE rates      AS t SET price = 100
  FROM rooms      AS r
  JOIN properties AS p ON p.property_id = r.property_id
  JOIN users      AS u ON u.user_id     = p.user_id
 WHERE t.room_id = r.room_id
   AND r.room_id = 2102
   AND u.user_id = 2
;

Note that all joins are on PKs, so this is fast. Also there are no multiple trips to the app and back.

Try it on dbfiddle or visualise the explain!


Prevent mysql process being killed by OOM killer I need to protect mysql process so that it won't be killed when system runs out of memory, we have a bunch of adhoc job run on the server from time to time, so I would prefer the adhoc jobs being killed rather than mysql.

I know I can adjust the oom_score_adj, by setting it to -1000, I can exclude it from OOM killer's consideration. But I am not sure if I need to restart the mysql process for new oom_score_adj to take effect? Does adjust the oom_score_adj for a running process still takes effect?

I was following this post and nobody else mentioned about restarting the server for new oom_score_adj to take effect:
https://blog.rimuhosting.com/2015/12/11/dont-let-the-oom-killer-stop-mysql/

Another dumb question is that I can't seem to write to the oom_score_adj file, always getting the permission denied. The oom_score_adj is not owned by root, I have tried a couple of options but keep getting the permission issue:

 echo "echo '-100' > /proc/5653/oom_score_adj" | sudo -u {file_owner} sh

Appreciated for any help!

RHEL7 uses systemd.
Using the OOMScoreAdjust as follows:

sudo systemctl edit mysqld.service

Note: I'm not sure of the exact service name

Add the following text:

[Service]
OOMScoreAdjust=-1000

restart with:

systemctl restart mysqld.service

Check by looking at the score:

cat /proc/$(pidof mysqld)/oom_score_adj


Adding a default constraint in Oracle while altering a column I'm new to Oracle. I need to port a SQL Server command to Oracle.

I want to alter a column to add a new constraint with a default value.

SQL Server command

ALTER TABLE <schema_name>.<table_name>
ADD CONSTRAINT [<constraint_name>]
DEFAULT (1) FOR [<column_name>]

Oracle command

ALTER TABLE <table_name>
ADD CONSTRAINT <constraint_name>
DEFAULT (1) FOR <column_name>

I get the following error when running the Oracle query:

Error report -
SQL Error: ORA-00904: : invalid identifier
00904. 00000 -  "%s: invalid identifier"
*Cause:
*Action:

It might be because FOR is an invalid identifier?

But then how do I add a constraint with a default value for a specific column?

Do we need to alter a column and set the default value?

Which is the right way to do this?

A default value is not a constraint in Oracle. You simply alter the column as:

SQL> create table tab1 (col1 number, col2 number);

Table created.

SQL> alter table tab1 modify (col2 default 1);

Table altered.

SQL> select * from user_constraints;

no rows selected

SQL> insert into tab1 (col1) values (0);

1 row created.

SQL> select * from tab1;

      COL1       COL2
---------- ----------
         0          1


Understanding a notification system I have been looking into how to build a notification system on SE and elsewhere and found myself drawn to the solution that is the accepted answer here: https://stackoverflow.com/questions/9735578/building-a-notification-system
which uses this structure:
╔═════════════╗      ╔═══════════════════╗      ╔════════════════════╗
║notification ║      ║notification_object║      ║notification_change ║
╟─────────────╢      ╟───────────────────╢      ╟────────────────────╢
║ID           ║—1:n—→║ID                 ║—1:n—→║ID                  ║
║userID       ║      ║notificationID     ║      ║notificationObjectID║
╚═════════════╝      ║object             ║      ║verb                ║
                     ╚═══════════════════╝      ║actor               ║
                                                ╚════════════════════╝

A notification is about something (object = event, friendship..) being changed (verb = added, requested..) by someone (actor) and reported to the user (subject). Here is a normalized data structure (though I've used MongoDB). You need to notify certain users about changes. So it's per-user notifications.. meaning that if there were 100 users involved, you generate 100 notifications.

I thought at first that I understood this approach, but when I started getting ready to implement it I realized I apparently don't understand it particularly well. The last few comments on the answer are questions from other users who also have had trouble understanding the solution.
I'm not sure if this is the model I'll end up following, but given the number of upvotes it has, I'm sure it would benefit me to understand it, and I'd certainly like to learn more. I hope this will also be of use to others who have had trouble grasping this solution (incidentally, I don't have enough internet points to leave a comment on that answer directing to this question, anyone else please do!)
Questions
If I understand it right, notificationObjectID is a foreign key pointing to the notification_object table, and notificationID is a foreign key pointing to the notification table. It seems like object should be a foreign key referring to the ID of the database entry the notification is about (e.g. a specific event or post), but don't we then need another field to indicate which table that ID belongs to?
The author wrote

notification_object.object identifies change type, like a string "friendship" The actual reference to changed object with its extra data that I talk about is in notification_change.notificationObjectID

which doesn't seem to make sense to me. Object is a string (enum?) and notificationObjectID is a foreign key referring to the object the notification is about? Then how are the middle and right tables connected at all?
It seems that the middle table specifies what object (or type of object) the notification is about, e.g. an event or post. We can then have many entries in notification_change that point to the same object type, which allows us to bundle notifications (like "25 users posted on X's wall) - hence the 1:n relationship between the middle and right tables.
But why is there a 1:n relationship between the left and middle tables? Are we going to give "25 users posted on Sam's wall" and "Mary updated her "Friday Picnic" event the same notification ID? If all notifications for the same user have the same notification ID, why do we even need the table on the left?
A performance question - say John posts a comment on Mary's picnic event. It seems like we'd need to do a lookup to see if a notification_object already exists for Mary's Picnic before we created the notification_change entry. Is this going to negatively impact performance, or is it a non-issue? Continuing the questions from the previous paragraph, how would we know which notification entry to point the notification_object to?

Thanks for such thorough questions, and sorry for all of confusion - commenting 1 year after initial answer is hard and now 3 years after.. initial thoughts fade and confuse me too, but I fear to edit the hole thing because I'm not working on storing notifications on backend right now and not sure if I make good judgements without practical application

I think at the time of writing:

Yes and no, notificationID was a foreign key, notificationObjectID was not. You do need another FK field to tie tables together. I blame my mongo experience in not being so clear about it :(
Yes and no, notification_object.object is vague because you can have it just as a string, or something complex (JSON, or FK). In my case it was just a noun.

So it all depends on how your notifications look. For simple case, you just want to link entire notification to some URL, like friends page - thats why having object (entityType) as a string is useful - you tie URLs to it.

Notification "you have 3 friend requests added" can be stored differently.

If you want to show them one at a time - you'll have 3 notifications, 3 notification objects (friend_request) and 3 entries in notification_change that link to specific friend user.

If you want to show one notification - you'll have 1 notification, 1/more objects and 3/more actions. So in this complex case, like «you have 3 friend requests from user A, user B, user C» - you use notificationObjectIDs for each user and have several links in your notification text.

Should you use 1 or 3 friend_request objects? It depends on

What is the object and what is the action. Is «article liked/commented»? Or is it «like/comment added» and object hierarchy gets linked only during display? Here is facebook distinguishing «photo commenting» from «user mentioning» which semantically seem to be very close — you have same photo, same actor but different notifications that could have been merged together

Can you remove a notification or do you need a history?
So if I send a friend request and then cancel it, or comment and then delete an article, like and then unlike something - should It show this history (as another action) to the end user as two different actions? Probably not.  In addition its techincally more complex - you need to search if there is an existing notification_object, add new notification_change to it (if not - add a new object) so that later I would need to search through notification_change to remove it. Instead I would just add another notification_object to the same notification and remove it if its gone with actions getting cascade-deleted.

On the other hand, there could be cases, where it might be useful to have grouping of actions where nothing gets wiped from the history.

I think thats why at the time of writing, 1:n relation between left and middle tables were done - so that you could group notifications not only by actors on the same entity (middle-right tables) but by several object/entities too.

But sure, you can simplify entire case and optimize storage by reversing left-middle relationship to n:1, so that you have per-user notifications generated for one event.

So that it would it look more like this..

╔═════════════╗      ╔═══════════════════╗      ╔════════════════════╗
║notification ║      ║notification_object║      ║notification_change ║
╟─────────────╢      ╟───────────────────╢      ╟────────────────────╢
║ID           ║←—n:1—║ID                 ║—1:n—→║ID                  ║
║noteObjFK    ║      ║entityType         ║      ║noteObjFK           ║
║viewerUserID ║      ║entityID           ║      ║actionOnEntity      ║
╚═════════════╝      ╚═══════════════════╝      ║actorUserID         ║
                                                ╚════════════════════╝

Hope this helped


Should I use ReFS for a new SQL Server? We are in the process of building a new production Windows Server 2012 R2 server to run SQL Server 2014 Enterprise Edition.

There is a bit of advice on the internet that suggests for Windows Server 2012 and SQL Server 2012 I should stick to NTFS (e.g. itknowledgeexchange) as database commands like DBCC CHECKDB don't work.

However, there is also advice that SQL Server 2014 now supports ReFS (e.g. Brent Ozar, msdn social)

Is there a recommendation from Microsoft or a general rule of thumb on when to use NTFS and if/when to use ReFS for data and log files in SQL 2014 and Server 2012 R2?

There is a bit of advice on the internet that suggests for Server 2012 and SQL 2012 I should stick to NTFS e.g. itknowledgeexchange) as database commands like DBCC CHECKDB don't work.

This is no longer true with SQL Server 2014 and is fully documented in KB 2974455

  This problem is resolved in Microsoft SQL Server 2014. Beginning in this version of SQL Server, the DBCC CHECKDB command does not use named streams to create the internal snapshot database.

In fact, In-Memory OLTP (Hekaton) is supported as well (initially it had problems), but I would not recommend to put your mission critical database servers on ReFS (it's new and there might be bugs that people have not yet discovered as opposed to NTFS which is around since ages) - check

SQL Server AND REFS Part 1 and 2 by @swasheck

Adding for completeness :

Bob Dorr - Principal SQL Server Escalation Engineer recently published a blog post on this:

Sparse Files – Supported on both NTFS and REFS


Postgresql: when temporary tables are deleted in postgresql I am inserting data into temp table and its working fine. below is the psedo sql code for the same

with cte as(

)
select *
into temp_table
from cte

with this approach data is inserted very fast into temp table. As per my knowleged temp table is deleted once the session is closed. but my temp table is not deleted even if closed my pgadmin connection.

My question is does temp table in postgresql are deleted automatically or they remains on disk space until we delete them.

Regards,

Sanjay Salunkhe

In fact you created a regular table. You have to specify that this is a temporary table:

with cte as(
-- <a_query>
)
select *
into temporary temp_table
from cte;

or (the recommended syntax):

create temporary table temp_table as
-- <a_query>

See SELECT INTO and CREATE TABLE AS.


GridFS: why can't I shard chunks with a hashed key? There is a statement in the MongoDb documentation:

  You cannot use Hashed Sharding when sharding the chunks collection.

It does not say that, but I guess it means that I can only use ranged sharding. But it seems VERY inefficient. In fact, they suggest that the shard key should be the files_id or the files_id + the chunk number. They both increase monolitically. But the documentation also says, that you should not use ranged sharding when the keys increase monolitically. So there is a contradiction here.

Why on earth they do not allow me to use hashed sharding on the chunks collection? At least it should be explained in the documentation.

GridFS is for storing large binary data chunked into smaller documents (by default, 255KB each). The access pattern for GridFS is different from a sharded collection where random document distribution might be more desirable (for example, with a hashed shard key).

With GridFS the documents relating to a single file are normally read sequentially: identified by unique files_id and ordered by chunk number n. The supported shard keys for GridFS enable range queries based on the order that drivers will reconstruct a GridFS byte stream. A hashed shard key does not support range queries so would be extremely unhelpful for read performance (n chunk lookups would be required and data would be randomly distributed).

  In fact, they suggest that the shard key should be the files_id or the files_id + the chunk number. They both increase monolitically.

The default values for these two fields indeed increase monotonically, which will lead to a hot shard for writes if you shard an fs.chunks collection using the default ObjectID value for files_id.

The chunk number is expected to be a sequence, but you can (and should) provide your own custom IDs when uploading files if you want to improve write distribution for GridFS in a sharded deployment. Official MongoDB drivers should provide an API for setting the _id when creating a new GridFS file.

  At least it should be explained in the documentation.

Definitely! I've raised DOCS-10993 to improve this information in the MongoDB manual. I suspect there are also improvements to be made in the driver documentation so will review those as part of documenting the overall recommendation.


Temporary tables created on disk: is huge I have an issue with a huge number of Temp tables writing on disk. I have tried many things to avoid this but no luck and I really am giving up. I though I might need some expertise to help solve this issue.

My machine is dedicated to four wordpress sites. The server has 8 cores and 64GB RAM.

Every time I run mysqltunner I get this message

  Temporary tables created on disk: 84% (3K on disk / 3K total)

and the number keeps increasing.

From PHPmyadmin I notice this message:

  Issue:
  Too many sorts are causing temporary tables.
  Temporary tables average: 1.24 per minute, this value should be less than 1 per hour.

Here is my configuration

    [mysqld]
    log-error=/var/lib/mysql/server.ict-hardware.com.err
    default-storage-engine=InnoDB
    performance-schema=ON
    skip-name-resolve=1

    # MyISAM #
   key-buffer-size                = 110M
   myisam-recover                 = FORCE,BACKUP
   join_buffer_size               = 4M
   sort_buffer_size               = 4M

   # SAFETY #
   max-allowed-packet             = 16M
   max-connect-errors             = 1000000
   # CACHES AND LIMITS #
   tmp-table-size                 = 512M
   max-heap-table-size            = 512M
   query-cache-type               = 0
   query-cache-size               = 0
   max-connections                = 500
   thread-cache-size              = 100
   open-files-limit               = 65535
   table-definition-cache         = 8096
   table-open-cache               = 8096

   # INNODB #
   innodb-flush-method            = O_DIRECT
   innodb-log-files-in-group      = 2
   innodb-log-file-size           = 3075M
   innodb-flush-log-at-trx-commit = 1
   innodb-file-per-table          = 1
   innodb-buffer-pool-size        = 30G
   innodb_buffer_pool_instances   = 30

   # LOGGING #
   log-error                      = /var/lib/mysql/./mysql-error.log
   log-queries-not-using-indexes  = 1
   slow-query-log                 = 1
   slow-query-log-file            = /var/lib/mysql/./mysql-slow.log
   long_query_time                = 10

   max_allowed_packet=268435456
   open_files_limit=2048

After Several Investigation i found I have two main query that take more than 1 sec

SELECT DISTINCT(wp_posts.post_parent) as ID
FROM wp_posts
INNER JOIN wp_postmeta AS pf1
ON (wp_posts.ID = pf1.post_id)
INNER JOIN wp_term_relationships
ON (wp_posts.ID = wp_term_relationships.object_id)
WHERE wp_posts.post_type = "product_variation"
AND pf1.meta_key IN ("attribute_pa_women-clothes")
AND pf1.meta_value IN ("s","")
AND ( wp_posts.ID IN (
SELECT object_id
FROM wp_term_relationships
WHERE term_taxonomy_id IN ( 401 ) ) )
AND ( wp_posts.ID IN (
SELECT post_id
FROM wp_postmeta
WHERE meta_key LIKE "attribute_pa_%"
GROUP BY post_id
HAVING COUNT( DISTINCT meta_key ) = 1 ) )
GROUP BY pf1.post_id
HAVING COUNT(DISTINCT pf1.meta_key) = 1
LIMIT 29999

and

SELECT DISTINCT YEAR( post_date ) AS year, MONTH( post_date ) AS month
FROM wp_posts
WHERE post_type = 'attachment'
ORDER BY post_date DESC

Any help please :)

Thanks

You "can't tune your way out of a performance problem".  So let's look for a villain and fix it.

Change long_query_time down to 1.  Wait a day.  Then run pt-query-digest.

More: http://mysql.rjweb.org/doc.php/mysql_analysis#slow_queries_and_slowlog

A query that is running often or long will show up in the slowlog; let's look at the first couple of them.

A likely candidate is postmeta:  http://mysql.rjweb.org/doc.php/index_cookbook_mysql#speeding_up_wp_postmeta

After edit

First query:

IN ( SELECT ... ) is notoriously inefficient.  Change to a JOIN .. ON.

And change the index(es) as indicated in the indexing link above.

Is LIMIT  29999 realistic?  Suggest lowering that significantly.

Second query:

INDEX(post_type, post_date)


Updating a local table with a per-row count(*) which is an aggregate of inner joins on remote server I have a table of around 350m rows on a linked server in which I added an additional INT column to serve as a count(external_identification) of records as the result of a join on PACKAGE and DOC2. Since the tables are so large I'd like to process the update in batches, both so I can gauge progress and to avoid creating huge temp tables. Each column is indexed.

Would this be a good example where a CTE comes into play? Quite honestly they confuse me with the way they need to be written, it's hard to visualize...

The tables are structured as:

ServerA (utility SQL server)
Table: CLIP_IDs
Columns: Package_UUID nvarchar(255), MessageExtractState tinyint, [count] int (350m rows)

ServerB (main database server)
Table: PACKAGE
Columns: Package_UUID nvarchar(255), Package_id bigint (650m rows)
Table: DOC2
Columns: External_Identification nvarchar(255), Package_id bigint (2b rows)

Both SQL servers are linked both ways, if initiating the query from one is more efficient. I have a feeling issuing the query from ServerA will be, as it seems like the execution plan offers less remote queries.

I stopped the query below after 26 hours because I think I have a syntax logic error. Can someone explain what it is and offer any suggestions please?

Executed from ServerA:

DECLARE @rowsUpdated INT

SET @rowsUpdated = 1

WHILE (@rowsUpdated > 0)
BEGIN
    UPDATE CLIP_IDs
    SET [Count] = x.[count]
    FROM (
        SELECT TOP 50000 c.package_uuid
            ,count(d.external_identification) AS [count]
        FROM CLIP_IDs c
        INNER JOIN ServerB.DATABASE.dbo.package p(NOLOCK) ON c.package_uuid = p.package_uuid
        INNER JOIN ServerB.DATABASE.dbo.doc2 d(NOLOCK) ON p.package_id = d.package_id
        WHERE c.messageextractstate = 1
            AND c.[count] IS NULL
        GROUP BY c.package_uuid
        ) x

    SET @rowsUpdated = @@rowcount

    PRINT N'Finished set of rows: ' + convert(VARCHAR, getdate(), 120)
END

Depending on your permissions, the linked server could be trying to stream all the data over locally and then doing filtering. References

Performance implications of using OPENQUERY in a view
Which one is more efficient: select from linked server or insert into linked server?
http://blogs.msdn.com/b/psssql/archive/2009/09/02/distributed-queries-remote-login-permissions-and-execution-plans.aspx

You might be able to skip that pain by computing the total aggregate count first into a table on the local server and then beat against that.

CREATE TABLE #LOCAL
(
    package_uuid nvarchar(255) NOT NULL PRIMARY KEY CLUSTERED
,   [count] bigint
);

INSERT INTO
    #LOCAL
SELECT
    p.package_uuid
,   count(d.external_identification) AS [count]
FROM
    ServerB.DATABASE.dbo.package p
    INNER JOIN
        ServerB.DATABASE.dbo.doc2 d
        ON p.package_id = d.package_id
GROUP BY
    p.package_uuid;

Try running that query locally on ServerB first to get an understanding of the theoretical throughput without factoring in your network. You can then do some quick and dirty estimates based on data sizes (500 + 8 per row in temporary table) and then it depends on your network. Hopefully this is all local network.

If the time is significantly different between the run on ServerB and pulling it back over, then you might need to use the OPENQUERY syntax to force the join on the remote server. Code approximately

CREATE TABLE #LOCAL
(
    package_uuid nvarchar(255) NOT NULL PRIMARY KEY CLUSTERED
,   [count] bigint
);

INSERT INTO
    #LOCAL
SELECT
    OQ.package_uuid
,   OQ.[count]
FROM
    OPENQUERY(ServerB,
    N'
    SELECT
        p.package_uuid
    ,   count(d.external_identification) AS [count]
    FROM
        DATABASE.dbo.package p
        INNER JOIN
            DATABASE.dbo.doc2 d
            ON p.package_id = d.package_id
    GROUP BY
        p.package_uuid
    ) AS OQ;


Writing SQL to check for a role if exist and add member I am not to good at writing SQL so please help with below as i am doing something wrong here

I need to alter a role say role_a to add member Domain/SQLAgent

The problem is how can i check or write a query to do this on SQL servers where there is one more role role_b only existing if role a is not there

Basically, how can i write a script to check on server if role a exist alter that role to add member and if role b then do same.

modifying as Q is not duplicate

I want to check if role_a exist then

ALTER ROLE [role_a] ADD MEMBER [Domain/SqlAgent]

else

ALTER ROLE [role_b] ADD MEMBER [Domain/SqlAgent]

Note@ Both the roles do no exist together

Your basic question boils down to "How can I tell if a given role exists in a database." All of the other code you already know how to do.

The easiest way is to query the sys.database_principals table to see if the role is there. Based on your sample question, here is a good check and update into the different roles:

IF EXISTS (
    SELECT *
    FROM sys.database_principals
    WHERE name = 'role_a'
        and type = 'R'
        )
BEGIN   -- Role_A exists, add to role
    ALTER ROLE [role_a] ADD MEMBER [Domain/SqlAgent]
END
ELSE IF EXISTS (
    SELECT *
    FROM sys.database_principals
    WHERE name = 'role_b'
        and type = 'R'
        )
BEGIN   -- Role_B exists, add to role
    ALTER ROLE [role_b] ADD MEMBER [Domain/SqlAgent]
END


How to change type from one owner to two different types of owners I currently have (tables changed):

User

UserID uniqueidentifier,
CompanyID uniqueidentifier, (for future reference)
.. more unrelated fields

(PK: UserID)

UserRating

UserID uniqueidentifier,
MovieID uniqueidentifier,
RatingTypeID uniqueidentifier,
Rating tinyint

(PK: UserID, MovieID, RatingTypeID)

RatingType

RatingTypeID uniqueIdentifier,
Title varchar(25)

(PK: RatingTypeID)

The RatingType table contains "Favorite", "Need to Watch", etc.  So actual Rating value is how much they liked a Favorite, or how badly the want to watch a movie etc.

Now I introduce a new table:

Company

 CompanyID uniqueidentifier,
 .. more unrelated fields

The idea is that if a user becomes part of a company, due to requirements that cannot change, all the Ratings need to be Company ratings and not User Ratings.  I'm not a normalization guru, but for some reason creating another table (below) seems redundant, but maybe I'm thinking to much into it.

CompanyRating

CompanyID uniqueidentifier,
MovieID uniqueidentifier,
RatingTypeID uniqueidentifier,
Rating tinyint

(PK: CompanyID, MovieID, RatingTypeID)

This feels incorrect because it almost matches the rating table and if I need to do any averages it becomes more difficult to average across two tables.  Is there a better design for my needs?

I have a feeling you will benefit if you borrow party concept from Party-Role-Relationship, one of universal data models, which is capable of keeping all information you needed.

So you will have party_type table that will currently have 2 records, person and organization, one common party table that holds all people and companies (you may want to add 2 more tables , person and company that hold attributes specific to people and organizations respectively), and your rating will have a reference to party.