could sql create view with fix group even without data

create table t1(day varchar2(20),qty number);

create table t2(day varchar2(20));

insert into t1 values('Sunday',10)

insert into t1 values('Monday', 5);

insert into t1 values('Tuesday', 20)

insert into t1 values('Sunday',17)

insert into t2 select trim(to_char(sysdate+level,'Day')) WeekDay from dual connect by level<=7;

select t2.day, nvl(sum(t1.qty),0) Sum_qty from t1 right outer join t2 on(t1.day=t2.day) group by t2.day;

DAY       | SUM_QTY
:-------- | ------:
Tuesday   |      20
Sunday    |      27
Monday    |       5
Wednesday |       0
Thursday  |       0
Saturday  |       0
Friday    |       0

OR

create view sum_table as
select t2.day,
       nvl(sum(t1.qty),0) Sum_qty
from t1
right outer join
       (select trim(to_char(sysdate+level,'Day')) Day
                from dual
                connect by level<=7)t2
on(t1.day=t2.day)
group by t2.day;

dbfiddle here


Better jsonb schema for querying in postgres

There are 2 questions here;

First one is the index structure.

You can use gin(item) here (without jsonb_path_ops). It will be just fine. You are querying  for key (id) only not the full path.
You can create an expression index (or an gin index on a generated column) for extracting id field values to an integer[] value. Most probably this index will be smaller than jsonb index (~%10 smaller). But I am not sure whether it's faster for your case.

Here is an example for second usage.

Create an immutable function.

CREATE FUNCTION to_my_int_array (jsonb) RETURNS integer[] AS
$$
  SELECT
      ARRAY(
        SELECT (jsonb_array_elements($1) ->> 'id')::integer
      );

$$
LANGUAGE sql
IMMUTABLE
RETURNS NULL ON NULL INPUT;

Create an expression index with the function above:

CREATE INDEX ON my_table USING gin (to_my_int_array (item));

Filter the query with function;

SELECT
    mt.uid,
    my_item.*
  FROM my_table mt,
  LATERAL jsonb_array_elements(mt.item) ae,
  LATERAL jsonb_to_record(ae) AS my_item (id int, m text, s int)
  WHERE
    to_my_int_array(mt.item) @> '{1}'::integer[];

Second question is the output. The query above also shows output for version 11. After version 12, you can simply use the query below;

SELECT
    uid,
    my_item.*
  FROM my_table mt,
  LATERAL jsonb_to_record(jsonb_path_query_first(mt.item, '$[*] ? (@.id == 1) '))
    AS my_item(id int, m text, s int)
  WHERE
    mt.item @> '[{"id": 1}]';

| uid | id  | m   | s   |
| --- | --- | --- | --- |
| 1   | 1   | 123 | 1   |
| 2   | 1   | yes | 2   |
| 3   | 1   | qa  | 1   |


Querying MySQL InnoDB to find if the Record is in its Buffer Pool

There is no built-in mechanism to query the MySQL cache without causing MySQL to bring the row into memory from disk if it is not already in memory.

You might want to consider using a caching layer between your website and the database.  Stack Exchange uses Redis; you may want to evaluate that.


Performance of mysql equi-join observed in HDD and SSD

I am excited to hear reliable comparisons between HDD and SSD, but first...

Here are some of the variables you have not accounted for.  (Or maybe you did, but failed to state.)

InnoDB?
One says "Using index", meaning that the processing happened only in the index's BTree ("Covering" index); the other apparently had to bounce between the secondary index and the data.  This could make a significant difference.
Table sizes?
Was the cache (buffer_pool) cold?  Please restart mysqld before each test.
Same CPU, since you want to focus on the I/O, not the CPU time.
If it was the same server, which drive was c on?
SHOW CREATE TABLE -- there could be subtle issues.
MySQL version.
Are the indexes aligned with the data?  For example:  AUTO_INCREMENT and TIMESTAMP might be virtually in lock-step.  But UUID is very random.

8x and 5.5x seem to be reasonable.


Very large SQL Server 2016 result sets (over 75 GB) to a CSV file?

I, too, used to have problems exporting large result sets (7 - 8 GB) to delimited files. Neither SQLCMD nor BCP nor SSIS could handle dynamic result sets, dynamic text-qualification, adding a column header row, etc. So, I built my own tool to handle this. It currently exits as the DB_BulkExport Stored Procedure in the SQL# SQLCLR library (that I wrote), though the plan is to break it out into a stand-alone export utility and expand on the features.

Please note that the DB_BulkExport Stored Procedure is only available in the Full (paid) version of SQL# (i.e. it is not in the Free version), but I am not aware of any free utility to do this type of thing (hence why I wrote my own).

Regarding the export of large result sets: I so far have not run into problems with memory since it writes each row out to the file as it is read from the result set.

If you only need this export for a single table / query that doesn't really change in terms of structure / schema, then it might be best to write a small, specialized app yourself, either in .NET or PowerShell. The tricky part is in handling dynamic requirements. But if you know what the columns are named and the datatypes, etc, then it should be a simple-enough matter of opening the output file, executing the query, and then for every row in the SqlDataReader just String.Concat everything together, including text-qualification where needed and applying appropriate Format specifiers when needed (i.e. Date(Time) values).


You must refactor the query to perform WHERE, JOIN and GROUP BY clauses IN THAT EXACT ORDER !!!

Here is the refactored query :

select
    sq.search_id,
    count(1) search_id_count
from
    (select search_id from search where search_id > 47000) sq
    inner join
    (select search_id from search_hit where search_id > 47000) ssh
    using (search_id)
group by
    sq.search_id;

Running OPTIMIZE TABLE (reduces table fragmentation and recreate indexes) may not be necessary unless you do heavy INSERTs, UPDATEs, DELETEs. I'll say it is optional.

Give it a Try !!!


Is it useful to index a column if it is already the first column in a composite index?

Given these queries, is it useful to create a separate (non-clustered) index on just the BillID column?

First, note that an index on BillId will include TaxCode.  The clustered index key is the row loacator for this table, so every index must contain it.  And for non-unique indexes, the clustered index key columns are added as index key columns to make the index physically unique.

It just won't include the rest of the columns on the leaf pages.  Such an index is sometimes useful, especially if you have a wide table and no other indexes.

For instance a COUNT(*) or paging query could use this narrow unique index instead of having to scan the whole clustered index.

But for the question as stated, no, maintaining a second copy of the key index without the two non-key columns is unlikely to be useful.


Table column length is fine in MySQL 5.5 and 5.6, but upgrading to 5.7 results in too many columns

A guess...  See what you get for

SHOW VARIABLES LIKE '%engine%';

This may indicate that you are not really using MyISAM everywhere.

In the future, MyISAM is likely to go away.  So, start thinking about how to deal with InnoDB.

TINYTEXT is perhaps always worse than a suitable VARCHAR.

More than a hundred columns in a single table is usually a bad schema design.  Think about how your table can be logically broken up.

Don't splay an array across columns.


Good way to call multiple SQL Server Agent jobs sequentially from one main job?

SQL Sentry has a facility built exactly for this: to chain jobs and arrange them in various workflow orders.

I started using SQL Sentry years ago, before I ever joined the company, to do exactly this. What I wanted was a way to start a restore job on our test server immediately after the backup on production had finished.

What I had originally implemented was just a substantial buffer between the backup job start time and the restore start time. This wasn't exactly foolproof; since backup times varied the buffer often left us with wasted time where a restore hadn't started even though it could have. And occasionally the buffer wasn't enough.

What I implemented next was similar to what you have - I wrote a job on the test server that started shortly after the scheduled backup, and kept polling to see when the job was finished. That was later amended to just have a second step in the backup job that updated a table on the test server. Not really much different, except the restore job only had to watch a table locally instead of monitoring the job history remotely. Thinking back this could have been a trigger on that table that called sp_start_job so the job didn't have to run every n minutes (or be scheduled at all).

The final solution was to chain jobs together ... when the backup on server A finishes, Event Manager starts the restore job on server B. And if there was a third job, and a fourth job, or conditional logic based on what to do when a job fails vs. succeeds, etc., this can all be accounted for. The workflow designer will remind you quite a bit of SSIS:

The underlying mechanics of what I'm describing is not rocket surgery, of course. You could write this type of chaining wrapper yourself if you sat down and did it. Just providing you one alternative where you don't have to.


High PAGELATCH_* and WRITELOG waits. Are they related?

However my question is when a new record is inserted, does SQL Server take an exclusive PAGELATCH_EX on a buffer page, insert the record to the buffer page, write the record to the transaction log and then release the exclusive PAGELATCH_EX

You must note that latch only protects physical integrity of page while it is in memory so latch would be taken when page is in memory. Suppose a record is being inserted and for that page needs to be fetched. First, page would be locked and brought into memory then it would be latched and information would be written. The process after this would be

Generate log record
Update page LSN to match that of the log record
Change Data (dirty the page)
Release Latch
Commit Transaction Starting
FlushToLSN of Commit
Release locks
Commit Transaction Complete

For more details and explanation about above steps please read Bob Dorr's I/O presentation blog

Pagelatch* waits are non I/O waits and I have seen most of the time these wait are prominent because of allocation contention. My hunch is that it has to do something with how tempdb is configured. So how is your tempdb configured ?, how much tempdb data files are present ? Make sure they have same autogrowth and same size. When new page is created system pages like GAM,SGAM and PFS pages needs to be updated or are accessed and when SQL Server finds contention in accessing these pages such wait come into picture.


how to get a sorted result from mysql query?

I suppose you could do something like this from the command line/phpmyadmin:

Assuming the following table:

mysql> SELECT * FROM test;
+----+----------+
| id | cat_type |
+----+----------+
|  1 | free     |
|  2 | free     |
|  3 | free     |
|  4 | free     |
|  5 | paid     |
|  6 | paid     |
+----+----------+
6 rows in set (0.00 sec)

Make your custom ordering by selecting twice the table and using a variable as a counter:

mysql> SET @i := 0;
SET @j := 0.5;
SELECT id, cat_type FROM
(SELECT @i := @i + 1 as ordering, id, cat_type FROM test WHERE cat_type = 'free'
 UNION
 SELECT @j := @j + 2 as ordering, id, cat_type FROM test WHERE cat_type = 'paid')
AS base_table
ORDER BY ordering;
Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

+----+----------+
| id | cat_type |
+----+----------+
|  1 | free     |
|  2 | free     |
|  5 | paid     |
|  3 | free     |
|  4 | free     |
|  6 | paid     |
+----+----------+
6 rows in set (0.00 sec)

This is technically incorrect, as it could explore the original tables not in id order, but assuming you are using InnoDB, a full table scan in index order should be performed.

Also, this is terribly unoptimized:

+----+-------------------+------------+------+---------------+------+---------+------+------+-----------------+
| id | select_type       | table      | type | possible_keys | key  | key_len | ref  | rows | Extra           |
+----+-------------------+------------+------+---------------+------+---------+------+------+-----------------+
|  1 | PRIMARY           | <derived2> | ALL  | NULL          | NULL | NULL    | NULL |   12 | Using filesort  |
|  2 | DERIVED           | test       | ALL  | NULL          | NULL | NULL    | NULL |    6 | Using where     |
|  3 | UNCACHEABLE UNION | test       | ALL  | NULL          | NULL | NULL    | NULL |    6 | Using where     |
| NULL | UNION RESULT      | <union2,3> | ALL  | NULL          | NULL | NULL    | NULL | NULL | Using temporary |
+----+-------------------+------------+------+---------------+------+---------+------+------+-----------------+
4 rows in set (0.00 sec)

