Adding extra total column that sums all column values on each row I have the below query

SELECT 'Total_Invoices', SUM(T0.[DocTotal]) - (SUM(T0.[VatSum])-SUM(T0.[WTSum])) as "Amnt(Exl)", SUM(T0.[VatSum]) as "VAT", SUM(T0.[DocTotal]) as "Amnt(Incl)" FROM OINV T0 WHERE T0.[Series] <>'80'  AND
DateDiff(MM,T0.DocDate,'2017-02-02')=0
UNION ALL
SELECT 'Total_DownPayment', SUM(T0.[DocTotal]) - (SUM(T0.[VatSum])-SUM(T0.[WTSum])) as "Amnt(Exl)", SUM(T0.[VatSum]) as "VAT", SUM(T0.[DocTotal]) as "Amnt(Incl)" FROM ODPI T0 WHERE
DateDiff(MM,T0.DocDate,'2017-02-02')=0
UNION ALL
SELECT 'Total_CreditNotes', (SUM(T0.[VatSum])-SUM(T0.[WTSum]))-SUM(T0.[DocTotal]) as "Amnt(Exl)", -SUM(T0.[VatSum]) as "VAT", -SUM(T0.[DocTotal]) as "Amnt(Incl)" FROM ORIN T0 WHERE T0.[Series]='5' AND
DateDiff(MM,T0.DocDate,'2017-02-02')=0

Output is:

I want to create an extra column that adds a fourth column with row totals for the three rows. I have tried the below to no avail:

;WITH CTE AS
(
SELECT * FROM
(
    SELECT  SUM(T0.[DocTotal]) - (SUM(T0.[VatSum])-SUM(T0.[WTSum])) as "Amnt(Exl)", SUM(T0.[VatSum]) as "VAT", SUM(T0.[DocTotal]) as "Amnt(Incl)" FROM OINV T0 WHERE T0.[Series] <>'80'  AND
    DateDiff(MM,T0.DocDate,'2017-02-02')=0
)AS Qry1
    UNION ALL
    SELECT * FROM
(
    SELECT SUM(T0.[DocTotal]) - (SUM(T0.[VatSum])-SUM(T0.[WTSum])) as "Amnt(Exl)", SUM(T0.[VatSum]) as "VAT", SUM(T0.[DocTotal]) as "Amnt(Incl)" FROM ODPI T0 WHERE
    DateDiff(MM,T0.DocDate,'2017-02-02')=0
)AS Qry2
    UNION ALL
    SELECT * FROM
(
    SELECT (SUM(T0.[VatSum])-SUM(T0.[WTSum]))-SUM(T0.[DocTotal]) as "Amnt(Exl)", -SUM(T0.[VatSum]) as "VAT", -SUM(T0.[DocTotal]) as "Amnt(Incl)" FROM ORIN T0 WHERE T0.[Series]='5' AND
    DateDiff(MM,T0.DocDate,'2017-02-02')=0
)AS Qry3
)
SELECT *, Qry1 + Qry2 + Qry3 AS TotalSum
FROM CTE

How do I achieve this 4th column total?

I'm not sure you need the CTE you're attempting.  Seems like you're doing all the work needed in your first query and can use THAT as the CTE.

Here's a mockup you may be able to just run:

I named your first column "Category" so that you'd have something to use in the final SELECT statement.  Also surrounded the values to be added in individual ISNULL([Value],0) so that any NULL values wouldn't cause trouble.

Hope this helps a bit.

WITH CTE_Data
AS
(
SELECT 'Total_Invoices' AS Category, SUM(T0.[DocTotal]) - (SUM(T0.[VatSum])-SUM(T0.[WTSum])) as "Amnt(Exl)", SUM(T0.[VatSum]) as "VAT", SUM(T0.[DocTotal]) as "Amnt(Incl)" FROM OINV T0 WHERE T0.[Series] <>'80'  AND
DateDiff(MM,T0.DocDate,'2017-02-02')=0
UNION ALL
SELECT 'Total_DownPayment', SUM(T0.[DocTotal]) - (SUM(T0.[VatSum])-SUM(T0.[WTSum])) as "Amnt(Exl)", SUM(T0.[VatSum]) as "VAT", SUM(T0.[DocTotal]) as "Amnt(Incl)" FROM ODPI T0 WHERE
DateDiff(MM,T0.DocDate,'2017-02-02')=0
UNION ALL
SELECT 'Total_CreditNotes', (SUM(T0.[VatSum])-SUM(T0.[WTSum]))-SUM(T0.[DocTotal]) as "Amnt(Exl)", -SUM(T0.[VatSum]) as "VAT", -SUM(T0.[DocTotal]) as "Amnt(Incl)" FROM ORIN T0 WHERE T0.[Series]='5' AND
DateDiff(MM,T0.DocDate,'2017-02-02')=0
)

SELECT Category,
       [Amnt(Exl)],
       [VAT],
       [Amnt(Incl)],
       ISNULL([Amnt(Exl)],0) + ISNULL([VAT],0) + ISNULL([Amnt(Incl)],0) AS SumRequested
FROM CTE_Data;


Why can the target of the OUTPUT INTO clause not be a view? Here is my statement:

DELETE FROM table1
OUTPUT deleted.col1, deleted.col2
INTO view1;

It gives me this error:

The target 'view1' of the OUTPUT INTO clause cannot be a view or common table expression.

The view is simple and updatable. Why can I not insert into an updatable view using the OUTPUT clause?

From MSDN - Output clause:

"The OUTPUT clause is not supported in the following statements:

DML statements that reference local partitioned views, distributed partitioned views, or remote tables.
INSERT statements that contain an EXECUTE statement.
Full-text predicates are not allowed in the OUTPUT clause when the database compatibility level is set to 100.
The OUTPUT INTO clause cannot be used to insert into a view, or rowset function.
A user-defined function cannot be created if it contains an OUTPUT INTO clause that has a table as its target.
"


Delete the second record where a user has two records I have a simple database scheme, which due to previously being badly set up has numerous duplicate records.

record_table

|--------------------------------------------------------|
| id  | subid  | catid  | start_date | end_date          |
|--------------------------------------------------------|
| 1   |  1     | 1      | 2015-01-01 | 2015-12-31        |
| 2   |  1     | 1      | 2015-01-01 | 2015-12-31        |
| 3   |  2     | 1      | 2015-01-01 | 2015-12-31        |
| 4   |  2     | 1      | 2015-01-01 | 2015-12-31        |
| 5   |  3     | 1      | 2015-01-01 | 2015-12-31        |
|--------------------------------------------------------|

I want to delete records 2 and 4, because there is already a record for that subid/catid combo that covers the same dates.

I want to be able to do:

DELETE from record_table WHERE catid='1' AND
    start_date<='2015-02-13' AND
    end_date>='2015-02-13' AND
    [the user has two records for the previous conditions]

Solution

Create a TempTable(record_table_keep) with minimum id for every subid,catid combination
Perform a DELETE JOIN between record_table and record_table_keep using LEFT JOIN
Only delete records from record_table whose id is not in record_table_keep

Proposed Query

CREATE TABLE record_table_keep
SELECT id,subid,catid FROM record_table WHERE 0=1;
ALTER TABLE record_table_keep ADD PRIMARY KEY (id);
INSERT INTO record_table_keep (id,subid,catid)
SELECT MIN(id) id,subid,catid FROM record_table GROUP BY subid,catid;
DELETE A.* FROM record_table A
LEFT JOIN record_table_keep B USING (id)
WHERE B.id IS NULL;
DROP TABLE record_table_keep;

Give it a Try !!!


Will logRotate cause any unavailability in MongoDB service? In another question, I've mentioned that how would I rotate logs in MongoDB Replica Set installed in Windows Server. However, it seems there's no built-in way to get it automatically on Windows - only with 3rd party tools.

That being said, I was wondering what would happen if I connect into MongoDB and execute

use admin
db.runCommand( { logRotate : 1 } )

Will it cause any unavailability in MongoDB service? Or will be just a matter to stop log capture for couple seconds and start writing in the new file?

The should not be any break in service. Just remember set systemLog.logRotate or --logRotate to rename or it will open same file again...


Get all rows in table A even if no rows in table B in CROSS JOIN I Have This query

SELECT *
FROM ( ( SELECT SUM(Auditoria.Importe - ISNULL(Auditoria.Pago, 0)) AS Deuda
         FROM Auditoria
           INNER JOIN
              Articulos_Auditoria ON Auditoria.CUD = Articulos_Auditoria.CUD
           LEFT OUTER JOIN
              Cancelaciones ON Auditoria.CUD = Cancelaciones.CUD
         WHERE (Cancelaciones.CUD IS NULL)
           AND ( CAST(Auditoria.Fecha AS date) <= CAST(@Fecha AS date) )
           AND (Auditoria.Terminal = @Term)
           AND (Auditoria.Id_Empresa = @empresa)
           AND (Articulos_Auditoria.Cortado = 0)
        ) AS Deuda
     CROSS JOIN
       ( SELECT DISTINCT Pagos_Auditoria.TipoPago,
                         SUM(Pagos_Auditoria.Cantidad) AS Cant
         FROM Pagos_Auditoria
           INNER JOIN
              Auditoria ON Pagos_Auditoria.CUD = Auditoria.CUD
           INNER JOIN
              Articulos_Auditoria ON Auditoria.CUD = Articulos_Auditoria.CUD
         WHERE ( ( ( CAST([Fecha] as date) ) <= CAST(@Fecha as date) )
             AND (Auditoria.Terminal = @Term)
             AND (Articulos_Auditoria.Cortado = 0)
             AND (Auditoria.Id_Empresa = @empresa)
               )
         GROUP BY Pagos_Auditoria.TipoPago
       ) AS Pagos
     )

Table A is Deudas an table B is Pagos. It works fine when rows in Table B (Pagos) are returned, but I want to see rows in table A and get 0 in fields of table B if no rows are
returned from Table B.

Specifications
 - Table A Deudas have always rows to return
 - Table B Pagos may have rows to return
 - There's no ID or Key between the tables

I tried a sub SELECT ... WHERE NOT EXIST but for the quantity of
evaluated rows is not very efficient.

What is the simplest way to do it?

Edit 1

I tried

SELECT *
FROM ((SELECT        SUM(Auditoria.Importe - ISNULL(Auditoria.Pago, 0)) AS Deuda
FROM            Auditoria INNER JOIN
                            Articulos_Auditoria ON Auditoria.CUD = Articulos_Auditoria.CUD LEFT OUTER JOIN
                            Cancelaciones ON Auditoria.CUD = Cancelaciones.CUD
WHERE        (Cancelaciones.CUD IS NULL) AND (CAST(Auditoria.Fecha AS date) <= CAST(@Fecha AS date)) AND (Auditoria.Terminal = @Term) AND (Auditoria.Id_Empresa = @empresa) AND (Articulos_Auditoria.Cortado = 0)) AS Deuda

LEFT JOIN

(SELECT DISTINCT Pagos_Auditoria.TipoPago, SUM(Pagos_Auditoria.Cantidad) AS Cant
FROM            Pagos_Auditoria INNER JOIN
        Auditoria ON Pagos_Auditoria.CUD = Auditoria.CUD INNER JOIN
        Articulos_Auditoria ON Auditoria.CUD = Articulos_Auditoria.CUD
WHERE (((CAST([Fecha] as date))<=CAST(@Fecha as date)) AND (Auditoria.Terminal = @Term) AND (Articulos_Auditoria.Cortado = 0) AND (Auditoria.Id_Empresa = @empresa))
GROUP BY Pagos_Auditoria.TipoPago) AS Pagos ON TRUE)

