Days in between two dates group by month

First I'll create a very abbreviated version of a calendar table that stores just month start and end dates.

CREATE TABLE #LAZY_DATE_DIM (
    MONTH_START_DATE DATETIME,
    MONTH_END_DATE DATETIME,
    PRIMARY KEY (MONTH_START_DATE)
);

INSERT INTO #LAZY_DATE_DIM WITH (TABLOCK)
SELECT DATEADD(MONTH, ROW_NUMBER() OVER (ORDER BY (SELECT NULL)), '18991201')
, DATEADD(DAY, -1, DATEADD(MONTH, ROW_NUMBER() OVER (ORDER BY (SELECT NULL)), '19000101' ))
FROM master..spt_values;

You may find it useful to create a permanent calendar table in your database, but there are ways to generate the data that you need on the fly if that isn't an option.

Next step is to mock up your sample data:

CREATE TABLE #HRAL (
    StartDate DATETIME,
    EndDate DATETIME,
    [Type] VARCHAR(1)
);

INSERT INTO #HRAL VALUES ('2017-09-28','2017-10-02','L');
INSERT INTO #HRAL VALUES ('2017-10-03','2017-10-10','R');
INSERT INTO #HRAL VALUES ('2016-11-10','2016-11-11','L');
INSERT INTO #HRAL VALUES ('2017-08-17','2017-12-25','R');

I broke down the overall query into a few different steps. First step is to join the tables together such that we get a row for every relevant month that intersects with the start and end dates in your HRAL table. I used the following code:

SELECT
  h.*
, dd.*
FROM #HRAL h
INNER JOIN #LAZY_DATE_DIM dd ON
    dd.MONTH_START_DATE > DATEADD(MONTH, -1, h.StartDate)
    AND dd.MONTH_START_DATE < h.EndDate

The intermediate results:

╔═════════════════════════╦═════════════════════════╦══════╦═════════════════════════╦═════════════════════════╗
║        StartDate        ║         EndDate         ║ Type ║    MONTH_START_DATE     ║     MONTH_END_DATE      ║
╠═════════════════════════╬═════════════════════════╬══════╬═════════════════════════╬═════════════════════════╣
║ 2017-09-28 00:00:00.000 ║ 2017-10-02 00:00:00.000 ║ L    ║ 2017-09-01 00:00:00.000 ║ 2017-09-30 00:00:00.000 ║
║ 2017-09-28 00:00:00.000 ║ 2017-10-02 00:00:00.000 ║ L    ║ 2017-10-01 00:00:00.000 ║ 2017-10-31 00:00:00.000 ║
║ 2017-10-03 00:00:00.000 ║ 2017-10-10 00:00:00.000 ║ R    ║ 2017-10-01 00:00:00.000 ║ 2017-10-31 00:00:00.000 ║
║ 2016-11-10 00:00:00.000 ║ 2016-11-11 00:00:00.000 ║ L    ║ 2016-11-01 00:00:00.000 ║ 2016-11-30 00:00:00.000 ║
║ 2017-08-17 00:00:00.000 ║ 2017-12-25 00:00:00.000 ║ R    ║ 2017-08-01 00:00:00.000 ║ 2017-08-31 00:00:00.000 ║
║ 2017-08-17 00:00:00.000 ║ 2017-12-25 00:00:00.000 ║ R    ║ 2017-09-01 00:00:00.000 ║ 2017-09-30 00:00:00.000 ║
║ 2017-08-17 00:00:00.000 ║ 2017-12-25 00:00:00.000 ║ R    ║ 2017-10-01 00:00:00.000 ║ 2017-10-31 00:00:00.000 ║
║ 2017-08-17 00:00:00.000 ║ 2017-12-25 00:00:00.000 ║ R    ║ 2017-11-01 00:00:00.000 ║ 2017-11-30 00:00:00.000 ║
║ 2017-08-17 00:00:00.000 ║ 2017-12-25 00:00:00.000 ║ R    ║ 2017-12-01 00:00:00.000 ║ 2017-12-31 00:00:00.000 ║
╚═════════════════════════╩═════════════════════════╩══════╩═════════════════════════╩═════════════════════════╝

We can find the overlapping days by taking the maximum of the two start dates, the minimum of the two end days (adding one to the month end date from the calendar table), and using DATEDIFF to find the difference of days. Below is the query that shows some of the intermediate column values:

SELECT
  dd.MONTH_START_DATE
, h.StartDate
, start_dt.dt calc_start_dt
, h.[Type]
, dd.MONTH_END_DATE
, h.EndDate
, end_dt.dt calc_end_dt
FROM #HRAL h
INNER JOIN #LAZY_DATE_DIM dd ON
    dd.MONTH_START_DATE > DATEADD(MONTH, -1, h.StartDate)
    AND dd.MONTH_START_DATE < h.EndDate
CROSS APPLY (
    SELECT MAX(start_dt)
    FROM (VALUES (dd.MONTH_START_DATE), (h.StartDate)) x (start_dt)
) start_dt (dt)
CROSS APPLY (
    SELECT MIN(end_dt)
    FROM (VALUES (DATEADD(DAY, 1, dd.MONTH_END_DATE)), (h.EndDate)) x (end_dt)
) end_dt (dt)

The result set:

╔═════════════════════════╦═════════════════════════╦═════════════════════════╦══════╦═════════════════════════╦═════════════════════════╦═════════════════════════╗
║    MONTH_START_DATE     ║        StartDate        ║      calc_start_dt      ║ Type ║     MONTH_END_DATE      ║         EndDate         ║       calc_end_dt       ║
╠═════════════════════════╬═════════════════════════╬═════════════════════════╬══════╬═════════════════════════╬═════════════════════════╬═════════════════════════╣
║ 2017-09-01 00:00:00.000 ║ 2017-09-28 00:00:00.000 ║ 2017-09-28 00:00:00.000 ║ L    ║ 2017-09-30 00:00:00.000 ║ 2017-10-02 00:00:00.000 ║ 2017-10-01 00:00:00.000 ║
║ 2017-10-01 00:00:00.000 ║ 2017-09-28 00:00:00.000 ║ 2017-10-01 00:00:00.000 ║ L    ║ 2017-10-31 00:00:00.000 ║ 2017-10-02 00:00:00.000 ║ 2017-10-02 00:00:00.000 ║
║ 2017-10-01 00:00:00.000 ║ 2017-10-03 00:00:00.000 ║ 2017-10-03 00:00:00.000 ║ R    ║ 2017-10-31 00:00:00.000 ║ 2017-10-10 00:00:00.000 ║ 2017-10-10 00:00:00.000 ║
║ 2016-11-01 00:00:00.000 ║ 2016-11-10 00:00:00.000 ║ 2016-11-10 00:00:00.000 ║ L    ║ 2016-11-30 00:00:00.000 ║ 2016-11-11 00:00:00.000 ║ 2016-11-11 00:00:00.000 ║
║ 2017-08-01 00:00:00.000 ║ 2017-08-17 00:00:00.000 ║ 2017-08-17 00:00:00.000 ║ R    ║ 2017-08-31 00:00:00.000 ║ 2017-12-25 00:00:00.000 ║ 2017-09-01 00:00:00.000 ║
║ 2017-09-01 00:00:00.000 ║ 2017-08-17 00:00:00.000 ║ 2017-09-01 00:00:00.000 ║ R    ║ 2017-09-30 00:00:00.000 ║ 2017-12-25 00:00:00.000 ║ 2017-10-01 00:00:00.000 ║
║ 2017-10-01 00:00:00.000 ║ 2017-08-17 00:00:00.000 ║ 2017-10-01 00:00:00.000 ║ R    ║ 2017-10-31 00:00:00.000 ║ 2017-12-25 00:00:00.000 ║ 2017-11-01 00:00:00.000 ║
║ 2017-11-01 00:00:00.000 ║ 2017-08-17 00:00:00.000 ║ 2017-11-01 00:00:00.000 ║ R    ║ 2017-11-30 00:00:00.000 ║ 2017-12-25 00:00:00.000 ║ 2017-12-01 00:00:00.000 ║
║ 2017-12-01 00:00:00.000 ║ 2017-08-17 00:00:00.000 ║ 2017-12-01 00:00:00.000 ║ R    ║ 2017-12-31 00:00:00.000 ║ 2017-12-25 00:00:00.000 ║ 2017-12-25 00:00:00.000 ║
╚═════════════════════════╩═════════════════════════╩═════════════════════════╩══════╩═════════════════════════╩═════════════════════════╩═════════════════════════╝

Now I can put it all together by using DATEDIFF along with GROUP BY. The final query:

SELECT
  dd.MONTH_START_DATE
, SUM(DATEDIFF(DAY, start_dt.dt, end_dt.dt)) [days]
, h.[Type]
FROM #HRAL h
INNER JOIN #LAZY_DATE_DIM dd ON
    dd.MONTH_START_DATE > DATEADD(MONTH, -1, h.StartDate)
    AND dd.MONTH_START_DATE < h.EndDate
CROSS APPLY (
    SELECT MAX(start_dt)
    FROM (VALUES (dd.MONTH_START_DATE), (h.StartDate)) x (start_dt)
) start_dt (dt)
CROSS APPLY (
    SELECT MIN(end_dt)
    FROM (VALUES (DATEADD(DAY, 1, dd.MONTH_END_DATE)), (h.EndDate)) x (end_dt)
) end_dt (dt)
GROUP BY
  dd.MONTH_START_DATE
, h.[Type];

The results match yours:

╔═════════════════════════╦══════╦══════╗
║    MONTH_START_DATE     ║ days ║ Type ║
╠═════════════════════════╬══════╬══════╣
║ 2016-11-01 00:00:00.000 ║    1 ║ L    ║
║ 2017-09-01 00:00:00.000 ║    3 ║ L    ║
║ 2017-10-01 00:00:00.000 ║    1 ║ L    ║
║ 2017-08-01 00:00:00.000 ║   15 ║ R    ║
║ 2017-09-01 00:00:00.000 ║   30 ║ R    ║
║ 2017-10-01 00:00:00.000 ║   38 ║ R    ║
║ 2017-11-01 00:00:00.000 ║   30 ║ R    ║
║ 2017-12-01 00:00:00.000 ║   24 ║ R    ║
╚═════════════════════════╩══════╩══════╝

If you prefer to avoid the CROSS APPLY syntax you can accomplish the same thing with a CASE expression.


How to upgrade PostgreSQL minor releases without a dump/restore?

