I want to perform multiple date adds on a date range and select those individual dates

Having a days generator function like the following:

create FUNCTION [DateFuncs].[DateRange]
(
    @DateFrom date,
    @DateTo date
)
RETURNS TABLE
AS
RETURN
(
    WITH Nbrs_3( n ) AS ( SELECT 1 UNION SELECT 0 ),
    Nbrs_2( n ) AS ( SELECT 1 FROM Nbrs_3 n1 CROSS JOIN Nbrs_3 n2 ),
    Nbrs_1( n ) AS ( SELECT 1 FROM Nbrs_2 n1 CROSS JOIN Nbrs_2 n2 ),
    Nbrs_0( n ) AS ( SELECT 1 FROM Nbrs_1 n1 CROSS JOIN Nbrs_1 n2 ),
    Nbrs ( n ) AS ( SELECT 1 FROM Nbrs_0 n1 CROSS JOIN Nbrs_0 n2 )

    SELECT
        dateadd(d,n-1,@DateFrom) as Date,
        datepart(yyyy,dateadd(d,n-1,@DateFrom)) as YearNum,
        datepart(mm,dateadd(d,n-1,@DateFrom)) as MonthNum,
        datepart(dd,dateadd(d,n-1,@DateFrom)) as DayNum,
        datepart(dw,dateadd(d,n-1,@DateFrom)) as WeekDayNum,
        n-1 as Offset
    FROM ( SELECT ROW_NUMBER() OVER (ORDER BY n)
    FROM Nbrs )
    D ( n )
    WHERE n <= datediff(d,@DateFrom,@DateTo)+1
)

check the following query

--preparation code
declare @data table
(
    effDate date,
    discDate date,
    opMon bit,
    opTue bit,
    opWed bit,
    opThu bit,
    opFri bit,
    opSat bit,
    opSun bit
)

insert into @data (effDate,discDate,opMon,opTue,opWed,opThu,opFri,opSat,opSun)
values  ('2012-10-28','2012-11-27',0,1,0,0,0,0,1)

-- your query
select
     dr.Date
from
    @data as da
cross apply
    DateFuncs.DateRange(da.effDate,da.discDate) as dr
where
    case
        when dr.WeekDayNum=1 and da.opSun=1 then 1
        when dr.WeekDayNum=2 and da.opMon=1 then 1
        when dr.WeekDayNum=3 and da.opTue=1 then 1
        when dr.WeekDayNum=4 and da.opThu=1 then 1
        when dr.WeekDayNum=5 and da.opWed=1 then 1
        when dr.WeekDayNum=6 and da.opFri=1 then 1
        when dr.WeekDayNum=7 and da.opSat=1 then 1
    end = 1


Why am I getting blocked when I call OBJECT_NAME(object_id, database_id) from sys.dm_tran_locks?

Because the metadata functions do not obey transaction isolation semantics. If you want to avoid getting blocked, join to sys.schemas and sys.objects instead of using the metadata functions. This will also allow you to set the isolation level in a single statement instead of peppering NOLOCK hints all over the query...

This was reported by Adam Machanic on Connect several versions ago:

http://connect.microsoft.com/SQL/feedback/ViewFeedback.aspx?FeedbackID=432497

Closed as Won't Fix.


How to improve performance of query for distinct suppliers in big sales table?

For typical data distributions, emulating a loose index scan should be the magic trick for you.
Requires a multicolumn index to be fast:
CREATE INDEX ON sale_data(rscode, hul_code);

The variant with correlated subquery in the rCTE should be fastest for the simple case:
WITH RECURSIVE cte AS (
   SELECT min(hul_code) AS hul_code
   FROM   sale_data
   WHERE  rscode = '133955'    -- provide rscode here

   UNION ALL
   SELECT (SELECT hul_code
           FROM   sale_data
           WHERE  rscode = '133955'     -- repeat rscode here
           AND    hul_code > c.hul_code
           ORDER  BY 1
           LIMIT  1
           )
   FROM   cte c
   )
SELECT s.hul_code, s.name, s.latitude, s.longitude
FROM   cte c
JOIN   store s USING (hul_code);

Detailed explanation:

Optimize GROUP BY query to retrieve latest record per user

There may be faster alternatives for particular data distributions.
Asides:
DISTINCT might make sense for data distributions with very few rows per distinct (rscode, hul_code) (producing large numbers of resulting rows). But what you have doesn't make a lot of sense:
IN (Select Distinct(hul_code) as hulcode ... )

The column alias (as hulcode) is noise since IN ignores column names.
DISTINCT does not require parentheses. (hul_code) is interpreted as ROW constructor, short for ROW(hul_code). The row wrapper happens to be stripped automatically for the single column in this case. But you might introduce complications for cases with multiple columns.
There is the Postgres variant DISTINCT ON () which requires parentheses, but that's not used (nor needed) here. Related:

Select first row in each GROUP BY group?

But none of this makes a lot of sense with an IN construct, where you might just drop DISTINCT altogether. Better, yet, rewrite as:
SELECT s.hul_code, s.name, s.latitude, s.longitude
FROM  (
   SELECT DISTINCT hul_code
   FROM   sale_data
   WHERE  rscode='133955'
   ) sd
JOIN  store s USING (hul_code);

.. in the unlikely case that you should have such a data distribution.
And your columns rscode seems to hold numeric data. If so, it should be a numeric type, not varchar.


how to Grant Permission on a table to a user on the other database in oracle

Oracle article about Create database link:
To access a remote schema object, you must be granted access to the remote object in the remote database.

That means you need to grant the privileges locally (to the database on which they are) to the user as whom a user connect via the database link.

So, for example, if the database link was created like so

create database link DB_LINK
connect to TESTUSER
identified by PASSTHEWORD
using ...

then you have to

grant all on a.tablename to TESTUSER;


Converting "string" dates with errors to normal dates or NULL

For those interested, here is a function that does the job :

CREATE FUNCTION to_date_or_null(ADate TEXT, AFormat TEXT) RETURNS DATE AS
$BODY$
BEGIN
    RETURN to_date(ADate,AFormat);
EXCEPTION
    WHEN others THEN RETURN NULL;
END;
$BODY$ LANGUAGE plpgsql IMMUTABLE STRICT;

I used it this way :

UPDATE extcg SET newdate=to_date_or_null(olddate,'yyyymmdd');

And thanks to @Luuk for mastering Google searches ;)


SQL Server: SSMS "Save Results As" creates CSV that Excel mangles

First, make sure you've got the query options set up the way you want.

Go to the Query menu, and choose Query Options:

The first of the two highlighted options tells SSMS to include the column headers in your CSV file. The second tells SSMS to put single quotes around columns that include a comma.

I suspect you've already done this, but thought I should mention it for completeness.

If this isn't sufficient to get your data into a format Excel will correctly interpret, my next step might be (as noted in the comments) to use the Import/Export Wizard to generate a SSIS package that does this. However, the wizard does not always get the quoting correct - in particular, it can put things in double quotes, but won't "double up" the double quotes embedded in the string.

Generally at that point, I resort to brute force.

I create a modified query that:

Replaces double quotes in the data (") with double double quotes ("");
Converts numeric/date values to strings, and
Puts all data into double quotes, and (finally)
Outputs all the columns concatenated together, separated by commas.

So, instead of

 ID | First Name | Last Name |      Address
----+------------+-----------+-------------------
  1 |  John "JT" |   Smith   |  123 Wayne's Way

I generate:

One_Big_Column
------------------------------------------------
"1","John ""JT""","Smith","123 Wayne's Way"

If I'm going to use the query regularly, it'll include a sortOrder column (set to 1), and be UNION ALLed with a SELECT that creates the header row. I then wrap the UNION ALLed queries (making them a subquery), so I can select just One_Big_Column, and sort by sortOrder.

SELECT One_Big_Column
  FROM (
        SELECT '"Id","First Name","Last Name","Address"' as One_Big_Column
              ,0 as sortOrder
        UNION ALL
        SELECT '"' + CAST(Id as varchar(30)) + '","'
                    + REPLACE(firstname, '"','""') + '","'
                    + REPLACE(lastname, '"','""') + '","'
                    + REPLACE(address, '"','""') + '"'
              ,1 AS sortOrder
          FROM myTable
       ) sq
 ORDER BY sortOrder
;

You can see this run here.

Note: If you're formatting the CSV manually, you'll probably want both of the check boxes noted above turned off.


Need to understand the drastic difference in execution plan between two queries

To summarize: the application sends a TVP where each row is a set of search parameters, where a null value in the TVP functioning as a "wildcard" indicate no filtering on that attribute.  The goal of the query is to return all the rows in the target table that match any of the rows in the input TVF.

So if the TVP sends (Id=123,Name=null),(ID=null,Name='Joe'), the procedure should return all rows match either the first set of criteria or the second.

  Am I expecting too much of the optimizer ?

Yes.  For this to work well, the QO would neeed to create a seperate plan for each row in the input TVF, and it simply was never build to do that.  For each row in the TVF a table scan will be required, as no single index can be used to evaluate the join criteria.

So you actually need to run a separate query for each row in the input TVF.  You can cursor over them and load a temp table, reducing this to an iterative form of a classic dynamic search query, which you can use Dynamic SQL or OPTION RECOMPILE to get tailored execution plans.

This

  What I am trying to understand is why the "OR is null" clause degrades the execution plan so much even if there are indexes on the table.

Is simple and not too relevant.  In the sample you posted if b.guid is null, then the query returns every row in Party.


Snapshot on Always On Availability Group in suspect mode

Operating system error 665(The requested operation could not be completed due to a file system limitation)

This is a common issue with large sparse files as it causes NTFS metadata fragmentation. You're not hitting a SQL Server issue, you're hitting (as the error states) a filesystem limitation (NTFS).

  What can we do to avoid errors like this?

Not use snapshots. Not use NTFS (ReFS instead).

  Will changing to ReFS help or perhaps are there any other NTFS settings that can be ammended?

Yes, changing to ReFS should alleviate the issue, assuming you actually need the snapshots. Changing less data in the source database would also help alleviate the issue.

No, there aren't really any other NTFS settings that can be changed.


Postgres files postgresql.conf and pg_hba.conf - which take priority?

There is no priority to choose because they have different purposes.

password_encryption in postgresql.conf tells how to hash a new password when it's changed or a new user is created with a password.

the field in pg_hba.conf tells what kind of authentication scheme should be used when a client that matches the rules attempts to connect.

When connecting with a client that does not support SCRAM authentication:
If the first line that matches in pg_hba.conf for this connection attempt has, in the METHOD field:

scram-sha-256, the connection will be rejected.

md5 and the password of this account is stored with an
md5 hash (independently of password_encryption), the connection
will succeed.

md5 and the password of this account is stored with an
scram-sha256 hash (independently of password_encryption), the connection will be rejected.

A superuser can check what kind of password is assigned to existing accounts by looking at the hashed passwords in the system table pg_catalog.pg_authid.


How to prepare a date and double for a mysql replace statement?

Here is the output of the NOW() function

mysql> select now();
+---------------------+
| now()               |
+---------------------+
| 2012-04-24 22:07:47 |
+---------------------+
1 row in set (0.03 sec)

mysql>

This is the format to enter mydate. For a double, just append .0 to any integer. No need for numbers to to have quotes:

REPLACE into mytable (CU, DA, VA) values('ABC', '2012-04-24 22:07:47', 23.0);


PostgreSQL Index Only Scan can NOT return ctid

Your understanding is almost but not quite correct. True, every btree index tuple needs a ctid (or some form of block number and tuple index) to point to the heap tuple (table row). But (at least) since the introduction of "heap-only tuples" with Postgres 8.3, there may be a HOT chain to follow to arrive at the current live tuple in the snapshot - with a different ctid than the one stored in the index.

Since it is also not possible to use the ctid (or any system column) as index expression, it is currently (and for the forseeable future) completely impossible to get a ctid from an index-only scan.

Related:

Redundant data in update statements
How do I decompose ctid into page and row numbers?

Quoting the Postgres Wiki on Index-only_scans / Interaction_with_HOT:

  With HOT, it became possible for an index scan to traverse a so-called
  HOT chain; it could get from the physical index tuple (which would
  probably have been created by an original INSERT, and related to an
  earlier version of the logical tuple), to the corresponding physical
  heap tuple. The heap tuple would itself contain a pointer to the next
  version of the tuple (that is, the tuple ctid), which might, in turn,
  have a pointer of its own. The index scan eventually arrives at tuple
  that is current according to the query's snapshot.


Turn off Error AND Usage Reporting SQL Server

There are registry keys you can edit, see Configure usage and diagnostic data collection for SQL Server (CEIP). Alternatively, there's a thread over on Microsoft's Azure forums that suggests this behavior is caused by some sort of misconfiguration in the C:\Program Files\Microsoft SQL Server\150\Shared\SqlWtsn.exe.config file and either renaming that file to SqlWtsn.exe.config.OLD or editing the contents of the file make fix the issue. I can say that the few times I've encountered this issue, renaming SqlWtsn.exe.config worked for me.


Migrate a SQL Server instance from one server to another

The database is 17.5mb, so quite small. I am assuming you can RDP onto the server in AWS, or have the ability to RDP? If so, then simply take a backup of the database, copy the backup down to your local machine, and then restore it locally.


why is blevel null for some indexes in Oracle DB?

Indexes whose the name start with SYS_IL.. are LOB indexes.

BLEVEL is a property of B-tree indexes, and does not make sense for LOB indexes.

Even if you collect statistics, BLEVEL for such an index will remain null.


I want to change to Dynamic SQL Query instead of CASE statement

As mentioned by Dan Guzman in the comments, you can do this with UNION ALL instead of dynamic SQL:

select *
from Employee e
    inner join department d
        on e.departmentid=d.id
where d.departmentkeys=1 AND e.department1 = d.departmentvalues

UNION ALL

select *
from Employee e
    inner join department d
        on e.departmentid=d.id
where d.departmentkeys=2 AND e.department2 = d.departmentvalues

UNION ALL

select *
from Employee e
    inner join department d
        on e.departmentid=d.id
where d.departmentkeys=3 AND e.department3 = d.departmentvalues;

Akina mentioned another option, which is to use multiple left joins to the same table.

This has the possibility of producing different results if there are any employees that don't have a department, and UNION ALL is probably a better option, performance-wise here.  But this is the general approach for the multiple join option:

select * from Employee e
left join department d1
    on e.departmentid=d1.id and d1.departmentkeys=1 and e.department1=d1.departmentvalues
left join department d2
    on e.departmentid=d2.id and d2.departmentkeys=2 and e.department2=d2.departmentvalues
left join department d3
    on e.departmentid=d3.id and d3.departmentkeys=3 and e.department3=d3.departmentvalues


What will happen if you need a fifth echelon? And what if an employee has a role in more than one division?

A smaller, simpler schema should cover your business case and require less work to implement, maintain and extend. For example:

Employee (id, name)

Division (id, name, subdivision_of)
    [subdivision_of -> Division.id]

Position (id, name)

Role (employee_id, division_id, position_id)
    [
        employee_id -> Employee.id,
        division_id -> Division.id,
        position_id -> Position.id
    ]

If you don't want one person to be able to work in more than one division, I suppose you can declare employee_id as unique.

Edit: another problem that I see is a redundancy anomaly in the employee table. It repeats the information about the hierarchy of divisions. If you ever change that structure you have to remember to go over every row in the employees table to make sure that change is reflected there.


Query executes fast, but keeps the table locked for some time

The documentation says:

  Indices are not required for child key columns but they are almost always beneficial.
  Each time an application deletes a row from the parent table, it performs [some query].
  If this SELECT returns any rows at all, then SQLite concludes that deleting the row from the parent table would violate the foreign key constraint and returns an error.
  If these queries cannot use an index, they are forced to do a linear scan of the entire child table. In a non-trivial database, this may be prohibitively expensive.

  So, in most real systems, an index should be created on the child key columns of each foreign key constraint. The child key index does not have to be (and usually will not be) a UNIQUE index.

You should create an index on Drawings(ParentPartNumber).


I found a solution where i configure rsyslog to pick up that file and forward it to my target server.

# RSyslog Configuration for MongoDB /etc/rsyslog.d/mongod.conf
$ModLoad imfile
$InputFileName /var/log/mongodb/mongod.log
$InputFileTag mongodb
$InputFileStateFile mongodb-state
$InputFileFacility local3
$InputRunFileMonitor
local3.* action(type="omfwd" target="target" port="514" protocol="udp" action.resumeRetryCount="100" queue.type="linkedList" queue.size="10000")

Based on:
https://www.rsyslog.com/sending-messages-to-a-remote-syslog-server/
and
https://serverfault.com/questions/396136/how-to-forward-specific-log-file-outside-of-var-log-with-rsyslog-to-remote-serv


Should I pause Mirroring and stop SQL Server services before running windows updates?

Suggest you to PAUSE mirroring and then patch the windows.

Ideally, I would follow below approach :

If you have a witness configured, turned it OFF as during the patching, if sql server service is restarted or the server reboots, then a witness will initiate a failover.

ALTER DATABASE [db_name] SET WITNESS OFF

Always patch the current mirrored server, so if things go wrong, then you atleast have your principal ON.

Reboot the mirror server (if necessary)

Now failover to the newly patched mirror server. Run below T-SQL on the current Principal server ALTER DATABASE [your database] SET PARTNER FAILOVER
Once the failover is completed, patch the current mirror (which was originally primcipal)

Reboot if necessary

Now failback to the original principal server.
Add back the witness (if removed from step 1)

ALTER DATABASE [db_name] SET WITNESS = 'TCP://[FQDN]:[port_number]'
As a saftey measure, run DBCC CHECKDB on your databases.

From BOL :


Could not fork new process for connection: Could not allocate memory

I think that your machine is overloaded. You don't say what your max_connection setting is, but based on what you say I would assume it to be something around 1000.

As long as the number of active sessions is around 80, your memory should not get exhausted, but I assume that there are spikes of more active sessions, and your RAM is exhausted.

I am surprised that your machine runs well with 80 active sessions, since you have only 16 cores (you didn't say anything about the storage).

1000 databases seem a lot; I assume that each of them is small.

Since you have so many databases, I recommend that you move some to other clusters on different machines. That is an easy way to distribute the load.

Using a lower value for max_connections would reduce the danger of overload and memory exhaustion.


How to store formula method in the table

You could create a view to select from and return the value the calculated value.

This is the SQL to create the view for the CPU formula:

CREATE VIEW cpu_value
AS

SELECT id, name, cost, commission, bonus,
((commision + fulfilment + bonus) - (cost) / 10)) AS value
FROM items WHERE category_id = 1;

This would be the one for the HDD:

CREATE VIEW hdd_value
AS

SELECT id, name, cost, commission, bonus,
((commision + cost) / 10)) AS value
FROM items WHERE category_id = 2;

To query them you would just do:

SELECT value FROM cpu_value WHERE id = 1;

this would perform the formula for the specific cpu with id = 1.

If you wanted to recombine them you can always create an additional view that merges the two views back together.


sp_Blitz - RegOpenKeyEx() returned error 2

My guess is that it's related to this Github issue. Another user reported it, but because I can't quickly reproduce it, and the reporting user didn't want to submit code to fix it, we ended up closing the issue.
If you'd like to help troubleshoot and fix that issue, you can make notes in that Github issue, and use the First Responder Kit's Contributing Guide to get started with the code.


PostgreSQL: The database cluster initialization failed

I had the same issue installing 9.1.4 on Windows 7. I managed to find a solution online that worked.

The steps I followed are:

Uninstall PostgreSQL
Delete the postgres user if it still exists.

net user postgres /delete

Create the postgres user with a password you can remember

net user /add postgres <password>

Add the postgres user to the Administrators group

net localgroup administrators postgres /add

Add the postgres user to the Power Users group

net localgroup "power users" postgres /add

Run a command window as the postgres user

runas /user:postgres cmd.exe

Run the install file from within the command window.

C:\Download\postgresql-9.1.4-1-windows.exe

This should run the installation successfully.
Remove the postgres user from the Administrators group.

net localgroup administrators postgres /delete


SQL Server communicates using TDS, or Tabular Data Stream, protocol.  By default, TDS is not encrypted.

Encryption can be negotiated between the client and the server at time of connection.  Essentially, you enable encryption at the server, then roll it out by making the necessary changes at the client end.  Since encryption is not forced at this point you are simply reducing the visibility of some data over the wire.  Security is about layers of protection; encrypting communications, even if only some communication, is still a valuable layer in the security stack.

Once you have all clients configured to use TDS encryption, you configure the server to only accept encrypted connections.  At that point, all communication between the server and its clients will be encrypted.


There isn't such a fine grained tracking available in Postgres (other than using DTrace which apparently gives a lot of information).

There is a whole chapter in the manual "Monitoring Database Activity"
http://www.postgresql.org/docs/current/static/monitoring.html

Another option is the module pg_stats_statements:
http://www.postgresql.org/docs/current/static/pgstatstatements.html


This has 0 to do with databases.

  collecting the database of the Skills

Sure, but you're really just crawling their API. Find the web portal: https://www.amazon.com/b?node=13727921011

From there you'll see b?node= in the url. I searched for node= in the api. I found this in the passage,

ProductCategoryId Identifier for a product category (or browse node).
ProductCategoryName   Name of a product category (or browse node).

Knowing that the term here is browse node. I set out googling for a newer API than the MarketPlace Web Services (Amazon MWS) which was last updated in 2011. I found the "Product Advertising API" which was last updated in 2013. BTW, this is normal for Amazon. Their docs are crap, so move fast through them.