but I get

  An expression of non-boolean type specified in a context where a condition is expected, near ')'.

I'm missing something?

You can use an outer apply instead of a cross join.

Using this simplified table structure.

create table T1(ID1 int);
create table T2(ID2 int);

Add three rows to T1

insert into T1 values(1);
insert into T1 values(2);
insert into T1 values(3);

Query with outer apply.

select T1.ID1,
       T2.ID2
from T1
  outer apply (
              select T2.ID2
              from T2
              ) as T2;

Result:

ID1         ID2
----------- -----------
1           NULL
2           NULL
3           NULL

Add one row to T2

insert into T2 values(1);

Result:

ID1         ID2
----------- -----------
1           1
2           1
3           1


Automatic MySQL Sync from master, where Slave was offline for long period of time Does anyone here know if there are any tools, not using mysql's built in replication that will sync up two databases when the slave has been inaccessible from the internet for extended periods of time?

The idea is to set up a virtual machine basis for our web developers, who can power on the VM and have it automatically clone from a slave of the production DB for the purposes of testing/development. they all require their own DB's but they should all replicate the master DB on startup.

Diagram of operation:

Production DB  ->  Production Read Only Slave  ->  Dev Virtual Machine

The only DB I could think of would be Percona XtraDB Cluster (PXC)
It has two methods for copying an entire Cluster Node

SST (State Snapshot Transfer) is the full copy of data from one node to another. It’s used when a new node joins the cluster, it has to transfer data from existing node. There are three methods of SST available in Percona XtraDB Cluster: mysqldump, rsync and xtrabackup (Percona XtraBackup with support of XtraDB Cluster will be released soon, currently you need to use our source code repository). The downside of mysqldump and rsync is that your cluster becomes READ-ONLY while data is being copied from one node to another (SST applies FLUSH TABLES WITH READ LOCK command). Xtrabackup SST does not require READ LOCK for the entire syncing process, only for syncing .frm files (the same as with regular backup).
IST (Incremental State Transfer) : Functionality which instead of whole state snapshot can catch up with te group by receiving the missing writesets, but only if the writeset is still in the donor’s writeset cache.

I addressed this before

Oct 25, 2012 : mysql read slave to fetch entire database on start
Oct 03, 2012 : Does this make my XtraDB Cluster hang?

CAVEAT
This would be the ideal solution if the entire database is InnoDB. If you have read-only MyISAM tables, that would be OK provided you copy the MyISAM to every node manually. SST will do a full copy of MyISAM tables, but the MultiMaster Replication feature of PXC will not replication MyISAM data.
As for PXC, I rigorously tested it in Amazon EC2 and it works like a dream. PXC works as advertised.
UPDATE 2013-02-06 16:40 EDT
drogart made the following comment:

I don't think having one node of your production cluster be a dev/testing environment is a good idea. That would mix dev write traffic back into the prod db. It would also create a situation where devs could impact the prod db cluster by spinning up a new node.

Here has a very valid point. In light of this, be very carful and sparing as to what developers can do this.
Nevertheless, should you ever need a full copy of production in a dev server, offpeak:

STEP01) Setup new PXC server
STEP02) Configure wsrep options
STEP03) Start MySQL (SST should kick off)
STEP04) Shutdown MySQL
STEP05) Comment out wsrep options
STEP06) Restart MySQL (Full of Prod Data with no connectivity to the Real Cluster)

Once this dev server is set up, do the following to update it:

STEP01) Shutdown MySQL
STEP02) Uncomment wsrep options
STEP03) rm -f /var/lib/mysql/galera.cache
STEP04) rm -f /var/lib/mysql/grastate.dat
STEP05) Start MySQL (SST should kick off)
STEP06) Comment out wsrep options
STEP07) Restart MySQL (Full of Prod Data with no connectivity to the Real Cluster)

CAVEAT : Do this copy of Prod to Dev during offpeak hours !!!


When is Service Master Key Exactly generated? As it is created automatically, on Microsoft official source it says during setup.

but in the 70-764 official exam prep book page 18, also there is another non official blog which say

  Any time SQL Server has to protect one of those entities(DMK or Linked Server Login), it
  automatically creates a new service master key, if none already
  exists.

So I see a contradiction here. If it is created during a setup then it should not wait for some objects to be created, and if it is created after the setup, then it is not right to mention about setup generation. Should we correct the information in the blog?

... but there is another non official blog which says ...

I'd go with the official unless for some reason it's been clearly proven otherwise.

  Should we correct the information in the blog?

It'd be nice, but I'm not sure what the chances would be of that happening. Worth a shot.

  If it is created during a setup then it should not wait for some objects to be created ...

You can look in sys.symmetric_keys and see the service master key (in master) which has a create date and modified date. If you do a fresh install of SQL Server, these will be the same and should be roughly around the time of the first startup as part of the install which would make the official documentation correct.


What are these generic looking activities from sp_who2? I am trying to determine whether a sql server instance is actually in use, or just sitting there. It has no user databases, just the normal system databases and distribution. I'm fairly sure there is nothing happening, but it is in our production environment, so I can't just switch it off without being sure.

I have set up an agent job to run sp_who2 and log to a table every minute to see if I can catch any activity. I have the following rows, but nothing else (except stuff with my name on it!). They all look fairly generic - like it's the agent doing it's thing - but I'd just like to check first.

Are these jobs likely to be anything more than generic jobs doing nothing except checking to see if there's something to do?

DBName  Command ProgramName
msdb    AWAITING COMMAND    SQLAgent - Email Logger
master  SELECT INTO         SQLAgent - TSQL JobStep (Job 0x988FC626DDAAEE4D8F49FC83540F48A5 : Step 1)
msdb    AWAITING COMMAND    SQLAgent - Alert Engine
msdb    AWAITING COMMAND    DatabaseMail - SQLAGENT - Id<19792>
msdb    AWAITING COMMAND    SQLAgent - Generic Refresher
msdb    AWAITING COMMAND    SQLAgent - Job invocation engine
msdb    SELECT              SQLAgent - Alert Engine
msdb    AWAITING COMMAND    SQL Management
msdb    AWAITING COMMAND    SQLAgent - Job Manager
msdb    UPDATE              SQLAgent - Schedule Saver

All of that appears to be the result of normal Sql Agent activity.

Assuming you don't have any 'real (non-system)' Sql Agent job running (meaning you don't really need the Sql Agent running), my suggestion would be to create a Windows Task scheduler process using SQLCMD to run your sp_who2 process on your defined interval and then STOP the Sql Agent.  What gets logged into your table then?


SQL Server 2008 Indexed View I am attempting to add schemabinding to a view in SQL Server 2008, but I am getting an error of

  Syntax 'Openrowset/Openquery/Opendatasource' is not allowed in schema-bound objects.

I am using Openquery because I am querying a linked server and this is the way I must query it.  Is there a workaround for this?

This was the syntax I tried with my view

Create View dbo.Test
With Schemabinding
As

  Select * FROM OPENQUERY(191.183.18.101, 'Select * from ls1prod.hrinfo')

Go

The documentation for Indexed Views is pretty clear about what's allowed, and what's not allowed.

Specifically:

  ROWSET functions (OPENDATASOURCE, OPENQUERY, OPENROWSET, AND OPENXML)

There's no 'workaround' because it's not allowed.


SQL Full Text Search - Result Order changing I am using Full Search text to implement search functionality. Basically I will have to do search on all the columns with the search string. Search string will have combination of values from different columns. However I will not know what all columns will I have to search. For example, using the following query in full text SQL search to find customer based on Last Name, Address and Zip

DECLARE @SearchString VARCHAR(200)
SET @SearchString = 'Adam OR West OR 60608'

SELECT Id, First_Name, Last_Name, Address,City,State,Zip, Rank
FROM Patient_Ex PAT
    INNER JOIN CONTAINSTABLE(Patient_Ex,*, @SearchString) CT
        ON PAT.Id = CT.[Key]

Results of the above query

Id  FirstName   LastName    Address City    State   Zip RankID
25  Adam Avenue Adam     North Albay Avenue Chicago IL  60619   272
26  Adam Avenue West    South Escaaba Avenue    Chicago IL  60620   272
27  Adam Avenue Adam     eclaire Avenue Chicago IL  60634   272
28  Adam Avenue West    Leamington Avenue   Chicago IL  60657   272
29  Adam Avenue Adam    North England Avenue    Chicago IL  60660   272
30  Adam Avenue West    West 59th Street    Chicago IL  60608   272
31  Adam Avenue Adam    West 21st Place Chicago IL  60608   272
32  Adam Avenue West    South Albay Avenue  Chicago IL  60655   272

However, I am expecting the result to be something like this below. If the same row has all the three search criteria matching I would like that to be displayed at the top. In this scenario, a record having Last name as Adam, Address containing West and Zip 60608 should be displayed as top records

Id  FirstName   LastName    Address City    State   Zip RankID
30  Adam Avenue Adam    West 59th Street    Chicago IL  60608   272
31  Adam Avenue West    West 21st Place Chicago IL  60608   272
27  Adam Avenue Adam    eclaire Avenue  Chicago IL  60634   272
28  Adam Avenue West    Leamington Avenue   Chicago IL  60657   272
29  Adam Avenue Adam    North England Avenue    Chicago IL  60660   272
25  Adam Avenue West    North Albany Avenue Chicago IL  60619   272
26  Adam Avenue Adam    South Escanaba Avenue   Chicago IL  60620   272
32  Adam Avenue West    South California Avenue Chicago IL  60655   272

How do I modify the query to get the result similar to the above one.
I searched the forum and found a similar question, however there is no answer for it. Any help to resolve the issue will be of great help.

Thanks in advance,

Santha

Try This:

DECLARE @SearchString VARCHAR(200)
SET @SearchString = 'Adam OR West OR 60608'

SELECT Id, First_Name, Last_Name, Address,City,State,Zip, CT.Rank
FROM Patient_Ex PAT
    INNER JOIN CONTAINSTABLE(Patient_Ex,*, @SearchString) CT
        ON PAT.Id = CT.[Key]
ORDER BY CT.RANK DESC

Explanation:
The table produced by CONTAINSTABLE includes a column named RANK. The RANK column is a value (from 0 through 1000) for each row indicating how well a row matched the selection criteria.

Update:

Try adding weights to your keyword and see if it changes things:

DECLARE @SearchString VARCHAR(200)
SET @SearchString ='ISABOUT (Adam WEIGHT (.8), West WEIGHT (.4), 60608 WEIGHT (.2) )'
SELECT Id, First_Name, Last_Name, Address,City,State,Zip, CT .RANK
FROM Patient_Ex PAT
INNER JOIN CONTAINSTABLE(Patient_Ex,*,@SearchString,LANGUAGE N'English' ) AS CT
ON PAT.Id = CT .[KEY]
ORDER BY CT.RANK DESC;
GO

If the above doesn't works either try rebuilding your Catalog if you have  very few rows in database as people have reported wrong rank outputs with databases having less no of rows while the ranking working fine with database having large no. of rows,another workaround is to create dummy table in your database and index it in your catalog.
Please read this if you want to learn more about how ranks are calculated.
Source : 1,2