If you are doing a minor version upgrade, nothing will or should touch your data directory.  If you are using binary packages, just run whatever upgrade procedure they provide (apt/yum upgrade, etc.).  If building from source, just do make install over the old files and then restart the server (pg_ctl restart or whatever you're using).

The documentation could perhaps be amended to say that you could even install the new binaries before restarting the server.


How to crosstab with a composite "row name"

crosstab doesn't permit composite keys. it requires rows provide a unique identifier for which to pivot them on. This means in your case (id,project) has to be serialized. You can do this with jsonb, or array or the like. Here I'm just using a text[].

SELECT id, project, aug, sep
FROM crosstab(
  $$
    SELECT ARRAY[id,project]::text[], id, project, month, hours
    FROM foo
    ORDER BY 1
  $$,
  $$
    SELECT DISTINCT month FROM foo ORDER BY 1
  $$
) AS c(rn text[], id int,project text,aug int,sep int);

 id | project | aug | sep
----+---------+-----+-----
  1 | A       |   1 |   3
  1 | B       |   2 |   5
  2 | A       |   2 |   4
(3 rows)

A couple of notes,

you should probably be storing month as a date.
you may also want to look at the MADlib pivot which does permit composite keys.

There are lots of other ways you can solve this problem too without using crosstab,

SELECT id, project, jsonb_object_agg( month, hours ) AS month_hours
FROM foo
GROUP BY id, project
ORDER BY 1,2;
 id | project |     month_hours
----+---------+----------------------
  1 | A       | {"aug": 1, "sep": 3}
  1 | B       | {"aug": 2, "sep": 5}
  2 | A       | {"aug": 2, "sep": 4}
(3 rows)


path to data files is included in full dump.
No, SIDs don't have to match
You may want to add REMAP_DATAFILE option (https://docs.oracle.com/database/121/SUTIL/GUID-7C9DDBED-91AC-4FA2-AB91-1FF214DA89B0.htm#SUTIL926 ) to impdp .  Alternatively, you can just pre-create all tablespaces on target database.


Is MongoDB IXSCAN operator a scan or a seek?

I guess MongoDB can do index seek and index scan ?

Yes MongoDB can do index seek and index scan, but it can do both during one fetch.  That is what happened in your case. If started with a seek to the leftmost record of your index cuisine_1 and did a scan on 25359 keys.  During the scan of 25359 keys 1553 key matched your search criteria. Index not being a covering one, it had to do a lookup (for other columns) from 1553 documents.

If so, how do we know that a seek id performed ?

It will always do a seek if it it using a index, I guess your question is when it is not doing a seek + scan but only seek?

IXSCAN means:

  If the query planner selects an index, the explain result includes a
  IXSCAN stage. The stage includes information such as the index key
  pattern, direction of traversal, and index bounds.

If you see this, it will indicate a seek to one index key and no scan.  But anything more than one will indicate a seek + scan.

  "nReturned" : 1,
  "totalKeysExamined" : 1,


This is not an easy task to translate from one flavor of SQL to another. That's either (kind of) expensive or difficult to do for yourself.

No database language flavor is really easy to digest and translate into another, because all have lots of system procedures, data types, functions and others that are really system specific.

Each SQL flavor implements a subset of the SQL 92, 99 or 2003 Standard, and then goes further with local features.


Postgres - detect change in JSONB column value from 'null' to some value across rows

JSONB supports equality checks. So, if you're using it, you need only

Create an AFTER trigger on

CREATE TRIGGER foo
  AFTER UPDATE OF json_data
  ON myTable
  FOR EACH ROW
  WHEN (new.json_data IS DISTINCT FROM old.json_data)
  EXECUTE PROCEDURE myFunction;

Either,

Insert the id, and new.update_time into a new table, or
Update the update_time setting it the current time, and query later for update_time > last_time_ran.


Assuming your pseudo-code is a good approximation of what your actual code is doing, you'd need something like:

DECLARE @sql NVARCHAR(MAX);
SET @sql = '';
WHILE (Data is in the table)
BEGIN
    SELECT TOP 1 @result = result from table;

    @sql = @sql + ', ' + @result;

END

If you don't initialize @sql to a zero-length string prior to the loop, SQL Server will attempt to append @result to a NULL string, which results in a NULL string, hence the @sql at the end of the loop will appear empty.


Allow Null Columns vs separate 1-to-many tables

Allow null is a better approach. It gives us significant performance benefits with significant effort shortage to accomplish the task.
A separate table will cost more JOIN. To query such a data is/can be certainly slower, than to query single table


Ordering groups/partition windows by other column values

You can use the the max() with an over() clause partitioned by Venue to get the max date for each venue and then use that as the first column in the order by clause.

select T.Event_number,
       T.Venue,
       T.Date,
       T.Attendance,
       T.rn
from (
     select S.Event_number,
            S.Venue,
            S.Date,
            S.Attendance,
            S.rn,
            max(S.Date) over(partition by S.Venue) as MaxDate
     from SampleTable as S
     ) as T
order by T.MaxDate desc,
         T.Venue,
         T.rn;


Help Writing an UPDATE Statement With JOIN

I think you just missed the ON clause and placed the xyz alias twice. Corrected:

UPDATE mi
SET totalvalue = xyz.TV
FROM masterinfo mi
  INNER JOIN                     -- removed incorrectly placed alias xyz
  (
    SELECT accID, SUM(totalval) TV
    FROM OPENQUERY(EA, 'select accID, totalval from sv')
    GROUP BY accID
  ) xyz                          -- correct here
  ON xyz.accID = mi.accID        -- missing ON clause, adjust
                                 -- according to your structure
 ;

Once - and if - you are sure that the above is correct (from the point of what exactly you want to do), you may try to do the aggregation in the remote server. It might be more efficient:

UPDATE mi
SET totalvalue = xyz.TV
FROM masterinfo mi
  INNER JOIN
  (
    SELECT accID, TV
    FROM OPENQUERY(EA, 'select accID, sum(totalval) TV from sv group by accID')
  ) xyz
  ON xyz.accID = mi.accID
;

and I think this one can be simplified a bit:

UPDATE mi
SET totalvalue = xyz.TV
FROM masterinfo mi
  INNER JOIN
     OPENQUERY(EA, 'select accID, sum(totalval) TV from sv group by accID') xyz
  ON xyz.accID = mi.accID
;


SQL Server in memory temp table disk usage

Based on this post by Michael Swart (provided by Aaron Bertrand), this seems to be the normal behavior of Hekaton.


Running SSIS from a different server than the primary database instance

I would suggest reading up on SSIS at MSDN:
Initial Installation (Integration Services) -this references having a standalone installat of just SSIS.

Quote from above link:

  SQL Server Integration Services is installed through the SQL Server
  2008 Setup program. You can install Integration Services alongside
  other SQL Server components or you can install Integration Services as
  a stand-alone component. Integration Services includes both client and
  server applications.

  To use a dedicated server for extraction, transformation, and loading
  (ETL) processes, we recommend that you install a local instance of the
  SQL Server Database Engine when you install Integration Services.
  Integration Services typically stores packages in an instance of the
  Database Engine and relies on SQL Server Agent for scheduling those
  packages. If the ETL server does not have an instance of the Database
  Engine, you will have to schedule or run packages from a server that
  does have an instance of the Database Engine. This means that the
  packages will not be running on the ETL server, but instead on the
  server from which they were started. As a result, the resources of the
  dedicated ETL server are not being used as intended. Furthemore, the
  resources of other servers might be strained by the running ETL
  processes.

Some additional information could be found here as well: Considerations for Installing Integration Services. You might want to take under consideration the licensing as well and still validate the standalone server is licensed properly.


Indexing a string by a subset of its characters (i.e.: the Kanji ones)

There is a way of implementing what you want by using Regular Expressions and a couple of triggers.

To set the stage, I assume your tables are created this way:

CREATE TABLE entries
(
    entry_id int PRIMARY KEY,
    keyword varchar,
    reading varchar,
    meaning varchar
) ;

CREATE TABLE  entries_kanji
(
    kanji nchar,
    entry_id int REFERENCES entries (entry_id)
) ;

I assume that your definition of Kanji can be translated to a Regular Expression. To begin with, I use the definition of Kanji found on Localizing Japan. That is, we can define one funcion to check whether a character is a Kanji by doing this:

CREATE OR REPLACE FUNCTION is_kanji(IN a_char character)
RETURNS boolean AS
$BODY$
    SELECT
        a_char ~ '[\x3400-\x4DB5\x4E00-\x9FCB\xF900-\xFA6A]'
$BODY$
LANGUAGE sql
IMMUTABLE
STRICT;

The ~ operator means text_on_the_left matches the regular expression on the right. See Pattern Matching in PostgreSQL.

The result of executing the following query

SELECT
    is_kanji('齢');

is true.

Next step is, given a sentence (a string where there are several Kanji chars), we SELECT all of them. This can be done with the following query:

SELECT
    unnest(regexp_matches('答 answer 電 electricity 教 teach',
      '[\x3400-\x4DB5\x4E00-\x9FCB\xF900-\xFA6A]', 'gi')) AS kanji

The function regexp_matches returns an array containing all the parts of the passed string that match the regular expression. This array is converted into a set of rows by means of the unnest function.

Once we have this, we create one trigger function to be associated with INSERTS, UPDATES and DELETES into the entries table.

This function would take care:

CREATE FUNCTION trigger_entry()
RETURNS trigger AS $$
BEGIN
    if TG_OP = 'DELETE' or TG_OP = 'UPDATE' then
        -- When we delete or update rows, we delete all
        -- corresponding entries_kanji.
        DELETE FROM
            entries_kanji
        WHERE
            entry_id = OLD.entry_id ;
    end if ;
    if TG_OP = 'INSERT' or TG_OP = 'UPDATE' then
        -- When we insert or update rows, we insert all
        -- corresponding entries_kanji
        INSERT INTO
            entries_kanji (kanji, entry_id)
        SELECT
             unnest(regexp_matches(NEW.keyword,
                 '[\x3400-\x4DB5\x4E00-\x9FCB\xF900-\xFA6A]', 'g')) AS kanji,
             new.entry_id ;
        -- If case we use a 'before' trigger... we return NEW row (untouched)
        return new ;
    end if ;

    -- In case we use a 'before' trigger, ... we return OLD row (untouched).
    return old ;
END $$
LANGUAGE plpgsql
VOLATILE ;

And we associate this trigger function with one trigger (I have chosen it to be an AFTER trigger, in case some other BEFORE triggers do some data manipulation; otherwise, it could also be a BEFORE trigger):

CREATE TRIGGER trigger_make_entries_kanji
    AFTER INSERT OR UPDATE OF entry_id, keyword OR DELETE
    ON entries
    FOR EACH ROW
    EXECUTE PROCEDURE trigger_entry();

With this setup, you can now insert data into entries, and get the kanji in entries_kanji:

INSERT INTO
    entries (entry_id, keyword, reading, meaning)
VALUES
    (1, 'One sentence with one kanji(朗) symbol', 'reading_1', 'meaning_1'),
    (2, '答 answer 電 electricity 教 teach', 'reading_2', 'meaning_2');

and see that you get the proper information in the entries_kanji table:

SELECT
    *
FROM
    entries_kanji
ORDER BY
    entry_id, kanji ;

You get

+-------+----------+
| kanji | entry_id |
+-------+----------+
| 朗    |        1 |
| 教    |        2 |
| 答    |        2 |
| 電    |        2 |
+-------+----------+

You can also check that the trigger takes care when you UPDATE or DELETE entries.

The advantages of triggers versus materialized views are that

They change the information in real time, without need for refreshing
They are available also in older versions of PostgreSQL (which might be important if version is out of your control)

The disadvantages are:

You (or someone in your team) need to have some knowledge on how to program triggers. Triggers can have some nasty side-effects if not used properly.
You carry some extra cost when inserting, updating or deleting your table's data.

Depending on your use case, you can decide which solution is more appropriate.


How can a raised alert fail to notify an operator via e-mail?

You might check the on duty schedule for that particular operator.  Did this alert happen while the operator was not on duty?

Is SQL Agent set to use Database Mail and a specified Mail Profile for the Alert System?  (Right Click SQL Server Agent > Properties > Alert System)

These are two things that I can immediately think of that could cause this behavior.

Hope this can help.


PostgreSQL optimistic reads (equivalent to SQL Server's Read Committed Snapshot)

This is how Postgres was designed from ground up (as opposed to SQL Server where this was added very late)

See the manual for details: http://www.postgresql.org/docs/current/static/mvcc-intro.html

You can't actually configure Postgres so that readers would block writers (automatically or implicitly)

The only way to block read access in Postgres is to (manually) lock the complete table exclusively (lock table foo access exclusive).

This will block any access to the table (not only individual rows).

And unlike SQL Server, Postgres also does not have lock escalation where the whole table is locked for writes when a certain threshold of row locks is exceeded.

So just because one transaction updates 95% of all rows in a table (and thus locks them for other writers), that doesn't mean that other transactions can't update the remaining 5% - with SQL Server, you'd most probably wind up with a lock escalation locking the whole table.


I don't think that you can tell from the "verbose" listing whether any pages were skipped because they were known to contain only visible tuples.

I think that it will only vacuum pages flagged as "all visible" if it decides to freeze tuples in the table, either because of approaching transaction wraparound or because you explicitly ran VACUUM with the FREEZE option.  (If you use the vacuumdb command-line executable, this is the -F option.)

In our shop we supplement the autovacuum activity with a scheduled VACUUM FREEZE ANALYZE on the database during non-peak hours.  That minimizes the overhead of autovacuum during peak hours, and slightly improves query performance, because visibility checks are simpler.


Is introducing a where condition into the join clause bad practice

Putting a condition in the WHERE clause and the ON clause are two very different things.  The ON clause is part of the JOIN.  Now with an INNER JOIN you won't see any difference in the output (unless also using GROUP BY ALL).  But an OUTER JOIN is a different story.  If you moved that particular condition into the WHERE clause it would (among other things), in effect, negate the OUTER portion of your query.

I wrote an article with several examples here.

Just remember that the ON clause joins the two tables together.  The WHERE clause excludes rows from the output.  For your specific example lets say you have a row in TB_REQUEST_DATA without a match in TB_SPOT_REPORT_REQUEST.  With r.master_seq=206 in the ON clause of the  OUTER JOIN you will get a row returned with the value of TB_REQUEST_DATA but NULLs in the TB_SPOT_REPORT_REQUEST columns.  If on the other hand you put it in the WHERE clause it's going to eliminate the row entirely.

Let's say however that you want every row of TB_REQUEST_DATA but only the information in TB_SPOT_REPORT_REQUEST where master_seq = 206 AND SEQ = REQUEST_SEQ.  That's when you would put the condition in to the ON clause.


All versions

If you create an index on a column that its definition is longer than 900 or 1700 (depending on version and type of index), you'll either get a warning and the index creation will succeed (if the table is empty or the existing data are not that long) or an error and the index creation will fail (if a row or more exceed the limit).

For the warning case, the limits are:

SQL Server 2014 and Older

If you create a clustered index on a varchar(>900) column you will receive a warning when creating the index.

If you create a non-clustered index on a varchar(>900) column you will receive a warning message when creating the index.

SQL Server 2016 and Newer

If you create a clustered index on a varchar(>900) column you will receive a warning message when creating the index.

If you create a non-clustered index on a varchar(>1700) column you will receive a warning message when creating the index.

Inserting Data

If you don't insert more data than is limited by the index and the version of SQL Server then you will not receive an error message in your application / SSMS / SQL Query / ...

However, inserting more than the limits specified by the version and index type will result in an error message.

Testing

You can try this out over at db<>fiddle. The links for each test are provided below the test examples.

Varchar Non-Clustered Index Limitations on SQL Server 2012

CREATE TABLE VarcharNonClustered
(
  ID INT
  ,Name VARCHAR(2000)
)

GO

✓

CREATE NONCLUSTERED INDEX idx_VarcharNonClustered_Name ON VarcharNonClustered(Name)
GO

✓

Warning! The maximum key length is 900 bytes. The index 'idx_VarcharNonClustered_Name' has maximum length of 2000 bytes. For some combination of large values, the insert/update operation will fail.

INSERT INTO VarcharNonClustered
VALUES (1,REPLICATE('a',1701))
GO

Msg 1946 Level 16 State 3 Line 1
Operation failed. The index entry of length 1701 bytes for the index 'idx_VarcharNonClustered_Name' exceeds the maximum length of 900 bytes.

INSERT INTO VarcharNonClustered
VALUES (1,REPLICATE('a',1700))
GO

Msg 1946 Level 16 State 3 Line 1
Operation failed. The index entry of length 1700 bytes for the index 'idx_VarcharNonClustered_Name' exceeds the maximum length of 900 bytes.

INSERT INTO VarcharNonClustered
VALUES (1,REPLICATE('a',901))
GO

Msg 1946 Level 16 State 3 Line 1
Operation failed. The index entry of length 901 bytes for the index 'idx_VarcharNonClustered_Name' exceeds the maximum length of 900 bytes.

INSERT INTO VarcharNonClustered
VALUES (1,REPLICATE('a',900))
GO

1 rows affected

db<>fiddle here

Varchar Clustered Index Limitations on SQL Server 2012

CREATE TABLE VarcharClustered
(
  ID INT
  ,Name VARCHAR(2000)
)
GO

✓

CREATE CLUSTERED INDEX idx_VarcharClustered_Name ON VarcharClustered(Name)
GO

✓

Warning! The maximum key length is 900 bytes. The index 'idx_VarcharClustered_Name' has maximum length of 2000 bytes. For some combination of large values, the insert/update operation will fail.

INSERT INTO VarcharClustered
VALUES (1,REPLICATE('a',1701))
GO

Msg 1946 Level 16 State 3 Line 1
Operation failed. The index entry of length 1701 bytes for the index 'idx_VarcharClustered_Name' exceeds the maximum length of 900 bytes.

INSERT INTO VarcharClustered
VALUES (1,REPLICATE('a',1700))
GO

Msg 1946 Level 16 State 3 Line 1
Operation failed. The index entry of length 1700 bytes for the index 'idx_VarcharClustered_Name' exceeds the maximum length of 900 bytes.

INSERT INTO VarcharClustered
VALUES (1,REPLICATE('a',901))
GO

Msg 1946 Level 16 State 3 Line 1
Operation failed. The index entry of length 901 bytes for the index 'idx_VarcharClustered_Name' exceeds the maximum length of 900 bytes.

INSERT INTO VarcharClustered
VALUES (1,REPLICATE('a',900))
GO

1 rows affected

db<>fiddle here

Varchar Non-Clustered Index Limitations on SQL Server 2016

CREATE TABLE VarcharNonClustered
(
  ID INT
  ,Name VARCHAR(2000)
)

GO

✓

CREATE NONCLUSTERED INDEX idx_VarcharNonClustered_Name ON VarcharNonClustered(Name)
GO

✓

Warning! The maximum key length for a nonclustered index is 1700 bytes. The index 'idx_VarcharNonClustered_Name' has maximum length of 2000 bytes. For some combination of large values, the insert/update operation will fail.

INSERT INTO VarcharNonClustered
VALUES (1,REPLICATE('a',1701))
GO

Msg 1946 Level 16 State 3 Line 1
Operation failed. The index entry of length 1701 bytes for the index 'idx_VarcharNonClustered_Name' exceeds the maximum length of 1700 bytes for nonclustered indexes.

INSERT INTO VarcharNonClustered
VALUES (1,REPLICATE('a',1700))
GO

1 rows affected

INSERT INTO VarcharNonClustered
VALUES (1,REPLICATE('a',901))
GO

1 rows affected

INSERT INTO VarcharNonClustered
VALUES (1,REPLICATE('a',900))
GO

1 rows affected

db<>fiddle here

Varchar Clustered Index Limitations on SQL Server 2016

CREATE TABLE VarcharClustered
(
  ID INT
  ,Name VARCHAR(2000)
)
GO

✓

CREATE CLUSTERED INDEX idx_VarcharClustered_Name ON VarcharClustered(Name)
GO

✓

Warning! The maximum key length for a clustered index is 900 bytes. The index 'idx_VarcharClustered_Name' has maximum length of 2000 bytes. For some combination of large values, the insert/update operation will fail.

INSERT INTO VarcharClustered
VALUES (1,REPLICATE('a',1701))
GO

Msg 1946 Level 16 State 3 Line 1
Operation failed. The index entry of length 1701 bytes for the index 'idx_VarcharClustered_Name' exceeds the maximum length of 900 bytes for clustered indexes.

INSERT INTO VarcharClustered
VALUES (1,REPLICATE('a',1700))
GO

Msg 1946 Level 16 State 3 Line 1
Operation failed. The index entry of length 1700 bytes for the index 'idx_VarcharClustered_Name' exceeds the maximum length of 900 bytes for clustered indexes.

INSERT INTO VarcharClustered
VALUES (1,REPLICATE('a',901))
GO

Msg 1946 Level 16 State 3 Line 1
Operation failed. The index entry of length 901 bytes for the index 'idx_VarcharClustered_Name' exceeds the maximum length of 900 bytes for clustered indexes.

INSERT INTO VarcharClustered
VALUES (1,REPLICATE('a',900))
GO

1 rows affected

db<>fiddle here

Summary

SQL Server 2012 + Non-Clustered Index on Varchar() = 900 characters max
SQL Server 2012 + Clustered Index on Varchar() = 900 characters max
SQL Server 2016 + Non-Clustered Index on Varchar() = 1700 characters max
SQL Server 2016 + Clustered Index on Varchar() = 900 characters max

Your Question

  Creating an Index on a varchar(1024) column succeeded. Does it work?

It will work with the above limitations.

References

Maximum Capacity Specifications for SQL Server (SQL Server 2016)
Maximum Capacity Specifications for SQL Server (SQL Server 2012)


Create a check constraint across multiple schemas in PostGIS

You need a trigger that runs on UPDATE and INSERT (when a row would otherwise be modified). As a-horse-with-no-name said in a comment:

  Not possible. A check constraint can only validate column values of one row in one table.


Oracle TNS Permission denied when creating database

Please, verify if you are installing Oracle 32bit or 64 bit.
This is important because Oracle in Metalink Knolwedge Base says:

  Getting ORA-12546 ORA-12560 and ORA-516 while creating the database
  (Windows64) (Doc ID 365852.1)

  You can run Oracle 32bit, on Windows 32bit.  OR  You can run Oracle
  64bit, on Windows 64bit.

  You can not mix the versions.

Also, report the complete error stack.
Normally Oracle zip files have 32 or 64 bit eported in the name of the archive itself.
 Here you can find a lot of way to identify the executables architecture in Windows:
How to check if a binary is 32 or 64 bit on Windows?

Regards

Giova


SQL Server 2016: cannot restore database from .bak file because of huge transaction log?

When you backup a database, it grabs all the data from your database and the part of transaction log that it needs to put the database in the consistent state after restore.

When you restore database from backup it creates exactly that database that was backuped, so if you now have a log file of 300Gb it means the original database on your production server has a log file of 300Gb.

Yes you can shrink your log file now, on your test server, but first you should put your database in SIMPLE RECOVERY MODEL. This will not affect your production database.

But if your backup size was 145Mb only, this means that your production database has a problem: someone put it into FULL RECOVERY MODEL and does not take log backups. It's clear from your database size: when log file is >300 times greater than data file, it's a problem with wrong recovery model

Here is the code to check your database size:

use yourDB;
select name, type_desc, cast(size * 8. / 1024 as decimal(20,2)) as Mb
from sys.database_files;

UPDATE:

  ...to set up a maitenance plan every night so that it does a full
  backup of the DB, re-organizes/updates indexes and, once a week,
  updates statistics.Up to now, a night backup has always been enough.

  ... Anyway, should I want to keep thos transactional logs and the full
  recovery model, what should I know to improve things?
         I mean, if I wanted to take log backups in order to reduce transactional logs, what shall I do? Am I supposed to change any
  settings in the maintenance plan

You don't need FULL recovery model.
As for now, your backup plan suppose that it's acceptable to you to lose 1 day of work, and it's normal for a small noncritical database.

When you are in simple recovery model, minimally logged operations like index rebuild or select into will be minimally logged, that means they will be faster.

In case you need to restore your database it will be one simple restore from a full backup.

Your log management will be minimal: it will be automatically truncated on checkpoint.

When you are in full recovery model it means that every change to your data will be fully logged, and all that changes will be retained in your log file until you do a log backup. So what happened to you now is that your log file contains all the changes made to your database from your first full backup (since no log backup was taken) till now.

If you back up your log now, you'll be waiting much more time than you was waiting today while 300Gb of log space was zeroing out. To back up this log you'll need enough disk space, about 300Gb. After this backup completes, your log size will not change even if you'll try to shrink it because the active portion of the log will reside in the last VLF.

So if you decide to stick with full recovery model even after taking this enormous log backup you will still have a problem with your log size. You'll have to wait until the active portion of the log will move to the 1st VLF and it can require some time because your database is small enough, changes are small too, but your last VLFs are enormous. After shrinkig you'll have to decide how often will you take log backups and what is the appropriate log file size for your database.

So who and why chose FULL recovery model? Some HA technologies require it: log shipping, mirroring, AOAG. Or someone who cannot allow data lost. For example, if today your data file got lost, you could take your tail of the log backup (300Gb) and restore your database to the last minute of its life by restoring first your last night full backup and then restoring your log backup.
Without it all that you could do was to restore your database from yesterday's backup, i.e. restored database will contain the data at the state of yesterday with all today's work lost.

What they are talking about in the comments? Point-in-time restore. Full recovery model permits you to restore your database to any point in time in the past (only in case you have unbroken log backup chain, of course). Do you really need it? Can it be a situation when you are told to bring your db to 2017-01-04 03:45:00? Or maybe it's enough to you to bring it back to 2017-01-04 00:00:00, the time of your full backup? If only latter is the case, you are good enough to stay in simple recovery model and continue to back up your db every night at 00:00:00

I just do not know how to fit in 1 page the materials that merit a book :)
Try to start from BOL: Recovery Models and Transaction Log Management


MySQL: Find ID with only 2 attached IDs in subtable

You've tagged your question with "t-sql", so here goes (although I think/hope that this should be ANSI SQL compliant) :

SELECT DISTINCT mt.ThreadID
FROM MessageThreads AS mt
INNER JOIN MessageThreadAccessUsers AS mtau1 ON mtau1.ThreadID=mt.ThreadID AND mtau1.UserID=1
INNER JOIN MessageThreadAccessUsers AS mtau2 ON mtau2.ThreadID=mt.ThreadID AND mtau2.UserID=2
LEFT JOIN  MessageThreadAccessUsers AS mtau3 ON mtau3.ThreadID=mt.ThreadID AND mtau3.UserID NOT IN (1, 2)
WHERE mtau3.ThreadID IS NULL;

This is probably more efficient than a GROUP BY with conditional SUM()s, particularly so if you have an index on MessageThreadAccessUsers that covers the columns (ThreadID, UserID). If users are distinct for each thread, you can even remove DISTINCT.

Edit: Added LEFT JOIN and WHERE .. IS NULL to exclude any other users.


How to create a clustered index on a schema bound view with the least amount of contention caused?

EXAMPLE,

CREATE VIEW FilteredIndexedView WITH SCHEMABINDING AS

SELECT TextColumn1, TextColumn2, DateColumn1
FROM dbo.BoringTable
WHERE DateColumn1 < '1/1/2019';

CREATE CLUSTERED INDEX IX_FilteredIndexView_DateColumn1 ON FilteredIndexView (DateColumn1);

INSERT INTO dbo.BoringTable (TextColumn1, TextColumn2, DateColumn1)
SELECT 'SomeText', 'SomeOtherText', '12/11/2019'; -- Some date that falls outside the filter of the indexed view

Whenever data will be inserted WHERE DateColumn1 < '1/1/2019' then index IX_FilteredIndexView_DateColumn1 will be updated in same query plan.
That means Insert query cost will increase.
If data inserted is outside the bound of give view i.e. WHERE DateColumn1 >='1/1/2019' then in this case index IX_FilteredIndexView_DateColumn1 won't be maintain. That means there won't be extra cost of maintaining index.

As this table is highly transnational and if most of the INSERT or DML operation lies within  WHERE DateColumn1 < '1/1/2019' then INSERT COST will INCREASE significantly.

In this example it appear that Index View is NO NO.

If Insert operation finish in seconds and whatever the cost incur is acceptable then Index View is no problem.

In First place, why you will create view with Single table ?

Alternatively you can try Filtered Index

Create NonClustered index ix_Boring_Date on Boring(DateColumn1) include (TextColumn1,TextColumn2)
where DateColumn1 < '1/1/2019'

Keep Rebuilding index from time to time.

Suggested Reading by  @PaulWhite


How to write function in sql which accept table as input and return result back as table?

You cannot include a table declaration in the stored procedure declaration.
If you really want to pass rows of data, create a table-valued parameter type, then use it in your stored procedure.

How about a simpler table valued function, that calculates product and quotient of 2 integers:

CREATE FUNCTION fn_CalcProductQuotient(
@Value1 INT,
@Value2 INT)
RETURNS TABLE
AS
RETURN (
SELECT @Value1 * @Value2 AS [Product]
       ,@Value1 / @Value2 AS [Quotient]
);

Make sure to use an inline user-defined function. This way the plan optimiser can parallelise your plan (if you have multiple cores) and you will get much better performance.

You can use this function as following:

SELECT  [RowID]
       ,[Value1]
       ,[Value2]
       ,Calc.[Product]
       ,Calc.[Quotient]
FROM Items i
CROSS APPLY fn_CalcProductQuotient(Value1, Value2) Calc


Which instance's timestamp is used as SCN in Oracle 12c rack?

SCN actually has a 3 second granularity now. As far as RAC goes, all cluster nodes must have synchronized clocks using NTP or some such; if the system times get too out of sync the cluster will start evicting nodes, trying to get them on the same page again. For more details on what SCN is and how it works, check these links:

http://www.dba-oracle.com/t_rac_scn.html
https://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:48052838748707
https://www.red-gate.com/simple-talk/sql/oracle/oracle-system-change-number-an-introduction/


MySQL Innodb_row_lock_current_waits is 2^64 - 1

There is a open bug report on this for MySQL 5.6.14

Bug #71520 Constantly increasing Innodb_row_lock_current_waits value

From the bug report, note this entry

  [25 Jun 2015 6:58] Zhenye Xie in my environment, I got a strange
  value. (-1 as uint64)

mysql> show status where Variable_name = 'Innodb_row_lock_current_waits';

+-------------------------------+----------------------+
| Variable_name                 | Value                |
+-------------------------------+----------------------+
| Innodb_row_lock_current_waits | 18446744073709551615 |
+-------------------------------+----------------------+
1 row in set (0.06 sec)

  in lock0wait.cc, srv_stats.n_lock_wait_current_count.inc() srv_stats.n_lock_wait_current_count.dec(); is not thread safe.
  srv_stats is a global variable, but it has just a lock on trx
  surrounds changing the value.

You were right when you said it must be an overflow/error code of some sort

As recent as Jun 14 of this year, this issue may or may not exist in MySQL 5.7.11

  [14 Jun 9:55] Ángel OR Good morning,

  I am seeing this in MySQL 5.7.11-log:

mysql> show status like '%Innodb_row_lock_current%';

+-------------------------------+-------+
| Variable_name                 | Value |
+-------------------------------+-------+
| Innodb_row_lock_current_waits | 45    |
+-------------------------------+-------+
1 row in set (0.00 sec)

mysql> select count(*) from information_schema.innodb_lock_waits;

+----------+
| count(*) |
+----------+
|        0 |
+----------+
1 row in set (0.00 sec)

mysql> select version();
+------------+
| version()  |
+------------+
| 5.7.11-log |
+------------+
1 row in set (0.00 sec)


Duplicated rows (x) amount of times in a table

You can get it using a table value or a value list, and a CROSS JOIN.

INSERT INTO foo
SELECT CONCAT(SO, t.x) as SO, SO_Line
FROM   foo
CROSS JOIN (VALUES('-1'),('-2'),('-3'),('-4'),('-5')) t(x)
WHERE  SO = 'ABC';
GO

15 rows affected

SELECT * FROM   foo;
GO

SO    | SO_Line
:---- | ------:
ABC   |       1
ABC   |       3
ABC   |       5
DEF   |       1
DEF   |       2
ABC-1 |       1
ABC-2 |       1
ABC-3 |       1
ABC-4 |       1
ABC-5 |       1
ABC-1 |       3
ABC-2 |       3
ABC-3 |       3
ABC-4 |       3
ABC-5 |       3
ABC-1 |       5
ABC-2 |       5
ABC-3 |       5
ABC-4 |       5
ABC-5 |       5

dbfiddle here

Instead of use a value list, you could use an INLINE User defined function like this:

CREATE FUNCTION tvValues(@Num int)
RETURNS table
AS
RETURN
(
     SELECT TOP (@Num) ROW_NUMBER() OVER (ORDER BY S.[object_id]) [item]
     FROM    sys.all_objects S
)
GO

Then convert your query to:

INSERT INTO foo
SELECT CONCAT(SO, '-', t.item) as SO, SO_Line
FROM   foo
CROSS JOIN tvValues(7) t
WHERE  SO = 'ABC';
GO

NOTE: I've used tvValues(7) but you can use other values.

This is the result:

SELECT * FROM   foo;
GO

SO    | SO_Line
:---- | ------:
ABC   |       1
ABC   |       3
ABC   |       5
DEF   |       1
DEF   |       2
ABC-1 |       1
ABC-2 |       1
ABC-3 |       1
ABC-4 |       1
ABC-5 |       1
ABC-6 |       1
ABC-7 |       1
ABC-1 |       3
ABC-2 |       3
ABC-3 |       3
ABC-4 |       3
ABC-5 |       3
ABC-6 |       3
ABC-7 |       3
ABC-1 |       5
ABC-2 |       5
ABC-3 |       5
ABC-4 |       5
ABC-5 |       5
ABC-6 |       5
ABC-7 |       5

dbfiddle here


This would do what you desire:

WITH p AS (
    INSERT INTO parent_table (column_1)
    SELECT $1
    RETURNING id)
INSERT INTO child_table (parent_table_id, column_a, column_b)
SELECT p.id, t.a, t.b
FROM   p, (SELECT unnest($2::text[]) AS a, unnest($3::bigint[]) AS b) t

The subtle difference here is that unnest() calls in the same SELECT list are unnested in parallel if the number of elements is identical. Careful though: In Postgres 9.6 or older, if the number is not the same, it results in a Cartesian product instead. The behavior was sanitized in Postgres 10. See:

What is the expected behaviour for multiple set-returning functions in select clause?
Is there something like a zip() function in PostgreSQL that combines two arrays?

You could use a cleaner form with generate_subscripts() or other techniques, but those would be much more verbose. Details in this related question:

How to preserve the original order of elements in an unnested array?

Postgres 9.4

The new WITH ORDINALITY in Postgres 9.4 allows a much cleaner (and only moderately more verbose) form for this:

WITH p AS (...)
INSERT INTO child_table (...)
SELECT p.id, ta.a, tb.b
FROM   p
     , unnest($2::text[]) WITH ORDINALITY AS ta(a, rn)
JOIN   unnest($3::bigint[]) WITH ORDINALITY AS tb(b, rn) USING (rn);

And this special case can be even simpler with the new form of unnest() that accepts multiple arrays:

WITH p AS (...)
INSERT INTO child_table (...)
SELECT p.id, t.a, t.b
FROM   p, unnest($2::text[], $3::bigint[]) AS t(a, b);

Example in this related answer.


Database Design Question for a weird use case

I would approach it this way.  Have a "ParentWorkPackageID" that links back to the parent work package.  Add as many additional properties as you need to the work packages.

You can easily (using a recursive CTE) query for a work package and any children it may have.  You don't mention your engine so I'm not going to provide an example.

You could optionally do away with the Project table completely and simply assume that any workPackage with NULL for a Parent must be the top level workPackage/Project.  But I think keeping Project as a separate table will be cleaner since there are probably attributes that apply to projects and not to sub-work packages.

EDIT

I corrected the syntax (sorry about the check constraint error).
I've also included an example for you.

DROP TABLE IF EXISTS dbo.WorkPackage
DROP TABLE IF EXISTS dbo.Project

CREATE TABLE dbo.Project
    (
    ProjectID INT NOT NULL IDENTITY(1,1)
    , ProjectName NVARCHAR(100) NOT NULL
    , CONSTRAINT PK_Project PRIMARY KEY CLUSTERED (ProjectID)
    )

CREATE TABLE dbo.WorkPackage
    (
    WorkPackageID INT NOT NULL IDENTITY(1,1)
    , ProjectID INT NOT NULL
    , ParentWorkPackageID INT NULL
    , WorkPackageName NVARCHAR(100) NOT NULL
    , CONSTRAINT PK_WorkPackage PRIMARY KEY CLUSTERED (WorkPackageID)
    , CONSTRAINT FK_WorkPackage_ProjectID FOREIGN KEY (ProjectID) REFERENCES dbo.Project (ProjectID)
    , CONSTRAINT FK_WorkPackage_ParentWorkPackageID FOREIGN KEY (ParentWorkPackageID) REFERENCES dbo.WorkPackage (WorkPackageID)
    )

GO

INSERT INTO dbo.Project
(ProjectName)
VALUES ('TestProject')

INSERT INTO dbo.WorkPackage
(ProjectID, ParentWorkPackageID, WorkPackageName)
VALUES (1, NULL, 'First Work Package')
    , (1, 1, 'First child Package')
    , (1, 2, 'First grand child Package')
    , (1, 1, 'Second Child')

/** Easy to find all packages for a project... **/
SELECT * FROM dbo.WorkPackage WHERE ProjectID = 1

/** Find all children for a specific work package using recursive CTE
    - adds a Generation column so you can see parent to child descendants, regardless of when they were created.
    **/
;WITH CTE_FindChildren AS
    (
    SELECT WP.WorkPackageID AS WorkPackageID
        , WP.ParentWorkPackageID AS ParentWorkPackageID
        , Generation = 1
    FROM dbo.WorkPackage AS WP
    WHERE WP.ParentWorkPackageID IS NULL
    UNION ALL
    SELECT WP.WorkPackageID AS WorkPackageID
        , CWP.WorkPackageID AS ParentWorkPackageID
        , Generation = Generation + 1
    FROM dbo.WorkPackage AS WP
        INNER JOIN CTE_FindChildren AS CWP ON CWP.WorkPackageID = WP.ParentWorkPackageID
    )
SELECT WP.*
    , CF.Generation
FROM CTE_FindChildren AS CF
    INNER JOIN dbo.WorkPackage AS WP ON WP.WorkPackageID = CF.WorkPackageID
WHERE WP.ProjectID = 1
ORDER BY Generation, WorkPackageID


sp_configure: Preserve values of 'xp_cmdshell' and 'Show Advanced Options'

Error is at below select statements. If you do ALT+F1 on sys.confiugrations you'll notice that value_in_use column is of type sql_variant and you are trying to assign it to int variables. To fix this, use below statements in your script and it should work fine.

SELECT @advanced_options = cast(value_in_use as int)
FROM sys.configurations
WHERE name = 'show advanced options'

SELECT @xp_cmdshell = cast(value_in_use  as int)
FROM sys.configurations
WHERE name = 'xp_cmdshell'


First, I would argue that you are testing performance backwards. Instead of measuring how many inserts you can perform in one second, measure how long it takes to perform n inserts, and then divide by the number of seconds it took to get inserts per seconds. n should be at least 10,000.

Second, you really shouldn't use _mysql directly. It makes your code non-portable to other Python database adapters.

Third, _mysql is defaulting to whatever auto-commit setting is the default for your database, and MySQL is generally auto-commit on by default. MySQLdb turns off auto-commit by default.

Fourth, if you really want to insert a lot of rows fast, you should use cursor.executemany(sql, list_of_tuples_of_parameters). This is internally converted into a multi-row insert, and is an atomic operation for MySQL.

Please read PEP-249.


How to create table with default DATE column using { ts }?

Whatever syntax that is, that is not correct in Oracle.

Use this:

create table my_table (
    transaction_date DATE DEFAULT timestamp'1900-01-01 00:00:00' NULL
);


Fixed database paths in stored procedures/views - is there a smoother way of doing this?

A linked server definition includes the name of the server that you are connecting to and includes the security settings for the linked server.   If you  (assuming Microsoft SQL Server) "SELECT * FROM master.sys.databases" you will see all of your linked servers.

Therefore, if you script out all of the linked servers and recreate them on the new server, then you should be able to set up the new server just the same as the old server. (Exception is that any SQL Server logins used in linked servers will not include the passwords, so you will need to update the scripts.)

Of course, the other issue is that one of the servers that you link to will change and you need to update your linked server settings in the primary server.
From that you can determine which linked server definitions need to be changed.

I suggest that you name your linked servers according to their purpose, rather than with a server name.  (E.g. FinancialUpdates rather than AccountingSQL005.)  That way any code hidden away in stored procedures will not need to be changed when some server name changes or a database is moved to a new server.  This also suggests using separate linked servers, even if pointing to the same physical server, for different sets of data.

Just update the linked server by scripting out the definition and change the server name.  Then use sp_dropserver and sp_addlinkedserver to drop the old definition and add the new one.


Exclude databases from automatic full backup

Its easy using T-SQL.

I would suggest not to use Maiantenance plan. Instead go with a much flexible and widely adapted TSQL based solution - SQL Server Backup by Ola Hallengren

EXECUTE dbo.DatabaseBackup
@Databases = 'USER_DATABASES, -DB1,DB2', -- All user databases, except Db1, DB2
@Directory = 'C:\Backup',
@BackupType = 'FULL',
@Verify = 'Y',
@Compress = 'Y',
@CheckSum = 'Y',
@CleanupTime = 24


Removing "duplicates" within a time range

The easy way to make this work is to add an AUTO_INCREMENT to your transaction table and then to look for instances where the license plate value changes.  Consider this schema and query:  (you can also see it on SQL Fiddle)

This solution works by taking advantage of the fact that records are inserted in order and that a sequential number can be used to find adjacent records.  The records you're interested in are the ones where the sequence is disrupted.  In your case, that happens when the license plate value changes from one record to the next.

CREATE TABLE PLATE_VIEWS
( `SYSKEY` int not null AUTO_INCREMENT
, `PLATE` varchar(7)
, `DATE_IN` datetime
, `TIME_IN` varchar(10)
, PRIMARY KEY (SYSKEY)
)
;

INSERT INTO PLATE_VIEWS
    (`PLATE`, `Date_in`, `TIME_IN`)
VALUES
    ('DE54RPY', '2013-08-29', '14-24-30'),
    ('DE54RPY', '2013-08-29', '14-24-36'),
    ('DE54RPY', '2013-08-29', '14-24-42'),
    ('BDDX987', '2013-08-29', '15-55-05'),
    ('DE54RPY', '2013-08-29', '17-05-33'),
    ('DE54RPY', '2013-08-29', '17-05-48')
;

Now this query gets you the first capture of each plate:

SELECT
  A.SYSKEY
, A.PLATE
, A.DATE_IN
, A.TIME_IN
FROM PLATE_VIEWS A
LEFT OUTER JOIN PLATE_VIEWS B
ON A.SYSKEY = B.SYSKEY + 1
WHERE A.PLATE <> B.PLATE
   OR B.PLATE IS NULL

If you can't change the schema of your table to add an AUTO_INCREMENT column, then start by creating a temporary table that includes the auto increment and fill it with the range of plate scans that you are interested in.  Then run the above query against the temporary table.

EDIT: In and Out

OP says there are two lanes, one for coming in and one for going out.  If each lane has a camera and both capture transactions to the same table, you can catch the situation where a car leaves and comes back without any different cars coming in the meantime.  If we change the column names to be more generic (DATE_SEEN instead of DATE_IN etc.) and add a LANE column which says "in" or "out", then you can change the query like so:

SELECT
  A.SYSKEY
, A.PLATE
, A.DATE_SEEN
, A.TIME_SEEN
, A.LANE
FROM PLATE_VIEWS A
LEFT OUTER JOIN PLATE_VIEWS B
ON A.SYSKEY = B.SYSKEY + 1
WHERE A.PLATE <> B.PLATE
   OR A.LANE <> B.LANE
   OR B.PLATE IS NULL

This will catch a car that leaves and comes back.  (See SQL Fiddle)

This query also shows when cars leave.  If you want to ignore when cars leave, just change the WHERE clause like so:

WHERE (A.PLATE <> B.PLATE
   OR A.LANE <> B.LANE
   OR B.PLATE IS NULL)
  AND A.LANE = 'IN'


Centralized Database for user information

Design is largely driven by personal preference, and I don't have enough context to tell you if you're approach is one I would agree with or not.  However, if you wish to proceed with your current setup, I can at least provide some answers to your questions.

  Currently we can't have foreign keys to user id as that is in a different database.... Does anyone have a suggestion for a better way of handling this (other than bringing the databases together)?

As you suspect you cannot define an explicit foreign key relationship between objects in different databases.  This StackOverflow post walks through a simple way to configure an implied fkey relationship, however it uses triggers that come with inherit risks and this approach will be unable to take advantage of any performance improvements you would get with a typical foreign key relationship.

  Also, we have to hard code that separate database name in any stored procedures or queries that may need the user data, which makes it a pain to develop with as I can only have 1 development database rather than keeping one that works with different branches. Does anyone have a suggestion for a better way of handling this (other than bringing the databases together)?

If you want to avoid hard links in your code to other databases, configure synonyms for said objects that exist in another database so your code doesn't have to use any three-part queries.


SQLAnywhere: Visualize in real time CPU and RAM consumption of sql connections

You can get the approximate CPU consumption with one of the connection properties:

  ApproximateCPUTime

  Returns an estimate of the amount of CPU time accumulated by a given
  connection, in seconds. The value returned may differ from the actual
  value by as much as 50%, although typical variations are in the 5-10%
  range. On multi-processor computers, each CPU (or hyperthread or core)
  accumulates time, so the sum of accumulated times for all connections
  may be greater than the elapsed time. This property is supported on
  Windows and Linux.

See http://dcx.sybase.com/index.html#1201/en/dbadmin/connection-properties.html for usage in v12.0.1.

I'm not aware of any statistics on RAM consumption at connection level - I can see how it would be hard to get accurately as a lot of the resource might be shared with other connections.

Hope this helps.


Mysql sync_binlog = 50 and innodb_flush_log_at_trx_commit = 2 with replication

Another way to get past these possible problems is to use a Galera Cluster (eg, MariaDB 10 or PXC).  With Galera, you can (should) be sloppy about such syncs; instead rely on the cluster's ability to recover automatically and efficiently from a dead machine, whether dead from power, OS, or whatever.  One caveat, though:  You need to make sure a single failure (such as power) won't kill all nodes in the cluster simultaneously.

With Galera, you can write and read any node.  3 is the minimal number of nodes to provide automatic recovery from the loss of any one node.  You can configure it to do all writes to a specific node, thereby pretending to have Master+Slaves configuration.  (In some applications, this is actually beneficial.)

Back to your situation.  Set both of those settings to 1 or the Master; set them to 50 and 2 for the Slaves, then...  If a Slave dies, assume it is corrupt and rebuild it; do not try to recover the data, since the necessary syncs may not have been done.  To lighten the load on the Master, don't send standalone reads to it.


Connect as postgres user on AWS RDS to install extension to pg_catalog

Your attempt to write to the pg_catalog schema probably fails because you don't have a superuser on RDS, just a role with limited admin privileges. And Amazon is smart enough to prevent users from messing with the system catalogs. I.e.: no CREATE privilege in the schema pg_catalog.
It's generally not advisable to install additional modules like citext into the pg_catalog system schema. (Some exceptions have the schema preset.) Install it to public or some dedicated schema.

Is it recommended to install extensions into pg_catalog schema?

The "home" schema of the installed extension needs to be in the search_path. The manual:

The schema containing the citext operators must be in the current
search_path (typically public); if it is not, the normal
case-sensitive text operators will be invoked instead.

You can easily set the search_path for one / some / all users or databases or user / database combinations or temporarily or for the current session or generally:

How does the search_path influence identifier resolution and the “current schema”


SQL Server 2008 R2 Restore Database From File - "failed: 38(failed to retrieve text for this error. Reason: 15105)"

It looks like you are having a disk read problem or perhaps a corrupted backup.

I would try

a. Run RESTORE VERIFYONLY FROM DISK =
        N'S:\MSSQL10_50.MSSQLSERVER\MSSQL\Backup\Charms_OrangeGrove_290114.bak'
b. If it comes up clean try copying it to a different location
        (preferably a different drive on different physical hardware)

Here is a link where someone had a similar problem.  It has some additional steps he tried and mentions that he had a hardware problem.

http://social.msdn.microsoft.com/Forums/sqlserver/en-US/2a127a18-dfd0-4668-afc4-f5eb497f4977/sql-server-2008-restore-fails-msg-3203?forum=sqldisasterrecovery


What is the Difference between Archiving and Purging in Oracle database?

Purge Process

Purging is the process of freeing up space in the database or of deleting obsolete data that is not required by the system. The purge process can be based on the age of the data or the type of data.

Archive Process

Archiving is the process of backing up the obsolete data that will be deleted during the purge process. During the archive process, data will be moved from the main transactional tables to the backup tables.


Disk write in iotop when doing mysql "Copy to tmp table"

If your new instance of MySQL is creating temporary tables with the InnoDB storage engine, the innodb_io_capacity could be throttling the rate at which it writes pages to disk.

However, even throttled, I would expect a mere 400MB to be pretty quick to write to disk. Have you considered the possibility that the disk drives on your new server are simply faulty?

I would recommend running some benchmarks with sysbench or iozone to see if you can reproduce the slow I/O speed without MySQL.

Re your comment:

The best figures to look at for a database server are random read, random write, and mixed workload. Run a similar benchmark on your old database server, and compare them. If your new database server has significantly lower numbers, that would explain the difference you're seeing.


Pass multiples values to script into sqlplus in Oracle

You can use COLUMN command in SQL*PLUS as shown below.

SQL> COL column_value NEW_VALUE v_column_value
SQL> select column_name column_value from table(sys.dbms_debug_vc2coll('Logons Per Sec', 'Executions Per Sec')));

SQL> @"test.sql" &&v_column_value

Documentation: COLUMN in SQL*PLUS


Foreign Key constraint on fixed value field

It sounds like you have a "One True Lookup Table" (OTLT) anti-pattern and you are mixing entities in this table. You've found why it isn't a good idea:

can't have filtered foriegn keys
can't FK to constants
can't have multiple parents

Your sample code above is confusing (you have multiple parents for the same Code column) so I'll give you what I understand

CREATE TABLE OutlineFilesCostCentre (
    CostCentreCode ...NOT NULL --PK
    ...
    )

CREATE TABLE OutlineFilesLocations (
    LocationCode ... NOT NULL --PK
    ...
    )

CREATE TABLE Assets (
    ...
    CostCentreCode ... NOT NULL,
    LocationCode ... NOT NULL,
    ...

    CONSTRAINT FK_Assets_CostCentre" FOREIGN KEY ("CostCentreCode")
                REFERENCES "dbo"."OutlineFilesCostCentre" ("CostCentreCode"),
    CONSTRAINT FK_Assets_Locations" FOREIGN KEY ("LocationCode")
                REFERENCES "dbo"."OutlineFilesLocations" ("LocationCode")
   ...)

If you have 30 codes to lookup you will have 30 lookup tables: this is correct.

If you insist on OTLT you'll have to add extra columns to store type in Assets and FK them to your OTLT. I wouldn't do this.

    ...
    CostCentreType char(2) NOT NULL,
    CostCentreCode ... NOT NULL,
    LocationType  char(2) NOT NULL,
    LocationCode ... NOT NULL,
    ...

Or use triggers to maintain the correct codes. I wouldn't do this either.


I think I found my problem. My testing server install was an ubuntu mysql default; not percona.

https://bugs.launchpad.net/percona-xtrabackup/+bug/655584/comments/3


I just tried quickly to find an answer because there is a lot here, but this is a common question. Didn't find quite what I wanted yet so a few thoughts to guide you:

First: It depends is the default answer here.

It really depends on what the performance characteristics are of your system. You should review what your waits and filestats look like and consider where your pain points are and build according to that. There are sizing concerns and performance concerns.

A few thoughts to guide you:

RAID 5 can be fine for data files unless you are doing some heavy writes and performance becomes a concern. So this isn't always good, but a place I feel safe about RAID 5, again your mileage really varies based on your performance needs.
Mirroring or RAID 10 is where I tend to like to go with log files and TempDB.
In some environments, TempDB needs to be isolated onto it's own first because it is a shared resource and used when queries spill over from memory to disk as well.. In some environments, log files need to be isolated and get better performance because of the insert activity.. In some environments, data files need the best IO (heavy read/reporting).
You need to look at Filestats and Waitstats and Perfmon and really see where your pains are.

Using multiple file groups is a great approach/answer for certain workloads. Separating TempDB is usually a best practice though I'd never put TempDB on RAID 5 typically.

And if the environment is pushing no IO? Having no IO waits? I've seen and placed environments all one one big happy set of drives and things have been fine. It really depends on a lot.

I'd spend some time profiling your workload. Look at the PAL tool and run some perfmon scripts on your existing environment. Look at your SQL Server File Statistics. Read about some best practices in SQL Server IO, and make informed decisions and monitor to see how they look as you go.


Understanding SQL Developer user accounts

Yes, you did:

create user dummy identified by dummy;

By default, for local logins, Oracle databases use OS authentication based on OS user group membership. Any user who is a member of the dba group or whatever group was specified as the OSDBA group can log in locally as sysdba without providing a password (or providing any password).

I recommend that you forget this part:
alter session set "_ORACLE_SCRIPT"=true;
create user dummy identified by dummy;

Tutorials with this workaround (alter session set "_ORACLE_SCRIPT"=true;) are the result of laziness or lack of knowledge about the CDB architecture introduced in Oracle 12c. That is how incorrect "solutions" spread.
The author forgot to tell you that by doing the above, you break a basic principle of the CDB architecture (because you create a new user that is treated as built-in Oracle-managed users + your data in the future should be stored in pluggable databases, not the root container). That is not how a container database is used.
Instead of doing the above, after getting the SQL prompt with this docker exec -it 0500ab7ee75c bash -c "source /home/oracle/.bashrc; sqlplus /nolog", you should follow as below.
Connect to the database instance, below connects to the root container of the database. Do not bother with a password at this point:
connect / as sysdba

List pluggable databases, you should see a pluggable database named ORCLDB1.
SQL> show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 ORCLPDB1                       READ WRITE NO
SQL>

Connect to that pluggable database:
SQL> show con_name

CON_NAME
------------------------------
CDB$ROOT
SQL> alter session set container=ORCLPDB1;

Session altered.

SQL> show con_name

CON_NAME
------------------------------
ORCLPDB1
SQL>

And create the user there:
SQL> create user dummy identified by dummy;

User created.

SQL> GRANT ALL PRIVILEGES TO dummy;

Grant succeeded.

SQL>

Now you can connect with the dummy user, which is in the ORCLPDB1 pluggable database, and not in the root container. For that, use orclpdb1.localdomain as service name and not ORCLCDB.localdomain.


A CPU core being in an "IO wait" state inthe OS means that there are tasks that could be assigned for that core to work on, but they are all waiting for an IO operation to complete.
In your case displayed there, you have only one task that really busy and that is mysql, and it is spending most of its time waiting for IO operations to complete. This is not unusual. If you run cat /dev/sda > /dev/null you'll see the IO wait count jump up, more so if you run that a couple of times concurrently.
It may indicate that ere are things you could optimise to speed up your application(s) considerably:

if the delay is in making updates, then getting faster drives
if the delay is due to queries performing scans of large tables or indexes, then by improving the queries or your index choices
if the delay is mostly due to read IO and there isn't much scanning going on then your common working set may be larger than can fit in memory and adding/allocating more RAM may be helpful
if the delay is a mix or read/write or mostly random access read, then improving IO latency would be useful (the data rates displayed there are not high given the drive is 100% busy, so I assume you have a traditional drive and there is significant random access happening, so upgrading to SSD may help a lot)

Of course if everything is responding with acceptable speed this may be normal for your app and system and you might not need to alter anything yet (though it is always worth taking a look at index use and so forth, and proactively monitoring CPU & IO metrics, so you can pre-emptively spot and perhaps fix future problems that will happen as your use scales up before they actually become significant).


Connecting to SQL Server using Oracle SQL Developer

I followed these instructions to add the jTDS Java library to Oracle SQL Developer. Basically it's about downloading the jTDS zip, unzipping it in some place you can reach later and then add the jTDS.jar as a JDBC third party driver in SQL Developer ((In the main menu) Tools > Preferences > (On the tree menu in the new window) Database > Third party JDBC drivers > Click on 'Add entry')

I was then able to use my credentials to connect to a SQL Server instance/database using the following connection details. I'm currently using Oracle Developer 4.0.1.14.

The default port for SQL Server instance is 1433, however your SQL Server instance may be running on a non default port. The following site has different methods to determine the port.

http://www.mssqltips.com/sqlservertip/2495/identify-sql-server-tcp-ip-port-being-used/


Windows Logs Application is full of "Login failed for user 'sa'. Reason: Password did not match that for the login provided. [CLIENT: ****]"

A login attempt was made from the IP address given. The attempt was to log in using the credential sa. A password was supplied but it was not the correct password.

sa is the default sysadmin credential in SQL Server. Errorlog spam with this message usually means 1 of 2 things.

You have an application that was configured to use the sa crendential
You've made yourself a honeypot and a malicious actor has gained a pipe to your database server in an attempt to log in and take it over.

If it's #1, then go to the application you're hosting at that IP address and change its webconfig.

If it's #2, then you need to blacklist that IP address - preferably by firewalling your database server off from the public internet (which you should really do anyways to be frank).

See also:

sa best practices
Securing SQL Server


View permission to SQL Server logs on SSMS GUI

Yes, this is possible by creating a user with 'securityadmin' server role and then if required Deny his access to SQL Server Query Window through LOGON Trigger

USE [master]
        GO

    ----Create Login
    CREATE LOGIN [DBA_ErrorLogUser]
    WITH PASSWORD=N'123', DEFAULT_DATABASE=[master],
    CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
    GO

    --- Add server Add securityadmin
    --  To view SQL Server error logs user must be a part of securityadmin server role
    EXEC master..sp_addsrvrolemember
    @loginame = N'DBA_ErrorLogUser', @rolename = N'securityadmin'
    GO

    --- Mapp user with login to grant access to logs
    CREATE USER [DBA_ErrorLogUser] FOR LOGIN [DBA_ErrorLogUser]
    GO

    --- Deny Alter to any Login
    DENY ALTER ANY LOGIN TO DBA_ErrorLogUser
    GO

    --- Grant permission to view Sql Server Logs
    Grant EXECUTE ON master.sys.xp_readerrorlog TO DBA_ErrorLogUser
    GO

    --- Create a log on Trigger to deny access to Query Window
    IF EXISTS ( SELECT  *
                FROM    master.sys.server_triggers
                WHERE   parent_class_desc = 'SERVER'
                        AND name = N'Deny_QueryWindowLogin_Trigger' )
        DROP TRIGGER [Deny_QueryWindowLogin_Trigger] ON ALL SERVER
    GO

    Create TRIGGER Deny_QueryWindowLogin_Trigger ON ALL SERVER
WITH EXECUTE AS 'sa'
FOR LOGON
AS
BEGIN

    DECLARE @data XML
    SET @data = EVENTDATA()

    DECLARE @AppName SYSNAME,
            @LoginName SYSNAME

    SELECT  @AppName = [program_name]
    FROM    sys.dm_exec_sessions
    WHERE   session_id = @data.value('(/EVENT_INSTANCE/SPID)[1]', 'int')

    SELECT  @LoginName = @data.value('(/EVENT_INSTANCE/LoginName)[1]', 'sysname')

    IF @AppName= 'Microsoft SQL Server Management Studio - Query' AND @LoginName = 'DBA_ErrorLogUser'
        BEGIN
            ROLLBACK ; --Disconnect the session

        END
END ;

Note: Must Read securityadmin Server Role Permissions


Update to MySQL 5.7 and high disk space consumption

ibdata1 never shrinks.
.ibd files never shrink.
When a table is in ibdata1 and is ALTERed, space in that file is temporarily consumed, thereby possible stretching the file.
With .ibd, an ALTER will probably create a new copy, then toss the old one, thereby temporarily using diskspace, then releasing it.
There are too many variables to make any predictions.  But a fairly safe rule is to never fill up more than half the disk.

The best thing going forward is rather painful:

Dump all the tables
Toss ibdata1 (or make a fresh install)
Turn on innodb_file_per_table
Reload the dump file.

That will

leave a small ibdata1 file, which is necessary for meta data, etc.
Create each table in its own .ibd file.
Allow future ALTERs to only temporarily consume extra disk space.

Another thing to note:  innodb_file_per_table (ON or OFF) applies only to tables CREATEd or ALTERed after that.  All existing tables say either in ibdata1 or .ibd until such an action is taken.  Correlary:  It is possible to have some tables in ibdata1 and some in separate .ibd files.  It may be beneficial to put small tables in ibdata1 (OFF) and large ones in .ibd files (ON).


Does a read apply locks when run outside of a transaction?

The way SQL Server handles locks is based on the isolation level that you are using and the state of the transaction. The default isolation level for SQL Server is READ COMMITTED. So if you perform a SELECT command without an explicit transaction, then the dynamic locking will decide the lock granularity.

For READ COMMITTED isolation level, the SHARED locks acquired on the index keys/RID (row identifier) will be released after the scan/seek has completed.

Refer to Locking in SQL Server and
Isolation Levels


How to change the datatype of a column from integer to money?

Just try the following command.

ALTER TABLE products ALTER COLUMN price TYPE money using price::text::money

Non-quoted numeric values can be converted to money by casting the numeric value to text and then money


You can specify the name of the database superuser at the time of running initdb.
It will default to the name of the user running the initdb command itself, but specifying --username or -U will change that.
https://www.postgresql.org/docs/9.6/app-initdb.html
Let me highly suggest that you don't even for a second consider trying to take away any privileges from the database superuser role, regardless of its name.
Many privilege checks within the database are coded in such a way that the superuser is always allowed to continue, no questions asked. It is, after all, the superuser role.
I can only assume that your auditors are either clueless or were referring to some use of the postgres role by applications or by developers. In the latter cases, it is definitely advisable to restrict access and create appropriate roles/users within the database for these tasks.
This is my recommendation: make sure that user postgres cannot login from anywhere (besides locally via unix sockets and localhost, and possibly via network for streaming replicas) via pg_hba (and use at least peer and md5 authentication).
Changing the superuser's name does in no way increase security.


Is it good idea to use RAID 1 mirroring to back up database content

Relying on RAID 1 as a backup is not a great idea. If you had data corruption in the database, it would likely appear on both drives - certainly if it were caused by faulty SQL commands being executed. RAID 1 will only protect you from physical failure of one of the two drives, not corruption caused by a duff application or end-users.

In addition, backups that are stored on the same machine is also asking for trouble. You should always store backups off the source machine, and use something like mysqldump or the Percona Xtrabackup tool to perform these backups.

If you can mount an external volume onto the machine through NFS (or CIFS if Windows), then use Xtrabackup to backup the database content onto the additional volume, which is physically on a separate machine, then if you lost the machine, you could rebuild and restore from the backup.

Ensure you also backup your configuration, such as OS users, MySQL configuration files, etc. as without these, restoration may prove trickier.

Hope that all makes sense?

Dave


You have two possibilities: PIVOT and a crosstab:

DECLARE @sampleData AS TABLE (
    SiteId int,
    SiteName varchar(50),
    EventCode int,
    EventCount int
);

INSERT INTO @sampleData VALUES
(1,'Abbottabad',43,53),
(1,'Abbottabad',44,52);

SELECT *
FROM @sampleData
PIVOT (SUM(EventCount) FOR EventCode IN ([43],[44])) AS p;

SELECT
    SiteId,
    SiteName,
    SUM(CASE EventCode WHEN 43 THEN EventCount END) AS [OpenEvent Count(Event = 43)],
    SUM(CASE EventCode WHEN 44 THEN EventCount END) AS [CloseEvent Count(Event = 44)]
FROM @sampleData
GROUP BY SiteId,
    SiteName;

Personally I prefer a crosstab, as it is more flexible.

Edit: changed to original table to clarify the technique.

IF OBJECT_ID('tempdb..#MyLogs') IS NOT NULL
    DROP TABLE #myLogs

CREATE TABLE #MyLogs(
    SiteId int,
    SiteName varchar(50),
    EventCode int
);

