Select consecutive rows that are in a date range of each other

The logic looks simple at first, but it's quite complicated to get it right.

Let's have a working solution first, and worry about performance later. Tested at rextester.com:

SELECT t.id, t.dateID, t.name
FROM  myTab AS t
WHERE
       ( SELECT b.dateID
         FROM myTab AS b
         WHERE t.name = b.name
           AND b.dateID < t.dateID
         ORDER BY b.dateID DESC
         LIMIT 1
       ) + INTERVAL 1 MONTH  <=  t.dateID
    OR
       t.dateID + INTERVAL 1 MONTH <=
       ( SELECT b.dateID
         FROM myTab AS b
         WHERE t.name = b.name
           AND t.dateID < b.dateID
         ORDER BY b.dateID ASC
         LIMIT 1
       )
 ;

Regarding efficiency: the query will perform rather poorly. An index on (name, dateID, id) will help but the query will still need to do 2 subqueries for each row of the table.


Is it okay to use MySQL Workbench with MariaDB?

This warning is shown for any other but the supported MySQL versions (at the time being these are 5.6, 5.7 and 8.0). The application uses language features and other resources that may or may not work with other servers. It's actually not tested, which is the main reason why this warning comes up.

MariaDB might be based on MySQL, but over time it more and more diverts from its root and it's very likely that there is an increasing number of incompatibilities.


MySQL Cluster - Got error 1601 'Out extents, tablespace full' from NDB

First 2500*50kB ~= 128MB. Then if you will check CREATE TABLESPACE syntax default INITIAL_SIZE for the datafile is 128MB!

So if you want to store more data you can either specify INITIAL_SIZE you need while creating tablespace or you can ALTER TABLESPACE ADD DATAFILE.

By specifying STORAGE DISK in CREATE TABLE statement you stated that you want on disk table not in memory. With such tables ALL REPORT MEMORYUSAGE shows nothing meaningful for that table.

If you want "classic" NDB table which is stored in memory just skip TABLESPACE ts1 STORAGE DISK modifiers and then ALL REPORT MEMORYUSAGE will show you how much memory is used for the data and indexes.


Convert seconds into HH:MM:SS format in MySQL

https://dev.mysql.com/doc/refman/5.7/en/date-and-time-functions.html

see

GET_FORMAT()
SEC_TO_TIME()
TIME_FORMAT()

and possibly others.

Not knowing the datatype of your values, I can't be more specific.


Make SQL Server Agent retry failed script with Try/Catch

Did you use RAISERROR with severity 16?
This is "user defined error" and should be picked up by SQL Server agent

Did you set the job step to retry (Retry Interval and Retry Attempts)

Finally, is the error terminating (severity 20) such that the CATCH block isn't hit?


Delete data from a billion row table based on where clause

Figured a better way to do this

Insert into new_tbl select * from old_tbl where start_date < now -INTERVAL '2 years'
During downtime window:

insert into new_tbl select * from old_tbl where id not in (select id from new_tbl ) and id not in ( select id from old_tbl)

Rename old_tbl to old_tbl_drop and new_tbl to old_tbl.
Drop old_tbl_drop.


There's a couple of ways you can check how many days have past since you installed the evaluation edition:
From Pinal Dave:
SELECT create_date AS 'SQL Server Installation Date'
       ,DATEADD(dd, 180, create_date) AS 'SQL Instance will expire on'
sys.server_principals
WHERE NAME = 'NT AUTHORITY\SYSTEM'

Alternative solution - Finding Your SQL Server Evaluation Time Limit:
EXEC sp_configure 'show advanced options', 1;
RECONFIGURE
GO

EXEC sp_configure 'Agent XPs', 1;
RECONFIGURE
GO

DECLARE @RemainingTime INT
DECLARE @InstanceName SYSNAME
SELECT @InstanceName = CONVERT(SYSNAME, SERVERPROPERTY('InstanceName'))
EXEC @RemainingTime = xp_qv '2715127595', @InstanceName
SELECT @RemainingTime 'Remaining evaluation days:'

GO

The evaluation edition is a full edition without limitation (other than the timeframe you can use it for), as mentioned in the License section of the Docker hub.
I don't believe you need to enter a serial after you've purchased it (you just need to retain the purchase information Microsoft provides in case you were audited). You can follow this guide on how to upgrade to a non-evaluation edition.


SELECT id, type, rate
FROM
( ( SELECT id, type, rate, 25-rate AS diff
    FROM rates
    WHERE type = 'A'
      AND rate < 25
    ORDER BY rate DESC
      LIMIT 3
  )
  UNION ALL
  ( SELECT id, type, rate, rate-25 AS diff
    FROM rates
    WHERE type = 'A'
      AND rate >= 25
    ORDER BY rate ASC
      LIMIT 3
  )
) AS tmp
ORDER BY diff
LIMIT 3 ;

Test at SQL-Fiddle


Remove string after first and second specific character in a string

For a SQL solution, try something like this. It sounded like the section of text you wanted.

-- the same logic will work against a column in a query too.
if object_id('tempdb..#textThing','U') is not null
    begin
        drop table #textThing;
    end;
create table #textThing (
     id int identity(1,1) not null primary key
    ,text_stuff text
    ,varchar_stuff varchar(max)
);
insert into #textThing (text_stuff, varchar_stuff)
select 'Confirmed Diagnosis
================
Stroke || Entered: 19-02-2019 01:36:39 || Notes: brain fell out, recommend duct tape

Suspected Diagnosis
================
Transient ischaemic attack || Notes: Identified || suspect brain loose'
,'Confirmed Diagnosis
================
Stroke || Entered: 19-02-2019 01:36:39 || Notes: jose fell of my left eye onto my right pinky toe.

Suspected Diagnosis
================
Transient ischaemic attack || Notes: Identified || there''s something missing from jose''s eye '

;with initialText as ( -- to clean, reducing line ends to only single char(13) (carriage return) stripping off char(10) (line feed), this only applies to the info in memory and will not affect the column in the table.
select
     replace(replace(replace(convert(varchar(max),text_stuff),char(10),''),char(13),''),'Confirmed Diagnosis ================','') cleanstext
    ,replace(replace(replace(varchar_stuff,char(10),''),char(13),''),'Confirmed Diagnosis ================','') cleanvarchar
    ,reverse(replace(replace(convert(varchar(max),text_stuff),char(10),''),char(13),''))  cleanstext1
    ,reverse(replace(replace(varchar_stuff,char(10),''),char(13),'')) cleanvarchar1
from #textThing
)
select
     left(cleanstext,charindex('Suspected Diagnosis================',cleanstext) -1) textConfirmedDiagnosis
    ,left(cleanvarchar,charindex('Suspected Diagnosis================',cleanvarchar) -1) varcharConfirmedDiagnosis
    ,right(cleanstext,charindex(reverse('Suspected Diagnosis================'),cleanstext1) -1) textSuspectedDiagnosis
    ,right(cleanvarchar,charindex(reverse('Suspected Diagnosis================'),cleanvarchar1) -1) varcharSuspectedDiagnosis
from initialText

Results were

[textConfirmedDiagnosis]
Stroke || Entered: 19-02-2019 01:36:39 || Notes: brain fell out, recommend duct tape

and

[varcharConfirmedDiagnosis]
Stroke || Entered: 19-02-2019 01:36:39 || Notes: jose fell of my left eye onto my right pinky toe.

If your results are different then you got some extra characters in your string that I do not have in mine.

I may have written it, but I have no idea how it works, logic dictates I'd need to reverse() it one more time to get the right value, but hey, who am I to argue with a working script, tested on 2014, 2017 and 2017 on centos


LOB Segment is listed in DBA_SEGMENTS, but has not corresponding row in DBA_LOBS

One possible reason is setting a LOB column unusable:

SQL> create table t1 (c1 number, c2 clob);

Table created.

SQL> insert into t1 values (1, 'A');

1 row created.

SQL> commit;

Commit complete.

SQL> select segment_name from dba_lobs where table_name = 'T1' and column_name = 'C2';

SEGMENT_NAME
------------------------------
SYS_LOB0000015673C00002$$

SQL> select segment_name from dba_segments where segment_name = 'SYS_LOB0000015673C00002$$';

SEGMENT_NAME
--------------------------------------------------------------------------------
SYS_LOB0000015673C00002$$

SQL> alter table t1 set unused column c2;

Table altered.

SQL> select  table_name, column_name from dba_lobs where segment_name = 'SYS_LOB0000015673C00002$$';

no rows selected

SQL> select segment_name from dba_segments where segment_name = 'SYS_LOB0000015673C00002$$';

SEGMENT_NAME
--------------------------------------------------------------------------------
SYS_LOB0000015673C00002$$

To find the table the orphaned LOB column belongs to:

select u.name, o.name TABLENAME, decode(bitand(c.property, 1), 1, ac.name, c.name) as column_name
from sys.obj$ o, sys.col$ c, sys.attrcol$ ac,sys.lob$ l,sys.obj$ lo,sys.obj$ io,
sys.user$ u,sys.ts$ ts
where o.owner# = u.user#
and o.obj# = c.obj#
and c.obj# = l.obj# and c.intcol# = l.intcol#
and l.lobj# = lo.obj# and l.ind# = io.obj# and l.ts# = ts.ts# and c.obj# =
ac.obj#(+)
and c.intcol# = ac.intcol#(+) and lo.name ='SYS_LOB0000015673C00002$$';

NAME                           TABLENAME                      COLUMN_NAME
------------------------------ ------------------------------ --------------------------------
BP                             T1                             SYS_C00002_16120712:10:58$

To remove the column and LOB segment:

SQL> alter table t1 drop unused columns;

Table altered.

SQL> select segment_name from dba_segments where segment_name = 'SYS_LOB0000015673C00002$$';

no rows selected

Based on: Orphaned Lobs after marking LOB column unused (Doc ID 461651.1)


Big Size Database and increasing number of users

You're asking a scaling issue which can be solved in many different ways using different technologies based on your current restraints and environments.  If the issue is coming from locking you can change the isolation level to snapshot isolation if you don't mind seeing the old value until the new ones is complete.  This is good if you have plenty of TempDB space and can afford an extra 14 or so bytes per row for the version overhead.

You can use snapshots to query the old data but as new data is added expect slower performance if your snapshot disks aren't fast enough to query the stale data.

You can mirror the data to a different server, read that from a snapshot and update the snapshot after all write operations are complete and replicated.  This will cause your queries to fail while you make a new snapshot but that's very fast, or you could just snapshot by day and delete the old one.  The problem with this is that standard edition of SQL Server requires you write to the mirrored pair first, then to the primary.  This means if the secondary is down the writes on the primary won't commit.  This isn't bad in your scenario actually since they only get updated once per day and if the network is fast enough, along with fast enough disks on the 2nd side to snapshot report from and write to, you're good to go.

Do you have 2 servers?  If so, I'd say mirror to the secondary in this scenario and always perform your reads against a snapshot which get's recreated once a day after the primary is updated and mirrored over.

Edit:
Note there is a HUGE difference between "Database Snapshots" and "Snapshot Isolation".  Snapshot Isolation works on a ROW level and is done to let you read the old value while the new one is being updated.  "Database Snapshots" work by making a copy of the old data page when it changes and referencing that, and it will stay until you drop it.  Note neither of these will help you if the issue is memory or disk IO.


Aggragate values over time in temporal table

This is fairly straightforward. You can query the data from your temporal tables using the FOR SYSTEM_TIME clause to return the history of each record, then simply use DATEPART to identify the month and aggregate using GROUP BY. You can see an example of this here, using SECOND instead of MONTH to group on because I can't insert literal datetime values in the temporal date fields.