It is mandatory to create a route when creating an event notification? I'm starting with service broker. Already managed to setup an event notification for blocked processes based on this answer and it's working flawlessly. Then I wanted to setup another one to notify for deadlocks and I see a CREATE ROUTE statement:

-- create the route for the service
CREATE ROUTE DeadLockNotificationsRoute
    WITH SERVICE_NAME = 'DeadLockNotificationsService',
    ADDRESS = 'LOCAL';
GO

But on previous one I did not ran this step, it was not included and it works. Looking on several places here, here, here, here and some other places I see the create route step is not always included. For dynamic routing seems mandatory of course. Reading on MSDN-Create route is not clear for me if it is mandatory or not and when it should be included.

If you are using WMI event alerts for server events then service  broker is not required as it uses Service Broker SQL/Notifications/ProcessWMIEventProviderNotification/v1.0 in msdb and Service Broker is enabled by default for msdb for 2005 and up.

Read up on - understanding WMI provider for server events

To Answer your questions :

  But I see lot of recommendations to use service broker too. What's the difference between them?

Its your choice to use either Service Broker or WMI Providers for server events. I find WMI Providers for server events more easy to implement.

Basically, using Event notifications allows you to capture information using SQL Server Service Broker by creating a service and queue for the events. For e.g. if you set up Event notification to capture DEADLOCK GRAPH then 3 service broker objects are created :

A QUEUE to hold up DEADLOCK_GRAPH event messages
A SERVICE to route the messages to the queue and
An Event NOTIFICATION to capture the deadlock graph and package it in a message that is sent to the Service.

For WMI Providers for server events - Any event that can be captured through event notifications has a corresponding WMI Event Object, and any WMI management application can subscribe to these event objects. SQL Server Agent was updated to manage WMI events, through the use of WMI Query Language (WQL), a query language similar to T-SQL that is used with WMI and Agent Alerts for WMI events.

  By using WMI I can avoid the hassle of creating service/queues?

Yes but it is in no way a replacement of Service Broker. Event Notification uses Service Broker. WMI Provider for server events uses WMI event objects.

  WMI is broader and allows to track more event types? Or it is something else?

WMI can only handle these DDL Event Groups. Service Broker is much broader.

For monitoring and alerting on Deadlocks, you can use my script.

Refer to : Jonathan's excellent article on Handling Deadlocks in SQL Server


How to simulate a table join on itself? Let's assume this is my current data:

product_number  date         value
100             2010-01-01   1
100             2010-02-01   1
100             2010-03-01   1
200             2010-01-01   1
200             2010-03-01   1

How can I join the table on "itself", to fill the dates where other products have missing dates (compared to all the results, that change all the time)?

As you can see, product #200 doesn't have a 2010-02-01 date and I need to get a NULL value there.

Expected result:

product_number  date         value
100             2010-01-01   1
100             2010-02-01   1
100             2010-03-01   1
200             2010-01-01   1
200             2010-02-01   NULL # add NULL values where it doesn't have a date
200             2010-03-01   1

Basically: get a list of all distinct dates, find products where that date doesn't exist and simulate an entry with a NULL or empty value to fill the gap

You'll need to first create a list of every product_number and date combination. You can do this using a CROSS JOIN of your table:

select distinct p.product_number, d.date
from yourtable p
cross join yourtable d;

See SQL Fiddle with Demo. This will create a list of data similar to:

| PRODUCT_NUMBER |                            DATE |
|----------------|---------------------------------|
|            100 |  January, 01 2010 00:00:00+0000 |
|            200 |  January, 01 2010 00:00:00+0000 |
|            100 | February, 01 2010 00:00:00+0000 |

You will then use the above query and LEFT JOIN to your table to return the final result:

select
  pd.product_number,
  pd.date,
  t.value
from
(
  -- list of every product/date
  select distinct p.product_number, d.date
  from yourtable p
  cross join yourtable d
) pd
left join yourtable t
  on pd.date = t.date
  and pd.product_number = t.product_number
order by pd.product_number, pd.date;

See SQL Fiddle with Demo.  Giving a final result of:

| PRODUCT_NUMBER |                            DATE |  VALUE |
|----------------|---------------------------------|--------|
|            100 |  January, 01 2010 00:00:00+0000 |      1 |
|            100 | February, 01 2010 00:00:00+0000 |      1 |
|            100 |    March, 01 2010 00:00:00+0000 |      1 |
|            200 |  January, 01 2010 00:00:00+0000 |      1 |
|            200 | February, 01 2010 00:00:00+0000 | (null) |
|            200 |    March, 01 2010 00:00:00+0000 |      1 |

The LEFT JOIN returns all rows from your list of products and dates regardless of whether a matching row exists in the other table.

This could also be written as:

select
  p.product_number,
  d.date,
  t.value
from
(
  -- list of every product
  select distinct product_number
  from yourtable
) p
cross join
(
  -- list of every date
  select distinct date
  from yourtable
) d
  -- then join to the table
left join yourtable t
  on  d.date = t.date
  and p.product_number = t.product_number
order by p.product_number, d.date ;

See SQL Fiddle with Demo. This may have better performance depending on your table size.

Now if you wanted to return a list of all dates, regardless of whether or not they appear in the table, then I would suggest creating a table of dates.  This table would be used in a similar manner to create a list of all dates/products which you would then join.

The table would be similar to:

CREATE TABLE dates
    (`date` datetime)
;

INSERT INTO dates
    (`date`)
VALUES
    ('2010-01-01 00:00:00'),
    ('2010-02-01 00:00:00'),
    ('2010-03-01 00:00:00'),
    ('2010-04-01 00:00:00'),
    ('2010-05-01 00:00:00')
;

You'd then use the following query to get the list of dates/products:

select distinct p.product_number, d.date
from yourtable p
cross join dates d

And finally, you would join that back to your table:

select
  pd.product_number,
  pd.date,
  t.value
from
(
  -- list of every product/date
  select distinct p.product_number, d.date
  from yourtable p
  cross join dates d
) pd
left join yourtable t
  on pd.date = t.date
  and pd.product_number = t.product_number
order by pd.product_number, pd.date;

See SQL Fiddle with Demo. Or an alternative:

select
  p.product_number,
  d.date,
  t.value
from
(
  -- list of every product
  select distinct product_number
  from yourtable
) p
cross join
 dates
  d
  -- then join to the table
left join yourtable t
  on  d.date = t.date
  and p.product_number = t.product_number
order by p.product_number, d.date ;

See SQL Fiddle with Demo. Again this may have better performance based on the table size.  Using this type of solution, you'd return all dates even those not in your table:

| PRODUCT_NUMBER |                            DATE |  VALUE |
|----------------|---------------------------------|--------|
|            100 |  January, 01 2010 00:00:00+0000 |      1 |
|            100 | February, 01 2010 00:00:00+0000 |      1 |
|            100 |    March, 01 2010 00:00:00+0000 |      1 |
|            100 |    April, 01 2010 00:00:00+0000 | (null) |
|            100 |      May, 01 2010 00:00:00+0000 | (null) |
|            200 |  January, 01 2010 00:00:00+0000 |      1 |
|            200 | February, 01 2010 00:00:00+0000 | (null) |
|            200 |    March, 01 2010 00:00:00+0000 |      1 |
|            200 |    April, 01 2010 00:00:00+0000 | (null) |
|            200 |      May, 01 2010 00:00:00+0000 | (null) |


In a trigger, how to check if no fields have changed? If we receive an update statement that does not check if the value has changed in the where clause, what are the different ways to ignore that update inside a trigger?

I know we can do a comparison of each individual field (handling the ISNULL side as well), but where it's a table that has 50+ fields, is there a faster/easier way to do it?

Note: This could be used to reduce IO/noise in audit records etc...

EXAMPLE

For a table of:

CREATE TABLE myTest
(ID int NOT NULL IDENTITY(1,1),
 Field1 varchar(10) NULL,
 Field2 varchar(20) NULL)

with an after updater trigger containing:

INSERT INTO myTestAudit (ID, Field1, Field2, DateTimeUpdate)
SELECT ID, Field1, Field2, getDate()
FROM inserted

with initial values:

INSERT INTO myTest (Field1, Field2)
SELECT 'a', 'b' UNION ALL
SELECT 'a', 'c'

Now run an update:

UPDATE myTest set Field2 = 'b' WHERE Field1 = 'a'

To bring back rows where at least one value has changed you can use

SELECT /*TODO: Column List*/
FROM   INSERTED I
       JOIN DELETED D
         ON I.ID = D.ID
            AND EXISTS (SELECT I.*
                        EXCEPT
                        SELECT D.*)


Dynamic SQL With Double Quoting I am attempting to run a dynamic SQL statement and it works for any storename that does not have an apostrophe in it.  However, when the storename has an apostrophe in it the syntax does not properly, however I am stuck using dynamic SQL in this instance.

How should I ammend this syntax in order for storename to include an apastrophe?

Here is DDL

Declare @TestHyphens Table(StoreName varchar(500),topItemSale varchar(500))

Insert Into @TestHyphens (StoreName, topItemSale) Values
('Bob''s Burger''s','Pen'), ('Jacks Coffee Shop','Pencil')
,('Larry''s Hair Shack','Rainbow'), ('Manny''s Sandwich Shop','Sunglasses')

Declare @sql nvarchar(4000), @storename varchar(500)

Declare db_cursor CURSOR FOR
Select StoreName
FROM @TestHyphens

OPEN db_cursor
FETCH NEXT FROM db_cursor INTO @storename

