The sp_WhoIsActive documentation says:

  The [used_memory] column is also reported based on a number of 8 KB pages. The number a combination of both procedure cache memory and workspace memory grant.

However, I need to point out a few things in your question:

Queries that are waiting on compilation memory aren't shown in your screenshot
Those queries waiting for compilation memory are looking for a different kind of memory (gateways) - I'm not saying you don't have memory pressure, I'm just saying I probably wouldn't sort by memory_used to track the problem down
Your screenshot shows a serious blocking issue - queries have been waiting minutes (not seconds) because they're blocked by others

In your case, I'd start by troubleshooting the lead blocker. Don't worry about the queries waiting to compile - they haven't even started yet. In your scenario, the last thing you should be doing is starting even more queries, which are likely to just get blocked anyway and consume more workspace memory while they wait. Get the lead blocker out of the way instead.


What has to be adhered to is the query you know you are going to make.

Let go back to the original question's query

SELECT /* [things omitted] */ articles.blogpost_id, articles.id AS articleid
FROM blogposts
JOIN articles ON articles.blogpost_id = blogposts.id
WHERE blogposts.deleted = 0
AND blogposts.title LIKE '%{de}%'
AND blogposts.visible = 1
AND blogposts.date_published <= NOW()
ORDER BY blogposts.date_created DESC
LIMIT 0 , 50

Look carefully at the WHERE clause. I see two static values

blogposts.deleted = 0
blogposts.visible = 1

These cannot be indexes by themselves because of their implied cardinality. Think about it:

deleted is either 0 or 1 (That's a Cardinality of 2)
visible is either 0 or 1 (That's a Cardinality of 2)

All Query Optimizers (MySQL, Oracle, PostgreSQL, MSSQL, etc) would take one look at those indexes and decide not to use the index.

Look at the ORDER BY clause : ORDER BY blogposts.date_created DESC. This shows a predictable ordering schema. This may help bypass the need for sorting.

Combining these three columns into a single index gives the Query Optimizer some relief by gathering data with delete=0,visible=1,date_created already merged.

Although MySQL is perfectly capable of doing Index Merging, you should never expect the Query Optimizer to do index merging on indexes with very low cardinalities. Even if it did, just expect poor performance. Query Optimizers will choose full table scans, full index scans, and range scans over lopsided index merges any day. Creating covering indexes can bypass having to do index merges, but it can become a burden or a big waste of time and diskspace if the cardinality of the multiple-column index is still too low. Thus, the Query Optimizer would still choose not use it. Therefore, you must know your key distribution well in order to formulate multiple column indexes that you will write queries to use properly.

Here are some nice Links on Covering Indexes and When to Make Them

http://peter-zaitsev.livejournal.com/6949.html
http://www.mysqlperformanceblog.com/2006/11/23/covering-index-and-prefix-indexes/
http://ronaldbradford.com/blog/tag/covering-index/

As far the date_published column goes, it would be alright to add it to the index. It would bring that much more relief to the Query Optimizer. It will perform an index scan to test for <=NOW() but that OK. That's will covering indexes are for.


How to query a postgresql database to determine it's cluster and version (and port)?

That would be

SELECT current_setting('cluster_name'), current_setting('server_version');

You can get all parameters that way. For automated processing, server_version_num is often better.


Large RAM use after import database - ubuntu server

Yes, this is normal. Linux will use any free memory if needed for cache and buffers - it will be reallocated if needed for anything else (i.e. stating a new program, an existing program needing more memory for something, caching more recently accessed information, or the hypervisor requesting some memory back by inflating the local balloon if you VM is setup that way, and so on).

This is all managed automatically, but if you really want to fully clear it yourself you can by running sync; echo 3 > /proc/sys/vm/drop_caches as root. The only reason I can think of for doing that is if you are running performance tests and want a repeatable "from cold" starting point without completely restarting.

If mysql is using memory mapped files for accessing its data files (which is likely as there can be performance benefits to this method) then any block currently loaded from them will show up as cached data, so as well as the 250Mb input file your ~3G of cache will include things already in memory counted in the "cached" figure before you started, the data and index blocks created as a result of the import, and any related blocks that needed to be read as part of the process.


MySQL function to return breadcrumb-like string from hierarchical structure

delimiter //
create function derp(IN myId int) returns varchar(255) begin
    select concat(t1.name, ' > ', t2.name, ' > ' t3.name) from a t1 join a t2 on t1.parent_id=t2.id join a t3 on t2.parent_id = t3.id where t1.id=myId;
end//

Not tested, just off the top of my head.  For reference http://dev.mysql.com/doc/refman/5.5/en/create-procedure.html


Adding a new database to an existing sql cluster

Can I use the existing drives (Data,log) drives, create a new database and map the mdf and ldf to the existing mountpoints?

Yes you can use the existing drives for new database. You would need a new set of shared drives if you are installing new SQL Server clustered instance.

  Do I need to create another MSDTC drive for this application?

Not necessarily, a single MSDTC can work for multiple instances. But if you want to know what all queries need MSDTC I would direct you to Cindy Gross Blog Do I Need MSDTC for SQL Server Cluster. Quoting

  If your application calls DTC directly or if you use a feature in SQL Server that calls DTC, then you need DTC to be available. Some examples of how SQL Server uses DTC: linked servers, OPENROWSET, OPENQUERY, OPENDATASOURCE, remote procedure calls, BEGIN DISTRIBUTED TRANSACTION, updatable subscriptions for transactional replication (immediate and queued updating are now deprecated). You can choose to enlist in a DTC transaction from CLR, SSIS, and DTS. SQL Server does not support using DTC with some features such as database mirroring.


Best procedure to measure query optimization effects?

At the risk of stating the obvious, first decide what you are optimizing for. Almost always it is elapsed time, though you may be trying to reduce CPU use (to reduce license fees) or IO (to avoid a disk upgrade, say). Then find a way to measure that thing, holding other variables constant. Be sure your chosen measure actually measures what you target and not a bunch of other stuff too.

For example, running a query in SSMS and looking at the status bar execution time will show round-trip network time plus plan compilation time plus IO time plus query execution time plus client caching time plus SSMS rendering time. For large result sets and slow networks the overhead and variability may swamp any actual improvement.

Having a completely clean bufferpool and plan cache for each itteration will give great reproducability. It is unlikely to be the actual production scenario, however. Since production is what you're trying to make fast then I'd suggest you test in a production-like environment, with similar memory & disk, plans compiled and the working set in the bufferpool.


Can install a test machine with Oracle 11G R2, not express edition, on a VirtualBox?

Most of your questions are covered in official documentation.
http://docs.oracle.com/cd/E11882_01/nav/portal_11.htm

Read the installation guide (NOT the "quick installation" one).

I don't recommend using "pre-built" virtual machines - try to install OS and db from scratch. The process is the same as installing on "real" hardware. Use CentOS as mentioned in comments.

Not sure for RAC, but Data Guard works perfectly.

Good luck and have fun :)


Oracle, how to manually re-instantiate a PL/SQL package?

The answer is NO.

Now you have two options IMHO.

You can make the package stateless by removing package variables.
You can catch this ORA-ORA-04061 in the application code and re-execute the command.

Useful links:
AskTom
ORA-04061: existing state of  has been invalidated


Stuck at Dynamic SQL execution in a procedure

There were couple of issues that @vercelli pointed out in the comments. The main issue why the code was not working was that two columns COMMISSION_PCT and DEPARTMENT_ID had NULL values for many records in the cursor. In order to handle those NULL values, I had to use NVL function on these two columns within the INSERT clause as listed below. The code looks like below now:

-- Step 1
CREATE OR REPLACE PROCEDURE not_manager_procedure
IS
  v_SQL VARCHAR2(3000);
  v_insert_data VARCHAR2(3000);
  CURSOR emp_cur
  IS
    SELECT *
    FROM employees
    WHERE employee_id NOT IN
      (SELECT manager_id FROM employees WHERE manager_id IS NOT NULL
      );
BEGIN
  v_SQL := 'CREATE TABLE non_managers AS (SELECT * FROM employees    WHERE     1=2)';
  EXECUTE IMMEDIATE v_SQL;
  --DBMS_OUTPUT.PUT_LINE();
  FOR emp_row IN emp_cur
  LOOP
    v_insert_data := 'INSERT INTO non_managers VALUES
      (
      '||emp_row.employee_id||',
      '''||emp_row.first_name||''',
      '''||emp_row.last_name||''',
      '''||emp_row.email||''',
      '''||emp_row.phone_number||''',
      '''||emp_row.hire_date||''',
      '''||emp_row.job_id||''',
      '||emp_row.salary||',
      '||NVL(emp_row.commission_pct, 0)||',
      '||emp_row.manager_id||',
      '||NVL(emp_row.department_id, 0)||'
      )';
    DBMS_OUTPUT.PUT_LINE(v_insert_data);
    EXECUTE IMMEDIATE v_insert_data;
    --DBMS_OUTPUT.PUT_LINE(v_insert_data);
  END LOOP;
  COMMIT;
END not_manager_procedure;
/

-- Step 2
BEGIN
  not_manager_procedure;
END;
/

I'll try to write this code again without using cursor and dynamic SQL as suggested by Vercelli but this requirement was to understand cursor, procedure and dynamic SQL.

Thanks everyone.


SQL Server: migrate one table to discrete DB server

If it is one time, then you should use bcp out and bulk insert .. much better and faster than the linked server approach that you are thinking, since in the linked server approach if you run the query on the source server then you are pushing the data from source server to destination server which is an expensive operation. Alternatively, you can use SSIS as well.

If you need to keep the data in the new server sync with the old one, then your only option is to use transactional replication.

Also, refer to my answer on : Which one is more efficient: select from linked server or insert into linked server?


How to change the default initial and next for new partitions in oracle

Actually the "FOR PARTITION" part of my query was the issue.
This worked :
ALTER TABLE MY_EVENTS MODIFY DEFAULT ATTRIBUTES
STORAGE (INITIAL 65536 NEXT 65536);


RANK() OVER (PARTITION BY xxx, yyy ORDER BY zzz) provides a number ranking for each row where xxx and yyy are identical, and orders that list by zzz

The sub-query contained in your question:

SELECT Sr_No
FROM (
    SELECT *,
    [Rank] = RANK() OVER (PARTITION By Email, Campaign_Name ORDER By Sr_No DESC)
    FROM dbo.LeadsContacts
) T
WHERE T.Rank>1

returns a list of Sr_No values where the corresponding rows have duplicate Email and Campaign_Name column values, excluding the most-recent row.  The most recent row is excluded by the WHERE T.Rank>1 statement, in combination with the RANK() ... ORDER BY clause return rows sorted in Sr_No reverse order (the DESC) keyword.

I used the following code to test your query against a small sample of rows:

USE tempdb;

CREATE TABLE dbo.LeadsContacts
(
    Sr_No INT NOT NULL
    , Email VARCHAR(255) NOT NULL
    , Campaign_Name VARCHAR(255) NOT NULL
);

INSERT INTO dbo.LeadsContacts (Sr_No, Email, Campaign_Name)
VALUES (1, 'test@test.com', 'campaign1')
    , (2, 'test@test.com', 'campaign1')
    , (3, 'test@test.com', 'campaign1')
    , (4, 'test@test.co.uk', 'campaign1')
    , (5, 'test@test.co.uk', 'campaign1')
    , (6, 'test@test.com', 'campaign1')
    , (7, 'test@test.co.uk', 'campaign2');

SELECT *
FROM dbo.LeadsContacts;

DELETE
FROM dbo.LeadsContacts
WHERE Sr_No IN
(
    SELECT Sr_No
    FROM (
        SELECT *,
        [Rank] = RANK() OVER (PARTITION By Email, Campaign_Name ORDER By Sr_No DESC)
        FROM dbo.LeadsContacts
    ) T
    WHERE T.Rank>1
);

SELECT *
FROM dbo.LeadsContacts;

--DROP TABLE dbo.LeadsContacts;

The only rows left in the table are:

    5, 'test@test.co.uk', 'campaign1'
    6, 'test@test.com', 'campaign1'
    7, 'test@test.co.uk', 'campaign2'


Either use a simple integer and represent BC as negative:

CREATE TABLE information (
  id serial PRIMARY KEY,
  year integer NOT NULL
);

or use a date that's constrained to the 1st of Jan and use 2014-01-01 etc in input/output:

CREATE TABLE information (
    id serial PRIMARY KEY,
    year date NOT NULL,
    CONSTRAINT year_must_be_1st_jan CHECK ( date_trunc('year', year) = year )
);

Which is more appropriate depends mostly on how your app is using the information. I think it's OK to represent a year as a simple integer - just please don't do this for year integer, month integer. That's awful to work with, and is where a constrained date is more appropriate.

I tend to use constrained dates so that when the requirements inevitably change I can adapt more easily.


As that error message say: It can not find IP address to "my_ip:my_port" named machine! ;-)

