Data inherently ordered as if it were a clustered index I have the following table with 7.5 million records:

CREATE TABLE [dbo].[TestTable](
    [Id] [int] IDENTITY(1,1) NOT NULL,
    [TestCol] [nvarchar](50) NOT NULL,
    [TestCol2] [nvarchar](50) NOT NULL,
    [TestCol3] [nvarchar](50) NOT NULL,
    [Anonymised] [tinyint] NOT NULL,
    [Date] [datetime] NOT NULL,
CONSTRAINT [PK_TestTable] PRIMARY KEY CLUSTERED
(
    [Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF,
ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

I notice that when there is a non-clustered index on the date field:

CREATE NONCLUSTERED INDEX IX_TestTable_Date ON [dbo].[TestTable] ([Date])

-and I run the following query:

UPDATE TestTable
SET TestCol='*GDPR*', TestCol2='*GDPR*', TestCol3='*GDPR*', Anonymised=1
WHERE [Date] <= '25 August 2016'

-the data coming returned by the index access operation is sorted to match the key order of the PK/CX, reducing performance.

Query plan

I was surprised to find that removing the index from the date field actually improves the performance of the query by about 30% because it no longer performs the sort:

Query plan

My theory, and this may be obvious to the more experienced among you, is that it has figured out that the date column is implicitly ordered exactly the same as the primary key / clustered index.

So my question is: Is it possible to take advantage of this fact in order to improve the performance of my query?

I mocked up test data that mostly reproduces your issue:
INSERT INTO [dbo].[TestTable] WITH (TABLOCK)
SELECT TOP (7000000) N'*NOT GDPR*', N'*NOT GDPR*', N'*NOT GDPR*', 0, DATEADD(DAY, q.RN  / 16965, '20160801')
FROM
(
    SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) RN
    FROM master..spt_values t1
    CROSS JOIN master..spt_values t2
) q
ORDER BY q.RN
OPTION (MAXDOP 1);

DROP INDEX IF EXISTS [dbo].[TestTable].IX_TestTable_Date;
CREATE NONCLUSTERED INDEX IX_TestTable_Date ON [dbo].[TestTable] ([Date]);

Statistics for the query that uses the nonclustered index:

Table 'TestTable'. Scan count 1, logical reads 1299838, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
SQL Server Execution Times: CPU time = 984 ms,  elapsed time = 988 ms.

Statistics for the query that uses the clustered index:

Table 'TestTable'. Scan count 1, logical reads 72609, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
SQL Server Execution Times:  CPU time = 781 ms,  elapsed time = 772 ms.

Getting to your question:

Is it possible to take advantage of this fact in order to improve the performance of my query?

Yes. You can use the nonclustered index that you already have to efficiently find the maximum id value that needs to be updated. If you save that to a variable and filter against it you get a query plan for the update that does the clustered index scan (without the sort) that stops early and therefore does less IO. Here's one implementation:
DECLARE @Id INT;

SELECT TOP (1) @Id = Id
FROM dbo.TestTable
WHERE [Date] <= '25 August 2016'
ORDER BY [Date] DESC, Id DESC;

UPDATE TestTable
SET TestCol='*GDPR*', TestCol2='*GDPR*', TestCol3='*GDPR*', Anonymised=1
WHERE [Id] < @Id AND [Date] <= '25 August 2016'
AND [Anonymised] <> 1 -- optional
OPTION (MAXDOP 1);

Run stats for the new query:

Table 'TestTable'. Scan count 1, logical reads 3, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'TestTable'. Scan count 1, logical reads 4776, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
SQL Server Execution Times:  CPU time = 515 ms,  elapsed time = 510 ms.

As well as the query plan:

With all of that said, your desire to make the query faster suggests to me that you plan to run the query more than once. Right now your query has an open ended filter on the date column. Is it truly necessary to anonymize the rows more than once? Can you avoid updating or scanning rows that were already anonymized? It should certainly be faster to update a range of dates with dates on both sides of it. You could also add the Anonymised column to your index, but that index will needed to be updated during your UPDATE query. In summary, avoid processing the same data over and over if you can.
The original query that you have with the sort is slower due to work done in the Clustered Index Update operator. The amount of time spent on the index seek and the sort is only 407 ms. You can see this in the actual plan. The plan executes in row mode so the time spent on the sort is the time of that operator along with every child operator:

That leaves the sort operator with about 1600 ms of time. SQL Server needs to read pages from the clustered index in order to perform the update. You can see that the Clustered Index Update operator does 1205921 logical reads. You can read more about sorting optimizations for DML and optimized prefetch in this blog post by Paul White.
The other query plan that you have (without the sort) takes 683 ms for the clustered index scan and about 550 ms for the Clustered Index Update operator. The update operator does not do any IO for this query.
The simple answer as to why the plan with the sort is slower is that SQL Server does more logical reads on the clustered index for that plan compared to the clustered index scan plan. Even if all of the needed data is in memory there's still an overhead and cost to doing those logical reads. A better answer is much harder to get, in that as far as I know the plans won't give you any further details. It is possible to use PerfView or another tool based on ETW tracing to compare call stacks between the queries:

On the left is the query that does the clustered index scan and on the right is the query that does the sort. I marked call stacks in blue or red that only appear in one query. Not surprisingly, the different call stacks with a high numbered of sampled CPU cycles for the sort query appear to have to do with the logical reads required to perform the update on the clustered index. In addition, there are differences in number of sampled cycles between the queries for the same operation. For sample, the query with the sort spends 31 cycles acquiring latches whereas the query with the scan only spends 9 cycles acquiring latches.
I suspect that SQL Server is picking the slower plan due to a query plan operator costing limitation. Perhaps part of the difference in run time is due to hardware or your edition of SQL Server. In any case, SQL Server is not able to figure out that the date column is implicitly ordered exactly the same as the clustered index. Data is returned from the clustered index scan in clustered key order, so there is no need to perform a sort in an attempt to optimize IO when doing the clustered index update.


Software/Hardware Upgrade I've been working on a software/hardware upgrade from SQL2005 on win 2003 to SQL2008 R2 SP2 on win2008.  The new hardware has 48 cores with 128 gig of ram whereas the older server 16 cores with 32Gig ram.  We did several batch run and the result just didn't add up.  The newer server was slower than the older server?

I've run out of ideas and would like some pointers please.

Thanks

Run the same query on a backup on both systems that matches production as closely as possible.  Show us the queries, execution plans, execution times, and perfmon counters.  This is the best way to find out what's happening.  I'm sure we can see what's going on.  During 1 DB migration I had a major networking issue with a firewall pegged at 100% CPU between the web servers and SQL.  End user experience was horrible, and NETWORK_IO was the highest wait stat, so maybe it's not SQL Server related.

I recently went from 2x E 4core / 48gb ram to 4x E7 8 core CPUs /256GB ram and saw some interesting results as well.

I had to tweak:
-Parallelism threshold and set maximum parallelism setting per query.  Check your
SELECT *
FROM SYS.DM_OS_WAIT_STATS
ORDER BY 3
Seeing CXPACKETS way high won't mean you have a parallelism issue, but I found it helpful to increase the threshold and CXPACKETAS fell from #1.


How can I insert a record into a table that depends on a foreign key linking to a previous table? Following on from this question. I have a dictionary file written in XML. I've written a program in VB.NET to parse this file and store all the data in Class objects, and now I want to write a subroutine to insert all of this data into an Access database. What I'm struggling with is working out how to use SQL to INSERT all of the data.

There are 20 tables, and the main table is called Entry, which all the other tables link back to. To give a simplified example of the data structure, there is also a Keyword table, which links back to the Entry. And there is the Keyword_Priority table, which links back to Keyword. Both relationships are one-to-many, as below:

My question

I can use SQL INSERT for the Entry table to create one record. But how would I then create a record for the Keyword table, referring to the primary key of the Entry record I've just created? And how would I then create a record for Keyword_Priority, referring to the Keyword record? And so on.

This is all going to be done through VB.NET - I can't use Forms because there are 180,000+ entries to input. Is there any way to store a variable in SQL that I can refer to later?

INSERT INTO Entry (EntrySequence)
VALUES ('new EntrySequence');

INSERT INTO Keyword (Keyword, EntryFK)
SELECT 'new Keyword', Entry.EntryID
FROM Entry
WHERE Entry.EntrySequence = 'new EntrySequence';

INSERT INTO Keyword_Priority (KeywordPriority, KeywordFK)
SELECT 'new KeywordPriority', Keyword.Id
FROM Keyword /* , Entry */
WHERE Keyword.Keyword = 'new Keyword'
/* AND Entry.EntrySequence = 'new EntrySequence'
   AND Entry.EntryID = Keyword.EntryFK */


MariaDB threads goes around 10K suddenly We have a big Database, It is about 750G. And also for replication this is our Master DB and we have 4 slaves, 3 of them are sync without delay, and one of them has 2 hours delay.

OS : Ubuntu 16.04
DB : MariaDB 10.2.9
app : php 7

Sometimes mysql thread increase suddenly (about 5k) without any reason, by chance I found that if I stop one of first three servers slave trough stop slave it can decrease the threads to normal numbers (around 500), and start slave by star slave after 1 minute or more.

Every time Mysql threads goes up more than 4000 in a second I should use :
stop slave; start slave; on every server ! and this is not good solution!

*** And even worse cases, stop slave; start slave; won't work and Mysql threads is going up more than 10K and I should stop and start Master database Mariadb  ( I mean systemctl stop mysql && systemctl stop mysql) and it is clear that at that time my application is not reachable by users!

My questions are :
-1 : what is the reason of this accidents ?
-2 : what is temporary solution ?
-3 : will something like maxscale help me in this scenario or not
     -3.1 : If yes how does maxscale will help me ?
-4 : what is the best solution for my problem, how can I trace the issue ?

Many things may be involved.

First, which of these are you talking about?  (And what values do you have?)

"Threads_cached": "5",      -- 1000 might be good for you
"Threads_connected": "10",
"Threads_created": "36404", -- divide by Uptime
"Threads_running": "4",     -- 20 is a 'large' value

"Max_used_connections": "238",
"max_connections": "400",   -- we may need to tune this to avoid the problem

How do you "load balance" the Slaves?  Assuming that the connections are normally quick, "round robin" is better than any sophisticated method.

You should consider adding another Slave -- Keep in mind that if you lose a Slave (crash, maintenance, hiccup, etc), you will be back down to 3, which seems has the problem.  Having 4 up-to-date slaves may make the problem much rarer.

The reason for throttling max_connections is to throw the problem back to the client, forcing it to pick another Slave.  (I assume you recover from connection error and try again?)

Please describe how the data comes in.  It sounds like sensor data that is pushed from remote sensors?  Is there anything that would cause it to "sync" -- that is everyone sends at the top of the minute?  If so, is there a way to stagger the pushes?

Do the remote sensors do the following?

connect to mysql
insert one row (please provide details and SHOW CREATE TABLE)
disconnect

?  If not, please describe it.

Is there any "connection pooling"?  You mentioned "maxscale"; are you using that?

What is the average number of connections over an hour (or day or whatever)?  As a first cut, I would set max_connections to twice that.

Please provide (1) RAM size, (2) SHOW VARIABLES, (3) SHOW GLOBAL STATUS after mysql has been running for a day.  This may give more insight into the problems.


Stored procedures prefixed with sp_MSdel, sp_MSins, sp_MSupd I noticed in the database I manage a bunch of stored procedures for delete, insert, update prefixed with sp_MSdel, sp_MSins and sp_MSupd.

Does anyone know what tool creates these stored procedures?

Here is the source code of the update stored procedure (I replaced the table name and the field names):

create procedure [dbo].[sp_MSupd_dboTableName]
        @c1 int = NULL,
        @c2 nvarchar(50) = NULL,
        @c3 nvarchar(50) = NULL,
        @c4 nvarchar(50) = NULL,
        @c5 nvarchar(50) = NULL,
        @c6 datetime = NULL,
        @c7 datetime = NULL,
        @pkc1 int = NULL,
        @bitmap binary(1)
as
begin
update [dbo].[TableName] set
        [field2] = case substring(@bitmap,1,1) & 2 when 2 then @c2 else [Rid] end,
        [field3] = case substring(@bitmap,1,1) & 4 when 4 then @c3 else [Pid] end,
        [field4] = case substring(@bitmap,1,1) & 8 when 8 then @c4 else [AdUsername] end,
        [field5] = case substring(@bitmap,1,1) & 16 when 16 then @c5 else [FhaEmployeeID] end,
        [field6] = case substring(@bitmap,1,1) & 32 when 32 then @c6 else [AdAccountsRegisteredTimestamp] end,
        [field7] = case substring(@bitmap,1,1) & 64 when 64 then @c7 else [EmployeeIdsRegisteredTimestamp] end
where [id] = @pkc1
if @@rowcount = 0
    if @@microsoftversion>0x07320000
        exec sp_MSreplraiserror 20598
end
GO

Those procedures are created when SQL Server Replication (Transactional Replication) is configured for a table in the database. See this link for more info.


Use SSIS to get measures from a tabular model I need to get source data from an Azure Analysis Services via SSIS. While I can easily use an OLE DB source in SSIS to fetch columns from tables, I do not see measures listed.

Is there a way to get values from measures?

You cannot get the value of a measure without an evaluation context. You can however execute a DAX query to fetch the data that you need. Once you have defined your connection manager, instead of choosing Table or View as your data access mode, choose SQL Command. You can then specify the DAX Query which will evaluate the measure within your defined context. After you have defined your query you can click on the Preview button to see the data that will be returned.

You can find a step by step guide on how to do this here.


How can I copy the definition of a field from one table to another at design-time? I have a table with ten fields, f1 (being an integer field) to f10 (being a character field). In between there are other integer, character and date fields.
I would like to copy those field definitions to another table, without needing to re-write the whole definitions. I'm working with the Data Dictionary, who seems not to cover this feature.
Edit after first answer
The first answer mentions how to do this at runtime, but I'm specifically looking for a solution at design-time.
Edit
I've found there are different commandline utilities which can be used for working with Progress database: ProDB for creating a new database, ProUtil for doing several tasks (but I don't find a ProUtil <add a field to a table>).
How can I achieve this?

I would like to copy those field definitions to another table, without needing to re-write the whole definitions.

How about this?
create table t2 as
select *
from t1
where 1 = 2 ;

OK, it doesn't copy indexes and such like, but it gets you the basic structure, as requested.

I'm working with the Data Dictionary, who seems not to cover this feature.

Given that the job of a Data Dictionary is to manage the structures of other tables, I'd say that's a pretty big hole in its functionality!


Why does PK constraint require a separate index when I already have a suitable unique clustered index? This is part of table definition from here:

CREATE TABLE [dbo].[JobItems] (
[ItemId]            UNIQUEIDENTIFIER NOT NULL,
-- lots of other columns
CONSTRAINT [PrimaryKey_GUID_HERE] PRIMARY KEY NONCLUSTERED ([ItemId] ASC)
);

CREATE UNIQUE CLUSTERED INDEX [JobItemsIndex]
ON [dbo].[JobItems]([ItemId] ASC);

This is some legacy design, so please don't ask "why". Anyway when I look up the list of indexes I see that there're two indexes - one of them is JobItemsIndex and the other is PK-GUID-HERE and they are both for JobItems table.

My question is...

Why is there a need for a separate index to maintan the PK when I already have JobItemsIndex which is unique and includes the very same column and so suitable for maintaining the PK constraint?

It doesn't need a separate index. It's only done that way because you've actually told it to be in the script. You could do this with the same functionality:

CREATE TABLE [dbo].[JobItems] (
[ItemId]            UNIQUEIDENTIFIER NOT NULL,
-- lots of other columns
CONSTRAINT [JobItemsIndex] PRIMARY KEY CLUSTERED ([ItemId] ASC)
);


moving to AWS RDS (InnoDB vs MyISAM I am in the process of moving my servers from stand alone hosting to AWS using RDS (MySQL) for database usage.

One of the first things RDS states is that in order to use their backup, and snapshot features of RDS the tables must be using InnoDB.

My site has always run on MyIsam, probably mainly because 10+ years ago, it seemed like the right choice.

Because of this, my database structure is quite simple, and quite separate, there are no join queries on the site, so every select is only getting data from one table.

some of the tables have quite a few indexed fields, The site requires, frequent inserts, regular updates and many reads,

I don't use full text searching, the majority of queries revolve around either the UserID or the tables autoincrement ID

So, my question is, what to expect if i was to move to Innodb? I am not that worried about the transaction side of it. and for this reason MyISAM is ok. I am more concerned about backup, previously I had a cron job that would automate the backup , but of course this came at a price of lockouts while the backup was in process.

MY understanding of InnoDB is that because it does row level locking, this would not be an issue.

But what about performance? I have read so many reviews and bench tests, but i dont know how they relate to my situation.

Can I move over to InnoDB without worrying about setting up foreign keys etc, can i keep things as simple as they are now, or would that negatively affect the system if using InnoDB.

I realize that InnoDB has become the default option in MySQL these days, which makes me think that all dbs should be running on it anyway.

What I dont want to do is have my database fall over because I didnt set up innodb properly.

Is it even worth moving my tables over to it, given its been running just fine on MyISAM for years.

Or is it a case of, this is good cause to change?

Without knowing your dataset size, or your usage (read or write heavy? queries per second?), here are some generalities:

InnoDB has improved drastically in even the last few years performance-wise. This, along with the (mostly) ACID compliance and row-level locking, is why it's now the default in 5.5+
If you're not using full text indexes in your MyISAM table, you should be fine switching to InnoDB
Make sure you know the changes to the mysql version you're going to on RDS. Performance might not be the only 'gotcha' on moving to InnoDB at RDS. If RDS is newer than your current application, make sure there's not any features that changed. You do this by:
Test it!! Don't make the move on your application until you've tested it out.

One method to test it is to turn on the general query log for a week or so (if the performance hit won't be an issue) and then run it against the RDS instance to see how it runs.

Disclaimer: I'm not an expert on RDS, so I don't know the best way to test against it.


What is stored in mysqld-slow.log? My MySQL log directory contains the following files:

7.9K   mysqld.err-old
4.0G   mysqld-slow.log
94     mysql.log
93M    mysqld.err

mysqld-slow.log contains a long list of queries with some metadata.

Questions

Which queries are logged into that file?
Which tools do I have to analyze the runtime of slow queries?

Queries that exceed the number of second set by long_query_time land in the slow query log.
If you want to parse and examine the mysqld-slow.log file, please use mysqldumpslow.
If you want better ways to examine slow queries, you may want to apply one of the following suggestions:
SUGGESTION #1
Keep in mind that mysqld-slow.log contains slow queries that have finished. It will not record long running queries that are in progress. If you would like to catch queries in the act of running long before they are recorded in the slow query log, please look over my past posts on how to set that up in Query Profiles

Jul 26, 2012 : What is running right now?
Dec 28, 2011 : Is there a tool like Microsoft's "SQL Server Profiler" for MySQL?

SUGGESTION #2
If you are using MySQL 5.1+, did you know that you could record the slow log into a MyISAM table? That way, you can query the MyISAM table rather than parsing a large text file.
I have past posts on how to set that up:

Mar 30, 2012 : MySQL slow log configuration
Nov 18, 2011 : Create a custom log table in MySQL


In a Trigger, can I determine if a column was explicitly set to a values or not mentioned in update statement? Given a Table with a column col1 like this:

create table MyTest (
    col1 int NULL,
    col2 decimal(7, 2) NULL
);

insert into MyTest values ( 1, 1.1);

Is there a way for a trigger for update to decide which of the following update statements was executed ?

A:

update MyTest set col1 = 3;

B:

update MyTest set col1 = 3, col2 = 1.1;

It makes a difference if this is going to be a set-based trigger, or if you are designing it to handle only one row at a time, but this should get you pointed in the right direction.

IF
UPDATE(col1)
AND UPDATE(col2)
AND EXISTS(SELECT * FROM inserted WHERE col1 = 3 AND col2 = 1.1)
BEGIN
    DO STUFF
END

IF
UPDATE(col1)
AND EXISTS(SELECT * FROM inserted WHERE col1 = 3)
BEGIN
    DO STUFF
END


Why is determining existence (or not) of rows matching certain criteria using a 'LIMIT 1' query on indexed columns examining more than 1 row? Consider a table created like this:

CREATE TABLE `someTable` (
  `id` bigint(11) unsigned NOT NULL AUTO_INCREMENT,
  `owner_id` bigint(11) unsigned NOT NULL,
  `device_id` bigint(11) unsigned NOT NULL,
  `reviewed` tinyint(1) NOT NULL DEFAULT '1',
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (`id`),
KEY `owner_id reviewed` (`owner_id`,`reviewed`),
KEY `device_id reviewed` (`device_id`,`reviewed`)
);

This table records events that happen to a device which has an owner. There are ~1000 owners, each of which can have 1-100 devices. Each device can have millions of events in the table.

We need two queries to tell us:

Does a given owner_id have unreviewed events on any device?
Does a given device_id have unreviewed events?

We have been using:

SELECT 1 FROM someTable s WHERE s.reviewed = 0 AND s.owner_id = 0 LIMIT 1;
SELECT 1 FROM someTable s WHERE s.reviewed = 0 AND s.device_id = 0 LIMIT 1;

But we have found that as the number of events grows these queries are slowing down unexpectedly. Running EXPLAIN on them says that we will examine many rows. I admit that I was expecting that it would only need to examine 1 row. My thinking was that MySQL could look up the corresponding index ('owner_id reviewed' index or 'device_id reviewed' index) and tell immediately whether or not there were any corresponding rows.

I understand that EXPLAIN's rows column is just an estimate and that it may not be entirely accurate for queries with LIMIT 1, but I have found that the runtimes of these queries is increasing into many seconds (sometimes minutes) as the data set increases, which leads me to believe that MySQL is indeed examining more than one row.

What am I doing wrong here? How can I restructure my queries (or indexes) to do this efficiently?

We are running MySQL 5.6.21 on Linux. The table is innodb. I have created a simple SQL fiddle here: http://sqlfiddle.com/#!9/f0f4f1/1

LIMIT provides a window into the result set.  LIMIT 1 is equivalent to LIMIT 0, 1; return 1 row starting at offset 0.  Explain plan reports the estimated number of rows the LIMIT is running against.  However, only one match should be examined.

Testing on your SQL Fiddle shows the query runs against the INDEX (KEY) which should be the fastest access method.  I have found queries of these form are sometimes faster than queries returning a constant.

SELECT owner_id
FROM someTable s
WHERE reviewed = 0
AND owner_id = 0
LIMIT 1;

I would check the I/O on the device(s) storing your data.  I/O contention can cause issues on larger databases.

I would also consider a query in the form:

SELECT MIN(event_id)
FROM someTable s
WHERE reviewed = 0
AND owner_id = 0;

With an (owner_id, review, event_id) indexed.  This should run against the index and examine only one row of the index.


How Can I Make My Statement Accept Multiple Results? I get the error "Subquery returns more than 1 row" when I run the following statement.

SELECT `labels`.*,
       IF((SELECT `label_id`
           FROM   `customer_print_sessions`
           WHERE  `customer_id` = (SELECT `customer_id`
                                   FROM   `customer_accounts_logins`
                                   WHERE  `id` = '1069')
                  AND `customer_print_sessions`.`label_id` = `labels`.`id`) IS
          NOT NULL
       , 'Y', 'N') `in_queue`
FROM   `labels`
WHERE  `id` IN (SELECT `label_id`
                FROM   `customer_groups_label_relation`
                WHERE  `group_id` IN (SELECT `group_id`
                                      FROM   `customer_accounts_group_relation`
                                      WHERE  `customer_id` =
                                             (SELECT
                                             `customer_id`
                                                              FROM
                                             `customer_accounts_logins`
                                                              WHERE  `id` =
                                             '1069'
                                             )))
ORDER  BY `labels`.`plu` ASC;

I ran each SELECT statement separately to determine which one returns multiple results that this statement as a whole was not expecting. I've determined that the following part of the above statement is the part that is returning multiple results which aren't expected.

SELECT `label_id`
FROM   `customer_print_sessions`
WHERE  `customer_id` = (SELECT `customer_id`
                        FROM   `customer_accounts_logins`
                        WHERE  `id` = '1069')
       AND `customer_print_sessions`.`label_id` = `labels`.`id`

How can I write this so that the IF function and the whole statement will work with the multiple results that are being returned?

UPDATE: Since the above statement returns multiple results of the same value, is there a way I can write it to just return one?

I added GROUP BY label_id to make the original statement work.

SELECT `labels`.*,
       IF((SELECT `label_id`
           FROM   `customer_print_sessions`
           WHERE  `customer_id` = (SELECT `customer_id`
                                   FROM   `customer_accounts_logins`
                                   WHERE  `id` = '1069')
                  AND `customer_print_sessions`.`label_id` = `labels`.`id` GROUP BY `label_id`) IS
          NOT NULL
       , 'Y', 'N') `in_queue`
FROM   `labels`
WHERE  `id` IN (SELECT `label_id`
                FROM   `customer_groups_label_relation`
                WHERE  `group_id` IN (SELECT `group_id`
                                      FROM   `customer_accounts_group_relation`
                                      WHERE  `customer_id` =
                                             (SELECT
                                             `customer_id`
                                                              FROM
                                             `customer_accounts_logins`
                                                              WHERE  `id` =
                                             '1069'
                                             )))
ORDER  BY `labels`.`plu` ASC;


I have 2 parent tables with 1 child and I want to perform a query that involves both parents I have one table that contains information about different types of hops for use in brewing and a second that contains a list of aromas used to describe hops.  I've made a third table that contains a primary key and 2 foreign keys (one is the primary key of the hops table and one is the primary key of the aromas table).  I set it up as two tables because any hops variety may have several aroma descriptors and any aroma may apply to several hops varieties.

The child of the two parents was created in the following way:

CREATE TABLE IF NOT EXISTS aromareference
( ID int(5) NOT NULL AUTO_INCREMENT,
  HopsID int(5) DEFAULT NULL,
  DescriptorID int(5) DEFAULT NULL,
  CONSTRAINT FOREIGN KEY (HopsID)
    REFERENCES hops (ProductID),
  CONSTRAINT FOREIGN KEY (DescriptorID)
    REFERENCES hopsdescriptors (DescriptorID),
  PRIMARY KEY(ID)
);

The hops table has a column called HumanName, as does the hopsdescriptors table.  I would like to perform a query using the HumanName from the hops table to list from the hopsdescriptors table all of the applicable HumanNames of the aromas.

As an example output, I would be looking for something like:

+------------+
| Centennial |
+------------+
| Floral     |
| Citrus     |
+------------+

The basic structure of the tables in question is as follows:

CREATE TABLE IF NOT EXISTS hops
('ProductID' int(5) NOT NULL AUTO_INCREMENT,
'HumanName' varchar(50) DEFAULT NULL,
'ShortDescript' varchar(500) DEFAULT NULL,
'PrimaryUse' int(1) DEFAULT NULL,
'AlphaAcidMin' float(5,2) DEFAULT NULL,
'AlphaAcidMax' float(5,2) DEFAULT NULL,
'CohumuloneMin' int(3) DEFAULT NULL,
'CohumuloneMax' int(3) DEFAULT NULL,
PRIMARY KEY ('ProductID')
);

CREATE TABLE IF NOT EXISTS hopsdescriptors
('DescriptorID' int(5) NOT NULL AUTO_INCREMENT,
'HumanName' varchar(20) DEFAULT NULL,
'ShortDescript' varchar(500) DEFAULT NULL,
PRIMARY KEY ('DescriptorID')
);

I imagine this is a pretty common query to perform, however I'm brand new to this stuff and I haven't even been able to think about the question clearly enough to google it.

Use aromareference table to fetch all referenced records from both tables, hops and hopsdescriptors.

Then filter records by hops.HumanName (or whatever field of hops or hopsdecriptors), and show the required columns.

SELECT hd.HumanName
FROM aromareference a
     INNER JOIN hops h ON a.HopsId = h.ProductId
     INNER JOIN hopsdescriptors hd ON a.DescriptorID = hd.DescriptorID
WHERE h.HumanName = 'Centennial'


CRUD operations on json data I have a large chunk of json data associated with each id and I have to somehow get the data for my front end by querying through that large chunk.

My TaskDetails table has a structure something like:

id | data
----------------
1  | [{"name":"Roy","Country":"USA","Hobby":"Swim"},
     {"name":"Roy","Country":"USA","Hobby":"Cricket"},
     {"name":"Anam","country":"Greece","Hobby":"Polo"}]
2  | [{"Address":"Church Street","Sex":"M"},
     {"Address":"Amsterdam","Sex":"F"},
     {"Address":"MG Road","Sex":"M"}]

where id is of type integer and data is of type json. Each entry of json data has an array of records.

In the row with id=1 each record has keys (name,Country,Hobby) with respective values. Now this array can be larger, perhaps 10,000 records, all held in a single json row. Something like :

1 | [{"name":"Roy","country":"USA","Hobby":"Swim"},
    {"name":"Roy","country":"USA","hobby":"Cricket"},
    {"name":"Anam","country":"Greece","Hobby":"Polo"},
    {"name":"Roy","country":"USA","hobby":"Swim"},
    {"name":"Roy","country":"USA","hobby":"Cricket"},
    {"name":"Anam","country":"Greece","hobby":"Polo"} ,
    {"name":"Roy","country":"USA","hobby":"Swim"},
    {"name":"Roy","country":"USA","hobby":"Cricket"},
    {"name":"Anam","country":"Greece","hobby":"Polo"},
    {"name":"Roy","country":"USA","hobby":"Swim"},
    {"name":"Roy","country":"USA","hobby":"Cricket"},
    {"name":"Anam","country":"Greece","hobby":"Polo"}]

Now I want to do CRUD operations for the data in this single row.

If I query for all data which is has name="Roy" I should get all the records having name = "Roy" and the output should be:

{"name":"Roy","country":"USA","Hobby":"Swim"},
{"name":"Roy","country":"USA","hobby":"Cricket"},
{"name":"Roy","country":"USA","hobby":"Cricket"},
{"name":"Roy","country":"USA","hobby":"Swim"},
{"name":"Roy","country":"USA","hobby":"Cricket"},
{"name":"Roy","country":"USA","hobby":"Swim"},
{"name":"Roy","country":"USA","hobby":"Cricket"}

I am not able to form a query for this. I am trying with something like:

select *
from TaskDetails
where data->0->>'name'='Roy'

but it will not give me all the records. How should I query? I cannot use jsonb since our prod PostgreSQL DB is currently 9.3.4.

Matheus de Oliveira created handy functions for JSON CRUD operations in postgresql. They can be imported using the \i directive. Notice the jsonb fork of the functions if jsonb is your data type.

9.3 json
https://gist.github.com/matheusoliveira/9488951

(and while jsonb is not an option for Michael, for those who are using 9.4, there is a jsonb variant: https://gist.github.com/inindev/2219dff96851928c2282)

Operations Provided:

postgres=# SELECT json_append('{"a": 1}', '{"b": 2, "c": 3, "a": 4}');
        json_append
---------------------------
 {"a":1,"b":2,"c":3,"a":4}
(1 row)

postgres=# SELECT json_delete('{"b": 2, "c": 3, "a": 4}', '{b, c}');
 json_delete
-------------
 {"a":4}
(1 row)

postgres=# SELECT json_update('{"a": 1}', '{"b": 2, "c": 3, "a": 4}');
 json_update
-------------
 {"a":4}
(1 row)

postgres=# SELECT json_merge('{"a": 1}', '{"b": 2, "c": 3, "a": 4}');
     json_merge
---------------------
 {"b":2,"c":3,"a":4}
(1 row)


Simplest way to edit single entry in XML column? As I understand MS SQL Server Management Studio doesn't allow directly editing/replacing entries in XML columns via GUI (copy/paste doesn't work etc.).

What is the easiest option to replace a single entry in an XML column? Do I have to use something different from UPDATE/REPLACE for an XML column value?

If you just need to replace the whole piece of XML in once, then you can do a normal UPDATE, eg something like this:

UPDATE yourTable
SET yourXML = '<yourNewValidXML/>'
WHERE rowId = 1

If you need to edit individual attributes or elements then you can use the .modify method of the XML data-type in SQL Server to update single values.  Here's a simple example to get you started:

DECLARE @t TABLE ( rowId INT IDENTITY PRIMARY KEY, yourXML XML )

INSERT INTO @t ( yourXML )
VALUES ( '<Users>
    <User Name="Bob"></User>
    <User Name="Mikhail"></User>
    <User Name="John"></User>
    <User Name="Sue"></User>
</Users>' )

SELECT 'before' s, DATALENGTH(yourXML) dl, yourXML
FROM @t
WHERE rowId = 1

-- Update one attribute
UPDATE @t
SET yourXML.modify('replace value of (Users/User/@Name[.="Bob"])[1] with "wBob"')
WHERE rowId = 1

SELECT 'after' s, DATALENGTH(yourXML) dl, yourXML
FROM @t
WHERE rowId = 1

If you need more help, please post a small sample of your XML and your expected results.


Efficiently get first 10 results and total number of results For a search page, I need the first 10 results and the total number of results of a query.

That part is easy:

SELECT TOP(10) ... FROM ... WHERE ... ORDER BY ...
SELECT COUNT(*) FROM ... WHERE ...

But it's also amazingly slow. Both statements take about the same time.

So I found this one:

SELECT TOP(10) ... FROM ... WHERE ... ORDER BY ...
SELECT @@ROWCOUNT

That cuts down the time by ~50%. Great. But unfortunately the rowcount returned is 10. If I remove the "TOP(10)" the rowcount is correct, but the timespan is more or less the same as in the original version. (Even though I only access the first 10 rows.)

That's frustrating, because the total-number-of-lines info must exist, even in the TOP(10) version. (At least I think so, because we use a very complex ORDER BY clause, with 3 "CASE" conditions to sort empty data to the bottom)

Is there an efficient way to get both? TOP(10) data and total number?

If your ORDER BY and WHERE clauses are complicated, then proposed solution should cut time in half:
1. Create a new table for intermittent results (it can be a temp table, table variable or memory optimized table with session id marker and schema_only setting).
2. Insert all rows from source query into this table, including order by expressions as additional columns.
3. Run your count and top 10 query from this new table.


mysqladmin not taking inline password I am trying to set a cron job for taking backup from my slave machine. So I need to stop the slave

I issued a command

mysqladmin --user=root --password=test_pass stop-slave

But it is throwing error:

  mysqladmin: connect to server at 'localhost' failed error: 'Access
  denied for user 'root'@'localhost' (using password: YES)'

Now I tried with the command

mysqladmin --user=root --password stop-slave

It prompts for password and I gave as test_pass and everything was fine.

Why so happened? What is the alternative?

NB: BTW my mysql version is mysql-5.0.95-5, it it make any sense.

Command line arguments are subject to interpretation by the system's command shell, changing the behavior of the command or changing the value of the arguments before they are passed into the called program.

When an argument (such as the value for --password) contains an character that the shell may interpret, they need to be either quoted (usually enclosed in single quotes ' in unix or double quote " in Windows) or escaped individually (usually with a backslash \ before each metacharacter) to avoid interpretation by the shell.

While the specific characters are system-specific, some characters to watch out for include:

$ & ! \ [ ] < > `

If the password, for a really bad example, were set to pa$$word ...

mysql --password=pa$$word     # does not work
mysql --password='pa$$word'   # works
mysql --password=pa\$\$word   # works, but slightly unclear what's going on at first glance

Further reading:

http://www.gnu.org/software/bash/manual/bashref.html#Quoting
http://www.sal.ksu.edu/faculty/tim/unix_sg/shell/metachar.html
http://www.grymoire.com/Unix/Quote.html

Update: to escape either ' single or " double quotes in the password, you can either escape them with a leading backslash, or enclose the entire argument in the opposite style of quotes if there are no other characters that the chosen style of quoting isn't compatible with.

mysql --password="like'this" # password has a single quote in the middle
mysql --password='like"this' # password with a double quote in the middle

If you have a single quote and other special characters as well, you're stuck with backslash escaping because, in unix, the double quote is "weaker" than a single quote and many metacharacters are still expanded when enclosed in double quotes but not single quotes.

This is not MySQL-specific but applies to anything with command line arguments.

You can typically use the echo command to see how the shell is interpreting your arguments.

$ echo foo$bar
foo                # literal 'foo' plus the (empty) shell variable $bar

$ echo foo\$bar
foo$bar            # backslash prevents expansion of $bar as a variable

$ echo "foo$$bar"  # weaker double quote doesn't prevent expansion so
foo9691bar         # the $$ expands to the unix process id (pid) of the current shell

$ echo 'foo$$bar'
foo$$bar           # "stronger" single quote prevents shell expansion

$ echo "foo'bar"
foo'bar            # double quote allows single quote within the literal

Follow-up: The bash shell (and presumably some others) allows escaping single quotes within single-quoted strings, though the convention is bizarre (probably based on some long-forgotten decision lost now to the mists of time):

Replace each ' inside the string with '\'' before wrapping the entire string in single quotes... so the literal string foo'bar is expressed as 'foo'\''bar'.

Like I said, bizarre.  This is needed because a backslash escapes a single quote outside of a single quoted string,  a backslash escapes nothing inside a single-quoted string in bash, and single quoted strings can be closed and reopened by multiple single quotes as long as there are no unescaped intervening characters that have special meaning.  So '\'' closes the quoting of the string, then supplies the escaped literal, then reopens the quoting of the string.


Benefits of using WITH TABLOCK on an INSERT Under some circumstances, doing an INSERT INTO <tablename> (WITH TABLOCK) will be faster due to minimal logging. Those circumstances include having the database in the BULK_LOGGED recovery model.

Is there any other potential performance benefit to using WITH TABLOCK on an INSERT on an empty table when the database (tempdb) is using the SIMPLE recovery model?

I'm working with SQL Server 2012 Standard Edition.

My use case is for creating and then immediately populating a temp table within a stored procedure using an INSERT...SELECT, which could contain as many as a few million rows. I try to avoid that kind of tempdb abuse, but it is sometimes needed.

I'm trying to build a case to require TABLOCK. It doesn't seem like it would hurt anything, and might have a benefit. I'm trying to figure out if there is enough potential benefit to add it wherever throughout our code base, where I'm sure there is no other process that wants to write to the table.

I'm usually inserting into a newly created local temp table with a clustered PK, but do sometimes use a heap.

I know of a few benefits but they're mostly situational.

Using TABLOCK will reduce concurrency but will immediately take a table lock on the target table. As long as you can guarantee that just one session will insert into the table this will avoid unnecessary row or page locks and will prevent lock escalation. After all, if you are inserting so much data that you'll get lock escalation anyway why not do it upfront?
If you are inserting into an empty page-compressed heap without TABLOCK all of the pages will have row compression instead of page compression:

The newly inserted row is page-compressed:

if new row goes to an existing page with page compression

if the new row is inserted through BULK INSERT with TABLOCK

if the new row is inserted through INSERT INTO ... (TABLOCK) SELECT FROM

Otherwise, the row is row-compressed.

In SQL Server 2016 the TABLOCK hint is required to get parallel insert into heaps, CCIs (Clustered Columnstore Indices), and local temp tables. There are lots of restrictions, some of which aren't documented. There can't be an IDENTITY column, the insert can't be done through an OUTPUT, etc.

See also The Data Loading Performance Guide


"Unrelated" INSERT and UPDATE blocking each other Repro scenario:

CREATE TABLE test (
  ID int IDENTITY(1,1),
  mykey nvarchar(255) NOT NULL,
  exp_date datetime,
PRIMARY KEY (ID));
GO

CREATE INDEX not_expired_keys ON test (exp_date, mykey);

INSERT INTO test (mykey, exp_date) VALUES ('A', NULL);

I start transaction 1:

-- add key B
BEGIN TRANSACTION;
INSERT INTO test (mykey, exp_date) VALUES ('B', NULL);
...

and then execute transaction 2 in parallel:

-- expire key A
BEGIN TRANSACTION;
UPDATE test SET exp_date = GETDATE() WHERE exp_date IS NULL AND mykey = 'A'; -- <-- Blocking
ROLLBACK;

As it turns out, transaction 1's uncommitted INSERT blocks transaction 2's UPDATE, even though they affect disjoint sets of rows (mykey = 'B' vs. mykey = 'A').

Observations:

The blocking also occurs on the lowest transaction isolation level READ UNCOMMITTED.
The blocking goes away if I put a unique index on mykey. Unfortunately, I cannot do that, since key names can be reused once a key has expired.

My questions:

(Out of curiosity:) Why do these statements block each other even on the READ UNCOMMITTED level?
Is there an easy and reliable way to make them not block each other?

Lets take a look on the execution plans.

1st query - Insert

BEGIN TRANSACTION;
INSERT INTO test (mykey, exp_date) VALUES ('B', NULL);

And its execution plan

We see that sql server is doing Clustered Index Insert operation.

Now lets take a look on update

BEGIN TRANSACTION;
UPDATE test
SET exp_date = GETDATE()
WHERE exp_date IS NULL AND mykey = 'A' -- <-- Blocking

And its execution plan

SQL Server scans the Clustered Index of the table, and put U lock on it, even if it can choose another index to find the matching rows. The reason is, because we have only 1 row in the table, and SQL Server Optimizer find easier to scan the Clustered Index instead of searching data in the non-clustered index.

But what if we force the sql server to use non-clustered index?

BEGIN TRANSACTION;
UPDATE test
SET exp_date = GETDATE()
FROM test WITH(INDEX = not_expired_keys)
WHERE exp_date IS NULL AND mykey = 'A' -- <-- No Blocking!!!

And its execution plan

I think if we put more rows in the table SQL Server will choose the non-clustered index for finding the rows that must be updated, and there will be no blocking.


What is the effect of replacing indexes with filtered (non-null value) indexes? Our project runs a very large, very complicated database. So about a month ago, we noticed that the space used by indexed columns containing null values was getting too large. As a response to that, I wrote as script that would dynamically search through all single-column indexes containing more than 1% of null values, then drop and recreate those indexes as filtered indexes on the condition that the value was NOT NULL. This would drop and recreate hundreds of indexes throughout the database and typically free up almost 15% of the space used by the whole DB.

Now I have two questions about this:

A) What are the downsides of using filtered indexes in this fashion? I would assume that it would only improve performance, but are there any performance risks involved?

B) We received errors ('can't drop index XYZ beause it does not exist or you do not have permission') on dropping and recreating the indexes, even though when checked afterwards, everything had gone exactly as expected. How can this happen?

Thanks for any help!

Edit: In response to @Thomas Kejser

Hi and thanks, but it turns out this was a disaster. At the time we didn't understand several things such as:

During a query, SQLOS makes index plans prior to determining that it cannot use NULL values for joining table columns. IE, you truly do need to have a WHERE clause filter fitting the index for each and every filtered index used in the query, or the index will not be used at all.
Dropping and creating indexes and redundantly updating their statistics yet again afterwards still may not be enough to produce the updated plans, which we assumed they would. It appears in some cases only a high enough workload will force SQL Server to reassess the plans.
There are some exotics to the functionality of the execution planner that are difficult to determine by common sense and logic alone. With thousands of code-behind -generated variations of different queries even, seemingly useless indexes can help in some statistics and query plans that end up being used in critical queries.

In the end, these changes were reverted. So filtered indexes are a powerful tool, but you need to truly understand exactly what data is being fetched from those columns. Where normal indexes aside from the space issues are rather easy to apply, filtered indexes represent very customized solutions. They are certainly not a replacement for a regular index, rather an extension to them in those special circumstances they're required.

Very interesting approach. My upvote for the creativity.

Since you reclaimed the space, I assume the original indexes are no longer in place? The downsides of filtered indexes then are:

Too many of them may cause the search space of the optimiser to grow too large, leading to poor query plans as the optimiser times out
There are several situations where a filtered index will not even be considered, even though the non-filtered equivalent would be. Notably, this can happen when you get a hash join on the indexed column or if you try to ORDER BY the column (without a filter)
Query parameterisation doesn't work with filtered indexes (see: http://www.sqlservercentral.com/blogs/practicalsqldba/2013/04/08/sql-server-part-9-filtered-index-a-new-way-for-performance-improvemnt/)

In practical terms, this means that you have to be extremely careful with filtered indexes as they will often result in horrible query plans. I would not go so far as to call them useless, but I view them as an addition to traditional indexes, not as a replacement (as you are trying to do).


Why PostgreSQL's service are different between system service and pg_ctl? Installed PostgreSQL 10 by a normal user.

Enabled and started its service:

sudo systemctl start postgresql-10
sudo systemctl enable postgresql-10

Then login with postgres user:

sudo su - postgres

Add this setting to .bash_profile file:

export PATH=$PATH:/usr/pgsql-10/bin/

Restart PostgreSQL by pg_ctl:

-bash-4.2$ pg_ctl restart

Then exit to normal user and check postgresql's status again, it failed. Even use this way to restart can't success:

sudo systemctl restart postgresql-10
sudo systemctl stop postgresql-10

It always failed.

But if I use postgres user to test restart, stop, start, all of them can success.

These 2 ways aren't controlling the same process?

Add postgresql-10 service content

$ sudo systemctl cat postgresql-10.service

# /usr/lib/systemd/system/postgresql-10.service
# It's not recommended to modify this file in-place, because it will be
# overwritten during package upgrades.  If you want to customize, the
# best way is to create a file "/etc/systemd/system/postgresql-10.service",
# containing
#       .include /usr/lib/systemd/system/postgresql-10.service
#       ...make your changes here...
# For more info about custom unit files, see
# http://fedoraproject.org/wiki/Systemd#How_do_I_customize_a_unit_file.2F_add_a_custom_unit_file.3F

# Note: changing PGDATA will typically require adjusting SELinux
# configuration as well.

# Note: do not use a PGDATA pathname containing spaces, or you will
# break postgresql-setup.
[Unit]
Description=PostgreSQL 10 database server
Documentation=https://www.postgresql.org/docs/10/static/
After=syslog.target
After=network.target

[Service]
Type=notify

User=postgres
Group=postgres

# Note: avoid inserting whitespace in these Environment= lines, or you may
# break postgresql-setup.

# Location of database directory
Environment=PGDATA=/var/lib/pgsql/10/data/

# Where to send early-startup messages from the server (before the logging
# options of postgresql.conf take effect)
# This is normally controlled by the global default set by systemd
# StandardOutput=syslog

# Disable OOM kill on the postmaster
OOMScoreAdjust=-1000
Environment=PG_OOM_ADJUST_FILE=/proc/self/oom_score_adj
Environment=PG_OOM_ADJUST_VALUE=0

ExecStartPre=/usr/pgsql-10/bin/postgresql-10-check-db-dir ${PGDATA}
ExecStart=/usr/pgsql-10/bin/postmaster -D ${PGDATA}
ExecReload=/bin/kill -HUP $MAINPID
KillMode=mixed
KillSignal=SIGINT

# Do not set any timeout value, so that systemd will not kill postmaster
# during crash recovery.
TimeoutSec=0

[Install]
WantedBy=multi-user.target

If you start the server manually using pg_ctl, systemd doesn't know about it.

It will happily try to start the server, which will fail (because the server is already running), and it cannot stop the server, because it does not know it is running.

So always use systemctl when starting and stopping the service. Reloading with pg_ctl reload is fine.


Must be superuser after GRANT SELECT ON pg_settings(PostgreSQL) PostgreSQL - 9.6

I connect as postgres to userdb, add grant select pg_settings for user.

➤ psql://postgres@postgresql-96.local:5432/postgres

# \c userdb
You are now connected to database "userdb" as user "postgres".

➤ psql://postgres@postgresql-96.local:5432/userdb

# GRANT SELECT ON pg_settings TO user;
GRANT
Time: 7.728 ms

➤ psql://postgres@postgresql-96.local:5432/userdb

# \q

After i connect as user to userdb and try get hba_file

psql -h postgresql-96.local -p 5432 -U user -d userdb
Password for user user:
psql (9.6.6)
Type "help" for help.

userdb=> SHOW hba_file;
ERROR:  must be superuser to examine "hba_file"
userdb=>

userdb=> select setting from pg_settings where name='hba_file';
 setting
---------
(0 rows)

But i cannot show, get, view hba_file.
How get hba_file as user?
Thank you

I am not entirely sure, but I think you can't get around the "superuser" check for those settings.

With Postgres 10 you could use the new default role pg_read_all_settings but if upgrading is not an option now, the easiest way would be to create a function that runs with superuser privileges.

Create the following function as the superuser:

create function public.list_settings()
  returns setof pg_catalog.pg_settings
as
$$
  select *
  from pg_catalog.pg_settings;
$$
language sql
security definer;

The  security definer means that the function always runs with the privileges of the user who created the function (the superuser).

Then grant execute on that function to your user:

grant execute on function public.list_settings() to the_user;

The the regular user can do:

select *
from public.list_settings()
where name = 'hba_file';

Of course you could create the function in a different schema, e.g. admin


Select postgresql within 1 hour range of current date GOAL

I have a table which is fed by a collection of an agent ZABBIX, with a   TIMEOUT of 3 seconds.

I would like to do aSELECT within a range of 1 hour in 1 hour always from the current date of the execution of SELECT, the query below does not report any errors, but does not bring any information.

SQL

 SELECT
     *
 FROM
     history
 WHERE
     "clock"> = now () - interval '60 minutes';

DATE

 2019-10-28 08:10:00.424430
 2019-10-28 08:11:03.424523
 2019-10-28 08:12:06.424596
 ...
 2019-10-28 09:10:00.424430
 2019-10-28 09:11:03.424523
 2019-10-28 09:12:06.424596

DESIRED OUTPUT

 2019-10-28 08:00:03.424439
 2019-10-28 09:00:03.424522
 2019-10-28 10:00:03.424591

NOTE: The clock column of thehistory table is TIMESTAMP

Maybe you need something like

SELECT MIN(clock)
FROM history
WHERE date_trunc('day', clock) = CURRENT_DATE
/* or WHERE clock >= CURRENT_DATE */
GROUP BY date_trunc('hour', clock)

?


How to change owner of a table within a PostgreSQL Database on Azure? I attempting to restore a database into a PostgreSQL instance on Azure.

I am issuing these statements as postgres and attempting to change owner to another role that was a superuser.

My restore errors on this line (actual names changed to protect the innocent):

ALTER TABLE schema_m.table_a OWNER TO role_a;

SQL Error [42501]: ERROR: must be member of role "role_a"
I've tried:  GRANT postgres TO role_a GRANTED BY postgres;

I believe the issue is related to the postgres user not being a superuser on Azure PostgreSQL, but maybe I'm wrong.

I've also tried granted all privs to both:

 GRANT ALL PRIVILEGES ON SCHEMA  schema_m TO role_a;
 GRANT ALL PRIVILEGES ON SCHEMA  schema_m  TO postgres;

But I still receive the error. Am I missing something obvious?

PostgreSQL 9.5.14, compiled by Visual C++ build 1800, 64-bit

That doesn't seem to be specific to Azure. It's a general rule in Postgres: Only the owner or a superuser can use ALTER TABLE.

To verify your role is a superuser (or not), run in the current session:

SELECT rolsuper FROM pg_roles WHERE rolname = current_user;

Related:

Give particular privileges for a table to existing role


What is most efficient way to compare two large result sets in SQL Server 2012 Current advice for the most efficient way to compare two large result/row sets seems to be to use the EXCEPT operator. This self contained SQL script below gets very inefficient as row sizes increase (change @last values). I have tried to find unique entries in a combined table but with no improvement.

DECLARE @first AS INT, @step AS INT, @last AS INT;

-- This script is comparing two record sets using EXCEPT
-- I want to find additions from OLD to NEW
-- As number of rows increase performance gets terrible
-- I don't have to use two tables. I could use one combined table but I want the same result as quickly as possible

-- Compare 100 to 110 rows - 0 seconds
-- Compare 1000 to 1010 rows - 1 seconds
-- Compare 10000 to 10010 rows - 16 seconds
-- Compare 100000 to 100010 rows - ABORT after 8 minutes (tables are populated in 18 seconds)

DECLARE @temptableOLD TABLE ([Result1] int);
SET @step = 1;  SET @first = 1; SET @last = 100000
WHILE(@first <= @last) BEGIN INSERT INTO @temptableOLD VALUES(@first) SET @first += @step END

DECLARE @temptableNEW TABLE ([Result1] int);
SET @step = 1;  SET @first = 1; SET @last = 100010
WHILE(@first <= @last) BEGIN INSERT INTO @temptableNEW VALUES(@first) SET @first += @step END

select * from @temptableNEW
except
select * from @temptableOLD

EXCEPT implies a DISTINCT operation.

I would use NOT EXISTS if this is not actually required.

However the problem you are having is likely that you are getting a nested loops on an unindexed table due to the poor cardinality estimates associated with table variables.

select * from @temptableNEW
except
select * from @temptableOLD
OPTION (RECOMPILE)

Will be able to take account that the tables have 100K rows each and give a different plan.

In SQL Server 2012 you can only add indexes to table variables via constraints. If the values are unique you could use

DECLARE @temptableOLD TABLE ([Result1] int UNIQUE CLUSTERED);

to add an index. If done on both tables the plan (after the recompile hint is added) will likely use a merge join instead. Without any indexes I would expect a hash join.


Database size reduced after backup on PostgreSQL 8.3 and restore in PostgreSQL 9.4 I did a pg_dump on a JIRA database that I was hosting in a PostgreSQL 8.3 server. Size of the database after vacuum full was 217132652 (approximately 207 MB).

Then I restored that JIRA database on a PostgreSQL 9.4 server with the following command:

$ psql -X -v ON_ERROR_STOP=1 -d jira2 -U jira -h localhost < jiradb2017_03_12.sql

I assume the restore would exit on any error since I used ON_ERROR_STOP=1, but the SQL script finished correctly (despite some warnings not related to data restore).

I ended up with a database with a size of 158019348 (approximately 151 MB).

So, what's the story here? Can I just assume database was restored successfully and PostgreSQL optimized its storage (somewhere between 8.3 and 9.4 versions) engine and is using space more efficiently?

When you restore a database you have all the information on it packed, with no empty space between rows (or in indices), unless some specific settings are in place (basically: FILLFACTOR for tables and FILLFACTOR for indices).

On the other hand, when your database has been in use for some time, and you've had your share of inserts, updates and deletes, free unused space will appear. This is because of the way PostgreSQL and Multiversion Concurrency Control, a.k.a. MVCC work. MVCC allows for less lockings, which basically means you save  time. But you pay a price in terms of space:

Every UPDATE is equivalent to an INSERT together with a DELETE, with the overhead (at least in terms of space used) associated to both.
When you have several transactions running, and every one is INSERTing, UPDATEing or DELETEing, you have simultaneously several copies of every row involved.
The space allocated to these row versions won't be freed immediately after commit, and for a while, will be unused space within the files where your table data (and indexes) is being stored.

Autovacuum takes care of this space being made reusable by default, or you could have some specific procedure for routine vacuuming.

This fact already can explain the size change.

Optimizations between versions probably also took place; and can explain further improvements. Optimizations could also have been made for speed and not for size, and the actual size could actually grow from one version to the next. I really don't know the specifics to be able to tell; although the comment from @Erwin states that both changes making your tables shrink and changes making your tables bloat (grow) have taken place since version 8.3.

To distinguish between the two effects, if you're curious, you could just, as @Jack Douglas suggests, restore your database on 8.3. It will most probably shrink in size. If it shrinks to less than 151 MB (a size smaller that what you get with 9.4 version), then the removal of unused space made your DB shrink, and the version change actually made your DB grow.

For a better understanding of MVCC, look at Bruce Momjian's presentation.


Why does this CTE return the bulk of its results immediately, but then take several minutes to finish? I've got a schema roughly like this (this is a simplification of my actual schema):

CREATE TABLE foo (
    key1 NUMERIC(6) NOT NULL,
    key2 VARCHAR(32) NOT NULL,
    val VARCHAR(255) NULL,
    CONSTRAINT foo_pk PRIMARY KEY (key1, key2) --PK on key1, key2
    )
GO
CREATE TABLE bar (
    key1 NUMERIC(6) NOT NULL,
    key2 VARCHAR(32) NOT NULL,
    val VARCHAR(255) NULL,
    CONSTRAINT bar_pk PRIMARY KEY (key1, key2) --PK on key1, key2
    )
GO
CREATE TABLE aliases (
    id VARCHAR(32) NOT NULL PRIMARY KEY,
    text VARCHAR(255) NOT NULL,
    CONSTRAINT aliases_uk UNIQUE (text) --PK on id, unique constraint on text
    )
GO

INSERT INTO aliases (id, text)
SELECT REPLACE(NEWID(), '-', ''), 'Text1' UNION ALL
SELECT REPLACE(NEWID(), '-', ''), 'Text2' UNION ALL
SELECT REPLACE(NEWID(), '-', ''), 'Text3' UNION ALL
SELECT REPLACE(NEWID(), '-', ''), 'Text4' UNION ALL
SELECT REPLACE(NEWID(), '-', ''), 'Text5' UNION ALL
SELECT REPLACE(NEWID(), '-', ''), 'Text6' UNION ALL
SELECT REPLACE(NEWID(), '-', ''), 'Text7' UNION ALL
SELECT REPLACE(NEWID(), '-', ''), 'Text8' UNION ALL
SELECT REPLACE(NEWID(), '-', ''), 'Text9' UNION ALL
SELECT REPLACE(NEWID(), '-', ''), 'Text10' UNION ALL
SELECT REPLACE(NEWID(), '-', ''), 'Text11' UNION ALL
SELECT REPLACE(NEWID(), '-', ''), 'Text12' UNION ALL
SELECT REPLACE(NEWID(), '-', ''), 'Text13' UNION ALL
SELECT REPLACE(NEWID(), '-', ''), 'Text14' UNION ALL
SELECT REPLACE(NEWID(), '-', ''), 'Text15' UNION ALL
SELECT REPLACE(NEWID(), '-', ''), 'Text16';
GO

BEGIN
    DECLARE @i INT = 1;
    WHILE (@i <= 234)
    BEGIN
        INSERT INTO foo (key1, key2, val) SELECT @i, id, id FROM aliases;
        SET @i = @i + 1;
    END;
    INSERT INTO bar (key1, key2, val) SELECT key1 + 234, key2, val FROM foo;
END;
GO

I want to get a list of all key1 and key2 pairs (using the user-friendly names for key2, with the user-friendly aliases for the vals concatenated. My initial attempt looks like this:

WITH
foos_and_bars (key1, key2, val) AS (
    SELECT key1, key2, val FROM foo UNION ALL
    SELECT key1, key2, val FROM bar),
texts (key1, key2_name, val_text, rnum) AS (
    SELECT key1, a1.text, a2.text, ROW_NUMBER() OVER (PARTITION BY key1, key2 ORDER BY val)
    FROM foos_and_bars
    JOIN aliases a1 ON a1.id = foos_and_bars.key2
    LEFT JOIN aliases a2 ON a2.id = foos_and_bars.val),
partitioned (key1, key2_name, val_text, rnum, maxnum) AS (
    SELECT key1, key2_name, val_text, rnum, MAX(rnum) OVER (PARTITION BY key1, key2_name)
    FROM texts),
recurse (key1, key2_name, val_texts, rnum, maxnum) AS (
    SELECT key1, key2_name, val_text, rnum, maxnum
    FROM partitioned
    WHERE rnum = 1
    UNION ALL
    SELECT r.key1,
        r.key2_name,
        CAST(r.val_texts + CHAR(13) + CHAR(10) + p.val_text AS VARCHAR(255)),
        p.rnum,
        r.maxnum
    FROM recurse r
    JOIN partitioned p
    ON p.key1 = r.key1
    AND p.key2_name = r.key2_name
    AND p.rnum = r.rnum + 1)
SELECT * FROM recurse WHERE rnum = maxnum;

It works, and I get 7480 rows in less than a second, but then SSMS runs for no less than 2 minutes and 40 seconds puzzling out the last 8 rows. These last rows are no more complex than the first (in fact, with the data currently in the system, rnum and maxnum are never greater than 1), and as these tables were created exclusively for mocking on this SE question, there should be no locks.

The issue seems to be limited to the recursive CTE, as selecting from the top down to partitioned yields 7488 rows in less than a second. What could the CTE be getting stuck on?

Execution plans (using a setup that matches the simplified schema):

Plan for all records
Records for only one key1

The output is going into a view that is going to back a Sybase window, which will be used for searching records. As such, it needs to be capable of retrieving all the rows, though the program pulls 1000 rows at a time by default.

The anchor part of the recursive SQL query produces all 7488 rows. On my machine, that part of the query finishes in under 100 ms. SSMS does not immediately show all 7488 rows in the grid results. It only shows 7480 for me as well. I suspect this happens because the results are sent in packets and the remaining 8 rows aren't enough to fill a packet.

The second half of the query is extremely expensive compared to the first. Looking at the execution plan you can see six clustered index scans on the inner side of a nested loop. Those scans will execute at least once for every row from the anchor part. SQL Server does a total of 44928 scans over a few minutes to ultimately produce 0 rows from that part. Once that part of the execution is complete SQL Server sends the remaining packet of rows to the client and you see all 7488 rows in the results grid of the completed query. It's not true that it takes three minutes to find the last 8 eight rows. It finds them almost instantly. It just takes three minutes to send them.

You can see a large amount of work performed in the bottom half of the query by looking at the actual plan. Note the thickness of the arrows and the number of executions for the scan operators:

That's why the query is slow. I don't have any advice for you to make it fast because I don't understand what you're trying to accomplish with this query.


Why doesn't my trigger put data into my table? When I execute my trigger, it says it executed commands, but there is still no data in the tAttackPlan where it is supposed to insert data into. Can not figure out why.

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

IF OBJECT_ID ('trAttack', 'tr') IS NOT NULL
    DROP TRIGGER trAttack
GO
CREATE TRIGGER trAttack
   ON tAttackPlan
   AFTER INSERT, UPDATE
AS
BEGIN

    SET NOCOUNT ON;
    DECLARE @AttackRequirementID INT, @MinionID INT, @DomainID INT, @MinimumCount INT, @Count INT, @MinionID_min INT
    SELECT @DomainID = (SELECT @DomainID FROM inserted)
    SELECT @Count = (SELECT SUM(Count) FROM tCensus GROUP BY MinionID)
    SELECT @MinionID_min = tMinion.MinionID
    FROM dbo.tMinion
    INNER JOIN dbo.tCensus ON dbo.tMinion.MinionID = dbo.tCensus.MinionID
    CROSS JOIN dbo.tAttackRequirement
    CROSS JOIN dbo.tAttackPlan
    INNER JOIN dbo.tDomain ON dbo.tAttackPlan.DomainID = dbo.tDomain.DomainID

    INSERT INTO tAttackPlan (DateOfAttack, DomainID)
    VALUES (1050, @DomainID)

    IF @Count < @MinimumCount
        RAISERROR ('There are not enough minions to attack', 16, 10)
END
GO

What learning_DBAdmin is saying makes sense...
if this is the only script you are running, it will only create the trigger...
when you insert or update the table tAttackPlan, it will trigger the trAttack trigger.

Now let's analyze the trigger... If an insert or update is done on the tAttackPlan table the trigger will insert into tAttackPlan the attack date and domainID... Is this what you really want? It seems to be recursive to me but not sure of the whole story behind this piece of code. ... What I see is if your trigger is triggered it will insert into the tAttackPlan table which will trigger the trAttack again, and so on and so forth.

Also, note that a trigger has 2 internal tables inserted and deleted, which define the new value of the insert/update (in inserted) and the old value or records to delete (in deleted)...

Please check Microsoft sites to see how triggers work.


Does the version of a SQL Server Witness matter? Can you have a SQL Server witness in a mirror setup which has a higher SQL Server version than the mirror?

Ie. the mirror is on SQL Server 2012 Standard and the witness uses SQL Server 2014 Express.

From BOL :

  •For a mirroring session to be established, the partners and the witness, if any, must be running on the same version of SQL Server.)

Note: Express edition of the same sql server version is supported.

Edit: based on the comment from @Raidri

Technically it is possible to have a witness with a higher version of sql server than the 2 partners, but it is highly recommend and supported that all servers have the same VERSION of sql server running - edition can be different.

  Except during an upgrade from an earlier version of SQL Server, the server instances in a mirroring session must all be running the same version of SQL Server. For example, a SQL Server 2008 witness is supported when you are upgrading from a SQL Server 2008 mirroring configuration but cannot be added to an existing or new SQL Server 2008 R2 or later mirroring configuration.


Need help designing table with list of IDs to store I have a table that I need to create to hold saved emails for FUTURE delivery (to, from, message, scheduled send date, etc).

The catch here is that I don't know who's supposed to receive the email until the day of delivery.

What I mean is, the email is created to go to certain selected organizations, but the "members" of the organization will be constantly joining and leaving, so if someone creates an email for delivery next month, the list of member email addresses to send to by then will be different.

So, what I need to save in the table is the list of organizations that the email should go to, so that I can query for the latest list of member email addresses when I actually send the email.  Hope that makes sense.

Anyway, so my question is: what is considered a "proper design" for this?

My initial thought is to just save a comma delimited list of organization IDs.

I know I will never have to search on which organizations were on the list, so I don't care if it's not query-able, and I know I could normalize it into one row per recipient organization, but it seems such an unnecessary repeat of data for no purpose, especially since I only query on the SENDER not the recipients.

So is a list of IDs just a horrible, no good, only-a-newbie-would-think-of-that, bad thing? Or can it be used in some cases? Or is there some other way to do this that I don't know about? I'm sure I can't be the only one who's run into a situation like this before!

Do not use a list of ids. You will regret it. Use junction tables.

DDL:

create table organization (
  organization_id bigserial primary key,
  name text not null
);

create table individual (
  individual_id bigserial primary key,
  email_address varchar(255) not null
);

create table organization_member (
  organization_id bigint references organization(organization_id),
  individual_id bigint references individual(individual_id),

  primary key (organization_id, individual_id)
);

create table email_message (
  email_message_id bigserial primary key,
  send_at datetime not null,
  subject varchar(80),
  body text
);

create table email_message_organization (
  email_message_id bigint references email_message(email_message_id),
  organization_id bigint references organization(organization_id),

  primary key (email_message_id, organization_id)
);

DML:

-- add an org:

insert into organization (name) values ('Acme, Inc');

-- add an individual:

insert into individual (email_address) values ('user@example.com');

-- associate individual w org:

insert into organization_member values (1,1);

-- add an email message:

insert into email_message (send_at, subject) values ('2013-07-31 12:12:12', 'test');

-- associate an email message with an org:

insert into email_message_organization values (1, 1);

-- when it's time to send the email, get the right people:

select
e.subject,
e.body,
i.email_address
from individual i
join organization_member om using (individual_id)
join email_message_organization emo using (organization_id)
join email_message e using (email_message_id);

-- send the emails, record that they were sent in another table

You should use the Party model for representing individuals and organizations, which is not what I used, but you should still look it up.


Database Transfer from PostgreSQL to MySQL Is there any method to tranfer the data and schema from a postgresql database to mysql?

Pg_dump will create a plaintext SQL script with the statements needed to re-create the  schema and the data. You can then import this script into mysql. You may need to tweak the script in certain places due to postgres/mysql differences.


Products relation structure I'm trying to design a products relations table structure. There is an existing product table that has the following fields:

A product could be related to other product but also to a category or a subcategory. Or even, have the three type or just two type of relations at same time.

At the same time, a product subcategory could have a relation to other subcategory or category also to a product.

The main goal for what this is for is when a product is selected by the user, he can get suggestions to other product related to the one selected.

So whats the best table structure you can suggest to store those relations?

I was thinkin some like this:

But I dont know if thats reliable way.

Thank you in advance!

EDIT:

Maybe I did not explain well. Lets say a product x is related to products in Category Y and/or also related to products in Subcategory Z. But could also be related directly to a single product. There are chances to a product have the three types of relations or only two or one of them.

And the Subcategory field of product could be have the three types of relations too.. Or none of them..

Here the more clear and powerful model for you domain


How to Save JSON Data in MySQL? I receive a series of JSONAPI (https://jsonapi.org/format/) documents as a response to an API call that I make in my application. I need to save the API responses to my database so I can reference it again in the future (I cannot make another API call that can guarantee that I get the same data again). In the JSON documents, there will be a series of unique ids that I can use as part of a LIKE query.

Is it a good idea to save a JSON string in a column of a table? One benefit of it is that I can save the entire JSON in a single row/column. I'm concerned about scaling -- and how LIKE once I start having a lot of rows/columns of JSON documents that I need to search.

Another option is to loop through each element in the JSONAPI's data element and save all of the data in their own columns and relationship tables. This feels like a more structured way to save the data, but I'm concerned it's overkill since I really only need to reference the JSON document on occasion and can use a LIKE.

Is there a best practice for this?

I don't think there's anything wrong with storing JSON objects in a DB record. Though I would use the actual JSON type, rather than just varchar or something of that sort. Using a JSON field type allows you to use MySQL functions like JSON_CONTAINS to search that object.

Depending on what, and how frequently you need to search this data, I would consider just pulling out the particular data you need to search through, and store that as it's own column alongside the whole JSON object. This way, you can easily search for what you need, and still keep the entire object in the event you need to reference it in the future.


Where are Full-Text indexes in SSMS 2008 R2 I created a new database, a few tables, and full-text index & catalog using SQL Server Management Studio without any problem.  I wanted to copy the T-SQL creation scripts for each of those to include them in my documentation.  I can get the creation scripts for the database, tables, foreign keys, and the catalog but I can't seem to find the full-text index.  I've checked the related table's Script Table As->CREATE To and it's not there, nor is it with the Catalog.  Any ideas?  Is it because I'm only running SQL Server Standard edition?

In SSMS

Tools
Options
"SQL Server Object Explorer"
"Script full text catalogs"

Default is "false"...


FLUSH TABLES WITH READ LOCK not working? I use this command to "lock" my database, flush logs , take a LVM snapshot and release it.
Testing the recover + binlogs , appear it's not working.
(I discovery this when during the recover, applying the binlogs give error of  duplicate key where supose not should exists at that moment)
The behave I get is the data files still be writting after the flush.
So , what's wrong here?
I miss something?
# echo "FLUSH TABLES WITH READ LOCK ; flush logs" | mysql -vvv
--------------
FLUSH TABLES WITH READ LOCK
--------------

Query OK, 0 rows affected (0.00 sec)

# mysql --version
mysql  Ver 14.14 Distrib 5.6.25, for Linux (x86_64) using  EditLine wrapper

I not sure , but after flush with read lock , if I connect to any database and try create a table, it's should lock and wait until the UNLOCK , don't ?
This isn't happen, it create the table successfully.
EDIT
I found a documentation where say to run SET GLOBAL read_only = ON after the flush and SET GLOBAL read_only = OFF.
All other suggestions, blogs, tutorials, manuals don't mention this is needed.
More doubts!!!
EDIT 2
Insert and updates still working too...

Found the reason my script failed.

The lock is released when the connection is finished.

Appear be quite obvious, but I don't remember read at any place about this behave and since few commands are able to execute from prompt with mysqladmin (not this case) induce to wrong interpretation of FLUSH behave.

My first script, where fails to lock look like this :

    # Trava banco de dados e faz flush dos dados em disco
    echo
    echo "Travando banco e sincronizando dados..."
    echo "FLUSH TABLES WITH READ LOCK" | mysql -vvv 2>&1 | awk '{print " | "$0}'

    echo "Forcando novo binlog"
    echo "FLUSH LOGS" | mysql -vvv 2>&1 | awk '{print " | "$0}'

    # sincroniza FS
    sync
    sleep 1

    # cria snap , com 10G de dados
    echo
    echo "Criando snapshot..."
    echo "  lvcreate --snapshot $VG/lvmysql -n $vN -L 10G"
    lvcreate --snapshot $VG/lvmysql -n $vN -L 10G

    # libera banco de dados
    echo
    echo "Liberando banco de dados..."
    echo "UNLOCK TABLES " | mysql -vvv  2>&1 | awk '{print " | "$0}'

    echo
    echo "Snapshot criado : "
    lvs -o +lv_time | awk '{print "  "$0}'

The problem is the FLUSH running in standalone session, when this session close it's released.

To solve I run all at unique session.
This is how I rewrite it and work properly

    VG=system
    vN=lv_bkp1
    # Trava banco de dados e faz flush dos dados em disco
    echo
    {
    echo "
    select '-- Travando banco e sincronizando dados...' ;
    FLUSH TABLES WITH READ LOCK  ;

    select '-- Forcando novo binlog' ;
    FLUSH LOGS ;

    select '-- Logando posicao do bin-log, caso necessario...' ;
    SHOW MASTER STATUS ;

    select '-- ! sync' ;
    system sync ;

    select '-- ! sleep 1' ;
    system sleep 1 ;

    select '-- Criando snapshot...' ;
    system lvcreate --verbose --snapshot $VG/lvmysql -n $vN -L 10G ;

    select '-- Liberando banco de dados...' ;
    UNLOCK TABLES ;
    "
    } 2>&1 | mysql --unbuffered -vvv 2>&1 | awk '{print " | "$0}'

    echo
    lvs -o +lv_time | awk '{print "  "$0}'


Those dashes are separators between the column headers and the results, since the columns in your example have no names, that isn't immediately obvious. You can see that better here:
SET NOCOUNT ON;
SELECT ServerName = @@SERVICENAME;
SELECT VersionInfo = @@version;

To suppress the column headers:

Go to the Tools menu then select Options
Navigate to Query Results >> SQL Server >> Results to Text
Uncheck the box for Include column headers in the result set.

Note that this settings page also includes other settings that affect the results both for "Results to text" and "Results to file", including maximum data length per column (else SSMS will silently truncate), and whether results should be fixed width (the default) or delimited.


Log Shipping or Always on as DR for SQL Failover CLuster Which is easier to maintain as Disaster Recovery on a remote site for a 2-node SQL Server Cluster

Log Shipping
Single Instance of Always on Availability Group Instance.

Any suggestion will be highly appreciated.

My experience is that an AlwaysOn high availability group, HAG, is easier to maintain than log shipping. Lets set some variables for our scenario.

1.) Your version and edition of SQL Server is SQL Server 2016 Enterprise Edition. We are not on the latest SQL Server patch level. We need to apply a newly released service pack.

2.) The SQL Server database environment exists at a typical small to medium sized company (The active passive AlwaysOn build that you described in your question leads me to this conclusion). This company has a nightly or weekly maintenance window but like most companies the service level agreement is either four or five nines of up time (The database must be available either 99.99% or 99.999% of the time).

Lets look at how each feature allows you, the DBA, to keep the I.T. manager happy by maintaining those up time numbers.

An AlwaysOn HAG gives you the flexibility of being able to fail over back and forth between your AlwaysOn replicas. My assumption is that the primary and secondary replicas are not in close physical proximity to each other. Perhaps they are in different data centers or different racks within a data center. Regardless, If I need to perform maintenance on the primary I can set the availability mode from asynchronous to synchronous, fail over, perform my maintenance, fail back over, and set the availability mode back to asynchronous (https://docs.microsoft.com/en-us/sql/database-engine/availability-groups/windows/availability-modes-always-on-availability-groups).
  With a properly configured AlwaysOn HAG listener the end user will experience little to no interruption and you'll get to easily maintain that SLA.

Now lets take a look at Log Shipping. Without doing a cut and paste from the Microsoft books online there are five steps that have to be manually performed before you can failover. They can be found here... https://docs.microsoft.com/en-us/sql/database-engine/log-shipping/fail-over-to-a-log-shipping-secondary-sql-server .

Considering that most incidents and or maintenance windows occur after hours would you rather have an AlwaysOn high availability group that you can easily failover back and forth between replicas or try and get the primary and secondary databases into sync by applying transaction log backups?


Obtain a "list" of stored procedures which include INSERTs without naming the columns involved As part of some enhancement, a new column has been added to over 750 tables. Now my problem is within almost 3000+ of stored procedures, some old INSERTs do not name columns.

Is there any way I can get a "list" of all the stored procedures that are inserting without column names?

I have tried:

SELECT OBJECT_NAME(object_id)
FROM sys.sql_modules
WHERE definition LIKE '%insert into%'
AND definition NOT LIKE '%) value%'
AND definition NOT LIKE '%)%' + CHAR(10) + '%value%'
AND definition NOT LIKE '%)%' + CHAR(13) + '%value%'
AND definition LIKE '%value%'

But it's still misses a lot.

P.S. - The above query is returning Insert Into (ColumnNames) SELECT scenario too , which i don't want.

There is a DMV called sys.dm_sql_referenced_entities. It returns a column is_insert_all documented thus

  1 = The object is used in an INSERT statement without a column list (object-level only).

This seems to be what you're looking for.

Here's an example of it in practice. First we'll create a simple one-column table

drop table if exists dbo.T1;
go
create table dbo.T1(c int);

Now two stored procedures, one references columns explicitly the other does not.

create or alter procedure dbo.p_named
as
begin
    insert dbo.T1(c)  -- columns are listed
    select 1;
end
go

create or alter procedure dbo.p_not_named
as
begin
    insert dbo.T1   -- no column list
    select 2;
end
go

The DMV is a table-valued function. As such it can be invoked using the CROSS APPLY syntax. The names of procedures to pass to this function can be obtained from the catalog view sys.procedures. The documentation for this DMV states

  schema_name is required when the referencing class is OBJECT.

We can get the schema name using a built-in metadata function handily called SCHEMA_NAME(). Putting it all together we get

select
    p.name, is_insert_all -- other columns exist
from sys.procedures as p
cross apply sys.dm_sql_referenced_entities(CONCAT(SCHEMA_NAME(p.schema_id), '.', p.name), 'OBJECT');

Which returns this

name          is_insert_all
------------- -------------
p_named       0
p_named       0
p_not_named   1


Storing hex values as strings or integers? I've inherited a database that uses sets of 32-byte hex strings to attempt to uniquely identify objects. Each string is called a fingerprint and each object can have up to 20 fingerprints.

I'm migrating the database across servers and I've noticed that re-populating the Fingerprint table takes hours. It seems that each fingerprint is given an integer id and is stored as a varchar, there's an index on the fingerprint column and hence the slow inserts.

My question is: is it worth my while to refactor this design and remove the Fingerprint table altogether? I would guess that indexing on numbers rather than strings would be faster, is this correct?

If the design looks like this: (hopefully this make sense)

Object(1,*)<----->(*,*)ObjectFingerprint(*,*)<------>(1,*)Fingerprint
 id                      object_id                          id
                         fingerprint_id                     fingerprint

Would it be better to use:

Object(1,*)<----->(*,*)Fingerprint
 id                     object_id
                        fingerprint

I'm using a MySQL server with INNODb tables and I have roughly 1.4 million objects.

I would recommend using the BINARY type; strings waste a lot of space, and integers require conversion.

http://dev.mysql.com/doc/refman/5.0/en/binary-varbinary.html

For the other part: why do you need the associative table? If objects and fingerprints are 1 to 1, you could either store them directly in the object table, or just have single table with the id, fingerprint, and object id FK.


Best approach to have a Live copy of a table in the same database I have a table where 15,000 to 20,000 rows are being inserted every hour.

Table Schema is something like

ColumnName   DataType
ID           BIGINT IDENTITY(1,1)
Column1      INT
Column2      INT
Column3      DATETIME DEFAULT = GETDATE()

No row is ever update nor deleted. As you can imagine table grows huge in very short time.

The table is queried as well time to time. SELECT statements with filtering on DATETIME column and sometimes other filtering on INT column too.

To my knowledge, I have two options

indexing columns (Datetime, some int columns) to get better performance on select statements and get a performance hit on my INSERTS

OR

Keep indexing minimum, Primary key Clustered index and a Datetime index and keeping the inserts very fast and get a performance hit on selects.

Another option that was suggested to me, was to create another table, Populate that table from this current table and index the living hell out of it to help all the possible select queries. Read data from this duplicated table.

Keep the original table as it is with minimum indexing, for quick inserts.

Since I am duplicating the data, I know this option violates the basic rules of normilazation, but this sounds like a good option for keeping inserts and reads as fast as possible.

The problem is how can I maintain this near real time copy of this table inside the same database.

I do not want to use any After Insert triggers as this will end up firing 15,000 to 20,000 times an hour.

What other options I have to keep this near real time copy of table in the same database??? Or maybe another approach altogether, any suggestion or pointers in the right direction are much appreciated.

20,000/60 = 333 records per minute is not that rate where you worry about inserts, even if they are burst. Of course it depends on your hardware, but since your table is rather huge, you need something big. So I would defenitely create 1 or 2 indexes (you can omit clustered) to speed up your queries.

At a higher insert rate you probably go with partitioning and split PAGELATCH contention between partitions. Or you could use a variation of GUID clustered primary key. There is a lot of arguing here and you may want to check Tom Kejser blog post here.

Also there is no shame if you go below 3NF. People create highly denormalized data shops just for reporting purposes. Since you have enterprise edition you could use CDC (Change Data Capture) technique and create SSIS package to update reporting table, at a time you need fresh data.


How do I get postgres to log the stored procedures it is running? I'm not asking how to log what the stored procedure does, I simply want a list of all the stored procedures that are being triggered and run dumped into the statement log.

I've got it logging all the statements with:

log_statement=all

but all that is logging is the statements sent to the server, not the triggered stored procedures that are running because of them.

I'm trying to find out which which functions are being fired because of triggers.  I wouldn't mind know which triggers are firing, but most interested in knowing which functions are getting called because of them.

I've inherited this code base and it has a LOT of stored procedures, I'd just like to know what's getting run, I can turn on logging in those procedures if I need it, but I don't want to go annotating all the stored procs to see all the statements they are executing.

You can see a count of all user-defined functions being called in the system view pg_stat_user_functions.

There is no facility to write all function calls to the log.  That would be quite bulky.

If you're feeling adventurous, you can play with the settings debug_print_parse and debug_print_plan to get more detailed information about what is being called.  Again, this will be bulky.


Slow connection to SQL Server both locally and by client Yesterday I was experiencing extremely slow connectivity to the SQL Server with connections via management studio taking 5+ seconds where normally connecting to the server occurs within seconds. This issue both happened when connecting to the SQL Server on the actual server VM and then also from client pcs. Executing requests on their own seemed to be fine once connected, we just appeared to have issues connecting to the SQL Server it self.

CPU Utilisation had spikes but averaged around 75% with memory also around the 85 % mark suggesting resources were available for clients to connect.

Given that we are not experiencing the issues right now, is there any way I can troubleshoot what was causing it and if not and should we experience the issues again, is there anything I should look for in particular with Extended Events when troubleshooting?

Thanks

There is no way to see performance in the past unless you have a monitoring system in place during the event in question.

Assuming you don't have a monitoring solution in place, you could be prepared in case this happens in future.

For instance, you could look at wait stats over a time period using my wait stats capture script.  The script shows what SQL Server actually waited on over a short period of time.  For instance, if you happened to run that script while the problem in your questions was occurring, it's possible the output might show thread starvation via the THREADPOOL wait.  Check Paul Randal's site for details about that wait.


How to find values missing from a sequence? I want to find rows missing from a sequence of values in my table. For example, in this picture documentno 'YD4BC2006008' is missing.
I want to find the missing lines via c_order_id and documentno.
I tried this but it didn't return the expected result:
select * from
(SELECT distinct c_order_id, documentno,cast(right(documentno,3) as integer) as no
FROM adempiere.c_order
ORDER BY documentno, c_order_id) as f

In this example  many rows missing: from 'DGPOS2003030' to 'DGPOS2003068'.
How do I write a query to retrieve them?

One easy way is to generate a list of all potential documentnos and remove from that all the ones that exist:
select 'YD4BC' || generate_series(2006004, 2006015) as missing_documentno
except
select documentno from documents;


Getting column information from the MySQL command line I am trying to get all the columns from MySQL using bash scripting. I want them to be stored in an array. If I write it this way:

mysql -uroot -pPassword1 "select column_name
    from information_schema.columns
    where table_schema = 'dbName'
    and table_name = 'tableName';"

...I get an error that the identifier name is too long. If I write this way:

mysql -uroot -pPassword1 <<- SMTH
    select column_name
    from information_schema.columns
    where table_schema = 'dbName'
    and table_name = 'tableName';
SMTH

everything works fine. But I can't output it to a variable. Any suggestions?

Add -e option:

  ·   --execute=statement, -e statement

  Execute the statement and quit. The default output format is like that produced with --batch. See Section 4.2.4, “Using Options
  on the Command
             Line”, for some examples. With this option, mysql does not use the history file.

mysql -uroot -pPassword1 -e "select column_name from information_schema.columns where table_schema = 'dbName' and table_name = 'tableName';"

Test:

root@onare:/home/onare# mysql -uroot -ponaare -e "select column_name from information_schema.columns where table_schema = 'test' LIMIT 10;"
+--------------+
| column_name  |
+--------------+
| AnswerID     |
| QuestionID   |
| ReplyContent |
| id           |
| emp_id       |
| call_start   |
| call_end     |
| call_type    |
| idEmployee   |
| eName        |
+--------------+
root@onare:/home/onare#

EDIT 2:

If you want to avoid the query header (column_name), use --skip-column-name:

root@onare:/home/onare# mysql -uroot -ponaare -e "select column_name from information_schema.columns where table_schema = 'test' LIMIT 10;" --skip-column-names
+--------------+
|     AnswerID |
|   QuestionID |
| ReplyContent |
|           id |
|       emp_id |
|   call_start |
|     call_end |
|    call_type |
|   idEmployee |
|        eName |
+--------------+
root@onare:/home/onare#


Can slowms be set on each individual database in MongoDB? I am using db.setProfilingLevel(1, 1000) to set the profile level for each database. I see the level can be set for each database but the slowms is a global state. Is there any way to set this value for each individual database?

If I can't set it for each individual database, why there is a method db.setProfilingLevel(level, slowms) accept the second parameter as slowms. It looks like this parameter is for global value rather than database value. What is confused is this method is specific for setting a single database profile level but for some reasons it also set slowms in global setting.

setProfilingLevel actually affects two things. First parameter (level) tells when system writes to profile -collection (0-never, 1-only when slower than slowms or 2-always). Second parameter (slowms) affects actually always to syslog output of ALL databases. So, with db.setProfilingLevel(0,-1), nothing is ever written to profile-collection, but ALL queries (of all DBs) are written to mongod.log


How to set the deadlock priority for a maintenance task created trough the graphical interface How can I change the deadlock priority for a Sql Server 2012 Enterprise maintenance task that is created and configured using the GUI wizard?

I.e. There is a maintenance plan with an "rebuild index of all tables of XY" or "reorganize index of all tables of XY" subtask, which was created using the maintenance plan wizard of Sql Server Management Studio.

I know I can change the deadlock priority for a transaction by executing SET DEADLOCK_PRIORITY LOW, but where would I insert such a command?

If at all possible I would like to avoid abandoning the GUI subtasks in favor of some sort of stored procedure or script. Noone here, including me, has the expertise to maintain such a script.

From my research so far, it seems like setting the priority for a gui task is simply not possible. If this is true, I still would appreciate a definitive answer confirming this, so I can stop searching.

Try adding an Execute T-SQL Statement Task at the beginning of your maintenance plan

With the following statement in it :

SET DEADLOCK PRIORITY NORMAL