-- Insert some test data
INSERT INTO #MyLogs
SELECT 1 AS SiteId, 'Abbottabad' AS SiteName, 43 AS EventCode
FROM master.dbo.spt_values
WHERE type = 'P'
    AND number BETWEEN 1 AND 53;

INSERT INTO #MyLogs
SELECT 1 AS SiteId, 'Abbottabad' AS SiteName, 44 AS EventCode
FROM master.dbo.spt_values
WHERE type = 'P'
    AND number BETWEEN 1 AND 52

SELECT
    SiteId,
    SiteName,
    SUM(CASE EventCode WHEN 43 THEN 1 END) AS [OpenEvent Count(Event = 43)],
    SUM(CASE EventCode WHEN 44 THEN 1 END) AS [CloseEvent Count(Event = 44)]
FROM #MyLogs
GROUP BY SiteId,
    SiteName;


SQL Server: Getting string before the last occurrence '>'

SELECT LEFT(assocname, LEN(assocname) - CHARINDEX('>',REVERSE(assocname)))

So we're reversing it, finding the first '>', then subtracting that from the length to find how far that character is from the beginning. Then take all characters up to that point.


xenforo vulnerability, limit max open connections by ip address

I have an old post from March 2012 that limits a specific user's connections by the hour

How can I limit MySQL connections?