The Product Advertising API supports a method called ItemSearch which supports a BrowseNode argument which does exactly what you want. You'll have to crawl through that because it only returns 10 results per page.

Working with Amazon is a blast. Godspeed. Cache the data with a time_crawled_idx, and time_crawled_detail. You'll only get the ASIN from that. From there, off to ItemLookup

If you're looking to spec out the project, be glad to help contact info in profile.


Is there a way to used grouped data multiple times in a query

The following will get you the assignment answer. There are many ways to solve this, and it is not necessarily bad to group several times in sub-queries. Most RDBMS engines will be able to optimize your query (some) and performance is likely not an issue for this particular question.

SELECT TOP (1)
     Department
    ,SumSalary
    ,AvgSalary
FROM
(
SELECT
     Department
     ,SumSalary
    ,AVG(SumSalary) OVER(PARTITION BY NULL) AS AvgSalary
FROM
(
SELECT
     Department
    ,SUM(Salary) AS SumSalary
FROM DIM.Property
GROUP BY Department
) SubQuery
) x
WHERE SumSalary > AvgSalary
ORDER BY SumSalary DESC

I'd suggest that you read up on Window Functions so that you can fully explain any work submitted (there are plenty of sites and technical documentation for each DB and ANSI SQL).

Also note that a TOP (1) gets just the single highest paying department that is above the average, since you may have many that meet this criteria. To ensure you get the highest paying the ORDER BY guarantees the highest paying is on top. You still have the possibility of two departments paying the same amount though in which case you have a non-deterministic query (not guaranteed to get the same department every time). You need a tie-breaker unless you accept this potential for random results or it is guaranteed not to happen by some other business process or rule (yeah right!).


Connect to a local PostgreSQL server using peer auth with pgAdmin

Make sure you have the OS user, which you have used to launch PGAdmin3 or PGAdmin4, is in the database.
For creating an OS user in the database, you can use following command:

sudo -u postgres psql -c "CREATE USER $(id -nu;" postgres

In HOST name address include the Postgres unix_socket_directories
You can use following command to get the unix_socket_directories setting

sudo -u postgres psql -c "SHOW unix_socket_directories;"


database to use for Automated Teller Machine

For projects using Visual Studio, I'd almost invariably recommend using SQL Server Express.  The product is free to use, as long as you can live with the limitations: Microsoft SQL Server Express supports 1 physical processor, 1 GB memory, and 10 GB relational storage.  I'd expect for an ATM project you won't be locally storing a lot of data, so that shouldn't be a concern.


Mitigating MySQL replication delay in single master/multiple slave setup

Since "native" MySQL uses asynchronous replication, there is no system-wise way to guarantee nor force a slave to apply a master's statement at any given time.

Semi-sync replication shortens this lag time by at least ensuring your statement is recognized by the slave -- but does not go as far as making sure it has been applied on your slave.

This is a common issue. Ultimately, your application needs to be replication-aware, such that your query "knows" it is being executed on master or on slaves.
In your case, the common solution is to make sure a SELECT query which immediately follows a write operation is executed on master.

You may choose to use synchronous solutions: MySQL NDB Cluster or Galera Replication. Even with Galera you would need a special configuration to make sure a consistent write-and-read is successful.


VARCHAR column as Primary Key is not a good choice as normally we create Cluster Index on same column.

Cluster Index on VARCHAR columns is a bad choice because of expected high fragmentation rate. Every new inserted key value will try to find its place somewhere between existing keys and normally cause page split and high index fragmentation. As a result poor performance and extra index rebuild/reorganise cost.

Secondly while using varchar key column as Foreign Key will take extra space as compared to Pseudo key auto-incremented column.

BUT

Clustered index on  auto-incremented column may create "hot spot". Read this carefully Is 'Avoid creating a clustered index based on an incrementing key' a myth from SQL Server 2000 days?

Though hot spot could be a problem, when to many users are trying to insert values but still in your case i would like to go for auto-incremented column as compare to varchar.


MySql - ERROR 1071 Specified key was too long; max key length is 1000 bytes while restoring dump

You may to have adjust some VARCHAR length in some table  based on the Character Set you are using.
That exact error message is actually posted as a bug report from Nov 2004, when in fact, it is not really a bug. That should direct you on how to adjust key lengths, especially your PRIMARY KEYs.
If you know which table is causing the Error 1071, you have to do the following:
Step 01) mysqldump only database schema
mysqldump --routines --triggers --all-databases --no-data > MySQLSchema.sql

Step 02) mysqldump only data
mysqldump --all-databases --no-create-info > MySQLData.sql

Step 03) Using vi or some other editor, edit the table's PRIMARY KEY to manually limit the PRIMARY KEY in such a way that the key does not exceed 1000 characters.
Step 04) Load the schema
mysql -A < MySQLSchema.sql

Step 05) Load the data
mysql -A < MySQLData.sql

You may have to resort to this if you cannot otherwise figure this out,
UPDATE 2012-01-23 11:43 EDT
Since you mentioned a certain table has the wrong storage engine, here is what you do:
Look above at Step 03. Go find the table in the file. Look for ENGINE=MyISAM in the file and change it to ENGINE=InnoDB. Then, load the schema file first and the data file afterwards.
UPDATE 2012-01-23 11:52 EDT
You should add this to to /etc/my.cnf
[mysqld]
default-storage-engine=InnoDB

then run service mysql restart. If you cannot restart mysql at this time, run this in the mysql client:
mysql> SET GLOBAL default_storage_engine = 'InnoDB';

However, the mysqldump will still have the ENGINE=MyISAM at the end of each CREATE TABLE. You will have to perform the 5-Step plan I mentioned above including changing the ENGINE=MyISAM to ENGINE=InnoDB. DO NOT CHANGE THAT FOR THE mysql DATABASE !!! Change only those tables that are yours.


How to backup a secondary log shipped standby mode database

You could restore the same backup that was used to create the log shipped database on another server, and then apply all the transaction log backups.  There is no limit to the number of log-shipped databases you can have.  I don't know if Management Studio supports this option, but it is fairly straightforward process to implement manually (T-SQL, powershell, etc.) as you are simply copying files and restoring a database.  The database will need to stay read-only mode if you plan on restoring any more transaction logs.


MySQL Select additional rows if initial query has specific column value returned

Conditional inclusion of rows can be emulated using UNION and JOIN to the original table:

http://sqlfiddle.com/#!9/80555b/5

SELECT
    orig.*
FROM
    relationships AS orig
WHERE
    c_owner_id = $c_id
    OR c_linked_id = $c_id

-- this will add some additional rows where orig rows would match the IF cond
UNION

SELECT
    sub.*
FROM
    relationships AS sub
JOIN
    relationships AS orig ON sub.c_owner_id = orig.c_owner_id
WHERE
    sub.type = 'parent'
    AND orig.type = 'parent'
    AND orig.c_linked_id = $c_id;

You can define specific ordering for the added rows to come after the right "main" rows. You can only order the resultset of entire UNION as ORDER BY in unioned parts has no meaning.


Dump with special characters in table column name

I tried renaming the table via "adminer" (php mysql manager) but even there the table name was not correct (not showing at all, to be honest). I tried renaming but of course I produced a new table column.

In the end I had to recreate the table.

I managed to get most of the schema from the SHOW CREATE TABLE contabilita_banche\G above.
Then I took the dump with the data, adjusted the CREATE TABLE command with a utf-8 column name and restored the new dump.

Thank you very much Rolando for your support!


How to deal with a chain of several linked 1:n:n*m:n*m*k "join" explosion?

It sounds like you're talking about a self-referential relationship, especially with the inclusion of "manager" titles. This is a common pattern, especially with employee tables. While they may implement it differently the main RDBMS vendors typically offer recursive capabilities. This means that with a special syntax you can easily query this kind of a relationship without writing the hundreds of JOINs you would otherwise need. An example for Microsoft T-SQL recursive capabilities is here and focuses on manager/employee relationships.

Every row should have a ManagerID or Parent attribute, and this can be NULL or the Employee's own ID in the case of the leader (e.g. CEO). This allows you to correctly identify your root node. If you need to traverse down from different levels simply change the start point for the recursion.


How do I create/publish a report in SQL Server 2014?

Publishing a report has not changed much in SQL Server since 2005. Developing a report changed when SQL Server 2012 came out just with the tool that you would use. I would suggest you start out by going through the Stairway To SQL Server Reporting Services by Jessica M. Moss. After reading through that series I would suggest getting a book on SSRS, anything by Stacia Misner on the subject is a good start.


How do I best measure the query performance?

I like to use the SQLQueryStress free tool when comparing a before and after scenario. With SQLQueryStress you can execute each stored procedure as many times as you like, and get the total average stats for all executions.

For example, you could execute each stored procedure 100 times, and then use the stats to back up your improvements. "Over 100 executions, my improvements save a total of 30 seconds and the stored proc does 1500 less reads per execution." I think you get the idea.

If there are parameters in the stored proc, it's always a good idea to double check that your improvements work with many different sets of parameters. SQLQueryStress does some cool stuff with letting you substitute parameters in your query to get a better overall picture of how the stored proc might be performing.

SQLQueryStress documentation: http://www.datamanipulation.net/sqlquerystress/documentation/documentation.asp


tables for different sources of payments

I normally go with modified version of (2).

payment_type (payment_type_id, PK(payment_type_id) );
payments(payment_id , payment_type_id, [other_attrubutes],
  PK(payment_id), UNIQUE(payment_id,payment_type_id), FK(payment_type_id)
);

paypal_payment(payment_id , payment_type_id, [paypal_attributes],
 PK(payment_id), FK(payment_id ,
payment_type_id)   REFERENCES payments(payment_type_id,payment_type),
 CHECK  (payment_type_id='paypal')
)
manual_payment(payment_id , payment_type, [manual_attributes],
 PK(payment_id), FK(payment_id ,
payment_type_id)   REFERENCES payments(payment_type_id,payment_type),
 CHECK  (payment_type_id='manual')
)

Check constraints on each detail table ensures that manual table details never go into paypal table, and vice versa. Unique constraint allows detail tables to have a reference to 2 columns.

(3) is also not so bad, but I don't like exclusive arcs - adding new payment type will require changes to payments, and trigger used to enforce that only one of columns is not null.


Specific disadvantages to storing infrequently accessed large files in a large database

InnoDB Architecture

This first thing that comes to mind is the InnoDB Buffer Pool. It holds three things

Data Pages from Accessed .ibd files
Index Pages from Accessed .ibd files
Insert Buffer for Changes to Secondary Indexes

Any query accessing infrequent data will basically remove the working dataset from the InnoDB Buffer Pool just to load the adhoc data. To get those data back, you have two options:

OPTION 1 : Reread the data and index pages from disk. You are certainly going to experience a cache storm  that will impact Server Load with Disk I/O, Memory Utilization, and possible swapping.
OPTION 2 : Unload Buffer Pool Before Accessing Old Data and Reload Buffer Pool Afterwards. This is a new feature for MySQL 5.6. Apparently, it was made for occasions like this. Here are the options for loading the InnoDB Buffer Pool at Startup, Shutdown and On-Demand :

