SSRS ReportServer_XXX.log is giving incorrect CPU count

I think the "CPU Throttling" message indicates that the server has a power setting enabled that will dynamically reduce CPU speed to conserve power (usually the "Balanced" power scheme set in BIOS). Not sure if this is related to your issue, but you probably don't want it enabled on a server.


MS SQL :Export data from query output to excel which has a column with huge data

Welcome to hell. I've found that the most reliable way to get data from SQL Server to Excel is to use the querying capability from within Excel itself. The location of this function has moved around in recent releases, but right now, it's under Data, Get Data, From Database. This usually gets the best results for various data types, long data, etc.


Pulling autogrowth events by file name from default trace

As Remus mentioned in a comment, you have to join the trace table result to sys.master_files to get the physical file names.

Here is a completed query. Note that the duration in the trace is in microseconds, and I have adjusted the computation accordingly to output a more sensible metric (hopefully you don't have actual minutes of auto-growth happening):

DECLARE @trcfilename nvarchar(2048);
SELECT @trcfilename = path FROM sys.traces WHERE is_default = 1;

SELECT
    DB_NAME(mf.database_id) AS DatabaseName,
    mf.name AS LogicalFileName,
    mf.physical_name AS PhysicalFileName,
    a.NumberOfGrowths,
    CAST(a.DurationOfGrowthsInSeconds AS decimal(18, 3)) AS DurationOfGrowthsInSeconds
    FROM
    (
        SELECT
            tt.DatabaseID AS database_id,
            tt.FileName AS LogicalFileName,
            COUNT(*) AS NumberOfGrowths,
            SUM(tt.Duration / (1000 * 1000.0)) AS DurationOfGrowthsInSeconds
            FROM sys.fn_trace_gettable(@trcfilename, default) tt
            WHERE (EventClass IN (92, 93))
            GROUP BY
                tt.DatabaseID,
                tt.FileName
    ) a
    INNER JOIN sys.master_files mf ON
        (mf.database_id = a.database_id) AND
        (mf.name = a.LogicalFileName);


Files like #sql_* are temp files used by things like ALTER TABLE.  When successfully finished they are removed.  So, I conclude that and ALTER (or some other admin query) was rudely aborted (such as with a crash).

They can safely be deleted.

You 'should' switch to InnoDB.


Is it better to use multiple one-to-many relationships, or polymorphic?

The question you should ask yourself is whether or not a website click is really the same as an ad click.  Do they have exactly the same predicates? (other than what type of parent they have)  Similarly, when you access click data, will both types often be considered together or are they always treated separately?

The reason that this is important is that you want to draw a distinction between a true subtyping situation and simply having two distinct tables with similar schemas.  Someone with a programming background might be tempted to say: "These two things look alike, so they should be like implementations of an Interface."  This leads you to a polymorphic design.

However, the considerations for entity subtyping are not quite the same as the considerations for code re-use.  If there is no reason to comingle both types of clicks in a single query, then there isn't necessarily a good reason to comingle both types of clicks in a supertype table.  There may be a trade-off to be made between a bit of code reuse and keeping your data organized for convenient and logical retrieval.


Will performance and speed improve when retrieving data from a .txt file instead of the Database?

ORDER BY RAND() LIMIT 6 requires fetching the entire table, shuffling it, then delivering 6 rows.

Performance is depending on how many rows are hit -- it is 10K per request (to get only 6 rows).  Work on shrinking that.

There is no 'perfect' way to fetch 6 rows from a table, but this gives 5 pretty-good ways.  See which one fits best in your app for performance improvement.


I am pretty sure that the sqlservr.exe.config config file works in SQL Server 2014, but will take me a few minutes to confirm. I am also pretty sure that it is not "officially" supported, and so not "recommended" to use, but on occasion I find it handy to use and have done so.

Whether or not the location of the data should be in a config file or table depends largely on what the overall needs are. How often will the values change? You say "hard-coded", but lookup values can change per each release, sometimes. Do you want to have to run an UPDATE statement, or find a way to include maintaining this config file in your build process (you need to find some way to ensure that it is always there).

Also, will the values ever be needed outside of the .NET / SQLCLR code? Having them in a table makes them more available to all existing queries.

Also, will there ever be a need for separation between databases for these values? With a config file it is shared across all App Domains for that Instance. A config table can just as easily be placed in a common Database (assuming multiple databases might want the values) or placed into each Database. You can have separation of values within the config file, but that requires having different names for the keys, and that makes it harder to standardize within the .NET code.

If it makes sense enough to be read-only and common / shared, then why not start out trying the config file approach, and if you find any reason that it doesn't meet your needs, just move it over to being stored in a table. After all, neither option is going to require that many hours of work, and the time difference between them is negligible, so it won't cost much if you do need to switch to the other approach.

NOTES:

In the StackOverflow question linked in this question, the answer from @David states that this config file is no longer working as of SQL Server 2014. I have just tested and confirmed that it does indeed work on SQL Server 2014 and even on 2016!
In the StackOverflow question linked in this question, the answer from @Remus states that storing in the DB is preferred due to the config file being disconnected from SQL Server and possibly not on related servers, or possibly out-of-synch with them. This point should not be ignored, and those concerns should help the team look at the big picture of how the application lives and hence come to a more informed decision. However, there are certainly scenarios that do not have such concerns and which allow for the build process to ensure that this config file is in place in each environment, just as that build process does the same checks for config files on web servers, etc.
In the blog referenced in @Remus's answer in the linked question, the author mentions an "issue" with getting errors attempting to read AppSettings, and a work-around is noted to call ConfigurationManager.ConnectionStrings.Count;. That is correct. However, you don't need to make that call each time; it only needs to be done once for the life of the App Domain. So, it works just fine to place it in a static class constructor as follows:

static _your_class_name_here_()
{
    int _BugBeGone = ConfigurationManager.ConnectionStrings.Count;
}

And no, you do not need to have a <connectionStrings> section in your config file for the above to work.
That same blog post also mentions that the config file is read only once, when the App Domain is created and that you will need to restart the instance -- or possibly unload the App Domain -- in order to have any changes made to the config file after the App Domain is created become visible to your code. The first part is correct: the config file is loaded once and cached for the life of the App Domain. However, you do not need to restart the Instance in order to refresh the config values, nor even unload the app domain. You just need to call:

ConfigurationManager.RefreshSection("appSettings");

But don't do that for every call, unless the function or stored procedure is meant to run occasionally. If this is something that will run at least once per second, or more often, then create a separate function or stored procedure to do just that refresh and execute it when necessary.


How to get all the data before the current date and data for the next 3 days from current date.DATEDIFF(now(),d.tentative_date_of_return) <= now() + 3

You want

AND d.tentative_date_of_return <= CURDATE() + INTERVAL 3 DAY


And that is what we have VPD (Virtual Private Database) for, a feature that developers like to reinvent.

Using Oracle Virtual Private Database to Control Data Access

Set up environment:

create user app identified by app default tablespace users
  quota unlimited on users account lock;
grant create table to app;

create user u1 identified by u1;
create user u2 identified by u2;
create user u3 identified by u3;
grant create session to u1, u2, u3;

CREATE TABLE  app.tblss_user(
user_id       INTEGER NOT NULL,
user_name     VARCHAR2(15),
reg_date      DATE
);

CREATE TABLE app.job (
    job_id      INTEGER NOT NULL,
    job_name    VARCHAR(25),
    job_salary  INTEGER NOT NULL,
    fkuser_id   INTEGER NOT NULL);

grant select, update on app.job to u1, u2, u3;

insert into app.tblss_user values (1, 'U1', sysdate - 400);
insert into app.tblss_user values (2, 'U2', sysdate);
commit;

insert into app.job values(1, 'JOB1', 1000, 1);
insert into app.job values(2, 'JOB2', 1200, 1);
insert into app.job values(3, 'JOB3', 800, 2);
commit;

Our data:

SQL> select * from app.tblss_user;

   USER_ID USER_NAME       REG_DATE
---------- --------------- ---------
         1 U1              24-JAN-17
         2 U2              28-FEB-18

SQL> select * from app.job;

    JOB_ID JOB_NAME                  JOB_SALARY  FKUSER_ID
---------- ------------------------- ---------- ----------
         1 JOB1                            1000          1
         2 JOB2                            1200          1
         3 JOB3                             800          2

Now create the security functions providing the predicates:

create or replace function app.job_select_security (p_owner varchar2, p_table_name varchar2)
return varchar2 as
  l_user_id number;
begin
  if sys_context('userenv', 'session_user') = 'APP' then return null; end if;
  begin
    select user_id into l_user_id from tblss_user
      where user_name = sys_context('userenv', 'session_user');
    exception when no_data_found then return '1=2';
  end;
  return 'fkuser_id = ' || l_user_id;
end;
/

grant execute on app.job_select_security to u1, u2, u3;

create or replace function app.job_update_security (p_owner varchar2, p_table_name varchar2)
return varchar2 as
  l_user_id number;
  l_reg_date date;
begin
  if sys_context('userenv', 'session_user') = 'APP' then return null; end if;
  begin
    select user_id, reg_date into l_user_id, l_reg_date from tblss_user
      where user_name = sys_context('userenv', 'session_user');
    exception when no_data_found then return '1=2';
  end;
  if l_reg_date > sysdate - 365 then return '1=2'; end if;
  return 'fkuser_id = ' || l_user_id;
end;
/

grant execute on app.job_update_security to u1, u2, u3;

Create the policies using the above functions:

BEGIN
  DBMS_RLS.add_policy('APP', 'JOB', 'JOB_SELECT_POLICY',
                      'APP', 'job_select_security',
                      'SELECT', TRUE);
end;
/

begin
  DBMS_RLS.add_policy('APP', 'JOB', 'JOB_UPDATE_POLICY',
                      'APP', 'job_update_security',
                      'UPDATE', TRUE);
END;
/

And now test. Selecting:

SQL> conn u1/u1
Connected.
SQL> select * from app.job;

    JOB_ID JOB_NAME                  JOB_SALARY  FKUSER_ID
---------- ------------------------- ---------- ----------
         1 JOB1                            1000          1
         2 JOB2                            1200          1

SQL> conn u2/u2
Connected.
SQL>
SQL> select * from app.job;

    JOB_ID JOB_NAME                  JOB_SALARY  FKUSER_ID
---------- ------------------------- ---------- ----------
         3 JOB3                             800          2

SQL> conn u3/u3
Connected.
SQL> select * from app.job;

no rows selected

Updating:

SQL> conn u1/u1
Connected.
SQL> update app.job set job_salary=1400;

2 rows updated.

SQL> select * from app.job;

    JOB_ID JOB_NAME                  JOB_SALARY  FKUSER_ID
---------- ------------------------- ---------- ----------
         1 JOB1                            1400          1
         2 JOB2                            1400          1

SQL> conn u2/u2
Connected.
SQL> update app.job set job_salary=1400;

0 rows updated.

SQL> select * from app.job;

    JOB_ID JOB_NAME                  JOB_SALARY  FKUSER_ID
---------- ------------------------- ---------- ----------
         3 JOB3                             800          2

SQL> conn u3/u3
Connected.
SQL> update app.job set job_salary=1400;

0 rows updated.


Function pg_indexes_size(oid) does not exist

8.4 is a pretty ancient version now.  The function you are after is not mentioned in the docs, therefore it is sane to think it simply did not exist back then.  9.0 introduced a number of administration functions, and pg_indexes_size() is one of those.

In any case, it would be prudent to upgrade to a supported version (9.2+ at the time of writing.


Should I create a primary key on Customer_id and mentioned also the
  foreign key in this way it creates automatically a clustered index, or
  should I just declare it as a foreign key and then I create a cluster
  unique index on Customer_id.

The physical indexes (clustered and unique) will be identical in both cases so it makes no difference from a performance perspective whether the index also supports the primary key constraint.  I would personally use a clustered primary key index since relational tables should have a primary key and add the foreign key too.


PostgreSQL : inconsistent result when using WHERE clause on VIEW

The effect of ORDER BY is only mandatory on the same query level.
Your view acts as derived table and if you just SELECT * FROM inv_groups_vw, the order is preserved.
However, when adding another WHERE clause to the outer SELECT, you would have to add ORDER BY to the outer query as well.
In your particular case, the order of rows is not guaranteed, even for the simple query:
SELECT * FROM inv_groups_vw

Since the only ORDER BY in the view is in a subquery itself. It's an implementation detail that the sort order happens to be preserved. That can change any time without notice.
More importantly, your objective seems to be to order the values in items, which is a related, but different issue.
Complete rewrite
After feedback from your comments I think this should be best to fulfill your requirement:

items (i.e. array_agg(i)) are REQUIRED to be ordered by item_index.

CREATE OR REPLACE VIEW inv_groups_vw AS
SELECT g.id,
       g.sku,
       g.name,
       g.description,
       CASE
          WHEN count(i.id) = 0 THEN '[]'::json
          ELSE json_agg(i ORDER BY m.item_index)
       END AS items
FROM   inv_items                    g
LEFT   JOIN inv_items_group_members m ON m.group_id = g.id
LEFT   JOIN inv_items               i ON i.id = m.item_id
                                     AND NOT i.is_group
WHERE  g.is_group
GROUP  BY g.id;    -- assuming g.id is the pk!


SQL Server - How can varbinary(max) store > 8000 bytes?

The maximum size you can specify is 8000.  Anything larger needs to be specified as (MAX)

See https://msdn.microsoft.com/en-us/library/ms188362.aspx for details.


How are page splits determined internally in MySQL?

The author is talking about PAGE_N_DIRECTION. This has nothing to do with triggering splitting, which is performed by page_dir_split_slot. Splitting/merging is determined by a MIN and MAX on the directory slot

/* The maximum and minimum number of records owned by a directory slot. The
number may drop below the minimum in the first and the last slot in the
directory. */
#define PAGE_DIR_SLOT_MAX_N_OWNED 8
#define PAGE_DIR_SLOT_MIN_N_OWNED 4

You don't split when you fall under MIN, you merge (via page_dir_balance_slot).

PAGE_N_DIRECTION is for an optimization,

  Try a search shortcut based on the last insert.


Assuming there are no gaps in the range of dates for a particular code, a simple aggregation query would solve this:

DECLARE @TestData TABLE (
    DateInfo DATE
    ,Code VARCHAR(1)
    )

insert into @TestData (DateInfo,Code)
values
('1/1/2017',    'F'),
('1/2/2017' ,   'F'),
('1/3/2017' ,   'F'),
('1/4/2017' ,   'R'),
('1/5/2017' ,   'P'),
('1/6/2017' ,   'P'),
('1/7/2017' ,   'P'),
('1/8/2017' ,   'P')

;
WITH TestData
AS (
    SELECT min(DateInfo) AS 'From'
        ,max(DateInfo) AS 'To'
        ,Code
    FROM @TestData
    GROUP BY code
    )
SELECT convert(VARCHAR(10), [From], 101) AS 'From'
    ,convert(VARCHAR(10), [To], 101) AS 'To'
    ,Code
FROM TestData
ORDER BY [From]

If the dates for a particular 'code' in your example are not consecutive, then you have what is known as a 'Gaps and Island' problem.  I took this example and tweaked it for your situation.  I added a new row for code 'F' with a date of 1/9/2017 so you could see how the same code could have multiple date ranges.

DECLARE @TestData TABLE (
    DateInfo DATE
    ,Code VARCHAR(1)
    )

insert into @TestData (DateInfo,Code)
values
('1/1/2017',    'F'),
('1/2/2017' ,   'F'),
('1/3/2017' ,   'F'),
('1/4/2017' ,   'R'),
('1/5/2017' ,   'P'),
('1/6/2017' ,   'P'),
('1/7/2017' ,   'P'),
('1/8/2017' ,   'P'),
('1/9/2017' ,   'F')
;
;

WITH mycte
AS (
    SELECT *
        ,DATEADD(DAY, - ROW_NUMBER() OVER (
                PARTITION BY code ORDER BY [DateInfo]
                ), [DateInfo]) AS grp
    FROM @TestData
    )

--select * from mycte       --Uncomment to see the data from mycte

SELECT Code
    ,min([DateInfo]) AS [From]
    ,max([DateInfo]) AS [To]
FROM mycte
GROUP BY Code
    ,grp
ORDER BY [From]

The ROW_NUMBER() window function in this example creates 'partitions' of each code and assigns a row number for each row in each 'partition'.  A new 'code' starts the row number back to 1 for that partition.

The DateAdd logic takes the source date from your table, subtracts 'its' row number as 'days' and creates a 'grp' column that we can use to GROUP BY when we select from mycte.

To get a better understanding of what the code with the ROW_NUMBER() window function is doing, you can uncomment the line that says uncomment to see mycte and just run down the that select statement.  It returns the following result - (Notice that the unbroken date sequences for each 'code' have the exact same 'grp' data.)

Now, it just a matter of selecting the MIN as [From] and MAX as [To] for each 'grp' and order appropriately to give you the final result.


pt-online-schema-change -- can't get it to stop quitting w/ "Threads_running=108 exceeds its critical threshold 100"

The problem was that every now and then an unrelated query put a lot of stress on the server and was causing the threads running to nearly max out. It was doing this regularly before the schema change was first run.


How do I reduce the database size on AWS RDS MySQL?

Write a script that will create a script
MYSQL_CONN="-hAmazonRDSDNSName -u... -p..."
SQL="SELECT CONCAT('OPTIMIZE TABLE ',table_schema,'.',table_name,';')"
SQL="${SQL} OptimizeTableSQL FROM information_schema.tables WHERE table_schema = 'signs'"
SQL="${SQL} ORDER BY (data_length+index_length)"
mysql ${MYSQL_CONN} -AN -e"${SQL}" > /root/OptimizeAllTables.sql
less /root/OptimizeAllTables.sql

When you are satisfied that the script will do the job then do this:
mysql ${MYSQL_CONN} < /root/OptimizeAllTables.sql

Give it a Try !!!
CAVEAT
Since the default for innodb_file_per_table is ON, each InnoDB table being optimized will actually have its .ibd file shrunk. Any MyISAM tables (which I hope you don't have) will also have their .MYD and .MYI files shrunk as well.
UPDATE 2012-09-18 18:19 EDT
I just tried the following:

Created Instance1 with 5G Disk
Modified Instance1 to 10G Disk
Modified Instance1 to 5G Disk (error message : Requested storage size (5) cannot be less then the current storage size (10))
Create Instance2 with 5G
Loaded Instance1 with 2MB data
Created Snapshot of Instance1 (same size : 10G)

If I restore the snapshot, it creates a third Instance. DB Snapshot must be the whole Server Instance, not a dump of any kind.
You will have to do the following messy procedure:

mysqldump the data from the 20GB Instance
Create a New 10G Instance
load the mysqldump into the new 10GB instance
verify all the data you want is present
destroy the old 20GB

At step 4, please run this Big Storage Engine Space Query on both the 20GB and 10GB instances
SELECT IFNULL(B.engine,'Total') "Storage Engine",
CONCAT(LPAD(REPLACE(FORMAT(B.DSize/POWER(1024,pw),3),',',''),17,' '),' ',
SUBSTR(' KMGTP',pw+1,1),'B') "Data Size", CONCAT(LPAD(REPLACE(
FORMAT(B.ISize/POWER(1024,pw),3),',',''),17,' '),' ',
SUBSTR(' KMGTP',pw+1,1),'B') "Index Size", CONCAT(LPAD(REPLACE(
FORMAT(B.TSize/POWER(1024,pw),3),',',''),17,' '),' ',
SUBSTR(' KMGTP',pw+1,1),'B') "Table Size" FROM
(SELECT engine,SUM(data_length) DSize,SUM(index_length) ISize,
SUM(data_length+index_length) TSize FROM
information_schema.tables WHERE table_schema NOT IN
('mysql','information_schema','performance_schema') AND
engine IS NOT NULL GROUP BY engine WITH ROLLUP) B,
(SELECT 3 pw) A ORDER BY TSize;

You will see how much wasted space disappeared when migrating the mysqldump to the new 10GB Instance. This will definitely defragment all InnoDB tables. You may need to run the above script (/root/OptimizeAllTables.sql) I gave you above to run OPTIMIZE TABLE on all the tables in the signs database once a week to keep the .ibd as small as possible. You can also run the Big Storage Engine Space Query to Monitor when it starts approaching 10GB.


get result of search in database postgresql and work with it every time

From PostgreSQL's point of view you can use Materialized Views or Unlogged Tables

For other solutions you can check different caching mechanisms


How to check parameter value existence in a procedure?

If you use a count() aggregate on a resultset that has no rows, you'll get zero, not a null.

If a SELECT doesn't find anything, an exception is raised (NO_DATA_FOUND), and you need to catch that exception in an EXCEPTIONS block.

exceptions
  when no_data_found:
    dbms_output.put_line('ooups');
end;

In your case the second select is raising the exception - since zero is not null, your first if branch will not be taken. (update doesn't raise an exception if it doesn't update anything.)

Your procedure is generally too complicate already though. You can declare variables in the procedure "header" directly, remove both selects - all the info you want can be gotten from a single update.

create or replace
procedure cust_pay(temp_code number, pay_amount number)
as
  new_balance number;
begin
  update customer
  set cbalance = cbalance - pay_amount
  where cust_code = temp_code
  returning cbalance into new_balance;

  if sql%rowcount = 0 then
    dbms_output.put_line('No such customer: ' || temp_code);
  elsif new_balance < 0 then
    dbms_output.put_line('Negative: ' || new_balance);
  else
    dbms_output.put_line('Positive: ' || new_balance);
  end if;
end;

(This assumes that cust_code is unique.)

If the update didn't do anything, sql%rowcount will be zero, which tells you whether that customer exists or not (equivalent to your first select).
The update itself is unchanged from your code except that the returning ... into avoids the need for your third select and grabs the updated value directly into a local variable.


Data impact on analyzing mysql table stats

If you are using the InnoDB engine for your tables #1 is relevant.  If you are using MyISAM, #3 is relevant.  But...

You should "never" have to run ANALYZE TABLE.  (There are exceptions, but they are so rare, that I will assume that the problem is elsewhere.)

Possible cause #1:  Your data has grown to be bigger than the cache (buffer_pool).  How big is the data?  What is the value of innodb_buffer_pool_size?  Are you using InnoDB?  How much RAM?

Possible cause #2:  Some queries have less than perfect indexes.  Set long_query_time=1; turn on the slowlog; wait a day; look in the slowlog to see what is slowest; come back for advice.


By default mysql server accept connections only from localhost, to change this behaviour you should change in mysql config the bind-address variable in /etc/mysql/my.cnf:

 bind-address=ipAddressOfYourDatabaseServer

than restart mysql server.

 sudo service mysql restart

The explain: http://www.howtogeek.com/howto/mysql/switch-mysql-to-listen-on-tcp/

After this i recommend you, that change your iptables, that your 222 port should be reachable only from your other VPS.

 iptables -A INPUT -s ! yourOtherVPSipAddress -p tcp -dport 222 -j DROP

The other thing: the mysql_ funcrions are deprecated, as the other commenter wrote. If you do not want to make a lot of work with your existing code, you should use mysqli. See the php manual, only a minimal work with sintax if you use mysqli as function library.


percona cluster 5.6 - handshake with remote endpoint failed, but endpoint is not part of config

I feel a bit stupid, but the answer to that strange behavior was simple:

The old node1 cluster node was trying to connect to the cluster again after a reboot. I forgot to uninstall the mysql installation on that node and instead only stopped the mysql server. That was well as long the system did not restart mysql.

To fix the situation I removed the old config files on node1 and also uninstalled the percona cluster packages.

Everything is peachy now.


I had the same problem and this helped me to solve it:

SQL command to clean job queue in pgAdmin 4

I just had to uninstall pgAdmin, delete all the files on C:Program Files directory and in App Data directory, as well.


Writing a trigger for every time a user logs in

I would suggest using basic auditing.  Parameter audit_trail can be set to DB, and you can audit system privileges, including CREATE SESSION.  Auditing CREATE SESSION captures (among others), database and O/S user IDs.  Views of interest are sys.aud$ and dba_audit_session.  You may not need to add a logon trigger if the information you want is already captured through auditing.  L Sallwasser


LEFT OUTER JOIN-ing the same table, grouping, summing and finding 0 or NULL

I think you want this:

SELECT
      s1.phrase,
      COUNT(DISTINCT s1.id) AS count,
      SUM(s2.results) AS sub_results
 FROM tbl_search s1
   LEFT OUTER JOIN tbl_search s2 ON s1.id = s2.original
 WHERE s1.results = 0
   AND s1.original IS NULL
 GROUP BY s1.phrase
 HAVING SUM(s2.results) = 0
     OR SUM(s2.results) IS NULL
 ORDER BY count DESC ;

The COUNT(DISTINCT s1.id) is because you may have more than one searches with same phrase (but different id).

Tested at SQLfiddle.

If you don't want to see the sub_results column, you can use the simpler:

SELECT
     s1.phrase,
     COUNT(*) AS count
FROM tbl_search s1
WHERE s1.results = 0
  AND s1.original IS NULL
  AND NOT EXISTS
      ( SELECT *
        FROM tbl_search s2
        WHERE s1.id = s2.original
          AND s2.results > 0
      )
GROUP BY s1.phrase
ORDER BY count DESC ;


If you have a single RAID10 volume then as far as SQL Server is concerned you have one volume and you can't control how things are stored, splitting things into extra files unnecessarily will likely have detrimental effects as it would on a single disk.

If you wish to try gain performance benefits from segregating data between spindles then you need to split the drives into separate RAID arrays, perhaps four in R10 for data and two in R1 for logs, or three R1s for everything, data and logs spread over the three.

Unfortunately there is no fixed wisdom here, it will vary greatly depending on your application's I/O loads. Splitting data and logs makes little difference for many load patterns (though can make a massive difference for certain write heavy loads) for instance. The only general advice that is applicable without a lot more detail about the application is "bung it all on that volume on one file/data and one/logs and trust the I/O scheduler to be fairly bright".


When two tables have the some column structure (as Location and Address do here) that is almost always an indication that they should be the same table (conceptually and logically, even if separated physically for performance reasons).

So ask yourself, "What is the design assumption that leads me to think that Address and Location are separate entities rather than the same one?"

Answer:
The relationship between Employer and Address is 1-N; but the relationship between Post and Location is 1-1. Therefore, each Address must have a FK to the Employer, and each Post must have a FK to the Address, with a transitive relationship to the Employer via the Address table.


There are a few reasons this can happen.

1) The directory does not exist:

mysql> SET GLOBAL general_log_file='/this/directory/does/not/exist/developserver.log';
ERROR 1231 (42000): Variable 'general_log_file' can't be set to the value of '/this/directory/does/not/exist/developserver.log'
mysql>

2) The directory exists, but you do not have permission to write to it & create the new file:

root@ubuntu-xenial:~# chmod 000 /var/log/newmysqllogdirectory
root@ubuntu-xenial:~# mysql -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 8
Server version: 5.7.26-0ubuntu0.16.04.1 (Ubuntu)

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

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> SET GLOBAL general_log_file='/var/log/newmysqllogdirectory/foo.log';
ERROR 1231 (42000): Variable 'general_log_file' can't be set to the value of '/var/log/newmysqllogdirectory/foo.log'
mysql>


Generally enums are for static lists, i.e. lists that never change. Departments is a bad choice because these can change at any time. Continents is good because these are highly unlikely to change, but countries would be bad because these do change. Think of things that are constants, such as seasons, months, day of week etc... You want to keep enums small. As a general rule that I apply personally, if a enum needs 10 or more entries, I create a regular list.


Best way to perform backups with filegroups and then restore those backups

You cannot restore a single filegroup to an earlier state, because that will leave the database in an inconsistent state.

You can restore a database one filegroup at a time. However, as long as a particular filegroup is not up to date with the PRIMARY filegroup, any access to a table that is stored (even in part) on that filegroup will cause the query to error out. Only after all filegroups are restored to the same level (meaning to the same "log sequence number") can the entire database be accessed.


Does using inequality () or NOT in where clause lead to SQL ignoring the index?

A <> X predicate is sargable.
SQL Server can convert it to two range seeks (on < X or > X).
Unless the index is covering for the query however you may well not see this. The tipping point for a query using a non covering index and look ups is typically very low. (at most selectivity of a single digit percent).
So unless, say, 99% of the table does have value X the <> will match too many rows for that plan to be chosen.
Even in this case likely you should be considering a filtered index only containing the 1% of rows that don't match X rather than a full index including the highly unselective value.
Unless the domain of possible values is entirely fixed and quite small you don't really have a choice between using <> X or writing it as IN(all,values,that,are,not_x) - you need to choose the one with the correct semantics for all possible values.


MySQL After Update Trigger apparently not running

My initial guess would be based on the trigger's flow. Why ?
According to the Book

Chapter 11 Page 251 Paragraph 3 says the following:

The most significant difference between BEFORE and AFTER triggers is that in an AFTER you are not able to modify the values  about to be inserted into or updated with the table in question -- the DML has executed, and it is too late to try to change what the DML is going to do.

From the this statement, this is what I understand: Since the DML is executed and the DML is still spoken of in the future imperfect tense (I am not an English teacher), the new values are really not available to be read in a SELECT since the trigger is not done.
In your case, you are doing an AFTER UPDATE on phpbb_rathermenacing.phpbb_bbdkp_memberlist. With the trigger being 99.99999999% done, you are attempting to do the following in the third clause of the IF statement:
        ((select min(m.member_rank_id)
            from phpbb_bbdkp_memberlist m
            WHERE m.phpbb_user_id = NEW.phpbb_user_id AND
                  m.member_guild_id = NEW.member_guild_id AND
                  m.member_id != NEW.member_id) >= new.member_rank_id OR
        (select count(m.member_rank_id)
           from phpbb_bbdkp_memberlist m
          WHERE m.phpbb_user_id = NEW.phpbb_user_id AND
                m.member_guild_id = NEW.member_guild_id AND
                m.member_id != NEW.member_id) = 0)

You are asking for the count and state of the table phpbb_bbdkp_memberlist that has not yet been changed. Regardless of the Storage Engine, I am very sure you will never get a response because the trigger has to finish in order for the Storage Engine to even think about posting changes to disk.
I have two old posts where I discuss why a trigger is no place for business intelligence:

Aug 11, 2011 : Policies RE database triggers in well-designed applications?
Aug 15, 2011 : Performance of a Trigger vs Stored Procedure in MySQL

SUGGESTIONS

Make that trigger a stored procedure
Remove the AFTER UPDATE trigger
Run the stored procedure after the UPDATE in your application.


Data virtualization is the provision of an abstraction layer so the data consumer does not have to know the physical location or format of the original data. You may have a PostGres DB, a MySQL DB, a SQL Server DB, a whole batch of Parquet/ORC files and the person writing the query is completely unaware of that physicality. As far as they are concerned they are hitting a connection to Presto (or whatever data virtualization solution you'd chosen use).

Technologies such as Apache Presto allow a central point against which to run SQL queries but Presto itself is configured to know where and what the source data is, the end user does not need to know.  Presto is an open-source tool that has had a lot of input from Teradata, particularly with regard to JDBC connectivity, security and LDAP authentication.  It also has commercial support from StarBurst.  Starburst has recently announced a cost based query optimizer for Presto.

AWS have faith in Presto because they have based AWS Athena on it.  The beauty of it is that data does not have to reside in a relational database.  It can be file based as well.

In terms of screening sensitive data you can choose who has access to what but it isn't a data masking or obfuscation tool.

It isn't snake oil but neither is it a silver bullet.  There is obviously a hit on the source systems and you have to understand what that hit is.  The key benefit is that you don't have to shift data all over the place and have a huge plethora of technology to support that data movement.


There are two ways to store Large Objects LOBs(like photos and images) in your property table. You can use the BYTEA data type which is limited to 1GB. The other option is OID, which stores large objects in a special LOB structure. The OID column is limited to 2GB.

OID

Here is the relevant documentation from postgres regarding OIDS.

-- Add the column
ALTER TABLE property ADD COLUMN photo OID;

-- insert the image dilbert.jpg
INSERT INTO property VALUES('dilbert', lo_import('/var/lib/pgsql/dilbert.jpg'));

-- export the image to the tmp directory
SELECT lo_export(property.photo, '/tmp/dilbert.jpg') FROM photos;

BYTEA

Here is a great example from Jack Douglas on how to use a function to import data into a bytea column. Just remember that in order to use the function, you would have to run the following command to activate plpgsql.

psql mydatabase -c 'CREATE LANGUAGE plpgsql;'
psql mydatabase < bytea_import_function.sql

(where bytea_import_function.sql contains the code from Jack's example.)

Now connect to psql and your database

psql mydatabase

-- Add the column
ALTER TABLE property ADD COLUMN photo BYTEA;

-- Insert binary data
insert into photos (name,photo) values('dilbert_bytea',bytea_import('/var/lib/pgsql/dilbert.jpg'));

-- Select data
SELECT name, photo FROM property;

OID has several advantages over BYTEA. BYTEA will preload your images into memory before sending them to the user, whereas an OID column streams the data directly to the user.


Failover scenario with principal "hard down"

I found the problem.

SQL Native Client and the .NET SQL Data Provider should seamlessly connect to a failover partner even if the primary is down; however, the default configuration of both providers is to try both TCP/IP and Named Pipes.

This configuration is fine, and works appropriately, when the servers are still both available but the primary has failed over to the backup, because the network-layer connection will succeed, but the application-level connection will be promptly refused causing the client to try the mirror. If the primary is unresponsive, however, the client will waste the entire default 20-second timeout period just waiting for the primary server to respond, first over TCP/IP, then over Named Pipes (which by itself has a default 20- to 30-second timeout period at the network level), and so in this situation it will return an error without ever having tried the backup mirror.

The solution, according to this TechNet article, is to force the client, one way or another, to connect using only TCP/IP, which will fail fast enough that the client has ample time to try both partners. You can manually configure the data providers to do this, or you can override their default settings case-by-case, by specifying the proper protocol to use in the connection string. The parameter to include that forces TCP/IP is Network=dbmssocn;. On top of that, the SQL client has a "three strikes" approach before it gives up and tries the failover mirror, so the Connection Timeout should be set long enough that it will get through this process and try the failover partner at least once. A timeout of 30 seconds should be adequate.

We tried this fix when our primary DB server was powered down again, and it does work. Now we're in the process of updating the config files of all our in-house apps (we have at least a half-dozen desktop apps and intranet sites that target this failover pair; it's a well-loved stack).


What is the most convenient indexing strategy for two foreign key constraints?

The create table script 2 is correct.

InnoDB requires indexes on foreign keys and referenced keys so that foreign key checks can be fast and not require a table scan. In the referencing table, there must be an index where the foreign key columns are listed as the first columns in the same order. Such an index is created on the referencing table automatically if it does not exist. (This is in contrast to some older versions, in which indexes had to be created explicitly or the creation of foreign key constraints would fail.) index_name, if given, is used as described previously.


sqlplus SP2-0341 (line overflow during variable substitution)

SQL*Plus is not a data loading tool. Use the proper tools.

SQL Developer can create CSV files.
Instead of SQL*Plus, use SQL*Loader to load the data.

SQL*Plus Limits


Shrink Tempdb log file when reaching a specific size

Isn't it better to just accept that the requirement for your SQL Server is a certain disk space and make sure you have the budget to allocate that amount of space? Keep shrinking seems like a futile fight in the end. Makes me think of the old blog post I wrote a decade ago about file shrinking and leaking roof: http://sqlblog.karaszi.com/leaking-roof-and-file-shrinking/. Anyhow, you could crate an SQL Server Agent alert for this based on a performance monitor counter, that kicks off an Agent job to do the shrink. But again, I wouldn't do it that way.


SQL Server Insert Into - How to identify the column that is causing truncation error

Unfortunately, you have encountered quite old a "feature". There's been a Connect ticket open since 2008, and for almost ten years this hasn't been significant enough to warrant a fix.

The standard workaround is, like you figured, a select into... followed by comparing table metadata. Another possibility is binary searching the offending column, but that's manual work too. There are some hacks for metadata comparison, but simple, elegant solution doesn't exist. Maybe some third-party tools would be of help, but I am not aware of such.


SQL Server weird issue with indexes and query planner

For some reason however, when I pick the SCRIPT INDEX -> CREATE TO -> NEW QUERY WINDOW from SSMS Object Explorer, it offers the basic index creation script where the index is NOT filtered.

"For some reason" indeed. So weird and strange. You probably have "Script for server version: SQL Server 2005" set in SSMS Tools > Options > SQL Server Object Explorer > Scripting:

2005 didn't support filtered indexes, so it leaves out the WHERE clause on purpose. Make sure that is set to 2008 or better and try again.

For the queries that are performing differently, are you sure the databases are identical? Do they use these indexes, that are filtered on one system and not on the other? Are both databases set to the same compatibility level? Have you updated statistics on the copy? Have you performed any index maintenance? Has the data skewed? Have you tried recompiling the queries with the largest behavioral changes?

(These are all formed as questions but you can interpret them as rhetorical and quasi-suggestions.)


SQL Server 2016 vs 2012 insert performance

Obviously it is very difficult the coincidence of having exactly the same versions in the same server but... I hope my results will help you.
I have two different machines configured both Windows Server 2012 R2 Standard. Unfortunately they don't have the same hardware but similar:

Machine 1 (SQL Server 2016)

Processor: Intel(R) Xeon(r) CPU X5650 @2.67GHz
Microsoft SQL Server 2016 (RTM) - 13.0.1601.5 (X64)   Apr 29 2016    23:23:58   Copyright (c) Microsoft Corporation  Standard Edition     (64-bit) on Windows Server 2012 R2 Standard 6.3  (Build 9600: ) (Hypervisor)

Machine 2 (SQL Server 2012)

Processor: Intel(R) Xeon(R) CPU E5-2667 0 @ 2.9 GHz
Microsoft SQL Server 2012 - 11.0.5058.0 (X64)
May 14 2014 18:34:29
Copyright (c) Microsoft Corporation
Standard Edition (64-bit) on Windows NT 6.3  (Build 9600: ) (Hypervisor)

And I run 5 times in both machines the same script you have provided and obtained the following average:

2012: 9961
2016: 8971

What is almost the opposite results that you obtain. Anyway, as you have seen, my 2012 machine has better processor but the hard disk that normally is what shows the difference is the same. So, even if 2012 has better resources is a bit slower for my case.

(Sorry, please, check again, my first version had some important erratums)


How to open SQL Server 2008 R2 Configuration manager in Windows 8.1

Open run command window using "Windows+R" type mmc.exe after console opened from file menu choose SQLSERVERMANAGER10.

Or you can type "SQLSERVERMANAGER10.msc" in run command window.


Is it possible to resume an interupted mysql import from a backup file?

I haven't worked with PHPAdmin for a very long time, but it should be able to generate insert ... ignore instead of just insert. If my memory serves me well, there is a checkbox 'using insert ignore' on export/backup page.


How to make Swingbench 2.6 and Oracle 18c work together?

Partially it could be fixed by adding

alter session set "_ORACLE_SCRIPT"=true;

command at the top of these five files:

swingbench/sql/CreUser.sql
swingbench/sql/json_createuser.sql
swingbench/sql/shdg_createuser.sql
swingbench/sql/soedgcreateuser.sql
swingbench/sql/tpcds_createuser.sql

During configuration sys as sysdba Administrator Username is filled by default. And during oewizard script execution Java throw a lot of

  ORA-01950: no privileges on tablespace 'USERS'

exceptions. So I've resolved it by:

$ sqlplus / as sysdba
SQL> grant unlimited tablespace to sys;
SQL> alter user sys quota unlimited on users;


Piecemeal restore only one filegroup without primary filegroup restore

You can't restore a file or filegroup without restoring the PRIMARY filegroup. There are a number of reasons for this given your scenario:

Under SIMPLE mode, a Partial Backup always contains the PRIMARY filegroup and all read/write filegroups. You would need to switch to FULL recovery to utilise file/filegroup only backups that can exclude the PRIMARY.
If no PRIMARY filegroup existed, the system tables and metadata for your database would not exist, so no database users, no schema information, nothing that SQL Server needs to know about your data, files and filegroups would be available.
Under SIMPLE, no log backups are taken, so you cannot replay the transactions against the non-PRIMARY read/write filegroups meaning they must be restored from a backup consistent with PRIMARY.

Even under FULL recovery, the primary filegroup must be restored to allow you to restore individual files/filegroups because all the core metadata about your database is there.

The idea of Partial Backups and Piecemeal restores is to allow you to get back online in a disaster quicker by restoring the essential filegroups needed for the database to be operational, and you can restore additional filegroups later.

I would suggest you look at making some changes to your database:

Move all user objects and data out of the PRIMARY filegroup so that restoring it is less time consuming.
Switch to FULL recovery mode and implement separate filegroup backups and frequent log backups.
Mark any filegroups that should not be updatable as READ ONLY.

These change will allow you to simplify your restore process. Assuming you'd made the above changes and wanted to restore the file you've mentioned, the process would be:

Take a tail-log backup
Restore the primary filegroup from backup
Restore all log files with no recovery (except the last restore of the tail log backup, which is done with recovery) (DB is online at this point)
Run "RECOVER" on all the undamaged filegroups (Data in these FGs is now accessible)
Restore the damaged filegroup from backup (DB is now fully recovered)

This would be quicker than partial backups under SIMPLE recovery because the undamaged filegroups do not have to get restored from backup, instead the data files on disk are simply recovered by SQL Server and transactions replayed from the log onto those files as appropriate. If the PRIMARY filegroup is small, and you're taking frequent log backups, the whole process should be quicker than restoring all the read/write filegroups as with partial backups under SIMPLE.


Extract Year only from 'Select @@version' output

Found a related answer from this post: https://stackoverflow.com/questions/13331621/sql-query-to-get-sql-year-version. Below code will provide year only. Credit goes to that post.

Declare @SQLVersion varchar(100)
Declare @TargetServer Varchar (4)
--Extract SQL Server Year
set @SQLVersion = (SELECT
   CASE SUBSTRING(CONVERT(VARCHAR(50), SERVERPROPERTY('productversion')), 1, 2)
          WHEN '8.' THEN '2000'
          WHEN '9.' THEN '2005'
          WHEN '10' THEN '2008'
          WHEN '11' THEN '2012'
          WHEN '12' THEN '2014'
          WHEN '13' THEN '2016'
          WHEN '14' THEN '2017'
          WHEN '15' THEN '2019'
    END)
print @SQLVersion


Is it *always* bad to have long-running transactions, or is this case OK?

This is nothing fundamentally wrong with this, is it? Even though the transaction takes a long time before it finally commits,

The risk is locks.  Only one transaction can update a given row at a time.  There can be various reasons for this.  A few easy to understand cases are:

Update statements which depend on the current state, such as update blah set count = count + 1.  What would happen if two processes each read a row with count = 4, they each add 1, which produces 5, and they both write 5?  One of them had their results corrupted.
Indexes - select first_name, last_name from users where last_name = 'Jones'; - imagine there is an index on last_name.  Great!  We can optimize the query by scanning the index, then for each row, go out to the table and retrieve the two columns.  Imagine a transaction updated the table, but had not yet updated the index, then we come along and suddenly select first_name, last_name from users where last_name = 'Jones';  returns Jane Doe.  Indexes are expected to be consistent with the table.
Cascading deletes - if table B defines a foreign key constraint referring to table A with on delete cascade, what happens if you query table B before the cascade delete has happened?

The solution to all of these problems, and more, is locking.  Postgres will lock the row for any updates to it so that any other updates to that row have to wait for the lock to be released.  When running in a transaction, locks continue to be held until the transaction is committed or rolled back.

It seems to me that it's guaranteed that I will be able to SELECT from the table while this transaction block is running, but is it also possible to INSERT and DELETE and UPDATE?

In PostgreSQL, I believe that is correct.

My brain cannot comprehend how exactly the database would handle this. Is this what a deadlock is?

A deadlock is when multiple transactions, running concurrently, each require the same locks, but request them in a different order.
Process A wants to lock rows X and Y.  Process B wants to lock rows Y and X.  They each go ahead.  Process A locks row X and process B locks row Y.  Now what do they do?  They enter a state where they wait for each other.  Because they each are waiting on each other, neither makes progress, so they wait forever.  That is what creates the "dead" in deadlock.  The processes will appear to be dead because they are sleeping forever.

Or will they continue INSERTing and UPDATEing and DELETING from a "ghost table" and all changes are lost when the long-running transaction finally COMMITs? That doesn't sound right. I assume that it would refuse any "changing" operation to the table while that transaction is running, correct?

Selects should work fine because what happens with insert, update, and delete, is Postgres creates new versions of the rows and keeps track of which transactions see which versions.  If you're not in a transaction and simply select a row, you should see the last committed version of the row at the time you query it.  Inserts, updates, and deletes, however, will block, waiting in line for their turn.
Nothing should error.  To a reasonable degree, blocking is normal.  Blocking is the database protecting your data.  However, blocking is a scalability problem because it means only 1 transaction at a time can change something.  Like all performance concerns, it's a question of your workload and what tradeoffs you can tolerate.  Blocking is a common problem encountered when troubleshooting database performance.
So what's the problem with long-running transactions?
Any rows updated in a transaction may cause other transactions to wait.  Those transactions may also be holding locks, and now they are stalled, which may cause them to block others.  This pattern leads to blocking chains and in an OLTP workload, can degrade performance substantially.
The premise is simple: Hold as few locks as you need for as short of a time as possible.
What to do in your case?
It depends.  Is the truncate statement being run in production or only in test environments?  Are there foreign keys involved?
It's odd that the inserts would take so long for you.  Can you batch them into a single statement or keep the rows in a second table and after truncate, do a insert into test select ... from second_table?
It really depends on what problem it's causing you, if any.  If the table isn't used by most of your queries, then maybe your transaction is just fine.  If you're getting blocking issues (issue defined as something impacting users) in production, then you should try to optimize the inserts.


SSMS IntelliSense doesn't work on particular database

It might be latency, or it might be something else. Run a SQL Server Profiler trace on the remote DB, starting the trace just prior to connecting via SSMS remotely. IntelliSense works by running queries to get the meta-data, so you will be able to see if it is even attempting to work or not, and if so, potentially if it is running into any problems.

You should filter the ApplicationName column on LIKE %IntelliSense%, though the full name of the program is Microsoft SQL Server Management Studio - Transact-SQL IntelliSense. Events to select: SQL:BatchStarting, SQL:BatchCompleted, SQL:StmtStarting, SQL:StmtCompleted, SP:Starting, SP:Completed, SP:StmtStarting, SP:StmtCompleted, RPC:Starting, and RPC:Completed.

Try it first on a DB that works so you know what the normal / expected behavior should look like. Hitting Control + Shift + R to force a reload of the local cache should produce a decent amount of activity captured by your trace.


Logging occurs in all recovery models. The log is retained in bulk logged and full recovery models until the log is backed up. Bulk logged only minimally logs certain actions e.g. an index rebuild, meaning you can't do point in time restores when using bulk logged. As you are using simple recovery model, changing to bulk logged will be of no benefit to reducing log size. It would make your setup more complex actually as you would have to start taking regular log backups.

If you have a large transaction in simple recovery model, this transaction will still be logged and cause your log file to grow. This is what allows you to rollback the transaction in any recovery model. If you left that transaction open (don't commit or rollback) your log would grow and grow. Once committed (or rolled back) that logging information no longer has to be retained in simple recovery model and SQL server will reuse the log in a cyclic nature.

Your solution would be to minimise the size of your ETL insert transactions. You could perform smaller inserts in batches and commit those (if that is appropriate for data consistency). You could also look at transaction handling in your ETL tool. SSIS for example allows all inserts in a package to be part of the same transaction, you could change this behaviour so each transformation has it's own transaction.


Spatial Data Cluster in sql-server ? ( like ST_ClusterWithin in Postgis )

Having looked a the description of ST_ClusterWithin, I believe the geography::Point and STDistance methods are what you're looking for. (See Serge's answer to https://stackoverflow.com/questions/42004474/merge-stored-procedure-and-ctefunction-together-to-get-results-from-reference for an example.)


Using ODBC Connection to Access Firebird Database

You should be able to create a Linked Server to almost any ODBC data source.

I would suggest trying the Microsoft OLE DB Provider for ODBC Drivers.

While you need to enter something in the Product Name field, I believe you can enter anything, such as "Firebird".

For Data Source, enter the name of the ODBC System DSN you've previously created.

You'll need to confirm the other options for "Security" and "Server Options", and test to see if it works.


When is the time that Couchbase write documents to disk?

I will answer your question as I lay out the higher level flow objects take through Couchbase on their way to disk.

Object is written to the managed cache by the application
Simultaneously a pointer to that object is placed into a queue (Disk Write Queue) for the object to be persisted to the DB storage engine, currently called Couchstore. A pointer is also put into a replication queue to replicate that object to a replica, but that is another discussion.
Depending on server resources, namely disk write speed, the objects will then be moved to Couchstore. The disk write queue can grow quite large if the amount of incoming data is large and the disk are slow. This is an area that needs to be looked at as part of sizing a cluster. It is not just all about raw read/write speeds, but also how the queue is filling and draining. You want the disk fill and drain rates to be as close as possible and the disk write queue to be as close to 0 as you can get it. This is especially critical if you are going to be using PERSIST_TO style writes.
Once it gets to Couchstore, it is persisted to disk. There is some batching in there, 1000 per vBucket if I recall correctly. This is to improve performance. Also, while there is an OS buffer (I was incorrect in using that term before), Couchbase does not depend on it, but there is an fsync after each batch.

One thing to remember, pretty much all of this can be monitored using the Admin Rest API or looked at in the WebUI. These are all great candidates for being monitored on an ongoing basis if you care about performance.


Short answer is 'No', you can't reliably prove this without lower level details.  Auditing and Triggers will work from the database layer.  If you have access to the GoldenGate Trail files (files with all the extracted changes), you can see exactly what GoldenGate will replicate by using logdump (another binary that comes with OGG).

It sounds like you have bi-directional replication (active-active back).  If so, your Extract process should be configured to ignore your OGG user, since you don't want to replicate changes back to the source.

Another thing to considers whether or not triggers are enabled on your unused/secondary site.  I have seen weird things happen when triggers are not disabled on the receiving side (duplicated history records, unique key violations, etc).

Also, if your secondary site is unused, maybe DataGuard is a better solution.  It all depends on your software and infrastructure architecture.  It won't be a bi-directional solution, but arguably a simpler solution than OGG.


Category entity relationship / subtype relationships design

I think this looks pretty reasonable for a system in which a user can add comments or attachments associated with a contract, a specific line of the contract, or a proposed modification to a line of a contract.

Since you asked for potential shortcomings or pitfalls, here are a few you might consider:

Without knowing much about your system, it seems strange that a variation must apply to only one contract line.  Is there ever a case where a variation might apply to multiple lines?  And then a user might want to comment on the multi-line variation in its entirety?  If so, this case doesn't appear to be handled  right now.
It's surprising that there aren't any columns other than EntityType that are common across all entities.  Perhaps there should be an EntityName column rather than separate columns ContractName, ContractLineName, and VariationName?  This would make the example query you describe in your question far simpler.
The data model  does not strictly enforce that the Variation contains logically consistent data.  It stores both a ContractId and a ContractLineId.  The foreign keys for these columns enforce that each is valid independently, but allow for a ContractLineId to be inserted that comes from the incorrect ContractId (that is, different than the ContractId for that ContractLineId in the ContractLine table).  One option to address this is to remove the ContractId column from the Variation table.


Why is my dev server faster than a much more powerful RDS?

Looks like my initial theory was correct.

A standard RDS is capped at 3 IOPS per GB of storage, hence why my writes were hovering around 332 on a 100 GB instance. Not sure how they went over 300; maybe Amazon is not strict about it or maybe MySQL Workbench just had latency and calculated it slightly off.

Anyways we launched a new RDS with provisioned IOPS set to 1,000. Now the queries are running 3x faster than they were before.

It does seem strange to me that the limit is based on the storage size. Those two aren't necessarily directly related in my opinion.


Perhaps you are looking for this:

select issues.id, array_agg(journal.notes) from issues
left outer join journal on (issues.id = journal.issue_id and journal.notes != '')
group by issues.id

Please check this http://sqlfiddle.com/#!1/24db9/2


The syntax you are looking for is the case statement.  You can use IF .. EXISTS for controlling the flow in your code but for something like this the CASE WHEN statement is what you want.

SELECT CASE WHEN col3 IS NULL THEN col1 + ' ' + col2
    WHEN col3 LIKE '-%' THEN col1 + ' ' + col2 + col3
    WHEN col3 NOT LIKE '-%' THEN col1 + ' ' + col2 + ' ' + col3
    END AS ConcatCol
FROM TableName;

and simplified to:

SELECT col1 + ' ' + col2
       + CASE WHEN col3 IS NULL THEN ''
            WHEN col3 LIKE '-%' THEN col3
            ELSE ' ' + col3
         END AS ConcatCol
FROM TableName;

Assuming I understood what you are trying to do of course.

EDIT: Added alias for column


why mysql binlog creating new log file daily

4. I have no CRON which do some task auto magically

I'll speculate that you actually do.  Linux systems often use the logrotate utility to manage rotation of log files from numerous disparate subsystems.

Distro builders love to coerce software installed from packages to Do Things The One True Way™ so it should not surprise you if they have "extended" MySQL server beyond the official documentation, to rotate its own binlog.

You may find syslog entries mentioning "logrotate" around the time that the binlog changes, and further explanation is likely to be found in a file somewhere around here:

/etc/logrotate.d/mysql-server


Force MariaDB Galera to only create InnoDB tables

This is a good question. People tend to want that when using NDB cluster and Galera.

The right answer is that you should report the plugin creators to create the tables with no engine definition, so you can set it with "default_engine" option. While on other cases you could disable the engine and force the engine substitution with the default one, that is not a good practice, it is discouraged on the latest versions and impossible with MyISAM.

The truth is that once the developer writes ENGINE=X, it is forcing the creation of the engine, as written. The only way to accomplish that is by rewriting the query on-the-fly. That is available in MySQL 5.7 (not yet GA), with several plugins. The only way I can think of doing it now is with a SQL proxy like ProxySQL or MySQL Proxy. Please note that some of those are infamous for bad performance, so you may want to enable it only on plugin activation/disabling.

There is obviously another option: download the MariaDB Cluster sources, disable MyISAM on the CREATE TABLE Syntax, and compile!


SQL0204N "DB2ADMIN.TA" is an undefined name. SQLSTATE=42704

DB2 (as other standard-compliant SQL databases) converts unquoted identifiers to uppercase, so when you say select * from db2admin.ta DB2 will look for the table named 'TA' in the schema 'DB2ADMIN'. That's exactly what the error message tells you.

According to the SYSCAT.TABLES query results, you have created your table and schema with lowercase names. To access them you will need to quote both identifiers (separately):

select * from "db2admin"."ta"

Note the double quotes.


Since we are talking about the clustered index, just because you defined the CI key column as ID, you still have the DeletedDate data in the leaf data pages of the index.  That's the nature of the clustered index:  It is the table data.

Because you are typically having queries that look like:

select *
from YourTable
where DeletedDate is null;

You will likely benefit from a filtered index.

create nonclustered index IX_YourFilteredNci
on YourTable(<Key Columns Here>)
where DeletedDate is not null;
go

I didn't explicitly put the key columns here (and nonkey columns through the use of the INCLUDE clause) because you didn't publish the DDL of your table.

As in my comment above to your question, the choice of key columns (not just columns, but also the order of the columns) will largely depend on your workload and the typical queries that would be using this index.

If you are looking to cover your query(ies), then you would need to ensure that the index satisfies all of the data required of the query(ies).  Not to mention, if you have other WHERE clauses (besides your NULL check on DeletedDate) or joins to consider, then the order of your key columns can be the deciding factor between a scan or a seek.  And even though it is filtered, and depending on how much data you have in the index, the penalty could be considerable.


Is disabling triggers to disable foreign key constraints a good idea?

You can create the foreign key constraints with the option NOT VALID. Then they are declared, but are not enforced:

create table parent (id integer primary key);
create table child (id integer primary key, pid integer);

insert into parent values (1),(2),(3);
insert into child values (1,100),(2,200),(3,300); --<< invalid pid values!

alter table child
     add constraint fk_child_parent
     foreign key (pid) references p (id) NOT VALID; --<< succeeds


Replicate RDS-MySQL to a non-amazon host?

Looks like Amazon has chosen to forbid this for now:

https://forums.aws.amazon.com/thread.jspa?threadID=46377&tstart=0


Get position of mysqlbinlog error. I have the error's line number

You can follow this steps/workaround :

Take converted binary logs in separate file.

mysqlbinlog --base64-output=DECODE-ROWS -v hostname_binlog.00XXX > Binary_log.sql

You directly Binary_log.sql file on MySQL server or you can use mysqlbinlog command ( if you have sufficient space)

mysqlbinlog --base64-output=DECODE-ROWS -v hostname_binlog.00XXX | mysql -v -u root

Here using -v ( verbose ), logs will printed on terminal & you can get details of each statement.


How to query which users are using which certificates in SQL Server?

That information is not exactly available and may be a security risk if it was, because essentially you'd need to know the public keys used with that certificate. This StackOverflow answer may be relevant and essentially agrees with my previous statement.
Taking a step back, these are the things you can derive though:

If your certificate was generated for a specific database, then all principals within that database are applicable to that certificate. You should be able to see which principals are mapped to the given database certificate by querying sys.database_principals WHERE type = 'C' as discussed here.

You should be able to get the principal of who owns a given certificate by using the principal_id to join to the sys.server_principals view to get the principal name.


Options for dumping Oracle queries to text files with no database server filesystem access

You could email them with UTL_MAIL or POST them to another server with UTL_HTTP

Alternatively, save the complete reports as a clob in a table and query from any client - this is a little more work as you'll have to rewrite your reporting procedures, but is still easier than starting from scratch on the client.


Is checking for optional characters required in mysql REGEXP?

From the quite clear examples here, I performed a few tests (below).
The tests give 1 for true (i.e. a match) and 0 for false (no match).

Note the use of ^ and $ for the start and end of the REGEXP.

To answer your specific question, your first REGEXP doesn't care about any characters around 'ket' or 'Mark' - it just checks to see if either exists anywhere in the string and returns 1 if there is any match. '^Mark' will search for 'Mark' and 'Market' and any other string beginning with 'Mark'. The same thing happens for the $ for the end of strings.

Check out the examples and try and follow them - Regular expressions are a great string to your SQL bow and it is very worthwhile learning them.

mysql> SELECT 'Mark' REGEXP '^Market|.*katerina.*';
+--------------------------------------+
| 'Mark' REGEXP '^Market|.*katerina.*' |
+--------------------------------------+
|                                    0 |
+--------------------------------------+
1 row in set (0.01 sec)

mysql> SELECT 'Market' REGEXP '^Market|.*katerina.*';
+----------------------------------------+
| 'Market' REGEXP '^Market|.*katerina.*' |
+----------------------------------------+
|                                      1 |
+----------------------------------------+
1 row in set (0.00 sec)

mysql>

mysql> SELECT 'Market' REGEXP 'ket|Mark';
+----------------------------+
| 'Market' REGEXP 'ket|Mark' |
+----------------------------+
|                          1 |
+----------------------------+
1 row in set (0.00 sec)

mysql> SELECT 'Market' REGEXP 'ket$|Bark';
+-----------------------------+
| 'Market' REGEXP 'ket$|Bark' |
+-----------------------------+
|                           1 |
+-----------------------------+
1 row in set (0.00 sec)

mysql> SELECT 'Market' REGEXP 'ket|Bark';
+----------------------------+
| 'Market' REGEXP 'ket|Bark' |
+----------------------------+
|                          1 |
+----------------------------+
1 row in set (0.00 sec)

mysql> SELECT 'Market' REGEXP '^ket|Bark';
+-----------------------------+
| 'Market' REGEXP '^ket|Bark' |
+-----------------------------+
|                           0 |
+-----------------------------+
1 row in set (0.00 sec)

mysql> SELECT 'Market' REGEXP '^ket$|Bark';
+------------------------------+
| 'Market' REGEXP '^ket$|Bark' |
+------------------------------+
|                            0 |
+------------------------------+
1 row in set (0.00 sec)

mysql>


You are doing a double hop impersonation that requires delegation.


Database storage limit for open source PostgresSQL

On their webpage About they clarify that the limit is a lot huge than 4GB.

Limit        Value

Maximum      Database Size  Unlimited

Maximum      Table Size 32 TB

Maximum      Row Size   1.6 TB

Maximum      Field Size 1 GB

Maximum      Rows per Table Unlimited

Maximum      Columns per Table  250 - 1600 depending on column types

Maximum      Indexes per Table  Unlimited


how to replace dbms_reputil.from_remote in oracle 12.2.0.1 since All objects removed except REPLICATION_IS_ON, _OFF and _ON

Desupport of Advanced Replication

  Starting in Oracle Database 12c release 2 (12.2), the Advanced
  Replication feature of Oracle Database is desupported.

  The Oracle Database Advanced Replication feature is desupported in its
  entirety. The desupport of this feature includes all functionality
  associated with this feature: multimaster replication, updateable
  materialized views, hierarchical materialized views, and deployment
  templates. Read-only materialized views are still supported with basic
  replication.

  Oracle recommends that you replace your use of Advanced Replication
  with Oracle GoldenGate.


What's the differences between the two version of oracle client download file, "client" and "client home"?

LINUX.X64_193000_client.zip is a fully featured installer where you can choose what to install and where, then the installer copies the files to the destination and registers the client.

LINUX.X64_193000_client_home.zip is an "image" of and "Administrator" type installation, that you unzip to the destination folder then register it.

Simplified Image-Based Oracle Database Client Installation

  Starting with Oracle Database 19c, the Oracle Database client software
  is available as an image file for download and installation. You must
  extract the image software into a directory where you want your Oracle
  home to be located, and then run the runInstaller script to start the
  Oracle Database client installation. Oracle Database client
  installation binaries continue to be available in the traditional
  format as non-image zip files.

  As with Oracle Database and Oracle Grid Infrastructure image file
  installations, Oracle Database client image installations simplify
  Oracle Database client installations and ensure best practice
  deployments.

About Image-Based Oracle Database Client Installation

  Starting with Oracle Database 19c, installation and configuration of
  Oracle Database Client software is simplified with image-based
  installation.

  To install Oracle Database Client, create the new Oracle home, extract
  the image file into the newly-created Oracle home, and run the setup
  wizard to register the Oracle Database product.

  You must extract the image software (client_home.zip) into the
  directory where you want your Oracle Database Client home to be
  located, and then run the Setup Wizard to start the Oracle Database
  Client installation and configuration. Oracle recommends that the
  Oracle home directory path you create is in compliance with the Oracle
  Optimal Flexible Architecture recommendations.

  Using image-based installation, you can install Oracle Database Client
  32-bit and 64-bit configurations of the Administrator installation
  type.

  As with Oracle Database and Oracle Grid Infrastructure image file
  installations, Oracle Database Client image installations simplify
  Oracle Database Client installations and ensure best practice
  deployments. Oracle Database Client installation binaries continue to
  be available in the traditional format as non-image zip files.


The issue was resolved by removing the reportserverurl entry in the config file


I don't know why you can't see the InnoDB plugin. I've never seen that happen.

You can also use this method to check what version of InnoDB you have loaded:

mysql> SHOW VARIABLES LIKE 'innodb_version';

If it returns a number, then you're using the InnoDB plugin. If it returns nothing, then you're using the builtin InnoDB (or no InnoDB at all).

I suggest that you upgrade to MySQL 5.5. You don't have to fuss with loading the InnoDB plugin, it's loaded and enabled by default, and the builtin InnoDB is not present. Also, MySQL 5.1 is officially end-of-life, and it's no longer a supported product as of 2013-12-31.

  Then, is there any other way through which I can upgrade InnoDB plugin related Dll files?

Virtually everyone does this by upgrading the version of MySQL, for example 5.1.58 (2011-07-05) to 5.1.73 (2013-12-03). As you can imagine, there were a lot of important fixes, including security fixes, in the 29 months between these two versions.

In theory, you could download a Zip file of 5.1.73, unpack it and retrieve the InnoDB plugin DLL, and manually move it into the plugin directory of your installed 5.1.58 instance. You should shut down the MySQL Service before doing this.

The caveat is that you would be enabling a version of the plugin (5.1.73) that was never tested with the older version of MySQL. They aren't supposed to break backward compatibility between point-releases, but internal interfaces aren't as strict about that.

Also, upgrading only the InnoDB plugin would not include any fixes implemented in the rest of the MySQL code outside the storage engine.

PS: For what it's worth, the InnoDB plugin existed in MySQL before the Oracle acquisition of MySQL.

2005-10-07: Oracle announces acquisition of Innobase Oy.
2008-04-15: First alpha of the InnoDB plugin.
2009-09-01: First beta release of the InnoDB plugin in MySQL 5.1.38.
2010-01-27: Oracle completes the acquisition of Sun Microsystems.
2010-04-06: First GA release of the InnoDB plugin in MySQL 5.1.46.


Choose record based on varying condition

SELECT Key, MAX(Status) FROM Table
GROUP BY Key

Nice and simple - works because Change is after Book when ordered by the MAX aggregate function.


Should I be wary of ON DUPLICATE KEY UPDATE?

First of all:

Yes, it is fine and common practice. For simple upserts, I would say it is best practice.

There are some rather rare cases to worry about and things that may bite you - especially considering that you come from the SQL Server world:

When you use it against a table with two or more UNIQUE constraints. In those cases, you can't easily set to have different behaviours for different constraint violations (while it is possible with the MERGE statement).
An INSERT that violates 1 of the 2 unique constraints will go to the UPDATE clause. If you want different behaviour depending on which constraint is violated, it will be hard to implement (possibly with some complex CASE expressions but may not be feasible in all scenarios).
A big difference with SQL Server (and all other major SQL implementations) is that UNIQUE constraints are checked after every row INSERT or UPDATE and not as the SQL standard says at the end of statement (and don't even think about deferred constraint checks).

The problem with that is for an INSERT ... SELECT ... ON DUPLICATE KEY UPDATE ...; statement, the order of how rows are processed matters. If the SELECT creates 2 rows to be inserted that conflict on a UNIQUE constraint, the first one to be processed will be inserted and the second will fall to the UPDATE clause (and the order of processing will of course depend on the execution plan). This means non-deterministic result on the state of the table after the statement.
So one has to be really careful when writing such statements (and any UPDATE statement really).
Related to the previous point, INSERT ... ON DUPLICATE KEY UPDATE statements are marked unsafe for statement based replication, so if you use them you are restricted to row or mixed based replication.


Can't export table structure with expdp, parameter full=X conflicts with tablespaces=X

What you posted works fine.
[oracle@o81 ~]$ expdp \'/ as sysdba\' tablespaces=users content=metadata_only

Export: Release 19.0.0.0.0 - Production on Sat Nov 14 12:48:17 2020
Version 19.9.0.0.0

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

Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Starting "SYS"."SYS_EXPORT_TABLESPACE_01":  "/******** AS SYSDBA" tablespaces=users content=metadata_only
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type TABLE_EXPORT/TABLE/TABLE
Master table "SYS"."SYS_EXPORT_TABLESPACE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYS.SYS_EXPORT_TABLESPACE_01 is:
  /u01/app/oracle/homes/OraDB19Home1/rdbms/log/expdat.dmp
Job "SYS"."SYS_EXPORT_TABLESPACE_01" successfully completed at Sat Nov 14 12:48:43 2020 elapsed 0 00:00:26

[oracle@o81 ~]$

You get this error when the tablespace specirfied does not exist:
[oracle@o81 ~]$ expdp \'/ as sysdba\' tablespaces=users123456 content=metadata_only

Export: Release 19.0.0.0.0 - Production on Sat Nov 14 12:50:16 2020
Version 19.9.0.0.0

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

Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Starting "SYS"."SYS_EXPORT_TABLESPACE_01":  "/******** AS SYSDBA" tablespaces=users123456 content=metadata_only
ORA-31655: no data or metadata objects selected for job
Job "SYS"."SYS_EXPORT_TABLESPACE_01" completed with 1 error(s) at Sat Nov 14 12:50:19 2020 elapsed 0 00:00:03

[oracle@o81 ~]$


Select fixed number of records/rows from growing table from SQL Server?

There's probably a more elegant way to achieve this but it can be done by using the NTILE function inside a CTE.

WITH cte AS (

  SELECT NTILE(5) OVER (ORDER BY id asc) AS dist,
  memory
  FROM TABLE)

  SELECT TOP 1 id, memory FROM cte WHERE dist = 1

  UNION

   SELECT TOP 1 id, memory FROM cte WHERE dist = 2

  UNION

   SELECT TOP 1 id, memory FROM cte WHERE dist = 3

  UNION

   SELECT TOP 1 id, memory FROM cte WHERE dist = 4

  UNION

   SELECT TOP 1 id, memory FROM cte WHERE dist = 5;


Change only one cell value on select statement

The following query matches your description from UPDATE 1 (with inline comments showing how it matches) and also yields the correct result for your SQL Fiddle (there is no longer an unwanted row for for the 2015-10-21 sale).

-- (1) Check the sales table if it has any combo with duration=0 for the selected user
DECLARE @finalComboId INT = (
    SELECT TOP 1 s.comboid
    FROM sales s
    JOIN combo c
        ON s.comboid = c.id
        AND c.duration = 0
    WHERE s.username = 'user1'
)

IF (@finalComboId IS NOT NULL)
BEGIN
    SELECT s.username, s.saledate, r.destinationcombo as comboid
    FROM (
        -- (2) If there is a result, then take the most recent combo
        -- (with duration <> 0 and with most recent saledate)
        SELECT TOP 1 c.id AS recent_comboid
        FROM combo c
        JOIN sales sc
            ON sc.comboid = c.id
            AND sc.username = 'user1'
        WHERE c.duration <> 0
        ORDER BY sc.saledate DESC
    ) rc
    -- Find all sales rows from that most recent combo
    -- (The SQL Fiddle has just one, but more generally I assume there could be multiple)
    JOIN sales s
        ON s.comboid = rc.recent_comboid
    -- (3) After finding that combo, than calculate from relation the destinationcombo...
        -- NOTE: I intrepret this as finding all sales rows from that most recent combo
    JOIN related r
        ON r.finalCombo = 3 --...where FinalCombo is the combo found on step 1)
        AND r.sourceCombo = s.comboid --...and sourcecombo is the combo found on step 2).
END
ELSE
    SELECT * FROM sales WHERE username='user1'
GO

However, there are a few things that remain confusing and leave me wondering if I am interpreting the problem correctly:

The sourcecombo field is not used in your example query, but the problem statement indicates that it is important when matching to relation.
Why does the ELSE clause select all rows from sales when the the main body is selecting only the most recent combo?
What if there is more than one combo with duration = 0? There is no guidance in the problem statement about which row should be picked.


Index for date column along with multiple other columns in where clause

This is a "range:  joining_date >= '2017-01-01' AND joining_date <= '2017-05-31'

This is sort of a range:  department_no IN (5,4,7) .  There are cases where it acts efficiently like =, there are cases where it should be considered a "range".

This is an =:  department_no IN (5) .  That is, the Optimizer turns it into department_no = 5, which has much more optimization potential.

http://mysql.rjweb.org/doc.php/index_cookbook_mysql says to put range columns last, not first in an index.

In your queries, it is hard to predict what is optimal.

If you are likely to sometimes have single-item INs, then you need

INDEX(department_no, joining_date) -- in THIS order

Also, newer versions of MySQL may work well with that index for the multi-item IN by leapfrogging across the table.  So add that index.

As for

INDEX(joining_date, ...)

When using a 'range', that index will not get past joining_date to whatever other column(s) follow it.  So you may as say just

INDEX(joining_date)

As for the GROUP BY...  Neither of your queries has any hope of using an index for the GROUP BY.  So there will be some form of extra effort after the WHERE -- a tmp table, a sort, something.

Hiding a column in a function month(joining_date) -- the Optimizer can't use the joining_date column an any index for this.
The WHERE left the results in some messed up order (assuming a multi-item IN)

Unrelated... I like this pattern:

AND joining_date >= '2017-01-01'
AND joining_date  < '2017-01-01' + INTERVAL 1 MONTH

It avoids computing the end, leap year, DATE vs DATETIME vs DATETIME(6) issues, etc.

Bottom line: Have this one index:

INDEX(branch_name, department_no, joining_date)

Then get the EXPLAIN SELECT ... to see if it did the leapfrogging.  If that fails, let's rethink.


Efficient database design for multiple price based on quantity and attribute

First of all you must define your entities and these form your tables.  From what you provide above I can see two tables based on two entities:  Product and price.

Then you will add your attributes to each entity such as colour and colour print.

Then you should decide on the nature of your relationships - product has a 1:M relationship with price.

To avoid anomalies in your database you will have to normalise your tables - see https://beginnersbook.com/2015/05/normalization-in-dbms/

Hope this helps :-)


T-SQL script to perform a compressed backup with maximum compression level

SQL Server's built-in backup compression only has one level of compression (as Mark and Adrian have already said). The most likely reason that you are getting a smaller final file by compressing the uncompressed backup is that it will contain large amounts of plain text. WinRAR and other traditional compression utilities are very good at compressing plain text.

An alternative means of getting smaller backup files is to use a third-party backup application. There are several of these on the market, including Red Gate's SQL Backup Pro and Idera's SQL Safe Backup. These contain more efficient compression algorithms that should give you smaller backups without post processing. I don't use either of these personally so can't recommend a specific one.


What is "Index Fragmentation Percent" a percentage of?

It's going to be logical fragmentation for indexes, and extent fragmentation for heaps.  The BOL reference on sys.dm_db_index_physical_stats actually gives pretty good information on the topic:

  Logical Fragmentation

  This is the percentage of out-of-order pages in the leaf pages of an
  index. An out-of-order page is a page for which the next physical page
  allocated to the index is not the page pointed to by the next-page
  pointer in the current leaf page.

  Extent Fragmentation

  This is the percentage of out-of-order extents in the leaf pages of a
  heap. An out-of-order extent is one for which the extent that contains
  the current page for a heap is not physically the next extent after
  the extent that contains the previous page.

  The value for avg_fragmentation_in_percent should be as close to zero
  as possible for maximum performance. However, values from 0 percent
  through 10 percent may be acceptable. All methods of reducing
  fragmentation, such as rebuilding, reorganizing, or re-creating, can
  be used to reduce these values. For more information about how to
  analyze the degree of fragmentation in an index, see Reorganize and
  Rebuild Indexes.

Think about an index, as highlighted above it's the "percentage of out-of-order pages in the leaf pages".  An index page is going to be a doubly-linked list.  In other words, each page points to the next page and the previous page in it's respective index level.  Here's a picture on what a doubly linked list looks like (for illustrative purposes):

Note: the above image was just randomly pulled from the internet, it's a simple representation.  But it does lack A's next pointer, and F's previous pointer.  I didn't want any confusion that it's exactly how a leaf page in a SQL Server index would look like.

This structure is going to represent the logical flow of the data.  In other words, what page comes next regardless of what the physical reality is.  That logical fragmentation comes into play when the actual physical next page isn't what the next page is pointed to by the current leaf page.

Given the above definition, say you have four index pages.  1 out of 4 of those index pages next page pointer is not the physical page next to it, but the other 3 do have that physical nature.  In that case, your percent of fragmentation would be 25%.  Of course, that was a simple example and in reality you wouldn't care about an index less than thousands of pages.  But hopefully that makes it a little clearer exactly what that number represents.

Here's a few good reads on Clustered Index Structures, Nonclustered Index Structures, and Heap Structures.


Insert String Value To Binary(64) Column

You need to apply the convert in the declare line, not during the insert. The error happens on the declare line, so the insert is never reached.


ORA-00907: missing right parenthesis on query with subqueries and EXCEPT operator

EXCEPT is an SQL standard that is not available in Oracle. You probably want to use the Oracle equivalent of MINUS.
See here for a good answer on the two keywords.
Edit: @a_horse_with_no_name corrected thanks!


SQL query - How to select to display group, group total, grand total?

GROUP BY ROLLUP does what you want.

As an example:

SELECT o.schema_id
    , type_desc
    , [Count Of Objects] = COUNT(o.object_id)
FROM sys.objects o
GROUP BY ROLLUP (o.schema_id, o.type_desc)

This produces the following output:

╔═══════════╦════════════════════════╦══════════════════╗
║ schema_id ║       type_desc        ║ Count Of Objects ║
╠═══════════╬════════════════════════╬══════════════════╣
║ 1         ║ PRIMARY_KEY_CONSTRAINT ║                7 ║
║ 1         ║ SERVICE_QUEUE          ║                3 ║
║ 1         ║ USER_TABLE             ║                8 ║
║ 1         ║ NULL                   ║               18 ║
║ 4         ║ INTERNAL_TABLE         ║               16 ║
║ 4         ║ SYSTEM_TABLE           ║               72 ║
║ 4         ║ NULL                   ║               88 ║
║ NULL      ║ NULL                   ║              106 ║
╚═══════════╩════════════════════════╩══════════════════╝

The NULL shown in the first two columns represents the roll-up amounts.

To make it "pretty", you can use some substitutions for the NULL rolled-up values:

SELECT [Schema Name] = CASE
        WHEN s.name IS NULL THEN
            '[Grand Total]'
        ELSE s.name
        END
    , [Object Type] = CASE
        WHEN o.type_desc IS NULL THEN
            '[Total - ' + COALESCE(s.name, 'Overall') + ']' COLLATE SQL_Latin1_General_CP1_CI_AS
        ELSE o.type_desc
        END
    , [Count of Objects] = COUNT(o.object_id)
FROM sys.objects o
    INNER JOIN sys.schemas s ON o.schema_id = s.schema_id
GROUP BY ROLLUP (s.name, o.type_desc);

╔═══════════════╦════════════════════════╦══════════════════╗
║  Schema Name  ║      Object Type       ║ Count of Objects ║
╠═══════════════╬════════════════════════╬══════════════════╣
║ dbo           ║ PRIMARY_KEY_CONSTRAINT ║                7 ║
║ dbo           ║ SERVICE_QUEUE          ║                3 ║
║ dbo           ║ USER_TABLE             ║                8 ║
║ dbo           ║ [Total - dbo]          ║               18 ║
║ sys           ║ INTERNAL_TABLE         ║               16 ║
║ sys           ║ SYSTEM_TABLE           ║               72 ║
║ sys           ║ [Total - sys]          ║               88 ║
║ [Grand Total] ║ [Total - Overall]      ║              106 ║
╚═══════════════╩════════════════════════╩══════════════════╝


How to insert data into tables which reference each other?(MySQL)

Not sure I understood the task, but here is some food for thought for an alternative schema:

CREATE TABLE Departament(
    DepartamentID varchar(25) not null,
    Floor varchar(25) not null,
        CONSTRAINT pk_dept PRIMARY KEY (DepartamentID, Floor),
) ENGINE=InnoDB;

CREATE TABLE Employee(
    ID varchar(25) not null,
    DepartamentID varchar(25),
    Floor varchar(25),
        CONSTRAINT pk_ID PRIMARY KEY (ID),
        CONSTRAINT fk_dept FOREIGN KEY (DepartamentID, Floor)
            REFERENCES Departament (DepartamentID, Floor)
) ENGINE=InnoDB;

CREATE TABLE Attendant (
    ID varchar(25) not null,
    DepartamentID varchar(25) not null,
    Floor varchar(25) not null,

    CONSTRAINT pk_attendent PRIMARY KEY (ID),
    CONSTRAINT ak_attendent UNIQUE (DepartamentID, Floor),
    CONSTRAINT fk_... REFERENCES Employee ...,
    CONSTRAINT fk_... REFERENCES Department ...,
) ENGINE=InnoDB;


Better ways than rownum limits on a union all for fallback-style queries?

Maybe like this:

select * from (
 Select ... from ... where rownum = 1
 union all
 Select ... from ... where rownum = 1
 union all
 Select ... from ... where rownum = 1
) where rownum = 1

If you want to retrieve only one row of each table there is no need to read any more and limit the rows as early as possible.


Oracle Illegal use of long datatype when moving tablespace to bigfile

To move the table without changing the column datatype I believe you'll need to export the table then import it into the new tablespace.

Note that if you're using Oracle 10g or later use expdp and impdp instead of exp/imp. (See: http://docs.oracle.com/cd/B19306_01/server.102/b14215/dp_export.htm)

You should use a command like:

  exp file=mytable.dmo log=mytable .log tables=mytable  feedback=1

To export the table. Then drop it in the old tablespace and create it in the new tablespace.

Then use:

  imp file=mytable.dmo log=mytable.log full=y feedback=1 ignore=y

To import the table. Hope this helps.

References:

http://www.orafaq.com/wiki/Import_Export_FAQ
http://psoug.org/reference/export.html
http://www.oracle-base.com/articles/10g/oracle-data-pump-10g.php
http://docs.oracle.com/cd/E11882_01/server.112/e22490/dp_overview.htm


use dbx;
select foo
from db1.dbo.table
join db2.dbo.table on condition
where some_function();

This query consumed lots of CPU and requested a large memory grant. In which database? The information you want simply doesn't exist as a concept. As a human with insight knowledge and with hindsight benefit, you probably would be able to explain why 75% of CPU is due to db1 and 15% is due to db2. But ultimately you just can assign queries to a database. The fact that some (ok, most) queries are 100% contained inside a db does not mean that all query resources can be assigned deterministically to a db.

However, for practical means is relatively simple to automate exactly what you did in your post: inspect the plans and identify all physical access operators locations and use this info to assign the query resources to a DB.

with xmlnamespaces (default 'http://schemas.microsoft.com/sqlserver/2004/07/showplan')
select x.value(N'@NodeId',N'int') as NodeId
    , x.value(N'@PhysicalOp', N'sysname') as PhysicalOp
    , x.value(N'@LogicalOp', N'sysname') as LogicalOp
    , ox.value(N'@Database',N'sysname') as [Database]
    , ox.value(N'@Schema',N'sysname') as [Schema]
    , ox.value(N'@Table',N'sysname') as [Table]
    , ox.value(N'@Index',N'sysname') as [Index]
    , ox.value(N'@IndexKind',N'sysname') as [IndexKind]
    , x.value(N'@EstimateRows', N'float') as EstimateRows
    , x.value(N'@EstimateIO', N'float') as EstimateIO
    , x.value(N'@EstimateCPU', N'float') as EstimateCPU
    , x.value(N'@AvgRowSize', N'float') as AvgRowSize
    , x.value(N'@TableCardinality', N'float') as TableCardinality
    , x.value(N'@EstimatedTotalSubtreeCost', N'float') as EstimatedTotalSubtreeCost
    , x.value(N'@Parallel', N'tinyint') as DOP
    , x.value(N'@EstimateRebinds', N'float') as EstimateRebinds
    , x.value(N'@EstimateRewinds', N'float') as EstimateRewinds
    , st.*
    , pl.query_plan
from sys.dm_exec_query_stats as st
cross apply sys.dm_exec_query_plan (st.plan_handle) as pl
cross apply pl.query_plan.nodes('//RelOp[./*/Object/@Database]') as op(x)
cross apply op.x.nodes('./*/Object') as ob(ox)


SQL Find Minimum Value Greater than Previous Row

My solution using a cursor and a temporary table, I can imagine another solution, but it involves an expensive recursive query.

CREATE TABLE #tv
(
    businesskey int NOT NULL,
    id int NOT NULL,
    timevalue int,
);
GO

DECLARE @bk int, @tv1 int, @tv2 int, @tv3 int;
DECLARE @last_min int = 0;
DECLARE @last_bk int = 0;
DECLARE @id int;

DECLARE curtv CURSOR FORWARD_ONLY READ_ONLY FOR
    SELECT   businesskey, timevalue1, timevalue2, timevalue3
    FROM     timetest
    ORDER BY businesskey, TimeTestIdentityId;

OPEN curtv;
FETCH NEXT FROM curtv INTO @bk, @tv1, @tv2, @tv3;

WHILE @@fetch_status = 0
BEGIN

    -- First row of a businesskey, basically set counters to 0
    IF @last_bk  @bk
    BEGIN
        SET @last_bk = @bk;
        SET @last_min = 0;
        SET @id = 0;
    end

    SET @id = @id + 1;

    -- select minimum positive value
    -- it could be replaced by the below inline UDF but I think it doesn't worth.
    SELECT @last_min = min(v) + @last_min
    FROM   (VALUES (@tv1-@last_min),(@tv2-@last_min),(@tv3-@last_min)) as t(v)
    WHERE  v > 0;

    INSERT INTO #tv VALUES (@bk, @id, @last_min);

    FETCH NEXT FROM curtv INTO @bk, @tv1, @tv2, @tv3;
END

CLOSE curtv;
DEALLOCATE curtv;
GO

-- one temp table has been generated you could add an index.
ALTER TABLE #tv ADD CONSTRAINT [PK_tv] PRIMARY KEY (businesskey, id);
GO

SELECT * FROM #tv ORDER BY businesskey, id;
GO

IF OBJECT_ID('tempdb..#tv') IS NOT NULL DROP TABLE #tv;
GO

businesskey | timevalue
----------: | --------:
          5 |       131
          5 |       135
          5 |       140
          5 |       141
          5 |       148
          5 |       150
          6 |       134
          6 |       138
          6 |       143
          6 |       144
          6 |       151
          6 |       153

db<>fiddle here

Using a recursive CTE:

IF OBJECT_ID('min_positive') IS NOT NULL
    DROP FUNCTION min_positive;
GO

-- inline UDF you should add as many parameters as you need.
CREATE FUNCTION min_positive(@v1 int, @v2 int, @v3 int)
RETURNS TABLE AS
RETURN
    SELECT MIN(v) AS minpos
    FROM (VALUES (@v1),(@v2),(@v3)) AS t(v)
    WHERE v > 0;
GO

;WITH ct1 AS
(
    -- set a rank for each businesskey
    -- I need it for the anchor in the recursive part
    SELECT businesskey, timevalue1, timevalue2, timevalue3,
           RANK() OVER (PARTITION BY businesskey ORDER BY TimeTestIdentityId) rk
    FROM   timetest
)
, ct2 AS
(
    -- first row of each businesskey
    SELECT businesskey, timevalue1, timevalue2, timevalue3, rk,
           (SELECT minpos
            FROM   min_positive(timevalue1, timevalue2, timevalue3)) AS lm
    FROM ct1
    WHERE rk = 1

    UNION ALL

    -- next row of the businesskey untill the end of the rank
    SELECT ct1.businesskey, ct1.timevalue1, ct1.timevalue2, ct1.timevalue3, ct1.rk,
           (SELECT minpos + ct2.lm
            FROM min_positive(ct1.timevalue1-ct2.lm, ct1.timevalue2-ct2.lm, ct1.timevalue3-ct2.lm)) lm
    FROM ct1
    JOIN ct2
         ON ct2.businesskey = ct1.businesskey
         AND ct1.rk = ct2.rk + 1
)
SELECT   businesskey, lm
FROM     ct2
ORDER BY businesskey, rk;
GO

db<>fiddle here