You also need to remove anonymous users : Cannot drop anonymous user from mysql.user

Get rid of test user access : MySQL : Why are there "test" entries in mysql.db?

Get rid of all users with no password with

DELETE FROM mysql.user WHERE password='';
FLUSH PRIVILEGES;

You could add a blanket rule on a specific IP addr (like 10.20.30.40)

INSERT INTO mysql.user SET
user='',host='10.20.30.40',
max_user_connections = 1,
max_connections = 1;
FLUSH PRIVILEGES;

Give Them a Try !!!


The problem may be a namespace issue.  You can \dn to list namespaces.

Keep in mind that by default \d only lists relations in the search path, and you can run show search_path to see what this is.

If you want to list relations in a namespace outside the search path, \d mynamespace.* will list them and their attributes.  You can also:

set search_path = 'mynamespace';
\d
set search_path = 'public';

To temporarily change the search path for purposes of using \d


Is this way of MySQL shutdown via bat file sufficient to minimise corruption?

If you want to shutdown MySQL effectively, you need to run several things
@echo off
rem
rem Flush Everything and its Grandmother
rem
echo "Stopping MySQL Service"
"C:\host\MariaDB\bin\mysql.exe" -u root -prootpass -ANe"SET GLOBAL innodb_fast_shutdown=0; STOP SLAVE; FLUSH BINARY LOGS; FLUSH TABLES"
"C:\host\MariaDB\bin\mysqladmin.exe" -u root -prootpass shutdown
echo "Stopped MySQL Service"
pause

