Is it possble to replicate MySQL server on Azure virtual machines I have two Windows Server 2012 R2 virtual machines on Azure. I need to replicate the database on each of the windows servers. I have followed this link but I am not getting the connection between the two MySQL servers. It is showing as connecting to master. Is there any other way to replicate MySQL databases on two different windows servers?

For purposes of this discussion, there is only one way to replicate from one MySQL server to another, and the post that you linked to does seem to describe it correctly.

In this setup, the slave server needs to have the ability to establish a tcp connection to the master on port 3306. (The master never initiates this connection, so the connection only needs to be established from slave, to master.)

You can verify this on the slave at a command prompt:

C:\>mysql --host=ip.addr.of.master --port=3306 --user=username --password=password

The ip.addr.of.master should be the private IP of the master, and should be the same one you used in the CHANGE MASTER TO statement you executed on the slave.  You should also be able to use the username and password from the same statement to connect.

If this doesn't work, either, then the problem is probably not that you didn't follow the setup correctly, but is simply that the slave server's machine cannot reach the master machine over the network.

The steps to remedy this are going to be determined by the Azure setup, such as whether the two servers are in the same cloud service and virtual network, and whether there's a firewall on the master server that needs to be configured to allow access from the slave.

The command, above, should produce a numeric error code that you can translate into a descriptive error by using perror nnnnn command or by finding the code here.  I would expect, based on the description, that you will see 10060, connection timed out, which would be the strongest confirmation that what you have is a networking issue.


Loop INSERT EXEC AT through linked servers Relating to Inserting EXEC AT linked server into temp table

I am now in the midst of dynamically insert each result of dynamic sql to the temp table for each registered linked server. Below is my query:

SELECT
    @LinkedServer = @LinkedServer + ' INSERT INTO #eapb EXEC(@remotecmd) AT [' + name + '];'
FROM sys.servers s
WHERE server_id > 0;

Below is the error:

Msg 137, Level 15, State 2, Line 1
Must declare the scalar variable "@remotecmd".

Manually, I can achieve this through below script:

insert into #eapb
EXEC (@remotecmd) AT [server_1];
insert into #eapb
EXEC (@remotecmd) AT [server_2];

If I try to get @remotecmd outside like this,

SELECT
    @LinkedServer = @LinkedServer + ' INSERT INTO #eapb EXEC('+@remotecmd+') AT [' + name + '];'
FROM sys.servers s
WHERE server_id > 0;

I get below similar error:

Msg 156, Level 15, State 1, Line 2
Incorrect syntax near the keyword 'DECLARE'.
Msg 102, Level 15, State 1, Line 52
Incorrect syntax near ')'.

If I am understanding this correctly (even though the first part of what you are doing is not being shown), I think you just need to place the declaration and setting of @remotecmd into the @LinkedServer variable. For example:

DECLARE @CRLF NCHAR(2) = NCHAR(0x000D) + NCHAR(0x000A);
DECLARE @LinkedServer NVARCHAR(MAX) = N'DECLARE @RemoteCMD NVARCHAR(MAX);' + @CRLF;

SET @LinkedServer += N'SET @RemoteCMD =
N''some query here'';
';

SELECT @LinkedServer += N' INSERT INTO #eapb EXEC(@RemoteCMD) AT [' + name + N'];' + @CRLF
FROM   sys.servers s
WHERE  server_id > 0;

When doing Dynamic SQL, I find it best to use NVARCHAR(MAX) for the variable, and also prefix string literals / constants with a capital-N.

Also, since you are using SQL Server 2014, you can take advantage of the += operator (introduced in SQL Server 2008, I believe), and save yourself some typing :-)


MySQL: error 1118 (42000) row size too large storage_engine = MyISAM
table_type = MyISAM
version = 5.1.25-rc

database_name.sql dump size is 3.3G:

mysqldump -u user -p database_name > database_name.sql

When I try to restore the database (trying to restore on MySQL 5.1.25-rc and on 5.7.17):

mysql -u user -p database_name < database_name.sql

...an error:

  ERROR 1118 (42000) at line 7192: Row size too large. The maximum row size for the used table type, not counting BLOBs, is 65535. You have to change some columns to TEXT or BLOBs

"Questions that may already have your answer" doesn't have answer for me :(

Sounds like the import forced the Engine to be InnoDB??

If so, then let's see the my.cnf and figure out what to fix.

If not, then let's see the CREATE TABLE that is leading to the problem.

Or, maybe, a CHARACTER SET default changed.

Show us the first part of the dump and the CREATE TABLE just before the naughty table.


Can't set up witness in Repmgr 2.0 I am trying to setup repmgr using the autofailover quick setup tutorial provided on Github Link

But I am kind of stuck on the witness section, when trying to create the witness with repmgr -d repmgr -U repmgr -h [MASTERIP] -D /var/lib/postgresql/9.1/witness -f /var/lib/postgresql/9.1/repmgr/repmgr.conf witness create

I get the following error:

Connection to database failed: fe_sendauth: no password supplied

I am not sure which database the error is talking about? Is it the master's DB or the witness'?

Also, I haven't been able to find a password parameter to pass to repmgr yet, is there even one?

Below are the repmgr.conf files for each server:

Witness:

cluster=main
node=3
node_name=witness
conninfo='host=[WITNESS IP] dbname=witness user=witness password=witness  port=5499'
master_response_timeout=60
reconnect_attempts=6
reconnect_interval=10
failover=automatic
promote_command='promote_command.sh'
follow_command='/usr/lib/postgresql/9.1/bin/repmgr standby follow -f /etc/repmgr/repmgr.conf'

Standby:

cluster=main
node=2
node_name=node2
conninfo='host=[STANDBY IP] dbname=[DB NAME] user=[DB USER NAME] password=[PASSWORD]'
master_response_timeout=60
reconnect_attempts=6
reconnect_interval=10
failover=automatic
promote_command='promote_command.sh'
follow_command='/usr/lib/postgresql/9.1/bin/repmgr standby follow -f /etc/repmgr/repmgr.conf'

Master:

cluster=main
node=1
node_name=node1
conninfo='host=[MASTER IP] dbname=[MASTER DB] user=[MASTER DB USER] password=[PASSWORD]'
master_response_timeout=60
reconnect_attempts=6
reconnect_interval=10
failover=automatic
promote_command='promote_command.sh'
follow_command='/usr/lib/postgresql/9.1/bin/repmgr standby follow -f /etc/repmgr/repmgr.conf'

The error message means that repmgr account can't login onto master server with provided password (probably empty one).

You need to allow connections on master, in pg_hba.conf:

host    all     repmgr  192.168.1.1/32       md5
host    replication     all     192.168.1.1/32       md5

then trigger a master server reload:

/usr/lib/postgresql/9.1/bin/pg_ctl reload -D /var/lib/postgresql/9.1/main

(where -D is the data directory of PostgreSQL).

Assumsing you've created a repmgr account, on master with some secret_passw0rd

createuser --login --superuser -P repmgr -W
createdb -O repmgr repmgr

then on standby server create a .pgpass file in postgres home:

su - postgres
cat > .pgpass << EOF
*:*:*:repmgr:secret_passw0rd
EOF
chmod 0600 .pgpass

That's all under assumption that you're using md5 password authentication for replication, if you switch to trust you could skip setting the password:

host    all     repmgr  192.168.1.1/32       trust
host    replication     all     192.168.1.1/32       trust

Now you can check connection to your master server:

psql -h master.hostname -U repmgr


Who deleted a record? Is it possible to audit a Postgresql 9.4.5 database in such way that I could find when a specific record was deleted? I don't know what do I need to have enabled / available for such

No, it is not. Data that is gone is gone. This type of auditing is normally implemented by custom code (triggers writing to an audit table or something like this).


DB2 importing/replacing fields from a file that contains XML In two tables I have a column called table1.column_xml and table2.column_xml_2 defined as CLOB(1000000). The fields in the column contain XML formatted information like below

column_xml
--------------------------------------------------------------------------------
<values>
 <comment>test test test</comment>
 <rights></rights>
 <title>TEST MAN</title>
</values>
--------------------------------------------------------------------------------

What I would like to do is import/or replace any existing values in table2.column_xml_2 with the values from table1.column_xml.

So in simpler terms I want to export the values from column_xml into a file then import/replace into column_xml_2

Why don't you just

update table2 set column_xml2 =
(select column_xml from table1 where <predicate clause>) where <predicate clause>;

?

With CLOBs you pretty much have to do that. You can only update the entire CLOB. Now if are on DB2 9.1 or higher, you could store these in a field of type XML and then you could attempt to update specific XML elements like how you are hoping to do.


Why is server configuration 'min server memory' configured at 0, but running at 16 (sometimes 8)? We started to monitor sql server instance non-active server configurations and find something interesting.
Why is server configuration 'min server memory' configured at 0, but running at 16 (sometimes 8)?
The instances use the default value. 0 is the default and we don't change it, but apparently something changes it when SQL Server starts.
If this is the default behavior why isn't this value (16 or 8) configured as default instead of 0?

Does anyone have an (official/documented) explanation to this behavior?

Why is server configuration 'min server memory' configured at 0, but running at 16 (sometimes 8)? The instances use the default value. 0 is the default and we don't change it, but apparently something changes it when SQL Server starts

When you leave 'Min Server Memory' to default value of 0 this does not means that Lowest possible value is 0. The lowest possible value would be 16 MB. This is documented in This Blog.msdn

For SQL Server 2008 R2

Sp_configureOption   DefaultSetting     LowesPpossibleValue     HighestPossiblevalue

Min server memory          0                 16                Less Than Max server memory

For SQL Server 2012

Sp_configureOption   DefaultSetting     LowesPpossibleValue     HighestPossiblevalue

Min server memory          0                 16                Less Than Max server memory


How to check if a value is in an array in a table I have a table if key - array pairs and need to check if a string is an element in a given array in this table. I have managed to write an if statement to check if the element is in an array but if I try to get the array from the table it does not work.

In the example below,
SELECT list FROM public."HelloWorldTable" WHERE name = 'HelloWorldList'
selects the list element that contains an array that is identical to the one in the other if statement but I can not seem to access it the same.

MWE

CREATE TABLE IF NOT EXISTS "HelloWorldTable"(
    Name text PRIMARY KEY,
    List text[]
);

INSERT INTO public."HelloWorldTable" (name, list) VALUES ('HelloWorldList', ARRAY['Hello', 'World']);

DO $$
BEGIN
  IF 'Hello' = ANY (ARRAY['Hello', 'World']::text[]) THEN
      RAISE NOTICE 'Found';
  ELSE
      RAISE NOTICE 'Not found';
  END IF;

-- failing attempt:

--  IF 'Hello' = ANY (SELECT list FROM public."HelloWorldTable"
--    WHERE name = 'HelloWorldList') THEN
--      RAISE NOTICE 'Found';
--  ELSE
--      RAISE NOTICE 'Not found';
--  END IF;

END $$;

