Using a DB2 merge with conditionals for matches? Within a PHP script I'm running a working merge but I need to add date conditionals to it, and my updates cause it to no longer work.

The logic I'm looking for is this:

If there's a match AND the expire_date is before today, do an insert
If there's a match AND the expire_date is after today, do an update
If no match, just insert

The matching has been working but I basically want to disregard matches that are expired. What am I doing wrong here?

This is running in db2 for iseries

                    MERGE INTO products AS P
                    USING ( VALUES (
                        CAST(:CUSTOMER AS INT),
                        CAST(:SLSCODE AS INT),
                        CAST(:DTL1 AS INT),
                        CAST(:DTL2 AS INT),
                        CAST(:DTL3 AS INT),
                        CAST(:COUNT AS INT),
                        CAST(:LAST_DATE AS DATE),
                        CAST(:FLAG AS SMALLINT),
                        CAST(:ORDER AS INT),
                        CAST(:PIECES AS VARCHAR(45))
                        )
                    )
                AS S(CUSTOMER,REGION,DTL1,DTL2,DTL3,COUNT,LAST_DATE,FLAG,ORDER,PIECES)
                ON s.CUSTOMER = p.CUSTOMER and s.REGION = p.REGION and s.DTL1 = p.DTL1 and s.DTL2 = p.DTL2 and s.DTL3 = p.DTL3 and s.COUNT = p.COUNT and s.LAST_DATE = p.LAST_DATE and s.FLAG = p.FLAG and s.ORDER = p.ORDER AND s.PIECES = p.PIECES

                WHEN MATCHED AND p.expire_date > current_date
                    THEN UPDATE SET  last_date = s.last_date, order = s.order, pieces = s.pieces, expire_date = s.expire_date

                WHEN MATCHED AND p.expire_date < current_date
                    THEN INSERT VALUES (s.customer,s.region, s.dtl1, s.dtl2, s.dtl3, s.count, s.last_date, s.flag, s.order, s.pieces, s.expire_date)

                WHEN NOT MATCHED
                    THEN INSERT VALUES (s.customer,s.region, s.dtl1, s.dtl2, s.dtl3, s.count, s.last_date, s.flag, s.order, s.pieces, s.expire_date)

The manual says WHEN MATCHED does not allow INSERT. However, you should be able to use the expire_date condition in the USING clause:

MERGE INTO products AS P
USING ( VALUES (
...
    )
)
AS S(CUSTOMER,REGION,DTL1,DTL2,DTL3,COUNT,LAST_DATE,FLAG,ORDER,PIECES)
ON s.CUSTOMER = p.CUSTOMER
and s.REGION = p.REGION
and ...
and p.expire_date > current_date
WHEN MATCHED
THEN UPDATE ...
WHEN NOT MATCHED
THEN INSERT ...


Oracle: Traversing a graph of my foreign keys? Given a table, what is a query that will show foreign key constrains, both immediate and nested? I.e. if T2 has an FK on T1, and T3 has an FK on T2, I would like to show T1 -> T2 -> T3.

I usually use this statement.

with fk_list as (
  select parent_table.table_name parent,
         parent_cons.constraint_name as pk_constraint,
         child_table.table_name child,
         child_cons.constraint_name as fk_constraint
  from user_tables parent_table
    join user_constraints parent_cons on parent_table.table_name = parent_cons.table_name and parent_cons.constraint_type IN ('P', 'U')
    join user_constraints child_cons on child_cons.r_constraint_name = parent_cons.constraint_name and child_cons.constraint_type   = 'R'
    join user_tables child_table on child_table.table_name = child_cons.table_name and child_table.table_name <> parent_table.table_name
)
select level,
       fl.child,
       (select listagg(fk.column_name,',') within group (order by fk.position) from user_cons_columns fk where fk.constraint_name = fl.fk_constraint) as fk_columns,
       fl.parent,
       (select listagg(pk.column_name,',') within group (order by pk.position) from user_cons_columns pk where pk.constraint_name = fl.pk_constraint) as pk_columns
from fk_list fl
where fl.child <> fl.parent
start with fl.parent = 'root table'
connect by prior fl.child = fl.parent
order by level;

It is not the most efficient one, but it does work for me.

Note that it requires 11.2 due to the use of listagg()


Join Multiple Tables for Aggregates How do I join multiple tables for aggregates with Sum and Count functions?

The query I'm trying is as below:

Select
  campaigns.id,
  campaigns.name,
  Count(landers.campaign_id) As landers_count,
  Sum(conversions.revenue) As total_revenue
From
  campaigns Left Join
  conversions
    On campaigns.id = conversions.campaign_id Left Join
  landers
    On campaigns.id = landers.campaign_id
Group By
  campaigns.id

I even tried Outer joins but no luck and  I'm getting inaccurate results.

My example tables are as below:

Campaigns table:

| id | name           |
+----+----------------+
| 1  | Facebook Ads   |
| 2  | Bing Ads       |
| 3  | Direct Mailing |
| 4  | Solo Ads       |

Landers table:

| id | name        | campaign_id |
+----+-------------+-------------+
| 1  | Lander 1    | 1           |
| 2  | Lander Two  | 2           |
| 3  | Lander 3    | 4           |
| 4  | Lander Four | 1           |

Conversions table:

| id | revenue | campaign_id | lander_id |
+----+---------+-------------+-----------+
| 1  | 25.00   | 1           | 1         |
| 2  | 12.00   | 1           | 4         |
| 3  | 19.00   | 4           | 3         |

The results I'm expecting should look like below:

| campaigns.id | campaigns.name | landers_count | total_revenue |
+--------------+----------------+---------------+---------------+
| 1            | Facebook Ads   | 2             | 37.00         |
| 2            | Bing Ads       | 1             | 00.00         |
| 3            | Direct Mailing | 0             | 00.00         |
| 4            | Solo Ads       | 1             | 19.00         |

Fiddle based on answer from @'Willem Renzema'

Fiddle

This request is quite old, but as the accepted answer is wrong, I thought I'd add a correct one, so future readers don't get too confused.

A campain has landers and conversions. If we merely join all tables, we get for a campaign with two landers and three conversions 2 x 3 = 6 result rows. If we sum or count then, we'll get wrong results (the number of landers will be three-fold in the example and the conversion sum will be doubled).

There are mainly two ways to go about this:

Aggregate in subqueries in the select clause.

select
  id, name,
  (select count(*) from landers l where l.campaign_id = ca.id) as landers_count,
  (select sum(revenue) from conversions co where co.campaign_id = ca.id) as total_revenue
from campaigns ca
order by id;

Aggregate before joining.

select ca.id, ca.name, l.landers_count, co.total_revenue
from campaigns ca
left join
(
  select campaign_id, count(*) as landers_count
  from landers
  group by campaign_id
) l on l.campaign_id = ca.id
left join
(
  select campaign_id, sum(revenue) as total_revenue
  from conversions
  group by campaign_id
) co on co.campaign_id = ca.id
order by ca.id;

You can use COALESCE to get zeros instead of nulls in your result.


sp_send_dbmail: Failing When Adding Attachment I have a simple script that should send an attachment using send_dbmail.  It  works when I exclude the attachment or send a very small file, e.g. 5KB. The prod file I want to send is 280KB.
    Exec msdb.dbo.sp_send_dbmail
    @profile_name = 'Activeprofile',
    @recipients = 'Dave@Dave.com.au',
    @subject = 'EmailStuff',
    @file_attachments = 'D:\SSIS\SSISData.csv'

The Database mail configurations are default at 1MB file sizes and the only error I get in the database mail log is

Message
The mail could not be sent to the recipients because of the mail server failure. (Sending Mail using Account 2 (2020-07-22T15:25:34). Exception Message: Cannot send mails to mail server. (Failure sending mail.).
)

This is SQL 2016 SP1 CU5 (Yes I want to patch but management are 'scared')
The process fails running from SQL Agent Job or Query window.
The email targets an internal relay server. I am able to send the file using a test SQL server running SQL 2017, I was wondering if this was a bug with SQL 2016.

After fighting with this for half a day I took SQL out of the equation and tried to perform the same task using Power Shell. This also failed, my network guy ran a PCAP between SQL and relay server and we have identified the issue.
File was getting to the relay server but for whatever reason on that particular SQL server (AWS) by the time the file made it to relay the file was fragmented, wasn't sending the full file.
This SQL box was built before my time by someone who 'DID' SQL, it has several setup and config issues so I am pushing for a rebuild.


Denormalization Strategies: When is it appropriate to create redundancy for faster querying? So I sometimes see databases purposely add redundant information for faster querying. I saw that this was mentioned here but I was wondering when would this be better than indexing, or creating a view? Or perhaps it is never better?

I know there are definitely scaling issues with adding this redundancy, but is there a time when it is more ideal to sacrifice scalability for immediate speed gains?

It seems that denormalization is done on a case-by-case basis. If you do decide to denormalize your tables for efficiency, make sure you are thoroughly testing your queries. As @Pieter Geerkens mention

  Every redundancy made for for the purpose of one specific query will slow down all other queries, inserts, and updates against the same data.

That is denormalizing while may speed up some gains, there are very huge costs in not only speeds of other queries, but also in scalability. It's important to take these all into consideration along with other ways to improve performance.

In short, I'll end it with a quote from Chris Date who wrote the book Database in Depth: Relational Theory for Practitioners:

  I believe firmly that anything less than a fully normalized design is strongly contraindicated ... [Y]ou should "denormalize" only as a last resort. That is, you should back off from a fully normalized design only if all other strategies for improving performance have somehow failed to meet requirements.

Extended Reading:

Scaling Secret #2: Denormalizing Your Way To Speed And Profit
Database War Stories #3: Flickr


Create a row based on a condition (column value) I have a table that contains the following data;

EmpNo | GivenName | PreferredName
---------------------------------
1     | Paris     | Paris
2     | Ashley    | Ash

I am trying to get an output where, if the GivenName and PreferredName values are same, then the row should be returned as it is, but if they are different then the row should be returned twice (if possible, the row returned twice should have a NULL in PreferredName in the first occurence and GivenName a NULL in the second). Something like this:

EmpNo | GivenName | PreferredName
---------------------------------
1     | Paris     | Paris
2     | Ashley    | NULL
2     | NULL      | Ash

I have no idea where to begin. I tried using CASE statement and FULL OUTER JOIN to no avail. I have as well tried using the code below, and it is giving me the answer for the first part of my question:

SELECT [EmpNo]
      ,[GivenName]
      ,[PreferredName]
FROM [Emp] E
WHERE [GivenName]= [PreferredName]
UNION ALL
SELECT [EmpNo]
      ,[GivenName]
      ,[PreferredName]
FROM [Emp]

You can do this with the below query:

DECLARE @Employees TABLE
(
    EmpNo INT,
    GivenName NVARCHAR(255),
    PreferredName NVARCHAR(255)
)

INSERT INTO  @Employees
VALUES (1, 'Paris', 'Paris'), (2, 'Ashley', 'Ash')

SELECT EmpNo,
    GivenName,
    PreferredName
