Oracle - Any way to view uncommited changes to a particular table? I'm debugging through a batch process currently that does a lot of DML statements, but doesn't do a commit right away.  It would be nice to be able to view the "pending" changes from another session while the transaction is not committed.  Is this possible?

Example:

Insert into table myTable (col1, col2) values ("col1", "col2");

--Somehow view the pending transaction maybe by system view?....

...other DML statements....

commit;

There are a few different approaches depending on the details of your batch process and why you're trying to view the uncommitted changes.

1) Oracle Workspace Manager is a tool that was originally designed to allow people developing Spatial applications to have the equivalent of extremely long-running transactions (i.e. transactions that may require multiple days or weeks of humans figuring out where to run a pipeline in one transaction).  Your batch process could create a new workspace (which is logically like creating a new transaction), make whatever changes it would like in that workspace while committing whenever it wanted.  In a separate session, you wouldn't see any of the committed changes until you entered the batch process's workspace.  When the batch process finished, it could merge it's workspace back into the live workspace which is the equivalent of committing a transaction.

2) The DBMS_XA package can be used to allow you to "hand off" a transaction from one session to another and to allow one session to connect to a transaction started by another session.  This is a pretty obscure package to be using, but there was a nice example of using it in the PL/SQL Challenge (you may need a free account to access it) recently.

3) If you're just trying to see the status of the batch process rather than seeing the actual data, the batch process can write logging information using autonomous transactions that you could then query from another session. Or you could use the DBMS_APPLICATION_INFO package to have your application update various attributes in V$SESSION and/or V$SESSION_LONGOPS so that you could monitor the status of the load from another session.


Combing two queries in one I have two tables, e.g. "Locations" and "Connections"

"Locations" has values

Id | Dimension
---------------
 1 |    4
 2 |    8
 3 |    2

"Connections" maintains attributes

Origin | Destination | Value | Distance_KM
-------------------------------------------
   1   |      2      |  500  |     30
   1   |      3      |  100  |     20
   2   |      1      |  100  |     10
   2   |      3      |  300  |     10
   3   |      1      |  100  |     40

I want to create an output with the following Attribute Table. Where "In" correspond to "Destination" from "Connections" and "Out" to "Origin" accordingly.

Id | Dimension | In_Value | In_Count | In_Dist | Out_Value | Out_Count | Out_Dist
----------------------------------------------------------------------------------
 1 |     4     |    200   |     2    |    50   |    600    |    2      |     50
 2 |     8     |    500   |     1    |    30   |    400    |    2      |     20
 3 |     2     |    400   |     2    |    30   |    100    |    1      |     40

I can achieve the result that I strive for separately with two queries.

Query 1

SELECT C.Destination, SUM(C.Value) AS In_Value, COUNT(C.Destination) AS In_Count, SUM(C.Distance_KM) AS In_Dist
FROM Connections AS C
GROUP BY C.Destination

Query 2

SELECT C.Origin, SUM(C.Value) AS Out_Value, COUNT(C.Origin) AS Out_Count, SUM(C.Origine_KM) AS Out_Dist
FROM Connections AS C
GROUP BY C.Origin

Nevertheless, there should be only one query that solves my issue, is not it? I tried this but no success.

SELECT L.Id AS Id, L.Dimension AS Dimension, C.In_Value, C.In_Count, C.In_Dist, C.Out_Value, C.Out_Count, C.Out_Dist
FROM   Locations AS L
LEFT   JOIN (
             SELECT C.Destination,
                    SUM(C.Value) AS In_Value,
                    COUNT(C.Destination) AS In_Count,
                    SUM(C.Distance_KM) AS In_Dist
             FROM Connections AS C
             GROUP BY C.Destination
        ) ON L.Id = C.Destination
LEFT JOIN (
           SELECT C.Origin,
                  SUM(C.Value) AS Out_Value,
                  COUNT(C.Origin) AS Out_Count,
                  SUM(C.Origine_KM) AS Out_Dist
           FROM Connections AS C
           GROUP BY C.Origin
        ) ON L.Id = C.Origin

Basically, I do not know if I eligible to add a second LEFT JOIN ON to the query with already existing LEFT JOIN ON, am I?

References:

Two SQL LEFT JOINS produce incorrect result
Multiple left joins on multiple tables in one query

SELECT L.Id AS Id, L.Dimension AS Dimension,
       sq1.In_Value, sq1.In_Count, sq1.In_Dist,
       sq2.Out_Value, sq2.Out_Count, sq2.Out_Dist
FROM Locations AS L
LEFT JOIN (
           SELECT C.Destination,
                  SUM(C.Value) AS In_Value,
                  COUNT(C.Destination) AS In_Count,
                  SUM(C.Distance_KM) AS In_Dist
           FROM Connections AS C
           GROUP BY C.Destination
         ) AS sq1 ON L.Id = sq1.Destination
LEFT JOIN (
           SELECT C.Origin,
                  SUM(C.Value) AS Out_Value,
                  COUNT(C.Origin) AS Out_Count,
                  SUM(C.Distance_KM) AS Out_Dist
           FROM Connections AS C
           GROUP BY C.Origin
         ) AS sq2 ON L.Id = sq2.Origin

fiddle


MySQL trigger after insert with insert and delete instructions does not work properly I have the following trigger created in phpMyAdmin:

Table: tb_agenda
 Time: AFTER
 Event: INSERT

BEGIN
    INSERT INTO tb_realizacao (dt_agenda,
                               titulo,
                               titulo_en,
                               descricao,
                               descricao_en,
                               dt_cadastro)
        SELECT  dt_agenda,
                titulo,
                titulo_en,
                descricao,
                descricao_en,
                dt_cadastro
        FROM tb_agenda
        WHERE dt_agenda < NOW();

    DELETE FROM tb_agenda
     WHERE dt_agenda < NOW();
END

The trigger works 50%. The INSERT instruction works perfectly but the DELETE one doesn't execute. I tried to execute it in the SQL panel and it works fine.

Your trigger tries to do something that cannot be done in MySQL. You cannot use an SQL statement (DELETE, in your case) on the table that is associated to the trigger. You will get an error like this:

ERROR 1442 (HY000): Can't update table 't' in stored function/trigger because it is already used by statement which invoked this stored function/trigger.

You are probably using MyISAM for your table. The DELETE does not cause a rollback, because the engine is not transactional. If you switch to InnoDB (which is usually the best choice) you will notice that the trigger and the original INSERT will completely fail.


Performance for fast filling table I am currently creating a web application using the twitter streaming API and would need some advice concerning my database scheme.

I basically have a list of hashtags that I track in real-time. All the tweets having that hashtag are stored in a table.

Thing is that the table is getting quickly huge, and it is far from efficient to crawl it afterwards (to get the list of tweets for a given user for example).

Just for information, I got something like 700.000 tweets crawled in less that 24 hours.
This was for 5 hashtags, but I would like to scale up to a hundred at least.

I store them all and then populate another table of unique users out of it, which is transformed into a leaderboard.

So I was considering adding indexes, using the hashtag as a key.
But I was also considering separating the tweets in several tables, one per hashtag in order to have a less juge amount of data to crawl.

The table would be created at runtime when a new hashtag is added.

Am I going the right way with this, or is that a bad practice?

Or is Mysql just a bas idea and I should go for another technology?

This is in general not the best practice.  It depends upon the DB you are using, but generally a relational DB should be more efficient at handling the varying sets of rows if they are all in one table, than if you break the sets of rows into separate tables.  If you let the DB manage the data, it will do a better job of optimizing IO than if you try to surround this with a bunch of tables in the file system.

Indeed, you should make the hashtag column indexed if you are going to commonly retrieve all the rows for a given hashtag.

There is a caveat: if you know that you will only be processing one hashtag value at a time, and you will never want to have data from two hashtag values in process at the same time, there is a possibility that creating tables like you suggest might perform well.  However, I doubt you are in this situation, and it is better to let the DB do what it is supposed to do.


Creating index using dynamic SQL I have following code for creating gist index

CREATE INDEX web_stats_project_107_properties_crosspromo_gist_idx
    ON web_stats_project_107 USING gist (properties)
    WHERE properties @> '"block_level_0"=>"cerber-head"'::hstore
        AND properties @> '"block_level_1"=>"head"'::hstore
        AND properties @> '"block_level_2"=>"cross-news"'::hstore
        AND properties ? 'block_level_5'::text
        AND (NOT (properties ? 'block_level_6'::text));

It works smoothly when I use it directly.

But I need to use it inside the trigger with some variables. And here the problem comes.

I tried two variants of code:

createIndex := 'CREATE INDEX idx_'||Fulltable_name||'_properties_crosspromo_gist
ON public.'||Fulltable_name||' USING gist (properties)
WHERE properties @> '"block_level_0"=>"cerber-head"'::hstore
AND ...;

and