I found this other question but I can not get it working the way I need it to. I have tried adding ::text and ::text[] to the second if statement but I only get different errors.
How can I solve this?

Use instead:

DO $do$
BEGIN
   IF EXISTS (
      SELECT FROM hello_world_table
      WHERE  name = 'HelloWorldList'
      AND    'Hello' = ANY (list)
      ) THEN
       RAISE NOTICE 'Found';
   ELSE
      RAISE NOTICE 'Not found';
   END IF;
END $do$;

db<>fiddle here

With a subselect between the parentheses, like you tried, the ANY construct expects a set of element types, not an array. That would work with the column list being type text, not text[].

I can see how this is confusing, as Postgres automatically extracts the single value from a single column in a returned set in most other contexts to match the expected form. Like:

SELECT '{Hello,World}' = (SELECT list FROM hello_world_table WHERE name = 'HelloWorldList');

The untyped string literal '{Hello,World}' is even cast to text[] automatically, deriving the type from the subselect on the other side.

But in this case, the ANY construct is resolved to its alternative form expecting a set, which makes your attempt fail.

You could make it work by unnesting the array to produce the expected set:

SELECT 'Hello' = ANY (SELECT unnest(list) FROM hello_world_table
                      WHERE name = 'HelloWorldList');

But don't. Use an EXISTS subquery instead and move the condition to the WHERE clause like demonstrated.

Related:

How to use ANY instead of IN in a WHERE clause with Rails?
Check if value exists in Postgres array
PL/pgSQL checking if a row exists