FROM @Employees
WHERE GivenName = PreferredName
UNION ALL
SELECT EmpNo,
    GivenName,
    NULL AS PreferredName
FROM @Employees
WHERE GivenName <> PreferredName
UNION ALL
SELECT EmpNo,
    NULL AS GivenName,
    PreferredName
FROM @Employees
WHERE GivenName <> PreferredName


Send environment info to package At my work we have three different environments: dev, test, and prod.  We also quite a few different SSIS packages.  Currently, when we need to deploy to a particular environment, we update the connection for the package in Visual Studios and deploy it to the correct environment.  I'd like to make this easier.  I'd like to pass in the environment name, the server name, and the database name and have a parameterized connection string in order to run the package correctly.

I'm currently not allowed to set up an Integration Services Catalog for these environments and variables.  The only options I have is to somehow pass in the parameters when I set up the SQL Server Agent Job that will execute these packages on a schedule.

I previously attempted using a parameter in the SSIS package, but when I attempted to pass in a value I got an error stating that parameters are read only.

So, how can I set up these packages to send the pertinent data they need for the server they're running on?

Clarification: We are currently deploying our packages by uploading them to MSDB.

A common approach is to use a different SSIS configuration file for each environment.

Give it the same name, but different contents (server name, local paths, any other variables) in each environment.


Is using a Trigger to Insert a new Record into a View seen as bad practice? So I was recently having a conversation with one of my new work colleagues and we got onto the topic of how best to INSERT data into multiple tables.

Their teaching taught them to not use a TRIGGER to split an INSERT onto a VIEW into multiple INSERTS into their respective tables, is this seen as bad practice?

Following is how I attempted to solve the INSERT issue without creation of duplicate values in the base tables.

VIEW

CREATE VIEW [Person].[vPeopleAllInformation] AS
SELECT pt.Type_Name AS Type,
p.Firstname,
p.Lastname,
e.Email AS Email,
ph.Number AS PhoneNumber
FROM [Person].[People] p
FULL JOIN [Person].[Person_Types] pt ON p.Person_Type = pt.Person_Type_ID
FULL JOIN [Person].[Person_Emails] e ON e.Person_ID = p.Person_ID
FULL JOIN [Person].[Person_Phones] ph ON ph.Person_ID = p.Person_ID

TRIGGER

CREATE TRIGGER [Person].[tPerson_Email_Phone] ON [Person].[vPeopleAllInformation]
INSTEAD OF INSERT
AS
BEGIN

INSERT INTO [Person].[People] (Person_Type, Firstname, Lastname)
SELECT p.Person_Type_ID, i.Firstname, i.Lastname FROM INSERTED i
INNER JOIN [Person].[Person_Types] p ON i.Type = p.Type_Name
WHERE NOT EXISTS (SELECT 1 FROM [Person].[People] WHERE Firstname = i.Firstname AND Lastname = i.Lastname);

INSERT INTO [Person].[Person_Emails] (Person_ID, Email)
SELECT p.Person_ID, i.Email FROM INSERTED i
INNER JOIN [Person].[People] p ON p.FullName = CONCAT(i.Lastname, ', ', i.Firstname)
WHERE NOT EXISTS (SELECT 1 FROM [Person].[Person_Emails] WHERE Email = i.Email);

INSERT INTO [Person].[Person_Phones] (Person_ID, Number)
SELECT p.Person_ID, i.PhoneNumber FROM INSERTED i
INNER JOIN [Person].[People] p ON p.FullName = CONCAT(i.Lastname, ', ', i.Firstname)
WHERE NOT EXISTS (SELECT 1 FROM [Person].[Person_Phones] WHERE Number = i.PhoneNumber);

END

Is this the right way of doing it or should I indeed split it into multiple queries when the user attempts to add another Record to the view?

Short answer

  No, don't do that.

Longer answer:

What problem are you trying to solve? It seems like maybe it's "I would like to update multiple base tables with a single INSERT tsql command." If that's the case, then... Sure? I guess? Why not? As long as you accept that the scope of the implementation in question to do is pretty limited and clunky. You're really just obfuscating away the multiple steps you need to perform anyway. But what happens when you want to DELETE from that view? Or UPDATE from that view? Do you need other triggers to handle those cases?

At a minimum, if you're commited to trying it out for yourself and discovering the "why nots" on your own, I'd at least recommend modularizing out the separate INSERT statements in that trigger into their own procs. At least then you can reuse spInsertPerson 1, 'Bob', 'Smith' when all you really want to do is insert Bob Smith without mucking about with other side-effecting executions.

Still... I probably wouldn't...


Why is ALTER faster than UPDATE (for full table rewrites)? Let's create a garbage table..
CREATE TABLE foo
  AS SELECT x::text FROM generate_series(1,20e6) AS t(x);
Time: 14077.654 ms (00:14.078)

Now, let's look at boring UPDATE,
UPDATE foo SET x = 'foo';
Time: 22239.050 ms (00:22.239)

Compared with ALTER,
ALTER TABLE foo
  ALTER COLUMN x
  SET DATA TYPE text
  USING 'foo';
Time: 9480.027 ms (00:09.480)

It's approximately twice as fast.

Both do rather different things under the hood.
ALTER TABLE takes an exclusive lock on the table and rewrites it. The manual:

The rewriting forms of ALTER TABLE are not MVCC-safe. After a table
rewrite, the table will appear empty to concurrent transactions, if
they are using a snapshot taken before the rewrite occurred. See
Section 13.5 for more details.

UPDATE only takes a write lock on affected rows (all in this case) and adds new row versions to the table, roughly doubling its physical size (unless it can reuse dead space). Concurrent readers can proceed happily.
Table-rewriting forms of ALTER TABLE do not write to the WAL. UPDATE, OTOH, also has to write to the WAL first. That's also why UNLOGGED tables can UPDATE in roughly half the time: no WAL by definition.
The fiddle demonstrates some effective differences:
db<>fiddle here


how to find out how long the last DBCC checkdb took to complete? In most of my servers I have dbcc checkdb scheduled to run.

I can get the last known good DBCC Checkdb through DBCC DBINFO().

I can read the tracks left on the sql server log and I get something like the picture below:

Or I can run the following script:

IF OBJECT_ID('TEMPDB..#MAINT') IS NOT NULL
   DROP TABLE #maint

CREATE TABLE #maint (Results varchar(255) NULL)
INSERT INTO #maint(Results) EXEC('master..xp_cmdshell ''sqlcmd -E -Q"dbcc checkdb([my_database])"''')

select * from #maint

and get something like this:

I could also have a look at the default trace.

what am I after?

How to find out how long did it take to run dbcc checkdb the last time.

If it is scheduled, then getting it from the SQL Agent job's runtime duration is an option.

