Central Management Server - Query against host server I like the idea of setting up a Central Management Server to run queries against other servers that I have to maintain. I'd like to be able to run one query to get information from all servers, like log sizes, backup history, index fragmentation, etc.

I'm just not sure how to set it up correctly so that I can also run these queries against the host database.  It seems crazy that you can't manage your Central Management Server.

How do people get around this?  Do you create a specialized instance whose sole purpose is acting as the management server?  Or pick a server that you just don't care about the health?

I don't want to have to run one query for the single server, and a second for the groups.  That seems to defeat the purpose.

Am I missing something?

Update with solution copied from comment below

Oh snap!  I stand corrected.

My original attempt was to register the CNAME/Alias as the CMS, then register the server by name.  This always failed.

Second attempt, registered the CMS using the server name. I then registered the server using the IP address and this worked.  I don't have clue how they're different, but it works so I'm happy.

I assume that we are indeed talking about Central Management Servers here. And not Master/Target Agent or something else which isn't related to CMS. I see a lot of confusion in this area. Here are a few options:

Skip the CMS. You can register whatever SQL Servers you have in the traditional way (locally) and use a multi server query window (MSQW) for the local server group. Again, I frequently see folks believing that MSQW can only be used with CMS.

Just register the SQL server wherever you want to have it in the CMS tree, but use the IP instead of the machine name. SSMS won't understand that it is the same machine.

Configure an alias on the client, and use that alias name in the server registration. Obvious downside is that all clients where you want to use the CMS list need to have that alias. Sort of defeats the purpose of CMS in the first place.

Perhaps an additional DNS entry pointing to your server? I'm no DNS person, so I can't say if you can have two DNS entries pointing to the same IP.

I'd go for the IP address in the CMS registration. In fact, I did exactly just that a few days ago with a client.


Create index on a Postgres table as fast as possible I have a large database import (100GB) on a Postgres table without indexes.

After the import, I want to create an index for queries as fast as possible. No data is accessed in the table as long as the index is not ready.

What is the quickest way of building the index? I have to build the index on 3 columns (two varchar, one date). The index creation takes about 2 hours, which is really not useful.

Is there any way I could speed up the index creation? Probably it's not better to set the index before data import, as this will slow down the import?

I am aware that Postgres has an option to create an index "without locking", but wouldn't that decrease performance, since it enables Postgres to access the data while index is being created?

This is a virtual machine. The memory of the server can be increased to my needs. Currently I have 32GB. work_mem and maintenance_work_mem are still at the default configuration values. I don't have SSD for the database. Would this certainly speed things up?

I don't have any boolean flags or similar that could be used for partial indexes. In fact, I want each row in my table to be indexed.

If it's an option, you could pre-sort the data externally, before database import.

In my test using PostgreSQL 9.6.1, a table with three double precision columns and 10M records with random values, creating an index on the same data, but preordered, shaved off 70% of index creation time:

db=# create table indexing_test_data (a varchar(50), b varchar(50), c real);
CREATE TABLE
Time: 3,586 ms
db=# insert into indexing_test_data select random()::text, random()::text, random() from generate_series(1, 10000000);
INSERT 0 10000000
Time: 25590,475 ms
db=# select a, b, c into indexing_test_sorted from indexing_test_data order by a, b, c;
SELECT 10000000
Time: 77389,665 ms

db=# create index test_data_idx on indexing_test_data (a, b, c);
CREATE INDEX
Time: 57399,140 ms

db=# create index test_sorted_idx on indexing_test_sorted (a, b, c);
CREATE INDEX
Time: 16219,639 ms

An extra speedup can be had if you can afford to use a database with C locale and collation, which can then use a feature called abbreviated keys, available since PostgreSQL 9.5. This speeds up sorts up to 20x, but only works on C locale, due to buggy locale support in older libraries:

db=# create database testdb lc_collate "C" lc_ctype "C" template template0;
CREATE DATABASE
Time: 429,710 ms
db=# \c testdb
You are now connected to database "testdb" as user "user".
testdb=# create table indexing_test_data (a varchar(50), b varchar(50), c real);
CREATE TABLE
Time: 2,794 ms
testdb=# insert into indexing_test_data select random()::text, random()::text, random() from generate_series(1, 10000000);
INSERT 0 10000000
Time: 25977,964 ms
testdb=# select a, b, c into indexing_test_sorted from indexing_test_data order by a, b, c;
SELECT 10000000
Time: 20794,850 ms

testdb=# create index test_data_idx on indexing_test_data (a, b, c);
CREATE INDEX
Time: 16371,426 ms

testdb=# create index test_sorted_idx on indexing_test_sorted (a, b, c);
CREATE INDEX
Time: 8046,787 ms

Here, indexing sorted data takes around 50% time of unsorted, but compared to first version, that took 57 seconds, you're down to 14% of time.

Other things, as mentioned in the comments, can help too: setting maintenance_work_mem appropriately and not running create index concurrently (which is significantly slower).


Can I do a sargable first letter match on two tables? select value
from persons p join persons2 p2
    on left(p.lastname,1) = left(p2.lastname,1)

SQL Server. Is there any way to make this SARGable/run faster? I can't create columns on persons table, but I can create columns on persons2.

If the lastname column is indexed in at least one of the tables then you could also use LIKE

SELECT *
FROM   persons p
       INNER JOIN persons2 p2
               ON p2.lastname LIKE LEFT(p.lastname, 1) + '%'

The plan for this can have a seek on the table specified to the left of the like.

i.e. ON p.lastname LIKE LEFT(p2.lastname, 1) + '%' would not be able to make use of the index on persons2that was used above but could seek one on persons.

The suggestion in the other answer of indexing a calculated column on both sides is more flexible however. As for a nested loops plan either table can be on the inside and it would also allow a many to many merge join without requiring a sort.


SQLAnywhere: Failure on creation of procedure I wanted to create a procedure like this:

CREATE OR REPLACE PROCEDURE __generate_new_VSN(@oldVSN varchar(100))
RETURNS VARCHAR(100)
AS
BEGIN
    RETURN '1'
END

but getting when running it via isql:

Anweisung konnte nicht ausgeführt werden.
Syntaxfehler bei 'RETURNS' in Zeile 2
SQLCODE=-131, ODBC 3-Status="42000"
Zeile 1, Spalte 1

which can be translated to something like

Command couldn't be executed
Syntax error at 'RETURNS' on line 2
SQLCODE=-131, ODBC 3-Status="42000"
Zeile 1, Spalte 1

What did I wrong?

The problem is you are mixing the CREATE FUNCTION syntax with that for CREATE PROCEDURE

This should work:

CREATE OR REPLACE PROCEDURE __generate_new_VSN(@oldVSN varchar(100), OUT @newVSN  varchar(100))
BEGIN
      RETURN '1'
END

the full syntax is here


Enable slow_query_log tries to write to slow_log.CSV I'm running MySQL 5.5

I wanted to truncate the slow log file, so I did:

SET GLOBAL slow_query_log = 0;

then truncated the file in filesystem.

But, when I enable it back it fails:

SET GLOBAL slow_query_log = 1;
ERROR 13 (HY000): Can't get stat of './mysql/slow_log.CSV' (Errcode: 2)

Although the slow_query_log_file set explicitly to another path.

SHOW GLOBAL VARIABLES LIKE 'slow_query%';
+------------------------------------+---------------------------------+
| Variable_name                      | Value                           |
+------------------------------------+---------------------------------+
| slow_query_log                     | OFF                             |
| slow_query_log_file                | /home/mysql_data/jobs1-slow.log |
| slow_query_log_timestamp_always    | OFF                             |
| slow_query_log_timestamp_precision | second                          |
| slow_query_log_use_global_control  |                                 |
+------------------------------------+---------------------------------+

What is wrong? I want MySQL log to the specified file. Plain text, not CSV.

SHOW GLOBAL VARIABLES LIKE 'log_output';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| log_output    | FILE  |
+---------------+-------+

Before I stopped it logging was fine.

I did these steps :

set global slow_query_log=0;
remove the slow log file in system
set global slow_query_log=1;

it works fine, so I tried these steps:

mysql> set global slow_query_log=0;
Query OK, 0 rows affected (0.12 sec)

mysql> set global slow_query_log_file='/tmp/slow.log';
Query OK, 0 rows affected (0.10 sec)

mysql> set global slow_query_log=1;
Query OK, 0 rows affected (0.00 sec)

still works, and new slow log file is generated as /tmp/slow.log.

At last, the value of global variable log_output decides where to generate the general log and slow log's output, not the file format.


How to setup Oracle SSL using self-signed certificate between client and DB? I want my DB to send the data to the client in an encrypted way.
I Googled and found that Oracle has SSL. Unfortunately, I can't find a step by step tutorial which explains how to create a self-signed certificate, configure SSL on DB side, configure SSL on client side and test it.

Please, share your knowledge with me.

Thank you in advance.

---EDIT

My steps:

Run Oracle Wallet Manager on DB server.
Create New wallet.
Create New Certificate Request.
Exported Certificate Request.
Using ssl.ca-0.1 (Downloaded from http://www.openssl.org/contrib/) created self-signed root certificate using new-root-ca.sh and imported as a trusted certificate on DB server.
Created self-signed server certificate by running the sign-server-cert.sh and imported as an user certificate on DB server.

Here I am confused , .... don't know how to configure client side

In ssl.ca-0.1 there is also files new-user-cert.sh and sign-user-cert.sh, for what they are used I don't understand.

Tried to run them using the same certification request that was exported from DB server and tried to import it on the client wallet AM I WRONG?

May be yes, because it doesn't import user certificate... I am really confused don't understand the main idea.

I copied wallet generated on DB server on the client side wallet location, AM I RIGHT?

I found the answer.

In my post I've described how to create trusted and user certificates. As I guess trusted certificate is public key and user certificate is a private key(correct me if I am wrong).

Now I write how I have done this job.

--on DB server

Run owm(Oracle Wallet Manager) and:

Create new wallet.
Create certification request.
Export certification request.
using ssl.ca-0.1(Downloaded from http://www.openssl.org/contrib/) created self-signed root certificate using new-root-ca.sh and imported as a trusted certificate on DB server.
Created self-signed server certificate by running the sign-server-cert.sh and imported as an user certificate on DB server.

--On client server
Copied root certificate(generated previously).

Create new wallet.
Create certification request.
Import trusted certificate by choosing copied root certificate.

Edit tnsnames.ora file on client server to use TNS entry using protocol TCPS and port, for example, 2484.

Edit listener.ora file on DB server to listen port 2484 and protocol TCPS.

Open Net Manager and configure SSL on both sides.

--DB side

Indicate wallet directory.

I've unchecked "Require Client Authentication"
and other options what you want.

--Client side

Indicate wallet directory..

and other options what you want.


Naming convention for automated table outputs (processed with Python) I have several tables that I process nightly with Python.

For example, I have read-only access to an incidents table. I process the table with a specialized Python library called ArcPy. The script has a number of steps, including calculating the Minimum Bounding Geometry (a.k.a. convex hull) of like-incidents. The script processes the incidents table nightly and outputs the results to a new table.

I would like to follow database conventions and industry practices when outputting these tables, where possible. Having a distinct naming convention would allow me to easily identify master tables from output tables.

Is there a naming convention for automated table outputs (processed with python, or otherwise) that I should be using?

I dont think there are any hard rules around this. It's really up to you to decide what convention suits you best.

The options available to you may be influenced by the database software you are using.

The 3 suggestions i have are:

Use a different schema i.e [Processed] and keep the table name the same.
Add a prefix so that all processed tables appear together.
Add a suffix to the table name. So that when you sort by name the original and processed appear together.

The 2nd and 3rd option make more sense if you spend more time working with tables as groups or with original and processed as a pair.


How many rows will be locked by SELECT … ORDER BY xxx LIMIT 1 FOR UPDATE? As was asked on stackoverflow but then for MySQL, I was wondering how this works in PostgreSQL. How many rows get locked when I do a "FOR UPDATE" in combination with a "LIMIT", a "ORDER BY" and some "WHERE"'s. And they update the returned row. I was hoping the "FOR UPDATE"-query would only lock ONE row but maybe I miss some implementations issue. (I'm planning to write some stress-test to reproduce this more reliably)

These queries cause a deadlock in my system:

(session1) select field1, field2, ... from documents where transaction_path='some/path' for update
(session2) select field1, field2, ... from documents where (transaction_path is null) and queue_id=2 and next_pickup_ts<now() order by next_pickup_ts limit 1 for update
(session3) select field1, field2, ... from documents where (transaction_path is null) and queue_id=2 and next_pickup_ts<now() order by next_pickup_ts limit 1 for update

But I do not understand why. (There is a unique index on transaction_path.)

If you use EXPLAIN you'll see the ordering. For a random plan I concocted with some throwaway data and similar query (ORDER BY ... LIMIT 1 FOR UPDATE) I got the plan:

 Limit  (cost=33.09..33.10 rows=1 width=26)
   ->  LockRows  (cost=33.09..39.88 rows=543 width=26)
         ->  Sort  (cost=33.09..34.45 rows=543 width=26)
               Sort Key: t_id
               ->  Seq Scan on m  (cost=0.00..30.38 rows=543 width=26)
                     Filter: (b_id <= 33)
(6 rows)

Here, you can see the rows are potentially locked before the limit is applied.

Now, in practice, the first row that gets matched will generally get locked and returned, causing LockRows on the later rows to get skipped ... but there's no guarantee of this. So you shouldn't rely on it.

In general, mixing row-locking and LIMIT is a bad idea that should generally be avoided.

If you must do it, you'll want to do something like:

SELECT *
FROM my_table
WHERE id = (SELECT id FROM my_table
            WHERE mywhereclause
            ORDER BY ... LIMIT 1)
  AND mywhereclause
FOR UPDATE;

where you force a target row to be found, and only then locked.

You should repeat the WHERE clause in the outer query to ensure that the row still matches the predicate after any lock-wait occurs. Otherwise what can happen is that the inner query finds the row, returns the ID, and you try to lock the row with that ID. PostgreSQL sees that someone else has a lock on that row, so it waits for that lock to be released. Then it re-checks the WHERE clause on the outer query to make sure it still matches (in case the row was deleted, or was UPDATEd). Because the inner query is an uncorrelated subquery it won't re-calculate it, though, so the inner WHERE clause doesn't get re-run, and you can get rows that no longer match the inner WHERE clause returned if you don't repeat it in the outer WHERE clause.

Mixing row limits and row locking is hard. Building correct queuing systems that actually perform better than a single worker is even harder. Use an off the shelf queuing system and save yourself a lot of hassle.

(PostgreSQL 9.5 will have SKIP LOCKED, which makes this way easier, but there won't be a stable release of it for over a year from now, so don't hold your breath.)


Upgrade Postgresql from 9.6 to 11 with different data directory I am trying to upgrade PostgreSQL version from 9.6 to 11. I am following this link. The problem is I have changed my default location of PostgreSQL data directory from /var/lib/postgresql/ to /opt/postgresql/. So when I am using pg_lsclusters it is returning following

When I am running the sudo pg_upgradecluster -m upgrade 9.6 main its returning following error

Error: move_conffile: required configuration file /var/lib/postgresql/11/main/postgresql.conf does not exist
Error: Could not create target cluster

You can specify a custom data_directory as an optional parameter to the command pg_upgradecluster:

pg_upgradecluster [-v newversion] oldversion name [newdatadir]

So for your case, it should be:

sudo pg_upgradecluster -m upgrade 9.6 main /opt/postgresql/11/main

Note that you must first remove any existing cluster at the specified location (/opt/postgresql/11/main) and create an empty directory with the correct owner before running pg_upgradecluster:

sudo pg_dropcluster --stop 11 main
sudo mkdir -p /opt/postgresql/11/main
sudo chown postgres:postgres /opt/postgresql/11/main


Why mysqldump is returning with error 1143: Couldn't execute 'show table status like I ran the following command to create a mysqldump

mysqldump -ubackup -psomething --single-transaction --quick somedatabase | gzip > 4_19.gz

I got following error after 60% (estimating checking size of the zip file) of the dump created:

mysqldump: Couldn't execute 'show table status like 'customer\_cohort\_paid'': SELECT command denied to user ''@'%' for column 'customer_id' in table 'sales_order' (1143)

It says of about permission issues for a blank user user ''@'%'. Moreover, grants for backup user is as followed:

mysql> show grants for 'backup'@'%';
+------------------------------------------------------------------------------------------------------------------------------------------------+
| Grants for backup@%                                                                                |
+------------------------------------------------------------------------------------------------------------------------------------------------+
| GRANT SELECT, LOCK TABLES, SHOW VIEW, EVENT, TRIGGER ON *.* TO 'backup'@'%' IDENTIFIED BY PASSWORD '*SOMETHING' |
+------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

What am I doing wrong?

There is a bug report on this: mysqldump: Couldn't execute 'show table status': SELECT command denied to user. Surprisingly, it's not a bug at all. here is why:
customer_cohort_paid might be a view. Whatever user created the view is not listed anymore in mysql.user. What this does is make such a view incapable of being dumped.
You could do one of two(2) things
SUGGESTION #1
Force the dump with -f
mysqldump -f -ubackup -psomething --single-transaction --quick somedatabase | gzip > 4_19.gz

If you can force the dump to make views appear in the dump file, you may want to edit it (See my old post Modify DEFINER on Many Views or this other one How do I change the DEFINER of a VIEW in Mysql?)
WARNING: If any one extended INSERT command has a syntax problem or improperly escaped or encapsulated data, the hundreds of rows in that one INSERT may not be loaded.
SUGGESTION #2
Find out the original user who made the view, and recreate that user
Run this query : select * from mysql.tables_priv;
This will show all table-level and column-level grants. Look at the first three columns and figure out what GRANT command to make that user again.


AlwaysOn High Availability Take database offline from primary I have below scenario where i need your help:

I have configured the AlwaysOn High Availability on my server. I have two replicas. Now I have requirement to make one database offline from the primary replica and make it online after some time.

So what happens in this scenario in background?

After making the database from primary replica online, the database from the secondary replica goes into restoring state. And we need to always manually delete the database from secondary server and remove it from AG group and add it again to both in sync again.

Is there any solution for this? If not are there any steps to do the above task from code?

Now I have requirement to make one database offline from the primary replica and make it online after some time.

This is not possible since database is added to AOAG. You cannot take the db offline unless you remove it from AG.

What you can do is ...

Disable the login on primary.
Enable read_only secondary.
Have your application configure to use read intent and read from secondary
After your testing is done, enable the login on primary.


what's extsort.* (8 - 11 MB) in MongoDB dbpath? Directory _tmp I never saw this in my dbpath and didn't found any explanation with a search engine.

We run MongoDB 3.2.8

# ls -alh; pwd
total 151M
drwxr-xr-x. 2 mongod mongod 4.0K Sep 12 22:18 .
drwxr-xr-x. 6 mongod mongod  12K Sep 12 22:16 ..
-rw-r--r--. 1 mongod mongod 8.0M Sep 12 21:54 extsort.0
-rw-r--r--. 1 mongod mongod  10M Sep 12 21:54 extsort.1
-rw-r--r--. 1 mongod mongod 7.9M Sep 12 22:02 extsort.10
-rw-r--r--. 1 mongod mongod 7.9M Sep 12 22:07 extsort.11
-rw-r--r--. 1 mongod mongod 9.9M Sep 12 22:07 extsort.12
-rw-r--r--. 1 mongod mongod 7.9M Sep 12 22:11 extsort.13
-rw-r--r--. 1 mongod mongod 9.9M Sep 12 22:12 extsort.14
-rw-r--r--. 1 mongod mongod 8.0M Sep 12 22:16 extsort.15
-rw-r--r--. 1 mongod mongod  11M Sep 12 22:18 extsort.16
-rw-r--r--. 1 mongod mongod 7.9M Sep 12 21:55 extsort.2
-rw-r--r--. 1 mongod mongod 9.9M Sep 12 21:55 extsort.3
-rw-r--r--. 1 mongod mongod 7.9M Sep 12 21:55 extsort.4
-rw-r--r--. 1 mongod mongod 9.8M Sep 12 21:55 extsort.5
-rw-r--r--. 1 mongod mongod 7.9M Sep 12 21:56 extsort.6
-rw-r--r--. 1 mongod mongod  11M Sep 12 21:57 extsort.7
-rw-r--r--. 1 mongod mongod 8.0M Sep 12 21:58 extsort.8
-rw-r--r--. 1 mongod mongod 9.9M Sep 12 22:01 extsort.9
/data/name-replicaset/name-replicaset_0/_tmp

These files are used when the sort you are attempting in an aggregation exceeds the maximum allowed memory and you have allowDiskUse : true specified.

The relevant pieces of the code are here (where the SortedFileWriter is called) and here (where the extsorter.# files are written to temp by SortedFileWriter). Basically, this is the number of files required by MongoDB to perform the sorting that is being specified in your aggregations.


Simple queries to Redshift really slow I just started testing AWS Redshift and populated a single node with the AWS sample data.

Querying a table with 10 or ~400 rows takes around 2 seconds, uncached.

I'm not sure if I'm misunderstanding something, but I assumed that would be a lot faster. Is there something I'm missing here?

Thanks! Any feedback appreciated.

The whole idea around Redshift is not to run in a single node. It is actually designed to run in a sharded cluster and it is expected to have very bad numbers within only one node. If you look at the internals you'll see that is actually designed to run on top of a set of nodes, adding an extra layer for the query processing.

If you are already familiar with the basics, you can check the Best practices.


Large INSERTs performance optimization I have 15 Amazon AWS EC2 t1.micro instances which simultaneously populate Amazon RDS MySQL d2.m2.xlarge database with data using large INSERTs (40000 rows in query).
The queries are sent continuously. The table is INNODB, two INT columns, there is index for both columns. CPU utilization of RDS instance is about 30% during data receiving.
When I have one EC2 instance, the speed is in orders of magnitude faster than I run 15 instances simultaneously. In light of this, the 15-instances group works slower and slower until the speed becomes totally unsatisfactory.
How can I optimize performance for this process?
UPDATE
My SHOW CREATE TABLE result is the following:
CREATE TABLE `UserData` (
 `uid` int(11) NOT NULL,
 `data` int(11) NOT NULL,
 PRIMARY KEY (`uid`,`data`),
 KEY `uid` (`uid`),
 KEY `data` (`data`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1

I need 2 indexes because it is necessary for me to fetch data by uid and by data value.
I insert data with INSERT INTO UserData (uid, data) VALUES (1,2),(1,3),(1,10),... with 40000 (uid,data) pairs.
15 parallel instances insert ~121 000 000 rows in 2 hours, but I am sure that it can be much more faster.

Two hints for you:

The KEY uid is redundant, because it is covered by the PRIMARY KEY
40,000 rows at a time might make for too large a transaction. Although these are very small rows (two INTs) this may cause the transaction to go to disk, depending on your settings. I usually go with around 1,000 rows at a time (I go as low as 100 and as high as 10,000).
Please try doing 40 * 1,000 and see if this works better for you than 1 * 40,000


InnoDB possible error We are moving now from MyISAM to InnoDB. We have faced previous problems in MyISAM where the index fail and the table is not available for access. So we are wondering what type of errors that InnoDB might show because we want to fully maintain integrity of our data.

Below is .cnf file.

[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
user=mysql
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
innodb_file_per_table

server-id=9508071
log-bin=mysql-bin
log-error=mysql-bin.err
binlog_do_db=datacv

[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

MyISAM tables sometimes get corrupted in a power failure.  REPAIR TABLE is the remedy.

InnoDB checks and repairs all its tables after any crash.

If a crash occurs in the middle of, say, a multi-row UPDATE in MyISAM, some of the rows will be updated, some won't.  In InnoDB, the entire "transaction" will be automatically rolled back.  That is, it's all or none.

Check your indexes when moving:  http://mysql.rjweb.org/doc.php/myisam2innodb


Need to query records based on Date only (Sample Query given) sSQL = "
SELECT ordID, ordName, ordDate, ordShipping, ordStateTax,
       ordHandling, ordTotal, ordDiscount, ordPrivateStatus
FROM orders
WHERE (ordStatus = 7)
"

I want to display orders that came in yesterday as a daily report script I am building

How would I go about it?

SQL Server 2005

ordDate has this format: 2015-01-28 14:58:49.000

An open-ended range is the safest in terms of granularity and sargability, and being immune to underlying data type changes:

DECLARE @today DATETIME;
SET @today = DATEADD(DAY,DATEDIFF(DAY,'20000101',GETDATE()),'20000101');

SELECT ... WHERE ordDate >= DATEADD(DAY,-1,@today)
             AND ordDate < @today;

You may get suggestions for other creative ways to find the rows from yesterday, but they'll be less safe and/or slower. You will probably be suggested things like this:

WHERE CONVERT(CHAR(10), ordDate, 112) = CONVERT(CHAR(10), GETDATE(), 112);
-- not sargable! Will cause a table/index scan even if there is an index on ordDate

WHERE ordDate BETWEEN <yesterday at mignight> AND <yesterday at 23:59:59.997>
-- unsafe! Will break if variables or underlying columns change data types

Some links:

What do BETWEEN and the devil have in common?
Bad habits to kick : mis-handling date / range queries
What is the most efficient way to trim time from datetime?

On that last one, pay attention to the performance graphs, particularly how well the open-ended range does.

When you move to SQL Server 2008 or better, you can also consider the easier CONVERT(DATE approach, since this is one of the very few isolated scenarios where applying a function to a column is still sargable. However, you should also read this:

Cast to date is sargable but is it a good idea?

To create a procedure for this, in SQL Server:

CREATE PROCEDURE dbo.myreport
  @ReportDate DATETIME = NULL
AS
BEGIN
  SET NOCOUNT ON;

  -- report on yesterday if parameter is NULL:
  SET @ReportDate = COALESCE(@ReportDate, DATEADD(DAY, -1, GETDATE()));

  -- remove time from datetime, in case time is passed:
  SET @ReportDate = DATEADD(DAY, DATEDIFF(DAY, '20000101', @ReportDate), '20000101');

  SELECT ordID, ordName, ordDate, ordShipping, ordStateTax,
    ordHandling, ordTotal, ordDiscount, ordPrivateStatus
  FROM dbo.orders
  WHERE (ordStatus = 7)
    AND orderDate >= @ReportDate
    AND orderDate < DATEADD(DAY, 1, @ReportDate);
END
GO

Now in your ASP code, after opening oConn, you do something like this:

set cmd = CreateObject("ADODB.Command")
cmd.Activeconnection = oConn
cmd.CommandText = "dbo.myreport"
cmd.commandType = 4 'adCmdStoredProc
dateParam = date()
cmd.parameters.Append ccmd.CreateParameter("ReportDate", adDate, 1, 0, dateParam)
set oRS = cmd.execute()
do while not oRS.eof
  ...

Sorry, my classic ASP is rusty, so you may have to make adjustments. Thankfully, there are dozens and dozens of sites out there that include tutorials on using connection, command and recordset objects in classic ASP.


Is it possible to track a function's dependencies in Postgres? When there is a function that is dependent on another object, is there any way to track that dependency?

For example, is there a way to tell that test2() relies on test1?:

create table test1 (id int);
create function test2() returns int language sql as
   $$ select id from test1 limit 1 $$;

Context: I have a system of materialized views, with a "refresh all" procedure implemented using the logic from https://wiki.postgresql.org/wiki/Refresh_All_Materialized_Views. Unfortunately, the database has evolved such that one of the matviews must call another matview through an intermediary function. The only workaround I can think of is to either scrap this system and define the refresh order manually, or introduce false dependencies on the function's source matview whenever the function is used. Neither option is appealing; is there any alternative?

Since functions are stored as strings in PostgreSQL, and there is no limit to the languages a function could be written in, PostgreSQL cannot analyze the code to add dependencies.

The best option you have is to manually figure out the dependencies.

If you store that information in a database table, you could make sure that all REFRESH MATERIALIZED VIEW statements are executed in the correct order.


SQL Server 2012 Job History Missing I have a SQL Server 2012 database with a number of Agent jobs.  The jobs run without a problem but the job history is always empty.  Why?

I also note that running a job manually (right click and "Start Job at Step...") will run the job but the window showing progress of the job never closes.  It is as if the job steps complete but the job itself never completes.

All tasks have been executed as a sysadmin so permissions should not be an issue.

Running the jobs via sp_start_job results in the jobs completing but still no job history.

I've done all the usual checking/changing of jobhistory_max_rows_per_job etc.

While this only addresses a small part of your question (and you may already be aware):

When you right-click on a job and choose "Start Job at step...", the window that opens does not close automatically when the job is finished. You have to manually close the window (which can be done before or after the job completes).

Once the "Execute job" line in the window has a status (whether success or error), there should (normally) be an entry in sysjobhistory, even if the window hasn't closed (just confirmed in my system).


Store a formula in a table and use the formula in a function I have a PostgreSQL 9.1 database where part of it handles agent commissions. Each agent has his/her own formula of calculation how much commission they get. I have a function to generate the amount of commission each agent should get, but it's becoming impossible to use as the number of agents grow. Am forced to do some extremely long case statements and repeating code, which has made my function very big.

All the formulas have constant variables:

d .. days worked that month
r .. new nodes accuired
l .. loyalty score
s .. subagent commission
b .. base rate
i .. revenue gained

The formula can be something like:

d*b+(l*4+r)+(i/d)+s

Each agent negotiates the payment formula with the HR dept. So can I store the formula in the agents table then have like a small function that just gets the formula from the table and translates it with values and computes the amount?

Prepare

Your formulas look like this:

d*b+(l*4+r)+(i/d)+s

I would replace the variables with $n notation so they can be replaced with values directly in plpgsql EXECUTE (see below):

$1*$5+($3*4+$2)+($6/$1)+$4

You can store your original formulas additionally (for the human eye) or generate this form dynamically with an expression like:

SELECT regexp_replace(regexp_replace(regexp_replace(
       regexp_replace(regexp_replace(regexp_replace(
      'd*b+(l*4+r)+(i/d)+s'
      , '\md\M', '$1', 'g')
      , '\mr\M', '$2', 'g')
      , '\ml\M', '$3', 'g')
      , '\ms\M', '$4', 'g')
      , '\mb\M', '$5', 'g')
      , '\mi\M', '$6', 'g');

Just make sure, you translation is sound. Some explanation for the regexp expressions:

  \m .. matches only at the beginning of a word
  \M .. matches only at the end of a word

4th parameter 'g' .. replace globally

Core function

CREATE OR REPLACE FUNCTION f_calc(
    d int         --  days worked that month
   ,r int         --  new nodes accuired
   ,l int         --  loyalty score
   ,s numeric     --  subagent commission
   ,b numeric     --  base rate
   ,i numeric     --  revenue gained
   ,formula text
   ,OUT result numeric
)  RETURNS numeric AS
$func$
BEGIN
   EXECUTE 'SELECT '|| formula
   INTO   result
   USING  $1, $2, $3, $4, $5, $6;
END
$func$ LANGUAGE plpgsql SECURITY DEFINER IMMUTABLE;

Call:

SELECT f_calc(1, 2, 3, 4.1, 5.2, 6.3, '$1*$5+($3*4+$2)+($6/$1)+$4');

Returns:

29.6000000000000000

Major points

The function takes 6 value parameter and formula text as 7th. I put the formula last, so we can use $1 .. $6 instead of $2 .. $7. Just for the sake of readability.
I assigned data types for the values as I saw fit. Assign proper types (to implement basic sanity checks) or just make them all numeric:
Pass in values for dynamic execution with the USING clause. This avoids casting back and forth and makes everything simpler, safer and faster.
I use an OUT parameter because that's more elegant and makes for shorter clearer syntax. A final RETURN is not needed, the value of the OUT parameter(s) are returned automatically.
Consider the lecture on security by @Chris and the chapter "Writing SECURITY DEFINER Functions Safely" in the manual. In my design, the single point of injection is the formula itself.
You could use defaults for some parameters to further simplify the call.


postgis query id to multiline query I need some insight on how to get data out of a telemetry DB in just the right way and fumbling with this produces either errors or incorrect data

I have a table like this:

CREATE TABLE Telemetry
(
  pk              SERIAL PRIMARY KEY NOT NULL,
  id              VARCHAR(36)        NOT NULL,
  organizationid  VARCHAR(36)        NOT NULL,
  lat             REAL               NOT NULL,
  lon             REAL               NOT NULL,
  basetype        VARCHAR(16)        NOT NULL,
  name            VARCHAR(64)        NOT NULL,
  updatetimestamp TIMESTAMP          NOT NULL
);

and a query like this:

select id, ST_Multi(point(lon,lat)::geometry) as cords from telemetry GROUP BY id;

But that produces:

  [42803] ERROR: column "telemetry.lon" must appear in the GROUP BY
  clause or be used in an aggregate function Position: 27

so, I give this a swing:

SELECT
  id,
  organizationid,
  basetype,
  name,
  ST_Multi(point(lon, lat) :: GEOMETRY) AS cords
FROM telemetry
GROUP BY id, organizationid, basetype, name, updatetimestamp, cords
ORDER BY updatetimestamp;

which produces no error, but then doesn't group as I would expect.

d333aa7f-223d-4a13-8a4b-50e7ecbe424e,...,PACKAGE,xHSUPlzbizu8IOybrtDw,...
8e272cf7-bc03-4d70-b139-03081f6fa1b4,...,VEHICLE,...
d333aa7f-223d-4a13-8a4b-50e7ecbe424e,...,PACKAGE,xHSUPlzbizu8IOybrtDw,...

Notice how d333.. repeats? What I need is

id,orgid,baseType -> [(lat,lonn),(lat,lon)...]

id, org, basetype will all be the same

I need all the telemetry positions for a given id sorted by time; each telemetry event is captured with a timestamp and I need  each point in the multipoint to be added as it was found in time series (or it's no good).

In case it isn't obvious, I need to track positional data as something moved and draw these points on a map.

There are a few problems here.

The id column in your schema is not declared as unique. What would you do if you had two of the same ids? @a_horse_with_no_name reads this as greatest-n-per-group, but why the greatest? What's the criteria for?

INSERT INTO telemetry ( id, lat, lon ) VALUES (1,1,1);
INSERT INTO telemetry ( id, lat, lon ) VALUES (1,1,2);

You're using point(). Point is a native-type function. If you're using PostGIS do not use point(). Instead, create your points with ST_MakePoint. (saves the casting).
You should consider not storing your types as lat/log points, but instead storing them as geometry, or geography. This will be faster, though it'll cost a bit more in space. Further, it'll work with indexing better.

The first reading of this problem is addressed by @a_horse_with_no_name  but I'm thinking you want a multipoint for every id in the table. For that you need a PostGIS aggregate, like ST_Union

SELECT id, ST_Union(ST_point(lon,lat)) AS cords
FROM telemetry
GROUP BY id;

That should work.

SELECT id,
  ST_AsText(ST_Union(ST_MakePoint(lon,lat))) AS cords_with_union,
  ST_AsText(ST_Collect(ST_MakePoint(lon,lat))) AS cords_with_collect
FROM ( VALUES
    (1, 1::real, 2::real),
    (1, 2::real, 2::real),
    (1, 2::real, 2::real), -- a duplicate (for id:1)
    (2, 6::real, 5::real)
) AS t(id,lon,lat)
GROUP BY id;

 id |  cords_with_union   |   cords_with_collect
----+---------------------+-------------------------
  1 | MULTIPOINT(1 2,2 2) | MULTIPOINT(1 2,2 2,2 2)
  2 | POINT(6 5)          | MULTIPOINT(6 5)

You can force a MULTIPOINT if you really want that by wrapping ST_Union like ST_Multi(ST_Union()).


Here is what you are missing

Connecting with user a via a socket is the same as connecting with user a via localhost.
Connecting with a socket will not use TCP/IP

If you are going to explicitly using a hostname, you must use TCP/IP with port 3306 and no socket

mysql -ua -p -h hosta --protocol=tcp -P3306

I have discussed using --protocol=tcp in my earlier posts

Jul 16, 2015 : mysql.sock not generated when starting MySQL server using XAMPP
Jul 13, 2015 : mysql restart issue after move database
Jul 11, 2013 : Rare error lasts only a second "Can't connect to local MySQL server through socket '/tmp/mysql.sock' (146)" 146=ECONNREFUSED
Mar 28, 2013 : MySQL - ERROR 1045 (28000): Access denied for user

You need to delete blank users, blank hosts, and blank passwords from mysql.user

DELETE FROM mysql.user WHERE user = '';
DELETE FROM mysql.user WHERE host = '';
DELETE FROM mysql.user WHERE password = '';
FLUSH PRIVILEGES;


Regarding table access How to revoke access for particular table from an user who have 'select any table' privilege?

I want to block access to system tables that have password details such as user$ etc..

DB : Oracle 11.2.0.4

When the O7_DICTIONARY_ACCESSIBILITY parameter is set to false, users with SELECT ANY TABLE privilege can not access tables such as SYS.USER$, unless they have direct privileges to them.


MySQL duplicate data entry issue I know this issue can be solved using unique key constraint on column and some insert command variation.

But I exactly want to know that at last why the following approach goes wrong sometimes and a duplicate entry is added:

a value is checked in the column
if record returns is > 0 then update is issued, otherwise simple insert command

Most of the times it works. You can say more than 98%, but why does it fail sometimes?

I just want to understand where the glitch is in this approach because record existence is checked first.

One scenario in which this would fail depends on how busy (or unlucky) you are in timing.

Session A does the select on the value, sees that it should INSERT
Session B does a select on the value before session A does the required insert or update of the value constraint
Session A inserts row, updates value
Session B inserts row, updates value

It's all a matter of timing (as suggested by your 98% works). You could acquire a WRITE LOCK for Session A on the table you are inserting AND on the table you are updating the value, which will block Session B until Session A releases the lock.

Alternatively, you could go ahead and let the DB handle the locking automatically by creating a UNIQUE INDEX and issuing a INSERT .. ON DUPLICATE UPDATE (recommended)


Why do the MySQL bin log files still exist after a purge or flush? I've used PURGE BINARY LOGS as well as FLUSH LOGS, but the mysql directory still contains these files:

mysql-bin.000025
mysql-bin.000024
mysql-bin.000023
mysql-bin.000022
mysql-bin.000021
mysql-bin.000020
mysql-bin.000019
mysql-bin.index

Is there a reason why using the commands are not working? These files are taking a lot of space. I would like to get rid of them safely.

The PURGE BINARY LOGS statement deletes all the binary log files listed in the log index file prior to the specified log file name or timestamp. Deleted log files also are removed from the list recorded in the index file, so that the given log file becomes the first in the list.
I hope you have purged binary logs upto mysql-bin.000019 using command
PURGE BINARY LOGS TO 'mysql-bin.000019';

If you need to purge all logs do like
PURGE BINARY LOGS TO 'mysql-bin.000025';

This will remove binary logs upto mysql-bin.000025.
UPDATE
You can try
RESET MASTER;

RESET MASTER Deletes all binary log files listed in the index file, resets the binary log index file to be empty, and creates a new binary log file
The effects of RESET MASTER differ from those of PURGE BINARY LOGS in 2 key ways:

RESET MASTER removes all binary log files that are listed in the index file, leaving only a single, empty binary log file with a numeric suffix of .000001, whereas the numbering is not reset by PURGE BINARY LOGS.

RESET MASTER was not intended to be used while any replication slaves are running. The behavior of RESET MASTER when used while slaves are running is undefined (and thus unsupported), whereas PURGE BINARY LOGS may be safely used while replication slaves are running.

CAVEAT by RolandoMySQLDBA
If you run RESET MASTER with Slaves connected and running, the IO Thread of each Slave will immediately lose its place. Replication is thus broken and you will have to spend time getting the data on all Slaves sync'd again. If you want to safely delete binary logs from a Master without breaking Replication integrity, here is what you do:

Run SHOW SLAVE STATUS\G on each Slave.
Take note of Relay_Master_Log_File. This is the binary log whose latest statement was successfully executed in the Slave).
From all displays of SHOW SLAVE STATUS\G, determine which Relay_Master_Log_File is the oldest (For example, 'mysql-bin.00123').
You can run PURGE BINARY LOGS TO 'mysql-bin.00123'; None of the Slaves will lose its place.

The overall effect? This will leave behind binary logs on the Master whose statements that have not been executed on all Slaves as of yet.


MongoDB Data loss following machine PowerOff with writeConcernMajorityJournalDefault true Background:

I'm using mongodb 4.2.0
I have deployed a mongo cluster which contain 5 configs, 3 querys and 3  - shards. Each shard consist of 4 replicas and 1 arbiter.
All members are set on VMs.
ReplicaSets writeConcernMajorityJournalDefault flag is true.
Test conducted with pymongo

The Test:

Before implementing the cluster on the production environment, I've conducted several "stress tests". I have created a simple script that performs many inserts to the cluster and returns the amount of successful inserts.

When I run and stop the script everything is just fine. The number of inserts_count is identical to the count of documents in the collection.

BUT, When I run the script and then PowerOff the Primary member, I'm facing a hitch. My script's insert_count is bigger (10-20) than the count of documents in my collection. I assume that I'm losing data.

I got successful insert acknowledge even though my replicaSet is set writeConcernMajorityJournalDefault true.

Raising the primary doesn't help to retrieve the lost data.

I think the data was still in memory!

Conclusion:

I believe that there is some malfunction with the journaling setting.

Please help me understand what am I doing wrong.

P.S:

I tried insert with {w: majority, j: true, wtimeout: 5000} parameters.

Same results :/

How much time your query was running?? and from where you get next count as you mentioned after stopping primary you get this count is lesser?? also If you can share your script.

It will be obvious that If you are stopping the Script the data will not going to be insert as you expected.


Why does the fill factor in SQL Server default to 0 (100%)? I understand fill-factor, pages and index structure and I therefor understand why a 100% fill factor is a rare best practice. So why does it default to 0 (or 100% ) by default? Why not 90 or 95?

Is there something I'm missing?

Just throwing out a few suggestions here as to why Microsoft would do this:

They have geared their default to be best possible OLAP performance (less page reads with a fill factor of 0/100)
They are assuming that INSERTed data will most likely be at the end of the table, making the extra space per page useless
They are assuming that typically UPDATEd data will not lengthen row data very often, causing a page split

This is just guessing here.  The only people that can really answer that question accurately is the SQL Server team themselves.


Oracle no privileges on tablespace USERS I have a brand new Oracle database that is giving the error:

ORA-01950: no privileges on tablespace 'USERS'

I have done:

alter user kainaw quota 100M on 'USERS';
grant unlimited tablespace to kainaw;

Still, a single insert returns that error. Other than disk quota, what else causes the "no privileges on tablespace 'USERS'" error?

UPDATE:

Oracle version is 11.2.0.3.0 (11g). I am logging in from the command prompt on the server. So, I alter user kainaw as sysdba. Then, I logout and login a user kainaw to test:

insert into i.test values (1);

Note: i.test is a table with only a number field. I get the error above. I logout as kainaw, login as sysdba, play with permissions, logout, login, test, error, logout, login, ...

You are granting the privileges to the incorrect user.

The schema owner i owns the table, and is therefore the user that needs to be granted the relevant permissions on the tablespace.


Should Postgres function return/coerce numeric scale/precision? The following function returns two generic numerics, not the numeric(20,10) I think I'm specifying in the function return definition. This is unexpected (by me). Is there a way to ensure the function does the "coercion" to numeric(20,10) so outside code doesn't have to cast?
drop function test_numeric;
CREATE OR REPLACE FUNCTION test_numeric ()
RETURNS TABLE(m numeric(20,10), n numeric(20,10)) AS $$
-- RETURNS TABLE(m numeric, n numeric) AS $$
BEGIN
    RETURN QUERY
    select
        -- returned as written (with more than 10 decimal places)
        88888888.88888888888888888 as mm,
        -- truncated as expected
        88888888.88888888888888888::numeric(20,10) as nn;
END;
$$ LANGUAGE plpgsql;

[Edit based on feedback in answers] Here's a fiddle to show how the function signature does not enforce the return type (hoped for). M is the point. Even though the signature says we're returning a numeric(20,10) a generic numeric gets returned. The problem is when subsequent code expects a numeric(20,10) but the type is numeric (comes in when doing a create or update view/table etc). What's most unexpected is that the function signature basically gets ignored, or overridden by the code, to the next user down the road sees the function signature, expects a 20/10 and gets something totally different.
Fiddle
Another fiddle showing it doesn't matter at all what scale or precision is specified to be returned; any numeric scale/precision can be returned and will remain the scale or precision it was inside the function body.
Fiddle
[Further Edit] Apologies for beating the horse and demonstrating my misunderstanding of the Numeric type perhaps. But if the scale and precision matters when changing this view, it's hard to understand why it wouldn't matter when defining the table the function will return. I could maybe understand if numeric(20,10) was a subtype of numeric and could be passed if I'd specified numeric to be returned. Just never heard of something less specific passing for something specified to be more specific (in a strongly typed language).
Fiddle

RETURNS TABLE(m numeric(20,10), n numeric(20,10)) does not enforce the data types of the returned columns; it's simply a declaration, metadata intended to inform the callers what the function should return. The declaration cannot not require anything, it's a "promise" that is to be fulfilled by the function implementation. The function code itself is responsible for what it returns. In other words, you must use explicit type casts, as necessary, in the function body.
If the function doesn't fulfil the promise, it's an implementation bug.

P.S.
Looking at the source code of coerce_type, it simply compares type OIDs, so any numeric is compatible with any other numeric:

    if (targetTypeId == inputTypeId ||
        node == NULL)
    {
        /* no conversion needed */
        return node;
    }


Setting shared_buffers in postgresql.conf does not seem to take effect We're on CentOS release 6.6, PostgreSQL version 8.4.20. (Yes, this is not bleeding edge.)

In postgresql.conf, we have:

shared_buffers = 4096MB

kernel shm values are set nice and high:

[root@green data]# sysctl -a | grep shm
kernel.shmmax = 15922077696
kernel.shmall = 3887226
kernel.shmmni = 4096
kernel.shm_rmid_forced = 0

We have plenty of memory:

[root@green data]# free
             total       used       free     shared    buffers     cached
Mem:      31097812   30474972     622840    2873672    1961088   20565360
-/+ buffers/cache:    7948524   23149288
Swap:      1959920      93852    1866068

Yet the value for shared_buffers reported by pg_settings is only 512MB, not the 4GB set in postgresql.conf:

postgres=# select name, setting, min_val, max_val, context from
pg_settings where name='shared_buffers';
      name      | setting | min_val |  max_val   |  context
----------------+---------+---------+------------+------------
 shared_buffers | 524288  | 16      | 1073741823 | postmaster

Yes, we've done a full restart, and SHOW config_file confirms that I've edited the right postgresql.conf.

My very great thanks to anyone who can provide insight into this mystery.

The canonical unit for shared_buffers is pages of 8kB, so the actual memory allocated in bytes is:

524288 * 8192 = 4294967296 or 4096*1024*1024 as requested.

You can also check the size of the segment of memory with ipcs -m


If I correct understand Your question this should work. I have provided a link to SQL FIDDLE:

declare @max_date datetime
declare @min_date datetime

select @min_date = '2015-10-01 07:00:00'
select @max_date = '2015-10-31 05:00:00'

SELECT EmployeeId
    , (SELECT TOP 1 CAST(CAST(CONVERT(DATE, CheckTime) AS VARCHAR(10)) + ' ' + CAST(MIN(CAST(CheckTime AS TIME)) AS VARCHAR(8)) AS DATETIME) FROM table_name AS t2 WHERE t2.EmployeeId = t1.EmployeeId GROUP By CheckTime ORDER BY CAST(CheckTime AS TIME) ) AS  MinDateTime
    , (SELECT TOP 1 CAST(CAST(CONVERT(DATE, CheckTime) AS VARCHAR(10)) + ' ' + CAST(MIN(CAST(CheckTime AS TIME)) AS VARCHAR(8)) AS DATETIME) FROM table_name AS t2 WHERE t2.EmployeeId = t1.EmployeeId GROUP By CheckTime ORDER BY CAST(CheckTime AS TIME) DESC ) AS MaxDateTime
FROM table_name AS t1
WHERE CheckTime BETWEEN @min_date and @max_date GROUP BY EmployeeId;

SQL Fiddle - http://sqlfiddle.com/#!6/fd199/2
DATA:

1   2015-10-14 18:06:22
1   2015-10-28 23:06:40
2   2015-10-07 18:13:58
2   2015-10-14 11:14:15
1   2015-10-14 19:20:11
2   2015-10-08 10:20:34
3   2015-10-01 20:25:28
3   2015-10-30 10:26:04

RESULT:

1   2015-10-14 18:06:22.000 2015-10-28 23:06:40.000
2   2015-10-08 10:20:34.000 2015-10-07 18:13:58.000
3   2015-10-30 10:26:04.000 2015-10-01 20:25:28.000


Form groups computing max - min depending on number of peers This is table:

CREATE table point (
         datetime timestamp,
         value float)

I have data in some range of time

7:00 - 7:15:   0   5 : 5

7:15 - 7:30:   -   - : null

7:30 - 7:45:   7   - : 2

INSERT INTO point values ('2011-01-01 07:01:00', 0);
INSERT INTO point values ('2011-01-01 07:14:00', 5);
INSERT INTO point values ('2011-01-01 07:31:00', 7);

This is the the query:

SELECT date_trunc('minute', datetime) - ((EXTRACT(MINUTE FROM
      datetime)::int) % 15) * interval '1 minute',
    MAX(value) - MIN(value)
    from point
    where datetime BETWEEN '2011-01-01 07:00:00' and '2011-01-01 07:45:00'
    group by 1

This is the current result:

2011-01-01 07:00:00 |        5
2011-01-01 07:30:00 |        0

My expect result is:

2011-01-01 07:00:00 |        5
2011-01-01 07:30:00 |        2

The second result I want is 7 - (5 - 0) = 2. If previous time also has only one value, it also should be 7 - (5 - 0) = 2.

If I understand correctly ...

Use a subquery and compute the value depending on the count of rows.
Then get the final result using the window function lag():

SELECT time_interval
     , CASE WHEN ct > 1 THEN span ELSE span - lag(span) OVER (ORDER BY time_interval) END AS span
FROM  (
   SELECT date_trunc('minute', datetime) - ((EXTRACT(MINUTE FROM datetime)::int) % 15) * interval '1 minute' AS time_interval
        , CASE WHEN count(*) > 1 THEN MAX(value) - MIN(value) ELSE MAX(value) END AS span
        , count(*) AS ct
   FROM   point
   WHERE  datetime BETWEEN '2011-01-01 07:00:00' and '2011-01-01 07:45:00'
   GROUP  BY 1
   ) sub


Retrieve by inserting ORDER of Merged table in SQL Server I have a 3 tables such as "BASE_Customer", "BASE_Invoice" and "BASE_Payment" the below structure,

Query:

CREATE TABLE BASE_Customer
(
    CustomerId INT IDENTITY(1,1),
    CustomerName VARCHAR(45)
    PRIMARY KEY(CustomerId)
)
INSERT INTO BASE_Customer (CustomerName) VAlUES ('LEE')

CREATE TABLE BASE_Invoice
(
    InvoiceId INT IDENTITY(1,1),
    InvoiceDate DATE,
    CustomerId INT NOT NULL,
    InvoiceMethod VARCHAR(45) NOT NULL, -- CASH or CREDIT
    Amount MONEY,
    PRIMARY KEY(InvoiceId)
)
INSERT INTO BASE_Invoice (InvoiceDate, CustomerId, InvoiceMethod, Amount) VAlUES ('2020-01-16', 1, 'CASH', 1000);
INSERT INTO BASE_Invoice (InvoiceDate, CustomerId, InvoiceMethod, Amount) VAlUES ('2020-01-16', 1, 'CREDIT', 2000);
INSERT INTO BASE_Invoice (InvoiceDate, CustomerId, InvoiceMethod, Amount) VAlUES ('2020-01-17', 1, 'CREDIT', 500);
INSERT INTO BASE_Invoice (InvoiceDate, CustomerId, InvoiceMethod, Amount) VAlUES ('2020-01-18', 1, 'CREDIT', 2000);
INSERT INTO BASE_Invoice (InvoiceDate, CustomerId, InvoiceMethod, Amount) VAlUES ('2020-01-18', 1, 'CASH', 150);
INSERT INTO BASE_Invoice (InvoiceDate, CustomerId, InvoiceMethod, Amount) VAlUES ('2020-01-19', 1, 'CREDIT', 1000);
INSERT INTO BASE_Invoice (InvoiceDate, CustomerId, InvoiceMethod, Amount) VAlUES ('2020-01-19', 1, 'CREDIT', 3000);
INSERT INTO BASE_Invoice (InvoiceDate, CustomerId, InvoiceMethod, Amount) VAlUES ('2020-01-19', 1, 'CASH', 1000);
INSERT INTO BASE_Invoice (InvoiceDate, CustomerId, InvoiceMethod, Amount) VAlUES ('2020-01-20', 1, 'CREDIT', 2250);

CREATE TABLE BASE_Payment
(
    PaymentId INT IDENTITY(1,1),
    PaymentDate DATE,
    CustomerId INT NOT NULL,
    InvoiceId INT NULL,
    PaymentMethod VARCHAR(45) NOT NULL, -- CASH or CREDIT, ADVANCE
    Amount MONEY,
    PRIMARY KEY(PaymentId)
)
INSERT INTO BASE_Payment (PaymentDate, CustomerId, InvoiceId, PaymentMethod, Amount) VAlUES ('2020-01-16', 1, 1, 'CASH', 1000);
INSERT INTO BASE_Payment (PaymentDate, CustomerId, PaymentMethod, Amount) VAlUES ('2020-01-16', 1, 'CREDIT', 500);
INSERT INTO BASE_Payment (PaymentDate, CustomerId, InvoiceId, PaymentMethod, Amount) VAlUES ('2020-01-18', 1, 4, 'ADVANCE', 500);
INSERT INTO BASE_Payment (PaymentDate, CustomerId, PaymentMethod, Amount) VAlUES ('2020-01-18', 1, 'CREDIT', 2000);
INSERT INTO BASE_Payment (PaymentDate, CustomerId, InvoiceId, PaymentMethod, Amount) VAlUES ('2020-01-18', 1, 5, 'CASH', 150);
INSERT INTO BASE_Payment (PaymentDate, CustomerId, PaymentMethod, Amount) VAlUES ('2020-01-18', 1, 'CREDIT', 5000);
INSERT INTO BASE_Payment (PaymentDate, CustomerId, PaymentMethod, Amount) VAlUES ('2020-01-19', 1, 'CREDIT', 1200);
INSERT INTO BASE_Payment (PaymentDate, CustomerId, InvoiceId, PaymentMethod, Amount) VAlUES ('2020-01-19', 1, 7, 'ADVANCE', 500)
INSERT INTO BASE_Payment (PaymentDate, CustomerId, InvoiceId, PaymentMethod, Amount) VAlUES ('2020-01-19', 1, 8, 'CASH', 1000);
INSERT INTO BASE_Payment (PaymentDate, CustomerId, InvoiceId, PaymentMethod, Amount) VAlUES ('2020-01-20', 1, 9, 'ADVANCE', 750);

Customer table should Join to both tables "BASE_Invoice" and "BASE_Payment" (CustomerId is Foreign Key on both tables)
when inserting a data into "BASE_Invoice" table if invoice method column CASH at the time Invoice Payment inserting with payment method CASH,
when inserting a data into "BASE_Invoice" table if invoice method column CREDIT some times we insert payment with payment method ADVANCE or otherwise we don't insert payment on payment table.
on payment table only can insert CREDIT payment methods

I need to merge all the tables once and retrieve the ORDER of output such as below screen shot,

The issue is how to merge tables and get the Order of transaction

Databases do not store data "in order" (for the most part).

If you want data out in a specific order, you must supply an appropriate ORDER BY clause.

In your data model:

BASE_Invoice is a transaction.
BASE_Payment is a transaction.

You're missing a table that holds the information for a transaction.  That table will include all columns common to both types of transactions, a transaction ID (PK) for all types of transactions, and information about the transaction that can be used by the ORDER BY clause.

It some cases, the Transaction ID could be used to determine ORDER BY.  In other cases, you may need to include a timestamp column to be used by the ORDER BY clause.


Is there a way to use PIVOT syntax to accomplish the following? I have table_A and want to have a result like table_B.  I can accomplish this by using a WHILE loop with a counter a loop 12 times to get my data in the format I want in table_B.  Wondering if there was a cleaner way.  Thank you!

--Unpivot the table.

SELECT Location, Account,Dept, Year, Month, Amount
FROM
   (SELECT Location, Account,Dept, Year, Month, Amount, January ... Dec
   FROM Table_A) p
UNPIVOT
   (Amount FOR Month IN
      (January, Feb ... Dec)
)AS Table_B;
GO

reference: link


Reading audit records in Oracle As a DBA, what is the best way to read the audit records on daily basis? Say, i'm the administrator and I would like to view the audit records made by the DBAs. I have enabled audit sys operatiosn to TRUE and and it creates records for the same but is there any easy readable format to clearly list out the changes made by the DBAs?

The audit records are written to the operating system's audit trail defined by the audit_file_dest parameter. You can change the format of to XML by changing the audit_trail parameter but the files will be still kept on the file system.
This due to security reasons since DBAs shouldn't be able to access those files if they are being audited.
You can play around with external tables or create a shell script for analyzing the files but there is no built in mechanism for analyzing the data.


How do I reduce the database size on AWS RDS MySQL? I have an RDS MySQL database on Amazon Web Services.

I want to reduce the size of the database in the control panel in order to save money.

Q: How do I make it smaller? Their site says you have to do it some magic way (from backups or such), but I don't want to mistakenly whack it!

Step by Step please... Thanks!

This is what happened: Amazon asked what my desired max size should be. I chose 10GB.  I then added data until the table exceeds 10GB.  Amazon then automatically increased the size limit to 20GB.  In their manual, they state "increasing" is automatic, but "decreasing" is not.

After removing some of the excess data so the data size is under 10GB again, is there a way to make a new 10GB db and copy the data to it using a snapshot perhaps?

Write a script that will create a script
MYSQL_CONN="-hAmazonRDSDNSName -u... -p..."
SQL="SELECT CONCAT('OPTIMIZE TABLE ',table_schema,'.',table_name,';')"
SQL="${SQL} OptimizeTableSQL FROM information_schema.tables WHERE table_schema = 'signs'"
SQL="${SQL} ORDER BY (data_length+index_length)"
mysql ${MYSQL_CONN} -AN -e"${SQL}" > /root/OptimizeAllTables.sql
less /root/OptimizeAllTables.sql

When you are satisfied that the script will do the job then do this:
mysql ${MYSQL_CONN} < /root/OptimizeAllTables.sql

Give it a Try !!!
CAVEAT
Since the default for innodb_file_per_table is ON, each InnoDB table being optimized will actually have its .ibd file shrunk. Any MyISAM tables (which I hope you don't have) will also have their .MYD and .MYI files shrunk as well.
UPDATE 2012-09-18 18:19 EDT
I just tried the following:

Created Instance1 with 5G Disk
Modified Instance1 to 10G Disk
Modified Instance1 to 5G Disk (error message : Requested storage size (5) cannot be less then the current storage size (10))
Create Instance2 with 5G
Loaded Instance1 with 2MB data
Created Snapshot of Instance1 (same size : 10G)

If I restore the snapshot, it creates a third Instance. DB Snapshot must be the whole Server Instance, not a dump of any kind.
You will have to do the following messy procedure:

mysqldump the data from the 20GB Instance
Create a New 10G Instance
load the mysqldump into the new 10GB instance
verify all the data you want is present
destroy the old 20GB

At step 4, please run this Big Storage Engine Space Query on both the 20GB and 10GB instances
SELECT IFNULL(B.engine,'Total') "Storage Engine",
CONCAT(LPAD(REPLACE(FORMAT(B.DSize/POWER(1024,pw),3),',',''),17,' '),' ',
SUBSTR(' KMGTP',pw+1,1),'B') "Data Size", CONCAT(LPAD(REPLACE(
FORMAT(B.ISize/POWER(1024,pw),3),',',''),17,' '),' ',
SUBSTR(' KMGTP',pw+1,1),'B') "Index Size", CONCAT(LPAD(REPLACE(
FORMAT(B.TSize/POWER(1024,pw),3),',',''),17,' '),' ',
SUBSTR(' KMGTP',pw+1,1),'B') "Table Size" FROM
(SELECT engine,SUM(data_length) DSize,SUM(index_length) ISize,
SUM(data_length+index_length) TSize FROM
information_schema.tables WHERE table_schema NOT IN
('mysql','information_schema','performance_schema') AND
engine IS NOT NULL GROUP BY engine WITH ROLLUP) B,
(SELECT 3 pw) A ORDER BY TSize;

You will see how much wasted space disappeared when migrating the mysqldump to the new 10GB Instance. This will definitely defragment all InnoDB tables. You may need to run the above script (/root/OptimizeAllTables.sql) I gave you above to run OPTIMIZE TABLE on all the tables in the signs database once a week to keep the .ibd as small as possible. You can also run the Big Storage Engine Space Query to Monitor when it starts approaching 10GB.


UNION SELECT after LEFT JOIN and JOIN SELECT DISTINCT re.*
    FROM `reports` r
    LEFT JOIN `users` u
    ON u.`id_rep` = r.`id`
    JOIN `customers` c
    ON u.`id` = r.`uid`
    WHERE r.`uid` = '1' and r.`name` LIKE 'urgent'
    ORDER BY r.date DESC

Without making any modifications prior to the WHERE clause, would it be possible to use UNION (or a better approach) after the LIKE statement in order to SELECT from a fourth table called workers and ORDER the results BY r.date DESC?

Using Union and ORDER BY:

Be explicit with results form first query:

(SELECT DISTINCT rep.name, rep.date as rdate
FROM `reports_new` rep
LEFT JOIN `repuser_new` ru
ON ru.`id_rep` = rep.`id`
JOIN `clients` c
ON c.`id` = rep.`id_client`
WHERE ru.`id_user` = '1' and rep.`name` LIKE 'urgent')

UNION
(SELECT username,rdate FROM users)

ORDER BY rdate DESC

I assumed the second table had a date column. Otherwise and explicit value could be used.


SQL Server 2012 cannot use file server for log shipping I have a problem with log shipping in SQL Server 2012.
I have two servers for log shipping, let's say server A and server B.

For the first experiment, when I use a shared folder from my computer based on Windows 10, it works great.

But when I try to use a shared folder from my file server such as synologi or infortrend, it failed, even after I added permissions for SQL Server Agent, my administrator on each server, my user on each server, even if I added full control permission for everyone.

Do you have any advice for my problem? Or must SQL Server 2012 log shipping use a file server which is based on a Windows OS?

When I look in the job history, I see:

  Error, Can't open backup device

I assume that this backup job from log shipping can't be done because the device (file server) cannot be accessed by SQL Server.

I am very sure that my servers, and my file server, use the same domain and the same segment, that's why I doubt that log shipping from SQL Server 2012 really works with a file server.

If I manual backup using the GUI, it won't show us the directory mapped in My Computer. But if I use a backup script, it would fail because there is no file backup in my file server.

Trying a BACKUP DATABASE command to the share, I get the following error message:

Msg 3201, Level 16, State 1, Line 1
Cannot open backup device '\\[fileserver IP]\coba\TESTLAGICOBA.bak'.
Operating system error 86(The specified network password is not correct.).
Msg 3013, Level 16, State 1, Line 1
BACKUP DATABASE is terminating abnormally.

Make sure your log shipping primary instance's SQL Server and SQL Server Agent services are configured using a domain account and has write access on the specified backup share. If these are in place it should work fine.

Note: The SQL Server Agent can be configured to use a local account as well. The primary issue is that whatever account is used must have read/write access on folder where backup is happening.

To check it manually, you can try to take a manual backup on the share specified to see if that works and make sure write access is in place. I am assuming that your SQL Server and SQL Server Agent accounts are configured to use the same domain account.

Run the below commands. Just change the DBName and FileserverPath as you have in your environment:

BACKUP DATABASE [DBName]
TO DISK = N'\\[FileserverPath]\DBName_FULLBackup.bak'
WITH INIT;
GO
EXECUTE sys.xp_cmdshell 'dir \\[FileserverPath]\';

If this gives an error, create a folder on your fileserver, share it and grant write permissions to the SQL Server Agent account, then try again.


Which mysql storage engine to choose? I'm confused about which mysql engine should I choose (lets talk about the most used : MyISAM and InnoDB).

The theories say:

Both work with BTree indexes, but MyISAM can work with FULLTEXT index also.
InnoDB is more strict in data integrity while MyISAM is loose.
InnoDB has transactions while MyISAM does not.
InnoDB has foreign keys and relationship contraints while MyISAM does not.
MyISAM are faster to read but slower to write (I'm not sure about this one).

When I create a table, should I always sacrifice something if I want to keep integrity and I want to speed up the query making search by "text"?

How do you decide which engine to use if you need

integrity ?
speed ?
constraints ?
search by text ?
transactions ?

Both works with b-tree index, but MyISAM can work with FULLTEXT index also.

MySQL 5.6 is supposed to support FULLTEXT indexing as well. Look for it when it goes GA.

InnoDB is more strict in data integrity, while MyISAM is loose
InnoDB has transactions while MyISAM does not.
InnoDB has foreign keys and relationship contraints while MyISAM does not.

This is true in light of the fact that InnoDB supports transaction isolation via MVCC. That, in part, makes InnoDB fully ACID compliant.

MyISAM are faster to read but slower to write (I'm not sure about this one)

This depends entirely on the row format of the MyISAM table (Fixed or Dynamic). Slow is relative. After all, each DML statement against a MyISAM performs a full table lock each and every time.
Given a large enough cache, InnoDB can be faster for both reads and writes because COMMITs can be delayed. This leaves data already cached in the InnoDB Buffer Pool available for reads right out of memory.
InnoDB caches both Data and Indexes while MyISAM only caches Indexes. Thus, disk reads for data from MyISAM is always required.


Why can't we perform joins in a distributed database like Bigtable? From this answer to "What database does Google use?":

  Bigtable is not a relational database. It does not support joins nor does it support rich SQL-like queries.

Is this obvious? I do not understand why.

Bigtable doesn't use SQL (a query language) so SQL can't be used directly to query the database. And Bigtable doesn't have "relations" in the same way as relational databases, it's more like bare tables.

If you want to get data from two tables, you have to do two lookups, and combine the result set in the application code. In other words the "join" operation is not built-in in the database, so you have to do that kind of operations in the application, if needed.

The fact that Bigtable doesn't support SQL and JOIN operations has nothing to do with that it is a distributed database system. There are distributed databases that has support for SQL. E.g. VoltDB and MySQL Cluster. There are also many databases that aren't distributed and doesn't use SQL e.g. Kyoto Cabinet.


Are Availability Groups feasible for 4 TB of data? Currently we have a database which is 4 TB in size. We would like to put it in a 2014 Availability Group to scale out reads. Data gets changed a lot inside this database.

I have following questions:

Is this size okay for Availability Groups (we are looking to have 2-3 replicas)?
If network dies for 1hour, how fast can we get the replica in sync?
Do you guys have any consideration I should be following before using Availability Groups?

Nismo,

It's not the SIZE of the database that matters (no jokes intended), it's the rate of change coupled with the infrastructure available. For example, a relatively static database might perform poorly on 1Gb connection on an overloaded switch with 5400 RPM sata drives.

If the rate of change (aka look at your log flush bytes) is less than around 200 MB/sec and you have very fast storage (or a ton of front end cache) and extremely low latency networking then I would say you'll be fine.

Let's go over your questions now:

  1.Is this size okay for Availability Groups (we are looking to have 2-3 replicas)?

It's not about the size, but the infrastructure. So, yes that's fine.

  2.If network dies for 1hour, how fast can we get the replica in sync?

How fast does an unladened swallow fly? Seriously though, there is no way for us to tell you. You'll need to take into account the rate of log generation + latency + blocked redo + sync/async status.

The better question to ask is, "Do I have enough space on my primary for my transaction log to NOT TRUNCATE for an hour?"

  3.Do you guys have any consideration I should be following before using Availability Groups?

I would consider taking a serious look at SQL 2016 if these are your concerns. Parallel redo (coming soon), better network utilization, distributed AGs, direct seeding, etc.

If you're intent on using 2014, then the best recommendation is to make sure the server hardware and infrastructure are up to the rate of change you'll be creating. Additionally watch when you do DDL against objects as it could pose potential problems with redo.

If you're worried about the replicas catching up in time, just remove the replicas from the AG, restore the transaction logs to them and then bring them back into the AG. Remember, your primary won't be able to have the log file re-use any VLFs while an AG is "behind" as it'll only be able to mark for re-use up to that LSN.


T-SQL Daylight Saving lookup table - poorly performing table-valued function I've created a "Daylight Savings" lookup calendar table for the GMT region. The function I'm using to query the table to return the local datetime from a UTC datetime is performing poorly.

Any help to improve this, including changing the way the TVF is coded, would be appreciated.

The function will be used in queries that can return 1m+ rows on a frequent basis. The function is used when querying warehouse tables containing trip data.

The start and end datetimes of the trips are stored in UTC and the function above is used to convert them into local time. A developer, long since gone from the company, wrote a scalar function that converts UTC time to local time. I was tasked to rewrite that function using a calendar table and a TVF as TVF are supposed to better performing than scalar functions

Without the function:

SQL Server Execution Times:    CPU time = 4633 ms,  elapsed time = 4909 ms.

execution plan without function

With the function:

SQL Server Execution Times:    CPU time = 20795 ms,  elapsed time = 21176 ms.

execution plan with function

Here's a sample output from the table

CREATE TABLE dbo.DSTLookup
(
     [Id] int,
     [Tzid] int,
     [DT_WhenSwitch] datetime,
     [DSTOffSetSeconds] int,
     [GMTOffSetSeconds] int
)

INSERT INTO dbo.DSTLookup
VALUES (29, 2, N'2014-03-30T01:00:00', 3600, 0),
       (30, 2, N'2014-10-26T02:00:00', 0, 0),
       (31, 2, N'2015-03-29T01:00:00', 3600, 0),
       (32, 2, N'2015-10-25T02:00:00', 0, 0),
       (33, 2, N'2016-03-27T01:00:00', 3600, 0),
       (34, 2, N'2016-10-30T02:00:00', 0, 0),
       (35, 2, N'2017-03-26T01:00:00', 3600, 0),
       (36, 2, N'2017-10-29T02:00:00', 0, 0),
       (37, 2, N'2018-03-25T01:00:00', 3600, 0),
       (38, 2, N'2018-10-28T02:00:00', 0, 0)

This is the TVF:

CREATE FUNCTION dbo.FN_GetLocalTime_FromUTC_BasedOnTZId
     (@StartDateTime DATETIME, @EndDateTime DATETIME, @Tzid INT)
/*=========================================================================
*   2017-03-27
*   Returns local time from UTC time based on timeZoneId
*
==========================================================================*/
RETURNS TABLE
AS
    RETURN
        (
         WITH cteStartDate AS
         (
            SELECT
                RN = ROW_NUMBER() OVER (ORDER BY D.Id DESC),
                D.DSTOffSetSeconds 's_DST_OffSet',
                D.GMTOffSetSeconds 's_GMT_OffSet'
            FROM
                dbo.DSTLookup D
            WHERE
                D.DT_WhenSwitch <= @StartDateTime
                AND D.Tzid = @Tzid
         ),
         cteEndDate AS
         (
             SELECT
                 RN = ROW_NUMBER() OVER (ORDER BY D.Id DESC),
                 D.DSTOffSetSeconds 'e_DST_OffSet',
                 D.GMTOffSetSeconds 'e_GMT_OffSet'
             FROM
                 dbo.DSTLookup D
             WHERE
                 D.DT_WhenSwitch <= @EndDateTime
                 AND D.Tzid = @Tzid
         ),
         cteConvertStartDate AS
         (
              SELECT
                  DATEADD(SECOND, (COALESCE(S.s_DST_OffSet, 0) + COALESCE(S.s_GMT_OffSet, 0)), @StartDateTime) 'LocalStartDateTime'
              FROM
                  cteStartDate S
              WHERE
                  S.RN = 1
         ),
         cteConvertEndDate AS
         (
              SELECT
                  DATEADD(SECOND, (COALESCE(E.e_DST_OffSet, 0) + COALESCE(E.e_GMT_OffSet, 0)), @EndDateTime)    'LocalEndDateTime'
              FROM
                  cteEndDate E
              WHERE
                  E.RN = 1
         )
         SELECT
             S.LocalStartDateTime, E.LocalEndDateTime
         FROM
             cteConvertStartDate S, cteConvertEndDate E
);
GO

To query the TVF:

SELECT *
FROM dbo.FN_GetLocalTime_FromUTC_BasedOnTzId
    ('2017-03-27 10:00:30', '2017-03-27 10:15:54', 2);

Execution plan following Max's recommendations to include the primary key.

If Tzid and DT_WhenSwitch define a unique row I recommend clustering the dbo.DSTLookup table by those two columns. You can make those columns the primary key if you want or you can just make them be the clustered index.
CREATE TABLE dbo.DSTLookup
(
     [Id] int,
     [Tzid] int,
     [DT_WhenSwitch] datetime,
     [DSTOffSetSeconds] int,
     [GMTOffSetSeconds] int
);

CREATE CLUSTERED INDEX CI_DSTLookup ON dbo.DSTLookup ([Tzid], [DT_WhenSwitch]); -- new

INSERT INTO dbo.DSTLookup
VALUES (29, 2, N'2014-03-30T01:00:00', 3600, 0),
       (30, 2, N'2014-10-26T02:00:00', 0, 0),
       (31, 2, N'2015-03-29T01:00:00', 3600, 0),
       (32, 2, N'2015-10-25T02:00:00', 0, 0),
       (33, 2, N'2016-03-27T01:00:00', 3600, 0),
       (34, 2, N'2016-10-30T02:00:00', 0, 0),
       (35, 2, N'2017-03-26T01:00:00', 3600, 0),
       (36, 2, N'2017-10-29T02:00:00', 0, 0),
       (37, 2, N'2018-03-25T01:00:00', 3600, 0),
       (38, 2, N'2018-10-28T02:00:00', 0, 0);

The reason for doing that is it will allow very fast individual row lookups. For both queries against the table you want to filter on [Tzid] and to find the first [DT_WhenSwitch] value in descending order. With the right clustered index getting that row can be a single clustered index seek.
To get that the plan I want I'm going to simplify the TVF a bit with the APPLY and TOP operators. I also want to make it very obvious to the optimizer that I get back just one row every time. Here's one implementation:
CREATE FUNCTION dbo.FN_GetLocalTime_FromUTC_BasedOnTZId
     (@StartDateTime DATETIME, @EndDateTime DATETIME, @Tzid INT)
/*=========================================================================
*   2017-03-27
*   Returns local time from UTC time based on timeZoneId
*
==========================================================================*/
RETURNS TABLE
WITH SCHEMABINDING
AS
RETURN
(
        SELECT
               DATEADD(SECOND, (COALESCE(S.s_DST_OffSet, 0) + COALESCE(S.s_GMT_OffSet, 0)), @StartDateTime) 'LocalStartDateTime'
             , DATEADD(SECOND, (COALESCE(E.e_DST_OffSet, 0) + COALESCE(E.e_GMT_OffSet, 0)), @EndDateTime)   'LocalEndDateTime'
        FROM (SELECT 1 t) t
        OUTER APPLY (
            SELECT TOP 1
                D.DSTOffSetSeconds 's_DST_OffSet',
                D.GMTOffSetSeconds 's_GMT_OffSet'
            FROM dbo.DSTLookup D
            WHERE D.DT_WhenSwitch <= @StartDateTime AND D.Tzid = @Tzid
            ORDER BY D.DT_WhenSwitch DESC
        ) s
        OUTER APPLY (
            SELECT TOP 1
                 D.DSTOffSetSeconds 'e_DST_OffSet',
                 D.GMTOffSetSeconds 'e_GMT_OffSet'
             FROM dbo.DSTLookup D
             WHERE D.DT_WhenSwitch <= @EndDateTime AND D.Tzid = @Tzid
             ORDER BY D.DT_WhenSwitch DESC
        ) e
);

Here is the query plan for your example query in the question:

As expected we only do two seeks against the clustered index:

(1 row(s) affected)
Table 'DSTLookup'. Scan count 2, logical reads 4, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
SQL Server Execution Times:
CPU time = 0 ms,  elapsed time = 1 ms.

I wasn't able to test against SQL Server 2008, but I think that syntax will work on that platform. db fiddle for SQL Server 2014.


Postgresql installation, avoid creating “postgres” user I have a constraint, that I can use only one user on a SUSE Linux Enterprise Server 11.

Now, I need to install PostgreSQL 9.4. Is it possible to somehow avoid the automatic creation of "postgres" linux user by PostgreSQL's installation wizard, and use current user as a database admin?

Additional explanation about the weird  constraints:

We have to host our software on the server of the IT company, which is subsidiary of the big bank. They have quite strict rules, due to storing and processing of the sensitive user data. So, one of the rules, is that we can have only one user there and everything should be installed under this user (with sudo rights). Initially, the server has only the most basic software (and PostgreSQL is not included there).

The full answer is in this post: https://serverfault.com/questions/929677/postgresql-installation-avoid-creating-postgres-user

The only way is to build PostgreSQL from source.


How to make this query simpler SELECT IH.InvoiceNo,
    C.Name AS NameofCustomer,
    IP.Amount AS CashAmount,
    0 CreditNote,
    0 ChequeAmount,
    0 Credit,
    IP.Amount AS SalesValue
  FROM InvoiceHeader IH
LEFT JOIN Customer C ON IH.CustomerId = C.Id
LEFT JOIN InvoiceDetail ID ON ID.InvoiceNo = IH.InvoiceNo
LEFT JOIN DistributionCenter DC ON DC.Id = ID.DistributionCenterId
LEFT JOIN InvoicePayment IP ON IP.InvoiceNo = IH.InvoiceNo
WHERE DC.Id = 1 AND IP.PaymentTypeId=1 AND IP.InvoiceDate
BETWEEN'2018/05/01' AND '2018/06/19' AND IH.Deleted = 0
  UNION
SELECT  IH.InvoiceNo,
     C.Name AS NameofCustomer,
     0 CashAmount,
     IP.Amount AS CreditNote,
     0 ChequeAmount,
     0 Credit,
     IP.Amount AS SalesValue
FROM InvoiceHeader IH
LEFT JOIN Customer C ON IH.CustomerId = C.Id
LEFT JOIN InvoiceDetail ID ON ID.InvoiceNo = IH.InvoiceNo
LEFT JOIN DistributionCenter DC ON DC.Id = ID.DistributionCenterId
LEFT JOIN InvoicePayment IP ON IP.InvoiceNo = IH.InvoiceNo
WHERE DC.Id = 1 AND IP.PaymentTypeId=2 AND IP.InvoiceDate BETWEE'2018/05/01'
AND '2018/06/19' AND IH.Deleted = 0
 UNION
    SELECT  IH.InvoiceNo,
     C.Name AS NameofCustomer,
     0 CashAmount,
     0 CreditNote,
     IP.Amount AS ChequeAmount,
     0 Credit,
     IP.Amount AS SalesValue
   FROM InvoiceHeader IH
   LEFT JOIN Customer C ON IH.CustomerId = C.Id
   LEFT JOIN InvoiceDetail ID ON ID.InvoiceNo = IH.InvoiceNo
   LEFT JOIN DistributionCenter DC ON DC.Id = ID.DistributionCenterId
   LEFT JOIN InvoicePayment IP ON IP.InvoiceNo = IH.InvoiceNo
   WHERE DC.Id = 1 AND IP.PaymentTypeId=3 AND IP.InvoiceDate BETWEEN
  '2018/05/01' AND '2018/06/19' AND IH.Deleted = 0
   UNION
 SELECT  IH.InvoiceNo,
     C.Name AS NameofCustomer,
     0 CashAmount,
     0 CreditNote,
     0 ChequeAmount,
     IP.Amount AS Credit,
     IP.Amount AS SalesValue
 FROM InvoiceHeader IH
 LEFT JOIN Customer C ON IH.CustomerId = C.Id
 LEFT JOIN InvoiceDetail ID ON ID.InvoiceNo = IH.InvoiceNo
 LEFT JOIN DistributionCenter DC ON DC.Id = ID.DistributionCenterId
 LEFT JOIN InvoicePayment IP ON IP.InvoiceNo = IH.InvoiceNo
 WHERE DC.Id = 1 AND IP.PaymentTypeId=4 AND IP.InvoiceDate BETWEEN
 '2018/05/01' AND '2018/06/19' AND IH.Deleted = 0
 ORDER BY IH.InvoiceNo

Amount has values that paid and PaymentTypeId has the payment type. I am trying to retrieve data into 4 colomns with condition of PaymentTypeId = 1,2,3 or 4  this is the query i got. Are there any simple way to this task?

Well, at least it seems like you can get rid of the UNIONs. Use a CASE ... END to get the values for cashamount, creditnote, chequeamount and credit depending on paymenttypeid. Use IN (or multiple OR) to filter for paymenttypeid in the WHERE clause.

SELECT DISTINCT
       ih.invoiceno,
       c.name nameofcustomer,
       CASE ip.pamenttypeid
         WHEN 1
           THEN ip.amount
         ELSE
           0
       END cashamount,
       CASE ip.paymenttypeid
         WHEN 2
           THEN ip.amount
         ELSE
           0
       END creditnote,
       CASE ip.paymenttypeid
         WHEN 3
           THEN ip.amount
         ELSE
           0
       END chequeamount,
       CASE ip.paymenttypeid
         WHEN 4
           THEN ip.amount
         ELSE
           0
       END credit,
       ip.amount salesvalue
       FROM invoiceheader ih
            LEFT JOIN customer c
                      ON c.id = ih.customerid
            LEFT JOIN invoicedetail id
                      ON id.invoiceno = ih.invoiceno
            LEFT JOIN distributioncenter dc
                      ON dc.id = id.distributioncenterid
            LEFT JOIN invoicepayment ip
                      ON ip.invoiceno = ih.invoiceno
       WHERE dc.id = 1
             AND ip.paymenttypeid IN (1, 2, 3, 4)
             AND ip.invoicedate BETWEEN '2018/05/01'
                                        AND '2018/06/19'
             AND ih.deleted = 0;

Note, that UNION eliminates duplicates. So I added DISTINCT to have duplicates eliminated in this version too. Remove it, if this side effect of UNION wasn't actually wanted or if there cannot be any duplicates (I don't have the complete picture of your schema to judge that.).

But, it's only a shorter query text. Depending on the existing indexes, your first approach might execute faster. Check the execution plans, when in doubt.


MDF file full - cannot find the source of its giant size All,

I am completely at a loss here and I'm hoping you guys can help me out on this one. I am experiencing the following situation on an SQL Server 2008 R2 database.

MDF-file:
Allocated space: 87392.69 MB
Available space: 256.13 MB

The LDF file is only a few megabytes, most of which are available. So as you can see my database is pretty full at the moment. I tried looking for the source of this, but here is where things get "weird".

When check the top tables by usage the top table was only 3 GB big, followed by a few of 1 GB. I've summarized all those totals and came to something around +- 20 GB of data. There's a giant gap here... and I'm not sure where it's coming from. I've been googling for the last few hours hoping to get some pointers, I've checked if any BLOB data was causing this but that seems to be not the case as far as I can see.

Am I missing something here? Did anyone experience something like this before? I can't find anything related to this so I'm completely at your mercy.

If you need any specific details I'll add them when requested.

Top 10 tables based on Aaron's query. (masked names for security reasons)

Thanks in advance

Identify the top 10 biggest tables/indexes with this as a starter:

SELECT TOP (10) s.name, o.name, ps.row_count,
  used_bytes = ps.used_page_count * 8192,
  reserved_bytes = ps.reserved_page_count * 8192
FROM sys.dm_db_partition_stats AS ps
INNER JOIN sys.objects AS o
ON ps.[object_id] = o.[object_id]
INNER JOIN sys.schemas AS s
ON o.[schema_id] = s.[schema_id]
WHERE o.is_ms_shipped = 0
ORDER BY reserved_page_count DESC;

SQL Server doesn't just occupy 87 GB for nothing - the space is being used somewhere. Maybe it's in a system table:

SELECT TOP (10) s.name, o.name, ps.row_count,
  used_bytes = ps.used_page_count * 8192,
  reserved_bytes = ps.reserved_page_count * 8192
FROM sys.dm_db_partition_stats AS ps
INNER JOIN sys.all_objects AS o -- change this to all_objects
ON ps.[object_id] = o.[object_id]
INNER JOIN sys.schemas AS s
ON o.[schema_id] = s.[schema_id]
-- WHERE o.is_ms_shipped = 0 -- comment this out
ORDER BY reserved_page_count DESC;

Would also be curious to see what size the backup is, and what happens if you try shrink the data file.


Distribute values equally Each ship docks at a port. If too much ships arrive at a single port the crew gets overwhelmed.

How to return a result set so a port does not get overloaded?
One possible solution and set based result would be:

SELECT shipnr, portnr FROM (
SELECT shipnr, portnr, DENSE_RANK() OVER(PARTITION BY portnr ORDER BY shipnr) AS N FROM ships
) AS a
ORDER BY N, portnr

However, this is not what I am looking for since at the end we get three ships arriving at F.
Below is an example I did by hand of the result set I am looking for:

1   A
6   B
10  C
11  D
13  E
16  F
20  F
2   A
7   B
12  D
14  E
17  F
21  F
3   A
8   B
15  E
18  F
22  F
4   A
9   B
19  F
5   A

Since there are many port F it should return in groups of 2 so that specific port is not overwhelmed.

Ok, here is my revised approach, using NTILE.  It actually gives roughly the same as your proposed output.

declare @ships table
(
shipnr int,
portnr varchar(50)
);

insert into @ships (shipnr, portnr)
values (1, 'A'), (2, 'A'), (3, 'A'), (4, 'A'), (5, 'A'), (6, 'B'), (7, 'B'), (8, 'B'), (9, 'B'), (10, 'C'),
(11,'D'), (12, 'D'), (13, 'E'), (14, 'E'), (15, 'E'), (16, 'F'), (17, 'F'), (18, 'F'), (19, 'F'), (20, 'F') , (21, 'F') , (22, 'F')

DECLARE @PortNumber INT = (SELECT COUNT(DISTINCT portnr) FROM @ships) - 1

;WITH CTE_Ships AS
    (
    SELECT S.shipnr
        , S.portnr
        , NT = NTILE(@PortNumber) OVER (PARTITION BY portnr ORDER BY (portnr))
    FROM @ships AS S
    )

SELECT * FROM CTE_Ships ORDER BY NT, portnr


Joining two tables using one to group another I have a sqlite database with 2 tables.  Table a has a running list of events and the times they happened. Table b has the start and finish of time each event.

How can I find the minimum event from table a for each group defined in table b?  Schema is described in my SQLFiddle.

I'm trying to get a result like:

id1  id2  time event
c1   d1   2    5
c1   d2   1.2  10
c1   d1   5    8.2

SELECT a.id1, a.id2, `time`, event
FROM a
LEFT JOIN b ON a.id1 = b.id1 AND a.id2 = b.id2
            AND a.`time` BETWEEN b.stime AND b.etime
Where not exists ( select null from a A1
                          Where a1.id1=a.id1 and a1.id2=a.id2
                          AND a1.`time` BETWEEN b.stime AND b.etime
                          And a1.`time` < a.`time` )

Cfr. http://sqlfiddle.com/#!5/284fb/4

Another way to obtain these results:

with e as (select rowid R, b.* from b)
select e.*,  a.* from (
 select min( a.`time` ) T, e.R  from a,  e
  where a.`time` between e.stime and e.etime
      and a.id1 = e.id1
      and a.id2 = e.id2
  group by e.R
 ) M, a, e
 where e.R = M.R
   and a.id1 = e.id1
   and a.id2 = e.id2
   and a.`time` = M.T;

With your current schema, that contains a serious flaw, there is no way to obtain your result in a timely fashion.  With some simple modifications, you can get it in 21 seconds.

Modify your tables as follows:

CREATE TABLE a (
 `time`        REAL       NOT NULL,
  idA number,
  id1      CHAR        NOT NULL,
  id2       CHAR        NOT NULL,
  event    REAL);
CREATE TABLE IF NOT EXISTS "b"(
  id1 TEXT,
  id2 TEXT,
  stime REAL,
  etime REAL,
  idB number);
CREATE UNIQUE INDEX pk_b on b(idB);
CREATE INDEX i_b1 on b(id1,id2);
CREATE INDEX i_a1 on a(idA);

I've given b a primary key.  a.idA contains a reference (foreign key) to b.  This update took quite some time.   Now the following query finishes in 21 seconds.

select b.*, a.event, a.`time`
  from b, a
  where a.idA = b.idB
    and not exists (select null from a a1
                     where a1.idA = b.idB
                       and a1.`time` < a.`time`)

--EQP-- 0,0,1,SCAN TABLE a
--EQP-- 0,1,0,SEARCH TABLE b USING INDEX pk_b (idB=?)
--EQP-- 0,0,0,EXECUTE CORRELATED SCALAR SUBQUERY 1
--EQP-- 1,0,0,SEARCH TABLE a AS a1 USING INDEX i_a1 (idA=?)
c2|d2|834.0|1395.0|5|49.407297300706|834.0
c1|d2|1750.0|5332.0|1|49.9163756294503|1750.0
c1|d1|2088.0|5330.0|2|49.6802055002045|2088.0
....
c2|d1|9994707.0|9998630.0|5986|49.4294519703533|9994707.0
c2|d2|9998164.0|10000000.0|5988|48.9248418596976|9998164.0
c1|d1|9998324.0|9999377.0|5990|17.4151947643984|9998324.0
c1|d2|9998791.0|10000000.0|5987|48.9222722695804|9998791.0
c2|d1|9999744.0|10000000.0|5989|49.8495713740755|9999744.0
Run Time: real 21.612 user 21.377030 sys 0.207799
sqlite> select count(*) from a;
11313047
sqlite> select count(*) from b;
5990


Not getting distinct results using case query This is the query I am running. I am getting the correct results except they come in on a different line for each corpstorenum,
so i am getting :

Corpstorenum,BuffetYN, deliverybeeryn, dineinbeeryn,saladbar
store1,n,n,n,y
store1,n,n,y,n
store1,n,y,n,n
store1,n,n,n,n

instead of:

store1 n,y,y,y

This is my code:

select distinct corpstorenum,
case when storenumbers.storenumber is not null and concept ='buffetAY' then 'Y' else 'n' end as BuffetYN ,
    case when storenumbers.storenumber is not null and concept ='Beer' and delivery#>0 then 'Y' else 'n' end as DeliveryBeerYN ,
    case when storenumbers.storenumber is not null and concept ='Beer' and dinein#>0 then 'Y' else 'n' end as DineInBeerYN,
    case when storenumbers.storenumber is not null and menusize='WithMeal' and dinein#>0 then 'Y'
else 'n' end as SaladBar
from storenumbers
    left join prod5219 on storenumbers.storenumber = [Prod5219].store

You have multiple rows in prod5219 for store store1, and each row has a unique set of values for concept and menusize.

Run this to check that out:

select storenumbers.*
    , prod5219.concept
    , prod5219.menusize
from storenumbers
    left join prod5219 on storenumbers.storenumber = [Prod5219].store

Simply adding distinct to the select clause doesn't magically remove "extra" rows from the output.  distinct simply returns rows that are unique, across all columns returned by the query.


How to change DB2 Demo license to a free license (Express Edition) For a PoC (Proof of concept) I downloaded DB2 which comes with a 90 day trial license.

Since we would like continuing to use the DB2 for further demonstrations I would like to import a license file for a free DB2 version (DB2 Express Edition).

How can I do that?

Get the free license file
import it in our system

DB2 Express edition is (actually was, as it's not available anymore) not free; DB2 Express-C is the free edition.

If you license a paid edition of DB2 its license file will be available for download from the usual source, IBM Passport Advantage. DB2 Express-C license is not available separately, it can only be had by downloading and installing the Express-C package, at which point you might as well just use it for your POC.

To install a license file you'd use the db2licm (license manager) utility by running this as the target instance owner:

db2licm -a <path to license file>

You may want to double-check the demo license agreement though -- it probably says that you must remove the demo installation after its license expiration, which would mean that installing the Express-C license into it, though it might be technically possible, would violate the agreement.

Update: According to the documentation you can continue using the installed product after the trial license expiration by registering a valid license file; it looks like adding the Express-C license should be OK. Note though that if you have used in the trial any of the features unavailable in the Express-C edition, your installation may behave erratically or even fail to start.


SQLCMD Exporting 16.5 Million Rows I am exporting a table to a TSV file using sqlcmd, and I am running into issues. The table has 16+ million rows and about 55 columns.

The problem is that it does not export the full table, but seems to stop randomly at various points (i am guessing a timeout?)  Each time a different number of rows are exported and each time the file is of a slightly different size (indicating that I am not hitting any row or size limit).

I am not using any timeout switch (meaning the default of "as long as it takes" is used).

Here is my command (with most columns removed for simplification and illustration purposes):

sqlcmd -U myUsername -P myPassword -S SERVERNAME -d "DBNAME" -Q "SELECT ROW_KEY, ISNULL(CARRIER_ID, ''),ISNULL(CONVERT(VARCHAR(19),ORDER_DATETIME,120),''),ISNULL(HEIGHT, ''),ISNULL(RESIDENTIAL_CHARGE, '') FROM MYTABLE" -o "OUTPUT_FILE.txt" -h-1 -s"|" -W

I wonder if it could have something to do with timeouts or the use of ISNULL() on all of the columns (although when i run the query in sql server management studio I get the correct number of rows returned e.g. 16 million + )?

Again, I get about 4-8 million rows each time, but never the full amount. I am in a sql server 2k5 db, and running sqlcmd from a remote machine with sql server 2k8.

You'll probably want to do this with bcp, which is intended for this kind of thing. It would be something like this:

bcp "SELECT ROW_KEY, ISNULL(CARRIER_ID, ''),ISNULL(CONVERT(VARCHAR(19),ORDER_DATETIME,120),''),ISNULL(HEIGHT, ''),ISNULL(RESIDENTIAL_CHARGE, '') FROM MYTABLE" queryout OUTPUT_FILE.txt -c -S <servername> -U <login_name> -P <password>

Replace -c with -w if you're dealing with nvarchar/nchar, and want Unicode output.


Issues with enabling pg_stat_statements I am having issues quering pg_stat_statements and puzzled what else I missed?
See below what I have done. I still have issues when I query pg_stat_statements! I got error saying the libraries is not loaded.

#show config_file;

------------------------------------------------------------
 /var/opt/rh/rh-postgresql96/lib/pgsql/data/postgresql.conf

I have edit the config file and add the following

shared_preload_libraries = 'pg_stat_statements'         # (change requires restart)
pg_stat_statements.max = 10000
pg_stat_statements.track = all

3.# \dx
                                     List of installed extensions
        Name        | Version |   Schema   |                        Description
--------------------+---------+------------+-----------------------------------------------------------
 pg_stat_statements | 1.4     | public     | track execution statistics of all SQL statements executed
 plpgsql            | 1.0     | pg_catalog | PL/pgSQL procedural language
(2 rows)

#select count(*) from pg_stat_activity;

 count
-------
    43
(1 row)

#select * from pg_stat_statements;

ERROR:  pg_stat_statements must be loaded via shared_preload_libraries

Thanks,
Lucia

In case of such issues it may be useful to check:

show shared_preload_libraries ;
select name,setting,source,sourcefile,sourceline,pending_restart from pg_settings where name = 'shared_preload_libraries';
Maybe something overrides your shared_preload_libraries somewhere or maybe server didn't actually restart, then 2. will show that as setting field will show something else than pg_stat_statements or pending_restart will show t if that change still waits for restart.

(i don't have comment rights here, so i post as answer hoping it will help)