Copy & rename db so I can attach both copies I have a db called 'mysite_db' on a server and I have created a copy on my local machine which is also 'mysite_db'.  I would like to attach the db (from my local machine) to the server which already has this db name. Meaning I have to change my local db's name (I guess I also need to change the physical file).  I'm lost with how to do that. Can you please guide me?

My goal is to load the 2 databases on the server for example: 'mysite_db' and 'myNewSite_db'

If You want to use the same directory for your mdf\ldf files You will have to (physically) rename them.
1. Detach files from local PC
2. Rename mdf and ldf files
3. Copy\Move the files to the server directory where the original Database is stored.
(In My example I used D:\Data for mdf and E:\Logs for ldf, change the path before running the script)
4. Use this code:

USE [master]
GO
CREATE DATABASE [myNewSite_db] ON
( FILENAME = N'D:\Data\myNewSite_db.mdf' ),
( FILENAME = N'E:\Logs\myNewSite_db_log.ldf' )
FOR ATTACH
GO

5. If You want to change the logical file name to be the same as the physical one use:
(Assuming the original logical file name was "mysite_db")

USE [myNewSite_db]
GO
ALTER DATABASE [myNewSite_db]
    MODIFY FILE (NAME=N'mysite_db', NEWNAME=N'myNewSite_db')
GO
ALTER DATABASE [myNewSite_db]
    MODIFY FILE (NAME=N'mysite_db_log', NEWNAME=N'myNewSite_db_log')
GO

Good luck,
Roi


Why can't we write ddl statement directly into the PL/SQL block Why can't we write ddl statements directly in PL/SQL block, for example when i write

CREATE OR REPLACE PROCEDURE test IS
BEGIN
    truncate table table_name; // error
END test;
/

But,

CREATE OR REPLACE PROCEDURE test IS
BEGIN
    execute immediate 'truncate table table_name'; // works fine
END test;
/

Why second one executed successfully ?

As it says in the documentation:

Only dynamic SQL can execute the following types of statements within PL/SQL program units:

Data definition language (DDL) statements such as CREATE, DROP, GRANT, and REVOKE

A TRUNCATE operation is DDL.
When using EXECUTE IMMEDIATE, remember that any DDL operations you execute will implicitly COMMIT the current transaction.


On which machine should TCP Chimney Offload be disabled? I am advising someone on how to disable TCP Chimney Offload, which is enabled by default by Windows Server 2003 SP2. We are working in an environment with multiple server machines, namely the application server machine which communicates with the database server machine.

This can cause communication problems between application and database. See KB942861 and Windows Scalable Networking Pack - Possible Performance and Concurrency Impacts to SQL Server Workloads.

On which machine should TCP Chimney Offload be disabled? Application, database or both?

Bit of a carbon copy of a previous answer...

Personally, I disable it at both ends of the pipe but the problem usually manifests at the database end (due to the comparatively high traffic). As described in this MSDN blog post, I would start with disabling in the OS with:

netsh int ip set chimney DISABLED

It may be necessary to disable the features at the card driver level in some cases. It certainly won't hurt to do so by default.


Return a column per date in a range Let's say I have Table A: BookingsPerPerson

Person_Id    ArrivalDate    DepartureDate
123456       2012-01-01     2012-01-04
213415       2012-01-02     2012-01-07

What I need to achieve with a view is the following:

Person_Id    ArrivalDate    DepartureDate    Jan-01    Jan-02    Jan-03    Jan-04    Jan-05    Jan-06    Jan-07
123456       2012-01-01     2012-01-04       1         1         1         1
213415       2012-01-02     2012-01-07                 1         1         1         1         1         1

The system is for events, so each hotel booking could take anything between 1 to 15 days but no more than that. Any ideas would be very much appreciated.

You can use the PIVOT function to perform this query.  My answer will include both a Static and dynamic version because sometimes it is easier to understand it using a static version.

A Static Pivot is when you hard-code all of the values that you want to transform into columns.

-- first into into a #temp table the list of dates that you want to turn to columns
;with cte (datelist, maxdate) as
(
    select min(arrivaldate) datelist, max(departuredate) maxdate
    from BookingsPerPerson
    union all
    select dateadd(dd, 1, datelist), maxdate
    from cte
    where datelist < maxdate
)
select c.datelist
into #tempDates
from cte c

select *
from
(
    select b.person_id, b.arrivaldate, b.departuredate,
        d.datelist,
        convert(CHAR(10), datelist, 120) PivotDate
    from #tempDates d
    left join BookingsPerPerson b
        on d.datelist between b.arrivaldate and b.departuredate
) x
pivot
(
    count(datelist)
    for PivotDate in ([2012-01-01], [2012-01-02], [2012-01-03],
              [2012-01-04], [2012-01-05], [2012-01-06] , [2012-01-07])
) p;

Results (See SQL Fiddle With Demo):

PERSON_ID | ARRIVALDATE | DEPARTUREDATE | 2012-01-01 | 2012-01-02 | 2012-01-03 | 2012-01-04 | 2012-01-05 | 2012-01-06 | 2012-01-07
=====================================================================================================================================
123456    | 2012-01-01  | 2012-01-04    | 1          | 1          | 1          | 1          | 0          | 0          | 0
213415    | 2012-01-02  | 2012-01-07    | 0          | 1          | 1          | 1          | 1          | 1          | 1

The dynamic version will generate the list of values to transform to columns:

DECLARE @cols AS NVARCHAR(MAX),
    @query  AS NVARCHAR(MAX)

;with cte (datelist, maxdate) as
(
    select min(arrivaldate) datelist, max(departuredate) maxdate
    from BookingsPerPerson
    union all
    select dateadd(dd, 1, datelist), maxdate
    from cte
    where datelist < maxdate
)
select c.datelist
into #tempDates
from cte c

select @cols = STUFF((SELECT distinct ',' + QUOTENAME(convert(CHAR(10), datelist, 120))
                    from #tempDates
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)')
        ,1,1,'')

set @query = 'SELECT person_id, arrivaldate, departuredate, ' + @cols + ' from
             (
                select b.person_id, b.arrivaldate, b.departuredate,
                    d.datelist,
                    convert(CHAR(10), datelist, 120) PivotDate
                from #tempDates d
                left join BookingsPerPerson b
                    on d.datelist between b.arrivaldate and b.departuredate
            ) x
            pivot
            (
                count(datelist)
                for PivotDate in (' + @cols + ')
            ) p '

execute(@query)

The results are the same (see SQL Fiddle With Demo):

PERSON_ID | ARRIVALDATE | DEPARTUREDATE | 2012-01-01 | 2012-01-02 | 2012-01-03 | 2012-01-04 | 2012-01-05 | 2012-01-06 | 2012-01-07
=====================================================================================================================================
123456    | 2012-01-01  | 2012-01-04    | 1          | 1          | 1          | 1          | 0          | 0          | 0
213415    | 2012-01-02  | 2012-01-07    | 0          | 1          | 1          | 1          | 1          | 1          | 1


Slow update on large table with subquery With SourceTable having >15MM records and Bad_Phrase having >3K records, the following query takes almost 10 hours to run on SQL Server 2005 SP4.

UPDATE [SourceTable]
SET
    Bad_Count=
             (
               SELECT
                  COUNT(*)
               FROM Bad_Phrase
               WHERE
                  [SourceTable].Name like '%'+Bad_Phrase.PHRASE+'%'
             )

In English, this query is counting the number of distinct phrases listed in Bad_Phrase that are a substring of the field Name in the SourceTable and then placing that result in the field Bad_Count.

I would like some suggestions on how to have this query run considerably faster.

While I agree with other commenters that this is a computationally expensive problem, I think that there is a lot of room for improvement by tweaking the SQL that you are using.  To illustrate, I create a fake data set with 15MM names and 3K phrases, ran the old approach, and ran a new approach.

Full script to generate a fake data set and try out the new approach

TL;DR

On my machine and this fake data set, the original approach takes about 4 hours to run.  The proposed new approach takes about 10 minutes, a considerable improvement.  Here is a short summary of the proposed approach:

For each name, generate the substring starting at each character offset (and capped at the length of the longest bad phrase, as an optimization)
Create a clustered index on these substrings
For each bad phrase, perform a seek into these substrings to identify any matches
For each original string, compute the number of distinct bad phrases that matches one or more substrings of that string

Original approach: algorithmic analysis

From the plan of the original UPDATE statement, we can see that the amount of work is linearly proportional to both the number of names (15MM) and the number of phrases (3K).  So if we multiple both the number of names and phrases by 10, the overall run time is going to be ~100 times slower.