And if you're using Ola's solution (which I'd recommend) then his dbo.CommandLog table has StartTime and EndTime columns for all the actions it performs, including DBCC work.


Query to obtain cccurrences within a certain period of time I've been looking for and thinking of a solution to the following but I cant come up with it, and hope someone can help me.

I have the following table:

ClientId    RecordDateTime
--------    -------------------
      1     2019-02-14 14:05:49
      1     2019-02-14 14:06:34
      1     2019-02-14 14:07:19
      1     2019-02-14 14:08:49
      1     2019-02-14 14:09:34
      1     2019-02-14 14:10:32
      1     2019-02-14 14:12:32
      1     2019-02-14 14:14:18
      1     2019-02-14 14:15:10

I need to count the amount of occurrences using a determined amount of minutes to split the occurrences. For example:

I set the limit to 1 minutes
First datetime is 14:05:49
Second datetime is 14:06:34, which has less than 1 minute difference with the previous one (14:05:49), so I consider it belongs to the same occurrence.
Third datetime is 14:07:19, which also has less than 1 minute difference with the previous one (14:06:34), so is still same occurrence
Now it comes 14:08:49, which has a difference with the previous one of MORE than 1 minute, so I consider its a new occurence.
Next 14:09:34, which has a difference of less than 1 minute with 14:08:49.

And so it goes.

At the end, the result I want is:

ClientId   Occurrence Start      Occurrence End
--------   -------------------   -------------------
       1   2019-02-14 14:05:49   2019-02-14 14:07:19
       1   2019-02-14 14:08:49   2019-02-14 14:10:32
       1   2019-02-14 14:12:32   2019-02-14 14:12:32
       1   2019-02-14 14:14:18   2019-02-14 14:15:10

This is just an example, but I have a lot of data, from multiple ClientIds.
Is there  way of doing this without using stored procedures and looping through each row?

Thanks in advance for any help

You need to do some grouping for it

CREATE TABLE Table1
    ([ClientId] int, [RecordDateTime] datetime)
;

INSERT INTO Table1
    ([ClientId], [RecordDateTime])
VALUES
    (1, '2019-02-14 14:05:49'),
    (1, '2019-02-14 14:06:34'),
    (1, '2019-02-14 14:07:19'),
    (1, '2019-02-14 14:08:49'),
    (1, '2019-02-14 14:09:34'),
    (1, '2019-02-14 14:10:32'),
    (1, '2019-02-14 14:12:32'),
    (1, '2019-02-14 14:14:18'),
    (1, '2019-02-14 14:15:10')
;

select
  ClientID
  ,min(RecordDateTime) as [Occurrence Start]
  ,max(RecordDateTime) as [Occurrence End]
from
(
select
  ClientID
  ,RecordDateTime
  ,sum(grp) over(PARTITION BY ClientID ORDER BY RecordDateTime) as sgrp
from
  (
   select
       ClientID
       ,RecordDateTime
       ,isnull(prevRecordDT,RecordDateTime) as prevRecordDT
      ,case when datediff(second,prevRecordDT,RecordDateTime) <=60 then 0 else 1 end as grp
  from
    (
    select
          ClientID, RecordDateTime,
          LAG(RecordDateTime,1,NULL)OVER(PARTITION BY ClientID ORDER BY RecordDateTime) as prevRecordDT
    from Table1
    ) as s
  ) as g
) as a
group by ClientID,sgrp

output of it:

ClientID    Occurrence Start    Occurrence End
1   14/02/2019 14:05:49 14/02/2019 14:07:19
1   14/02/2019 14:08:49 14/02/2019 14:10:32
1   14/02/2019 14:12:32 14/02/2019 14:12:32
1   14/02/2019 14:14:18 14/02/2019 14:15:10

dbfiddle
 source of inspiration

A short description:

select
      ClientID, RecordDateTime,
      LAG(RecordDateTime,1,NULL)OVER(PARTITION BY ClientID ORDER BY RecordDateTime) as prevRecordDT
from Table1

key point here is the LAG function , to get the previous value

ClientID    RecordDateTime  prevRecordDT
1   14/02/2019 14:05:49
1   14/02/2019 14:06:34 14/02/2019 14:05:49
1   14/02/2019 14:07:19 14/02/2019 14:06:34

Then, do a difference, in seconds , to test if it's in interval:
,case when datediff(second,prevRecordDT,RecordDateTime) <= 60 then 0 else 1 end as grp

select
       ClientID
       ,RecordDateTime
       ,isnull(prevRecordDT,RecordDateTime) as prevRecordDT
      ,case when datediff(second,prevRecordDT,RecordDateTime) <= 60 then 0 else 1 end as grp
  from
       (
          select
               ClientID, RecordDateTime,
               LAG(RecordDateTime,1,NULL)OVER(PARTITION BY ClientID ORDER BY RecordDateTime) as prevRecordDT
          from Table1
        ) as s

ClientID    RecordDateTime  prevRecordDT    grp
1   14/02/2019 14:05:49 14/02/2019 14:05:49 1
1   14/02/2019 14:06:34 14/02/2019 14:05:49 0
1   14/02/2019 14:07:19 14/02/2019 14:06:34 0
1   14/02/2019 14:08:49 14/02/2019 14:07:19 1

After this, just do a sum base on grp column
,sum(grp) over(PARTITION BY ClientID ORDER BY RecordDateTime) as sgrp

select
  ClientID
  ,RecordDateTime
  ,sum(grp) over(PARTITION BY ClientID ORDER BY RecordDateTime) as sgrp
from
  (
   select
       ClientID
       ,RecordDateTime
       ,isnull(prevRecordDT,RecordDateTime) as prevRecordDT
      ,case when datediff(second,prevRecordDT,RecordDateTime) <= 60 then 0 else 1 end as grp
  from
    (
    select
          ClientID, RecordDateTime,
          LAG(RecordDateTime,1,NULL)OVER(PARTITION BY ClientID ORDER BY RecordDateTime) as prevRecordDT
    from Table1
    ) as s
  ) as g

ClientID    RecordDateTime      sgrp
1         14/02/2019 14:05:49   1
1         14/02/2019 14:06:34   1
1         14/02/2019 14:07:19   1
1         14/02/2019 14:08:49   2
1         14/02/2019 14:09:34   2

And finally, do min and max grouping by ClientID and sgrp


How to improve the liveness of a system when having a long-running transaction blocking multiple reading transactions Is there any recommended approach for improving the application liveness when having, over the same database object, multiple reading transactions, and one long-running transaction which is updating the data?
For example, a transaction Ti is updating several records of [CUSTOMER] table (which can last hours long), and multiple Tj transactions are trying to read the number of orders of each client. I'm assuming that if Ti gets an exclusive lock, all Tj transactions will be suspended resulting in a bad performance (low liveness).
My application is at design time, and I haven't chosen a concrete DBMS yet.
My goal is that Tj are served with the newest version of [CUSTOMER] before Ti began and that Ti doesn't block Tj.

SQL Server offers Read Committed Snapshot Isolation and readers don't blocker writers and vice versa. As Mustaccio said though, the real culprit or problem here is that an update really shouldn't take hours. When done properly, with good table design that includes proper normalization, changing date should take a matter of seconds.


Tempdb Configuration best practices I've been asked to provide my input on best practices to improve tempdb performance. I realise that this is a case by case basis and tempdb in one estate may have different requirements to another due to workload volume and type however I was hoping to get an idea of best practices beyond my current suggestions.

At the moment we have 1 data file which I will increase to 4 to match the number of logical cores. My tempdb files currently reside on the same drives as other database files, they will be moved to their own to stop IO contention.
 I'm also to implement trace flags  1117,1118 to grow all files equally in a file group and solely use uniform extents. Finally, a further change I'll look to make is enabling Instant File Initialisation.

Beyond what I have above, are there any best practices I've missed?
Also, it suggested that storing tempdb on it's own drive is a best practice but should all tempdb files be stored on the same drive or should .ldf,.mdf
& .ndf be stored on their own volumes.

Thanks

X files, with X being the number of processors up to a limit (8, 16 or so).
Preallocate size.
Always on separate volume because I really like the ability to check the IO load on that one. Tempdb overload happens rarely, but if it happens.... it is good to be able to set an alarm.

Regarding drives: if you CAN keep them on the same drive, you either have a very small database (in which case - get it on an M.2 / U.2 SSD with enough IOPS) or have a problem to start with - of the drive is "fake" (like a SAN delivering a LUN that in reality has a lot behind) I keep all my volumes these days on shared storage, and at the moment even on realtively slow HDD (7200 RPM), but it is backed by at the moment 6.4 TB of M.2 SSD which will even grow larger, so my read access time is pretty much guaranteed below one millisecond. Even with all ending up on the same discs, I really want separate volumes to check contention with one look.

Same with mdf and ldf - all on their own volume.


Joining ~ 40 normalized tables for 200 columns to display detailed user profile. Can this be optimized? A profile can have many profile types. That profile can have many fields, which are all tied to lookup tables. Some fields can have many selections.

profile -> profile_date -> one relationship status, one zodiac, many languages, many countries traveled, many cuisines, many activities, ...

profile -> profile_network -> one company, one school, ...

profile -> ...

A lookup table for the fields looks like this:

id  type
1   ♈ ARIES
2   ♉ TAURUS
3   ♊ GEMINI
4   ♋ CANCER
5   ♌ LEO
6   ♍ VIRGO
7   ♎ LIBRA
8   ♏ SCORPIO
9   ♐ SAGITTARIUS
10  ♑ CAPRICORN
11  ♒ AQUARIUS
12  ♓ PISCES

If the field is a single selection, we join the lookup table and we're done.

-> profile_date (zodiac_type_id) -> zodiac_type.id

If the field is a multiple selection, there's a join table in between for the many-to-many.

-> profile_date -> (profile_date_id) profile_date_languages (language_type_id) -> language_type.id

In addition, I am joining other things like this to get all of their photos:

profile -> personas -> photos

In addition, the query also filters for things like persons who like each other, etc.

The query seems to start performing poorly with just a low number of profiles, like less than 10. If a single person has an obnoxious profile where they check all the multiple selections e.g. speak every language, been to every country, it grinds the database to crippled performance for everyone if their mega profile ends up being fetched.

That's the idea. You can see more of the fields here:

Sample query:
https://pastebin.com/wug86Wrr

Sample query plan:
http://www.sharecsv.com/s/a6333a7f610e049dd81ebcfc19a4c02f/temp_file_limit_exceeded.csv

Users will always be filtering by at least profile.birth_date (age), profile.radius (geolocation). Paid users can filter by any field. :)

PostgreSQL 9.6

Some ideas I had were:

Optimize this query (I am not sure what can be done, am mostly wondering if I'm just doing something really stupid/unreasonable)
Right now I am joining many tables per card, so the initial load is slower, but the data is all there if the user decides to explore and things look instant to them. I could instead fetch the minimum preview for the cards (join few tables per card), then only join the full profile (1 card filtered by id, many/the rest of the joins) if the user clicks on the preview to see the detail view (but I feel this is not the best user experience because the user has to now wait for the load every time they click a profile which is more noticeable). I am not sure this would even help because users can also filter by these fields, so I believe I would need to join them beforehand.
Use ElasticSearch (I feel like I wouldn't need this until many more profiles?) I've done something similar to this with Algolia and it handled it like a champ, but I imagine this can just be done with PostgreSQL.

Option 4 - correct your schema design and stop using 'IDs' or whatever other surrogate key, the zodiac table is NOT a lookup table. it's a relation that holds all zodiac signs, and the way you tell the zodiac signs apart is their name, therefore, the key should be:

CREATE TABLE Zodiacs (Zodiac VARCHAR(11) PRIMARY KEY);

Use the Zodiac as the foreign key in your profile table, and now you don't need a join at all.
Do that to all the other one-to many as well, like relationship status, company, school etc. and you just saved yourself most of the joins.
For the many-to-many, like languages and countries, so the same, and you will now need only one join instead of two for each relation.
Give it a try and see how all your queries magically shrink by 90%, and perform orders of magnitude better. you will also explicitly maintain uniqueness and prevent logical duplicates, and you will also see how filters perform orders of magnitude better as the optimizer will be able to correctly evaluate distribution of the actual value, not some random ID from another table.

BTW - there are no fields in a SQL database, there are columns and rows only.
These are the counterparts of Tuples and attributes in the relational model

HTH


using lag() on multiple columns (gaps and islands issue) I have a problem I am trying to solve where I need to eliminate duplicate consecutive rows in a table using the first 7 columns as the basis to determine if it is a duplicate. The data looks like:

textColA|textColB|textColC|textColD|textColE|numCol1|numCol2|eventNum
--------+--------+--------+--------+--------+-------+-------+--------
    x   |    y   |    z   |  foo   |  bar   |  1    | 2     | 1
    x   |    y   |    z   |  foo   |  bar   |  1    | 2     | 2
    x   |    y   |    z   |  foo   |  bar   |  1.1  | 2     | 3
    x   |    y   |    z   |  foo   |  bar   |  1.1  | 2     | 4
    x   |    y   |    z   |  foo   |  bar   |  1    | 2     | 5
    x   |    y   |    z   |  foo   |  bar   |  1    | 2     | 6

With these data in the order displayed, I want to retain eventNum 1,3 and 5 - event 5 because even though it is the same as 1 & 2 but follows 4, which is not the same.

I have the code to do it using a cursor looping through each row and doing the comparisons but while this will work for a table with 1, 2 or even 10 million rows, it will likely be problematic on the 300-400m row tables I need to process.

I've looked at different solutions using the lag() function but the examples I see only deal with one column and I can't seem to the get the syntax right for using multiple columns.

Any help is appreciated.

lag() only works over a single column, so you need to define one new "column" for each lag value. You can use a common Window clause so that it's clear to the query planner that it only needs to sort the rows once -- although I think Postgres can determine this for itself too -- but it also saves typing.

After that it's just a matter of comparing the "lagged" values to see if they are in any way different. is distinct from is handy here as the first "lagged" values will all be null, so they are distinct from the first row's values and so the first row will be included in the output.

Putting it all together, it works like this:

select textColA, textColB, textColC, textColD, textColE, numCol1, numCol2, eventNum
from (
  select
    l.*,
    lag(textColA) over w as lagTextColA,
    lag(textColB) over w as lagTextColB,
    lag(textColC) over w as lagTextColC,
    lag(textColD) over w as lagTextColD,
    lag(textColE) over w as lagTextColE,
    lag(numCol1) over w as lagNumCol1,
    lag(numCol2) over w as lagNumCol2
  from lagger l
  window w as (order by textColA, textColB, textColC, textColD, textColE, eventNum)
) x
where
   textColA is distinct from lagTextColA
or textColB is distinct from lagTextColB
or textColC is distinct from lagTextColC
or textColD is distinct from lagTextColD
or textColE is distinct from lagTextColE
or numCol1 is distinct from lagNumCol1
or numCol2 is distinct from lagNumCol2
;

Complete fiddle is here: https://dbfiddle.uk/?rdbms=postgres_11&fiddle=2e11af872a47bd75a20c64f87e7a6cd6 (NB In the fiddle I forgot the other columns in the Window clause)


SQL Server Management Studio for a remote database With a SQL Sever database which is on a remote hosted dedicated server I can work using SQL Server Management Studio installed either on the same server or on my local computer. I the first case I should work using Remote Desktop and this makes the work a little bit slower. In the second case I need to open additional port in server's firewall, but I will have more comfortable user experience.

What is the recommended practice of these two?

The way that I know of to do this is to establish a VPN connection to the other site, thus eclipsing the need to open a port and maintaining the security of the setup, while eliminating the need to RDP into the other server. Granted, you run the risk of dropping a connection while you're working, thus possibly ending queries prematurely, but otherwise ... it's what we do and it works well for us.

If you're going to use them on a regular basis remotely you may look into a semi-permanent VPN connection (I'm sure there's a better name .. VPN bridge?) that will allow you to just connect by IP address. A networking guy can weigh in better here.


Can I rely on reading SQL Server Identity values in order? TL;DR: The question below boils down to: When inserting a row, is there a window of opportunity between the generation of a new Identity value and the locking of the corresponding row key in the clustered index, where an external observer could see a newer Identity value inserted by a concurrent transaction? (In SQL Server.)

Detailed version

I have a SQL Server table with an Identity column called CheckpointSequence, which is the key of the table's clustered index (which also has a number of additional nonclustered indexes). Rows are inserted into the table by several concurrent processes and threads (at isolation level READ COMMITTED, and without IDENTITY_INSERT). At the same time, there are processes periodically reading rows from the clustered index, ordered by that CheckpointSequence column (also at isolation level READ COMMITTED, with the READ COMMITTED SNAPSHOT option being turned off).

I currently rely on the fact that the reading processes can never "skip" a checkpoint. My question is: Can I rely on this property? And if not, what could I do to make it true?

Example: When rows with identity values 1, 2, 3, 4, and 5 are inserted, the reader must not see the row with value 5 prior to seeing the one with value 4. Tests show that the query, which contains an ORDER BY CheckpointSequence clause (and a WHERE CheckpointSequence > -1 clause), reliably blocks whenever row 4 is to be read, but not yet committed, even if row 5 has already been committed.

I believe that at least in theory, there may be a race condition here that might cause this assumption to break. Unfortunately, documentation on Identity doesn't say a lot about how Identity works in the context of multiple concurrent transactions, it only says "Each new value is generated based on the current seed & increment." and "Each new value for a particular transaction is different from other concurrent transactions on the table." (MSDN)

My reasoning is, it must work somehow like this:

A transaction is started (either explicitly or implicitly).
An identity value (X) is generated.
The corresponding row lock is taken on the clustered index based on the identity value (unless lock escalation kicks in, in which case the whole table is locked).
The row is inserted.
The transaction is committed (possibly quite a lot of time later), so the lock is removed again.

I think that between step 2 and 3, there is a very tiny window where

a concurrent session could generate the next identity value (X+1) and execute all the remaining steps,
thus allowing a reader coming exactly at that point of time to read the value X+1, missing the value of X.

Of course, the probability of this seems extremely low; but still - it could happen. Or could it?

(If you're interested in the context: This is the implementation of NEventStore's SQL Persistence Engine. NEventStore implements an append-only event store where every event gets a new, ascending checkpoint sequence number. Clients read events from the event store ordered by checkpoint in order to perform computations of all sorts. Once an event with checkpoint X has been processed, clients only consider "newer" events, i.e., events with checkpoint X+1 and above. Therefore, it is vital that events can never be skipped, as they'd never be considered again. I'm currently trying to determine if the Identity-based checkpoint implementation meets this requirement. These are the exact SQL statements used: Schema, Writer's query, Reader's Query.)

If I'm right and the situation described above could arise, I can see only two options of dealing with them, both of which are unsatisfactory:

When seeing a checkpoint sequence value X+1 before having seen X, dismiss X+1 and try again later. However, because Identity can of course produce gaps (e.g., when the transaction is rolled back), X might never come.
So, same approach, but accept the gap after n milliseconds. However, what value of n should I assume?

Any better ideas?

When inserting a row, is there a window of opportunity between the generation of a new Identity value and the locking of the corresponding row key in the clustered index, where an external observer could see a newer Identity value inserted by a concurrent transaction?

Yes.

The allocation of identity values is independent of the containing user transaction. This is one reason that identity values are consumed even if the transaction is rolled back. The increment operation itself is protected by a latch to prevent corruption, but that is the extent of the protections.

In the specific circumstances of your implementation, the identity allocation (a call to CMEDSeqGen::GenerateNewValue) is made before the user transaction for the insert is even made active (and so before any locks are taken).

By running two inserts concurrently with a debugger attached to allow me to freeze one thread just after the identity value is incremented and allocated, I was able to reproduce a scenario where:

Session 1 acquires an identity value (3)
Session 2 acquires an identity value (4)
Session 2 performs its insert and commits (so row 4 is fully visible)
Session 1 performs its insert and commits (row 3)

After step 3, a query using row_number under locking read committed returned the following:

In your implementation, this would result in Checkpoint ID 3 being skipped incorrectly.

The window of misopportunity is relatively small, but it exists. To give a more realistic scenario than having a debugger attached: An executing query thread can yield the scheduler after step 1 above. This allows a second thread to allocate an identity value, insert and commit, before the original thread resumes to perform its insert.

For clarity, there are no locks or other synchronization objects protecting the identity value after it is allocated and before it is used. For example, after step 1 above, a concurrent transaction can see the new identity value using T-SQL functions like IDENT_CURRENT before the row exists in the table (even uncommitted).

Fundamentally, there are no more guarantees around identity values than documented:

Each new value is generated based on the current seed & increment.
Each new value for a particular transaction is different from other concurrent transactions on the table.

That really is it.

If strict transactional FIFO processing is required, you likely have no choice but to serialize manually. If the application has less oneous requirements, you have more options. The question isn't 100% clear in that regard. Nevertheless, you may find some useful information in Remus Rusanu's article Using Tables as Queues.


Temporarily Archive Databases on SQL Server 2008 R2 I've got a client that has approximately 100 databases across 10 servers. We're attempting to create a standardized process for removing old and unnecessary databases. In the process, we'd like a step where we can temporarily 'archive' a database in case it is still in use. This way, we can easily tell if someone is attempting to access it but can not, and if they are we can turn it back on. Otherwise, we'll remove it.

We have backups, so obviously one option would be to create the backup and delete the database. Then, if it needs to be restored, restore from a backup. However, my client is looking for a simpler method if at all possible.

I've googled for awhile and I can't find any method of temporarily archiving a database in SQL Server. So, I was considering recommending that the client rename the database for approximate a week, and then, depending on the response from clients, delete the database. This method would still include the backups.

My question boils down to: What are the potential pitfalls with temporarily renaming a database? Does SQL Server remove any logins or authentication information when a database is renamed and then renamed back? Does anyone have a more standardized recommendation for this process, or a direction to point me to get me researching further?

Thank you!

To answer your questions first:

  What are the potential pitfalls with temporarily renaming a database?

At this scale, it'd be bothersome and just potentially messy to be renaming this many databases multiple times in the case some are needed. It may be a pain cutting off all connections when re-naming and could confuse other users as well.

  Does MSSQL remove any logins or authentication information when a
  database is renamed and then renamed back?

Nope! Not an issue.

  Does anyone have a more standardized recommendation for this process,
  or a direction to point me to get me researching further?

In this case, I'd probably just take the databases OFFLINE temporarily to see if they are needed.

You can do this via the GUI by right-clicking a database, Task -> Take Offline, or via TSQL:

ALTER DATABASE [dbname] SET OFFLINE;

When a database is offline, all connections are closed and no new connections can be made to it. It's quick to bring it back online if required:

ALTER DATABASE [dbname] SET ONLINE;

Plus, when the database is offline it's using less resources than an online database, so it may be better for performance in the mean time as well.


Your query has 5 equality conditions in 5 different columns:

select *
from t_event
where create_user_id=7
  and event_create_date = '2012-12-18 00:00:00'
  and event_type_cd = 11
  and domain_id = 602
  and job_id =1

limit 1 ;                  -- why is this for?

Now you have 2 indexes (domain_id_event_type_cd and event_user_FK), which cover only 3 of the 5 column/conditions of the query.

So the plan that the optimizer chooses is an index_merge operation, e.g. it uses the two indexes to find rows that match the 2 and 1 conditions respectively, then intersects them (Using intersect) to find the common rows and then has to do an additional search on the table (Using where) for the rest 2 conditions that are not covered by the indices.

You could add a compound index on all those columns. The order does not matter at all for this query, it matters for other queries you may have and whether it could be used for them:

ALTER TABLE t_event
    ADD INDEX i5
        (domain_id, event_type_cd, create_user_id, event_create_date, job_id) ;

Then the Explain would show only Using index and only the above index would be used.


Merging one SQL database into another Currently in my CI I'm completely wiping production db with development db. That was fine for testing but I'm coming to a stage where I need to start merging instead of completely wiping.
Right now the command I'm using to wipe and write is as follows:
mysqldump -u "$DB_USERNAME" -p"$DB_PASSWORD" -h "$DB_HOST" "development" > dump.sql
mysql -u "$DB_USERNAME" -p"$DB_PASSWORD" -h "$DB_HOST" "production" < dump.sql

I tried the following for merging:
mysqldump --no-create-db --no-create-info -u "$DB_USERNAME" -p"$DB_PASSWORD" -h "$DB_HOST" "development" > dump.sql

Which I thought would do the trick but I get the following error:
ERROR 1062 (23000) at line 23: Duplicate entry '1' for key 'PRIMARY'

Any ideas where I'm falling short?

Adding this to the mysqldump will probably work:
 --insert-ignore

The error message implies that you already have a row with primary key id=1 and are trying to add it again.
Caveat:  If you have modified that row, this technique will not achieve the "merge" goal.  Please specify the "merge" more thoroughly.


Is it possible to restore sql-server bak and shrink the log at the same time? We have a bak file from a client that we have transferred to our developer offices for problem investigation. The backup is currently 25GB and the restored database is about the same size however it needs 100GB to be restored. I believe this is because there database is set up to have a 75GB transaction log size. After restoring the database we can shrink the log file but is there a way to do this in the restore?

There is a do-not-ever-do-this-in-a-live-environment hack you can use where space is limited, by restoring the log file to a compressed folder. Attempt this by compressing an existing folder and restoring to it will result in an error, so you have to cheat with a symbolic link.

Create a compressed folder D:\LogCompressed\
Create a symbolic link to the compressed folder mklink /D /J D:\Log\ D:\LogCompressed\
Restore your database with the ldf file pointing at D:\Log\
Shrink the log file to an appropriate size
Detach the database, move the log file to an uncompressed folder, attach

It's dirty, it's cheating, DO NOT EVER DO IT IN LIVE, but it works. Quick test of a newly created database with a 32MB log file shows it as occupying 330kb on disk when compressed, decompress the folder and on disk size is back to 32MB.


Why there is no much change in the incremental backup size of level 0 and level 1 I'm implementing incremental backup in my database. As per my knowledge after level 0 backup, level 1 backup will take only the backups of changes that are done. But in my db after level 0 full backup, level 1 backup is also taking the almost same backup size of level 0.

On Friday, level 0 backup. Backup Size: 49.8 GB

BACKUP INCREMENTAL LEVEL 0 DATABASE PLUS ARCHIVELOG;

On Saturday, level 1 backup. Backup Size: 45.2 GB

BACKUP INCREMENTAL LEVEL 1 DATABASE PLUS ARCHIVELOG;

Is there any mistake which I have done here in the RMAN command. Please help.....

But in my db after level 0 full backup, level 1 backup is also taking
  the almost same backup size of level 0.

Verify if the backup generated in level 0 exists there in the location, while level 1 backup occurs.

The level 0 backup might have got deleted or removed from the location. This might be the reason for this issue.


SSIS 2008: Illegal characters in path when trying to rename a file I have a task that should rename a file
I have a variable called RenameFileName
\\servername\csv\BACKUP\" + (DT_WSTR,4)DATEPART("yyyy",GetDate()) + RIGHT("0" + (DT_WSTR,2)DATEPART("mm",GetDate()) ,2) + RIGHT("0" + (DT_WSTR,2)DATEPART("dd",GetDate()),2) + ".csv

When I run the task I get the error
Illegal characters in path

Am I doing anything wrong? Below are a list of the variables I have tried
\\\\servername\\csv\\BACKUP\\" + (DT_WSTR,4)DATEPART("yyyy",GetDate()) + RIGHT("0" + (DT_WSTR,2)DATEPART("mm",GetDate()) ,2) + RIGHT("0" + (DT_WSTR,2)DATEPART("dd",GetDate()),2) + ".csv

\\servername\csv\BACKUP\" + (DT_WSTR,4)DATEPART("yyyy",GetDate()) + RIGHT("0" + (DT_WSTR,2)DATEPART("mm",GetDate()) ,2) + RIGHT("0" + (DT_WSTR,2)DATEPART("dd",GetDate()),2) + ".csv

"\\servername\csv\BACKUP\" + (DT_WSTR,4)DATEPART("yyyy",GetDate()) + RIGHT("0" + (DT_WSTR,2)DATEPART("mm",GetDate()) ,2) + RIGHT("0" + (DT_WSTR,2)DATEPART("dd",GetDate()),2) + ".csv"

"\\\\servername\\csv\\BACKUP\\" + (DT_WSTR,4)DATEPART("yyyy",GetDate()) + RIGHT("0" + (DT_WSTR,2)DATEPART("mm",GetDate()) ,2) + RIGHT("0" + (DT_WSTR,2)DATEPART("dd",GetDate()),2) + ".csv"

Please note that the following works:
\\\\servername\\csv\\BACKUP\\ABC.csv

This however does not work
\\\\servername\\csv\\BACKUP\\"+1+".csv

It errors as soon a I put in double quotes. Single quotes are fine and they get put in the filename
Thanks

\\\\servername\\csv\\BACKUP\\" + (DT_WSTR,4)DATEPART("yyyy",GetDate()) + RIGHT("0" + (DT_WSTR,2)DATEPART("mm",GetDate()) ,2) + RIGHT("0" + (DT_WSTR,2)DATEPART("dd",GetDate()),2) + ".csv

You almost had it here but you missed the leading and trailing quotes.
\\servername\csv\BACKUP\" + (DT_WSTR,4)DATEPART("yyyy",GetDate()) + RIGHT("0" + (DT_WSTR,2)DATEPART("mm",GetDate()) ,2) + RIGHT("0" + (DT_WSTR,2)DATEPART("dd",GetDate()),2) + ".csv

Now you're missing the leading and trailing quotes and you failed to escape your back slashes.
"\servername\csv\BACKUP" + (DT_WSTR,4)DATEPART("yyyy",GetDate()) + RIGHT("0" + (DT_WSTR,2)DATEPART("mm",GetDate()) ,2) + RIGHT("0" + (DT_WSTR,2)DATEPART("dd",GetDate()),2) + ".csv"
Now you've got the quotes but not escaping the backslashes
"\\servername\csv\BACKUP\" + (DT_WSTR,4)DATEPART("yyyy",GetDate()) + RIGHT("0" + (DT_WSTR,2)DATEPART("mm",GetDate()) ,2) + RIGHT("0" + (DT_WSTR,2)DATEPART("dd",GetDate()),2) + ".csv"
This works correctly for me in SSIS expression.

FYI
\\servername\csv\BACKUP\"+1+".csv
again you are missing the leading and trailing quotes.  However this won't work because it sees 1 as an integer and you need to convert it.
"\\\\servername\\csv\\BACKUP\\"+"1"+".csv"

The above works.
Normally when doing these sorts of expressions I do it iteratively.  Adding 1 piece to the expression at a time so if it breaks I know where it broke.  Remember you can click "evaluate expression" to see the result.
If the expression evaluates correctly but the package doesn't work then it would be another issue with the package.


How to write the `apply` function in postgresql I'm trying to figure out how to use arrays as inputs to variadic functions and to have a function resembling apply in other languages.
So for instance, what's the best way of writing apply in the following statement?
DROP FUNCTION IF EXISTS test_greatest;

CREATE FUNCTION test_greatest(arr INT[]) RETURNS INT AS $$
BEGIN
    RETURN apply(greatest, arr)
END;
$$ LANGUAGE plpgsql;

SELECT test_greatest(ARRAY[1,6,3])
-- OUTPUTS 6

I found that unnest can works by converting an array into a relation:
DROP FUNCTION IF EXISTS test_max;

CREATE FUNCTION test_max(arr INT[]) RETURNS INT AS $$
BEGIN
    RETURN max(z) FROM unnest(arr) as z;
END;
$$ LANGUAGE plpgsql;

SELECT test_max(ARRAY[1,6,3])
-- OUTPUTS 6

but I'd really like to be able to write sql functions with apply.

I'd really like to be able to write sql functions with apply ...

Short answer: You can't.
Not with PostgreSQL.

... to have a function resembling apply in other languages.

Never come across "apply", myself nor do I think it "adds" much to the code.
Given that you'll spend far more time reading [other people's] SQL than writing it, which of these is clearer / easier to understand?
return apply( greatest, arr );
return greatest( arr );

Now, I could see the attraction if you were writing your code in 'C' and so could use Function Pointers for that first argument, which can be immensely powerful, but in a SQL context?  I can't really see the benefit.


MySQL will not start Please help, I really need to recover my databases.

service mysql start start: Job failed to start

mysqld --verbose 140401 20:16:11 [Warning] Can't create test file /var/lib/mysql/developer.lower-test
140401 20:16:11 [ERROR] mysqld: File '/var/log/mysql/mariadb-bin.~rec~' not found (Errcode: 122)
140401 20:16:11 [ERROR] MYSQL_BIN_LOG::open_purge_index_file failed to open register file.
140401 20:16:11 [ERROR] MYSQL_BIN_LOG::open_index_file failed to sync the index file. 140401 20:16:11 [ERROR] Aborting

This is what I've gathered so far.

In the Linux command prompt, I ran this:

sh-4.1# perror 122
OS error code 122:  Disk quota exceeded
MySQL error code 122: Internal (unspecified) error in handler
sh-4.1#

Evidently, the first or next binlog cannot be created

Check your diskspace by running

df -h /var/lib/mysql

Clear up the disk so binlogs can be made


SQL server Read Only permission automatically for new databases When I setup my database I create a sql user that my application runs as.  The sql user gets read only access to all the databases on the server. The problem is the user won’t have read only access to any new created databases in the future. There is another app that creates these new databases for users and I don’t have a way to add my user script into the templates database created.  Is there a way to automatically update the sql user for read only for the future ones?  SQL server 2014.

Reference:
model Database

  The model database is used as the template for all databases created
  on an instance of SQL Server. Because tempdb is created every time SQL
  Server is started, the model database must always exist on a SQL
  Server system. The entire contents of the model database, including
  database options, are copied to the new database. Some of the settings
  of model are also used for creating a new tempdb during start up, so
  the model database must always exist on a SQL Server system.

Add the user to your model database db_datareader role.  All future database will mimic this privilege.

USE [model]
GO
CREATE USER [sqllogin1] FOR LOGIN [sqllogin1]
GO
USE [model]
GO
ALTER ROLE [db_datareader] ADD MEMBER [sqllogin1]
GO

Once you run this sqllogin1 user will be created in all new database and will be a member of db_datareader role.


If a query triggers a statistics update and times out are the statistics still updated? I have a fulltext query which is usually very fast but may time out when it causes a statistics update since statistics updating is very slow on this database. Usually the query "recovers" to normal speed after the statistics have been updated but I have seen a case where the query always times out which I can only explain with the fact that the statistics are never updated (or so I think).

Unfortunately I cannot reproduce the problem at this point since we switched statistics updating to “Auto Update Statistics Asynchronously” to prevent the timeout from happening in the first place (slightly out of date statistics are not a problem for us).

So my question is

Is there a guarantee that statistics are updated on a query that times out if statistics updating is set to the (default) synchronous statistics updating (Auto Update Statistics Asynchronously = false)?

I found a reference that states otherwise - if that is the case I cannot explain the continuously timing out query case.

That was actually my question at AskSSC. I should have just tested it myself as I accepted an incorrect answer.

With the following test table

CREATE TABLE StatsTest
(
a varchar(max),
b varchar(max)
)

DECLARE @VCM VARCHAR(MAX) = 'A'

INSERT INTO StatsTest
SELECT TOP 20000
       REPLICATE(@VCM,10000),
       REPLICATE(@VCM,10000)
FROM master..spt_values v1,  master..spt_values v2

And the following test code

         SqlConnection connection = new SqlConnection(...);
            connection.Open();

            SqlCommand command = connection.CreateCommand();
            command.CommandTimeout = 12;
            command.CommandType = CommandType.Text;
            command.CommandText = @"SELECT COUNT(*)
FROM StatsTest
WHERE
      a LIKE '%foo%' OR
      b LIKE '%foo%' ";
            command.ExecuteScalar();

Profiler shows the following

First it creates the stats for column b successfully (the initial SP:StmtStarting /SP:StmtCompleted pair) . Then it starts creating the stats for column a (The selected SP:StmtStarting entry in the screen shot). This entry is followed by an AUTOSTATS event confirming that the statistics on b were created then the timeout kicks in.

It can be seen that the stats creation occurs on the same spid as the query and so this also aborts the creation of stats on column a. At the end of the process only one set of stats exists on the table.

Edit

The above refers to stats creation, to test auto update of the stats I ran the above query without a timeout so both sets of stats were successfully created then updated all columns of all rows so that the stats would be out of date and re-ran the test. The trace for that is pretty similar

Finally just for completeness with SET AUTO_UPDATE_STATISTICS_ASYNC ON the trace looks as follows. It can be seen that system spids are used to perform the operation and they are unaffected by the query timeout as would be expected.


What is the purpose of having a table NOT LOGGED INITIALLY I came across this recently. When defining a table in DB2 LUW (at least 9.5 or higher), you can define it as NOT LOGGED INITIALLY.

Example from the book I read:

CREATE TABLE products (
    productID      INT,
    product_Name   VARCHAR(30)
)
NOT LOGGED INITIALLY;

The documentation I have read states that the table is not logged during execution of INSERT, UPDATE, DELETE, CREATE INDEX, ALTER TABLE, or DROP INDEX until a COMMIT statement is executed. Everything before the COMMIT is not logged. Everything after the COMMIT is.

And apparently as long as you define the table as NOT LOGGED INITIALLY, at any point you can issue an ALTER TABLE <table-name> ACTIVATE NOT LOGGED INITIALLY to put the table back into a non-logging state until a COMMIT is issued again.

They gave one example that I can see where this could be useful. The book I read stated that you could issue the following

ALTER TABLE <table-name> ACTIVATE NOT LOGGED INITIALLY WITH EMPTY TABLE;

and this will apparently delete all the data in the table without logging it. I can understand this as being desirable in a test environment where you wish to clear data and re-test without the performance overhead of logging the delete for rollback.

But beyond this case, I am puzzled. Is there any reason you would not want logging on a table outside of a test environment? What other uses are there for this kind of table?

The classic real-world example is where the table I am creating is a copy from a static external source.  If the DB fails during the initial load, it will be faster to just drop the partially loaded table and start my import operation over from the beginning, than to suffer the overhead of fully logging the transaction.

For example, I am loading my reporting environment with a list of customers from my transactional environment's backup file.


how to drop oracle 12c instance on linux I have created an Oracle 12c container db instance.  I need to drop the  instance. What's the most effective way to drop instance?

$ sqlplus / as sysdba
SQL> startup force mount exclusive restrict
SQL> drop database;


Condition On WHERE Clause I need to add a condition on the WHERE clause such that when sevnum is not null then relate the tables by premnum and sevnum. However, the code below returns an error.

SELECT *
FROM prem c, tempTable t
WHERE
  CASE WHEN t.sevnum IS NOT NULL
     THEN t.premnum = c.premnum
     AND t.sevnum = c.sevnum
  END
;

WHERE (    t.sevnum IS NOT NULL
       AND t.premnum = c.premnum
       AND t.sevnum = c.sevnum)
/* OR (t.sevnum IS NULL) */


Microsoft SQL secondary read-only DB for SSRS? I know that Microsoft SQL has AlwaysOn with a read-only secondary replica. However the cost of enterprise edition for all of our SQL servers makes this not possible. We have SSRS configured to run reports against our OLTP databases but it puts high loads on them.

Are there any other options of getting a secondary copy that could be used to point SSRS at?

Transactional replication

In theory Transactional Replication would be your go to here, but in reality it can be tricky to set up and maintain. This is very opinionated, but I don't think you would find many DBA's who really enjoy (setting up) transactional replication.

I am not saying you shouldn't, but it can get frustrating.

Log shipping

Another option could be Log shipping while keeping the databases in Standby Mode, but then you would have to choose between:

A) Getting your log restores behind.