createIndex := 'CREATE INDEX idx_'||Fulltable_name||'_properties_crosspromo_gist
ON public.'||Fulltable_name||' USING gist (properties)
WHERE (((((properties @> '"block_level_0"=>"cerber-head"'::hstore)
AND ...;

And I even can't apply it for DB because of syntax error

  ERROR:  syntax error at or near "=>" LINE 32: ...perties) WHERE
  (((((properties @> '"block_level_0"=>"cerber-...

or

  ERROR:  syntax error at or near "CREATE" LINE 32:
  createIndex := 'createIndex := 'CREATE INDEX idx...

Seems I have to escape some symbols with some characters.

Any ideas how to do that?

createIndex := 'CREATE INDEX idx_'
    || Fulltable_name
    || '_properties_crosspr_gist ON public.'
    || Fulltable_name
    || '
    USING gist (properties)
    WHERE properties @> $$"block_level_0"=>"cerber-head"$$::hstore
      AND properties @> $$"block_level_1"=>"head"$$::hstore
      AND properties @> $$"block_level_2"=>"cross-news"$$::hstore
      AND properties ? $$block_level_5$$::text
      AND NOT properties ? $$block_level_6$$::text';


decrypt data in column before load into destination I have a table named city which contains two columns

city_id (int)
city_name (varbinary) -- encrypted column

I want to extract data from this table, transform (decrypt the city_name) and load (decrypted city_name) into new table (destination).

I used the following query in oledb source in ssis. but it returns city_name column as NULL.

select city_id, CONVERT(nvarchar(50), decryptbykeyautocert(cert_id('Usercert'),NULL,city_name)) as city_name
from city

The above query works fine in sql server management studio query editor.

plz guide how to decrypt data before insert into destination.

regards,

When calls to decrypt methods return NULL, either the column is NULL or the account that is using the methods doesn't have access to the key.

From BOL

  Permissions

  Requires VIEW DEFINITION permission on the symmetric key and CONTROL permission on the certificate.

Look at at the account that is executing the SSIS package and verify it has the required permissions.

You can also launch SSMS as the account that runs the SSIS packages and try stepping through the underlying code of DecryptByKeyAutoCert. Something approximately like

OPEN SYMMETRIC KEY MyKeyNameHere
   DECRYPTION BY CERTIFICATE Usercert;

SELECT
    city_id
,   CONVERT(nvarchar(50), DecryptByKey(city_name)) AS city_name
FROM city;

CLOSE SYMMETRIC KEY MyKeyNameHere;


How do I find out whenever a value changes in a certain column? Need help if anyone can offer!
I currently have my select statement returning the TOP 1000 * results from a table. They are CC numbers that are currently active. I'd like to see how I can go about writing a query that could show me what has CC numbers have changed.
If something has changed, it will send a report to the listed people. If nothing, then it does nothing. It only actions once something has happened. We have an inhouse system that will do the automated part... I just need a select statement that will return what has changed. If I can get some help on this, it would be amazing!
Thank you!

If you're not already logging changes yourself in your user defined tables, then you'll need to implement a feature that does so for your. Here is a list of features you can use to accomplish this:

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

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

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

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

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


Are individual queries faster than joins? Conceptual question: Are individual queries faster than joins, or: Should I try to squeeze every info I want on the client side into one SELECT statement or just use as many as seems convenient?

TL;DR: If my joined query takes longer than running individual queries, is this my fault or is this to be expected?

First of, I am not very database savvy, so it may be just me, but I have noticed that when I have to get information from multiple tables, it is "often" faster to get this information via multiple queries on individual tables (maybe containing a simple inner join) and patch the data together on the client side that to try to write a (complex) joined query where I can get all the data in one query.

I have tried to put one extremely simple example together:

SQL Fiddle

Schema Setup:

CREATE TABLE MASTER
( ID INT NOT NULL
, NAME VARCHAR2(42 CHAR) NOT NULL
, CONSTRAINT PK_MASTER PRIMARY KEY (ID)
);

CREATE TABLE DATA
( ID INT NOT NULL
, MASTER_ID INT NOT NULL
, VALUE NUMBER
, CONSTRAINT PK_DATA PRIMARY KEY (ID)
, CONSTRAINT FK_DATA_MASTER FOREIGN KEY (MASTER_ID) REFERENCES MASTER (ID)
);

INSERT INTO MASTER values (1, 'One');
INSERT INTO MASTER values (2, 'Two');
INSERT INTO MASTER values (3, 'Three');

CREATE SEQUENCE SEQ_DATA_ID;

INSERT INTO DATA values (SEQ_DATA_ID.NEXTVAL, 1, 1.3);
INSERT INTO DATA values (SEQ_DATA_ID.NEXTVAL, 1, 1.5);
INSERT INTO DATA values (SEQ_DATA_ID.NEXTVAL, 1, 1.7);
INSERT INTO DATA values (SEQ_DATA_ID.NEXTVAL, 2, 2.3);
INSERT INTO DATA values (SEQ_DATA_ID.NEXTVAL, 3, 3.14);
INSERT INTO DATA values (SEQ_DATA_ID.NEXTVAL, 3, 3.7);

Query A:

select NAME from MASTER
where ID = 1

Results:

| NAME |
--------
|  One |

Query B:

select ID, VALUE from DATA
where MASTER_ID = 1

Results:

| ID | VALUE |
--------------
|  1 |   1.3 |
|  2 |   1.5 |
|  3 |   1.7 |

Query C:

select M.NAME, D.ID, D.VALUE
from MASTER M INNER JOIN DATA D ON M.ID=D.MASTER_ID
where M.ID = 1

Results:

| NAME | ID | VALUE |
---------------------
|  One |  1 |   1.3 |
|  One |  2 |   1.5 |
|  One |  3 |   1.7 |

Of course, I didn't measure any performance with these, but one may observe:

Query A+B returns the same amount of usable information as Query C.
A+B has to return 1+2x3==7 "Data Cells" to the client
C has to return 3x3==9 "Data Cells" to the client, because with the join I naturally include some redundancy in the result set.

Generalizing from this (as far fetched as it is):

A joined query always has to return more data than the individual queries that receive the same amount of information. Since the database has to cobble together the data, for large datasets one can assume that the database has to do more work on a single joined query than on the individual ones, since (at least) it has to return more data to the client.

Would it follow from this, that when I observe that splitting a client side query into multiple queries yield better performance, this is just the way to go, or would it rather mean that I messed up the joined query?

Are individual queries faster than joins, or: Should I try to squeeze every info I want on the client side into one SELECT statement or just use as many as seems convenient?

In any performance scenario, you have to test and measure the solutions to see which is faster.

That said, it's almost always the case that a joined result set from a properly tuned database will be faster and scale better than returning the source rows to the client and then joining them there. In particular, if the input sets are large and the result set is small -- think about the following query in the context of both strategies: join together two tables that are 5 GB each, with a result set of 100 rows. That's an extreme, but you see my point.

  I have noticed that when I have to get information from multiple tables, it is "often" faster to get this information via multiple queries on individual tables (maybe containing a simple inner join) and patch the data together on the client side that to try to write a (complex) joined query where I can get all the data in one query.

It's highly likely that the database schema or indexes could be improved to better serve the queries you're throwing at it.

  A joined query always has to return more data than the individual queries that receive the same amount of information.

Usually this is not the case. Most of the time even if the input sets are large, the result set will be much smaller than the sum of the inputs.

Depending on the application, very large query result sets being returned to the client are an immediate red flag: what is the client doing with such a large set of data that can't be done closer to the database? Displaying 1,000,000 rows to a user is highly suspect to say the least. Network bandwidth is also a finite resource.

  Since the database has to cobble together the data, for large datasets one can assume that the database has to do more work on a single joined query than on the individual ones, since (at least) it has to return more data to the client.

Not necessarily. If the data is indexed correctly, the join operation is more likely to be done more efficiently at the database without needing to scan a large quantity of data. Moreover, relational database engines are specially optimized at a low level for joining; client stacks are not.

  Would it follow from this, that when I observe that splitting a client side query into multiple queries yield better performance, this is just the way to go, or would it rather mean that I messed up the joined query?

Since you said you're inexperienced when it comes to databases, I would suggest learning more about database design and performance tuning. I'm pretty sure that's where the problem lies here. Inefficiently-written SQL queries are possible, too, but with a simple schema that's less likely to be a problem.

Now, that's not to say there aren't other ways to improve performance. There are scenarios where you might choose to scan a medium-to-large set of data and return it to the client if the intention is to use some sort of caching mechanism. Caching can be great, but it introduces complexity in your design. Caching may not even be appropriate for your application.

One thing that hasn't been mentioned anywhere is maintaining consistency in the data that's returned from the database. If separate queries are used, it's more likely (due to many factors) to have inconsistent data returned, unless a form of snapshot isolation is used for every set of queries.


DB architecture: What if a table is part of another table My SQL Server database has the initial table named Lead. This table has all kinds of info about a lead person. It has 3 foreign key columns: IndustryId, BusinessTypeId and ReferenceId. I have a problem in the architecture concerning the tables Lead and Reference.

This is the script of the table Lead:

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[Lead](
    [LeadId] [int] IDENTITY(1,1) NOT NULL,
    [LeadName] [nvarchar](100) NOT NULL,
    [CompanyName] [nvarchar](100) NOT NULL,
    [Phone] [nvarchar](50) NOT NULL,
    [Address] [nvarchar](max) NOT NULL,
    [DOB] [date] NULL,
    [IndustryID] [int] NOT NULL,
    [BusinessTypeID] [int] NOT NULL,
    [IsDeleted] [bit] NOT NULL CONSTRAINT [DF_Lead_IsDeleted]  DEFAULT ((0)),
    [ReferenceId] [int] NOT NULL,
    [IsLead] [bit] NOT NULL,
    [Email] [nvarchar](100) NOT NULL,
    [Website] [nvarchar](100) NOT NULL,
    [RepresentativeName] [nvarchar](100) NOT NULL CONSTRAINT [DF_Lead_RepresentativeName]  DEFAULT (''),
    [IsClosed] [bit] NOT NULL CONSTRAINT [DF_Lead_IsClosed]  DEFAULT ((0)),
    [feedback] [bit] NOT NULL CONSTRAINT [DF_Lead_feedback]  DEFAULT ((0)),
    [feedbackMemo] [nvarchar](100) NULL,
 CONSTRAINT [PK_Lead] PRIMARY KEY CLUSTERED
(
    [LeadId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

GO

ALTER TABLE [dbo].[Lead]  WITH CHECK ADD  CONSTRAINT [FK_Lead_LeadIndustry] FOREIGN KEY([IndustryID])
REFERENCES [dbo].[Industry] ([IndustryId])
GO

ALTER TABLE [dbo].[Lead] CHECK CONSTRAINT [FK_Lead_LeadIndustry]
GO

ALTER TABLE [dbo].[Lead]  WITH CHECK ADD  CONSTRAINT [FK_Lead_LeadReference] FOREIGN KEY([ReferenceId])
REFERENCES [dbo].[Reference] ([ReferenceId])
GO

ALTER TABLE [dbo].[Lead] CHECK CONSTRAINT [FK_Lead_LeadReference]
GO

ALTER TABLE [dbo].[Lead]  WITH CHECK ADD  CONSTRAINT [FK_Lead_LeadType] FOREIGN KEY([BusinessTypeID])
REFERENCES [dbo].[BusinessType] ([BusinessTypeId])
GO

ALTER TABLE [dbo].[Lead] CHECK CONSTRAINT [FK_Lead_LeadType]
GO

And this is the script for the table in question Reference:

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[Reference](
    [ReferenceId] [int] IDENTITY(1,1) NOT NULL,
    [ReferenceName] [nvarchar](100) NOT NULL,
    [Address] [nvarchar](max) NOT NULL,
    [DOB] [date] NULL,
    [Memo] [nvarchar](max) NOT NULL,
    [IsDeleted] [bit] NOT NULL,
    [Phone] [nvarchar](100) NOT NULL,
    [DateTimeAdded] [datetime] NOT NULL,
    [AddedByUserId] [int] NOT NULL,
    [DateTimeUpdated] [datetime] NULL,
    [UpdatedByUserId] [int] NOT NULL,
 CONSTRAINT [PK_Reference] PRIMARY KEY CLUSTERED
(
    [ReferenceId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

GO

The problem is that each Lead has a Reference, but the Reference may be a Lead. In the UI while adding a new Lead, user is asked to set its Reference, but he may choose an existing Lead to be the one who referenced the new Lead, or he may choose a normal Reference.

So now I'm not sure, what is the best way to set those tables? Should I merge them in a single table Lead and when it's a simple Reference I keep the unneeded columns as Null, or keep both tables and add a new column to Reference table "LeadId" without making it a foreign key? Or maybe another method.
Please advise.

Two ways to do it:

Option 1, as you suggest, is to make a new LeadId FK column in the [Reference] table, and make it nullable. This will work, it'll be easy enough to query, but you'll have a column with lots of nulls.
Option 2 is the 3NF (third normal form) way, with a third table that links [Reference] to [Lead]. That would contain a ReferenceId FK and a LeadId FK. You would need to join to this table in queries, but you wouldn't be stuck with a lot of nulls.


For AlwaysOn AGs, can a secondary replica cause contention on the primary when the resources of the secondary are maxed out? Is it possible and in what ways can the secondary replica cause contention against the primary replica when the secondary replica's resources are maxed out (e.g. does the primary "lock" up while waiting on the secondary to catch up on synchronizing)?

Does it make a difference if the Availability Mode of the secondary replica is set to Asynchronous commit vs Synchronous commit?

In very simple terms:

In asynchronous mode, if the replica can't keep up with its primary then the log send queue will just increase indefinitely until the load is reduced.

In perfmon:
SQLServer:Database Replica Log Send Queue

Bascially the primary will keep humming along but the replicas will just end up further and further behind.

In synchronous mode, if the replica can't catch up, then you will see an increase in transaction delay on the primary. In other words the time the primary spends waiting on replicas to commit / harden a transaction.

In perfmon:
SQL Server Database Replica :Transaction Delay

Unless ofcourse the replica is offline, then there is no delay.

For more detailed reading on what causes latency in synchronous mode: https://blogs.msdn.microsoft.com/psssql/2018/04/05/troubleshooting-data-movement-latency-between-synchronous-commit-always-on-availability-groups/

@scismon makes a good point. The logs will fill up in async mode if the replica(s) can't keep up.

select log_reuse_wait_desc, * from sys.databases

log_reuse_wait_desc shows up with AVAILABILITY_REPLICA


What exactly happens during Database Mirroring? What about during a fail over? So I am trying to configure a two server setup, both containing SQL with independent storage. We're using windows server 2012, and I have a question about mirroring. If the server's data bases are in sync then the primary server fails and the mirrored server takes over. How do I re-synchronize the servers after I put the primary(now the one that will act as the mirror) back up? Is that handled automatically?

Edit: I guess I mean after a fail over, how does the old primary get back in sync with the mirror that has been receiving transactions. I think the endpoints are crucial as Kin said.

When you do a failover, a role reversal happens i.e. your primary becomes secondary and your secondary becomes primary.

ALTER DATABASE [yourDatabase]
SET PARTNER FAILOVER
GO

Doing so, only the role reversal happens (this is what happens when you initiate a failover using above command) and mirroring direction is reversed - principal becomes mirror and vice-versa. Note that MIRRORING is not BROKEN. Refer to my answer : If I fail over one database, do the others that share the same mirror endpoint fail over as well? for more details.

You can have your application use Failover Partner=your_mirror_serve_name, so that your application is redirected to replica database when a failover happens.

Refer to : connectionstring in Database Mirror setup


how to rebuild / reinstall ssrs (reportserver, reportservertempdb) databases? Our server crashed. We got it back up and running however, the mentioned databases have been corrupted.

Is there a programmatic / automatic way of rebuilding or reinstalling the SSRS databases?

If not:

Since we can still select * from the databases, how do I grab the permissions and roles on different reports?
How do we retrieve subscription information?

I’m not 100% sure but I think there is no supported way to rebuild these.

If you have backups of these databases then just restore them. If not you can try creating another empty database and then try to synchronize structure and data and see if that can help.

I’d suggest you use some third party tools for this.


Select query with custom column and value of other column I want to have a query that have a custom column and have some value of other column

here is example of a table

table 1 : id, name, emp_id

I want to have input like this

id | name | emp_id | proccess    |
1  | John | 002    | Work of John|

The process is just costum column I want to do it in Select query..

This is what I tried:

Select id, name, emp_id, "Work of "+name as process, from table1

But its not working. I also want to add 2 or more column value in custom column.

Thanks

The answer Is Concat

You can use concat to combine an array and value of column

example query:

Select id, name, emp_id, concat("Work of ", name) as process, from table1


how to insert data from one table to another SELECT a.nid, a.stock, b.qty, sum( b.qty )
FROM uc_product_stock a
INNER JOIN uc_order_products b ON a.nid = b.nid
GROUP BY a.nid

the above query give me the following result:

nid stock   qty sum(b.qty)
10  746     1   4
11  256     4   11

now i want to insert this in my custom table. For eg, the structure of custom table is:

id | orderIn | orderOut
10  |   746   |   4
11  |   256   |   11

Iam using this query, but it gives error:

insert into custom (id, OrderIn, OrderOut)
select
a.nid,
a.stock,
b.qty,
sum(b.qty)
from
table1 a inner join table2 b on a.id=b.id
group by a.id

You'd use an INSERT..SELECT query like this:

insert into custom (sku, OrderIn, OrderOut)
select
a.id,
a.stock,
sum(b.prod_order)
from
table1 a inner join table2 b on a.id=b.id
group by a.id

More details at http://dev.mysql.com/doc/refman/5.1/en/insert-select.html

The basic idea is that you build up your select query to define the set of data you want, and then use that as an input to an Insert statement to write it into the table you want to put it in.

In your question you've not defined how the created column is generated, so that isn't covered in this query.


Mysql 5.1.X How to upload data from dump file without the partitioning commands? I need to upload data from a dump file (large one) that includes partitioning info into a database that doesn't support partitioning.

How can I tell the mysql command line to ignore the partitioning information in the dump file?

Notes:
The dump file isn't created by me. Its an auto generated backup so creating it without partitions is not an option.

The file is too large to edit manually. But adding a line to it is an option.

Thanks !

If your target database is an older MySQL version such as MySQL 5.0, then partitioning commands will already be ignored since they are wrapped in comments like this:

/*!50500 PARTITION BY RANGE  COLUMNS(column)
(PARTITION partition_name VALUES LESS THAN ('value') ENGINE = InnoDB,
 PARTITION partition_name VALUES LESS THAN ('value') ENGINE = InnoDB) */;

If you are loading into MySQL 5.1 or higher, then one trick that came to my mind is to change the version number in the comment section. You do not have to manually edit the dump file, you can process the file through sed. For example:

sed 's\!50500 PARTITION\!60000 PARTITION\g' dumpfile.sql | mysql


What is the best way to divide data for random groups? I would like to have persisted column which will randomly group data for 32 groups according to one of varchar key column in table. My idea was:
SELECT ABS(CAST(HASHBYTES('MD5',[keyColumnFromTable]) AS bigint) % 31)
Questions:

There is any better way to do this except CHECKSUM (different values on differents COLLATE) and own functions ?
There will be any difference If i will use SELECT CAST(HASHBYTES('MD5',[keyColumnFromTable]) AS tinyint) % 31 ? I was reading that CAST to tinyint is taking into consideration last byte of data. Will be any affectt on randomness ?

One alternative would be to use a sequence to perform round-robin allocation. Define the minimum and maximum values of the sequence to match the requisite number of buckets. Something like
CREATE SEQUENCE group_sequence
    AS tinyint
    START WITH 0
    INCREMENT BY 1
    MAXVALUE 31
    CYCLE;

The persisted column holding the group number can have a default value of ..next value for group_sequence.
The randomness of this will depend on the arrival order of new rows. If, say, you have 32 input streams and they send rows one after another each stream's rows will end up in one group, obviously. If data arrive in a batch, that batch will be evenly spread over all groups, give-or-take a few rows. Whether this is significant to the quality of your calculations I cannot say.
On the matter of casting to tinyint I do not have a mathematical answer, just an observation. Since an MD5 hash returns 16 bytes casting to bigint already produces truncation. Why would casting to tinyint be more problematic? You can perform a modulo calculation directly on the result of HASHBYTES, which will perform an implicit cast to 4-byte int.
Note also that casting the hash could produce a negative integer. Taking the absolute value after calculating the modulo will give about half the number of rows in the zero group compared to other groups. Since tinyint cannot be negative this at least would be avoided.


Optimizing intersect query in MySQL I'm facing a performances problem in our MariaDB server.

We have a table linking users to pages, a user belongs to 1+ pages.

I have a page that have users and I want to get the 10 best pages based on users they share with the current page (page_id).

Here is the schema :

CREATE TABLE IF NOT EXISTS `pages_users3` (
  `user_id` bigint(20) unsigned NOT NULL,
  `page_id` bigint(20) unsigned NOT NULL,
  `comments` int(11) NOT NULL DEFAULT '0',
  `likes` int(11) NOT NULL DEFAULT '0',
  `shares` int(11) NOT NULL DEFAULT '0',
  `posts` int(11) NOT NULL DEFAULT '0',
  `interactions` int(11) NOT NULL DEFAULT '0',
  `created_at` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
  `updated_at` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
  PRIMARY KEY (`page_id`,`user_id`) `clustering`=yes,
  KEY `user_page` (`user_id`)
) ENGINE=TokuDB  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci `compression`='tokudb_zlib' AUTO_INCREMENT=30127543590 ;

I have a query that can retrieve similar pages of a page based on users intersections :

SELECT `pages_users`.`page_id` , COUNT(*) AS count
FROM `pages_users`
LEFT JOIN `pages_users` AS `pu2` ON `pu2`.`user_id` = `pages_users`.`user_id`
WHERE `pages_users`.`page_id` != *PAGE_ID*
AND `pu2`.`page_id` = *PAGE_ID* /* To avoid getting the current page in results */
GROUP BY `pages_users`.`page_id`
ORDER BY `count` DESC
LIMIT 10

Unfortunaly this query runs in about 1m30s, so I would like to optimize it...

Here is the result of an explain :

+------+-------------+-------------+--------+-----------------------------------------------------------------------------------+------------------------------------------------+---------+----------------------------------------+-----------+-----------------------------------------------------------+
| id   | select_type | table       | type   | possible_keys                                                                     | key                                            | key_len | ref                                    | rows      | Extra                                                     |
+------+-------------+-------------+--------+-----------------------------------------------------------------------------------+------------------------------------------------+---------+----------------------------------------+-----------+-----------------------------------------------------------+
|    1 | SIMPLE      | pages_users | index  | pages_users_user_id_page_id_unique,pages_users_user_id_page_id_interactions_index | pages_users_user_id_page_id_interactions_index | 20      | NULL                                   | 165354919 | Using where; Using index; Using temporary; Using filesort |
|    1 | SIMPLE      | pu2         | eq_ref | pages_users_user_id_page_id_unique,pages_users_user_id_page_id_interactions_index | pages_users_user_id_page_id_unique             | 16      | soprism_prod.pages_users.user_id,const |         1 | Using index                                               |
+------+-------------+-------------+--------+-----------------------------------------------------------------------------------+------------------------------------------------+---------+----------------------------------------+-----------+-----------------------------------------------------------+
2 rows in set (0.00 sec)

I'm requesting your help because I spent many hours trying to optimize that query and I feel confused :(
I'm open to anything, from refactoring to changing server parameters...
Thank you !

Some additional insights :

SELECT COUNT(user_id) FROM `pages_users` WHERE `page_id` = *PAGE_ID*;
+----------------+
| COUNT(user_id) |
+----------------+
|         146132 |
+----------------+
1 row in set (0.08 sec)

SELECT COUNT(user_id) FROM `pages_users`;
+----------------+
| COUNT(user_id) |
+----------------+
|      165354918 |
+----------------+
1 row in set (1 min 0.98 sec)

Hardware : Quad Core Xeon, 32Gb RAM, Raid 1 2x2Tb

Please try:
(edited 2014-12-18: replaced Left with INNER join):

SELECT
    `pages_users`.`page_id` ,
    COUNT(`pu2`.`page_id`) AS `count`
FROM
    `pages_users`
    INNER JOIN `pages_users` AS `pu2`
        ON `pu2`.`user_id` = `pages_users`.`user_id`
           AND `pu2`.`page_id` = *PAGE_ID*
WHERE
    `pages_users`.`page_id` != *PAGE_ID*
GROUP BY
    `pages_users`.`page_id`
ORDER BY
    `count` DESC
LIMIT 10

or as an alternative

SELECT
    `pages_users`.`page_id` ,
    COUNT(`pu2`.`pu2_page_id`) AS `count`
FROM
    `pages_users`
    INNER JOIN
    (
        SELECT
            `pu2_intern`.`page_id` AS `pu2_page_id`
        FROM
            `pages_users` AS `pu2_intern`
        WHERE
            `pu2_intern`.`page_id` = *PAGE_ID*
    )  AS `pu2`
        ON `pu2`.`user_id` = `pages_users`.`user_id`
WHERE
    `pages_users`.`page_id` != *PAGE_ID*
GROUP BY
    `pages_users`.`page_id`
ORDER BY
    `count` DESC
LIMIT 10

You could also add another index just on the field page_id:

KEY `page` (`page_id`)

I hope it could help you.


SQL Server 2012: Login failed for user. Reason: Failed to open the database configured in the login object Every so often, my SQL Server 2012 throws the following errors:
Error (likely related to the one below):

Source: spid99
The client was unable to reuse a session with SPID 99, which had been reset for connection pooling. The failure ID is 46. This error may have been caused by an earlier operation failing. Check the error logs for failed operations immediately before this error message.
Error: 18056, Severity: 20, State: 46.

Immediately before the error above (same timestamp):

Source: Logon
Login failed for user 'myUser'. Reason: Failed to open the database 'myDatabase' configured in the login object while revalidating the login on the connection.
Error: 18456, Severity: 14, State: 46.

My ASP.NET 4.5 website (consuming the DB) throws this error at the same time:

System.InvalidOperationException: Internal connection fatal error.

What may be causing these issues and how can I fix them?

We had the same issue. Several app servers were logging the same error. It turned out to be a bad SFP network port in the Hyper-V hosts frame. As soon as it was disabled all of the errors stopped immediately.


Optimising expensive join/subquery by filtering with conditionals This is a question about short-circuiting expensive JOINs or sub-queries in Postgresql (9.5 or 9.6). I'm also be interested in hearing how people in general solve the check-then-execute problem.

I'm writing a lot of queries that should only conditionally return a result, such as if the (web) user owns the record or if the record was modified. I'm trying to prevent building expensive views inside Postgresql and multiple back-and-forth queries to check for conditions in the application itself, so I attempt to write queries that first select the correct record and show which conditions failed, and only execute the view if the conditions pass.

For instance, this checks if the (application) user owns a record before returning it:

SELECT is_owner, is_newer, json
FROM (
     SELECT id, owner = '053bffbc-c41e-dad4-853b-ea91fc42ea18' "is_owner"
          , modified >= created "is_newer"
     FROM datasets
     WHERE id = '056e4eed-ee63-2add-e981-0c86b8b6a66f'
) cond
LEFT JOIN LATERAL (
     SELECT id
     FROM datasets
     WHERE is_owner and is_newer
) authed
    ON cond.id = authed.id
LEFT JOIN LATERAL (
     SELECT json
     FROM view_dataset
     WHERE id = authed.id
) dataset
    ON true;

Resulting in (is owner):

is_owner | is_newer | json
t          t          {...}

And a negative result (not owner):

is_owner | is_newer | json
f          t          NULL

So the application knows which error to return but we don't have to build or parse the view if the conditions don't pass.

However, EXPLAIN ANALYSE shows Postgresql still executes the view query in the last LEFT LATERAL JOIN even though the middle JOIN doesn't have any results, and I can't get it to short-circuit to prevent the (expensive) view_dataset SELECT from running. If I set json to null the query skips everything but the first SELECT; but if it is set to a value from the last query, it will always execute all SELECTs, so I guess the query planner thinks it has to get a result for that json field in the top SELECT query and doesn't short-circuit the JOINs.

I wonder if I can force Postgresql to drop the expensive view query.

I have also tried a CTE, which does seem to skip the JOIN query:

WITH cond as (
    SELECT id, owner = '053bffbc-c41e-dad4-853b-ea91fc42ea18' "is_owner", modified >= created "is_newer" FROM datasets WHERE id = '056e4eed-ee63-2add-e981-0c86b8b6a66f'
)
SELECT cond.id, cond.is_owner, cond.is_newer, json FROM
    (SELECT id FROM cond WHERE cond.is_owner and cond.is_newer) filtered
    LEFT JOIN LATERAL
    (SELECT id, json from view_dataset) dataset
    USING (id)
    RIGHT JOIN cond
    USING(id);

... but this query and variations are at least 2x slower.

So my question is how to maximise performance by short-circuiting JOINs or subqueries based on conditions; and I'm also interested to hear if somebody has other ideas how to implement some check-first-then-execute pattern such as checking for record ownership.

Not sure why authed is needed at all. What does:

SELECT is_owner, is_newer, json
FROM (
     SELECT id, owner = '053bffbc-c41e-dad4-853b-ea91fc42ea18' "is_owner"
          , modified >= created "is_newer"
     FROM datasets
     WHERE id = '056e4eed-ee63-2add-e981-0c86b8b6a66f'
) cond
LEFT JOIN LATERAL (
     SELECT json
     FROM view_dataset
     WHERE id = cond.id
) dataset
    ON is_owner and is_newer;

by you? I also agree with the comment made by a_horse_with_no_name. LATERAL can be a tremendous help for pushing predicates down to the base tables in special cases, but it is just a sub-query in disguise so in most cases it makes more sense to do an ordinary join. Do also try:

SELECT is_owner, is_newer, json
FROM (
     SELECT id, owner = '053bffbc-c41e-dad4-853b-ea91fc42ea18' "is_owner"
          , modified >= created "is_newer"
     FROM datasets
     WHERE id = '056e4eed-ee63-2add-e981-0c86b8b6a66f'
) cond
LEFT JOIN view_dataset wd
    ON wd.id = cond.id
   AND cond.is_owner
   AND cond.is_newer;

EDIT. table valued function

CREATE FUNCTION get_view_dataset(int,bool)
    RETURNS setof view_dataset AS '
        SELECT *
        FROM view_dataset wd
        WHERE wd.id = $1
          AND $2;
    ' LANGUAGE SQL;

and then use that function in your query as:

SELECT is_owner, is_newer, json
FROM (
     SELECT id, owner = '053bffbc-c41e-dad4-853b-ea91fc42ea18' "is_owner"
          , modified >= created "is_newer"
     FROM datasets
     WHERE id = '056e4eed-ee63-2add-e981-0c86b8b6a66f'
) cond
LEFT JOIN get_view_dataset(cond.id, cond.is_owner AND cond.is_newer);

All untested.


How can this larger subquery be (much) faster? In my organisation Ricoh is the supplier of printers and copiers. Papercut is used for registering which copies are made and for which account. Another application working with Ricoh is registering copies in a different database, but both are somewhat linked.
My organisation wanted a central place to retrieve the total cost so I wrote some queries. Most work fine, but something weird is going on with the one below and I'm unable to figure out why this is failing. What could be the reason of the following issue.
It's hard to make a db<>fiddle as the databases are quite complicated. I'll try to explain the issue here.
In essence there are two main tables. A table maintained by Ricoh containing client identifiers ClientId, a price (cost of the copywork), print timestamps and a job identifier. The table maintained by Papercut also has a timestamp, a printed flag and a reference to the corresponding JobId in its job_comment.
Now consider the following query which gives the total printcost when having printed using the seperate Ricoh application.
SELECT CliendId,
       SUM(ricoh.price) AS cost
FROM
  (SELECT a.SystemId as ClientId,
          a.ProcessInternalUid as JobId,
          a.price
   FROM   ricoh.printhistory AS a
   WHERE a.Exportdate >= '2020-10-01'
     AND a.Exportdate <= '2020-12-12') AS ricoh
INNER JOIN
  (SELECT substring(b.job_comment, 16, 36) AS JobId
   FROM  papercut.printer_usage_log AS b
   WHERE b.usage_date >= '2020-10-01'
     AND b.usage_date <= '2020-12-12'
     AND b.printed = 'Y'
   GROUP BY substring(b.job_comment, 16, 36)) AS papercut ON (papercut.JobId = ricoh.JobId)
GROUP BY ricoh.CliendId

If I run this query I have instant results, where the subqueries ricoh and papercut have 96.321 and 9.354 rows respectivly.
But if I change the lower bound of the date range to 2020-11-01 the query takes 49 minutes (!). While the subqueries have 46.223 and 4.547 records respectivly.
How can this be? How can a join with larger subqueries result in much faster results? I'm really puzzled by this. What could be a reason for this increase in time? (Then I can try to debug further)
I've done some (initial) debugging and noticed the following:

There is a limit date. If I set the lower bound to anything higher then 2020-10-08, the execution time gets large. I went looking for some troublesome records with that timestamp but found none. Also, choosing any date higher, like even 2020-12-01 results in high execution times.
If I remove the GROUP BY aggregation (and the sum) execution is instantaneous. But if I add an ORDER BY ricoh.price then execution time rises again. Could something with the price be an issue? It's a floating point number. I tried converting it to an integer but the issues remains.

EDIT:
Added the execution plans as requested by user J.D. I'm not really that familiar with these plans. I would guess the main difference is that Hash Match?

'Fast' query: Execution plan
'Slow' query: Execution plan

Also, this query is running on SQL server 2012. And since the database is managed by external firms I cannot add indexes. Is there a way to rewrite the query to force the 'fast' execution plan?

Executon Plans
Reasons for different execution plans and/or different duration of execution plans can be because of different search predicates (aka WHERE clauses). This is what you have already observed.
When you change the WHERE clause and if the query isn't parameterised, then SQL Server's Query Optimizer will try and create a new best execution plan based on the information provided in the query.
To achieve the best execution plan, the Query Optimizer will have a peak at the statistics of the indexes/columns that could possibly be involved in the future execution plan and based on the information (data histogram) that is available in the statistics, the Query Optimizer will choose one or the other index.
That is a very short explanation on how SQL Server goes about creating an optimal execution plan based on the statistics.
Now what can impact the creation of a not so optimal execution plan?
If the data in the statistics are old or outdated or haven't been automatically updated, because the trigger value of the automatic statistics updated hasn't been reached, then the Query Optimizer could assume that an index is better than another and make a bad choice.
Trigger values for automatic statistic updates are when the following number of data rows have changed:

older than SQL Server 2016 : 20% of total rows changed + 500 rows
SQL Server 2016 or newer : Square Root (1000 * number of rows changed)

Reference: Statistics (Microsoft | SQL Docs)
Your Execution Plans
I took the liberty of having a look at your execution plans in SentryOne's Plan Explorer (free as in free beer, highly recommended, not affiliated). The advantage is that the tool displays the amount of rows for each branch of the execution.
Good Plan

Bad Plan

Observations
The execution plans are basically the same. It's just that the Clusterd Index Scan on [dbo].[RCH_DM_Printhistory] in the bad/slow plan is at the beginning and in the good/fast plan this step is performed later on.
Good Plan
If you have a look at the red number just above the lines, you will note for the good plan, that the query retrieves:

 2'874 rows from Nested Loop part
14'808 rows from the Clustered Index Scan

The Query Optimizer only has to compare 2'874 rows with 14'808 rows which is pretty fast.
Bad Plan
If you have a look at the number for the bad plan, then the query has to retrieve:

    5'759 rows from the Clustered Index Scan
4'048'577 rows from the Nested Loop part

In the bad plan the query optimizer has to compare 5'759 rows with 4 Mio rows, which is going to be slow anyway.
The Query Optimizer normally creates a plan that has tables/indexes with lower row numbers at the beginning in order to reduce the amount of data it has to compare with other tables later on in the execution plan.
Generally the execution plans seem to be the best possible execution plans for your statements.
Estimated Rows and Statistics
As pointed out by others the amount of rows estimated is always 1 compared to the actual amount of rows retrieved. This is bad, because it means the Queryy Optimizer is relying on statistics that contain the wrong information on the amount of distributed values in the indexes or relying on statistics that only have scanned a certain amount of data in the table(s).
Possible Solutions
(See script at end of post for retrieving pre-formatted statements and information on trigger level for status updates)

Update the statistics for the Clustered Index ORDERBY on the table [dbo].[RCH_DM_PRINTHISTORY]. You can retrieve the statements to update the statistics from my query at the end of the post.

Ensure Auto Update Statistics has been turned on for your database(s).
SELECT sdb.name,
       sdb.is_auto_create_stats_on,
       sdb.is_auto_update_stats_on,
       sdb.is_auto_update_stats_async_on
FROM sys.databases AS sdb

The value for is_auto_update_stats_on should be 1.

Ensure Auto Create Statistics has been truned on for your database(s).
SELECT sdb.name,
       sdb.is_auto_create_stats_on,
       sdb.is_auto_update_stats_on,
       sdb.is_auto_update_stats_async_on
FROM sys.databases AS sdb

The value for is_auto_create_stats_on should be 1.

Create the missing indexes mentioned in the execution plans. Be wary though. Sometimes the so-called "Missing Indexes" are already available. Test in a non-productive environment.

Re-write the query using temporary tables or CTEs.

Statement to Retrieve Statistics Information
SELECT 'DBCC SHOW_STATISTICS ([' + [sch].[NAME] + '.' +[so].[NAME] + '] , [' + [ss].[NAME] + ']) WITH STAT_HEADER'       AS [SHOW_STATISTICS],
       'update statistics ' + [sch].[name] + '.' + [so].[name] + ' ' + [ss].[name] + ' WITH FULLSCAN'                    AS [UPDATE_STATISTICS] -- PAGECOUNT=100, ROWCOUNT=100 | FULLSCAN
       'DBCC UPDATEUSAGE(' + DB_NAME() + ', ''' + [sch].[NAME] + '.' +[so].[NAME] + ''')'                                AS [UPDATE_USAGE],
       [sch].[name] + '.' + [so].[name]                        AS [TableName],
       [ss].[name]                                             AS [Statistic],
       [sp].[last_updated]                                     AS [StatsLastUpdated],
       [sp].[rows]                                             AS [RowsInTable],
       [sp].[rows_sampled]                                     AS [RowsSampled],
       [sp].[modification_counter]                             AS [RowModifications],
       100 / (1.0 * [sp].[rows]) * [sp].[modification_counter] AS [PercentChanged],
       SQRT(1000 * [sp].[rows])                                AS [> 2014 Algorithm Change Value],
       CASE
            WHEN SQRT(1000 * [sp].[rows]) < [sp].[modification_counter] THEN 1
            ELSE 0
       END                                                     AS [Auto Update > 2014 Triggered],
       [sp].[rows] * 1.0 / 100 * 20 + 500                      AS [<=2014 Algorithm Change Value],
       CASE
            WHEN [sp].[rows] * 1.0 / 100 * 20 + 500 < [sp].[modification_counter] THEN 1
            ELSE 0
       END                                                     AS [Auto Update <= 2014 Triggered]
FROM   [sys].[stats] [ss]
       JOIN [sys].[objects] [so]
            ON  [ss].[object_id] = [so].[object_id]
       JOIN [sys].[schemas] [sch]
            ON  [so].[schema_id] = [sch].[schema_id]
       OUTER APPLY [sys].[dm_db_stats_properties]([so].[object_id], [ss].[stats_id]) sp
WHERE  1 = 1
       AND [so].[type] = 'U'
           -- AND [sp].[modification_counter] > 0
           -- AND 100/(1.0*[sp].[rows])*[sp].[modification_counter] < 10.0     -- maximum percentage change (certain tables have a high volatility)
           -- AND 100/(1.0*[sp].[rows])*[sp].[modification_counter] > 0.001    -- minimum percentage change (we aren't going to be looking at statistics with a very low percentage of change)
           -- AND [sp].[rows] > 1000000                                        -- only look at statistics which contain more than 1'000'000 rows.
           -- AND [sp].[last_updated] < dateadd(hh,-1,getdate())               -- only look at statistics which have been updated more than an hour ago
       AND [sch].[name] = 'dbo'
       AND [so].[name] = 'RCH_DM_PRINTHISTORY'
       -- AND [ss].[name] NOT LIKE '_WA_Sys%'                                  -- Exclude automatically create statistics
       -- AND [ss].[name] not like '_dta_stat%'                                -- Exclude statistics crete by the Database Tuning Advisor
       -- AND (SQRT(1000 * [sp].[rows]) < [sp].[modification_counter] OR [sp].[rows] * 1.0 / 100 * 20 + 500 < [sp].[modification_counter])
ORDER BY
       [sch].[name] + '.' + [so].[name] ASC,
       [ss].[name] ASC,
       [sp].[last_updated] DESC;

This statement is pre-configured for the RCH_DM_PRINTHISTORY table mentioned in this question. Change the value for [so].[name] to search different tables.


Why scan clustered index instead seek nonclustered index? There is SQL script:

CREATE TABLE users
  (
     id        INT,
     firstname VARCHAR(50),
     surname   VARCHAR(50)
  );

CREATE CLUSTERED INDEX ix_users_id
  ON users (id);

CREATE NONCLUSTERED INDEX ix_users_firstname
  ON users (firstname);

SELECT firstname,
       surname
FROM   users
WHERE  firstname = 'John';

I do not understand why for the above SELECT request, Engine of SQL Server 2019 selected follows Execution Plan:

Why the Clustered Index is scanned? I thought, faster is:

seek Nonclustered Index;
move on a Clustered Index by Clustered Index Pointer, which
storing in Leaf Node of Nonclustered Index;
and take rest surname value from there.

The key idea here is that your index contains (firstname,id), but not surname.  So the options for this query

SELECT firstname,
       surname
FROM   users
WHERE  firstname = 'John';

are

1) Scan the clustered index

2) Seek the non-clustered index, and then for every matching row in the index, Seek on the Clustered Index to find the surname.  It's this "bookmark lookup" that is the most expensive part of the query, and if a reasonable percentage of your users are named 'John', it may well be cheaper just to scan the clustered index.

This is why we have indexes with included columns.  You can add surname to the index to enable this query to seek on the non-clustered index, and avoid the bookmark lookup.  The index would then be a "covering index" for the query.  eg

CREATE NONCLUSTERED INDEX ix_users_firstname
  ON users (firstname)
   include (surname);


Log Shipping- Can I alter the schema of a table that is being used in the secondary instance? We have two SQL Server instances with log shipping set on a database.
Can I alter the schema (add columns, remove columns etc.) of the table in the Primary instance/db while the table is being used in the Secondary instance/db?

Can I alter the schema (add columns, remove columns etc.) of the table in the Primary instance/db while the table is being used in the Secondary instance/db?

Yes you can. You can run alter command on primary database and at same time a select query can run on secondary database accessing same table this is perfectly fine and allowed. But when the changes you made on primary are replayed on secondary then the users might be disconnected and unless restore operation completes you wont be able to run query. This is subject to condition that you have selected check box Disconnect users in database while restoring


Why do SQL Server auto-created statistics sometimes include the primary key? I had always understood that SQL Server auto-created statistics only include one column.  In other words, they are not multi-column statistics.
However, today I came across some auto-created statistics that have two columns.  The second column is always the primary key of the table.   There are other auto-created statistics on the same table that don't have a second column.

auto_created = 1 on the sys.stats table.
Also, some statistics have a different naming convention on the exact same tables. Some of them have a column name in the statistic name.  What makes SQL use a different naming convention?
For example,

_WA_Sys_BegEffDate_7363C5E4
_WA_Sys_0000000B_7363C5E4

These are not causing problems, I am just curious about it.

I have spent a decent amount of time searching for an explanation of the different naming conventions on some of my own databases, and haven't found anything definitive.
That being said, in both our cases we are dealing with very old databases that have undergone many upgrades over the years. My best guess is that at some point Microsoft switched the auto-created statistics to use an incremental alphanumeric pattern instead of using partial column names (somewhere around SQL Server 2000 or 2005?). This may have been done to reduce the potential for naming conflicts or to better handle the renaming of a column.
I haven't seen the Primary Key be added into auto-created statistics, but that might also be an artifact of a very old version.


How to convert _id field of all existing documents from type String to Number At one of my collections I have an _id field that stores numbers. The field was defined originally in the Mongoose model as String. However, all the data concerning this field in the collection contains pure integers stored as strings. I want to preserve all the data in my collection while converting the type of this field (and all associated _id's) from type String to type Number. Any suggestions as how to do that? Thanks.

db.getCollection('test').find().forEach(function (doc) {
    db.getCollection('test').remove({ _id : doc._id});
    tempId = new NumberLong(doc._id);
    doc._id = tempId;
    db.getCollection('test').save(doc);
    }
);

This will do. You may want to add error handling as per your use case. Also make sure you have backup before migrating.


For the above requirement, following query should work.

SELECT j.jobid, j.name, af.id, af.kind, af.location
FROM jobs j
INNER JOIN job_queues jq ON jq.id = j.job_queue_id
INNER JOIN organisations o ON o.id = jq.organisation_id
LEFT OUTER JOIN job_attached_files jaf ON jaf.job_id = j.id
INNER JOIN attached_files af ON af.id = jaf.attached_file_id
WHERE o.Id = 1

P.S: I am posting this query without any testing, so if there are any compilation errors or any unwanted results, please correct the query if you want or notify me, so that I can edit my answer accordingly


SELECT records that contains just ENGLISH A - Z 1 -9 charachters I have a non English language website. Recently someone published a lot of English spammy topics.

I need a SQL statement to select topics that contains only English language.

Knowing that some of our original (not spammy) website topics contains some phrases in English we do not want to bring those for us.

I tried:

SELECT * FROM `posts` WHERE `title` NOT LIKE '[^\x20-\x7E]'

The result was all posts.

For example:

"Where are you from?"    ---> Only English NOT ALLOWED
"من اين انت؟"                     ---> Only Arabic ALLOWED
"مامعنى كلمة where بالانكليزي"  --> English & Arabic ALLOWED

We just need to browse which like the no. 1 without results like no. 3

For MySQL you can use REGEXP

Like

 SELECT * FROM table WHERE column REGEXP '^[[:alnum:]]+$';

Or if you need to exclude some letters

 SELECT * FROM table WHERE column REGEXP '^[A-Za-z0-9]+$';

SQL Server has a similar Expression like2

SELECT * FROM table WHERE column NOT LIKE '%[^ ,.-~]%' collate Latin1_General_BIN

DEMO für ,ysql

CREATE TABLE Demo (ID INT NOT NULL AUTO_INCREMENT
, Name VARCHAR(50) NOT NULL,
     PRIMARY KEY (ID));

INSERT INTO Demo (Name)

SELECT '0123456789'

UNION ALL

SELECT 'abcdefghijklmnopqrstuvwxyz'

UNION ALL

SELECT 'ABCDEFGHIJKLMNOPQRSTUVWXYZ'

UNION ALL

SELECT '0.a'

UNION ALL

SELECT '0,a'

UNION ALL

SELECT '0éa'

UNION ALL

SELECT '0!a'

UNION ALL

SELECT '0?a'

UNION ALL

SELECT '0''a'

UNION ALL

SELECT '0#a'

UNION ALL

SELECT '0+a'

✓

✓

SELECT * FROM Demo  WHERE Name REGEXP '^[A-Za-z0-9]+$';

ID | Name
-: | :-------------------------
 1 | 0123456789
 2 | abcdefghijklmnopqrstuvwxyz
 3 | ABCDEFGHIJKLMNOPQRSTUVWXYZ

SELECT * FROM Demo

ID | Name
-: | :-------------------------
 1 | 0123456789
 2 | abcdefghijklmnopqrstuvwxyz
 3 | ABCDEFGHIJKLMNOPQRSTUVWXYZ
 4 | 0.a
 5 | 0,a
 6 | 0éa
 7 | 0!a
 8 | 0?a
 9 | 0'a
10 | 0#a
11 | 0+a

db<>fiddle here

SQL SERVEr Example

CREATE TABLE #Demo (ID INT NOT NULL IDENTITY(1,1), Name VARCHAR(50) NOT NULL)
INSERT INTO #Demo (Name)

SELECT '0123456789'

UNION ALL

SELECT 'abcdefghijklmnopqrstuvwxyz'

UNION ALL

SELECT 'ABCDEFGHIJKLMNOPQRSTUVWXYZ'

UNION ALL

SELECT '0.a'

UNION ALL

SELECT '0,a'

UNION ALL

SELECT '0éa'

UNION ALL

SELECT '0!a'

UNION ALL

SELECT '0?a'

UNION ALL

SELECT '0''a'

UNION ALL

SELECT '0#a'

UNION ALL

SELECT '0+a'
GO

11 rows affected

SELECT * FROM #Demo WHERE Name NOT LIKE '%[^ ,.-~]%' collate Latin1_General_BIN
GO

ID | Name
-: | :-------------------------
 1 | 0123456789
 2 | abcdefghijklmnopqrstuvwxyz
 3 | ABCDEFGHIJKLMNOPQRSTUVWXYZ
 4 | 0.a
 5 | 0,a
 8 | 0?a

SELECT * FROM #Demo
GO

ID | Name
-: | :-------------------------
 1 | 0123456789
 2 | abcdefghijklmnopqrstuvwxyz
 3 | ABCDEFGHIJKLMNOPQRSTUVWXYZ
 4 | 0.a
 5 | 0,a
 6 | 0éa
 7 | 0!a
 8 | 0?a
 9 | 0'a
10 | 0#a
11 | 0+a

db<>fiddle here


Restore Structure Only We have a database that is 3TB+ in size, and don't have enough space to restore it.

However, we want to retrieve code (stored procs) from a 15 day old backup.

Is is possible to restore the structure only from an Oracle backup?

The source of stored procedures is stored in the SYSTEM tablespace in the SOURCE$ table.

You need not to restore any user data at all, simply restore the SYSTEM, SYSAUX and UNDO tablespaces and skip the rest. You can then open the database with the other tablespaces offlined, and you can access the stored procedures as usual, or query DBA_SOURCE or SOURCE$ directly.


Why does Oracle indicate my table is using so little space? I'm running Oracle 11g.  I have a table with two BLOB columns.  I inserted 10,000 rows into the table, and each column contained a byte array of size 10289.

As you can see, there are 10,000 rows in the table and each column has 10,289 bytes in it:

SQL> select count(*) from MYUSER.MYTABLE;

  COUNT(*)
----------
     10000

SQL> select min(length(COL1)), min(length(COL2)) from MYUSER.MYTABLE;

MIN(LENGTH(COL1))      MIN(LENGTH(COL2))
-----------------      -----------------
            10289                  10289

Therefore the table should be using up at least 205MB of space (10289 bytes * 2 * 10000).

But I used the query given here to find out how much space the table is using, and I got the following:

SQL> SELECT SUM(bytes), SUM(bytes)/1024/1024 MB FROM dba_extents WHERE owner = 'MYUSER' AND segment_name = 'MYTABLE';

SUM(BYTES)         MB
---------- ----------
   2097152          2

Why does it say the table is using up just 2 MB when it should be using up 250 MB?

The byte arrays I'm inserting are compressed data, so Oracle couldn't possibly be compressing that data any further.

Your table's segment is only 2 MB.  The rest of the data is stored in the LOB segments which are physically separate from the table segment.

SELECT column_name, segment_name
  FROM dba_lobs
 WHERE owner = 'MYUSER'
   AND table_name = 'MYTABLE'

should show you that there are two LOB segments associated with the table.  You can then query dba_segments to get the size of those segments

SELECT sum(bytes)/1024/1024 mb
  FROM dba_segments
 WHERE owner = 'MYUSER'
   AND segment_name IN (SELECT segment_name
                          FROM dba_lobs
                         WHERE owner = 'MYUSER'
                           AND table_name = 'MYTABLE')


Connecting to an 11g Standard Database (Local) I've installed 11g Standard on a VM, it's going to be connected to from other Computers/Users. It installs successfully but I'm not really sure how to create my first database on it (using SQL Developer).

I'm trying to connect with SQL developer, but I'm not really sure of the parameters? Should I be able to connect at this stage? What do I enter for Username/Password etc...?

I've seen guides but they are for Express Edition. This is on a Windows 8 64 Bit VM Btw.

Ok, you have the database software installed. Here is the 11.2 Installation Quick Guide for Windows Here is the 11.2 documentation home. Now you need to install a database instance and start a listener. Then you need to create a connection in SQL Developer to the database instance.

Some essential tools during this are SQLPlus on the server. Enterprise Manager Database Control http://host.domain:1158/em/ You will need to Remote Desktop into the server to perform database setup tasks as the SYS user. On the server DOS window:

sqlplus /nolog
SQL>connect sys@myinstance AS SYSDBA
enter your password.

If you did not create a database instance during the install, use Database Configuration Assistant (DBCA). A universal approach for client connectivity is to use a tnsnames.ora file located in $ORACLE_HOME/network/admin. The parameters for this become the connection parameters in SQL Developer.

In a DOS window on the server, run the listener control with

lsnrctl help
lsnrctl status   tells you which instances are running.

Once you have the instance created, create a new tablespace to hold your tables. Then create a user that will be the schema owner of the tables associated with that tablespace. It is a good practice to create a separate user for each person in addition to the schema owner. Then create tables, views, stored procedures, etc.

Hope this is enough to get you started. The links are good reads.


MySQL cluster fast inserts but slow selects I have a MySQL cluster with 3 Data Nodes and one SQL node. Currently I have 1 million 1kb records. The inserts are very fast, takes only a few ms. But the selects are very slow >20s.

All machines are m1.large EC2 instances (8GB RAM).

Here is the config.ini:

[NDB_MGMD]
NodeId=49
HostName=host1
DataDir=/var/lib/mysql-mgmd-data
Portnumber=1186

[NDBD DEFAULT]
NoOfReplicas=1
DataMemory=1536M
IndexMemory=2560M
StringMemory=25
MaxNoOfTables=4096
MaxNoOfOrderedIndexes=2048
MaxNoOfUniqueHashIndexes=512
MaxNoOfAttributes=24576
MaxNoOfTriggers=14336

FragmentLogFileSize=256M
InitFragmentLogFiles=SPARSE
NoOfFragmentLogFiles=16
RedoBuffer=48M
MaxNoOfConcurrentOperations=100000
MaxNoOfConcurrentTransactions=16384
TimeBetweenGlobalCheckpoints=1000
TimeBetweenEpochs=100
TimeBetweenEpochsTimeout=32000

DiskCheckpointSpeedInRestart=100M
DiskCheckpointSpeed=10M
TimeBetweenLocalCheckpoints=20

HeartbeatIntervalDbDb=15000
HeartbeatIntervalDbApi=15000

MemReportFrequency=30
BackupReportFrequency=10
LogLevelStartup=15
LogLevelShutdown=15
LogLevelCheckpoint=8
LogLevelNodeRestart=15

SharedGlobalMemory=384M
DiskPageBufferMemory=1G
BatchSizePerLocalScan=512
[NDBD]
NodeId=1
HostName=host2
DataDir=/mnt/mysql-cluster/1/
[NDBD]
NodeId=2
HostName=host3
DataDir=/mnt/mysql-cluster/2/
[NDBD]
NodeId=3
HostName=host4
DataDir=/mnt/mysql-cluster/3/
[MYSQLD DEFAULT]
[MYSQLD]
NodeId=53
HostName=host1

my.cnf

[mysqld]
ndbcluster
ndb-nodeid=53
ndb-connectstring=host1,

Insert:

INSERT INTO mytab(mykey, a, b, c, d, e, f, g, h, i,j)VALUES (1,1,2,3,4,5,6,7,8,9,0);

Select:

SELECT * FROM mytab WHERE mykey = 12345;

Create table statement:

 mytab | CREATE TABLE `mytab` (
  `mykey` varchar(32) NOT NULL,
  `a` varchar(100) DEFAULT NULL,
  `b` varchar(100) DEFAULT NULL,
  `c` varchar(100) DEFAULT NULL,
  `d` varchar(100) DEFAULT NULL,
  `e` varchar(100) DEFAULT NULL,
  `f` varchar(100) DEFAULT NULL,
  `g` varchar(100) DEFAULT NULL,
  `h` varchar(100) DEFAULT NULL,
  `i` varchar(100) DEFAULT NULL,
  `j` varchar(100) DEFAULT NULL,
  PRIMARY KEY (`mykey`)
) /*!50100 TABLESPACE mytab_space STORAGE DISK */ ENGINE=ndbcluster DEFAULT CHARSET=latin1 MAX_ROWS=1000000000
/*!50100 PARTITION BY KEY (mykey) */ |

Has someone an idea why it is so slow?
Has someone experience with MySQL-cluster and EC2 m1.large instances?

It seems that for solving your problem you chose the wrong tool.

MySQL Cluster is good when you do mostly key based lookups from multiple threads from memory based tables. Disk based tables may be useful when you have rarely read data. Or your work dataset is small portion of the table which should fit into memory cache whose size is defined by DiskPageBufferMemory config variable.

If your queries need many range or full scans - MySQL Cluster is slow even on physical machines. That is because such queries need a lot of data exchanges between data nodes. Try pinging between your data nodes. And for range scan data nodes may need to exchange hundreds and thousands of such messages.

Also MySQL previously stated that for data nodes your should use physical machines and have good interconnect for data node traffic. I doubt that this recommendation is no more valid nowadays.

And I think you should try cleaning up your config. For testing most of those things hardly changes anything and some setting may be slowing down things. Try such simplified NDBD_DEFAULT section:

[NDBD DEFAULT]
NoOfReplicas=1
DataMemory=1536M
IndexMemory=2560M

LockPagesInMainMemory=2
ODirect=1


PostgreSQL not using partial index when using boolean in WHERE clause I'm trying to create a partial index which includes rows based on a boolean value but in my tests neither rows with the value set to TRUE or FALSE are included in the index, or at least the index isn't being used.

Example schema

CREATE TABLE pitest
(
    id SERIAL,
    name TEXT,
    location TEXT,
    inactive BOOLEAN DEFAULT false NOT NULL
);

CREATE INDEX idx_name ON pitest (name) WHERE inactive IS NOT TRUE;

Example rows

id  |        name         |          location          | inactive
----+---------------------+----------------------------+----------
101 | Anne Lopez          | Bajiao                     | t
102 | Jane Garcia         | Sanhe                      | f

I've populated the table with 1000 rows of which 109 have inactive = TRUE and the remaining 891 have inactive = FALSE.

Queries that should and shouldn't match the WHERE clause in the index both result in a sequential scan:

# EXPLAIN SELECT location FROM pitest WHERE name = 'Anne Lopez';
                      QUERY PLAN
-------------------------------------------------------
 Seq Scan on pitest  (cost=0.00..21.50 rows=1 width=9)
   Filter: (name = 'Anne Lopez'::text)

# EXPLAIN SELECT location FROM pitest WHERE name = 'Jane Garcia';
                      QUERY PLAN
-------------------------------------------------------
 Seq Scan on pitest  (cost=0.00..21.50 rows=1 width=9)
   Filter: (name = 'Jane Garcia'::text)

I've tried this on 2 different servers, 9.3 and 9.4 and with a larger test table with >1.5m rows of which around 150,000 had inactive = TRUE.

I'm trying to minimise the amount of disk space used by indexes by only indexing users without the 'inactive' flag set, but don't want to have to have to specify this flag in queries. Is it possible to use partial indexes in this way? Thanks!

Partial indexes make sense when they match the query pattern. In your case if you would have queries with inactive IS NOT TRUE condition then the index would be used otherwise it's simply not useful.

Cannot use the partial index:

SELECT location FROM pitest WHERE name = 'Anne Lopez';

Can benefit from the index:

SELECT location FROM pitest WHERE name = 'Anne Lopez' AND inactive IS NOT TRUE;


Changing Service Account password in SQL Server - Clarification We have the SQL Server Service and SQL Server Credential using same AD Level service Account.

Because of recent security changes we need to change the Service account password at AD Level.

At this stage :

If I change my SQL Server Credential password first with new password will it get directly applied to SQL Server Service account which is using same service account ?

If by any chance:

Do we need to change the SQL Server account first in configuration manager followed by credential account password change?

Please clarify.

This may not be clear to you from the documentation states:

  If the instance of SQL Server is running under a domain account and
  the password for that account is changed, the password used by SQL
  Server must be updated to the new password.

This implies that you have to change the password in Active Directory first and then follow that procedure to update the SQL Server service account password using Configuration Manager.

As long as the service is running it will keep running after you change the password in Active Directory, but it will need the new one the first time it restarts.

Trying to change the password in Configuration Manager before it's changed in Active Directory will result in a failure to start the service as the account won't be able to authenticate.

Your credential will need to be changed after changing the password in Active Directory and isn't really related to when you change your service account password. You could do the credential first and the service account second or the other way around, as long as you change it in Active Directory first.

Changing the service account or the credential account will have no impact on one another. Updating one won't change the other and you have to change both yourself.


Security best practices for SSIS package execution via SQL Agent job We're trying to rein in the number of sysadmins on our servers, and one of the issues we're running into pertains to SQL Agent security -- especially with regard to SSIS jobs and our ETL team. I understand that non-sysadmins cannot execute SSIS job steps by default and that proxies can be created in order to allow this, but I'm still struggling with the implementation.
This post talks about using proxies to allow non-sysadmins to manage and maintain SQL jobs, but creating credentials for each of my ETL developers and maintaining those via screen-share every time their password changes (according to our AD policy) seems clumsy at best. Even with just a handful of developers this quickly becomes untenable. I can't afford to have jobs fail because someone's password expired, and I'd rather not rely on people remembering that the credential needs to be updated every time their password changes.
I'm curious how other shops deal with this. We can't be the only company with a team of ETL developers that need to be able to create and manage SQL Agent jobs to run the SSIS packages they develop. Do you use a service account for the credential/proxy and grant everyone on the ETL team access to the proxy? And if so, does this same service account "own" the SQL Agent job or do the individual developers?
It would be nice if anyone on the team could make changes to jobs as needed, but it seems the only way to accomplish this is to use a "shared" service account or make everyone sysadmins -- which is exactly the scenario we're trying to avoid. What are the best practices? Can anyone speak to their own implementation? I'm trying to understand how I can use credentials and proxies to help, but I  haven't been able to find much concrete guidance with specific implementation instructions or best practices.
Apologies if my question was all over the place. Here's the scenario: An SSIS developer deploys a package and creates a SQL Agent job to run that package. By default, he's the owner of the job. Because he's not a sysadmin, however, the job throws this error: Non-SysAdmins have been denied permission to run DTS Execution job steps without a proxy account.  The step failed. If I make the sa account the owner of the job, it executes but the SSIS developer can no longer make changes to it. Even if I grant ssis_admin in SSISDB and db_ssisadmin in msdb the job will fail.
I want to have my cake and eat it too. Obviously the job needs to run, and I would prefer not to involve a sysadmin every time a job needs edited. The closest I've come to being able to do this is to create a credential for the developer, associate a proxy with that credential, then give the proxy access to the SSIS Package Execution subsystem. But then I'm stuck maintaining passwords for each of the credentials every time they change. And if I should ever miss updating a credential password when it expires or is changed, the job will begin failing again.

The ETL dev owns the job;
The ETL dev belongs to SQLAgentOperatorRole in msdb (which allows him to make changes to his own jobs but not those owned by others);
The ETL dev belongs to db_ssisadmin, db_ssisltduser, and db_ssisoperator in msdb;
The ETL dev belongs to ssis_admin in SSISDB Even then, and even if I'm the one executing the job (as a sysadmin), it fails with Non-SysAdmins have been denied permission to run DTS Execution job steps without a proxy account.  The step failed.

The job will only succeed if it's owned by a sysadmin or if the SSIS step executes using a proxy. Does everyone just make their ETL devs sysadmins and call it a day? I'm leaning toward creating a service account to associate the credential with whose password doesn't expire then creating an SSIS proxy for the credential and granting all of the SSIS devs access to the proxy. At least then they're able to edit the jobs they own, the jobs will succeed so long as the SSIS step uses the proxy, and I'm not having to update credential passwords all the time.

You don't need to use a proxy account nor do you need to grant sysadmin to your ETL team for them to be able to run SSIS packages and SQL Agent jobs.
You should first look into the SQL Server Agent Fixed Database Roles, such as the SQLAgentOperatorRole. (I'm guessing you have already, but in case you haven't yet.)
If that alone doesn't solve your issue then you should also look into Integration Services Roles (SSIS Service) - Roles in the msdb database. If you need the ETL team to be able to execute SSIS packages that they don't own, then you'll want to particularly look into the db_ssisoperator role or db_ssisadmin role which are certainly more limited than sysadmin. (It sounds like db_ssisoperator is probably what you want.)
One caveat with the db_ssisadmin role is it has the ability to run an SSIS package that can execute the T-SQL to grant the sysadmin role to a user (if the package runs under an existing sysadmin account), should someone on your team feel maleficent enough to create such a package and run it, as mentioned in this warning:

**** Warning ****Members of the db_ssisadmin role and the dc_admin role may be able to elevate their privileges to sysadmin. This elevation of privilege can occur because these roles can modify Integration Services packages and Integration Services packages can be executed by SQL Server using the sysadmin security context of SQL Server Agent.

If you only need your ETL team to be able to run only their own packages, then the above warning is moot, because you can use the db_ssisltduser role instead.


insert row if table is empty, else update row (mysql) i have 2 tables "table1" and "table2" both have TIMESTAMP cols for starttime and endtime. i want something like

if (table2 is empty)
insert into table2 (table2.starttime, table2.endtime) value (table1.starttime, table1.endtime (last row))
else if (TIMESTAMPDIFF(SECOND,table2.endtime(last row),table1.starttime(lastrow))<10)
update table2.endtime (last row) value (table1.endtime)
else
insert into table2 (table2.starttime, table2.endtime) value (table1.starttime, table1.endtime (last row))

i have tried different aproches but i am unable to get it working specially IF-ELSE and IS NULL check.
i can get last row as (SELECT endtime from table1 order by endtime DESC limit 1)so kindly ignore that part.
MySql version: 5.5.38

got it working but there might be better ways

CREATE PROCEDURE simple6(IN name VARCHAR(20))
BEGIN

SET @sql_text = concat('SELECT endtime INTO @endtime1 FROM ',name,'big order by endtime DESC LIMIT 1');
PREPARE stmt FROM @sql_text;
EXECUTE stmt;

if @endtime1 IS NULL
then

SET @sql_text2=concat('INSERT INTO ',name,'big (starttime,endtime) SELECT starttime,endtime FROM ',name,' ORDER BY endtime DESC LIMIT 1');
PREPARE stmt2 FROM @sql_text2;
EXECUTE stmt2;
DEALLOCATE PREPARE stmt2;

else
SET @sql_text3= concat('set @cat:= (IF(TIMESTAMPDIFF(SECOND,(SELECT endtime from ',name,'big order by endtime DESC limit 1),(SELECT starttime from ',name,' order by starttime DESC limit 1)) < 5,1,0))');
PREPARE stmt3 FROM @sql_text3;
EXECUTE stmt3;
DEALLOCATE PREPARE stmt3;

if @cat !=0
THEN
SET @sql_text4=concat('UPDATE ',name,'big SET endtime = (SELECT endtime FROM ',name,' ORDER BY endtime DESC LIMIT 1) ORDER BY ID DESC LIMIT 1');
PREPARE stmt4 FROM @sql_text4;
EXECUTE stmt4;
DEALLOCATE PREPARE stmt4;

else
SET @sql_text5=concat('INSERT INTO ',name,'big (starttime,endtime) SELECT starttime,endtime FROM ',name,' ORDER BY endtime DESC LIMIT 1');
PREPARE stmt5 FROM @sql_text5;
EXECUTE stmt5;
DEALLOCATE PREPARE stmt5;

end if;
end if;
DEALLOCATE PREPARE stmt;

END\\


Multi-field unique constraint where we include one or another field based on nullity Suppose I have four columns

| x | y | z | w |

I would like the following constraint:

If w is NULL, then x, y, z must be unique.
If w is not NULL, then x, y, w must be unique.

Is this possible?

First, for constraint number 2, nothing special is needed. A UNIQUE constraint ignores - and accepts - NULL values so a simple unique constraint on (w,x,y) will work just fine enforcing that.

For constraint number 1, I don't think there's a way with DDL in version 5.6 so your best option is probably the trigger solution by @stefan.

If you can upgrade to 5.7, you can use a GENERATED column and a unique index on it:

ALTER TABLE t
  ADD wz int GENERATED ALWAYS AS (CASE WHEN w IS NULL THEN z END),
  ADD UNIQUE INDEX x_y_z_uq (x, y, wz) ;


How can I control the order of a query regarding to inner join I have 3 tables.

They have

table_a 5m records

table_b 20m records

table_c 20m records

I'm performing the query below.

SELECT * FROM table_a a
INNER JOIN table_b b ON a.a_id = b.a_id AND b.b_come='100'
INNER JOIN table_c c ON b.c_id = c.c_id AND c.c_type IN ('A','B')
WHERE date(a.a_dt) = '01/01/2012 00:00:00.000'

The query is quite slow. I think it is because the data in its nature they have

about 1000 records of a.a_id = b.a_id

and

about 20m*20m records of b.c_id = c.c_id.

I think I should get the result of a.a_id = b.a_id then do the one of b.c_id = c.c_id so that the performance could be a lot better. But it seems INNER JOIN has no concept of order. How should I do this to improve the performance?

The easiest way to influence a query plan and make it run faster is to have good indexes and ensure the statistic on those indexes are up-to-date.  In your case have indexes on the primary and foreign keys in each table.  It would be best if you included b_come and c_type in their respecitive indeces, too.  (Sorry, I'm not strong on Informix; if the syntax allows the include clause or filtered indexes investigate and implement these.)

Wrapping the date() function around a_dt generally precludes any use of an index on that column.  If makes it "non-SARGable" in the jargon.  It may be better to structure it as

WHERE a.a_dt >= '01/01/2012 00:00:00.000'
and   a.a_dt <  '01/02/2012 00:00:00.000' -- or whatever the day after 1/1 is in your locale.


Aggregation GROUP BY bottleneck I have a performance bottleneck with a SELECT GROUP BY operation.
Schema
I have a table like this:
CREATE TABLE [InverterData](
    [InverterID] [bigint] NOT NULL,
    [TimeStamp] [datetime] NOT NULL,
    [ValueA] [decimal](18, 2) NULL,
    [ValueB] [decimal](18, 2) NULL
    CONSTRAINT [PrimaryKey_e149e28f-5754-4229-be01-65fafeebce16] PRIMARY KEY CLUSTERED
    (
        [TimeStamp] DESC,
        [InverterID] ASC
    ) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF
    , IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON
    , ALLOW_PAGE_LOCKS = ON)
)

and a Index like this:
CREATE NONCLUSTERED INDEX [TimeStamp_Power-NonClusteredIndex] ON [dbo].[InverterData]
(
    [InverterID] ASC,
    [TimeStamp] ASC
)
INCLUDE
(
    [ValueA],
    [ValueB]
)

The [InverterData] table as has following storage stats:

Data space: 26,901.86 MB
Row count: 131,827,749
Partitioned: true
Partition count: 62

Usage
With my descripted schema (one plus some extra table that are not important for my question) I can run super fast queries like this:
SELECT [TimeStamp], [ValueA], [ValueB]

FROM [InverterData]

JOIN [Inverter] ON [Inverter].[ID] = [InverterData].[InverterID]
JOIN [DataLogger] ON [DataLogger].[ID] = [Inverter].[DataLoggerID]

WHERE [DataLogger].[ProjectID] = 20686
 AND [InverterData].[TimeStamp] >= '20160108'
 AND [InverterData].[TimeStamp] < '20160109'

Excecution timespan: 178ms
Excecution plan:

Problem
I now want to make a SELECT GROUP BY by [InverterID] and 15 minute interval of [TimeStamp].
Somethink like this:
SELECT [InverterID]
 , DATEADD(MINUTE, DATEDIFF(MINUTE, 0, [TimeStamp] ) / 15 * 15, 0) AS [TimeStamp]
 , SUM([ValueA]), SUM([ValueB])

FROM [InverterData]

JOIN [Inverter] ON [Inverter].[ID] = [InverterData].[InverterID]
JOIN [DataLogger] ON [DataLogger].[ID] = [Inverter].[DataLoggerID]

WHERE [DataLogger].[ProjectID] = 20686
 AND [InverterData].[TimeStamp] >= '20160107'
 AND [InverterData].[TimeStamp] < '20160108'

GROUP BY
 [InverterID], DATEADD(MINUTE, DATEDIFF(MINUTE, 0, [InverterData].[TimeStamp] ) / 15 * 15, 0)

Excecution timespan: 4637ms
Excecution plan:

Attempts
I think it can be related to the necessary Sort operation here:

As far as I know it is possible to avoid this SORT by creating a matching indexer. But I don't know how to do this with my 15 minute interval grouping.
Question
As you can see the excecution timespan of the SELECT GROUP BY is massivly longer. But I don't know where and how to avoid the bottleneck!?
Update 1 (related to @Max Vernon answer)
If it is possible ide like to have a faster solution where I can flexible change the interval (for example 10, 15 or 6o minutes). So without calculated columns.

You could add a calculated column to the table and build an index from the calculation.

For instance, the table would be:

CREATE TABLE dbo.InverterData
(
     InverterID bigint NOT NULL
    , TS datetime NOT NULL
    , ValueA decimal(18, 2) NULL
    , ValueB decimal(18, 2) NULL
    , TS15 AS (DATEADD(MINUTE, DATEDIFF(MINUTE, 0, TS ) / 15 * 15, 0))
    , CONSTRAINT PK_InverterData PRIMARY KEY CLUSTERED
    (
        TS DESC,
        InverterID ASC
    ) WITH (
        PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF
        , IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON
        , ALLOW_PAGE_LOCKS = ON
        )
);

I've modified the name of the TimeStamp column to TS since TimeStamp is a reserved word.

After looking at Paul's comment; I think a good index might be:

CREATE INDEX IX_InverterData_ID_TS151
ON dbo.InverterData (TS15, InverterID);

If you change the WHERE clause to operate on the indexed timestamp column, I get no sort in the plan.  I figure the change to the where clause is likely not a problem since you appear to be selecting on whole days.

SELECT InverterData.InverterID
    , InverterData.TS15
FROM InverterData
WHERE InverterData.TS15 >= '20140107'
    AND InverterData.TS15 < '20160108'
GROUP BY InverterData.InverterID
    , InverterData.TS15
OPTION (RECOMPILE);

Granted, I've not included the other tables in my example, since you have not provided those details.

The plan for the query above is, with 100,000 rows in my sample table:

Having said all that, without the actual table definitions, including the partitioning scheme, etc, it is difficult to tell how well this will actually work for you.

Since adding a (non-persisted) computed column is a meta-data only operation, modifying the table should be nearly instantaneous.  You'd still likely want to do this when you know there are no other transactions (or as few as possible) occurring so the required schema lock (although very short lived) won't block.  The DDL to modify the table is:

ALTER TABLE dbo.InverterData
ADD TS15 AS (DATEADD(MINUTE, DATEDIFF(MINUTE, 0, TS ) / 15 * 15, 0));


How to show the Availability group listener instead of the nodes? I have just installed a sql server availability group, always on, with 2 servers,

SQLPROD1_TS and SQLPROD2_TS  as you can see on the picture below:

select distinct rcs.database_name,
ar.replica_server_name,
drs.synchronization_state_desc,
drs.synchronization_health_desc,
CASE rcs.is_failover_ready
WHEN 0 THEN 'Data Loss'
WHEN 1 THEN 'No Data Loss'
ELSE ''
END as FailoverReady

from sys.dm_hadr_database_replica_states drs

inner join sys.availability_replicas ar
       on drs.replica_id = ar.replica_id
      and drs.group_id = ar.group_id

inner join sys.dm_hadr_database_replica_cluster_states rcs
        on drs.replica_id = rcs.replica_id

order by replica_server_name

the listener for the availability group is calle SQLPROD_TS as you can see on the picture below

select * from
sys.availability_group_listener_ip_addresses

select * from
sys.availability_group_listeners

select * from
sys.dm_tcp_listener_states

all is good, however, when I ask people to connect to SQLPROD_TS when they browse the network they can only see the nodes of the cluster.

Is there a way I could make sqlProd_ts to show up on the picture above, instead of the nodes?

Is there a way I could make sqlProd_ts to show up on the picture above, instead of the nodes?

No, much as Dan Guzman has said, the listener is not an instance of SQL Server and as such will not respond to browser interrogations. In fact, availability groups do not use the browser service.

If you want people to use the listener, tell them the listener name.


PostgreSQL checkpoint log explained I know what PostgreSQL checkpoint is and when it is happening.

I need some additional information about the logs produced by the log_checkpoints = on parameter, so please explain some points of it to me:

2017-09-09 16:31:37 EEST [6428-6524] LOG:  checkpoint complete: wrote 30057 buffers (22.9%); 0 transaction log file(s) added, 0 removed, 47 recycled; write=148.465 s, sync=34.339 s, total=182.814 s; sync files=159, longest=16.143 s, average=0.215 s

I know that 22.9% of shared buffers are written (I have 1024 MB shared_buffers so that means 234 MB are written out).
I know that 47 WAL files are recycled, i.e., they are not needed anymore for crash recovery, because the real data from them is already on disk.

Question A. But what about write=148.465 s and sync=34.339? What is the difference? What is write and why its time is far more than the fsync() operation?

Question B. What are sync files? Which files: WAL files? Why sync files are 159, but there are only 47 recycled files? What is the relation between these?

Thank you!

The write and sync phase mean rather different things (unfortunately).  In the middle phase of a checkpoint, it writes out all the dirty buffers from shared buffers.  It does this in a throttled way, mainly regulated by checkpoint_completion_target.  The time reported here is the time it spent writing those buffers, as well as the time it spend sleeping in order to regulate the rate at which the buffers get written.  So not all of the "write" time was spent actively writing.  The length of time spent in the write phase, 2.5 minutes, doesn't really mean anything without knowing what your other settings are.

During the sync phase, it syncs all of those files it just wrote to (as well as any data files other processes may have written to).  This stage is unthrottled, it syncs them as fast as it can.  That this took 34 seconds, with one file in there taking 16.143 seconds, suggests that your IO hardware is not up to the workload you are throwing at it.

The 159 synced files refers to the number of data files that had been dirtied since the end of the last checkpoint, and so needed to be synced before the end of this one.  The 47 refers to WAL files, not data files.  WAL files contain references to changes made to data files.  It is quite easy for 47 WAL files to contain references for 159 data files (or for that matter, 15,900 data files).  But it is not quite that simple, because the WAL files being recycled are the ones from two checkpoints ago, so the relation between 47 and 159 is an analogy, not a concrete count.


SQL Server REVOKE requires CASCADE I have the following graph:

B granted select on D and D granted select on E and E granted select on G.
B has grant select with grant option and has granted select D with grant option.
D has grant select with grant option and has granted select E with grant option.
E has grant select with grant option and has granted select G with grant option.

When I try to revoke the GRANT SELECT on D, I get an error that requires the use of CASCADE.

Code:

EXECUTE AS USER = 'userB';
GO
REVOKE GRANT OPTION FOR SELECT ON EMP FROM userD;
GO
REVERT;

Error:

  To revoke or deny grantable privileges, specify the CASCADE option.

What I need is to remove the GRANT SELECT without removing the GRANT SELECT from E and G.

When you grant permissions to a user using the WITH GRANT OPTION clause, then subsequently revoke that permission, SQL Server will prevent the revoke unless you explicitly add the CASCADE option to the REVOKE ... statement.
This prevents a security risk whereby you grant WITH GRANT OPTION to a user, who subsequently grants rights to others unbeknownst to you.  You then revoke the rights to that user, but the damage has already been done.  This error message is thrown when you attempt to revoke permissions that have been assigned with the grant option, unless you use the CASCADE option:

Msg 4611, Level 16, State 1, Line 44
To revoke or deny grantable privileges, specify the CASCADE option.

In order to revoke permissions that have been granted using the WITH GRANT OPTION clause, you must revoke the permission using the CASCADE option. This also removes the revoked rights from all users that have been granted the rights by the user with the GRANT OPTION.  If you want those other users to retain the rights granted by the user with the GRANT OPTION, you must then manually assign those rights explicitly to those other users.
This code below is a simple repro showing the symptoms, and how to deal with it.
First, create an object we can assign rights to:
IF OBJECT_ID('dbo.TestSelect') IS NULL
CREATE TABLE dbo.TestSelect
(
    t INT
);

Create three users:
IF NOT EXISTS (
    SELECT 1
    FROM sys.database_principals dp
    WHERE dp.name = 'u1')
CREATE USER u1 WITHOUT LOGIN;

IF NOT EXISTS (
    SELECT 1
    FROM sys.database_principals dp
    WHERE dp.name = 'u2')
CREATE USER u2 WITHOUT LOGIN;

IF NOT EXISTS (
    SELECT 1
    FROM sys.database_principals dp
    WHERE dp.name = 'u3')
CREATE USER u3 WITHOUT LOGIN;

Grant SELECT WITH GRANT OPTION to user u1:
GRANT SELECT ON dbo.TestSelect TO u1 WITH GRANT OPTION;

Grant SELECT WITH GRANT OPTION to user u2 as user 'u1' :
EXECUTE AS USER = 'u1';

GRANT SELECT ON dbo.TestSelect TO u2 WITH GRANT OPTION;

REVERT

Grant SELECT WITH GRANT OPTION to user u3 as user 'u2' :
EXECUTE AS USER = 'u2';

GRANT SELECT ON dbo.TestSelect TO u3 WITH GRANT OPTION;

REVERT

Ensure we are running as the initial user (in my case dbo)
SELECT USER_NAME();

Attempt to remove the SELECT rights from user u1 without using the CASCADE option, which will fail:
REVOKE SELECT ON dbo.TestSelect FROM u1;
--Msg 4611, Level 16, State 1, Line 44
--To revoke or deny grantable privileges, specify the CASCADE option.

Add the CASCADE option, which works:
REVOKE SELECT ON dbo.TestSelect FROM u1 CASCADE;

See if the other users have access to the test table (they don't since we've revoked access using the CASCADE option):
EXECUTE AS USER = 'u2';

SELECT *
FROM dbo.TestSelect;
--Msg 229, Level 14, State 5, Line 51
--The SELECT permission was denied on the object 'TestSelect', database 'Test', schema 'dbo'.

Revert back to the 'dbo' user, so we can grant SELECT on the test object to the remaining users:
REVERT -- back to 'dbo'

GRANT SELECT ON dbo.TestSelect TO U2;
GRANT SELECT ON dbo.TestSelect TO U3;

Confirm the other users have access to the test object:
EXECUTE AS USER = 'u2';

SELECT *
FROM dbo.TestSelect;

REVERT -- back to 'dbo'

EXECUTE AS USER = 'u3';

SELECT *
FROM dbo.TestSelect;

REVERT -- back to 'dbo'

Clean up the test object and users:
DROP TABLE dbo.TestSelect
DROP USER u3;
DROP USER u2;
DROP USER u1;


Cannot start MySQL Cluster Management Service Could not determine which nodeid to use for this node. Specify it with --ndb-nodeid= on command line.

This is what EventViewer is saying

Start with

/usr/sbin/ndb_mgmd --ndb-nodeid=123

or set it based on hostname in config.ini

[ndb_mgmd]
hostname=192.168.1.123
NodeId=123

You could try http://www.severalnines.com/cluster-configurator/