The first line does the following

STOP SLAVE : If this MySQL Service is a Slave, it will close the IO and SQL Threads and close the file handles opened against the relay logs. If replication is not enabled, it will be just a warning.
FLUSH BINARY LOGS : If binary logging is enabled, it will close the current binary log (which flushes it), and open a new one (file size of the new binary logs will be less than 200 bytes). If binary logging is disabled, it will be just a warning.
FLUSH TABLES : This closes any open file handles against all open tables, alerting the OS to perform the disk flush of all table changes in memory.
SET GLOBAL innodb_fast_shutdown=0 : The default value for innodb_fast_shutdown is 1. This allows InnoDB to suspend its changes in the redo logs and the double write buffer. Thus, changes will be applied the next time MySQL Service is started. This is also known as crash recovery upon startup. By setting innodb_fast_shutdown to 0, this causes InnoDB to apply changes during the shutdown. That will result in the MySQL Service starting faster because it does not need to do crash recovery.

I have written about using innodb_fast_shutdown many times

Apr 15, 2012 : Mysql Innodb: InnoDB: ERROR: the age of the last checkpoint is InnoDB: which exceeds the log group capacity
Mar 06, 2013 : How to properly kill MySQL?
Apr 23, 2013 : Start/Stop MySQL
May 05, 2013 : Issue after moving the ib_logfile1 and ib_logfile0 files
Dec 09, 2013 : Purge old records from mysql ibdata1 file
Jan 07, 2015 : Flush/Clean MySql ib_logfile0 ib_logfile1