innodb_buffer_pool_dump_now
innodb_buffer_pool_dump_at_shutdown
innodb_buffer_pool_load_now
innodb_buffer_pool_load_at_startup

EPILOGUE

OPTION 1 is something you reluctantly live with until all subsequent queries bring back your most recent working dataset
OPTION 2 requires scripting adhoc reports to do the following

Backing up the InnoDB Buffer Pool
Run the adhoc queries on the infrequent data
Restoring the InnoDB Buffer Pool

OPTION 2 requires upgrading to MySQL 5.6
There are other ways MySQL and InnoDB are impacted. I discussed these in my recent post How to optimize MySQL for large BLOB updates


How to find if a column is referenced in a computed column?

Thanks to Scott Hodgin I found it in sys.sql_expression_dependencies

SELECT
    OBJECT_NAME(sed.referencing_id)     AS referencingTable
    , pc.[name] AS computedColumn
    , pc.is_computed
    , cc.[name] AS referencedcolumn
    , cc.is_computed
FROM sys.sql_expression_dependencies sed
JOIN sys.[columns] pc ON sed.referencing_minor_id = pc.column_id AND sed.referencing_id = pc.[object_id]
JOIN sys.[columns] cc ON sed.referenced_minor_id = cc.column_id AND sed.referenced_id = cc.[object_id]
WHERE sed.referencing_minor_id > 0      -- referencing object is Column
AND sed.referenced_minor_id > 0         -- referenced object is Column
AND sed.referencing_id = sed.referenced_id  -- references the same table


Types of ways to achieve Many-to-Many relationship between tables in DB?

When you get to 100 million rows, we might need another chat.  Meanwhile, this describes the optimal schema for many:many.


Will enabling row movements on list-partitioned table cause performance problems(oracle 11g r2)

You should be fine; the rowid would be a show-stopper if you were using it
Temporarily, there would be "holes" in the data files, but Oracle will fill them in without any intervention.  Ensure you have automatic tablespace management enabled.
You can invoke that command but no need to; let Oracle take care of it
The person that stated that probably meant there was a one-time hit, or he didn't know what he was talking about, or using an old version.  You tested; his environment may have had a rat's nest of views.


Prevent MariaDB from filling up tmpdir and hanging the system

tmp is used when mysql/mariadb engine can't use ENGINE=MEMORY for temporary tables. That happens for many reasons:

Temporary table is too big to fit into join/sort buffer. That may
be caused by too small buffers or by queries that produce too big
results due to the bad design.
Temporary table contains text/blob fields that are not appropriate
for MEMORY engine.
Proper index(es) are missed for the query, so massive filesorts performed on the disk in the mysql/tmp dir.

Anyway as far as disk-based temp-tables are significantly slower than in-memory ones, queries that fill up the mysql/tmp dir are listed in the slow queries log along with short diagnosis.


'You cannot add or change a record because a related record is required in table 'Booking'.' error when removing a PKFK value in a SQL database in C#

"UPDATE Room SET BookingID = '', Occupied = False WHERE Room.BookingID = '" + TxbID.Text + "'";

You don't have a Booking record with a BookingID of ''. I suspect you have your relationship the wrong way round; a Booking should have a RoomID rather than a room having a BookingID, or if you want to persist with the current architecture you should perhaps be setting Room.BookingID null
Please read this website, not because it solves your problem, but because you're making the most grave mistake anyone can make when writing database software, and you need to kick the habit now. (If you did it while working for my company, they'd just fire you)


How do I use subquery on the same table in MySQL?

You could try to use EXISTS clause instead of IN, since its usually less performance expensive:

  DELETE FROM TABLE T_delete
  WHERE EXISTS (SELECT 1
                FROM TABLE t_join
                WHERE value_was IS NULL
                AND T_delete.ID1 = t_join.ID1)
  OR EXISTS (SELECT 1
                FROM TABLE t_join
                WHERE value_was <= value_now
                AND T_delete.ID2 = t_join.ID2);

Not sure if it would improve the performance, but you could also try to split this query into two instead of using a OR.

There's more info about optimizing queries with EXISTS here.


MySQL: What do we mean by 0 value in in_use column of show open tables statement?

From the online manual

http://dev.mysql.com/doc/refman/5.6/en/show-open-tables.html

The number of table locks or lock requests there are for the table. For example,
if one client acquires a lock for a table using LOCK TABLE t1 WRITE, In_use will be 1.
If another client issues LOCK TABLE t1 WRITE while the table remains locked, the client
will block waiting for the lock, but the lock request causes In_use to be 2.

I have done this simple test:

mysql> show open tables like 'City';
+----------+-------+--------+-------------+
| Database | Table | In_use | Name_locked |
+----------+-------+--------+-------------+
| world    | City  |      0 |           0 |
+----------+-------+--------+-------------+
1 row in set (0.00 sec)

mysql> lock table world.City write;
Query OK, 0 rows affected (0.00 sec)

mysql> show open tables like 'City';
+----------+-------+--------+-------------+
| Database | Table | In_use | Name_locked |
+----------+-------+--------+-------------+
| world    | City  |      1 |           0 |
+----------+-------+--------+-------------+
1 row in set (0.00 sec)

mysql> unlock tables;
Query OK, 0 rows affected (0.00 sec)

mysql> show open tables like 'City';
+----------+-------+--------+-------------+
| Database | Table | In_use | Name_locked |
+----------+-------+--------+-------------+
| world    | City  |      0 |           0 |
+----------+-------+--------+-------------+
1 row in set (0.00 sec)

The number of rows of "show open tables" is about the one returned by "mysqladmin status". Mysql has a table cache that contains file descriptors for the table data file.

mysql@ubu-14:~$ mysqladmin status
Uptime: 1391  Threads: 4  Questions: 81  Slow queries: 0  Opens: 75
Flush tables: 1    Open tables: 68  Queries per second avg: 0.058

mysql> show open tables;
+--------------------+----------------------------------------------------+--------+-------------+
| Database           | Table                                              | In_use | Name_locked |
+--------------------+----------------------------------------------------+--------+-------------+
| performance_schema | events_waits_summary_by_user_by_event_name         |      0   |           0 |
+--------------------+----------------------------------------------------+--------+-------------+
75 rows in set (0.00 sec)


How to get a value from a table that a value is referenced from?

SELECT  a.question,
        a.answer
FROM    Answers a
INNER JOIN Questions q
    ON q.id = a.question
WHERE a.answer = 1

The JOIN syntax is described in the PostgreSQL documentation "2.6. Joins Between Tables"

(emphasis mine)

  A query that accesses multiple rows of the same or different tables at one time is called a join query.


Running Aggregation logic on multiple shards of MongoDB

You don't ever need to connect to specific shards in a MongoDB database. Instead, you connect to a mongos instance that handles the routing for you.

In your case, you would connect to the mongos instance normally, by typing mongo into the terminal, or through a language specific client. You send your aggregation operation to the mongos instance, and it will distribute the operations to each of the shards and combine the result at the end.

Sharding is, in many ways, transparent to the user (though certainly not transparent to the database architect): many queries that run on a non-sharded mongod instance will run in the same manner on a mongos instance.


search path is getting updated automatically

Let me pull out my crystal ball and see what you did...
Ah! I see you restoring a plain format pg_dump from an interactive session using \i dumpfile, so that the settings from the dump remain active in your session after the restore is completed.


How can instructors add users to their classes and at the same time not know all of the institution users?