Check your configuration. Somewhere there is written that "my_ip:my_port"


Create an XML Schema collection in SQL Server 2012?

The scheme could look like this.

<?xml version="1.0" encoding="UTF-8"?>
<xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema">
    <xs:element name="R">
        <xs:complexType>
            <xs:attribute name="C">
                <xs:simpleType>
                    <xs:restriction base="xs:string">
                        <xs:maxLength value="80"/>
                    </xs:restriction>
                </xs:simpleType>
            </xs:attribute>
            <xs:attribute name="O" type="xs:string"/>
            <xs:attribute name="N" type="xs:string"/>
        </xs:complexType>
    </xs:element>
</xs:schema>

It defines a single element R with the attributes C, O and N where C is restricted to 80 characters.

A valid XML document according to that schema would contain only one element R as the root node.

What you have here is a XML fragment and SQL Server can handle those just fine. Actually, the default behaviour for the XML datatype is that it will allow XML fragments.

Create the schema:

create xml schema collection dbo.R as
'<?xml version="1.0" encoding="UTF-8"?>
<xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema">
    <xs:element name="R">
        <xs:complexType>
            <xs:attribute name="C">
                <xs:simpleType>
                    <xs:restriction base="xs:string">
                        <xs:maxLength value="80"/>
                    </xs:restriction>
                </xs:simpleType>
            </xs:attribute>
            <xs:attribute name="O" type="xs:string"/>
            <xs:attribute name="N" type="xs:string"/>
        </xs:complexType>
    </xs:element>
</xs:schema>
';

Test:

declare @xml as xml(dbo.R);

set @xml='
  <R C="firstName" O="" N="Fred" />
  <R C="lastName" O="" N="Bloggs" />
  <R C="emailAddress" O="" N="FredBloggs@gmail.com" />
  <R C="phone" O="" N="" />
  <R C="mobile" O="" N="" />
  <R C="fax" O="" N="" />
  <R C="OrganisationId" O="" N="286" />
';

select @xml;

If you want to change the XML to only validate XML documents you need to specify document in the variable declaration.

declare @xml as xml(document dbo.R);


For what is worth, I ended changing the VM in Azure from type A to type D and then attaching another disk and moving the TEMPDB to that disk. So my final VM now is a D2 Standard with 7 GB RAM and three data disks, one for the MDF files, another one for LDF files and the new TEMPDB disk.

I gave up trying to understand with the A3 some things you mentioned and simply upgrade the virtual machine. I even went from A2 to A3 and even though I did find some improvements, I ended up changing to a D2 VM.

In the document that you stated, Microsoft recommends a D3 for Enterprise Edition or D2 for Web or Standard Edition, and use of Premium Storage, among other things in the check list at the beginning of the document.


Firstly, i suggest you the steps to quickly find a correct normalization of the table, using the example that you brought, showing you the correct normalization.

Define the key

The key is usually defined by its usefulness, or its role in operations like joins (if we need a join lot of times we could choose a key instead of another).

I choose A U B as a key. I usually underline the key, but, because in this board we can't underline text, I will write the key as AB

Define the functional dependencies

You already found the FD, so we can go to the next step.

2NF Normalization

Looking at the FD the correct normalization is:

R1 (the previous big table) (**A**:R3,**B**:R2, C)
  AB -> C

(:R2 indicates a foreign key)

In this table we can find the AB -> C functional dependency.

R2 (**B**,F, G, H)
  B -> F,
  F -> GH

R3 (**A**, D, E, I, J)
  A -> DE,
  D -> IJ

3NF Normalization

R1 (**A**:R3, **B**:R2, C)
  AB -> C

R2 (**B**, F:R4)
  B -> F

R3 (**A**, D:R5, E)
  A -> DE

R4 (**F**, G, H)
  F -> GH

R5 (**D**, I, J)
  D -> IJ


since you are going back to a temporary table and updating rows one by one, you should put an index on the table so the updates go faster. alter table tempnumbers add index(num);


How to access table datatype like an array?

While @coordinates is a variable, it is a table variable, not an array. You access data stored in a table variable the same way you would access data in a conventional table, i.e. using a query.

Now when you want to retrieve data of a table's specific row, you need a way of referencing that row. Often you use some sort of an ID column. In this case you could add such column in your @coordinates declaration:

declare @coordinates table
(
  id int identity (1,1),
  latitude decimal(12,9),
  longitude decimal(12,9)
);

The id column is declared as an IDENTITY column and will be populated automatically as rows are added to @coordinates. So now after running your INSERT statement, the contents will look like this:

id  latitude      longitude
--  ------------  ------------
1   28.666670000  77.216670000
2   19.014410000  72.847940000

And in order to retrieve, say, latitude from row 1, you would do

SELECT latitude FROM @coordinates WHERE id = 1;

If necessary, you can use it as a scalar expression where appropriate, for example:

SELECT
  some_columns,
  (SELECT latitude FROM @coordinates WHERE id = 1) + another column AS some_alias
FROM
  some_table
WHERE
   ...

Granted, this is not as pretty as just @coordinates.latitude[1], but it is a conventional way of working with data in SQL.

Note, though, that if you need to retrieve the contents of this table in a query involving other tables and the row ID is going to depend on some table's column, it will be more natural to join @coordinates to the corresponding table:

SELECT
  ...
  c.latitude + t.some_other_column AS whatever
FROM
  some_table AS t
  INNER JOIN @coordinates AS c ON t.some_column = c.id
WHERE
  ...


I am not able to create this DB2 SP. What is the issue with it? Not getting it

You cannot use a simple SELECT statement in compound SQL. It must be SELECT INTO. Another problem with your code is that all DECLARE statements must come before any other statements.


Guidelines on best indexing strategy for varying searches on 20+ columns

Capture the queries that people actually make.
Analyze data from #1; then devise short, multi-column, indexes (2, maybe 3, columns each)
No.  Especially not for yes/no flags.  It's ok to combine such, as in #2.
See #3.
The order of columns is important in an index.  A 5-column index is (roughly) equivalent to the 1-col index on its first column, but is not useful if you don't query on that column.
Probably.
Forcing into memory is counter-productive because it takes away space for caching other stuff.

More discussion:  http://mysql.rjweb.org/doc.php/eav


Structuring data in MongoDB for a to-do list type application

creating a sub collection should offer better read performance
see http://docs.mongodb.org/manual/core/data-modeling/

I think it would be a good idea to add an id to each subdocument so it is easier to access it (because from your schema you may have identical items)

like:

{
    username: "joe",
    password: "...",
    items: [
        subid:{
            text: "This is a task",
            completed: 1
        },
        ...
    ]
},
...

you can use subid = ObjectId() to autogenerate ids, or create this in your application.

I don't think there is a problem with having thousands of items in a document just remember that the maximum document size is 16 megabytes.


The error “Table or view does not exists” (at your second try) is caused by the SELECT qid,question FROM inserted; - As far as i can see, there’s no inserted table in your question. Is this table/view yours? Or it’s another user’s?

Hope this helps.


Do I need a partition scheme for each partitioned table