GIVE IT A TRY !!!
NOTE : The reason for all the other commands is to close as many different file handles as possible before the actual shutdown process. Each closed file handle will induce a flush of the table if there are any disk changes. This gives the shutdown process less to work on, focusing its attention mainly on rolling forward all changes in the InnoDB Plumbing.
Here is a Pictorial Representation of InnoDB

This picture was made by Percona CTO Vadim Tkachenko
This picture should let you see what moving parts are in motion during the shutdown.
UPDATE 2016-10-13 07:45 EDT
@a_horse_with_no_name commented

Are you saying that a "regular" shutdown does not in fact cleanly shut down the database engine? (especially that it will not flush everything to disk?)

When it comes InnoDB, shutting down MySQL is subject to interpretation. This is why innodb_fast_shutdown exists. The documentation says:

The InnoDB shutdown mode. If the value is 0, InnoDB does a slow shutdown, a full purge and a change buffer merge before shutting down. If the value is 1 (the default), InnoDB skips these operations at shutdown, a process known as a fast shutdown. If the value is 2, InnoDB flushes its logs and shuts down cold, as if MySQL had crashed; no committed transactions are lost, but the crash recovery operation makes the next startup take longer.
The slow shutdown can take minutes, or even hours in extreme cases where substantial amounts of data are still buffered. Use the slow shutdown technique before upgrading or downgrading between MySQL major releases, so that all data files are fully prepared in case the upgrade process updates the file format.
Use innodb_fast_shutdown=2 in emergency or troubleshooting situations, to get the absolute fastest shutdown if data is at risk of corruption.

