Postgres client unable to detect UTF-8 database Context

I am deploying a Vagrant box for my development team, and have encountered a problem restoring UTF-8 collated dumps. I don't believe it is related to the now-infamous "UTF8" has no equivalent in "LATIN1" problem, as my issue seems to lie in the client. Additionally, I have successfully restored exactly the same dump files on other Precise boxes (though precise64) with the same client and server packages (9.1). However, I am uncertain if other supporting libraries may not be present in my Vagrant box.

I can not alter the way in which the dump files are created, but I don't believe the problem lies there, anyway.

Problem

I successfully programatically create and restore a UTF-8 (specifically, en_CA.UTF-8) dump by creating a table with:

createdb --lc-collate=en_CA.UTF-8 --lc-ctype=en_CA.UTF-8 -E UTF8 -T template0 ${SCHEMA}

(If relevant, I'll add that my other successful restores (on the precise64 system) did not require any additional locale parameters to createdb.)

Then restore,

pg_restore -d ${SCHEMA} --single-transaction /tmp/${SCHEMA}_-_latest.backup

I have attempted with both postgresql.conf's client_encoding commented out and manually set to client_encoding = 'UTF8'; both behave the same.

My /etc/default/locale contains

LC_ALL=en_CA.UTF-8
LANG=en_CA.UTF-8

And yet, at the psql prompt:

schema=# SELECT * FROM table;
ERROR:  character 0xe28099 of encoding "UTF8" has no equivalent in "LATIN1"

schema=# show server_encoding; show client_encoding;
 server_encoding
-----------------
 UTF8
(1 row)

 client_encoding
-----------------
 LATIN1
(1 row)

The schema is UTF-8, right (so the commented-out client_encoding should use this value)?

schema=# SELECT pg_encoding_to_char(encoding) FROM pg_database WHERE datname = ...
pg_encoding_to_char
---------------------
 UTF8
(1 row)

Setting it manually at the command prompt works:

schema=# set client_encoding to UTF8
SET
schema=# show client_encoding;
 client_encoding
-----------------
 UTF8
(1 row)

schema=# SELECT * FROM table;
<EXPECTED DATA>

What have I done wrong?

EDIT for Craig:

vagrant@precise32:~$ locale
LANG=en_CA.UTF-8
LANGUAGE=
LC_CTYPE="en_CA.UTF-8"
LC_NUMERIC="en_CA.UTF-8"
LC_TIME="en_CA.UTF-8"
LC_COLLATE="en_CA.UTF-8"
LC_MONETARY="en_CA.UTF-8"
LC_MESSAGES="en_CA.UTF-8"
LC_PAPER="en_CA.UTF-8"
LC_NAME="en_CA.UTF-8"
LC_ADDRESS="en_CA.UTF-8"
LC_TELEPHONE="en_CA.UTF-8"
LC_MEASUREMENT="en_CA.UTF-8"
LC_IDENTIFICATION="en_CA.UTF-8"
LC_ALL=

psql detects the client_encoding from the LC_CTYPE variable in the environment; this falls back to LC_ALL and then LANG if unset.

In the terminal you're launching psql from, run locale. e.g.

$ locale
LANG=en_US.UTF-8
LC_CTYPE="en_US.UTF-8"
LC_NUMERIC="en_US.UTF-8"
LC_TIME="en_US.UTF-8"
LC_COLLATE="en_US.UTF-8"
LC_MONETARY="en_US.UTF-8"
LC_MESSAGES="en_US.UTF-8"
LC_PAPER="en_US.UTF-8"
LC_NAME="en_US.UTF-8"
LC_ADDRESS="en_US.UTF-8"
LC_TELEPHONE="en_US.UTF-8"
LC_MEASUREMENT="en_US.UTF-8"
LC_IDENTIFICATION="en_US.UTF-8"
LC_ALL=

Paste the result as an edit to your question. I strongly suspect your terminal locale will be en_CA, not en_CA.UTF-8.


How to store a Preferred Contact? I'm creating a CRM type application for a client. They need to store information about Companies and information about Contacts within those companies. Each Company can have multiple Contacts associated with them but only one of the Contacts in each Company can be the "Preferred" contact.

So I have the following two tables:

Company
PK: ID
Name
Tel
etc

Contact
PK: ID
FK: CompanyID
Name
Tel
etc

I can see two ways to add the Preferred Contact attribute. I could put a FK:PreferredContactID field on the Company table or I could put a PreferredContact boolean field on the Contact table. The former means that I would have a circular relationship the latter means that I have to have a constraint "Only one Contact within a Company can be the Preferred".

My question is which of these is the best option and why or is there a better way?

I would not be worried about a circular reference: they are common in data modelling (any tree/forest/graph structure for instance) so your tools should cope with it.

The "property of the contact" method is probably what I would default to, as it is the sort of thing where the rules are likely to change (if a company decides two people should get all important comms they might want to list two preferred contacts) and this method feels more flexible in that respect.

Other than that, from a design point of view as your rules currently stand (at least the ones that you've mentioned) either works as well from an integrity point of view as both almost enforce the "at most one per company rule" (though "property of a contact" requires an extra constraint/index to do so) but in either case you need to wrap custom some logic either as triggers or functions in other layers of your application logic, to enforce the rest of the rule (enforcing that there should be at least on preferred contact if any contacts exist, dealing with people who move between companies if the model considers that (rather than treating such a move as a delete and a new person being added), and so forth).


MYSQL: Values of a column where two other columns have same value I have a table say table1 with three columns lets name them col1 col2 and col3. I have a suspicion that col2 and col3 have some values that are identical i.e. to say there are some rows where col2 = col3. In case, if for these rows col1 is the same I will have no problem in my application. But how to check for those where col1 = col2 but they don't point to the same col1 value.

I mean SELECT * FROM table1 WHERE col2 = col3 gives me records for col2 = col3 in same rows. what I want for each col2 value search col3 if it is same send those records

Something like this (this is pseudo SQL) but in MYSQL

SELECT DISTINCT col2 FROM table1
foreach col2
    same_records = SELECT DISTINCT col1 FROM table1 WHERE col2 = col3
    records UNION same_records
end foreach
SELECT records

Is there is a way to get these results (if they exist)? I believe that I have to use self-join on the table.

You mean something like

SELECT col2, col3 FROM table WHERE col2 = col3 GROUP BY col2, col3
HAVING COUNT(DISTINCT col1) > 1;

Would show you the col2/col3 combinations where col2 = col3 but col1 differs between those rows.


Percona and MariaDB-10.0 ignoring not null on table fields On the percona-5.6 and MariaDB-10.0 servers, the NOT NULL declared on table field are being ignored. I am able to leave fields declared as NOT NULL empty when inserting data and the servers are not throwing an error or stopping the insertion from going on. When i try it on MySQL-5.6 and MariaDB-5.5, it works fine as expected and does not allow yo to insert empty values on fields declared as NOT NULL.

Please, advise why this is the case on mariaDB-10.0. Thanks

This is taken from the MySQL documentation:

  Changes in MySQL 5.7.1 (2013-04-23, Milestone 11)

  ...
  If a column is declared as NOT NULL, it is not permitted to insert NULL into the column or update it to NULL. However, this constraint was enforced even if there was a BEFORE INSERT (or BEFORE UPDATE trigger) that set the column to a non-NULL value. Now the constraint is checked at the end of the statement, per the SQL standard. (Bug  #6295, Bug#11744964).
  ...

Basically to change this behavior can do the following:

MariaDB> SET SESSION sql_mode := 'NO_AUTO_VALUE_ON_ZERO,STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION';
MariaDB> SELECT @@GLOBAL.sql_mode 'sql_mode::GLOBAL',
          @@SESSION.sql_mode 'sql_mode::SESSION';
+------------------------+-----------------------------------------------------------------------------------------------------------------+
| sql_mode::GLOBAL       | sql_mode::SESSION                                                                                |
+------------------------+-----------------------------------------------------------------------------------------------------------------+
| NO_ENGINE_SUBSTITUTION | NO_AUTO_VALUE_ON_ZERO,STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION |
+------------------------+-----------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

MariaDB> SHOW CREATE TABLE `table_name`;
+------------+----------------------------------------------------------------------------+
| Table      | Create Table                                                               |
+------------+----------------------------------------------------------------------------+
| table_name | CREATE TABLE `table_name` (                                                |
|            |        `id` INT(11) UNSIGNED NOT NULL,                                     |
|            |        `field` VARCHAR(20) DEFAULT NULL,                                   |
|            |        `field_with_default_value` VARCHAR(20) NOT NULL DEFAULT 'myDefault' |
|            | ) ENGINE=InnoDB DEFAULT CHARSET=latin1                                     |
+------------+----------------------------------------------------------------------------+
1 row in set (0.00 sec)

MariaDB> INSERT INTO `table_name`(`id`, `field`, `field_with_default_value`)
   VALUES
   (1, 'Value', NULL);
ERROR 1048 (23000): Column 'field_with_default_value' cannot be null

I hope this a light on the road.


Query execution time in Oracle SQL Developer I know I can use set timing on to see how long it took to execute a query after the execution is finished.

But is it possible to see current execution duration in Oracle SQL Developer while the query is still running?

SQL Server Management Studio as well as old Query Analyzer have such a feature:

On recent versions you can see a timer in the "Task Progress" view (View->Task Progress). Annoyingly it resets to zero the moment the task finishes! :


Why does the log file grow larger than data file for an availability group database on the secondary replica? I've got a SQL Server 2016 AG set up with only two replicas (primary and secondary). Both synchronous, non-readable secondary, and backup the primary only.
Transaction Log backups occur on the primary replica via scheduled SQL Jobs that execute on both replicas every half hour. Full backups occur nightly. FULL recovery model.
The issue is over time the secondary replica log file eventually grows to be larger than the data file, while the primary remains fine. The only thing I do that helps is a manual failover, which seems to kick off the log backup jobs on the (now old) secondary replica since its readable again. Any tips on how to handle this?

Since the log file is only growing on the secondary replica, something is preventing transaction log truncation on that database (but not on the primary).
You'll need to check the value of log_reuse_wait_desc in sys.databases on the secondary replica to see why the transaction log isn't being cleared there.

Since log_reuse_wait_desc is currently showing "NOTHING" you should check on the health of the AG itself.  Open up the AG dashboard (in SSMS, under "Always on High Availability" right-click the AG and choose "Show Dashboard).  Check to see if there are any errors or warnings, and confirm that the secondary replica is listed and is healthy / synchronized.
Additional, check out the SQL Server error log on the secondary to see if there are any errors or warnings related to the AG or the database in question.  This is a weird one!


psql does not require a password on macOS with Postgres.app When opening the psql console, there is no password prompt. This behaviour is the same for all postgres users. I can basically do everything without a password. This seems rather unsafe.

How do I enable password checking? I'm running macOS Mojave and use Postgres.app with postgresql version 10.

This is the command I use:

psql -Umyuser # this user has a password set

Found the answer in another post on stackexchange.

I've edited the authentication lines in pg_hba.conf and changed "trust" into "password" for all lines. It now looks like this:

# TYPE  DATABASE        USER            ADDRESS                 METHOD

local   all             all                                     password
host    all             all             127.0.0.1/32            password
host    all             all             ::1/128                 password
local   replication     all                                     password
host    replication     all             127.0.0.1/32            password
host    replication     all             ::1/128                 password

Now psql asks me for a password every time.


MySQL design problem I have nearly no experience in database design, so forgive me if I'm asking something super nooby and ridiculous :)

I have an item I need to create. This item will be constructed from 3-4 types of objects, with multiple object instances. Basically as per below:

Object 1: only 1 instance per item
Object 2: extends Object 1 and shares a foreign key, may have multiple instances, one instance mandatory
Object 3: extends Object 2 and shares a foreign key, may have multiple instances, one instance mandatory . There are 3 types of Object 3 which are dependent on a field from Object 2 (haven't yet figured out how these need to link without code).
Object 4:  extends Object 3 and shares a foreign key, may have multiple instances, not mandatory, so may be omitted.

Is there a way to dynamically generate Object 3 through MySQL? Like if field #3 shows Object 3.1 call that table, if it shows 3.2 call that instead. Similar to a constructor.

Also is there a way for me to map related objects? For example, since everything is dependent on Object 1, I'd like to be able to call Object 1 and have it pull all related instance of Objects 2, 3, and 4 related to that specific object. I was thinking of creating a separate table to hold primary keys of related objects, but I'm thinking there is probably a better way of doing that.

Also I've read that I shouldn't use stored procedures, and instead do everything with code. Is that correct, because I would think for my model it would be more convenient and cleaner to just call on Object one, and have MySQL do all the joins internally and return a single completed object

Thank you very much for your help.

My question is this, is there a way to dynamically generate Object 3 through MySQL.

When I hear "generate," I tend to interpret that as "insert," but I think you're asking whether MySQL could dynamically select and return the appropriate related records to the application based on certain criteria.

If that's what you're asking, then it seems you have correctly surmised that one sensible way to implement that is indeed with stored procedures...

  Also I've read that I shouldn't use stored procedures, and instead do everything with code

Nonsense, I say.  Disregard that advice.  Stored procedures, used properly, can be used to build an excellent interface between the database tables and the application.

One aspect in particular seems to suit your request:

  MySQL supports a very useful extension that enables the use of regular SELECT statements (that is, without using cursors or local variables) inside a stored procedure. The result set of such a query is simply sent directly to the client. Multiple SELECT statements generate multiple result sets, so the client must use a MySQL client library that supports multiple result sets. -- http://dev.mysql.com/doc/refman/5.5/en/stored-routines-syntax.html

Of course, like any other tool, there are inevitably people who come up with ways of misusing them, but this doesn't reflect on the suitability of the tool when properly applied.

But a single call to the database from your application could retrieve the primary object and related data, with stored procedures.

  I was thinking of creating a separate table top hold primary keys of related objects

That sounds like you're describing a junction table...

  but I'm thinking there is probably a better way of doing that.

If the relationship among objects is many-to-many, then this is the appropriate way of expressing that relationship... but not when it isn't.


MariaDB Galera Arbitrator failing to sync with SSL So the scenario is this. I have already setup a 4 node MariaDB Galera cluster (10.5). I'm also adding on top a 5th Galera Arbitrator and everything syncs and connects just fine. However when I enable SSL on the cluster ( after bootstrapping it from scratch ) I'm able to sync the 4 nodes but the Arbitrator for some reason does not sync.
Interesting is that when the nodes are syncing they're outputting "connecting...ssl://172.31.0.4:4567".
However when the arbitrator is trying to sync it's outputting "connecting...tcp://172.31.0.4:4567".
Seems like it's trying over tcp for  some reason.
It's config is this when I start it up
    name:    garb
    address: gcomm://mariadb-galera-0:4567,mariadb-galera-1:4567,mariadb-galera-2:4567,mariadb-galera-3:4567,mariadb-galera-arb:4567
    group:   scluster
    sst:     trivial
    donor:
    options: socket.ssl_key=/etc/ssl/galera/server-key.pem;socket.ssl_cert=/etc/ssl/galera/server-cert.pem;socket.ssl_ca=/etc/ssl/galera/ca-cert.pem;socket.ssl_cipher=AES128-SHA; gcs.fc_limit=9999999; gcs.fc_factor=1.0; gcs.fc_master_slave=yes
    cfg:
    log:

It doesn't give me an ssl or cert error just timing out
INFO: (8bef8261-9d27, 'tcp://0.0.0.0:4567') connection to peer 00000000-0000 with addr tcp://172.31.0.4:4567 timed out, no messages seen in PT3S, socket stats: rtt: 39 rttvar: 19 rto: 200000 lost: 0 last_data_recv: 3500 cwnd: 10 last_queued_since: 3499991400 last_delivered_since: 3499991400 send_queue_length: 0 send_queue_bytes: 0
The pem files are exactly the same that I'm using across all nodes + the arbitrator.
I read in the documentation that I need to specify the cipher otherwise I'll be getting an error. I tried AES128-SHA and AES128-SHA256 and not declaring it at all but still timing out.
Below is the wsrep provider options from the phpmyadmin from one of the nodes. The cipher seems empty but then again why is the arbitrator trying to connect to "tcp://" and not "ssl://" like the others ?

Thank you for helping

There is a flag socket.ssl = yes that the options of garbd need which for some reason is not documented that needs to be enabled for the arb to try to connect via SSL.


SQL Server single query takes longer by end of week SQL Server 2008 R2.  We are reporting off a DB-Amp backup of our SalesForce instance.
I have a SQL query that takes about 8 seconds to run on Monday.  By Friday the duration is 31 seconds and my app is giving up a second too early.  By the next Monday, the query is performing quickly again.
Here is the SQL
I don't have any Jobs running weekly that do any cleanup.  Rebuilding indexes and sp_syspolicy_purge_history are nightly jobs.
I checked Profiler and nothing else is happening while the query is running slowly.
I don't see any User Temp tables that were created before today (Friday).
I have another similar query that never slows down.
Any ideas?!

I didn't see the speedup this weekend?!
However, I did find the AccountTeamRollup CTE was a huge drain.
I was able to remove the CTE and replace the referring 'Team' column with the below.  It went from 30sec to 4sec.

,STUFF(
    (SELECT ',' + SUB.UserN AS [text()]
    FROM (
        Select distinct atm.AccountId, u.Name UserN
        From AccountTeamMember atm (nolock)
            INNER JOIN [User] u  (nolock)
                ON atm.UserId = u.Id) SUB
    WHERE SUB.AccountId = acct.Id
    FOR XML PATH('') ), 1, 1, '' ) Team


Cannot start SQL SERVER SERVICE in Windows Server 2000 This morning this problem occurs when my co-worker says they cannot connect to database.

I've checked the server and found that the SQL SERVER SERVICE is currently stopped. The problem is I cannot start it manually I've also tried to start the service in services.msc and there's still no luck.

We are using SQL SERVER 2000 ENTERPRISE EDITION
Here the screencaps:

Heres the what it says in the Event Logs

SQL Server is aborting. Fatal exception c0000005 caught.

You don't mention the version of SQL Server you are running, however, are you up-to-date with updates?  There have been fixes for Access Violations in SQL Server that you may not have at this time.

Here is an MSDN post that has some relevance (though it is specific to SQL 2008):
sql server 2008 is terminating because of fatal exception c0000005

Access Violations occur because the application has attempted to access memory areas that it doesn't own.  That is a problem with code.  And quite often there are service packs or updates that address these issues.

Without knowing exactly which version of SQL Server you are running a more specific fix cannot be suggested.


Joining three tables with intermediate nullable option and filter condition Sorry if this seems like a dumb question, but I've read through tens of answers and tried many different options, yet my query results were either overly inclusive or exclusive.

The basic scenario is, having tables A <- B <- C, and the foreign keys A.b_id (nullable) and B.c_id, how can I get A's where either:

b_id is null (B is absent)
b_id is not null (B is present), AND its associated C fits a particular criterion (C.active is true, or similar)

To restate, an A may be independent of a B, but every B belongs to a C, and I would like to get the A's that either have no B or have a B that belongs to a C with a specific condition.

Many answers seem to deal with an A that is joined to both B and C, but in this case it's the middle or intermediate table that is optional. (And I haven't been able to figure out how to assemble parts of different examples into a working solution.)

It may be possible through a UNION, but that also seems like a last resort. Is there a way to do this through joins?

I had initially tried two outer joins, but of course that's not the same thing.

I later tried inner joining B and C and then having A left join that as a derived (aliased) table, but this would still return all results, even where (using the example) active was false.

In that case it's like everything is still being outer-joined, but I thought the derived table strategy (seen in different posts) was supposed to solve that problem.

Thanks.

Theoretically this should work according your conditions:

SELECT
    *
FROM
    A
LEFT JOIN
    B
    ON B.id = A.b_id
LEFT JOIN
    C
    ON C.b_id = B.id
WHERE
    (B.id IS NULL) OR (C.active = 1);


SQL query to count with subtraction from count of other sql table I have 2 tables as below I column is common to both. Table 2 has FK from Table 1.

I need to get the count of ID_Tag per BoxNo as result. Condition

List all BoxNo and subtract the count if the ID_Tag appears in table 2.
If BoxNo has 0 tags after subtraction do not display in the result

SQL Query I have tried

 Select tb1.BoxNo,
        Count(tb1.ID_Tag) - Count(tb2.DelID_Tag) As NumberOfTags
 From Table1 tb1
   Left Outer Join Table2 tb2 on tb1.ID_Tag = Tb2.DelId_Tag
 Group By tb1.BoxNo

I get the list of all the BoxNo with the count from Table1.

This works for your sample data, but may need further testing (and possible adjustments) against a larger set of data.

--Demo setup
declare @Table1 table (Id_tag int, BoxNo varchar(5))
declare @Table2 table (DeleteID_tag int, Col1 varchar(5))

insert into @Table1(Id_tag,BoxNo) values
(5699,'A4'),
(5538,'A4'),
(4567,'A5'),
(2222,'A3'),
(1234,'A4');
Insert into @Table2(DeleteID_tag,Col1) values
(1234,'abc'),
(2222,'jkl')
;

--The actual query
SELECT t1.BoxNo
    ,count(*)
FROM @Table1 t1
WHERE NOT EXISTS (
        SELECT *
        FROM @Table2
        WHERE DeleteID_tag = t1.Id_tag
        )
GROUP BY t1.BoxNo

| BoxNo | (No column name) |
|-------|------------------|
| A4    | 2                |
| A5    | 1                |


trcsess utility problem Can trcsess utility merge files pertaining to a user session scattered across different processes in a shared server configuration?

Is this tool have this function?

TRCSESS

  TRCSESS is useful for consolidating the tracing of a particular session for performance or debugging purposes.

Yes, you can merge the scattered user session trace files using this tool and can get formatted out using TKPROF.

  TKPROF

  TKPROF can locate statements using the greatest resource. With baselines available, you can assess whether the resources used are reasonable given the work done.

Its usage and syntax are given in the following reference in details.

Reference:Tracing Utilities


How to transfer postgres databases from one server to other VPS server has old Debian 6 Squeeze with Postgres 9.1
It has 24 databases.
Every night backup copies are created using pg_dump to /root/backups directory for every database.
This directory has 24 .backup files with total size 37 GB.

I installed new VPS server with Debian 10 and Postgres 12.

How to transfer those databases to new server ?

Both server have ssh and root user, postgres port 5432  open,  100 MB internet connection and fixed IP addresses. In night they are not used by users, can stopped during move.

Should I download .backup files and use pg_restore or use pipe to restore whole cluster.

Well, according to the instructions you should use the postgres 12 tools against the 9.1 database when dumping for an upgrade

so your first step it to upgrade your backup procedure to use the pg_dump from postgres 12

If your network between the two VPSs has low tatency you can do that over the network, but if not you'll need to find a different way.

Possibly copy the 9.1 data files onto the 12 server using rsync  and the run pg_upgrade --link over those files to upgrade the 9.1 files to 12

you can do an initial pass with rsync while the database service is running and then shut it down for the final pass.


Deadlock Priority High Chosen as deadlock victim I have SQL Server 2016 SP2 (13.0.5237.0). Here is a deadlock graph I noticed recently in my system. The process with high deadlock priority was chosen as a victim (probably because of high log usage compared to the other process). But that shouldn't have happened. Is this a defect introduced in SQL? Is there a way to prevent a process with high deadlock priority from becoming a victim?
Here is the deadlock xml:

<deadlock>
 <victim-list>
  <victimProcess id="process1d3ea515848" />
 </victim-list>
 <process-list>
  <process id="process1d3ea515848" taskpriority="10" logused="5800" waitresource="OBJECT: 9:290100074:0 " waittime="4711" ownerId="359850034" transactionname="user_transaction" lasttranstarted="2019-07-31T08:12:25.267" XDES="0x1d2f62d1840" lockMode="Sch-M" schedulerid="3" kpid="10320" status="suspended" spid="52" sbid="0" ecid="0" priority="5" trancount="4" lastbatchstarted="2019-07-31T08:13:30.990" lastbatchcompleted="2019-07-31T08:13:29.427" lastattention="1900-01-01T00:00:00.427" clientapp=".Net SqlClient Data Provider" hostname="XXX" hostpid="14944" loginname="NT AUTHORITY\SYSTEM" isolationlevel="read committed (2)" xactid="359850034" currentdb="9" currentdbname="XXX" lockTimeout="4294967295" clientoption1="673415200" clientoption2="128056">
   <executionStack>
    <frame procname="CreatePartition" line="30" stmtstart="2052" stmtend="2218" sqlhandle="0x03000900d670d75323ec7f0094aa000001000000000000000000000000000000000000000000000000000000">
ALTER PARTITION FUNCTION...    </frame>
    <frame procname="CreatePartitions" line="26" stmtstart="1618" stmtend="1788" sqlhandle="0x030009000f95cb5424ec7f0094aa000001000000000000000000000000000000000000000000000000000000">
EXEC CreatePartition     </frame>
   </executionStack>
   <inputbuf>
Proc [Database Id = 9 Object Id = 1422628111]   </inputbuf>
  </process>
  <process id="process1d4593028c8" taskpriority="0" logused="0" waitresource="METADATA: database_id = 9 DATA_SPACE(data_space_id = 65601), lockPartitionId = 0" waittime="21105" ownerId="359870992" transactionname="sqlsource_transform" lasttranstarted="2019-07-31T08:14:29.460" XDES="0x1d4911c8de0" lockMode="Sch-S" schedulerid="4" kpid="5052" status="suspended" spid="93" sbid="0" ecid="0" priority="0" trancount="0" lastbatchstarted="2019-07-31T08:14:29.460" lastbatchcompleted="2019-07-31T08:14:29.447" lastattention="1900-01-01T00:00:00.447" clientapp=".Net SqlClient Data Provider" hostname="XXX" hostpid="14944" loginname="NT AUTHORITY\SYSTEM" isolationlevel="read committed (2)" xactid="359870992" currentdb="9" currentdbname="XXX" lockTimeout="4294967295" clientoption1="671088672" clientoption2="128056">
   <executionStack>
    <frame procname="GetXXX" line="17" stmtstart="-1" sqlhandle="0x03000900abe7e85629eb7f0094aa000001000000000000000000000000000000000000000000000000000000">

CREATE PROC [dbo].[GetXXX]
AS
BEGIN
    SET NOCOUNT ON;
    SELECT ...    </frame>
   </executionStack>
   <inputbuf>
Proc [Database Id = 9 Object Id = 1458104235]   </inputbuf>
  </process>
 </process-list>
 <resource-list>
  <objectlock lockPartition="0" objid="290100074" subresource="FULL" dbid="9" objectname="TableXXX" id="lock1d4eeca4f80" mode="Sch-S" associatedObjectId="290100074">
   <owner-list>
    <owner id="process1d4593028c8" mode="Sch-S" />
   </owner-list>
   <waiter-list>
    <waiter id="process1d3ea515848" mode="Sch-M" requestType="wait" />
   </waiter-list>
  </objectlock>
  <metadatalock subresource="DATA_SPACE" classid="data_space_id = 65601" dbid="9" lockPartition="0" id="lock1d48e40dc00" mode="Sch-M">
   <owner-list>
    <owner id="process1d3ea515848" mode="Sch-M" />
   </owner-list>
   <waiter-list>
    <waiter id="process1d4593028c8" mode="Sch-S" requestType="wait" />
   </waiter-list>
  </metadatalock>
 </resource-list>
</deadlock>

The process with high deadlock priority was chosen as a victim...

I've run into this before with maintenance operations - specifically index reorgs, as described on my blog here: Deadlock Victim Choice in SQL Server - An Exception?

In that case, I was able to reproduce the problem, having a process with the highest deadlock priority (10) chosen as the victim when deadlocked with a process with the lowest deadlock priority (-10).

  Is there a way to prevent a process with high deadlock priority from becoming a victim?

The only solution I'm aware of is to avoid doing these operations (partition switching, altering partition functions, index maintenance) during times of heavy user activity.  Preferably during some kind of maintenance window.

It seems like there's something internal to the way deadlock victims are chosen that "knows" one of the operations is a maintenance task, and is hard-coded in some way to choose that as the victim over a non-maintenance user query.  That's all just speculation though based on my observations.

  ...probably because of high log usage compared to the other process

For what it's worth, this is actually the opposite of how deadlock victims are normally chosen.  The process with the higher log usage is not chosen as the victim, because it would require more work to roll it back.

There's an interesting example similar to yours on Stack Overflow as well, which implies that acquisition of a SCH-M lock is part of the problem (which would make sense, given the types of operations we're discussing): Why would SQL Server's SET DEADLOCK_PRIORITY HIGH not be honored?


Model Entity Representation of an Organigram I'm new to database and I'm trying to represent and organigram of a family (Great Grand father, Grand father, father, etc) using MER (Model Entity Relationship), and I'm quite confused on if I should be represent them hierarchical or if I should represent them as Individual entities.

I think you need to do as follows. Create a Person entity with data fields such as id, name, address and create another entity Person-Relationship with data fields such as pr-id, rel-type with instance of grandfather, father, etc...The physical data model might look as follows,

pr_type
{
     pr_id(pk), pr_type
}

person
{
     person_id(pk), pr_id(fk),  person_name
}

Here, pr_type data field may contain the entities such as great grandfather, grandfather, father, son, etc...

I hope this will give some understanding. Thanks.


Is Oracle 12.2 backward compatible with Oracle 12.1 Current Oracle DB I am using is 'Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit' and we have a use case for using 'Oracle 12.2.X.X.0'. We want to use the elongated object name lengths of 128 which come with Oracle 12.2

Is Oracle 12.2 backward compatible and supports everything on Oracle 12.1? Like Stored Procs, JBDC connections etc.

If new tables & columns with long names on 12.2 start coming in, Can those tables be used in conjunction with tables created on 12.1 for joins etc., and can they start residing in the same schemas?

Here is the list of changes:

Behavior Changes, Deprecated and Desupported Features for Oracle Database 12c Release 2 (12.2)

Such basic functionality that you mentioned (table joins, stored procedures, JDBC connections) should of course work.

Sure, there are new features, and some changes, but I can not recall anything significant related to those areas that worked in 12.1 but did not in 12.2.


When creating asymetric key from assembly does assembly need to stay on the file system I am deploying a SQL Server CLR project to my database and am usure of something. To give the function in the project external access I am creating an asymetric key from the projects assembly, creating a login from that key and granting access to that login. From MSDN:

USE master
GO

CREATE ASYMMETRIC KEY SQLCLRTestKey FROM EXECUTABLE FILE = 'path\to\my\assembly.dll'
CREATE LOGIN SQLCLRTestLogin FROM ASYMMETRIC KEY SQLCLRTestKey
GRANT EXTERNAL ACCESS ASSEMBLY TO SQLCLRTestLogin
GO

My question is, do I need to keep the assembly on the file system after the key is created for the login to work? I know I could test this by doing the above then removing the assembly and seeing if things break but the only environment I have this type of access to right now is a production one and I don't want to muck around with things there. Thus, I asked here. Could anyone tell me if I need to keep the assembly after the key is created? Thanks!

No need to keep the file. The file is read by the CREATE ASYMMETRIC KEY statement, the key is extracted and saved in the database and that's it, all further references to it will use the key from the database.

As a general rule databases try to avoid dependencies on file system because they have to work on backup restore scenarios, there could be involved in log shipping or database mirroring etc. For example when you create an assembly in SQLCLR the assembly is loaded into the database and this becomes the master copy, not the one in the file system. Is all about making the database a self contained entity. Now of course I know in the real world this story has some holes, but the CREATE ASYMMETRIC KEY is not one of them :)


How to determine if an Index is required or necessary I've been running an auto-index tool on our MS SQL database (I modified a script originating from Microsoft that looks at the index statistics tables - Automated Auto Indexing). From the stats, I now have a list of recommendations for indexes that need creating.

Edit: The Indexes described above take information from the DMV's that tell you what the database engine would use for indexes if they were available and the scripts take the Top x recommendations (by seeks, user impact etc.) and put these in a table.

(Edit above partially taken from Larry Coleman's answer below in order to clarify what the scripts are doing)

As I am new to database admin, and having had a quick search around the net, I am reluctant to take the plunge and blindly add the recommended indexes. However, not being experienced in  the field, I am looking for some advice on how to determine whether the recommendations are necessary or not.

Do I need to run the SQL Profiler, or is it better to examine the code that queries the tables? And do you have any other advice?

I use Jason Strate's index analysis scripts (Old location). They tell you how much your existing indexes are used as well as how much missing indexes would have been used. I typically don't add indexes unless they make up more than 5 or 10% of the queries on a table.

Most importantly, though, it's about making sure the application responds fast enough for the users.

Update: Jason Strate's index analysis blog articles for newer scripts (New location)

Double Update: These days, I use sp_BlitzIndex® when performing index analysis.


Get reference count of reverse foreign key relations Image I have a simple user table like this:

ID|Name
-------
12|Foo

I want to know: How many reverse foreign key relations point to the user with ID 12? I mean the number of rows which are a foreign key to user and contain the value 12.

I use PostgresSQL, but a portable solution would be better ...

I don't know postgres well, but I am not aware of any way to do this in a single query. I also doubt there'll be a truly portable way, but the approach is similar.

Generally, I think you'd need to:

Query system (meta) tables to find all foreign key table and column names.
Use this to generate a (possibly massive) select query that counts all the 12s


Old user removed, now having DEFINER errors Old users were removed from the mysql.user table and now while running some views, I got the error that the user@host does not exist. Also while taking a dump, a view is unable to be dumped due to user not existing. I have been researching on the best way to update definers when I remove an old user from the mysql.user table. Of course, the old users were DEFINERS in one view, or sp. A quick fix I did was to take a dump of the db with a regex that removes the definers from the dump file and restore the db - this way the new definer will be the user performing the restore operation.
Also I found 2 other ways online (https://www.adminbirds.com/mysql/how-to-change-the-definer-for-views/) - I need to verify this method.

to change definers in views: SELECT CONCAT("ALTER DEFINER=youruser@hostVIEW ", table_name, " AS ", view_definition, ";") FROM information_schema.views WHERE table_schema='your-database-name'; this will generate the alter statement for manually changing the definer in the information_schema.views table.
To change definers in store procedures: UPDATEmysql.procp SET definer = 'user@%' WHERE definer='root@%' this will manually change the definer in the mysql.proc table. Although I read from MySQL doc page that It is not supported that the server will notice manual manipulation of this table.

So here is my question: If I run the above commands, will it affect the data? Is it safe to run? I know the ALTER statement only affects structure, but just want to be sure, since I will be running this in a prod environment as well. The UPDATE statement definitely changes the data in the mysql.proc table and I need to be sure this is safe to proceed. Please advise.

as You can see from text of both commands - they not work with data other than single table, so from this point of view - both safe.

In worst case - You already have not worked views and continue have them :), but it not expected result.

But, in any case - the best practice - make backup before any kind of similar operations, so You always can rollback.

Notice in server documentation:

  The server manipulates the mysql.proc table in response to statements
  that  create, alter, or drop stored routines. It is not supported that
  the  server will notice manual manipulation of this table.

more related to other types of manipulation, like - manual edit of BLOB object with text or parameters, but change definer - it just change single column.


Derive daily delta from total daily counts? Tsql I have a table with columns:

ID,
tableName,
totalRowCount,
Date

Each day I'm appending the number of rows in the relevant table. What I want to do is create a table which has the number of new rows created on each table each day. Whats the best way to do this?

If this is for SQL Server 2012 or later version, you could use the LAG analytic function to produce the required result:

SELECT
  ID,
  tableName,
  rowCountDelta = totalRowCount - LAG(totalRowCount, 1, 0) OVER (PARTITION BY tableName
                                                                     ORDER BY Date),
  Date
FROM
  dbo.yourTable
;

The above query assumes that the first entry in a partition is indeed the very first addition of rows. (LAG(totalRowCount, 1, 0) means the absence of the previous totalRowCount will be treated as a zero.) If that cannot be assumed safely in every case and you would prefer to return NULL for first entries, you could use just LAG(totalRowCount) OVER ... instead:

SELECT
  ID,
  tableName,
  rowCountDelta = totalRowCount - LAG(totalRowCount) OVER (PARTITION BY tableName
                                                               ORDER BY Date),
  Date
FROM
  dbo.yourTable
;

If you insist on storing the results in a table, you can just add an INTO clause to the query. A possibly better idea, though, might be to save the query as a view.


SSMS / SQL-Server delays console output (RAISEERROR WITH NOWAIT) It is well known, that SSMS (or maybe the SQL Server, but I'd guess more the SSMS) delays PRINT messages until several lines (40) are gathered in the buffer, before they are really printed to the message window in SSMS (exception: something else is printed out, e.g. the number of rows affected by an UPDATE or the batch is done).
So I usually use RAISEERROR(<text>, 0, 0) WITH NOWAIT to print it out immeadiately. This works well for the first 500 rows, after this SSMS seems to start to buffer them again (50 lines, starting at 1000 lines the buffer seems to increase to 1000).
Does anybody knows, how I can prevent this "buffer feature" (e.g. if I manually run the statistic updates by using Ola Hallengreens maintenance solution, I'd prefer to know, what it really does at the moment without having to use sp_whoisactive etc.).
PS: You can simulate this behavior by using the following "script"
DECLARE @i INT = 0

WHILE @i < 10000
BEGIN
    SET @i += 1
    RAISERROR('Step %i', 0, 0, @i) WITH NOWAIT
    --PRINT @i
    WAITFOR DELAY '00:00:01.0' -- wait 1 second, feel free to decrease
END

To have full control over the message display, you could execute the script with PowerShell using System.Data.SqlClient, handling the InfoMessage event and setting FireInfoMessageEventOnUserErrors on the connection. I don't think there is a way to control the behavior in SSMS or other SQL Server tools. – Dan Guzman yesterday


Free space of toasted content I have just deleted a big part of the data stored in one jsonb inside my users table (Its not needed anymore) and just keep an id inside that column. I expected to free a lot of disk space with this action.

UPDATE users SET my_jsonb = '{id: xxxx }';

The problem is that when i try to see the free space in the disk remains the same

/dev/md125      935G  501G  387G  57%

Also the table

 Table                                            Size        External
 users                                          | 497 GB     | 478 GB

My last option is to try a VACUUM FULL users but since this table is used in production environment it would not be convenient to lock it a unkown ammount of time.

Im running a postgres 10.5 database on a CENTOS RHEL7 machine.

A plain VACUUM will make the free space available for new data (i.e., other TOASTed JSON data).

If you really want to give the free space back to the OS, and you cannot afford the exclusive lock, try pg_repack:

  To perform a full-table repack, pg_repack will:

  create a log table to record changes made to the original table
  add a trigger onto the original table, logging INSERTs, UPDATEs and DELETEs into our log table
  create a new table containing all the rows in the old table
  build indexes on this new table
  apply all changes which have accrued in the log table to the new table
  swap the tables, including indexes and toast tables, using the system catalogs
  drop the original table

It still needs enough space for the original table and the new copy.


Select a value for the max I have a query which selects a max value over all the values of in a datetime column (dtimesent).

select dtimesent, max(ResponseTime) AS maxtime
FROM allrequests
where Request like '%Requestsa%'
group by dtimesent

I need to get the value of the request colmn for each max response time in each group by.
Is there any easy way to do this?  There can be multiple rows per dtimesent value so I can't just include the request column in the group by,

Many thanks,

RANK will deal with multiple max values (instead of ROW_NUMBER)

SELECT
   dtimesent, Request, ResponseTime
FROM
    (
    select dtimesent, Request, ResponseTime ,
       RANK() OVER (ORDER BY ResponseTime DESC) AS rn
    FROM allrequests
    where Request like '%Requestsa%'
    ) T
WHERE
   rn = 1

Also see How to get the MAX row please


What are the differences between leaf and non-leaf pages? I've been running some index usage reports, and I'm trying to get a definition of Leaf and Non-leaf.  There seem to be both Leaf and Non-leaf Inserts, Updates, Deletes, Page Merges, and Page Allocations.  I really don't know what it means, or if one is better than the other.

If someone could give a simple definition of each, and also explain why Leaf or Non-leaf matters, it would be appreciated!

Think about it like this: leaf level pages are the end of the road for the data search through the B-tree structure of an index. They contain the data defined in the index definition (or in the case of a clustered index, all of the table's data) and a row locator to the data row (in the case of a nonclustered index).

Non-leaf level pages contain the "road map" to the leaf level pages/data by including the key value as well as a pointer to either another non-leaf level page (depending on the B-tree depth and location of the intermediate page) or the resulting leaf level page (index page for a nonclustered index, and data page for a clustered index).

Edit:  Here's a good image to put a picture to the idea.


is it possible to rollback only a partition/subpartition? Is it possible to create a savepoint and to rollback only a partition of a table?
Lets assume I created a monthly partition and subpartitions with a group_id for table x.

Now i run some stored procedures to alter the content in table x for a certain group id in a certain month. Before I run the stored procedure, i want to create a savepoint for the partition/subpartition and if necessary I want to rollback only the altered partion/subpartition and not the whole table.

I am asking for oracle and MSSQL.

Unfortunately this is not possible in SQL Server - you can of course create a backup, or create snapshots on the server (HyperV for example) to accomplish this. Other than that, it's not possible to only do it for a table - the closest I'd consider is differential back/restore.


List all mysql records in a master table and last record in a related child table I want to list all records in a MySQL master table and the last record in the child table that is related to the master. Here is my code

$strsql="SELECT contact.id, contact.title, contact.init, contact.first_name, contact.last_name, contact.city, contact.home_phone, contact.mobile_phone, contact.e_mail,  status.status AS status, fundsource.fundsource AS fundsource, category.category AS category,contact.score, ROUND(value/1000000,1) AS value_M, jstate.jstate AS to_do, journal.l_date AS last_activity, journal.note AS journal FROM contact

LEFT JOIN journal ON journal.con_id=contact.id
LEFT JOIN jstate ON jstate.jstate_code=journal.jstate_code
LEFT JOIN category ON category.cat_code=contact.cat_code
LEFT JOIN status ON status.st_code=contact.st_code
LEFT JOIN fundsource ON fundsource.fundsource_code=contact.fundsource_code

WHERE journal.l_date=(SELECT MAX(journal.l_date) from journal WHERE journal.con_id = contact.id) GROUP BY contact.id " ;

But it outputs only related records which matching the PK and FK. Not displays all records in the master. Can any one help me?

Your WHERE-condition is reducing the output. If you need all the records from the contact table, please try something like this:

$strsql="SELECT contact.id, contact.title, contact.init, contact.first_name, contact.last_name, contact.city, contact.home_phone, contact.mobile_phone, contact.e_mail,  status.status AS status, fundsource.fundsource AS fundsource, category.category AS category,contact.score, ROUND(value/1000000,1) AS value_M, jstate.jstate AS to_do, journal.l_date AS last_activity, journal.note AS journal FROM contact

LEFT JOIN journal ON journal.con_id=contact.id AND journal.l_date=(SELECT MAX(journal.l_date) from journal WHERE journal.con_id = contact.id)
LEFT JOIN jstate ON jstate.jstate_code=journal.jstate_code
LEFT JOIN category ON category.cat_code=contact.cat_code
LEFT JOIN status ON status.st_code=contact.st_code
LEFT JOIN fundsource ON fundsource.fundsource_code=contact.fundsource_code

WHERE contact.scode=$user->scode and contact.dcode=$user->dcode AND contact.st_code !=17 AND contact.st_code !=18
GROUP BY contact.id " ;


What is the maximum number of rows in a clustered index on datetime column? I would like know what the max no of rows is in a clustered index (non-unique) on a datetime column table in SQL Server 2008R2.

The number of rows is limited only by available storage. i.e. there isn't one.

Maximum Capacity Specifications for SQL Server

I suspect it might actually be 9,223,372,036,854,775,807 rows (maximum size of a bigint). To put that number of rows into context, if you inserted a billion rows per day, it would take approximately 25,269,512 years to exhaust.

Update: please read comments below, as they talk specifically about the uniqueifier.


How to use relationships to extract data via select Disclaimer: Novice in SQL

I have 3 tables. There is one "major" table (labeled used_for_lookup below) used to do look-ups on and the other two (labeled fid_table and did_table below) are foreign keys in the major table. For the sake of the argument, the tables below have been simplified down to the problem.

The database operations insert into the did_table and fid_table. These are generally very infrequent. When an event occurs, the details are captured in the used_for_lookup table. This table is then used to do SELECTS on. The columns fid and did in the used_for_lookup table are references into the fid_table and did_table.

A simplified version with data:

sqlite> select * from used_for_lookup;
id          datetime    fid         did
----------  ----------  ----------  ----------
1           2014-10-10  1           1
sqlite> select * from did_table;
id          other_ints  did_important_string
----------  ----------  -----------------------------------------------------
1           1           this needs to go into the query result from did table
sqlite> select * from fid_table;
id          other_ints  fid_important_string
----------  ----------  -----------------------------------------------------
1           1           this needs to go into the query result from fid table

The most general query is that we query used_for_lookup table between 2 date events on the datatime column.

When we do a select, like so:

sqlite> select * from used_for_lookup where datetime between '2014-10-09' and '2014-10-11';
id          datetime    fid         did
----------  ----------  ----------  ----------
1           2014-10-10  1           1

we need to "convert" fid and did column values to the fid_important_string and did_important_string values for when we report back the answer.

They are foreign keys which helps with data integrity, but I'm stumped on how do you now use the column lookups to get the strings out of other tables into one answer?

Thanks.

Try this

select used_for_lookup.id,
        used_for_lookup.datetime,
        fid_table.fid_important_string,
        did_table.did_important_string
 from used_for_lookup
 join fid_table on fid_table.id = used_for_lookup.fid
 join did_table on did_table.id = used_for_lookup.did
where datetime between '2014-10-09' and '2014-10-11';


Strict mode not working mongo Shell I was test in process of testing the strict mode of mongo shell queries. This the link i am referring.
Sample document looks like -

   {  "_id" : ObjectId("19876"),
    "ip" : "127.0.0.1",
    "pgDownloadTime" : NumberLong(25000),
    "domain" : "test.domain.com",
    "pgAccessTime" : NumberLong("1469422166381"),
    "pgAccessMin" : NumberLong(1469422140),
    "pgAccessHour" : NumberLong(1469421000),
    "contentType" : 1,
    "tags" : [
            "testTag1",
            "testTag2"
    ],
    "catIds" : [
            "cat1",
            "cat2"
    ]

Query using strict mode is -

    db.testData.find({ "pgAccessMin" : { "$gte" : { "$numberLong" : "1469406600" }, "$lt" : { "$numberLong" : "1469413800" } }, "domain" : "test.domain.com" })

And Query without using strict mode is -

    db.testData.find({ "pgAccessMin" : { "$gte" : 1469406600, "$lt" :1469413800 }, "domain" : "test.domain.com" })

Issue is that Strict mode query is not providing results but query without strict mode provides the data.
I am using mongo version - 3.2.7

The MongoDB Extended JSON format allows representation of additional BSON data types that are not part of standard JSON.

There is a subtle distinction between:

being able to parse the extended JSON into a JavaScript representation
the parser then taking the additional step of interpreting the extended key/value pairs into an object with associated JavaScript wrappers such as NumberLong() or Date()

As per the MongoDB Extended JSON page you linked to, the mongo shell can parse the strict mode Extended JSON (which conforms to the JSON standard) but without recognition of type information:

  Other JSON parsers, including mongo shell and db.eval(), can parse strict mode representations as key/value pairs, but without recognition of the type information.

The strict mode is only only intended as a data interchange format (for example, transferring a collection via mongoexport and mongoimport).

For queries in the mongo shell you should use the mongo Shell Mode representations as noted in the MongoDB Extended JSON documentation.


How to implement High Availability for all nodes in a sharded database? I'm doing some research into implementing sharding for one of my large (~2 TB) production databases.
Currently the database has HADR via a multi-replica AlwaysOn Availability Group.
Since sharding a database requires it to be broken out into multiple smaller pieces across multiple nodes, is it possible to leverage AlwaysOn Availability Groups with the sharded database?...would I need to create a failover cluster that duplicates every node in my sharded database to multiple secondary replicas to be able to do so?
Is there a better way to implement High Availability for a sharded database? (I know sharding inherently has a level of High Availability, because if one node goes down the rest of the database is still accessible, but I need a solution that is fully High Availability so that no part of the database is ever inaccessible.)

There's no difference in HA solutions when you break up a large database into multiple smaller ones.  Either Availability Groups or Failover Cluster Instances will work.
AGs have the benefit that you can perform cross-shard queries on any instance that has readable replicas of all of the shards.  So, for instance you can have a shared database that's always present on every node, and the app can always perform cross-database queries from.  Just note that cross-database transactions (including all writes) are not supported except between databases in the same AG (not blocked though).


What's the best way to get data for sampling points within a timeframe I'm looking for some advice how to set up a set of queries which check for an aggregated value for sampling points within a time period. This should run at an IQ-server, so maybe not this many procedure calls would be cool ;)

I had a look into the windowing feature. I think it might only work for aggregate data for timeframe, not at a sampling point. So having this scenario:

I have a list of items with a start and an end date. I need to collect a sum of processes active at a current time. It's not about system processes, but more about something like how many craftsman were working at a given time.

Imagine tables like this (I've modified the example a little to leave out boring parts.... so might it not run perfectly)

create table items (
    id int ot null default autoincrement,
    "Type" integer,
    TimeStampStart datetime null,
    TimeStampend datetime null
)

is currently used by this queryset:

create table #Processes(
  "Type" integer,
  "timestamp" "datetime" null,
  "Sum" integer null
  )

set @date = '20120303'
while @date <= '20130505'
  begin
    insert into #Processes
      select "Type",'timestamp'=@date,'Sum'="count"()
        from "items"
        and "TimeStampStart" between "dateadd"("day",-"abs"(100),@date) and @date
        and "TimeStampStart" <= @date
        and "isnull"("TimeStampEnd",@date) >= @date
        group by "Type"
    set @date = "dateadd"("ss",3600,@date)
  end
select * from #Processes;

This might not the best way of doing it. So I'm looking for a better approach ;)

I would take answer by Micheael Green a step further and suggest generating a Numbers table. It will help many other algorythms as well. Another handy table is Calendar table with every date for +/- 20 years. You can get numbers from ID column of such table as well.

Here is a query I came up with. You can easily wrap it into SP to TVF. It works for me. I tested on my dev db so had to prefix table names with tmp.

Play with >, >=, <, <= sings to count or not those processes that start/finish on boundary.

---- Create temp tables to test
create table tmp_numbers  (
id int null
)
insert into tmp_numbers values (0)
go
insert into tmp_numbers
select MAX(ID)+1 from tmp_numbers
go 1000
create clustered index Idx_ID on tmp_numbers (id)
go

create table tmp_items (
    "Type" integer,
    TimeStampStart datetime null,
    TimeStampend datetime null
)
go

insert into tmp_items
select 1,'2012-03-03 02:13:01.000','2012-03-03 15:09:05.000'
UNION ALL
select 2,'2012-03-03 07:33:59.990','2012-03-03 14:59:10.000'
UNION ALL
select 3,'2012-03-03 22:13:01.000','2012-03-04 15:09:05.000'
UNION ALL
select 4,'2012-03-04 10:33:59.990','2012-03-04 14:59:10.000'
UNION ALL
select 5,'2012-03-04 23:20:00.000','2012-03-05 02:50:00.000'
UNION ALL
select 6,'2012-03-05 12:00:00.000','2012-03-05 23:01:00.000'

------ The query itself

declare @start datetime, @end datetime
set @start = '20120303'
set @end  = '20120305'

select
ID,
DATEADD(hour, id, @start),
DATEADD(hour, id+1, @start),
(select COUNT (*)
    from tmp_items
    where TimeStampStart <= DATEADD(hour, tmp_numbers.id +1, @start)
    and TimeStampEnd > DATEADD(hour, tmp_numbers.id, @start)

    -- To limit number of Log ("items") rows scanned:
    and TimeStampEnd >= @start

)
from tmp_numbers
where id < DATEDIFF (hh,@start, @end)+24;

Another way to organise this query to overcome single CPU limitatation

declare @start datetime, @end datetime
    set @start = '20120303'
    set @end  = '20120305'

create table #Temp (
StartTime datetime null,
EndTime datetime null,
RowNum int null)

insert into #Temp
  select
    DATEADD(hour, id, @start),
    DATEADD(hour, id+1, @start),
  from tmp_numbers
    where id < DATEDIFF (hh,@start, @end)+24;

 update #Temp
 set RowNum =  (select COUNT (*)
        from tmp_items
        where TimeStampStart <= #Temp.EndTime
        and TimeStampEnd > #Temp.StartTime

        -- To limit number of Log ("items") rows scanned:
        and TimeStampEnd >= @start
    )


Always On HAG Primary Database Log Files I tried to search the group but can't find similar answer,  I have a Primary Database that has a Large Transaction Log, I tried to shrink the file in the primary but can't set the default size to a smaller size, I believed I need to change the recovery model to simple to shrink the file, and now I'm not sure what will happen because the database is part of HAG/Always on and it is the primary node. I tried the readable secondary set = no, nothing changes, I can't managed to resize the default initial size to a lower size.

Can you quantify 'large'?  Are things breaking?  What's the reason you want to shrink it?

Regardless, you won't be able to switch the DB to SIMPLE mode if it's in an AG.  You'll get the following:

  Msg 1468, Level 16, State 2, Line 1 The operation cannot be performed
  on database "MyDBName" because it is involved in a database mirroring
  session or an availability group. Some operations are not allowed on a
  database that is participating in a database mirroring session or in
  an availability group.

  Msg 5069, Level 16, State 1, Line 1 ALTER
  DATABASE statement failed.

What I would do is go find out if transaction log backups are being taken and their frequency.  in FULL recovery model, the log file isn't automatically truncated and cleared, you'll need to take log backups for that to happen. (In SIMPLE recovery, the log is automatically cleared so there's no need to maintain it.

If you're taking log backups, you may need to take them more frequently as the log file could be filling up between log backups.

https://www.sqlshack.com/beginners-guide-sql-server-transaction-logs/
https://technet.microsoft.com/en-us/library/ms345583(v=sql.105).aspx


set compatibility mode in Azure Sql Database? Is it possible to set  compatibility mode in Azure Sql Database  to match SQL Server 2014 ?

We have a existing application using SQL Server 2014 - can i just change the connection string to point to a Azure Sql Database with sql 2014 compatability?

I am not disgarding anything of @Shanky, but I would like to say that at presently in Azure SQL Server database version is 12.0.2000.8 . Which is the RTM version of SQL Azure and MAJORVERSION of SQL2014.

I am attaching the screen shot here which I have connected to My Azure SQL Server database through on-premises SSMS 2017.

Here I am writing the TSQL query to check the MajorVersion, ProductLevel, Edition, ProductVersion through the below mention query in on-premises as well as in Azure.

SELECT
  CASE
     WHEN CONVERT(VARCHAR(128), SERVERPROPERTY ('productversion')) like '8%' THEN 'SQL2000'
     WHEN CONVERT(VARCHAR(128), SERVERPROPERTY ('productversion')) like '9%' THEN 'SQL2005'
     WHEN CONVERT(VARCHAR(128), SERVERPROPERTY ('productversion')) like '10.0%' THEN 'SQL2008'
     WHEN CONVERT(VARCHAR(128), SERVERPROPERTY ('productversion')) like '10.5%' THEN 'SQL2008 R2'
     WHEN CONVERT(VARCHAR(128), SERVERPROPERTY ('productversion')) like '11%' THEN 'SQL2012'
     WHEN CONVERT(VARCHAR(128), SERVERPROPERTY ('productversion')) like '12%' THEN 'SQL2014'
     WHEN CONVERT(VARCHAR(128), SERVERPROPERTY ('productversion')) like '13%' THEN 'SQL2016'
     WHEN CONVERT(VARCHAR(128), SERVERPROPERTY ('productversion')) like '14%' THEN 'SQL2017'
     ELSE 'unknown'
  END AS MajorVersion,
  SERVERPROPERTY('ProductLevel') AS ProductLevel,
  SERVERPROPERTY('Edition') AS Edition,
  SERVERPROPERTY('ProductVersion') AS ProductVersion;
  Go

Here I am just attaching the screen shot of Azure SQL database compatibility level , which I have got after execution of this TSQL query in Microsoft Azure portal.

Even I have also checked out with New Blank database ( which is created by myself) and also checked with Sample (AdventureWorksLT) (this is the sample database which Microsoft is providing at present in the Azure portal).

With the respect of both database I have got the same version with compatibility.

To determine the current compatibility level of your database, execute the following Transact-SQL statement:

SELECT compatibility_level
FROM   sys.databases
WHERE  [name] = 'Your Database Name';

The alignment of SQL versions to default compatibility levels are as follows:

100: in SQL Server 2008 and Azure SQL Database
110: in SQL Server 2012 and Azure SQL Database
120: in SQL Server 2014 and Azure SQL Database
130: in SQL Server 2016 and Azure SQL Database
140: in SQL Server 2017 and Azure SQL Database

As per Joseph Sack SQL Database Engine Blog documentation here as on dated
July 7, 2017 we are announcing the official public preview of compatibility level 140 in Azure SQL Database.

As per MSDN BOL documentation here On Azure SQL Database Managed Instance, this T-SQL feature has certain behavior changes. See Azure SQL Database Managed Instance T-SQL differences from SQL Server for details for all T-SQL behavior changes.

To change the COMPATIBILITY_LEVEL through TSQL.

ALTER DATABASE database_name
SET COMPATIBILITY_LEVEL = { 140 | 130 | 120 | 110 | 100 | 90 }

The database_name is the name of the database to be modified.

COMPATIBILITY_LEVEL { 140 | 130 | 120 | 110 | 100 | 90 | 80 }

Is the version of SQL Server with which the database is to be made compatible.

The following compatibility level values can be configured:

 Product       Database Engine Version  Compatibility Level Designation Supported Compatibility Level Values
    SQL Server 2017 (14.x)     14             140                     140, 130, 120, 110, 100
    Azure SQL Database         12             130                     140, 130, 120, 110, 100
    SQL Server 2016 (13.x)     13             130                     130, 120, 110, 100
    SQL Server 2014 (12.x)     12             120                     120, 110, 100
    SQL Server 2012 (11.x)     11             110                     110, 100, 90
    SQL Server 2008 R2         10.5           100                     100, 90, 80
    SQL Server 2008            10             100                     100, 90, 80
    SQL Server 2005            9              90                      90, 80
    SQL Server 2000            8              80                      80

To Change the compatibility level of SQL Azure database in Microsoft Azure Portal.

ALTER DATABASE CompabilityTestDB
SET COMPATIBILITY_LEVEL = 140;
Go

I am also attaching the screen shot.

After changing to check the compatibility level of Azure SQL database in Azure Portal.

SELECT compatibility_level
FROM   [sys].[databases]
WHERE  [name] = 'CompabilityTestDB';

After changed the compatibility level of Azure SQL database in portal I am also attaching the screen shot here.

  Very Important Note: As of January 2018, in Azure SQL Database, the default compatibility level is 140 for newly created databases. We do not
  update database compatibility level for existing databases. This is up
  to customers to do at their own discretion. With that said, we highly
  recommend customers plan on moving to the latest compatibility level
  in order to leverage the latest improvements.

  If you want to leverage database compatibility level 140 for your
  database overall, but you have reason to prefer the cardinality
  estimation model of SQL Server 2012 (11.x), mapping to database
  compatibility level 110, see ALTER DATABASE SCOPED CONFIGURATION
  (Transact-SQL), and in particular its keyword
  LEGACY_CARDINALITY_ESTIMATION = ON.

  For details about how to assess the performance differences of your
  most important queries, between two compatibility levels on Azure SQL
  Database, see Improved Query Performance with Compatibility Level 130 in Azure SQL Database.
  Note that this article refers to compatibility
  level 130 and SQL Server, but the same methodology applies for moves
  to 140 for SQL Server and Azure SQL Database.

For further you ref here , here, here and Default compatibility level 140 for Azure SQL databases


SQL Server - Bulk load blocking queries on heaps SQL Server books online say in SET TRANSACTION ISOLATION LEVEL:

"Optimized bulk load operations on heaps block queries that are running under the following isolation levels:
SNAPSHOT
READ UNCOMMITTED
READ COMMITTED using row versioning".

What does optimized bulk load mean here exactly?

Optimized bulk load means SQL Server is using a special bulk load code path (fast load) and minimal logging.
These concepts are described in:

SQL Server 2016, Minimal logging and Impact of the Batchsize in bulk load operations
Prerequisites for Minimal Logging in Bulk Import
The Data Loading Performance Guide

From that last link:

Although readers under RCSI are not affected by X locks, there are two bulk load situations that will block RCSI queries (as well as NOLOCK queries) in SQL Server 2008:

When populating a heap with TABLOCK using BULK INSERT, INSERT-SELECT, or other bulk load operations. This is because the heap load acquires a BULK OPERATION intent exclusive (IX) lock and the NOLOCK or RCSI readers acquire a BULK OPERATION shared (S) lock. To bulk load a heap and permit concurrent readers even using RCSI or NOLOCK, you must eliminate the TABLOCK hint and thus give up minimal logging.


How to constrain many-to-many to mark a single record as a "default"? Suppose I have the following entities:

customer(id, name)

template(id, name)

With a junction table:

customer_template(id, customerId, templateId, is_default)

A customer may have any number of templates but only one is allowed to be the default.

How do I constrain the junction table so that there is one and only one default template assigned?

You could use a filtered unique index.

create unique nonclustered index ixf_customer_template_default
  on dbo.customer_template(customerId,is_default)
    where is_default = 1;

That will allow only one default and any number of non default rows per customer.

rextester demo: http://rextester.com/HLQB10886

create table customer_template(
    id int identity(1,1)
  , customerId int
  , templateId int
  , is_default bit
);

create unique nonclustered index ixf_customer_template_default
  on dbo.customer_template(customerId,is_default)
    where is_default = 1;

insert into customer_template values
 (1,1,0),(1,2,0),(1,3,1);

/* error when adding another default */

insert into customer_template values
 (1,4,1)

  Cannot insert duplicate key row in object 'dbo.customer_template' with unique index 'ixf_customer_template_default'. The duplicate key value is (1, 1).
  The statement has been terminated.


Start, stop, configure PostgreSQL through Mac terminal I am having basic problems using PostgreSQL on my Mac OSX Yosemite, through the terminal. I tried google-ing but this is becoming increasingly difficult and info very scattered so I keep running into walls.
I did the following:

installed PostgreSQL 9.5.2 via Homebrew
modified my shell config file (.zshrc) to include PostgreSQL bin directory in $PATH variable
I did the "initdb" routine and "told" Postgres that I want to store my databases in /usr/local/cellar/postgresql/9.5.2/data

Here I tried starting postgreSQL:
$pg_ctl /usr/local/cellar/postgresql/9.5.2/data -l logfile start

first error:

pg_ctl: another server might be running; trying to start server anyway
/bin/sh: logfile: Permission denied

As far as I understand, I am trying to start the PostgreSQL server and direct its output and errors into a log file, located in a location for which my user has no write privileges.
How can I fix this?
Here I tried stopping the server and try again:
$pg_ctl -D /usr/local/cellar/postgresql/9.5.2/data stop

This worked ok. So,

How can I use other location to start the server and enable log
How do I check if the server is running if I need to stop it (if errors appear)?

The first error indcates the server is already running.  Have you tried connecting with psql or run the following to establish that?

pg_ctl -D /usr/local/cellar/postgresql/9.5.2/data status

If you know it is down, then for the second error with the logfile on startup, either reissue the command with an absolute path to where you can write a file:

pg_ctl -D /usr/local/cellar/postgresql/9.5.2/data -l /tmp/logfile start

Or if that doesn't work for you, try starting without the -l switch:

pg_ctl -D /usr/local/cellar/postgresql/9.5.2/data start

Without the switch, you still get the PostgreSQL logs in the pg_log directory under the $PGDATA - in your case /usr/local/cellar/postgresql/9.5.2/data/pg_log


Subtract two dates stored as varchar I have 2 varchars like:

20160316131824
20160316080002

How can I subtract to see if there is more than 24 hours of difference between the 2 values?

I tried using the symbol - but it gives me an error. What is the best way?

Here is the sample query to convert the given varchar2 value to date and calculated the difference in hour.

select (to_date('20160316124052', 'yyyy-mm-dd hh24:mi:ss')-to_date('20160316080002', 'yyyy-mm-dd hh24:mi:ss'))*24 DateAndTime from dual;

Update

SELECT (sysdate-to_date(START_TIME,'YYYYMMDDHH24MISS'))*24 FROM LOGGING WHERE NAME='TERR_PAST' AND UPPER(STATUS) = 'EXECUTED';


Multiple mdf, empty one is missing Hi guys i have a big problem. I know i did such stupid and unforgivable mistake.
And know please help me if you can.
I wanted to make copy of database. I should make it by backup and then restore as another database but faster option was to stop mssql server service, copy files and then attach as new database.
The problem is that few minutes eariel i have created new mdf file on primary filegroup in my database i wantedo to copy. That was empty mdf file, just new one. Then i stoped mssql service and deleted that new empty mdf file from hard drive manually. I dont know why, i thought that: "this was just for tests we dont need anymore". When i copied files, i started mssql service and my database is in "recovery Pending" mode and cant find that empty mdf file. Even if i create the same file from another database ofcourde mssql server knows that this is not proper file. I cant make alter table remove file beacuse of error:

Database cannot be opened due to inaccessible files or insufficient memory or disk space

Is there any possibilities to recover that database? I have backup but i will lost data from one day.

Please help if you can

Might sound silly but first thing, check your recycle bin. If the secondary file was small/empty it may still be there.

IF it is, shut off sql server, go to recycle bin, right click the file and choose restore. Then restart sql server and see if it works.

IF you did not drop the secondary file from the filegroup then the database expects there to be more files, so the error "inaccessible files" makes sense.


Join between different frequency tables Assume that I have two tables, DailyTable and QuarterlyTable, where each have 3 columns: ID, Date, and Value.

As the names indicate, the frequency of data stored in DailyTable is daily, while the frequency of data stored in QuarterlyTable is quarterly.

How can I join the two tables such that I get daily results combining the most recent (point in time) data from each table based on ID and Date?

DailyTable

    ID   |  Date   |    Value  |
---------+---------+------------
    1    |1/1/2010 |     10    |
    1    |1/2/2010 |     15    |
              ...
    1    |3/1/2010 |     20    |
              ...
    1    |4/1/2010 |     30    |

QuarterlyTable

    ID   |  Date   |    Value  |
---------+---------+------------
    1    |1/1/2010 |   1000    |
    1    |4/1/2010 |   2000    |
              ...

Result

    ID   |  Date   |    Value  |    Most Recent Quarterly Value
---------+---------+-------------------------------------------
    1    |1/1/2010 |     10    |             1000
    1    |1/2/2010 |     15    |             1000
              ...
    1    |3/1/2010 |     20    |             1000
              ...
    1    |4/1/2010 |     30    |             2000

I am dealing with financial data, where the daily table stores stock prices, volume etc., while the quarterly table stores financial information such as net income, revenues. The update frequency of quarterly data depends on each company so it is not safe to assume that quarters have 90 days distance.

The start and end date is dependent on the ID. QuarterStart date is the earnings announcement date which is different for each ID. The table has 3,000 IDs. QuarterEnd date is simply next QuarterStart date - 1 business day (in the financial industry, Fiscal and Calendar quarter are two similar but not identical terms. Both are quarters, but calendar year is based on constant date ranges, while Fiscal is based on announcements).

I m looking for a solution that will not require the creation and maintenance of another table.

Here is a solution that uses CROSS APPLY to find the value for the latest quarter that ends on or before the daily date. If your table is indexed by (ID, Date), this query will be quite efficient with a one-row seek to look up the quarterly value for each daily date.

This solution also does not require a calendar table and makes no assumption about the duration of a quarter. However, it does make an assumption that you have no gaps in your quarterly table. For example, if you were missing a year's worth of quarters, the solution would identify the "most recent quarter" as the quarter that happened a year ago. This may be perfectly fine, but you should at least be aware of the assumption.

CREATE TABLE #DailyTable (ID INT, DailyTableDate DATE, Value INT)
CREATE TABLE #QuarterlyTable (ID INT, QuarterlyTableDate DATE, Value INT)
ALTER TABLE #QuarterlyTable ADD UNIQUE CLUSTERED (ID, QuarterlyTableDate)

INSERT INTO #DailyTable (ID, DailyTableDate, VALUE) VALUES (1,'2010-01-01',10)
INSERT INTO #DailyTable (ID, DailyTableDate, VALUE) VALUES (1,'2010-02-01',15)
INSERT INTO #DailyTable (ID, DailyTableDate, VALUE) VALUES (1,'2010-03-01',20)
INSERT INTO #DailyTable (ID, DailyTableDate, VALUE) VALUES (1,'2010-04-01',30)

INSERT INTO #QuarterlyTable (ID, QuarterlyTableDate, VALUE) VALUES (1,'2010-01-01',1000)
INSERT INTO #QuarterlyTable (ID, QuarterlyTableDate, VALUE) VALUES (1,'2010-04-01',2000)

SELECT d.ID,
    d.DailyTableDate AS Result_Date,
    d.Value,
    qt.Value AS Most_Recent_Quarterly_Values
FROM #DailyTable AS d
CROSS APPLY (
    -- Find the value for latest quarter that ended on or before the daily date
    SELECT TOP 1 q.Value
    FROM #QuarterlyTable q
    WHERE q.ID = d.ID
        AND q.QuarterlyTableDate <= d.DailyTableDate
    ORDER BY q.QuarterlyTableDate DESC
) qt


Invoke-SqlCmd - does casting as integer prevent injection I am lazy and would rather parameterize once (to get an object_id for a name) and use Invoke-SqlCmd for the rest of the work.

Given a series of functions meant to work with an arbitrary server object, is the below test enough to confirm that CmdletBinding an input parameter as Int32 prevents injection by way of the user-supplied $object_id?

function foo {
    [CmdletBinding()]Param(
        [Int32]$object_id
    )
    $query = "select type_desc from sys.objects where object_id = $object_id;"
    Invoke-Sqlcmd -ServerInstance "localhost" -Database "tempdb" -Query $query
}

foo 3
foo "0 union all select name from sys.syslogins where sid = 0x01"
foo $null
foo ([math]::Pow(2,31)+1)
foo @(1,2)

You can, but there's problems with doing that.

Primarily, it's still not a great idea because the pattern isn't extendable.  It only works for certain data types.  As soon as you have a string parameter, you're back where you were with injection.  It also means that you have to care about how certain data types (byte arrays, GUIDs, datetimes) get cast to strings.

You also have to be aware that null-valued parameters will get silently modified to 0 since [int32] is a non-nullable type.  You'd have to specify [nullable[int32]] to prevent that behavior.  Even then, since null values that are cast to a string will result in an empty string, you get a much more descriptive error with a parameter.

Would you rather debug this:

  Invoke-Sqlcmd : Incorrect syntax near ';'.

Or this:

  Exception calling "Fill" with "1" argument(s): "The parameterized query '(@object_id int)select type_desc from sys.objects where object_i' expects the parameter '@object_id', which was not supplied."

Finally, if you're going to use the query a lot it might be harder for the system to reuse the query plan.

It's really not that difficult to specify once you're used to it:

function foo {
    [CmdletBinding()]
    Param(
        $object_id
    )

    $Query = 'select type_desc from sys.objects where object_id = @object_id;'
    $ConnectionString = 'Data Source={0};Initial Catalog={1};Integrated Security=SSPI' -f 'localhost', 'tempdb'

    $SqlConnection = New-Object -TypeName System.Data.SqlClient.SqlConnection -ArgumentList $ConnectionString

    $SqlCommand = New-Object -TypeName System.Data.SqlClient.SqlCommand -ArgumentList $Query, $SqlConnection
    $SqlCommand.Parameters.Add('@object_id', [System.Data.SqlDbType]::Int).Value = $object_id

    $SqlDataAdapter = New-Object -TypeName System.Data.SqlClient.SqlDataAdapter -ArgumentList $SqlCommand

    $DataTable = New-Object -TypeName System.Data.DataTable -ArgumentList 'objects'

    [void]$SqlDataAdapter.Fill($DataTable)
    $SqlConnection.Dispose()

    , $DataTable
}

[If you're fine with outputting DataRows only, you can drop the comma from the last line.  The comma forces the function to output the DataTable whole.  Without the comma, you'll get a stream of DataRows.]

It looks complicated, but you can reuse this pattern over and over again.  I essentially copied the above code from my own scripts.

If your concern is code deployment, then I'd argue that this method is still better.  The above code requires only the .Net Framework.  Indeed, the code even works on PowerShell Core 6.0 (granted, you have to call (foo <value>).Rows to get equivalent output because Core hasn't been taught how to display DataTables at the command line).  Using Invoke-Sqlcmd adds a dependency on the SqlServer PowerShell module being installed.  Sure, it's a first party module, but it still doesn't ship with Windows by default.  You definitely lose some benefits of encapsulation being done by others without Invoke-Sqlcmd, but there's still a trade-off.


How to normalize data en-masse? Goal:

Extract address fields from a users table into a separate addresses table
linked by foreign key.

I have created the structure below, and have millions of rows to deal with. I
need to do this once, but relatively quickly (ie, I can't loop over every row
in my Rails code)

The query below does work to extract the address fields into new rows in the
addresses table. But I can't figure out how to link the new rows back to the
users table that the data came out of.

Structure:

Before:

User
  login
  address_line_one
  address_line_two
  address_city
  address_state
  address_zip

After:

User
  login
  address_id

Address
  id
  line_one
  line_two
  city
  state
  postal_code
  country

What I have so far:

INSERT INTO addresses (line_one, line_two, city, state, postal_code, country)
SELECT address_line_one,
  address_line_two,
  address_city,
  address_state,
  address_zip AS zip,
  'US'
FROM users
WHERE
  address_line_one IS NOT NULL ||
  address_line_two IS NOT NULL ||
  address_city     IS NOT NULL ||
  address_state    IS NOT NULL ||
  address_zip      IS NOT NULL

PROBLEM

I don't know how to get the address_id back into the users table

This answer assumes that users.login is unique.

First, alter the users table to include an address_id column.
Next, if you add a addresses.login column you would then be able to do the following query after the insert into addresses:

UPDATE users, addresses SET users.address_id = addresses.address_id
WHERE users.login = addresses.login

Finally drop the addresses.login column

An alternative would be to use an AFTER INSERT trigger on addresses to update the users table based on the unique address entries. Unfortunately, this assumes that all the fields of addresses combined represent a unique users.login, which for example would be invalid if a married couple each had logins but shared the same address.


Row_number and wrapping select ? I have this code :

Select name, age , row_number() over (partition by name order by age desc) rn from my table

(goal : select the oldest guy among same names)

If I want to select all the row numbered records which is 1 ,I guess I must do :

Select ... from ( above query) where rn=1

But this  force me to wrap it with another select.

Is there any other solution instead of wrapping in select ?  (cte is also like wrapping)

This isn't generally possible but is in the specific case in your question.

Windowed functions are allowed only in steps 5.1 and 6 in the Logical Query Processing flow chart here (The SELECT and ORDER BY).

SELECT cannot be used to reduce the returned rows. In SQL Server 2008 ORDER BY can only do so in conjunction with TOP (For 2012 OFFSET ... FETCH can filter on the basis of ORDER). As you are only interested in ones where the ROW_NUMBER expression evaluates to 1 in this case you can use

SELECT TOP (1) WITH TIES name,
                         age
FROM   mytable
ORDER  BY row_number() OVER (partition BY name ORDER BY age DESC)

SQL Fiddle

Following discussion in the comments about how this could be extended to get the TOP 2 per partition. This would be possible

SELECT TOP (1) WITH TIES name,
                         age
FROM   mytable
ORDER  BY CASE
            WHEN row_number() OVER (partition BY name ORDER BY age DESC) <= 2 THEN 0
            ELSE 1
          END

I personally wouldn't use either of the queries in this answer though. I would just wrap the query in a table expression and filter using WHERE.


Accepted Values Stored in Variable I am trying to write a re-usable query and this is pseudo-code of what I am after.  Is this possible to do in Sql Server 2008 or do I just need to write a different stored procedure for each possibility?

Declare @acceptedvalues varchar(max), @whole12 varchar(500)

if @whole12 = 'MNM'
Begin
  SET @acceptedvalues = 'One', 'Two', 'Three'
End

if @whole12 = 'ALS'
Begin
  Set @acceptedvalues = 'Five', 'Seven', 'Nine'
End

Select
persID
,case when numinput NOT IN (@acceptedvalues) then 'Two' end as [validnumber]
FROM mixednuts

EDIT
I am trying to use a table variable - and this is my syntax, but it gives me an error of

  Msg 137, Level 16, State 1, Line 32
  Must declare the scalar variable "@acceptedvalues".

Declare @whole12 varchar(500)

DECLARE @acceptedvalues AS TABLE
(
    AValue int NOT NULL
)

Set @whole12 = 'MNM'

if @whole12 = 'MNM'
Begin
  INSERT INTO @acceptedvalues (AValue)
  VALUES ('1'), ('2'), ('3')
End

if @whole12 = 'ALS'
Begin
  INSERT INTO @acceptedvalues (AValue)
  VALUES ('5'), ('6'), ('7')
End

Create Table #MixedNuts
(
  persid int
  ,numinput int
)

Insert Into #MixedNuts Values ('1', '5'), ('2', '10'), ('3', '2')

Select
persID
,case when numinput NOT IN (@acceptedvalues) then 'Two' end as [validnumber]
FROM #mixednuts

You need to declare @acceptedvalues as a table variable.  Similar to the below.

DECLARE @acceptedvalues AS TABLE
(
AValue VARCHAR(50) NOT NULL
)

INSERT INTO @acceptedvalues (AValue)
VALUES ('One'), ('Two'), ('Three')

Then it will work as you have specified in the case statement.

Sorry, you need to query the @AcceptedValues.

WHEN numinput NOT IN (SELECT AValue FROM @AcceptedValues) THEN 'Two' END AS [validnumber]


Where can I find a syntax reference for Oracle SQL Developer's "Generate DB Doc" function? Oracle SQL Developer (I'm using v3.2) has a feature called "DB Doc", which generates documentation for database objects. I mostly want to use it to generate documentation for my stored procedures, functions, packages and types. However, I can't find any documentation for it, describing what syntax I should use.

I have worked out that I should use a /* ... */ comment on the line(s) immediately above my procedure/whatever, and (by borrowing from JavaDoc) I have successfully used @param and @returns statements, but I'm not sure what else I can use. For example, are there fields for author, version, data modified, etc.?

I've done numerous searches of this site, Google, and Oracle's documentation, to no avail!

Community wiki answer initially based on comments left by thatjeffsmith:

This is an exhaustive list of what it supports:

(reproduced from http://pldoc.sourceforge.net/maven-site/samples/sample1.sql)

CREATE OR REPLACE
PACKAGE CUSTOMER_DATA
IS
/**
* Project:         Test Project (PLDoc)

* Description:     Customer Data Management

* DB impact:       YES

* Commit inside:   NO

* Rollback inside: NO

* @headcom
*/

/**
* Record of customer data.
*
* @param id     customer ID
* @param name       customer name
* @param regno      registration number or SSN
* @param language   preferred language
*/
TYPE customer_type IS RECORD (
  id                        VARCHAR2(20),
  name                      VARCHAR2(100),
  regno                     VARCHAR2(50),
  language                  VARCHAR2(10)
);

/** Table of customer records. */
TYPE customer_table IS TABLE OF customer_type INDEX BY BINARY_INTEGER;

/**
* Gets customer by ID.
*
* @param p_id       customer ID
* @param r          record of customer data
* @throws no_data_found if no such customer exists
*/
PROCEDURE get_customer (
  p_id              VARCHAR2,
  customer_rec      OUT customer_type);

/**
* Searches customer by criteria.
*
* @param p_criteria record with assigned search criteria
* @param r_records  table of found customers (may be empty!)
*/
PROCEDURE get_by_criteria (
  p_criteria        customer_type,
  r_records         OUT customer_table);

/**
* Creates a customer record.
*
* @param customer_rec record of customer data
*/
PROCEDURE create_customer (
  customer_rec      customer_type);

/**
* Changes customer data.
*
* @param customer_rec record of updated customer data
*/
PROCEDURE update_customer (
  customer_rec      customer_type);

END;
/

We support everything in PLDOC - we just have a GUI vs a CLI for it. There are three code samples there, you should be able to do anything listed in those samples.


Can I make something similar to a makefile for psql? Basically I have a handful of .sql files that I regularly modify in structure that need to be run in a certain order:

CREATE.sql,
INSERT.sql,
PLPSGSQL.sql,
TEST.sql,
DROP.sql

Since every structural modification requires a full re-run of this sequence I would like to save myself the trouble of tapping throgh my command history and compile them into a makefile-esque structure; e.g.

$ Makepsql
$ psql
> \i CREATE.sql
> \i INSERT.sql
> \i PLPSGSQL.sql
> \i TEST.sql
> \i DROP.sql
> \q

Is there something like this?

Write a bash script and pass those files as arguments to psql.

#!/bin/bash

set -e #abort on error

psql -v ON_ERROR_STOP=1 -f CREATE.sql
#...insert your scripts here
#...
psql -v ON_ERROR_STOP=1 -f DROP.sql

If you want the Makefile functionality (e.g. check modified dates of files and dependencies), just write a Makefile, and use the psql commands!


Change Data Capture in MySQL/PHP by writing to a MySQL table (rather than streaming to Kafka/Rabbit/etc)? I have a small internal web app I'm developing with PHP and MySQL. I'm looking to sync client webpages to a server by requesting all changes since a certain date.

I've found a number of solutions for doing this via a 3rd party Change Data Capture library in MySQL (Maxwell, Debezium, etc.), but they all seem to stream to Kafka or something similar. This is a smaller, simple project without any sort of message queue/broker.

All I want is for changes to be recorded in the same MySQL database in a table with columns like the CRUD action, the table that changed, the table row ID, and the datetime. Then I could query for all rows changed since a certain date and time.

Is there a simple way or a library that can record this information right in the same MySQL database?

The most obvious solution to this would perhaps be to use triggers. You can have triggers that fire before/after inserts, deletes and updates on the table(s) in question, and then log the changes in your special table, including a time stamp and what type of write operation it was.

Another approach could be to use an audit plugin - MariaDB's audit plugin works with older versions of MySQL at least up to 5.5. MySQL has Enterprise Audit, which presumably has a proprietary licence. These plugins will log to a file, but you can set up a cron job or similar to load that into a database table.

(With MariaDB 10.3+ you would be able to use system-versioned tables which would allow you to store all changes to rows.)


Restore Oracle database from cold backup files I have cold backup files for an oracle database. Can someone tell me a step by step procedure on how to restore the database on my machine.I am using Oracle 11g

Cold backups, like hot backups can be taken in different ways. One is by using rman - recommended - the other is using OS tools like copy/dd. Next to that, storage based snapshots could be used. So in order to know how to restore, it is important to know how the backup has been made.

If the backup is properly made using RMAN, there is not much more to do than starting your instance, connect to the catalog, or find a snapshot controlfile that contains the backup administration, connect to the instance and issue a restore database command. If it is an OS tools backup, use the same tool[s] to copy the files to their original locations and start the database, once done.

There are many potential pitfalls using cold OS tool backups. I have seen situations in which the admin told me ....

  'eventually we switched to xcopy because we got file locking problems
  ...'

instead of looking further. He missed the point that the database was started during the cold backup. This is one of my reasons not to be a big fan of COLD backups. They make me shiver.

Be smart and carefully read Oracle® Database Backup and Recovery User's Guide
11g Release 2 (11.2) It is worth every minute you spend studying it. Oracle backup and recovery is not hard, there are just a lot of options.


Copy to Data Warehouse I have been tasked with copying select databases from the production SQL server to the Data Warehouse (at least nightly). However, no matter how I try to copy the data over, I run into an issue. Below is our setup:

Production Enviroment

SQL 2014 Enterprise
around 20 TB of data
Weekly Full backups, Nightly Diff (minus on night of weekly), every 15 minutes Transaction Logs backup

Data Warehouse

SQL 2016 Enterprise
In the same network/datacenter as prod
In theory a 10G connection between servers
Both in VMware both on same storage device

Here is the options I have found and the reasons it will be troublesome:

Mirroring - Breaks Full/Diff backups
Log Shipping - Breaks 15 minute transaction logs
Restoring from full/diff nightly - Too much data (still a possibility, but MGMT not happy about it)
Presenting same virtual drive on both servers - Causes issues with vmware/non-standard setup

All I have to do is get the data over, after that its up to our vendor to do the manipulations. Am I overlooking some options? Has anyone else run into this?  I am new to DBA, so any help is appreciated.

The restore from full/diff nightly is actually a good option, because it kills two birds with one stone: you get the Data Warehouse and you're also regularly testing your backups.

The typical method to populate a data warehouse from production is to use an ETL (Extract-Translate-Load) technology, like SQL Server Integration Services (SSIS). SSIS packages can be created in Visual Studio or simple ones can be created with the Import and Export Wizard. Now with 20 TB you obviously can't copy all the data every night - even a 10G connection probably couldn't keep up with that. But if you can copy most of the data over once and then incrementally load the changed/updated data every night, that should work. (The Generate Scripts wizard could help you replicate the table structures if necessary.)

Because you're running Enterprise edition on the source instance, you have several options for finding what's changed on a daily/nightly basis. Your best option would be Change Data Capture (aka CDC). The little brother of CDC is Change Tracking, which is less powerful but also a little easier to work with. If you're lucky enough that most or all of the tables have reliable UpdateDate columns, then that will work as well.

There would be challenges in keeping the structure of your source and Data Warehouse tables in sync, but you should be able to mitigate that by making sure you push changes to the Data Warehouse databases when you push them to the production database. If the Data Warehouse is just a copy of the production stuff, then you should be able to just run the same release package/scripts.

I realize this may seem daunting, but once you've got it set up, it should mostly run itself.


You don't seem to need the ticket table. The output does contain the ticket.id column, but it is always same as ticket_history.ticket_id, which is also included in the output.

In view of the above, the following query reads only ticket_history. It uses conditional aggregation to build the output you are after:

SELECT
  ticket_id,
  MIN(id) AS id,
  MAX(CASE name WHEN '%%Close' THEN NULL ELSE create_time END) AS open_time,
  MAX(CASE name WHEN '%%Close' THEN create_time ELSE NULL END) AS close_time
FROM ticket_history
GROUP BY
  ticket_id
HAVING COUNT(name LIKE '%\%Close'            OR NULL) = 1
   AND COUNT(name LIKE '%\%Raw\%% %\%new\%%' OR NULL) = 1
;

The HAVING clause makes sure a ticket has both open and close rows. The OR NULL trick is explained in this Stack Overflow answer:

Why do I need “OR NULL” in MySQL when counting rows with a condition

The id column is derived as MIN(id) on the assumption that a Close row will always appear after the corresponding Open one, and therefore the latter's id will always be lower than the former's.

The open_time and close_time values are obtained using conditional aggregation. Depending on whether the name column matches the '%\%Close' pattern or not, the create_time column is aggregated as either close_time or open_time.

You can use this SQL Fiddle demo to try the solution.