How to limit PostgreSQL INSERTs to assure that the caller has an attribute conistent with the parent table?

Nevermind. The long response time seems to be some aberration in the DB server. Running the same code a little later is doing the INSERTs is around 10msec pretty consistently. Sorry to bother you!


Mongo Update keeps adding to the the size of the collection

I'd hazard a guess that your code is running the upsert command fairly quickly. It may depend on your MongoDB version, but on the latest version an update command which does not specify update modifiers will just overwrite the entire document, which involves deleting and re-writing it.

It seems likely that in between another upsert operation, a second upsert is run and when it sees no document it inserts a new one. In this case you might want to look at not updating the entire document.

You could also look into using a unique index and a little re-try logic which might work for you based on how infrequently this appears to be happening.


How to import an existing schema and tablespace into a postgres database, from a filesystem-level backup

Say I have an existing database, and I want to add to it another
  schema with content. I saved that content previously through a
  filesystem-level backup of the tablespace that served the schema's
  tables and index

Unfortunately, an isolated backup of a tablespace cannot be used for any purpose whatsoever.

I think what you're trying to do is based on the  premise that a tablespace could be more or less moved around, be possibly plugged to a different database or the same database at a different point in time, that kind of thing. But none of this is possible. A tablespace holds live metadata in rows and elsewhere that become wrong the moment you isolate it from the rest of the instance.

The results of DDL statements go into the pg_catalog schema of each database. pg_catalog also contains references to objects that are shared across all databases and not stored in any particular database, such as pg_authid or pg_database.

Looking at Have postgres' pg_dump export an index I can see where your idea is coming from. However I don't see how @mustaccio answer has any practical use. If you have a FS-level image of a table plus its indexes, they having matching TIDs, and then what? There's no practical use to these files outside of the rest of the cluster.


You need to consider one more parameter, if you are doing it for multispeciality hospital, you should add a

Doctor table,

Department table, also

Doctor schedule (ex: Mon-Wed 10 am to 2pm, Thus-Sat 12 pm to 4pm )


On which fields to index? Fields queried (WHERE) or fields returned (SELECT)

The accepted answer overlooks the concept of covering indexes, and also does not mention the importance of indexes on multiple columns, together in one index.

A single index over both columns in the WHERE clause:

ALTER TABLE clients ADD KEY(source,added) -- adding this
ALTER TABLE clients ADD KEY(added,source) -- or this

...will usually help you more than an individual index on each column, because the optimizer may otherwise only select to use one of the two columns.  Which of the above indexes will help more depends on the distribution of values in "source" and "added."  The selected index used for the query will appear in "key" in the EXPLAIN output.  "Using where" usually means that, out of the rows the chosen query plan will result in fetching, the server realizes that some number of them will still not meet the selection criteria and will have to be subsequently filtered by the server (as in the example, where potentially a large number would have to be filtered, since no index was used).

A covering index could be particularly valuable also, because contrary to the assertion that "the fields within a row are quick and easy for the engine to get," they are only quicker and easier than finding the rows by scanning the entire table -- they still take time, and consume resources.

This is where covering indexes come in.  Adding an index with (source,added,zip,name) would likely improve your performance substantially, because once the server has found the relevant rows by using the index, it does not need to look up the rest of the data because the data is actually inside the index.  When a covering index is being used, the "key" column of explain will contain the name of the index being used, and the "Extra" column will include "using index" (meaning, using index to actually retrieve the data, not just find it.)

So while it is true that you index on your selection criteria, that is not the entire story.

Note also that no matter what is indexed, an index will only be used for actual searching starting with the leftmost column in the index, until a column is encountered that is not in the WHERE clause.

Thus, a index on (source,added) can optimize the finding of rows for a query with both "source" and "added" in the WHERE clause... or with just "source" in the WHERE clause, but this index will not be used for lookups with only "added" in the where clause, because there's a column to the left of that, that isn't being used.  Similarly, a single index on (source,added,zip,name) could optimize lookup for queries with WHERE clauses mentioning source ... or source and added ... or source and added and zip ... or source and added and zip and name... but not just "zip" ... not just "name" ... not "added" and "name" and "zip"... you get the idea.  An index is irrelevant beginning at, and to the right of, any column not referenced in WHERE when it comes to optimizing the lookup of rows.

Note that the order in which you list things in the where clause makes no difference, as long as all of the conditions are AND.  That's a misconception you'll find online.  Any equivalent expression is understood as equivalent by the optimizer.

Also, unless you explicitly disabled it, IN('Foo','foo') is redundant because the selection would be case-insensitive thanks to collations, so 'foo' should be sufficient to find any permutation of capitalization.


Corruption of files in postgres: The database subdirectory "base/13067" is missing

It is hard to know what the limit of the corruption is.  If this is the only problem, you should be able to get the globals with:
pg_dumpall -g

And the data with:
pg_dump desired_database

If this works, it would allow you to reload your data into a new cluster.


mysql Replication and Point in time Recovery

Steps you should follow:

a) Execute flush logs; on Slave server and Take a backup of slave server using mysqldump now new binary log will be created on slave server

b) Import the dump taken using mysqldump and after that using mysqlbinlog specify binlog file to read from till point of recovery time this will restore your slave status to a point you need to start with.


SQL Server Import Wizard Excel into SQL Server 2016 Error 0xc020901c

The important portion of that is the following:

  There was an error with Destination - IACList.Inputs[Destination >Input].Columns[Approval Number] on Destination - IACList.Inputs[Destination >Input]. The column status returned was: "The value violated the integrity constraints for the column."

So check the data type of the "Approval Number" column in SQL, or any other constraints/foreign keys you've added to the table, and compare that to the values in your new spreadsheet.

If the table was created based on values from the first spreadsheet, it is possible the columns is set to a too-small datatype, or the wrong datatype entirely.

For example, maybe the first spreadsheet contained only numbers in the "Approval Number" column, so the column in SQL has a datatype of INT. But the second one has some rows with letters ("197234b" or "unknown" or something), or rows with values too large for an INT datatype.

You'll need to either change the datatype of that column to something more appropriate for the actual data you are receiving (varchar or BIGINT or whatever), or edit the spreadsheet before you import it to remove any bad values.

EDIT: Also check out the ideas in this Stackoverflow question: looks like Excel sometimes tries to import extra blank rows at the end of the list. You can handle this in one of several ways:

Press CTRL-END in Excel, this will take you to the bottom of where Excel thinks there is data. Delete (don't just clear) those entire extra rows.
You can change the constraints on the destination to "Allow NULLs" in the destination rows, then delete them from SQL afterwards
You can change the filter criteria for your input in SSIS to exclude any rows with missing values
You can add an "error" output to the data transformation in SSIS to output any failing rows to a different dataset, which you can either ignore, or insert into a table with no constraints (all varchar(255)s, ALLOW NULL on every column, etc). That way if there are different errors in the future, you can see right away which rows were an issue.


Get a list of Databases which were not modified with in a date range - SQL server

can we get an output for list of databases which were not modified for last two months in a server and based on that we have to delete all of them.

Regardless of what you may find on the internet, the only way to be 100% sure is to run an audit/trace/etc. against the servers and inspect the traffic that each database is receiving as some system processes will update the databases and will need to be filtered out before continuing (which the audit/trace/etc. should be able to filter itself).

Possible Options:

SQL Audit
Server Side Trace
Extended Events

If you don't need to be absolutely 100% sure, then feel free to choose any of the other methods on the internet such as the index usage DMVs, pages in the buffer pool, last accessed date, etc.


Checking if a transitive key dependency is in 3rd normal form

A simple check to see if a relation schema is in Third Normal Form is to see if every left hand side of non-trivial functional dependencies is a superkey (and this test can be used also to check if a relation is in Boyce-Codd Normal Form), or, if this is not the case, if every attribute on the right part is a prime attribute (i.e. part of any candidate key).

So in you case both the dependencies A → BCD and BC → A (and also BC → D) have a candidate key as left part, so the schema is in Third Normal Form (and in this case also in the more strict Boyce-Codd Normal Form).


Check if there are similar rows in a table

this would be exact

select min(id), max(id)
from table
group by start_da, end_date
having count(*) > 1

start in

select t1.*, t2*
from table t1
join table t2
      on t1.startdate between t2.startdate and t2.end_date


SQL Server 2012 Reporting Services: "Unable to connect to the remote server"

I had a similar problem when installing SSRS for a SCOM installation.  It was configured with SSL but when I removed this I started getting the same error as above. Solution was to change the SecureConnectionLevel in rsreportserver.config to 0.

https://connect.microsoft.com/SQLServer/feedback/details/686214/removing-ssl-cert-in-config-manager-doesnt-properly-modify-rsreportserver-config


Apportioning usage of PostgreSQL WAL to database/table/query

If you are using PostgreSQL v13, you can install pg_stat_statements, which logs the amount of WAL per statement in the wal_bytes column. So you could run
CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

SELECT wal_bytes, calls, query
FROM pg_stat_statements
ORDER BY wal_bytes DESC
LIMIT 10;

The WAL itself has no connection to a certain SQL statement, but if you examine the entries with pg_waldump, you can connect them to certain files, which correspond to database objects. That route is cumbersome.
Upgrade to v13.


What is better to use for this case, a CTE or a table variable?

I would probably do it this way, using set-based dynamic SQL instead of any looping constructs, CTEs, etc. We could probably eliminate the table variable as well, but not enough information in the question for that, so this code assumes the table variable is already populated.

DECLARE @sql NVARCHAR(MAX) = N'';

SELECT @sql += N'
EXEC msdb.dbo.sp_send_dbmail
  @profile_name = @profileName,
  @recipients = N''recipientlist'',
  @subject = N''someSubject'', @body = N'''
    + col1 + N' and '
    + col2 + N' and '
    + col3 + N' are the columns'';'
FROM @tableVar1;

EXEC sys.sp_executesql @sql, N'@profileName SYSNAME', @profileName;


Get count of near points based on 2 unrelated tables

simply want to get cities with number of listings near it as the count.

Then don't GROUP BY listingid eg
DECLARE @distance FLOAT = 100000;
SELECT c.name CityName, count(l.ListingID) Listings
FROM listings l, Cities c
WHERE l.Location.STDistance(c.Location) <= @distance
GROUP BY c.name

Or you might try something like:
DECLARE @distance FLOAT = 100000;
SELECT c.name CityName,
(
  select count(*)
  FROM listings l
  WHERE c.Location.STDistance(l.Location) <= @distance
) Listings
FROM Cities c


Changing the owner of a replication job will not impact replication.


If you are concerned about the cost of iops, which are incredibly cheap in Amazon, then you are talking about a scale which sqlite will not be able to handle. There is no direct way to compare iops between two server types without benchmarking under your exact work load. Even then, i/o is going to be a function of how well your server is tuned.  Generally speaking, a true rdbms has several levels of in memory buffering to reduce i/o as much as possible.  Additionally, adding caching layers in front of the DB will help.

If you expect any concurrency at all, sqlite will not be sufficient.  It is really only good for development or embedded environments.


How can clone database in mariadb prompt character?

General answer - No!

First of all - mysqldump it is not a simple sql command, this is
    complicated logic behind it, such as:
 - show definition of all objects - tables, routings, foreign keys
 - manage export import in proper order, like disable constraints and etc

Second (in addition to first) - mysql console (and SQL as language) do not support database level commands, like select all - You would need do this for each tables, objects, routines.

Federated engine (FederatedX) also not resolve "problem"


How do I calculate COUNT(*) for a range without needing to use multiple queries in mysql

using the case you can define any range you want.

select
  case
    when RESULT between 0 and 50 then '0-50'
    when RESULT between 50 and 100 then '51-100'
    when RESULT between 100 and 150 then '101-150'
    when RESULT between 150 and 200 then '151-200'
    when RESULT between 200 and 250 then '201-250'
    else 'OTHERS'
  end as `Range`,
  count(1) as `Count`
from PERSON
group by `Range`;


This is a  guess, but I would try enabling lazy schema validation for the linked server under heavy load:

EXEC master.dbo.sp_serveroption @server=N'Linked_Server_name', @optname=N'lazy schema validation', @optvalue=N'true'

  If this option is set to false, the default value, SQL Server checks
  for schema changes that have occurred since compilation in remote
  tables. This check occurs before query execution. If there is a change
  in the schema, SQL Server recompiles the query with the new schema.

  If this option is set to true, schema checking of remote tables is
  delayed until execution. This can cause a distributed query to fail
  with an error if the schema of a remote table has changed between
  query compilation and execution.

Source

As to only check the linked server metadata when querying the linked server itself.

Another workaround could be using dynamic sql statements to query the linked server

DECLARE @SQL nvarchar(max)
IF (Linked Server not under heavy load = true)
BEGIN
SET @SQL =
N'SELECT * FROM OPENQUERY(LinkedServer,''SELECT * FROM master.sys.databases'')'
EXEC SP_EXECUTESQL @SQL
END


Wildcard search using parameters in function with dynamic SQL

Let's suppose for country_name you wanted to do a leading and trailing wildcard search.

You don't need dynamic SQL for this. Just:

CREATE OR REPLACE FUNCTION report_get_countries_new (_pattern text)
  RETURNS SETOF lookups.countries AS
$func$
   SELECT *
   FROM   lookups.countries
   WHERE  country_name LIKE '%' || _pattern || '%'
$func$ LANGUAGE sql;

Call:

SELECT * FROM report_get_countries_new ('ic');  -- without wildcards!

This negates SQL injection risk completely, since that comes with dynamic SQL.

The caller can still include wildcards at will (unless you process the parameter to filter wildcards), but there is a hardcoded leading wildcard and also a trailing one (unless the parameter ends with \ removing the special meaning from the trailing %).

Even if you work with dynamic SQL and EXECUTE in PL/pgSQL, there is no risk for SQL injection as long as you pass values as values with the USING clause:

Related:

SQL injection in Postgres functions vs prepared queries
PostgreSQL Stored Procedure Performance
Table name as a PostgreSQL function parameter


Do SQL Server or MYSQL contain JSON supporting functions?

SQL Server 2016 will support JSON strings.

Currently with the CTP2 version you can only export a table to JSON string.
But with the release of CTP3 version you can also read and store JSON string in SQL Server tables.

You can check all about how JSON will work with SQL Server on my blog. SQL Server will store JSON in NVARCHAR datatype columns only, unlike XML datatype for XML data.

Edit: With CTP 3.x you can create a Table with NVARCHAR(max) data type column to store JSON text, but make sure to add a CHECK constraint to validate the JSON, like:

CREATE TABLE CustomerOrder (
    BusinessEntityID INT,
    FirstName NVARCHAR(50),
    MiddleName NVARCHAR(50),
    LastName NVARCHAR(50),
    EmailPromotion INT,

    OrderDetailsJSON NVARCHAR(MAX) -- normal column with NVARCHAR datatype
        CHECK ( IsJSON ( OrderDetailsJSON ) = 1 ) -- CHECK Constraint to validate JSON string
)

The official SQL Server team blog also has a series of articles on this topic.


equivalent of oracle sql administration query in SQL Server 2008

As in Oracle the owner column denotes the schema, the following should be roughly equivalent:

select type_desc, count(*) as number
from sys.all_objects
where object_name(schema_id) = 'ownerSID'
group by type_desc;

Not sure how to get the real "owner" in SQL Server though  (the user who owns the object is not the same thing as the schema).

In SQL Server foreign keys, primary keys and other constraints are also contained in that view, so you probably might want to exclude them with a where type not in ('F', 'D', 'PK')


SQL Server CASE statement dependent on other rows in result set

So this is what i came up with. Was fairly straightforward actually. So as I stated in the post that code was in a CTE. I left that code alone, later in the CTE I added

CASE WHEN MC.HasSubstitutionVariance = 1
                      AND ( COUNT(MC.CostGroupId) OVER ( PARTITION BY MC.ProdId,
                                                         MC.CostGroupId,
                                                         MC.OprId ) > 1
                            AND LEFT(MC.CostGroupId, 3) IN ( 'LAB', 'FOH',
                                                             'L T', 'F T' )
                          ) THEN 0
                 ELSE MC.HasSubstitutionVariance
            END AS HasSubstitutionVariance ,

Which says is the costgroup repeated and is it standard material, if so mark substitution variance as 0. I thought you couldn't use OVER within a CASE statement? Or is it only certain window functions you can't use within CASE?


Altering a table to add a New Column vs Create table with New Column

This is a question which gets asked regularly in some form or other here. Take a look at my reply to a similar one not so long ago.

Basically, there are two main tools out there for this - either Percona's pt-online-schema-change (available here) or Shlomi Noach's oak-online-alter-table (from here).

There are a couple of other possible solutions mentioned which may or may not be more applicable to Windows (you didn't mention your OS) but you do specifically mention MySQL 5.5 which appears to preclude this (unless Percona have back-ported it?).

The usual disclaimers apply - about testing before implementing in production.

[EDIT - in response to OP's comment]

With replication, the situation is, apparently, the following (from here).

  REPLICATION

  If you use pt-online-schema-change to alter a table on a server with
  slaves, be aware that:

The tool is not tested with replication
The tool can break replication if not used properly
Although the tool sets SQL_BIN_LOG=0 by default (unless --bin-log is specified), triggers which track changes to the table being altered
still write statements to the binary log
Replicaiton will break if you alter a table on a master that does not exist on a slave
Update slaves first if columns are being added or removed
Do not use this tool in production before testing it

So, not exactly confidence inspiring, but not a total no-no either. Just follow the recommendations and test on a non-production machine.

Another possibility would be to use the relatively new online DDL capacity introducted in MySQL 5.6. From here, check a report of someone who tried to use Percona 5.6's online DDL - but, they apparently didn't apply changes to slaves before masters. All I can say is follow the instructions, test and see how it goes, whatever method you eventually decide on.

Did you check out the table here (from my original post to which I referred you)? It appears to be very similar situation to yours - add a column to a table with ~1,100,000 records - no locking, done in 30 seconds with pt-online-schema-change? Might be good for you? :-)


You can use following steps

On old Server

1.Stop mysql server

2.Copy contents of datadir to another location on disk as ... mysqlbackup

3.Start mysql server again

4.Compress the data (tar -czvf mysqlbackup.tar.gz mysqlbackup)

5.Copy the compressed file to new server

On New Server

1.Install MySQL [MySQL version should be same as of old server](don't start MySQL server)

2.Unzip compressed file (tar -xzvf mysqlbackup.tar.gz)

3.Move contents of mysqlbackup to the datadir.

4.Make sure that permissions of datadir are correct

5.Make sure your innodb_log_file_size is same on new server, or if it's not, don't copy the old log files (MySQL will generate these)

6.Start MySQL.

You can also look at How can I move a database from one server to another?


Filtering characters to just return numeric characters in a string?

To use regular expressions, you would need to use a SQLCLR function. Solomon Rutzky has created a library of useful CLR functions called SQLsharp. The free edition includes several regular expression functions including RegEx_Replace4k as used below:

SELECT
    M.mob,
    numeric_only =
        SQL#.RegEx_Replace4k
        (
            M.mob,  -- Source
            N'\D',  -- Regular expression
            N'',     -- Replace matches with empty string
            -1,     -- Unlimited replacements
            1,      -- Start at character position
            NULL    -- Options (see documentation)
        )
FROM #MOB AS M;

This produces the output shown below:

╔═════════════════╦══════════════╗
║       mob       ║ numeric_only ║
╠═════════════════╬══════════════╣
║ (00) 1234 5678  ║   0012345678 ║
║ +61 012 345 678 ║  61012345678 ║
║ 1234 5678       ║     12345678 ║
╚═════════════════╩══════════════╝

Regular expressions are a bit overkill for this simple requirement, so writing your own CLR implementation to just remove non-numerics would likely be even faster. Nevertheless, I have found the library function above to be as fast, if not faster, than the best T-SQL implementations (T-SQL string manipulation is rather slow).

For a T-SQL implementation, see Splitting Strings Based on Patterns by Dwain Camps.


How to I interleave logs from two different Oracle tables?

select 'LOG_1' AS LOG_SOURCE, TIME_CREATED,ENTRY_TEXT
  FROM LOG_1

  UNION ALL

  select 'LOG_2', TIME_CREATED,ENTRY_TEXT
  FROM LOG_2

  ORDER BY 2

I formalize this kind of logic as a view so you can document and comment it.

  CREATE OR REPLACE FORCE VIEW COMBINED_LOGS

  (LOG_SOURCE,

  TIME_CREATED,

  ENTRY_TEXT ) AS

  select 'LOG_1' , TIME_CREATED,ENTRY_TEXT
  FROM LOG_1

  UNION ALL

  select 'LOG_2', TIME_CREATED,ENTRY_TEXT
  FROM LOG_2;

  COMMENT ON TABLE COMBINED_LOGS IS 'Explanatory comment goes here';

This works for me on Oracle 11g R2 and Oracle 9i, perhaps it was the use of keyword SOURCE as a column name which I have corrected to LOG_SOURCE

Edit: Corrected to UNION all after the comment. See here for more discussion on the details of UNIONS.  This should be done for performance reasons and to ensure identical records are not removed.


pgScript is a local script extension of pgAdmin, which you most probably do not want here.

pgAdmin is a GUI, not a console application - there is no stdin you could easily use. If you need stdin to stream your content, use psql, which is a console application - with the \copy meta-command of psql.

If you have a file (which you obviously do), just use SQL COPY from pgAdmin:

COPY action_abilitations (id, group_action_id, ...)
FROM 'C:\Users\usernexus\Desktop\database05-12-2012.sql';

The file needs to be readable to the postgres system user.

More info in this closely related request to the pgAdmin support list.


You can use jsonb_set to replace a deep-in part of a jsonb structure.

...jsonb_set(thing,'{0,comments,1,message}','"something"');

To put that together with setting a specific element of a PostgreSQL array, it would be:

update tasks set comments[1]=jsonb_set(comments[1],'{0,comments,1,message}','"something"')
     where id=1;

Figuring out that '0' and '1' are the required subscripts to use in the path, if you didn't already know that, is another matter.

You can use jsonpath (to be new in version 12) to extract elements of JSON arrays based on conditionals (rather than just based on the array index), but I don't see a way to either update the extracted value, nor to get it to return to you an path at which that value was found so that you can sub the path into jsonb_set.


Why would a procedure sometimes hang executing SELECT...INTO #Temp2 FROM #Temp1 JOIN LocalTable...?

The bad plan XML showplan provided shows that two tables in the query have an indicated zero rows (other objects in the plan are fine):

captured from SQL Sentry Plan Explorer's Plan Tree view

This may be due to a problem automatically updating statistics, or perhaps due to some quirk of running under snapshot isolation. There really isn't enough information yet to say. In any case, this is something you should investigate.

If the table & index statistics really are missing (or empty), manually building these will will almost certainly resolve the problem you are seeing.

Given incorrect information, the query optimizer generates a plan featuring nested loop joins:

This plan has the potential to execute for a long time when the runtime row counts greatly exceed those planned for by the QO. The query isn't blocked; it is simply continuing to execute using a flawed strategy.

The 'good plan', generated when reasonable statistics are available, produces an execution plan that uses hash joins and parallelism:


You cannot have an EXCEPTION clause in a LOOP - END LOOP block.
Wrap the first UPDATE in its own BEGIN - END block and add the clause there.


lsnrctl START [listener_name]

  In the preceding command, listener_name is the name of the listener defined in the listener.ora file. It is not necessary to identify the listener if you are using the default listener name LISTENER.

If you just hit lsnrctl start without the listener name then Oracle tries to start default listener which listens on two address:

(ADDRESS=(PROTOCOL=ipc)(KEY=PNPKEY))
(ADDRESS=(PROTOCOL=tcp)(PORT=1521))

If you wish to start a specific listener then, first of all define the listener in listener.ora file and specify listener name to start it as lsnrctl start listener_name as shown below:

[oracle@ora12c admin]$ lsnrctl start listener3

LSNRCTL for Linux: Version 12.1.0.2.0 - Production on 07-JAN-2017 19:53:16

Copyright (c) 1991, 2014, Oracle.  All rights reserved.

Starting /u01/app/oracle/product/12.1.0/db_1/bin/tnslsnr: please wait...

TNSLSNR for Linux: Version 12.1.0.2.0 - Production
System parameter file is /u01/app/oracle/product/12.1.0/db_1/network/admin/listener.ora
Log messages written to /u01/app/oracle/diag/tnslsnr/ora12c/listener3/alert/log.xml
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.30.133)(PORT=1523)))

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.30.133)(PORT=1523)))
STATUS of the LISTENER
------------------------
Alias                     listener3
Version                   TNSLSNR for Linux: Version 12.1.0.2.0 - Production
Start Date                07-JAN-2017 19:53:17
Uptime                    0 days 0 hr. 0 min. 0 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /u01/app/oracle/product/12.1.0/db_1/network/admin/listener.ora
Listener Log File         /u01/app/oracle/diag/tnslsnr/ora12c/listener3/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.30.133)(PORT=1523)))
The listener supports no services
The command completed successfully
[oracle@ora12c admin]$ cat listener.ora
# listener.ora Network Configuration File: /u01/app/grid/product/12.1.0/db_1/network/admin/listener.ora
# Generated by Oracle configuration tools.

ENABLE_GLOBAL_DYNAMIC_ENDPOINT_LISTENER2 = ON

VALID_NODE_CHECKING_REGISTRATION_LISTENER = SUBNET

VALID_NODE_CHECKING_REGISTRATION_LISTENER2 = SUBNET

LISTENER1 =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.30.133)(PORT = 1522))
    )
  )

LISTENER3 =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.30.133)(PORT = 1523))
    )
  )

ENABLE_GLOBAL_DYNAMIC_ENDPOINT_LISTENER = ON

Set the local_listener initialization parameter. Its up to you use the number of listeners. In my case I have used both.

SQL>alter system set local_listener='(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.30.133)(PORT=1522))','(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.30.133)(PORT=1523))';

Update

  ORA-00119: invalid specification for system parameter LOCAL_LISTENER

  ORA-00132: syntax error or unresolved network name 'LISTENER1'

Oracle says-

  00132, 00000, "syntax error or unresolved network name '%s'"

  Cause:  Listener address has syntax error or cannot be resolved.

  Action: If a network name is specified, check that it corresponds
            to an entry in TNSNAMES.ORA or other address repository
            as configured for your system. Make sure that the entry
            is syntactically correct.

When we create a database and provide the listener name, Oracle creates a TNS alias in tnsnames.ora file and that alias is used as local_listener value.

$ cat $ORACLE_HOME/network/admin/tnsnames.ora
#LISTENER1 =
#  (ADDRESS = (PROTOCOL = TCP)(HOST = ora12c.dba.com)(PORT = 1522))

In your case the alias name should be LISTENER_ORA12C instead of LISTENER1.
I had commented that alias to simulate the scenario.

So go to the tnsnames.ora make sure that alias is there. Then try again to start your Oracle instance.

[oracle@ora12c admin]$ vi tnsnames.ora
# tnsnames.ora Network Configuration File: /u01/app/oracle/product/12.1.0/db_1/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.

LISTENER1 =
  (ADDRESS = (PROTOCOL = TCP)(HOST = ora12c.dba.com)(PORT = 1522))

[oracle@ora12c admin]$ sqlplus / as sysdba

SQL*Plus: Release 12.1.0.2.0 Production on Sun Jan 8 09:10:35 2017

Copyright (c) 1982, 2014, Oracle.  All rights reserved.

Connected to an idle instance.

SQL> startup
ORACLE instance started.

Total System Global Area  838860800 bytes
Fixed Size          2929936 bytes
Variable Size         624954096 bytes
Database Buffers      100663296 bytes
Redo Buffers            5455872 bytes
In-Memory Area        104857600 bytes
Database mounted.
Database opened.
SQL> show parameter local_listener

NAME                     TYPE    VALUE
------------------------------------ ----------- ------------------------------
local_listener               string  LISTENER1

Reference:Configuring and Administering Oracle Net Listener


SQL Server Log Shipping Randomly Stops Copying and Restoring

I spent a while in a test environment trying to reproduce your issue and have had some success.  If I set up Log Shipping in its most basic form, and in such a way as to mimic your situation, everything appears to be working.  However, I have found one way to configure the environment that results in messages similar to yours...

Message 1:

Message 2:

Let's walk through how I configured things in order to get the messages above:

Opened the Log Shipping properties on the Primary database.
Clicked "Backup Settings" and entered the same network folder for both input boxes (such as \\NetworkLocation_\DestinationForLogBackups).
Clicked "Ok".
Clicked the elipse (...) button to the right of my Secondary server instance.
Entered the destination network folder for copied files (such as \\NetworkLocation_\DestinationForLogCopies).
Checked the "Disable this job" checkbox down in the lower right.
Clicked "Ok"

The key to causing these errors is number 6 above.  This causes the following scenario to take place...

The Log Backup job backs up to a location (we'll call it "A").
The Copy job does nothing as it is disabled.
The Restore job attempts a restore from the location specified in the Copy job tab above (we'll call it "B").
The Restore job finds no suitable log files to apply in location B because they are all being backed up to location A and NOT being copied over to B (due to the Copy job being disabled).

The end result is not a clear error message outlining the root cause, but instead misleading messages that cause confusion about what is really happening.

This all seems to fit your situation, except for your comment:

  First of all my copy jobs are all working fine, it appears to be the
  restore jobs that are having a problem.

It would be a good idea to verify whether the Copy job is indeed enabled and is working as expected.  Look through the history of that job carefully and see if any of the information above helps you in identifying what your next step should be.


I used snapmanager with SQL 2005, it worked really well.  I would take a snapshot, swing it over, mount it on our reporting server, and voila.

The only problem I had is that most of what I wanted to do required undocumented NetApp commands.  Once I got them, I just put them into a SQL job and our reporting environment was refreshed daily, and used less space on NetApp.


You can use the DUAL table.

  DUAL is purely for the convenience of people who require that all SELECT statements should have FROM and possibly other clauses. MySQL may ignore the clauses. MySQL does not require FROM DUAL if no tables are referenced.
  Quoting from MySQL doc-ref


How to create a table dynamically based on json data?

You need dynamic SQL for that, which carries the hazard of SQL injection.
However, done properly, this is safe against SQLi:

DO
$do$
BEGIN

EXECUTE (
   SELECT format('CREATE TABLE %I(%s)', metadata->>'tablename', c.cols)
   FROM   public.json_metadata m
   CROSS  JOIN LATERAL (
      SELECT string_agg(quote_ident(col->>'name')
                        || ' ' ||  (col->>'datatype')::regtype, ', ') AS cols
      FROM   json_array_elements(metadata->'columns') col
      ) c
   );
END
$do$;

Table and column names are treated as case-sensitive. (You may want lower-case instead.) The type name is treated as case-insensitive, and any valid type name works.

Of course, this would raise an exception for the non-existent data type String you display. Try with text instead.

Note the use of format(), the object identifier type regclass, quote_ident(), the aggregation of columns in the LATERAL subquery and the DO command to execute dynamic SQL.

Related:

How to do this binary appearance analysis
SQL injection in Postgres functions vs prepared queries
Table name as a PostgreSQL function parameter


MySQL recommends using indexes for a variety of reasons including elimination of rows between conditions: How MySQL uses Indexes.

This makes your datetime column an excellent candidate for an index if you are going to be using it in conditions frequently in queries. If your only condition is BETWEEN NOW() AND DATE_ADD(NOW(), INTERVAL 30 DAY) and you have no other index in the condition, MySQL will have to do a full table scan on every query. I'm not sure how many rows are generated in 30 days, but as long as it's less than about 1/3 of the total rows it will be more efficient to use an index on the column.

Your question about creating an efficient database is very broad. I'd say to just make sure that it's normalized and all appropriate columns are indexed (i.e. ones used in joins and where clauses).


I managed to create an array of size one instead of using a object type. Through thorough reading I found no mention of whether a nested table within a type was possible, although  varrays of nested tables are; Nested tables of varrays are also allowed.

CREATE TYPE correction_info_objtyp AS OBJECT
(
   correction_date   DATE,
   raised_by         VARCHAR(50),
   problem           VARCHAR(255)
);

CREATE TYPE correction_info_ntabtyp AS TABLE OF correction_info_objtyp;

CREATE TYPE assignment_status_info_objtyp UNDER status_info_obj
(
   corrections     correction_info_ntabtyp
);

CREATE TYPE assignement_status_info_v AS VARRAY(1) OF assignment_status_info_objtyp

CREATE table assignement_tbl
(
    assignment_id       NUMBER(6),
    assignment_title    VARCHAR(40),
    elements            elements_v,
    assignment_status   assignement_status_info_v
)VARRAY assignment_status STORE AS LOB assignement_status_store; -- contains the nested table


Can I alter a view being referenced with schemabinding if the change fulfills the binding requirement?

You don't need to drop the dependent views, but you do need to alter them to (temporarily) not be schemabinding. If any of them are indexed, this means you will need to re-create the indexes.

SQL Server doesn't have some weighting system for schemabinding: you can't change the object. Period.

ALTER VIEW dbo.view_name
--Remove this WITH SCHEMABINDING
AS
  SELECT ...


Find dates when exactly records changed for user

-- Guessed table definition
CREATE TABLE dbo.Example
(
    [ID]            integer NOT NULL,
    [Name]          char(1) NOT NULL,
    EffectiveDate   datetime NOT NULL,
    [Status]        varchar(3) NOT NULL,

    CONSTRAINT CUQ_dbo_Example_Name_EffectiveDate
    UNIQUE CLUSTERED ([Name], EffectiveDate DESC),

    CONSTRAINT PK_dbo_Example_ID
    PRIMARY KEY NONCLUSTERED ([ID])
);

-- Sample data from the question
INSERT dbo.Example (ID, Name, EffectiveDate, [Status])
SELECT 1, 'A', CONVERT(datetime, '21-10-2014', 105), 'OFF' UNION ALL
SELECT 2, 'A', CONVERT(datetime, '20-10-2014', 105), 'OFF' UNION ALL
SELECT 3, 'A', CONVERT(datetime, '19-10-2014', 105), 'On' UNION ALL
SELECT 4, 'A', CONVERT(datetime, '18-10-2014', 105), 'On' UNION ALL
SELECT 5, 'A', CONVERT(datetime, '17-10-2014', 105), 'On' UNION ALL
SELECT 6, 'A', CONVERT(datetime, '16-10-2014', 105), 'OFF' UNION ALL
SELECT 7, 'A', CONVERT(datetime, '15-10-2014', 105), 'On' UNION ALL
SELECT 8, 'A', CONVERT(datetime, '14-10-2014', 105), 'On' UNION ALL
SELECT 9, 'A', CONVERT(datetime, '13-10-2014', 105), 'OFF' UNION ALL
SELECT 10, 'A', CONVERT(datetime, '12-10-2014', 105), 'OFF' UNION ALL
SELECT 11, 'A', CONVERT(datetime, '11-10-2014', 105), 'OFF';

Solution:

-- Query
SELECT
    E.[ID],
    E.[Name],
    E.EffectiveDate,
    E.[Status]
FROM dbo.Example AS E
WHERE
    NOT EXISTS
    (
        SELECT NULL
        FROM dbo.Example AS E2
        WHERE E2.[Name] = E.[Name]
        AND E2.[Status] = E.[Status]
        AND E2.EffectiveDate = DATEADD(DAY, -1, E.EffectiveDate)
    )
ORDER BY
    E.Name,
    E.EffectiveDate DESC;

Execution plan:


query on 2 fields but 1 is a unique value

I have extended a bit your sample data:

create table tbl (fld1 text, fld2 text);
insert into tbl values
('abc',  'D123'),
('xyz',  'D123'),
('abc',  'D125'),
('abc',  'D128'),
('def',  'D128'),
('abc',  'D789');

You can use EXIST in your WHERE clause with a dataset grouped by fld2:

select
    t1.*
from
    tbl t1
where
    fld1 = 'abc'
    and exists (select 1
                from tbl t2
                where t2.fld2 = t1.fld2
                group by fld2
                having count(*) = 1);

fld1 | fld2
:--- | :---
abc  | D125
abc  | D789

db<>fiddle here

Or you can use a CTE to get all fld2 rows with a count of 1, and join it with all rows where fld1 = 'abc':

with count1 as
(
    select   fld2
    from     tbl
    group by fld2
    having   count(*) = 1
)
select
    t1.*
from
    tbl t1
join
    count1
    on t1.fld2 = count1.fld2;

db<>fiddle here


Select rows for which at least one row per set meets a condition

Basically you are looking for the expression:
client_status IS DISTINCT FROM 'n'

The column client_status should really be data type boolean, not text, which would allow the simpler expression:
client_status IS NOT FALSE

The manual has details in the chapter Comparison Operators.

Assuming your actual table has a UNIQUE or PK constraint, we arrive at:
CREATE TABLE test (
  company_id    integer NOT NULL,
  client_id     integer NOT NULL,
  client_status boolean,
  PRIMARY KEY (company_id, client_id)
);

Queries
All of these do the same (what you asked), which is the fastest depends on data distribution:
SELECT company_id, client_id
FROM   test t
WHERE  EXISTS (
   SELECT FROM test
   WHERE  company_id = t.company_id
   AND    client_status IS NOT FALSE
   );

Or:
SELECT company_id, client_id
FROM   test t
JOIN  (
   SELECT company_id
   FROM   test t
   GROUP  BY 1
   HAVING bool_or(client_status IS NOT FALSE)
   ) c USING (company_id);

Or:
SELECT company_id, client_id
FROM   test t
JOIN  (
   SELECT DISTINCT company_id, client_status
   FROM   test t
   ORDER  BY company_id, client_status DESC
   ) c USING (company_id)
WHERE  c.client_status IS NOT FALSE;

Boolean values sort FALSE -> TRUE -> NULL in ascending sort order. So  FALSE comes last in descending order. If there's is any other value available, then that one's picked first ...

Sorting null values after all others, except special

The added PK is implemented with a useful index for these queries. If you want faster, yet, add a partial index for query 1:
CREATE INDEX test_special_idx ON test (company_id, client_id)
WHERE  client_status IS NOT FALSE;

You could use window functions, too, but that would be slower. Example with first_value():
SELECT company_id, client_id
FROM  (
   SELECT company_id, client_id
        , first_value(client_status) OVER (PARTITION BY company_id
                                           ORDER BY client_status DESC) AS stat
   FROM   test t
   ) sub
WHERE  stat IS NOT FALSE;

For lots of rows per company_id, one of these techniques may be faster, still:

Optimize GROUP BY query to retrieve latest record per user


TRY TOP 10000 INSTEAD of 1 to quickly read messages from Queue that will clean the queue faster

WAITFOR ( RECEIVE TOP ( 10000)
                   CONVERT(XML, CONVERT(NVARCHAR(MAX), message_body))
                  FROM dbo.[EventNotificationQueue]

> --into @message_body

                  ), TIMEOUT 1000


How should I tune Postgresql for 20 GB of RAM?

I would recommend using pgtune written by Greg Smith.

Simply run it on your server as follows:

pgtune -i postgresql.conf -o postgresql-tuned.conf

It has few more options, but just doing that and using generated postgresql.conf will do wonders for your server performance.

I think PostgreSQL should include this in standard install, and even run it by default - such that people cannot complain about bad PostgreSQL performance.


OUTER JOIN with join condition causes unexpected results

It is not a bug, it is how LEFT JOIN works.

b LEFT JOIN j ON <SomeCondition>

would return all rows from b regardless of the join condition. Join condition defines/limits only what rows would be returned from j.

SELECT b.*
FROM #Base b
LEFT OUTER JOIN #JoinTable j ON b.Key1 = j.Key1 AND b.RefDate < '2014-01-01'
WHERE j.Key1 IS NULL

You can think of it as follows:

Go through all rows of #Base.
For each row in #Base find all rows from #JoinTable that satisfy the join criteria (b.Key1 = j.Key1 AND b.RefDate < '2014-01-01'). There could be no such rows in #JoinTable, in this case return NULLs. Note, that join criteria doesn't filter #Base, it filters only #JoinTable.

When you put b.RefDate < '2014-01-01' into WHERE, then you are filtering #Base.


Is there a way to obtain 'NaN' using only numbers?

divide infinity by infinity.

jasen=# select cot(0)/cot(0);
 ?column?
----------
      NaN


How to query if record exists for prior month in mysql

Another way is to use variables to calculate the flag. The variables will store the values of custId and orderDate as well as the calculated flag value. All three will be used to calculate the subsequent row's flag.

You will need to sort the rows by custId, then by orderDate and implement the following logic to calculate the flag values:

if the current row's custId is different from the one stored in the corresponding variable (which at this point is carrying the previous custId), set the flag to false;
otherwise, if the year and month of the current row's orderDate match those of the date stored in the corresponding variable (which, similarly, is the date from the previous row), keep the previous flag value;
otherwise, set the flag to true if the current orderId minus one month is the same year and month as the date stored in the variable;
store the current custId and the year and month of the current orderId for use in the next iteration.

The MySQL-specific SQL query for that could be something like this:

SELECT
  custId,
  orderDate,
  flag AS hasOrderInPriorMonth
FROM
  (
    SELECT
      @flag :=
        CASE
          WHEN o.custId <> @custId THEN false
          WHEN EXTRACT(YEAR_MONTH FROM o.orderDate) = @orderMonth THEN @flag
          ELSE (EXTRACT(YEAR_MONTH FROM o.orderDate - INTERVAL 1 MONTH) = @orderMonth)
        END AS flag,
      @custId := o.custId as custId,
      o.orderDate,
      @orderMonth := EXTRACT(YEAR_MONTH FROM o.orderDate)
    FROM
      (SELECT @custId := '', @orderMonth := 0, @flag := false) as x,
      orders as o
    ORDER BY
      custId    ASC,
      orderDate ASC
  ) AS s
;

A demo of this query is available at SQL Fiddle. (It borrows the test setup created by Jehad Keriaki for his own answer.)


Query not returning items in the original order

Without an ORDER BY clause, the order the values are returned in will be unreliable, as they can be influenced by indexes and the data pages the individual rows are stored on.

If you require the data to be in a particular order, use the ORDER BY clause.


In the comments you indicate that your SQL Service is running as the Network Service account.

http://msdn.microsoft.com/en-us/library/windows/desktop/ms684272(v=vs.85).aspx

  The NetworkService account is a predefined local account used by the
  service control manager. This account is not recognized by the
  security subsystem, so you cannot specify its name in a call to the
  LookupAccountName function. It has minimum privileges on the local
  computer and acts as the computer on the network.

This is the default account for installations under Vista and Windows Server 2008. During installation this account was granted Full Control file system permissions to the SQL Server data directory as seen in the File System Permissions Granted to SQL Server Per-service SIDs or Local Windows Groups section of the following article.

http://msdn.microsoft.com/en-us/library/ms143504.aspx#Reviewing_ACLs

Since you have relocated some of your database file the SQL Server service account needs permissions to the new locations. In the comments @Marian provided instructions for granting Network Service the required permissions.

  Right click the drive -> Properties -> Security -> Edit -> add the
  Network Service user -> give him full control. By default he hasn't.
  Or better, just create a specific folder and give him Full Control
  permissions there. And move the data files in that folder.

The advice to create a new folder and move the files there is excellent as well.

Any additional services that are running as the Network Service account will aslo have permissions to this folder. Ideally the SQL Services should each have their own separate account.

http://support.microsoft.com/kb/2160720

  When choosing service accounts, consider the principle of least
  privilege. The service account should have exactly the privileges that
  it needs to do its job and no more privileges. You also need to
  consider account isolation; the service accounts should not only be
  different from one another, they should not be used by any other
  service on the same server. Do not grant additional permissions to the
  SQL Server service account or the service groups. Permissions will be
  granted through group membership or granted directly to a service SID,
  where a service SID is supported. For more details please refer to
  Books Online Topic Setting Up Windows Service Accounts


DISTINCT column combination with permutations

This looks more like a stack overflow question yet Union will give a distinct combination.

SELECT concat("from",':',"to") as res From Table
UNION
SELECT concat("to",':',"from") From Table


To model the classical student - class - prof situation, you need to reverse your model. class can implement the n:m relationship between student and prof. To be precise:

[student] m -- n [class] n -- 1 [prof]

The n:m relationship between student and class would be implemented by another table. Like [participant]:

[student] 1 -- n [participant] n -- 1 [class] n -- 1 [prof]

A prof has many classes. A class has many participants. A student is participant in many classes. Student and prof are only linked by participation in classes in this model.

Generally, just because a student is linked to a professor in some way does not mean the student participates in all classes of the professor. That's an unwarranted over-interpretation. What a relationship between two entities actually means is defined by additional meta-data, usually indicated by the column names used.


1 hour query, is there another way to do this?

No way!  Every 'end' is not optimizable because of the leading wildcard.  That means that if there are, say, a mere 3K 'end' entries in match, then there will be over a billion (350K * 3K) tests to perform!

The query can be partially optimized by

INDEX(type, string) -- in `match`
INDEX(name) -- in `user`
SELECT ... FROM user JOIN match ON match.type='exact' AND user.name=match.string
UNION ALL
SELECT ... FROM user JOIN match ON match.type='begin' AND user.name LIKE CONCAT...
UNION ALL
SELECT ... FROM user JOIN match ON match.type='end' AND user.name LIKE CONCAT...;

The match INDEX minimizes the effort in finding the relevant rows.
The user INDEX is useful for the first two SELECTs.
The UNION lets the optimizer operate on each SELECT independently.
The ALL avoid a de-dupping phase that is (I assume) unnecessary.

Have you considered a FULLTEXT index?  If it works, it will be a million times as fast.  (Perhaps literally a million!)

Here's a kludge that can make the third SELECT sargable (gee, I learned a new word!) -- Reverse both the match.string and the user.name, then do the LIKE like 'begin'.  No, don't use the REVERSE function in this query, actually build the two tables (or maybe a separate pair of tables) with the reversed strings.


A performance Issue when querying a big table using a PK/clustered index

If speeding up this query is critically important, I'd consider creating a covering index:

CREATE INDEX IX_MyHugeTable_Date_Store_Qty_Amt
    ON MyHugeTable (Date, Store)
       INCLUDE (Qty, Amt)
;

Adding a new index will impact the time to add, update, and delete rows from the table. You should test to determine the impact of adding this index to the nightly update process. It won't help if this speeds up the query generating the summary data for each day, but the slowdown in inserting and updating daily changes is slowed down more than the summary query is sped up. This would hold true with some of the other suggestions from the comments; test to make sure that the changes you make do not harm normal operations.

FYI: the idea behind a covering index is simple - if an index has all the columns a query refers to, then the engine can retrieve that information from the index without actually touching the table itself. This index should take up much less space per row than the table (with around 200 columns), so the query should perform much better.

As noted by David Browne, your index would include not just the four columns listed, but the other 3 keys that make up the primary key. That's because all non-clustered indexes on a table with a clustered index use the cluster key to identify the row location in the main table. See this link for full details. Still, the index will be much narrower than your ~200 column table.


Get NULL statistics for a column in MS SQL Server

Nothing so easy. You would need to use DBCC SHOW_STATISTICS and look at the histogram, taking into account that the statistics might be filtered or multi column.
Probably easier to just let SQL Server do it and generate an estimated execution plan for the statement select * from foo where bar is null SQL Server will then use appropriate statistics if they exist or create them if they don't (assuming that the auto create statistics option is enabled).
You can then look at the estimated number of rows going into the SELECT iterator (This shows up as StatementEstRows in the XML for purposes of parsing programatically)
Example code
/*Create temporary stored procedure with query of interest*/
CREATE PROC dbo.#plan_test
AS
SELECT *
FROM master..spt_values
WHERE low IS NULL

GO

/*Generate an execution plan*/
SET FMTONLY ON
EXEC dbo.#plan_test
SET FMTONLY OFF;

/*Retrieve the plan and parse the estimated rows*/
WITH  XMLNAMESPACES ('http://schemas.microsoft.com/sqlserver/2004/07/showplan' as sql)
SELECT  query_plan.value('(//sql:StmtSimple[1]/@StatementEstRows)[1]', 'float') AS EstimatedNumberOfRows
FROM sys.dm_exec_cached_plans
CROSS APPLY sys.dm_exec_sql_text(plan_handle)
CROSS APPLY sys.dm_exec_query_plan(plan_handle)
WHERE sys.dm_exec_sql_text.objectid=OBJECT_ID('tempdb.dbo.#plan_test')
     AND sys.dm_exec_sql_text.dbid=2

DROP PROC dbo.#plan_test


Oracle: How do I query a Hierarchical table?

CONNECT BY is the correct way to handle data that is naturally recursive.

I don't know what your table looks like but maybe something like:

SELECT *
FROM some_table st
START WITH st.location = 'BLDG-01'
CONNECT BY PRIOR st.location = st.parent;

This should get nodes under "BLDG-01".

The START WITH clause is your base case.

Another explanation (aside from the Oracle one which I assume you've already read and had trouble with, it's probably very terse):

http://www.adp-gmbh.ch/ora/sql/connect_by.html

Also:

http://psoug.org/reference/connectby.html

And:

http://www.oradev.com/connect_by.jsp


SQL Server 2008-R2/2012 with Failover Cluster - minimizing downtime of program updates

I am not sure if you are proposing an in-place or migration upgrade. As this answer indicates, I prefer migration upgrades when possible.

Assuming a migration - you shouldn't need to rely on any SQL Server 2012 features to assist in the migration and reduce the downtime. For your new environment planning, you might look into AlwaysOn Availability Groups and some of the benefits and differences from using failover clustering instances. Failover Cluster Instances are still a fine option in SQL Server 2012, though. Especially when you want to use shared storage, keep a copy of the data, have the DBs, jobs, logins, etc. all just work the same on the passive node as the active node.

As to how to make the migration faster - I talk about it a bit in the linked answer above - but basically you could do a plan like this if migrating:

Build the new environment and test it/prep it for readiness
Do a test migration to it, verify apps look good and work. Hopefully you can freeze jobs and logins at this point so you have the jobs and logins you need moved over
For the actual live cutover - restore full databases to it specifying with norecovery (this is assuming your DBs are already in full recovery mode on the source, if not they'll need to be properly there.) from your latest full backup.
Using a combination of differential and transaction log backups, restore the backlog of diffs and transaction logs (again specifying with norecovery) until you get to
When you are "caught up" on your restores and have your "cutover window" - you can hold activity, do your last log restores - finally specifying with recovery on the last ones and ensure all apps are good.

That is an approach to minimize the downtime when doing a migration style. When you do an in-place it's a different story and there frankly isn't a whole heck of a lot to be done to change or speed up processing.

Obviously this is a decision you would have to have made well ahead of the upgrade - in place doesn't need new hardware.. Migration does.


SSRS: how to handle unavailability of service when datawarehouse is being rebuilt

Database Snapshots are read-only, so you couldn't run read/write ETL processes against the snapshot. However, you could point reports, end-user adhoc queries, etc. at the snapshot. This would allow you to run the ETL processes against the live database without interfering with user reporting. A new snapshot would need to be created after ETL processes complete. Depending on available storage, you could store multiple snapshot copies.
Regarding the data warehouse (DW) refresh, you may want to implement incremental load processes. Instead of mass truncate/insert operations, compare the staged source data with what already exists in the DW; Only insert/update the data that needs to change. This will give you a huge benefit in terms of resources used and time required for the ETL processes to complete.


Well I think it's quite simple.

You just get the message that Service Broker isn't enabled. But you query the system databases, not your current Database. Just as an idea, might it be, that you run the Script for setting up the notification in your master database while you forget to change the database context to your db01 where the Service Broker is enabled?
Another edge case can be the msdb, recheck if it is enabled to.
Otherwise try to enable/disable the Service Broker in your db01 and retry.

You can use this:

ALTER DATABASE dbo1 SET DISABLE_BROKER
ALTER DATABASE dbo1 SET ENABLE_BROKER


How does Couchdb calculates the document revision number?

So after some time, I got to know that couchdb calculates revision number as such:

seq_num-md5HashOfDocJsonContentWithoutRevField


MariaDB does not start after restoring incremental backups with mariabackup

When the permissions look correct, but you're still getting Errcode: 13 "Permission denied", then the next thing to check is whether a security system is causing the problem. On CentOS that system is SELinux. To check if it's running in enforcing mode, execute this:

sestatus

You can do setenforce 0 to temporarily change it to permissive mode.

To avoid the problem:

When you initially install MariaDB, the SELinux security context should be set correctly for all the files. However, when you do mv /var/lib/mysql/ /var/lib/mysql_bak/, then you remove it. I think you can get it back by simply executing restorecon -Rv /var/lib/mysql.

For reference, read this page in the MariaDB Knowledgebase: Security-Enhanced Linux with MariaDB


What difference between these 2 SQL styles?

As for the difference between INNER JOIN...ON vs WHERE clause, there is a good answer here. There are several answers there and the accepted answer pretty much summarises it all.

However, I cannot but comment that you query can be rewritten to significantly improve the performance, like this:

select wifi_name
      ,sum(case when success = 1 then 1 else 0 end)*100/count(*) as success_rate
from conn_log
group by wifi_name;

In PostgreSQL 9.4+, it is even simpler:

select
    wifi_name,
    count(*) filter (where success = 1) / count(*) * 100 as success_rate
from conn_log
group by wifi_name;


There are many open ends in your question, but partitioning by customer could to be the way to go - especially if:

you expect many customers,
each of them could have tons of data ("tons" means much more than RAM cache size),
most of their datasets will be mutually exclusive (each customer sees different subset of data).

RULEs or triggers are a performance overhead, and can be avoided.

Consider something along these lines:

BEGIN;

CREATE USER tenant1;
CREATE USER tenant2;

CREATE SCHEMA app;
CREATE SCHEMA tenant1;
CREATE SCHEMA tenant2;

CREATE TABLE app.objects_nonphysical(id int);
CREATE TABLE app.objects_physical(id int);
CREATE TABLE app.objects_mapping(id int);
CREATE TABLE tenant1.objects_nonphysical() INHERITS(app.objects_nonphysical);
CREATE TABLE tenant1.objects_physical() INHERITS(app.objects_physical);
CREATE TABLE tenant1.objects_mapping() INHERITS(app.objects_mapping);
CREATE TABLE tenant2.objects_nonphysical() INHERITS(app.objects_nonphysical);
CREATE TABLE tenant2.objects_physical() INHERITS(app.objects_physical);
CREATE TABLE tenant2.objects_mapping() INHERITS(app.objects_mapping);

GRANT USAGE ON SCHEMA tenant1 TO tenant1;
GRANT USAGE ON SCHEMA tenant2 TO tenant2;
GRANT SELECT,INSERT,UPDATE,DELETE ON ALL TABLES IN SCHEMA tenant1 TO tenant1;
GRANT SELECT,INSERT,UPDATE,DELETE ON ALL TABLES IN SCHEMA tenant2 TO tenant2;

/* TEST: simulate login as customer */
SET SESSION AUTHORIZATION tenant2;
/* No schema needed - default search_path works */
SELECT count(*) FROM objects_nonphysical;

ROLLBACK;

You do not need any triggers / rules to maintain it.

There are open ends here - that's just a draft... Some issues:

PK, FK and indexes are not "inherited".
even if you create them, the PK is not enforced on master table
you can overcome this by using same sequence for all tenants
obviously, application must be adjusted for this model


Check difference between results not listed and identify which day is the date

Check out my answer to the following question on StackOverflow

https://stackoverflow.com/questions/3093924/select-all-months-within-given-date-span-including-the-ones-with-0-values/3111441#3111441

You could easily apply this method to your problem, and return data for all the days between a range, including those with no data content.

Dave


Consolidate / sort / combine / merge data for dummies

You want to merge the data, in database terms it is called joining tables. If you want it non-script please refer here
to get a understanding how to do it non-scripted on MS-Access.


I would begin with a little tweak:

In your current design, there is no need to have "id" column on vocabularylists table. I will write it from top of my head, so apologies in advance for any syntactic mistakes. :)

First change:

CREATE TABLE IF NOT EXISTS `vocabularylists` (
  `name` varchar(20) UNIQUE NOT NULL,
  `id` int(11) NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`id`)
);

This way, you will have numeric ID and easily modifiable name of the vocabulary. This will make occasional renaming of vocabulary list a bit easier. But, of course, you can use only name columnt and totally strip out the id.

Second change:
You need to connect the vocabularies to vocabularylists with a foreign key constraint. The vocabularies table will look like this:

CREATE TABLE IF NOT EXISTS `vocabularies` (
    `vocabulary` varchar(20) NOT NULL,
    `translation` varchar(20) NOT NULL,
    `list_id` int(11),
    FOREIGN KEY (list_id) REFERENCES vocabularylists(id) ON DELETE CASCADE
);

By creating foreign key, you ensure so-called referential integrity: from now on, your database cannot store vocabulaies that do not belong to a vocabulary list. Also (as ensured by ON DELETE CASCADE) any deletes on vocabularylists automaticcaly result to deletion of corresponding entries on vocabularies.

Third change:

Querying the vocabularies table will be slow. Put indexes on your tables:

CREATE TABLE IF NOT EXISTS `vocabularylists` (
  `name` varchar(20) UNIQUE NOT NULL,
  `id` int(11) NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`id`),
  INDEX `name_idx` (`name`)
);
CREATE TABLE IF NOT EXISTS `vocabularies` (
    `vocabulary` varchar(20) NOT NULL,
    `translation` varchar(20) NOT NULL,
    `list_id` int(11),
    FOREIGN KEY (`list_id`) REFERENCES vocabularylists(`id`) ON DELETE CASCADE,
    INDEX `vocabulary_idx` (`vocabulary`),
    INDEX `translation_idx` (`translation`)
);

This will give you much better performance.

A hint:

You have a very simple structure defined here. With little data, there will be no significant problems with performance. But the only thing you really need - looking at those two db tables - is a key-value store. So instead of a database, you could easily use a plain/CSV/... file which will be loaded on application startup into some in-memory store (at the beginning a hashmap, then something like Redis maybe).

Also a word in one language can have multiple meanings in another. Your database - as it is - cannot store those things. I don't know if it is desired behavior but it will be limiting you in case you are extending your application.


Make custom aggregate function easier to use (accept more input types without creating variants)

Before going into details - are you sure you are not re-inventing a wheel? This might burn down to the very popular topic of greatest-n-per-group.

Your query:

SELECT min(value) as value, valued_min(value, name) as name FROM kv;

Can be rewritten with stock Postgres as:

SELECT value, name
FROM   kv
ORDER  BY value
LIMIT  1;

Which also can use a simple btree index on (value) or (value, name) for an index or index-only scan - much faster.

I am pretty sure any other example can be solved with built-in functionality as well. To get one row per group, your query would be:

SELECT grp_col, min(value) AS value, valued_min(value, name) AS name
FROM   kv
GROUP  BY grp_col;

Replace with:

SELECT DISTINCT ON (grp_col)
       grp_col, value, name
FROM   kv
ORDER  BY grp_col, value;

Again, faster. And much more versatile. Detailed explanation:

Select first row in each GROUP BY group?


only use extended join-clause if some variable is TRUE

In case its a (multi)-polygon, I want to extend the join-clause with additional join-clauses:

What you ask for can be achieved with plain boolean logic in the JOIN clause. Append:
AND ((st_geometrytype(PAR_geom) IN ('ST_MultiPolygon', 'ST_Polygon')) IS NOT TRUE

  OR a.path[VAR_main_id] = 1
 AND a.path[array_upper(a.path, 1)] > 1
 AND b.path[array_upper(b.path, 1)] > 1

  OR a.path[VAR_main_id] > 1
 AND b.path[array_upper(b.path, 1)] <= 4
)

Meaning, either the type is not among the listed ones ('ST_MultiPolygon', 'ST_Polygon'), or the following conditions have to be met.
Operator precedence makes AND bind before OR, so we need no additional parentheses.
The function might look like this:
CREATE OR REPLACE FUNCTION test_return(par_geom      geometry
                                     , par_tolerance float8 = 0.0000001)
  RETURNS SETOF geometry
  LANGUAGE plpgsql AS
$func$
DECLARE
   var_main_id int;
BEGIN
   CASE ST_GeometryType(par_geom)
   WHEN 'ST_LineString'  THEN
       var_main_id = 0;
   WHEN 'ST_Polygon', 'ST_MultiLineString', 'ST_MultiPoint'  THEN
       var_main_id = 1;
   WHEN 'ST_MultiPolygon' THEN
       var_main_id = 2;
   ELSE
       RAISE EXCEPTION 'this function does not work on single-points';
   END CASE;

   RETURN QUERY
   WITH repeated_pts AS (
      SELECT d.path, d.geom
      FROM   st_dumppoints(par_geom) d  -- evaluate once
      WHERE  st_npoints(st_snaptogrid(par_geom, par_tolerance)) <> st_npoints(par_geom)
   )
   SELECT b.geom
   FROM   repeated_pts a
   JOIN   repeated_pts b ON a.path[1:var_main_id] = b.path[1:var_main_id]
                        AND a.path[array_upper(a.path, 1)] < b.path[array_upper(b.path, 1)]
                        AND st_dwithin(a.geom, b.geom, par_tolerance)
                        -- append here!
                        AND ((st_geometrytype(PAR_geom) IN ('ST_MultiPolygon', 'ST_Polygon')) IS NOT TRUE

                          OR a.path[VAR_main_id] = 1
                         AND a.path[array_upper(a.path, 1)] > 1
                         AND b.path[array_upper(b.path, 1)] > 1

                          OR a.path[VAR_main_id] > 1
                         AND b.path[array_upper(b.path, 1)] <= 4
                        );
END
$func$

Might be possible to simplify further. I lack insight.


I want Powershell's invoke-sqlcmd to ignore SQL execution errors

How can I [...] temporarily make SQL tolerant of those and not raise stopping errors

Test to see if the object exists before trying to drop it or create it.  That way you'll still throw errors in the event that the DROP or CREATE itself fails rather than for collisions.

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[YourTableName]') AND type in (N'U'))
DROP TABLE [dbo].[YourTableName]
GO

IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[YourTableName]') AND type in (N'U'))
CREATE TABLE [dbo].[YourTableName] ( ... )

[Note: This syntax comes from the scripts that Microsoft.SqlServer.Management.Smo.Scripter generates.]

I'm assuming that your PowerShell scripting script is using Microsoft.SqlServer.Management.SMO.Scripter?  Why not specify Scripter.Options.IncludeIfNotExists = $true?  You get all the same options that you get with SSMS's Generate Scripts tool.  Do something like this:

Add-Type -AssemblyName 'Microsoft.SqlServer.Smo, Version=14.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91'

$ServerName = 'servername'
$DatabaseName = 'databasename'
$ScriptFileName = 'C:\scriptfilename.sql'
if (Test-Path $ScriptFileName) {
    Remove-Item $ScriptFileName
}

$SMOServer = New-Object -TypeName Microsoft.SqlServer.Management.Smo.Server -ArgumentList $ServerName
$SMODatabase = $SMOServer.Databases.Item($DatabaseName)

$Scripter = New-Object -TypeName Microsoft.SqlServer.Management.Smo.Scripter -ArgumentList $SMOServer
$Scripter.Options.AllowSystemObjects = $false
$Scripter.Options.IncludeHeaders = $true
$Scripter.Options.IncludeIfNotExists = $true
$Scripter.Options.Indexes = $true
$Scripter.Options.Permissions = $true
$Scripter.Options.ScriptBatchTerminator = $true
$Scripter.Options.ScriptSchema = $true
$Scripter.Options.TargetServerVersion = [Microsoft.SqlServer.Management.Smo.SqlServerVersion]::Version100
$Scripter.Options.WithDependencies = $true
$Scripter.Options.FileName = $ScriptFileName
$Scripter.Options.AppendToFile = $true
$Scripter.Options.ToFileOnly = $true

# Drop all tables
$Scripter.Options.ScriptData = $false # Don't script data
$Scripter.Options.ScriptDrops = $true # Script drop statements
foreach ($SMOTable in $SMODatabase.Tables) {
    $Scripter.EnumScript($SMOTable.Urn)
}

# Create all tables with data, indexes, and permissions
$Scripter.Options.ScriptData = $true # Script table data
$Scripter.Options.ScriptDrops = $false # Script create statements
foreach ($SMOTable in $SMODatabase.Tables) {
    $CreateScripter.EnumScript($SMOTable.Urn)
}

This scripts the tables using SQL Server 2008 syntax (Version100).  I'm using a version of the libraries that ships with Sql Server Management Studio 17.


MySQL: how to replicate users in master-master configuration

replicate-do-db=mysql will definitely work. I tried it on test servers.


Creat db snapshot of a db in restoring state

No, you cannot. The database has to be online.

As Scott referenced


Oracle DBA - what's the difference between the connection strings?

I have an answer for this right here:

What database name should I use to connect Tomcat with my database

Essentially there's 2 ways you can connect to the DB - with the SID or a Service Name.  In Oracle 12c and up, the option for connecting via SID is essentially gone away.

A SID is not a good way to identify a database, all it really does is identifies a particular Oracle instance running in a particular Oracle Home.

You should be using service names to connect in 11g+.


Why do VSS Snapshots cause Availability Groups to fail over?

The failover policies are configurable - so you can adjust the thresholds
https://docs.microsoft.com/en-us/sql/database-engine/availability-groups/windows/configure-flexible-automatic-failover-policy?view=sql-server-ver15
of course to do this you need to know which policy triggers the failover in the first place
https://techcommunity.microsoft.com/t5/sql-server-support/diagnose-unexpected-failover-or-availability-group-in-resolving/ba-p/318474


How can I create DB2 objects with Linux SUDOer authority?

Your OS authorities don't matter to DB2, which has its own authorization mechanism, although it is based on the OS authentication. In other words, you must have a login ID in the OS, which, if you are successfully authenticated, will be used to determine what authorizations you have from the DB2 point of view. You can read more about the model here: http://pic.dhe.ibm.com/infocenter/db2luw/v10r5/topic/com.ibm.db2.luw.admin.sec.doc/doc/c0021804.html

At a minimum after installing DB2 and creating an instance you will have the instance owner user that has sufficient authority to create a database. The creator of the database, in this case the instance owner, automatically gets the SECADM (security administrator) and DBADM (database administrator) authorities over that database, which allow that user to create objects in the database and grant various privileges to other users.

With your sudoer  rights in the OS you can su -l to the instance owner user (might be db2inst1 as this is the default) and perform whatever job you need.

As for the database migration tools, consider the IBM Database Conversion Workbench, which is a freely available plugin for IBM Data Studio that is also free.


There may be a more elegant or performant solution, but here's a simple option:

select * from mytable
 where rank <= 10000
   and (   priority = 1
        OR NOT EXISTS (SELECT 1 FROM mytable
                        WHERE rank <= 10000
                          AND priority = 1
                      )
       )
;

This works as follows:  the NOT EXISTS statement will either evaluate to TRUE for every record where the rank meets our criteria, or to FALSE for every such record. So, if none of the records that meet our other criteria have priority = 1, this is TRUE for all those records, and all records that meet our other criteria will be returned.

If there are records that meet our other criteria and have priority = 1, then this statement will be FALSE for all records that meet our other criteria. In that case, only records where the other branch of the OR is TRUE - where priority = 1 - will be returned.

NOTE: for this to work properly, the NOT EXISTS subquery must check all the query's criteria. If you actually want records where:

fieldA is between Jan 1 2017 and March 31 2017; and
fieldB is less than 240000

Then the query would have to be:

select * from mytable
 where fieldA >= '2017-01-01' AND fieldA < '2017-04-01'
   and fieldB < 240000
   and (   priority = 1
        OR NOT EXISTS (SELECT 1 FROM mytable
                        WHERE fieldA >= '2017-01-01' AND fieldA < '2017-04-01'
                          AND fieldB < 240000
                          AND priority = 1
                      )
       )
;


How to permanently fix IntelliSense poor functioning on SSMS?

SSMS 2016 is available for free. It has much better IntelliSense than in previous versions. https://msdn.microsoft.com/en-us/library/mt238290.aspx I highly recommend upgrading.

Other reasons why you may be having problems, from personal experience:

Highly utilized server makes it take longer to work. I have personally seen it take 5 minutes on a really bogged down machine.
Local machines with low resources.
Incorrect query syntax. This can cause IntelliSense to not load anything because it can not tell what it is supposed to be doing.
Poor connection between local machine and server. We just moved locations at my job, with a slower connection, and my IntelliSense load times have drastically changed.


Which user defined data type is memory optimized?

The query below will identify memory-optimized table types in the current database.

SELECT
      OBJECT_SCHEMA_NAME(tt.type_table_object_id) AS SchemaName
    , tt.name TableTypeName
FROM sys.table_types AS tt
JOIN sys.objects AS o ON
    o.object_id = tt.type_table_object_id
WHERE tt.is_memory_optimized = 1;

You can query other catalog views to get other meta-data. For example, columns
can be listed with this query:

SELECT
      OBJECT_SCHEMA_NAME(tt.type_table_object_id) AS SchemaName
    , tt.name AS TableTypeName
    , c.name AS ColumnName
    , ty.name AS ColumnTypeName
    , c.max_length AS ColumnLength
    , c.is_nullable AS IsNullable
FROM sys.table_types AS tt
JOIN sys.objects AS o ON
    o.object_id = tt.type_table_object_id
JOIN sys.columns AS c ON c.object_id = o.object_id
JOIN sys.types AS ty ON ty.system_type_id = c.system_type_id AND ty.user_type_id = c.user_type_id
WHERE tt.is_memory_optimized = 1
ORDER BY
      SchemaName
    , TableTypeName
    , c.column_id;

If you need the complete definition with indexes, constraints, etc. as DDL, script the type using SSMS Object Explorer or PowerShell.


What computations can NOT be performed in standard SQL?

loops cannot be expressed in standard SQL

That is not the case. The relevant part of the Standard is known as SQL/PSM (Persistent Stored Modules). Its procedural paradigm includes 'loops'.

However, consider that core Standard SQL has been relationally complete (by Codd's definition of that term) since at least SQL-92, allowing for 'computations' of arbitrary complexity. Also that a SQL query is more like a specification than an implementation i.e. states the intent for the DBMS to carry out as it sees fit which may itself involve 'looping'.


mysql dump import incredibly slow on my developer's machine

This answer speeded up everything a lot:

https://stackoverflow.com/a/2167641/292408

I simply

SET autocommit=0;
SET unique_checks=0;
SET foreign_key_checks=0;

at the beginning, and

COMMIT;
SET unique_checks=1;
SET foreign_key_checks=1;

at the end.

Now it took 3 minutes.

(Courtesy of @andreasemer via twitter)


Pivot time series into date period columns with array aggregate cells

This question is old, but you haven’t accepted any answer yet, so I will add another one.

You need some aggregation of your data, and a pivot table. The most elegant way to do the former is by means of a CTE, and the most elegant way to do the latter is with CROSSTAB; however, as of Postgres 9.6, and unlike in other DBMS, you cannot reference a CTE from CROSSTAB. I will show an example for each of the two possible ways out: 1) Use a CTE, and reimplement a poor man’s pivoting on your own 2) Instead of a CTE, create a view once for all and reference it in your CROSSTAB queries. In both cases you’ll have to issue only one query per report, and you won’t have to create any temporary table.

The general problem of pivots is that, in pure SQL, you cannot define a query whose result has a variable number of columns, and you cannot define column headings dynamically. If you want that, you have to build your query with a procedural language, either server-side (plpgsql, as in Abelisto’s answer) or client-side (PHP, java, whatever...). My examples below are in pure SQL, so they have a fixed number of days (three, as in your example data), with fixed column headings ("day 1", "day 2", "day 3"), but they are built in a way that minimizes the needed edits when you change them.

First, the initial data. I started from the ones joanolo used, but my approach is different, because I use SMALLINT instead of BOOLEAN for result. My reason for doing this will become clear in the following.

CREATE TABLE time_series (
  date_time TIMESTAMP NOT NULL,
  entity TEXT NOT NULL,
  result SMALLINT DEFAULT 0 -- 1 means success, 0 failure.
);

INSERT INTO time_series VALUES
  ('2016-01-01 11:00', 'a', 1),
  ('2016-01-01 17:00', 'a', 1),
  ('2016-01-01 11:01', 'b', 0),
  ('2016-01-01 11:03', 'c', 1),
  ('2016-01-01 13:00', 'd', 1),
  ('2016-01-02 11:00', 'a', 1),
  ('2016-01-02 11:01', 'b', 0),
  ('2016-01-03 11:03', 'e', 1),
  ('2016-01-03 11:04', 'e', 1),
  ('2016-01-03 11:05', 'e', 1),
  ('2016-01-03 11:06', 'e', 0);

You only really need an array of two integers (a in my examples): a[1] (total count) and a[2] (success count). The failure count is simply a[1] - a[2] and the success percentage is 100*(a[2]::float)/a[1]. You can compute the total count by COUNT(result); if you define result SMALLINT you can simply use SUM(result) to keep track of the success count. If you store result as BOOLEAN, you have to use SUM(CASE WHEN result THEN 1 ELSE 0 END). If you store them as strings, SUM(CASE WHEN result = 'success' THEN 1 ELSE 0 END). If you cannot change your time_series table, edit the code below as appropriate.

Solution 1

This is inefficient ad rather ugly, but it’s worth being shown to illustrate how to use a CTE and what pains we had to suffer before CROSSTAB came along. When changing intervals, it has to be modified in four places: initial day, final day, rows in the main selection list, and rows in the list of joined tables. However, using a numeric column rn allows not to write explicit dates in the joined tables, which simplifies the task.

WITH ct AS (
  SELECT EXTRACT('days' FROM day - MIN(day) OVER()) + 1 AS rn, sub.*
    FROM (
     SELECT
          entity,
          DATE_TRUNC('day', date_time) AS day,
          ARRAY[COUNT(result), SUM(result)] AS a
       FROM time_series
      WHERE date_time BETWEEN TIMESTAMP '2016-01-01'                 -- initial day
                          AND TIMESTAMP '2016-01-03 23:59:59'        -- last second of final day
      GROUP BY 1,2
    ) AS sub
)
SELECT e.entity
       , d1.a AS "day 1"                                             -- add as many as you need
       , d2.a AS "day 2"
       , d3.a AS "day 3"
  FROM (SELECT DISTINCT entity FROM ct) e
  LEFT JOIN (SELECT entity, a FROM ct WHERE rn = 1) d1 USING(entity) -- add as many as you need
  LEFT JOIN (SELECT entity, a FROM ct WHERE rn = 2) d2 USING(entity)
  LEFT JOIN (SELECT entity, a FROM ct WHERE rn = 3) d3 USING(entity)
  ORDER BY e.entity;

 entity | day 1 | day 2 | day 3
--------+-------+-------+-------
 a      | {2,2} | {1,1} |
 b      | {1,0} | {1,0} |
 c      | {1,1} |       |
 d      | {1,1} |       |
 e      |       |       | {4,3}

Solution 2

The code for the view is essentially the CTE code of the previous example, but it is simpler, because CROSSTAB allows us using GENERATE_SERIES with timestamp values, so we don’t need a numeric rn column to categorize the data. Note that this view, once created, will not need modifications.

CREATE VIEW ts_view AS
  SELECT
      entity,
      DATE_TRUNC('day', date_time) AS day,
      ARRAY[COUNT(result), SUM(result)] AS a
    FROM time_series
    GROUP BY 1,2;

This is the main query. When changing intervals, it has to be modified in three places: initial day, final day, and output columns. Formatting is best done client-side, but in this case I have done it server-side. Instructions on how to change this are in the comments

SELECT * FROM CROSSTAB ($$
    SELECT
        entity,
        day,
        -- You have to repeat the result type of the following expression
        -- as the type of the "day N" columns below.
        -- e.g.  a --> INTEGER[] ,  100*(a[2]::FLOAT)/a[1] --> FLOAT , etc.
        -- In TO_CHAR, D is changed to your locale's decimal point
        TO_CHAR(100*(a[2]::float)/a[1], '990D99')||'%'
    FROM ts_view ORDER BY 1
  $$,$$
    SELECT GENERATE_SERIES (
      TIMESTAMP '2016-01-01',  -- initial day
      TIMESTAMP '2016-01-03',  -- final day
      '1 day'
    )
  $$
) AS (
    entity TEXT
    , "day 1" TEXT             -- add as many as you need
    , "day 2" TEXT
    , "day 3" TEXT
);

 entity |  day 1   |  day 2   |  day 3
--------+----------+----------+----------
 a      |  100.00% |  100.00% |
 b      |    0.00% |    0.00% |
 c      |  100.00% |          |
 d      |  100.00% |          |
 e      |          |          |   75.00%

As a final remark, the second argument of CROSSTAB can be something like

$$
WITH n(ow) AS (VALUES(DATE_TRUNC('day', NOW())))
SELECT GENERATE_SERIES(n.ow + '-2 days', n.ow, '1 day') FROM n
$$

and the query would always return a dynamic report for the last three days (today included).


Writing SQL injection proof dynamic DDL in Oracle

DBMS_ASSERT might be what you're looking for:

╔═════════════════════════════╦══════════════════════════════════════════════════════════════════════════════════════════════════╗
║         Subprogram          ║                                           Description                                            ║
╠═════════════════════════════╬══════════════════════════════════════════════════════════════════════════════════════════════════╣
║ ENQUOTE_LITERAL Function    ║ Enquotes a string literal                                                                        ║
║ ENQUOTE_NAME Function       ║ Encloses a name in double quotes                                                                 ║
║ NOOP Functions              ║ Returns the value without any checking                                                           ║
║ QUALIFIED_SQL_NAME Function ║ Verifies that the input string is a qualified SQL name                                           ║
║ SCHEMA_NAME Function        ║ Verifies that the input string is an existing schema name                                        ║
║ SIMPLE_SQL_NAME Function    ║ Verifies that the input string is a simple SQL name                                              ║
║ SQL_OBJECT_NAME Function    ║ Verifies that the input parameter string is a qualified SQL identifier of an existing SQL object ║
╚═════════════════════════════╩══════════════════════════════════════════════════════════════════════════════════════════════════╝

That same page also links to some examples on how you can avoid SQL injection.


Optimal table with N:1 foreign key column

I would just put a non-clustered index on ThingID.  A and declare the relationship.


You can use a numbers table for this. Here's a very simple way to create one that will support 5 year+ ranges:

CREATE TABLE dbo.Numbers(Number INT PRIMARY KEY);

INSERT dbo.Numbers SELECT ROW_NUMBER() OVER (ORDER BY name)
FROM sys.all_objects;

Now, given the sample data:

CREATE TABLE #CN(ID INT, StartDate SMALLDATETIME, EndDate SMALLDATETIME);

INSERT #CN(ID, StartDate, EndDate)
SELECT           1417,'2015-09-30 09:45','2015-10-10 09:45'
UNION ALL SELECT 973 ,'2015-09-14 11:03','2015-09-24 11:03';

We can join against the numbers table like this in order to produce the result you're looking for:

;WITH cn AS
(
  SELECT ID,
         s = DATEADD(DAY, 0, DATEDIFF(DAY, '19000101', StartDate)),
         e = DATEADD(DAY, 0, DATEDIFF(DAY, '19000101', EndDate))
  FROM #CN
)
SELECT cn.ID, DATEADD(DAY, n.Number-1, cn.s)
  FROM cn
  INNER JOIN dbo.Numbers AS n
  ON n.Number <= DATEDIFF(DAY, cn.s, cn.e) + 1;

Clean up your #temp table:

DROP TABLE #CN;


Is there any significant performance difference when using string vs bigint as a primary key in Postgres (Aurora)?

The question of performance can only be assessed if you know the exact SQL statement. There is no way to answer it in general.
That said, you should go with bigint. A bigint takes 8 bytes of storage, while a text with the value in your question requires 18 bytes (1 byte compressed TOAST header). This becomes particularly relevant if you want an index on the column.
Besides, comparing numbers is faster than comparing strings.


Slow-running query returns incomplete resultset

In SQL what's how many CPU's do you have for that server? What's your MAXDOP and Cost threshold for parallelism'?

One thing you can do to check to see if your cost threshold is too low is run the same query with OPTION (MAXDOP 1)

SELECT cc.ReportID,
       cc.ReportDate,
       cc.UnitID,
       cc.Callsign,
       cc.SpeciesCode,
       cc.ComName,
       cc.CatchWeight,
       cc.DayGrid,
       cc.NightGrid,
       cc.UnitEffortType,
       cc.UnitEffort,
       cc.Depth,
       cc.ReportType,
       cc.EffortDesc,
       cc.LicenceUsed,
       vn.ValidFrom,
       vn.Nation,
       vn.NationCode,
       vg.GRT
FROM   vwVesselGRT vg
       INNER JOIN vwVesselNationality vn
         ON vg.UnitID = vn.UnitID
       INNER JOIN vwClientCatch cc
         ON vn.UnitID = cc.UnitID
            AND vn.ValidFrom <= cc.ReportDate
            AND vn.ValidTo > cc.ReportDate
WHERE  cc.ReportDate <= '2016-04-30'
OPTION (MAXDOP 1);

Without a total re-write you should be able to Schema Bind your Views you can add indexes on your views without indexing the underlying tables.

Best option if this is going to be something you use often though is to re-write your 3 views into a single query and then make your indexes based on that.

How bad is your query plan for just selecting from each of the views every single bit of that query Plan screams re-write to me though.

Views are like a saved select I wouldn't really use them for anything an application depends on.

as a temporary fix without any tuning you can just run this

SELECT cc.ReportID,
       cc.ReportDate,
       cc.UnitID,
       cc.Callsign,
       cc.SpeciesCode,
       cc.ComName,
       cc.CatchWeight,
       cc.DayGrid,
       cc.NightGrid,
       cc.UnitEffortType,
       cc.UnitEffort,
       cc.Depth,
       cc.ReportType,
       cc.EffortDesc,
       cc.LicenceUsed,
       vn.ValidFrom,
       vn.Nation,
       vn.NationCode,
       vg.GRT
INTO   BadQueryTempFixTable
FROM   vwVesselGRT vg
       INNER JOIN vwVesselNationality vn
         ON vg.UnitID = vn.UnitID
       INNER JOIN vwClientCatch cc
         ON vn.UnitID = cc.UnitID
            AND vn.ValidFrom <= cc.ReportDate
            AND vn.ValidTo > cc.ReportDate
WHERE  cc.ReportDate <= '2016-04-30'

Then From the other side just query

SELECT ReportID,
       ReportDate,
       UnitID,
       Callsign,
       SpeciesCode,
       ComName,
       CatchWeight,
       DayGrid,
       NightGrid,
       UnitEffortType,
       UnitEffort,
       Depth,
       ReportType,
       EffortDesc,
       LicenceUsed,
       ValidFrom,
       Nation,
       NationCode,
       GRT
FROM   BadQueryTempFixTable


Running total until specific condition is met

As suggested in comments, I can get the latest id of the order in a single position by watching running total goes to zero. Then I can just select orders in range

declare @latestId int;
WITH T1
     AS (SELECT *,
                SUM(qty) OVER (ORDER BY id ROWS UNBOUNDED PRECEDING) AS total
         FROM   [dbo].[orders])
SELECT top (1) @latestId = id
FROM   T1
WHERE  total <= 0
order by id

select * from [dbo].[orders] where 1 <= id and id <= @latestId


Storing data files in separate physical drives

To answer the question, generally the best option is the S.A.M.E. for all databases!

Stripe And Mirror Everything - also known as RAID 10 (or RAID 1+0).

  According to manufacturer specifications and official independent
  benchmarks, in most cases RAID 10[8] provides better throughput and
  latency than all other RAID levels[9] except RAID 0 (which wins in
  throughput).[10] Thus, it is the preferable RAID level for
  I/O-intensive applications such as database, email, and web servers,
  as well as for any other use requiring high disk performance.[11]

As you can see from the oracle-base link (an absolutely super site for all things Oracle BTW), you can see that this is the preferred general RAID level for Oracle - with the proviso that one can delve a bit deeper and use other RAID levels for different file types (data, logs, control files...). See the table in the link below,

but this is the take-home message (IMHO) - in the RAID Levels section:

  1+0 or 10     As the name suggests this is a combination of RAID 1 and
  RAID 0. This sounds like it should be the same as 0+1, but it is
  subtly different. Each disk is mirrored individually and striping
  occurs across all the mirrored pairs. This is the best RAID for
  Oracle.

So, 1+0 is definitely the preferred option.

Microsoft's page RAID Levels and SQL Server also says:

  RAID 10 provides the performance benefits of disk striping with the
  disk redundancy of mirroring. RAID 10 provides the highest
  read-and-write performance of any one of the other RAID levels, but at
  the expense of using two times as many disks.

Note that last bit but at the expense of using two times as many disks - one gets nothing for nothing! "Yae cannae beet the laws o' physics, Jim..." (with apologies to Gene Roddenberry).

For completeness, Severalnines (a top PostgreSQL consulting company) says:

  Setting up RAID for the database drives not only protects from data
  loss, it can also improve performance if using the right RAID
  configuration. RAID 1 or 10 are generally thought to be the best, and
  10 offers parity and overall speed.

and for MySQL, consider Percona's advice (Percona are a very highly regarded MySQL consulting group with their own fork of the server):

  The best performance without making compromises to redundancy is
  achieved by the use of an advanced controller with a battery-backed
  cache unit and preferably RAID-10 volumes spanned across multiple
  disks.

So, again generally, RAID 1+0 is considered the optimal solution. However, if you read around this topic, you will see that there are issues such as expense - no organisation has infinite resources, so decisions are sometimes made on budgetary grounds not to go with RAID 1+0 but rather RAID 5.

This, IMHO, is a huge mistake - it's a false economy. You might be saving some money on disks, but your employees' and customers' time (and sanity) are valuable resources in their own right (painful past experience, but I've had therapy and I'm OK now...).

So, ideally, it is better to spread data across disks. To answer the part of your question about if one is running more than one database on the same server, then questions of multi-tenancy arise.

For example, you have to ask yourself "If I take down the system, that's two clients gone or would I rather be able to take them down separately if required?". That's a question which only the stakeholders in your own organisation can answer - Management, Customers, DBA's...

Personlly, I would keep separate clients' data as separate as possible, but again you have budget considerations - a separate disk subsystem for each client, software licencing issues and the like. My advice is to read deeply around the area in order to be able to provide a sound strategy for your own organisation given the resources to hand.

p.s. +1 for an interesting question and welcome to the forum!


If you delete 40GB of indexes, then certain backups will be smaller, such as:

Full
File
Filegroup

Some backups won't generally be smaller, such as:

Log

Differential backups (Full, File, Filegroup) may or may not be much smaller as it depends if any objects those non-clustered indexes are created on are actually having data modifications happening.

Some backup sizes might not go down by 40GB, if for example compression is already being used or those indexes are on a read_only filegroup that isn't generally backed up via filegroup backups.

However, overall, the answer is "Yes, it should be smaller".


What happens when SQL Server is in Single User Mode?

So in single_user mode - you would be quite unlikely to have locking problems in that database. It is what it sounds like - single user - and it doesn't mean Single Username - it means one user. So it's used when you as a DBA want to do something that can't be done with others users in. Maybe you are trying to do a repair option of a checkdb. Maybe you are trying to change some object metadata and don't have a better way to kick other users out. Etc.

But it really isn't an isolation level "thing" it's really an "access thing" - if you want to eliminate or avoid locking - there are a lot of things to look at such as better performing queries, snapshot isolation levels, improvements to code, etc.


It's possible and not ;) Basicly SSAS allow only to have a Windows Authentication. If you want to restrict some data/cubes and so on you can prepare a role and everything should work fine.

Another solution that you can use is the dynamic security. You can add to ssas a table with a user mapping and then modify the cube to return only atributes for current user (windows user)

Last solution that match the best for you, but is not good at all is to share the ssas via http (IIS Server and msmdpump.dll). Than you can connect to ssas using for example one windows account and than in the CUstomData connection stirng parameter send the user sql id and check this custom data in the ssas via custom assemblies. It's a horros in management so i am not recommanding this solution. Addictionally if you will want to connect to this cube from Excel you will write some addin to excel or teach the user to prepare a connection string... Don't do that..

You can also write a script using AMO to crate dynamicly the ssas Role. You can read the sql user make for him the ssas role and set what he can see in your cube.

Maybe the best solution for you will be to use the SSRS reports where you can easily set some custom authentication, what do u think?


PGSQL: Which is faster, many constraints or a single trigger?

A short answer would be: generally check constraints are faster than triggers.

However, if you think a trigger will be easier to maintain than multiple check constraints, do not hesitate and try this solution. Performance deterioration will not be high. I used this solution a few times, in all cases the triggers were up to 10-15% slower than check constraints. In my opinion this is not an exorbitant price for the extra capabilities and flexibility that triggers give.

An additional important advantage of triggers is that you may reject incorrect rows instead of raising an exception, which can be particularly comfortable in bulk inserts or updates. Information on rejected rows should be stored in some way, e.g. in a form of a log, in an auxiliary (temporary) table or using notifies.