To the point that if you wanted to do this in a large table, doing it programatically or using cursors would be a better idea.


Finding the reason of the lost data on oracle database

Use Oracle's "tool" called Log miner.

restore some older backup of your database
identify ROWID (physical location) of record which was accidentally deleted
restore all archived redo logs
use log miner to get the SQL which deleted the row
log miner will also tell you culprits program name and hostname


Up until 9.4

You can go and ask the system catalogs (pg_type, to be precise):

SELECT rolname
  FROM pg_type t
  JOIN pg_authid r ON typowner = r.oid
 WHERE typname = 'bla';

 usename
─────────
 dezso

From PostgreSQL 9.5 on

From this version on, there is a new object identifier type called regrole, and it makes the query a bit simpler:

SELECT typowner::regrole
  FROM pg_type t
 WHERE typname = 'bla';

But there is more to it - the new 9.5 version of psql already displays the owner when using \dT+:

      Schema       | Name | Internal name | Size | Elements |  Owner   | ...
-------------------+------+---------------+------+----------+----------+ ...
 zel_api_r14_00_09 | bla  | bla           | 4    | a        | postgres | ...


From at least SQL Server 2008 R2 SQL Server comes with an understanding of HTML.

http://msdn.microsoft.com/en-us/library/ms142499.aspx says: "
SQL Server installs its own XML and HTML filters. In addition, any filters for Microsoft proprietary formats (.doc, .xdoc, .ppt and so on) that are already installed on the operating system are also loaded by  SQL Server."

You mention umlauts, but not what you want to do with them.  Umlauts and other characters that may seem unusual to English readers, can be managed by choosing the proper collation for the Index. Also the chosen Word Breaker (Neutral, English, French, etc) will affect your results.


Network line switch-over in Oracle Data Guard

Sure, you can configure alternate locations.

You may have this now on the primary database:

log_archive_dest_1='location=use_db_recovery_file_dest' -- FRA for local archivelogs
log_archive_dest_2='service=standby_TNS_wired_network ...'

And you can configure this:

log_archive_dest_1='location=use_db_recovery_file_dest' -- FRA for local archivelogs
log_archive_dest_2='service=standby_TNS_wired_network ... alternate=log_archive_dest_3'
log_archive_dest_3='service=standby_TNS_wireless_network ... alternate=log_archive_dest_2'
log_archive_dest_state_2=enable
log_archive_dest_state_3=alternate

Example 17-3 Defining an Alternate Oracle Net Service Name to the Same Standby Database

LOG_ARCHIVE_DEST_2='SERVICE=stby1_path1 MAX_FAILURE=1 ALTERNATE=LOG_ARCHIVE_DEST_3 DB_UNIQUE_NAME=stdby1'
LOG_ARCHIVE_DEST_STATE_2=ENABLE
LOG_ARCHIVE_DEST_STATE_3=ALTERNATE
LOG_ARCHIVE_DEST_3='SERVICE=stby1_path2 DB_UNIQUE_NAME=stdby1'


As for question 1, I don't think there's any way to know who enabled replication once you've torn it down. Even if it was still up, it'd be difficult (there is a login_name column in syssubscriptions that you might be able to use, for instance).

Speaking to point the second, the documentation for sp_addpublication says you need either sysadmin or db_owner to enable replication.


How to get a column of the select to be "cumulative" (like in excel)?

If I understand you correctly, you want to have another field in your query which shows the total count for all posts, regardless of dates then.
You could achieve this like so :

 SELECT DATE(post_date) AS date,
           concat(post_title) titles, COUNT(*) AS count,
           (select count(*) from wp_posts
            WHERE post_status = 'publish'
            AND post_parent = 0
            AND post_type = 'post') as CumulativeCount
    FROM  wp_posts
    WHERE post_status = 'publish'
    AND post_parent = 0
    AND post_type = 'post'
    GROUP BY DATE(post_date)
    ORDER BY DATE(post_date)


How to write sql statement for following scenario?

You need to use three things

GROUP BY
GROUP_CONCAT
DISTINCT within GROUP_CONCAT

Here is such a query

Select room_no, group_concat(distinct category) categories
from help_desk where room_no in (133,120) group by room_no;


Postgres Streaming Replication: How to re-sync data in master (with standby) after failover

During the process there is a good chance that the process fails requesting wall files that have never existed in the old time line. That does not help quickly restoring the requested redundancy.
The safest method is to just create a fresh new replica.
So if your requirement is to always have at least one valid replica, make sure you have two of them. This gives you time to rebuild the former primary as a new replica and still keep operating at your requested redundancy level.
There was a bug that is solved in 9.5.22 that caused premature recycling of wall files during crash recovery.


Below are what I did to speed up running the scripts with SQLCMD as the outcome of the reading the nice article AmmaR referred me to:

For each script, I added OPTION (RECOMPILE), so SQL would not need to have a query plan, it is compiling and running the script every time. It is especially important when we have used variables in the script. The variables are unknown to SQL until run time. For example in the code below, the randomId is calculated based on what RAND() function returns, so SQL Server has no idea what it would be beforehand. Adding OPTION (RECOMPILE) helped to run this script faster, even though I was running it using SQLCMD.

`

SET @i = 1

WHILE @i <= @Address_Row_Count
BEGIN
    SELECT @RandomId = ROUND(((@Upper_Town - @Lower -1) * RAND() + @Lower), 0)

    UPDATE a
    SET
        a.TownOrCity =
                    (
                        SELECT
                            Town
                        FROM
                            @TownTble aa
                        WHERE
                            aa.Id = @RandomId
                    )
    FROM
        Addresses a
    WHERE
        a.Id = @i
        AND a.PersonId IS NOT NULL OPTION (RECOMPILE)

    SET @i = @i + 1
END `

Thanks for your help!


what happens when I try to insert data into mysql while table is locked?

The "normal" result is the INSERT query waiting until the lock is released. But there are some limits and special cases (you wrote about locking TABLES so MyISAM or explicit LOCK TABLES statement)

the waiting can timeout (lock_wait_timeout)
Concurrent Inserts are possible when you just append to the end of the table, in that case no waiting happens and INSERTs run in parallel. That needs the locking to originate implicitly from other INSERT/SELECT etc or explicitly from READ LOCAL table lock. If you explicitly lock the table without the LOCAL modifier, this feature cannot be used according to the manual.

But nowadays you should mostly use the InnoDB engine and transaction. In that case there are usually no explicit table locks used. There still can be metadata locks. But according to the manual InnoDB will not escalate row locks to table locks, instead using Multiple granularity locking according to https://www.percona.com/blog/2010/06/08/table-locks-in-show-innodb-status/ .
With row locks many operations can run in parallel and inserts should only wait if there is a gap lock on the gap into which the insert should put the inserted row.


Recovery time effects of Availability Groups

It is very clear that the synchronous secondary replica is not able to
  keep-up with the load primary is generating (even though both machines
  are of same configuration). And the side effect of this is the log on
  primary will keep on growing (even we take log backups it can't
  truncate the log)

In synchronous mirroring/alwayson the secondary must acknowledge that it hardened (written to disk) the log before the primary's commit is allowed to continue. The primary is then free to truncate/reuse it's own log as it needs. If you cannot truncate the primary it means the secondary is not synchronized. This would point toward a problem with the ability to ship the log to the secondary and write it to disk. The two obvious bottlenecks would be the network speed and the secondary's log file storage. Both are easy to measure and diagnose, as they're straight forward USE (utilization, saturation, errors) OS level metrics.