Suitable code for MONTH would be:

SELECT ID, Year, Month, AVG(Value) AS AvgValue
FROM
(
  SELECT ID, Value, DATEPART(MONTH, BeginDate) AS Month, DATEPART(YEAR, BeginDate) AS Year
  FROM TemporalData
  FOR SYSTEM_TIME ALL
) s
GROUP BY ID, Month, Year


Mysql - Examine last query run by thread in sleep state

First you have to set general logs of mysql by add lines in /etc/mysql/my.cnf 's mysqld section.

general_log     = 1
general_log_file        = /var/log/mysql/mysql.log

Restart mysql -

sudo service mysql restart

If you don't want to restart mysql server-

SET global log_output = 'FILE';
SET global general_log_file='/var/log/mysql/mysql.log';
SET global general_log = 1;

If you wants to read logs from table, mysql database have a general_log table which insert all logs. you can get by-

   SET global log_output = 'table';
   SET global general_log = 1;
   SELECT * FROM mysql.general_log;

and you can disable by -

SET global general_log = 0;

Now you can see those process which are in sleep state & get its Id-

show full processlist;

open new tab and see the logs of ID & its running command-

cat /var/log/mysql/mysql.log | grep ID_value

or if you read logs from table then-

 SELECT * FROM mysql.general_log where thread_id=1;

Be careful to use it on Production server, it may effect your server performance (due to additional disk write) if transactions committing on huge no. per second.


SQL function which returns true/false depending on success of insert query

One way with little noise in the code:

create function test()
  returns boolean as
$$
   WITH ins AS (
      insert into    ...
      select         ...
      from           ...
      where          ...
      returning      true
      )
   SELECT true FROM ins
   UNION ALL SELECT false;
$$
language sql volatile;

The function, being declared as returns boolean (and not returns setof boolean) returns only the first row. If no INSERT happens, the first (and only) row delivers false.

Or, a shorter version of your first variant:

...
   WITH ins AS (
      insert into    ...
      )
   SELECT EXISTS(TABLE ins)
...

See:

Is there a shortcut for SELECT * FROM?

Related (with alternatives):

How to return a value from a function if no value is found

Quoting the manual to clarify the behavior of SQL functions:

  SQL functions execute an arbitrary list of SQL statements, returning
  the result of the last query in the list. In the simple (non-set)
  case, the first row of the last query's result will be returned. (Bear
  in mind that “the first row” of a multirow result is not well-defined
  unless you use ORDER BY.) If the last query happens to return no rows
  at all, the null value will be returned.

Bold emphasis mine.

Note: the above is a single query. And we don't need ORDER BY, since a plain UNION ALL without more operations in the outer query level simply appends the resulting row to the set in last position.


Need a query to show user present/absent within given interval

So, at the heart of things, we want to take a list of users, combine that with a list of ranges, and report how many records each user has in each range (1 or more means "present", 0 means "absent").

First (since you explain that you're trying to track whether users have logged on to the system every day for a month), I should note that there are a number of good ways to do this simply using dates, and a date table (a list of all possible dates in your range), and that matching date to date (note, date, not datetime or timestamp) might be faster than checking intervals. However, from our conversations, I think you need times down to milliseconds for other aspects of your project, and maintaining both is pointless. This approach probably makes more sense than converting the milliseconds to a date to compare to another date.

You have said you want to use three values to define the intervals:

The minimum value we want to look at (@dstart)
The maximum value we want to look at (@dend)
The size of each interval we want to examine (@interval)

Again, coming out of our discussion, your expectation is that these values will always relate as follows:

@datart + (N * @interval) - 1 = @dend

where N is the number of intervals to be checked. However, it's always a good idea to account for mistakes in the input. If the above statement is not true, then we have a partial interval to account for at the end of the analysis period. This can be handled three ways: add another full interval (that goes past @dend); add the partial interval (ending at @dend); or ignore the partial interval. You agreed that ignoring the partial interval made sense.

For our initial test data , we'll use the following:

INSERT INTO tblA (user_login, tstamp) VALUES
       ('a', 1),('a',3),('a',5),('a',6)
      ,('x', 2),('x',6),('x',7),('x',10)
;

SET @dstart = 1;
SET @dend = 10;
SET @interval = 3;

IN fact, this gives us an example of a partial interval. With our sample values, we should be checking three intervals: from 1 through 3; from 4 through 6; and from 7 through 9. Any tstamps set to 10 will be ignored.

My first suggestion is to generate the interval ranges (the start and end values for each interval) with a recursive Common Table Expression, or CTE.

At its base, a CTE lets you define a temporary table for the duration of a single query statement. The expression can be used multiple times within the query, just like a actual table. It has the additional advantage of allowing you to build the "table" by creating an initial data set, and then using the existing set recursively to build the full data set. This is frequently used for assembling hierarchical data (like organization charts), but can also be used to build something like a list of sequential dates, or our intervals.

Here's the CTE to build the interval list:

WITH recursive theRange AS
     (SELECT @dstart as startRange
            ,@dstart + @interval as endRange
        FROM DUAL
      UNION ALL
      SELECT endRange, endRange + @interval
        FROM theRange
       WHERE endRange + @interval <= @dend
     )

We start with our initial interval:

SELECT @dstart as startRange
      ,@dstart + @interval as endRange
  FROM DUAL

Then, using that as our starting point, we add new intervals, starting each new interval with the end value from the previous interval:

SELECT endRange, endRange + @interval
  FROM theRange

The WHERE clause tells us when to stop:

 WHERE endRange + @interval <= @dend

However, this didn't work out for you; it evidently requires a newer version of MariaDB than what you have available.

So, for option two, I went with using a "numbers table". If you're not acquainted with those, it's a table (or, in our case, a view) with one column, holding integer values (in most cases stepping by one, and starting at 0 or 1). This allows a set-based solution to tasks that might otherwise require a loop or a cursor.

I've borrowed the code to do this from the accepted answer to this question. Check that answer (and the article it links to) for more info on numbers tables.

CREATE OR REPLACE VIEW generator_16
AS (SELECT 0 n)   UNION ALL (SELECT 1 n)   UNION ALL (SELECT 2 n)   UNION ALL
   (SELECT 3 n)   UNION ALL (SELECT 4 n)   UNION ALL (SELECT 5 n)   UNION ALL
   (SELECT 6 n)   UNION ALL (SELECT 7 n)   UNION ALL (SELECT 8 n)   UNION ALL
   (SELECT 9 n)   UNION ALL (SELECT 10 n)  UNION ALL (SELECT 11 n)  UNION ALL
   (SELECT 12 n)  UNION ALL (SELECT 13 n)  UNION ALL (SELECT 14 n)  UNION ALL
   (SELECT 15 n);

CREATE OR REPLACE VIEW generator_256
AS SELECT ( ( hi.n << 4 ) | lo.n ) AS n
     FROM generator_16 lo, generator_16 hi;

We start by manually creating a view with the numbers from 0 to 15 (which require up to 4 bits to represent). We can then expand quickly by using a neat little trick. If take the bit representation of the numbers 0-15, shift the bits 4 places to the left, and "concatenate" each of these with our original numbers (i.e., a cross join), we get all numbers represented in 8 bits (the "concatenation is actually a bitwise OR). Do thsi once, and you get 0 - 255; do it again with those results, and you get 0 - 65,535; again, and you get 0 - 4,294,967,296; etc. We shouldn't need that many numbers, but I'll include the views in the full code at the end.

So, our first interval will run from @dstart to @dstart + @interval - 1; our second from @dstart + @interval to @dstart +  (2 * interval) - 1. More generally, the start of interval X is @dstart + ((X - 1) * @interval), and the end if @dstart + (X * @interval) - 1). Since our generator starts us at 0 instead of 1, X will be n + 1 in our actual query:

INSERT INTO intervalRange (startRange, endRange)
SELECT @dstart + (n * @interval)
      ,@dstart + ((n + 1) * @interval) - 1
  FROM generator_256
 WHERE @dstart + ((n + 1) * @interval) - 1 <= @dend
;

And, of course, we stop when the next endRange would be bigger than @dend.  I'll include a query to show you the intervals below, as well.

Once we have the intervals, we simply need to match each interval up to the data for each user in our table, and see how many rows have a tstamp value that falls within the current range:

SELECT user_login, dt, CASE WHEN theCount = 0 THEN 0 ELSE 1 END as presence, theCount
  FROM (SELECT user_login, endRange as dt
              ,SUM(CASE WHEN     tblA.tstamp >= r.startRange
                             AND tblA.tstamp <= r.endRange
                     THEN 1
                     ELSE 0
                   END
                  ) as theCount
          FROM tblA CROSS JOIN intervalRange r
         GROUP BY user_login, endRange
       ) z
;

The subquery aliased as z does all the real work here. We match every record in tblA with every interval; then, we group the records togather based on user_login and startRange, and count the records that fall into our range (with the SUMmed CASE statement). Since each interval is compared with all recods for the user_login, we do get a row out even if none of the records fall within the interval; we do need to see cases where the user wasn't present.

The outer part of the query uses the count to set presence (it's 0 if the count is zero, 1 otherwise); we could probably do this all at once, but it would make the query that much harder to understand, and we'd just be duplicating effort.

So, here's the full code. I tested it using the MySQL 5.5 engine at db-fiddle.com (most of the code was tested in pieces using the MariaDB 10.2 RC engine at dbfiddle.uk, but I haven't been able to get that site to respond for the past couple of days).

CREATE TABLE tblA (user_login varchar(128), tstamp bigint, state varchar(128));

INSERT INTO tblA (user_login, tstamp) VALUES
       ('a', 1),('a',3),('a',5),('a',6)
      ,('x', 2),('x',6),('x',7),('x',10)
;

SET @dstart = 1;
SET @dend = 10;
SET @interval = 3;

CREATE OR REPLACE VIEW generator_16
AS (SELECT 0 n)   UNION ALL (SELECT 1 n)   UNION ALL (SELECT 2 n)   UNION ALL
   (SELECT 3 n)   UNION ALL (SELECT 4 n)   UNION ALL (SELECT 5 n)   UNION ALL
   (SELECT 6 n)   UNION ALL (SELECT 7 n)   UNION ALL (SELECT 8 n)   UNION ALL
   (SELECT 9 n)   UNION ALL (SELECT 10 n)  UNION ALL (SELECT 11 n)  UNION ALL
   (SELECT 12 n)  UNION ALL (SELECT 13 n)  UNION ALL (SELECT 14 n)  UNION ALL
   (SELECT 15 n);

CREATE OR REPLACE VIEW generator_256
AS SELECT ( ( hi.n << 4 ) | lo.n ) AS n
     FROM generator_16 lo, generator_16 hi;

CREATE OR REPLACE VIEW generator_64k
AS SELECT ( ( hi.n << 8 ) | lo.n ) AS n
     FROM generator_256 lo, generator_256 hi;

CREATE OR REPLACE VIEW generator_4b
AS SELECT ( ( hi.n << 16 ) | lo.n ) AS n
     FROM generator_64k lo, generator_64k hi;

CREATE TABLE intervalRange (startRange bigint, endRange bigint);

INSERT INTO intervalRange (startRange, endRange)
SELECT @dstart + (n * @interval)
      ,@dstart + ((n + 1) * @interval) - 1
  FROM generator_256
 WHERE @dstart + ((n + 1) * @interval) - 1 <= @dend
;

SELECT * FROM intervalRange;

SELECT user_login, dt, CASE WHEN theCount = 0 THEN 0 ELSE 1 END as presence, theCount
  FROM (SELECT user_login, endRange as dt
              ,SUM(CASE WHEN     tblA.tstamp >= r.startRange
                             AND tblA.tstamp <= r.endRange
                     THEN 1
                     ELSE 0
                   END
                  ) as theCount
          FROM tblA CROSS JOIN intervalRange r
         GROUP BY user_login, endRange
       ) z
;

Here's the results using the test data shown:

user_login | dt | presence | theCount
-----------+----+----------+---------
   a          1          1         2
   a          4          1         2
   a          7          0         0
   x          1          1         1
   x          4          1         1
   x          7          1         1

Note that user x only has 3 logins counted; his login with timestamp 10 matches our @dend value, but @dend doesn't match up with the end of an interval, so anything more than '9' is ignored.

I also generated some test data that should be more similar to what would actually be seen, using these values:

INSERT INTO tblA (user_login, tstamp) VALUES
       ('a', 1492834674999)
      ,('a', 1493038075000)
      ,('a', 1493056075000)
      ,('a', 1493088475000)
      ,('a', 1493162275000)
      ,('a', 1493209075000)
      ,('a', 1493218075000)
      ,('a', 1493300875000)
      ,('a', 1493381875000)
      ,('a', 1493390875000)
;

SET @dstart = 1492798675001;
SET @dend = 1493403475000;
SET @interval = 86400000;

Results were:

user_login |       dt      | presence | theCount
-----------+---------------+----------+---------
   a         1492885075000          1         1
   a         1492971475000          0         0
   a         1493057875000          1         2
   a         1493144275000          1         1
   a         1493230675000          1         3
   a         1493317075000          1         1
   a         1493403475000          1         2


Duplicate key error for composite primary key when running update query without having duplicate

You have a primary key on the following fields, meaning that when combined they must be unique.

PRIMARY KEY (entity_type,entity_id,deleted,language)

Currently, they are unique because you have two entries in the table for language. After your update, language would have the same entry for both rows, and the rows would no longer be unique.


Alter the letters after "ACCOUNTS\" to lower case?

UPDATE dbo.[User]
SET username_column = N'ACCOUNTS' + LOWER(SUBSTRING(username_column, 9, 4000))
WHERE username_column LIKE 'ACCOUNTS\%';


Does using a read committed isolation level and a NOLOCK query hint impact performance?

If you have stored procedure that starts with:

SET TRANSACTION ISOLATION LEVEL READ COMMITTED;

And then you have a SELECT query like this:

SELECT * FROM dbo.MyTable mt WITH (NOLOCK);

Then the SELECT query will use the READ UNCOMMITED isolation level* - in other words, the NOLOCK will override the SET statement.  This is documented in SET TRANSACTION ISOLATION LEVEL (Transact-SQL):

  All read operations performed within the transaction operate under the rules for the specified isolation level unless a table hint in the FROM clause of a statement specifies different locking or versioning behavior for a table.

As far as performance, using NOLOCK will ignore locks taken by other queries, and take / release locks more quickly, so you will likely experience less blocking.  However, this is at the expense of the correctness of the results.

* Since this is a table-level hint, the hint would need to be applied to each table involved in the SELECT query in order to be functionally equivalent to using SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED


If you are still inserting data for 2014, then you risk problems with this method, because rows inserted between steps 2 and 5 are going to end up getting dropped rather than moved.

If you are not still inserting data for 2014, then I think you should change step 5 to "rewrite the trigger to throw an error upon insertion of 2014 data" and move it up to be step 0.  That would remove the doubt.

But it does seem like you are doing a lot of tinkering.  If you want to remove the partitioning for 2014, why would you want to keep it for 2015?  Why did you implement partitioning in the first place and why is that reason no longer valid for 2014 (but still valid for 2015)?  Getting rid of the partitioning might speed up the queries, but it might not.  Reorganizing partitioned tables isn't something you should do on a hunch.  Do you have a QA system you can use to time the queries and see if they get faster?  And wouldn't you want faster queries for the newer data at least as much as for the older data?


Reordering rows in a chapter table when creating, sorting, and deleting

Edit:

The main thrust of my first answer (now at the bottom of this post) did not properly address the question which was about:

  how I should handle extra additions, say, an Author wants to INSERT a
  Chapter with chapter_number as 4, and expect the rest of the rows to
  automatically sort themselves.

and not how to design a (flexible) schema for the storing of book and chapter data.

I'll leave the schema design stuff in place under a line, just in case anyone is interested in the future and deal with the core issue in this edit.

You'll have to ensure that your chapters remain in the correct sequence programmatically, either using TRIGGERs or through your coding environment of choice or perhaps a combination of both? You mentioned in comments that you use Django - Python is a very powerful tool and should be well capable of tackling any programming task that is thrown at it - programmer competence being a given.

My own personal view is that the database is the last bastion of defence of your data and that any data manipulation/treatment/processing should be done as close to the server as possible. Since you apparently haven't settled on an RDBMS yet, it's impossible to give any definitive code that would help resolve the problem.

All I will say is that PostgreSQL's PL/pgSQL is vastly more powerful than MySQL's procedural language extension (see here and here - a quote from the second reference: It closely resembles Oracle's PL/SQL language. - although admittedly not as sophisticated). À vous le choix!

Do as much of the work as you can server-side using it - I would give some thought as to how using the WITH RECURSIVE construct might be of help. Googling the term PostgreSQL recursive query produces many helpful pages! A good example of its power can be seen in an answer to a recent question here.

Finally, I couldn't let this go without my pet rant (was in first answer - moved to here)!

<getting_on_to_white_charger>

I'm firmly of the belief that those who produce database systems are remiss and lackadaisical in their implementation of more features from within DDL.

If rules are stored in the structure of the database itself, then no matter how stupid, inexperienced or just plain lazy your contract programmer, student intern or half-wit cousin that your wife insisted you give a job to is, they still can't mess up the system!

The vastly underrated Firebird RDBMS is one of only two systems) that support CHECK constraints with subqueries! A small glimpse into the power of this feature can be seen in this example (from yours truly!).

</getting_on_to_white_charger>

Phwew! Deep breath - relieved that I got that off my chest! :-).

