Before answering when to use it and why, it's first paramount in understanding exactly what GO is, and what it isn't.

The keyword GO is used by SQL Server Management Studio and SQLCMD in order to signify one thing and only one thing:  The end of a batch of statements.  In fact, you can even change what you use to terminate batches to something other than "GO":

That above screenshot is an option within SSMS that is configurable.

But what is a batch??  This BOL reference says it best:

  A batch is a group of one or more Transact-SQL statements sent at the same time from an application to SQL Server for execution.

Simple as that.  It's just a custom way an application (yes... an application) sends statements to SQL Server.  Let's see an application-looking example of this.  I'll use PowerShell to mimic what an application would do to send statements and batches to SQL Server:

$ConnectionString = "data source = SomeSQLInstance; initial catalog = AdventureWorks2012; trusted_connection = true; application name = BatchTesting;"

try {
    $SqlConnection = New-Object System.Data.SqlClient.SqlConnection($ConnectionString)
    $SqlCmd = New-Object System.Data.SqlClient.SqlCommand
    $SqlCmd.Connection = $SqlConnection

    # first batch of statements
    #
    $SqlCmd.CommandText = "
        select * from humanresources.department where departmentid = 1;
        select * from humanresources.department where departmentid = 2;
        select * from humanresources.department where departmentid = 3;
        select * from humanresources.department where departmentid = 4;"

    # execute the first batch
    #
    $SqlConnection.Open()
    $SqlCmd.ExecuteNonQuery()
    $SqlConnection.Close()

    # second batch of statements
    #
    $SqlCmd.CommandText = "
        select * from humanresources.department where departmentid = 5;
        select * from humanresources.department where departmentid = 6;
        select * from humanresources.department where departmentid = 7;
        select * from humanresources.department where departmentid = 8;"

    # execute the second batch
    #
    $SqlConnection.Open()
    $SqlCmd.ExecuteNonQuery()
    $SqlConnection.Close()
}
catch {
    $SqlCmd.Dispose()
    $SqlConnection.Dispose()
    Write-Error $_.Exception
}

The comments give it away, but you can see above that we're programmatically sending two batches to SQL Server.  Let's verify that, though.  My choice here is to use Extended Events:

create event session BatchTesting
on server
add event sqlserver.sql_batch_starting
(
    set
        collect_batch_text = 1
    where
    (
        sqlserver.client_app_name = N'BatchTesting'
    )
),
add event sqlserver.sql_batch_completed
(
    set
        collect_batch_text = 1
    where
    (
        sqlserver.client_app_name = N'BatchTesting'
    )
),
add event sqlserver.sql_statement_starting
(
    set
        collect_statement = 1
    where
    (
        sqlserver.client_app_name = N'BatchTesting'
    )
),
add event sqlserver.sql_statement_completed
(
    set
        collect_statement = 1
    where
    (
        sqlserver.client_app_name = N'BatchTesting'
    )
)
add target package0.event_file
(
    set
        filename = N'<MyXelLocation>\BatchTesting.xel'
);
go

alter event session BatchTesting
on server
state = start;
go