While @@FETCH_STATUS = 0
BEGIN

        Set @storename = REPLACE(@storename, '''', '''''''''');

        SET @storename = CHAR(39)+CHAR(39)+@storename+CHAR(39)+CHAR(39);

        SET @SQL = N'Select * from @TestHyphens where storename IN ('+@storename+ N')'

        Print @storename
        Print @sql

    FETCH NEXT FROM db_cursor INTO @storename

END

Close db_cursor
DEALLOCATE db_cursor

And print statements produce:

''Bob''''s Burger''''s''
Select * from @TestHyphens where storename IN (''Bob''''s Burger''''s'')
''Jacks Coffee Shop''
Select * from @TestHyphens where storename IN (''Jacks Coffee Shop'')
''Larry''''s Hair Shack''
Select * from @TestHyphens where storename IN (''Larry''''s Hair Shack'')
''Manny''''s Sandwich Shop''
Select * from @TestHyphens where storename IN (''Manny''''s Sandwich Shop'')

And as you can see these are incorrectly hyphenated -

''Manny''''s Sandwich Shop''
''Larry''''s Hair Shack''
''Bob''''s Burger''''s''

You can temporarily change QUOTED_IDENTIFIER.

DECLARE @TestHyphens TABLE(StoreName varchar(500),topItemSale varchar(500))

INSERT INTO @TestHyphens (StoreName, topItemSale) VALUES
('Bob''s Burger''s','Pen'),
('Jacks Coffee Shop','Pencil'),
('Larry''s Hair Shack','Rainbow'),
('Manny''s Sandwich Shop','Sunglasses');

SET QUOTED_IDENTIFIER OFF

SELECT StoreName, TopItemSale
FROM   @TestHyphens
WHERE  StoreName = "Bob's Burger's"

SET QUOTED_IDENTIFIER ON

| StoreName      | topItemSale |
|----------------|-------------|
| Bob's Burger's | Pen         |

Rextester here

Using within the cursor

DECLARE @sql nvarchar(MAX), @storename varchar(500)

DECLARE db_cursor CURSOR FOR
SELECT StoreName
FROM #TestHyphens

OPEN db_cursor
FETCH NEXT FROM db_cursor INTO @storename

WHILE @@FETCH_STATUS = 0
BEGIN

    SET @SQL = N'SET QUOTED_IDENTIFIER OFF '
               + N'Select * from #TestHyphens where StoreName = "' + @storename + N'"'
               + N'SET QUOTED_IDENTIFIER ON';

    EXECUTE SP_EXECUTESQL @SQL;

    FETCH NEXT FROM db_cursor INTO @storename
END

CLOSE db_cursor
DEALLOCATE db_cursor

This is the result:

| StoreName             | topItemSale |
|-----------------------|-------------|
| Bob's Burger's        | Pen         |
| Jacks Coffee Shop     | Pencil      |
| Larry's Hair Shack    | Rainbow     |
| Manny's Sandwich Shop | Sunglasses  |

Rextester here


Maintaining referential integrity in a booking system I am developing a second version of a corporate training booking system on Microsoft SQL Server 2005.

I have 3 tables (simplified for this question).

Table 1- CourseSize table- this determines the maximum number of participants able to book into the session.
Table 2- Session- the course to be provided, the date/time and a reference to CourseSize record.
Table 3- Booking table- the person who made the booking with reference to the relevant Session record.

The SQL is as follows-

CREATE TABLE [dbo].[CourseSize]
(
    [ID] [int] IDENTITY(1,1) NOT NULL,
    [CourseSizeMax] [tinyint] NOT NULL,
    CONSTRAINT [PK_CourseSize] PRIMARY KEY CLUSTERED ([ID] ASC)
);

CREATE TABLE [dbo].[Session]
(
    [ID] [int] IDENTITY(1,1) NOT NULL,
    [CourseSizeID] [int] NOT NULL,
    CONSTRAINT [PK_Session] PRIMARY KEY CLUSTERED ([ID] ASC)
);
GO

ALTER TABLE [dbo].[Session] WITH CHECK
ADD CONSTRAINT [FK_Session_CourseSize]
FOREIGN KEY([CourseSizeID])
REFERENCES [dbo].[CourseSize] ([ID]);
GO

ALTER TABLE [dbo].[Session]
CHECK CONSTRAINT [FK_Session_CourseSize];
GO

CREATE TABLE [dbo].[Booking]
(
    [ID] [int] IDENTITY(1,1) NOT NULL,
    [SessionID] [int] NOT NULL,
    CONSTRAINT [PK_Booking] PRIMARY KEY CLUSTERED ([ID] ASC)
);
GO

ALTER TABLE [dbo].[Booking] WITH CHECK
ADD CONSTRAINT [FK_Booking_Session]
FOREIGN KEY([SessionID])
REFERENCES [dbo].[Session] ([ID]);
GO

ALTER TABLE [dbo].[Booking]
CHECK CONSTRAINT [FK_Booking_Session];
GO

Problem- I need to ensure that inserts into the Booking table are stopped if the number of bookings against a session has reached the maximum course size.  In the past I have used something like this:

INSERT INTO Booking
SELECT 1 AS SessionID
WHERE
    (
        SELECT COUNT(*)
        FROM Booking
        WHERE SessionID = 1
    ) <=
    (
        SELECT CourseSizeMax
        FROM CourseSize
            INNER JOIN Session ON CourseSize.ID = Session.CourseSizeID
        WHERE Session.ID = 1
    );

This fails when multiple users make bookings at the same time.

I tested the above query by setting the CourseSizeMax to 2, and using WAITFOR time. I scheduled the query to run 3 times; 2 at the same time and the last 1 ms before the other two. They all were able to insert a row, thereby exceeding the session size limit.

I would really like to avoid using a trigger to handle this; my preference is to use constraints.  I have total control of the design of the database so there is no problem with changing the structure if need be.

How do I prevent simultaneous updates from breaking the course size limitation?

If you only had two tables, Session and Booking, you could do this:

Add CourseSizeMax column to your dbo.Session table, and add a UNIQUE constraint on [dbo].[Session]([ID], [CourseSizeMax]) - it is needed later.
Add CourseSizeMax column, and BookingNumber  column to your dbo.Booking table.
Add a FK constraint on dbo.Booking(CourseId, CourseSizeMax) referring to Session
Add a CHECK(BookingNumber BETWEEN 1 AND CourseSizeMax)
Add a UNIQUE constraint on dbo.Booking(CourseId, BookingNumber)

and you are all set, as long as all your constraints are trusted.

I am not sure why would you need the third table CourseSize at all.


Oracle "new or old reference not allowed in table level trigger" I have this table

CREATE table Pharmacists (
PhFname varchar2(10),
PhLname varchar2(10),
Salary number(7),
Nationality varchar2(10),
Shift_Start varchar2(2),
Shift_End varchar2(2),
PhID number(7) primary key,
Super_ID number(7) ,
bdate date,
constraint super_fk foreign key (Super_ID) references Pharmacists (PhID) on delete cascade);

I tried to create this trigger

create or replace trigger updShE
after update of Shift_Start on Pharmacists
begin
update Pharmacists
set Shift_End=to_number(:new.Shift_Start, '99') +8
where PhID = :New.PhID ;
end;

but this error appears

  new or old reference not allowed in table level trigger

You need a row before level trigger for that.

And you don't need an update, just assign the new value:

create or replace trigger updshe
  BEFORE update of Shift_Start on Pharmacists
  FOR EACH ROW
begin
  :new.shift_end := to_number(:new.Shift_Start, '99') + 8;
end;
/


Slow fulltext search for terms with high occurence I have a table that contains data that is extracted from text documents. The data is stored in a column called "CONTENT" for which I have created this index using GIN:

CREATE INDEX "File_contentIndex"
  ON "File"
  USING gin
  (setweight(to_tsvector('english'::regconfig
           , COALESCE("CONTENT", ''::character varying)::text), 'C'::"char"));

I use the following query to perform a full text search on the table:

SELECT "ITEMID",
  ts_rank(setweight(to_tsvector('english', coalesce("CONTENT",'')), 'C') ,
  plainto_tsquery('english', 'searchTerm')) AS "RANK"
FROM "File"
WHERE setweight(to_tsvector('english', coalesce("CONTENT",'')), 'C')
  @@ plainto_tsquery('english', 'searchTerm')
ORDER BY "RANK" DESC
LIMIT 5;

The File table contains 250 000 rows and each "CONTENT" entry consists of one random word and a text string that is the same for all rows.

Now, when I search for a random word (1 hit in the whole table) the query runs very fast (<100 ms). However, when I search for a word that is present in all the rows, the query runs extremely slow (10 minutes or more).

EXPLAIN ANALYZE shows that for the 1-hit search a Bitmap Index Scan followed by a Bitmap Heap Scan is performed. For the slow search a Seq Scan is performed instead, which is what is taking so long.

Granted, it is not realistic to have the same data in all rows. But since I can't control the text documents that's uploaded by the users, nor the searches they perform it is possible that a similar scenario arises (search on terms with very high occurrence in DB). How can I increase the performance of my search query for such a scenario?

Running PostgreSQL 9.3.4

Query plans from EXPLAIN ANALYZE:

Quick search (1 hit in DB)

"Limit  (cost=2802.89..2802.90 rows=5 width=26) (actual time=0.037..0.037 rows=1 loops=1)"
"  ->  Sort  (cost=2802.89..2806.15 rows=1305 width=26) (actual time=0.037..0.037 rows=1 loops=1)"
"        Sort Key: (ts_rank(setweight(to_tsvector('english'::regconfig, (COALESCE("CONTENT", ''::character varying))::text), 'C'::"char"), '''wfecg'''::tsquery))"
"        Sort Method: quicksort  Memory: 25kB"
"        ->  Bitmap Heap Scan on "File"  (cost=38.12..2781.21 rows=1305 width=26) (actual time=0.030..0.031 rows=1 loops=1)"
"              Recheck Cond: (setweight(to_tsvector('english'::regconfig, (COALESCE("CONTENT", ''::character varying))::text), 'C'::"char") @@ '''wfecg'''::tsquery)"
"              ->  Bitmap Index Scan on "File_contentIndex"  (cost=0.00..37.79 rows=1305 width=0) (actual time=0.012..0.012 rows=1 loops=1)"
"                    Index Cond: (setweight(to_tsvector('english'::regconfig, (COALESCE("CONTENT", ''::character varying))::text), 'C'::"char") @@ '''wfecg'''::tsquery)"
"Total runtime: 0.069 ms"

Slow search (250k hits in DB)

"Limit  (cost=14876.82..14876.84 rows=5 width=26) (actual time=519667.404..519667.405 rows=5 loops=1)"
"  ->  Sort  (cost=14876.82..15529.37 rows=261017 width=26) (actual time=519667.402..519667.402 rows=5 loops=1)"
"        Sort Key: (ts_rank(setweight(to_tsvector('english'::regconfig, (COALESCE("CONTENT", ''::character varying))::text), 'C'::"char"), '''cyberspace'''::tsquery))"
"        Sort Method: top-N heapsort  Memory: 25kB"
"        ->  Seq Scan on "File"  (cost=0.00..10541.43 rows=261017 width=26) (actual time=2.097..519465.953 rows=261011 loops=1)"
"              Filter: (setweight(to_tsvector('english'::regconfig, (COALESCE("CONTENT", ''::character varying))::text), 'C'::"char") @@ '''cyberspace'''::tsquery)"
"              Rows Removed by Filter: 6"
"Total runtime: 519667.429 ms"

Questionable use case

  ...each CONTENT entry consists of one random word and a text string that is the same for all rows.

A text string that is the same for all rows is just dead freight. Remove it and concatenate it in a view if you need to show it.

Obviously, you are aware of that:

  Granted, it is not realistic ... But since I can't control the text ...

Upgrade your Postgres version

  Running PostgreSQL 9.3.4

While still on Postgres 9.3, you should at least upgrade to the latest point release (currently 9.3.9). The official recommendation of the project:

  We always recommend that all users run the latest available minor
  release for whatever major version is in use.

Better yet, upgrade to 9.4 which has received major improvements for GIN indexes.

Major problem 1: Cost estimates

The cost of some textsearch functions has been seriously underestimated up to and including version 9.4. That cost is raised by factor 100 in the upcoming version 9.5 like @jjanes describes in his recent answer:

PostgreSQL not using index

Here are the respective thread where this was discussed and the commit message by Tom Lane.

As you can see in the commit message, to_tsvector() is among those functions. You can apply the change immediately (as superuser):

ALTER FUNCTION to_tsvector (regconfig, text) COST 100;

which should make it much more likely that your functional index is used.

Major problem 2: KNN

The core problem is that Postgres has to calculate a rank with ts_rank() for 260k rows (rows=261011) before it can order by and pick the top 5. This is going to be expensive, even after you have fixed other problems as discussed. It's a K-nearest-neighbour (KNN) problem by nature and there are solutions for related cases. But I cannot think of a general solution for your case, since the rank calculation itself depends on user input. I would try to eliminate the bulk of low ranking matches early so that the full calculation only has to be done for few good candidates.

One way I can think of is to combine your fulltext search with trigram similarity search - which offers a working implementation for the KNN problem. This way you can pre-select the "best" matches with LIKE predicate as candidates (in a subquery with LIMIT 50 for example) and then pick the 5 top-ranking rows according to your rank-calculation in the main query.

Or apply both predicates in the same query and pick the closest matches according to trigram similarity (which would produce different results) like in this related answer:

PostgreSQL FTS and Trigram-similarity Query Optimization

I did some more research and you are not the first to run into this problem. Related posts on pgsql-general:

thread 1 (2011)
thread 2 (2012)
thread 3 (2014)

Work is ongoing to eventually implement a tsvector <-> tsquery operator.

Oleg Bartunov and Alexander Korotkov even presented a working prototype (using >< as operator instead of <-> back then) but it's very complex to integrate in Postgres, the whole infrastructure for GIN indexes has to be reworked (most of which is done by now).

Major problem 3: weights and index

And I identified one more factor adding to the slowness of the query. Per documentation:

  GIN indexes are not lossy for standard queries, but their performance
  depends logarithmically on the number of unique words. (However, GIN
  indexes store only the words (lexemes) of tsvector values, and not
  their weight labels. Thus a table row recheck is needed when using a
  query that involves weights.)

Bold emphasis mine. As soon as weight are involved, each row has to be fetched from the heap (not just a cheap visibility check) and long values have to be de-toasted, which adds to the cost. But there seems to be a solution for that:

Index definition

Looking at your index again, it doesn't seem to make sense to begin with. You assign a weight to a single column, which is meaningless as long as you don't concatenate other columns with a different weight.

COALESCE() also makes no sense as long as you don't actually concatenate more columns.

Simplify your index:

CREATE INDEX "File_contentIndex" ON "File" USING gin
(to_tsvector('english', "CONTENT");

And your query:

SELECT "ITEMID", ts_rank(to_tsvector('english', "CONTENT")
                       , plainto_tsquery('english', 'searchTerm')) AS rank
FROM   "File"
WHERE  to_tsvector('english', "CONTENT")
       @@ plainto_tsquery('english', 'searchTerm')
ORDER  BY rank DESC
LIMIT  5;

Still expensive for a search term that matches every row, but probably much less.

Asides

All of these issues combined, the insane cost of 520 seconds for your second query is beginning to make sense. But there still may be more problems. Did you configure your server?
All the usual advice for performance optimization applies.

It makes your life easier if you don't work with double-quotes CaMeL-case identifiers:

Are PostgreSQL column names case-sensitive?


JSON to BSON in mongoDB I have a little bit confusion how the json data internally organized (physical storage structure for dynamic schema objects)

db.some_collection.insert({x:1,y:4,z:34})
db.some_collection.insert({p:1,q:5,z:"nothing"})

let us assuming if we created the indexes on 'x' and 'q', then how the json data internally organized/stored.

Looking for vital suggestions

Your question is very broad as there are multiple storage engines in MongoDB 3.0+ (and a storage engine API). Physical layout for data and indexes will depend on the storage engine implementation, but it sounds like your general question is how documents with varying schemas are stored and indexed.

Conceptually each document is stored in a contiguous binary record allocation which may include padding depending on the storage engine. The server-side document representation is in BSON (http://bsonspec.org) which is a binary JSON-like format with a richer type system than JSON. BSON records can vary in size; indexes (typically B-tree) point to the current location of an indexed record.

MongoDB (as at 3.2) does not maintain any schema information outside of the actual BSON data. Each BSON document includes all of the field names as well as the values. This dynamic schema (or "schema on read") approach allows documents to be interpreted as they are read from storage without consulting or updating a central schema catalog.

For more information, see:

MMAP Storage Engine Internals and Understanding MongoDB Storage (MMAP is the default storage engine through MongoDB 3.0)
A Technical Introduction to WiredTiger (optional storage engine in MongoDB 3.0, default in 3.2)


If exists then update else insert I am trying to create a STORED PROCEDURE that will be used to UPDATE a table called machine. This table has three columns (machine_id, machine_name and reg_id).

In aforementioned table,reg_id (INT) is a column whose values can be changed for a machine_id.

I would like to define a QUERY/PROCEDURE to check if a reg_id already exists in that table. If it does, then UPDATE that row, otherwise INSERT a new row.

Can someone please help me to write that QUERY/PROCEDURE?

Hope This helps, DUPLICATE KEY UPDATE

create table machine(
  machine_id int not null primary key,
  machine_name varchar(50),
  reg_id int
);

insert into machine (machine_id, machine_name, reg_id)
values(1, 'my_machine', 1);

INSERT INTO machine (reg_id, machine_id, machine_name) VALUES (1, 1, 'test_machine')
  ON DUPLICATE KEY UPDATE machine_name=VALUES(machine_name);

Work on SQL Fiddle


Why can't sa be impersonated in a procedure? In order to solve a request regarding temporarily giving elevated privileges to a user I noticed that it was not possible to use EXECUTE AS LOGIN = 'sa'; inside of a procedure, but I can't figure out the reason since the error message does not fit the situation.
Here's how to test it (I run this part as a sysadmin):
--Create the procedure with the impersonation
USE master;
GO

CREATE PROCEDURE impersonate_procedure
WITH EXECUTE AS OWNER
AS
BEGIN

    EXECUTE AS LOGIN = 'sa';
    CREATE LOGIN appname_owner WITH PASSWORD = 'randomlygeneratedpassword';

END;
GO

--The login who should receive the privileges temporarily
CREATE LOGIN ronaldo WITH PASSWORD = N'123StrongP@SSw0rd';
GO

CREATE USER ronaldo FOR LOGIN ronaldo WITH DEFAULT_SCHEMA = dbo;
GO

GRANT EXECUTE ON dbo.impersonate_procedure TO ronaldo;
GO

Now I try to execute the procedure while logged in as ronaldo:
--Fails
EXEC dbo.impersonate_procedure;
GO

I get the following error:

Cannot execute as the server principal because the principal "sa" does
not exist, this type of principal cannot be impersonated, or you do
not have permission.

When I make this (sysadmin again):
GRANT IMPERSONATE ON LOGIN::sa to ronaldo;
GO

The login ronaldo can execute the content of the procedure body successfully:
--Works
EXECUTE AS LOGIN = 'sa';
CREATE LOGIN appname_owner WITH PASSWORD = 'randomlygeneratedpassword';

But if he tries to run the procedure, the error remains and it is bothering me because:

the principal sa exists;
it's a type of principal that can be inpersonated;
the login ronaldo was granted the privilege to impersonate sa (but shouldn't be
needed since the procedure was created by a sysadmin with the clause
WITH EXECUTE AS OWNER.

Why do I get that error?

Please, remove the WITH EXECUTE AS OWNER and it should work
--Create the procedure with the impersonation
USE master;
GO

CREATE PROCEDURE impersonate_procedure
--WITH EXECUTE AS OWNER
AS
BEGIN

EXECUTE AS LOGIN = 'sa';
CREATE LOGIN appname_owner WITH PASSWORD = 'randomlygeneratedpassword';

END;
GO


Alter default privileges for role I have database called Blackacre and two roles who have access to it: johnrole and janerole. janerole has been granted ALL privileges and johnrole has only been granted the SELECT privilege.

Problem is when a user from janerole creates a new table, then a user from johnrole can't SELECT from the new table.

ERROR:  permission denied for relation mopery

I tried to use ALTER DEFAULT in different ways to automate that:

ALTER DEFAULT PRIVILEGES FOR ROLE johnrole IN SCHEMA public
   GRANT SELECT ON TABLES TO johnrole;
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO johnrole;
ALTER DEFAULT PRIVILEGES FOR ROLE johnrole GRANT SELECT ON TABLES TO PUBLIC;
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO GROUP johnrole;

None of them work and I still end up with the same error.

When I use ALTER DEFAULT to automate future tables privileges for user e.g john then everything works, he can SELECT from new tables created in the database without any error.

ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO john;

This is how I added people to roles:

GRANT johnrole TO adam;
GRANT janerole TO eva;

Granted usage on Blackacre database like this:

GRANT USAGE ON SCHEMA public TO johnrole;
GRANT USAGE ON SCHEMA public TO janerole;

Gave some privileges like that:

GRANT ALL ON ALL TABLES IN SCHEMA public TO janerole;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO johnrole;

After that I tried to give automatic privileges with ALTER DEFAULT as above described don't work. Any ideas how to get default privileges working properly?

janerole has been granted ALL privileges and johnrole has only been granted the SELECT privilege.

You got at least one aspect of the command backwards in all your attempts:
Alter default privileges for the role that creates objects - which in turn grants privileges to another role. I.e.:

ALTER DEFAULT PRIVILEGES FOR ROLE janerole IN SCHEMA public
GRANT SELECT ON TABLES TO johnrole;

Do this while logged in as janerole (or a member of the role).

Note that default privileges only apply to the target role, not to members of this role.

And don't forget to GRANT USAGE ON SEQUENCES as well if you have any serial columns. Possibly SELECT and UPDATE, too.

There may be more problems, the question is not completely clear.

Related:

How to manage DEFAULT PRIVILEGES for USERs on a DATABASE vs SCHEMA?
Can't alter default privileges

Note: There is an open bug in pgAdmin, the display can be incorrect (still kicking in the current pgAdmin 1.22).


Will a MERGE between two tables ever be faster than a DROP and SELECT INTO on the second table? Is there a scenario where it's faster for synchronizing the data from TableA to TableB by using a MERGE query as opposed to dropping TableB and doing a SELECT * INTO TableB FROM TableA?

Will a MERGE between two tables ever be faster than a DROP and SELECT INTO on the second table?

I think, in general, I'd say probably not.  But it depends on a lot of factors.

Bulk Loading (minimal logging)

SELECT...INTO will benefit from bulk load optimizations.  The INSERT portion of the MERGE query might benefit from these optimizations, but the UPDATE portion won't.  So the MERGE statement will likely result in more transaction log writes, making it slower.

DROP vs DELETE

SELECT...INTO will also benefit from the fact that DROP TABLE is a metadata operation, and thus will "remove" all the rows very quickly.  The DELETE portion of the MERGE query will be logged normally, resulting in more transaction log writes, making it slower.

Concurrency

SELECT...INTO will benefit from reduced locking / blocking / concurrency issues, because it will have exclusive access to the target table.  The MERGE statement will potentially have to deal with lock escalation, blocking by other processes, etc, unless you provide TABLOCK hints.

Index Maintenance

The MERGE statement also has to deal with index maintenance (you mentioned there is a clustered index and a nonclustered index on the target table in this scenario), whereas the SELECT...INTO does not.  To mitigate this, you could potentially drop the indexes and recreate them after the MERGE has run.

Additional thoughts

All of this somewhat depends on what the breakdown (in terms of # of rows) is between INSERTs, UPDATEs, and DELETEs in the MERGE statement, but in general I would expect the SELECT...INTO case to be quicker unless the MERGE statement has been designed very carefully.

Some related information that might be interesting to you and this situation:

Why "Select * into targettable from sourcetable “ is faster than “insert into targettable select * from sourcetable
The Data Loading Performance Guide

Sidebar: in a comment, you mentioned:

  I'm asking because in my case I have to synchronize about 300 million records, and with my testing of only 5 million records, the SELECT INTO took about 30 seconds, the MERGE took about 4 minutes. :(

To compare "apples to apples" make sure to include the time it takes to create the two indexes you mentioned for the SELECT...INTO case:

  In my specific case, there's actually no indexes on TableB at the time of the SELECT INTO, but a single unique clustered (PK) and a single non-clustered index is created on it on after the data is inserted. If a MERGE query was used instead, then both tables would have unique clustered indexes and non-clustered indexes on them.


Difference between encrypted backup of an encrypted DB and an unencrypted backup of an encrypted DB Let's say we have a fully encrypted database (the type of encryption is not considered in this scenario), and we produce two backups for that database.

If I encrypt only one of the backups, would there be a difference between it and the other one? Is it redundant to encrypt the backup if the database was already encrypted?

I ask from the perspective of the security of the data, not performance or anything else.

Thanks!

Does Rman Re-Encrypt TDE (Tablespace) Encrypted Data? (Doc ID 819167.1)

(above link requires an account associated with a valid support contract)

  When using column encryption, these columns as well as other data in
  the tables are encrypted again during the backup. If you are using
  tablespace encryption these are not further encrypted during backups.

...

  ... for compressed backup on an encrypted tablespace, RMAN must decrypt
  all the blocks, compress them, and encrypt them using RMAN encryption ...


SQL Server on linux unable to export to bacpac I'm trying to export my database to a .bacpac file.  I get the following error:

(provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server)

The command I'm using is:

/opt/mssql/bin/sqlpackage /action:Export /SourceServerName:localhost /SourceDatabaseName:MyDB /targetfile:'/media/jake/1F171B4C75D06418/MyDB.bacpac'

The database I'm trying to back up is on a different ssd than my mssql installation.  I don't know if that makes a difference.

I've been googling around for how to fix this and most people using windows had their similar problem solved by making sure that various windows services were running and starting them if they weren't.  But I'm not sure how to check for the same thing in linux, but I think all the services I need are currently running because I can connect to the database via sqlcmd as well as through JetBrains' DataGrip application.  So I know that a connection is possible.  I just don't know why sqlpackage is having trouble connecting...

I've also tried variations of this command on the SourceServerName parameter:

/opt/mssql/bin/sqlpackage /action:Export /SourceServerName:tcp:localhost,1433 /SourceDatabaseName:MyDB /targetfile:'/media/jake/1F171B4C75D06418/MyDB.bacpac'

And the error is slightly different:

(provider: TCP Provider, error: 0 - No such host is known.)

According to the official documentation for SqlPackage on 2017 CTP 2.1, the command to export data and schema is as follows:

/opt/mssql/bin/sqlpackage /a:Export /ssn:tcp:<your_server> /sdn:<your_database> /su:<username> /sp:<password> /tf:</path/to/your/file.bacpac>

Looking at your command, it appears you're missing credentials, so I'd give this a try (I've shortened the other parameters as well, and removed the TCP port):

/opt/mssql/bin/sqlpackage /a:Export /ssn:tcp:localhost /sdn:MyDB /su:<user> /sp:<password> /tf:'/media/jake/1F171B4C75D06418/MyDB.bacpac'


Why does linked server need delegation where as file access require no delegation settings? I have a ssis package deployed to ssis catalog in ServerA. It is configured to be run via sql agent job step.
The job step is set to Run As proxy user that is pointing to a domain user credential. Example: domainname\user1
Effectievly the ssis package is getting run in context of the above user.
The ssis package access a file on shared drive located on ServerB and dumps the data into a sql table on ServerC.
The sql table on ServerC is access via linked server configured on ServerA using the same windows account and impersonate is check marked. Delegation is not configured.
There is an error when dumping data to sql table via linked server since I have not configured delegation. My question is that - why does linked server need delegation for Windows domain user where as accessing a shared network file using the same account doesn't need any delegation?

This happens as the ssis is not involved in a two hops windows authentication process.
As you said you use a proxy so you impersonate an account that is used directly against the CIFS share.
Instead with a linked server you are doing a two hops windows authentication:

Local sql server
target of linked server

To avoid two hops in ssis, don't use linked server. Instead use a direct connection string.


Does using lower Isolation Level improve OLTP performance? We have a SQL Server 2008 R2 instance which gets millions of rows of data per day from automatic devices such as CTI and AVR. On the same instance there are many heavy reports which analyze the data.

I'm looking into the option of separating these activities into two distinct processes:

Getting the rows from the automatic devices.
Analyzing the data and producing heavy reports.

I plan to try to set the isolation level for the first process to the minimum (read uncommitted) as data integrity is not such an issue.

Would I get significant performance improvement from both the separation and usage of the lowest isolation level?

SQL Server 2008 Solution

Seeing as you are thinking of duplicating the database into two instances, changing the isolation level from the default Read committed isolation level to Read uncommitted will not have much of an impact.

Reference: Isolation Levels in the Database Engine

The inserts occur on instance1.database which does not have any active (SELECT) queries.

Transferring the data from instance1.database to instance2.database (Reporting database) will have a small impact during the night. Otherwise the users are not impacted by the INSERTs occurring on instance1.database during normal office hours while accessing instance2.database.

No need to change the isolation level.

Alternate Solution : Availability Groups

You might be better off with a newer SQL Server version (2012 or greater) and Availability Groups. This way you could have your live data sent to the AG and you just configure the second replica to be READ-ONLY, which you then query for reporting purposes. This is documented in the MSDN article:

Configure Read-Only Access on an Availability Replica (SQL Server)

Here is an overview of the AlwaysOn Availability Groups (SQL Server)


How to delay replication start I'm creating a new publication for replication using the @allow_initialize_from_backup option to avoid the snapshot immediate creation and therefore locking all objects replicated on articles. The issue is that, either using SSMS wizard or tsql, when I start the publication (no subscription created yet), immediately I get lot of deadlocks on the tables been replicated. I'm checking all this information and more but I can't manage to do what I need. Haven't found a clear answer online. What I'm missing here?

sp_addpublication at MSDN
How to: Initialize a Transactional Subscription from a Backup (Replication Transact-SQL Programming)
Starway to SQL Server Replication
Replication Without Creating a Snapshot
similar question but different errorTransaction without snapshot?

And this is the code I'm trying to run:

EXEC sp_replicationdboption @dbname = N'db_name',
     @optname = N'publish',
     @value = N'true'
GO

EXEC sys.sp_addlogreader_agent @job_login = NULL,
     @job_password = NULL,
     @publisher_security_mode = 1
GO

EXEC sys.sp_addqreader_agent @job_login = NULL,
     @job_password = NULL,
     @frompublisher = 1
GO

-- Adding the transactional publication
EXEC sp_addpublication @publication = N'db_name',
     @description =
     N'Transactional publication of database ''db_name'' from Publisher ''distributor''.',
     @sync_method = N'native',
     @retention = 0,
     @allow_push = N'true',
     @allow_pull = N'false',
     @allow_anonymous = N'true',
     @enabled_for_internet = N'false',
     @snapshot_in_defaultfolder = N'true',
     @compress_snapshot = N'false',
     @ftp_port = 21,
     @allow_subscription_copy = N'false',
     @add_to_active_directory = N'false',
     @repl_freq = N'continuous',
     @status = N'active',
     @independent_agent = N'true',
     @immediate_sync = N'true',
     @allow_sync_tran = N'false',
     @allow_queued_tran = N'false',
     @allow_dts = N'false',
     @replicate_ddl = 1,
     @allow_initialize_from_backup = N'true',
     @enabled_for_p2p = N'false',
     @enabled_for_het_sub = N'false'
GO

EXEC sp_addpublication_snapshot @publication = N'db_name',
     @frequency_type = 1,
     @frequency_interval = 0,
     @frequency_relative_interval = 0,
     @frequency_recurrence_factor = 0,
     @frequency_subday = 0,
     @frequency_subday_interval = 0,
     @active_start_time_of_day = 0,
     @active_end_time_of_day = 235959,
     @active_start_date = 0,
     @active_end_date = 0,
     @job_login = NULL,
     @job_password = NULL,
     @publisher_security_mode = 1

EXEC sys.sp_addsubscription
    @publication = N'db_name',
    @subscriber = N'subscriber server',
    @destination_db = N'db_name',
    @subscription_type = N'Push',
    @sync_type = N'initialize WITH backup',
    @backupdevicetype = N'Disk',
    @backupdevicename = N'F:\Backup\db_name\',
    @update_mode = N'read only',
    @subscriber_type = 0;

-- Add subscription agent
EXEC sys.sp_addpushsubscription_agent
    @publication = N'db_name',
    @subscriber = N'subscriber server',
    @subscriber_db = N'db_name',
    @job_login = NULL,
    @job_password = NULL,
    @subscriber_security_mode = 1,
    @frequency_type = 64,
    @frequency_interval = 1,
    @frequency_relative_interval = 1,
    @frequency_recurrence_factor = 0,
    @frequency_subday = 4,
    @frequency_subday_interval = 5,
    @active_start_time_of_day = 0,
    @active_end_time_of_day = 235959,
    @active_start_date = 0,
    @active_end_date = 0,
    @dts_package_location = N'Distributor'
GO

Got it. See my code, there is an option when creating the publication

EXEC sp_addpublication @publication = N'db_name',
       ...
    @immediate_sync = N'true'
    ...
GO

That's the equivalent of checking on the publication creation wizard the checkbox Create a snapshot immediately and keep the snapshot available to initialize subscriptions.

That's why when running it, tried to automatically create the snapshot. And as last snapshot is from couple of months, there is lot work to do in the middle. Ran with false value and now everything is ok, runnig smoothly and without issues.


Clarification on Cardinality and Participation I'm currently redesigning a schema with 20 or so tables and my first task is to create an ERD from the existing tables/constraints/relationships. This has proven more difficult than I expected because while I'm good at creating ERDs from business rules, extracting that same information from a database that was poorly designed has been challenging.

My specific questions revolve around translating the constraints and relationships from the DDLs into cardinality and participation on the ERD. An example:

The following DDLs for two sample tables (I included only relevant attributes, ie FKs and PKs):

CREATE TABLE SCHEMA.SCHEDULE (
    SCHEDULE_ID NUMBER(22,0),
    CONSTRAINT SCHEDULE_PK PRIMARY KEY (SCHEDULE_ID)
) ;

CREATE TABLE SCHEMA.OBJECTS (
    OBJECT_ID NUMBER,
    SCHEDULE_ID NUMBER,
    CONSTRAINT OBJECT_PK PRIMARY KEY (OBJECT_ID),
    CONSTRAINT OBJECTS_SCHEDULE_FK FOREIGN KEY (SCHEDULE_ID) REFERENCES SCHEMA.SCHEDULE(SCHEDULE_ID)
) ;

From the above DDL, I understand the following:
- SCHEMA.SCHEDULE is the parent, and SCHEMA.OBJECTS is the child

I then was able to make the following ERD:

What I can't figure out, is the cardinality and participation. I am guess that for SCHEDULE it is (1,M) and OBJECTS it is (0,m). Is this correct?

Any resources or excellent clear explanations with lots of examples is also appreciated. I am creating the ERD with min/max notation.

Thank you.

After further research, and for anyone else in the future, I thought I would post what I have learned looking into this topic. Credit mostly goes to "Data Modeling and Database Design" by Narayan S. Umanath and Richard W. Scamell (2nd Edition).

I came up with following participation and cardinality:

In reference to the relationship above, I determined the participation and cardinality for the entity "Objects" in the "has" relationships to be optional (0) with a cardinality of many (m). The reason is because the foreign key "schedule_ID" in the "Objects" table is an optional attribute, meaning "Objects" does not have mandatory participation in the relationship. Additionally, the business logic dictates that each tuple in the "Objects" table can only correspond to one tuple in the "schedule" table.

For the (0,m) that defines the participation and cardinality of the "schedule" entity in the relationship "has", parent entities in relationships are rarely total participation, meaning in majority of cases (unless specific business logic requires it) they have partial partition (denoted by the "0"). For the cardinality, each tuple in the "schedule" table can be related to many objects.

Additionally, I compiled the following notes while looking into this that might be helpful in these types of cases (creating ERD for existing databases):
- by default, relationships between parents and children are (0,m)(0,1).
- can not enforce mandatory participation on parent side without the use of triggers.
- in order to for participation on child entity, put a NOT NULL constraint on the foreign key fields.
- to specify alternative keys (candidate keys), you can place a UNIQUE constraint.

I invite others to offer comments and critique. Hope this helps someone!


Guru hints not working in pgAdmin Guru hints don't work on my pgAdmin (1.18.1) - no hint was ever shown, "Hints" item in "Help" menu is grey and even though I can set options for the hints, the changes won't save - when I open "Options" the next time, both fields are unchecked again. I ran the installer again to "repair" the install, but the hints are still not working.

So how to enable the hints?

you will find valuable information in pgAdmin III documentation, and to be more precise in the settings page about miscellaneous options. You can check that Guru hints are enabled (unchek the Do not show guru hints; I’m one myself box if it's checked). You'll notice that guru hints won't be available everywhere, but only on some objects in you database, for example tables (saying something like "hey, what about defining a primary key for this table?"), functions and so on.

Good luck!


How to restore from .wt files? Environment
Windows Professional 10
MongoDB 3.6.4 2008R2Plus SSL (64 bit)  (before computer crash)
MongoDB 4.0.2 2008R2Plus SSL (64 bit)  (after computer crash)

I have been developing locally, and turned on computer today to blue screen of death.

I had no option but to reset Windows 10 and keep personal files.

I therefore have this directory:

C: > data

which contains two directories, db and log.

Below is contents of directory db:

Below is contents of directory log:

I have downloaded and installed:

MongoDB 4.0.2 2008R2Plus SSL (64 bit)

and I accepted the default installation directories:

Question

How can I restore the databases and collections from the contents of my original mongoDB data directory?

Note - I am on the same computer, after a Windows 10 reset, and new install of MongoDB.

Related Questions

There are similar questions below, but they don't seem to provide an authoritative and accepted answer:

How to restore .wt backup file to local MongoDB?

How to copy wt files from another mongo instance?

https://stackoverflow.com/q/37557319

https://stackoverflow.com/q/32551549

Some sort of benevolent magic occurred after doing the following:

Remove MongoDB Community Edition as a Windows Service, see here.

sc.exe delete MongoDB

Then when I went into my favourite MongoDB GUI (Robo3T), the databases were just there!

So i ran:

mongodump

To get a "proper" dump of all my databases.

I'm confused how this could happen because the databases are still at their original location:

C:\data\

And the new MongoDB installation data location was:

C:\Program Files\MongoDB\Server\4.0\data\

¯_(ツ)_/¯

Atleast I have a proper mongodump backup now.


Saving and managing multiple database "states"? I'm very sorry if this is the wrong place to ask this question.

I have been tasked with implementing a system to allow administrators to make changes to data in a database, and save these changes into "states" or "plans".  The states need to be compare-able, with a final state chosen to become the "primary state" and be used for all future queries.

Normally, for things like this, I would duplicate the database and make changes there, but the database I have been entrusted with is extremely large and impossible to duplicate quickly.  I'm using MS SQL Server, and interfacing with Entity Framework in .NET.

Do any of these tools provide inclusive solutions to this problem?  How could this be done realistically?  A coworker has suggested version control, which sounds promising, but I'm wondering if there is a better way.

SQL Server provides various ways to maintain states of the table + you can design your own:

Change data capture : CDC is use to capture insert, update or delete operation on a table. But remember, it does not support computed columns.
Change tracking: This also capture DML statements like CDC but does not hold historical data.
SCD Type 2: By creating your own historical data change table by using timestamp and mark the latest record as active.
Microsoft Sync Framework : You can use this if you want to have a replication of database. I used it when I had to do the sync between offline SQL Server to online. However, this may cost effort in implementation and also it will not provide you all the features you are looking for.

For future releases (i.e. SQL Server 2016 and above)

Temporal Tables: I did use that in my experiments on SQL Server 2016 CTP 2 and found it promising. In a way you can consider them as SCD type 2.

If I have to choose at your place than I would go for CDC.


Archive backups with a PowerShell script launched by SQL Server Agent I would like to run a Powershell script via the SQL Server Agent.
This script works fine if I run it manually through the command prompt, but the job history in SQL Server Management Studio tells me a syntax error on line 8.

Is there a difference in the SQL Server Agent runtime environment?

Here is my script:

$ArchivesMPath = "E:\ArchivesBDD\Mensuelles"
$SauvegardeMPath = "E:\SauvegardeBDD\Mensuelle"
$ArchivesQPath = "E:\ArchivesBDD\Quotidiennes"
$SauvegardeQPath = "E:\SauvegardeBDD\Quotidienne"
$DAYSOFWEEK = "Dimanche", "Lundi", "Mardi", "Mercredi", "Jeudi", "Vendredi", "Samedi"
$MONTHS = "Decembre", "Janvier", "Fevrier", "Mars", "Avril", "Mai", "Juin", "Juillet", "Aout", "Septembre", "Octobre", "Novembre", "Decembre"

$DAILYDIR = "$ArchivesQPath\$($DAYSOFWEEK[(get-date).AddDays(-1).DayOfWeek.value__])"
if ([System.IO.Directory]::Exists($DAILYDIR)){
    Remove-Item $DAILYDIR -Force -Recurse
}
New-Item -Path $DAILYDIR -ItemType directory
Move-Item -Path "$SauvegardeQPath\*" -Destination $DAILYDIR

if ((get-date).AddDays(-1).day -eq 1){
    $MONTHLYDIR = "$ArchivesMPath\$($MONTHS[(get-date).month])"
    if ([System.IO.Directory]::Exists($MONTHLYDIR)){
        Remove-Item $MONTHLYDIR -Force -Recurse
    }
    New-Item -Path $MONTHLYDIR -ItemType directory
    Move-Item -Path "$SauvegardeMPath\*" -Destination $MONTHLYDIR
}

Thank you in advance for your help

I believe the problem you're having has to do with SQL Server Agent Tokens as referred to by this Stack Exchange post SQL Agent - PowerShell step “syntax error”

Excerpting from that post

  This is a not very intuitive and I was never able to find anything
  concrete on the explanation [e.g. no exact BOL or white paper was
  found]. The syntax error in the SQL Agent job is a T-SQL syntax error
  based on User Tokens. So that basically means that a PowerShell
  Sub-Expression Operator is treated as a Token to SQL Server Agent. So
  in PowerShell this $( ) appears to be treated as reserved character
  sequence for SQL Server Agent.

In your example, the problem line is this

$DAILYDIR = "$ArchivesQPath\$($DAYSOFWEEK[(get-date).AddDays(-1).DayOfWeek.value__])"

Notice the $( which is a Powershell sub-expression.  This part is attempting to be interpreted by the SQL Server Agent as a token.

My suggestion would be to separate out the sub-expression into another Powershell variable ($DAILYDIRDAY) and then use that to finish building the $DAILYDIR variable.

Give this a try:

$ArchivesMPath = "E:\ArchivesBDD\Mensuelles"
$SauvegardeMPath = "E:\SauvegardeBDD\Mensuelle"
$ArchivesQPath = "E:\ArchivesBDD\Quotidiennes"
$SauvegardeQPath = "E:\SauvegardeBDD\Quotidienne"
$DAYSOFWEEK = "Dimanche", "Lundi", "Mardi", "Mercredi", "Jeudi", "Vendredi", "Samedi"
$MONTHS = "Decembre", "Janvier", "Fevrier", "Mars", "Avril", "Mai", "Juin", "Juillet", "Aout", "Septembre", "Octobre", "Novembre", "Decembre"
$DAILYDIRDAY = $DAYSOFWEEK[(get-date).AddDays(-1).DayOfWeek.value__]
$DAILYDIR = "$ArchivesQPath\$DAILYDIRDAY"

and follow up with the rest of the Powershell script


Inserting 500K records into SQL Server 2008 is taking 15s; how to optimize? I have a SQL Server 2008 machine that is running backed by a SAN (not entirely certain of the SAN configuration). I've been noticing that some queries are slow to respond, so have been running some tests trying to optimize/index the slow parts. I am not a SQL Server DBA (background is more MySQL centric), but have the task of improving this performance.

My tests are the following. I created 2 tables:

CREATE TABLE "Z_SIMULATION_0"
(
    "ID" INT NOT NULL,
    "VALUE" INT NOT NULL,
    PRIMARY KEY ("ID")
);

CREATE TABLE "Z_SIMULATION_1_TABLE"
(
    "ID" BIGINT NOT NULL,
    "ELEMENT" BIGINT NOT NULL,
    "flag" INT NULL DEFAULT NULL,
    "IS_ACTIVE" BIT NOT NULL DEFAULT b'0',
    PRIMARY KEY ("ID", "ELEMENT")
);

The indexes for the Z_SIMULATION_1_TABLE as exported by SSMS are:

CREATE NONCLUSTERED INDEX [IX_Z_SIMULATION_1_IS_ACTIVE]
    ON [dbo].[Z_SIMULATION_1_TABLE] ([IS_ACTIVE] ASC)
       WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF,
             SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF,
             ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]

CREATE NONCLUSTERED INDEX [IX_Z_SIMULATION_INDEX_ACTIVE]
    ON [dbo].[Z_SIMULATION_1_TABLE] ([ID] ASC, [IS_ACTIVE] ASC)
    INCLUDE ([ELEMENT], [flag])
       WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF,
             SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF,
             ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]

CREATE NONCLUSTERED INDEX [IX_ZSIMULATION1_ELEMENT]
    ON [dbo].[Z_SIMULATION_1_TABLE]( [ELEMENT] ASC)
       WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF,
             SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF,
             ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]

CREATE NONCLUSTERED INDEX [IX_ZSIMULATION1_ID]
    ON [dbo].[Z_SIMULATION_1_TABLE] ([ID] ASC)
       WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF,
             SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF,
             ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]

ALTER TABLE [dbo].[Z_SIMULATION_1_TABLE]
  ADD CONSTRAINT [PK_Z_SIMULATION_1_TABLE]
  PRIMARY KEY NONCLUSTERED ([ID] ASC, [ELEMENT] ASC)
      WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF,
            SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF,
            ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]

I then populated the Z_SIMULATION_0 table with 600K random (non-repeating) ID/Value pairs (IDs are between 1 & 600k).

Finally, I executed this query:

INSERT INTO Z_SIMULATION_1_TABLE (ID, ELEMENT, IS_ACTIVE)
    SELECT 2, value, 0
    FROM Z_SIMULATION_0
    WHERE ID>=1 AND ID <= 500000;

This query took 14s to complete, which I find is significantly too long. I don't see why an insert into a blank table should take this long, and more importantly, I'm not sure how to optimize things to make it any faster.

.

I see the Sort (for the index I presume?) is taking the bulk of the time, but not sure what to do about that.

How can I improve the performance/reduce the amount of time it takes to do the insert?  I'm open to changing/modifying/deleting indexes as required or necessary.

It isn't just doing one insert operation per row, there is one insert per row per index with the associated sorts too. Each of those sorts may be spooling to disk (and probably is with that much data) to there is a lot of IO going on.

When completely rebuilding a table's contents (i.e. starting with a blank table) it is usually more efficient to drop or disabling all the indexes except the clustered index (if the table has one) before inserting the rows then recreating the indexes afterwards is often more efficient.

Update

If you can't remove the indexes because they may be used, then do some analysis as @ypercube implies to ensure that they are indeed required and useful and remove any that are of doubtable benefit. You may of course be able to temporarily drop some of the indexes if not all. Experiment to see if you would benefit from a clustered index (rules of thumb say no in this example, but rules of thumb do not always apply so running your own application specific benchmarks is often a good idea).

Having said that, if you are only doing a partial update in real life the issue is moot: if you are only inserting enough data to increase the table by 1% in size then dropping the indexes would never be more efficient anyway. Also it would be worth performing your benchmarks on realistically sized data rather than an empty table if the table in real life is not going to be empty.

If you have ruled out index changes, or done as much as you can in that direction, the only thing that you can do from there is to improve the IO performance. As we know nothing of your IO subsystem we can't help you much at the moment, but a few general tips include keeping tempdb on different drives to your main database, for some IO patterns keeping logs on different spindles can help too, or look to spreading the indexes between filegroups on different drive, and so forth. If you have enterprise edition so can use partitioning and your insert blocks map to a nice partitioning scheme, you could use partition switching to do the inserts faster then switch the whole block in in one do. If your data stores are SAN based, try moving tempdb to local drives. If you have control to change the hardware perhaps have some/all structures on SSDs. Adding more ram may help but probably not: you'd have to add enough that all those index sort operations could all happen in RAM all at the same time and SQL Server might still spool to disk (because it considered the RAM better used for something else) and even if you can arrange for the whole sorting operation to be done in RAM the data still has to be written to physical media at the end, there is no avoiding that.

There are a lot of articles (and whole books) out there on the subject of optimising your database IO system unfortunately. If you list your current disk technolog{y|ies} and layout someone might be able to make suggestions for practical things to try first. No matter what you try, remember to run realistic application benchmarks: artificial benchmarks are useful as a quick and easy gauge of what to expect, but sometimes a complexity of your real workload will mean that a massive benefit seen in artificial benchmarks in dev/tes is all but destroyed in production.


MySQL REPLACE appears to corrupt data For reference, this issue was posted on StackOverflow.com before coming here.

In MySQL I am running multiple REPLACE queries to update hardcoded URLs in a database. Queries look like this:

UPDATE some_table
SET some_column = REPLACE(some_column, 'some_url', 'another_url')

The update process goes through every table and every string-based column in the database and runs a query like the above to ensure all potential occurrences are updated.

The system on which this is run also maintains some user content in the database. This content, which is (as far as I can tell) supposed to be unaffected by the update, becomes corrupted:

Before: Welcome to Company’s Learning
After : Welcome to Companyâ€™s Learning

The corruption looks like a string that may contain a unicode character which is not replaced correctly during the REPLACE query.

All tables are InnoDB, and utf8_unicode_ci. The server runs MySQL 5.5.25, on IIS 7.5.

Any idea why this kind of corruption is taking place?

That does not look like a corruption problem. It might be a collation issue, not with the database, but with the IIS 7 or Moodle.

You did not specify if MySQL was running in Linux or Windows, so here it goes:

I ran queries to unveil collations and character sets

For MySQL 5.5.12 running on a Windows 7 machine, I get this:

mysql> show variables like '%coll%';
+----------------------+-------------------+
| Variable_name        | Value             |
+----------------------+-------------------+
| collation_connection | latin1_swedish_ci |
| collation_database   | latin1_swedish_ci |
| collation_server     | latin1_swedish_ci |
+----------------------+-------------------+
3 rows in set (0.00 sec)

mysql> show variables like '%char%';
+--------------------------+---------------------------------+
| Variable_name            | Value                           |
+--------------------------+---------------------------------+
| character_set_client     | latin1                          |
| character_set_connection | latin1                          |
| character_set_database   | latin1                          |
| character_set_filesystem | binary                          |
| character_set_results    | latin1                          |
| character_set_server     | latin1                          |
| character_set_system     | utf8                            |
| character_sets_dir       | C:\MySQL_5.5.12\share\charsets\ |
+--------------------------+---------------------------------+
8 rows in set (0.00 sec)

mysql>

For MySQL 5.5.9 running in Linux, I get this:

mysql> show variables like '%coll%';
+----------------------+-------------------+
| Variable_name        | Value             |
+----------------------+-------------------+
| collation_connection | utf8_general_ci   |
| collation_database   | latin1_swedish_ci |
| collation_server     | latin1_swedish_ci |
+----------------------+-------------------+
3 rows in set (1.75 sec)

mysql> show variables like '%char%';
+--------------------------+----------------------------+
| Variable_name            | Value                      |
+--------------------------+----------------------------+
| character_set_client     | utf8                       |
| character_set_connection | utf8                       |
| character_set_database   | latin1                     |
| character_set_filesystem | binary                     |
| character_set_results    | utf8                       |
| character_set_server     | latin1                     |
| character_set_system     | utf8                       |
| character_sets_dir       | /usr/share/mysql/charsets/ |
+--------------------------+----------------------------+
8 rows in set (1.69 sec)

mysql>

You have to make sure your connector software's character set/collation settings match the database it is connecting to. If the REPLACE ran on the DB Server, the REPLACE is a case-sensitive match. I could easily see a collation shift in the eyes of IIS or Moodle messing up a server-side string replace.

You may want to substitute this with doing a client side replace using PHP's str_replace


MySQL Trigger Validate Before Insert I am new to MySQL TRIGGER. I want to know is it possible to check before insert using last record found?

Table AccountChecking

 ====================================================
 || ID ||  ACID  || Debit  ||    Credit   ||  Saldo ||
 ====================================================
 || 1  ||   10    || 0      ||      0     ||  100   ||
 || 2  ||   20    || 20     ||      0     ||  120   ||
 || 3  ||   30    || 0      ||      20    ||  100   ||
 ====================================================

So, what I want trying to do in Mysql is : check the inserted last Saldo with ( Debit or Credit and Saldo ) addition to Table AccountChecking , if not match the MySQL itself reject the inserted Query.

This is my Query :

DELIMITER $$

CREATE
    TRIGGER `DB`.`balanceCheck` BEFORE INSERT
    ON `DB`.`AccountChecking`
    FOR EACH ROW BEGIN
        IF(NEW.Saldo != (SELECT (SALDO+NEW.Debit-NEW.Credit) FROM AccountChecking WHERE ACID='10' ORDER BY ID DESC))
        THEN
            signal SQLSTATE '00000' SET message_text = "no no no DONT INSERT !";
        END IF;
    END$$

DELIMITER ;

I think you are on the right track. One thing though is that you need to handle the case when there is no match. Assuming someone adds a row like:

(4, 40, 0, 0, 1000000000)

Your IF statement will evaluate to:

IF ( 1000000000 != null ) THEN ...
IF ( null ) THEN ...

i.e. this will be allowed.

If you add a create table statement and some insert statements, together with info on whether they should be allowed or not I'm pretty sure someone can come up with a suggestion.


Postgres Table Ownership I'm new to PostgreSQL and I'm trying to change the table owner for a bunch of tables. I was able to change the ownership by logging in a the postgres user and executing alter table owner to user1, but when I login as user1 it still shows postgres as the owner. For instance, when logged in as the postgres user and executing \dt or select * from pg_tables where tableowner = 'user1' it shows user1 as the owner, but when I login as user1 and do the same thing it still shows postgres as the table owner. I've tried restarting the service and that didn't change anything. What am I missing?

You must be seeing different tables from different database clusters, databases or schemas.

Either you are logging into a different database by mistake or you got yourself confused with tables of the same name in different schemas of the same database.

The latter would typically happen with two different roles that have different search_path settings or even the (default) setting:

search_path = "$user",public

Without schema-qualification, tables of the same name are found in the "current" schema first ...

Your test would also find the "wrong" table:

select * from pg_tables where tableowner = 'user1';

Test instead with:

select * from pg_tables where tablename = 'my_tbl';

You should see two or more rows for the same table name ...

Of just use schema-qualified table names to avoid possible confusion:

ALTER TABLE my_schema.my_tbl OWNER TO user1;
SELECT * FROM my_schema.my_tbl;

Start by reading about schemas in the manual.


Killed DbccFilesCompact found running for days There were 2 running, actually, the first one I killed went away quickly. The second is in suspended status, from sys.dm_exec_requests the percent_complete doesn't change (88.87) and the estimated_completion_time keeps going up. What can I do? There is no room left on the one mdf file drive, is that the problem? This is why my coworker started the shrink (he's on vacation now, long story why they started doing the shrinks, databases is being moved to Hadoop, and I'm new (3rd week)). Please advise.. thanks!

I just killed it today and it went away immediately... hope this helps someone else. Thanks!


Will altering a column name in a MySQL master be replicated to the slave I have a replicated MySQL database that until recently was supported by a DBA. Unfortunately, he has now left so we're playing catch up.

From what I can tell the replication that is set up is:

binary_log based (i.e. not GTID)
row based
the table being altered is InnoDB

I'm basing this on a few things

The output from SHOW SLAVE STATUS references the binary log
The mysql.gtid_executed table on the master and slave are both empty
The output from SHOW GLOBAL VARIABLES LIKE 'binlog_format'; is ROW

The master server is using MySQL version 5.7.9. The slave is using MySQL version 5.7.17

If I change a column name in the master database using the command

ALTER TABLE table_name CHANGE COLUMN old_column_name new_column_name DATETIME NOT NULL

will the change be replicated to the slave?

Or do I need to change the column name in the slave as well?

I was able to test this on our replicated environment.

Surprisingly, the column name was updated in the slave environment. I assumed this would not be the case because the row-based replication was configured.


SQL Server replicating data to an archive I have the following scenario:

Multiple SQL Servers where production machines log about 80GB of data (mostly images) daily in a database (each machine has a server with one database). Since these servers have about 4TB of space the amount of days they can store is limited. So the data on these servers is overridden frequently. But we want to make reports, that date back to up to 2 years.

I could code stored procedures to fetch all new data entries and copy them to a backup SQL Server which has more space every 2 hours or so. But I am talking about a database with approximately 25 tables and I think there might be a better way.

Do you think transactional replication or log shipping might be better options in this scenario?

If the SQL Server on the production site roles over the data (I have not yet had the opportunity to check how this actually works on the systems), I don't want the backup server to replicate this. The Backup server should keep all records and only add new ones.

Any advice on this would be appreciated.

if i understand correctly log shipping and transactional replication is not an option because they basically update on the other side whatever you do on the primary server.
you will get an updated database and if the info is overridden so is the secondary database.
i would maybe try create a planned DWH database that i would copy the fresh data every period of time in some way.. maybe SSIS or bulk insert.

looks like it'll take some work like understanding when is data being overridden: every 5 minutes? at random times when they update it in the app? and then decide accordingly on what to do(maybe even use a trigger on insert or update to bulk insert it to a DWH table in s different server).
I hope this helps.
keep in mind if the updates and insert happen often this triggers may cause performance issues in some cases if done constantly.