SQL> r
  1  with t as (
  2      select  1.0 as x from dual
  3      union all select -2.0 as x from dual
  4      union all select  3.0 as x from dual
  5      union all select -4.0 as x from dual
  6      union all select  5.0 as x from dual
  7      union all select -6.7 as x from dual
  8      union all select  8.9 as x from dual)
  9  select sum(case when x > 0 then x else 0 end) as sum_of_positives,
 10         sum(case when x < 0 then x else 0 end) as sum_of_negatives
 11*   from t

SUM_OF_POSITIVES SUM_OF_NEGATIVES
---------------- ----------------
            17,9            -12,7


Invert a Boolean expression which can return UNKNOWN

You could enclose the condition in a CASE expression that returns a binary result, for instance 1 or 0:

SELECT
  ...
FROM
  ...
WHERE
  CASE WHEN someColumn = someValue THEN 1 ELSE 0 END = 1
;

Negating the expression will give you all the other rows from the same data source, including those where someColumn is null:

SELECT
  ...
FROM
  ...
WHERE
  NOT CASE WHEN someColumn = someValue THEN 1 ELSE 0 END = 1
  -- or: CASE WHEN someColumn = someValue THEN 1 ELSE 0 END <> 1
;

Since SQL Server 2012 you also have the IIF function, which is just a wrapper around a binary CASE like above. So, this CASE expression:

CASE WHEN someColumn = someValue THEN 1 ELSE 0 END

will look like this if rewritten using IIF:

IIF(someColumn = someValue, 1, 0)

And you can use it exactly the same way as the CASE expression. There will be no difference in performance, only the code will be slightly more concise, possibly cleaner that way too.


Does the SELECT statement count into DML?

You do manipulate and restrict data: GROUP BY, ORDER BY, TOP, JOIN, lock hints, etc

You just don't change the database state when you do.

It boils down to whether you read "manipulate" to include "change state"


Restricted user on my local SQL Server database?

If the instance name is .\SQLEXPRESS you have an instance of SQL Server Express but not localdb.

The default install will give builtin\administrators permissions on the database so you will have to start the Managment Studio (SSMS) by right clicking the shortcut and select run as admin to have an administrative token and be able to create databases.

When running the SSMS as admin you can add your user account to the sysadmin role in the database server and after that run things using your user account without elevation.


ERROR 1166 (42000) at line 65203: Incorrect column name ''

The only database object I can think of with the option CREATE ALGORITHM is a VIEW
To see all the views you have, run this
SELECT table_schema,table_name FROM information_schema.views;

or
SELECT table_schema,table_name FROM information_schema.tables WHERE engine IS NULL;

From the given error message, one of the views in your database has a column that no longer exists. This can easily happen if you drop a table or a column from a table that is referenced in the view.
Please look over all your views and make sure you can SELECT from all of them.
CORRECTIVE ACTION
You may have to recreate the mysqldump after you fix the VIEW in the source database.
If you cannot recreate the mysqldump due to the size, you may have edit the mysqldump to ignore the VIEW definition altogether.


Colloboration between T-SQL Table and Excel Sheet(s)

It's very easy to do this in access. Setup an ODBC Connection on the users computer then add in a linked-table into the access db.

I don't think it would take too long for someone to create an application with a nice winform interface. We have one at my office where if you create a function/stored procedure in SQL starting with certain characters, lets say dbo.app_*******, it will appear as a report in the application and any parameters in the stored procedure will prompt the user to input a value in the app.


whether a big (but not used) ibdata1 slow anything down

There are things to keep in mind if you decide not to shrink ibdata1.

First of all, if you switch to innodb_file_per_table and you do not perform the Cleanup, the InnoDB tables will still reside in ibdata1. Any future tables you create will be stored in .ibd files.

As a side note, since you are using innodb_file_per_table, you will need to increase innodb_open_files.

The only way to know which InnoDB tables are still in ibdata1, you would have to run this:

SELECT table_schema,table_name
FROM information_schema.tables
WHERE engine='InnoDB';

then go into each DB Folder of /var/lib/mysql (or datadir) and see if a table has both .frm and .ibd. Those will both are outside ibdata1. Those with .frm only are still in ibdata1.

If you would like to extract all InnoDB tables out of ibdata1 without performing the Cleanup, simply run the following:

SQL="SELECT CONCAT('ALTER TABLE ',table_schema,'.',table_name,' ENGINE=''InnoDB'';')"
SQL="${SQL}" FROM information_schema.tables WHERE engine='InnoDB'"
mysql -u... -p... -ANe"${SQL}" > ConvertInnoDB.sql
mysql -u... -p... -A < ConvertInnoDB.sql

This will also defrag all InnoDB tables that are already outside ibdata1.

INSERTs and  UPDATEs against those tables inside ibdata1 can still make ibdata1 grow. Is that bad? If ibdata1 resides in ext3 disk, there is OS-dependent size limit of 2TB. If you are nowhere near 2TB, then don't worry about performance. Switching to innodb_file_per_table spares you any growth spurts of ibdata1. If ibdata1 ever approaches 2TB, you need to

service mysql stop
In /etc/my.cnf, change innodb_data_file_path from ibdata1:10M:autoextend to ibdata1:2047G:ibdata2:10M:autoextend
service mysql start

Here is another point: What lives in ibdata1 besides table data, table indexes, table metadata? Objects for MVCC and Transaction_isolation. Here are the things that ibdata1 provides for MVCC

Double Write Buffer (to avoid caching to the OS)
Insert Buffer (for processing Changes to Secondary Indexes)
Undo Tablespace (used to revert changes of Uncommitted Transactions)
Rollback Segments (List of pointers into Undo Tablespace)

These things can make ibdata1 grow but not to any dangerous levels. Over time, I have seen ibdata1 grow to 10G at worst in an environment that featured constant dropping and recreating of InnoDB tables.

Performance should be OK. With a bloated ibdata1, you can probably handle big transactions in terms of rollback and insert buffering. I would just increase innodb_log_buffer_size to compensate.


alter column varchar to nvarchar - will file group on full drive have issues?

So 1 filegroup with 2 files, one on each drive ?
In this case the writes will be distributed according to the space available on each file -
see the MS reference :- https://docs.microsoft.com/en-us/sql/relational-databases/databases/database-files-and-filegroups?view=sql-server-ver15
So the SQL Server engine should sort it out - however you might not get the best performance for this you would probably want a new filegroup with a number of equally sized files - but life gets complicated quickly and for most databases it is not worth the extra planning / management for the small percentage performance improvements available.
Only precaution I would take is to set the maximum size of the file on the nearly full disk to be equal to its current size, as you wouldn't want it to attempt an autogrow and for that to fail.


How can I manage PL/SQL package source at the method level?

Some pondering if you're in fact having a XY problem. YMMV.

  Using an existing utility, is there a way to store package methods as discrete files in source control and deploy them to their package individually?