The result of a shutdown will leave InnoDB in one of these three states, two of them requiring some work during crash recovery at startup.
The reason for the answer I posted is the OS. In my opinion, the Linux OS and the right hardware provide a better environment for disk writing and flushing. I don't have that same confidence with Windows. My suggestions are just to give mysqld additional advantages in Windows.
InnoDB can holistically shutdown. My personal preference is to use innodb_fast_shutdown=0 so I don't worry about the redo logs during maintenenaces or worry about the OS.


Use the password command after logging in to change your password:

SQL> password
Changing password for SYSTEM
Old password:
New password:
Retype new password:
Password changed
SQL>


Should I create many ID for a same Person/entity in my DB?

Properly normalized it will have 3 tables as follows:

Kid

kid_id (pk)
classroom_id (fk to Classroom)

Teacher

teacher_id (pk)
name

Classroom

classroom_id (pk)
teacher_id (fk to teacher)

This way you have no duplication and no update anomalies.


write "ENGINE=INNODB DEFAULT CHARSET=UTF8MB4" once for the whole script

You can do it relatively easily by following these steps:

1) Take a dump of your database - for the sake of this example, I've used the MySQL sakila sample database "designed to represent a DVD rental store". The command I used was:

  mysqldump -u root -p sakila > sakila.sql