B) Getting your connections kicked out when logs are restored.

It is not ideal, but it could work.
There are other things you would need to work around, listed here.

Backup Restore

You could restore from backup, either by using your full (+ Diff / Log) backups, and simply restoring up until your last backup.
The main advantage being that it is simple to set up and does not put a lot of strain on the server.

You could also set up side by side restores to refresh your data during reporting hours.

Check out Restore-DbaDatabase by Dbatools if you haven't yet.

The main disadvantage being that you would work on stale data.
If this disadvantage is a no-go or not depends on your requirements, hardware, database size,....


MySQL query suddenly slow down (MySQL Version 5.5.58)

I have two tables articles and comments. articles has 1K rows, while comments has 100K rows:

CREATE TABLE `articles` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `text` text COLLATE utf8_unicode_ci,
  `type` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
)

CREATE TABLE `comments` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `text` mediumtext COLLATE utf8_unicode_ci,
  `result` varchar(255) COLLATE utf8_unicode_ci,
  `article_id` int(11) DEFAULT '0',
  `user_id` int(11) DEFAULT '0',
  `spec_id` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `index_comments_on_result` (`result`),
  KEY `index_comments_on_user_id` (`user_id`),
  KEY `index_comments_on_article_id` (`article_id`),
  KEY `index_comments_on_spec_id` (`spec_id`),
)