Yes you could add three additional tables called Departments, DepartmentUsers and DepartmentClasses.
Departments would have a primary key field departmentId (and probably some other fields like departmentName, etc).
DepartmentUsers would be a linking table between Departments and InstitutionUsers so it would store the departmentId and institutionUserId (as foreign keys). (This table determines which users are in which department.)
DepartmentClasses would be the same as above, except it would link Departments to InstitutionClasses, so it would store the departmentId and institutionClassId. (This table determines which classes are in which department.)
Then for a particular class you can see only the applicable users (within the department) who can be added to it via InstitutionClasses join to DepartmentClasses join to Department join to DepartmentUsers.
(Then the institutionUserID field in your InstitutionClasses table only represents who's already been added to the class. You may want to rename the column to something more explicit like registeredInstitutionUserID.)


Does auto increment on Primary Key prevent concurent inserts?

If I change this column to a long with auto-increment enabled. Will this become a concurrent insert bottleneck?

The answer is NO because the step when SQL Server gets next identity value is outside of transaction. This is why when you perform ROLLBACK identity does not decrease and you can have gaps.

Please see answer to similar question here


You can use SUBSTRING_INDEX() function for that, no need for regexes:

   SUBSTRING_INDEX( SUBSTRING_INDEX(path, '/', 2), '/', -1)


Indexing with Oracle Text

  3.3.6.1 CONTEXT Index and DML

  A CONTEXT index is not transactional. When you perform inserts,
  updates, or deletes on the base table, you must explicitly synchronize
  the index with CTX_DDL.SYNC_INDEX.


If the database is set to "simple" mode, then the transaction log is truncated after the transaction.  Because you are only inserting 1000 records at a time, the transaction never needs to grow the entire amount.


Change column type from varchar to text in all tables at once

I guess you don't want to manually run all the necessary ALTER TABLE statements.

You can use the following statement to generate the needed ALTER TABLE statements:

select 'alter table '||table_schema||'.'||table_name||' alter column '||column_name||' type text;'
from information_schema.columns
where table_schema = 'public'
  and column_name = 'description'
  and data_type = 'character varying'
  and character_maximum_length = 255;

replace 'public' with your schema if your tables are not located in the public schema.

Run the above statement, spool the output into a text file, then run the generated script.

Something like this:

psql (9.3.4)
Type "help" for help.
postgres=> \t
Showing only tuples.
postgres=> \o alter.sql
postgres=> select 'alter table '||table_schema||'.'||table_name||' alter column '||column_name||' type text;'
postgres-> from information_schema.columns
postgres-> where table_schema = 'public'
postgres->   and column_name = 'description'
postgres->   and data_type = 'character varying'
postgres->   and character_maximum_length = 255;
postgres=> \i alter.sql


MSSQL Mirroring equivalent to Oracle Data Guard Snapshot Standby

Mirroring does NOT have this capability.  If you are using the Enterprise Edition of SQL Server then an Availability Group (MSSQL's successor to mirroring) with a readable secondary would do this, but you would be required to license both instances with EE licenses.


What standard SQL isolation levels do the SQLite isolation levels correspond to?

DEFERRED, IMMEDIATE, and EXCLUSIVE are not isolation levels. All three modes ensure the serializable isolation level. (SQLite's transcations are always serializable because it actually serializes them; there is a single, database-wide lock.)

IMMEDIATE takes the equivalent of a write lock at the beginning of the transaction; this prevents a deadlock when two concurrent read-only transactions try to update to a write lock. EXCLUSIVE is useful when you want to lock the database without actually writing.


SQL Server Express vs. Oracle Express Edition vs DB2 Express-C

I think you are missing couple important parameters. If you believe the project will advance , you have to keep in mind that one day you will need to switch to non-express version. And at that point license cost becomes very important; switching to another RDMS may require serious refactoring (and usually it does). For startup project I'd not pay too much attention to hardware configuration supported in express version, I'm pretty sure even the lowest will work fine. More important thing in my opinion is DBA's skills; in practice, not so many database professionals are equally good in all RDMS. So ask your DBA which server she/he would prefer before making a decision.


Optimizing postgresql maintenance statements on psql

The most pertinent advice I've seen is in this SO answer, but it seems the with clause has to be inside the SQL statement it's being called in.

Yes, you can use a modifying WITH, i.e. a CTE that deletes from one table and returns the id values to the rest of the query, so they can be used to delete from the second table:

WITH
  links_link_deleted AS
    ( DELETE FROM links_link
        WHERE submitted_on >= timestamp'2016-07-23 01:00:00'
          AND submitted_on < timestamp'2016-08-23 01:00:00'
      RETURNING id
    )
DELETE FROM links_publicreply
  WHERE submitted_on >= timestamp '2016-07-23 01:01:00'
    AND submitted_on < timestamp '2016-09-07 01:00:00'
    AND answer_to_id IN
        ( SELECT id
          FROM links_link_deleted
        )
 ;

  In other words, the following wouldn't work would it?

That would work but it would be two statements.


Some notes

When you create a login, then the name must match AD exactly
EXEC sp_grantlogin 'DOMAIN\Andy Soell
When you map the login to a database user, it can be renamed
EXEC sp_grantdbaccess 'DOMAIN\Andy Soell', 'Dilbert'

The 2nd bit will fix your issue with implied schema. This happens if don't qualify schema (eg dbo) because SQL Server 2000 has pretty much no user-schema separation like later versions

Note: you can't rename users in SQL Server 2000 IIRC: need to run sp_revokedbaccess then sp_grantdbaccess. If you own objects because of your implied schema then you'll need sp_changeobjectowner. I think SQL EM will prompt you.

That is, you should always qualify schema in SQL Server.

CREATE TABLE dbo.viewname...

SELECT * FROM dbo.myTable

EXEC dbo.MyProc

Then no need to change the users


Why are my domain and username being prepended to stored procedures I create?

If you didn't explicitly set it, then objects created by you will be created under the default schema for your user. In SSMS, look in:
Database -> Security -> Users -> <you> -> Properties -> General -> Default schema
ALTER SCHEMA dbo TRANSFER [COMPANYDOMAIN\MYUSERNAME].ThisIsAStoredProcedure;
Change the setting you looked at in 1.

Of course, you may not have permission to do 2 or 3, in which case it's a question for the person that does have permission.


Permissions are exposed to a DBA in DBA_TAB_PRIVS.

SELECT * FROM dba_tab_privs WHERE table_name='EXAMPLE';

Your user John will be able to see his grants in ALL_TAB_PRIVS.


Generally, you want the applications as close as practical to the database server. (This advice holds true regardless of the app & database, so even though I'm a SQL Server guy, I'm going to try to write this to help folks looking for similar answers for MySQL, PostgreSQL, etc.)

Network issues boil down to two things: throughput at latency.

Throughput: the more data the app moves, the slower performance will feel. If the app only does small, targeted queries to bring back the minimum number of rows & columns that they need, then it'll feel fast. On the other hand, if the app has been doing SELECT * FROM TABLE with no filtering, and doing the filtering client-side, then the reduced network bandwidth can be a problem.

Good design: only bring back the minimum data you need for a screen
Bad design: bring back the entire table, filter it client-side (MS Access apps do a lot of this)

Latency: if the app manages its own transactions, latency will be a problem. Some applications start a transaction, then give the database server one row at a time to accomplish a goal. They parcel work out in small doses, and they handle failure/retry logic inside their code (rather than a stored proc.) This used to be fine when the database server was within 1-10 milliseconds of the app, but if you start to stretch that to 50-100ms, transactions will feel exponentially slower to end users.

Good design: build a stored procedure, have your app call it with all the parameters necessary to accomplish a business transaction
Bad design: start a transaction, insert one row, wait for the database server to acknowledge it, then send another row in another table, and another row, holding locks open the whole time

Throughput: the more concurrency you have, the slower performance will feel. If everyone at every office has the exact same usage patterns - like they all come back from lunch at exactly 1PM and start back working at the same time - then the throughput of your network pipe can be a problem. Now you can start to see how the above can start to snowball: if you have a chatty app that manages its own transactions, then it'll have an even tougher sending/receiving the data it needs to the SQL Server, and locks will be held open even longer as the shared pipe throughput gets smaller.

To monitor for that, start by looking at each SQL Server's network port utilization today. You can track that via Perfmon counters or Task Manager, looking to see what percentage of its existing network port it uses - and how that relates to the MPLS connection that you're proposing.


How space is compared in where condition

I have Oracle on local. But you can see example on SQLFiddle

create table a1 ( i varchar(10));

insert into a1 values('a');
insert into a1 values(' ');
insert into a1 values('v ');

select * from a1 where i <> ' ';
select * from a1 where i = ' ';
select * from a1 where i like '% %';


pt-online-schema-change error creating triggers

The error message shows a lock wait timeout.

This means you cannot lock the table.

There is always three triggers needed for pt-osc on the source table:

INSERT trigger
DELETE trigger
UPDATE trigger

The triggers are not create atomically. They are created one at a time.

If root@localhost has GRANT ALL PRIVILEGES on *.*, you should be able to create the trigger. It looks like the table must have very high read/write traffic.

You will have to clean up as follows:

Kill the pt-osc

Login to MySQL and run

USE master
DROP TABLE `_website_new`;
DROP TRIGGER pt_osc_master_website_ins;
DROP TRIGGER pt_osc_master_website_upd;
DROP TRIGGER pt_osc_master_website_del;

The, start pt-osc over again


GROUP BY by primary key column causes each row to become a separate group. In this case the column not included into grouping / aggregating has only one value within the group, so this value may be returned without ambiguity.
If there is no primary column / expression as a part of grouping expression then server must consider that there may be more than one value for a group, and it generates error because of possible ambiguity.
In general the fact that the error is not generated is an extension it seems...
If PK is a part of grouping expression than formally all another parts are excess. I.e. if id is PK then group by id, group by id,a,b,x, group by id,a,b, group by id,b,x, group by id,a,x, ... will have the same meaning and effect.
But the best practice is to use an aggregate function for each non-groupby column / expression in any case.


MySql How to rollback my database to a certain point in time the fastest way?

If you are trying to rollback to previous state after making logical changes i.e. DDL/DMLs - then xtrabackup can help. However, so is making a raw copy of the data while MySQL is shutdown before making the changes. Once you are done with the changes, stop MySQL then restore the previous copy, start it back up.

If you are looking for a more complex point in time recovery - yes, xtrabackup with incremental can help but not as fast the previous suggestion. You can do it like this for example:

Take full backup
Make changes to data
Take incremental backup
Make changes to data
Restore backup from #1
Make more changes to data
Restore backup from #3 - at this point you have restored to a snapshot before you did #4.

This combination of incremental allows you to restore at different points in time instead of single full raw copy.


Option 1: Set up a sharded cluster to distribute writes. However a sharded cluster brings complexity in terms of additional components needed (mongos, config servers, multiple replica sets, etc), along with technological overhead you'll need to deal with (balancer, chunk migration, no point-in-time backup/restore, etc).

Option 2: Set default writeConcern to 0, a.k.a. 'fire and forget' mode. Read more here. Basically, your application writes to mongod process (mongod in Primary, if you use a replica set), and receive no acknowledgement back. Two ways to use writeConcern:

Set as default across replica set
Use writeConcern per query

Read more about it here.


SQL Server 2012 Powershell script to generate database with schema and data

I wanted to use Powershell to script a small database so that I can track changes in git. I found the same script you did to use as a starting point:
Automated Script Generation with Powershell and SMO

You can find all of the options for the ScriptingOptions class here: ScriptingOptions Class

You're getting the error because of the now deprecated ScriptTransfer method.

Replace this line

$transfer.ScriptTransfer()

with this

$transfer.EnumScriptTransfer()

and your script should run.


Run this on the production database

DBCC OPENTRAN(name_of_database)

There is probably an open transaction that won't allow your logfile (.ldf) to recycle. You can also verify this with

select log_reuse_wait_desc, * from sys.databases

Once you've killed or stopped the transaction that's preventing your ldf from being reused, you can shrink the log file to a more reasonable size.

USE MYDATABASE
GO

-- get logical name of ldf file
select name, type_desc from sys.databases where type=1

/* checks to see if ldf's vlf (virtual log files) can be marked inactive and reused */

/* this may take a long time depending on the size of your database and the activity*/

CHECKPOINT
CHECKPOINT

/* shrink the log file */
DBCC SHRINKFILE (N'logicalname' , some_reasonable_size_in_mb)

Once you've taken these steps on your production database, you can retry backing up the database for you dev environement.

Before restoring your backup, you can check how much space it will take up with

RESTORE FILELISTONLY FROM DISK = 'path\of\backup_file'

Look at the size column which will give you the size of each file being restored in bytes. This way you'll have a better idea of how much space you need before even trying to restore. Prevents unwanted surprises.


Locked out of my own database through invariant() failure

Deleting .ns file in data/db worked for me.

Invariant failure usually occurs if mongo is not been shut down properly, and improper shutdown will corrupt .ns (namespace that mongo creates for each database) file. So deleting .ns file will solve the issue.


No, that's one of the main benefits of mirroring is the page repair; it checks for corruption and then tries to address it if it finds any.


Setting up Availability group for report server databases

The reason the documentation states that automatic failover is not supported is that there are post-failover tasks that must be carried out to ensure the Report Server is completely online. See this section for information on exactly what tasks need to be completed. Essentially it boils down to needing to stop the SQL Agent on the old primary and restart the Report Server service.

  I am thinking to create the listener first with 2ips for those multi
  subnet so that we can use same while installing reporting services and
  point the listener name in instance and it's report server database to
  it so that it is aware of primary replica even after failover. Is my
  above understanding correct? Is that how it's suppose to work?

Yes, build your AG and configure the listener first, then when you'r configuring the report server you can point it at the listener.

  Secondly, document says automatic failover is not supported even when
  I use sync mode between N1 and N2 ? Is that true? And why would that
  be the case only with SSRs? Also if it is , can we automate the
  faiover or does it have to be manual everytime since I am thinking
  data source application string can use the option multi subnet
  failover = true ?

There seems to be some crossover here where you're talking about report data sources and Report Server databases. RS databases are the databases used by report server to manage and maintain the report server. They're typically named ReportServer and ReportServerTempDB. These databases do not support automatic failover because there are post-failover tasks you must complete to bring the service back to a 100% normal state.

These tasks can be automated through scripts easily enough, but that is is what the documentation means by not supported. For your report data sources, these only need AG specific parameters in the connection string (such as MultiSubnetFailover or ApplicationIntent) if the source databases are in an AG as well. If they are then adding those keys to your connection strings will ensure the reports continue working post-failover. They should handle failover automatically.

Something to note is this line from the documentation:

  The use of MultiSubnetFailover, with the report server databases, is not supported.

What this means for you, given you have a multi-subnet cluster, is that you should set the RegisterAllProvidersIP setting to 0. This setting is 1 by default and means that each IP assigned to the listener is registered in DNS. If MultiSubnetFailover is enabled, the client will try to connect to each IP in parallel and return the connection from the first IP that responds. As this setting is not supported by the RS service for connections to the RS databases, after a failover you will likely encounter timeouts trying to connect.

The solution is to set RegisterAllProvidersIP to 0 and set a low TTL for that host record. This ensures that only the active IP is registered with DNS for the listener, and after failover the new IP is registered with DNS. This means new connections to the listener use the correct IP and you avoid timeouts.


How do I skip an offline database over the network in MySQL import?

With some suggestion from Akina, I'm able to find one post in SO about how to determine if an IP is online or not through the network before executing the MySQL import command. Here is the link to the answer : https://stackoverflow.com/a/21252613/10910692

Below is the code I'm using in my batch file:

ping -n 1 192.xxx.xxx.xx | find "TTL=" >nul
if errorlevel 1 (
    echo host is offline..
    ) else (
    echo Updating host...
    mysql.exe -h 192.xxx.xxx.xx -P -u -p inventory < C:\xxx\data\xxx.sql
)

I also took Akina's suggestion to concatenate both the import and update query into one file and only need to call that one file for the update. As I mention in my question, the code above is repeated for about 20 times.

If I stick to my previous method and assuming that all IP is offline, it will take about 800 secs = 13-14 mins for the batch file to finish processing while with the method above, it reduces to 1-2 mins only! (based on assumption that each command took 5 secs max while in reality its less than 5 secs per command).


Simply add -t <sequencename> for all the sequences you want to dump.


MySQL-5.5

Bummer.

It appears Analytical Functions are not supported in v5.5

If it did, the solution would be something like:

select TaskJournalId, TaskId, TaskStatus, TaskStart, TaskEnd, TaskInfo
from (
  select  TaskJournalId, TaskId, TaskStatus, TaskStart, TaskEnd, TaskInfo
    ,row_number() over (partition by TaskJournalId,TaskId order by TaskStart DESC) rn
  from TaskJournal
)
where TaskStatus=3 and rn=1;

My Answer

Upgrade your database.


the database system to store any value associated with such external writes temporarily in a special relation in the database

Can't we behave with these values like other values?

That's exactly what is suggested in your quote: you store data to be written externally in a (special) relation, because they are special, transient entities.
Suppose you have a workflow system in which a task is assigned to a user by updating its user_id attribute ("internal write"). It also needs to send an email notification (an external write) to that user. These two happen asynchronously, but the external one must always happen after the internal one is persisted. You update the task record as usual (nothing "fake" about it) and create a new outbound_email entity with whatever information is relevant. When the task update is committed, you process outbound_email and delete the transient record.
This works pretty much like a write-ahead log for external actions: you first create a record for the action, then perform that action at an appropriate moment, then discard the log record as it is no longer needed. If the flow is interrupted, you replay the "log" to recover.


PL/pgSQL - Indexes vs Collation vs Pattern Ops

consider using the citext data type available in contrib. you don't have to do this upper / lower nightmare anymore. for the regular expression type you can use gist along with gist_trgm_ops. it will boost regular expressions nicely.


Cannot see new drive in SSMS maintenance wizard

+1 for sensibly wanting to get the backups on a separate drive. However, if the new cluster volume is on the same SAN, you're not really accomplishing much. It would likely be much better to run backups to a file share on a totally different system.

As a workaround, you can easily add an "Execute T-SQL Statement" task to your Maintenance Plan to manually specify the backup statement:


Can SSRS XML data sources contain spatial data?

As far as i have been able to read/research it seems that the answer is no.
However i have found that I  can merge 2 datasets to achieve what I wanted to do.


Logging of incorrect row in SQL Server Integration Services

Check this:
https://stackoverflow.com/questions/11628352/etl-ssis-redirecting-error-rows-to-a-seperate-table

It has all the steps you need to have the error records stored somewhere else.


write_location and flush_location of pg_stat_replication view

The two columns in pg_stat_replication are basically pointing out the difference between write(2) and fsync(2)

From the write(2) manpage:

  write() writes up to count bytes from the buffer pointed buf to the file referred to by the file descriptor fd.

  The  number  of  bytes  written  may be less than count if, for example, there is insufficient space on the underlying physical medium, or the RLIMIT_FSIZE resource
  limit is encountered (see setrlimit(2)), or the call was interrupted by a signal handler after having written less than count bytes.  (See also pipe(7).)

  For a seekable file (i.e., one to which lseek(2) may be applied, for example, a regular file) writing takes place at the current file offset, and the file offset is
  incremented  by  the number of bytes actually written.  If the file was open(2)ed with O_APPEND, the file offset is first set to the end of the file before writing.
  The adjustment of the file offset and the write operation are performed as an atomic step.

  POSIX requires that a read(2) which can be proved to occur after a write() has returned returns the new data.  Note that not all file systems are POSIX conforming.

From the fsync(2) man page:

  fsync() transfers ("flushes") all modified in-core data of (i.e., modified buffer cache pages for) the file referred to by the file descriptor fd to the disk device
  (or other permanent storage device) where that file resides.  The call blocks until the device reports that the transfer has completed.  It  also  flushes  metadata
  information associated with the file (see stat(2)).

  Calling  fsync()  does  not  necessarily  ensure  that the entry in the directory containing the file has also reached disk.  For that an explicit fsync() on a file
  descriptor for the directory is also needed.

  fdatasync() is similar to fsync(), but does not flush modified metadata unless that metadata is needed in order to allow a subsequent data retrieval to be correctly
  handled.   For  example,  changes  to  st_atime  or  st_mtime (respectively, time of last access and time of last modification; see stat(2)) do not require flushing
  because they are not necessary for a subsequent data read to be handled correctly.  On the other hand, a change to the file size (st_size, as  made  by  say  ftrun-
  cate(2)), would require a metadata flush.

  The aim of fdatasync() is to reduce disk activity for applications that do not require all metadata to be synchronized with the disk.

Under normal operation by default, PostgreSQL calls fsync (or fdatasync, if you're on linux), to ensure that the data in a WAL segment is well and truly written to disk.

In normal operation, when a write call happens, it's basically copying that data from PostgreSQL to the filesystem cache, which is just another bit of RAM. If the server crashes, then you will still lose that data, because it's still in RAM, even though it's just a different part of RAM.

On COMMIT, though, PostgreSQL calls fsync, which forces the WAL segment data to be written out to disk, not just to the filesystem cache in RAM. Which ensures that after a crash, committed data is still committed, and can be replayed.

It also has the added advantage of needing to call fsync fewer times for the data files, because all of the necessary data to properly replay the changes to those files are contained in the WAL segments, which are durably persisted to disk.

A nice description of this behavior under Linux can be found in the article Flushing Disk Buffers.

The syncing behavior can be controlled with wal_sync_method and fsync

Hope that helps answer your question. =)


How to implement a 'default' flag that can only be set on a single row

SQL Server 2008 - Filtered unique index

CREATE UNIQUE INDEX IX_Foo_chk ON dbo.Foo(chk) WHERE chk = 'Y'


How do you identify database .mdf and .ldf file names?

Querying the attached databases

You should query the sys.master_files table like this:

SELECT * FROM sys.master_files

...or you could restrict the output to only relevant columns:

SELECT NAME,
   physical_Name,
   database_id,
   FILE_ID,
   type_desc,
   state_desc,
   SIZE * 8 / 1024 AS SizeMB
FROM   sys.master_files

This will return a list of database that are attached to your SQL Server instance. This will not list databases that have been detached from your SQL Server instance.

Deprecated View sysaltfiles

You shouldn't be using the sys.sysaltfiles view as this is deprecated and has been added for backwards compatibility only as is mentioned in the reference given by MD Haidar Ali Khan.

  This SQL Server 2000 system table is included as a view for backward compatibility. We recommend that you use the current SQL Server system views instead. To find the equivalent system view or views, see Mapping System Tables to System Views (Transact-SQL). This feature will be removed in a future version of Microsoft SQL Server. Avoid using this feature in new development work, and plan to modify applications that currently use this feature.

The sys.database_files view

The sys.database_files table as pointed out by SQL_Underworld is only for the database you are currently connected to.

Attaching database files as database

You could attach the database files you have found on your disk with the following command:

USE [master]
GO
CREATE DATABASE [xxx] ON
    (FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\xxx.mdf'),
    (FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\xxx.ldf')
    FOR ATTACH
GO

This will attach the xxx.mdf and xxx.ldf files to your SQL Server instance as database xxx. You could then rerun the above query to find information about the database.

Detaching a database

After you have found the relevant information you could then detach the database again by running the following query:

USE [master]
GO
EXEC sp_detach_db @dbname = N'xxx'
GO

Size column in sys.master_files

There is a delay in the size reported in sys.master_files according to the MSDN article sys.master_files (Transact-SQL)

  When you drop or rebuild large indexes, or drop or truncate large tables, the Database Engine defers the actual page deallocations, and their associated locks, until after the transaction commits. Deferred drop operations do not release allocated space immediately. Therefore, the values returned by sys.master_files immediately after dropping or truncating a large object may not reflect the actual disk space available.


Setting up Master-Master Replication in MySQL on a live system

create replication user on backup
configure replication on live as slave and backup as master
start slave
profit!


Replication setup on a windows server is equivalent to doing it in other systems: The official manual provides you a detailed guide. In short:

Configure on both servers a different server-id, and activate the binary log on the master. If it is not already done, the most typical way to do that is modify the my.ini file and restart the service (net restart mysql):

[mysqld]
log-bin
server-id = 1

Make also sure that the master is accesible from the slave node and it is not bound to localhost (bind-address = 127.0.0.1)
Create a user on the master for the slave to connect with REPLICATION SLAVE privileges (use MySQL-command line for this):

mysql master> CREATE USER 'user_name'@'ip.of.the.slave' IDENTIFIED BY 'password';
mysql master> GRANT REPLICATION SLAVE ON *.* TO 'user_name'@'ip.of.the.slave';

Make a backup of the master and apply it to the slave. There are several ways to do that (stopping the server and copying the files works), but the easiest would be to se mysqldump (navigate to the bin directory of your sql-server installation (f.e. C:\Program Files\MySQL\MySQL Server 5.7\bin). Also you may need to provide credentials by appending this to both commands: -uroot -pPassword)

(on the master windows command line) mysqldump --all-databases --master-data > dump.sql
(on the slave windows command line ) mysql < dump.sql

At the beginning the dump.sql you will find a line like this:

-- CHANGE MASTER TO MASTER_LOG_FILE = 'file_name', MASTER_LOG_POS = file_pos;

Use those parameters to configure the replication on the slave:

mysql slave> CHANGE MASTER TO MASTER_HOST = 'ip.of.the.master',
                 MASTER_USER = 'user_name_you_just_created',
                 MASTER_PASSWORD = 'password_you_just_defined',
                 MASTER_LOG_FILE = 'file_name',
                 MASTER_LOG_POS = file_pos;

(beware, no quotes for integers, like MASTER_LOG_POS).
If everything is ok until now, just start replication on the slave:

mysql slave> START SLAVE;

To check that it is running, execute:

mysql slave> SHOW SLAVE STATUS;

And look for:

   Slave_IO_Running: Yes
  Slave_SQL_Running: Yes

GTID-based replication is a bit different, and I have not mentioned it.


How to execute a trigger procedure with an expression as an argument?

It cannot be done.  From the docs:

The arguments are literal string constants


Assuming all previously-created columns are fixed-width like char and datetime, adding column C above just added it to the end of the fixed-width section of the record (effectively a meta-only change). However, recasting it as a varchar required it to be moved to the variable-width section of the record, forcing an implicit rebuild of the table. Paul Randal explains the internals of records in great detail in Inside the Storage Engine: Anatomy of a Data Record.


Write to different schema with own tablespace from another account

Quota calculation is based on table owner, not on the user that inserts (or deletes or updates) data.

For example if u1 creates a table, u2 inserts 300MB of data, then u3 deletes 100MB of that same data and u4 updates 100MB of that data, only the quota of u1 is checked, for other users, just permissions at the table level (grant insert/delete etc) are checked.

In the example you give, there is no contradiction, u2 inserts into the table owned by u1, so only the quota of u1 is checked.

Note there is no need to specify QUOTA 0M as the default is not to allow quota on tablespaces.

http://docs.oracle.com/cd/B10500_01/server.920/a96521/users.htm#15298

You can assign each user a tablespace quota for any tablespace (except a temporary tablespace). Assigning a quota does two things:

Users with privileges to create certain types of objects can create those objects in the specified tablespace.

Oracle limits the amount of space that can be allocated for storage of a user's objects within the specified tablespace to the amount of the quota.

By default, a user has no quota on any tablespace in the database. If the user has the privilege to create a schema object, you must assign a quota to allow the user to create objects. Minimally, assign users a quota for the default tablespace, and additional quotas for other tablespaces in which they can create objects.


Mysql:Need to upload data to database, get duplicate key error - how can dup keys be ignored

You'll have to decide what you want to happen when a duplicate is found, because you can:

ignore it (insert ignore ...)
do something (insert ... on duplicate key update)

Since mysqldump usually dumps each table in a single insert statement, you can easily just add those keywords in the dump file.

See this SO post for a more in-depth discussion on the pros and cons of the two possiblities.


Join on primary keys to primary key vs primary key in range

Edit. I misunderstood the question. I thought this was about app design rather than query performance.

For an App: Personally i would avoid using an intermediate table. Its just extra steps and complication.

The #table makes sense for queries.

Provided you have useful indexes and your stats are up to date, you are typically better off leaving SQL Server to decide the best execution plan.

Depending on the growth and distribution of data the best execution plan may change over time. So i would be reluctant to invest a lot of time and effort coding to squeeze the last 1% of extra performance.


Missing archivelogs when restoring duplicate

Use RESTORE ... PREVIEW. I intentionally deleted some archivelogs, so I do not have a backup of them.

Now I want to check if I can restore the database to a specified time:

run
{
  set until time "to_date('2018-11-12 19:58:00', 'YYYY-MM-DD HH24:MI:SS')";
  restore database preview;
}

Output:

executing command: SET until clause

Starting restore at 12-NOV-18
using channel ORA_DISK_1

List of Backup Sets
===================

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
4       Full    1.11G      DISK        00:00:03     11-NOV-18
        BP Key: 4   Status: AVAILABLE  Compressed: NO  Tag: TAG20181111T221533
        Piece Name: /oracle_backup/DOMINO_O77/04thvv85_1_1
  List of Datafiles in backup set 4
  File LV Type Ckp SCN    Ckp Time  Abs Fuz SCN Sparse Name
  ---- -- ---- ---------- --------- ----------- ------ ----
  1       Full 2397081    11-NOV-18              NO    +DATA/DOMINO_O77/DATAFILE/system.257.991918055
  3       Full 2397081    11-NOV-18              NO    +DATA/DOMINO_O77/DATAFILE/sysaux.258.991918107
  4       Full 2397081    11-NOV-18              NO    +DATA/DOMINO_O77/DATAFILE/undotbs1.259.991918131
  7       Full 2397081    11-NOV-18              NO    +DATA/DOMINO_O77/DATAFILE/users.260.991918133

no backup of archived log for thread 1 with sequence 63 and starting SCN of 2784642 found to restore
no backup of archived log for thread 1 with sequence 64 and starting SCN of 2784723 found to restore
no backup of archived log for thread 1 with sequence 68 and starting SCN of 2784830 found to restore

List of Backup Sets
===================

BS Key  Size       Device Type Elapsed Time Completion Time
------- ---------- ----------- ------------ ---------------
7       151.71M    DISK        00:00:01     12-NOV-18
        BP Key: 7   Status: AVAILABLE  Compressed: NO  Tag: TAG20181112T195537
        Piece Name: /oracle_backup/DOMINO_O77/09ti2bdp_1_1

  List of Archived Logs in backup set 7
  Thrd Seq     Low SCN    Low Time  Next SCN   Next Time
  ---- ------- ---------- --------- ---------- ---------
  1    43      2396293    11-NOV-18 2397794    11-NOV-18
  1    44      2397794    11-NOV-18 2398565    11-NOV-18
  1    45      2398565    11-NOV-18 2398568    11-NOV-18
  1    46      2398568    11-NOV-18 2398574    11-NOV-18
  1    47      2398574    11-NOV-18 2398581    11-NOV-18
  1    48      2398581    11-NOV-18 2398588    11-NOV-18
  1    49      2398588    11-NOV-18 2398606    11-NOV-18
  1    50      2398606    11-NOV-18 2398912    11-NOV-18
  1    51      2398912    11-NOV-18 2498959    11-NOV-18
  1    52      2498959    11-NOV-18 2498962    11-NOV-18
  1    53      2498962    11-NOV-18 2499076    11-NOV-18
  1    54      2499076    11-NOV-18 2499560    11-NOV-18
  1    55      2499560    11-NOV-18 2499580    11-NOV-18
  1    56      2499580    11-NOV-18 2601155    11-NOV-18
  1    57      2601155    11-NOV-18 2601158    11-NOV-18
  1    58      2601158    11-NOV-18 2601263    11-NOV-18
  1    59      2601263    11-NOV-18 2601701    11-NOV-18
  1    60      2601701    11-NOV-18 2601705    11-NOV-18
  1    61      2601705    11-NOV-18 2601709    11-NOV-18
  1    62      2601709    11-NOV-18 2784642    12-NOV-18

BS Key  Size       Device Type Elapsed Time Completion Time
------- ---------- ----------- ------------ ---------------
9       13.50K     DISK        00:00:00     12-NOV-18
        BP Key: 9   Status: AVAILABLE  Compressed: NO  Tag: TAG20181112T195610
        Piece Name: /oracle_backup/DOMINO_O77/0bti2beq_1_1

  List of Archived Logs in backup set 9
  Thrd Seq     Low SCN    Low Time  Next SCN   Next Time
  ---- ------- ---------- --------- ---------- ---------
  1    65      2784741    12-NOV-18 2784798    12-NOV-18
  1    66      2784798    12-NOV-18 2784812    12-NOV-18
  1    67      2784812    12-NOV-18 2784830    12-NOV-18
recovery will be done up to SCN 2784849
Media recovery start SCN is 2397081
Recovery must be done beyond SCN 2397081 to clear datafile fuzziness
Finished restore at 12-NOV-18

The complete recovery is not possible, and RMAN reported the missing archivelog backups:

no backup of archived log for thread 1 with sequence 63 and starting SCN of 2784642 found to restore
no backup of archived log for thread 1 with sequence 64 and starting SCN of 2784723 found to restore
no backup of archived log for thread 1 with sequence 68 and starting SCN of 2784830 found to restore


Oracle partition by interval using persian calendar

I don't see any way to have intervals that are defined in a different calendar than your database-level NLS_CALENDAR. You could get the same effect by partitioning on a numeric representation of the (Persian) month each date falls in, using a virtual column:

create table test_temp_times (
  id number(18) not null,
  xdate date not null,
  str varchar2(20),
  ydate as (to_number(to_char(xdate, 'YYYYMM', 'nls_calendar=persian')))
)
partition by range(ydate)
interval(1)
(partition p0 values less than (139201))
enable row movement;

If that is populated with a record for every day for the year following your example start date:

insert into test_temp_times (id, xdate, str)
select level, date '2013-03-20' + level, null
from dual
connect by level < 366;

The partitions that are created will look something like:

select table_name, partition_name, high_value
from user_tab_partitions where table_name = 'TEST_TEMP_TIMES';

TABLE_NAME                     PARTITION_NAME                 HIGH_VALUE
------------------------------ ------------------------------ ----------
TEST_TEMP_TIMES                P0                             139201
TEST_TEMP_TIMES                SYS_P479                       139202
TEST_TEMP_TIMES                SYS_P480                       139203
TEST_TEMP_TIMES                SYS_P481                       139204
TEST_TEMP_TIMES                SYS_P482                       139205
TEST_TEMP_TIMES                SYS_P483                       139206
TEST_TEMP_TIMES                SYS_P484                       139207
TEST_TEMP_TIMES                SYS_P485                       139208
TEST_TEMP_TIMES                SYS_P486                       139209
TEST_TEMP_TIMES                SYS_P487                       139210
TEST_TEMP_TIMES                SYS_P488                       139211
TEST_TEMP_TIMES                SYS_P489                       139212
TEST_TEMP_TIMES                SYS_P490                       139213

 13 rows selected

And you can check which partitions the month boundaries fall in:

select utp.partition_name, min(ttt.xdate), max(ttt.xdate)
from test_temp_times ttt
join user_objects uo on uo.object_id = dbms_rowid.rowid_object(ttt.rowid)
join user_tab_partitions utp on utp.table_name = uo.object_name
and utp.partition_name = uo.subobject_name
group by utp.partition_name
order by partition_name;

PARTITION_NAME                 MIN(TTT.XDATE) MAX(TTT.XDATE)
------------------------------ -------------- --------------
P0                             2013-03-20     2013-03-20
SYS_P479                       2013-03-21     2013-04-20
SYS_P480                       2013-04-21     2013-05-21
SYS_P481                       2013-05-22     2013-06-21
SYS_P482                       2013-06-22     2013-07-22
SYS_P483                       2013-07-23     2013-08-22
SYS_P484                       2013-08-23     2013-09-22
SYS_P485                       2013-09-23     2013-10-22
SYS_P486                       2013-10-23     2013-11-21
SYS_P487                       2013-11-22     2013-12-21
SYS_P488                       2013-12-22     2014-01-20
SYS_P489                       2014-01-21     2014-02-19
SYS_P490                       2014-02-20     2014-03-19

At least, I think that's what you're trying to achieve... Unfortunately I can't add a demo as SQL Fiddle doesn't have the partitioning option, but this is tested against 11.2.0.3.

Of course, you have to make it use the partitions for the query... if I just do:

select * from test_temp_times
where xdate = date '2013-11-01';

It finds the row with plan:

-----------
| Id  | Operation           | Name            | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
-------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |                 |     1 |    47 |   164   (0)| 00:00:02 |       |       |
|   1 |  PARTITION RANGE ALL|                 |     1 |    47 |   164   (0)| 00:00:02 |     1 |1048575|
|*  2 |   TABLE ACCESS FULL | TEST_TEMP_TIMES |     1 |    47 |   164   (0)| 00:00:02 |     1 |1048575|
-------------------------------------------------------------------------------------------------------

If I explicitly add the virtual column to the query:

select * from test_temp_times
where xdate = date '2013-11-01'
and ydate = to_number(to_char(date '2013-11-01', 'YYYYMM', 'nls_calendar=persian'));

Then it knows which partition to query:

----------------------------------------------------------------------------------------------------------
| Id  | Operation              | Name            | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
----------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT       |                 |     1 |    47 |    14   (0)| 00:00:01 |       |       |
|   1 |  PARTITION RANGE SINGLE|                 |     1 |    47 |    14   (0)| 00:00:01 |     9 |     9 |
|*  2 |   TABLE ACCESS FULL    | TEST_TEMP_TIMES |     1 |    47 |    14   (0)| 00:00:01 |     9 |     9 |
----------------------------------------------------------------------------------------------------------

Clearly I haven't created any indexes yet. If you are looking for a whole month's worth of data, you would only need to query on a single ydate value, and ignore xdate; but presumably you'd need a mix at least some of the time.


Running SQL Server on VMWare along with many other VM's

There is nothing inherently wrong with the current configuration however you have not included info that would be relavent, such as whether the host is overcommitted on RAM/CPUs. What problem are you trying to solve - performance and/or HA/DR? Assuming you are not overcommitted:

Make sure the SQL Server instance(s) have sensible max memory settings. Ensure resource is allocated to accomodate that. If money is no object, use resource pools.
VMware (in my opinion) is a better choice than HyperV, but it isn't a big deal which you choose.
If you have enough memory to hold all the databases on the instance(s) which are virtualized, your main concern after that is CPU pressure, which is easy to measure. If you don't (which seems to be the case from your description), you're relying on storage for IO much more, so using the SSDs is obviously a good approach.
I'd suggest the contractor DBA you are working with is coming up with ideas designed to keep him/her in a job as opposed to solving a problem. Either that or they have no experience of VMware. Or both. You can still allocate resources in VMware in much the same way as with HyperV. Striping tables is not necessarily a performance improvement, depending on the nature of your storage array and the IO requirements of the other VMs.
If your SQL instance is Enterprise edition, you ought to use a physical box.
WHen you say "6 CPUs" are you talking about an 8 socket box with unpopulated sockets or a single socket populated with 6 cores? If it is the latter, your performance issues will almost certainly be caused by processor availability, as VMware will only use all cores allocated to a VM once they all become available.

Bottom line - it seems that the host is under resourced for memory and CPU cores, which won't go away with HyperV. If you have need for more than 128GB of memory (based on amount of data in all databases) a physical box for the SQL server would be a good choice.

I wouldn't bother with anything fancy like table partitioning until you have sorted your resource issues.


How do I use pg_dump and pg_restore to move a large database to another machine?

When you do

pg_restore --dbname=mydb --no-owner mydb.dump

You are trying to connect to the mydb database which still does not exist. Connect to an existent one and use the --create parameter:

pg_restore --dbname=existentdb --no-owner --create mydb.dump

http://www.postgresql.org/docs/current/static/app-pgrestore.html


merge back to back rows based on start & end date

WITH x AS
(
SELECT
    Zone,
    Status,
    CASE WHEN
             COALESCE(LAG(Status)
             OVER (PARTITION BY Zone ORDER BY Zone, [From]), '') <> Status
         THEN 1 ELSE 0 END AS LS,
    [From],
    [To]
FROM
    t
), Y AS
(
SELECT
    Zone,
    Status,
    SUM(LS) OVER (PARTITION BY Zone ORDER BY Zone, [From]) Grp,
    [From],
    [To]
FROM
    x
)
SELECT
    Zone,
    Status,
    MIN([From]) [From],
    MAX([To]) [To]
FROM
    y
GROUP BY
   Zone, Status, Grp
ORDER BY
   Grp;

Zone | Status | From                | To
:--- | :----- | :------------------ | :------------------
z1   | Open   | 01/01/2015 00:00:00 | 10/01/2015 00:00:00
z1   | Closed | 10/01/2015 00:00:00 | 20/01/2015 00:00:00
z1   | Open   | 20/01/2015 00:00:00 | 26/01/2015 00:00:00

db<>fiddle here


Unique index for subquery with RANK() function

Relying on the order of RevisionId to determine the latest revision is incredibly dangerous, and points to a design issue here.

Since you're using SQL Server 2008+, what I would recommend is to add a bit flag IsLatest to the MyEntity table to identify the latest revision. Then, create a unique filtered index to constrain each entity to only have a single latest revision (technically, this isn't required given what you asked for, but it's a Really Good Idea):

CREATE UNIQUE NONCLUSTERED INDEX UC_MyEntity_IsLatest_MyEntityId
    ON [dbo].[MyEntity](IsLatest, MyEntityId)
        WHERE (IsLatest = 1);

Then use the same technique to enforce the constraint on the name:

CREATE UNIQUE NONCLUSTERED INDEX UC_MyEntity_IsLatest_Name
    ON [dbo].[MyEntity](IsLatest, Name)
        WHERE (IsLatest = 1);

You'll need to manage the flag by turning it off from the "previous latest" revision before inserting the "new latest" revision, which is not much extra work. I'll leave it up to you to determine how this should work with the Deleted flag involved.

This solution enforces the constraint by design, and will greatly simplify the view definition, which you may not even need to index separately. (Indexing the Body column which is nvarchar(MAX) is a bit suspect, but that's a separate issue.)

Note: I included the IsLatest column in the key of the indexes on purpose, as the optimizer isn't smart enough to implicitly include the value of that column from the filter predicate (since the predicate is arbitrary, I assume it doesn't even try). This is what will allow the index to be used when you specify WHERE IsLatest = 1 in a query.


Create users identified by the same password as another user in MariaDB 10.0.X

CREATE USER has number of forms. You can provide the plain password as well as already hashed value stored in the engine:

authentication_option:
  IDENTIFIED BY 'string'
| IDENTIFIED BY PASSWORD 'password_hash'
| IDENTIFIED {VIA|WITH} authentication_plugin
| IDENTIFIED {VIA|WITH} authentication_plugin {USING|AS} 'authentication_string'


But here in this case I want to know how MongoDB will create index on _id; is it on both _id.a and _id.b field or just on _id.b field?

The index will index the embedded document as a whole; i.e. the entirety of { "a" : 8, "b" : ObjectId(...) }. This is a different index entirely than one that would be built solely on _id.a, _id.b, or a compound index on both fields.

Note that querying on a nested document requires an exact match of the specified document. To illustrate:

> db.embed.insert({ "_id" : { "a" : 1, "b" : 2 } } )
WriteResult({ "nInserted" : 1 })

> db.embed.find( { "_id" : { "b" : 2, "a" : 1 } } )
>

> db.embed.find( { "_id" : { "a" : 1, "b" : 2 } } )
{ "_id" : { "a" : 1, "b" : 2 } }

If a single query will be filtering on _id.a, field1 and field2 (e.g. db.foo.find( { "_id.a" : 8, "field1" : "value1", "field2" : "value2" }) ) then then I would recommend building a compound index on the three fields, i.e. { "_id.a" : 1,  "field1" : 1, "field2" : 1 }.

Update with more info for comment:

The two queries are not functionally equivalent; the 2nd query will only filter on { "_id": {$lt:{"b":5} }, "field1": "value1" }. For example, try the following:

db.foo.insert({ "_id" : { "a" : 1, "b" : 1 } } )
db.foo.insert({ "_id" : { "a" : 2, "b" : 2 } } )
db.foo.insert({ "_id" : { "a" : 2, "b" : 1 } } )
db.foo.insert({ "_id" : { "a" : 1, "b" : 2 } } )

db.foo.find({
  "_id.a":{ "$gt" : 1 },
  "_id.b":{ "$lt": 2}
})

db.foo.find({
  "_id": { "$gt" : { "a": 1 } },
  "_id": { "$lt" : { "b": 2 } }
})

Note that queries that filter on multiple fields have an implicit logical AND as long as the fields being filtered on are different. The second query will effectively only be filtering on "_id": { "$lt" : { "b": 2 } } as there are two filters on _id. The $and filter would be needed for the logical AND to be used as intended in this query.

That being said, these two queries are asking different things. The first query is filtering on the embedded values "_id.a" and "_id.b":

> db.foo.find({
...   "_id.a" : { "$lt" : 2 }
... })
{ "_id" : { "a" : 1, "b" : 1 } }
{ "_id" : { "a" : 1, "b" : 2 } }
>

> db.foo.find({
...   "_id.b" : { "$lt" : 2 }
... })
{ "_id" : { "a" : 1, "b" : 1 } }
{ "_id" : { "a" : 2, "b" : 1 } }

Whereas the second query is asking to return documents where _the entire value of id is greater than / less than the value specified. This can return unexpected results:

> db.foo.find({
...   "_id": { "$lt" : { "a": 2 } }
... })
{ "_id" : { "a" : 1, "b" : 1 } }
{ "_id" : { "a" : 1, "b" : 2 } }
>
> db.foo.find({
...   "_id": { "$lt" : { "b": 2 } }
... })
{ "_id" : { "a" : 1, "b" : 1 } }
{ "_id" : { "a" : 1, "b" : 2 } }
{ "_id" : { "a" : 2, "b" : 1 } }
{ "_id" : { "a" : 2, "b" : 2 } }
> db.foo.find({
...   "_id": { "$gt" : { "b": 1 } }
... })
>