A separate partition function/scheme for each table/index provides the most flexibility and granularity but can increase complexity and redundant maintenance when boundaries are added or removed, such as when aligning or partitioning related tables.

I generally recommend a single function for all tables/indexes that are partitioned similarly and boundaries are always added/removed at the same time. You'll need separate partition schemes related to the function only if your file group mappings differ. You'll of course require separate partition functions if you need to maintain boundaries at different times.


If it were me, rather than flagging current, I would just time stamp each entry. Then, you would not need to update anything. You would only need to insert a new row. This way, you can query sets of data to plot against time, as a side benefit.


Is there a strategy to use timestamp as primary key?

Not a good idea.

PostgreSQL provides many functions that pertain to current timestamps,

transaction_timestamp()
statement_timestamp()
clock_timestamp()
timeofday()
now()

The problem is none of these are insertion time. You have multiple backends. Each backend can process one transaction. These can transactions can start at the same time, or they can call these functions at the same time (they'll be on different threads/processes).


Does Index Rebuild Reduces physical read from disk

Immediately after you rebuild an index, its pages are more likely to be cached in memory.

Consider this scenario:

Your server has a wide variety of queries running, and a wide variety of tables are cached in memory
You run a query hitting a specific table - say, COUNT(*) from table - and all of its pages were not cached in memory. Your query does a lot of physical reads to bring that data up into cache.
You rebuild its index.
You run the query again - but this time, its pages are now cached in memory. You see less physical reads on that query.

This is one of the reasons you don't really wanna use physical reads to gauge the success of your performance tuning efforts. Start by using logical reads instead. (Heck, you could have skipped step #3 altogether and you'd still see less physical reads - but that doesn't mean you should run every query twice in order to reduce physical reads.)


How to rebuild SYSAUX corrupted tablespace?

Corruption of the SYSAUX tablespaces is apparently a normal thing. It happens every now and then and can be fixed following these steps:

Find the corrupt block (V$DATABASE_BLOCK_CORRUPTION)

Run the following script to retrieve the blocks that are corrupt:

select * from v$database_block_corruption;

This will output a list of corrupt blocks. In most cases rebuilding the index where the corrupt block is located will fix the corruption.

Rebuild all the indexes (Script)

Run the following script to rebuild all the indexes for the SYSAUX tablespace:

    set lines 90;
    set heading off;
    set feedback off;

    spool runme.sql

    select
    'alter index '||owner||'.'||index_name||' rebuild online tablespace sysaux;'
    from
    dba_indexes
    where tablespace_name = 'SYSAUX';

    spool off

    spool rebuild.lst
    @runme.sql
    spool off

Run DBMS_REPAIR()

Have a look at the official documentation for more details (see references)

Open up a Service Request

If all else fails, you might want to consider opening up a Service Request with Oracle.

Reference:
- sysaux corruption tips (Burleson Consulting)
- Using DBMS_REPAIR to Repair Data Block Corruption (Oracle)
- V$DATABASE_BLOCK_CORRUPTION (Oracle)

  Disclaimer
  In no way affiliated with any of the companies.


I'm going to use MS SQL Server to do this since I have a copy to hand.  I believe most any of the majors would do it similarly.

First a sample table, with data.  I use a table variable but it's the same for any flavour of table.

declare @t table (FirstName char(10), LastName char(10));

insert @t(FirstName,LastName)
values ('John','Smith'),('John','Doe'),('Jane','Doe');

You can get all pairs by doing a self-join:

select
    a.FirstName, a.LastName, b.FirstName, b.LastName
from @t as a
cross apply @t as b;

Using CROSS APPLY avoids having to jump through hoops to find a join condition for an ON clause.

Next you need something to count.  This is where the CASE statement comes in.  The case returns an integer value per pair of first names, which is what gets counted. (If I'm reading your question correctly you want where the LastNames match so that's the comparison I have.  Hopefully it's obvious how to modify this if I'm wrong.)

select
    ...
    case
        when a.LastName = b.LastName then 1
        else 0
    end
...etc.

Add in a SUM() and GROUP BY and you get your answer:

select
    a.FirstName,
    b.FirstName,
    sum(
    case
        when a.LastName = b.LastName then 1
        else 0
    end
    ) as CommonLastNames
from @t as a
cross apply @t as b
group by a.FirstName, b.FirstName;


SSAS cube process time increased after making fact and dimension small

Processing a cube largely consists of 3 steps,

Getting the data
building indexes
calculating aggregations

Step 2 and 3 are the least expensive (during processing) in my opinion so let's start with that.

Building indexes does little more than calculating bitmap indexes for your attribute relationships. So depending on how many of those you have designed this could take longer or not, but usually shouldn't take half an hour. This does little more than saying "hey if this item group is filtered on, I already know what Items are in it, so I can add those up instead of doing a NONEMTPTYCROSSJOIN"

Calculating aggregations is the process in which the subtotals are calculated for every level where you defined them. If you haven't used any usage based optimization or attribute hierarchies and haven't defined any aggregations yourself those are only calculated on the leaf level of your dimensions hierarchies (aka the lowest level of every attribute). This is basically "hey if I need the sales for this item group I have precalculated it and don't have to add up these items"

A large part of processing time is used while fetching data. If you would trace your queries while processing you would notice that for every single dimension attribute a SELECT DISTINCT attributename FROM dimension_table is executed. If that dimension table is a view, and the query from the view is slower after adding a where, that dimension processing might become slower by time_the_view_is_slower * number_of_attributes.
How many columns are in the select list of your view is largely irrelevant, the number of attributes in your dimension is.

If you have a distinct count measure anywhere your extra where could have inserted a sort operation too because of a new execution plan.

So in your case my guess would be that your dimension views have gotten slower due to the fact that the valid date isn't indexed, or the queries aren't selective enough and the added processing time of the source queries exaggerates the reduced time of building indexes and aggregations.

You most likely have improved your cube browsing and MDX performance in the process by reducing the number of cells calculated with less data in the cube, but the processing time may just have grown by creating slower source queries, and that gets multiplied by the number of attributes.

Then again, I'm not sure what the problem is with larger processing times if you leave everything default. The performance of the SSAS solution should be more important than the processing time. If you run in to issues due to the processing time that might be another problem that needs fixing in another way.

So I guess, in the end, If you're worried about processing time, either load less data in your source database (which, depending on your setup, could reduce ETL loading time) or tune all those distinct queries by indexing your view accordingly.

If you want to know what exactly is slowing down your processing you could trace all the queries before and after the change and compare the execution times to see what queries are killing you.


SQL Server 2008R2 Migration to new Platform - What Steps Required?

Since you're staying with 2008R2 (both source and destination) there is nothing stopping you from just backing up and restoring all of your databases including the system databases. As Shanky pointed out, this would save a great deal of time:

1) Backup and Restore Databases - Is this the best option?

It's an option. If you need the downtime to be less, you can use mirroring or log shipping (or do it by hand) to keep the databases in sync and then migrate during a fast downtime or cutover.

Please note that system databases cannot be log shipped or mirrored and would need to either be frozen or copied right before the cutover.

2) Migrate Logins - use Microsoft KB? kb/918992

If you restore the master database to the new instance, no migration should be needed. All server level logins are stored in the master database.

3) Migrate Credentials/Certificates - what is required for this?

If this is inside of SQL Server, these would be held in their respective databases (and possibly master as well). The one difference would be the service master key (SMK) that would change. You make want to back that up and restore it on the new server if you're relying on automatic key decryption.

4) Migrate SQL Server Agent Jobs - (Object Explorer Details > Select All Jobs > Script Job; Is this process the best option?)

If you restore the msdb system database, all agent jobs will be held in it. There would be no need to script->restore.

5) Migrate SSIS Packages - (How to do this?)

If the SSIS packages are on the filesystem (not default) it would be trivial to create the same location on the new server and copy. By default the SSIS packages are held in the msdb system database and restoring it would get you to the same place, just like #4 and previous.

6) Migrate Database Mail Accounts/Profiles - (Create a script or recreate in SSMS?)

This is also stored in msdb. See #4, #5.

7) Recreate assemblies

These live in their respective databases. If backup and restore is used, this should not be a problem. Any assemblies outside of SQL Server would need copied to the new server.

8) Recreate Linked Servers

These also live in the master database, see #2.


How to understand I/O bottleneck for SQL Server

If you're using good ol' fashioned spinning rusty frisbees, also known as magnetic hard drives, this isn't an unusual number to see when you're doing random reads and writes. You can outpace those drives with a simple USB 3 flash drive.

To find out for sure, run the portable edition of CrystalDiskMark on your server during a maintenance window. Test the C drive, test where data is stored, where logs are stored, etc - and then compare it to your laptop's SSD. You might be surprised at how much slower random reads & writes are on this array of spinning rusty frisbees.


SQL Server 2008 R2 SQL Server Profiler and "exec sp_reset_connection"

You sure it is 8 seconds?

Starting with SQL Server 2005 (as stated in BOL) when a trace is saved to either a file or a database table, the duration column value is in microseconds

Update
Came across this post on SO: sp_reset_connection taking a long time to run


Short answer - unless it's a huge SQL database and you're doing clever things with it then just ignore the filegroup backup, and stick with the database backup.

Long answer - With SQL server you have databases and filegroups, a database contains all of the data within that database and is nice and straight forward. A filegroup on the other hand contains either all or some of the data within the database. Filegroups hold the physical files (eg the mdf files) which make up the database, and in most cases there will only be one of them, normally called Primary, which will contain the one mdf used.

Now in a large installation you might find that your database mdf files are too large to fit onto one disk, so you might want to split the database across multiple mdf files, and at this point filegroups come into play. If you split all the tables which are regularly used and updated into one mdf on a fast HDD and put it in the Primary filegroup, and then put the less used tables used more for reference onto a slower HDD in a secondary filegroup, you could make best use of the system, however you might not want to bother backing up the secondary mdf, since the data within that file very rarely gets changed. You can therefore choose to do a filegroup backup instead of a complete database backup, and only backup the Primary filegroup, therefore reducing the amount of storage space required for your backup.