All this XEvents session is doing is capturing the statements and batches that start and complete from an application named "BatchTesting" (if you notice my connection string in my PowerShell code example, it's a quick way to look at a particular originator of events by using the "application name" connection string parameter and filtering off of that).

After I execute the PowerShell code to send those batches and statements, I see the following results:

As you can see from the screenshot, it's clear how the statements are divided up into the two different batches, also evident by the means we used to call the batches.  And if we look in the batch_text of the first occurrence of sql_batch_starting, we can see all of the statements included in that batch:

    select * from humanresources.department where departmentid = 1;
    select * from humanresources.department where departmentid = 2;
    select * from humanresources.department where departmentid = 3;
    select * from humanresources.department where departmentid = 4;

With that explanation of what a batch is, now comes the answer to your question of when to terminate batches.  The rules for batches are found on this BOL reference regarding batches:

  CREATE DEFAULT, CREATE FUNCTION, CREATE PROCEDURE, CREATE RULE, CREATE
  SCHEMA, CREATE TRIGGER, and CREATE VIEW statements cannot be combined
  with other statements in a batch. The CREATE statement must start the
  batch. All other statements that follow in that batch will be
  interpreted as part of the definition of the first CREATE statement.

  A table cannot be changed and then the new columns referenced in the
  same batch.

  If an EXECUTE statement is the first statement in a batch, the EXECUTE
  keyword is not required. The EXECUTE keyword is required if the
  EXECUTE statement is not the first statement in the batch.

Likewise, certain runtime errors (compile errors won't allow the execution of a batch to start) that occur during a batch can cause different behaviors:  to abort the batch totally, or to continue the batch and only abort the offending statement (the above link gives two really good examples:  An arithmetic overflow error, for instance, will stop the execution of the batch, whereas a constraint violation error will only prevent the current statement from completing but the batch will continue executing).

Like many things in our profession, though, personal preference will be a huge driving force behind how you as an individual and writer of T-SQL code terminate batches.  Some people only explicitly define batches when they absolutely have to (see above for those requirements), and others terminate batches programmatically 100% of the time, even when they are only executing a single statement in a Query Window in SSMS.  Most people typically fall somewhere in the middle of those two boundaries.  For what it's worth, statement terminators have a same following with also very few enforced requirements.  A big part of all of this is code style, where it isn't enforced (in SSMS and SQLCMD).


Find the average number of n:m connections of two tables?

I have no idea which RDBMS you are using, but on SQL Server using CTEs (Common Table Expression) it might look like something similar:

with tbl_count (user_id, nboflists) as
(
   select user_id, count(list_id)
   from lists_users
   group by user_id
)
select avg(nboflists)
from tbl_count

There must also be dozen of other (better?) ways  of doing this...


I think you can move your subquery onto the join condition, as you're just looking for customers' transactions before or on 1st Jan 2013.

SELECT  C.Customer_id,
        C.tran_id,
        t.date
    FROM CUSTOMERs C
    INNER JOIN TRANSACTIONS T ON c.Customer_id= t.Customer_id
    AND t.tran_Date <= '2013-01-01'
  WHERE  C.Customer_id NOT IN(1,2,3)


Using output value of column from function for another column in SQL*Loader

In don't think you can use the resullt of a function. The documentation for the loader can be found in the Utilities Manual.
Applying SQL Operators to Fields says

  The execution of SQL strings is not considered to be part of field setting. Rather, when the SQL string is executed it uses the result of any field setting (...)

Field setting means that the field names are assigned to the substrings of the current data of the record of the  file that is loaded. How this works is described in Specifying the Position of a Data Field: If you have a POSITION(x:y) specification for a column named colthen the substring from position  x to position y is the field value of col and it can be references as :col in an sql-string. If there is no POSITION specifiaction found as in the specification of your fields col2, col4,  col5, col7, col12, col13, LAST_UPDATE_T and LAST_UPDATE_USER_I, then  POSITION is defined some way depending on the previous field and the data type of the column. So because the col2-Field (that is referenced by :col2 in an sql-string) has no explicit field specification it is the  field of INTEGER EXTERNAL  type  (of default lenght 1) following col1. So it actually is POSITION(9:9) and therefore the first character of:col6

If you want to set the column values of a table depending on other column values you can use a trigger on the table.


What to do when archive logs are lost/corrupted that are not backed up

As soon as your recovery thread is interrupted, making it impossible to recover from an earlier backup to the current time, the remedy is to take a backup before something more serious happens.

Next investigate how the archives became corrupted and solve that.

Backups are of no importance, important is the ability to recover from a disaster and for that backups or physical standby databases can be very convenient to have.


Why does SqlSentry Plan Explorer not give the duration of my queries?

Martin is correct, in order to see duration and other metrics from an actual plan with Plan Explorer, "Get Actual Plan" must be run from within Plan Explorer. Actual plan XML does not contain this data, regardless of source.

Plan Explorer FREE uses STATISTICS TIME & IO behind the scenes and auto-correlates the data with the plan tree, and Plan Explorer PRO uses a dynamic rowset trace for this purpose. An advantage of the latter method is the ability to provide the full call stack including plans and metrics for embedded dynamic SQL calls. Neither method is affected by statement duration.

This is why we use our own .queryanalysis file format when saving plans from within Plan Explorer, as it contains this extra metadata along with the plan XML.


SSIS - Iterative Data Flow Task with Schema Differences

I was able to solve my problem by using the information on this site:

http://www.sqlservercentral.com/articles/Integration+Services+(SSIS)/70875/

This showed me how to build a comma-delimited string from an Object variable by using a Foreach Loop Container.


For your use case, you actually need two different indexes:

CREATE INDEX ON messages(recipient_id);
CREATE INDEX ON messages(sender_id);

Your multi-column indexed would be used by the recipient_id = ?? part of the query, but it would not be used by the other (it can be used by other databases that know how to do Skip Scanning, such as Oracle, although it might not be extremely efficient).

Depending on how your PRIMARY KEY is for the message(s) table, you might not need one of them (the implicity index associated with the PK will do the job).

You can then either use your query as is, and let PostgreSQL do a BitmapOr, or convert the query to a UNION and avoid the OR. Timings should be about the same in both cases. If you are sure that there are not messages where both the sender_id and the recipient_id are the same (i.e.: nobody sends a message to him/herself, or if s/he does, you don't mind showing it twice), then a UNION ALL (which is slightly quicker) could be used instead with the same result.

OR conditions tend not to be handled too well by most databases. This specific case, which is rather common and rather simple, is handled well by PostgreSQL.

Experimental check

Table definitions (simplified)

CREATE TABLE users
(
    user_id integer PRIMARY KEY,
    user_name text
) ;

CREATE TABLE messages
(
    sender_id    integer NOT NULL REFERENCES users(user_id)
                 ON UPDATE CASCADE ON DELETE RESTRICT,
    recipient_id integer NOT NULL REFERENCES users(user_id)
                 ON UPDATE CASCADE ON DELETE RESTRICT,
    send_date    timestamp NOT NULL DEFAULT now(),
    message_text text,
    PRIMARY KEY(sender_id, recipient_id, send_date)
) ;
CREATE INDEX ON messages (recipient_id) ;
-- Following index not needed, given our primary key
-- CREATE INDEX ON messages (sender_id) ;

We populate the tables with some simulated data:

-- Create 1000 users
INSERT INTO users (user_id, user_name)
SELECT
    user_id, 'user' || user_id AS user_name
FROM
    generate_series(1, 1000) AS x(user_id) ;

-- Create (aprox.) 100000 messages
INSERT INTO messages (sender_id, recipient_id, send_date)
SELECT
    (random()*999+1)::integer AS sender_id,
    (random()*999+1)::integer AS recipient_id,
    send_date
FROM
    generate_series(timestamp '2017-01-01', timestamp '2017-01-31', interval '25 seconds') AS x(send_date);

ANALYZE;

And, at this point, we check which are the execution plans that we get:

-- Checking the query with OR
EXPLAIN ANALYZE
SELECT *
FROM messages
WHERE recipient_id = 123 OR sender_id = 123
ORDER BY send_date ;

gives ...

QUERY PLAN
1   Sort  (cost=420.21..420.71 rows=200 width=48) (actual time=0.430..0.445 rows=192 loops=1)
2     Sort Key: send_date
3     Sort Method: quicksort  Memory: 34kB
4     ->  Bitmap Heap Scan on messages  (cost=10.31..412.56 rows=200 width=48) (actual time=0.092..0.389 rows=192 loops=1)
5           Recheck Cond: ((recipient_id = 123) OR (sender_id = 123))
6           Heap Blocks: exact=157
7           ->  BitmapOr  (cost=10.31..10.31 rows=200 width=0) (actual time=0.061..0.061 rows=0 loops=1)
8                 ->  Bitmap Index Scan on messages_recipient_id_idx  (cost=0.00..5.04 rows=100 width=0) (actual time=0.033..0.033 rows=97 loops=1)
9                       Index Cond: (recipient_id = 123)
10                ->  Bitmap Index Scan on messages_pkey  (cost=0.00..5.17 rows=100 width=0) (actual time=0.025..0.025 rows=95 loops=1)
11                      Index Cond: (sender_id = 123)
12  Planning time: 0.379 ms
13  Execution time: 0.527 ms

And using UNION:

-- Not using OR, but UNION-ing
EXPLAIN ANALYZE
SELECT *
FROM messages
WHERE recipient_id = 123
UNION
SELECT *
FROM messages
WHERE sender_id = 123
ORDER BY send_date ;

you get the following query plan:

QUERY PLAN
1   Sort  (cost=538.87..539.37 rows=200 width=48) (actual time=0.387..0.399 rows=192 loops=1)
2     Sort Key: messages.send_date
3     Sort Method: quicksort  Memory: 34kB
4     ->  HashAggregate  (cost=529.22..531.22 rows=200 width=48) (actual time=0.268..0.317 rows=192 loops=1)
5           Group Key: messages.sender_id, messages.recipient_id, messages.send_date, messages.message_text
6           ->  Append  (cost=5.07..527.22 rows=200 width=48) (actual time=0.038..0.192 rows=192 loops=1)
7                 ->  Bitmap Heap Scan on messages  (cost=5.07..262.55 rows=100 width=48) (actual time=0.038..0.094 rows=97 loops=1)
8                       Recheck Cond: (recipient_id = 123)
9                       Heap Blocks: exact=89
10                      ->  Bitmap Index Scan on messages_recipient_id_idx  (cost=0.00..5.04 rows=100 width=0) (actual time=0.022..0.022 rows=97 loops=1)
11                            Index Cond: (recipient_id = 123)
12                ->  Bitmap Heap Scan on messages messages_1  (cost=5.19..262.67 rows=100 width=48) (actual time=0.033..0.085 rows=95 loops=1)
13                      Recheck Cond: (sender_id = 123)
14                      Heap Blocks: exact=86
15                      ->  Bitmap Index Scan on messages_pkey  (cost=0.00..5.17 rows=100 width=0) (actual time=0.021..0.021 rows=95 loops=1)
16                            Index Cond: (sender_id = 123)
17  Planning time: 0.178 ms
18  Execution time: 0.521 ms

For the sake of completeness: UNION ALL:

    QUERY PLAN
1   Sort  (cost=537.11..537.62 rows=201 width=48) (actual time=0.213..0.229 rows=160 loops=1)
2     Sort Key: messages.send_date
3     Sort Method: quicksort  Memory: 32kB
4     ->  Append  (cost=5.08..529.42 rows=201 width=48) (actual time=0.034..0.173 rows=160 loops=1)
5           ->  Bitmap Heap Scan on messages  (cost=5.08..264.74 rows=101 width=48) (actual time=0.034..0.086 rows=82 loops=1)
6                 Recheck Cond: (recipient_id = 123)
7                 Heap Blocks: exact=77
8                 ->  Bitmap Index Scan on messages_recipient_id_idx  (cost=0.00..5.05 rows=101 width=0) (actual time=0.022..0.022 rows=82 loops=1)
9                       Index Cond: (recipient_id = 123)
10          ->  Bitmap Heap Scan on messages messages_1  (cost=5.19..262.67 rows=100 width=48) (actual time=0.030..0.075 rows=78 loops=1)
11                Recheck Cond: (sender_id = 123)
12                Heap Blocks: exact=72
13                ->  Bitmap Index Scan on messages_pkey  (cost=0.00..5.17 rows=100 width=0) (actual time=0.020..0.020 rows=78 loops=1)
14                      Index Cond: (sender_id = 123)
15  Planning time: 0.222 ms
16  Execution time: 0.280 ms

You can see that, in both cases, the execution plan is nearly the same for the two firsts approaches, and better for the UNION ALL.

You can check all this at Rextester.

Your original setup

If you do exactly the same but don't have the CREATE INDEX ON messages (recipient_id) ; statement, what you would get is:

QUERY PLAN
1   Sort  (cost=2123.86..2124.36 rows=200 width=48) (actual time=27.305..27.320 rows=227 loops=1)
2     Sort Key: send_date
3     Sort Method: quicksort  Memory: 35kB
4     ->  Seq Scan on messages  (cost=0.00..2116.22 rows=200 width=48) (actual time=0.108..27.097 rows=227 loops=1)
5           Filter: ((recipient_id = 123) OR (sender_id = 123))
6           Rows Removed by Filter: 103454
7   Planning time: 0.474 ms
8   Execution time: 27.392 ms

QUERY PLAN
1   Sort  (cost=2135.60..2136.10 rows=201 width=48) (actual time=15.716..15.803 rows=227 loops=1)
2     Sort Key: messages.send_date
3     Sort Method: quicksort  Memory: 35kB
4     ->  HashAggregate  (cost=2125.90..2127.91 rows=201 width=48) (actual time=15.571..15.621 rows=227 loops=1)
5           Group Key: messages.sender_id, messages.recipient_id, messages.send_date, messages.message_text
6           ->  Append  (cost=0.00..2123.89 rows=201 width=48) (actual time=0.061..15.371 rows=227 loops=1)
7                 ->  Seq Scan on messages  (cost=0.00..1857.01 rows=100 width=48) (actual time=0.060..15.092 rows=117 loops=1)
8                       Filter: (recipient_id = 123)
9                       Rows Removed by Filter: 103564
10                ->  Bitmap Heap Scan on messages messages_1  (cost=5.20..264.87 rows=101 width=48) (actual time=0.086..0.248 rows=110 loops=1)
11                      Recheck Cond: (sender_id = 123)
12                      Heap Blocks: exact=101
13                      ->  Bitmap Index Scan on messages_pkey  (cost=0.00..5.18 rows=101 width=0) (actual time=0.066..0.066 rows=110 loops=1)
14                            Index Cond: (sender_id = 123)
15  Planning time: 0.333 ms
16  Execution time: 16.006 ms

QUERY PLAN
1   Sort  (cost=2131.58..2132.08 rows=201 width=48) (actual time=14.847..14.865 rows=227 loops=1)
2     Sort Key: messages.send_date
3     Sort Method: quicksort  Memory: 35kB
4     ->  Append  (cost=0.00..2123.89 rows=201 width=48) (actual time=0.076..14.731 rows=227 loops=1)
5           ->  Seq Scan on messages  (cost=0.00..1857.01 rows=100 width=48) (actual time=0.076..14.497 rows=117 loops=1)
6                 Filter: (recipient_id = 123)
7                 Rows Removed by Filter: 103564
8           ->  Bitmap Heap Scan on messages messages_1  (cost=5.20..264.87 rows=101 width=48) (actual time=0.082..0.209 rows=110 loops=1)
9                 Recheck Cond: (sender_id = 123)
10                Heap Blocks: exact=101
11                ->  Bitmap Index Scan on messages_pkey  (cost=0.00..5.18 rows=101 width=0) (actual time=0.060..0.060 rows=110 loops=1)
12                      Index Cond: (sender_id = 123)
13  Planning time: 0.284 ms
14  Execution time: 14.931 ms

... which are much worse query plans, because you need some sequential scans. You can check this approach also at this Rextester.


Get max date for each year from list of dates

It is possible that I am missing something, but you should be able to get the result easily using the max() aggregate and a GROUP BY:

select
  max(date) maxdate,
  year
from yourtable
group by year;

See SQL Fiddle with Demo


You may want to consider just setting the Recovery Model for tempdb to Simple. That should get you around the problem you are having as it will reclaim all of the space used in the transaction log for tempdb. The MSDN documentation also suggests setting the Recovery Model for tempdb to Simple for performance reasons.

MSDN: http://msdn.microsoft.com/en-us/library/ms175527.aspx

EDIT
According to the documentation, for SQL Server 2005 - 2008 R2, tempdb cannot be backed up and should always be set to the Simple recovery model. If for some reason you are not using the Simple recovery model for tempdb, I would suggest switching to it.

EDIT 2
Just to be certain that the recovery model for tempdb is set to Simple, execute the following stored procedure:

sp_helpdb

Check the results for tempdb, and make sure the status column for tempdb shows something like:

Status=ONLINE, Updateability=READ_WRITE, UserAccess=MULTI_USER, Recovery=SIMPLE, ...

If it does, then your problem may be related to the operations you are performing. Are you trying to transactionally write to a temp table? Do you have open transactions that are not getting closed? Is there one operation in particular that is crashing, or does everything not work? You may want to check the answers to this similar question on serverfault for some more ideas. Long story short, if you're still getting this error after setting the Recovery Model to Simple I would start looking at the operations you're trying to perform as a possible culprit.

EDIT 3
From the output of DBCC SQLPERF (LOGSPACE) it appears that your tempdb log file is only 1.24 MB, which seems unnecessarily small to me. You can increase the size of the log file by using Enterprise Manager, or you should be able to use the following SQL (although you might need to look up the log file name by checking the files on tempdb):

ALTER DATABASE tempdb MODIFY FILE (NAME = templog, SIZE = 50MB)

You could try increasing the log file size to see if that helps.


Stopping SQL Server Agent: Effect on runnings jobs

Yes, jobs are aborted. Non-SQL process (eg CmdExec) are aborted too.
No more jobs run until SQL Server Agent is started again. Missing scheduled jobs will not be run.

After comment:

SQL Server Agent is just another connection to the DB engine. It will send an abort to the DB engine and all SQL jobs are stopped.

Abort means something here: it is a specific client action. See SQL Server Transaction Timeout


Can I install SQL server 2012 on a same machine with SQL server 2005?

Yes, you can install an additional instance of SQL Server 2012 (you'll need a named instance if your 2005 instance is a default), and it can happily co-exist without interfering with your existing 2005 instance. None of your existing databases will be touched, and you will still be able to have both 2005 and 2012 versions of the client tools, too (this isn't true for all side-by-side installs of different versions).

You could also opt for an in-place upgrade; this will keep your named instance and upgrade your databases. Need to be careful with this one though; run at least the 2012 upgrade advisor in case you have 80 compatibility issues, queries with old join syntax, etc. This is not an exhaustive check (it can't check queries constructed by dynamic SQL, and it won't check queries submitted by applications either) but it's a good start.

To learn more about how SQL Server behaves with multiple version I would suggest you to read Work with Multiple Versions and Instances of SQL Server


Index Maintenance on Indexed View using Ola Hallengren Script

I run Ola's scripts from SQL Agent, and the jobs that use them are owned by an ID that has sysadmin server role.  You could also ask Ola for his thoughts.


Database Structure with Recurring Payment

Systems I have worked with in the past stored for each customer a row for each recurring product they have. Each row has a product id, start date and anticipated end date, and a "billed to date". Each month when you bill them you increment the date.

From that you generate an invoice (storing the values to make up the invoice), and they customer makes payments against that. Their balance is calculated from opening balance + invoices +/- payments.

It gets complicated when product prices change over time, or discounts only apply for a specific period. You need to store enough detail that you can recreate the invoice correctly at that point in time.  If you give a refund that you don't want to refund too much, and if required can you pro-rate adjustments?

A good approach is to ask yourself what level of detail do you need to see on an invoice. If I had to recreate the invoice, adjust it or provide a refund, where would I get the data from. You need to either store the detail or be able to reproduce it reliably. Can you?

The next level to consider is, what accounting information to I need to report? How much did we sell last month? what discounts applied? how many customers have paid? who owes us money and how much?(debt mngt) How much tax have i charged? (is anything exempt? different rates?)

Look and the end results you need and work back from there.


INSERT with ON CONFLICT does not insert rows as expected

I see an elephant in the room:
... and deleted_date is null

There can be rows with non-null deleted_date, which are ignored by your test with  SELECT but still conflict in the unique index on (feed_id,feed_listing_id).
Aside, NOT IN (SELECT ...) is almost always a bad choice. Even if it's not going to break completely with all NOT NULL columns in your case, it still typically delivers bad performance. See:

Select rows which are not present in other table

Use NOT EXISTS instead:
SELECT *
FROM   listing_master  m
WHERE  feed_id = 'ddf'
AND    NOT EXISTS (
   SELECT FROM listing
   WHERE  feed_id = 'ddf'
   AND    feed_listing_id = m.feed_listing_id
   AND    deleted_date is null                 -- ?????
   );

What's more, even with deleted_date is null out of the way, this expectation of yours is strictly incorrect:

... while it clearly should add 136 of them.

You only tested for feed_id = 'ddf'. There can be any number of additional rows with a different feed_id. So it would be "at least 136 of them".

If that's not it, we need to talk about possible concurrent write access. Related:

Concurrent transactions result in race condition with unique constraint on insert
How to use RETURNING with ON CONFLICT in PostgreSQL?

Or possible index corruption. In this case the cure would be:
REINDEX INDEX listing_feed_id_feed_listing_id_idx;


How to disable flashback query logging for a specific table (Oracle)?

You can declare table as NOLOGGING. Then some of the DML and DDL operations will not be recorded in redo logs.

Oracle logging_clause

Of course this has many caveats:

if you have many NOLOGGING operations basically you won't be able to flashback or restore the table
UPDATE or DELETE operations are logged in any case
only direct path INSERTS can skip logging. But that means that such INSERT will not reuse empty space in table segment and will extend it every time you do such INSERT.

In my experience in some situations one can save a lot of redo/archivelog space using NOLOGGING but you have to adapt your code to it.


Why has mongorestore stopped recognizing the --ssl option

The instructions for installing on OS X manually via curl currently use the non-SSL version as an example and do not actually link to the latest version (eg. 3.4.2 is included at the moment but 3.4.4 is the latest production release). I've raised DOCS-10176 to correct this information.

In the interim, if you visit the MongoDB Download Center which is referenced directly above the curl step you can find links for of the available packages to download. The OS X downloads for MongoDB 3.0+ should all default to a version with SSL support, or explicitly highlight if a selected distribution does not include SSL encryption.


SQL Server service account change from virtual account to AD account

The virtual accounts tied to the service sid (in this case NT SERVICE\MSSQLSERVER) will retain any permissions given to them. This is because they don't go away, they are still tied to the service via the service sid.

When you change the account, for the example you gave, other than a few house keeping items there is nothing else given. It is very well possible to change to a domain account that does not have the proper permissions to even start the SQL Server service. Nothing is transferred.

For example, if you had an account that had full access to read and write to a directory not in the SQL Server standard locations (that are part of the install process) and changed the account, the permissions stay with that account. The new account would need to be granted those permissions in order for SQL Server to properly work with anything in that folder.

The last part of being linked to the virtual account. No, they are not linked. The virtual account and service sid will stay with whatever they had.


how to insert data into extra columns of target avro table when source table is having less no of columns compared to target using hive or impala?

You need to specify which columns you want to insert into in the insert statement. In the statement you wrote, the engine doesn't know which 7 columns out of the 8 to put the data in.
http://www.cloudera.com/documentation/enterprise/5-5-x/topics/impala_insert.html

Insert overwrite table target_table (col1, col2, col3, ...)
Select col1, col2, col3 ... From source_table;

NULL will be inserted into columns not mentioned in the column list.


Can't NoSQL DB ensure consistency if it is residing in a single server?

Yes, a write to a single instance, distributed database will be immediately consistent.  You likely still won't have ACID, as each NoSQL variant has its own ways of handling (or not) atomicity.
Although, I have to wonder what the goal would be here.  The main feature of a NoSQL database is its ability to support partition tolerance...which is lost in a single instance configuration.  I can't think of many scenarios like that where I wouldn't just run Postgres.


How to count number of distinct days from one table using two dates (for range) from another table for each row?

SELECT a.*, COUNT(DISTINCT b.loginday)
FROM usertable a
JOIN logintable b
    ON b.loginday BETWEEN a.mindate AND a.maxdate
GROUP BY a.userid /* , a.mindate, a.maxdate, ... */


How to model a Letter Transportation business context in a database diagram?

Although you stated that the structure depicted in your diagram is part of a university project, the objective should be to make it as realistic as possible, so I consider that carrying out an interview (or a series of interviews) with the experts of a correspondence company might be very helpful (perhaps indispensable).

In this way, since creating a full and accurate entity-relationship diagram (or any of its derivatives) for this kind of business domain would require a much more in-depth examination (and, thus, a very long series of to-ings and fro-ings), the goal of this answer is to point you in the right direction with respect to the technical methods you might have to employ, presenting an expository analysis that includes my first draft of an IDEF1X model made up of elements that seem feasible, so that you can use it as a reference to capture the meaning of a real scenario.

Central aspects

The way I see it, the aspects of the business domain analysis that require special attention are the Events in which a Letter might be involved; therefore, I will detail bellow a hypothetical context that consists of some supossed Types of Event that appear of relevance (but, naturally, you should confirm, modify or discard all the discussed points on your own).

Considerations

I have listed the elements (brought up in the talk we had via chat) that I consider particularly helpful to work on forwarding the situation:

  [A]t any time, I would like to know where the letter is - no matter if it is in some warehouse or it is being transported.
  In my design sender is only the original sender of a letter (client of the company) and receiver (recipient) is a person to which letters is addressed - there is always one sender and one recipient for a letter.
  [F]or one transport there can only be one courier.
  One letter can be stored in up to about 10 warehouse. However number of all warehouses is much higher - few hundreds (or maybe even thousands).
  My idea was to store every client (sender / recipient) in database. So if some person have send or received at least one letter it […] is in database (but if person sends / receives more letters it is in database only once).

Business rules

Then, based on (a) such elements, (b) the content of your question, and (c) some estimations and assumptions that appear viable, I wrote the following formulations that describe the tentative interrelationships between the hypothetical entity types of significance (i.e., a substantial part of the business context rules, which serve the purpose of defining the corresponding conceptual model):

Person, Letter and Address

A Letter is sent by exactly one Person (who plays the role of Sender)
A Person sends zero-one-or-many Letters
A Letter addresses exactly one Person (who carries out the role of Addressee)
A Person (performing the role of Addressee) is addressed in zero-one-or-many Letters
A Person keeps zero-one-or-many Addresses
An Address is kept by zero-one-or-many Persons
An Address locates the Sender of zero-one-or-many Letters
An Address locates the Addressee of zero-one-or-many Letters

Letter and Event

A Letter is involved in one-to-many Events
An Event is classified by exactly one EventType
An Event is

either a Dispatch1
or an Exit2
or a Transport3
or a HalfwayStorage4
or a DeliveryAttempt5
or a Receiving6

1 Dispatch. The Event that occurs when a certain Person leaves a specific Letter in a precise Warehouse so that it can take part in a transportation process.

2 Exit. The Event that happens when a particular Letter departs from a given Warehouse to start the transportation.

3 Transport. The Event that occurs when a certain Letter is being moved between distinct Warehouses while the transportation is taking place.

4 Halfway Storage. The Event that happens when a concrete Letter is kept in an exact Warehouse in the middle of the transportation.

5 Delivery Attempt. The Event that occurs when a certain Person (playing the role of Courier) tries to deliver a specific Letter to the Person who was addressed in it.

6 Receiving. The Event that happens when a certain Letter is received in the Address that locates the Person who was addressed in such Letter.

Note: Some of these Types of Event (or Event Types) might come about several times with respect to a certain Letter (e.g., a Letter could be involved in multiple Transport occurrences during a whole transportation process); other Types of Event appear to be constrained to a unique occurrence regarding a concrete transportation process (e.g., Dispatch and Receiving). On the other hand, a real business domain may entail more Event Types, and the ones discussed in this answer might simply not apply or could have distinct characteristics, that is why performing an analysis yourself is paramount. Furthermore, the terms I use here are merely explanatory, as they possibly are not equal to those employed in a real organization.

Warehouse and the different subtypes of Event: Dispatch, Exit and HalfwayStorage

A Warehouse lodges zero-one-or-many Dispatches
A Warehouse is the origin of zero-one-or-many Exits
A Warehouse is the destination of zero-one-or-many Exits
A Warehouse provides zero-one-or-many HalfwayStorages

Vehicule and the distinct subtypes of Event: Exit, Transport and DeliveryAttempt

A Vehicule is utilized in zero-one-or-many Exits
A Vehicule is employed in zero-one-or-many Transports
A Vehicule is used in zero-one-or-many DeliveryAttempts

Person and the subtypes of Event: Transport, DeliveryAttempt and Receiving

A Person (who plays the role of Courier) carries zero-one-or-many Transports
A Person (who plays the role of Courier) conveys zero-one-or-many DeliveryAttempts
A Person (who plays the role of Courier) participates in zero-one-or-many Receivings

Illustrative IDEF1X model

Consequently, I built an IDEF1Xa model in terms of all the points explained above. It is shown in Figure 1 (make sure to click the link so that you can observe it at a higher resolution):

a Integration Definition for Information Modeling (IDEF1X) is a highly recommendable data modeling technique that was established as a standard in december 1993 by the United States National Institute of Standards and Technology (NIST). It is solidly based on (a) some of the theoretical works authored by the originator of the Relational Model, i.e., Dr. E. F. Codd; on (b) the Entity-Relationship view, developed by Dr. P. P. Chen; and also on (c) the Logical Database Design Technique, created by Robert G. Brown.

Event and its subtypes

As demonstrated in said model, there is a supertype-subtype relationship that comes about

between Event (the supertype), and
Dispatch, Exit, Transport, HalfwayStorage, DeliveryAttempt and Receiving (the subtypes).

The Event superentity type possesses the properties that are common to all its subtypes, i.e., LetterNumber and DateTime (exposed as a composite PRIMARY KEY [PK for brevity]), along with a discriminator, i.e., EventTypeCode, which indicates the exact kind of subtype instance that must be complementing each Event occurrence.

The PKs of Dispatch, Exit, Transport, HalfwayStorage, DeliveryAttempt and Receiving are, at the same time, FOREIGN KEYs (FKs) that point to the Event PK.

Every one of these subtypes displays the properties (or attributes) that apply exclusively to each of them, and some of those properties are FKs that illustrate the relationships that such subtypes have with some of the other entity types included in the model.

In this regard, you might find of help my answers to

“Modeling a scenario in which each Music Artist is either a Group or a Solo Performer”;
“How to model an entity type that can have different sets of attributes?”;
“Modelling a database structure for multiple user types and their contact information”;
and the answers grouped by the subtypes tag.

Data samples

You did not clarify if the diagram is going to be used as a conceptual platform to construct a logical database structure (with proper tables, columns, column types and constraints) on a particular SQL database management system, but some data samples in tabular form will help to supply a more rounded answer that introduces further possibilities.

An EventType table

A table standing for the EventType entity type would fulfill a “look-up” role, and it may comprise the following rows:

 +-—————————————-+-————————————————-+-———————————————-+
 | EventTypeCode | Name             | Description     |
 +-—————————————-+-————————————————-+-———————————————-+
 | D             | Dispatch         | The event that… |
 +---------------+------------------+-----------------+
 | E             | Exit             | The event that… |
 +---------------+------------------+-----------------+
 | T             | Transport        | The event that… |
 +---------------+------------------+-----------------+
 | H             | Halfway Storage  | The event that… |
 +---------------+------------------+-----------------+
 | D             | Delivery Attempt | The event that… |
 +---------------+------------------+-----------------+
 | R             | Receiving        | The event that… |
 +---------------+------------------+-----------------+

Note that the EventTypeCode column, that has to be constrained as the PK, contains values that are meaningful (and, hence, are highly legible from the end user point of view), which enhances their usage when referenced from columns with FK constraints. At the same time, since the aforementioned values are physically light (as for their size in bytes), they behave quite fast regarding data retrieval processes and optimizes disk space consumption (mainly when pointed to from FKs).

An Event table

Then, let us suposse that a table dennoting the entity type named Event maintains the data points that follow for the Letter that is primarily identified by the Number 1750:

 +-————————————-+-———————————————————————-+-—————————————-+
 | LetterNumber | RegisteredDateTime      | EventTypeCode |
 +-————————————-+-———————————————————————-+-—————————————-+
 | 1750         | 2016-11-12 16:58:12.000 | D             |
 +--------------+-------------------------+---------------+
 | 1750         | 2016-11-15 09:12:05.000 | E             |
 +--------------+-------------------------+---------------+
 | 1750         | 2016-11-15 10:12:05.000 | T             |
 +--------------+-------------------------+---------------+
 | 1750         | 2016-11-15 11:12:05.000 | T             |
 +--------------+-------------------------+---------------+
 | 1750         | 2016-11-15 12:12:05.000 | T             |
 +--------------+-------------------------+---------------+
 | 1750         | 2016-11-15 12:46:01.000 | H             |
 +--------------+-------------------------+---------------+
 | 1750         | 2016-11-17 06:24:07.000 | T             |
 +--------------+-------------------------+---------------+
 | 1750         | 2016-11-17 07:24:07.000 | T             |
 +--------------+-------------------------+---------------+
 | 1750         | 2016-11-17 08:24:07.000 | T             |
 +--------------+-------------------------+---------------+
 | 1750         | 2016-11-17 09:10:13.000 | H             |
 +--------------+-------------------------+---------------+
 | 1750         | 2016-11-18 08:01:01.000 | T             |
 +--------------+-------------------------+---------------+
 | 1750         | 2016-11-18 09:01:01.000 | T             |
 +--------------+-------------------------+---------------+
 | 1750         | 2016-11-18 09:27:12.000 | H             |
 +--------------+-------------------------+---------------+
 | 1750         | 2016-11-19 11:16:08.000 | D             |
 +--------------+-------------------------+---------------+
 | 1750         | 2016-11-19 11:48:03.000 | R             |
 +--------------+-------------------------+---------------+

As shown, that table would be retaining a sequence of all the Events in which Letter Number 1750 was involved during a transportation. Of course, it would keep all the Events connected with all the pertinent Letters, but I only depicted a few hypothetical rows related to a supossed Letter Number 1750 in order to simplify the example. The sort of “chronological” data structure held in this table is commonly referred to as a time series.

The Event.EventTypeCode column, which should be constrained as a FK, holds values that indicate the kind of Event that was registered (in a user-friendly manner, in accordance with the points mentioned in the previous section).

Application programs sharing data from tables representing the subtypes

A table with partial Event information (like the one previously deliberated) can be displayed in, e.g., a page of a web application program that provides a link that redirects to subsequent pages based on (a) the Event Type, (b) the Letter Number and (c) the Date and Time of the Event of interest.

In turn, the subsequent pages will exhibit the full Event information. E.g., if

the EventTypeCode is D,
the LetterNumber is 1750, and
the DateTime is 2016-11-12 16:58:12.000,

then the redirection must take to a page that presents the complete Dispatch row, e.g.:

 +-————————————-+-———————————————————————-+-———————————————-+
 | LetterNumber | RegisteredDateTime      | WarehouseNumber |
 +-————————————-+-———————————————————————-+-———————————————-+
 | 1750         | 2016-11-12 16:58:12.000 |               x |
 +--------------+-------------------------+-----------------+

In case that

the EventTypeCode is T,
the LetterNumber is 1750, and
the DateTime is 2016-11-15 10:12:05.000,

the redirection ought to take to a page that sets out the pieces of information of the whole Transport row:

 +-————————————-+-———————————————————————-+-——————————————-+-—————————-+-————————-+-—————————-+
 | LetterNumber | RegisteredDateTime      | VehiculeNumber | CourierId | Latitude | Longitude |
 +-————————————-+-———————————————————————-+-——————————————-+-—————————-+-————————-+-—————————-+
 | 1750         | 2016-11-12 16:58:12.000 |              w |         x |        y |         z |
 +--------------+-------------------------+----------------+-----------+----------+-----------+

Integrity, consistency and derivability

Evidently, a database structure with these characteristics entails a considerable set of constraints that must be put up to protect the integrity of the data, guaranteeing its consistency with the rules determined at the conceptual level (e.g., the cardinalities of the relationships between entity types/entity type instances).

Some of these constraints can be configured declaratively by way of PK and FK definitions, but others require resorting to a procedural approach —due to limitations in the declarative facilities provided by the major SQL database management systems—.

Among the rules that have to be enforced procedurally is the feature that (1) each Event row must be suplemented at all times by (2) the respective counterpart in exactly one of the tables representing the subtypes, which (3) must “comply with” the value enclosed in the Event.EventTypeCode column —standing for the discriminator—, so the utilization of ACID TRANSACTIONS is convenient to ensure that these circumstances are always met. Other possibility would be employing TRIGGERS, but they tend to make things messy.

And, yes, it would be practical to express and fix as VIEWs several queries that “combine” data from some or all the related tables in order to, e.g., simplify the pertinent data derivation.


Oracle Grid Control Notification for Data Guard failures

Apply Lag will notify you when the apply is slow, not just when it has stopped.  This may give you too many notifications, but it seems like you could tweak the threshold to limit these.

See also:
Oracle® Data Guard Concepts and Administration 10g Release 2 (10.2)
Oracle® Data Guard Broker 10g Release 2 (10.2)


Nobody can really tell you exactly why this person coded the WHERE clause this way. I can tell you that I can't think of a reason that form is better than:

r.roomprovider_id <> br.roomprovider_id

(And while I can interpret how the code is currently written, I am not sure of the expected/intended behavior - should a row be included or excluded when the right side is NULL?)

My guesses for their motivation include:

job security through obfuscation
job security through forcing bad performance, that they could fix later easily, since this will remove the possibility to seek on any of those columns if they are suitably indexed to satisfy the query
CHECKSUM() was a shiny new function they just learned, and they just had to implement it somewhere
they believed that CHECKSUM() was less prone to inaccuracy from collisions than other methods, or that - with only one input - it served any purpose whatsoever


PostgreSQL SQL syntax - JOIN and substrings

select a.host, b.host, b.volume
from hosts as a
  join hosts as b
    on b.volume like '%'||a.volume||'%'
   and a.host <> b.host;

Instead of LIKE you can also use position which might be easier to read:

select a.host, b.host, b.volume
from hosts as a
  join hosts as b
    on a.host <> b.host
   and position(a.volume in b.volume) > 0

SQLFiddle: http://sqlfiddle.com/#!15/a67c5/1


Raising an error with a system message_id in SQLCLR

Do I have any option other than raising the error and having the msg_id being 50000?

You can define custom messages that have an id > 50000, or you can choose to not define a message and use id = 50000. Internal error numbers can't be raised by users because allowing that would open up all sorts of avenues of abuse...

How you implement this really depends on your environment, so I'm not sure we can give a recommendation for what to do in this case.

One alternative to the built-in methods (I hate that custom messages are global within an instance) which is a bit out-of-the-box, and may or may not be appropriate, is to raise your errors with the default message id of 50000, and return a custom error code in the message itself (with or without an actual error message). This will let you localize the message outside the context of SQL Server, while still retaining the ability to capture specific types of messages. (Note: best done if you have some kind of client-side framework that handles the errors.) The disadvantage is that this kind of behaviour isn't usually expected by callers.


Debugging Postgres error: "could not send data to client: Broken pipe"

Yes, it seems like you've understood this correctly. The client sent the data to a postgresql backend, and then abruptly disconnected (or was killed). The message is basically "I have an answer for you, but I have nowhere to send it!"

I would definitely look at the logs of the other services and compare timestamps to try and look deeper into what is going on.

Hope that helps. =)


You can get it by using SUM(CASE...) statement.

SELECT   PROJECT_ID,
         COALESCE(SUM(CASE WHEN SOURCE_NAME = 'A' THEN AMOUNT END), 0) A_TOTAL,
         COALESCE(SUM(CASE WHEN SOURCE_NAME = 'B' THEN AMOUNT END), 0) B_TOTAL,
         COALESCE(SUM(CASE WHEN SOURCE_NAME NOT IN ('A','B') THEN AMOUNT END), 0) OTHER_TOTAL,
         COALESCE(SUM(AMOUNT), 0) AS TOTAL,
         YEAR
FROM     FINANCE_SOURCE
GROUP BY PROJECT_ID, YEAR;

PROJECT_ID | A_TOTAL | B_TOTAL | OTHER_TOTAL | TOTAL | YEAR
---------: | ------: | ------: | ----------: | ----: | ---:
         1 |       0 |      70 |           1 |    71 | 2016
         1 |      10 |      20 |          50 |    80 | 2017

dbfiddle here

As @JackDouglas has suggested, let me add his new solution that could be slightly quicker.

SELECT PROJECT_ID
     , A_TOTAL
     , B_TOTAL
     , nvl(TOTAL,0)-nvl(A_TOTAL,0)-nvl(B_TOTAL,0) OTHER_TOTAL
     , TOTAL
     , YEAR
FROM( SELECT   PROJECT_ID,
               COALESCE(SUM(CASE WHEN SOURCE_NAME = 'A' THEN AMOUNT END), 0) A_TOTAL,
               COALESCE(SUM(CASE WHEN SOURCE_NAME = 'B' THEN AMOUNT END), 0) B_TOTAL,
               COALESCE(SUM(AMOUNT), 0) AS TOTAL,
               YEAR
      FROM     FINANCE_SOURCE
      GROUP BY PROJECT_ID, YEAR );

dbfiddle here
(Execution plan included.)


How to update a column whose value is being calculated from the values exists in other columns of same table in MySql?

One approach:

SELECT @ratio := SUM(averageLikes)/SUM(averageComments)
    FROM User_engagement;
UPDATE User_engagement
    SET totalInteraction = (totalComments + totalLikes) * @ratio;

But that may be impractical...  What happens when anything changes in the table?  Won't you have to recompute and update all the rows in the table?

Instead, do the computation when you need it:

SELECT ue.totalLikes, ue.averageLikes, ue.averageComments,
       (ue.totalComments + ue.totalLikes) * x.ratio AS totalInteraction
    FROM User_engagement AS ue
    JOIN ( SELECT SUM(averageLikes)/SUM(averageComments) as ratio
            FROM User_engagement ) AS x


cannot create a procedure with error [1064] [42000]

ERROR 1064 (42000): states that "You have an error in your SQL syntax".
Can you specify what is being executed as 'my code'. That is where SQL syntax appears to be going wrong.
Make sure you are within the right database before calling your procedure.

eg: USE dbname;


How to design indexes for columns with NULL values in MySQL?


Suggestions on database model design/ performance

As you are at the DBA site, it is likely we will give you database solutions :-)

As far as I can see, you are stuck with your two options - a row for every users answer or a large list. Best bet in terms of database design is to go for a row for every users answer and to make the columns as narrow as possible.

My solution to this would be to change your field "is_correct" in the "user_answer" table to "answer_status" and make it a tinyint. If the user gets the answer wrong, this is set to 0. If the user gets the answer right, it is incremented by 1. For displaying non correctly answered questions, you would scan for all questions where "answer_status" is < 2. Now the column would represent:

0 = question not yet answered or answered incorrectly
1 = question answered correctly once
2 = question answered correctly

When the user starts their exam, you will make an entry for every question in the question table for that user, with the default answer_status = 0.

If your "question_id" column and "user_id" column are both ints, you will store 9 bytes per row - which means if you use 100MB for this table, you would be storing ~119 million rows. If 10,000 rows per user, you will be storing data for ~12,000 users. You would most likely want to cluster this table on "user_id".

In addition, you could clean up the "user_answer" table by setting a flag in a "user_exam" table. It is likely you will need this table to encapsulate the questions into an exam and to list which exams users are enrolled for. This table could have a "status" field which could be set to "Completed" when a user has passed the exam. Subsequently all of the rows in the "user_answer" table can be deleted for that exam, for that user, once they have completed all the answers (save possibly getting some statistics like length of time taken). This would reclaim space in your database when the tables clustered index is rebuilt.


MySQL 5.6 DateTime Incorrect datetime value: '2013-08-25T17:00:00+00:00' with Error Code 1292

'2013-08-25T17:00:00+00:00'

This is a valid iso-8601 datetime value, but it is not a valid MySQL datetime literal.  On that point, the developer is incorrect.

The documentation explains what ALLOW_INVALID_DATES does:

  Check only that the month is in the range from 1 to 12 and the day is in the range from 1 to 31.

In other words, 2013-02-31 would be a permissible date if allow_invalid_dates is set.  This option does not do anything when the date or datetime isn't even in a valid format for MySQL.

The +00:00 is the timezone offset from UTC.  In this case, the time expressed is in UTC, so the offset is zero hours, zero minutes.

Your workaround would be to remove the STRICT_TRANS_TABLES from the sql_mode that is a default in the config file created during the MySQL 5.6 installation process... you need to carefully consider the implications of changing this, but it does allow the data to go in.

mysql> select @@sql_mode;
+--------------------------------------------+
| @@sql_mode                                 |
+--------------------------------------------+
| STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION |
+--------------------------------------------+
1 row in set (0.00 sec)

mysql> insert into datetimetest(dt) values ('2013-08-26T12:00:00+00:00');
ERROR 1292 (22007): Incorrect datetime value: '2013-08-26T12:00:00+00:00' for column 'dt' at row 1

-- remove STRICT_TRANS_TABLES -- note that executing this only removes it for your
-- current session -- it does not make a server-wide config change

mysql> set @@sql_mode='no_engine_substitution';
Query OK, 0 rows affected (0.00 sec)

mysql> select @@sql_mode;
+------------------------+
| @@sql_mode             |
+------------------------+
| NO_ENGINE_SUBSTITUTION |
+------------------------+
1 row in set (0.00 sec)

-- now MySQL will accept the invalid value, with a warning

mysql> insert into datetimetest(dt) values ('2013-08-26T12:00:00+00:00');
Query OK, 1 row affected, 1 warning (0.00 sec)

mysql> show warnings;
+---------+------+-----------------------------------------+
| Level   | Code | Message                                 |
+---------+------+-----------------------------------------+
| Warning | 1265 | Data truncated for column 'dt' at row 1 |
+---------+------+-----------------------------------------+
1 row in set (0.00 sec)

-- the value did get inserted, but the time zone information was lost:

mysql> select * from datetimetest;
+----+---------------------+
| id | dt                  |
+----+---------------------+
|  1 | 2013-08-26 12:00:00 |
+----+---------------------+
1 row in set (0.00 sec)


Using Resource Governor to limit query CPU usage for same user

The bad news

The only way to throttle the sessions you want is to uniquely identify them. If you cannot do this, then you cannot selectively throttle resources using the Resource Governor.

The good news

There are loads of ways to stratify connections into different resource pools. You may need the cooperation of the person(s) managing the application to segregate the incoming connections, but the options for classifying incoming connections for the purposes of resource pool assignment are functionally infinite. I recommend sticking to connection properties that you set on the application connection string. My favorites are...

host_name()
original_login()
program_name()

...but you can use any arbitrary logic that will uniquely (hopefully) apply to the incoming sessions you want to throttle. If you're feeling dangerous, you can use a lookup table and change the output of your classifier function on the fly (although the docs recommend against this).

For the copy-pasta hungry...

Demo Script

use [master]
go
create resource pool demo_pool;
create workload group demo_group using demo_pool;
create login my_bad_user with password='Plz_Halp_Me_StackExchange';
go
create or alter function dbo.GetResourceGroup()
returns sysname
with schemabinding
as
begin;
    declare @rg sysname = 'default';
    if original_login() = 'my_bad_user' set @rg = 'demo_group';
    if host_name() = 'MYDOMAIN\APP_HOST_1' set @rg = 'demo_group';
    if program_name() = 'My Custom Data Science App' set @rg = 'demo_group';
    return @rg;
end;
go
alter resource pool demo_pool with (
     max_cpu_percent = 25
    ,cap_cpu_percent = 25
);
go
alter resource governor with (classifier_function = dbo.GetResourceGroup);
alter resource governor reconfigure;
go

Demo Cleanup

use [master]
go

alter resource governor with (classifier_function = null)
alter resource governor disable;
drop workload group demo_group;
drop resource pool demo_pool;
drop login my_bad_user;
drop function if exists dbo.GetResourceGroup;
go


Import CSV file into Mysql with Multiple Delimiters/Field Separators

If you're on *nix - check out the tools sed, awk, grep and split and related (or even vi). As mentioned perl could do it, so could python or PHP (or C or Java or ...) This looks more like a task for programming tools rather than a database. That's not to say you couldn't do this using PL/SQL or T-SQL or , but sometimes the most suitable tool is not within the database server (and in this case, certainly not within MySQL).


This should do what you want:

DECLARE @Users AS TABLE
(
    UserID      integer,
    UserName    nvarchar(50)
);

INSERT @Users
    (UserID, UserName)
VALUES
    (12, N'Tom'),
    (132, N'Ann'),
    (32, N'Dina'),
    (2, N'Mark');

DECLARE @Input AS TABLE
(
    Value    nvarchar(500) NOT NULL
);

INSERT @Input (Value)
VALUES
    (N'To'),
    (N'An');

SELECT *
FROM @Users AS u
WHERE
    EXISTS
    (
        SELECT *
        FROM @Input AS i
        WHERE
            u.UserName LIKE N'%' + i.Value + N'%'
    );

Output:

╔════════╦══════════╗
║ UserID ║ UserName ║
╠════════╬══════════╣
║     12 ║ Tom      ║
║    132 ║ Ann      ║
╚════════╩══════════╝


SQLServer: Backup a database on a server and restore it on another server

It is not necessary to drop the database before restoring. Adding WITH REPLACE will restore the backup over an existing database.

RESTORE DATABASE MyDb FROM DISK = \\ServerProd\d$\DatabaseBackup\MyDb\MyDb_backup_2018_11_13_152440_3319782.bak' WITH REPLACE

Regarding the "Access is denied" error. The service account running your SQL Server will need to have permission on the network share.

If you are not using an Active Directory service account (which is highly recommended) you need to copy the .bak file to the server before restore.

Finally, I would suggest looking at Ola Hallengren's maintenance solution. It has a comprehensive backup and restore features: Ola Hallengren SQL Server Backup.
This will give you a flexible and robust (and free) backup solution.

Randi Vertongen suggested dbatools.

It is an amazing toolset for all things SQL Server. For your problem look at the commandlets Backup-DbaDatabase and Restore-DbaBackupFromDirectory.


How can I edit the content of a table in the data output of a query in pgAdmin III?

Agan, Franck, you have discovered another (serious this time) lacuna in the pgAdmin3 tool. Perhaps you can also put in a feature request for this also.

I tested it on Windows and Linux. If you provide a link here to any feature request, I would only be too glad to put in a "me too" on the wish list (same goes for the other one).

If data is editable, then the user should be allowed to edit the data in a grid like that.


Configuring Oracle 11gR2 RAC on Virtual Machine

You need 2 network interfaces, 1 for public network, 1 for private network. (1 is also enough with seperate VLAN support, but it is not common and goes against the design.)
The public IP is to be set on the public interface, and the private IP is to be set on the private interface. These 2 addresses are managed at operating system level and must be set before installing Oracle Grid Infrastructure.

Then you have a virtual IP, at least 1 for each node. This address will use the public network interface and it must be in the same subnet as the public IP address. This is managed by Oracle Grid Infrastructure, and this address must not be set at the operating system level or reserved by other systems. While this address logically belongs to a specified node, this address can be used by other nodes as well when a node goes down (vip failover).

Finally we have SCAN from version 11.2, which is a single name that is resolved to at least 1 IP address (but typically 3 addresses in the documentation and other examples) from the subnet of public network. It does not belong to any node, it belongs to the cluster. This behaves similarly to the above virtual IP.

The above is the minimum, you can have multiple public or private networks and multiple VIP and SCAN addresses over them in newer releases.


How to insert binary data into a PostgreSQL BYTEA column using libpqxx?

Took a while, but I figured out how to do it.  Here is some example code:

pqxx::connection conn( ... );
conn.prepare( "test", "INSERT INTO mytable( name, binfile ) VALUES ($1, $2)" );
pqxx::work work( conn );
for ( ... )
{
    std::string name = "foo";
    void * bin_data = ...; // -- obviously do what you need to get the binary data...
    size_t bin_size = 123; // -- ...and the size of the binary data

    pqxx::binarystring blob( bin_data, bin_size );
    pqxx::result r = work.prepared( "test" )( name )( blob ).exec();
}
work.commit();


How group with min(date) and select an another column in the same table

I infer that your data looks like this:

Person Table

╔══════════╦═══════╦════════╗
║ PersonID ║ Name  ║ Gender ║
╠══════════╬═══════╬════════╣
║        1 ║ John  ║ M      ║
║        2 ║ Vicky ║ F      ║
║        3 ║ Bob   ║ M      ║
╚══════════╩═══════╩════════╝

Job Table

╔══════════╦═════════════╦════════════╗
║ PersonID ║   JobName   ║  HireDate  ║
╠══════════╬═════════════╬════════════╣
║        1 ║ Electrician ║ 2010-01-01 ║
║        1 ║ Mechanic    ║ 2012-05-08 ║
║        2 ║ Scientific  ║ 2012-11-11 ║
╚══════════╩═════════════╩════════════╝

The first task is to find the first job (by hire date) for each person.  One neat way to do that is by using a correlated subquery:

SELECT j.*
FROM dbo.Job AS j
WHERE
    j.HireDate =
    (
        SELECT MIN(j2.HireDate)
        FROM dbo.Job AS j2
        WHERE j2.PersonID = j.PersonID
    );

Notice the correlation WHERE j2.PersonID = j.PersonID between the inner and outer queries there.  The output of that query is:

╔══════════╦═════════════╦════════════╗
║ PersonID ║   JobName   ║  HireDate  ║
╠══════════╬═════════════╬════════════╣
║        1 ║ Electrician ║ 2010-01-01 ║
║        2 ║ Scientific  ║ 2012-11-11 ║
╚══════════╩═════════════╩════════════╝

The execution plan (given a clustered PRIMARY KEY on PersonID, HireDate) is:

The interesting thing about that plan is the Job table is only scanned once, despite there being two references to it in the original query.  The plan uses an optimization that I call Segment Top.  Essentially the execution engine takes advantage of the index order to detect the start of a new group (segment) and take just the first row from each group (top).

Now that we have that result, all we need do is join it back to the Person table:

SELECT
    p.PersonName,
    p.Gender,
    j.JobName
FROM dbo.Person AS p
LEFT JOIN
(
    -- Previous query
    SELECT j.*
    FROM dbo.Job AS j
    WHERE
        j.HireDate =
        (
            SELECT MIN(j2.HireDate)
            FROM dbo.Job AS j2
            WHERE j2.PersonID = j.PersonID
        )
) AS j ON
    j.PersonID = p.PersonID
OPTION (MERGE JOIN);

The execution plan is:

The OPTION (MERGE JOIN) is not required; I just added it to show the plan you are likely to get when the tables contain a larger number of rows than in this small example.

Table definitions and sample data:

CREATE TABLE dbo.Person
(
    PersonID    integer NOT NULL,
    PersonName  varchar(30) NOT NULL,
    Gender      char(1) NOT NULL,

    PRIMARY KEY (PersonID)
);

CREATE TABLE dbo.Job
(
    PersonID    integer NOT NULL,
    JobName     varchar(30) NOT NULL,
    HireDate    datetime NOT NULL,

    PRIMARY KEY (PersonID, HireDate)
);

INSERT dbo.Person
    (PersonID, PersonName, Gender)
SELECT 1, 'John', 'M' UNION ALL
SELECT 2, 'Vicky', 'F' UNION ALL
SELECT 3, 'Bob', 'M';

INSERT dbo.Job
    (PersonID, JobName, HireDate)
SELECT 1, 'Mechanic', '20120508' UNION ALL
SELECT 1, 'Electrician', '20100101' UNION ALL
SELECT 2, 'Scientific', '20121111';


Trying SQL query with subquery in WHERE clause

We have a solution, no idea if it is legit ^^

SELECT
    muc . * ,
    (
        SELECT COUNT( * )
        FROM users AS u
        LEFT OUTER JOIN link_users_conversations luc ON u.id = luc.user_id
        WHERE luc.user_conversation_id = muc.id
    ) readers
FROM user_conversations AS muc
LEFT OUTER JOIN link_users_conversations mluc ON muc.id = mluc.user_conversation_id
WHERE mluc.user_id = :currentUserID
/* Check for all 'readers' if a relation exists */
AND EXISTS (
    SELECT *
    FROM link_users_conversations AS c0
    WHERE c0.user_id = :participantID0
    AND c0.user_conversation_id = muc.id
)
AND EXISTS (
    SELECT *
    FROM link_users_conversations AS cn
    WHERE cn.user_id = :participantIDn
    AND cn.user_conversation_id = muc.id
)
HAVING readers = :totalReaders /* Total readers could be higher while all EXISTS pass. */
LIMIT 1

I simplified names in this example.

So it mimics behaviour as seen at Facebook when starting a new Chat conversation. It will search the database if any other conversation has exactly (EXACTLY!) the same participants/readers/followers, if one exists, the new message will be posted to the existing conversation instead of creating a new one.

(FYI: The SQL provided is to check for existing conversations only. Creating messages and new conversations need other functions.)

If someone knows of a better way to do this check, please comment :)