You will then be asked for the password for the user that you are using - in this case I used root - this is not best practice!

Your dumpfile will contain many sections (2 examples shown) like the following (16 for the Sakila schema - one for each table in your database/schema):

  DROP TABLE IF EXISTS `actor`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!50503 SET character_set_client = utf8mb4 */;
CREATE TABLE `actor` (
  `actor_id` smallint unsigned NOT NULL AUTO_INCREMENT,
  `first_name` varchar(45) NOT NULL,
  `last_name` varchar(45) NOT NULL,
  `last_update` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`actor_id`),
  KEY `idx_actor_last_name` (`last_name`)
) ENGINE=InnoDB AUTO_INCREMENT=201 DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */;

and

DROP TABLE IF EXISTS `category`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!50503 SET character_set_client = utf8mb4 */;
CREATE TABLE `category` (
  `category_id` tinyint unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(25) NOT NULL,
  `last_update` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`category_id`)
) ENGINE=InnoDB AUTO_INCREMENT=17 DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */;

2) The key lines in these sections are at the ends of the table definitions:

) ENGINE=InnoDB AUTO_INCREMENT=201 DEFAULT CHARSET=utf8;

and

) ENGINE=InnoDB AUTO_INCREMENT=17 DEFAULT CHARSET=utf8;

3) use your editor of choice to change utf8 to utf8mb4. Or, if you're on Linux, you can use the sed utility:

sed 's/utf8/utf8mb4/g' sakila.sql > sakila.sql.bis // the "g" is for "global"

4) Check your output file (belt and braces!) using your editor.

5) Drop your Sakila schema using your mysql prompt or from MySQL Workbench (or your tool of choice):

DROP SCHEMA sakila;  -- "DROP DATABASE" does the same thing

6) Recreate an empty Sakila schema.

CREATE SCHEMA sakila;  -- ditto "CREATE DATABASE"

7) Having verified sakila.sql.bis, rename it sakila.sql overwriting the old file.

8) Load your modified SQL file, again using mysqldump (in reverse so to speak - note the change of > to < - output to input).

mysqldump -u root -p sakila < sakila.sql

Your final step is to set the default character set to utf8mb4 in my.cnf/my.ini.

[client]
default-character-set=utf8mb4

[mysql]
default-character-set=utf8mb4

[mysqld]
collation-server = utf8_unicode_ci
init-connect='SET NAMES utf8mb4'
character-set-server = utf8mb4

So now, all your tables will be UTF8MB4 and any future tables will be created with the same character set. You can, as mentioned in the last link, also use different collations per table and/or per field, but that's not part of the question - an internet search should be sufficient to deal with this requirement should you need it.


Will a clustered index speed up this query? (Inner join with a where clause)

You are totally trashing the index on NewTable.C2
Just drop / disable the index and then build / rebuild it after the insert is complete

And you need to break this up as you are putting a massive load on the transaction log

If you want to optimize the select then just run the select alone

INSERT INTO NewTable (C1, C2, C3, C4, C5, C6, C7)
SELECT OldTable1.C1, OldTable1.C2, OldTable1.C3, OldTable1.C4
     , OldTable1.C5, OldTable1.C6, OldTable1.C7
FROM OldTable1
JOIN OldTable2.C5
  ON OldTable2.C5 = OldTable1.C5
 AND OldTable2.C8 >= 70
 AND OldTable2.C8 <= 89;


Is it bad to create many mysql temporary tables simultaneously?

You want to prevent making temp tables as much as possible : Prevent copying to temp table (sql)

They do not work well in MySQL Replication : How are binlogs updated for MySQL temporary tables?

Since temp tables are a fact of life in the DB world, you may have to make some unorthodox changes to accommodate their existence.

Normally, mysqld has the habit of placing tmp tables in /tmp or wherever tmpdir is configured. That's usually on some poor unsuspecting disk.

An interesting alternative would be to setup a RAM disk and reconfigure tmpdir to use it

STEP 01 : Create a Mount Point for a RAM Disk

mkdir /var/tmpfs

STEP 02 : Add the RAM disk to /etc/fstab (16GB)

echo "none   /var/tmpfs              tmpfs   defaults,size=16g        1 2" >> /etc/fstab

STEP 03 : Add this line to /etc/my.cnf

[mysqld]
tmpdir=/var/tmpfs

STEP 04 : Enable the RAM Disk

You can do one of the following:

Just reboot the DB Server
mount -t tmpfs -o size=16g none /var/tmpfs

Before you do this, make sure you have enough RAM

Give it a Try !!!


Does DBCC SHRINKDATABASE free physical disk space during its run?

DBCC SHRINKDATABASE does not rollback when the process is killed so theoretically you'd be able to run it for a period of a time, kill the process, and have some reclaimed space (assuming there is any that can be reclaimed). See this for more information.
Also please see and research the warnings I provided against shrinking in my answer to your other StackExchange question.

...[it's] heavy operations, generally nonsensical to do because if
they grew to a certain size they'll likely grow back to that size soon
anyway and a growth operation is also heavy, and shrinking any
database files (except Temporary) does have a small risk of data
corruption.


Differential Backup of A Table In Sql Server

Consider using Transactional Replication

https://docs.microsoft.com/en-us/sql/relational-databases/replication/transactional/transactional-replication?view=sql-server-ver15

https://docs.microsoft.com/en-us/sql/relational-databases/replication/tutorial-replicating-data-between-continuously-connected-servers?view=sql-server-ver15

It allows keep up-to-date copies of your tables at reporting server, without copying any other tables/data that you do not need


Benchmarking set up over HDD+SSD combination?

For database performance it doesn't matter much on what storage device the operating system or the software is installed. What matters is the database storage and the amount of RAM.

When fixing database performance problems, the first thing to try is to improve the query performance and resource usage.


Unable to alter table - reason code is 10

Thanks to Charles for pointing out this command: DSPFD AMMLIBT/SAGRPPF

Which showed me that:

  Maximum members . . . . . . . . . . . . . . : MAXMBRS    *NOMAX

To fix this, I needed to change '*NOMAX' to '1'. To do this, I went to my greenscreen and did:

type chgpf ammlibc/sagrppf
F4
F10
Move cursor to 'Maximum members'
Change '*NOMAX' to '1'
Enter


Why do we need partitions in PostgresSQL? (or any other RDBMS)

A general goal in designing data storage and retrieval systems is that query time should scale with the amount of data being retrieved, not the amount of data that exists in total.  Partitioning is a powerful tool for achieving that goal.

Consider a table of session data that looks something like:

create table clickstream (
  clickstream_id bigserial primary key
, created timestamp default now() not null
, user_agent text
, client_ip inet
, entry_page text
, session_id text
--and so on...
);

Obviously you would have an index on created which would allow you to perform queries on a particular date/time range of the data.  If you get 10k sessions a day and you have 3 months of data online, querying for a day's worth of data works quite well.  If you get 1M sessions a day and you have 4 years of data online, this works poorly.

But why?  The nature of the I/O you're performing -- random reads -- doesn't scale well.  The query engine has to find the location in the created B-tree that corresponds to the start of your range, and likewise the end of your range, and traverse every element of the tree in between, and read each row individually.  Or rather, read each relevant block individually (since all I/O is at the block level), but many rows means many blocks, especially for wide tables.

What you would prefer to do is read all of those blocks sequentially, as in a full table scan.  In fact what if you are doing this kind of operation over and over again, you'll find yourself creating a temporary table with just the day's data and querying that instead, as an optimization.  This is the kind of optimization that partitioning let's the query engine do automatically.

Partition the table by day, and when you write (for example) select ... from clickstream where created >= current_date-1 and created < current_date the query engine will understand that this data falls entirely within one partition, and then compute that even if created is indexed on that partition that using said index is not worthwhile, and perform a much faster sequential scan.

Now, that's for an analytic workload.  Partitioning can also be valuable in a transactional workload, but in more limited ways.  Obviously if your transactional system is doing range scans in real-time then the exact same pattern can apply.  But it can also be useful to partition on certain keys.  For example if you have an orders table with a customer_id, and the nature of your business is such that most customers have many orders, and the orders table is always accessed using customer_id, you can divide the orders table into an arbitrary number of partitions, say 64, on a hash of customer_id.  This has the effect of reducing the overall table and index size when performing a query.  More of the rows in a particular block will be relevant, making read-ahead more effective. Partition-local B-trees will have fewer nodes, and therefore fewer blocks, and potentially even less depth.

To get back to the heart of your question, which if I understand it is "why do we have to do this at all?", the simple reality is that YOU always know more about your data than the RDBMS does.  This is the same reason query planners sometimes make mistakes.  Query planners can be supplied with better statistics, or given hints, but they're just not smart enough to wholesale restructure your data for you.  That is a high-level pattern recognition task that your brain is better suited to than any piece of code.  What's more, this kind of restructuring must be carefully considered and then implemented by hand.  I wouldn't even trust an RDBMS to automatically create indexes for me, much less rebuild the physical layout of a multi-terabyte table!

(One final note, PostgreSQL in particular only sorta supports partitioning. It's much easier to implement all of the above in an RDBMS like Oracle that supports it as a first-class construct.)


Deeptechtons, we really would like to see your answer, but since you haven't posted it yet, here is how it could be done.  This SQL is for Oracle not SQL Server, so it would have to be translated.  There are details missing from your post, so some assumptions used are likely invalid.

with AllEmployees As
   (select distinct PersonName from samples)
select * from AllEmployees
minus
select PersonName from samples
where trunc(not_avl_date) = trunc(sysdate)
and (not_avl_for = 1
   or (not_avl_for = 2 and :timespan LIKE '%AM%')
   or (not_avl_for = 3 and :timespan LIKE '%PM%')
);

Sample data:

create table samples (rid Number(3), PersonName Varchar2(20),
   not_avl_date date, not_avl_for integer);

insert into samples values (1,'Peter',sysdate-2, 1);
insert into samples values (2,'Peter',sysdate-1, 2);
insert into samples values (3,'Paul',sysdate-1, 3);
insert into samples values (4,'Sue',sysdate, 3);
insert into samples values (5,'Bob',sysdate-2, 2);
insert into samples values (6,'Zach',sysdate+1, 1);


Changing the column width in Transactional Replication

Changing the width of a column on a replicated table should be able to be done with a direct change without having to rebuild the replication.


Dynamically creating multiple triggers for individual rows sounds quite nasty. This is not likely to scale well.

After reading your update I would suggest you implement 1 trigger that fires for all CRUD operations on the SELLS table. This trigger checks to see if the modified rows of SELLS are in the SUBSCRIPTION table. If they are send a notification, if not don't send notification.