Is it possible to estimate the restore progress in SQL Server 2014 from pages read/written?

Look at percent_complete and estimated_completion_time in sys.dm_exec_requests. If it is in fact populated for restoring logs (I have only tested full database restores), it is going to be based on the same math you want to try and calculate manually.

SELECT percent_complete, estimated_completion_time
  FROM sys.dm_exec_requests
  WHERE session_id = <SPID running the backup>;

Obviously neither approach can predict pending concurrency / I/O interruptions, so it is nowhere near any sort of guarantee - just a ballpark guess. It also won't be possible to predict how much I/O will be required for any undo/redo operations, since these are not directly related to the size of the log.


SQL Server 2012 32 bit - will enabling locked pages allow it to use more memory?

Can I have this 32 bit installation use more memory using locked pages?

In the past, my answer would have been (a heavily qualified) "yes".

Using AWE (address windowing extensions) would enable up to 64GB memory. Unfortunately (from your point of view, perhaps) 32-bit AWE was removed from the database engine starting with SQL Server 2012.

In previous versions, 32-bit SQL Server running on a 64-bit version of Windows (WOW mode) was a supported configuration but one that I never encountered personally. Anecdotally, such configurations were often problematic; if you enjoy tracking down weird problems, 32-bit AWE SQL Server on 64-bit Windows is for you.

My very strong recommendation would be to use 64-bit SQL Server 2012 instead.


You could create a query that first lists or groups elements as in your first example. Then select on that (as a subquery) grouping by basket and crating a string per basket through GROUP_CONCAT(). Then you can use those results (perhaps as another subquery) grouping by the group_concat string and having count(*) > 1 to find baskets with equal contents.


postgres backup for specific table with blob oid

The oid column are avaliable in all tables, and I believe thats not a issue.

To create a logical dump of this table, you just need add a option -b (or --blobs in the pg_dump arguments, eg:

pg_dump \
  -U user\
  -t table_name \
  --blobs \
  -f dump_file_name \
  database_name

Another options will be useful is the -F c (or --format=custom) to change the default format from plain to custom.

Please take a look at the documentation for more details:
https://www.postgresql.org/docs/current/static/app-pgdump.html


MySQL Binary Installation (mysql_install_db)

I think you have a mistake, you can't execute:

[mysql@hostname ~]$ . mysql_install_db --defaults-file=my.cnf

Because the correct line is:

[mysql@hostname ~]$ ./mysql_install_db --defaults-file=my.cnf

You don't have in PATH environtment variable the current directory ".".


A MDF is a primary database file, ndf is a non-primary database file, ldf is a log database file. Together, these objects create a database in SQL Server. These files can be attached or detached from the SQL Server Instance.

Hierarchy is: SQL Server Instance -> SQL Server Databases -> SQL Server Schema -> SQL Server Tables.

The most common way to attach and detach a database is to use SSMS. (SQL Server Management Studios) SSMS has recently been placed as a standalone install, you could download SSMS on a Windows machine and then follow this KDB to attach the database to your Instance. I would recommend using caution when attaching unknown databases to your instance. You will also need to attach the log file and any other files that were included.

Once it has been attached, you may be able to view the objects inside it. This will depend on whether the database is encrypted with Transparent Data Encryption, what user permissions are set to, and other factors.

Objects include tables (this has data inside it which you are probably looking for), stored procedures (programmed scripts that can be executed and run), views (queries that look at tables to retrieve a set of data in a specific fashion) and may other things.


What version of SQL Server are you using?  From SQL Server 2008 onwards, Backup Compression can offer superb rates of compression.  I've seen backups pack down to 10% of their original size, so your 100GB+ could be more like 10GB.  Of course this really varies depending on the type of data you are storing; XML doesn't pack down too well for example.

You also have options like breaking the backup into multiple files so you could copy multiple files in parallel rather than one big backup file.  This won't help much if your "pipe" to the cloud is narrow though.

Have a look at your compressed backup size, then work out how many Megabytes per second you can transfer through your existing pipe ( ftp, WAN, etc ? ) eg by transferring a sample 1GB file to get a rough idea.

A final option, and something I'm looking at right now is SQL Server 2014 Clustered Columnstore with full Archival Compression ( DATA_COMPRESSION = COLUMNSTORE_ARCHIVE ).  Intended for 'cold', rarely accessed data, again this really varies but I've seen up to 30x compression.  YMMV.  I'm therefore considering an option of 1) drop all indexes, 2) add clustered columnstore with archive compression, 3) backup and move to cloud 4) recreate earlier indexes or leave as it.   I'm currently investigating this and the relationship between clustered columnstore compression and backup size.

Good luck!


The activity of altering big tables are done in phases:

Create a new table with required fields and indexes say in test DB (just structure)
Dump the data from the existing table and load the same to the newly created table in test DB
Now announce your downtime :)
Swap the tables by renaming - RENAME table ur_db.table_name to test.temp, test.table_name to ur_db.table_name, test.temp to test.table_name; This is an atomic operation taking fraction of a second.
Load the extra records to the newly created table ( the records that came after dumping and then loading). This step can be done before Step: 3 also once to reduce your downtime.
And your system is back

Few notes:

You need not hit information schema directly like this, try using SHOW TABLE STATUS from db like 'table_name'
The speed of alter table is more or less linked with the I/O speed. Last time when we ran a direct alter table(without the above steps), we had 40GB+ table size it took around 4hours. If your 20GB data is taking years, you are working on some outdated machine.
Also drop of the unwanted indexes like index_bslt_ondate, index_base_schedule_line_items_loan_base_schedule as some other indexes has the left most column as the indexed column

Please let me know if you need any clarification on any of these steps.

Edit : A simple python script to automate the process https://github.com/georgecj11/hotswap_mysql_table.git


You can try by capturing the traffic with tshark and some filters :
https://www.wireshark.org/docs/dfref/p/pgsql.html

It will not be very proper but should work.


How to list Extended Properties for all databases

You could run the command in a cursor over the databases:

DECLARE @DbName nvarchar(500), @SQL nvarchar(1000);
DECLARE C CURSOR LOCAL FAST_FORWARD FOR
SELECT QUOTENAME(name) from sys.databases order by database_id;
OPEN C;
FETCH NEXT FROM C INTO @DbName;

WHILE @@FETCH_STATUS = 0
BEGIN
    SET @SQL =
    N'
    USE '+@DbName+'
    SELECT '''+@DbName+''' as databasename ,name, value
    FROM fn_listextendedproperty(default, default, default, default, default, default, default)'
    EXEC SP_EXECUTESQL @SQL;

    FETCH NEXT FROM C INTO @DbName;
END
CLOSE C;
DEALLOCATE C;


While you've done a pretty thorough job of following the textbook requirements, there are some practical considerations that you might or might not want to include, depending on the expectations of the assignment.  Some of these have to do with bad requirements.  I don't know if you would get extra marks or reduced marks for pointing these out.

Food quantity as an integer is probably not precise enough
Food quantities should have a unit of measure. Some foods will be dry, some will be liquid. Iguanas probably need a few grams of "Iguana Chow" whereas elephants will need multiple kilos of whatever they're eating.
Amounts of money should be stored as MONEY or another non-integer data type.
If your zoos are all over the place you might need to consider storing a currency with the monetary amount
Storing the age of a visitor is a mistake.  Visitors age between visits.  You're better off storing the date of birth of the visitor and calculating their age at each visit, since you know the date of the visits
In a real system, some of this information is going to be optional. Not everyone is going to tell you their date of birth.  Similarly, you may not know when your giant tortoise was born.
People's names, if they matter, are usually stored in more than one field (e.g. given name and family name)
It's highly unlikely that every animal will have a personal record of their dietary requirements. Some animals might, but for a lot of animals it might make more sense to store the requirements for all animals of a given species.
The fact that animal species is not accounted for in the model is kind of questionable, since you would think that most zoos would care about something like that


MySQL-test suite not included with windows mysql installer?

You must one of the following situations:

Using an old version of MySQL/Windows (Missing from MSI or Zip File)
Using a current version of MySQL/Windows (Missing from MSI or Zip File)

I have MySQL 5.6.14 for Windows installed on my Desktop at Work
C:\>cd MySQL_5.6.14

C:\MySQL_5.6.14>dir
 Volume in drive C has no label.
 Volume Serial Number is A06D-D0B3

 Directory of C:\MySQL_5.6.14

10/01/2013  02:38 PM    <DIR>          .
10/01/2013  02:38 PM    <DIR>          ..
09/30/2013  03:58 PM    <DIR>          bin
12/14/2013  07:35 PM    <DIR>          data
09/30/2013  03:58 PM    <DIR>          docs
09/30/2013  03:58 PM    <DIR>          include
09/30/2013  03:58 PM    <DIR>          lib
09/30/2013  04:02 PM    <DIR>          mysql-test
09/30/2013  04:02 PM    <DIR>          scripts
09/30/2013  04:02 PM    <DIR>          share
09/30/2013  04:02 PM    <DIR>          sql-bench
09/30/2013  04:02 PM    <DIR>          support-files
09/30/2013  04:02 PM            17,987 COPYING
10/08/2013  08:26 AM               237 my.ini
09/30/2013  04:02 PM             1,141 my-default.ini
09/30/2013  04:02 PM             2,496 README
               4 File(s)         21,861 bytes
              12 Dir(s)  1,871,352,606,720 bytes free

C:\MySQL_5.6.14>

I went into the folder mysql-test
C:\MySQL_5.6.14>cd mysql-test

C:\MySQL_5.6.14\mysql-test>dir
 Volume in drive C has no label.
 Volume Serial Number is A06D-D0B3

 Directory of C:\MySQL_5.6.14\mysql-test