Lifespan of Launchpad-Launched Exterrnal Runtime Processes

For security reasons, Python processes are not reused. Each call to sp_execute_external_script uses its own process instance(s), at least as far as Python goes.

Since Microsoft SQL Server's Python and R integrations are very similar, I'd suspect the same holds true with R, as well, but I can't say that with 100% certainty.

Reference: reddit comment made by SQL Server engineering team program manager Tobias Ternström


Is there any (hidden) built-in function on MS-SQL to unquote object names?

Sometimes I store objects names in some of our databases
I must take care to store this names always with or without brackets.

An "object name" is technically called an identifier.  In some contexts an identifier will be appear TSQL code surrounded by [ and ] or " and ".  These characters are not part of the identifier, and you should never store them.
Instead store the identifier as a nvarchar(128) (or sysname), and add the delimiters at runtime using the QUOTENAME function.
The inverse of QUOTENAME is PARSENAME, which has the additional ability to navigate multi-part names.
Note that QUOTENAME has an optional second parameter, and if you specify a single quote character for that parameter QUOTENAME does not create a valid delimited identifier expression.  It emits a varchar literal expression.


SQL Server Linked Server performance: Why are remote queries so expensive?

The plan you have at the moment looks like the most optimal plan to me.

I don't agree with the assertion in the other answers that it is sending the 2.6M rows to the remote server.