How SQLite index ID (or line number) SQLite is based on a flatfile, and store virtual tables in a flatfile (I think based on offset, correct me if I'm wrong). But how it index the Primary Key (ID), which I think is identical to line number.

When having SELECT with WHERE clause that ID = 99,001, how it skips 99,000 rows to read only the requested row. How is the process in a flat file?

you're wrong.

There's nothing 'flat' in the SQLite database file; the file is internally split as pages, with trees and references between them.  A table doesn't' store records sequentially.

Check the file format docs


How to implement effective MySQL database backup mechanisms? MySQL database backup is really critical for my company's business, can anyone share some experience on this?

I think what you're looking for is mysqldump. Something like the following should get it done:

mysqldump -u root -p --databases DB_NAME > databasename.sql


MYSQL Select Distinct with SUM I am developing a Stocks management application, the Portfolio register table has 4 columns, other than date and registry_key, and it looks like this:

----------------------------------------
| stock_key | user_key | units | price |
----------------------------------------
| fund_a    | user_1   | 1000  | 100.35|
| fund_c    | user_2   | 4000  | 101.55|
| fund_d    | user_3   | 2000  | 105.65|
| fund_a    | user_1   | 1500  | 101.45|
| fund_b    | user_1   | 500   | 103.35|
----------------------------------------

I wish to select the portfolio of user_1 with distinct stock_key. Plus I need the price as the aggregate total paid over the number of units. For example:

For user_1 the result should be:

[
    {fund_key: "fund_a", units: sum(1000,1500), price: sum(1000*100.35,1500*101.45)/sum(1000,1500)},
    {fund_key: "fund_b", units: 500, price: 103.35}
]

This is important because once the user has brought more units of the same stock at a lower/higher price than before, the accumulated stocks price is averaged out.

Here's your query:

    SELECT
        concat("fund_key: ",stock_key,", units: ",sum(units),", price: ",round(sum(units*price)/sum(units),2)) as result
    FROM PRUEBA.new_table2
    GROUP BY stock_key,user_key;

Result:


Size of the database and table while inserting and deleting in sql server 2005 I have created a table : Table1. Inserted 100 thousand rows and deleted. I did the same around 100 times ( inserting and deleting). Will it cause of increasing the database and table size every time? ( I am using Delete instead of Truncate).

WHen you use delete the rows actually not deleted but marked as GHOST records, which will finally deleted by dedicated process named GHOST CLEANUP which works relatively slow in background

To force deletion of ghost records run:

DBCC FORCEGHOSTCLEANUP


Mongoimport monitoring successfully entered row/data How to monitor mongoimport hass successfully saved data into our database? Let's say I have 20,000 rows of CSV data. When I press the button to start mongoimport, the user will see the progress.

Is there a way to see each row successfully inserted to the DB, visually?

mongoimport --db dbname --collection collectionname  --type csv --headerline --file filename --maintainInsertionOrder

If your mongod uses journaling, follow local.oplog.rs collection, there are all inserts in the order.


Is it safe to cancel a PostgreSQL ALTER TABLE query that is waiting on a lock? We started an ALTER TABLE query hours ago and only recently realized (via pg_stat_activity) that it is waiting on a lock.  We discovered the other query that is holding a lock on the table we want to alter, and not letting it go.

Our query is a "simple" query (changing a column data type), but it is running on a massive table.

Rather than killing the process that is holding onto the lock, we've decided we'd rather kill the ALTER TABLE.

We did not wrap the ALTER TABLE in a transaction.

As far as I understand, the fact that our query is waiting for a lock means it has always been waiting for a lock, and it has never changed anything.

Is this true?  Is it safe for us to outright cancel our ALTER TABLE query?  Or is it possible that the query has already modified something and cancelling it would leave our database in a halfway state of some kind?

PS: The plan is to cancel it using SELECT pg_cancel_backend(pid);.  If this is a bad idea, please let me know.

As far as I understand, the fact that our query is waiting for a lock means it has always been waiting for a lock, and it has never changed anything.

Right -- if you see that pg_stat_activity.waiting is "true" for an ALTER TABLE, that almost certainly means that it's patiently waiting for the ACCESS EXCLUSIVE lock on its target table, and its real work (rewriting the table if necessary, changing catalogs, rebuilding indexes, etc.) hasn't started yet.

  Is it safe for us to outright cancel our ALTER TABLE query? Or is it possible that the query has already modified something and cancelling it would leave our database in a halfway state of some kind?

Canceling queries (or, equivalently, rolling back a transaction) in PostgreSQL doesn't have any database corruption hazards which you might have been spooked by in certain other databases (e.g. the terrifying warning at the bottom of this page). That's why non-superusers are, in recent versions, free to use pg_cancel_backend() and pg_terminate_backend() to kill their own queries running in other backends -- they are safe to use without fretting about database corruption. After all, PostgreSQL has to be prepared to deal with any process getting killed off e.g. SIGKILL from the OOM killer, server shutdown, etc. That's what the WAL log is for.

You may have also seen that in PostgreSQL, it's possible to perform most DDL commands nested inside a (multi-statement) transaction, e.g.

BEGIN;
ALTER TABLE foo ...;
ALTER TABLE bar ...;
-- more stuff
COMMIT; -- or ROLLBACK; if you've changed your mind

(awesome for making sure that schema migrations go in either all-together or not at all.) You said, though:

  We did not wrap the ALTER TABLE in a transaction.

That's fine for a single command -- from the docs,

  PostgreSQL actually treats every SQL statement as being executed within a transaction. If you do not issue a BEGIN command, then each individual statement has an implicit BEGIN and (if successful) COMMIT wrapped around it. A group of statements surrounded by BEGIN and COMMIT is sometimes called a transaction block.

So canceling that ALTER TABLE, either through pg_cancel_backend() or a Ctrl-C issued from the controlling psql prompt, will have a similar effect as if you had done

BEGIN;
ALTER TABLE ... ;
ROLLBACK;

(though as you hopefully got to see, canceling that expensive ALTER TABLE can save the database from a lot of unnecessary grinding if you're just going to ROLLBACK anyway.)


"Error while processing statement" running a query finding unique values with multiple values in other rows Is there anything wrong in the syntax of this query finding unique values with multiple values in other rows?
Select Distinct Ss.`cd`, Ss.`cd`
From nit.assrr As Ss
    Join (
       Select `cd`
       From nit.assrr
       Group By `cd`
       Having Count(Distinct `cd`) > 1
      ) As Y
On Ss.`cd` = Y.`cd`

I am getting this error:

[Cloudera]HiveJDBCDriver ERROR processing query/statement.
Error Code: 2, SQL state: Error while processing statement: FAILED:
Execution Error, return code 2 from
org.apache.hadoop.hive.ql.exec.mr.MapRedTask, Query:  .. my query

I suspect you are using a Hive version that is before 0.13.0. In that case the problem is the AS keyword when giving alias to subquery.

From HiveQL documentation Subqueries in the FROM Clause:

SELECT ... FROM (subquery) name ...
SELECT ... FROM (subquery) AS name ...   (Note: Only valid starting with Hive 0.13.0)

  ...
  The optional keyword "AS" can be included before the subquery name in Hive 0.13.0 and later versions (HIVE-6519).

Therefore, valid syntax would be:

Select Distinct Ss.`cd`, Ss.`cd`
From nit.assrr AS Ss
    Join (
       Select `cd`
       From nit.assrr
       Group By `cd`
       Having Count(Distinct `cd`) > 1
      ) Y                                  -- AS removed
On Ss.`cd` = Y.`cd` ;

Regarding the semantics of the query. I'm not sure what the actual problem is but this query is guaranteed to return 0 rows, no matter what the data in the table are.

COUNT(DISTINCT cd) will be 1 when you have GROUP BY cd so the HAVING clause will be FALSE always and the Y subquery will have 0 rows.


How do I debug an ETL process that is unable to commit any records? I used a DB link to connect to another DB and delete a couple of records. Later I ran an Informatica Workflow (ETL tool) to load data into that database using a DB connection that connects directly to it.

The Workflow hasn't committed any records since 2hrs (it should run just for 30mins or so..) so I am guessing it is because I was not able to run "commit" using the DB link. The ETL tools logs don't provide any error or debug information at this point.

Any ideas how I could debug this..? I ran another ETL process to  update a table and then run commit. But even after that the first process keeps on running without committing any records. I am not a DBA expert so probably am misinterpreting the DB behavior.. would really appreciate any suggestions. Thanks!

Since you're using a standard Oracle-to-Oracle database link, Oracle automatically does two-phase commits.  If you commit (or rollback) on one system, any changes made as part of the same transaction on the other system will committed (or rolled back) as well.

If there is an open transaction on the remote system that has row-level locks, that strongly implies that the local session is still there as well.  So you probably just have to do a commit (or a rollback) in the local session to remove the locks.

If the ETL process is really stuck waiting on locks that are held by the other session, you should be able to query gv$session on the remote database and see that the session that the ETL application is running is waiting on row-level lock contention.  Specifically, you would expect to see an event of enq: TX - row lock contention in gv$session.  You would also expect that if you queried dba_blockers that you would see the session ID of the session that connected from the local database over the database link and that if you queried dba_waiters that you would see that the ETL process's session was blocked by that session.


Calculating multiple medians I have defined function with table value parameter which returns median as per values passed to it. Function defined as:

    CREATE FUNCTION [dbo].[fn_GetMedian](@List TypeMedian READONLY)
    RETURNS INT
    AS
    BEGIN
    <function body>
    END

And Table Type TypeMedian definition as:

CREATE TYPE [dbo].[TypeMedian] AS TABLE(
    [VALUE] [int] NULL
)

Now I have a table Listing with filled values in it as and a table RESULT to be filled according to table Listing:

Tables structure as

LISTING(ListingCol1,ListingCol2,ListingCol3,ListingCol4,ListingCol5)
RESULT(Col1,Col2,Col3,Col4,Col5)

Listing table has more that 1000 rows of data.

All column from both tables are of type int.
Now I want to fill columns of RESULT table and that column could be calculate as:

Col1 = SUM(ListingCol1)
Col2 = SUM(ListingCol2)
Col3 = dbo.fn_GetMedian(ListingCol3)
Col4 = dbo.fn_GetMedian(ListingCol4)
Col5 = dbo.fn_GetMedain(ListingCol5)

And I'm doing so as:

INSERT INTO RESULT(Col1)
SELECT SUM(ListingCol1)

Update RESULT
SET Col2 = SUM(ListingCol2)

DECLARE @tbl_Median TypeMedian

INSERT INTO @tbl_Median
SELECT ListingCol3
FROM Listing

UPDATE RESULT
SET Col3 = dbo.fn_GetMedian(@tbl_Median)

-- For next column
DELETE FROM @tbl_Median

INSERT INTO @tbl_Median
SELECT ListingCol4
FROM Listing

UPDATE RESULT
SET Col4 = dbo.fn_GetMedian(@tbl_Median);

--And this update query I repeating for remaining columns.
How could I do that in single query?

For two sums and three medians, on a single table, I honestly can't see the benefit of using a complicated dynamic or function-based solution.

It is quite easy to construct a single query using Peter Larsson's median method that I showed you before:

CREATE TABLE dbo.Listing
(
    ListingCol1 integer NULL,
    ListingCol2 integer NULL,
    ListingCol3 integer NULL,
    ListingCol4 integer NULL,
    ListingCol5 integer NULL
);

CREATE TABLE dbo.Result
(
    Col1 integer NULL,
    Col2 integer NULL,
    Col3 integer NULL,
    Col4 integer NULL,
    Col5 integer NULL
);

-- Just to show indexes are helpful for the median calculations
CREATE INDEX i ON dbo.Listing (ListingCol3)
CREATE INDEX j ON dbo.Listing (ListingCol4)
CREATE INDEX k ON dbo.Listing (ListingCol5)

Solution

INSERT dbo.Result
(
    Col1,
    Col2,
    Col3,
    Col4,
    Col5
)
SELECT
    SC.SumCol1,
    SC.SumCol2,
    SQ3.MedianCol3,
    SQ4.MedianCol4,
    SQ5.MedianCol5
FROM
(
    -- Sums + counts needed for the median calculations
    SELECT
        SumCol1 = SUM(L.ListingCol1),
        SumCol2 = SUM(L.ListingCol2),
        CountCol3 = COUNT_BIG(L.ListingCol3),
        CountCol4 = COUNT_BIG(L.ListingCol4),
        CountCol5 = COUNT_BIG(L.ListingCol5)
    FROM dbo.Listing AS L
) AS SC
CROSS APPLY
(
    -- Median for column 3
    SELECT
        MedianCol3 = AVG(1.0 * SQ.ListingCol3)
    FROM
    (
        SELECT
            L3.ListingCol3
        FROM dbo.Listing AS L3
        WHERE
            L3.ListingCol3 IS NOT NULL
        ORDER BY
            L3.ListingCol3 ASC
        OFFSET (SC.CountCol3 - 1) / 2 ROWS
        FETCH NEXT 1 + (1 - SC.CountCol3 % 2) ROWS ONLY
    ) AS SQ
) AS SQ3
CROSS APPLY
(
    -- Median for column 4
    SELECT
        MedianCol4 = AVG(1.0 * SQ.ListingCol4)
    FROM
    (
        SELECT
            L4.ListingCol4
        FROM dbo.Listing AS L4
        WHERE
            L4.ListingCol4 IS NOT NULL
        ORDER BY
            L4.ListingCol4 ASC
        OFFSET (SC.CountCol4 - 1) / 2 ROWS
        FETCH NEXT 1 + (1 - SC.CountCol4 % 2) ROWS ONLY
    ) AS SQ
) AS SQ4
CROSS APPLY
(
    -- Median for column 5
    SELECT
        MedianCol5 = AVG(1.0 * SQ.ListingCol5)
    FROM
    (
        SELECT
            L5.ListingCol5
        FROM dbo.Listing AS L5
        WHERE
            L5.ListingCol5 IS NOT NULL
        ORDER BY
            L5.ListingCol5 ASC
        OFFSET (SC.CountCol5 - 1) / 2 ROWS
        FETCH NEXT 1 + (1 - SC.CountCol5 % 2) ROWS ONLY
    ) AS SQ
) AS SQ5;

Expected execution plan shape:


Terminology for table types I am currently unsure of the correct terminology for types of tables that exist, or indeed whether there is a term for the examples I have.

I have included my current understanding of the types that I am aware of further down in this question.

Q: Please provide a list of the different table types, with brief definitions and examples to illustrate them.

Sidenote: The context to this is that I am currently attempting to reduce a database to a bare-bones version (for testing). As part of this I am hoping to identify tables that contain values to be looked up and will not change (eg job titles and department titles) vs the "data" tables which may be emptied with no ill-effect on the remainder of the database (cf, referential integrity in the case of adding a staff member where no departments or job titles are defined).

Lookup tables

I believe this to be where a table refers to a second table, where many rows/records in the first table share identical fields/column data. In the simpler cases, this table may be replaced by an ENUM type on the original table.

Example, where many users can have the same job title, the "Job Title" table is identified as a lookup table and the "Users" table is identified as the data table (see below):

Users >- Job Title

Transactional / Junction / Many-to-Many tables

I believe this to be where a many-to-many relationship occurs between two tables, and this transaction/junction table resolves this relationship.

Example, where many developers may be working on many projects, the "developer_project" table is identified as the transaction/junction table, and the "Developer"/"Project" tables are indentified as data tables (see below).

Developer -< developer_project >- Project

Data/Entity table

I am pretty much making this one up, but where a table is able to exist on its own, irrespective of other tables I consider this to be primarily a data storage table as opposed to a structural table. It may use lookup tables as part of the normalisation/optimisation process, but this is incidental.

I have seen the term "weak" entity floating around and it may/may not apply here. I need to do further reading to be sure.

Example, "staff" and "building" may exist independently of any other tables whereas transactional or relational tables/entities may not:

Staff
Building

Self-Referencing Table

A self-referencing table has a foreign key to itself, creating a hierarchical structure.

create table employee (
  id serial primary key,
  name text not null,
  supervised_by int null references employee(id)
);

Closure Table

History Table

Subtype/Supertypes

Represents concrete entities and their abstraction. For example, it is wise to abstract an Individual and an Organization into a Party type, so that you can easily add either as a customer on a sales order line item.

Single Table Inheritance:

create table party (
  id serial primary key,
  party_type int not null references party_type(id),
  organization_name text null,
  individual_first_name text null,
  individual_last_name text null
);

Class Table Inheritance:

create table party (
  id serial primary key,
  party_type int not null
);

create table organization (
  id int primary key references party(id),
  name text not null
);

create table individual (
  id int primary key references party(id),
  first_name text not null,
  last_name text not null
);

In data warehousing:

Fact Table

Represents a process or event to be analyzed, and usually contains (unenforced) keys to dimension tables, as well as some numeric measures.

create table sales_item_fact (
  order_id bigint not null,
  order_line_item bigint not null,
  calendar_key int not null,
  customer_key bigint not null,
  quantity_sold int not null,
  unit_price numeric(19,4) not null
);

Dimension Table

Describes a dimension of a fact table, often with hierarchical attributes. For example you can analyze (break apart) dates into their component pieces to make querying easier.

create table calendar (
  id int primary key,
  date_iso date unique,
  year int,
  quarter_of_year_int,
  month_of_year int,
  ...
);

Aggregate Table


SSAS Feature to hold confidential data I need to use Data Cubes to analyse the Sales Performance. Since the product costs are highly confidential it should not be exposed to the middle management. However, the other information needs to be published to the middle management. Is there a feature in SSAS that could enable this requirement?

This is an overly broad question but SSAS has security features that allow you to shield data for certain users.

You should look into Roles and Permissions.

Since you are talking about shielding costs you probably just want to limit access to certain measures so you're probably looking for Grant custom access to cell data which limit access to the values:

  Cell security is used to allow or deny access to measure data within a
  cube.  Cell permissions apply to data inside the cell, and not to its
  metadata. Notice how the cell is still visible in the results of a
  query, displaying a value of #N/A instead of the actual cell value.

If you want to hide the fact that the measure exists you need to look at Grant custom access to dimension data

  In Grant custom access to cell data (Analysis Services), it was
  explained that fully hiding all visual aspects of a measure, and not
  just its cell data, requires permissions on dimension members. This
  section explains how to deny access to the object metadata of a
  measure.


Efficient way to check for number of records more than a limit from a table in SQL Server I came across queries that can most efficiently get the total sum of the records in the table. However, i want to group the records in a table by a column and check if the count of the records is higher than 1000, if not so, i need to return the count and if so, i can return -1.

TableDesign

CREATE TABLE [dbo].[Accounts](
    [Id] [bigint] IDENTITY(1,1) NOT NULL,
    [Description] [nvarchar](255) NOT NULL,
    [CreatedById] [bigint] NOT NULL,
    [CreatedDate] [datetimeoffset](7) NOT NULL,
    [ModifiedById] [bigint] NOT NULL,
    [ModifiedDate] [datetimeoffset](7) NOT NULL,
    [IsActive] [bit] NOT NULL,
    [StagingStatusId] [bigint] NULL,
    [IsApproved] [bit] NOT NULL,
    [IsActive] [bit] NOT NULL
)

Currently, I have the following query that groups the data by the status and then returns the count.

select StagingStatusId, count(1) AS Total
from I8Core.AccountCode
where IsActive = 1 and IsApproved = 1
group by StagingStatusId

However, as the number of records in the database table is higher (> 100k), the query gets slower and delayed.

The aim of this query is to know what statusids are higher than 999 and what are lesser than 999.

  For the records that are higher than 999 (Ex. if statusid = 1 & count
  = 1244, we need as statusid=1,count=999+), else, we need to return statusid=1, count=998 (for any value less than 999).

I found that the below query can return the total row count per table, but this cannot be applied to this scenario.

Kindly suggest the best practice to achieve this.

CREATE NONCLUSTERED INDEX IX_StagingStatusId ON Accounts(StagingStatusId)INCLUDE(IsActive, IsApproved);

SELECT T.StagingStatusId
     ,'StatCounts'= CASE WHEN T.Total>999 THEN '>999'
                         WHEN T.Total<999 THEN '<999'
                       END

 FROM (
select StagingStatusId, count(*) AS Total
from Accounts
where IsActive = 1 and IsApproved = 1
group by StagingStatusId
) AS T


Specifying the IP address to be used when connecting to an instance I have an application that was compiled with a hard-coded connection string (using the server's IP address '192.168.40.201' and not the server name) to an SQL Server.

A backup of the database has been restored on a stand-alone machine with no working network adapter and I would wish to be able to connect to this machine's SQL server using the IP address specified in the connection string of the application.

Is there any way to specify that the IP address to be used for this particular instance is '192.168.40.201'?

Have a look at How to setup and use a SQL Server alias

Excerpts below:

  Problem

  I have an application that has a specified database connection that I
  either can't or don't want to change. However, I need to move the
  database to a new server. How can I do this easily without breaking
  other things that maybe using this connection and/or database?

  Solution

  By using an alias, we can keep the application configuration the same,
  but tell the operating system to look somewhere else for the database.
  One proviso is that once an alias is created, it is set at the
  operating system level. That means if we have two applications which
  connect to MySQLServer on that computer, once the alias is created,
  both will be re-pointed based on the alias. You can create an alias
  using one of two utilities:

  SQL Server Configuration Manager
  SQL Server Client Network Utility

  SQL Server Configuration Manager

  Let's start by looking at how to do it using SQL Server Configuration
  Manager. In the left pane of SQL Server Configuration Manager, if you
  expand the SQL Native Client Configuration folder, there is a
  subfolder called Aliases (see Figure 1). If we click on this
  subfolder, we'll see any aliases that have been defined for the system
  shown in the right pane. In most cases, there won't be any defined. To
  create a new alias, right-click on Aliases and choose New Alias...
  from the pop-up menu.

Figure 1:

  A dialog window comes up (Figure 2) which allows us to specify our new
  alias. In the Alias Name property, you specify the name the
  application uses. For instance, if it connects to a SQL Server called
  MyOldServer, that's what you enter in here. You can also enter an
  alias that appears to point to a named instance, such as
  MyOldServer\MyInstance. You can then specify the server where the
  database really is, to include configuring the protocol, the server
  name, and the port. Clicking the OK button will create the alias. In
  the case of Figure 2, I'm creating an alias called MyOldServer which
  points to my local server's default instance.

Figure 2:

  You should now see the new alias listed in the right pane (Figure 3).

  The next step is to test the connection. You can do so quickly by
  either creating and testing a Data Source Name (DSN), or if you have
  the SQL Server client tools installed, by using SQL Server Management
  Studio (SSMS). I'll do so using SSMS. Figure 4 shows the dialog window
  to open a new connection to a database engine. For Server name:, enter
  in the alias you specified. In the example we called the alias
  OldServerName, so that's what we'll use here.

  Figure 4:

  If the alias is pointed correctly to the new location, a successful
  connection should be established. In SSMS, I was opening a new
  connection for Object Explorer. Figure 5 shows that the connection was
  successfully made using the alias. Note that as far as SSMS is
  concerned, the server name is the alias.

  Figure 5:


Is it possible to improve the execution performance of a query with 'simple' joins? The query is the following select statement:

select tbl1."DT_REF_TRANS", tbl1."VL_MTM",
       tbl1."VL_FUTURO", tbl1."DT_VENCIMENTO",
       tbl1."QT_PRAZODIASU", tbl1."ID_ATIVO_PASSIVO",
       tbl1."ID_CARTEIRA", tbl1."VL_MTM_ORIGEM",
       tbl1."ID_LOCAL_REGISTRO", tbl1."CD_NIVEL",
       tbl2."NO_ARQUIVO", tbl3."NO_PRODUTO",
       tbl3."ID_ENTRA_2804", tbl3."ID_ENTRA_VAR",
       tbl4."NO_MOEDA", tbl6."NO_SUBGRUPO",
       tbl5."NO_GRUPO_2804", tbl5."PERDA",
       tbl5."RENOVACAO", tbl5."PRZRENOV",
       tbl5."ANTECIPADU",
       tbl5."PRZANTECDU", tbl5."ANTECIPA",
       tbl5."DEPOSMAR", tbl5."RESERVA",
       tbl5."PERCATRASO",
       tbl5."PERCMORAM",
       tbl5."CALCATRASO", tbl5."NORMAL",
       tbl5."CODREGRA", tbl5."CODPRZAJU",
       tbl5."CD_NOVOMTM",
       tbl5."CD_DEPMARG",
       tbl5."ID_CONSOLIDA",
       tbl5."ID_TIPO_GRUPO",
       tbl7."NO_USUARIO_SIST",
       tbl8."NO_GRUPO_2804" as "GRUPOS_2804_NO_GRUPO_2804",
       tbl9."NO_GRUPO", "USUARIOS"."NO_USUARIO"
  from ((((((((("dbo".tbl1 tbl1
  left
  join "dbo".tbl2 tbl2
       on (tbl2."CD_ARQUIVO" = tbl1."CD_ARQ_ORIGEM"))
  left
  join "dbo".tbl3 tbl3
       on (tbl3."CD_PRODUTO" = tbl1."CD_PRODUTO"))
  left
  join "dbo".tbl4 tbl4
       on (tbl4."CD_MOEDA" = tbl1."CD_MOEDA"))
  left
  join "dbo".tbl6 tbl6
       on (tbl6."CD_SUBGRUPO" = tbl3."CD_SUBGRUPO"))
  left
  join "dbo".tbl5 tbl5
       on (tbl5."CD_GRUPO_2804" = tbl3."CD_GRUPO_2804"))
  left
  join "dbo".tbl7 tbl7
       on (tbl7."CD_USUARIO_SIST" = tbl5."CD_USUARIO_SIST"))
  left
  join "dbo".tbl8 tbl8
       on (tbl8."CD_GRUPO_2804" = tbl3."CD_GRUPO_2804"))
  inner join "dbo".tbl9 tbl9
       on (tbl9."CD_GRUPO" = tbl3."CD_GRUPO"))
  inner join "dbo"."USUARIOS" "USUARIOS"
       on ("USUARIOS"."CD_USUARIO" = tbl1."CD_NIVEL"))

You can see the execution plan here.

I've created and dropped indexes in columns inside the joins, have been testing with some hints ( like left loop join for example ( it helped me a lot in the past )). but, is there a way to improve a query that's not using where, or nothing else?

It's running in 30Seconds, returning 1M rows+.

Should I use IN somewhere? Exists? Some hint I don't know?

See, the question is not "hot to update THIS query". I would like to know tips to improve queries using this structure in general.

Many outer joins are not a best option when dealing with big tables. In a nutshell, your plan might not be as optimal as it could be when using outer joins.

Wrong RENDA_FIXA table estimation is the reason why you are getting Parallelism and Hash joins everywhere even though you are not expecting many rows in the first place.

You can try OPTION (MAXDOP 1) hint to see if that any helps.
Also you should check statistics on RENDA_FIXA and GRUPOS_USER_2804 tables.

The reason why you are getting that plan is because it expects 120,000 records, instead you're getting 1,800 that's why left loop joins helped you.

My advice is to try to deal with RENDA_FIXA table first, get the correct estimations out first, so the query optimizer could try to create a right plan.


is it possible to generate create index sql in MySQL 5.7 I have a table have many index, now I want to move the index to another table. Is it possible to auto generate the index create sql using SQL command? so I do not need to generate index by myself(generate create index sql by hande).

You can use SHOW INDEXES FROM table_name; to script the list of indexes on that table.


You need to create all customer_id/feedback_id combinations first and then left join to it.

Assuming there's a customer table with unique ids:

select x.customer_id, x.feedback_id, x.feedback_desc, cf.given_answers
from
 (
   select c.customer_id, f.feedback_id, f.feedback_desc
   from feedback as f
   cross join customer as c -- or (select distinct customer_id
                            --     from customer_feedback) as c
 ) as x
left join customer_feedback as cf
  on x.customer_id = cf.customer_id
 and x.feedback_id = cf.feedback_id ;

or, without a derived table:

select c.customer_id, f.feedback_id, f.feedback_desc, cf.given_answers
from feedback as f
  cross join customer as c -- or (select distinct customer_id
                           --     from customer_feedback) as c
  left join customer_feedback as cf
    on  c.customer_id = cf.customer_id
    and f.feedback_id = cf.feedback_id ;


How do these snapshot isolation level configurations interact on a SQL Server 2005 instance? I started troubleshooting a tempdb issue we are having on SQL Server 2005 Enterprise Edition. A developer is getting an error that tempdb is out of space. Technically, the error is:

  Transaction aborted when accessing versioned row in table
  'dbo.inserted' in database 'dbname'. Requested versioned row was not
  found. Your tempdb is probably out of space. Please refer to BOL on
  how to configure tempdb for versioning

I looked at the database configuration in sys.databases and found the following settings:

snapshot_isolation_state: 0

snapshot_isolation_state_desc: OFF

is_read_committed_snapshot_on: 1

I looked up what this meant in BOL and the salient information is as follows:

  snapshot_isolation_state

  State of snapshot-isolation transactions being allowed, as set by the ALLOW_SNAPSHOT_ISOLATION option:

  0 = Snapshot isolation state is OFF (default). Snapshot isolation is disallowed.

  1 = Snapshot isolation state ON. Snapshot isolation is allowed.

  2 = Snapshot isolation state is in transition to OFF state. All transactions have
  their modifications versioned. Cannot start new transactions using
  snapshot isolation. The database remains in the transition to OFF
  state until all transactions that were active when ALTER DATABASE was
  run can be completed.

  3 = Snapshot isolation state is in transition to
  ON state. New transactions have their modifications versioned.
  Transactions cannot use snapshot isolation until the snapshot
  isolation state becomes 1 (ON). The database remains in the transition
  to ON state until all update transactions that were active when ALTER
  DATABASE was run can be completed.

  snapshot_isolation_state_desc

  Description of state of
  snapshot-isolation transactions being allowed, as set by the
  ALLOW_SNAPSHOT_ISOLATION option:

  OFF
  ON
  IN_TRANSITION_TO_ON
  IN_TRANSITION_TO_OFF

  is_read_committed_snapshot_on

  1 = READ_COMMITTED_SNAPSHOT option is ON. Read operations under the
  read-committed isolation level are based on snapshot scans and do not
  acquire locks.

  0 = READ_COMMITTED_SNAPSHOT option is OFF (default). Read operations
  under the read-committed isolation level use share locks.

If I'm understanding this correctly, snapshot_isolation_state is OFF and disallowed. However, the 1 on is_read_committed_snapshot_on indicates that the database is using snapshot isolation. How is this possible if it's disallowed?

As @AlexKuznetsov noted, SNAPSHOT and READ_COMMITTED_SNAPSHOT are two different isolation levels. As such, snapshot_isolation_state indicates the former, while is_read_committed_snapshot_on the latter. Here are a few comments that I found that summarize the differences between the two:

READ COMMITTED SNAPSHOT does optimistic reads and pessimistic writes.
In contrast, SNAPSHOT does optimistic reads and optimistic writes.
~Bill Paetzke here

and ...

[READ_COMMITTED_SNAPSHOT] differs from the SNAPSHOT isolation level in
that instead of providing a reader with the last committed version of
the row that was available when the transaction started
(SNAPSHOT ISOLATION), a reader gets the last committed version of the
row that was available when the statement started
(READ_COMMITTED_SNAPSHOT).
~Uri Dimant (emphasis mine), here


Disable trigger effects in PostgreSQL I have a function where there is:

alter table a disable trigger trig1;
some code
alter table a enable trigger trig1;

It basicaly disables the trigger for the purpose of specific operations.

My question is:

What will happen if at the same time the trigger is disabled, another user from another session makes operations on a which relies on that trigger to be triggered?

ALTER TABLE ... DISABLE TRIGGER ... takes a SHARE ROW EXCLUSIVE lock. So no relevant concurrent operation can happen on the table:

  DISABLE/ENABLE ... TRIGGER

  This command acquires a SHARE ROW EXCLUSIVE lock.

where the docs on lock levels say that:

  SHARE ROW EXCLUSIVE

  ... This mode protects a table against concurrent data changes, and is self-exclusive so that only one session can hold it at a time.

Despite the name, a SHARE ROW EXCLUSIVE lock is a table-level lock. The name is historical.

Now, PostgreSQL has a mechanism you can (ab)use to skip triggers firing on certain operations. It's intended for replication tools like Slony-I and Londiste, and is called session_replication_role. Be aware that using it for other purposes may have "surprising" consequences if you then start using a logical replication tool.


Indexing strategy for dynamic predicate Assume SQL Server 2012 Standard edition.

My database has a table with 500 million rows. The table has about a dozen columns, none of which are very wide (some varchar(100)'s and some ints).

The clustered index (also primary key) is an identity column.

The application using this table has a screen where the user can search on most of the columns. One search field on the screen, which is required, has the option to search starts with or contains, resulting in either

WHERE ABC LIKE 'something%' -- starts with

or

WHERE ABC LIKE '%something%' -- contains

The actual queries are parameterized, unlike my examples here.

The other search fields do a starts with search just like the first example above, but they are not required. So, any combination of these fields can be searched on resulting in a dynamic where clause.

Given this information, what indexes should be created for optimal performance?

Bearing in mind that I'm new to query performance tuning, my naive strategy for this scenario alone is to create a non-clustered index for each column and using full text search for the column that has the contains search option. I'd love to hear why or why not that's a bad idea and what a better approach would be.

Update

It's known to me that full text searching is how to optimize the case of a "contains" search.

I'm much more interested in the other aspect of the problem: how to optimize for the other search fields which may or may not be present in any given query predicate. The details surrounding the field that can benefit from a full text index are included in my question only to help paint a more complete picture of my particular situation.

If I were you I would run a trace specific to hits on that table.  It shouldn't be overly intensive since you are restricting it to just queries against that table, from your application.  Run just the minimum needed by the DTA (Database Tuning Advisor).  Run it for a day here, a day there, make sure you get some end of week days and some end of month days.  Then run the whole lot through the DTA.

Here is why, I'm willing to bet that you have specific combinations of columns that are going to come up more often than not.  You can create more complex indexes based on that information.  You might also find that you can create some correlated statistics. Basically statistics that have more than one column.  For example creating a statistic on City and State together may improve queries against those two columns.

However make sure you don't create to many indexes.  On a table that large I'm guessing you do a fair number of writes and every additional index added will slow them down.  Of course you may do most of your writes during a batch process.

Also make sure that you put an automatic process to update your statistics periodically.  With that many rows the statistics aren't going to update on their own very often.  Only once 500+20% of the rows have changed, in at 500 mil rows that's a LOT.


How to change the Write Ahead Log directory How do you change the defined WAL directory?
During the installation I defined a directory, /var/lib/wal and the $PGDATA/pg_xlog has a symbolic link pointing to that directory.

How do I change the postgres setting to write the WAL files elsewhere - without having to re-install?

This can be done according to the documentation [here]:(http://www.postgresql.org/docs/9.1/static/wal-internals.html)

  It is advantageous if the log is located on a different disk from the
  main database files. This can be achieved by moving the pg_xlog
  directory to another location (while the server is shut down, of
  course) and creating a symbolic link from the original location in the
  main data directory to the new location.


Selecting difference between two tables returns different number of results than counts of same two tables I'm using SQL Server and when I run
SELECT * FROM TABLE1 EXCEPT SELECT * FROM TABLE2
it returns 1610 entries. When I query

SELECT COUNT(*) FROM TABLE1
SELECT COUNT(*) FROM TABLE2

I get 6399 and 4583, the difference of which is 1546, not 1610 as I would expect from the first query. What could be causing this discrepancy between the number of results returned and the difference of the individual table counts?

The EXCEPT operator is not going to limit the rows returned from TABLE1 to the difference between the row counts in TABLE1 and TABLE2 - it will eliminate from the resultset every row in TABLE1 that has a matching row in TABLE2.

In your example, the difference in row counts is 1816 but your resultset is 1610 entries. This means there are 4789 entries in TABLE1 that match a row in TABLE2, suggesting that some of the rows in TABLE1 are not unique.

You can test this out by running SELECT COUNT(DISTINCT *) FROM TABLE1. If the value is less than 6399, then there are duplicate rows of data and some of these are likely being eliminated by the EXCEPT operator hence the difference in row counts.


Commercial database systems versus open source database systems Can anyone tell me what are the reasons for buying a commercial database system and not using an open-source free version?

The reason for my question is that:

Commercial systems cost upwards of 10.000 $
The most popular websites (e.g. Twitter, Facebook, etc.) all use some open-source engine (for e.g. Twitter used MySQL and is switching slowly to Cassandra), thus proving the reliability of such systems

Is there any financial and/or technical advantage that commercial systems have over the open-source alternatives?

This really depends on your requirements. Many systems built on top of open source products are rock solid.

Note that your example does not impress me - Twitter is not reliable at all by my customers' standards - it has frequent downtimes, it loses tweets and followers, its search is not 100% correct etc. I work in finances, and my job is to develop robust and performant systems. In some (but not all) of the scenarios I encounter, open source PostgreSQL could get the job done just as well as closed source Oracle, in terms of speed and robustness.

If your needs are more or less standard, than you can save a lot of precious time if you go for Oracle or SQL Server, as you might spend less time on setting things up and maintenance. As such, your investment in licensing costs may pay off quite soon.

If, however, your needs are not completely mainstream, then open source is your friend. Open source products can innovate much faster. You don't have to persuade someone that your scenario is not "convoluted", you can get a fix right away instead of waiting for years. If you really need something, you can do it yourself.


Log Shipping with Failover Cluster Server Cores Failover Over Cluster 2 Node - 6 Cores- Enterprise Edition 2014
Log Shipping with Cluster with 4 Core License- Enterprise Edition 2014

My question is if I need to log ship do I need to match the server core of the Primary server that is the failover cluster (6 core) in my case or I can go with 4 core on the Log Shipped server to save licensing costs?

No, you don't need to match hardware. There's no technical requirement for that.

Lots of people choose to use slightly less robust hardware at DR

Saves on licensing costs
Offloaded reads may not need full production power
Assume that DR failovers aren't permanent

That said, whatever lower tier of hardware you choose has to be able to support your current workload.

In general, you want to make sure you're not hitting any really bad resource waits like THREADPOOL -- if you're hitting it on 6 cores, you'll definitely hit it on 4 cores.

If you have less RAM at DR, be aware of RESOURCE_SEMAPHORE waits.

The only way to tell for sure is to coordinate a failover and measure your server's response to your workload.

Hope this helps!


mysql multi threading for a single query I have big db of size 8GB and having 9 million contacts in this db. When we try to retrieve all contacts, it takes nearly 1 or 2 hour (in some cases result in timeout) to retreive all contacts for the excecution for a single query. Also I have 8 CPU and 24GB RAM, Xeon Processor, the mysql process won't distribute accross the 8 CPU's. My question is that, does we can enable multi threading for a single query.

Awaiting for your Reply

Neither MyISAM nor InnoDB have multithreaded query implementation. Nor do other XtraDB, TokuDB.
It is a long standing issue and limitation of MySQL. I don't know that the limitation is within the MySQL server, but rather in the storage engine implementation. No one ever did it...
I'm currently evaluating the column-store InfiniDB and Infobright IEE. They both support multi-cpu queries and the difference is overwhelming (due to multiple reasons, but CPU being obvious one).
You might want to look at Shard Query, a proxy to MySQL which splits your query into multiple queries, executes them on parallel processes and returns the aggregated result.


Scenario/Method (idea) for transfering database from one server to another The scenario is next.
Every day on linux mysql server script copy from samba share wich is "/var/lib/mysql/db2223" to another backup server over network lets say windows server. Database has lets say 200 GB of data.

One day linux mysql server get down and cannot be fixed.

Method: Take another machine install on it linux server configure and then create again in phpmyadmin only name of db "db2223" and copy all from backup to new "var/lib/mysql/db2223" directory.

Is this method good or bad ?

If you are using InnoDB, the "backup" is useless.  ibdata1 is needed.  And there are other issues.

If you are using MyISAM, it will probably work.


The server principal "loginname" is not able to access the database "SSISDB" under the current security context We have a stored procedure which calls an SSIS package from the SSISdb database.

It works when run as myself using my domain account, however the VB6 application which will call this connects to the databases via a SQL account.

SSIS needs to be run via a windows authentication account, so to get around changing the code to connect as a windows account, which is a big task apparently.. within the stored procedure that calls the ssis package, I added the "WITH execute as 'domain login'".

When you then run the stored procedure it comes up with the error:

  Msg 916, Level 14, State 1, Procedure "SP", Line 17 [Batch Start Line
  10] The server principal "windows account" is not able to access the
  database "SSISDB" under the current security context.

It has all of the correct permissions to the SSISDB, it has been granted ssisadmin under the msdb database.

I have also checked that its not an orphaned user against the databases - that's all fine.

I even made the login sysadmin briefly to test that - and the same error still appears which is strange.

I confirm that the SSIS package does not access any file shares or anything like xp_cmdshell.

It works if I log in as the domain account that's in the EXECUTE AS, so it seems it doesn't like the EXECUTE AS statement.

Any ideas what is going on here?

"EXECUTE AS" works by default only within one database.

You can overcome this by enabling cross-database ownership chaining. But this implies security issues!

Please have a look at books online: https://docs.microsoft.com/en-us/dotnet/framework/data/adonet/sql/enabling-cross-database-access-in-sql-server

An alternative solution is to move the calling procedure into the SSIS database and grant the VB script user execution permission on that particular procedure. Within that procedure you can then use "EXECUTE AS" to call the second procedure with the privileges needed.


MariaDB/MySQL: Import from dump (.sql) file remotely via network After I set my.ini file following the answer by Rolando here : https://dba.stackexchange.com/a/83385/191579 , today I managed to import about 40GB+ sqldump file in around 2-3 hours only. While on a different server; where I have set similar setting yesterday and importing around 20+GB data is still running today and I check it only managed to import less than half of the data inside (70+ million from a 200+ million rows).
Specs for both server are following:
Server 1 - import 40+GB data in 2-3 hours:
Intel Xeon E3 3.00 Ghz
16GB RAMs
MariaDB 10.3
Server 2 - import 20+GB data only managed to import less than half in 24hr:
Intel Xeon E3 3.40 Ghz
16GB RAMs
MariaDB 10.3
Clearly both server have almost similar specs but I don't understand why the other take so long to dump. Both of these database are not production/live db.
I usually opt to do import/dump locally for big files because I'm on the impression that it's much faster that way (obviously) but this case made me think of the otherwise. I'm currently taking the dump file into my PC to split it in smaller chunks and while waiting, I'm just wondering whether if I do import from a different pc via network (LAN etc.), will the process depending on the remote server pc specs or the local pc specs?
Edit:
I was looking around and found this : https://stackoverflow.com/a/12439368/10910692

Use:
mysqldump --extended-insert=FALSE
Be aware that multiple inserts will be slower than one big insert.

The 20+GB I dumped with this setting. Could it be the cause?
my.ini setting on Server 1 & Server 2:
[mysqld]

innodb_buffer_pool_size=1024M
innodb_file_per_table=1
skip-host-cache
skip-name-resolve

innodb_log_buffer_size = 256M
innodb_log_file_size = 1G
innodb_write_io_threads = 16
innodb_flush_log_at_trx_commit = 0
innodb_doublewrite=0

After a few tests, I conclude that the server itself (where I want to import) is slow due to having a lot of process going on. I think that was obvious from the beginning but I was just frustrated that I've set identical setting on both machines and getting a significantly different benchmarks.

The last test that I did was the following, 200MB sql dump file size I import to the destination server (server 2) using two method; locally & remotely; bench marked against another server unit I have locally (server 1). Few more details:

Server 1 - CPU load 20% - 30%  and RAM load 50% - 60%
Import on MariaDB 10.3 for 200MB sql dump file took less than 2 minutes.
Server 2 - CPU load 50% - 60% and RAM load 50% - 60%
Import on MariaDB 10.3 for 200MB sql dump file took up to 8 minutes locally.
Import on MariaDB 10.3 for 200MB sql dump file took up to 18 minutes remotely.

Like I mentioned, both servers have similar my.ini settings. I did not investigate further why Server 2 is slower but the import perform miles better when I discard mysqldump --extended-insert=FALSE setting on my dump files after finding that this causes extremely slow inserts!. The other obvious probable reason would be the server is busy (hint: CPU load). Also, the data that I need to import is actually for another party to use but since the time zone difference between us and them is 6 hours (I just found out about this!), I didn't pursue this aggressively.


What is the best practices to backup and restore full and partial database with SQL Server 2012 and C#? How can I do the following with C#:

Completely backup an existing database.
Partially backup an existing database.
Completely restore an existing database from a backup file.
Partially restore an existing database from a backup file.

My database saves images on the HARDDISK sometimes I need to backup and restore the database without the stored Images (might be 50 GB).

I need partially backup that execluding the FILESTREAM data
(The saved files should be not included in the backup file).

I need partially restore that retrive the database without FILESTREAM data (The stored files should be not restored).

What is the best practices to do that?

Remmber you need the following assemblies:
Microsoft.SqlServer.ConnectionInfo
Microsoft.SqlServer.Management.Sdk.Sfc
Microsoft.SqlServer.Smo
Microsoft.SqlServer.SmoExtended

More info http://technet.microsoft.com/de-de/library/microsoft.sqlserver.management.smo.aspx

using System;
using System.IO;

namespace ConsoleApplication2
{
 using Microsoft.SqlServer.Management.Common;
 using Microsoft.SqlServer.Management.Smo;

 public class SQLServerTools
 {
      public static void SqlBackup(
           // Sql Server name.
        string serverName,
           // The database name on which the backup operation runs.
        string databaseName,
           // The desired backup filename.
        string destinationBackupFilename,
           // this if you want to back partial database without files
    bool partial)
      {
           Console.WriteLine(
             "Backup on the server {0} the database {1} to the file {2}",
             serverName,
             databaseName,
             destinationBackupFilename);

           // Connect to default sql server instance.
           var sqlServer = GetSqlServer(serverName);

           // Define a Backup object variable.
           var sqlBackup = new Backup { Database = databaseName, };

           // Specify the type of backup, the description, the name, and the database to be backed up.
           if (partial)
           {
                sqlBackup.Action = BackupActionType.Files;
                sqlBackup.DatabaseFileGroups.Add("PRIMARY");
           }
           else
           {
                sqlBackup.Action = BackupActionType.Database;
           }

           // Declare a BackupDeviceItem.
           var deviceItem = new BackupDeviceItem(destinationBackupFilename, DeviceType.File);

           // Add the device to the Backup object.
           sqlBackup.Devices.Add(deviceItem);

           sqlBackup.Initialize = true;
           sqlBackup.ContinueAfterError = false;

           // Set the Incremental property to False to specify that this is a full database backup.
           sqlBackup.Incremental = false;
           sqlBackup.PercentCompleteNotification = 1;

           // Specify that the log must be truncated after the backup is complete.
           sqlBackup.LogTruncation = BackupTruncateLogType.Truncate;

           sqlBackup.PercentComplete += ProgressEventHandler;
           sqlBackup.Complete += ProcessCompleted;

           // Run SqlBackup to perform the full database backup on the instance of SQL Server.
           sqlBackup.SqlBackup(sqlServer);

           // Remove the backup device from the Backup object.
           sqlBackup.Devices.Remove(deviceItem);

           // Disconnect and dispose the connection.
           sqlServer.ConnectionContext.Disconnect();
      }

      public static void SqlRestore(
           // Sql Server name.
        string serverName,
           // The backup file directory.
        string backupFilePath,
           // The database directory.
        string databaseFolder,
           // The desired restored database name.
        string destinationDatabaseName,
           // The backup filename.
        string backupFilename,
           // The database log name.
        string databaseLogFileName,
           // This will restore the database in partial mode for more info about partial check in the stackoverflow
        bool partial)
      {
           // Current console cursor position.

           //Define a Backup object variable.
           var sqlRestore = new Restore
                              {
                                   Database = destinationDatabaseName,
                                   NoRecovery = false,
                                   ReplaceDatabase = true,

                                   // Specify the type of backup, the description, the name, and the database to be backed up.
                                   Action = RestoreActionType.Database
                              };

           var sqlServer = GetSqlServer(serverName);

           // If the backup directory not found then get the default.
           if (string.IsNullOrEmpty(databaseFolder))
           {
                const string SqlDataDirectory = "DATA";
                databaseFolder = sqlServer.InstallDataDirectory + Path.DirectorySeparatorChar + SqlDataDirectory;
           }

           // Declare a BackupDeviceItem
           var deviceItem = new BackupDeviceItem(
             backupFilePath + Path.DirectorySeparatorChar + backupFilename,
             DeviceType.File);

           const string SqlMdfExtention = ".mdf";
           const string SqlLdfLogExtention = "_log.ldf";

           var dataFileLocation = databaseFolder + Path.DirectorySeparatorChar + destinationDatabaseName + SqlMdfExtention;
           var logFileLocation = databaseFolder + Path.DirectorySeparatorChar + destinationDatabaseName + SqlLdfLogExtention;

           sqlRestore.RelocateFiles.Add(new RelocateFile(destinationDatabaseName, dataFileLocation));
           sqlRestore.RelocateFiles.Add(new RelocateFile(databaseLogFileName, logFileLocation));

           sqlRestore.ReplaceDatabase = true;
           sqlRestore.ContinueAfterError = false;

           sqlRestore.PercentCompleteNotification = 1;
           sqlRestore.PercentComplete += ProgressEventHandler;

           if (partial)
           {
                sqlRestore.Partial = true;
                sqlRestore.ContinueAfterError = true;
           }

           sqlRestore.Complete += ProcessCompleted;

           sqlServer.ConnectionContext.StatementTimeout = 60 * 60;

           // Add the device to the Restore object.
           sqlRestore.Devices.Add(deviceItem);

           // Run SqlRestore to perform the database restore on the instance of SQL Server.
           sqlRestore.SqlRestore(sqlServer);

           var database = sqlServer.Databases[destinationDatabaseName];
           database.SetOnline();

           // Remove the restore device from the restore object.
           sqlRestore.Devices.Remove(deviceItem);

           // Disconnect and dispose the connection.
           sqlServer.ConnectionContext.Disconnect();
      }

      private static Server GetSqlServer(string servername)
      {
           // Define Server connection
           var serverConnection = new ServerConnection(servername);
           var sqlServer = new Server(serverConnection);
           sqlServer.ConnectionContext.StatementTimeout = 60 * 60;
           return sqlServer;
      }

      private static void ProgressEventHandler(object sender, PercentCompleteEventArgs e)
      {
           Console.Write(e.Percent);
      }

      private static void ProcessCompleted(object sender, ServerMessageEventArgs e)
      {
           // Write if any error is exist.
           Console.WriteLine(e.Error.Message);
      }
 }
}


Saving arrays in database - PDO I want to save an array in my database friends column.  Actually I want to create friend request system.  My method is a little bit different.  I want to have two columns for this system.

______________________________________________________________________
| Name   | Friends                   |  Requests                     |
| Name1  | array(person1,person2..)  |  array(request1, request2..)  |
| Name2  | array(person1,person2..)  |  array(request1, request2..)  |
|________|___________________________|_______________________________|

When name1 adds name2 the name1 name must be put in requests of name2 when name2 accepts the requests the name1 will be removed from name2 requests and added to both name1 and name2 friends.

Now the problem is that I have no idea how to put an array into a column cell and how to get the name when needed from it by a select query.  I have just worked with simple queries a lot but not this kind of thing.

You should re-evaluate your database design. A relational database is not designed to store arrays in columns. Actually, elements in arrays are "columns" itself. So, what you're trying to do is store multiple columns in a single column...

I'd recommend two tables:
Persons (PersonId, Name,...)
Friends (FriendsId, PersonId, FriendId, Accepted), where both, PersonId and FriendId have a foreign key to Persons.PersonId. The column Accepted will be Zero as long as it is not accepted. When accepted, just change it to 1. Therewith, you don't have to move rows arond and take care that there are no inconsistent data (or even missing data).


Deadline rule date input after now Hi I'm trying to find a way to ensure deadlines inputted are later than the current time and date.
Currently I'm attempting this with a rule.

CREATE OR REPLACE RULE "DeadlineInput" AS
    ON INSERT TO "ComLog"
   WHERE new."Deadline" < now() DO INSTEAD  SELECT "ThrowError"('New deadline must be after current time'::bpchar) AS "ThrowError";
COMMENT ON RULE "DeadlineInput" ON "ComLog" IS 'Ensures deadline input is after the current time.';

I'm using a separate function to try to throw an error as when I tried to do it within the Rule it failed as the language was wrong.
The following is my function.

-- Function: "ThrowError"(character)

-- DROP FUNCTION "ThrowError"(character);

CREATE OR REPLACE FUNCTION "ThrowError"("ErrorString" text)
  RETURNS void AS
$BODY$

BEGIN

    RAISE EXCEPTION '%', "ErrorString";

END;

$BODY$
  LANGUAGE plpgsql IMMUTABLE
  COST 100;
ALTER FUNCTION "ThrowError"(character)
  OWNER TO reuben;

This always produces the error message regardless of the date and i don't know why. As a side question is there a way to make the string passed to my function contain the current time. Something like "ThrowError"('New deadline must be after current time %' now())

Thanks

Here's what the current manual says in Rules Versus Triggers:

  If checks for valid values are required, and in the case of an invalid
  value an error message should be generated, it must be done by a
  trigger.

Consider implementing the check in a trigger, not a rule. The trigger is the perfect tool for the job in your case. On the other hand, it is a known fact that rules are touchy and much harder to master.


How else to troubleshoot LCK_M_U from SQL Server? We have an INSERT statement coming from an Entity Framework application that has been causing pretty large blocking chains. The Wait Time and Wait Type in our monitoring tools for that process is empty, so it doesn't look like it's waiting on anything. This makes me believe that the application is opening a transaction, doing an insert and just not committing it.

This has been an intermittent issue so it's been hard to troubleshoot. Development team is looking into the code but insistent that nothing has changed. What else can I look at on the SQL Server side?

SQL Server 2012 SP2

Do you know exactly what the INSERT statement is and what line of EF code generates it? Just wrap whatever calls that around some logging code and log all the SQL to a text file (preferably using log4net, nlog, ETW etc.

Just wrap the code that calls the insert code with a call to set and unset the delegate described here.

e.g.

 ctx.Database.Log = (dbLog => log.Debug(dbLog));
// Do the insert
ctx.Database.Log = null;

Honestly, if you can deal with a slight slowdown in performance, and have the disk space just log everything. I log all the SQL in my Always Encrypted Sample app.

Assuming your INSERTS are wrapped around transactions, you should be able to see that instantly with this. Make sure your logging has timestamps so you can see how long the transaction remains open for.


Problem with unmasking a database user during a sproc I am having an issue with a stored procedure that refreshes dynamic data masking on a database. This sproc is run as a job against all databases on this server.
The problem that I have is that I have a system database user that handles API calls to the application that these DBs are a part of. We'll call the user "apiuser". The user needs to be unmasked in order to function.
Not every database has this user for various reasons (API is not enabled on all databases by default), so when the sproc runs, it should attempt to unmask the user only if they exist on that DB. What I came up with to do that was this:
if exists (select name from [sys].[database_principals] where name=N'apiuser')
GRANT UNMASK TO apiuser;

This line works fine when run on its own as a statement. However, when run as part of a stored procedure, it does not work. There is no error, the sproc runs just fine, it just does not unmask the user. My guess is for whatever reason the sproc is not handling the if exists part right, and so skips this component.
SET NOCOUNT ON;
GRANT UNMASK TO systemuser1;
GRANT UNMASK TO systemuser2;
if exists (select name from sys.database_principals where name='ApiUser')
  EXEC('GRANT UNMASK TO apiuser');
ALTER TABLE Table1 ALTER COLUMN Field1 ADD MASKED WITH (FUNCTION = 'default()');
ALTER TABLE Table2 ALTER COLUMN Field1 ADD MASKED WITH (FUNCTION = 'default()');
ALTER TABLE Table2 ALTER COLUMN Field2 ADD MASKED WITH (FUNCTION = 'default()');
END

Worth noting the other two unmasks (the other system users) work just fine. It's only the one using the IF EXISTS that does not.

To be honest, I've never used SQL Server masking before so this is mostly a guess, but if you try to run just the line of code EXEC('GRANT UNMASK TO apiuser'); directly, does it work?
I'm thinking by executing dynamic SQL you're running into the same limitation as if you created a temp table with dynamic SQL and then tried to access that table outside of the dynamic SQL after (it's not possible).
I can't find exactly why that's how SQL Server works with dynamic SQL, but here's some relevant posts (regarding temp tables) and I bet you're experiencing the same thing with unmask:
SQL Authority: SQL Server Dynamic SQL and Temporary Tables
StackOverflow: T-SQL Dynamic SQL and Temp Tables
As a solution, can you run just the GRANT UNMASK directly (eliminate the dynamic SQL) after the IF instead like your first example?
if exists (select name from [sys].[database_principals] where name=N'apiuser')
GRANT UNMASK TO apiuser;


In SQL Server, How Does Parallelism Change Memory Grants? I've heard conflicting things about memory grants for parallel select queries:

Memory grants get multiplied by DOP
Memory grants get divided by DOP

Which is it?

Sup?

For SQL Server queries that require additional memory, grants are derived for serial plans. If a parallel plan is explored and chosen, memory will be divided evenly among threads.

Memory grant estimates are based on:

Number of rows (cardinality)
Size of rows (data size)
Number of concurrent memory consuming operators

If a parallel plan is chosen, there is some memory overhead to process parallel exchanges (distribute, redistribute, and gather streams), however their memory needs are still not calculated the same way.

Memory Consuming Operators

The most common operators that ask for memory are

Sorts
Hashes (joins, aggregates)
Optimized Nested Loops

Less common operators that require memory are inserts to column store indexes. These also differ in that memory grants are currently multiplied by DOP for them.

Memory needs for Sorts are typically much higher than for hashes. Sorts will ask for at least estimated size of data for a memory grant, since they need to sort all result columns by the ordering element(s). Hashes need memory to build a hash table, which does not include all selected columns.

Examples

If I run this query, intentionally hinted to DOP 1, it will ask for 166 MB of memory.

SELECT *
FROM
     (
        SELECT TOP (1000)
               u.Id
        FROM dbo.Users AS u
        ORDER BY u.Reputation
     ) AS u
OPTION(MAXDOP 1);

If I run this query (again, DOP 1), the plan will change, and the memory grant will go up slightly.

SELECT *
FROM (
        SELECT TOP (1000)
               u.Id
        FROM dbo.Users AS u
        ORDER BY u.Reputation
     ) AS u
JOIN (
        SELECT TOP (1000)
               u.Id
        FROM dbo.Users AS u
        ORDER BY u.Reputation
     ) AS u2
ON u.Id = u2.Id
OPTION(MAXDOP 1);

There are two Sorts, and now a Hash Join. The memory grant bumps up a little bit to accommodate the hash build, but it does not double because the Sort operators cannot run concurrently.

If I change the query to force a nested loops join, the grant will double to deal with the concurrent Sorts.

SELECT *
FROM (
        SELECT TOP (1000)
               u.Id
        FROM dbo.Users AS u
        ORDER BY u.Reputation
     ) AS u
INNER LOOP JOIN ( --Force the loop join
        SELECT TOP (1000)
               u.Id
        FROM dbo.Users AS u
        ORDER BY u.Reputation
     ) AS u2
ON u.Id = u2.Id
OPTION(MAXDOP 1);

The memory grant doubles because Nested Loop is not a blocking operator, and Hash Join is.

Size Of Data Matters

This query selects string data of different combinations. Depending on which columns I select, the size of the memory grant will go up.

The way size of data is calculated for variable string data is rows * 50% of the column's declared length. This is true for VARCHAR and NVARCHAR, though NVARCHAR columns are doubled since they store double-byte characters. This does change in some cases with the new CE, but details aren't documented.

Size of data also matters for hash operations, but not to the same degree that it does for Sorts.

SELECT *
FROM
     (
        SELECT TOP (1000)
                 u.Id          -- 166MB (INT)
               , u.DisplayName -- 300MB (NVARCHAR 40)
               , u.WebsiteUrl  -- 900MB (NVARCHAR 200)
               , u.Location    -- 1.2GB (NVARCHAR 100)
               , u.AboutMe     -- 9GB   (NVARCHAR MAX)
        FROM dbo.Users AS u
        ORDER BY u.Reputation
     ) AS u
OPTION(MAXDOP 1);

But What About Parallelism?

If I run this query at different DOPs, the memory grant is not multiplied by DOP.

SELECT *
FROM (
        SELECT TOP (1000)
               u.Id
        FROM dbo.Users AS u
        ORDER BY u.Reputation
     ) AS u
INNER HASH JOIN (
        SELECT TOP (1000)
               u.Id
        FROM dbo.Users AS u
        ORDER BY u.Reputation
     ) AS u2
ON u.Id = u2.Id
ORDER BY u.Id, u2.Id -- Add an ORDER BY
OPTION(MAXDOP ?);

There are slight increases to deal with more parallel buffers per exchange operator, and perhaps there are internal reasons that the Sort and Hash builds require extra memory to deal with higher DOP, but it's clearly not a multiplying factor.


Multiple Similar Tables vs One Master Table I'm a web app developer and less experienced in database design, so would appreciate anyone's input.

When I first built this web app, the app managed one type of object (table) with a handful of properties (columns). As the app grew, there were additional types of very similar objects, so I created additional tables for each object type.  (You could say that all the object types inherit from the same class/prototype.) I'm finding that my tables for each object type are all nearly identical because each object type shares ~ 90% of the same property types.  For example, the tables may have nearly the exact same column names (& data types), but one table may have 1-2 extra columns to refer to properties specific to that object type.

Since all the object types are so similar (in the same way that Dog, Cat, and Mouse all inherit from the Animal class), would it be wise to put these all in the same table and simply allow some columns to contain NULL values when the columns are not relevant to that type?  Or is there some other alternative I'm not considering?

For reference, here are some similar posts I've already reviewed:

1) What are the design alternatives when having multiple "type" tables

2) https://stackoverflow.com/questions/9021838/database-design-multiple-tables-vs-a-single-table

3) Database design: Dividing multiple identical tables, good or bad?

Create the Animal table which has information that all animals share. Add another column to the Animal table called type or something similar. In this, specify whether the animal is a cat, dog, mouse, or whatever.

Then, create Cat, Dog, and Mouse tables that have the Animal ID (The primary key) as a foreign key. Each of those tables has columns specific for Cats, Dogs, and Mice specifically. The Mouse table might have a column Favorite_cheese or some such, which the normal animals table would not. To get the full mouse record, you can join the two tables together.

This allows you to have a relatively lean Animal table while still supporting subclassing functionality.


rename a column without disrupting users You can rename a column using the following statement:
ALTER TABLE table_name RENAME COLUMN column_name TO new_column_name;

However, this brings the old column out of commission immediately. Any use sites that used the old column name will start having problems.
Is there a way to put the table into a transitory state in which both the old and new name can be used to refer to the column, so that I can move over my clients and then afterwards decommission the old name?

Rename the column, add a generated column with the old name.  If inserts or updates of the old column haven't been corrected yet, provide insert and update triggers to handle the new column.  When no logic refers to the old column anymore, drop it.


how to create trigger in SQL server using two tables to find stock quantity I have two table sales and purchase. I want to create a trigger which stores final quantity in stock table by subtracting sales table sold quantity from purchase table.

There is kikkat as a name n quantity are 10, 15 respectively and both quantities should be subtracted from purchase quantity table and it should be saved in the stock table.

Instead of creating a trigger I would suggest you to create a new column in the purchase table which will be a computed column.

Before creating it, create a scalar function which will accept productname or productid and returns int. Inside it, write a query to find the total from sales for that particular row (by passing productname or id) and subtract it from purchase's quantity for that product.

If purchase can contain multiple entries for a particular product then create a separate table Stock and have columns as ID, ProductName\Id, stockQuantity (which will be a computed column as explained above).


Moving MAX and MIN calculation into a sub query to preserve the full result set in the outer query The query's objective in brief is to list all the posts according to its score, which is calculated according to a calculate scores of date, views, and rating

The problem with the query is that it only give 1 row result instead of showing all of the posts.

I think the problem is with using MAX() and MIN() without separating it from the LEFT JOIN query used to get the SUM() of the Rating and the Views.

THE MCVE http://sqlfiddle.com/#!9/70d1ec/1

SELECT
          p.id
         , p.date
         , p.title
         , r.module
         , r.module_id
         , IFNULL(v.total_views,0)  AS views
         , r.total_rating           AS rating

     #the following formula calculates the Score 10*(MAX(points)-(points))/(MAX(points)-MIN(points))

    , round((round(10-(((PD.MaxDate-p.date)/(PD.MaxDate-PD.MinDate))*10), 3) + round(10-(((MAX(v.total_views)-v.total_views)/(MAX(v.total_views)-MIN(v.total_views)))*10), 3) + round(10-(((MAX(r.total_rating)-r.total_rating)/(MAX(r.total_rating)-MIN(r.total_rating)))*10), 3))/3, 3) AS Score

  FROM  posts p

  LEFT  JOIN ( SELECT ra.module_id
              , ra.module   AS module
              , SUM(ra.ilike)    AS total_rating
           FROM rates ra
          WHERE ra.module = 'posts'
          GROUP
             BY ra.module_id
       ) r ON r.module_id = p.id

  LEFT  JOIN ( SELECT pv.post_id
              , SUM(1)    AS total_views
           FROM posts_views pv
          GROUP
             BY pv.post_id
       ) v ON v.post_id = p.id

   JOIN (SELECT MIN(date) AS MinDate, MAX(date) AS MaxDate FROM posts) PD

    ORDER BY Score DESC

Since you have aggregate functions in your SELECT clause, you need to specify what groups to apply them to. If you add:

GROUP BY   p.id
 , p.date
 , p.title
 , v.total_views
 , r.total_rating
 , r.module
 , r.module_id

before ORDER BY in your query, you will get 1 row per such group.

Without a GROUP BY clause, MySQL will randomly pick 1 row for you. This is a well-known bug in older MySQL versions.


Why conditioning before and after join, gets different results? I want to left join two tables and then return some of the records.

My first query was:

SELECT *
FROM card
LEFT OUTER JOIN user_card ON card.id = user_card.card_id
WHERE card.package_id =14
AND ( user_card.user_id =  '5'
OR user_card.user_id IS NULL)

Then when I noticed some problems
(ie result has fewer rows than just SELECT FROM card WHERE package_id=14)

I wrote this:

SELECT *
FROM (select * from card WHERE package_id=14 ) as cc
LEFT OUTER JOIN (select * from user_card WHERE user_id=5 ) as uu ON cc.id = uu.card_id

Now:

Why these two queries act differently?
How can I do it right without sub-querying ?

P.S : I'm using MySQL, and all id columns are INT.

To answer your first question:

In the first query, some records are filtered out AFTER getting the result of the left join. These rows are the ones that meet this condition:  card.package_id =14 AND user_card.user_id <> 5 [AND user_card.user_id IS NOT NULL]

On the other hand, in the second query, ALL result rows with "card.package_id =14" are returned regardless of the value of user_card.user_id

Example:

card.id     card.package_id
11          14
12          14
13          55

user_card.card_id   user_card.user_id
11                  5
12                  6

All possible values:

card.id package_id user_card.user_id
11      14         5
11      14         6
12      14         5
12      14         6
13      55         5
13      55         6

The first query will filter out the the rows with user_id=6, although the value of package_id is 14, (and will filter out all reos with package ID <> 14), so the returned values will be:

card.id package_id user_card.user_id
11      14         5
12      14         5

The second query would keep all rows with package id = 14, and give null to user id:

card.id package_id user_card.user_id
11      14         5
11      14         NULL
12      14         5
12      14         NULL

You can judge which output is needed, and based on that you chose which query to use.


postgres pg_xlog explodes during set tablespace I'm running postgreSQL 9.6.0 on CentOS 7.2. Occasionally I need to shift data on to a larger tablespace to prevent /var from filling up. When this is running the pg_xlog directory grows rapidly, and recovers very slowly. Periodic CHECKPOINT's subsequently help to kick the xlog clean up into action. However, this laborious  severely limits the amount of data I can shift in a short space of time. Upgrades and restarts are very tricky since the database records continuous long running collections from various data sources.

My gut feeling is that SET TABLESPACE prevents updates from WAL being committed, but this doesn't make much sense...

Any help or insight greatly appreciated!

When you move a table to a new table space and your wal_level is above "minimal", then the entire contents of the table are copied to the WAL stream.  That is why pg_xlog grows quickly.  Shrinking slowly could simply be because your archive_command can't keep up, and WAL files can't be removed until they are archived if archiving is on.  What are your settings for wal_level, archive_mode, and archive_command?


Does PL/SQL code run differently in sql plus vs sql developer? Today, I stumbled upon a question someone posted about the / character and ; character.  The Answer doesn't seem to be down-voted, so I questioned it a little more as to why it wasn't down-voted.  Something tells me that sql plus (command line) and sql run via sql developer behave differently.  And the usage of these characters is one example  Is this true?  If so, how can I find these differences?

Reference:

https://stackoverflow.com/questions/1079949/when-do-i-need-to-use-a-semicolon-vs-a-slash-in-oracle-sql

SQL*PLUS and SQL Developer both are client application for Oracle database. These applications just validate the syntax of statements and submit to Oracle Database server for execution. As far as the question is concerned about the uses of / and ; in these application I would like to demostrate as best as I can.

SQL*PLUS

In SQL*PLUS-
 ; is used to end the current statement.
 / is used to end current statement as well as If you are in the SQL prompt(SQL>) to run the command which are in the buffer.

Examples:

To end and execute the statement-

SQL> select * from jobs;

SQL> select * from jobs
  2  /

To run the command which is in the buffer(Only the SQL command(not SQL*PLUS command) and the last executed command remains in the buffer).

SQL> /

Instead, you can use RUN command for the same purpose. The difference is / doesnt show the command being executed but RUN does.

SQL> RUN
  1* select * from jobs

SQL*PLUS treats PL/SQL program as the SQL command except the ; and the blank line. In normal SQL command if you insert an blank line then it terminates the command as shown below.

SQL> select * from jobs
   2
SQL>

But in PL/SQL block it doesnt terminate the program.

To terminate and execute the PL/SQL block we use the same /.

SQL Developer

In SQL Developer we use CTRL+ENTER or F5 keys to execute commands. We dont need to use ; and / to indicate the end of the command instead we use Run Statement(CTRL+ENTER) or Run Script(F5).


Do database engines update when the updated value is the same? Suppose I am running multiple column update on one row. Does the database engine physically write new data to the database if some of the columns contain the same values as existed previously in the database? If so, how can it be avoided?

I'm about to implement a job which will run an update on a large table and most of the values will be the same but still, it has to recalculate them all. I am wondering if the update will also rewrite each column even if there is no need to, because the storage media will degrade faster.

You should assume the UPDATE is always writing the new value, even when identical. Write the query to eliminate no-ops with an appropriate WHERE clause. Approach it as a large table update, ie. do it in batches and make it idempotent. Is the only safe approach that is not victimized by assumptions and implementation specific details.

Case in point if you test prior to SQL Server 2012 you may conclude that an update that generates an identical in-place row image does not update the page and generates no log, while SQL 2012 under identical conditions you may conclude that it does not update the page but does generate log.


Use Aliases In Calculations In SQL Server 2008 I am attempting to run the below calculation, but I am getting an error of

  Msg 207, Level 16, State 1, Line 11
  Invalid column name 'Rpt1'.
  Msg 207, Level 16, State 1, Line 11
  Invalid column name 'Rpt2'.

This is the syntax I am trying to execute.

  Declare @saleID varchar(100), @totalsales int

Set @totalsales = (Select COUNT(numsales)
                  FROM OPENQUERY(444.222.333.123, 'Select
                                                   numsales, saleID
                                                   FROM saledate')
                  WHERE saleID = @saleID)

Select
Rpt1 = count(case when empID = '1931' Then 1 else 0 end)
,Rpt2 = count(case when empID = '1831' Then 1 else 0 end)
,Balance = @totalsales-Rpt1-Rpt2
From totalsaleinfo
Where saleid = @saleid

Another approach is you can layer your queries as sub-queries, which allow you to capture all results, like so.  Also you most likely wanted to use "sum" instead of "count".

select Rpt1, Rpt2, Balance = @totalsales-Rpt1-Rpt2
FROM   (Select Rpt1 = sum(case when empID = '1931' Then 1 else 0 end)
        ,      Rpt2 = sum(case when empID = '1831' Then 1 else 0 end)
        From totalsaleinfo
        Where saleid = @saleid) AS sums


Pull Subscription: process cannot read file due to OS error 5 I am trying to migrate a working pull subscription for transactional replication from one subscribing server to a new one. The subscribing server is at another site and is connected via a VPN tunnel. The serverName is resolvable via the hosts file.

I am trying to capture the existing configuration precisely, but clearly am missing something.

The error is The process could not read file '\\[server]\repldata\unc\[folder]\[folder]\[file].pre' due to OS error 5. I can RDP into the subscribing server with the distributor connection account and can access the file on the UNC share.

Everyone has permissions to the UNC share and we haven't seen this problem with other subscriptions.

The distribution process account is the SQL Server Agent, which I know is not best practice but matches the configuration of the existing working replication. I temporarily tried using a (local) Windows account

Again, we have tried to configure the subscribing server exactly as the working server. What are we missing? We never saw this error when setting up the previous subscription.

One note: the old subscription is still up and functioning, and uses the same accounts to connect to the distributor. I wonder if Access is Denied could be thrown due to a sharing conflict.

OS error 5 means Access is denied.

Instead of using SQL Server Agent service account — use pass-through authentication.

Create a local Windows account on both Publisher and Subscriber that has the same username and password.  Add the Windows account to SQL Server logins on both Publisher and Subscriber.  Use this account for the Distribution Agent process account.

Ensure the account is a member of the db_owner fixed database role in the subscription database, a member of the PAL at the Publisher, is a login associated with a user in the publication and distribution databases, and has read permissions on the snapshot share.

Replication Agent Security Model