09/30/2013  04:02 PM    <DIR>          .
09/30/2013  04:02 PM    <DIR>          ..
09/30/2013  03:58 PM    <DIR>          collections
09/30/2013  03:58 PM    <DIR>          extra
09/30/2013  03:58 PM    <DIR>          include
09/30/2013  03:58 PM    <DIR>          lib
09/30/2013  03:59 PM    <DIR>          r
09/30/2013  03:59 PM    <DIR>          std_data
09/30/2013  04:01 PM    <DIR>          suite
09/30/2013  04:02 PM    <DIR>          t
09/30/2013  03:58 PM            36,862 mysql-stress-test.pl
09/30/2013  03:58 PM           190,060 mysql-test-run.pl
09/30/2013  03:58 PM             1,447 purify.supp
09/30/2013  03:58 PM             2,860 README
09/30/2013  03:58 PM               655 README.gcov
09/30/2013  03:58 PM             4,600 README.stress
09/30/2013  03:58 PM            21,064 valgrind.supp
               7 File(s)        257,548 bytes
              10 Dir(s)  1,871,352,606,720 bytes free

C:\MySQL_5.6.14\mysql-test>

It's there for me. I installed this a couple of month ago using the no-install zip file.
SUGGESTION
Download the latest no-install zipfile for MySQL 5.6.15 and look for the mysql-test folder
UPDATE 2013-12-16 13:00 EST
I just downloaded the no-install zip file. I looked in it and it is in there. Please download it and extract. Of course, make sure you have Perl installed on the Windows machine.


How to show Large Objects (LOB) contents?

I ran

SELECT expiration_policy, lo_get(authentication), services_granted_access_to
FROM ticketgrantingticket
LIMIT 2;

after reading https://www.postgresql.org/docs/current/static/lo-funcs.html suggested by a now defunct comment. That worked.


Here are examples of both a STATIC and DYNAMIC PIVOT

First, let's create a test table

--Create demo temp table
IF OBJECT_ID('tempdb..#TestTable') IS NOT NULL
    DROP TABLE #TestTable
Create Table #TestTable (
    PayDay Date
    ,Category varchar(20)
    ,Average DECIMAL(11, 2)
    );

--insert some test data
insert into #TestTable values ('2016-12-01','Entertainment',32.32);
insert into #TestTable values ('2016-12-01','Extra Expenses',39.18);
insert into #TestTable values ('2016-12-01','Shopping',591.13);
insert into #TestTable values ('2016-12-15','Entertainment',34.34);
insert into #TestTable values ('2016-12-15','Extra Expenses',42.73);
insert into #TestTable values ('2016-12-15','Shopping',577.69);
insert into #TestTable values ('2016-12-29','Entertainment',34.34);
insert into #TestTable values ('2016-12-29','Extra Expenses',53.22);
insert into #TestTable values ('2016-12-29','Shopping',580.08);

Example of STATIC PIVOT where the Category column has a fixed set of known values.

--Example of STATIC pivot if the Categories are fixed
;
WITH cte_TestTable
AS (
    SELECT PayDay
        ,Category
        ,Average
    FROM #TestTable
    )
SELECT *
FROM cte_TestTable
PIVOT(SUM(Average) FOR Category IN (
            [Entertainment]
            ,[Extra Expenses]
            ,[Shopping]
            )) AS pvt
ORDER BY Payday;

Now, let's add another row to the test table with a new category

--Insert a new category for use in a DYNAMIC PIVOT
insert into #TestTable values ('2016-12-29','Bar Hopping',1000.08);

And now an example of a DYNAMIC PIVOT to handle variable categories

--Example of DYNAMIC PIVOT
DECLARE @cols AS NVARCHAR(MAX),
    @query  AS NVARCHAR(MAX);

select @cols = STUFF((SELECT distinct ',' + QUOTENAME(Category)
                    from #TestTable
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)')
        ,1,1,'')

set @query = 'SELECT PayDay, ' + @cols + ' from
             (
                select *
                from #TestTAble
            ) x
            pivot
            (
                SUM(Average)
                for Category in (' + @cols + ')
            ) p '

execute(@query)


How to convert date and/or time from character string as variable field

The below should return 'Not all databases backed up' if any of the databases haven't been backed up, and 'All databases backed up as of DD/MM/YYYY' if they have (this will be the highest backup date found)

;WITH CTE AS
(
SELECT
--sdb.Name AS DatabaseName,
ISNULL(MAX(backup_finish_date),0) AS LastBackUpTime,
[sdb].[name] AS DatabaseName
FROM sys.databases sdb
LEFT OUTER JOIN msdb.dbo.backupset bus ON bus.database_name = sdb.name
WHERE (bus.type = 'D' OR bus.[database_name] IS NULL)
AND sdb.name NOT IN ('master', 'model', 'msdb', 'tempdb','DBA')
AND sdb.state_desc  = 'ONLINE'
GROUP BY sdb.Name
)

SELECT
    CASE
        WHEN MIN([CTE].[LastBackUpTime]) IS NULL THEN 'Not all databases backed up'
        WHEN MIN([CTE].[LastBackUpTime]) <= DATEADD(dd,-30,GETDATE()) THEN 'Not all databases backed up'
        ELSE 'All databases backed up as of ' + CAST(MAX([CTE].[LastBackUpTime]) AS NVARCHAR(100))
    END
FROM CTE

The script looks for the highest backup date for all non-system (and non-DBA) databases, and returns the datetime equivalent of 0 if there are none (this converts to 1900-01-01 00:00:00.000). We can then check whether the minimum date is in our 30 day region or not.

The actual issue you were experiencing was because your CASE statement returned both a DATETIME data type and an VARCHAR data type - because of data type precedence for a CASE statement SQL server will always choose the data type of the highest precedence as the data type of that field - in this case that was a DATETIME.

To prevent this we simply need to CAST the DATETIME field as a VARCHAR, which prevents the implicit conversion from VARCHAR to DATETIME (lower to higher precedence)


Search for nested values in jsonb array with greater operator

This is generally hard to optimize: no direct operator or index support for jsonb for this kind of test.

EXISTS should at least be faster than what you have, while also avoiding duplicate rows (where multiple array elements match) and the additional (redundant) column pil in the result:

SELECT *
FROM   documents d
WHERE  EXISTS (
   SELECT FROM jsonb_array_elements(d.data_block -> 'PAYABLE_INVOICE_LINES') pil
   WHERE (pil->'AMOUNT'->>'value')::decimal > 1000
   );

Related:

Indexed range comparison of array key's value in row inside JSONB
Speed up range test for key values nested in jsonb array of objects

To make this faster by orders of magnitude, extract the maximum value per row and save it redundantly or use an IMMUTABLE function in a very small and fast (but also specialized) expression index:

CREATE OR REPLACE FUNCTION f_doc_max_amout(jsonb)
  RETURNS numeric AS
$func$
   SELECT max((a->'AMOUNT'->>'value')::numeric)
   FROM   jsonb_array_elements($1) a
$func$ LANGUAGE sql IMMUTABLE;

CREATE INDEX documents_max_amount_idx
ON documents (f_doc_max_amout(data_block -> 'PAYABLE_INVOICE_LINES'));

Query (must match index expression):

SELECT *
FROM   documents d
WHERE  f_doc_max_amout(data_block -> 'PAYABLE_INVOICE_LINES') > 1000;

dbfiddle here


How to pass parameters to delete statement?

You can't concatenate variables like that in a SQL statement and there is no need to do it anyway:

CREATE OR REPLACE PROCEDURE procAuth_Title_Delete (auth_ID NUMBER,
                                        title_Nos IN NUMBER)
IS
BEGIN
  DELETE FROM AUTHOR_TITLE
    where author_id = auth_ID  --<< just use the parameter
      and title_number = title_Nos;

  dbms_output.Put_line ( 'all records have been deleted with the values auth_ID: '||auth_ID||' and title_Nos: '||title_Nos||'.' );
END;
/


How fast should I expect to load data on a high performance computer?

This is a bit hard to tell, what is high-end hardware?

Using Oracle on EMC storage 60MB/s is certainly possible.
Using that same old Oracle on exadata can be slightly faster, 300MB/s and sometimes higher, again, depending on the configuration. A full rack is faster than a half rack ...


SQL Server 2016 - “Always on” issue on secondary server , redo thread blocked

As per my understanding, this issue could be due to the multi-threading model or parallelism.

Until SQL Server 2016, the transaction log redo was handled by single redo thread for each database, this model is called as serial redo.

Starting from SQL Server 2016, the default redo model is parallel redo. It provides multiple threads for transactions and additional worker threads for dirty page flush IO operations.

While working with the concurrent workload, DIRTY_PAGE_TABLE_LOCK waits are generated frequently by the worker thread, and at the same time, query threads will try to access the tables, this will cause the performance issues on both SELECT query and redo operations.

This issue was fixed on the latest Cummulative Update - https://support.microsoft.com/en-in/help/4135048/cumulative-update-1-for-sql-server-2016-sp2

As a workaround, you can disable the parallel redo model and enable the serial redo by enabling the trace flag 3459.

I hope this helps! Thank you.


Find when Oracle 10g table was last populated

Have a look at the TIMESTAMP column of the ALL_TAB_MODIFICATIONS. http://docs.oracle.com/cd/B19306_01/server.102/b14237/statviews_2097.htm

Also, keep in mind this part:

  Note: These views are populated only for tables with the MONITORING
  attribute. They are intended for statistics collection over a long
  period of time. For performance reasons, the Oracle Database does not
  populate these views immediately when the actual modifications occur.
  Run the FLUSH_DATABASE_MONITORING_INFO procedure in the DIMS_STATS
  PL/SQL package to populate these views with the latest information.
  The ANALYZE_ANY system privilege is required to run this procedure.


MySQL - Move data between partitions aka re-partition

You need to split each partition into two partitions?  Something like this (please verify the syntax):

ALTER TABLE table1  REORGANIZE PARTITION p2015half1 INTO
    partition `p0` values less than ('2015-10-01'),
    partition `p0` values less than ('2016-01-01') ;

That is, take any number of partitions (1 in this case), copy the data into any number of new partitions (2 in this case).