The query is actually proportional to the length of the name as well; while this is a bit hidden in the query plan, it comes through in the "number of executions" for seeking into the table spool.  In the actual plan, we can see that this occurs not just once per name, but actually once per character offset within the name.  So this approach is O(# names * # phrases * name length) in run-time complexity.

New approach: code

This code is also available in the full pastebin but I've copied it here for convenience.  The pastebin also has the full procedure definition, which includes the @minId and @maxId variables that you see below to define the boundaries of the current batch.

-- For each name, generate the string at each offset
DECLARE @maxBadPhraseLen INT = (SELECT MAX(LEN(phrase)) FROM Bad_Phrase)
SELECT s.id, sub.sub_name
INTO #SubNames
FROM (SELECT * FROM SourceTable WHERE id BETWEEN @minId AND @maxId) s
CROSS APPLY (
    -- Create a row for each substring of the name, starting at each character
    -- offset within that string.  For example, if the name is "abcd", this CROSS APPLY
    -- will generate 4 rows, with values ("abcd"), ("bcd"), ("cd"), and ("d"). In order
    -- for the name to be LIKE the bad phrase, the bad phrase must match the leading X
    -- characters (where X is the length of the bad phrase) of at least one of these
    -- substrings. This can be efficiently computed after indexing the substrings.
    -- As an optimization, we only store @maxBadPhraseLen characters rather than
    -- storing the full remainder of the name from each offset; all other characters are
    -- simply extra space that isn't needed to determine whether a bad phrase matches.
    SELECT TOP(LEN(s.name)) SUBSTRING(s.name, n.n, @maxBadPhraseLen) AS sub_name
    FROM Numbers n
    ORDER BY n.n
) sub
-- Create an index so that bad phrases can be quickly compared for a match
CREATE CLUSTERED INDEX IX_SubNames ON #SubNames (sub_name)

-- For each name, compute the number of distinct bad phrases that match
-- By "match", we mean that the a substring starting from one or more
-- character offsets of the overall name starts with the bad phrase
SELECT s.id, COUNT(DISTINCT b.phrase) AS bad_count
INTO #tempBadCounts
FROM dbo.Bad_Phrase b
JOIN #SubNames s
    ON s.sub_name LIKE b.phrase + '%'
GROUP BY s.id

-- Perform the actual update into a "bad_count_new" field
-- For validation, we'll compare bad_count_new with the originally computed bad_count
UPDATE s
SET s.bad_count_new = COALESCE(b.bad_count, 0)
FROM dbo.SourceTable s
LEFT JOIN #tempBadCounts b
    ON b.id = s.id
WHERE s.id BETWEEN @minId AND @maxId

New approach: query plans

First, we generate the substring starting at each character offset

Then create a clustered index on these substrings

Now, for each bad phrase we seek into these substrings to identify any matches.  We then compute the number of distinct bad phrases that matches one or more substrings of that string.  This is really the key step; because of the way that we have indexed the substrings, we no longer have to check a full cross-product of bad phrases and names.  This step, which does the actual computation, accounts for only about 10% of the actual run-time (the rest is the pre-processing of substrings).

Lastly, perform the actual update statement, using a LEFT OUTER JOIN to assign a count of 0 to any names for which we found no bad phrases.

New approach: algorithmic analysis

The new approach can be divided into two phases, pre-processing and matching.  Let's define the following variables:

N = # of names
B = # of bad phrases
L = average name length, in characters

The pre-processing phase is O(N*L * LOG(N*L)) in order to create N*L substrings and then sort them.

The actual matching is O(B * LOG(N*L)) in order to seek into the substrings for each bad phrase.

In this way, we have created an algorithm that does not scale linearly with the number of bad phrases, a key performance unlock as we scale to 3K phrases and beyond.  Said another way, the original implementation takes roughly 10x as long as we go from 300 bad phrases to 3K bad phrases.  Similarly it would take another 10x as long if we were to go from 3K bad phrases to 30K.  The new implementation, however, will scale up sub-linearly and in fact takes less than 2x the time measured on 3K bad phrases when scaled up to 30K bad phrases.

Assumptions / Caveats

I am dividing the overall work into modestly sized batches.  This is probably a good idea for either approach, but it is especially important for the new approach so that the SORT on the substrings is independent for each batch and easily fits in memory.  You can manipulate the batch size as needed, but it would not be wise to try all 15MM rows in one batch.
I am on SQL 2014, not SQL 2005, since I don't have access to a SQL 2005 machine.  I have been careful not to use any syntax that is not available in SQL 2005, but I may still be getting a benefit from the tempdb lazy write feature in SQL 2012+ and the parallel SELECT INTO feature in SQL 2014.
The lengths of both the names and phrases is fairly important to the new approach.  I'm assuming that the bad phrases are typically fairly short since that is likely to match real-world use cases.  The names are quite a bit longer than the bad phrases, but are assumed not to be thousands of characters.  I think this is a fair assumption, and longer name strings would slow down your original approach as well.
Some portion of the improvement (but nowhere close to all of it) is due to the fact that the new approach can leverage parallelism more effectively than the old approach (which runs single-threaded).  I'm on a quad core laptop, so it is nice to have approach that can put these cores to use.

Related blog post

Aaron Bertrand explores this type of solution in more detail in his blog post One way to get an index seek for a leading %wildcard.


How to store international phone numbers in PostgreSQL? What is the correct way to store phone number data after applying a new mask for user input (example for UK; 44 07400 123456) if the type of the existing column is character varying(32)? Does storing it as it is generated by mask has a drawbacks (especially space characters)?

In other words, what are some pros and cons of storing it as formatted or unformatted might be better to asking for my case, since I'm not dealing with the type of the column and I'm going to stay with the same type. Especially, what I'm really care about is that is, what would be best practice? +4407400123456 or 44 07400 123456 (with spaces).

Generally speaking, the presentation layer is where formatting should occur.  If spaces are only useful for presentation to the user, then reformat the phone number in the client app, and remove the spaces prior to storing them, since that will save space in the database.

Some of the questions about how to store phone numbers have been asked and answered here already.  See this and this.


Working with JSON data MySQL I am finding that documentation for working with MySQL JSON data is still at this point apparently at it's infancy. Either that or I'm doing it wrong.
I found several docs online, i.e. https://benjaminlistwon.com/blog/working-with-json-data-in-mysql-part-1-of-3/ and others - but neither are working for me.

I recently switched from storing the station data in separate tables per station (4,100+ tables I had to do a "dynamic" PHP query on to get the data - wow!) - to JSON for speed and performance reasons, and an added benifit of not doing 4200 table writes per hour.

To get the aggregate data it's much faster - for a single row of data I can just iterate through the JSON keys. Problem is that to pull data from 83+ rows where Key=Value, PHP says out of memory error. My guess is that means I need to match the JSON value in the query instead of in PHP.

Below: SQL dump of a JSON based data table, StationData. Weather data from various stations collected hourly.

https://drive.google.com/open?id=0B6ScTEIoK3tPbGhrZkJOZmhRUTQ

Problem I'm having is features like JSON_EXTRACT, JSON_SEARCH, and JSON_etc are just returning NULL.

For example, for Olathe Kansas, I want every data stored under {"KOJC":{"Temperature":"25", "Dewpoint":"23"}} etc etc - but nothing from any other keys. The key is KOJC.
(for giggles tried the MySQL doc example):

select JSON_KEYS('{"a": 1, "b": {"c": 30}}');
+---------------------------------------+
| JSON_KEYS('{"a": 1, "b": {"c": 30}}') |
+---------------------------------------+
| ["a", "b"]                            |
+---------------------------------------+
1 row in set (0.02 sec)

select JSON_KEYS(jsonData) from StationData;
+---------------------+
| JSON_KEYS(jsonData) |
+---------------------+
| NULL                |

etc... 85 rows blah blah

select JSON_EXTRACT(jsonData, "$.KOJC") from StationData;

+----------------------------------+
| JSON_EXTRACT(jsonData, "$.KOJC") |
+----------------------------------+
| NULL                             |
| NULL                             |

etc....
    85 rows in set (0.16 sec)

select jsonData->>'$.KOJC' as Station from StationData;
+---------+
| Station |
+---------+
| NULL    |

etc....
    85 rows in set (0.14 sec)

select * from StationData were JSON_SEARCH(jsonData, 'all', '$.KOJC');
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'JSON_SEARCH(jsonData, 'all', '$.KOJC')' at line 1

select * from StationData where JSON_EXTRACT(jsonData, "$.KOJC");
Empty set (0.15 sec)

UPDATE - I found what's happening. JSON_EXTRACT works for a non-arrayed JSON string but when working with an array I am unable to pull out array elements.

select JSON_EXTRACT('{"KOJC":{"Tmp":"25"}}', '$.KOJC');                          +-------------------------------------------------+
| JSON_EXTRACT('{"KOJC":{"Tmp":"25"}}', '$.KOJC') |
+-------------------------------------------------+
| {"Tmp": "25"}                                   |
+-------------------------------------------------+
1 row in set (0.01 sec)

^ that works
But the below does not when presented with a array:

select JSON_EXTRACT('[{"KOJC":{"Tmp":"25"}}, {"KTWX":{"Tmp":"32"}}]', '$.KOJC');
 +--------------------------------------------------------------------------+
| JSON_EXTRACT('[{"KOJC":{"Tmp":"25"}}, {"KTWX":{"Tmp":"32"}}]', '$.KOJC') |
+--------------------------------------------------------------------------+
| NULL                                                                     |
+--------------------------------------------------------------------------+
1 row in set (0.00 sec)

select JSON_EXTRACT('[{"KOJC":{"Tmp":"25"}}, {"KTWX":{"Tmp":"32"}}]', '$[KOJC][*]');
ERROR 3143 (42000): Invalid JSON path expression. The error is around character position 2.

select JSON_EXTRACT('[{"KOJC":{"Tmp":"25"}}, {"KTWX":{"Tmp":"32"}}]', '$[$.KOJC][*]');
ERROR 3143 (42000): Invalid JSON path expression. The error is around character position 2.

Do not have 4100 identical tables.  There are so many issues with such, and it is repeatedly panned in this and other forums.

4100 "stations", each with, say 10 readings, per hour?  41000 is about 12/second (average), which is not very much if you had one row per datapoint per station.  One table with 3 columns: hour (or datetime), station_id, reading.  And batch the 10 rows in a single INSERT statement for a little more efficiency.

How you can fetch the data in a variety of ways -- "just temperatures at 6am on weekends" becomes a simple, and somewhat efficient query.

Alternatively, have one table with 12 columns:  hour/datetime, station_id, and 10 specific readings.  This is likely to be more efficient in all ways.  This also has the advantage that the readings can have specific datatypes for each measurement.  Temperature or humidity:  TINYINT SIGNED is 1 byte and allows -128..127 or DECIMAL(5.2) (3 bytes) for +/-999.99 instead of a generic FLOAT (4 bytes).

The alternatives probably take a similar amount of disk space.  This database will be rather big after a year; you should probably estimate the future size and think ahead about indexing, and other issues.


SSMS 18.4 Error opening database properties dialog box Azure SQL DB Managed Instance I have three databases in an Azure SQL DB Managed Instance. I am using SSMS 18.4. One database I can right-click, choose Properties, and the dialog box comes up normally. The other two databases give me the following error.

I have spent a couple of hours Googling with no results. Anyone have any ideas?

Azure support pointed me to this article which worked to cure the problem. Apparently the issue was multiple old entries in the msdb backup sets. All left over from when I was experimenting with Azure SQL MI Migration Assistant. Interestingly, the database where the Properties dialog always worked also has multiple old entries in the backup sets. So it's still a head scratcher!


Cut substring between character I want to select the 3 numbers between ":". I don't know the length of this strings and the positions of the ":". I have as an example:

SELECT 'Espacio Asignado:358898204672: Espacio Usado:355976458989: Espacio Reclamable:2921745683:' FROM DUAL;

I think that with regexp, but I don't know how.

When you execute:

select total
from (SELECT regexp_substr('Espacio Asignado:358898204672: Espacio Usado:355976458989: Espacio Reclamable:2921745683:','[^:]+', 1, level) total
      FROM DUAL
      connect by regexp_substr('Espacio Asignado:358898204672: Espacio Usado:355976458989: Espacio Reclamable:2921745683:','[^:]+', 1, level) is not null)
where   REGEXP_LIKE(total, '^[[:digit:]]+$')
/

You get all the numeric values in the string.


what replication option should I choose? Just looking for some feedback here, there are lots of replication options out there, and I'd like to try something that those more expert than I have found to be successful.

Here's my scenario:

1 SQL STD 2008 R2 SP2, few desktop clients, many web-clients edit and read;
1 SQL ENT 2008 R2 SP2, desktop clients only;

lots of SP's and triggers;

Many (but not all) tables have App-managed Primary key (not an identity!) and/or a GUID;

Many (but not all) tables that have a GUID, use that GUID in 1-n relationships with 1 or more tables;
Some app-managed tables have neither a PK or a GUID;

The schema of the app-managed tables and sp's cannot be changed in order to satisfy a replication requirement (e.g remove @@rowcount or add not for replication contraint to a PK);

Schema changes will be frequent;

I'd like to try to achieve two-way replication, as updates can occur at either end. Redundancy (hot-swap, failover) is not an issue for us, as our business strategey isn't dependent upon 5-9's uptime, instead the ability to recover from failures, which we can do very well. Real-time replication may be very limited due to horrific WAN performance, is log shipping a good solution here? The goal is to make a DB available to clients in two physcial locations whom cannot oridinarily communicate otherwise. In addition, having updates lag a few hours or even a day is not a roadblock, as long as we can force a synch if we need a more recent one. Icing on the cake would be the ability to have SQL express clients participate in this topology as well, giving our mobile clients the ability to grab a copy of the DB when they can, and check in edits when they can (again).

I look forward to hearing how others have achieved this sort of environment. Thanks!

As some of your tables don't have a primary key, and I assume they don't have a unique index on them either, you are pretty much hosed.  Anything which will be real time (transactional, merge, or peer to peer) will require either a primary key or that replication put it's own column in place called a rowguid (basically it's own guid column).