Initial answer (schema design - didn't address the question):

  I understand that the way to handle this is to have chapter_number
  saved in multiples of 10 or 100 so that we have a 'room' between the
  rows.

This is one way of tackling the problem - however, see below!

  But it feels a bit hacky to me. Is there a better way?

You are correct, it is desperately "hacky" and it's great that you are looking for a more elegant solution.

Such a solution exists in the form of joining or linking tables. These are more formally called "Associative Entities" and are really helpful when there is (and/or may be) an arbitrary number of connections between one entity (i.e. table) and another. (There are actually 17 alternative names for this construct mentioned on the wiki page).

I did something like this many moons ago, so this is from memory, but I think that you should be able to adapt it for your needs. There is a fiddle here. The example is PostgreSQL, but it works for every server on the dbfiddle.uk palette. The reason for this is these two lines in the chapter_author table:

ca_is_primary_author CHAR(1),

and

CONSTRAINT ca_is_primary_author_ny CHECK (ca_is_primary_author IN ('Y', 'N'))

I used this construct because neither Oracle nor SQL Server have a BOOLEAN type and I wanted to keep the example as generic as possible. You may well want to make use of features of your own particular RDBMS when you come to apply any solution?

My idea of the tables' DDL would look something like this:

author table:

-- 1000 is very long for a name, but there could be a corporate author?
-- For PostgreSQL you could just use a TEXT field (1GB) or for MySQL you could
-- use MEDIUMTEXT (16MB). I used VARCHAR here because it will work for all servers,
-- but you may want to make use of types provided by your RDBMS of choice. Oracle,
-- and SQL Server undoubtedly have their own equivalents

CREATE TABLE author
(
  author_id INTEGER NOT NULL PRIMARY KEY,
  author_name VARCHAR (1000) NOT NULL, -- 1000 is long but maybe corporate?
  author_contact VARCHAR (30) NOT NULL -- mobile number?
  --
  -- other fields
  --
);

work table:

-- could be called "book", "tome", "volume" - whatever
-- note that there is no "author" field - many works are multi-author
-- and/or compendiums or the results of conferences/symposia/whatever
-- even different chapters may have different authors - see below

CREATE TABLE work
(
  work_id INTEGER NOT NULL PRIMARY KEY,
  work_title VARCHAR (100) NOT NULL,
  work_publisher INTEGER NOT NULL, -- could point to a publisher table?
  work_subject VARCHAR (1000) NOT NULL
  --
  -- other fields
  --
);

chapter table:

-- There is no "chapter_author" **_FIELD_**.
-- This is because even one chapter may be written by more than one author - this
-- is especially common for academic works where a "chapter" might well be
-- a scientific article in a journal!
-- This situation is covered in the "chapter_author" **_TABLE_**!

-- There is no "chapter_work" **_FIELD_**.
-- This is because a given chapter may appear in more than one work - a compendium
-- for example like Michael Stonebraker's "Readings in Database Systems"
-- https://www.thriftbooks.com/w/readings-in-database-systems-mogan-kaufmann-series-in-data-management-systems_michael-stonebraker/1147410/#isbn=1558605231&idiq=5917200
-- which is a compilation of articles previously published elsewhere
-- This situation is covered  in the "work_chapter" **_TABLE_**!

CREATE TABLE chapter
(
  chapter_id INTEGER NOT NULL PRIMARY KEY,
  chapter_number INTEGER NOT NULL,
  chapter_title VARCHAR (100) NOT NULL,
  chapter_subject VARCHAR (1000)  -- abstract of chapter?
  --
  -- other fields
  --
);

chapter_author table:

This is our first Associative Entity - it (obviously enough!) links chapters to authors!

CREATE TABLE chapter_author
(
  ca_work_id INTEGER NOT NULL,
  ca_author_id INTEgER NOT NULL,
  ca_is_primary_author CHAR(1),
  CONSTRAINT chapter_author_pk PRIMARY KEY (ca_work_id, ca_author_id),
  CONSTRAINT ca_work_fk FOREIGN KEY (ca_work_id) REFERENCES work (work_id),
  CONSTRAINT ca_author_fk FOREIGN KEY (ca_author_id) REFERENCES author (author_id),
  CONSTRAINT ca_is_primary_author_ny CHECK (ca_is_primary_author IN ('Y', 'N'))
);

work_chapter table:

-- Having the three fields as a PRIMARY KEY means that you can't have the same
-- chapter in the same position in the same work. You may need a mechanism to ensure
-- that there are no gaps in the sequence of chapters as you INSERT, UPDATE and DELETE?

CREATE TABLE work_chapter
(
  wc_work_id INTEGER NOT NULL,
  wc_chapter_id INTEGER NOT NULL,
  wc_chapter_position INTEGER NOT NULL,
  CONSTRAINT work_chapter_pk PRIMARY KEY (wc_work_id, wc_chapter_id, wc_chapter_position),
  CONSTRAINT wc_work_fk FOREIGN KEY (wc_work_id) REFERENCES work (work_id),
  CONSTRAINT wc_chapter_fk FOREIGN KEY (wc_chapter_id) REFERENCES chapter (chapter_id)
);

As I mention in the table definition, you will need to have some mechanism to enforce a "no gaps" rule in the sequence of the chapters - unfortunately, DDL isn't (yet) sophisticated enough to be able to enforce such complex rules.

As an aside:

p.s. welcome to the forum!


I have to find the records which contain 6-A, 6-B, 6-C...6-Z. using SQL?

Clean, but the naive version* will most likely perform better.

with t(i) as (select 0 union all select i+1 from t where i<25)
select      *
from        TABLE_1
where       exists (select null from t where test like '%' + replicate(char(ascii('A')+t.i),6) + '%')

*

select      *
from        TABLE_1
where       test like '%AAAAAA%'
        or  test like '%BBBBBB%'
        or  test like '%CCCCCC%'
        ...


Sales by month cross tab query using pivot

Since your new columns are numbers, you need to escape the names with square brackets:

select ShippedYear, 1 as Jan, 2 as Feb
from
(
    select  datepart(year, ss.SHIPPED_DATE) as ShippedYear
          , ss.SHIPPED_AMOUNT as Amount
          , datepart(month, ss.SHIPPED_DATE)) as MonthCode
    from    dbo.SALES_SHIPPER_LINE ssl join dbo.SALES_SHIPPER ss on ss.ID = ssl.SHIPPER_ID
) ps
pivot(
    sum(amt) for MonthCode in ([1],[2],[3],[4],[5],[6],[7],[8],[9],[10],[11],[12])
) piv;