But I need to ask "why"?  Would you show us SHOW CREATE TABLE and explain why you chose to use PARTITIONing.  I ask this question because everyone seems to have the impression that partitioning is beneficial.  Often it provides no benefit, especially not in performance.

The next question has to do with "billions of rows".  To do anything with that much data takes a long time -- simply because of I/O speed.  What kinds of queries are you doing on the table?  Do you have summary tables?  Do you purge "old" data?


What is the best SQL server backup strategy for large DB with few tables and lots of immutable data?

I think you should look into log shipping once you get it setup your 10 minutes logs can be shipped to the server, so only changes are updated.

Assuming the log shipping destination is also where you want your backups kept, you can do backups there.  Other then the initial backup, you can run for years and only ship the logs.

You could take full backups at both your primary and log shipping destination. That way you would have backups in both locations, and your only data moving daily is the logs being shipped.

Alternately you can use AlwaysOn instead of log shipping for the same effect.

Licensing: I am not an expert on licensing, but my understanding is that if the Log shipping (or AlwaysOn) destination instance is for DR only, there is no additional cost.


You can use pg_basebackup to take a copy of the PostgreSQL database over the replication protocol. It copies the whole DB though, it isn't incremental. You'll want to use the --xlog-method=stream argument unless you have a shared WAL archive set up.

Streaming replication isn't really well suited to the purpose you describe. You can set it to have replication lag, but it's more minutes or hours rather than days, as the amount of WAL you have to retain on the master or in an archive to get days of replication lag is huge. It's also fixed, not schedulable.

You could achieve what you describe with lots of WAL archiving plus a custom-created recovery.conf for each backup, but it'll be fiddly at best.

The thing you need to realise is that by (say) Friday, the master no longer has any record of the state it was in on Monday. It can't update last Friday's snapshot to this Friday because it no longer knows what state it was in on intermediate days like last Monday ... or last Friday. It doesn't know what state the old replica is in, nor how to get it from there to where it is now.

You can tell it to remember that much state by archiving WAL segments, but you need an immense amount of disk space for that and it's very inefficient to catch the backup up from WAL if your DB is busy.

I'd recommend just using pg_basebackup and/or pg_dump on a schedule.


how to shrink(and also reduce size on disk) a very big log file in SQL server 2016

I completely agree with Biju, I had similar case when log size was not reducing at all however this was in Sybase, nevertheless concept is same, you may refer the case here:

Database Backup size not reducing

You can also use sp_WhoIsActive to check what is currently running at your database and what is the underlying command, who is running it, from how long this is running and many other details; this would help you in finding whether you should kill the specified spid or should consult with user before taking any action. I hope this should help.


Importing data from file with ID dependencies

The only thing I can think of is a combination of a staging table and some shell scripting.

First step: import the raw file into a staging table, e.g. history_data_import which only contains time and value.

Second step: extract the corresponding values for the pair table from the filename and create a SQL statement like the following:

with pair_id as (
  select id
  from pair
  where exchange = 'binance' --<< this is "generated"
    and base_coin = 'btc' --<< this is "generated"
    and quoted_coin = 'usd' --<< this is "generated"
)
insert into history_data (pair, time, value)
select pi.id, imp.time, imp.value
from history_data_import imp
  cross join pair_id pi;

This assumes that the combination of (exchange, base_coin, quoted_coin) is unique in the pair table so that only a single row is returned when looking up the id.

How exactly you extract the parts of the filename is a different topic and depends on the operating system you are using. On Linux this is probably a piece of cake for awk gurus. But this would better be asked on e.g. https://superuser.com/


Query performance drastically depends on the database in use

At a high level, database context is one of the keys that make a cached plan unique (there are other things, like certain session settings, the query text obviously, etc).

Therefore, it is quite possible that the first time this query was run in one database, it compiled (and is now stuck with) a very different plan than when it was compiled for some other database.

There are many reasons why a plan could be different, depending what happened between the first compilation and any subsequent compilations, but now that you have two plans, it is irrelevant that you are issuing the USE commands in the same window. You're still going to use the plan that was compiled for that database. Quick repro:

USE AdventureWorks;
GO
SELECT TOP (1) Monkey = BusinessEntityID
  FROM AdventureWorks2012.Person.Person;
GO
USE OtherDB;
GO
SELECT TOP (1) Monkey = BusinessEntityID
  FROM AdventureWorks2012.Person.Person;
GO
SELECT * FROM sys.dm_exec_cached_plans AS p
CROSS APPLY sys.dm_exec_sql_text(p.plan_handle) AS t
WHERE t.[text] LIKE '%Monkey%';

You get three rows - ignore the first, because that is just the query I ran to verify (which is cached before execution, so adds itself to the resultset), and keep in mind if you run a zoo database or work at SurveyMonkey, you may have to filter further:

Clearly you can see we got a plan for AdventureWorks and a plan for OtherDB, even though this is the same instance of SQL Server. Again, I can't go back in history and tell you why or when you got two different plans, but it's obvious to me that this is a possible explanation. Easy for you to verify, of course.

So, one more time, please try issuing the query with OPTION (RECOMPILE) when you USE <slowdatabase>;, or otherwise evicting those specific plans from the cache and trying again:

DBCC FREEPROCCACHE(0x...); -- where 0x... is the plan_handle


Indexing a SHA hash in place of a VARCHAR

This question has the [Performance] tag so I suspect that you may be thinking of a hash index. In SQL Server, the maximum key length for nonclustered indexes is 1700 bytes. It is not possible to create a nonclustered index with a long string column as a key column. For example, for the following table:

DROP TABLE IF EXISTS #HASH_INDEX_DEMO;

CREATE TABLE #HASH_INDEX_DEMO (
    ID BIGINT NOT NULL,
    BIG_COLUMN_FOR_U VARCHAR(8000) NOT NULL,
    SMALL_COLUMN VARCHAR(10) NOT NULL
);

INSERT INTO #HASH_INDEX_DEMO WITH (TABLOCK)
SELECT RN, REPLICATE(CHAR(65 + RN % 26), (RN % 43) * (RN % 119)), 'SMALL'
FROM
(
    SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) RN
    FROM master..spt_values t1
    CROSS JOIN master..spt_values t2
) q;

Attempting to create this index:

CREATE INDEX I ON #HASH_INDEX_DEMO (BIG_COLUMN_FOR_U);

Fails with this error:

  Msg 1946, Level 16, State 3, Line 19 Operation failed. The index entry
  of length 1701 bytes for the index 'I' exceeds the maximum length of
  1700 bytes for nonclustered indexes.

If you need to do an equality search on that column then the query optimizer must do a table scan. For example, the following query takes about 0.6 seconds on my machine:

SELECT ID, SMALL_COLUMN
FROM #HASH_INDEX_DEMO
WHERE BIG_COLUMN_FOR_U = 'A'
OPTION (MAXDOP 1);

One alternative is to create a hash index on the column and to perform the equality search on both the hash index and the column itself. CHECKSUM() is probably the best choice because you don't need any cryptographic security for this and a small number of collisions is acceptable. You primary want something small and fast. The code below adds a computed column and creates an index on that column:

ALTER TABLE #HASH_INDEX_DEMO ADD BIG_COLUMN_FOR_U_CHECKSUM AS CHECKSUM(BIG_COLUMN_FOR_U);

CREATE INDEX I ON #HASH_INDEX_DEMO (BIG_COLUMN_FOR_U_CHECKSUM);

The query below returns the same results as the initial query but SQL Server is able to use the index. It finishes in 0.01 seconds on my machine.

SELECT ID, SMALL_COLUMN
FROM #HASH_INDEX_DEMO
WHERE BIG_COLUMN_FOR_U_CHECKSUM = CHECKSUM('A') AND BIG_COLUMN_FOR_U = 'A'
OPTION (MAXDOP 1);

Hash indexes are a good choice when the key length is too long to allow for a nonclustered index or disk space is at an absolute premium. In your question you estimate that the length of the column will be about 10 to 30 characters to the added complexity probably isn't worth it for your scenario.


How to migrate large blob table from mysql to postgresql?

I think that simplest way is to use that --hex-blob switch on mysqldump and restore by psql, with decode(string text, type text). However it's not that simple, because you need to change a little produced dump (sed, awk), adding that decode function. For example:

mysqldump -u root -p --skip-quote-names --hex-blob --skip-triggers \
    --compact --no-create-info mysql samples > prepg.dump

sed "s/0x\([0-9,A-F]*\))/decode('\1','hex'))/g" prepg.dump > pg.dump

psql session:

CREATE TABLE samples
(
    file_id integer PRIMARY KEY,
    file bytea
);

\i 'path/to/pg.dump'


MySQL: unique combination of two columns

You can use a non-standard MySQL construction and delete from a self-join:

delete f1.*
from friends f1
join friends f2
    on f1.userid = f2.friendid
   and f1.friendid = f2.userid
   and f1.userid > f2.userid;

when you have cleaned up the table, next step is to prevent this from happening again. The obvious choice would be a CHECK constraint guaranteeing that userid < friendid:

alter table friends add constraint ... check(userid < friendid)

and a unique constraint on these two columns:

alter table friends add constraint ... unique (userid, friendid);

Unfortunately CHECK constraint is not (AFAIK) supported by MySQL. You can however mimic this with generated columns:

alter table friends
    add leastid int as (least(userid, friendid))
    persistent after friendid;
alter table friends
    add greatestid int as (greatest(userid, friendid))
    persistent after friendid;

Now we can add a unique constraint on the generated columns:

alter table friends add constraint ak1_friends
    unique (leastid, greatestid);

insert into friends (userid, friendid) values (1,2);
ERROR 1062 (23000): Duplicate entry '1-2' for key 'ak1_friends'
insert into friends (userid, friendid) values (2,1);
ERROR 1062 (23000): Duplicate entry '1-2' for key 'ak1_friends'

To find common friends among for example 3 and 5 you can use a query like:

select f1.userid, f1.friendid, f2.userid, f2.friendid
from friends f1
join friends f2
    on f1.userid in (f2.userid, f2.friendid)
    or f1.friendid in (f2.userid, f2.friendid)