Since your schema can't be changed there aren't going to be any replication options which satisfy your requirements.


Prevent duplicates in MySql without using unique constraint I have a rules table consist of a column called priority, that must be gapless, incremental and unique.
A user must be able to re-order priority of the rules. For example, if there are 10 rules for a user and she moves the last rule to the 5th place, all of the other records between must get updated.
To prevent race condition during updates, I can use a select ... for update lock. But I can't find a way to guarantee the uniqueness of priority during inserts.
Firstly I wanted to use a deferred unique constraint, but figured out that MySql does not support this feature as Postgres do.
Then I thought about using a trigger on before insert, to calculate the priority, but I found that duplications still can show up.
What must I do? Is there any way to create a unique check that only works during inserts not updates?

Here's a complete test case, annotated:
CREATE TABLE t_rearr (
    id INT UNSIGNED NOT NULL AUTO_INCREMENT,
    priority INT NOT NULL,
    PRIMARY KEY (id),
    UNIQUE(priority)
) ENGINE=InnoDB;

INSERT INTO t_rearr (priority) VALUES (1),(2),(3),(4),(5),(6),(7),(8),(9),(10);

BEGIN;

SELECT GROUP_CONCAT(id ORDER BY priority) FROM t_rearr FOR UPDATE;
-- Expect 1,2,3,4,5,6,7,8,9,10
-- Note the FOR UPDATE

-- 3 steps to swap:
-- First, move out of way (this assumes 0 is not a valid priority)
UPDATE t_rearr SET priority =  0 WHERE priority =  5;

UPDATE t_rearr SET priority =  5 WHERE priority = 10;

UPDATE t_rearr SET priority = 10 WHERE priority =  0;

COMMIT;

SELECT GROUP_CONCAT(id ORDER BY priority) FROM t_rearr;
# Expect 1,2,3,4,10,6,7,8,9,5


group rows when row count exceeds certain count Query:

select city.name as name, sum(count) as views from profpostview
inner join professional as prof on prof.pfid = profpostview.viewerpfid
left join cityinfo as city on city.cityid = prof.cityid
where pfpid = _pfpid group by name order by views desc;

output:

**name , views**

'Ahmedabad', '14'
'Gandhinagar', '7'
'Mumbai', '5'
'Aalborg', '5'
'Rajkot', '3'
'Bhavnagar', '2'
'Baroda', '1'

required:

**name, views**

'Ahmedabad', '14'
'Gandhinagar', '7'
'Aalborg', '5'
'others', '11

explanation: I want to combine rows by adding view count when row count exceeds 3. Profpostview table is related to profpostinfo by pfpid and profpostinfo table is in turn related to cityinfo by cityid.

The final query should never return more than 4 rows. All the (summed) rows after the 3rd should be grouped into a 4th row.