I have a "select" query from those tables:

SELECT articles.*,
       count(DISTINCT CASE
                          WHEN s.result = 'OK' THEN s.user_id
                      END) user_ok,
       count(DISTINCT s.user_id) user_cnt,
       count(CASE
                 WHEN s.result = 'OK' THEN 1
             END) com_ok,
       count(s.id) com_cnt,
       bit_or(s.result = 'OK'
              AND s.user_id = 1) cur_user_ok,
       bit_or(s.user_id = 1) cur_user_all
FROM articles
LEFT JOIN comments s ON s.article_id = articles.id AND s.spec_id IS NULL
GROUP BY articles.id
ORDER BY articles.id ASC

This query will finish in ~100ms normally.

However, after 2~3 days of uptime, the query time will suddenly increase to about 200 seconds. This will usually last 4~6 hours before it gets back to its normal state (~100ms). If I restart MySQL in the period, there is a chance (about 50%) to solve the slowdown (temporary).

I have a backup routine run 3 times a day, which mysqldump all databases. I noticed that the moment the query time increases is almost always after the backup routine finished. But if I disable the backup routine, it still occurs.

The memory usage (RSS reported by ps) always stayed at about 500MB, and didn't seems to increase over time or differ in the slowdown period. Yet Handler_read_next increases a lot in the slowdown period: normally it is about 80K, but during the period it is 100M. SHOW PROFILES indicates that most of the time are spent in "Copying to tmp table".

Here's my my.cnf settings:

key_buffer = 16M
max_allowed_packet = 16M
thread_stack = 192K
thread_cache_size = 8
key_buffer_size = 256M
table_open_cache = 2000
sort_buffer_size = 4M
read_buffer_size = 1M
innodb_file_per_table = 1
innodb_buffer_pool_size = 2G
innodb_buffer_pool_instances = 2
innodb_log_file_size = 256M
tmp_table_size = 128M
max_heap_table_size = 512M
query_cache_size = 0
query_cache_type = 0

What are the possible reasons?

The InnoDB buffer pool is essentially a huge cache. (A variant of LRU - 'Least Recently Used'). If your working set data fits into that cache, then SELECT queries will usually be relatively fast. On the other hand, if the working set data doesn't fit into that cache, then MySQL will have to retrieve some of the data from disk (or whichever storage medium is used), and this is significantly slower.

Running mysqldump can bring huge amounts of otherwise unused data into the buffer pool, and at the same time the (potentially useful) data that is already there will be evicted and flushed to disk.

There are ways to avoid or minimise this problem:

You can manipulate system variables like innodb_old_blocks_time (increase this - 1000 = 1 second) and innodb_old_blocks_pct (default is 37 - allowed range is from 5 to 95, set a smaller value to evict data from mysqldump and similar faster). Both these variables are dynamic, so they can be given special values just before you run mysqldump, and then restored to the original values once it has completed. For details, see Making the Buffer Pool Scan Resistant.
With MySQL 5.6+ (or MariaDB 10.0+) it's also possible to run a special command to dump the buffer pool contents to disk, and to load the contents back from disk into the buffer pool again later. (See MySQL Dumping and Reloading the InnoDB Buffer Pool | mysqlserverteam.com.) This way you can still use mysqldump or other tools that "pollute" the buffer pool and then restore it afterwards.
A way to prevent that running backup is unintentionally evicting your working set data at all would be to replace your mysqldump backup method with Percona Xtrabackup or another physical backup tool that doesn't access the InnoDB buffer pool as such. Physical backup methods are also faster, and can be less disruptive than mysqldump. The disadvantage is that you'll need the exact same MySQL version and configuration on the system where the backup is restored.


The problem may stem from the way MySQL Query Optimizer makes choices as well as the way indexes are internally represented in InnoDB.
First look at the Cardinality of the Indexes. A primary key's cardinality must always be the actual row count of the InnoDB table. Now, look at the cardinality of the field1. If the index iif is less than that of the primary key, the MySQL Query Optimizer will choose the secondary index. To verify that the Cardinaliry of field1 is lower, run these queries:
SELECT COUNT(DISTINCT field1) FROM largetable;
SELECT field1,COUNT(1) fieldcount FROM largetable
GROUP BY field1 WITH ROLLUP;

Now, look at the internal representation of the indexes. A secondary index will contain two items: 1) the column value(s) being indexed, 2) the rowid from the Clustered Index (a.k.a. the gen_clust_index). Every time a column is referenced in the secondary index, a lookup of the actual row is done as well. Picture it : Two Keys Looks Up for every row in InnoDB.
Putting these two issues together, you find that a secondary index with a lower cardinality than the primary key, will still lookup the actual row using the primary key. This explains why a secondary index is chosen over the primary key and has takes twice as long or even longer to query.
Some people would disagree with this line of reasoning because I answered a question similar to this in StackOverflow (Nov 15, 2011). Although my answer was accepted, it has mixed upvotes and downvotes because some do not view the MySQL Query Optimizer and InnoDB index structure the same way.
If anyone from Percona sees this question and my answer and sees any flaw in my reasoning, please correct me so all can learn.
UPDATE 2012-04-23 12:56 EDT
The InnoDB storage engine does deep dives into the BTREE indexes to take educated guesses at the cardinality. Try setting innodb_stats_on_metadata off
[mysqld]
innodb_stats_on_metadata = 0

According to the documentation, when disabled, InnoDB does not update statistics during these operations. Disabling this variable can improve access speed for schemas that have a large number of tables or indexes. It can also improve the stability of execution plans for queries that involve InnoDB tables.


Backup a database with a huge number of tables Is there a way to backup a PostgreSQL database with a huge number of tables?

An attempt with pg_dump for a database of about 28000 tables resulted in the following error message:

pg_dump: WARNING:  out of shared memory
pg_dump: SQL command failed
pg_dump: Error message from server: ERROR:  out of shared memory
HINT:  You might need to increase max_locks_per_transaction.
pg_dump: The command was: LOCK TABLE public.link10292 IN ACCESS SHARE MODE
pg_dump: *** aborted because of error

An increase of max_locks_per_transaction to 256 instead of 64 resulted in a failure to start the server.

Anything else I can try? (PostgreSQL 9.0, Mac OS X.)

You should really increase max_locks_per_transaction. As specified in http://www.postgresql.org/docs/9.0/static/runtime-config-locks.html , changing this parameter may require to also change the System V shared memory. You have to increase that value also. In OS X this can be done as explained in http://www.postgresql.org/docs/9.0/static/kernel-resources.html#SYSVIPC (look for OS X section). I think you should really change at least shmmax.


Handling a very large MySQL database Sorry for the long post!

I have a database containing ~30 tables (InnoDB engine). Only two of these tables, namely, "transaction" and "shift" are quite large (the first one have 1.5 million rows and shift has 23k rows). Now everything works fine and I don't have problem with the current database size.

However, we will have a similar database (same datatypes, design ,..) but much larger, e.g., the "transaction" table will have about 1 billion records (about 2,3 million transaction per day) and we are thinking about how we should deal with such volume of data in MySQL? (it is both read and write intensive). I read a lot of related posts to see if Mysql (and more specifically InnoDB engine) can perform well with billions of records, but still I have some questions. Some of those related posts that I've read are in the following:

Can MySQL reasonably perform queries on billions of rows?
Is InnoDB (MySQL 5.5.8) the right choice for multi-billion rows?
Best data store for billions of rows
How big can a MySQL database get before performance starts to degrade
Why MySQL could be slow with large tables?
Can Mysql handle tables which will hold about 300 million records?

What I've understood so far to improve the performance for very large tables:

(for innoDB tables which is my case) increasing the innodb_buffer_pool_size (e.g., up to 80% of RAM).
Also, I found some other MySQL performance tunning settings here in
percona blog
having proper indexes on the table (using EXPLAN on queries)
partitioning the table
MySQL Sharding or clustering

Here are my questions/confusions:

About partitioning, I have some doubts whether we should use it or not. On one hand many people suggested it to improve performance when table is very large. On the other hand, I've read many posts saying it does not improve query performance and it does not make queries run faster (e.g., here and here). Also, I read in MySQL Reference Manual that InnoDB foreign keys and MySQL partitioning are not compatible (we have foreign keys).
Regarding indexes, right now they perform well, but as far as I understood, for very large tables indexing is more restrictive (as Kevin Bedell mentioned in his answer here). Also, indexes speed up reads while slow down write (insert/update). So, for the new similar project that we will have this large DB, should we first insert/load all the data and then create indexes? (to speed up the insert)
If we cannot use partitioning for our big table ("transaction" table), what is an alternative option to improve the performance? (except MySQl variable settings such as innodb_buffer_pool_size). Should we use Mysql clusters? (we have also lots of joins)

Thanks for your time,

Re: partitioning:

This is by far the best way to deal with large data sets. By allowing multiple indexes to run across different ranges instead of one index for the whole set, each individual index will stay at much higher quality.

If you can configure your application to maintain referential integrity itself, then you can safely drop the foreign keys. You'll have to make sure that referenced rows in child tables are updated appropriately whenever the parent row is updated. The database will no longer prevent you from messing that up, and cascade operations won't be available anymore. So you would need to program that into your application. Creating triggers to do it automatically will help.

Re: Indexing:

B+Tree indexes will start to perform poorly if the depth gets too high. The post you linked contains some good information. e.g. forget about even trying to access columns without an index.

For writes, if you have periodic content loads then it would make sense to drop the indexes before bulk insertion and recreate them afterwards. This would likely be faster than sequential individual inserts to both table and index. Partitioning would make this easier, as you could insert all data into a new partition, and then index it after.

Re: Alternative options

Use a better database. ;-) You will really start to feel the limitations of MySQL if your database grows to this scale. Other DBMSes offer a far more capable set of tools for dealing with data of this scope. Which database that is depends on your budget, use cases and contraints. MySQL may very well be "good enough", but you should definitely evaluate alternatives before diving in.

Re: Clustering

Clustering is better in some situations, worse in others. e.g. It will allow you to shard data, but sharding is just horizontal partitioning, so will have the same restrictions on foreign keys. Maintaining a cluster can also create a lot of overhead, particularly for write-intensive applications.


How can I tell WHY an insert on a certain table is slow? I know that an INSERT on a SQL table can be slow for any number of reasons:

Existence of INSERT TRIGGERs on the table
Lots of enforced constraints that have to be checked (usually foreign keys)
Page splits in the clustered index when a row is inserted in the middle of the table
Updating all the related non-clustered indexes
Blocking from other activity on the table
Poor IO write response time
... anything I missed?

How can I tell which is responsible in my specific case? How can I measure the impact of page splits vs non-clustered index updates vs everything else?

I have a stored proc that inserts about 10,000 rows at a time (from a temp table), which takes about 90 seconds per 10k rows. That's unacceptably slow, as it causes other spids to time out.

I've looked at the execution plan, and I see the INSERT CLUSTERED INDEX task and all the INDEX SEEKS from the FK lookups, but it still doesn't tell me for sure why it takes so long. No triggers, but the table does have a handful of FKeys (that appear to be properly indexed).

This is a SQL 2000 database.

Some things you can look at...