where 5 in (f1.userid, f1.friendid)
  and 3 in (f2.userid, f2.friendid);


how to Export a document contain tables name, columns names and column description from SQL Server 2008?

I've created an open source project to create MS SQL Server database documentation.

http://jeremykdev.github.io/SqlServerDatabaseDocumentationGenerator


Insert multiple values into stored procedure

You need to update your first stored procedure so it returns data using an OUT parameter.

You can create a temporary table variable that you will use to temporarily store the output IDs of your first stored procedure and then send them to the second.

It should end up looking something like this:

declare @OutputIdList table(localUpdateId int)
exec spGetObsoleteUpdatesToCleanup @OutputIdList=@OutputIDs OUTPUT
exec spDeleteUpdate select localUpdateId  from @OutputIdList

Microsoft has a blog post that explains how to give OUT parameters to stored procedures.

Here's some copy-pasted data from it.

    CREATE PROCEDURE Myproc
    @parm varchar(10),
    @parm1OUT varchar(30) OUTPUT,
    @parm2OUT varchar(30) OUTPUT
    AS
      SELECT @parm1OUT='parm 1' + @parm
     SELECT @parm2OUT='parm 2' + @parm
GO
DECLARE @SQLString NVARCHAR(500)
DECLARE @ParmDefinition NVARCHAR(500)
DECLARE @parmIN VARCHAR(10)
DECLARE @parmRET1 VARCHAR(30)
DECLARE @parmRET2 VARCHAR(30)
SET @parmIN=' returned'
SET @SQLString=N'EXEC Myproc @parm,
                             @parm1OUT OUTPUT, @parm2OUT OUTPUT'
SET @ParmDefinition=N'@parm varchar(10),
                      @parm1OUT varchar(30) OUTPUT,
                      @parm2OUT varchar(30) OUTPUT'

EXECUTE sp_executesql
    @SQLString,
    @ParmDefinition,
    @parm=@parmIN,
    @parm1OUT=@parmRET1 OUTPUT,@parm2OUT=@parmRET2 OUTPUT

SELECT @parmRET1 AS "parameter 1", @parmRET2 AS "parameter 2"
go
drop procedure Myproc


as described it will not work, but You can use prepared statements for this type of operations:

SET @tb_name = (SELECT grouptable.tablename FROM cmsdb.usertable LEFT JOIN cmsdb.grouptable ON cmsdb.grouptable.ID = cmsdb.usertable.groupID WHERE User = 2);

just keep in mind subquery above, must return single value, if not sure - add LIMIT 1

set @query = CONCAT('SELECT DATE(timestamp) As Date, AVG(Value) AS Value FROM ',  @tb_name , ' WHERE timestamp BETWEEN CURDATE()-INTERVAL 4 WEEK AND CURDATE() GROUP BY DATE(timestamp)');

prepare stmt1 from @query;
execute stmt1;

deallocate stmt1;


Assiging stored procedure results to SSIS variable

You have two choices to make this work. Either you can use a Single Result Set or you can use the OUTPUT parameter. You're currently using neither correctly.

OUTPUT parameter

Your stored procedure is defined as having a parameter of @OrderCount with a direction of OUTPUT

If you wanted to use the stored procedure within a tool, SSMS, .NET, whatever, it'd look something like

DECLARE @orderCount int = 0;
EXECUTE dbo.TestStoredProcSSVariable @orderCount OUTPUT;
SELECT @orderCount As OrderCountVariable;

It is valid to run the above without specifying OUTPUT but look at the value of @orderCount. It changes from 1435 to 0.

The same holds true when you're using the Execute SQL Task within SSIS. You must specify that the parameter is on OUTPUT and also specify it in the Parameter mappings tab.

Also specify the variable you want to map and use the OUTPUT direction there. Here I've mapped the result into an SSIS Variable of type Int32 called orderCount

Single Result Set

You have the first part of this correct - you've specified that the result set is Single Row.

You'll note that I am using EXECUTE dbo.TestStoredProcSSVariable ? as you must specify an input value or the proc call will break (at least as you've defined it). You could have hard coded a value in lieu of the ? like 0

Then, on the Result Set tab, here I'm mapping up the first column (zeroth ordinal) to a Variable called orderCountb

If you run the provided stored procedure, you will not get a value in orderCountb. Why? Because you aren't returning anything from the stored procedure call. I added a final statement inside the stored procedure of

SELECT @OrderCount AS OrderCount;

Do it yourself

You can explore either approach using the following biml. What is biml? The Business Intelligence Markup Language is the operating system for BI. Why you care about it is that it will allow you to transform some XML into an SSIS package. All you need to do is download and installed the free addon BIDS Helper

After installing BIDS Helper,

Right click on the project and select Add new Biml file
replace the contents of the file with the following XML
Fix the values in line 5. Update the Data Source to a real server and Provider to align with your SSIS version. Looking at your screenshot, this will likely be SQLNCLI10.1
Right click on BimlScript.biml and choose Generate SSIS Packages

Bimlscript.biml

<Biml xmlns="http://schemas.varigence.com/biml.xsd">
    <Connections>
        <Connection
            Name="tempdb"
            ConnectionString="Data Source=.\dev2014;Initial Catalog=tempdb;Provider=SQLNCLI11.1;Integrated Security=SSPI;"
            />
    </Connections>
    <Packages>
        <Package
            Name="dba_114775"
            ConstraintMode="Linear"
        >
            <Tasks>
                <ExecuteSQL
                    ConnectionName="tempdb"
                    Name="SQL Make procedure">
                    <DirectInput>
                        <![CDATA[IF EXISTS
(
    SELECT
        *
    FROM
        sys.procedures AS P
        INNER JOIN
            sys.schemas AS S
            ON S.schema_id = P.schema_id
    WHERE
        S.name = 'dbo'
        AND P.name = 'TestStoredProcSSVariable'
)
BEGIN
    DROP PROCEDURE dbo.TestStoredProcSSVariable
END
GO
CREATE PROCEDURE dbo.TestStoredProcSSVariable
(
    @OrderCount int OUTPUT
)
AS
BEGIN
    SET NOCOUNT ON;

    SET @OrderCount = 1135;
    SELECT @OrderCount AS OrderCount;
END

GO

]]>
                    </DirectInput>

                </ExecuteSQL>
                <Container Name="SEQC Result set" ConstraintMode="Linear">
                    <PrecedenceConstraints>
                        <Inputs>
                            <Input OutputPathName="SQL Make procedure.Output"></Input>
                        </Inputs>
                    </PrecedenceConstraints>
                    <Tasks>
                        <ExecuteSQL
                              ConnectionName="tempdb"
                              ResultSet="SingleRow"
                              Name="SQL SingleRow">
                            <DirectInput>EXECUTE dbo.TestStoredProcSSVariable ?;</DirectInput>
                            <Results>
                                <Result VariableName="User.orderCountb" Name="0" />
                            </Results>
                            <Parameters>
                                <Parameter DataType="Int32" VariableName="User.orderCountb" Name="0" />
                            </Parameters>
                        </ExecuteSQL>
                        <ExecuteSQL ConnectionName="tempdb" Name="SQL Placeholder">
                            <DirectInput>SELECT 1;</DirectInput>
                        </ExecuteSQL>
                        <ExecuteSQL ConnectionName="tempdb" Name="Execute SQL Task 2">
                            <DirectInput>SELECT 1;</DirectInput>
                            <PrecedenceConstraints>
                                <Inputs>
                                    <Input
                                        OutputPathName="SQL Placeholder.Output"
                                        EvaluationOperation="ExpressionAndConstraint"
                                        EvaluationValue="Success"
                                        Expression="@[orderCount] &lt; 5" />
                                </Inputs>
                            </PrecedenceConstraints>
                        </ExecuteSQL>
                    </Tasks>
                </Container>
                <Container Name="SEQC Output Parameter" ConstraintMode="Linear">
                    <PrecedenceConstraints>
                        <Inputs>
                            <Input OutputPathName="SQL Make procedure.Output"></Input>
                        </Inputs>
                    </PrecedenceConstraints>
                    <Tasks>
                        <ExecuteSQL
                              ConnectionName="tempdb"
                              Name="SQL Output parameter">
                            <DirectInput>EXECUTE dbo.TestStoredProcSSVariable ? OUTPUT;</DirectInput>
                            <Parameters>
                                <Parameter
                                    DataType="Int32"
                                    VariableName="User.orderCount"
                                    Name="0"
                                    Direction="Output" />
                            </Parameters>
                        </ExecuteSQL>
                        <ExecuteSQL ConnectionName="tempdb" Name="SQL Placeholder">
                            <DirectInput>SELECT 1;</DirectInput>
                        </ExecuteSQL>
                        <ExecuteSQL ConnectionName="tempdb" Name="Execute SQL Task 2">
                            <DirectInput>SELECT 1;</DirectInput>
                            <PrecedenceConstraints>
                                <Inputs>
                                    <Input
                                        OutputPathName="SQL Placeholder.Output"
                                        EvaluationOperation="ExpressionAndConstraint"
                                        EvaluationValue="Success"
                                        Expression="@[orderCount] &lt; 5" />
                                </Inputs>
                            </PrecedenceConstraints>
                        </ExecuteSQL>

                    </Tasks>
                </Container>
                <ExecuteSQL ConnectionName="tempdb" Name="SQL Placeholder">
                    <DirectInput>SELECT 1;</DirectInput>
                    <PrecedenceConstraints>
                        <Inputs>
                            <Input OutputPathName="SEQC Result set.Output" />
                            <Input OutputPathName="SEQC Output Parameter.Output" />
                        </Inputs>
                    </PrecedenceConstraints>
                </ExecuteSQL>
            </Tasks>
            <Variables>
                <Variable DataType="Int32" Name="orderCount">-1</Variable>
                <Variable DataType="Int32" Name="orderCountb">-1</Variable>
            </Variables>
        </Package>
    </Packages>
</Biml>

Enjoy the following SSIS package


Databases entering "In recovery" mode on restore