The square brackets [] are used to delimit identifiers. Since the column name begins with a number, this must be delimited. See MSDN for more details.


What is the correct way to select data grouped by time interval?

I really overcomplicated this.

As suggested by @Gaius, all I needed to do is to query all the data grouped by the hour of the purchase, i.e.

SELECT
  ts1.date,
  ROUND(EXTRACT(EPOCH FROM (ts1.starts_at - ts1.created_at))/3600) "hour",
  COUNT(*) sales_count
FROM ticket_sale ts1
WHERE
  ts1.movie_id = 1012718 AND
  ts1.date = '2018-04-26'
GROUP BY ts1.date, hour

I can post-process the data in the client-side to group it into the required intervals.


Unable to import shapefile into PostGisDatabase - problem of connection

The pgAdmin4 connection goes to the database named "postgres".
Apparently, a database named "gis_analysis" does not exist, or is not configured to accept connection.


How to get the row count from a table related to other table?

You would need an OUTER JOIN to solve this one, particularly a LEFT OUTER JOIN (also known as LEFT JOIN) like so:
SELECT F.FruitID, F.FruitName
FROM Fruits F
LEFT JOIN FruitSubClasses FSC
    ON F.FruitID = FSC.FruitID
WHERE FSC.FruitID IS NULL

A LEFT JOIN returns all rows from the table on the left-hand side of the JOIN clause regardless of the match in the predicate of the ON clause. For rows where there are no matches on the right-hand side, NULL values are returned for the columns of the right-hand table. Knowing this we can then filter out anything that matches in the JOIN with the WHERE clause above, and only get the non-matching Fruits (that don't exist in FruitSubClasses).
And if you wanted just the row counts in a single scalar value, you can use the same query above with the COUNT(*) function like so:
SELECT COUNT(*)
FROM Fruits F
LEFT JOIN FruitSubClasses FSC
    ON F.FruitID = FSC.FruitID
WHERE FSC.FruitID IS NULL


SQL to merge related documents into 1 query row (MySQL)

Your question appears to be a case of on the fly fields ( dynamic attributes ), which means that within your application end users can add information about the user on the fly, if this is not the case you need to reconsider schema design.

Here is the query that can be used to achieve the result, here you must know all the fields in advance:

SELECT users.id, users.email, group_concat(IF(fields.name='Bank',fields.value,'')) Bank
FROM users
LEFT JOIN fields ON (fields.parent_id= users.id)
GROUP BY users.id
ORDER BY updated_at DESC
LIMIT 100 OFFSET 0

also consider field updated_at is not mentioned by you in your example. I am assuming it is available only in one table that should be the users.


What are the implications of constraint locality in Postgres?

Constraint locality has nothing to do with tablespaces; a local constraint is one that is defined on a partition of a partitioned parent table that  itself has no identical constraint -- it is a logical locality.

You don't say what types of constraints you're talking about. PKs and unique constraints will cause creation of missing underlying indexes, which can obviously be affected by the change of the default tablespace.


Even when creating a table via SELECT ... INTO, you can include empty columns to be populated later. This avoids the need to ALTER the table even once:

Select * FROM MasterData
SELECT [Store]
    ,[SaleID]
    ,TPC = SUM(COALESCE([TotalCost],0))
    ,timesbilled = CAST(NULL as int)
    ,TC1 = SUM(COALESCE([TotalCost],0))
INTO HelperTable
FROM Costing
GROUP BY Store, SaleID

UPDATE t
SET t.TimesBilled = h.TimesBilled
   ,t.TC1 = CASE WHEN h.TimesBilled > 1
              THEN t.TC1/h.TimesBilled
              ELSE t.TC1
            END
FROM HelperTable t
INNER JOIN [Hold1] h
ON t.SaleID = h.SaleID

UPDATE x
SET TC1 = y.TPC
,TC2 = y.TC1
FROM MasterData x
INNER JOIN HelperTable y
ON x.Store = y.Store

If HOLD1 is guaranteed to have 0 or 1 records for each SaleID, you can compress this even further, avoiding the need for the UPDATE - at which point, you can actually skip HelperTable, and put a subquery directly into the MasterDate UPDATE statement:

UPDATE x
SET TC1 = y.TPC
,TC2 = y.TC1
FROM MasterData x
INNER JOIN (SELECT c.[Store]
                  ,c.[SaleID]
                  ,TPC = SUM(COALESCE(c.[TotalCost],0))
                  ,TimesBilled = h.TimesBilled
                  ,TC1 = CASE WHEN h.TimesBilled > 1
                           THEN SUM(COALESCE(c.[TotalCost],0))/h.TimesBilled
                           ELSE SUM(COALESCE(c.[TotalCost],0))
                         END
              FROM Costing c
                     LEFT  JOIN [Hold1] h ON (c.SaleID = h.SaleID)
             GROUP BY c.Store, c.SaleID, h.TimesBilled
           ) y ON x.Store = y.Store

On my system, the original query took 13 ms to execute (I ran it twice, same both times); My version with HelperTable took 10 ms the first time I ran it, and 6 the second; and the version without HelperTable took 3 ms the first time, and 0 ms the second. (Noting first and second runs as the second run may be able to use a cached plan).


How can I have an aggregate function in the WHERE clause?

You have to use a window function, which does something similar to a GROUP BY, but outpots all rows and not just the groups.

SELECT *
FROM (SELECT *,
             sum(available_count) OVER (PARTITION BY product_id) AS sum_count
      FROM j_product_store_availability psa) AS subq
WHERE onway = 0
  AND sum_count = 0
ORDER BY product_id;


Figured it out. A bad script granted database-wide SELECT permissions to the user. Found it using this script:

WITH    perms_cte as
(
        select USER_NAME(p.grantee_principal_id) AS principal_name,
                dp.principal_id,
                dp.type_desc AS principal_type_desc,
                p.class_desc,
                OBJECT_NAME(p.major_id) AS object_name,
                p.permission_name,
                p.state_desc AS permission_state_desc
        from    sys.database_permissions p
        inner   JOIN sys.database_principals dp
        on     p.grantee_principal_id = dp.principal_id
)
--users
SELECT p.principal_name,  p.principal_type_desc, p.class_desc, p.[object_name], p.permission_name, p.permission_state_desc, cast(NULL as sysname) as role_name
FROM    perms_cte p
WHERE   principal_type_desc <> 'DATABASE_ROLE'
UNION
--role members
SELECT rm.member_principal_name, rm.principal_type_desc, p.class_desc, p.object_name, p.permission_name, p.permission_state_desc,rm.role_name
FROM    perms_cte p
right outer JOIN (
    select role_principal_id, dp.type_desc as principal_type_desc, member_principal_id,user_name(member_principal_id) as member_principal_name,user_name(role_principal_id) as role_name--,*
    from    sys.database_role_members rm
    INNER   JOIN sys.database_principals dp
    ON     rm.member_principal_id = dp.principal_id
) rm
ON     rm.role_principal_id = p.principal_id
order by 1

https://web.archive.org/web/20141128165740/http://consultingblogs.emc.com/jamiethomson/archive/2007/02/09/SQL-Server-2005_3A00_-View-all-permissions--_2800_2_2900_.aspx


Error 23 on transaction log backup, full backup works fine

To answer the two specific questions in your post:

  Can I change the database property to simple restore and shrink the log file?

You can switch to the simple recovery model and shrink the log.  This might workaround the problem for a little while if it moves the data off of the faulty area.  Or it might cause more problems (if there are more "bad" areas in this drive / array that get written to).

  Can I run chkdsk on the D: drive (need to dismount) without messing up the database?

Again you can do that, but there are some caveats.  Tony mentioned one in a comment:

  You will need to stop SQL Server or detach all databases that have files on D: before you dismount it.

So there will be downtime involved for this.  Tony also mentioned that, before disconnecting everything, you should take a full backup and restore it somewhere else, to make sure the backup was good.  That way, once you have finished chkdsk and resolved any issues there, you can confidently restore from that backup.


Adding two columns results in concatenation instead of addition

Maybe you should use the type conversion function CInt()?

As in, CInt(expr1) + CInt(expr2).  Does this fix your problem?

This is necessary if you haven't defined the column data type as a numeric type.


How to name a new table storing location at which transaction occurred

I'd recommend keeping a few things in mind.  You will want to be consistent no matter what you decide.  For example, will you shorten 'transaction' to 'tx' so all of your stored procedures and scripts are easier to use?  How often will you be changing it?  Here are some good best cases from stackoverflow:

Singular names for tables

Singular names for columns

Schema name for tables prefix (E.g.: SchemeName.TableName)

Pascal casing, camel casing, your own casing, but uniform and standard.

Just in case I'll point out that this is an auditing record of transaction data.  I'd keep ACID compliance on the top of my mind if this data is very sensitive and requires to be kept/accurate such as for auditing or billing.

If so, please consider how you'll get the data in there with full ACID compliance.  You can update your stored procedures, add triggers/cdc (though those have their own limitations), or keep it within the same transaction for example.


How to restore one table from full backup in Sybase ASE

Take a look at the ASE Archive Database feature.

This features allows you to mount your dump file(s) as a read-only database; once mounted you can access the tables in the database with your desired select queries.

Technically ...

you'll need to setup a smallish scratch database for managing the mount
it's possible to make non-persistent changes to data; this consists of allocating (optional) disk space to hold any modified pages (these changes will not be reflected in the database dump file(s)

For an example of setting up the scratch and archive databases, see the Reference manual entry for the create archive database command.


split a row into multiple rows in sql server according to a specific column

You can generate the extra row(s) by doing an unpivot using a cross apply and the table value constructor.

select c.id,
       c.val
from dbo.a
  inner join dbo.b
    on a.id = b.id
  cross apply (values(a.id, a.val),
                     (b.id, b.val)) as c(id, val)
where a.val <> b.val;


Cannot alter MYSQL Table (stuck in a invalid default value loop)

Try changing both columns defaults at the same time:

ALTER TABLE yourtable
    CHANGE campaign_start_date campaign_start_date TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    CHANGE campaign_end_date campaign_end_date TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP;


Identity value jumps when restarting SQL Server

I found that it is a new feature (don't know what is use of it)

Before SQL Server 2012, identity allocations were always individually logged (as each value was used). This per-row logging activity could limit throughput in scenarios where many identity values are generated in a short space of time. To improve efficiency, SQL Server 2012 (and later) logs only the allocation of a batch of identity values. The allocated range is cached and issued on demand until a new batch of values is needed.
If SQL Server is restarted without the database containing the identity object being checkpointed on shut down, any remaining unused values in the cached range are lost, resulting in a jump in the values on restart.
Unfortunately, common ways of shutting down SQL Server 2012 do not currently automatically checkpoint databases (this contradicts the documentation, so it should be fixed at some point in the future). To avoid this particular cause of jumps in assigned identity values on SQL Server 2012, always shut down SQL Server using the T-SQL command SHUTDOWN (without the NOWAIT option).
The SHUTDOWN command will checkpoint all user databases correctly before shutting down the server. Do not use the Windows Service Control application, SQL Server Configuration Manager, the SQL Server Management Studio UI, or any other method.
You could also manually CHECKPOINT all databases before shutting down the SQL Server using any other method, but this requires you ensure no identity-allocation activity occurs in any database after your checkpoints, and before the shutdown completes. This may not be easy to achieve reliably.
More information and background in Lost Identity by Kalen Delaney.

Trace flag 272 is now documented in DBCC TRACEON - Trace Flags (Transact-SQL):

Disables identity pre-allocation to avoid gaps in the values of an identity column in cases where the server restarts unexpectedly or fails over to a secondary server. Note that identity caching is used to improve INSERT performance on tables with identity columns.
Note: Starting with SQL Server 2017, to accomplish this at the database level, see the IDENTITY_CACHE option in ALTER DATABASE SCOPED CONFIGURATION (Transact-SQL).
Scope: global only


Alternatives to MySQL Partitioning, so we can have a Unique Index

Since you won't have even a million rows in the table, don't bother to PARTITION it.  Then you won't have any trouble with the UNIQUEness constraint.
Change the indexing:

Plan A:

PRIMARY KEY(Id),   -- notice: `Created` was removed
INDEX(Created),    -- for purging
UNIQUE(`CustId`,`SourceName`,`SourceId`)  -- as desired

Plan B (this assumes you have no real use for id):

PRIMARY KEY(`CustId`,`SourceName`,`SourceId`)  -- Unique
INDEX(Created)    -- for purging

Purge daily, not monthly.  This is because DELETE is much slower than DROP PARTITION.  If you still run into performance troubles:  Using Plan B, write a loop to delete the rows for each CustId one at a time.

(I prefer Plan B for your situation.)

(My comments apply to all versions.)

Usually, each partition has 4M-7M of "free" space.  Hence a partitioned table is inherently bigger than the non-partitioned equivalent.


Can SQL Profiler filter data columns only for one event?

Unfortunately, this can't be done in the trace definition. Each filter defined executes sp_trace_setfilter filter which groups filters by column, but applies all the filters as a whole...

In the trace definition snippet below it ends up grouped like this:

(DatabaseId = 5 OR DatabaseID = 6) AND (DatabaseId <> 1 AND DatabaseID <> 0) AND (Login like User1 OR Login like User2)

-- Set the Filters
declare @intfilter int
declare @bigintfilter bigint

/******Filter by database ID include*****/
SET @intfilter = 5
exec sp_trace_setfilter @TraceID, 3, 0, 0, @intfilter  -- 3,0,0 = ColumnID, logical operator (and/or), comparison operator (0 = EQUAL, 1 = NOT EQUAL)

set @intfilter = 6
exec sp_trace_setfilter @TraceID, 3, 1, 0, @intfilter

/******Filter by database ID exclude*****/
SET @intfilter = 1
exec sp_trace_setfilter @TraceID, 3, 0, 1, @intfilter

set @intfilter = 0
exec sp_trace_setfilter @TraceID, 3, 0, 1, @intfilter

/******Filter by database Login LIKE*****/
exec sp_trace_setfilter @TraceID, 11, 0, 6, N'user1' -- 11,0,6 = ColumnID, logical operator (and/or), comparison operator (6 = LIKE)
exec sp_trace_setfilter @TraceID, 11, 1, 6, N'user2'

For real-time filtering you'd need to use something like the Save to table option and filter a query against the output there.


Automatically set timestamps for "create" and "modified"?

From here:

This limitation, which was only due to historical, code legacy reasons, has been lifted in recent versions of MySQL:

Changes in MySQL 5.6.5 (2012-04-10, Milestone 8)

  Previously, at most one TIMESTAMP column per table could be
  automatically initialized or updated to the current date and time.
  This restriction has been lifted. Any TIMESTAMP column definition can
  have any combination of DEFAULT CURRENT_TIMESTAMP and ON UPDATE
  CURRENT_TIMESTAMP clauses. In addition, these clauses now can be used
  with DATETIME column definitions. For more information, see Automatic
  Initialization and Updating for TIMESTAMP and DATETIME.

What can you do?

You can add a BEFORE UPDATE trigger and set the ts_modified to NOW() like this:

USE `test`;

DELIMITER $$

DROP TRIGGER IF EXISTS test.xyz_BEFORE_UPDATE$$
USE `test`$$
CREATE DEFINER = CURRENT_USER TRIGGER `test`.`xyz_BEFORE_UPDATE` BEFORE UPDATE ON `xyz` FOR EACH ROW
BEGIN

SET NEW.ts_modified=NOW();

END
$$
DELIMITER ;


Database design of Ledger Account and Balance sheet generation

As to what dezso said, ledgers are quite special depending on the business that is using them. You can try looking at other opensource applications with the same features and functions as your program if you're looking for a basis for your database.

You can look for some opensource projects at Ohloh.net and Github.com

But as a fellow application developer, I would suggest you take the time to read up on some basic database design best practices and principles. And a bit of normalization, at least up to the Third Normal Form. Having a faulty database can give you a lot of headaches in the long run when maintaining your application. Refactoring database changes aren't that easy with a mature program.


Converting MSSQL Stored Procedure to MySQL - Syntax Error

Comment from @ypercube is correct -- in this case, the syntax error you're encountering is because the previous line did not end in a semicolon:

Declare RowID, CountAll INT DEFAULT 0

Should be:

Declare RowID, CountAll INT DEFAULT 0;

In MySQL's stored procedure language, you have access to two different types of variables.

The first you declare with DECLARE and these are locally scoped to the procedure. Do not prefix these with @.
The second type of variable are called user variables and they are prefixed with @. You don't have to declare these. But they are kind of like "global" variables in that they are scoped to the session, and you can assign and read them outside the procedure. That is, you could assign a value to one of these variables in the procedure, and then after the procedure is done, you could read that user variable in any SQL expression.

In your procedure, you also have some input arguments. These are treated like the first type of variable. They are locally scoped, and they don't use the @ prefix. For example, you have an input argument UserID but this is not the same variable as @UserID that you use in your query.

This brings up the possibility of ambiguity when your variables have the same names as columns, and you try to use the variables to compare to the columns. MySQL does its best to have rules for precedence, but it is far easier to debug if you make sure your local variable names don't use the same names as columns.

Other issues:

This line:

SELECT @RowID := @@Identity;

Should be as follows in MySQL:

SET @RowID = LAST_INSERT_ID();

Comments starting with -- must have a space following them, which is non-ANSI but is meant to resolve syntax ambiguity. E.g., this:

--User

Must be changed to:

-- User


SQL Server Migrate Database - Set Compatibility Level

In general, it is not advisable to run a database in an older compatibility mode permanently, and the relevant Microsoft documentation from
ALTER DATABASE Compatibility Level (Transact-SQL) is:

  Use compatibility level as an interim migration aid to work around version differences

So the best option is to avoid doing it if possible.

Having said that, in the real world, we have ridiculous numbers of databases out there on our servers, and security concerns are pressuring us to get off of older versions of SQL Server and Windows.  Meanwhile, the application vendor is out of business, or the organization won't upgrade it because it is planning to retire the application (and maybe has been planning to do that for the past few years), or it would just cost too much to upgrade it, etc.  So I think it is not a rare situation where databases are being run permanently in an older compatibility mode.  Limit your risk by:

Thoroughly testing the solution
Maybe don't do this with a database that has a massive amount of code, a lot of users, or a high rate of transactions
Advise management of the risk


SQL Server - Round a number when it's one decimal before .50

If you want something other than a standard rounding, you will probably need to use a nested CASE statement.
Maybe take something like the below and tweak it:
DECLARE @Num DEC (10,2) = 8.49 --8.48

SELECT
CASE
WHEN @Num - FLOOR(@Num) >= 0.5 THEN ROUND(@Num,1)
WHEN @Num - FLOOR(@Num) = 0.49 THEN FLOOR(@Num) + 0.5
ELSE @Num
END AS NewValue


How to resolve: Specified key was too long; max key length is 767 bytes

I suspect the difference is a Unicode length issue. As you are not using a single-byte-per-character collation the total length of the fields in your key is 530 character (75+200+255) and not 530 bytes, and 530 characters could easily be more than 767 bytes depending on what language you are storing in those strings (in fact definitely will be if mysql stores unicode data always as two-bytes-per-character as MS SQL Server does in its NVARCHAR column type).

As to why it is working in Dev but not live:

If mysql doesn't use a fixed length encoding then it could be that existing data in some rows in production is long enough to break the 767 byte limit, but in Dev there are no such rows. The length() function (http://dev.mysql.com/doc/refman/5.1/en/string-functions.html#function_length) returns the number of bytes, not characters, so you can scan for such rows by selecting where LENGTH(className)+LENGTH(key)+LENGTH(owner) > 767.
Otherwise it could be that your collation settings are not identical between dev and production, try verifying those.


How to export an ER diagram from an Oracle DB?

Have you got access to Microsoft Visio?

If you can get an ODBC connection to your Oracle DB then you can use Visio to scan the database.  It will display all the entities, relationships, etc.

Have a look at this on Stack Exchange
https://stackoverflow.com/questions/4891387/how-can-i-import-the-contents-of-an-oracle-database-into-visio-to-create-an-enti

Hope that helps


PGAdmin4 Browser: Autocomplete not working

It works if you deactivate the default MacOS shortcut for switching the input sources (keyboard layouts)


Can a query whose execution plan uses parallelization use the cores of a processor on a another server that's part of the same Availability Group?

No, execution plan parallelization refers to using multiple cores on the server executing the query.  Parallelize plans are not executed across availability group replicas.


SQL Server - Extremely low avg_page_space_used_in_percent for heap table

This could be simply because of some delete or update operation removing rows from the page and up till now it was never used to fill additional rows. I do not see anything worrying here.

Go ahead and rebuild the heap to get more page fullness, but please note any nonclustered indexes (NCI) on the heap would also be rebuilt. - Shanky

Using a clustered table instead of a heap will fix likely these symptoms. - Dan Guzman


Get another table row on the basis of first table

...
IF ( EXISTS ( SELECT * FROM ... WHERE ... ),  -- "If any row found.."
     ( SELECT ... ),   -- "then run .."
     id ),             -- "If no row found, return .."
...


Is it possible to estabilish Dedicated Administrator Connection (DAC) using SSMS?

This error occurs when you try to connect to DAC using the right click Connect and the  Connect to Server dialog from the Object explorer

However, you can connect to DAC via SSMS by clicking the Database Engine Query button

The Connect To Database Engine dialog will pop out. Type the "admin:" prefix before the server name and you'll be connected to DAC

Hope this helps


Get reference count of reverse foreign key relations

I don't know postgres well, but I am not aware of any way to do this in a single query. I also doubt there'll be a truly portable way, but the approach is similar.

Generally, I think you'd need to:

Query system (meta) tables to find all foreign key table and column names.
Use this to generate a (possibly massive) select query that counts all the 12s


mongodb service not running after changing path

It is possible that mongodb.conf is not getting loaded because of wrong location (typically this is /etc/mongodb.conf) or a typo when you launch through service start

Try launching manually

mongod --config /etc/mongodb.conf

to rule out any issues with the conf file.


PostgreSQL replication not working after base backup

Your base backup looks old as current master (earlier slave) must be on timeline greater than 1 after getting promoted as master but your slave is on still timeline 1.
You can either create a new slave from scratch after taking a new base backup from new master.
Or you can try timeline switch in the current non replicating slave (not sure about it) refer it here http://paquier.xyz/postgresql-2/postgres-9-3-feature-highlight-timeline-switch-of-slave-node-without-archives/


Prepopulate database with empty / null values [mysql]

Actually, yes you can start an empty table's auto_increment from 101.

SAMPLE CODE

use test
drop table if exists fizzydrink;
create table fizzydrink
(id int not null auto_increment,num1 int,num2 int,primary key (id));
show create table fizzydrink\G
alter table fizzydrink auto_increment = 101;
show create table fizzydrink\G
insert into fizzydrink (num1,num2) values (1,2),(3,4),(5,6);
select * from fizzydrink;

SAMPLE CODE EXECUTED

mysql> use test
Database changed
mysql> drop table if exists fizzydrink;
Query OK, 0 rows affected (0.02 sec)

mysql> create table fizzydrink
    -> (id int not null auto_increment,num1 int,num2 int,primary key (id));
Query OK, 0 rows affected (0.05 sec)

mysql> show create table fizzydrink\G
*************************** 1. row ***************************
       Table: fizzydrink
Create Table: CREATE TABLE `fizzydrink` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `num1` int(11) DEFAULT NULL,
  `num2` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

mysql> alter table fizzydrink auto_increment = 101;
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> show create table fizzydrink\G
*************************** 1. row ***************************
       Table: fizzydrink
Create Table: CREATE TABLE `fizzydrink` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `num1` int(11) DEFAULT NULL,
  `num2` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=101 DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

mysql> insert into fizzydrink (num1,num2) values (1,2),(3,4),(5,6);
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> select * from fizzydrink;
+-----+------+------+
| id  | num1 | num2 |
+-----+------+------+
| 101 |    1 |    2 |
| 102 |    3 |    4 |
| 103 |    5 |    6 |
+-----+------+------+
3 rows in set (0.00 sec)

mysql>

CREATE 100 INITIAL USERS

use test
drop table if exists fizzydrink;
create table fizzydrink
(id int not null auto_increment,num1 int,num2 int,primary key (id));
show create table fizzydrink\G
insert into fizzydrink (id) values
(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),
(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),
(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),
(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),
(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),
(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),
(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),
(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),
(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),
(0),(0),(0),(0),(0),(0),(0),(0),(0),(0);
select * from fizzydrink;

You cannot add user id 0 because the auto_increment interprets it as GET NEXT ID

GIVE IT A TRY !!!


How to join 2 tables in mysql but with different values in one of the tables

You can use LEFT JOIN instead of JOIN.

With LEFT JOIN the query will return all records from the left table i.e. the one in the FROM, even if there is no matching record in the joint table.

SELECT * FROM inbox_messages_table i
LEFT JOIN users_table u ON i.from_user_id = u.user_id
WHERE u.banned = 0 AND to_user_id = $user_id AND deleted = 0 ORDER BY message_timestamp DESC


Permissions for SSIS/SQL Server agent jobs for multiple user groups

I'm not seeing how I can limit security to keep each AD group only able to run their own packages from a job.

If users are in different AD groups and their visibility into the SSIS catalog is properly restricted, then you should be able to assume that only the appropriate SSIS packages will be selected when creating new jobs or modifying existing jobs.  So what you really want to know comes down to your question of...

  What permissions should I use for Active Directory groups to be able to deploy and then run/schedule their own jobs only?

Creation, scheduling, and starting/stopping of SQL Agent jobs are all actions that involve two things, namely the current owner of the job and the account of the user logged in regards to their privileges via those three roles you mentioned.

I would solve this by creating one login per department and update the "owner" of existing jobs to the login which corresponds to the department that is responsible for each respective job.  Going forward, jobs are only assigned one of those logins.

For example, if each of these three logins belongs to the SQLAgentUserRole role, anyone using the login for Dept_A will only be able to see and modify jobs only owned by that login.  Jobs owned by logins associated with Dept_B and Dept_C won't even be visible when utilizing the login for Dept_A.  Credentials and SQL Agent proxies can then be setup in order to give any extra permissions to that login if needed (SSIS steps execute under the service account but any TSQL steps will use the job owner).  I'm not completely sure if a credential and proxy can be setup to allow users to update jobs as their assigned login/owner, though.

Another option is to create stored procedures that wrap around the system-supplied stored procedures to conduct actions.  Also see the social MSDN thread here.  The downside with this is that I'm not sure how GUI-friendly this approach is and you'll need to create a wrapper for each stored procedure that does something (sp_update_job, sp_start_job).  It will force all job changes to be scripted out which is a good practice in and of itself for things like review, troubleshooting, and documentation of changes but the trade-off is that more work happens for the developers.


Well, it would have been a lot faster and easier with 9.4, but it's doable with 9.3 in simple situations.

SELECT
('{' ||       -- Opening brace for the JSON object
string_agg(   -- Get all the key/value pairs together in the same string
concat_ws(':', '"' || a || '"', b || ''), ',') || '}')::json -- Concatenate values to "key" : "value" format

FROM (
-- Extract the key and value from each array element
SELECT json_array_elements(myjson)->'key' a,
json_array_elements(myjson)->'value' b FROM temp) AS FOO;

Not fun, but thankfully this is a one-time thing.

This can't be used to update a table, but you can create a temporary table and insert the values there first.


SQL Server - db_owner user can't see sys schemas

Ok, i resolved it. Sometimes google first, sorryu for post :)

It was necessary allow Ole Automation Procedures:

    sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
sp_configure 'Ole Automation Procedures', 1;
GO
RECONFIGURE;
GO

And then grant rights to user:

grant exec on schema::sys to [USER]


Get the 1st record and last record of a day in PostgreSQL

SELECT DISTINCT
    DATE_TRUNC('day', Createdtime) "Created date",
    MIN(Createdtime) OVER (PARTITION BY DATE_TRUNC('day', Createdtime)) "1st record",
    FIRST_VALUE(Value)  OVER (PARTITION BY DATE_TRUNC('day', Createdtime)
                              ORDER BY Createdtime ASC) "1st Value",
    MAX(Createdtime) OVER (PARTITION BY DATE_TRUNC('day', Createdtime)) "last record",
    FIRST_VALUE(Value)  OVER (PARTITION BY DATE_TRUNC('day', Createdtime)
                              ORDER BY Createdtime DESC) "last Value",
    FIRST_VALUE(Value)  OVER (PARTITION BY DATE_TRUNC('day', Createdtime)
                              ORDER BY Createdtime ASC)
    -
    FIRST_VALUE(Value)  OVER (PARTITION BY DATE_TRUNC('day', Createdtime)
                              ORDER BY Createdtime DESC) "diff in Value"
FROM sourcetable


Why is Primary Key not present at the end of Unique Key in MySQL?

But as seen in above snippet, the b_Unique key does not contain primary key a as the final key part.

It is a snippet problem.

show index from test

Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression
:---- | ---------: | :------- | -----------: | :---------- | :-------- | ----------: | -------: | :----- | :--- | :--------- | :------ | :------------ | :------ | :---------
test  |          0 | PRIMARY  |            1 | a           | A         |           0 |     null | null   |      | BTREE      |         |               | YES     | null
test  |          0 | b_UNIQUE |            1 | b           | A         |           0 |     null | null   | YES  | BTREE      |         |               | YES     | null
test  |          1 | single   |            1 | c           | A         |           0 |     null | null   | YES  | BTREE      |         |               | YES     | null
test  |          1 | double   |            1 | d           | A         |           0 |     null | null   | YES  | BTREE      |         |               | YES     | null
test  |          1 | double   |            2 | e           | A         |           0 |     null | null   | YES  | BTREE      |         |               | YES     | null
test  |          1 | triple   |            1 | f           | A         |           0 |     null | null   | YES  | BTREE      |         |               | YES     | null
test  |          1 | triple   |            2 | g           | A         |           0 |     null | null   | YES  | BTREE      |         |               | YES     | null
test  |          1 | triple   |            3 | h           | A         |           0 |     null | null   | YES  | BTREE      |         |               | YES     | null

show extended index from test

Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression
:---- | ---------: | :------- | -----------: | :---------- | :-------- | ----------: | -------: | :----- | :--- | :--------- | :------ | :------------ | :------ | :---------
test  |          0 | PRIMARY  |            1 | a           | A         |           0 |     null | null   |      | BTREE      |         |               | YES     | null
test  |          0 | PRIMARY  |            2 | DB_TRX_ID   | A         |        null |     null | null   |      | BTREE      |         |               | YES     | null
test  |          0 | PRIMARY  |            3 | DB_ROLL_PTR | A         |        null |     null | null   |      | BTREE      |         |               | YES     | null
test  |          0 | PRIMARY  |            4 | b           | A         |        null |     null | null   | YES  | BTREE      |         |               | YES     | null
test  |          0 | PRIMARY  |            5 | c           | A         |        null |     null | null   | YES  | BTREE      |         |               | YES     | null
test  |          0 | PRIMARY  |            6 | d           | A         |        null |     null | null   | YES  | BTREE      |         |               | YES     | null
test  |          0 | PRIMARY  |            7 | e           | A         |        null |     null | null   | YES  | BTREE      |         |               | YES     | null
test  |          0 | PRIMARY  |            8 | f           | A         |        null |     null | null   | YES  | BTREE      |         |               | YES     | null
test  |          0 | PRIMARY  |            9 | g           | A         |        null |     null | null   | YES  | BTREE      |         |               | YES     | null
test  |          0 | PRIMARY  |           10 | h           | A         |        null |     null | null   | YES  | BTREE      |         |               | YES     | null
test  |          0 | b_UNIQUE |            1 | b           | A         |           0 |     null | null   | YES  | BTREE      |         |               | YES     | null
test  |          0 | b_UNIQUE |            2 | a           | A         |        null |     null | null   |      | BTREE      |         |               | YES     | null
test  |          1 | single   |            1 | c           | A         |           0 |     null | null   | YES  | BTREE      |         |               | YES     | null
test  |          1 | single   |            2 | a           | A         |        null |     null | null   |      | BTREE      |         |               | YES     | null
test  |          1 | double   |            1 | d           | A         |           0 |     null | null   | YES  | BTREE      |         |               | YES     | null
test  |          1 | double   |            2 | e           | A         |           0 |     null | null   | YES  | BTREE      |         |               | YES     | null
test  |          1 | double   |            3 | a           | A         |        null |     null | null   |      | BTREE      |         |               | YES     | null
test  |          1 | triple   |            1 | f           | A         |           0 |     null | null   | YES  | BTREE      |         |               | YES     | null
test  |          1 | triple   |            2 | g           | A         |           0 |     null | null   | YES  | BTREE      |         |               | YES     | null
test  |          1 | triple   |            3 | h           | A         |           0 |     null | null   | YES  | BTREE      |         |               | YES     | null
test  |          1 | triple   |            4 | a           | A         |        null |     null | null   |      | BTREE      |         |               | YES     | null


I was lying when I said, I'm not able to recode it into SQL. I was just too lazy. Here is the code with an example of usage.

The Code is based on a TheiSen perl library, using QuickMedian.
Let's define a new table type to easily pass our data to the procedure.

CREATE TYPE dbo.TheilSenInputDataTableType AS TABLE
(
    ID INT IDENTITY(1,1),
    x REAL,
    y REAL
)

Please note the ID column, which is important here as our solution uses CROSS APPLY statement in order to achieve correct interpretation of the inner loop found in TheilSen.pm.

my ($x1,$x2,$y1,$y2);
foreach my $i(0 .. $n-2){
    $y1 = $y->[$i];
    $x1 = $x->[$i];
    foreach my $j($i+1 .. $n-1){
        $y2 = $y->[$j];
        $x2 = $x->[$j];

We will also need a new data type for storing an array of real type values.

CREATE TYPE [dbo].[RealArray] AS TABLE(
    [val] [real] NULL
)

Here is the f_QuickMedian function, returning median for given array. Credit for this one goes to Itzik Ben-Gan.

CREATE FUNCTION [dbo].[f_QuickMedian](@RealArray RealArray READONLY)
RETURNS REAL
AS
BEGIN
    DECLARE @Median REAL;
    DECLARE @QMedian REAL;

    SELECT @Median = AVG(1.0 * val)
    FROM
    (
        SELECT o.val, rn = ROW_NUMBER() OVER (ORDER BY o.val), c.c
        FROM @RealArray AS o
        CROSS JOIN (SELECT c = COUNT(*) FROM @RealArray) AS c
    ) AS x
    WHERE rn IN ((c + 1)/2, (c + 2)/2);

    SELECT TOP 1 @QMedian = val FROM @RealArray
    ORDER BY ABS(val - @Median) ASC, val DESC

    RETURN @QMedian
END

And the p_TheilSen Estimator:

CREATE PROCEDURE [dbo].[p_TheilSen](
      @TheilSenInput TheilSenInputDataTableType READONLY
    , @m Real OUTPUT
    , @c Real OUTPUT
)
AS
BEGIN
    DECLARE
        @m_arr RealArray
      , @c_arr RealArray;

    INSERT INTO @m_arr
        SELECT m
        FROM
        (
            SELECT
                t1.x as x1
                , t1.y as y1
                , t2o.x as x2
                , t2o.y as y2
                , t2o.y-t1.y as [y2 - y1]
                , t2o.x-t1.x as [x2 - x1]
                , CASE WHEN (t2o.x <> t1.x) THEN  CAST((t2o.y-t1.y) AS Real)/(t2o.x-t1.x) ELSE NULL END AS [($y2-$y1)/($x2-$x1)]
                , CASE WHEN t1.y = t2o.y THEN 0
                  ELSE
                    CASE WHEN t1.x = t2o.x THEN NULL
                        ELSE
                        -- push @M, ($y2-$y1)/($x2-$x1);
                        CAST((t2o.y-t1.y) AS Real)/(t2o.x-t1.x)
                    END
                  END as m
            FROM @TheilSenInput t1
            CROSS APPLY
                    (
                    SELECT  t2.x, t2.y
                    FROM    @TheilSenInput t2
                    WHERE   t2.ID > t1.ID
                     ) t2o
        ) t
        WHERE m IS NOT NULL

    SELECT @m = dbo.f_QuickMedian(@m_arr)

    INSERT INTO @c_arr
        SELECT y - (@m * x)
            FROM @TheilSenInput

    SELECT @c = dbo.f_QuickMedian(@c_arr)

END

Example:

DECLARE
      @in TheilSenInputDataTableType
    , @m Real
    , @c Real

INSERT INTO @in(x,y) VALUES (10.79,118.99)
INSERT INTO @in(x,y) VALUES (10.8,120.76)
INSERT INTO @in(x,y) VALUES (10.86,122.71)
INSERT INTO @in(x,y) VALUES (10.93,125.48)
INSERT INTO @in(x,y) VALUES (10.99,127.31)
INSERT INTO @in(x,y) VALUES (10.96,130.06)
INSERT INTO @in(x,y) VALUES (10.98,132.41)
INSERT INTO @in(x,y) VALUES (11.03,135.89)
INSERT INTO @in(x,y) VALUES (11.08,139.02)
INSERT INTO @in(x,y) VALUES (11.1,140.25)
INSERT INTO @in(x,y) VALUES (11.19,145.61)
INSERT INTO @in(x,y) VALUES (11.25,153.45)
INSERT INTO @in(x,y) VALUES (11.4,158.03)
INSERT INTO @in(x,y) VALUES (11.61,162.72)
INSERT INTO @in(x,y) VALUES (11.69,167.67)
INSERT INTO @in(x,y) VALUES (11.91,172.86)
INSERT INTO @in(x,y) VALUES (12.07,177.52)
INSERT INTO @in(x,y) VALUES (12.32,182.09)

EXEC p_TheilSen @in, @m = @m OUTPUT, @c = @c OUTPUT

SELECT @m
SELECT @c

Returns:

m = 52.7079
c = -448.4853

Just for a comparision, perl version returns following values for the same data set:

m = 52.7078651685394
c = -448.484943820225

I use TheilSen estimator to calculate DaysToFill metric for filesystems.
Enjoy!


MySQL query - how to remove variable data

Thanks for your replies.
The examples of text are shown in my original post, but I had to put the "code" formatting around them as the forum would not let me post without doing that. Here are the examples again:

">https://www.ebay.co.uk/itm/SUZUKI-DR-75 ... Sw7k9eIFHa

">http://www.hagon-shocks.co.uk/catalog/c ... x?TypeID=F
">https://www.moorespeedracing.co.uk/indi ... gKQjPD_BwE

I want to remove this whole section of text, but as you can see the centre section (i.e. the www part) varies in each line.
It is MySQL 5.5.65
Sorry, I have no idea how to use "fiddle".


Just use a simple and standardized GROUP BY and COUNT(DISTINCT ..)

CREATE TABLE foo(a,b,c)
AS VALUES
    ( 1, 20, 1 ),
    ( 3, 21, 2 ),
    ( 1, 22, 3 ),
    ( 2, 22, 4 ),
    ( 3, 21, 5 ),
    ( 1, 20, 6 );

SELECT a, COUNT(DISTINCT b)
FROM foo
GROUP BY a;


mysql client gives no output when trying to connect to a remote DB

Check your my.cnf settings.
Make sure the bind-address variable is commented or its value is 0.0.0.0

Make sure port 3306 (MySQL Default Port) is open (Both on the server and on the client).


What is the better way to handle a different types of measurement in grocery store items database

Using postgresql-unit

I'm big on types. I would use an extension for this. If you're not in the US and working with Imperial Units, take a look at Christoph Berg's brainchild postgresql-unit. First build the extension

sudo apt-get install bison flex build-essentials libpq-dev
git clone "https://github.com/ChristophBerg/postgresql-unit"
cd postgresql-unit
make
sudo make install

Then install the extension on your DB and configure your table.

CREATE EXTENSION unit;

CREATE TABLE products (
  id     serial        PRIMARY KEY,
  name   text          NOT NULL,
  unit   unit          NOT NULL,
  price  numeric(7,2)
);

INSERT INTO products ( name, unit, price )
VALUES ( 'Green Peas', '250 g', '2.99' );

# SELECT name, unit, price FROM products;
    name    | unit  | price
------------+-------+-------
 Green Peas | 250 g |  2.99

# SELECT name, unit @ 'kg' AS unit, price FROM products;
    name    |  unit   | price
------------+---------+-------
 Green Peas | 0.25 kg |  2.99

Likewise you can INSERT in other units if need be. They'll get stored in those units, but you can select in any unit you want.

INSERT INTO products ( name, unit, price )
VALUES ( 'KY Jelly', '1 kg', '8.00' );

# TABLE products;
 id |    name    | unit  | price
----+------------+-------+-------
  2 | Green Peas | 250 g |  2.99
  3 | KY Jelly   | 1 kg  |  8.00

# SELECT id, name, unit @ 'g' AS unit, price FROM products;
 id |    name    | unit   | price
----+------------+--------+-------
  2 | Green Peas | 250 g  |  2.99
  3 | KY Jelly   | 1000 g |  8.00


MySQL - LOAD DATA INFILE - Handling unescaped NULL in INFILE or creating INFILE with escaped NULL

Have you looked into the SELECT INTO OUTFILE syntax?

Here is a quote on the NULL string issue:

  If the FIELDS ESCAPED BY character is empty, no characters are escaped and NULL is output as NULL, not \N. It is probably not a good idea to specify an empty escape character, particularly if field values in your data contain any of the characters in the list just given.

So make sure you indicate a FIELDS ESCAPED BY clause to get NULLs written as \N

Hope this helps


Avoid tables locking while doing reindex

PostgreSQL documentation at https://www.postgresql.org/docs/10/sql-reindex.html
says the following:

  REINDEX is similar to a drop and recreate of the index in that the
  index contents are rebuilt from scratch. However, the locking
  considerations are rather different. REINDEX locks out writes but not
  reads of the index's parent table. It also takes an exclusive lock on
  the specific index being processed, which will block reads that
  attempt to use that index. In contrast, DROP INDEX momentarily takes
  an exclusive lock on the parent table, blocking both writes and reads.
  The subsequent CREATE INDEX locks out writes but not reads; since the
  index is not there, no read will attempt to use it, meaning that there
  will be no blocking but reads might be forced into expensive
  sequential scans.

So based on your requirements, you could change REINDEX into DROP INDEX + CREATE INDEX, which is what Pg::Reindex that you link to does.

The associated PostgreSQL documentation about why REINDEX is useful for B-Trees indexes reiterates the idea (at https://www.postgresql.org/docs/10/routine-reindex.html):

  REINDEX can be used safely and easily in all cases. But since the
  command requires an exclusive table lock, it is often preferable to
  execute an index rebuild with a sequence of creation and replacement
  steps. Index types that support CREATE INDEX with the CONCURRENTLY
  option can instead be recreated that way. If that is successful and
  the resulting index is valid, the original index can then be replaced
  by the newly built one using a combination of ALTER INDEX and DROP
  INDEX. When an index is used to enforce uniqueness or other
  constraints, ALTER TABLE might be necessary to swap the existing
  constraint with one enforced by the new index. Review this alternate
  multistep rebuild approach carefully before using it as there are
  limitations on which indexes can be reindexed this way, and errors
  must be handled.

Other than that the classical methodology is to create a new table, copy data into it, index it, and then rename it so that it takes the place of the old table. Of course you will need to handle properly accesses during that time, and all synchronization errors.

Also more generally how did you count about the free space gained with each method, and are you using autovacuum properly? Are you really so much constrained by space continously that you need to do this reindexing regularly? Otherwise you could schedule it during other maintenance windows.


How to put SQL Server 2012 in single-user mode

To restore master database SQL Server must be brought in single user mode, not the master database. As a fact you cannot bring master database in single user mode you would get error what you have pasted in the question.

To simply start SQL Server in single user mode. First stop SQL Server agent and then open command prompt with admin privileges.
Run:

net stop MSSQLSERVER

MSSQLSERVER is default instance. If you have named instance it would be.

net stop mssql$instance_name.

After above is done check SQL Server services are stopped.

Now open new command prompt(as administrator) go to Binn folder and run

sqlservr -m--if you have default instance

sqlservr -m -s instance_name--for named instance

NOTE: It is sqlservr

This will cause SQL Server to start in single user mode. You can check more details Here.

Now go ahead and launch new Command prompt as admin and login as below

SQLCMD –S hostanme\instance_name - E--for named instance

sqlcmd -S -E--for default instance

-E parameter is here for trusted connection and -S is for server name

After you are able to connect, run the restore command.

Now Again stop SQL Server service and start, you can do this from SQL Server configuration manager.

Please read This Blog for your reference


Querying a table with 200+ million rows by filtering on the clustered index AND additional column is super slow

Every time you add a new column predicate, the optimizer builds (sampled) statistics on that column to compute selectivity. This is where the time goes - building the statistics. On a large table, that can take a while, even with a low sampling rate.


Design using multiple tables for 1 booking table

Common table will be the best approach in my opinion. For example,

Person (person_id (PK), first_name, last_name, birthday);

Student (person_id(PK,FK to Person),...);
Staff (person_id(PK,FK to Person),job_title);
Association_member( person_id(PK,FK to Person), reg_date) ;
Resource_hire_log(resource_id, person_id (FK to Person), ....) ;

You may also think about Student, Staff, and Association member as roles which person can play .  Then you don't need Student, Staff, etc tables, but Role and Person_Role.

Alternatively, you can use exclusive arcs approach :

Resource_hire_log(resource_id,
 student_id (FK to Student), staff_id (FK to Staff),
 association_member_id (FK to Association_member), .... )

Then you will need to add check constraint to ensure only one of (student_id, staff_id, association_member_id ) is not null.


SQL Server - Select most recent record from each group when performance is critical

If you have a small-enough number of (StationID, ParameterID) pairs, then try a query like this:

select StationID, ParameterID, m.DateTime LastDate
from StationParameter sp
cross apply
  (
     select top 1 DateTime
     from MyTable
     where StationID = sp.StationID
      and ParameterID = sp.ParameterID
     order by DateTime desc
  ) m

To enable SQL Server to perform a lookup, seeking the latest DateTime for each (StationID,ParameterID) pair.

With only a Clustered Index on (StationID, ParameterID, DateTime), there's no way for SQL Server to discover the distinct (StationID, ParameterID) pairs without scanning the leaf level of the index, and it can find the largest DateTime while it's scanning.

Also at 100M+ rows, this table might be better as a Clustered Columnstore instead of a BTree Clustered Index.


Mongodb find() not working as expected, PHP

I got it working finally!

This is what I did.

$countries = $countries_tb->find(array('name.common' =>   array('$regex' =>  'Burk') ) );

Notice the . in name.common

Here is my foreach loop'

foreach( $countries as $country => $name) {

   foreach ($name as $n){

       if(isset($n->common)){

            var_dump($n->common);

        }

   }
}

//It prints out:    country_cities.php:90:string 'Burkina Faso' (length=12)

Somehow, this does NOT work

$countries = $countries_tb->find(
                         array('name'=>
                                 array('common' =>
                                         array('$regex' =>   'Burk')) ) );

This is the JSON'

{"_id":"55a0f42f20a4d760b5fc306d","altSpellings":["BF"],"area":272967,"borders":["BEN","CIV","GHA","MLI","NER","TGO"],"callingCode":["226"],"capital":"Ouagadougou","cca2":"BF","cca3":"BFA","ccn3":"854","cioc":"BUR","currency":["XOF"],"demonym":"Burkinabe","landlocked":true,"languages":{"fra":"French"},"latlng":[13,-2],"name":{"common":"Burkina Faso","native":{"fra":{"common":"Burkina Faso","official":"Burkina Faso"}},"official":"Burkina Faso"},"region":"Africa","subregion":"Western Africa","tld":[".bf"],"translations":{"cym":{"common":"Burkina Faso","official":"Burkina Faso"},"deu":{"common":"Burkina Faso","official":"Burkina Faso"},"fin":{"common":"Burkina Faso","official":"Burkina Faso"},"fra":{"common":"Burkina Faso","official":"Burkina Faso"},"hrv":{"common":"Burkina Faso","official":"Burkina Faso"},"ita":{"common":"Burkina Faso","official":"Burkina Faso"},"jpn":{"common":"ブルキナファソ","official":"ブルキナファソ"},"nld":{"common":"Burkina Faso","official":"Burkina Faso"},"por":{"common":"Burkina Faso","official":"Burkina Faso"},"rus":{"common":"Буркина-Фасо","official":"Буркина -Фасо"},"spa":{"common":"Burkina Faso","official":"Burkina Faso"}}}


User created index question - sp_Blitz 5/17/18 - Incorrect output?

You can have rows in sys.stats without a corresponding row in sys.tables.

Classic example: an indexed view. Watch this:

CREATE TABLE dbo.T1 (ID INT PRIMARY KEY CLUSTERED);
INSERT INTO dbo.T1 VALUES (1);
GO
CREATE OR ALTER VIEW dbo.vwJetta WITH SCHEMABINDING AS
  SELECT T1.ID
  FROM dbo.T1;
CREATE UNIQUE CLUSTERED INDEX CLIX ON dbo.vwJetta(ID);
GO
CREATE STATISTICS STAT ON dbo.vwJetta(ID);
GO
SELECT * FROM sys.stats WHERE user_created = 1;

Our query correctly shows a row:

Whereas your query does not, because there's no table involved:

Moral of the story: never, ever doubt anything you see on BrentOzar.com. Just kidding. But believe what you're seeing right here, and get to work digging into those stats.


Extracting dates from PostgreSQL database

Am I making a mistake using jsonb to store the configuration?

Yes. You're querying on it. That's usually an indicator to me that you're abusing JSONB.

A few other notes,

On your jsonb formatting. I've cleaned it up. Using JSON, "4" is not the same as 4. You should store numbers as numbers and not as strings
Your default for configuration is {}, but your sample data shows [{},{}]. It's bad practice to store some arrays-of-objects, and some objects in the top level. Pick one.


Can I get top 10 remote clients by average query count per minute?

Not sure it is possible to get exactly what you want. It probably depends on the setup of the application calling your database, how its connections are pooled etc. But the following will give you the number of current connections, plus their IP and application names:

SELECT c.client_net_address, s.[program_name], s.login_name
    , CURRENT_TIMESTAMP, COUNT(*) as ConnCount
FROM sys.dm_exec_connections c
INNER JOIN sys.dm_exec_sessions s ON c.session_id = s.session_id
GROUP BY c.client_net_address, s.[program_name], s.login_name

Unfortunately this doesn't indicate the activity on the connections. I don't know if the dmv's will be able to return you a history of all the calls broken down by connection/session, which sounds like what you want. This is exactly the sort of thing extended events are for, so in the absence using extended events, you may be stuck.

Coming from an execution side, you could do it for all queries in the last minute using something like the below:

SELECT  eqs.last_execution_time AS [Date Time], execsql.text AS [Script], [sql_handle]
FROM sys.dm_exec_query_stats eqs
CROSS APPLY sys.dm_exec_sql_text(eqs.[sql_handle]) AS execsql
WHERE eqs.last_execution_time >= DATEADD(mi,-1,CURRENT_TIMESTAMP)
ORDER BY eqs.last_execution_time DESC

Unfortunately this does not show connection information. What you are really after is a combination of the above two, all connections in the past minute and how many executions they performed. Unfortunately the dmv sys.dm_exec_connections only lists the most recent sql query, otherwise it would be a good candidate.

If you really needed this information, you could store both the above queries in tables, running inserts into the tables every minute using a job that is scheduled to run every minute. You could then analyse both tables and make some assumptions about what is calling the procedures.

There may be a way to provide all historic executions from a session/connection using dmv's which I don't know, hopefully this will put you on the right track.


Because you have OPTIMIZER_FEATURES_ENABLE at 8.1.7, OPTIMIZER_MAX_PERMUTATIONS is defaulting to a very high value. Fortunately this parameter is dynamic so you can set it just for your session without affecting your legacy application.


YOSHINORI MATSUNOBU'S BLOG would be the place to start. He recommends splitting up files that do sequential writes from files that do random reads and writes.

Random i/o oriented:

Table files (*.ibd)
UNDO segments (ibdata)

Sequential write oriented:

REDO log files (ib_logfile*)
Binary log files (binlog.XXXXXX)
Doublewrite buffer (ibdata)
Insert buffer (ibdata)
Slow query logs, error logs, general query logs, etc

I have referred to this blog in some of my older posts

Jul 29, 2015 : Should the Redo log on a production MySQL server be configured to another location?
Jan 27, 2015 : what would be the primary disadvantages of splitting my tables into table spaces and/or separate schemata
Jun 04, 2014 : Can I move the undo log outside of ibdata1 in MySQL 5.6 on an existing server?
Feb 06, 2014 : MySQL on SSD - what are the disadvantages?
Aug 14, 2013 : How do I determine how much data is being written per day through insert, update and delete operations?

Since you are doing this in Windows, you really need to split up the InnoDB files. Just remember when specifying a Drive to use drive letter, colon, and forward slash or double backslash. See my answer to Change existing datadir path for an example.


Subquery returned more than 1 value. My WHERE clause needs to remove SELECT statements

I found the solution.

I replaced the = (equal signs) to IN.

The WHERE clause now looks like this:

 WHERE
    (END_DATE IS NULL OR END_DATE > @REPORT_DATE)
    AND
    REPORT_SPOOL.FUND_ID IN (SELECT FUND_ID FROM FUND_HLD WHERE [DATE] = @REPORT_DATE)
    AND
    REPORT_SPOOL.FUND_ID IN (SELECT FUND_ID FROM FUND_TRD_LINE_VIEW WHERE [DATE] >= (SELECT DATEFROMPARTS(YEAR(@REPORT_DATE),MONTH(@REPORT_DATE),1)) AND [DATE] <= @REPORT_DATE)


Data Compression Enhancement in SQL Server 2012

There were under-the-covers performance enhancements (nothing concrete, see for example this book excerpt. Otherwise, no, 2008 R2 compression and 2012 compression are functionally equivalent.


The chinese characters are decoded correctly with utl_i18n.unescape_reference.  In fact they just aren't displayed properly in your query result which might not support those special characters.

You can confirm that with this SQL Fiddle.

It is the client which is respsonsible to display the characters appropriately.  If the client can not display a character it may show an upside down question mark, something else or just plain garbage.

So by now, the real question is where do you want to display this string...


If you add static strings to your SELECT Statement you always have to surround them on both sides with single ticks.

But you should urgently consider prepared statements like
https://docs.microsoft.com/de-de/dotnet/api/system.data.sqlclient.sqlcommand.prepare?view=netframework-4.8

String insertQuery = "insert into exam_add
  (id,session_id,Title,From_date,To_date,class_id,is_Post,is_Lock)
select
   '"+id+ "'
   ,s.session
   ,'" + title.Text+"'
   ,'"+ from.Value.Date.ToString("yyyy-MM-dd")+"'
   ,'"+to.Value.Date.ToString("yyyy-MM-dd")+ "'
   , c.class_name
   ,'"+x+"'
   ,'"+x+"'
 from year_session s, classes c
 where s.id = '1' and c.id='" + cls + "'";


Keep default value as 0.0 for 0 or NULL for numeric column

This is just a matter of displaying the data as required:

SQL> create table t1(c1 number(28,3));

Table created.

SQL> insert into t1 values(nvl(null, 0.0));

1 row created.

SQL> select * from t1;

        C1
----------
         0

SQL> col c1 format 999990.0
SQL> select * from t1;

       C1
---------
      0.0


SQL Server 2008 R2 - How to check Foreign Key constraints in a transaction only when committed?

No, server checks Foreign keys for every statement inside transaction

The way is to disable all the FKeys in target DB just after the beginning of transaction and enable them back just before commit.

Useful script:

DECLARE @sql NVARCHAR(MAX) = ''

SELECT @sql = @sql + N'ALTER TABLE [' + OBJECT_SCHEMA_NAME(fk.parent_object_id)+N'].['+OBJECT_NAME(fk.parent_object_id)+N'] NOCHECK CONSTRAINT ['+fk.NAME+N'];'+NCHAR(13)+NCHAR(10)
FROM sys.foreign_keys fk

PRINT @sql