Reduce the batch size from 10000 to something smaller, like 2000 or 1000 (you didn't say how large your row size is).

Try turning on IO Stats to see just how much IO the FK lookups are taking.

What is the waiting caused by when the insert it happening (master.dbo.sysprocesses)?

Lets start here and see where we go.


Transaction log restore hourly Is it possible to restore SQL Transaction Logs to a MS SQL database every hour without having to restore the full backup first?

Can you set database to No Recovery, apply transaction Logs, then set to Recover. Do that every hour? is that possible?

Reason for question is that the vendor is sending a full database backup each morning and instead of the large backup being sent they rather send T-Logs throughout the day.

Can you set database to No Recovery, apply transaction Logs, then set to Recover. Do that every hour? is that possible?

No .. once you recover, you cannot apply transaction logs anymore. You have to start again.

What you can do is ... apply transaction log and have the db in standby using ..
 restore .. with Standby (available for read queries).

RESTORE LOG MyAdvWorks
   FROM MyAdvWorks_log1
   WITH STANDBY = 'c:\undo.ldf'
GO

Alternatively, you can also ask the vendor to compress the backups to reduce size.


SQL Server AlwaysOn FCI vs AG Doubts I have a big confusion between Always On FCI/AG. I know that for FCI you need to have shared disks and in-case of Hardware failure or OS failure or System reboot the instance with db, logins etc will fail-over to another node. Plus instance is constant through the setup. For AG i am not clear when db fail-over occur ? what if hardware fails or OS fails or System reboots ? What instance names i must configure for all nodes ? What about logins we need to take care about ?

I have a big confusion between Always On FCI/AG. I know that for FCI you need to have shared disks and in-case of Hardware failure or OS failure or System reboot the instance with db, logins etc will fail-over to another node.

Shared or replicated disks, yes. This is because the Disks "move" between servers. Each potential failover target has SQL Server installed locally and the service is started when the local node owns the resources and is given the online command. The local service starts and mounts the database files which are on the shared or replicated disks. This is why you don't need to change or replicate any SQL Server based items (logins, jobs, etc.).

  For AG i am not clear when db fail-over occur ?

Failover of the Availability Group (not an individual database like Database Mirroring) will happen when the health checks either timeout, fail, or the lease fails to be obtained or renewed. When and under what circumstances is entirely dependent upon the flexible failover policy chosen and the type of issue the instance encounters.

  what if hardware fails or OS fails or System reboots ?

In all of those cases it is entirely dependent upon the configuration of the availability group. If you're set for automatic failover (synchronous commit + synchronized databases + automatic failover set) then in all of those situation the availability group should fail over.

  What instance names i must configure for all nodes ?

Entirely your choice. To make administration easier, I'd just use default instances.

  What about logins we need to take care about ?

Only objects at the database level will be replicated as part of the log stream. System databases cannot currently be put in availability groups. Thus, any server level object or objects not contained in the user databases will need to be replicated. Examples include: Logins, Jobs, Server Level Certificates, Proxies, Server Level Configurations, Linked Server Definitions, Security Audits, etc.


Geography Inside a CTE I have the following schema (simplified):

Locations

Id INT,
StaticLocation GEOGRAPHY

Capacities

LocationId INT
SpareCapacity INT

I'm trying to find, for each location a sum of the SpareCapacity of all locations within x miles of it.

This is complicated by the fact that there are a few CTEs which I'm joining to but the principal is the same (I hope). I'm confused because this works as a standalone query:

SELECT sum(SpareCapacity)
FROM Capacities UC
    JOIN Locations L ON L.Id = UC.LocationId
WHERE u.StaticLocation.STDistance(geography::Point(53, -2, 4326)) / 1609.344 < 10
GROUP BY u.StaticLocation

When I try to join it to a previous CTE though, I get the error:

Column 'Locations.StaticLocation' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

If I add the column to the GROUP BY clause I get another error:

The type "geography" is not comparable. It cannot be used in the GROUP BY clause.

The query inside my CTE looks like:

SELECT k.LocationId,
    (
    SELECT sum(SpareCapacity) Capacities
    FROM Capacities UC
        JOIN Location u ON u.id = UC.LocationId
    WHERE u.StaticLocation.STDistance(uk.StaticLocation) / 1609.344 < 10
    --GROUP BY u.StaticLocation
) Vacancies
FROM Cte_SomeData k -- cte which has some simple columns
JOIN Locations uk on k.LocationId = uk.Id
GROUP BY k.LocationId -- tried adding StaticLocation here without luck

If I replace uk.StaticLocation with geography::Point(53, -2, 4326) it works (but obviously I need it relative to the Location's geography not a static point. Am I missing a group by somewhere?

Using the examples from SQL Geography data type column in GROUP BY clause, I was able to group by a geography location.  See if this works for you.

--demo set up
Declare @Locations table (ID int, StaticLocation GEOGRAPHY)
Declare @Capacity table (LocationId int, SpareCapacity int)
insert into @Locations(id, StaticLocation) values
(1,geography::Point(41.878899,-87.636297, 4326)),
(2,geography::Point(40.749014,-73.985317, 4326))

insert into @Capacity(LocationId,SpareCapacity) values
(1,100),
(1,1000),
(2,200),
(2,2000)

--Solution
SELECT GEOGRAPHY::STGeomFromText(L.StaticLocation.STAsText(), 4326) as StaticLocation
    ,sum(SpareCapacity) as SumSpareCapacity
FROM @Capacity UC
JOIN @Locations L
    ON L.Id = UC.LocationId
--WHERE u.StaticLocation.STDistance(geography::Point(53, -2, 4326)) / 1609.344 < 10
GROUP BY L.StaticLocation.STAsText()

| StaticLocation                                 | SumSpareCapacity |
|------------------------------------------------|------------------|
| 0xE6100000010C821FD5B0DF5F444057CC086F0F7F52C0 | 2200             |
| 0xE6100000010C59BE2EC37FF04440BF620D17B9E855C0 | 1100             |


Error 9001, Severity: 21, State: 1 The log for database 'DB_NAME' is not available I have a SQL Server 2008 R2.

I got a mail that the backup had failed, I opened the SQL log viewer and started browsing.
And i found these two errors a bunch of times.

error 1:

Date        31-01-2013 13:20:30
Log     SQL Server (Current - 01-02-2013 05:13:00)

Source      spid20s

Message Error: 9001, Severity: 21, State: 1.

error 2:

Date        31-01-2013 13:20:30
Log     SQL Server (Current - 01-02-2013 05:13:00)

Source      spid20s

Message
The log for database 'DB_NAME' is not available. Check the event log for related error messages. Resolve any errors and restart the database.

After a little googling, I had read a few times that the errors indicated that the DB or log file maybe corrupt.
I read that I could confirm it, by running a DBCC CHECKDB 'DB_NAME', so i did, and here are the last to lines of the output:

CHECKDB found 0 allocation errors and 0 consistency errors in database 'DB_NAME'.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.

The output shows that the DB isn't corrupt. But can the log file still be corrupt?

I also read that one solution might be run something called "Emergency Mode Repair" to fix the corrupt files. But some users did NOT recommend it.

Could another solution be to delete the log file completely and then create a new?.

These symptoms sometimes occur when AutoClose in database settings is turned on. Deactivate it and try again. I had the same errors with 2008R2 Express Edition vor a Sharepoint Foundation 2010 database instance a short time ago. I investigated and found for example:

http://www.sqlservercentral.com/Forums/Topic1037190-1550-1.aspx
http://social.msdn.microsoft.com/Forums/sqlserver/en-US/acfacc5e-bf1e-474d-825e-9eff927e0311/the-log-for-database-xtrans-is-not-available
https://stackoverflow.com/questions/15771371/the-log-for-database-x-is-not-available

Then I changed "AutoClose" to off and the troubles had gone. Before that, I had to take the affected databases offline and online again.

The errors occured everytime a scheduled job did a DBCC CHECKDB. A manually started check of the databases did not show any error.


Managing timezones for user action datetimes I hope this is the right place to ask the following question, and I make enough sense what is my concern.

I have a table: mood_tracker, where I save about the feeling user has each day.

CREATE TABLE `mood_tracker` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `mood_id` int(10) unsigned NOT NULL,
  `user_id` int(10) unsigned NOT NULL,
  `mood_date` date NOT NULL,
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `mood_tracker_user_id_mood_date_unique` (`user_id`,`mood_date`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci

The thing is that user can use tracker app for each single day only once, so I depend on mood_date date field. But for example if I save date as UTC (any single static timezone) it will not work because users will be using different timezones.

My thing is I just got messed up with timezones, I'm building huge application where dates and times are very important things.

I'd really love to hear some suggestions how I can build schemas right way to make timezones flexible (adjust to user's timezone setting).

Thanks a lot.

Which do you want?  Let's say you and I are in different timezones.

Should my 10:00 should show as "10:00" to you?  If so, use DATETIME.

Should my 10:00 should show as "17:00" to you because we are 7 timezones apart?  If so, use TIMESTAMP.

The only requirement is that our client machines be honest about what timezone we are each in.  (And some config stuff.)

Meanwhile, get rid of the useless id and promote the UNIQUE composite index to be the PK.


How do I import updates to unique data in MySQL I have a reasonably large MySQL database that is an import of CSV values from a public data repository.  It has come to my attention lately that they occasionally update (ie. correct) data, usually from records published within the last few months.  And, when I update my data, I only pull the last few months (not the entire database again).

I'm trying to figure out a way to import the recent data on a regular basis where it will OVERWRITE the current data with the new data if it conflicts.  I have previously been doing this using the IGNORE command, to avoid conflicts, but that obviously keeps the original values.

I'd also prefer not to simply overwrite the entire database each month as it is a large file.  So importing is a pain at over a gig of data.

So, is there a command like IGNORE that I can use that will have the opposite effect? One that will replace all columns that match unique keys (and thus cause a conflict) with the data from the NEWER import. Or a similar strategy?

The opposite for INSERT IGNORE is INSERT ON DUPLICATE KEY UPDATE

Refer to the chapter 14.2.5.3 of MySQL reference for further details.


How can I edit the content of a table in the data output of a query in pgAdmin III? I wonder whether I can edit the content of table in the data output of a query in pgAdmin III? The query is a select * from [...]. MySQL Workbench allows users to edit the content of table in the data output of a query, but I fail to see the same option in pgAdmin III.

Agan, Franck, you have discovered another (serious this time) lacuna in the pgAdmin3 tool. Perhaps you can also put in a feature request for this also.

I tested it on Windows and Linux. If you provide a link here to any feature request, I would only be too glad to put in a "me too" on the wish list (same goes for the other one).

If data is editable, then the user should be allowed to edit the data in a grid like that.


How can I migrate a sybase database to mysql? I've been tasked with migrating a fairly complex legacy Sybase database into a MySQL database and I have no idea where to start.  I can't use any off the shelf software, so I'm going to have to write some sort of script that takes all the info out of the Sybase database and puts it into MySQL.  Does anyone have any resources or thoughts on how to do this quickly, as I have very little experience with Sybase or MySQL.

Having no experience with Sybase, I suppose I would start by exporting the database schema into a text file, then go through and modify the CREATE TABLE statements manually until I am able to load the schema into MySQL.

Next, dump the data from each sybase table into CSV format file and then in the MySQL database, use LOAD DATA INFILE.

Depending on your database size, this could be a painfully slow option, but it is 'easy'.


Update a column to same value for all rows in a group of the group by I have the following table:

name
key
uuid

na1
k1
NULL

na2
k2
NULL

na3
k1
NULL

If two or more rows have the same key value then I want to update the uuid value to the same uuid for those rows. If the key is unique then that row should have a unique uuid value.
Following is the desired outcome:

name
key
uuid

na1
k1
8274e89f-b119-4326-814d-4a864bbbe207

na2
k2
9a6c5f68-a3c2-4250-ac31-cc8c86a6440c

na3
k1
8274e89f-b119-4326-814d-4a864bbbe207

Any ideas how to achieve this?

Something like this, may be
with u (key, uuid) as (
select distinct on (key) key, gen_random_uuid() as uuid from test
)
update test t1 set uuid = (select u.uuid
  from u
  where u.key = t1.key)


if compact is required after move chunk I have mongodb (wired tiger) with two shards.
I added third shard and enabled the balancer.

I can see the logs where the data chunks are getting moved to the new shard.
After the movement of chunk where the disk space of old shards( from which data is moved) will be returned to the OS or compact command has to be executed.

As at MongoDB 4.0, chunk migration for a sharded collection does not automatically run compact or free disk space when complete. However, storage used by documents that have been migrated is marked as available for reuse for new data insertion/updates. An underlying assumption is that you will continue to add data to shards over time, so disk space does not need to be aggressively freed.

If you do want to free up disk space after significant changes in storage usage (for example, removing or migrating a large number of documents) you can manually compact collections.

It is important to note that the  compact command currently blocks operations for the database being compacted, so is not advisible to run on a production system without careful consideration of impact. One approach would be to compact each of your original shards using rolling maintenance (compacting one secondary at a time). If you have a significant number of collections to compact, you could also re-sync secondaries (one at at time) to rebuild all data files without downtime.


Time Series Table ( Many Columns ) Design Recommendations I am storing Time Series Data in a Postgresql database.

I am wondering what the performance ramifications are of the following two proposed design patterns.

Option 1:
    A single table with a timestamp and many columns ( maybe 100 ) containing various measurement data.

TABLE measurement (id, timestamp, measurementA, measurementB, measurementC ...)

Option 2:

Two tables. One with an id and timestamp and another table which contains a reference to the measurement table and then multiple fields describing the recorded data.

TABLE measurement (id, timestamp)
TABLE measurement_data (id, measurement_id, description, type, value)

I like the idea of option two. As my table structure is much smaller and simpler. However I have concerns of the effects of queries on the table. Will there be a huge performance hit for a simple query such as the following?

Using Option 1s Design

SELECT measurementA
FROM measurement
WHERE timestamp > X AND timestamp < Y

Using Option 2s Design

SELECT value
FROM measurement_data
WHERE description = 'measurementA'
  AND measurement_id IS IN (SELECT id
                            FROM measurement
                            WHERE timestamp > X AND timestamp < Y )

(type of option 2 is not represented in option 1. Makes the comparison slightly unfair, but hardly matters.)

The major difference between the two options is storage size. Assuming datatype real for your measurements and bigint for IDs (for lack of info), the row size of option 1 is:

8 + 8 + 100 x 4 + 24 + 4 = 444 bytes.

While the same occupies with option 2:

8 + 8 + 24 + 4 = 44 bytes in table measurement. Plus ...
(8 + 8 + 13 + 4 + 4 + 24 + 4) x 100 = 6500 bytes in measurement_data.
So 6544 bytes (!) total in option 2 for one row in option 1.

Plus you need more indexes in option 2, half of which are bigger by the same factor than in option 1 as well. Seems hopeless. Size matters.

OTOH the query for option 2 retrieves only rows with 65 bytes per hit in measurement_data, but it adds the overhead of at least an additional index-only scan on measurement. Still might prevail for a large number of hits and clustered (more considerations!) data. And updates should be cheaper, producing less table bloat - but such a design typically does not see many updates. Many related considerations depending on the complete picture.

In any case, ditch option 2 which stores some attributes redundantly over and over. Consider option 3 instead, a classical m:n relationship:

TABLE measurement (measurement_id bigint PK, ts timestamp)
TABLE measurement_kind (measurement_kind_id int PK, type int?, description text)
TABLE measurement_data (measurement_id bigint, measurement_kind_id int, value real, PK (measurement_id, measurement_kind_id))

Still 44 bytes for measurement. Plus:
(8 + 4 + 4 + 24 + 4) * 100 = 4400 bytes in measurement_data
So 4444 bytes total in option 3 for one row in option 1.

(And a negligible 100 x (4 + 4 + 13 + 3 + 24 + 4) = 5200 bytes once --> 8k mini-table for measurement_kind.)

The equivalent query to go with it:

SELECT md.value
FROM   measurement m
CROSS  JOIN (
   SELECT measurement_kind_id
   FROM   measurement_kind
   WHERE  description = 'measurementA'
   ) mk
JOIN   measurement_data md USING (measurement_id, measurement_kind_id)
WHERE  m.ts > X
AND    m.ts < Y;

Resumé: Maybe option 1, maybe option 3, never option 2.

Related (suggested!):

How to implement a many-to-many relationship in PostgreSQL?
Configuring PostgreSQL for read performance


PostgreSQL - Generate unique name on insert based on other columns I have this table:

create table people (
    id serial primary key,
    name varchar,
    surname varchar,
    uname varchar unique
);

I want to generate the value of uname on insert, based on name and surname values, appending a number if there is a duplicate, for example:

insert into people (name, surname) values ('John', 'Doe');
insert into people (name, surname) values ('John', 'Doe');
insert into people (name, surname) values ('Sarah', 'Doe');
insert into people (name, surname) values ('Sarah', 'Doe');

will produce

 id | name  | surname | uname
----+-------+---------+----------
  1 | John  | Doe     | JohnDoe
  2 | John  | Doe     | JohnDoe1
  3 | Sarah | Doe     | SarahDoe
  4 | Sarah | Doe     | SarahDoe1

Is it possible to do it with PostgreSQL?

You can achieve this with triggers and trigger procedures (trigger functions). Read Tutorial on triggers and from PostgreSQL documentation 40.9. Trigger Procedures. The following is the code for both the procedure and the trigger (there might be even more elegant ways to form the procedure):

CREATE OR REPLACE FUNCTION create_people() RETURNS trigger AS $$
    DECLARE
    u_num integer;
    BEGIN
    u_num := (SELECT count(*) FROM people WHERE NEW.name = people.name AND NEW.surname = people.surname);
    NEW.uname := NEW.name || NEW.surname || CASE u_num WHEN 0 THEN '' ELSE u_num::text END;
    RETURN NEW;
        END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER create_people BEFORE INSERT ON people
    FOR EACH ROW EXECUTE PROCEDURE create_people();

DISCLAIMER: As mentioned in the comments you should carefully plan on how to handle the possible deletes and updates on uname before using a solution based on this trigger on any production environment.