In such situations I am trying this piece of code:

ALTER DATABASE <DB> SET RESTRICTED_USER WITH ROLLBACK IMMEDIATE


Using MySQL binary logs to keep backups between database dumps?

Keeping compressed archives of binlogs is a very common way of achieving point in time recovery for your database, assuming you have a full backup from which you can roll forward.

Just copying away the binary logs with a remote transfer procedure (e.g. scp) would be fine- they can just be applied as is; however, it may create inconveniences if the binary logs are in use, being rotated/purged, and above all, being written because they are the last ones. While you could work around that- there is an easier method:

mysqlbinlog is an utility included in the mysql client code which not only allows to transform binlogs into sql, it also allows to remotely stream them by using the mysql protocol (so you don't need to setup additional infrastracture and code). In particular the -R option will allow you to connect to a remote server and stream its binlogs locally. Combined with the --raw and  --stop-never options you will have a built-in option to stream in almost real time the binary logs in the original format, without having to handle the different files and losing events, etc. Although you may still want to wrap that so that you can handle connection errors and other problems that could arise on the remote copy.

See the manual for more details on how it is suggested to proceed with this: https://dev.mysql.com/doc/refman/8.0/en/mysqlbinlog-backup.html
And here is a recipe on Percona XtraBackup website on how to use binlogs to apply incremental backups to an existing backup: https://www.percona.com/doc/percona-xtrabackup/8.0/innobackupex/pit_recovery_ibk.html

  How do I only get the binary logs since the exact time of the last mysqldump?

For that, you need to run mysqldump with --master-data or --dump-slave options (as well as single-transaction, or other way of achieving consistency) and gather the binlog or gtid coordinates. That way you can configure where to start gathering the binlogs (or easier, where to start applying those). A trick to make things even easier is to run FLUSH LOGS before a dump so you have less data to move around when doing a point in time recovery.

  If another transaction started before that time and finished after it, will it still be included properly in the log?

It may or may not be in the log, but its coordinate according to mysqldump will not be included. Transactions are only included in the binlog on commit time. Meanwhile they are cached on memory or disk. While it is possible to split logs as you may be thinking, I would suggest it is easier to keep them as similar as possible to the original format of the msater, and use the coordinates to handle consistency of the different backups you take- for simplicity and so you can reuse the same file for the recovery of similar backups.

  Can I use mysqlbinlog to only get the changes since the last mysqldump?

If still you prefer to keep them separate, use the rotation I suggest above, but you may need to increase the (unnecessary) database locking to make the rotation of the logs and the backups consistent. Or you can just use --start-position and --stop-position mysqlbinlog options for a one-time copy.


Unable to create / Move data to New Partitions from the existing partition SQL Server 2014

If your intention is to partition this data by year/month, I think your current partitioning column (which is an integer and not a 'true' date data type and not even in yyyymmdd format even as an integer) is all wrong for the job.  I'm also assuming you don't have another column in the table that IS the MonthlyDate in a true Date data type.

In my opinion (and assuming you have extra disk space), you should:

Create a new partition function that uses a DATE data type.  It is recommended that this should be RANGE RIGHT (see Dan Guzman's link at the bottom of this answer for explanations).  Make sure you create it with ranges well below your minimum date as well as ranges well above your maximum date.  Also, as time goes by, make sure you are splitting empty partitions well ahead of any data already being populated in them.
Create a new partition scheme that references the new partition function.
Create a new 'replacement' table (that references the newly created partition scheme) by scripting a create of the current table, but add a computed/persisted DATE column (ex:PartitionedColumn DATE) that is derived/converted from your integer MonthlyDate column.
Set up an insert statement, inserting records from the existing table into the new table, say a few thousand or a hundred thousand in a batch, within a transaction. When the transaction completes perform a log backup (if you're not in SIMPLE recovery).  That'll keep you transaction log from running out of space. Then repeat the batch... keep doing that until all records are written to the new table with the persisted computed column.
Then change the old table name, change the new table name to that of the old table

Keep in mind that all unique indexes that participate in a partitioning scenario, MUST have the partitioning column defined as part of the base index definition and not simply an included column.

Also, to enable partition switching (often used in a "sliding window"), all indexes on the table must be aligned.  BOL has good information about this.  Look for Special Guidelines for Partitioned Indexes.

Lastly, check out Dan Guzman's excellent post on Sql Server partitioning.


How to list the missing multicolumn statistics?

Other than the DTA, is there any other way to find out the missing multicolumn statistics?

Nope ! query optimizer assumes that columns within the same table are independent. Joe Sack talks about it here

  The query optimizer assumes that columns within the same table are independent. For example, if you have a city and state column, we may intuitively know that these two columns are correlated, but SQL Server does not understand this unless we help it out with an associated multi-column index, or with manually-created multi-column statistics. Without helping the optimizer with correlation, the selectivity of your predicates may be exaggerated.

and @paulwhite talks about it here.

  A certain amount of trial-and-error may be necessary to identify the multi-column statistic.


Multi-master (NoSQL) database solutions with cloud and no-premise option

You may want to consider Aerospike NoSQL database. You can install it on-premise and in the cloud yourselves. It has both AP and CP modes. It has Cross-Datacenter-Replication (XDR) which will replicate data from one cluster to another in asynchronous way. Network delays/disconnection are taken care of. However, XDR is an enterprise-only feature. Its not available in the free community version.


Get the date interval from table rows, based on a start stop column

It's actually very simple. Start from the wanted date and descend the dates until you find the first row. If it's 'start' then you are inside an interval. If it's 'stop', you are not:

select
  ( select startstop
    from mytable
    where mydate = '2017-02-07' and startstop = 'start'
       or mydate < '2017-02-07'
    order by mydate desc
    limit 1
  ) = 'start'
  as result ;

The complicated WHERE is for dealing with the inclusive date ranges. If inclusive-exclusive were used, the condition would be simper.

A similar way - perhaps a bit more clear - would be:

with ct as
  ( select mydate, startstop
    from mytable
    where mydate <= '2017-02-07'
    order by mydate desc
    limit 1
  )
select
       ('start') = (select startstop from ct)
    or ('2017-02-07', 'stop') = (table ct)
  as result ;


How to create an Sql table from SELECT with a multiple row DISTINCT constraint?

distinct is NOT a function. It always operates on all columns of the result.

The difference between select distinct (foo), bar and select distinct foo, bar is the same as the difference between select (foo), bar and select foo, bar. The parenthesis is just "noise".

When you write select (foo,bar) you are actually creating an anonymous record type in Postgres which results in a single column that has two attributes - which is not what you actually want.

As you are using Postgres, you can use the (proprietary) operator DISTINCT ON which - in contrast to the standard DISTINCT - does operate on a sub-set of the columns.

You have to specify an ORDER BY in that case to define which of the rows to take if there is more than one with the same combination of (field1, field2).

CREATE TABLE new_name
AS
SELECT DISTINCT ON (table.field1, table.field2),
       table.field1,
       table.field2,
       .....
FROM ...
WHERE ...
ORDER BY ..

If you want to stick to ANSI SQL you will need a window function for this:

create table new_name
as
select column1, column2, column3, column4, column5, column6
from (
   select column1, column2, column3, column4, column5, column6,
          row_number() over (partition by column1, column2 order by ...) as rn
   from the_table
   where ...
) t
where rn = 1;

For a large table, DISTINCT ON is probably faster than the solution with the window function.


How To Merge Database Projects on Visual Studio 2013

Certainly appears you can do that in Visual Studio 2012.  I just created a Database Project "database1", then added another database project to the same Solution, named "database2".

I then right-clicked the "Database1" project, clicked "Add Database Reference" and selected the "Database2" project.  Then, under "Database Location" I chose "Same Database".


MS SQL Server sys.indexes vs sys.sysindexes system table difference

sys.sysindexes - is old and deprecated.

  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.

you should use sys.indexes.


PostgreSQL multidimensional array - ok to use or misdesign in this case?

If you want to avoid any overhead for a fixed maximum of images, and keep the disk footprint to a minimum, don't use an array, that adds 24 bytes of overhead for the array type, similar to a the overhead of a row.

Add 5 individual columns to the row. Some empty columns with NULL values cost almost nothing. That's even more awkward to search for (you'll have to look into each column), but you say that's irrelevant for the use case.

If the number of images is dynamic I would suggest a normalized design with a 1:n table images, though (which wouldn't be a bad idea anyway).

If you need to search occasionally after all, a bloom index would be an option. The manual:

  This type of index is most useful when a table has many attributes and
  queries test arbitrary combinations of them. A traditional btree index
  is faster than a bloom index, but it can require many btree indexes to
  support all possible queries where one needs only a single bloom
  index. Note however that bloom indexes only support equality queries,

Or a GIN index on an ARRAY expression covering the 5 columns.

Related answers concerning storage:

Calculating and saving space in PostgreSQL
Does not using NULL in PostgreSQL still use a NULL bitmap in the header?
Configuring PostgreSQL for read performance


Why does DataContext.SubmitChanges() Exist?

I'm not 100% sure about LINQ but i would say that the seperation is based on transaction control. You apply any number of updates, inserts or deletes. That are then held as a unit of work, then once you've finished your changes you simply commit them to the database as a single action or roll all of the changes back, maintaining the integrity of your database.


Handling user logins while using a multiple schema design to support multi-tenancy

Your options are:

Use roles, and SET search_path on each role so it looks in the appropriate organization's schema. While in many ways the cleanest approach from a database perspective, this is a pain when giving orgs the right to manage their own users.
Require users to specify their organisation at login time, probably with user@domain style logins, or using mycompany.myhostedapp.com style URLs. Then look up the org by @domain suffix before authing the user against that org. That's probably what I'd do.
Create a view that's a union of all company user tables and look users up in this. Likely to be a performance nightmare, and how will you handle duplicates?
Create a parent table for all user tables, make all the user tables inherit it, and query the parent table. Just a slightly more efficient version of the view approach, not recommended.

I strongly suggest an authentication design that carries the company identity of the user along with their login request.