The plan looks to me as though for each of the 54 rows returned from the remote query it is performing an index seek into your local table to determine whether it is matched or not. This is pretty much the optimal plan.

Replacing with a hash join or merge join would be counterproductive given the size of table and adding an intermediate #temp table just adds an additional step that doesn't seem to give you any advantage.


Do not reject entire statement when deleting rows in batch

I was able to make this work with the below setup
Process:
1. Loop through the pg_constraint table and understand the constraints defined on the table that has to delete a record
2. Before deleting a record verify no reference record exists in any of the dependent table

Detailed implementation can be found below.
Let me know your thoughts on this.

            #1: DDL

            CREATE TABLE core.images
            (
                id serial NOT NULL,
                name character varying(1024) NOT NULL
                CONSTRAINT "PK_images" PRIMARY KEY (id)
            );

            create table core.app_images
            (
            id serial,
            image_id int,
            CONSTRAINT "FK_foo_image" FOREIGN KEY (image_id)
                 REFERENCES core.images (id) MATCH SIMPLE
                 ON UPDATE CASCADE ON DELETE RESTRICT
            );

            create table core.app_images1
            (
            id serial,
            image_id int,
            CONSTRAINT "FK_foo_image1" FOREIGN KEY (image_id)
                 REFERENCES core.images (id) MATCH SIMPLE
                 ON UPDATE CASCADE ON DELETE RESTRICT
            )

            #2: Funciton to check ref data integrity

            CREATE OR REPLACE FUNCTION core.check_ref_exists (
                p_schema TEXT,
                p_table TEXT,
                p_column TEXT,
                p_value TEXT
            )
            RETURNS BOOLEAN
            LANGUAGE plpgsql
            AS $$
            DECLARE
                r record ;
                v_sql TEXT ;
                v_return BOOLEAN DEFAULT FALSE ;
            BEGIN
                FOR r IN (
                    SELECT
                        pn.nspname s_name,
                        r_tab.relname AS t_name,
                        pa.attname AS c_name
                    FROM
                        pg_constraint pc
                    INNER JOIN pg_class tab ON tab.oid = pc.confrelid
                    INNER JOIN pg_class r_tab ON r_tab.oid = pc.conrelid
                    INNER JOIN pg_namespace pn ON pn.oid = r_tab.relnamespace
                    INNER JOIN pg_namespace pn_drv ON pn_drv.oid = tab.relnamespace
                    INNER JOIN pg_attribute pa ON (
                        pa.attrelid = pc.conrelid
                        AND pa.attnum = ANY (pc.conkey)
                    )
                    INNER JOIN pg_attribute pa_d ON (
                        pa_d.attrelid = pc.confrelid
                        AND pa_d.attnum = ANY (pc.confkey)
                    )
                    WHERE
                        contype = 'f'
                    AND tab.relname = p_table
                    AND pn_drv.nspname = p_schema
                    AND pa_d.attname = p_column
                ) loop

                    v_sql := 'SELECT TRUE FROM ' || p_schema || '.' || p_table || ' s
                        WHERE s. ID = ' || p_value || '
                        AND EXISTS (
                            SELECT 1 FROM ' || r.s_name || '.' || r.t_name || ' d WHERE s.' || p_column || ' = d.' || r.c_name || ')' ; EXECUTE v_sql INTO v_return ;
                    v_return := COALESCE (v_return, FALSE) ;
                    IF v_return = TRUE THEN
                        RETURN v_return ;
                    END IF ;

                END loop ;
                RETURN v_return ;
            END ; $$

            #3: Trigger Function
            CREATE OR REPLACE FUNCTION core.images_tr_func()
            RETURNS TRIGGER AS
            $BODY$
            DECLARE
                v_ref_exists boolean;
            BEGIN
                select core.check_ref_exists (
                    p_schema := 'core',
                    p_table :='images',
                    p_column :='id',
                    p_value := old.id::text
                ) into v_ref_exists;

                if v_ref_exists then
                    return null;
                else
                    return old;
                end if;
            EXCEPTION
                    WHEN OTHERS THEN
                            RAISE EXCEPTION 'SQLSTATE: %, SQLERRM: %',SQLSTATE,SQLERRM;
                            RETURN NULL;
            END;
            $BODY$
            LANGUAGE plpgsql;

            #4: Trigger
            DROP TRIGGER if exists images_tr ON core.images;

            CREATE TRIGGER images_tr BEFORE DELETE
                 ON core.images FOR EACH ROW
                 EXECUTE PROCEDURE core.images_tr_func();

            #5: Sample Data
            insert into  core.images(name) values('image1'),('image2'),('image3'),('image4');
            select * from  core.images;

            insert into core.app_images(image_id) values
            (1),(1),(2)

            select * from core.app_images

            insert into core.app_images1(image_id) values
            (2),(3),(3)

            select * from core.app_images1

            #6: Testing
            delete from core.images where id = 1; --Nothing happened as it is still referred
            delete from core.images where id = 4; --Deleted as it is an orphan record