This would be fairly straight forward if MySQL had implemented window functions or CTEs (they are working on it but it hasn't been announced yet in which version it will appear.) With a CTE:

with grp as
  ( select city.name, sum(count) as views
    from profpostview as ppview
      inner join professional as prof
        on prof.pfid = ppview.viewerpfid
      left join cityinfo as city
        on city.cityid = prof.cityid
    where pfpid = _pfpid
    group by city.name
  ),
the_first_three as
  ( select name, views
    from grp
    order by views desc
    limit 3
   ),
the_rest as
  ( select name, views
    from grp
    order by views desc
    limit 900000000000000 offset 3
   )
select name, views
from the_first_three

union all

select 'other', sum(views)
from the_rest

order by (name = 'other'), views desc ;

With CTEs and window functions:

with grp as
  ( select city.name, sum(count) as views,
           row_number() over (order by sum(count) desc) as rn
    from profpostview as ppview
      inner join professional as prof
        on prof.pfid = ppview.viewerpfid
      left join cityinfo as city
        on city.cityid = prof.cityid
    where pfpid = _pfpid
    group by city.name
  )
select name, views, rn
from grp
where rn <= 3

union all

select 'other', sum(views), 4
from grp
where rn > 3

order by rn ;

And while we are waiting for CTEs and window functions to be implemented, in current MySQL versiosn, we'll have to either duplicate code or use some trickery to avoid duplication. In any case, I don't think we can avoid doing the group by and summation twice. Here is one way to get the wanted output. It's essentially a rewrite of the above code 1, using derived tables instead of CTEs:

  ( select city.name, sum(count) as views
    from profpostview as ppview
      inner join professional as prof
        on prof.pfid = ppview.viewerpfid
      left join cityinfo as city
        on city.cityid = prof.cityid
    where pfpid = _pfpid
    group by city.name
    order by views desc
    limit 3
  )
union all
  ( select 'other', sum(views)
    from
      ( select city.name, sum(count) as views
        from profpostview as ppview
          inner join professional as prof
            on prof.pfid = ppview.viewerpfid
          left join cityinfo as city
            on city.cityid = prof.cityid
        where pfpid = _pfpid
        group by city.name
        order by views desc
        limit 900000000000000 offset 3
      ) grp
  )
order by (name = 'other'), views desc ;

If you want to resolve ties in some way, change the order by accordingly, eg. to order by views desc, name asc


Mark exported rows as exported I need to write an export module which marks exported rows as exported.
We currently run SQL Server Express 2012, but may switch to Oracle, so I need
a client-side approach. We are currently using ASP.NET 4.5.

I have a boolean field named "exported" which is supposed to be set to true.

My idea was to collect all affected rows IDs in an array in the client software and build something like

Metacode:

FOREACH(affectedRowsIDs as affectedRowID)
  $sql = "udpate myRows SET exported = 1 where id = $affectedRowID)
  execute $sql

The problem would be a lot of queries to run.

Different thought:

FOREACH(affectedRowsIDs as affectedRowID)
  $affRows += " , $affectedRowID"

$sql = "udpate myRows SET exported = 1 where id IN ($affRows)"

Could you please give me some feedback how you would do it?

The best solution will likely depend on your database server.  In PostgreSQL, the best way would be something like this:

 BEGIN; -- start transaction
 UPDATE myrows SET exported = true WHERE exported = false
 RETURNING *;
 -- do some work in the app to process these rows
 COMMIT; -- if successful, else ROLLBACK;

If that's not possible, then create a temp table, put the info you want to export in there, update exported based on the records in the temp table, and then commit once the transaction is complete.  Remember to clean up your temp table.


Using JOINS in QUERY clause in EXPDP I have a par file that is using JOINS in QUERY clause. But it is not working.

directory=expdp
DUMPFILE=Test_SIT_Query_1table_%U.dmp
encryption_password=oracle
parallel=8
logfile=TEST_SIT_Query_table.log
compression=all
cluster=N
METRICS=Y
query=IAO.VERIFIC:"SELECT /*+Parallel(8)*/ distinct A.* FROM IAO.VERIFIC A JOIN IAO.ELIJ B ON A.RACE_ID = B.RACE_ID WHERE B.CREATE_DT > SYSDATE - 30"
tables=IAO.VERIFIC

Below is the error I am getting.

ORA-31693: Table data object "IAO"."VERIFIC" failed to load/unload and is being skipped due to error:
ORA-00942: table or view does not exist

Could someone help what could be missing in the above par file.

I could not get the syntax you used working. We typically provide a WHERE clause in QUERY. Example:

SQL> create table t1 as select * from dba_objects;

Table created.

SQL> create table t2 as select * from dba_users;

Table created.

SQL> select count(*) from t1;

  COUNT(*)
----------
     23687

SQL> select count(*) from t1 where owner in (select username from t2 where username like 'SYS%');

  COUNT(*)
----------
     15775

Then export:

[oracle@o71 ~]$ expdp bp/bp tables=t1 query=t1:\"WHERE OWNER IN \(SELECT USERNAME FROM T2 WHERE USERNAME LIKE \'SYS%\'\)\"

Export: Release 19.0.0.0.0 - Production on Tue Mar 26 15:26:30 2019
Version 19.2.0.0.0

Copyright (c) 1982, 2019, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Starting "BP"."SYS_EXPORT_TABLE_01":  bp/******** tables=t1 query=t1:"WHERE OWNER IN (SELECT USERNAME FROM T2 WHERE USERNAME LIKE 'SYS%')"
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type TABLE_EXPORT/TABLE/STATISTICS/MARKER
Processing object type TABLE_EXPORT/TABLE/TABLE
. . exported "BP"."T1"                                   1.839 MB   15775 rows
Master table "BP"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for BP.SYS_EXPORT_TABLE_01 is:
  /u01/app/oracle/product/19.0.0/dbhome_1/rdbms/log/expdat.dmp
Job "BP"."SYS_EXPORT_TABLE_01" successfully completed at Tue Mar 26 15:26:54 2019 elapsed 0 00:00:21

[oracle@o71 ~]$

If IAO.ELIJ really exists, you could use something like this:

query=IAO.VERIFIC:"WHERE RACE_ID IN (SELECT B.RACE_ID FROM IAO.ELIJ B WHERE B.CREATE_DT > SYSDATE - 30)"


Which Table Will Get Locked With This Query INSERT INTO stats.cps
(time_stamp,entries,cust_id)
select start_time, count(*) , c_id from gb.str
where start_time between '$DATE10' and '$DATE'

group by start_time, src_id
order by start_time ASC

I had this query on a machine remote to the DB server and start_time wasn't an index so it was taking a while. I noticed on performance graphs the times it was executing there were spikes of increased load as expected that stopped appearing when I stopped the script that was executing the query.

Another application was misbehaving at those exact times, but that application used the gb.str table and not stats db where the data was inserted. The developers of that application think that this query was the reason for the poor performance of their application but gb.str shouldn't have been locked is my assumption. I ran long SELECT queries on gb.str to see if that application is getting a performance hit but it doesn't..

Would the query also lock gb.str to some extent? All the tables are TokuDB that came in with the MariaDB (10.0.22-MariaDB)

The way I interpret Transactions and Concurrency on the TokuDB wiki

  Insert select

  Serializable
  Grab read locks on all key ranges read by query
  Grab write locks on all keys written
  Repeatable read
  Grab read locks on all key ranges read by query
  Grab write locks on all keys written
  Read committed
  Grab write locks on all keys written
  Read uncommitted
  Grab write locks on all keys written

Inserts by the 3rd party application would need to update the keys and they may happen to be locked for certain rows that at the same time are selected-to-be-inserted by that query.

The way I solved it in my case which is make the query run for time slices slightly in the past so it cannot coincide with that application (which actually adds the data).


Sql Server 2012 Failover Replica in Synchronous Mode between Primary-Secondary on VPN is very slow I have installed SQL Server 2012 Clustered HA with Replica in Sync mode between two nodes connected by an high-speed VPN. The DB is encrypted. Everything works fine, but to do the replication, the HA system takes a lot of time.

The DB is made up of 90 tables of which only 10 are fast-moving. The average for the 10 tables is equal to 100,000 records. The others have from 1,000 to 25,000 records. Can you explain to me what is due to the slowness?

The Primary Server how often starts the synchronization mechanism with Secondary and the Secondary how often start the same sync with Primary server (I have chosen MASTER-MASTER HA replica)?

Because you're using Synchronous replication, all transactions have to be signed off by both servers before your query can complete. You say you have a 'high speed' VPN, but, you don't make any mention of latency or bandwidth.

I suspect that you'll never be happy with the performance of synchronous replication, due to the additional latency. You should strongly consider setting the HA replication to Asynchronous.

Source: https://msdn.microsoft.com/en-us/library/ff877931.aspx


With Postgresql v10 and WAL2JSON... Is it possible to include the database name / datname in the json? We have a set of databases that were not set up in a multi-tenant fashion. Looking to build a hub that will hold the data in a multi-tenant fashion using wal2json to pipe data from source to the hub. With each JSON object, I need to know which database it's coming from. Is this possible?

I don't think this is possible because AFAIK database name or related oid is not stored in JSON columns. But it is always possible to retrieve current database name in a SQL session with:

select current_database();


How to get all records where a joined column contains some value (at least once) Forgive my horrible description in the title. I have a table called timesheet which has one record per week per employee. There are many rows in timecard_entries for a timesheet. A timesheet can have multiple approvers because each timecard entry is connected to a project. What I need is to get all timecard entries on a timesheet that contains an approver.

Here is my query currently:

SELECT a.timesheetid AS sheet, b.timecardid AS entry, f.ControlsLead AS approver
FROM timesheet a
LEFT JOIN timecard_entries b ON a.timesheetid = b.sheet
LEFT JOIN sub_projects c ON b.subprojectid = c.subprojectid
LEFT JOIN tblCustomerPOItems d ON c.lineid = d.CUSTOMERPOITEMID
LEFT JOIN tblCustomerPOs e ON d.CustomerPO = e.CUSTOMERPOID
LEFT JOIN tblProjects f ON e.ProjectNum = f.PROJECTNOID

This returns:

sheet    entry    approver
1        68       1
1        69       3
1        70       3
1        71       3
1        72       3
1        73       1
1        74       1
2        97       1
2        98       1

So, in this case, if I want records with approver 3 I am looking to get all entries with timesheet = 1 because it contains at least one record with approver = 3

Required output:

sheet    entry    approver
1        68       1
1        69       3
1        70       3
1        71       3
1        72       3
1        73       1
1        74       1

Using the logic from the accepted answer the new query is:

SELECT a.timesheetid AS sheet, b.timecardid AS entry
FROM timesheet a
LEFT JOIN timecard_entries b ON a.timesheetid = b.sheet
WHERE a.timesheetid IN
(
    SELECT z.timesheetid FROM timesheet z
    LEFT JOIN timecard_entries y ON z.timesheetid = y.sheet
    LEFT JOIN sub_projects x ON y.subprojectid = x.subprojectid
    LEFT JOIN tblCustomerPOItems w ON x.lineid = w.CUSTOMERPOITEMID
    LEFT JOIN tblCustomerPOs u ON w.CustomerPO = u.CUSTOMERPOID
    LEFT JOIN tblProjects t ON u.ProjectNum = t.PROJECTNOID
    WHERE t.ControlsLead = 3
)

you can use a subquery in your where clause.

e.g.

SELECT sheet,
       entry,
       approver
FROM timesheet
WHERE sheet =
(
    SELECT sheet FROM timesheet WHERE approver = 3
);


(Mysql) Using Memory engine for 4.3 million row table? My new company stores an intermediate data for our loading process in a table using the memory engine. However a quite simple update (set a.id=b.id) is taking something like six hours to run.

I've checked how big the tables are and in this import they have a about 4.3 million rows of data in them. I'm certain the problem is the memory engine and the lack of index on id (there is a PK but its across 5 other attributes) based on HASH.

I'm 95% certain I need to change the table engine and index type but I want to make sure before I push for the change.

EDIT: Here is the statement that is is so slow:

select  @Extraction_id := id from extraction;

update items as a
set a.extraction_id=@Extraction_id;

Edit 2: create statement of table

CREATE TABLE `items` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `extraction_id` char(12) NOT NULL,
  `a` char(6) NOT NULL,
  `b` char(10) CHARACTER SET latin1 COLLATE latin1_bin NOT NULL,
  `c` date NOT NULL,
  `d` varchar(100) CHARACTER SET latin1 COLLATE latin1_bin NOT NULL,
  `e` varchar(255) CHARACTER SET latin1 COLLATE latin1_bin NOT NULL,
  `f` decimal(17,6) DEFAULT NULL,
  `g` decimal(17,6) DEFAULT NULL,
  `h` varchar(150) DEFAULT NULL,
  `i` varchar(150) DEFAULT NULL,
  PRIMARY KEY     (`id`,`extraction_id`,`a`,`b`,`c,`d`),
  KEY `fk_extraction` (`extraction_id`),
) ENGINE=MEMORY AUTO_INCREMENT=2343054016 DEFAULT CHARSET=latin1;

ENGINE=MEMORY is not designed for big tables.  InnoDB is well designed for virtually all use cases; switch to it.

It sounds like you need more than an ENGINE and index type change.  Please provide SHOW CREATE TABLE for the relevant tables, and the statement that is so slow.

set a.id=b.id?? -- By convention, DBAs call the PRIMARY KEY id.  It is very strange to be setting the PK of one table to the PK of another.

Chunking

One approach to UPDATEing 4M rows will less impact is to "chunk" up the problem.  I discuss details here.


NULL or NOT NULL by default? In MySQL, is it better to always allow nulls unless you know a field is required, or always use Not Null unless you know a field will contain nulls?  Or doesn't it matter?

I know in some DBMSs they say to use Not Null as much as possible because allowing nulls requires an extra bit (or byte?) per record to store the Null status.

In most DBs a NOT NULL column will be more efficient in terms of stored data for the reason you state, and also more efficient to query and index - so unless you want to allow NULLs in a column you should explicitly disallow them.

There will be a slight performance implication, as the extra NOT NULL constraints will potentially need to be checked for each row you affect with any INSERT or UPDATE but as most databases are relative write-light and read-heavy this is probably not a concern (the little extra time taken is unlikely to be noticeable at all anyway as it is a CPU-bound operation where the rest of the insert/update operation will be IO-bound and so a much more significant bottle-neck) and it gives you some "free" data checking so your code (or other people's code) can not accidentally put NULLs where other code is not expecting them and so may give incorrect results in their presence.

Edit: As Peter points out in his comment the above is a generalism and may not hold true for all DMBSs, though I'm pretty sure it does for mysql and mssql. Other complications in the area might include features like sparse tables (as implemented MSSQL 2008 for instance) which will alter the performance dynamics of (not)nullable columns.


Most common way to express cardinality in ascii I know that there are several symbols to express the cardinality between entities.

But is there a pragmatic common way to express cardinality in ascii?

Example: User -- Group. One user can have several groups and One group can have several users.

I looked at this page: https://en.wikipedia.org/wiki/Cardinality_(data_modeling)

For example: one-to-many    "order" ←→ "line item"

Here are some graphical ways to express cardinality:

https://en.wikipedia.org/wiki/Entity%E2%80%93relationship_model#Cardinalities

What is the most common way to express cardinalties?

Is there a standard/rfc for this?

The UML way (unified modeling language) is to depict the cardinality with <lower bound> .. <upper bound>, being each bound a non-negative integer and * to depict potentially infinite. You can find this on the point 7.5.4 Notation inside the documentation linked before.

However this applies generally to many different modeling diagrams and not exclusively to ERDs. When writing relationships between tables on a relational manner, the most widely used notation I found was N:M (or N to M, N,M). The way you describe the relationships is important too, since there are actually 2 cardinality/multiplicity parts that you need to specify. Check the following examples:

Order with line items. An order must have at least 1 line item and can potentially have infinite. A line item can't exist without an order and only belongs to 1 order. This would be implemented on relational databases with Order and LineItem table, with LineItem having a not null foreign key against Order.

Order to LineItem: 1 to N
LineItem to Order: 1 to 1

Employee with itself (chain of command relationship). A particular employee might have no boss (the top chief) or only 1. A boss can have no employees below, or an infinite amount. This would be implemented on relational databases with one table Employee, which has a foreign key with itself which can be null.

Boss to Employee: 0 to N
Employee to Boss: 0 to 1

Person with itself (father and mother relationships). Every person has one father and one mother. Each parent might not have children, or have many (let's say for example fathers can have infinite amount while mothers can have up to 10). The way to implement this on relational databases is through 2 fields on the same table Person, that links with itself through nullable foreign key (partial enforcement).

Person to father: 1 to 1
Father to person: 0 to N

Person to mother: 1 to 1
Mother to person: 0 to 10

Most of these relationships are usually simplified by just mentioning the upper bound of each side. So for the boss to employee it would be 1 to N and it's read as "a boss can have up to N employees, an employee can have up to 1 boss".

There are some caveats with these expressions on the implementation, depending on each database you use.

On the order example, if an order must have an existing line item when creating the record and a line item must have an existing order, then how can you load them without failing? On relational databases, first you load the Order and then it's line items, so at one point there is an order that doesn't have a line item, but this notation expresses not how it should be implemented but how these entities are related on a business level.

On the parenthood example, at some point there will be no info about a parent, otherwise you would have infinite amount of parenthood levels. So the actual implementation of the top level would be Person to father: 0 to 1.

As for the graphical example, I find the crow's foot notation the simplest to use, but there is no universal standard for this.


Creating a query to pull very specific information using four tables I have four tables, with a structure like this:

testData

This table holds all test data for every user.  Each row associates one response for a question, divided up by test.  A user can have many tests, a test can have many unique questions, a question can only have one response.

testUUID                               questionUUID                           answerUUID
===================================================================================================================
0d09eea5-4015-4777-92db-44cf48c9b3d6   fd7485fd-5c99-4d26-bdbd-a559b9e47d14   00044311-b9fe-411f-b125-61a40c090068
...(~2 million rows)

answerData

This table stores the actual answers to the questions.  There is a similarly structured questionData table, but I can work that in later.

uuid                                   answerText              answerCorrect
============================================================================
00044311-b9fe-411f-b125-61a40c090068   this is an answer       1
00044234-c820-432a-2003-239ffbbac982   this is another answer  0

testHistory

This stores data about each test a user has taken.

uuid                                   userUUID    (and lots of other columns including score)
==============================================================================================
0d09eea5-4015-4777-92db-44cf48c9b3d6   01b7f715-4142-4436-b527-26b5158578f7

userData

This stores data about the user.

uuid                                   userFirstName (etc.)
===========================================================
01b7f715-4142-4436-b527-26b5158578f7   tehbing

I need to figure out.... across all tests that a user has taken, find the questions that are missed the most so they can study those particular questions more often.  Ideally, questionUUID should be in one column and a count for the amount of times a question was missed in another column.  I can do this in PHP, but I feel that this is something the database can do much faster if structured correctly.

Indexes for the two biggest tables

testData table
Keyname         Type    Unique  Packed  Column          Cardinality Collation   Null
testQuestion    BTREE   Yes     No      testUUID        39354       A           No
                                        questionUUID    1692249     A           No
testUUID        BTREE   No      No      testUUID        34535       A           No
questionUUID    BTREE   No      No      questionUUID    11281       A           No
answerUUID      BTREE   No      No      answerUUID      54588       A           No

testHistory table
Keyname         Type    Unique  Packed  Column          Cardinality Collation   Null
PRIMARY         BTREE   Yes     No  uuid                44965       A           No
userUUID        BTREE   No      No  userUUID            5620        A           No
                                    totalQuestions      5620        A           No
                                    questionsMissed     44965       A           No
                                    testScore           44965       A           No
                                    testTimeStarted     44965       A           No
                                    testTimeCompleted   44965       A           No
testArchived    BTREE   No      No  testArchived        4           A           Yes

Using the hint Rick James provided, I came up with the following.  I'm not sure about efficiency, but it gives me the results I was looking for.

SELECT
    testData.questionUUID,
    COUNT(testData.answerUUID) AS wrongAnswerCount
FROM testData
    LEFT JOIN answerData ON answerData.uuid=testData.answerUUID
    LEFT JOIN testHistory ON testHistory.uuid=testData.testUUID
WHERE testHistory.userUUID = ?
    AND answerData.answerCorrect=0
GROUP BY testData.questionUUID
HAVING COUNT(testData.answerUUID) > 5
ORDER BY wrongAnswerCount DESC


SSIS Installation on Dedicated Server We are planning on installing SSIS 2016 on a new dedicated ETL server.

The packages will be called from SQL Server Agent (on a separate server), or from an application (again on a separate server).

From reading the Microsoft docs, it seems that we need to install Database Engine along with Integration Services on the dedicated SSIS server. Is Database Engine required on the SSIS server for our needs?

I'm confused by this: "If the ETL server does not have an instance of the Database Engine, you have to schedule or run packages from a server that does have an instance of the Database Engine". Does this also mean that the SSIS server is then redundant if run from a server that does have DB Engine?

You can install SSIS on a dedicated server. It will provide the capability of designing, executing and storing the packages. Packages would be accessible from agent service installed on other server as in your case. I see no issue in this design as per your requirement.
However this design will not use resources on dedicated SSIS server. All execution load will be on calling server with database engine.


The same First and the Last LSN I use SQL Express and my database has the following backup schedule:

Full backup - 24 hours
Differential backups - 6 hours
Transaction log backups - 30 minutes

My question is:

I have checked LSNs of transaction log backups that were made last night and discovered that one of them has the same First LSN and the Last LSN.

Why?
Was it an error?

This isn't an error. It means that there weren't any changes in the database.


MySQL - Joining table with different charset in a view I want to join tables from two different schemas. But the problem is both schemas follow different charset utf8_general_ci & utf8mb4_unicode_ci.

When i do a join on both tables, it works. But, when i create a view with same query and do a select on the view, it throws an error

Error Code: 1267. Illegal mix of collations (utf8mb4_general_ci,IMPLICIT) and (utf8mb4_unicode_ci,IMPLICIT) for operation '='

Try using CONVERT() to change the Chararcter Set first:
ON Convert(t1.f1 USING utf8mb4) COLLATE utf8mb4_unicode_ci = t2.f2


Is DB mail supported on SQL Server hosted on Azure VM (IaaS)? Is DB mail present in SQL Server hosted on Azure VM (IaaS)?

If DB mail is not supported, then what are the features available?

Database Mail is available on SQL Server on Azure IaaS and my suggestion is to configure it using SendGrid email delivery service (available on Azure Marketplace) that supplies your virtual machine with an SMTP relay. Leverage this service to setup database mail for your alerting or reporting needs.

Here you will find how to setup the SendGrid service on Microsoft Azure.

Once you have set up the SendGrid service you can configure it on your SQL Server VM as shown below.

First enable it on sp_configure:

/*
   Turn on database mail
*/

-- Select the correct database
USE [msdb]
GO

-- Just shows standard options
sp_configure
GO

-- Turn on advance options
sp_configure 'show advanced options', 1;
GO

-- Reconfigure server
RECONFIGURE;
GO

-- Turn on database xp's
sp_configure 'Database Mail XPs', 1;
GO

-- Reconfigure server
RECONFIGURE

Next, create an email account:

/*
   Creating mail account with Send Grid SMTP server
*/

-- Create a Database Mail account 1
EXEC msdb.dbo.sysmail_add_account_sp
  @account_name = 'act_Default_Email',
  @description = 'Mail account for use by all database users.',
  @email_address = 'craftydba@outlook.com',
  @replyto_address = 'craftydba@outlook.com',
  @display_name = 'SQL SERVER (IAAS-SQL16DEV)',
  @mailserver_name = 'smtp.sendgrid.net',
  @username = 'azure_d2dfaaa7a26e3f645f978bb723cd95cb@azure.com',
  @password = 'enter your unique password';
GO

-- Show the new mail accounts
EXEC msdb.dbo.sysmail_help_account_sp;
GO

In addition, you need to configure a mail profile.

/*
   Creating a mail profile
*/

-- Create a Database Mail profile
EXEC msdb.dbo.sysmail_add_profile_sp
  @profile_name = 'prf_Default_Email',
  @description = 'Profile used for administrative mail.' ;
GO

-- Show the new mail profile
EXEC msdb.dbo.sysmail_help_profile_sp;
GO

Next step is to link the email account to the profile:

/*
  Linking the mail profile to the account
*/

-- Add the account 1 to the profile
EXEC msdb.dbo.sysmail_add_profileaccount_sp
  @profile_name = 'prf_Default_Email',
  @account_name = 'act_Default_Email',
  @sequence_number = 1 ;
GO

-- Show the link between profile and accounts
EXEC msdb.dbo.sysmail_help_profileaccount_sp @profile_name = 'prf_Default_Email';

Next step, configuring database mail to give public access to the mail profile. This allows database users to send mail.

/*
   Given public access to profile
*/

-- Grant access to the profile to all users in the msdb database
EXEC msdb.dbo.sysmail_add_principalprofile_sp
  @profile_name = 'prf_Default_Email',
  @principal_name = 'public',
  @is_default = 1 ;

-- Show the new default profile
EXEC msdb.dbo.sysmail_help_principalprofile_sp

Finally use the following code to validate this solution.

/*
   Send test message
*/

-- Plain text message
EXEC msdb.dbo.sp_send_dbmail
  @profile_name = 'prf_Default_Email',
  @recipients = 'craftydba@outlook.com',
  @body = 'The stored procedure finished successfully.',
  @subject = 'Automated Success Message' ;
GO


Do I need to ANALYZE table after DROP INDEX? In our postgres db we historically have many similar indexes (e.g. is_deleted boolean) that have quite bad selectivity value. Worse than that, sometimes query planner breaks and postgres starts to use some of them instead of much more efficient indexes so we need to call ANALYZE manually to fix performance, so we decided to delete most of them.
Do we need to call ANALYZE for each table after deleting index?
According to the answer in Is it necessary to ANALYZE a table after an index has been created? postgres collects stats about actual values in table (for simple indexes) without index-related info but I couldn't find proof in docs.

No, it's not necessary. Statistics on the main table are not affected by indexes at all.
One thing to observe: expression indexes introduce new "derived" columns, and Postgres gathers separate statistics for those. Removing such indexes also removes the special statistics - which may lead to different query plans, beyond the fact that the index is gone.
Since you mention indexes on boolean columns: those hardly ever make sense and it's a good idea to remove those. boolean column typically only make sense as condition in a partial index. There is an example in the manual.


importing csv file with ragged right format and " text qualifier I'm importing csv files in ssis that are in a ragged right format, I've been able to create the columns perfectly fine but for some of the data rows it has included a " text qualifier into the data in the first column which makes the character count of each column out sync and pushes some characters over to the next columns, the " text qualifier is also found in the last column of the same row by itself instead of the value being blank.  These " text qualifiers are also not found in the original csv file, the flat file connection manager also does not let you use the text qualifier option when working with ragged right so i can't take them out that way.

The options I can think of are.

Preprocess the file to remove the double quotes using an 'execute process' task. An easy powershell script that could do this for you would be something like :
powershell -Command {cat \yourpath\stuff.txt | %{if($_ -match '"?(\w+)'){$Matches[1]} }>\yourpath\stuffout.txt}

Process the file as a single column of variable text. Use an execute script task to process each record and apply a regular expression as the one above ("?(\w+)) or one that better serves your business rules. Redirect each non-compliant record to a file to review later.

The second option is more "correct" as your etl will be prepared to report on non-compliant rows. You can expand on your regular expression to make it more specific.


Create new function by code if it doesn't exist I want to create new function by script in my database.
The script code is below:

IF Exists(Select * From sys.sysobjects A Where A.name =N'fn_myfunc' and xtype=N'FN') return;

CREATE FUNCTION fn_myfunc ()
returns varchar(10)
AS Begin
...
End

But when I execute the above script, SQL Server returns an error:

'CREATE FUNCTION' must be the first statement in a query batch.

Update Jan 2017 - SQL Server 2016+ / Azure SQL Database

SQL Server 2016 and the current version of Azure SQL Database now has the following syntax for functions, procedures, tables, databases, etc. (DROP IF EXISTS):

DROP FUNCTION IF EXISTS dbo.fn_myfunc;

And SQL Server 2016 Service Pack 1 adds even better functionality for modules (functions, procedures, triggers, views) which means no losing of permissions or dependencies (CREATE OR ALTER):

CREATE OR ALTER FUNCTION dbo.fn_myfunc ...

Both of these syntax enhancements can lead to much simpler scripts used for source control, deployments, etc.

But, if you're using...

Older versions

You need to do what SQL Server does when you script this from Management Studio:

IF NOT EXISTS (SELECT 1 FROM sys.objects WHERE type = 'FN' AND name = 'fn_myfunc')
BEGIN
    DECLARE @sql NVARCHAR(MAX);
    SET @sql = N'CREATE FUNCTION ...';
    EXEC sp_executesql @sql;
END

Or you can say:

BEGIN TRY
    DROP FUNCTION dbo.fn_myfunc;
END TRY
BEGIN CATCH
    PRINT 'Function did not exist.';
END CATCH
GO
CREATE FUNCTION...

Or you can just say:

DROP FUNCTION dbo.fn_myfunc;
GO
CREATE FUNCTION...

(Here you will get an error message if the function doesn't already exist, but the script will continue from the next GO, so whether the drop worked or not, the function will still be (re-)created.)

Note that if you drop the function and re-create it, you will lose permissions and potentially dependency information as well.


PostgreSQL - Base Backups and PITR According to the PostgreSQL documentation for producing Standalone Hot Backups:

These are backups that cannot be used for point-in-time recovery, yet
are typically much faster to backup and restore than pg_dump dumps.

It seems that all that is required to produce these kinds of backups is to use pg_basebackup utility and include the -X option, which essentially includes the required WAL files necessary to start the cluster. Why can't these backups be used for PITR? If I don't start this backup immediately, shouldn't I be able to place it in recovery mode and allow it to restore up to any point after the backup was created?

I agree that the wording is confusing.
A pg_basebackup taken with -X stream can of course be taken as a basis for point-in-time recovery.
I think the assumption here is that you take a pg_basebackup but do not have WAL archiving configured. Then the backup contains just the WAL segments required to recover to the end of the backup and reach a consistent state, but you don't have the WAL required to recover beyond that point.


The operation cannot be started by an account that uses SQL Server Authentication. SSIS Package I have a remote access in SSMS, when I try to execute an SSIS package I get, the operation can not be started by an account that uses SQL Server Authentication

How to fixed it?

Note: I can not use Windows Authentication remotely.

You cannot use a SQL Account to run an SSIS package in the Integration Services Catalog. There is logic within the CLR methods that are used to run the SSIS packages that reject non-windows authentication (as you have discovered).

As a consultant, I ran into issues where I'd use my corporate laptop plugged into a client's network. In that case, my account sdc\billinkc would not exist in the client domain. Instead, their servers would expect client\bfellows credentials to be presented. The work around is to use RunAs. Alternate reference

From my answer on deploying ispac to foreign domain...

  I created a suite of batch files that launch every process I need. They take the form of

runas /netonly:client\bfellows "C:\windows\system32\cmd.exe"

  I have one for a command prompt (above), Visual Studio, SSMS, PowerShell, PowerShell ISE and some other specialty apps that need to work with their domain.

  Using the runas approach, I've been able to deploy packages using all of the above methods (as well as deploying directly from a Visual Studio instance being run with foreign credentials).

You'd want to use ssms.exe and the correct install path to launch management studio and then SSIS packages would run.

Approach #2

If you don't have an account in the foreign domain and the only thing they are willing to do is create a sql login, then they will also need to

Create a Credential
Create a Proxy
Create a SQL Agent job that uses the proxy to run the SSIS package
Grant the sql login the rights to run the job

This will allow you to run an SSIS package in a manner that is allowed as well as present domain credentials to the required resources.


How simplify/optimize a search of keywords in two tables at the same time? I'm trying to model a function which will be used by a search engine (of cars for the example).
The last version of PostgreSQL will be installed on a Windows server (I don't know server's specifications for the moment). The default Postgresql configuration will be used.
In this search engine, you can type many words and each word can correspond to a specific car part or a tag.
The function below seems to work but i have some doubts about its performance on the long term because the 'tag' table and the 'parts' table will constantly grow. We can consider that each table will have more than a million of row.
The fields cars.id, parts.id, cars_parts, cars_tags.id are declared as primary keys so there is an index on each of them.
Each item in "tags" table contain only one word. Items in "parts" can contain many word.
How can I improve simplify it and improve its performance ?

CREATE OR REPLACE FUNCTION search(keywords text[], itemsLimit int)
    RETURNS TABLE(nbr bigint, cars_id bigint, cars_name varchar, cars_description text) AS $$
DECLARE
    potential_parts text[];
    potential_tags text[];
    keyword text;
BEGIN
    FOREACH keyword IN ARRAY keywords
    LOOP
    potential_parts := array_append(potential_parts, '%' || keyword || '%');
    potential_tags := array_append(potential_tags, keyword || '%');
    END LOOP;

    RETURN QUERY
    SELECT COUNT(*) nb, id, name, description
    FROM (
        SELECT cars.id, cars.name, cars.description
        FROM cars
        INNER JOIN cars_parts CP ON cars.id = CP.car_id
        INNER JOIN parts P ON CP.part_id = P.id
        WHERE p.name ILIKE ANY(potential_parts)

        /* Duplicates required to get cars having (part + tag) on top,
         using an order by on the count */
        UNION ALL

        SELECT cars.id, cars.name, cars.description
        FROM cars
        INNER JOIN cars_tags CT ON cars.id = CT.car_id
        INNER JOIN tags T ON CT.tag_id = T.id
        WHERE T.name ILIKE ANY(potential_tags)
    ) ids
    GROUP BY id, name, description
    ORDER BY nb DESC
    LIMIT itemsLimit;
END;
$$ LANGUAGE plpgsql;

-- Example of call :
    SELECT * FROM search(array['Fast', 'Wheel-554D'], 10);

The default Postgresql configuration will be used.

The default PostgreSQL configuration is meant to be adequate for smallish servers.  If you are worried about scalability, which you seem to be, then restricting yourself to using the default configuration is going to be a poor plan.

  WHERE p.name ILIKE ANY(potential_parts)

You are probably going to want a trgm_ops index on p.name, and similarly on T.name

You might also need to add user-space code which prevents people from searching on things of less than 3 or 4 characters.

  We can consider that each table will have more than a million of row.

Make a million rows of dummy data, and see what happens.  No use speculating when you can just do it.


Can a MySQL 5.6 master replicate to a 5.7 slave, which in turn replicates to a MySQL 8 slave? According to the docs, "MySQL supports replication from one release series to the next higher release series. For example, you can replicate from a master running MySQL 5.6 to a slave running MySQL 5.7". In theory, this also means that a 5.6 master can replicate to a 5.7 slave, which in turn replicates to a MySQL 8 slave. I'm assuming that the intermediate 5.7 server will write to its bin-logs in the 5.7 format, which will allow its 8.0 slave to replicate from it. Is that the case?

I'm doing this because I have a 5.6 instance that I want to upgrade to the latest mysql 8 with minimal downtime. I'd rather not upgrade incrementally, in order to only create a single short swapping downtime (the time it takes to turn of all writes on the master and promote the slave, and route traffic to it.)

Follow-up: we ended up doing the migration as I described in my question. We ran into no issues except minor ones related to change in behavior between version. The replication setup worked perfectly. This was done on RDS.


Does restoring a database overwrite transaction logs? I accidentally restored an sql server database from a backup file and lost all my previous data. I did not have any backup but I read that some third party software (like apexSQL) can retrieve some data by reading the transaction logs. However, if restoring the database has also overwritten the transaction log file, this operation is pointless.

So my question is: does restoring a database from a backup file also overwrite the transaction logs or just append to it.

During a Restore of an existing database, the transaction log will be completely overwritten. If data you needed to retrieve was written after the last known good backup (Full or Transaction) and you've already restored the database then the log is overwritten and the data is lost from an SQL Server perspective.

If a Server or SAN backup is taken, it could be possible to recover a copy of your original .mdf and .ldf file from here although this is generally frowned upon
For real recovery you should only use native SQL Server backups but if this is the only option to recover a production database, then you can't be faulted for that but it is not a good solution in its own right. But I would attach as a separate database and run CHECKDBs on it to make sure there is no corruption first.


Set column values based on condition statement I want to add values to a new column based on: if board_stage is less than alight_stage then service_direction equals 1, to indicate Inbound; if not, then service_direction equals 0, indicating Outbound.
The code I have tried is below. The main issue is that it keeps returning syntax errors before the CASE statement. I have also played around with WHERE and IF statements. However this returns the same syntax error in the same location, i.e. just before the condition statement.
Any tips? Many thanks
ALTER TABLE smartcardtable
ADD service_direction boolean;

UPDATE smartcardtable
SET service_direction
CASE board_stage < alight_stage THEN service_direction = '1'
ELSE '0' END;

The syntax for case is not correct and the datatype is Boolean, so you must set it as True or False

CREATE TABLE smartcardtable ("ID" INT, "board_stage" int,"alight_stage" int )

INSERT INTO smartcardtable VALUES (1,1,2)

ALTER TABLE smartcardtable
ADD service_direction boolean;

UPDATE smartcardtable
SET service_direction =
CASE WHEN board_stage < alight_stage THEN  TRuE
ELSE FALSE END;

1 rows affected

SELECT * FROM smartcardtable

ID | board_stage | alight_stage | service_direction
-: | ----------: | -----------: | :----------------
 1 |           1 |            2 | t

db<>fiddle here


MySQL: What is the maximum number of cores used by a single MySQL server? What is the maximum number of cores used by a single MySQL server in a production setting?

I recommend you MySQL 5.5 -- add this manual page and licence page and this benchmark together and you will see why. To clarify further: MySQL -- rather, the InnoDB storage engine -- will use all cores in the system but up until 5.4 it actually slowed down with a higher number of cores, Google fixed this and the first MySQL to contain it is 5.5.

This fix also can be found in the 1.0.4 plugin and newer which can be enabled in MySQL 5.1.38.


lock and get next n records from table in oracle I have a oracle table PHONE_NUMBER which has 2 columns PHONE and STATUS
PHONE_NUMBER
PHONE STATUS
1     U
2     O
3     U

U stands for used , O is open/UnUSED .
in my input I will get n as input number , which signifies the number of phones I want to reserve(update status to U from O )
I want a query that will lock(something like SELECT FOR UPDATE) the any n available row (rows that are not locked yet and having status as O )
Constraints: Table has millions of Used and unsed phones
comments:
problem is i Want n (input) rows to be locked with a particular status . i.e i have to work with rownum
I will be getting multiple concurrent calls in my service with sqli_n as input number of records to select :
so
SELECT * FROM PHONE_NUMBER WHERE STATUS='O' and rownum<=:sqli_n FOR UPDATE ;

this query wont work for me it will select only first n rows and if the first n rows are locked in 1 call the next/concurrent call will be stuck /wait until the first call updates the status to Used .
for update no wait will fail the above query
for update skip lock will also not work with row num

So, you have discovered that you can't do what you want; so you may want to do what you can by redesigning your process. One approach might be to introduce a new column, e.g. session_id, to indicate what application session, if any, is processing the row.
Each session will then start by atomically reserving a set of rows:
UPDATE phone_number SET session_id='<whatever>'
WHERE status='O' AND session_id IS NULL AND ROWNUM<=:sqli_n;
COMMIT;

It will then do whatever it needs to do and finally release the reserved rows:
UPDATE phone_number SET session_id=NULL
WHERE session_id='<whatever>';
COMMIT;

You'll likely need to revise indexes on this table.


Database Mail Configurations using gmail account After creating a profile and account using gmail account
smtp.gmail.com port 587
I get an error while trying to send a test email

  The mail could not be sent to the recipients because of the mail
  server failure. Cannot send mail to mail servers(The SMTP server
  requires secure connection or the client was not authenticated).

Command for sending

EXEC msdb.dbo.sp_send_dbmail
    @profile_name = 'Test Profile',  -- my profile name
    @recipients = 'myaddress@gmail.com',  -- my email address, the same one
                                          -- which is also configured above
    @body = 'This is a test email sent from TEST server',
    @subject = 'TEST EMAIL',
    @importance ='HIGH'

What I am doing wrong? Or should I change some settings from in my gmail account settings?
Thanks

Basically your error message says what you have to focus on.
As SSL is enabled make sure to set ON external unsafe application access in gmail setting and not to forget to refresh your account. If unsafe access is off, gmail will notify you by a message to assure that it is you trying to access and will show the way how to enable if you click yes.


MySQL create trigger after insert not inserting all values I am new to MySQL triggers. i want to insert the newly inserted booking from booking table to bookings_clone table.
PROBLEM:
All the field in bookings table are filled but in bookings_clone only these fields are getting inserted (id, uniqueid, branch_id, user_id, status, sms_status, email_status, created_at, updated_at) and rest of them are null. Any help in pointing me to right direction is highly appreciated. The table structure of bookings_clone is same as bookings.
CREATE TRIGGER:
CREATE TRIGGER after_booking_insert
AFTER INSERT ON bookings
FOR EACH ROW
INSERT INTO bookings_clone
(id, unique_id, branch_id, pos_user_id, user_id,
 user_address_id, address, postcode, city, town,
 status, order_due_date, order_due_time,
 checkout_mode, sms_status, email_status, notes,
 time_taken, accepted_at, delivery_charges,
 discount_amount, total_charges, sub_total_charges,
 initiator, created_at, updated_at, deleted_at)
 VALUES
 (NEW.id, NEW.unique_id, NEW.branch_id, NEW.pos_user_id, NEW.user_id,
 NEW.user_address_id, NEW.address, NEW.postcode, NEW.city, NEW.town,
 NEW.status, NEW.order_due_date, NEW.order_due_time,
 NEW.checkout_mode, NEW.sms_status, NEW.email_status, NEW.notes,
 NEW.time_taken, NEW.accepted_at, NEW.delivery_charges,
 NEW.discount_amount, NEW.total_charges, NEW.sub_total_charges,
 NEW.initiator, NEW.created_at, NEW.updated_at, NEW.deleted_at);

I've also tried this but the result is same
DELIMITER //
CREATE TRIGGER after_booking_inserted AFTER INSERT ON bookings
FOR EACH ROW BEGIN
    INSERT INTO bookings_clone select * from bookings where id = NEW.id;
END;//
DELIMITER ;

Bookings Table Structure:

I can't reprocuce your behaviour.

CREATE TABLE bookings(ID int AUTO_INCREMENT PRIMARY KEY,unique_id int, branch_id int, pos_user_id int, user_id int, user_address_id  int)

CREATE TABLE bookings_clone(ID int ,unique_id int, branch_id int, pos_user_id int, user_id int, user_address_id  int)

CREATE TRIGGER after_booking_inserted AFTER INSERT ON bookings
FOR EACH ROW BEGIN
    INSERT INTO bookings_clone select * from bookings where id = NEW.id;
END;

INSERT INTO bookings  VALUES(NULL,13,13,13,13,13)

SELECT * FROM bookings_clone

ID | unique_id | branch_id | pos_user_id | user_id | user_address_id
-: | --------: | --------: | ----------: | ------: | --------------:
 1 |        13 |        13 |          13 |      13 |              13

db<>fiddle here
So as long  as you don't have a [mre] without images, you should check if the tables arethe same.


PostgreSQL synchronous replication time out? I'm using PostgreSQL 9.3 with synchronous replication. I have 2 synchronous replicators and 1 async replicator. My problem is when 2 synchronous replicators are down, transaction will wait indefinitely.
I found following discussion about this,

http://postgresql.1045698.n5.nabble.com/Timeout-for-asynchronous-replication-Re-Timeout-and-wait-forever-in-sync-rep-td3293679.html

but could not found a solution. Is there a way to resolve this problem ? ( may be using tool like pg-pool )

That isn't a problem, it's by design.

If you don't want transactions to wait until a synchronous replica acknowledges a commit, then you don't want synchronous replication. You want async replication and should simply remove the synchronous_standby_names option.

What good would synchronous replication be if it gave up when no replica was reachable? "I promise your transaction is safe on another node - oh, unless the replicas are down, in which case I'll silently break my promise."

Generally what you should be doing is setting synchronous_commit to local in transactions that don't care if the commit is synchronous, which the docs say means:

  However, the special value local is available for transactions that wish to wait for local flush to disk, but not synchronous replication.

and leaving it as on for important transactions you can't afford to lose. So only those will wait until a standby is available to replicate the changes.

See also the docs on synchronous_standby_names.

Now, you might want a way to say "Wait for up to 5 minutes for a standby to acknowledge, but proceed anyway if it hasn't by then." That's not currently supported. I'm not really convinced it's useful either.


Does SQL Server place shared locks on scanned records when using REPEATABLE READ Assume a SQLCmd session which is using transaction isolation level REPEATABLE READ.

In this session I start a transaction and execute an UPDATE statement with a WHERE clause on  a non indexed column. This statement should evaluate WHERE clause for each record in the table, but only one will match.

If I examine the locks placed under this transaction after running the UPDATE statement I can only see two IX locks on Table and Page, and a X lock on the row which was updated.

My question is: shouldn't the database engine place shared locks on all the rows it read to ensure REPEATABLE READ? What if some other transaction updates a record so that it would match with my WHERE clause in the UPDATE statement, thereby violating the REPEATABLE READ.

If I execute a SELECT *, then I can see it placing S locks on each row, which are not already locked with X.

Can anyone help me understand this situation?

I've tried with both SQL Server 2008 R2 and 2012, same behavior in both.

What if some other transaction updates a record so that it would match with my WHERE clause in the UPDATE statement, thereby violating the REPEATABLE READ.

That would be SERIALIZABLE READ, not REPEATABLE. REPEATABLE only guarantees that the rows that qualified for the WHERE clause the first time will not disappear or be modified. It doesn't say anything about other rows qualifying in a second read.


Problems installing Postgis extension on Ubuntu 14.04 I have Ubuntu 14.04 with Postgres 9.3 and I'm trying to install Postgis extension. One of the most recommended and straightforward ways I found is very simple:

sudo apt-get install postgresql-contrib postgis postgresql-9.3-postgis-2.1

However, this throws an "unmet dependencies" error:

Some packages could not be installed. This may mean that you have
requested an impossible situation or if you are using the unstable
distribution that some required packages have not yet been created
or been moved out of Incoming.
The following information may help to resolve the situation:
The following packages have unmet dependencies.

postgis : Depends: libgdal1h (>= 1.9.0) but it is not installable
    Depends: libgeos-c1 (>= 3.4.2) but it is not going to be installed
    Depends: liblwgeom-2.1.3 (>= 2.0.0) but it is not going to be installed

postgresql-9.3-postgis-2.1 : Depends: libgdal1h (>= 1.9.0) but it is not installable
    Depends: libgeos-c1 (>= 3.4.2) but it is not going to be installed
    Depends: liblwgeom-2.1.3 (>= 2.1.2) but it is not going to be installed
    Depends: libproj0 (>= 4.8.0-1) but it is not going to be installed
E: Unable to correct problems, you have held broken packages.

I've seen quite a few people having this issue, but haven't found a good solution yet.

The closest question/answer to this that I found so far is this one, however it deals with Ubuntu 12.04, so I feel my question is not a duplicate.

As we can see here all the required packages are rightly updated in the official repository.
To solve the problem you can find a good start point here.


Is it any potential problem with having 2 constraints suppored by one index in Oracle? I have the following table :

CREATE TABLE action (action_id INT NOT NULL,
action_type_id INT NOT NULL,
action_date DATE NOT NULL);
CREATE INDEX IDX_ACTION_ACT_TYPE_ACT ON action(action_id, action_type_id);
ALTER TABLE action ADD CONSTRAINT PK_ACTION PRIMARY KEY (action_id)
  USING INDEX IDX_ACTION_ACT_TYPE_ACT;
ALTER TABLE action ADD CONSTRAINT UQ_ACTION UNIQUE (action_id,action_type_id)
  USING INDEX IDX_ACTION_ACT_TYPE_ACT;
-- I think the line below is irrelevant in the context of my question, but
-- in case I'm wrong...
ALTER TABLE action ADD CONSTRAINT FK_action_type_id FOREIGN KEY(action_type_id)
REFERENCES action_type(action_type_id);
-- Since action_type is a lookup added for normalization sake with no updates/deletes,
-- I don't see any value in having an index on FK column.

It's a common parent table for many detail tables that store extra information for a particular action. action_type_id is a part of unique constraint to let detail tables have FK to (action_id,action_type_id) and to enforce that each detail table stores only information it's supposed to store.

I wonder if there is any performance penalty for using the same index in 2 constraints compared to creating 2 indexes... In my opinion it should behave better than 2 indexes, but maybe I'm missing something. I'm using Oracle 10g if that matters.

Thank you for your answers.

As Nat has noted your UQ_ACTION constraint duplicates the primary key constraint.  Based on your FK usage into this table, your primary key is (action_id, action_type_id). As such it is redundant and should be removed.  If you wish your primary key to be action_id alone, then your foreign keys must reference only action_id.

I believe you were correct in modeling action_id as the primary key.  In this case change your child references.  (Having the action type id in the foreign key violates normal form and forces redundant information into the referencing tables.).  Child tables of your action table would have the action type id in their key if they apply to multiple types.

You may want a UK on action_id, but it is not necessary.  If you have such a UK with a corresponding index, then I would reverse the order on the index containing (action_type_id, action_id).  This will allow fast retrieval by action_id alone and by action_type_id where statistics indicate an index is useful.

However, to answer your question in cases where an index can be used for multiple constraints, no there is no penalty. In fact, the opposite is the case.

Maintaining indexes can be a relatively expensive operation.  Adding redundant indexes does have a penalty.  Each insert, update, or delete that changes the column in the redundant index will require additional work to update the index.

It appears you have recognized the value of not creating unused indexes.  While common, foreign key indexes are not necessary in many cases.  It is a good idea to have an index if you do deletes from the referenced table.  A surrogate index beginning with the foreign key may result in more efficient queries.  If you do frequent lookups by action_type_id, the index would be useful.  It might be more useful to have a multi-column index with other values used to filter the result set.


Can we decompose a schema into BCNF when it is not even in 1NF Given a schema r(A,B,C,D,E,F) and  a set of functional dependencies

A --> BCD
BC --> DE
B --> D
D --> A

As we can see the schema is not even in 1NF as can be read here, now the question is can we directly decompose the schema into 3NF forms or we have to first bring it to 1NF.

If we directly decompose, it will be as follows

Since A --> BCD is not a trivial relation and A is not a primary the relation breaks up into

r1(A,B,C,D) and r2(A,E,F)
and then we again break r2 because r1 is now in 3NF but not r2
then we get r1(A,B,C,D) r2(A,E) and r3(F)

But this does not make sense because we must first bring the relation into 1NF first and then decompose it, by doing that we can get two schemas r1(A,B,C,D,E) and r2(F). By this we see that r1 is now in BCNF and we need not further decompose it and r2 is also in BCNF.

Always hit it at first shot? It is not guaranteed. You apply normalization rules to remove anomalies from relations. Relations rise to a more normal form. You observe for anomalies again and if there are anomalies apply rules again. It is possible to reach most normal form possible for the relation at first move (as you know there is a limit for normalization of any relation. Ron Fagin showed that it is often impossible to achieve DKNF form) but not always. Ron Fagin's paper may help.


Is there a way to get slow-query-log-like profiling information on a single query? Specifically, I'm looking for this information:

# Query_time: 0.011922  Lock_time: 0.000088  Rows_sent: 107  Rows_examined: 663  Rows_affected: 0  Rows_read: 107
# Bytes_sent: 4152  Tmp_tables: 3  Tmp_disk_tables: 3  Tmp_table_sizes: 259964

on queries I run in the console.

We're running Percona 5.5.

I think you're looking for the "show profiles" query.  See http://dev.mysql.com/doc/refman/5.5/en/show-profiles.html


Update empty values with null I create a Job that runs the query below for all databases in a server in order to update empty values with null in all columns for all tables. The problem I'm facing is this query literally killed the sql server, was in a dev enviroment.

I notice the log for the first DataBase grew up from 6gb to 80gb and left the server without space.

Is there a better way to achieve update all empty columns with null in all Tables without crashing the server?

This query generate around 5,000 update statements for each database

I plane to run this kind of cleanup once a week.

SELECT
ROW_NUMBER ( )
    OVER (ORDER BY [object_id] DESC) AS iRow
,name,[object_id]
INTO #temp
FROM sys.Tables
WHERE
name NOT LIKE 'old%'
AND name NOT LIKE '%old'
AND name NOT LIKE '%copy'
AND name NOT LIKE 'copy%'
AND name <> 'sysdiagrams'

--SELECT * FROM #temp
DECLARE @intFlag INT,@endFlag INT

SELECT @intFlag= MIN(iRow) FROM  #temp
SELECT @endFlag= MAX(iRow) FROM  #temp

CREATE TABLE #temp2(name VARCHAR(250),tablename VARCHAR(250))

--SELECT * FROM #temp
WHILE (@intFlag<=@endFlag)
    BEGIN
        INSERT INTO #temp2
        SELECT c.name,(SELECT name FROM #temp WHERE iRow=@intFlag) AS TableName
        FROM syscolumns c
        INNER JOIN systypes t ON c.xtype=t.xtype
        WHERE id =(SELECT [object_id] FROM #temp WHERE iRow=@intFlag)
        AND isnullable = 1 AND c.xtype IN(99,167,175,231,239)
        order by c.name
        SET @intFlag = @intFlag + 1;
    END

DECLARE @SQLquery AS VARCHAR(MAX) = ''

SELECT @SQLquery +='UPDATE ['+tablename+'] SET ['+ name+'] = NULL WHERE LTRIM(RTRIM(['+name+']))='''';'
FROM #temp2

--SELECT @SQLQuery
EXECUTE (@SQLquery)

DROP TABLE #temp
DROP TABLE #temp2

I believe the problem is that you are executing all 5000 update statements as a single transaction. You need to break them up so each one is done in its own transaction. Then there are two options:

If you are in simple recovery mode (or can temporarily switch the database to simple recovery mode), then breaking them up should be enough for the database to automatically truncate the log after each transaction.
If you are in full recovery mode, you'll probably need to do a transaction log backup after each update statement to allow the log to be truncated.

So the last part of your query would look like this:

DECLARE @SQLquery AS VARCHAR(MAX)
DECLARE @name AS VARCHAR(250)
DECLARE @tablename AS VARCHAR(250)

DECLARE columns_cursor CURSOR FOR
  SELECT name, tablename
  FROM #temp2
OPEN columns_cursor

FETCH NEXT FROM columns_cursor INTO @name, @tablename
WHILE @@FETCH_STATUS = 0
BEGIN
  @SQLquery ='UPDATE ['+@tablename+'] SET ['+ @name+'] = NULL WHERE LTRIM(RTRIM(['+@name+']))='''';'
  --SELECT @SQLQuery
  EXECUTE (@SQLquery)
  BACKUP LOG db_name TO DISK = 'backup_path' -- Not needed in simple recovery mode
  FETCH NEXT FROM columns_cursor INTO @name, @tablename
END

CLOSE columns_cursor
DEALLOCATE columns_cursor
DROP TABLE #temp
DROP TABLE #temp2


Function/procedure to use dblink to fetch remote data and insert to multiple local tables Am trying to fetch data from remote db and insert into three local tables. Currently I have a query which is successfully getting the data into one table.

But now I was thinking of creating a function to do the whole procedure including inserting the data into the three tables. I guess am stuck here...

You could use a data-modifying CTE (introduced with PostgreSQL 9.1) for that to perform well. Consider the following demo:

CREATE OR REPLACE FUNCTION f_fetch3()
  RETURNS text AS
$func$

SELECT public.dblink_connect(
      'hostaddr=?.?.?.? port=5432 dbname=db user=postgres password=???');

-- Truncate tables first?
-- TRUNCATE tbl1, tbl2, tbl3;

WITH i AS (
    SELECT *
    FROM public.dblink(
        'SELECT id
               ,col1
               ,col2
               ,col3
        FROM    remote_tbl'
         ) AS d (id int, col1 text, col2 text, col3 text)
    )
    , x AS (
    INSERT INTO tbl1
    SELECT id, col1
    FROM   i
    )
    , y AS (
    INSERT INTO tbl2
    SELECT id, col2
    FROM   i
    )
INSERT INTO tbl3
SELECT id, col3
FROM   i;

/* -- Analyze tables?
ANALYZE tbl1;
ANALYZE tbl2;
ANALYZE tbl3;
*/

SELECT public.dblink_disconnect();

$func$ LANGUAGE sql VOLATILE SECURITY DEFINER
SET search_path=myschema, pg_temp;

ALTER FUNCTION f_fetch3() OWNER TO postgres;
REVOKE ALL ON FUNCTION f_fetch3() FROM public;

Call:

SELECT f_fetch3();

Tested with PostgreSQL 9.1.4.

If you put your password here, I would create that function in a separate schema and remove all access from schema and function from the general public.

You obviously have dblink installed, but for the general public: the extension has to be installed once per database:

CREATE EXTENSION dblink;


DB2 - Why new multiple C000000x subfolders are created in Archive Log folder? After switching to Archive mode (from Circular mode to keeping archive logs on the disk) we had C0000000 folder where archive logs were copied.
Now I noticed that folders C0000001, C0000002, C0000003 and C0000004 are created where C0000001 and C0000002 and C0000003 have only one LOG file.
Archive logs keep coming to C0000004 sub folder.

Time when those folders are created are some random times (for example it is not the time when online backup was performed).

Why those C000000x folders are created and is this regular behavior or we should be concerned?

Thank you

This is completely normal.  Each of these directories are for each unique log chain.  If you are familiar with software version control, each log chain is like a branch.

A new log chain is created each time you restore a database and roll forward to a point in time other than end of logs.

Here is why:  You have a database, SAMPLE.  It has reached the log file S007500.LOG.

If you restore the database back to the point in time that corresponds to the log file S007000.LOG, what should DB2 do with the fact that you now have 2 sets of log files with the names S007001.LOG ... S007500.LOG?  They represent 2 unique sets of transactions, and they are called log chains.

So, when you create a database, DB2 creates the first log chain, C0000001.  If you restore the database, it creates C0000002, etc.

By preserving these log chains, DB2 gives you the ability to restore each unique series of transactions for a database.


How to insert values into a table from a select query in PostgreSQL? I have a table items (item_id serial, name varchar(10), item_group int) and a table items_ver (id serial, item_id int, name varchar(10), item_group int).

Now I want to insert a row into items_ver from items. Is there any short SQL-syntax for doing this?

I have tried with:

INSERT INTO items_ver VALUES (SELECT * FROM items WHERE item_id = 2);

but I get a syntax error:

ERROR:  syntax error at or near "select"
LINE 1: INSERT INTO items_ver VALUES (SELECT * FROM items WHERE item...

I now tried:

INSERT INTO items_ver SELECT * FROM items WHERE item_id = 2;

It worked better but I got an error:

ERROR:  column "item_group" is of type integer but expression is of type
character varying
LINE 1: INSERT INTO items_ver SELECT * FROM items WHERE item_id = 2;

This may be because the columns are defined in a different order in the tables. Does the column order matter? I hoped that PostgreSQL match the column names.

Column order does matter so if (and only if) the column orders match you can for example:

insert into items_ver
select * from items where item_id=2;

Or if they don't match you could for example:

insert into items_ver(item_id, item_group, name)
select * from items where item_id=2;

but relying on column order is a bug waiting to happen (it can change, as can the number of columns) - it also makes your SQL harder to read

There is no good 'shortcut' - you should explicitly list columns for both the table you are inserting into and the query you are using for the source data, eg:

insert into items_ver (item_id, name, item_group)
select item_id, name, item_group from items where item_id=2;

dbfiddle here


Decomposition in 3NF using synthesis I have an assignment at the university and it is about decomposition. These are the relation and FDs.

V={ABCDEF},F={AB→CD,ABDE→F,BC→A,C→DF}

We need to find candidate keys, canonical cover(basis) and decomposition in 3NF with dependency preservation and lossless-join.

-I found that our candidate keys are: {ABE, BCE}

-Basis=  {AB→C,BC→A,C→DF}

-But I am not really sure about the decomposition in 3NF and really confused. This is my solution:

Minimal Basis R0= {AB→C,BC→A,C→DF}
Step 1: Generation
R1= (ABC)
R2=(BCA)
R3= (CDF)
Step 2: If none of the relation schemas in Ri contains a candidate key of R0 Create new relation schema Rj contains attributes form a key of R0

Candidate Keys={ABE,BCE}
R4= (ABE)
R5= (BCE)

Step 4: Eliminate relational schemas that are included in others (R1 and R2)
Result: Z= {R1, R3, R4, R5)

Do R4 and R5 relations make sense? What is the accurate decomposition in 3NF?

The problem is in your application of step 2:

  If none of the relation schemas in Ri contains a candidate key of R0 Create new relation schema Rj contains attributes form a key of R0

This means that you must add a new relation (but only one!) that contains one of the original candidate keys (any one!).

So correct decompositions in 3NF are {R1, R3, R4} or {R1, R3, R5}. You can chose one of the two as result of the question (and this means yes, the synthesis algorithm can produce different decompositions).

Finally we can note that this step is used to guarantee the lossless-join property (while the dependency preservation is guaranteed by the previous steps, since all the decompositions are considered). In fact a theorem exists that says that if a decomposition preserves the dependencies and has at least a relation which contain one of the original candidate keys, than this decomposition preserves the data (i.e. is a lossless-join decomposition).


Physical or logical backup takes more space when backing up same MySQL database? Physical backup is copying the data files that belongs to database. Logical backup is using SQL statements.
So physical or logical backup takes more space when backing up same MySQL database?

If you backup the files by copying the directory tree, the backup is the same size as the original.
If you use mysqldump (or similar tool) to backup, that will contain CREATE and INSERT statements to recreate the database from scratch.  This can be larger or smaller, depending on the data involved.
An example of "smaller": a BIGINT takes 8 bytes in a table, but if the value is 1, it will take about 2 bytes in a logical dump (1 and a comma).