Note that I never mentioned recovery (secondary's redo). If the problem is indeed that the secondary is not able to synchronize then redo is not playing any real role here.


Upgrade a database from SQL Server 2000 to SQL Server 2014

You cannot restore a SQL Server 2000 database onto a SQL Server 2014 instance. You need to restore to SQL Server 2008 or 2008 R2 instance first, then take a backup of that database which you can then restore to 2014.

There a lot of features from SQL Server 2000 that have been discontinued in SQL Server 2014.  See this list for the details about what features have been discontinued, and what to use as replacements.

Unfortunately, you have no choice but to re-code queries that use the ancient =* or *= join syntax.

SQLMag has a great article on how to do the migration.


Try the following code:

CREATE TABLE #Names
(
    [Type] VARCHAR(50),
    ColNum SMALLINT,
    ColName VARCHAR(50),
    ColDataType VARCHAR(20)
)

INSERT  INTO #Names VALUES
('Customer', 1, 'CustomerID', 'INT'),
('Customer', 2, 'CustomerName', 'VARCHAR(50)'),
('Customer', 3, 'CustomerJoinDate', 'DATE'),
('Customer', 4, 'CustomerBirthDate', 'DATE'),
('Account', 1, 'AccountID', 'INT'),
('Account', 2, 'AccountName', 'VARCHAR(50)'),
('Account', 3, 'AccountOpenDate', 'DATE'),
('CustomerAccount', 1, 'CustomerID', 'INT'),
('CustomerAccount', 2, 'AccountID', 'INT'),
('CustomerAccount', 3, 'RelationshipSequence', 'TINYINT')

CREATE TABLE #Data
(
    [Type] VARCHAR(50),
    Col1 VARCHAR(50),
    Col2 VARCHAR(50),
    Col3 VARCHAR(50),
    Col4 VARCHAR(50),
    Col5 VARCHAR(50),
    Col6 VARCHAR(50),
    Col7 VARCHAR(50)
)

INSERT  INTO #Data VALUES
('Customer', '1', 'Mr John Smith', '2005-05-20', '1980-11-15', NULL, NULL, NULL),
('Customer', '2', 'Mrs Hayley Jones', '2009-10-10', '1973-04-03', NULL, NULL, NULL),
('Customer', '3', 'ACME Manufacturing Ltd', '2012-12-01', NULL, NULL, NULL, NULL),
('Customer', '4', 'Mr Michael Crocker', '2014-01-13', '1957-01-23', NULL, NULL, NULL),
('Account', '1', 'Smith-Jones Cheque Acct', '2005-05-25', NULL, NULL, NULL, NULL),
('Account', '2', 'ACME Business Acct', '2012-12-01', NULL, NULL, NULL, NULL),
('Account', '3', 'ACME Social Club', '2013-02-10', NULL, NULL, NULL, NULL),
('Account', '4', 'Crocker Tipping Fund', '2014-01-14', NULL, NULL, NULL, NULL),
('CustomerAccount', '1', '1', '1', NULL, NULL, NULL, NULL),
('CustomerAccount', '2', '1', '2', NULL, NULL, NULL, NULL),
('CustomerAccount', '2', '3', '2', NULL, NULL, NULL, NULL),
('CustomerAccount', '3', '2', '1', NULL, NULL, NULL, NULL),
('CustomerAccount', '3', '3', '1', NULL, NULL, NULL, NULL),
('CustomerAccount', '4', '2', '2', NULL, NULL, NULL, NULL),
('CustomerAccount', '4', '4', '1', NULL, NULL, NULL, NULL)

DECLARE @Type VARCHAR(50) = 'Account' -- Or Customer, or CustomerAccount

DECLARE @SQLText NVARCHAR(MAX) = ''

SELECT  @SQLText += 'SELECT '

SELECT  @SQLText += ( -- Add in column list, with dynamic column names.
                SELECT  'CONVERT(' + ColDataType + ', Col' + CONVERT(VARCHAR, ColNum) + ') AS [' + ColName + '],'
                FROM    #Names
                WHERE   [Type] = @Type FOR XML PATH('')
            )

SELECT  @SQLText = LEFT(@SQLText, LEN(@SQLText) - 1) + ' ' -- Remove trailing comma

SELECT  @SQLText += 'FROM #Data WHERE [Type] = ''' + @Type + ''''

PRINT   @SQLText
EXEC    sp_executesql @SQLText

This returns the SELECT statement: SELECT CONVERT(INT, Col1) AS [AccountID],CONVERT(VARCHAR(50), Col2) AS [AccountName],CONVERT(DATE, Col3) AS [AccountOpenDate] FROM #Data WHERE [Type] = 'Account'


Optimise select distinct from large table

If you want this query gangsta fast create an indexed view that groups on [OpenTimeUtc], [SubscriberId]. That view will serve the date filter perfectly and it will pre-calculate the distinct as well. The query will result in a plan that does an index range scan on the view. Not possible to do any better than that.


In the c# code you posted the connection is only closed if it is 'Open', if the state was 'Connecting', 'Broken', 'Executing' or 'Fetching' it would be left open. It is standard practice in c# wrap your db connection in a using statement, this will make sure the connection is always properly disposed of, without the need to call connection.Close.

Here is an example where a stored proc is called from a connection with a using:

using (SqlConnection connection = new SqlConnection(connectionString))
{
    try
    {
        connection.Open();
        SqlCommand command = new SqlCommand("StoredProcName", connection);
        command.CommandType = CommandType.StoredProcedure;
        command.Parameters.Add(new SqlParameter("@SomeID", someID));
        command.ExecuteNonQuery();
    }
    catch (Exception)
    {
        /*Handle error*/
    }
}


You quoted your column aliases:

SELECT
tb_dim_equipe.no_equipe "EQUIPE",
tb_dim_profissional.no_profissional "PROFISSIONAL",
no_cidadao "CIDADÃO",
a.nu_cns "CNS",
sum(...) as "TOTAL"

Postgres, unlike standard-compliant SQL databases, normalizes unquoted identifiers by converting them to lower case, such that the reference to c.TOTAL is equivalent to c.total. However, quoted identifiers like as "TOTAL" are not normalized and remain known to Postgres exactly as they are written.

You should either drop quotes in aliases (which I would recommend):

SELECT
tb_dim_equipe.no_equipe AS equipe,
tb_dim_profissional.no_profissional AS professional,
no_cidadao AS cidadao,
a.nu_cns AS cns,
sum(...) AS total

or always reference quoted identifiers as quoted identifiers:

SELECT sum(c."TOTAL") from ... AS c


Service Broker activation procedure in C# with external dependencies

I suggest external activation rather than SQLCLR. That will provide capabilities like log4net that aren't easily doable in SQLCLR.

The trigger would begin a conversation and sends the project closure message.  The C# app would RECEIVE the closure message from the queue, do the SharePoint stuff, and end the conversation.  You'll also need to close the initiator side of the conversation to avoid endpoint leakage. Below is a single-queue example with the same SB service acting in both initiator and target roles.  Note that your C# will receive EndDialog messages for both sides of the conversation with this method.

CREATE QUEUE dbo.ProjectClosureQueue;
CREATE MESSAGE TYPE ProjectClosureRequest
    VALIDATION = WELL_FORMED_XML;
CREATE CONTRACT ProjectClosureContract (
      ProjectClosureRequest SENT BY INITIATOR
    );
CREATE SERVICE ProjectClosureService
    ON QUEUE dbo.ProjectClosureQueue ([ProjectClosureContract]);
GO

Trigger code snippet:

DECLARE @ProjectClosureConversationHandle  uniqueidentifier;
BEGIN DIALOG CONVERSATION @ProjectClosureConversationHandle
    FROM SERVICE ProjectClosureService
    TO SERVICE 'ProjectClosureService', 'CURRENT DATABASE'
    ON CONTRACT ProjectClosureContract
    WITH ENCRYPTION = OFF;
SELECT @ProjectClosureConversationHandle;

SEND ON CONVERSATION @ProjectClosureConversationHandle
    MESSAGE TYPE ProjectClosureRequest (@ProjectClosureXmlMessage);

Get next message in C# app:

WAITFOR (
    RECEIVE TOP (1)
           conversation_handle
           CAST(message_body AS xml)
        , message_type_name
    FROM dbo.ProjectClosureQueue
    ), TIMEOUT 10000;

If no message received, repeat receive.

If message_type is ProjectCloseRequest, do SharePoint stuff

If message_type is not ProjectCloseRequest or '"http://schemas.microsoft.com/SQL/ServiceBroker/EndDialog', log error

Finally, END CONVERSATION


Should I disable the guest user on system databases

No you shouldn't disable the guest account on the system databases.  You also shouldn't disable it on the user databases.  This will stop anyone from using SQL Server Management Studio to connect to the instance.  As long as you haven't granted any permissions in the user databases to public, and you haven't changed any of the default permissions in the databases, then the guest role poses no security risk.

Also your security team should stop making recommendations about how to secure SQL Server  until they've learned how SQL Server and it's tools work.


Error parsing DDL for schema.table in mysql workbench

There were no "fast" index ALTERs in 5.5.  Check out Percona's pt-online-schema-change to drop and re-add the index (without the comment).  It should work with only a small downtime.

A newer product is gh-ost, but that depends on the binlogs, and may not work for such an old version.


Pretty straightforward:

select e.*
from EmployeeFile e
inner join
(
  select FNAME, LNAME, DOB
  from OriginalData
  group by FNAME, LNAME, DOB
  having count(1) = 1
) o
on (e.FNAME, e.LNAME, e.DOB) = (o.FNAME, o.LNAME, o.DOB)


Best data modelling approach to handle redundant foreign keys in a database about Surveys, Questions and Responses

As per my understanding of your specifications, your business environment involves a conceptual-level ternary relationship. In this regard, you need to define:

the relationship  (or association) type between the entity types Person and Survey;
the relationship type between Survey and Question;
the relationship type that establishes the connection between the two aforesaid relationship types and, as a consequence, between Person, Survey and Question, i.e., Response (a shorter name that simplifies interpretation, from my point of view).

So, I consider that you are on the right track with your Approach 1, although it requires some small (yet important) refinements in order to make it more accurate. I will detail such refinements and other relevant considerations in the following sections.
Business rules
Let us expand the applicable business rules a bit and reformulate them in the following way:

A Person registers in zero-one-or-many Surveys
A Survey gets the registration of zero-one-or-many Persons
A Survey is integrated by one-to-many Questions
A Question integrates zero-one-or-many Surveys
A Question receives zero-one-or-many Responses
A Response is provided by exactly-one Person in the context of exactly-one Survey

Expository IDEF1X diagram
Then, I have created the IDEF1Xa diagram that is presented in Figure 1, which synthesizes the business rules formulated above:

a Integration Definition for Information Modeling (IDEF1X) is a highly recommendable 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 theoretical work authored by the sole founder of the relational model, i.e., Dr. E. F. Codd and also on the entity-relationship view developed by Dr. P. P. Chen.

The PersonSurvey relationship
As I see it, the PersonSurvey relationship is required to provide a means of authorization so that a Person can take part in a given Survey. In this way, once a certain Person has been registered in a specific Survey, he or she is authorized to provide Responses to the Questions that integrate the respective Survey.
The SurveyQuestion relationship
I assume that the property (or attribute) called suvery_question.question_number in your diagram is used to represent the Order of presentation of a given Question instance with respect to a particular Survey. As you can see, I have denoted such property as SurveyQuestion.PresentationOrder, and I think that you should prevent that (i) two or more Question.QuestionNumber values share (ii) the same PresentationOrder value in (iii) the same SurveyQuestion occurrence.
To portray that need, I have included a composite ALTERNATE KEY (AK) in the box representing this entity type, which is comprised of the combination of properties (SurveyNumber, QuestionNumber, PresentationOrder). As you are well aware, a composite AK can be declared in a logical DDL design with the aid of a multi-column UNIQUE constraint (as I exemplified in the SurveyQuestion table that is part of the expository DDL layout expounded a few sections below).
The Response entity type
Yes, with the Response entity type I am depicting a relationship between two other relationships; it may seem awkward at first glance but there is nothing wrong with this approach, as long as it (a) represents the features of the business context of interest accurately and (b) is represented properly in a logical-level layout.
Yes, you are totally correct, it would be an error to portray that part of the scenario at the logical level of abstraction by means of two Response.SurveyNumber (or, say, Response.SurveyId) values referenced from two different columns in the same Response row.
Derived logical SQL-DDL layout
-- You should determine which are the most fitting
-- data types and sizes for all your table columns
-- depending on your business context characteristics.

-- As one would expect, you are free to make use of
-- your preferred (or required) naming conventions.

CREATE TABLE Person (
    PersonId        INT      NOT NULL,
    FirstName       CHAR(30) NOT NULL,
    LastName        CHAR(30) NOT NULL,
    GenderCode      CHAR(3)  NOT NULL,
    BirthDate       DATE     NOT NULL,
    CreatedDateTime DATETIME NOT NULL,
    --
    CONSTRAINT Person_PK PRIMARY KEY (PersonId),
    CONSTRAINT Person_AK UNIQUE      (
        FirstName,
        LastName,
        GenderCode,
        BirthDate
    )
);

CREATE TABLE Survey (
    SurveyNumber    INT       NOT NULL,
    Description     CHAR(255) NOT NULL,
    CreatedDateTime DATETIME  NOT NULL,
    --
    CONSTRAINT Survey_PK PRIMARY KEY (SurveyNumber),
    CONSTRAINT Survey_AK UNIQUE      (Description)
);

CREATE TABLE PersonSurvey (
    PersonId           INT      NOT NULL,
    SurveyNumber       INT      NOT NULL,
    RegisteredDateTime DATETIME NOT NULL,
    --
    CONSTRAINT PersonSurvey_PK         PRIMARY KEY (PersonId, SurveyNumber),
    CONSTRAINT PersonSurveyToPerson_FK FOREIGN KEY (PersonId)
        REFERENCES Person (PersonId),
    CONSTRAINT PersonSurveyToSurvey_FK FOREIGN KEY (SurveyNumber)
        REFERENCES Survey (SurveyNumber)
);

CREATE TABLE Question (
    QuestionNumber  INT       NOT NULL,
    Wording         CHAR(255) NOT NULL,
    CreatedDateTime DATETIME  NOT NULL,
    --
    CONSTRAINT Question_PK PRIMARY KEY (QuestionNumber),
    CONSTRAINT Question_AK UNIQUE      (Wording)
);

CREATE TABLE SurveyQuestion (
    SurveyNumber       INT      NOT NULL,
    QuestionNumber     INT      NOT NULL,
    PresentationOrder  TINYINT  NOT NULL,
    IsMandatory        BIT      NOT NULL,
    IntegratedDateTime DATETIME NOT NULL,
    --
    CONSTRAINT SurveyQuestion_PK PRIMARY KEY (SurveyNumber, QuestionNumber),
    CONSTRAINT SurveyQuestion_AK UNIQUE      (
        QuestionNumber,
        SurveyNumber,
        PresentationOrder
    ),
    CONSTRAINT SurveyQuestionToSurvey_FK   FOREIGN KEY (SurveyNumber)
        REFERENCES Survey   (SurveyNumber),
    CONSTRAINT SurveyQuestionToQuestion_FK FOREIGN KEY (QuestionNumber)
        REFERENCES Question (QuestionNumber)
);

CREATE TABLE Response (
    SurveyNumber     INT      NOT NULL,
    QuestionNumber   INT      NOT NULL,
    PersonId         INT      NOT NULL,
    Content          TEXT     NOT NULL,
    ProvidedDateTime DATETIME NOT NULL,
    --
    CONSTRAINT Response_PK                 PRIMARY KEY (SurveyNumber, QuestionNumber, PersonId),
    CONSTRAINT ResponseToPersonSurvey_FK   FOREIGN KEY (PersonId, SurveyNumber)
        REFERENCES PersonSurvey   (PersonId, SurveyNumber),
    CONSTRAINT ResponseToSurveyQuestion_FK FOREIGN KEY (SurveyNumber, QuestionNumber)
        REFERENCES SurveyQuestion (SurveyNumber, QuestionNumber)
);

Two composite FOREIGN KEYs in the Response table
This is, probably, the most important point to discuss: the references made from a given Response row to

SurveyQuestion.SurveyNumber, and
SurveyPerson.SurveyNumber

must have matching values. As far as I am concerned, the best option to enforce this condition in a declarative way is by making use of two composite FOREIGN KEYs (FKs).
As shown in the DDL design, the first FK is making a reference to the PersonSurvey table PRIMARY KEY (PK), i.e., (PersonId, SurveyNumber), and is conformed by the columns Response.PersonId and Response.SurveyNumber.
The second FK is pointing to the SurveyQuestion table PK, i.e., (SurveyNumber, QuestionNumber), and is, accordingly, made up of the columns Response.SurveyNumber and Response.QuestionNumber.
In this way, the Response.SurveyNumber column is quite instrumental since it is used as part of a FK reference in two different constraints.
With this method, one ensures database management system-guaranteed referential integrity from

(a) Response to the PersonSurvey;
(b) Response to the SurveyQuestion; and
(c) each of the tables representing an associative entity type to the tables standing for independent entity types, namely Person, Survey and Question.

Derived data to avoid update anomalies
I have noticed in your diagram two elements that I esteem are worth mentioning. These elements are related to two PersonSurvey columns that can (should) be derived.
In that regard, you can derive the PersonSurvey.IsStarted datum by querying if a given Person occurrence has provided one or more Responses to Questions that integrate an exact Survey via the SurveyQuestion table.
And you can also obtain the PersonSurvey.IsCompleted data point by determining if a given Person instance has supplied a Response to all the Questions that cointain a value of 'TRUE' in the IsMandatory column in a specific SurveyQuestion row.
By way of the derivation of these values, you are preventing some update anomalies that would have eventually arisen in case you had kept such values in the SurveyQuestion column.
Important consideration
As @Dave rightly points out in his comment, if you face a future requirement demanding the management of different kinds of responses that imply managing dates, numeric values, multiple choice, and another possible aspects, you will have to extend this database layout.


LIKE with a wildcard at the start of the expression can't use a btree index. In order to make that query use an index, you need to create a trigram index for which you need to install the pg_trgm extension first.

create index on test_table using gin (nickname gin_trgm_ops);


Sort table so that linked rows show up as subsets in overall alpha sort

So - making the following assumptions:

memKey is the same for a family head and all subsidiary memberships for the family;
There is no direct pointer to the family head record in a subsidiary member row.
isFamHead acts as a Boolean, where "1" means the row represents a family head, and "0" means it doesn't.
There is only one row for any given memKey where isFamHead = 1

From the comments: Members with "single" memberships (1 person, no family) will have isFam = 0. Members who are part of a family membership will have isFam = 1; only the primary member from the family will have isFamHead = 1, it will be 0 for the rest.

Something like this should work. Column and table names are made up, of course, since none were provided except for the three relevant columns.

SELECT CASE WHEN `isFamHead` = 1 OR `isFam` = 0 THEN CONCAT(lastname,', ',firstname) ELSE '' END
       as FamilyHead
      ,CASE WHEN `isFamHead` = 0 AND `isFam` = 1 THEN '' ELSE CONCAT(lastname,', ',firstname) END
       as FamilyMember
      ,'blah blah' as Additional_Columns
  FROM (
        SELECT fh.lastname as fh_lastname
              ,fh.firstname as fh_firstname
              ,m.*
          FROM member m
                 INNER JOIN member fh ON (m.memKey = fh.memKey AND fh.isFamHead = 1)
       ) sq
 ORDER BY fh_lastname, fh_firstname, memKey, isFamHead DESC, lastname, firstname;

Our subquery adds the first and last name of the isFamHead row for the current row's memKey to the current row's data.

Then, we sort by:
 - the family head's last and first names
 - memKey (just in case there are two family heads with the same name - this pulls each actual family group together)
 - isFamHead in descending order - this makes the family head the first row of each family group
 - finally, the members' last and first names.

If we have the following families:

John Smith (head), Jane Levy, Paul Smith-Levy, Frank Smith (memKey = 10000)
John Smith (head), Patty Smith, Jack Smith, Alvin C Monk (memKey = 10001)
Adrian Monk (head), Trudy Monk, Ambrose Monk
Steve Rogers (a single member)

This should give us the following output:

Family Head          Family Member
------------------   -------------------
Monk, Adrian
                     Monk, Ambrose
                     Monk, Trudy
Rogers, Steve
Smith, John
                     Levy, Jane
                     Smith, Frank
                     Smith-Levy, Paul
Smith, John
                     Monk, Alvin C
                     Smith, Jack
                     Smith, Patty

NOTE: code is untested.


How to log error details when using using try/catch for dynamic SQL backup commands

When BACKUP DATABASE generates an error, it actually generates two. Unfortunately TRY/CATCH is not capable of capturing the first error; it only captures the second error.

I suspect your best bet to capture the real reason behind a failed backup is to automate your backups through SQLCMD (with -o to send output to a file), SSIS, C#, PowerShell etc. All of which will give you much greater control over capturing all of the errors.

The SO answer in the comment suggests using DBCC OUTPUTBUFFER - while it's possible, this does not seem like child's play at all. Feel free to have fun with this procedure from Erland Sommarskog's site, but this still doesn't seem to work well in combination with TRY/CATCH.

The only way I seemed to be able to capture the error message with spGET_LastErrorMessage is if the actual error does get thrown. If you wrap it in a TRY/CATCH the error gets swallowed and the stored procedure does nothing:

BEGIN TRY
  EXEC sp_executesql N'backup that fails...';
END TRY
BEGIN CATCH
  EXEC dbo.spGet_LastErrorMessage;
END CATCH

In SQL Server < 2012 you can't re-raise the error yourself, but you can in SQL Server 2012 and newer. So these two variations work:

CREATE PROCEDURE dbo.dothebackup
AS
BEGIN
  SET NOCOUNT ON;
  EXEC sp_executesql N'backup that fails...';
END
GO

EXEC dbo.dothebackup;
EXEC dbo.spGET_LastErrorMessage;

Or in 2012 and above, this works, but to a large degree defeats the purpose of TRY/CATCH, since the original error still gets thrown:

CREATE PROCEDURE dbo.dothebackup2
AS
BEGIN
  SET NOCOUNT ON;
  BEGIN TRY
    EXEC sp_executesql N'backup that fails...';
  END TRY
  BEGIN CATCH
    THROW;
  END CATCH
END
GO

EXEC dbo.dothebackup2;
EXEC dbo.spGET_LastErrorMessage;

In both of these cases, the error is still thrown to the client, of course. So if you're using TRY/CATCH to avoid that, unless there is some loophole I'm not thinking of, I'm afraid you'll have to make a choice... either give the user the error and be able to capture details about it, or suppress both the error and the actual reason.


Will the job schedule obey Daylight Saving rules?

If you want the job to follow DST rules then you need to use a named timezone that follows the DST rules you want to use.

Yes, as you have provided the named time zone in your start date value.

Reference:DBMS_SCHEDULER or DBMS_JOB And DST / Timezones Explained (Doc ID 467722.1)


How are database backups conducted using SQL Server VSS Writer Service

You will need to identify the Backup strategy which would be using the VSS Writers for taking backups/Restore.

In our environment we had Netbackup and HPDP (Data Protector). While for Netbackup the location would be the master media location ,for HPDP it used to be similar to what you have {922DDBA9-0686-46B8-94BC-XXXXXXXXXX}.

HPDP uses VSS Writers for the backups and it is what was in our case. It can differ in your situation but there is some backup agent which is takin it.


how to resolve mssqlsystemresource.mdf corruption?

Error message says mssqlsystemresource is not found.,you can try copying  resource database mdf and ldf to its place from another sql server instance.

Source version from which you will copy resource database mdf and ldf should be same as destination version

Below link may help you in knowing how to check resource database version and location of resource database
http://blog.sqlauthority.com/2010/01/31/sql-server-location-of-resource-database-in-sql-server-editions/

always its good to find out why all of a sudden this happened and add these files to as part of DR solution.


Changing Key (PK, FK) properties in DROP/Add column statements

You need to drop the FKs that reference it, then drop the PK and re-create it, then re-create the FK. There's no magic syntax for this, but you can get some ideas here for how to generate the drop/create scripts for the FKs:

Why dropping all foreign keys from a script is not working?

  would I get a warning if I am (unknowingly, of course) violating some constraints in the process, i.e., if I forget to drop some referenced table? Just curious as to how SQL server handles integrity/constraint violations.

You won't be able to drop the PK while any FK still references it. And of course you won't be able to re-create the FK until those other tables also have a column that can fill in for the new column you're adding to the PK.


Ways to speed up IN queries under PostgreSQL

IN() using many parameters will result in many cases in a sequential table scan. That might be slow, depending on table size and speed of your system.

Create a temporary table with all your variables and join on this table:

CREATE TEMP TABLE t AS
  SELECT * FROM (VALUES(1),(2),(3)) x(twitter_user_id);

SELECT
  twitter_personas.*
FROM twitter_personas
  JOIN t USING(twitter_user_id);

Use EXPLAIN to see the difference between the queryplans.


Without knowing your specific errors, I dont know what the easiest approach to recovering your database is.  For example, there could be minor problems like bad settings that cause PostgreSQL to be unable to start.  I would recommend asking a new question about getting PostgreSQL to start before looking at how to recover your data.

However, I can answer what are some aspects of your question about recovering data.  I have actually never seen data corruption in PostgreSQL, and so all my experience with it is second-hand (email list threads and the like).  It is very rare, and usually related to failing hardware.  If you do have data corruption, my recommendation is to restore from backup onto a new server while you run diagnostics and determine the problem on your existing server.

When you talk about recovering from the files in pg_xlog you are talking about using write logs as incremental backups.  You still need a compatible starting point, which means effectively you are going with a base backup plus all pg_xlog segments created from that point onward.

Now, if you have no good backups, and you have a lot of important data, it may be possible to hire an expert for significant expense to recover some or all of your data.   This is not cheap.

I think your immediate steps are to step back and ask why PostgreSQL is not starting and see what you can do about that first before assuming you need to recover your data.  If you need to recover your data, you hopefully have backups.


Postgres: Put 1 database in separate HD partition?

You can create a table space that uses the second partition. Then just alter the database to use that partition.

CREATE TABLESPACE fastspace LOCATION '/mnt/ssdpart';
ALTER DATABASE yourdb SET TABLESPACE fastspace;

See also, http://www.postgresql.org/docs/9.3/interactive/manage-ag-tablespaces.html and http://www.postgresql.org/docs/9.3/interactive/sql-alterdatabase.html

Good luck!


How does PolyBase collect statistics from external tables?

Creation and maintenance of PolyBase statistics is in the hands of the DBA.
The "getting started" guide provides several steps to establish PolyBase, the last of which is CREATE STATISTICS referencing the external table.
The documentation on CREATE STATISTICS states:

Statistics for external tables
When creating external table statistics, SQL Server imports the external table into a temporary SQL Server table, and then creates the statistics. For samples statistics, only the sampled rows are imported. If you have a large external table, it will be much faster to use the default sampling instead of the full scan option.

and further down

Limitations and Restrictions
Updating statistics is not supported on external tables. To update statistics on an external table, drop and re-create the statistics.

From this I conclude it is up to the system administrator to

create appropriate statistics for the intended workload
monitor for stale statistics
implement maintenance procedures for external statistics
ensuring there is sufficient space for the "temporary table" (TempDB?)

One imagines that, as the product matures and gains traction, more features from the current data engine (auto create & auto update statistics) will be ported to PolyBase. On the flip side, do you really want to wait for results while a 5PB store is sampled?


same table on different commands (inner join)

Command 1 is the SQL-89 standard for joining between tables.

Command 2 is the correct method of writing JOINs as per the ANSI-92 SQL standard (yes, that is 92 as in 1992). Some 23 years later and people still don't want to conform to ANSI-join standards.

Theoretically, there is no difference between the two in terms of performance, it's apparently just a hard habit for people to break and some find it more readable if they're used to writing queries in that way.

There's a few more reasons for why people use the old standard as opposed to the new one in this Stack Overflow post.

I also seemingly keep referring back to Aaron Bertrand's posts (even though this one is written about SQL Server). This one may give you some more information on why you should be using the ANSI-92 Standard style joins.


Please use string delimiters around date literals, and do whatever you can to avoid ambiguous, regional formats like DD/MM/YYYY. How about:
insert into dbo.testtable ([product_name], [price], [expire_date], [expire_time])
  values ('Teapot', 10.00, '20121223', '12:35:00');

I use YYYYMMDD because, except with DATE, YYYY-MM-DD can be misinterpreted. With DATETIME, for example:
SET LANGUAGE FRENCH;
DECLARE @d DATETIME = '2012-12-23';

Result:

Msg 242, Level 16, State 3
La conversion d'un type de données varchar en type de données datetime a créé une valeur hors limites.

Your original format could also cause problems even with date:
SET DATEFORMAT MDY;
DECLARE @d DATE = '23/12/2012';

Result:

Msg 241, Level 16, State 1
Conversion failed when converting date and/or time from character string.

Not to sound preachy, but please see this blog post:

Bad habits to kick : mis-handling date / range queries

And this video:

Recommended SQL Server Date Formats

And Tibor's excellent article:

The ultimate guide to the datetime datatypes


You can use the Import/Export wizard with SQL Server. I came across a very good write-up (nice pretty pictures) here on it.


MySQL: Is it possible to order a query by a specific letter using ORDER BY?

Have a look at below example

DROP TABLE IF EXISTS products;

create table products(pname CHAR(30),pdescription CHAR(30),price DECIMAL(10,2),manufacturer CHAR(30));

INSERT INTO products VALUES
    ('Toys','These are toys',15.25,'ABC'),
    ('Dolls','These are Dolls',35.25,'PQR'),
    ('DustPan','These are DustPan',75.25,'AZD'),
    ('Doors','These are Doors',175.25,'RAZD'),
    ('TV','These are TV',11175.25,'RAZD'),
    ('Bed','These are Bed',1175.25,'ARAZD');

/** Check all data **/

SELECT * FROM products;
+---------+-------------------+----------+--------------+
| pname   | pdescription      | price    | manufacturer |
+---------+-------------------+----------+--------------+
| Toys    | These are toys    |    15.25 | ABC          |
| Dolls   | These are Dolls   |    35.25 | PQR          |
| DustPan | These are DustPan |    75.25 | AZD          |
| Doors   | These are Doors   |   175.25 | RAZD         |
| TV      | These are TV      | 11175.25 | RAZD         |
| Bed     | These are Bed     |  1175.25 | ARAZD        |
+---------+-------------------+----------+--------------+
6 rows in set (0.00 sec)

/** Order by D% **/
SELECT
        pname, pdescription, price
    FROM
        products
    ORDER BY
    CASE
        WHEN pname LIKE 'D%' THEN 1
        ELSE 2
    END;
+---------+-------------------+----------+
| pname   | pdescription      | price    |
+---------+-------------------+----------+
| Dolls   | These are Dolls   |    35.25 |
| DustPan | These are DustPan |    75.25 |
| Doors   | These are Doors   |   175.25 |
| Toys    | These are toys    |    15.25 |
| TV      | These are TV      | 11175.25 |
| Bed     | These are Bed     |  1175.25 |
+---------+-------------------+----------+
6 rows in set (0.00 sec)


Getting nice PostgreSQL pgAdmin-style DDL

Digging around in the pgAdmin source code it appears that the routines to generate the DDL are embedded within the application code. For instance, I the code to generate the table DDL can be found in pgTable::GetSql.


PostgreSQL function parameters JSON to TYPE

One can use json_populate_record() to achieve this.  It's very easy to use:

json_populate_record(null::API_CAR, <your JSON here>)

Exemple with NodeJS :

exports.carPATCH = function (apiKey, args, res, next) {

    let car = "'" + JSON.stringify(args.v_car.value) + "'";
    apiKey = "'" + apiKey + "'";

    DB.query.any("SELECT * FROM v01_api_patch_carPATCH" +
        "(json_populate_record(NULL::API_CAR," + car + ")," + apiKey + ")", [true])
        .then(data => {
            res = resp.response_201(res, data);
        })
        .catch(error => {
            res = resp.error(res, error);
        })
};

And after, my function work perfectly because it's an object API_CAR


I think requirements like that stem from a fundamental misconception on how relational databases work and what a primary key should do.
Anyhow, in Postgres I would do it like this:
You indeed need a separate table to store the last generated id for each project prefix. To make that a unique value, this means that the project prefix also has to be unique.
The two base tables:
create table project
(
  project_id     integer primary key,
  project_prefix varchar(20) not null unique --<< unique is important!
);

create table issue
(
  issue_id   varchar(50) primary key, -- this needs to be longer than the prefix!
  project_id integer not null references project,
  severity   integer not null,
  title      text not null,
  constraint check_severity check (severity between 1 and 10)
);

The "generator" table could look like this:
create table id_generator
(
   prefix    varchar(20) not null primary key,
   issue_id  integer     not null default 1
   foreign key (prefix) references project (project_prefix)
);

Then we an create a function that generates the next issue id based on the prefix:
create or replace function generate_issue_id(p_prefix text)
  returns text
as
$$
  insert into id_generator(prefix)
  values (p_prefix)
  on conflict (prefix) do update
    set issue_id = id_generator.issue_id + 1
  returning concat(prefix, '-', issue_id);
$$
language sql;

Then we need to create a trigger for the issue table to automatically assign the issue_id. That way we don't have to provide the project prefix during insert.
create function assign_issue_id()
  returns trigger
as
$$
begin
  select generate_issue_id(project_prefix)
    into new.issue_id
  from project
  where project_id = new.project_id;
  return new;
end;
$$
language plpgsql;

create trigger issue_trigger
  before insert on issue
  for each row
  execute function assign_issue_id();

This works because the function will lock the "generator row" for the project for which the issue ID was generated. The lock will be held until the transaction is committed.
This will effectively serialize inserts into the issue table for a one project, i.e. if a second insert happens before the first one is committed (or rolled back) the second insert has to wait. As the increment of the project specific ID is committed together with the insert into the issue table, a rollback will also rollback the issue id increment, effectively making this a "gapless sequence".
The insert into the issue table then should not provide a issue_id as it is generated automatically:
insert into project (project_id, project_prefix) values (1, 'p1');
insert into issue (project_id, severity, title)
values
(1, 1, 'Not working'),
(1, 10, 'Wrong translation');

Online example


How can I determine size of database prior to importing from SQL dump?

I don't know of any tool that can do what you ask.
The space required can vary due to many factors:

Storage engines
Number of indexes per table
Data types
Character sets
Table options (for example row format and compression)
Numerous MySQL Server configuration options
Table encryption

If you are trying to optimize the storage, try launching a cloud instance with a modest instance type but over-provisioned with plenty of storage space. Then restore your database dump file, and measure the actual size of the restored database. Use that information to provision another cloud instance with exactly the storage you think is appropriate. Then drop the first one, since you only used it to get the size estimate.
Another consideration: Do you really want to provision only enough space for the current database? You don't think it will keep growing (most databases keep growing, in my experience). Does your storage volume have enough space for binary logs? For temporary tables needed during ALTER TABLE? For temporary tables needed during queries?
It's usually a good idea to make sure you have at least double the storage space of your current database size.


How can I have intensive stored procedures run on a different machine to where tables are stored?

For the last 20 years, CPUs have only slightly improved in performance.
MySQL rarely tops out a CPU these days.  When it does, there is usually an index that can be added or some SQL changed to make it quite down.
Hence, I suggest that the CPU you have is not that bad.

"Calculating analytics on demand" is a common problem in Data Warehousing.  There is a common solution:  build and maintain Summary table(s)  You can still use a Stored Proc to interface with those table(s) to provide the analytics, but many times as fast (elapsed time).

Federated?  Ouch.  That can be terribly slow.  There are cases where fetching each row is a roundtrip to the other machine.  Consider connecting to the other machine(s) and querying them directly.

Once you have gotten further in your design, start another Question for further advice.  Please provide data sizes, RAM and disk sizes, what the analytics are, schema, etc.

In particular, be sure to minimize disk footprint from the start.  Don't use BIGINT except in rare cases.  Don't use DOUBLE; pick something smaller, normalize 'names', etc.

Now you have me worried that you are at the mercy of whether Synology has the smarts to follow the above hints??


Read-only on an SQLite database which is being modified by another program

You are looking for WAL Mode.

  WAL provides more concurrency as readers do not block writers and a
  writer does not block readers. Reading and writing can proceed
  concurrently.


Update SQL n:n table with multiple associations

My suggestion is that you only delete from inv_item_group_members the items you deleted from the object and only insert into it the new items you added to the object.

You can do that by creating an itemsToDelete array, which you populate with item_ids each time you call the removeItem method of the class and an itemsToInsert which you populate each time you add an item whith the addItem method.

Then when you call the save method you delete the items whose item_id are present in the itemsToDelete array (obviously restricted also to group_id). Then you proceed to insert items present in the itemsToInsert array.

To make it resilient, you can either use a Map instead of arrays in order to prevent duplicates, or catch and ignore PK exceptions during the insert.


Selecting the popular posts using three tables

5 Most Popular Posts By Reviews

SELECT A.uid,B.post_count reviews,A.post
FROM posts A LEFT JOIN
(
    SELECT * FROM
    (
        SELECT pid,COUNT(1) post_count
        FROM post_reviews
        GROUP BY pid
        ORDER BY post_count DESC
    ) AA LIMIT 5
) B
ON A.id = B.pid;

5 Most Popular Posts By Shares

SELECT A.uid,B.post_count shares,A.post
FROM posts A LEFT JOIN
(
    SELECT * FROM
    (
        SELECT pid,COUNT(1) post_count
        FROM post_shares
        GROUP BY pid
        ORDER BY post_count DESC
    ) AA LIMIT 5
) B
ON A.id = B.pid;

I hope this gives you a start...


How to find offset/index base on id with order?

If you mean the number of rows with a smaller id value:

SELECT count(*) FROM product WHERE id < $1;


Why can members of db_datareader SELECT sys views

Metadata visibility configuration doesn't work in the same was as permissions for User tables - the rights are included implicitly whenever rights to underlying objects are granted.

Take the following example from the docs:

SELECT name, object_id
FROM sys.tables
WHERE name = 'myTable';
GO

The above returns no results for users that have been granted no rights on those underlying tables. It therefore follows that if you grant db_datareader (IE SELECT on all tables / views), you're also implicitly granting access to sys.tables for all Tables / Views in that database.

This is true for all GRANT states (IE DENY won't cause the user to see metadata for related objects, but GRANT will)

In summary, granting access to a user object also grants access to the metadata for that object, exposed via the system catalog.


Can't create Windows Login's on a case sensitive SQL Server 2005 Instance - MSG 15401

You chose a case sensitive collation when installing SQL Server

You will have "CS" in this result:

 SELECT SERVERPROPERTY('Collation')

You either live with it or rebuild the master database


Performance issue on SQL Server 2008 R2 hosted on Windows Server 2008 R2

As mentioned in comments on the question:

  sp_BlitzErik: If your CPUs have higher clock speeds why wouldn't they read data from memory faster?
  Sean Gallardy: So, you don't think a CPU that has a 59.1% faster clock speed (turbo boost up to 3.5Ghz) would have anything to do with a difference of 57.5% performance in a purely cpu/memory workload?
  Simon Hellings: If the query is not going parallel then the core speed matters. Core count is irrelevant at that point. If one car is travelling at 40mph and one at 70mph which one gets across the line in a straight line race?  Same thing with clock speed...

...the difference you are seeing in executing this query almost certainly down to the clock speed differentials between the two machines.

If the query doesn't generate a parallel execution plan, it's only going to be using one core no matter how many cores you have, or what settings you have for MAXDOP.

Effectively, this test is one 2.2GHz processor vs. one 3.5GHz processor. This is why you aren't seeing high overall CPU usage figures for the server: most of the cores are just sat there twiddling their thumbs.

Where the server is going to win out over your laptop is when it's not just one query running but many queries simultaneously.

I'm ridiculously over-simplifying here and I'm going to ignore all other possible resource contention (RAM etc.), but if 4 instances of this query hit the engine at the same time then your quad core laptop can execute all four together, but if we up that number of queries to 5 then one is going to have to wait for a core to become available. If it's 10 queries then 6 are going to have to wait their turn. On the 64-core server however, it can run those ten queries simultaneously, and another 54 for good measure!

Also, which edition of SQL Server 2008 is it? Your mention of it only using 64GB makes me wonder if it's Standard Edition, in which case it's worth bearing in mind that it will only use four physical processors anyway (32 of the servers cores) see Features Supported by the Editions of SQL Server 2008 R2 for details.

You mention that the client is complaining of a slow application, which I'd imagine is really what you'd like to get a handle on. I think the results from your test query have just led you down a blind alley. If you want to better address the performance issues the client is complaining about then you need to see what's actually happening on the server during use.

I'd start by installing Adam Machanic's excellent sp_whoisactive stored procedure and running that during times of high load to see what is going on. Even better might be to log the results to a table so you can get a more general picture.

Having mentioned Brent Ozar's site, I'd highly recommend downloading their FirstResponder kit as the tools in there can be invaluable in finding where the problems lie.

It's also worth having a look at the server's wait stats to see where the bottlenecks are - if you run the query found in Paul Randal's Wait statistics, or please tell me where it hurts, it will tell you what sort thing the server is waiting around for.


Are there other ways to select a dynamic list of columns?

Another way, similar to what I proposed to your previous question: Return a set of well known type. Since your column list is dynamic, create a temporary table for the purpose. This announces the type to the system. As a side-effect you get a temp table to keep results for the duration of the session - like you needed in your last question.

CREATE OR REPLACE FUNCTION select_prices(_tbl anyelement, _cols text)
 RETURNS SETOF anyelement AS
$func$
BEGIN

RETURN QUERY EXECUTE
'SELECT ' || colList || '
 FROM   prices
 WHERE  ...
 ORDER  BY ...';

END
$func$ LANGUAGE plpgsql;

Call:

CREATE TEMP TABLE t (col1, int, col2, date);
SELECT * FROM select_prices(NULL::t, 'col1, col2')

Or, to keep results in the temp table:

INSERT INTO t
SELECT * FROM select_prices(NULL::t, 'col1, col2')

If you need multiple tables in the same sessions, employ a sequence to get unique names. Related answer on SO:
Create a temporary table from a selection or insert if table already exist

However, this method (just like the other two in your question) are susceptible to SQL injection. You need to make sure it can't be abused.
SQL injection in Postgres functions vs prepared queries

Again, I would try to use this simple statement instead:

CREATE TEMP TABLE t AS
SELECT col1, col2 FROM prices;


If I make a change to one server in an Always On situation - like say adding an index - do I have to make the same change to the other server?

No, you just make the change to Primary (master replica) database which is writable. Rest other databases in an availability group are either read only or not accessible depending on if you have opened your secondary replicas for read or not.

  And if I do, is there a risk of the two servers not being in sync?

If the replica sync mode is set to Synchronous then it will always be in sync. If it is set to Async, then there it might lag a bit. In my experience, unless you have a very high latency, its always near to instantaneous for async replicas as well.

for few concepts, this is very detailed info that you will find on how it works.


You need to unleash two things

innodb_read_io_threads (Default is 4, Max is 64, raise it 8 or 16)
innodb_write_io_threads (Default is 4, Max is 64, raise it 8 or 16)
innodb_thread_concurrency (Default is 0. Since you don't have it, 0 is just fine)

I have this discussed this over the years. Please see some of my earlier posts on getting InnoDB to engage more CPU/Core activity.

May 26, 2011 : About single threaded versus multithreaded databases performance
Sep 12, 2011 : Possible to make MySQL use more than one core?
Sep 20, 2011 : Multi cores and MySQL Performance
Apr 26, 2012 : Is the CPU performance relevant for a database server?

Give it a Try !!!
UPDATE 2013-06-20 02:39 EDT
Your last comment

Hey Rolando, I added read_io and write_io at 8 and read through your other posts but it doesn't seem to be bringing down the CPU usage. I even dropped the 4 buffer pool instances down to 1 in hopes that it would have better performance, but still no go. Do you have any more suggestions that I could implement that will help drop CPU usage?

I would suggest raising innodb_buffer_pool_instances to 8 to match the number of cores. The MySQL Documentation suggests 1G per buffer pool instance. Then, you would raise to 16 (since you have innodb_buffer_pool_size at 16G). Try 8 first, then 16.
Even with this, I have some rather distressing news: Here is a Press Release From FusionIO from 2 years ago. Paragraph 2 says:

The StatSoft white paper concluded that Fusion ioDrives significantly increased the I/O performance in the STATISTICA suite of analytics software products and solutions, greatly increasing CPU utilization and efficiency. With ioMemory, StatSoft achieved 300 and 500 percent data performance and latency reduction improvements when compared to legacy disk-based storage. With the increased I/O performance enabled by the Fusion ioDrives, CPU utilization increased to 90 percent in tests of large data sets, versus the 32 percent CPU utilization observed with the disk-based technology.

If you were using RAID 10 SAS or even SSDs for /var/lib/mysql, InnoDB would have its day with the CPUs. However, from the looks of this Press Release, InnoDB is competing with FusionIO for CPU Utilization.
SUGGESTION
Find out what the IOPs are for the FusionIO disk and set innodb_io_capacity to that number. The default value for innodb_io_capacity is 200. If the IOPs are greater than 200, then please set it. If the IOPs are in the 1000's, then InnoDB has a chance to be on a level playing field with the FusionIO.
Give it a Try !!!


assuming ID is a primary key.

on conflict do nothing:

create or replace function t2_tab() returns trigger
as
$$declare r record;
begin
insert into tab2
select new.*
on conflict do nothing
returning * into r;
return r;
end;
$$ language plpgsql;


Fix OGR2OGR "failed when converting the varchar value to data type int" issue in the MSSQL table -

The good news: You're not missing anything obvious or arcane.

The bad news: If you've got a proper DBA onsite, it's time to get them involved.

Dollars to donuts the error is in the object_definition() of dbo.vw_AUTH_CREFNO. Typically the vw_ prefix indicates that the object is a VIEW, meaning that it's just a wrapper for a larger SELECT statement which itself can have a series of poorly written JOINs, malformed expressions, and nested evaluations.

If you have VIEW DEFINITION permission in the GIS_Test database on server zzzz (and the object is not encrypted), it's time to try extracting the base definition and crossing your fingers the DDL is sensibly written and it's an obvious error. In that Object Explorer pane you've got open, right-click dbo.vw_AUTH_CREFNO and hit Script View as Create To New Query Editor Window, then Ctrl+f and hope like hell you find NCPR as a string literal in the code. What's more likely though is that you've got one ore more of the following

a string column JOINing to a numeric column somewhere
an expression somewhere between string and numeric (look for the +-/*= characters)
an explicit conversion against a value where you've got "dirty data" in the base table (whether by typecasting or coalescing into a bad target)
something else?

If you're lucky enough that NCPR is a string literal somewhere in code, but it's nested below the first level object AND it's still in the same GIS_Test database, you can spelunk pretty broadly for it using the following snippet.

select
     def = object_definition([object_id])
    ,[name]
    ,schema_name([schema_id)
from sys.objects
where object_definition([object_id]) like '%NCPR%';

If you're not so lucky, you need to look for that value in the base data, or try more advanced methods looking for which (of possibly several) problematic expression is causing your current problem.

Good luck! ¯\_(ツ)_/¯


How to apply query to select of another query, PostgreSQL 9.6

I'm sorry, maybe I'm misreading something but I think you can get it using a single UPDATE.

UPDATE eco_track
SET    distance_1 = ST_3DDistance(ST_MakePoint(ST_X(ST_Transform(ST_SetSRID(ST_MakePoint(longitude, latitude), 4326),32652)),
                                               ST_Y(ST_Transform(ST_SetSRID(ST_MakePoint(longitude, latitude), 4326),32652)), altitude),
                                               (SELECT geom FROM eco.static_points WHERE name='Korea'))
WHERE latitude IS NOT NULL
AND   longitude IS NOT NULL
AND   altitude IS NOT NULL;

point_3d isn't used anywhere

point_3d AS (
    SELECT  ST_MakePoint(ST_X(ST_Transform(ST_SetSRID(ST_MakePoint(lon, lat), 4326),32652)),ST_Y(ST_Transform(ST_SetSRID(ST_MakePoint(lon, lat), 4326),32652)), alt)  FROM coordinates
    ),

static_points is constant, you can replace it in the UPDATE sentence

SELECT geom FROM eco.static_points WHERE name='Korea'

All the information is on the eco_track table


ola.hallengren - Alternative MySQL scripts!

You might like to visit https://github.com/shlomi-noach/awesome-mysql which is a list of recommended community tools for MySQL.
Specifically the kind of tools you want may be:

common_schema - DBA's framework for MySQL, providing a function library, views library and QueryScript interpreter.
sys - A collection of views, functions and procedures to help MySQL administrators get insight in to MySQL Database usage.
Percona Toolkit - a collection of advanced command-line tools to perform a variety of MySQL server and system tasks that are too difficult or complex to perform manually.
openark kit - a set of utilities that solve everyday maintenance tasks, which may be complicated or time consuming to do by hand, written in Python.


Avoiding Multiple Queries when Searching for Records Associated with a Set of Records

If I understand you correctly, you want to display the products regardless if they are associated with a price rule, right ?
In my opinion, you'd best achieve this with an OUTER JOIN, which will give you all products and simply return an empty PriceRuleId column for the products without price rules.

The following query will give you a result Matrix with all products and their price rules, if they have them.

select
     p.id as ProductId
    ,pr.id as PriceRuleId
    ,pr.apply_to as PriceRuleApplyTo
    ,pr.start_date as PriceRuleStartDate
    ,pr.end_date as PriceRuleEndDate
    ,pr.promo_code as PriceRulePromoCode
from
    products p
    LEFT OUTER JOIN price_rule_product prp ON prp.product_id = p.id
    LEFT OUTER JOIN
        ( SELECT id, apply_to, start_date, end_date, promo_code
            FROM  price_rule
            WHERE
                apply_to = 'line_items'
                AND ( start_date IS NULL OR start_date = 0 OR start_date <= 1366063902 )
                AND ( end_date IS NULL OR end_date = 0 OR end_date >= 1366063902 )
                AND ( promo_code IS NULL OR promo_code = '' )
            ) pr ON prp.price_rule_id = pr.id

I hope I understood the problem correctly.


Any non-SQL Server-process that consumes CPU may cause the entire SQL Server to be completely unusable.

As an example, Windows Update will bring SQL Server to a stop while it is unpacking .Net assemblies.

Any Anti-Virus process, even if it is configured to exclude SQL Server data files and folders may cause SQL Server to become unresponsive while it scans files and while it unpacks updated virus definition files.

If you have any management software installed, such as IBM Tivoli Server Monitor, and it decides to use 100% of one core, you probably won't be able to do anything other than cold shutdown the machine.  Don't ask me why I'm bringing that up.

I could go on, but suffice it to say, any server with a single core is simply asking for trouble with performance and manageability.   Check that, any computer with a single core should be considered unusable today.  Heck, my phone has 8 cores.

While you're at it, convince the virtualization guy to give each SQL Server a reasonable memory reservation that will ensure SQL Server's memory doesn't get paged out of the hosts main memory to disk.

For serious VMware-hosted SQL Servers, you should configure VMware according to the Architecting SQL Server on VMware Best Practices guide.

It really boils down to a single-core VM can only ever run a single thread at any given time.  Virtualization is not a silver bullet for that one-core-per-thread situation.

The above points take into account that Windows NT (the 1993 codebase which all modern versions of Windows are built on) utilizes preemptive multi-tasking.  Preemptive multi-tasking allows the O/S to enforce multi-tasking by forcing context-switching at regular schedules.  Even with preemptive multi-tasking, a server with a single core will still suffer from very poor performance if a single task decides to use as much CPU as possible, since the O/S will give that task as much CPU time as can be spared while still allowing higher-priority tasks to run, forcing CPU to 100%.  If SQL Server is running at the time CPU goes to 100%, SQL Server's quantum of CPU will be reduced so much as to become very noticeable.  In the above scenarios I outlined, I did not say, the machine (or even SQL Server) will completely "stop" or "die"; that would simply be untrue.  However, the machine may become so busy that for all intents-and-purposes the machine becomes unusable.  Adding a second core, while certainly not a panacea, reduces the likelihood that the machine will become completely unresponsive.  Each additional core means another thread can run to 100% CPU without causing the machine to become unusable.


Connecting to mysql using management tool

Socket implies that client and server are on the same machine.

SSH is for security.

So, try "standard".


Loop through Self JOIN on table until the operand column is NULL completely

Although MySQL has no CTE functionality, there are two major ways to create CTE expressions:
TECHNIQUE #1 : Write Stored Procedures to Traverse Recursively
Rather than Reinventing the Wheel, please see my past posts on how to make stored procedures

Oct 24, 2011 : Find highest level of a hierarchical field: with vs without CTEs
Oct 26, 2012 : Hierarchical queries without CTEs

TECHNIQUE #2 : Iterate Based on the Tree's Maximum Depth
First, here is some sample data
DROP DATABASE IF EXISTS dufran;
CREATE DATABASE dufran;
USE dufran;
CREATE TABLE reference
(
    user INT NOT NULL AUTO_INCREMENT,
    referee INT NOT NULL DEFAULT 0,
    name VARCHAR(20) NOT NULL,
    PRIMARY KEY (user)
);
INSERT INTO reference (name,referee) VALUES ('Rolando',0);
INSERT INTO reference (name,referee) VALUES ('Pamela',1);
INSERT INTO reference (name,referee) VALUES ('Carlik',2);
INSERT INTO reference (name,referee) VALUES ('Javonne',2);
INSERT INTO reference (name,referee) VALUES ('Dominique',2);
INSERT INTO reference (name,referee) VALUES ('Diamond',2);
INSERT INTO reference (name,referee) VALUES ('Azalia',3);

When loaded, this is the data
mysql> SELECT * FROM reference;
+------+---------+-----------+
| user | referee | name      |
+------+---------+-----------+
|    1 |       0 | Rolando   |
|    2 |       1 | Pamela    |
|    3 |       2 | Carlik    |
|    4 |       2 | Javonne   |
|    5 |       2 | Dominique |
|    6 |       2 | Diamond   |
|    7 |       3 | Azalia    |
+------+---------+-----------+
7 rows in set (0.00 sec)

mysql>

Here is how you compute the maximum depth
SET @depth = 0;
SELECT MAX(@depth:=@depth+1) max_depth FROM
(SELECT DISTINCT referee FROM reference) A;

Here is the maximum depth for the sample data
mysql> SET @depth = 0;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT MAX(@depth:=@depth+1) max_depth FROM
    -> (SELECT DISTINCT referee FROM reference) A;
+-----------+
| max_depth |
+-----------+
|         4 |
+-----------+
1 row in set (0.00 sec)

mysql>

Using this principle, iterate for the SELECT columns and the LEFT JOIN clauses
SET @depth = 0;
SELECT GROUP_CONCAT(CONCAT('t',dep,'.user AS lvl',dep))
INTO @sqllist FROM
(SELECT @depth:=@depth+1 dep FROM
(SELECT DISTINCT referee FROM reference) AA) A;
SET @depth1 = 0;
SET @depth2 = 1;
SELECT GROUP_CONCAT(sqljoin SEPARATOR ' ') INTO @sqljoins
FROM (SELECT CONCAT('LEFT JOIN reference t',@depth2:=@depth2+1,
' ON t',@depth2,'.referee = t',@depth1:=@depth1+1,'.user') sqljoin
FROM (SELECT DISTINCT referee FROM reference) AA) A;
SELECT @sqljoins;
SET @sqlstmt = CONCAT('SELECT ',@sqllist,
' FROM reference t1 ',@sqljoins);
SELECT @sqllist\G
SELECT @sqljoins\G
SELECT @sqlstmt\G

Here is the SQL generated
mysql> SET @depth = 0;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT GROUP_CONCAT(CONCAT('t',dep,'.user AS lvl',dep))
    -> INTO @sqllist FROM
    -> (SELECT @depth:=@depth+1 dep FROM
    -> (SELECT DISTINCT referee FROM reference) AA) A;
Query OK, 1 row affected (0.00 sec)

mysql> SET @depth1 = 0;
Query OK, 0 rows affected (0.00 sec)

mysql> SET @depth2 = 1;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT GROUP_CONCAT(sqljoin SEPARATOR ' ') INTO @sqljoins
    -> FROM (SELECT CONCAT('LEFT JOIN reference t',@depth2:=@depth2+1,
    -> ' ON t',@depth2,'.referee = t',@depth1:=@depth1+1,'.user') sqljoin
    -> FROM (SELECT DISTINCT referee FROM reference) AA) A;
Query OK, 1 row affected (0.00 sec)

mysql>     SELECT @sqllist\G
*************************** 1. row ***************************
@sqllist: t1.user AS lvl1,t2.user AS lvl2,t3.user AS lvl3,t4.user AS lvl4
1 row in set (0.00 sec)

mysql>     SELECT @sqljoins\G
*************************** 1. row ***************************
@sqljoins: LEFT JOIN reference t2 ON t2.referee = t1.user LEFT JOIN reference t3 ON t3.referee = t2.user LEFT JOIN reference t4 ON t4.referee = t3.user LEFT JOIN reference t5 ON t5.referee = t4.user
1 row in set (0.00 sec)

mysql>     SELECT @sqlstmt\G
*************************** 1. row ***************************
@sqlstmt: SELECT t1.user AS lvl1,t2.user AS lvl2,t3.user AS lvl3,t4.user AS lvl4 FROM reference t1 LEFT JOIN reference t2 ON t2.referee = t1.user LEFT JOIN reference t3 ON t3.referee = t2.user LEFT JOIN reference t4 ON t4.referee = t3.user LEFT JOIN reference t5 ON t5.referee = t4.user
1 row in set (0.00 sec)

mysql>

Here is the big question, does the SQL work ??? Take the SQL and run it dynamically:
PREPARE s FROM @sqlstmt;
EXECUTE s;
DEALLOCATE PREPARE s;

Here is the result:
mysql> PREPARE s FROM @sqlstmt;
Query OK, 0 rows affected (0.00 sec)
Statement prepared

mysql> EXECUTE s;
+------+------+------+------+
| lvl1 | lvl2 | lvl3 | lvl4 |
+------+------+------+------+
|    1 |    2 |    3 |    7 |
|    1 |    2 |    4 | NULL |
|    1 |    2 |    5 | NULL |
|    1 |    2 |    6 | NULL |
|    2 |    3 |    7 | NULL |
|    2 |    4 | NULL | NULL |
|    2 |    5 | NULL | NULL |
|    2 |    6 | NULL | NULL |
|    3 |    7 | NULL | NULL |
|    4 | NULL | NULL | NULL |
|    5 | NULL | NULL | NULL |
|    6 | NULL | NULL | NULL |
|    7 | NULL | NULL | NULL |
+------+------+------+------+
13 rows in set (0.03 sec)

mysql> DEALLOCATE PREPARE s;
Query OK, 0 rows affected (0.00 sec)

mysql>

I scripted this to give you a starting point and some homework. Here is what I mean: You probably  don't want to see NULL columns. You could do one of two things:

Convert each NULL to IFNULL(lvl1,''), GROUP_CONCAT all columns.
Compute the depth of a particular id and gather columns for it only.

Give it a Try !!!
UPDATE 2016-06-14 16:36 EDT
Someone just commented

Your max depth calculation is wrong. Proof: Add to your sample data: INSERT INTO reference (name,referee) VALUES ('Maria',4);

Let's try it out
mysql> INSERT INTO reference (name,referee) VALUES ('Maria',4);
Query OK, 1 row affected (0.00 sec)

mysql> SET @depth = 0;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT MAX(@depth:=@depth+1) max_depth FROM
    -> (SELECT DISTINCT referee FROM reference) A;
+-----------+
| max_depth |
+-----------+
|         5 |
+-----------+
1 row in set (0.00 sec)

OK, it says the depth is 5. Is there visible proof of this ?
mysql> SET @depth = 0;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT GROUP_CONCAT(CONCAT('t',dep,'.user AS lvl',dep))
    -> INTO @sqllist FROM
    -> (SELECT @depth:=@depth+1 dep FROM
    -> (SELECT DISTINCT referee FROM reference) AA) A;
Query OK, 1 row affected (0.00 sec)

mysql> SET @depth1 = 0;
Query OK, 0 rows affected (0.00 sec)

mysql> SET @depth2 = 1;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT GROUP_CONCAT(sqljoin SEPARATOR ' ') INTO @sqljoins
    -> FROM (SELECT CONCAT('LEFT JOIN reference t',@depth2:=@depth2+1,
    -> ' ON t',@depth2,'.referee = t',@depth1:=@depth1+1,'.user') sqljoin
    -> FROM (SELECT DISTINCT referee FROM reference) AA) A;
Query OK, 1 row affected (0.00 sec)

mysql> SELECT @sqljoins;
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| @sqljoins                                                                                |
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| LEFT JOIN reference t2 ON t2.referee = t1.user LEFT JOIN reference t3 ON t3.referee = t2.user LEFT JOIN reference t4 ON t4.referee = t3.user LEFT JOIN reference t5 ON t5.referee = t4.user LEFT JOIN reference t6 ON t6.referee = t5.user |
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> SET @sqlstmt = CONCAT('SELECT ',@sqllist,
    -> ' FROM reference t1 ',@sqljoins);
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT @sqllist\G
*************************** 1. row ***************************
@sqllist: t1.user AS lvl1,t2.user AS lvl2,t3.user AS lvl3,t4.user AS lvl4,t5.user AS lvl5
1 row in set (0.00 sec)

mysql> SELECT @sqljoins\G
*************************** 1. row ***************************
@sqljoins: LEFT JOIN reference t2 ON t2.referee = t1.user LEFT JOIN reference t3 ON t3.referee = t2.user LEFT JOIN reference t4 ON t4.referee = t3.user LEFT JOIN reference t5 ON t5.referee = t4.user LEFT JOIN reference t6 ON t6.referee = t5.user
1 row in set (0.00 sec)

mysql> SELECT @sqlstmt\G
*************************** 1. row ***************************
@sqlstmt: SELECT t1.user AS lvl1,t2.user AS lvl2,t3.user AS lvl3,t4.user AS lvl4,t5.user AS lvl5 FROM reference t1 LEFT JOIN reference t2 ON t2.referee = t1.user LEFT JOIN reference t3 ON t3.referee = t2.user LEFT JOIN reference t4 ON t4.referee = t3.user LEFT JOIN reference t5 ON t5.referee = t4.user LEFT JOIN reference t6 ON t6.referee = t5.user
1 row in set (0.00 sec)

mysql> PREPARE s FROM @sqlstmt;
Query OK, 0 rows affected (0.00 sec)
Statement prepared

Guess what the output says ?
mysql> EXECUTE s;
+------+------+------+------+------+
| lvl1 | lvl2 | lvl3 | lvl4 | lvl5 |
+------+------+------+------+------+
|    1 |    2 |    3 |    7 | NULL |
|    1 |    2 |    4 |    8 | NULL |
|    1 |    2 |    5 | NULL | NULL |
|    1 |    2 |    6 | NULL | NULL |
|    2 |    3 |    7 | NULL | NULL |
|    2 |    4 |    8 | NULL | NULL |
|    2 |    5 | NULL | NULL | NULL |
|    2 |    6 | NULL | NULL | NULL |
|    3 |    7 | NULL | NULL | NULL |
|    4 |    8 | NULL | NULL | NULL |
|    5 | NULL | NULL | NULL | NULL |
|    6 | NULL | NULL | NULL | NULL |
|    7 | NULL | NULL | NULL | NULL |
|    8 | NULL | NULL | NULL | NULL |
+------+------+------+------+------+
14 rows in set (0.00 sec)

mysql> DEALLOCATE PREPARE s;
Query OK, 0 rows affected (0.00 sec)

mysql>

Just as my code predicted. The depth is 5 and the Dynamic SQL just proved it.


For accessing Oracle 12c RAC database by JDBC to ensure high availability, should I use TAF or FCF?

In this case, should I use TAF or FCF?

TAF- Transparent Application Failover is a database session-level connection failover mechanism and works only for OCI clients.

FCF- Fast Connection Failover is a application-level failover mechanism. It depends on the implicit connection cache feature and implicit connection caching must be enabled.

TAF would be very useful for long running sessions whereas FCF is useful for pooled connections.

  What I want is to preserve the JDBC connections and redirect them to another oracle database on the array. Also, can transactions preserved?

TAF will fail over standard SQL SELECT statements that have been caught during a node crash in an in-flight transaction failure. Transactions involving INSERT, UPDATE, or DELETE statements are not supported by TAF.

  Besides, if TAF is suggested to be used, is it true that thick driver has to be used in JBoss?

Yes.

Reference:
Client Failover Best Practices - Oracle
Understanding How Fast Connection Failover Works
Comparison of TAF and Fast Connection Failover


What to monitor (key indicators) for MySQL server

Without knowing more about your environment and application(s) that your supporting I would suggest starting here:

Innodb Performance Optimization Basics By Peter Zaitsev

This is a great article that will give you a better idea of what metrics you should be paying attention to on the regular.


I got consistent results in my repeated tests with various versions over the last years:
count(*) is slightly faster than count(pk). It is also shorter and most of the time it better fits what is tested: the existence of a row.

Concerning:

  Is Postgres smart enough to pick up that a SERIAL PRIMARY KEY is going
  to exist in every row and never be false

The only relevant thing is the NOT NULL constraint. The PRIMARY KEY is NOT NULL automatically, serial or never false are orthogonal to the question.

With count(col), if PostgreSQL was trying to be smart and check the system catalog whether a column was NOT NULL and fall back to an equivalent count(*), you'd still have one more look-up on a system table than with count(*).

As for EXPLAIN output, there is a hint:

EXPLAIN SELECT count(*) FROM ...

Aggregate  (cost=4963.38..4963.43 rows=1 width=0) ...

EXPLAIN SELECT count(pk) FROM ...

Aggregate  (cost=4963.38..4963.43 rows=1 width=4) ...

Meaning, count(col) is not converted to count(*), even if it's defined NOT NULL.


Need to Display Query Result without Null Values in one column

with adding

Having (COUNT(Case when XX_MASTER_TICKETS.CurrentStatus = 1 Then 1 End) > 0)

Now try the query below

SELECT
CAST(xx_master_tickets.CreatedMoment AS DATE) TicketDate,
Outstanding =  COUNT(Case when XX_MASTER_TICKETS.CurrentStatus = 1 Then 1 End),
Closed = COUNT(Case when XX_MASTER_TICKETS.CurrentStatus = 2 Then 1 End),
Cancelled = COUNT(Case when XX_MASTER_TICKETS.CurrentStatus = 3 Then 1 End)
FROM         XX_MASTER_TICKETS INNER JOIN
             XX_DEF_ATM_LIST ON XX_MASTER_TICKETS.MachineID = XX_DEF_Machine_LIST.MachineID INNER JOIN
             XX_DEF_BRANCHES ON XX_DEF_Machine_LIST.BranchID = XX_DEF_BRANCHES.BranchID
WHERE     (XX_MASTER_TICKETS.CreatedMoment BETWEEN '01-01-2014' AND GETDATE()) and (XX_DEF_BRANCHES.BranchName = 'New York')
Group by CAST(xx_master_tickets.CreatedMoment AS DATE)
Having  (COUNT(Case when XX_MASTER_TICKETS.CurrentStatus = 1 Then 1 End) > 0)
Order by CAST(xx_master_tickets.CreatedMoment AS DATE)


AlwaysOn High Availability - not always kicking in

A deeper dive into the MS's documentation helped us discover the Flexible Automatic Failover Policy (https://docs.microsoft.com/en-us/sql/database-engine/availability-groups/windows/flexible-automatic-failover-policy-availability-group)

A setting is available to adjust the scenario when the failover should kick in. The default setting is 3.

ALTER AVAILABILITY GROUP AG1 SET (FAILURE_CONDITION_LEVEL = 5);

enabled us to cater for more scenarious, including turning off manually the service. Level 5 encapsulates scenarios/conditions of lesser level values.


Searching for the phrase "What does the node health value in a Datastax node compose of?" brings back several links to Datastax documentation pages, which all say basically the same thing:

Node health is a score-based representation of how fit a node is to handle search queries. The node health composite score is based on dropped mutations and uptime. A higher score indicates better node health. Nodes that have a large number of dropped mutations and nodes that are just started have a lower health score.


Does the number of indexes effect the modification of table?

Every time you update a record in the table, all indexes whose entries have changed because of that also need to be updated. So there more indexes you have, the longer the update will take.

Usually that is not a concern, but if you bulk-load a lot of table, it might be a good idea to disable all indexes (and constraints) and re-enable them after the load is done (as index rebuilding is faster than continuous updates).


Can't add or update child, Foreign Key Error

this message comes of you trying to insert data, that are not exist in the connected tabel

In you case

Your try to insert 12 as county_id_contry which doesn't exist in the table country.

So you have to insert 12 in your country table first, before you add to the state table


Update function to enter random/unique GUID per row

Just Add another column to your table and mark the AI checkbox. (AI=Autoincrement). This will make it automatically a PK (Primary Key).

It will automatically populate the column with a different number in each row.

Now if you want to create another type of key, use this GUID field in your update as part of the calculation so that it will variate in each row.

Why the update didn't work?

The reason for why your update didn't work it b/c it sets all values the same since you didn't have a condition to your update.

For example:

Update
    MyTable
set GUID=1
where
   AnotherColumn=1

In your update, you don't have a condition, hence all row values will be updated the same.

For example:

Update MyTable
set GUID=1+1

In other words, Update my table and set the GUID column with the value of 2.

it would be the same as having the following Update

Update MyTable
set GUID=1+1
where
    1=1

The WHERE clause filters which rows it will update.

So in conclusion if you want to create a GUID, you would need a variant in the formula per row.

Update MyTable
set GUID=1+UniqueValuePerRow

Since an update will not help you achieve what you want, you might want to create a while loop to update 1 row at a time or learn about Cursors.

...or simply add an auto increment field like in the image below.

Cheers!


upgrade from Mariadb 10.2.* to 10.3.* using mariabackup?

This is not what mariabackup is for. You backup and restore in the same "major" version (where "major" means it just means first 2 number of the version). If you need an upgrade, this is what mysql_upgrade is for.


Column name or number of supplied values does not match table definition error

You're not showing us what you're doing - but based on the table structure, this is what you should do:

create an INSERT statement that explicitly lists the columns it will insert into - assuming that ID might be an IDENTITY column that you don't want / can't insert into
define the exact number of values to fill into these columns

So your INSERT statement should be something like:

INSERT INTO dbo.tbl_Post (cat_id, ngo_id, title, description, active)
VALUES (42, 4711, 'Some title', 'Some description', 1)

What you should definitely get in the habits of avoiding is using INSERT INTO dbo.tblPost without explicitly defining the list of column to insert into. This is just a recipe for disaster, as soon as you change your table definition, all your existing INSERTs will break since they don't match the table definition anymore.

Therefore: always explicitly define the list of columns that an INSERT statement should fill data into!

Also see Aaron Bertrand's excellent blog post on the topic:
Bad habits to kick: using SELECT * / omit the column list


Select the second radio button Reorganize pages before releasing unused space, and then in the Shrink file to: option, put the minimum MB specified. You will be corrected if you put a lower MB than that specified. Then click OK.

I have found that using the DBCC commands for these work all the time, as the GUI did not work in some of my cases.

Also note, the extra space is there for a reason. You will need the space for future growth, but then that is a whole new topic in performance.


How to have SQL Server's SQL Agent pick up a new Powershell module

Do I need to tell SQL Server or Powershell to "refresh" the module somehow? How can I do that?

You can use import-module yourModuleName -Force.


Add standby database on different OS version?

I was able to resolve the issue by executing root.sh file in the AGENT_HOME folder. I think it was permission issue, I guess.


You have change all user tables and columns individually, droppng and creating all constraints as needed. It's straightforward, just tedious. (SO link)

You can also use COLLATE DATABASE_DEFAULT to coerce collations in code without actually knowing what one is used.


Declare @dt datetime ;
select @dt=  DATEADD(Hour,6,GETDATE())
SELECT FORMAT (@dt, 'MM/dd/yyyy, hh:mm:ss tt') as date