How to detect which indexes have checksums and which ones don't?

The checksum isn't at the object or index level (aka allocation unit in this context). It is at the page level.
I.e., an allocation unit can have some pages without checksum (those that hasn't been modified since you turned on checksum) and other pages do have checksum (those that has been modified since you turned on checksum).
So you have to look at the page level to see whether each page has checksum or not. Below can serve as a starting point for you, you probably want to customize to your needs, though.
SELECT
 OBJECT_SCHEMA_NAME(a.object_id) AS schema_
,OBJECT_NAME(a.object_id) AS tbl
,a.index_id
--,*
FROM sys.dm_db_database_page_allocations(DB_ID('Adventureworks'), NULL, null, null, 'DETAILED') AS a
CROSS APPLY sys.dm_db_page_info ( DB_ID(), a.allocated_page_file_id, a.allocated_page_page_id, 'LIMITED' ) AS p
WHERE p.has_checksum = 0
AND p.page_type IN(1, 2)

Note that as written it read every page in the database. So consider whether you want to use the parameters to sys.dm_db_database_page_allocations and run it only for those tables/indexes you are interested in.
You can of course also aggregate and count number of pages with/without checksum, etc. Here's another version:
SELECT
 OBJECT_SCHEMA_NAME(a.object_id) AS schema_
,OBJECT_NAME(a.object_id) AS tbl
,a.index_id
,SUM(CAST(p.has_checksum AS int)) AS pages_with_checksum
,SUM(CASE p.has_checksum WHEN 1 THEN 0 ELSE 1 END) AS pages_without_checksum
--,*
FROM sys.dm_db_database_page_allocations(DB_ID('Adventureworks'), NULL, null, null, 'DETAILED') AS a
CROSS APPLY sys.dm_db_page_info ( DB_ID(), a.allocated_page_file_id, a.allocated_page_page_id, 'LIMITED' ) AS p
WHERE p.page_type IN(1, 2)
AND OBJECTPROPERTY(a.object_id, 'IsMsShipped') = 0 --Filter out system tables
GROUP BY
  OBJECT_SCHEMA_NAME(a.object_id)
 ,OBJECT_NAME(a.object_id)
 ,a.index_id
 ORDER BY pages_without_checksum DESC, pages_with_checksum DESC

Note sys.dm_db_page_info was added in SQL Server 2019.


Execution plan has TOP operator for query without TOP or ORDER BY

NOT EXISTS can introduce a TOP 1 (as can EXCEPT in some cases). This is because it only cares about the fact that at least one row exists. This is called short circuiting, and you want this - it is much more likely to speed up your query than slow it down. And I'd be really surprised if the TOP didn't show up in the plan for different date ranges, unless there was a substantial difference in row counts and a different plan was compiled. Maybe you could post the two .sqlplan files somewhere so that we could associate each plan with the query and help determine the reasons for any differences. With a screen shot and the query that allegedly goes with it, there's not a ton to go on.

I'm not convinced that the TOP in your case is making this query run for 5 minutes. Rather than just look at the plan, I'd actually investigate what the query is doing for 5 minutes, while it is running, by looking at sys.dm_exec_requests - is it blocking? What is the wait type? Since you're using a ##global temp table (why?), this could be due to blocking, but it's unclear from just a screen shot of a plan. Why doesn't your ##temp table have a clustered index? What other indexes does it have? The TOP would probably be more efficient if it could sort on something sensible other than the output of a full table scan. You could consider changing the subquery to be TOP (1) ... ORDER BY but adding a clustered index (or an index that covers and satisfies all the where clauses in the subquery) should accomplish something quite similar.

For more info on NOT EXISTS and alternatives, see this blog post.


Group by without temporary table and filesort

Is ID_Links_Words a many-to-many table for connecting Words and Links?  Then this is the optimal schema:

CREATE TABLE ID_Links_Words(
    word ... NOT NULL,
    link ... NOT NULL,
    count ... NOT NULL,
    PRIMARY KEY(word, link), -- for going one direction
    INDEX(link, word)        -- and the other
) ENGINE = InnoDB;           -- Important because of the clustering of the PK.

The two indexes can be swapped, depending on the various queries you do; this arrangement -- with word first in the PRIMARY KEY -- is best for this SELECT.

Remove LEFT unless there is a compelling reason for it.

What percentage of the table has those 3 'words'?  If it is a non-trivial percentage, you are stuck with a table scan.

Check the settings of innodb_buffer_pool_size and key_buffer_size.

What version of MySQL are you using?  There were improvements in the handling of IN(...) in 5.6, perhaps before.  If you are in an older version, recommend upgrading.

If the my other hints don't do enough, try this:

(   SELECT  SQL_NO_CACHE L.id, L.link, sum(W.count) AS wordCount
        FROM  Links AS L
        LEFT JOIN  ID_Links_Words AS W ON L.id = W.link
        WHERE  W.word = '1025'
        GROUP BY  W.link
)
UNION  ALL (
    SELECT  SQL_NO_CACHE L.id, L.link, sum(W.count) AS wordCount
        FROM  Links AS L
        LEFT JOIN  ID_Links_Words AS W ON L.id = W.link
        WHERE  W.word = '1056'
        GROUP BY  W.link
           )
UNION  ALL (
    SELECT  SQL_NO_CACHE L.id, L.link, sum(W.count) AS wordCount
        FROM  Links AS L
        LEFT JOIN  ID_Links_Words AS W ON L.id = W.link
        WHERE  W.word = '2353'
        GROUP BY  W.link
           );

And, assuming that the LEFT is not necessary, test this

SELECT  W.link as id,
        ( SELECT  link
            FROM  Links
            WHERE  id = W.link
        ) AS link,
        wordCount
    FROM
      ( SELECT  SUM(count) AS wordCount
            FROM  ID_Links_Words
            WHERE  word IN ('1025','1056','2353')
            GROUP BY  link
      ) AS W ;

This, if I wrote it right, has the following potential benefits:

The GROUP BY is done without being bothered by the other table.
The other table is done after shrinking the number of rows by the GROUP BY.

Again, the IN could be turned into a UNION.

Edit

There is a world of difference between INDEX(word, link) and INDEX(link, word).  INDEXes are ordered.  If you have a word and need to find the links associated with it, the former is much better.  If you have a link and want to find the words the latter is much better.  Imagine looking in a list of names ordered by (last_name, first_name), and looking for all the last names, given a first name.

Index cookbook


Right click on the table and choose 'refresh' from the drop down menu.
Or better, right click on the database selected and choose 'refresh' from the drop down.


(MySQL)

There are several MySQL Replication topologies.  For HA, deploying at least one server in each of 3 datacenters and using Galera Cluster, failover is essentially automatic if a datacenter goes down.  Some form of proxy or load balancer could take care of redirecting users to the still-alive nodes.  When the datacenter comes back up, that node will get back in sync.

If a node crashes and burns, wheel in a new server; the live nodes will pour a copy of the data set into it, and you are back on the air.

And you did not get paged.

(Now perhaps we will hear from users for which it was not that seemless.)

Group Replication is another HA product on top of MySQL.  I have seen 3 nodes being set up during a conference talk.  It took one person less than 5 minutes.

Caveat:  I cannot say whether either of these is easier or harder than Oracle's offering(s).


how to edit and migrate DTS package on SQL server 2000

Per all the references I can find (most notably Upgrade Integration Services Packages on MS TechNet, and this StackOverflow answer), the options are:

Use the tools available with SQL Server 2005 or 2008 to convert the DTS package to an SSIS package; then, upgrade the SSIS package with SSDT; or
Rewrite the package with SSDT, for your current SQL Server version.

Note - in the article, it states that any ActiveX scripts will have to be thrown out. That's not entirely true - you'd need to rewrite them as a script component in SSIS. I believe I did this at one point (could be wrong - I had a lovely Perl script to break street addresses into number, pre-direction, street name, suffix and post-direction that I had to convert multiple times to different things, ending up as an SSIS C# script component).

As 2005 and 2008 are no longer supported, I suspect getting a developer copy to install and do the conversion could be difficult. And, even if you did (as noted in the SO post), the conversion process may not give you what you want.

Back in 2009, I was involved in moving our database (and about 100 DTS packages) from 2000 to 2008. We did use the conversion process, and most of the time it worked fairly well. That said, we also tested the SSIS packages side-by-side against the original DTS packages, to confirm the results. And, now, the conversion process would be a two-step one.

If you've got 10 packages to move, a simple rewrite makes sense (and gives you an opportunity to learn about the additional capabilities that SSIS provides). If you've got dozens of packages, and happen to have access to the 2005 or 2008 SQL Server tools (you don't need to run a server with that version to do the job, I don't believe), then it may make sense to go through the conversion process and see what you get. If most of what you have makes it through fine, that's wonderful - worst case scenario, you're back to a manual rewrite.


Does 'max_worker_processes' include background process from postmaster?

I believe that they are two different processes and max_process_workers does not include the bg process. Both of them are set and defined separately. If you are talking about backgroud WOKER process (bgw) it is a complete different process and is not included in the max_process_workers by default as it has to be supplied in separate code by user and is surely maintained by postgres process itself.


Calculating the number of times a group occures

I can offer how I'd handle it in MS SQL, using PIVOT and ROW_NUMBER().  These don't appear to exist in MySQL (perhaps the latest version has added them?), so I'm afraid it won't be helpful to you.  Perhaps someone more familiar with MySQL can use this to help get to a solution.

SELECT
    "1"
    + CASE WHEN "2" IS NULL THEN '' ELSE '-' + "2" END
    + CASE WHEN "3" IS NULL THEN '' ELSE '-' + "3" END AS Applications,
    Instances
FROM
    (
    SELECT
        SID, ProgramName, ROW_NUMBER() OVER (PARTITION BY SID ORDER BY ProgramName) AS Seq, Instances
    FROM
        (
        SELECT
            SID, ProgramName, COUNT(*) AS Instances
        FROM
            Login_Apps
        GROUP BY
            SID, ProgramName
        ) AS X
    ) AS Y
PIVOT
    (MIN(ProgramName) FOR Seq IN ("1", "2", "3")) AS Z


Setting up replication for various mongodb micro instances

You have 5 servers serv1,serv2,serv3,serv4,serv5 and you want to set up 5 instanses inst1,inst2,inst3,inst4,inst5. Each instance should have at least 2 data nodes (PRIMARY and SECONDARY) and 1 ARBITER

serv1: inst1_PRI, inst5_SEC, inst4_ARB

serv2: inst2_PRI, inst1_SEC, inst5_ARB

serv3: inst3_PRI, inst2_SEC, inst1_ARB

serv4: inst4_PRI, inst3_SEC, inst2_ARB

serv5: inst5_PRI, inst4_SEC, inst3_ARB

This architecture provide fault tolerance of 1. If one server goes down all instances are continue to work. If you loose 2 servers one instance should be affected ...


SSRS (2008 R2 to 2014) Migration Issues Running Reports

I found the solution in case anyone else comes across this.

The issue lied in the connection string property of the data source. For some reason (maybe standard, I am simply unaware of why) the connection string properties were empty after importing them to the new server. After setting them, they were able to hit the stored procedures and all reports ran as intended.


Does the "don't use errors for flow control" axiom apply to postgres?

Can't tell, from the top of my head, the exact sequence of events in case of an exception. But I can tell you that it is comparatively expensive and should be avoided unless needed. An exception (error) causes the surrounding transaction to be rolled back, unless trapped.

But to trap an error, you need an EXCEPTION clause in a plpgsql block (in a function, procedure or a DO statement). And it is even expensive to have an EXCEPTION clause in a plpgsql function block to begin with (before any exceptions are raised), because such a block effectively forms a subtransaction (that can potentially be rolled back) incurring substantially more overhead. Consequently, the manual warns:

  A block containing an EXCEPTION clause is significantly more expensive to enter and exit than a block without one. Therefore, don't use EXCEPTION without need.


ROW_FORMAT - COMPRESSED vs COMPACT vs DYNAMIC

COMPACT is format supported by Antilope. It stores first 768 bytes of BLOB in case its value doesn't fit in page.

DYNAMIC is almost the same as COMPACT except only 20 bytes for each BLOB field is used. Benefits - more BLOB fields are possible in a record.

COMPRESSED is used for compressed tables. Hence its benefits.


Yes. You will need to configure all the servers involved with your process to be of DTC.

When you use the native SSIS transaction capability, if the connection managers are entirely self contained, then behind the scenes a standard BEGIN TRAN...COMMIT|ROLLBACK are issued. When there are multiple servers involved, that's when the distributed transaction coordinator comes into play. Even if you're only reading from a source table, SSIS will attempt to enlist that SELECT statement in a transaction.

We learned this the hard way when dealing with DB2 as they apparently have the ability to enable/deny transaction stuff. We also had issues using an antiquated MySQL instance as it didn't support the concept of a transaction. In that instance, we had two data flows. One ran without a transaction and wrote to a local file, then used a transaction controlled data flow to read that file into our target table. Not an advisable approach for very large data sets but worked fine for our problem set.


Syntax error when trying to import database from two PostgreSQL databases

You seem to have omitted the new version of the upgraded server, but I strongly suspect it's still on 8.1 since the ALTER SEQUENCE ... OWNED BY clause was added in 8.2 (Compare ALTER SEQUENCE docs on 8.2 to ALTER SEQUENCE docs on 8.1).

You really need to understand that 8.4 and 8.1 aren't slightly different versions, they're massively different. It's like saying that Windows Vista and Windows 7 are "slightly different" versions because they're Windows versions 6.0 and 6.1 respectively. Or that Mac OS X 10.4 and 10.7 are "slightly different"... when they're more incompatible than not.

Yes, I know the version numbering in PostgreSQL is stupid; it's a historical oddity we seem to be stuck with despite periodic attempts to change it. See the version policy document linked in the prior answer for more information about what the versioning means, and read the release notes for a better understanding.


How to write this self join based on three columns

The problem with your provided query is that you're doing an INNER JOIN. Because there are no rows with a parentid of 5, the subquery would return NULL on those rows and be excluded.

What you want is a LEFT JOIN, something like this (sqlfiddle):

SELECT t.*, parentchildrelation.parentid, childs
FROM tbl_info t
LEFT JOIN
  (SELECT
    parentid,
    COUNT(DISTINCT id) AS childs
  FROM
    tbl_info
  GROUP BY parentid) AS parentchildrelation
  ON t.id = parentchildrelation.parentid
WHERE t.no_daughter != parentchildrelation.childs
   OR (t.no_daughter > 0 AND parentchildrelation.parentid IS NULL)
GROUP BY t.id, t.parentid;

I included more columns, because where id=5 and parentid=2 has two different values for no_daughter. You should be able to adjust those as needed.


Implicit conversion causing error part of the time

The calculation of the compute scalar operator is deferred to when it is actually used (the probe residual of Node ID = 1) and the rows that would have failed for you are filtered out by the previous Hash Match (Node ID = 3).

Have a look at Compute Scalars, Expressions and Execution Plan Performance by Paul White for more information.

There is no indication in execution plans that evaluation of an expression is deferred, or when (at which node) it was actually evaluated. You can normally code defensively around this using CASE (which comes with an evaluation order guarantee) and/or TRY_CONVERT.

Related Microsoft feedback: SQL Server should not raise illogical errors


How to replicate a mongodb pod with its persistent storage (Minikube - Kubernetes)

I believe this is because you have more than one pod trying to use the same using the same data directory which you can not do if the pod is running mongo, one data directory has to be exclusive to one mongo instance.

If the goal is to create a group of pods with the same data then you are looking at creating a replica set.

In order to do this first you will need to create multiple persistent volumes as so:

kind: PersistentVolume
apiVersion: v1
metadata:
  name: mongo-persistent-0
  labels:
    type: local
spec:
  storageClassName: manual
  capacity:
    storage: 100Gi
  accessModes:
    - ReadWriteOnce
  hostPath:
    path: "/var/lib/mongo/0"
---
kind: PersistentVolume
apiVersion: v1
metadata:
  name: mongo-persistent-1
  labels:
    type: local
spec:
  storageClassName: manual
  capacity:
    storage: 100Gi
  accessModes:
    - ReadWriteOnce
  hostPath:
    path: "/var/lib/mongo/1"
---
kind: PersistentVolume
apiVersion: v1
metadata:
  name: mongo-persistent-2
  labels:
    type: local
spec:
  storageClassName: manual
  capacity:
    storage: 100Gi
  accessModes:
    - ReadWriteOnce
  hostPath:
    path: "/var/lib/mongo/2"

Then you will want to create a statefulset binding to the persistent volumes which means that when the pods go down and come back up they attach to the correct persistent volume, the yaml will look something like this:

apiVersion: apps/v1beta1
kind: StatefulSet
metadata:
  name: replica-set-a
spec:
  serviceName: "replica-set-a"
  replicas: 3
  template:
    metadata:
      labels:
        role: replica-set-a
        environment: test
    spec:
      terminationGracePeriodSeconds: 10
      containers:
        - name: replica-set-a
          image: mongo
          resources:
            limits:
              memory: 2Gi
            requests:
              memory: 2Gi
          volumeMounts:
          - name: mongo-persistent
            mountPath: /data/db
          command:
            - mongod
            - "--bind_ip"
            - "0.0.0.0"
            - "--replSet"
            - a
            - "--smallfiles"
            - "--oplogSize"
            - "1024"
          ports:
            - containerPort: 27017
  volumeClaimTemplates:
  - metadata:
      name: mongo-persistent
    spec:
      storageClassName: manual
      accessModes:
        - ReadWriteOnce
      resources:
        requests:
          storage: 100Gi

Your service can remain the same apart from changing it to look at replica-set-a. Once they are all up you will need to issue the rs.initiate(... command in order to tell the pods they are now working as one replica set, details of this can be found in the mongodb documentation: https://docs.mongodb.com/manual/tutorial/deploy-replica-set/

** Note, I have copied the example yaml's from my sharded cluster that has been deployed through kubernetes which means I have removed some parameters so there could potentially be a typo, also you might want to scale down the persistent volumes and memory limits before proceeding.


SQL Server (2012) grant right to regular user to set database in read-only mode

The simple answer is:

GRANT ALTER ON database::[yourDB] TO [yourLogin];

That may, however, give more permissions than you intend. If you want to lock that down, you could look into module signing. That is, you'd create a stored procedure that, given a database name (or perhaps none at all depending on your use case) flips the state. Then, you "bless" the procedure such that it can run in an elevated context.


How are databases able to process queries in parallel while still retaining correct order?

Answers relevant to SQL Server.

If talking about query priority:

For SQL Server there is by default a FIFO principle adhered to. The first query to run gets priority.
However with the resource governor it is possible to not only limit the resources assigned to your sql server instances, it is also possible to create workload groups which can prioritize certain connections to be able to run before others.

If talking about parallelism:

For SQL Server it does this by running parallel streams, that are fed information by a parallel page supplier, and in their turn have their information fed through a gather streams operator into a stream aggregate operator, which gathers the information provided by the separate streams to then be passed on further.

In other words, for SQL Server this is done by gathering the processed data at the end, and bundling it a new. If needed they can then be sorted or filtered afterwards.

See this simple talk article for more details.


Mass update all cells in a column with a substring of it's original value


How to estimate the disk space for 1 million rows if I have a table of fewer rows in InnoDB MySQL

Your basic assumptions are correct, it is easy to see that the allocation per row is just under 100 bytes per row, and a bit more for the indexes, giving you a 200 * 1M = 200M of total size.

However, several important things have to be taken into account:

The tables must have identical structure and equivalent content- e.g. the same exact table with similar sized variable content.
With SHOW TABLE STATUS you are getting an estimation of size (rows, total size) based on sampling- it may not be accurate, specially if large changes happened to the table and automatic sampling didn't kick-off yet. You can force statistics gathering with ANALYZE TABLE command, but it can never guarantee you 100% accuracy
Everything is related to the logical size, not to physical size, or size on disk. If you were to delete 7/8ths of that table, the disk used on disk wouldn't shrink in the case of InnoDB, because disk defragmentation wouldn't happen- space is freed for other rows to be inserted, but it wouldn't be released to the filesystem. To reduce the on-disk size, you would need to recreate the table with OPTIMIZE TABLE, a more time consuming and blocking operations.
If disk space is a concern, consider using compression


Say you aggregate on the LoginName column for instance. Then you get one blue section for each login name, and it will show you also how many events you have for that login name. To see the actual events, for that login, you look at the white rows under that blue row. In your example, you have aggregated over duration, and in the pic you included you have no two events with the same duration. Aggregating over something which has very few duplicates isn't very meaningful. You can think of aggregation such as a group by with a COUNT(*), that is what you see in the blue section for each value in the columns that you "grouped/aggregated" over.


Sometimes the updated rows are not locked within instead of update trigger

When the update statement qualifies for a trivial plan, the optimizer rule that expands the instead-of trigger part of the statement (ExpandInsteadOfTriggerUpd) includes the part of the plan that reads from the base table. This rewrite includes adding an UPDLOCK hint to the base read. As usual, the UPDLOCK hint means that update locks are taken and held to the end of the transaction.

When the statement does not qualify for a trivial plan, the ExpandInsteadOfTriggerUpd rule only rewrites the write-cursor portion of the plan, leaving the base table reads untouched - no UPDLOCK hint is added.

My guess is that this trivial plan behaviour exists to avoid a deadlock scenario.