To me this sounds a peculiar requirement. In database a PL/SQL package is a single atomic unit (in fact there is two units (a specification and a body) but that's irrelevant in this context) that is modified and deployed as a whole.

Let's rephrase the question:

  Using an existing utility, is there a way to store Java class methods as discrete files in source control and deploy them to their class individually?

I'd say that's simply silly. IMO one should treat Oracle PL/SQL source code in the same love than any other programming language source code.

(I'm not ware of such a tool, but you very likely can make that by yourself.)

Your real problem are monolithic PL/SQL packages:

  [...] PL/SQL packages that are large and monolithic in nature, containing a wide assortment of functionality.

That conflicts your parallel development paths.

Reading the fine manual:

  A package is a schema object that groups logically related PL/SQL types, variables, constants, subprograms, cursors, and exceptions.

And finally the root cause:

  [...] because each package requires additional configuration in the ERP suite itself.

IMO tail wagging the dog.

Resolve this problem, refactor your PL/SQL to have a modular structure and your development/deployment conflict problems mostly disappear.

I don't know the "ERP suite configuration" details but I'm positive it's PITA-factor is much smaller than the current mess.


Size of Binary log file in Mysql Replication

I have resolved the issue myself only using following process :

Change the replication mode to ROW.
Restrict the size of max_binlog_size=30M

In this way binary log file cant be of huge size and easy to replicate.


MySQL: Can not add foreign key into create table statement

I am not sure about word press, but if you are using MySQL then you need to change your foreign key structure
Original:

foreign key (fk_coupons_targets) references wp_frequentVisitorCoupons_coupons(couponId)

After adjust:

foreign key fk_coupons_targets (col_name) references wp_frequentVisitorCoupons_coupons(couponId)

example:

ALTER table_name
ADD CONSTRAINT constraint_name
FOREIGN KEY foreign_key_name(columns)
REFERENCES parent_table(columns)

Note: if you want to add them foreign key to create table then you need to add only the last two lines


How to skip prefix column check in multi column index in MySQL?

It depends.  Since you are using MySQL 8.0, the index starting with type is probably fine.  I am guessing that the optimizer will be smart enough to

fetch rows with (0,1,15)
Continue fetching, now for rows with (1,1,15).

You can get some clues from EXPLAIN or EXPLAIN FORMAT=JSON.

You can 'prove' that it is (or is not) doing it in that optimal way by this technique:

FLUSH STATUS;
SELECT ...
SHOW SESSION STATUS LIKE 'Handler%';

If the total of the Handler_read_% values is very close to the number of rows returned, then it probably did it that optimal way.  Else the Handler_read values may be much larger.

I see no PRIMARY KEY on that table.  If that triple is 'unique' then make it the PK.  This will speed it up a little more due to "clustering" of the PK.

If the index is not good enough, rearrange it to have type last.

(Pet peeve:  Drop the constant "post" from column names; it clutters queries.)


On v18.5
Tools->Options->Environment->General->Uncheck Display status bar


How to make MySQL table name case insensitive in Ubuntu?

Open terminal and edit /etc/mysql/my.cnf

sudo nano /etc/mysql/my.cnf

Underneath the [mysqld] section.add:

lower_case_table_names = 1

Restart mysql

sudo /etc/init.d/mysql restart

Then check it here:

mysqladmin -u root -p variables


Rearranging Data in an MS Access 2007 table into a new table format/Result Output

Yes, it can be done, using UNION ALL:

SELECT [Name] & " Math" AS col1, tab1.Math FROM tab1
UNION ALL
SELECT [Name] & " Science" AS col1, tab1.Science FROM tab1
UNION ALL
SELECT [Name] & " Reading" AS col1, tab1.Reading FROM tab1
UNION ALL
SELECT [Name] & " Civics" AS col1, tab1.Civics FROM tab1
Order BY 1;


filter a table based on a second table matching all criteria

This may not be the best performing way to do this but it should work for you.

Set up code: (Anyone else wanting to answer feel free to copy this part)

CREATE TABLE #Attrib (itemno int, attrname varchar(50), attrvalue varchar(50));
INSERT INTO #Attrib VALUES
    (1, 'Color', 'Black'),
    (1, 'Connector', '3.5mm'),
    (1, 'Output', '5W'),

    (2, 'Color', 'Black'),
    (2, 'Connector', 'USB'),
    (2, 'Output', '10W'),

    (3, 'Color', 'Silver'),
    (3, 'Connector', '3.5mm'),
    (3, 'Output', '5W'),

    (4, 'Color', 'White'),
    (4, 'Connector', 'USB'),
    (4, 'Output', '2W');

CREATE TABLE #Filter1 (attrname varchar(50), attrvalue varchar(50));

INSERT INTO #Filter1 VALUES
    ('Color','Black'),
    ('Color','Silver'),
    ('Output','5W');
GO

Query:

SELECT DISTINCT itemno
FROM #Attrib A
JOIN #Filter1 F
    ON A.attrname = F.attrname
    AND A.attrvalue = F.attrvalue
GROUP BY itemno
HAVING COUNT(DISTINCT A.attrname) = (SELECT COUNT(DISTINCT attrname) FROM #Filter1);


Database Design when Admin and Employee have similar roles except Admin can see all the other Employees Data

Maybe have separate table for Vendor_id, Admin_id, employee_id. You could join on isnull(admin_id, employee_id).

That way, if the user has an admin_id, they could see all the records. If they don't, they would see only the records for their employee_id.


Automatically replicate table data into another table

You  can use triggers or replication,but replication will be a overkill for a single table..See below example for more details

Changes in Table A,should replicate to table B

CREATE TABLE [dbo].[a](
    [id] [int] NULL,
    [name] [char](2) NULL
)

CREATE TABLE [dbo].[b](
    [id] [int] NULL,
    [name] [char](2) NULL
)

Now create trigger which replicates only data changes

ALTER trigger [dbo].[trg_test]
on [dbo].[a]
for insert,update,delete
as

begin

set nocount on

--updated
if exists(select 1 from inserted) and exists(select 1 from deleted)
begin

update b
set b.id=i.id,
    b.name=i.name
    from b
    join
    inserted i
    on b.id<>i.id and b.name<>i.name
    join deleted d
    on b.id=d.id and b.name=d.name
return

end

--inserted
if exists(select 1 from inserted)
begin

insert into b
select * from inserted
end

--deleted
if exists(select 1 from deleted)
begin

delete b
from
deleted d
join
b
on b.id=d.id

end

end

now do some random datachanges

--inserts

    insert into a
    select 1,'d'
    union all
    select 3,'c'

--updates
update
a
set id=4

--deletes
delete from a

you can check at each stage to see data replication to b


Oracle user password with space character in it

Not able to login to oracle using sqlplus utility because username and password contain space

[oracle@o71 ~]$ sqlplus sys/Oracle123@\'localhost/MIN19_O71\' as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Tue Jul 30 23:12:19 2019
Version 19.4.0.0.0

Copyright (c) 1982, 2019, Oracle.  All rights reserved.

Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.4.0.0.0

SQL> alter user sys identified by "Oracle 123";

User altered.

SQL> Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.4.0.0.0

[oracle@o71 ~]$ sqlplus sys/\""Oracle 123\""@\'localhost/MIN19_O71\' as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Tue Jul 30 23:19:25 2019
Version 19.4.0.0.0

Copyright (c) 1982, 2019, Oracle.  All rights reserved.

Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.4.0.0.0

SQL>


SGA size not decreasing after reducing memory_target (Oracle 11.2.0.1)

Do not use show sga for querying the current SGA size.

Total System Global Area in the output of show sga is computed as select sum(value) from v$sga, you can easily confirm this by enabling SQL trace.

SQL> select * from v$sga;

NAME                      VALUE
-------------------- ----------
Fixed Size              2258040
Variable Size         490736520
Database Buffers      356515840
Redo Buffers            6471680

SQL> select sum(value) from v$sga;

SUM(VALUE)
----------
 855982080

This includes Variable Size, and that is not the current usage. Use v$memory_dynamic_components. For an even more detailed list, use v$sgastat and v$pgastat, I will not post output of those here.

SQL> show parameter memory_target

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
memory_target                        big integer 820M

SQL> show sga

Total System Global Area  855982080 bytes
Fixed Size                  2258040 bytes
Variable Size             494930824 bytes
Database Buffers          352321536 bytes
Redo Buffers                6471680 bytes

SQL> select component, current_size, min_size, max_size from v$memory_dynamic_components;

COMPONENT                 CURRENT_SIZE   MIN_SIZE   MAX_SIZE
------------------------- ------------ ---------- ----------
shared pool                  134217728  130023424  134217728
large pool                     8388608    8388608   71303168
java pool                      4194304    4194304    4194304
streams pool                   4194304          0    4194304
SGA Target                   515899392  515899392  515899392
DEFAULT buffer cache         352321536  289406976  352321536
KEEP buffer cache                    0          0          0
RECYCLE buffer cache                 0          0          0
DEFAULT 2K buffer cache              0          0          0
DEFAULT 4K buffer cache              0          0          0
DEFAULT 8K buffer cache              0          0          0
DEFAULT 16K buffer cache             0          0          0
DEFAULT 32K buffer cache             0          0          0
Shared IO Pool                       0          0          0
PGA Target                   343932928  343932928  343932928
ASM Buffer Cache                     0          0          0

Now, decreasing memory_target (and that is not necessarily the SGA).

SQL> alter system set memory_target=720M scope=memory;

System altered.

SQL> show parameter memory_target

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
memory_target                        big integer 720M

SQL> show sga

Total System Global Area  855982080 bytes
Fixed Size                  2258040 bytes
Variable Size             494930824 bytes
Database Buffers          352321536 bytes
Redo Buffers                6471680 bytes

show sga still shows the old values.

SQL> select component, current_size, min_size, max_size from v$memory_dynamic_components;

COMPONENT                 CURRENT_SIZE   MIN_SIZE   MAX_SIZE
------------------------- ------------ ---------- ----------
shared pool                  134217728  130023424  134217728
large pool                     8388608    8388608   71303168
java pool                      4194304    4194304    4194304
streams pool                   4194304          0    4194304
SGA Target                   515899392  515899392  515899392
DEFAULT buffer cache         352321536  289406976  352321536
KEEP buffer cache                    0          0          0
RECYCLE buffer cache                 0          0          0
DEFAULT 2K buffer cache              0          0          0
DEFAULT 4K buffer cache              0          0          0
DEFAULT 8K buffer cache              0          0          0
DEFAULT 16K buffer cache             0          0          0
DEFAULT 32K buffer cache             0          0          0
Shared IO Pool                       0          0          0
PGA Target                   239075328  239075328  343932928
ASM Buffer Cache                     0          0          0

Notice how SGA Target did not change at all. Instead, PGA Target was decreased. Decreasing it further:

SQL> alter system set memory_target=420M scope=memory;

System altered.

SQL> show sga

Total System Global Area  855982080 bytes
Fixed Size                  2258040 bytes
Variable Size             629148552 bytes
Database Buffers          218103808 bytes
Redo Buffers                6471680 bytes

Notice how Variable Size has changed, but Total System Global Area has not.

SQL> select component, current_size, min_size, max_size from v$memory_dynamic_components;

COMPONENT                 CURRENT_SIZE   MIN_SIZE   MAX_SIZE
------------------------- ------------ ---------- ----------
shared pool                  134217728  130023424  134217728
large pool                     8388608    8388608   71303168
java pool                      4194304    4194304    4194304
streams pool                   4194304          0    4194304
SGA Target                   381681664  381681664  515899392
DEFAULT buffer cache         218103808  218103808  352321536
KEEP buffer cache                    0          0          0
RECYCLE buffer cache                 0          0          0
DEFAULT 2K buffer cache              0          0          0
DEFAULT 4K buffer cache              0          0          0
DEFAULT 8K buffer cache              0          0          0
DEFAULT 16K buffer cache             0          0          0
DEFAULT 32K buffer cache             0          0          0
Shared IO Pool                       0          0          0
PGA Target                    58720256   58720256  343932928
ASM Buffer Cache                     0          0          0

Variable Size is there because, memory_max_size and sga_max_size was computed from memory_target at instance startup time.

SQL> show parameter memory_max

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
memory_max_target                    big integer 820M
SQL> show parameter sga_max

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
sga_max_size                         big integer 820M

This allows us to increase SGA back to that point. But those you can not change dynamically:

SQL> alter system set memory_max_target=420M scope=memory;
alter system set memory_max_target=420M scope=memory
                 *
ERROR at line 1:
ORA-02095: specified initialization parameter cannot be modified

SQL> alter system set sga_max_size=420M scope=memory;
alter system set sga_max_size=420M scope=memory
                 *
ERROR at line 1:
ORA-02095: specified initialization parameter cannot be modified

SQL> alter system set memory_max_target=920M scope=memory;
alter system set memory_max_target=920M scope=memory
                 *
ERROR at line 1:
ORA-02095: specified initialization parameter cannot be modified

SQL> alter system set sga_max_size=920M scope=memory;
alter system set sga_max_size=920M scope=memory
                 *
ERROR at line 1:
ORA-02095: specified initialization parameter cannot be modified


What's an alternative solution to using a null value for a foreign key field?

As Akina said in the comments, try to avoid breaking relationships and referential integrity in your databases. If there is a documented need to have a value in there that indicates "none", "unknown", "other" or something like that, it is better to have that in your lookup table than try to hammer Access apart to allow for NULLs in a Foreign Key field.


Find locations in table that are within a certain radius distance of a given latitude/longitude

There are lots of Q&A here.  As a first cut, use a "bounding box".

ANALYZE TABLE and OPTIMIZE TABLE are irrelevant.

You mention Lattitude, but you don't have such a column.

Either go with point and SPATIAL, or go with latitude and longitude; it is probably folly to mix them.


BACKUP failed to complete the command BACKUP DATABASE

Looks to be a post-completion check failure as there is no evidence elsewhere stating otherwise.


SQL Server 2016 SP1 Managed Backup to blob storage

While Microsoft corrects this bug in SQL 2016 a workaround is the following:

Create a credential to your azure storage but use an Access Key as password instead of a SAS (this was the procedure for SQL 2014).
Enable the Managed Backup (it will work correctly)

At this point MB is enabled but backups created by it (and any manual backup using the same storage) will fail because of the credential being created in 2014 mode, so

Modify the credential changing the Password to the SAS.

Now backups will start to run.
If you need to disable or configure MB again you will have to switch to Access Key and then back again to SAS.


PL/SQL for Updating 1 Column in Table A Where PK = a row in table B

Most efficient way to do the update is most likely not with PL/SQL, but with single SQL statement, for example:

update ord
set order_type=<new value>
where order_key in (
  select order_key
  from gr_ord
);

or with merge:

merge into ord
using gr_ord
on (ord.order_key = gr_ord.order_key)
when matched then
  update set order_type=<new value>;

You should be able to do ~3M update in a single transaction. I have run updates against table with 100+ columns at rate of 1M updates in 15 seconds (single thread in average server).


Disabling variable substitution inside SQLCMD script when using :r syntax

If you want to do the same thing that you are attempting to do within Management Studio, I think the simplest approach is to write a very simple PowerShell script to run the same thing that goes through each file and then executes the file with SQL CMD.

In my example, I have a directory named e:\test. I have a few .sql files in that directory and I want to execute them and disable variable substitution.  To do that I would use the following script within powershell:

$Path = "e:\test"
$files = Get-ChildItem e:\test\*.sql
ForEach ($file in $files) {
    Invoke-Sqlcmd -ServerInstance localhost -InputFile $file -DisableVariables
}

This obviously takes you out of Management Studio, but it should accomplish the task fairly easily and do so with variable substitution being disabled.


Select arbitrary amount of rows from child table and join them into parent table as columns

Try this:

SELECT
    parent.id,
    parent.name,
    GROUP_CONCAT(CONCAT(child.attribute, ":", child.val)) AS Attributes
FROM parent INNER JOIN child ON parent.id=child.parent_id
GROUP BY parent.id


SQL Grouping rows in a feed database table, output only grouped rows adding a group row

db-fiddle has strange effect on columns are not in order as per sql statement but works fine on my local Mysql 8.0

select sku,
       case  WHEN group_name IS NULL
             AND  type='single'
             THEN  TRIM(TRAILING '-' FROM REPLACE(sku,size,''))
              ELSE group_name
        END
        AS group_name,
        description,
        size,
        type
from products

This gives your desired output with cte expressions i.e excluding existing rows plus grouping key(same output as bottom table in your question).Tested on your dbfiddle

WITH cte1
AS
  (
   SELECT sku,
          TRIM(TRAILING '-' FROM REPLACE(sku,size,''))
          group_name,
          description,
          size,
          type
   FROM   products
   WHERE  group_name IS NULL
   AND    type = 'single'
  )

SELECT * FROM cte1

UNION

SELECT   DISTINCT(group_name) sku,
         NULL group_name,
         '' description,
         '' size,
         'group' type
FROM     cte1


read-only permissions except specific stored procedures

You can create a second user with elevated priviliges and use WITH EXECUTE AS 'username' clause in the stored procedure body, having the procedure executed as the user with readonly permissions would be elevated to the second user who can perform modifications to the second database.

In order for this to work you will also have to grant IMPERSONATE permission to the readonly user and to set a trust relationship between databases - this can be done in at least one of two ways:
ALTER DATABASE <dbname> SET TRUSTWORTHY ON
(which might impose some security issues)
or the second option to use certificates to authorize user. This link might be helpful (it's written in the context of Service Broker, but the idea is the same considering security): LINK


How to do differential backups in a SQL Server RDS instance

From https://aws.amazon.com/premiumsupport/knowledge-center/native-backup-rds-sql-server/

  To set up a native backup of the SQL Server database, use the
  following services:

  An Amazon S3 bucket to store your backup files
  An AWS Identity and Access Management (IAM) role to access the bucket
  The SQLSERVER_BACKUP_RESTORE option added to an option group on the DB
  instance

  exec msdb.dbo.rds_backup_database  @source_db_name='database_name',
  @s3_arn_to_backup_to='arn:aws:s3:::bucket_name/file_name_and_extension',
  @overwrite_S3_backup_file=1;

After that you can use similar command to create diff backup:

exec msdb.dbo.rds_backup_database
@source_db_name='database_name', @s3_arn_to_backup_to='arn:aws:s3:::bucket_name/diff_file_name_and_extension',
@overwrite_S3_backup_file=1,
@type = 'DIFFERENTIAL';


Convert date in date list condition to list of date ranges

SUGGESTION #1

SELECT A.* FROM table1 A INNER JOIN
(
    SELECT '2015-03-01' dtcolumn
    UNION SELECT '2015-03-15'
    UNION SELECT '2015-04-01'
    UNION SELECT '2015-04-15'
    UNION SELECT '2015-05-01'
) B ON
A.dtcolumn >= B.dtcolumn
AND A.dtcolumn < B.dtcolumn + INTERVAL 1 DAY;

SUGGESTION #2

SELECT * FROM table1 WHERE
(column >= '2015-03-01' AND
column < '2015-03-01' + INTERVAL 1 DAY)
UNION
SELECT * FROM table1 WHERE
(column >= '2015-03-15' AND
column < '2015-03-15' + INTERVAL 1 DAY)
UNION
SELECT * FROM table1 WHERE
(column >= '2015-04-01' AND
column < '2015-04-01' + INTERVAL 1 DAY)
UNION
SELECT * FROM table1 WHERE
(column >= '2015-04-15' AND
column < '2015-04-15' + INTERVAL 1 DAY)
UNION
SELECT * FROM table1 WHERE
(column >= '2015-05-01' AND
column < '2015-05-01' + INTERVAL 1 DAY);


What is the difference between xact_start and query_start in postgresql?

It also says that xact_start can be different than query_start, but
  doesn't explain why

It differs when the transaction encapsulates several queries, for example:

BEGIN;  -- starts at t0, beginning of transaction
query1; -- starts at t1
query2; -- starts at t2
COMMIT; -- starts at t4, end of transaction

If we look at the pg_stat_activity entry for this session while query2 is running, xact_starts will be t0 and query_start will be t2, with t2>t0, the difference being essentially the duration of query1.

xact_start helps discovering long running transactions, which is useful when troubleshooting or tuning a live database.


In which table we should put the foreign key

The relationship between Admin and Role doesn't seem to be one-to-one. It's either one to many (One Role can be assigned to multiple people, then you will have FK column in Admin table), or many-to-many (then it's gonna be a link table with 2 FKs to each table).


No, there is no built-in feature for merging one key into another, updating all referencing rows automatically. There could be all kinds of complications you need to resolve.

Simple case

You need to run UPDATE on all referencing tables and DELETE on the referenced table. The simple case would look like this:

BEGIN;

--  1. update references
UPDATE sale
SET    person_id = _org_id
WHERE  person_id = _dupe_id;

-- 2. kill dupe
DELETE FROM person
WHERE  id = _dupe_id;

COMMIT;

But there may be all kinds of ...

Complications

Imagine a table person_tag implementing an n:m relationship between person and tag with a unique constraint on (person_id, tag_id). You can't UPDATE rows that would result in duplicate tags. You need to resolve conflicts: only update rows that do not conflict with the unique constraint and delete the rest.

-- example for conflict resolution
UPDATE person_tag pt
SET    person_id = _org_id
WHERE  person_id = _dupe_id
AND NOT EXISTS (
   SELECT 1 FROM person_tag
   WHERE  person_id = _org_id
   AND    tag_id = pt.tag_id
   );

DELETE FROM person_tag
WHERE  person_id = _dupe_id;
-- end person_tag

There may be more sophisticated constellations ...

Full automation

If you have lots of referencing tables that can be treated in the same fashion (or a changing number), you can automate the procedure with dynamic SQL.

For the simple case with a single-column fk constraint and no complications:

CREATE OR REPLACE FUNCTION f_merge_id(_tbl regclass, _col text, _org_id int, _dupe_id int)
  RETURNS void AS
$func$
DECLARE
   rec record;
BEGIN
   FOR rec IN  -- find all referencing columns
      SELECT c.conrelid::regclass AS tbl, quote_ident(a2.attname) AS col
      FROM   pg_catalog.pg_attribute  a1
      JOIN   pg_catalog.pg_constraint c  ON c.confrelid = a1.attrelid
                                        AND c.confkey   = ARRAY[a1.attnum]
      JOIN   pg_catalog.pg_attribute  a2 ON a2.attrelid = c.conrelid
                                        AND a2.attnum   = c.conkey[1]
      WHERE  a1.attrelid = _tbl
      AND    a1.attname  = _col
      AND    c.contype   = 'f'  -- fk constraint
   LOOP        -- Redirect to _org_id all references to dupe_id
      EXECUTE format('
         UPDATE %1$s
         SET    %2$s = $1
         WHERE  %2$s = $2'
        ,rec.tbl, rec.col)
      USING _org_id, _dupe_id;
   END LOOP;

   -- Finally kill (now orphaned) dupe
   EXECUTE format('DELETE FROM %s WHERE %s = $1', _tbl, _col)
   USING _dupe_id;
END
$func$ LANGUAGE plpgsql;

Call:

SELECT f_merge_id('person', 'person_id', _org_id := 1, _dupe_id := 2);

This updates any number of tables that have a foreign key referencing the master table.

Using named parameters in the call, which is not required, but you'll want to make sure  not to confuse original and duplicate here.

SQL Fiddle.

Major points

If you are unfamiliar with the concept of dynamic SQL in plpgsql functions, refer to some of the related questions:

https://dba.stackexchange.com/questions/tagged/postgresql+dynamic-sql+plpgsql

Quote column names correctly to avoid SQL injection. quote_ident() does that for you:

SQL injection in Postgres functions vs prepared queries

If you have to deal with heavy concurrent load, you must be prepared that the final DELETE can fail, which would roll back the whole transaction. Concurrent transactions could enter new rows with _dupe_id. There are various ways to deal with that, which goes beyond the scope of this question.


Stored Procedures and External Procedure are distinct ones.

Stored procedures are stored in database and used from there. You can write them in different languages. Also note that you can pre-compile PL/SQL instead of interpreting

SQL/PL-SQL , most common
Java
.NET only in windows environment.

External Procedures are external functions/procedures in Operating System. You write a stored procedure in PL/SQL, use  call specification to call other languages in operating System. This potentially gives you power to call any function in other languages. Following usages come to my mind.

Use a Data Mining algorithm which is not implemented in Oracle Data Mining
Use ftp function to transfer files to another server
Use a graphic functions of Gnu Plot to create graphics and store them in database for later use.

These usages in my opinion are extreme and unnecessary. All of them can be accomplished with application programs. Nowadays nobody uses database as it is. There are always applications built using Java, .NET, PHP etc. Whenever possible, these other languages should be used in application.


As per MongoDB documentation here which prints the data distribution statistics for a sharded collection. You must call the getShardDistribution() method on a sharded collection, as in the following example:

db.myShardedCollection.getShardDistribution()

In the following example, the collection has two shards. The output displays both the individual shard distribution information as well the total shard distribution:

Shard <shard-a> at <host-a>
 data : <size-a> docs : <count-a> chunks : <number of chunks-a>
 estimated data per chunk : <size-a>/<number of chunks-a>
 estimated docs per chunk : <count-a>/<number of chunks-a>

Shard <shard-b> at <host-b>
 data : <size-b> docs : <count-b> chunks : <number of chunks-b>
 estimated data per chunk : <size-b>/<number of chunks-b>
 estimated docs per chunk : <count-b>/<number of chunks-b>

Totals
 data : <stats.size> docs : <stats.count> chunks : <calc total chunks>
 Shard <shard-a> contains  <estDataPercent-a>% data, <estDocPercent-a>% docs in cluster, avg obj size on shard : stats.shards[ <shard-a> ].avgObjSize
 Shard <shard-b> contains  <estDataPercent-b>% data, <estDocPercent-b>% docs in cluster, avg obj size on shard : stats.shards[ <shard-b> ].avgObjSize

For your further ref here, here


Why can I select all fields when grouping by primary key but not when grouping by another column

id is a primary key.
As far as I remember, this is actually a legal query according to ANSI/ISO SQL.
      Grouping by primary key results in a single record in each group which is logically the same as not grouping at all / grouping by all columns, therefore we can select all other columns.

create table t (id int primary key,c1 int,c2 int)
insert into t (id,c1,c2) values (1,2,3),(4,5,6);
select * from t group by id;

+----+----+----+
| id | c1 | c2 |
+----+----+----+
| 1  | 2  | 3  |
+----+----+----+
| 4  | 5  | 6  |
+----+----+----+

Reference given by @a_horse_with_no_name

https://www.postgresql.org/docs/current/static/sql-select.html#SQL-GROUPBY

  When GROUP BY is present, or any aggregate functions are present, it
  is not valid for the SELECT list expressions to refer to ungrouped
  columns except within aggregate functions or when the ungrouped column
  is functionally dependent on the grouped columns, since there would
  otherwise be more than one possible value to return for an ungrouped
  column. A functional dependency exists if the grouped columns (or a
  subset thereof) are the primary key of the table containing the
  ungrouped column.

While logically we would expect UNIQUE NOT NULL to follow the same behaviour, it applies only for PK (as described in the documentation)

create table t (id int unique not null,c1 int,c2 int);
insert into t (id,c1,c2) values (1,2,3),(4,5,6);
select * from t group by id;

  [Code: 0, SQL State: 42803]  ERROR: column "t.c1" must appear in the
  GROUP BY clause or be used in an aggregate function


This doesn't exactly answer your question, but Paul Randal included a very helpful query for aggregating wait stats with one of his surveys last December.  I have shamelessly copied and pasted it here, but am providing a link to his site, because there is a wealth of knowledge to be had from it.  Wait Stats

WITH Waits AS
    (SELECT
        wait_type,
        wait_time_ms / 1000.0 AS WaitS,
        (wait_time_ms - signal_wait_time_ms) / 1000.0 AS ResourceS,
        signal_wait_time_ms / 1000.0 AS SignalS,
        waiting_tasks_count AS WaitCount,
        100.0 * wait_time_ms / SUM (wait_time_ms) OVER() AS Percentage,
        ROW_NUMBER() OVER(ORDER BY wait_time_ms DESC) AS RowNum
    FROM sys.dm_os_wait_stats
    WHERE wait_type NOT IN (
        'CLR_SEMAPHORE', 'LAZYWRITER_SLEEP', 'RESOURCE_QUEUE', 'SLEEP_TASK',
        'SLEEP_SYSTEMTASK', 'SQLTRACE_BUFFER_FLUSH', 'WAITFOR', 'LOGMGR_QUEUE',
        'CHECKPOINT_QUEUE', 'REQUEST_FOR_DEADLOCK_SEARCH', 'XE_TIMER_EVENT', 'BROKER_TO_FLUSH',
        'BROKER_TASK_STOP', 'CLR_MANUAL_EVENT', 'CLR_AUTO_EVENT', 'DISPATCHER_QUEUE_SEMAPHORE',
        'FT_IFTS_SCHEDULER_IDLE_WAIT', 'XE_DISPATCHER_WAIT', 'XE_DISPATCHER_JOIN', 'BROKER_EVENTHANDLER',
        'TRACEWRITE', 'FT_IFTSHC_MUTEX', 'SQLTRACE_INCREMENTAL_FLUSH_SLEEP',
        'BROKER_RECEIVE_WAITFOR', 'ONDEMAND_TASK_QUEUE', 'DBMIRROR_EVENTS_QUEUE',
        'DBMIRRORING_CMD', 'BROKER_TRANSMITTER', 'SQLTRACE_WAIT_ENTRIES',
        'SLEEP_BPOOL_FLUSH', 'SQLTRACE_LOCK')
    )
SELECT
    W1.wait_type AS WaitType,
    CAST (W1.WaitS AS DECIMAL(14, 2)) AS Wait_S,
    CAST (W1.ResourceS AS DECIMAL(14, 2)) AS Resource_S,
    CAST (W1.SignalS AS DECIMAL(14, 2)) AS Signal_S,
    W1.WaitCount AS WaitCount,
    CAST (W1.Percentage AS DECIMAL(4, 2)) AS Percentage,
    CAST ((W1.WaitS / W1.WaitCount) AS DECIMAL (14, 4)) AS AvgWait_S,
    CAST ((W1.ResourceS / W1.WaitCount) AS DECIMAL (14, 4)) AS AvgRes_S,
    CAST ((W1.SignalS / W1.WaitCount) AS DECIMAL (14, 4)) AS AvgSig_S
FROM Waits AS W1
    INNER JOIN Waits AS W2 ON W2.RowNum <= W1.RowNum
GROUP BY W1.RowNum, W1.wait_type, W1.WaitS, W1.ResourceS, W1.SignalS, W1.WaitCount, W1.Percentage
HAVING SUM (W2.Percentage) - W1.Percentage < 95; -- percentage threshold
GO


Composite Key with a column that auto increments and reset when foreign key value changes

Automatic?

Unknown.  Most likely, not. (as in: no out-of-the-box database features)

Manual?

Yes.

You need to keep track of "last number used" in a table. eg

create table raf (
  raf_id        int, -- identity/autoincrement
  last_number   int default 4000 not null,
  constraint raf_pk primary key (raf_id)
);

From there, your process needs to go like this:

grab a lock for RAF_ID
"calculate" the next number.
Update the RAF table.
Use that value in the other table
COMMIT the data
Release the lock.

Depending on your RDBMS, some steps can be combined into a single step.  The HOWTO do these things (eg Best practice for "grab a lock") can also be RDBMS dependent.

Notes

The "grab a lock" step will serialize your code.  This is bad for databases.  This requirement/process will limit how fast you can add new rows.  I suggest you look at alternative methods (eg a VIEW that uses RANK() ).

Can this be implemented in a TRIGGER?  Maybe.


The Progress backup is a block-level image of the database & bi file, so there's no way to only restore certain tables. You'll need to

restore the db on a machine with enough space for the old db and the smaller db,
create an empty version of the db,
dump the required tables from the restored db,
load that table data into the new db file,
then copy the smaller db to your target machine


Truncate a table with 17 billion rows in an AG

Logging Extents
The amount of log data generated (and thus sent over the network to your other AG nodes) depends on how big the rows are in your 17 billion row table are.  TRUNCATE will definitely be a tiny amount compared to doing a DELETE.  But it could still be significant, depending on your infrastructure and expectations.
Consider the dbo.Votes table in the Stack Overflow sample database:

Each row is 28 bytes.  A page in SQL Server is 8 KB (8,192 bytes), so you can fit around 292 rows on a page.  This isn't exactly correct, since there is overhead for both pages and rows, but it's a decent approximation for this example.
That means it takes about 58,219,178 pages to hold all 17 billion rows.  When doing a DROP or TRUNCATE, a background task deallocates extents (groups of 8 pages).  Each of these deallocations is logged.  This means about 7,277,397 log records will be created by truncating this table.
Testing dbo.Votes
I tried this out on my copy of that sample database, after setting the recovery model to full, and taking full and log backups to initialize the backup chain.  The dbo.Votes table has 10,146,802 rows.  Based on our previous calculations, this should be around 34,749 pages, or 4,343 extents.
In reality, that table uses 47,721 pages allocated to it (because of the overhead mentioned before), which is 5,965.125 extents.
Now I'll TRUNCATE the table:
TRUNCATE TABLE dbo.Votes;

This completes instantly, but I ended up with 17,605 log records.  It looks like there are really 3 log records per extent (2 for updating IAM and GAM pages, one for updating the PFS page to deallocate the data page).
Those log records only totaled up to ~1.28 MB of log file usage.  But your real table has 1,600 times the rows as this, and your row sizes might be bigger.  This could mean over 1 GB of log data generated and sent to each replica over the network.
The amount of data grows further if you have nonclustered indexes on this table, which are logged in the same way.
Row Size Makes a Big Difference
Another case study could be the dbo.Comments table.  It has 3,907,472 rows, but each row is 1,424 bytes long (maximum - the Text column is nvarchar(700)).
Despite having significantly fewer rows than dbo.Votes, this table has 176,722 pages allocated to it.  TRUNCATEing dbo.Comments results in 63,792 log records and 4.86 MB of log data.
If your real row size is more in this ballpark, that could be over 7.5 GB of log data.
What to Do
Maybe your infrastructure and log files can handle several GB of data easily - if you have a 17 B row table, it seems like they should!  But I thought it would be worthwhile to mention that the amount of traffic is not necessarily insignificant, since the existing answers didn't bring this up.
Test in a non-prod environment if you can.  Measure the log file usage before and after, and make sure your prod infrastructure is set up to handle that amount of data.  Make certain that the log file has been presized to handle this truncate - having an autogrowth occur in the middle of this operation will slow things down a lot, and cause blocking.
If you can't test, do your best to estimate what the impact will be.  Use a query like this one to get the number of pages in the table.  Then divide that by 8 (to get the number of extents) and multiply by 3 to get the approximate number of log records.
My testing has had average log record sizes of around 70 bytes, but I don't know if this is typical.  But you could try multiplying the approximate log records by 70 to get the number of potential log bytes produced by the truncate.
With the AG in the mix, you could also play around with log stream compression.  I haven't used these trace flags, so I'm really just mentioning that it's another knob you can tune.

If you find that the TRUNCATE approach is too unpredictable, or too much for your systems, you could always use a normal DELETE in batches.  This uses more log in total, but you could spread it out over whatever period of time you like.  However, make sure you Take Care When Scripting Batches if you go that route.


Possible duplicate https://stackoverflow.com/questions/1545571/how-do-i-make-a-composite-key-with-sql-server-management-studio

It sounds like what you are wanting is a composite key.  There is a T-SQL example in the link provided.


3NF Decomposition - I'm not sure about this example

The theory is all fine and good, but it only starts to really make sense once you understand the practice. The Lawyer's version of the first three Normal Forms is:

The Key - there must be a Primary key for every relation being normalized.
The Whole Key - There must not be any functional dependencies of attributes on any proper subset of the Primary Key.
And Nothing but The Key - There must not be any functional dependencies of attributes on non-key attributes.

In practice relations frequently have multiple candidate keys, and these rules must apply to all of them in turn.

Also, when Normalizing one must determine the minimal key(s) for each relation, and use those rather than any Super Key.

Your lecturer didn't remove the fifth column altogether because the functional dependency in that column still exists, and must still be accounted for in the Normalization process.

Update:

The FD AD->C doesn't disappear by virtue of recognizing ADC as a subset of CDA; neither is it sensible to have two relations ADC and CDA both in the model as this is a redundancy of exactly the sort Normalization is designed to eliminate.


MySQL replication: 'Duplicated entry for PRIMARY key'

ASPECT #1 : Replication
I don't think that
replicate-wild-do-table = db_backup.%
replicate-rewrite-db = db->db_backup

belong together.
Other people have wondered about this as well

Jun 14, 2012 : Configured MySQL replication but its not working
Jun 13, 2012 : What is the difference between 'replicate-rewrite-db' and 'replicate-do-db ' during mysql replication ?
May 31, 2012 : Mysql replicate-rewrite-db not working

The problem stems from the order replication rules are processed. According to the MySQL Documentation on Replication Rules:

If any --replicate-rewrite-db options were specified, they are applied before the --replicate-* filtering rules are tested.

Even the MySQL Documentation on replicate-rewrite-db says:

The database name translation is done before the --replicate-* rules are tested.

The replicate-wild-do-table is enforced after the rewrite. It would not be surprising if this ordering somehow imposed an INSERT into a table that has data already.
You are probably asking how did the data get there ?
ASPECT #2 : mysqldump
Doing mysqldump --single-transaction would seem to be the greatest way to point-in-time dumps of data. Unfortunately, mysqldump --single-transaction has an Achilles' Heel : ALTER TABLE. If a table is subject to any ALTER TABLE commands, such as a DROP TABLE and CREATE TABLE, that can break the integrity of the transaction the mysqldump was trying to do the dump in. Truncating a table (which is DDL in the MySQL Universe) and dropping and adding indexes can also be as disruptive.
You can find more information on that from MySQL Performance Blog's Best kept MySQLDump Secret. I actually addressed this point in a past question describing 12 commands that can break the integrity of a mysqldump's transaction : MySQL backup InnoDB
CAVEAT

Personally, I would switch binary-format to STATEMENT
Like I said in MySQL backup InnoDB, you should have another slave (without any replication filters) dedicated to doing mysqldumps.
63000+ seconds behind? You should look into doing parallel table dumps : How can I optimize a mysqldump of a large database?

EPILOGUE
One or both of the aspects may have contributed to letting a row slip in during the mysqldump that should not have existed due to either the rewrite rules or the isolation of the mysqldump being overridden.
SUGGESTIONS
I would do a mysqlbinlog dump of all the relay logs since the start of the mysqldump to see all INSERTs that the Slave will process and see if those rows already exist on the Slave. If they do, you could probably do two things:
1 : Skip all the Duplicate Key errors
Simply add this to my.cnf on the Slave
[mysqld]
slave-skip-errors=1062
skip-slave-start

and restart mysql. Then, run START SLAVE;
all the duplicate-key errors will get bypassed. When Seconds_Behind_Master gets to 0, remove those lines and restart mysql.
2 : Download percona tools
The tools you need are

pt-table-checksum
pt-table-sync

Use these to find the differences in the Slave, and then correct them


I have a database backup (.bak) from SQL Server v7, how do I convert it for load into v10?

You can restore a SQL 7 backup on a SQL 2005 instance, then backup/restore or detach/attach to SQL 2012 or 2014.  "version 10" is SQL 2008, so you wouldn't want to stop there, probably.  You can get old installs of SQL from MSDN Subscriber Downloads, or the Express Edition is still available for download.


Performance difference for COALESCE versus ISNULL?

ISNULL is Sybase/SQL Server specific
COALESCE is portable

Then

ISNULL take 2 arguments
COALESCE takes 1-n arguments

Finally, and the fun bit. The result datatype and length/precision/scale

ISNULL is the same as the first argument
COALESCE is the highest according to datatype precedence

This last bit is why ISNULL is usually used because it's more predictable (?) and COALESCE can add unintended data type conversions: which is where the "it's slower" bit comes from

DECLARE @len10 varchar(10); --leave it NULL
SELECT
    ISNULL(@len10, '0123456789ABCDEF'),     -- gives 0123456789
    COALESCE(@len10, '0123456789ABCDEF');   -- gives 0123456789ABCDEF

All datatypes being the same, you won't see any practical difference...


Query of sys.login_token doesn't include particular windows group while connect within workgroup

The problem solved after I remove logins and associated users in SQL Server then add them back again.
Have no clue what went wrong but a record for people who might run into the same issue.


Postgres still connecting via SSL - despite invalid certificates

The documentation says:

  By default, PostgreSQL will not perform any verification of the server certificate.

In other words, SSL is used for encryption.

  […]
  To allow the client to verify the identity of the server, place a root certificate on the client and a leaf certificate signed by the root certificate on the server.
  […]
  Once a chain of trust has been established, there are two ways for the client to validate the leaf certificate sent by the server. If the parameter sslmode is set to verify-ca, libpq will verify that the server is trustworthy by checking the certificate chain up to the root certificate stored on the client. If sslmode is set to verify-full, libpq will also verify that the server host name matches the name stored in the server certificate.


How can I efficiently downsize some Unicode fields?

Regarding:

  I tested doing an “add new column, set new=old, drop old, rename old to new” for both of the main two fields I want to convert from nvarchar(max) to varchar(max), and it took 81 minutes on our test server ... before running out of disk space ... it was too slow.

and

  Another technique I plan to try is to create the new table def in a new schema, select into it from the old table, then move tables amongst schema and delete the old table.

Generally speaking, making a copy of the table with the ideal schema is my preferred approach. But, if you only now have maybe enough space to convert the two columns, are you sure you have enough space to make a copy of the entire table?

Also, the new table would just need to have a different name. It wouldn't need to be in a different schema.

Since you are on Enterprise Edition, have you considered or even looked at enabling Data Compression? Not only would it have the effect you are looking for on the NCHAR / NVARCHAR fields, but it would also save space on other fields with other types as well.

There are two types of Compression: Row and Page. You should read up on them and run the stored procedure that estimates what your savings would be.

Enabling compression can be done as an ONLINE operation, but might require a bit of disk space. If you do not have the available space for this, then you might could consider a hybrid approach where you build a copy of the table as TableNEW, and have the clustered index already created, and created with Compression enabled. Then you should be able to slowly fill TableNEW and the data will compress as it goes in. Of course, you will want to use INSERT INTO...SELECT to do it in batches. And the full benefit of the compression might not be realized until after you drop the original table and do a full rebuild on the Clutered Index of TableNEW.

Main MSDN documentation for Data Compression.
Enable Compression on a Table or Index
sp_estimate_data_compression_savings (mentioned in the "Enable Compression" document listed directly above)

And keep in mind that there are scenarios where you might not save that much space, or saving the space isn't worth the increase in CPU activity. But, that all depends on a lot of factors so it is really something that should be tested on your system.

You could always take the approach of:

Enable compression, either directly to the current table as ONLINE (if there is enough space to support it), or into a separate table that has Compression enabled.
IF you find that CPU actually has increased beyond the benefit of the space savings, then you have the option of building the table again with regular VARCHAR fields and no Compression. Because compression was already enabled, you should definitely have room for this now.

But again, like anything we do, it should be tested. I have heard for years how horrible "XML parsing" is on CPU, and how bad Compression is supposed to be, but in practice, those concerns are quite often overstated. The only way to know is to test on your system. (ps, just in case it is not clear being a text-only medium, these final statements are not attacking what @Kin said in his answer about needing to be cautious of CPU activity increase. He is correct, at least to a degree. I am just reminding everyone to be sure to put everything into perspective of current hardware and software and system setup).


MySQL starting 2 processes that can't be used

The name of the first one is join_buffer_size.  And 256K (not M) has been the default since 5.6.6.  Using 256M is likely to blow out RAM all too easily.  This would either slow down mysqld (due to swapping) or crash it (out of memory).

I don't know about the .so, but I would certainly suspect it.

As for 2 processes -- On some Operating systems, several processes, the number based on some configuration values, probably thread_cache_size and max_connections, plus usage patterns.


Can I install sql server on Win XP machine with user profile on D drive?

This Microsoft support document might be of some help in regards to that particular error:
http://support.microsoft.com/kb/2387754

  The Network Service account does not have permissions on the following folder:

  C:\Documents and Settings\NetworkService\Application Data\Microsoft\Protect

It might need permission to that particular directory on your D Drive


How to tune a stored procedure that is fast on test data but slow in production

"Performance" is a much broader topic than is being assumed here. There are quite a few factors that influence performance:

The Data Model: Are you using appropriate data types? Do you have Primary and Foreign Keys defined? Do you have repeating string values (bad) instead of lookup tables? Do you have an appropriate structure or something that is convoluted and/or over-engineered
Number of rows in the table(s): All things being equal, the Query Optimizer can choose to do things differently depending on the overall row-count of the table(s) involved in each query. Hence, testing in environments with radically different data volumes (not just distribution differences) can only test functionality, not performance.
Distribution of data values: This obviously affects statistics associated with indexes, but assuming that the "auto-create statistics" option is enabled (and it is by default), then any column used for filtering and/or sorting can have system-created statistics associated with it.
Indexes: The fact that indexes help find data should be obvious, but incorrectly defined indexes can sometimes hurt. Also, too many indexes can easily hurt DML operations (especially in relation to available hardware and contention).
Up-to-date statistics: Even if you have the same indexes between environments, and the exact same data, if the statistics are out of date then you can still see performance issues if the Query Optimizer doesn't know to use a certain index, or accesses it incorrectly due to obsolete row estimates.
Contention: What other queries are running on the system? Production will naturally have a lot more concurrent usage than a Development / QA / UAT system. More concurrent queries means more locking and less available hardware resources.
Code: There are usually several ways to achieve a goal, but they can have radically different requirements. There are many ways to write queries across the spectrum of horrible to great. And there are ways of structuring operations that are far better or worse than other methods. Often enough, even small changes in query and/or process structure can have major impact, regardless of any of these other factors.
Hardware: RAM (how much exists, how fast is it, and how much is available), CPU (how many cores, how fast are they, how many are allocated to SQL Server, how much is available), and Disk I/O (type of disk: SATA vs SAS vs ?, NAS vs SAN vs Local vs ?, Raid level, SSD vs non-SSD, etc, available throughput). For all of these areas, other processes running on the system can take up RAM, CPU, and I/O, which affects how much of the total amount is available to SQL Server to use. Meaning, tons of CPU does not help if most of it is being used by one or more programs outside of SQL Server.
Configuration: Things like Snapshot Isolation, Async Update Statistics, Auto-growth, Auto-shrink, etc can impact performance. Are they configured the same between Dev / QA / Production environments? Are they configured correctly to begin with?

Generally speaking, even horribly inefficient code and/or structure will appear to work quickly enough in most Development and QA environments. With only 100 - 1000 rows of data, poor choices in data modeling and/or coding will be hidden.

At the very least you should have, in a non-Production environment, the exact same structure and configuration (tables, indexes, database, server, etc) that Production has. At this stage you should be focused on a good, scalable data model as well as good T-SQL coding.

Indexes are a bit trickier as they require a representative data set (volume and distribution) since that will impact the execution plan. This can be done in one or more testing environments (QA, UAT, ?). If it is not possible to get this data into a lower environment due to sheer size, then this will have to be done in Production.

But, even with this, true performance tuning can really only be done in Production since that is "reality" whereas the other environments are not (unless you somehow manage to duplicate not only the hardware, but also the usage / load). So you do your best, educated guess in lower environments but you will only know in the end once it is in Production. For testing code changes, I have found it helpful to create a global temporary stored procedure so that I can do side-by-side comparisons in the ideal environment.


Don't mix

CASE value WHEN [compare_value] THEN result
          [WHEN [compare_value] THEN result ...]
          [ELSE result] END

and

IF(expr1,expr2,expr3)

syntaxes.

See the manual


How to get non-deleted messages for one user_id if the same user_id appears in two tables at same time?

The problem is query #4 needs a small adjustment. Now the query is:

SELECT p.*
    , rcp.*
    , msg.*
    , msg.id as message_id
FROM default_messages msg
    LEFT JOIN default_recipient rcp ON (msg.id = rcp.message_id)
    LEFT JOIN default_profiles p ON (p.user_id = msg.sender_user_id)
WHERE (msg.sender_user_id = 1 AND msg.deleted = 0)
       OR (rcp.user_id = 1 AND rcp.deleted = 0)
ORDER BY msg.date DESC;


1st : We JOIN tables/view or sub-query that returns a result-set.

2nd : ON condition is used to get the result matching given condition.
      Note: CROSS JOIN don't need ON condition.

I tried to correct you query. Hope this query will help you out to get desired result.

WITH CTE AS

(SELECT eventdate, eventtime, "alarmSeverity", eventposition, eventtype FROM alarm1 UNION
SELECT eventdate, eventtime, "alarmSeverity", eventposition, eventtype FROM alarm2 UNION
SELECT eventdate, eventtime, "alarmSeverity", eventposition, eventtype FROM alarm3
)

SELECT gs.h hours, severity."alarmSeverity", COUNT(CTE.eventtime)
     FROM GENERATE_SERIES(0, 23, 1) gs(h)
     CROSS JOIN (SELECT DISTINCT "alarmSeverity"
                    FROM CTE
                    WHERE "alarmSeverity" != 0
                ) AS severity
LEFT JOIN CTE
ON          extract(hour FROM CTE.eventtime) = gs.h AND CTE."alarmSeverity" = severity."alarmSeverity"
            AND CTE.eventdate+CTE.eventtime BETWEEN TIMESTAMP '2019-07-01T18:30:00.000Z' AND TIMESTAMP '2019-07-02T18:29:59.999Z'
GROUP BY gs.h, severity."alarmSeverity"
ORDER BY gs.h, severity."alarmSeverity";

Thanks!


Rollback to savepoint with repeatable read isolation

In PostgreSQL, a REPEATABLE READ transaction uses a fixed snapshot of the database.

So if a concurrent data modification caused a serialization error, then restoring to a savepoint and repeating the actions will always result in the same serialization error. You will still see the same state of the database, and if you update a row that has been modified after the snapshot was taken, that will still be the case.

You actually have to roll back and retry the whole transaction.

This is working as designed.

The SQL standard doesn't describe when exactly serialization errors should be thrown, because it does not deal with the implementation specifics of individual RDBMS. It says:

  The execution of a <rollback statement> may be initiated implicitly by an SQL-implementation when it detects
  the inability to guarantee the serializability of two or more concurrent SQL-transactions. When this error occurs,
  an exception condition is raised: transaction rollback – serialization failure.

So a database is at liberty to throw a serialization error whenever it thinks fit.


How can I specify the DDL of the index that is implicitly created when creating a materialized view log?

This is in fact a bug: Materialized View Log (Mlog$) Index Not Created in its Default Tablespace (Doc ID 1959658.1). Excerpt:

  The index created when a materialized view log is created appears to be created in the user's default tablespace rather than the same tablespace as the MV log table.
  Additionally, if the user has no quota on their default tablespace, the index is not created and an error is not reported, leading to possible performance issues.

There is no workaround stated though, and no indication that you can force the tablespace yourself (the create materialized view log statements has no such option).

The solution section of that article states that it will be fixed in 12.2 (currently scheduled for first half of 2016), but that you can request a backport from Oracle support. So the best course of action might well be to open an SR and request that backport or some other workaround.

The only possible workaround that I know would be to change the default tablespace for the user before the mview log creation, then change it back. This is obviously not good – who knows what other objects could be created in the mean time – but might be all you've got.

@a_horse_with_no_name has different suggestion: rebuild the index after the creation is done. This allows you to change the tablespace (and other storage options):

SQL> alter index "I_MLOG$_FOO" rebuild tablespace YOURTBS online ;


How to trace security_error_ring_buffer_recorded errors in SQL Server 2014 Express

Take a look on this article: Troubleshooting ImpersonateSecurityContext in RING_BUFFER_SECURITY_ERROR

  The error message 5023 details are:

  “The group or resource is not in the correct state to perform the
  requested operation”

  According to Microsoft documentation “The ImpersonateSecurityContext
  function allows a server to impersonate a client by using a token
  previously obtained by a call to AcceptSecurityContext (General) or
  QuerySecurityContextToken. This function allows the application server
  to act as the client, and thus all necessary access controls are
  enforced.”

  Basically this function is called when impersonation is required. To
  do so, there must already be a valid context handle.

  Analyse error logs to identify Login failures and report on the state
  number.

  Some reasons I've spotted are:

  1) A service (set as Local System) on a server attempting to log on to
  SQL Server and not having access

  2) IP registered on the DNS server with the same FQDN

You can also check SQL Server error log to check if SPN was successfully registrated.


Well as McNets answered all i had to do is alter the script.

ALTER TRIGGER [dbo].[TRG]
ON [dbo].[Table] AFTER DELETE
NOT FOR REPLICATION
AS
    BEGIN
        BLA BLA BLA
    END


The result you've got return seems very normal to me.

ANY will return TRUE when at least one value returned by the subquery compares positively.
Logically, you could compare this to using multiple OR statements, one OR comparison for each value returned by the subquery.

ALL on the other hand requires each value returned by the subquery to compare positively.
Logically, you could compare this to using multiple AND statements, one AND statement for each value returned by the subquery.

You stated that the subquery originally returned a single record. Therefore it didn't matter whether you used ALL or ANY. As soon as you added another record, there is a difference though. At that moment there are multiple values (ID's) in the subquery to compare to and they will logically be evaluated as OR/AND statements

Example

CREATE TABLE dbo.Tbl (ID int)
GO

INSERT dbo.Tbl VALUES (1),(2),(3)
GO

CREATE TABLE dbo.SubTbl (ID int)
GO

INSERT dbo.SubTbl VALUES (1),(2)
GO

SELECT ID
FROM dbo.Tbl
WHERE ID = ANY(SELECT ID FROM dbo.SubTbl)
GO

-- Has the same result as ANY:
SELECT ID
FROM dbo.Tbl
WHERE ID = 1 OR ID = 2
GO

SELECT ID
FROM dbo.Tbl
WHERE ID = ALL(SELECT ID FROM dbo.SubTbl)
GO

-- Has the same result as ALL:
SELECT ID
FROM dbo.Tbl
WHERE ID = 1 AND ID = 2
GO


Why would an UPDATE SET REPLACE() statement match rows, but change none and give no warnings?

REPLACE does not play with wildcards that way. I think you meant:

UPDATE [table]
  SET [column] = REPLACE([column],'TLD.com','TLD.org')
  WHERE [column] LIKE '%TLD.com%';

You have no WHERE clause, so it tried to update 618 rows, but it did not find any instances of %TLD.com% in that column. To see which rows should be affected, run a SELECT instead:

SELECT [column], REPLACE([column], 'TLD.com', 'TLD.org') AS new_value
  FROM [table]
  WHERE [column] LIKE '%TLD.com%';


DBI1011I Error when using db2iupdt to enable Text Search

According to the syntax diagram at http://pic.dhe.ibm.com/infocenter/db2luw/v10r5/topic/com.ibm.db2.luw.admin.cmd.doc/doc/r0002060.html, you should enclose the text search options in double quotation marks:

-j "TEXT_SEARCH,db2j_db2inst1,55000"

Don't forget to escape them in the shell.


Changes the default database owner in SQL Server 2008

ALTER AUTHORIZATION ON DATABASE::MyDB TO sa;

Note, this doesn't add the new owner to db_owner (sa + sysadmin map to dbo user which is different)


SELECT st.topup_nr, n.name AS network, p.name AS supplier
     , st.topup_sum, st.topup_qty
FROM  (
   SELECT t.topup_nr, s.network_id, s.supplier_id
        , sum(t.amount) AS topup_sum
        , count(*) AS topup_qty
   FROM  (
      SELECT simcard_id, amount
           , row_number() OVER (PARTITION BY simcard_id, ORDER BY date) AS topup_nr
      FROM   simcard_topup
      ) t
   JOIN   simcard_simcard s ON s.id = t.simcard_id  -- or so I assume
   GROUP  BY 1,2,3
   ) st
JOIN   simcard_network  n ON n.id = st.network_id
JOIN   simcard_supplier p ON p.id = st.supplier_id
ORDER  BY st.topup_nr, st.topup_qty DESC NULLS LAST
                     , st.topup_sum DESC NULLS LAST;

Notes

The core feature is the window function row_number() in the inner subquery to number topups per simcard. This makes aggregating for 1st, 2nd etc. topup  in the outer query simple.
For lack of information, assuming to join simcard_simcard to simcard_topup on s.id = t.simcard_id, though you also have on t.iccid_id = s.id in your code, which seems backwards ...
Don't use a basic type name like date as column name.
Why do you have CAST(t.date AS DATE) in your code? A date should be a date, so no need to cast it to date. Also, it seems we only need the date for ranking topups.
Counting per (network_id, supplier_id) and sorting the result by ... topup_qty DESC, topup_sum DESC, so the combination with the most topups comes first. NULLS LAST is just to be safe against NULL values.


Change the threshold to 23 hours 15 minutes...I assume the restore job is the one alerting, correct?  I believe there is an alert on all three.   possibly just backup and restore, but I don't have a LS setup in front of me.


Does SQL Server 2016 still contain sqlmaint.exe?

Microsoft documentation mentions they're removing it, it says "Next Version" but i assume this was written for a previous version. They advise to use maintenance plans in the future.

Microsoft Documentation for sqlmaint

Edit: Last update on that article was 03/14/2017. But given all the examples point to it being originally written with SQL Server 2008 in some and edited with later versions as examples (it's all over the place to be honest), it's probably safe to say the note at the top is wrong and it's actually removed already.


Setting up relationships on a gaming schema with multiple locales

Your approach is the correct one if the typical workload consists of small transactions that access only a few rows at a time (OLTP).

The extra join won't hurt, because it will be a nested loop join. Databases are optimized for stuff like that.

Any small performance loss will be outweighed by the advantage of having foreign keys to guarantee data integrity.


SQL Server 2008 R2 replication high delivery latency

Below script will help you find out how many undistributed commands are there. Also, do you see any errors in sql server or windows logs ? Is it just slow or any failures reported ?

SELECT ( CASE
           WHEN mdh.runstatus = '1' THEN 'Start - ' + CAST(
                                         mdh.runstatus AS VARCHAR)
           WHEN mdh.runstatus = '2' THEN 'Succeed - ' + CAST(
                                         mdh.runstatus AS VARCHAR)
           WHEN mdh.runstatus = '3' THEN 'InProgress - ' + CAST(
                                         mdh.runstatus AS VARCHAR)
           WHEN mdh.runstatus = '4' THEN 'Idle - ' + CAST(mdh.runstatus AS
                                                          VARCHAR)
           WHEN mdh.runstatus = '5' THEN 'Retry - ' + CAST(
                                         mdh.runstatus AS VARCHAR)
           WHEN mdh.runstatus = '6' THEN 'Fail - ' + CAST(mdh.runstatus AS
                                                          VARCHAR)
           ELSE CAST(mdh.runstatus AS VARCHAR)
         END )                                   [Run Status],
       mda.subscriber_db                         [Subscriber DB],
       mda.publication                           [PUB Name],
       RIGHT(LEFT(mda.name, Len(mda.name) - ( Len(mda.id) + 1 )),
       Len(LEFT(mda.name, Len(mda.name) - ( Len(mda.id) + 1 ))) - (
       10 + Len(mda.publisher_db) + ( CASE
                                          WHEN mda.publisher_db = 'ALL' THEN 1
                                          ELSE Len(mda.publication) + 2
                                        END ) )) [SUBSCRIBER],
       CONVERT(VARCHAR(25), mdh.[time])          [LastSynchronized],
       und.undelivcmdsindistdb                   [UndistCom],
       mdh.comments                              [Comments],
       'select * from distribution.dbo.msrepl_errors (nolock) where id = ' +
       CAST(mdh.error_id AS VARCHAR(8))          [Query More Info],
       mdh.xact_seqno                            [SEQ_NO],
       ( CASE
           WHEN mda.subscription_type = '0' THEN 'Push'
           WHEN mda.subscription_type = '1' THEN 'Pull'
           WHEN mda.subscription_type = '2' THEN 'Anonymous'
           ELSE CAST(mda.subscription_type AS VARCHAR)
         END )                                   [SUB Type],
       mda.publisher_db + ' - ' + CAST(mda.publisher_database_id AS VARCHAR)
                                                 [Publisher DB],
       mda.name
       [Pub - DB - Publication - SUB - AgentID]
FROM   distribution.dbo.msdistribution_agents mda
       LEFT JOIN distribution.dbo.msdistribution_history mdh
         ON mdh.agent_id = mda.id
       JOIN (SELECT s.agent_id,
                    maxagentvalue.[time],
                    SUM(CASE
                          WHEN xact_seqno > maxagentvalue.maxseq THEN 1
                          ELSE 0
                        END) AS undelivcmdsindistdb
             FROM   distribution.dbo.msrepl_commands t (nolock)
                    JOIN distribution.dbo.mssubscriptions AS s (nolock)
                      ON ( t.article_id = s.article_id
                           AND t.publisher_database_id = s.publisher_database_id
                         )
                    JOIN (SELECT hist.agent_id,
                                 MAX(hist.[time]) AS [time],
                                 h.maxseq
                          FROM   distribution.dbo.msdistribution_history hist (
                                 nolock)
                                 JOIN (SELECT agent_id,
                                              Isnull(MAX(xact_seqno), 0x0) AS
                                              maxseq
                                       FROM
                                 distribution.dbo.msdistribution_history (
                                 nolock)
                                       GROUP  BY agent_id) AS h
                                   ON ( hist.agent_id = h.agent_id
                                        AND h.maxseq = hist.xact_seqno )
                          GROUP  BY hist.agent_id,
                                    h.maxseq) AS maxagentvalue
                      ON maxagentvalue.agent_id = s.agent_id
             GROUP  BY s.agent_id,
                       maxagentvalue.[time]) und
         ON mda.id = und.agent_id
            AND und.[time] = mdh.[time]


Is it possible to retrieve a history of the remote connections to the MongoDB including the IP addresses and the executed commands?

If you haven't configured any extra logging, profiling, or auditing in advance, the default MongoDB log level will only include limited information such as connections, slow commands, and database drops rather than details of every command that was run. Logging every command will add significant write overhead for an active deployment.

However, assuming you have retained sufficient logs (and haven't suppressed connection information with the quiet logging mode), you should be able to identify when your server was restarted without authentication as well as the remote IPs that connected. You'll have to grep or parse available information out of the MongoDB log file(s).

Some example log search strings that might help construct a timeline of events:

[initandlisten] options -- shows the configuration options (such as authentication) used when your mongod server is started (or restarted).
connection accepted from --  includes the IP address for remote clients as well as a connection ID (for example: [conn123]) that you can use to find related log lines.
received client metadata -- provides (optional) client metadata (such as driver name and version) for a new connection.
dropDatabase -- indicates a database drop command was issued.

For a quick summary of IPs that connected you could also use mloginfo --connections.

To mitigate future issues, please review the Security Checklist in the MongoDB documentation.


Comparing the location of values in different tables

Seems to me that a combination of an INNER JOIN and EXISTS should do:

SELECT DISTINCT(s.pizza)
FROM dbo.Sells s
INNER JOIN dbo.Restaurants r
    ON s.rname = r.rname
WHERE EXISTS(SELECT 1 FROM dbo.Customers
             WHERE cname = 'bob'
             AND area = r.area)
;


Table design when one data is dependant of another and the second one has repeated values

With the clarifications in your comments, that there are only 2 levels of products, e.g. only Products and SubProducts and no subproduct is related to two or more products, your design is fine.

I would only add two unique constraints on table SubProducts, on (IDproduct, IDsubProduct) and on (IDproduct, Name) - or make one of them PRIMARY KEY and the other UNIQUE:

CREATE TABLE Products
( IDproduct INT NOT NULL
, Name VARCHAR(100) NOT NULL

, CONSTRAINT Products_PK
      PRIMARY KEY (IDproduct)
, CONSTRAINT Product_Name_UQ
      UNIQUE (Name)
) ;

CREATE TABLE SubProducts
( IDproduct INT NOT NULL
, IDsubProduct INT NOT NULL
, Name VARCHAR(100) NOT NULL

, CONSTRAINT SubProducts_PK
      PRIMARY KEY (IDproduct, IDsubProduct)
, CONSTRAINT SubProduct_Name_UQ
      UNIQUE (IDproduct, Name)
, CONSTRAINT Products_SubProducts_FK
      FOREIGN KEY (IDproduct)
      REFERENCES Products (IDproduct)
) ;


Which Is the Used Size Unit In DiskSpace Column at Storage Table In Zoneminder Database

59289474877 Bytes => 55.21 GByte .. pretty much the difference between 365.68 and 310.4

so the unit is Bytes if that helps..


Recommended hardware configuration Analysis and Reporting Services

Intuitively, if I was doing an OLAP solution for a retail chain, I'd say your infrastructure is really inappropriate for a system with substantial data volumes.  This sort of kit has trouble with the data volumes you get in insurance, which is probably a couple of orders of magnitude smaller than I would expect to see in retail.

As gbn states in the comments, SSRS is just a web application.  You can set up a farm - start with one server with a few GB of RAM and a couple of virtual CPUs.  Monitor it, and expand it if it's overloaded.

The amount of disk space used by SSAS depends on the volume and the aggregations you put on the cubes.  The storage format is quite compact - more so than SQL Server, but if you have large volumes of data it will start to get quite big.  If it's getting into the 100+GB range then you should also look at partitioning.

A surprisingly applicable generic solution

Now, your client probably doesn't want to hear this, but VMs are not my recommended hardware configuration for any business intelligence solution.  They work OK for transactional applications or anything that is not too computationally intensive.  BI for a retail chain is probably a bit aggressive for this sort of infrastrucutre.

As a generic 'starter for 10', My recommended configuration is a bare metal 2-4 socket server like a HP DL380 or DL580, and direct attach SAS storage.  The principal reason for this is that a machine of this sort is by far the best bang for buck as a B.I. platform and has a relatively modest entry price.  If you put a HBA on the machine then you can mount a LUN off the SAN for backups.

IOPS for IOPS, this sort of kit is an order of magnitude cheaper than any SAN-based virutal solution, particularly on sequential workloads like B.I.  The entry level for a setup of this configuration is peanuts - maybe £10-20,000 - and it's a lot cheaper and easier to get performance out of something like this than a VM based solution.  For a first approximation, the only situation where this kit is inappropriate for B.I. work is when the data volumes get too large for it, and you need something like Teradata or Netezza.

What can you do with your VMs, though?

A good start would be more RAM - try 32GB.  SSAS is a biblical memory hog, and you've got slow infrastructure.  If you're stuck with a VM, put as much RAM on it as possible.  Disk access is slow on VMs.
A second avenue is to partition the cube over multiple LUNs, where the LUNs are on separate physical arrays.  Not all SANs will give you this much control, though.  80GB is getting into the range where you might get a benefit from parititioning, particularly if you've got a platform with slow I/O.
Tune the buggery out of your cube aggregations - try usage based optimisation.  The more hits you can get from aggregations the more efficient the server will be.

Without measuring your workload, I doubt anyone here is in a position to make recommendations that are any more specific than that.  Although generic, the pointers above should be a reasonable start if you haven't implemented them yet.


Why innodb doesn't store geospatial data?

Because R-Trees are not B-Trees:

  For MyISAM tables, SPATIAL INDEX creates an R-tree index. For storage
  engines that support nonspatial indexing of spatial columns, the
  engine creates a B-tree index. A B-tree index on spatial values will
  be useful for exact-value lookups, but not for range scans.

Adding a completely different storage structure for InnoDb is significant effort (much more than for MyISAM due to locking and recovery).


MySQL: Create multiple tables with one query

Looking at the docs, it looks like you can't create multiple tables with one CREATE. You can use IF NOT EXISTS and LIKE together, like this:

CREATE TABLE IF NOT EXISTS table1 LIKE table_template;
CREATE TABLE IF NOT EXISTS table2 LIKE table_template;
CREATE TABLE IF NOT EXISTS table3 LIKE table_template;

Here's the page from the MySQL docs: CREATE TABLE


"How can I revert my character set" :

ALTER TABLE myTable CONVERT TO CHARACTER SET "utf8";

"How can I revert my type": (MySQL Engine):

ALTER TABLE myTbale ENGINE=MyISAM;

"how can I customize settings when I make new database?"

In your my.cnf

default-storage-engine=MyISAM

Best Regards.

Max.


MS SQL Server Error: Replication components are not installed on this server on Ubuntu

Confirmed. Replication on Linux begins with SQL Server 2019 according to Microsoft

  SQL Server 2019 introduces SQL Server Replication for instances of SQL Server on Linux.


Setting up Mirroring in SQL Express Edition

Express Edition doesn't include database mirroring, but you can do something similar with log shipping. Log shipping is a technique, not a feature - it's just a matter of frequently taking backups on your primary server, and then restoring them on the secondary.

The normal way of doing this requires SQL Server Agent to run the backup & restore jobs - but again, that's not something Express Edition includes. You'll need some kind of job scheduler.

Kathrine Villyard has a post on doing this with PowerShell.

When it comes to failing over, the basic concepts are the same as failing over Standard Edition's log shipping. They'll be manual steps on Express Edition - but they're also manual on the big brother, too, so don't feel bad.


MySQL Workbench always closes all existing resultset tabs when it executes the content in a specific tab. If you have several queries in your editor and execute all of them (and they return a result set) then a new resultset tab is created for each returned set.

If you want a specific resultset tab to stay while running other queries open it in a separate editor tab.


SQL Server : multiple databases with transaction log on one SSD array

Changing from sequential to logical read / write patterns is far more harmful on rotational drives than SSDs.  Because SSDs have no read heads, latency is much lower, even on non-sequential reads and writes.  You will take a performance hit if it's not sequential, but with higher-end SSDs, you'll be able to get close to or saturate bus speeds (SAS / SATA) on the system.  You'll need to make sure that whatever you're driving your array with can sustain the I/O.  I've found CrystalDiskMark benchmarks w/ 4k @ 32QD have been accurate indicators of performance on a busy system.  All that said, if your SSD array can sustain the same IOPS across all logs with random I/O as what your rotational drives can do sequential, you'll be fine.

I don't have any experience with FMDs vs SSDs.


Please don't detach and attach your database. If you detach it and something happens to it, you now have zero copies of your database. Much safer approaches:

Backup/Restore
USE master;
GO

BACKUP DATABASE floob TO DISK = 'c:\wherever\floob.bak' WITH INIT, COPY_ONLY;

ALTER DATABASE floob SET SINGLE_USER WITH ROLLBACK IMMEDIATE;

RESTORE DATABASE floob FROM DISK = 'c:\wherever\floob.bak'
  WITH REPLACE,
  MOVE N'floob_log' TO 'c:\new_path\floob.ldf';

Offline/Online
(You should still take a backup prior to doing this.)
USE master;
GO

ALTER DATABASE floob SET SINGLE_USER WITH ROLLBACK IMMEDIATE;

ALTER DATABASE floob MODIFY FILE
  (name = N'floob_log', filename = 'c:\new_path\floob.ldf');

ALTER DATABASE floob SET OFFLINE;

Now go to Windows Explorer, copy the ldf file to the new location, and rename the original. Make sure the SQL Server service account has adequate permissions on the new folder. Then:
ALTER DATABASE floob SET ONLINE;

ALTER DATABASE floob SET MULTI_USER WITH ROLLBACK IMMEDIATE;

Once the database is back up and running, you can safely delete the log file from the source location.

Both methods will need a brief downtime (how much depends on a lot of factors, including size of the database and speed of I/O both on the drives themselves and whatever path is between them), but neither will require you to reboot the server.


Can you restore log file backups to a mirror database after failure?

Short answer, yes.

Longer answer, you'd need to remove database mirroring without bringing the database online.  Then it's just a database in NORECOVERY so you can then restore transaction logs to it.  Then bring it online and use it as normal.


Why a transaction retrieve the same row more than one time?

I wonder why a transaction read the same row again. Why it
  did not use the old value? If it retrieve again to get updated value,
  getting different values should not be problem.

My guess is that the developer simply didn't consider the possibility of concurrent updates and the complications around that.

  When repeatable read isolation level should be used?

Assuming T1 is a explicit transaction (i.e. it has Begin Tran, Commit Tran), I believe running T1 under repeatable read isolation level would protect you from this particular problem. However it may cause blocking or deadlocks more than is necessary.

If the one problematic row is the only possible problem, then it may be sufficient to select it just once using the WITH (UPDLOCK) hint, which will prevent other transactions modifying it until T1 completes. However other transactions will still be able to read the row (which may then be updated by T1), unless they also use WITH (UPDLOCK).


Apparently not, based on this recent post which suggests that they didn't open it up to non-transactional engines, but specifically for MyISAM... and even as such, it's still experimental (and still seems like a horrible idea except for the extremely limited case of the DML manipulation of the tables in the `mysql` database).
The article "5 Tips for migrating your MySQL server to a Galera Cluster" is over a year old by now, but it seems unlikely that this statement will ever be modified by much, because of the nature of how the internals operate:

Please be adviced that this is an experimental feature, and as explained above, will never really be able to work as well and perform as well as using InnoDB tables. It's dangerous in many other ways too: for example non-deterministic functions are not protected for in any way (ie it is even more dangerous than SBR in MySQL classic replication). Still, this feature has turned out to be helpful for some users, for example if applications do occasional updates on some of the MyISAM tables in mysql.* database, which of course can't (yet?) be migrated to InnoDB.
(emphasis added)

So apparently the real motivation for tying in to MyISAM wasn't because it was a good idea, but because that's the storage engine of the mysql grant tables, which does make quite a bit of sense depending on how you access them.
Yes, Aria seems generally a safer place to put your data than MyISAM but it's still not transactional like InnoDB/XtraDB, and I fail to understand why anybody wanting the resilience of a cluster would avoid InnoDB, which is, you know, sort of exactly what Galera is actually for... other than the one or two things MyISAM and Aria have that InnoDB doesn't... which are spatial and fulltext (pre-5.6) indexes.


Segmenting data by percentage of a column total

You could calculate the percentage of the sum of a you've reached up till the current row, then segment that based on your thresholds.

with pct as (
  select a
  , (sum(a) over (order by a desc))/sum(a) over() pct
  from foo
)
select a
, pct
, sum(a) over (partition by
    case
      when pct <= .6 then 'A'
      when pct <= .9 then 'B'
      else                'C'
    end
    order by a desc) partial_sum
from pct
order by a desc;

Expanded version that actually shows the category (second subquery isn't necessary, just avoids repeating the partial sum expression):

with pct as (
  select a
  , (sum(a) over (order by a desc))/sum(a) over () pct
  from foo
), category as (
  select a
  , pct
  , case when pct <= .6 then 'A'
         when pct <= .9 then 'B'
         else                'C'
    end cat
  from pct
)
select a
, cat
, to_char(pct*100, '999.99')||'%' pct_of_total_a
, sum(a) over (partition by cat order by a desc) sum_by_category
from category
order by a desc;


Efficient Value Update with Large Database

When possible don't use scalar user defined functions (UDF) in queries. They force the entire plan to be serial, they can cause a lot of additional CPU resources to be used, they are a black box to the optimizer and can lead to cardinality estimation issues in other parts of the plan, and they can result in needlessly large memory grants.

Try running your UPDATE query without the UDF. Just take the code that you have and put it into the UPDATE query directly. Something like this:

update full_orig_month_Q1_1999
set mth_order = round(period/100,0)*12+period-100*round(period/100,0);

Does that improve the runtime and memory usage?


Split a pattern string into columns in select query

Does this cover your expected result?

SELECT SUBSTRING_INDEX(name, ':', -1) name,
       SUBSTRING_INDEX(age, ':', -1) age,
       SUBSTRING_INDEX(sex, ':', -1) sex
FROM (SELECT SUBSTRING_INDEX(details, ',', 1) name,
             SUBSTRING_INDEX(SUBSTRING_INDEX(details, ',', 2), ',', -1) age,
             SUBSTRING_INDEX(details, ',', -1) sex
      FROM   person) t;

name  | age | sex
:---- | :-- | :---
Tahir | 30  | male

dbfiddle here


Is there a good way to run a trigger for each record in a postgres table?

It can be done, using the following template:

CREATE TABLE tablename ( ... );

/* for direct invocation */
CREATE FUNCTION propagate_data(newrow tablename) RETURNS void
LANGUAGE plpgsql
AS $$
BEGIN
    INSERT INTO other_table VALUES (newrow.a, newrow.b, ...);
END:
$$;

/* trigger function wrapper */
CREATE FUNCTION propagate_data_trg() RETURNS trigger
LANGUAGE plpgsql
AS $$
BEGIN
    PERFORM propagate_data(NEW);
END;
$$;

CREATE TRIGGER propagate_data AFTER INSERT ON tablename FOR EACH ROW
    EXECUTE PROCEDURE propagate_data_trg();


copy data from public schema to another schema in postgresql

You're setting custom format.

Remove the -Fc option from the pg_dump command so the dump will be generated with COPY commands.

Add set schema 'myschema'; at the beggining of the file.

then

psql -U my_username -h localhost -d my_db -f db/my_dump.backup -v ON_ERROR_STOP=1


Strange Garbage Character in SQL server database like ÂÃÂÃÂÃÂÃÂÃÂÃÂÃ

According to the MSDN page for PDO::setAttribute, PDO::SQLSRV_ENCODING_UTF8 is the default value, so setting it explicitly probably wouldn't change the behavior.

Try the other option, PDO::SQLSRV_ENCODING_SYSTEM, which seems to have worked in this case from the MSDN forums: Some characters improperly retrieved through PDO driver.


Will altering order of columns in GROUP BY affect performance?

Despite what MSDN documentation says, no, it doesn't matter for GROUP BY queries.

You can test it here, at: SQL-Fiddle test (SQL-Server 2012)

CREATE TABLE test
( id INT IDENTITY(1,1)
, a  INT NOT NULL
, b  INT NOT NULL
, c  INT NOT NULL
, d  INT NOT NULL
, PRIMARY KEY (id)
) ;

CREATE NONCLUSTERED INDEX a_b_c_include_d_index
    ON test (a, b, c)
    INCLUDE (d) ;

INSERT INTO test
(a, b, c, d)
VALUES
 ... some 50K rows

The queries:

SELECT COUNT(*) AS num
     , MIN(cnt) AS min_count
     , MAX(cnt) AS max_count
     , MIN(min_d) AS min_min_d
     , MAX(min_d) AS max_min_d
     , MIN(max_d) AS min_max_d
     , MAX(max_d) AS max_max_d
FROM
  ( SELECT a
         , b
         , c
         , COUNT(d) AS cnt
         , MIN(d) AS min_d
         , MAX(d) AS max_d
    FROM test
    GROUP BY a, b, c             --- or with:  `GROUP BY b, a, c`
  ) AS grp ;

produce the same execution plan:

MICROSOFT SQL SERVER 2005 XML SHOWPLAN
SELECT
Compute Scalar
Cost: 0%
Stream Aggregate
(Aggregate)
Cost: 0%
Compute Scalar
Cost: 10%
Stream Aggregate
(Aggregate)
Cost: 10%
Index Scan
[a_b_c_include_d_index].[test]
Cost: 89%

Now if you change that Group by to:

    GROUP BY a, b, c
      WITH ROLLUP

it does make a difference and it does produce different execution plans (and different result sets of course). It still uses the index though, at least in that SQL-Fiddle test. It the mismatching order case, it gets the data from the index but it has to do an intermediate sort (to calculate the rollup values).


I used your approach two years ago for a similar problem, it works fairly well, except a bit pain with linq to sql (or entity framework), plus WinForm:

We created a few updatable views for each type of entity (or customer as per your sample) but linq to sql could not cope well with updatable views, or probably it's not the case now.

Now we used a different approach:

In the main table, we will have a column named Descriminator (something like UserType),

then for each type of entity, we will have its delegated table (extended from the main table), which uses same PrimaryKey as the main table - so of course, a foreign constraint refer back to the main table (i.e. Primary Key and foreign key on the same field), and the delegated table will have the specific columns related to that user type.

it works well too, and with this, we can take the advantage of entityframework's inheritance feature.

that's just my experience, so my suggestion is to write some small test apps to test out your approach(es) and evaluate. then get the best that has good performance and also suits your framework for UI.


postgres: index to SELECT strings like '%foo%';

The index should work as is. As pointed out by @jjanes, you just needed to run ANALYZE. However, I suggest you modify the index. The expression in your definition is not useful.

CREATE INDEX foo_idx ON intentions ((data LIKE '%foo%')) WHERE data NOT LIKE '%foo%';

The expression is always FALSE and useless noise. Either simplify to the slightly cheaper equivalent index:

CREATE INDEX foo_idx ON intentions ((FALSE)) WHERE data NOT LIKE '%foo%';

Or, better yet, put something useful there. Since each index entry is going to allocate MAXALIGN (typically 8 bytes) for data anyway, and the overhead per index row is another 12 bytes (and the default fillfactor for btree indexes is 90%) you might as well put the index to good use and help other queries. If you have any other queries that might use the same partial index?

If you put something worth up to 8 bytes (like one or two int4 columns), the index retains exactly the same size and performance, but offers more possible use cases. If (one of) the column(s) is involved in updates, you have some additional maintenance cost. Still, if you have anything useful, use it. Any additional column that's used in a JOIN / WHERE or ORDER BY clause. Or column(s) that enable index-only scans:

CREATE INDEX foo_idx ON intentions (<useful_column(s)>)
WHERE data NOT LIKE '%foo%';