How to limit the number of rows in select statement for a condition in sql server 2012 Customer|Product_Code|List_Price|Unit_Price|Unit_Unit_Price|Unit_List_Price|
--------+------------+----------+----------+---------------+---------------+
3BEACHES|CA050608    |9.46      |9.46      |EACH           |EACH
3BEACHES|CA050608    |9.46      |9.46      |INNER          |EACH
3BEACHES|CA050608    |9.46      |9.46      |SHIPPER        |EACH

In my select statement I am joining 4 Views - Customer,Product,Price Book and List Price View and I want only 1 row for a combination of Customer and Product_Code, I am getting 3 rows as attached above because there are different values in Unit_Unit_Price field for some Products, it can be any one of  "EACH" or "INNER" or "SHIPPER" but just 1.

I can't put a Where clause on any 1 Value(Each,Inner or Shipper) because for some Customer-Product_Code line it's just showing 1 row(Shipper or Each or Inner), so if I am using where clause on any 1 value of Unit_Unit_Price field then it's deducting the records which is not correct.

What I want: there are no repeating rows for Customer-Product_Code combination, I just need unique rows.

Code I am using:

SELECT TOP (100) PERCENT skh.ARCUS.IDCUST,
                         skh.ICITEM.ITEMNO AS product_code,
                         skh.ICITEM.CNTLACCT,
                         skh.v_list_price_each.price AS list_price,
                         skh.v_price_book.price,
                         skh.v_list_price_each.unit AS Expr2,
                         skh.v_price_book.price_unit
FROM skh.ARCUS
INNER JOIN skh.v_price_book ON skh.ARCUS.PRICLIST = skh.v_price_book.price_list
INNER JOIN skh.ICITEM ON skh.v_price_book.item_short = skh.ICITEM.ITEMNO
AND skh.v_price_book.company = skh.ICITEM.AUDTORG
INNER JOIN skh.v_list_price_each ON skh.v_price_book.item_short = skh.v_list_price_each.item_short
WHERE (skh.ICITEM.INACTIVE = 0)
  AND (skh.ARCUS.SWACTV = 1)
ORDER BY skh.ARCUS.IDCUST

It is difficult to say without your current query and sample data, but looks like this is something you want

select t1.*
from
(
select t.*,
    row_number() over (partition by Customer,Product_Code order by Unit_Unit_Price) as rnk
from
 (your current query) t
) t1
where t1.rnk=1

Note: If your current query doesn't have any group by then you can plug in row_number() over (partition by Customer,Product_Code order by Unit_Unit_Price) as rnk in your current query select clause and filter it in outer query as the example above.


How do I create a global MongoDB Admin account to see all Dbs? I'm working on MongoDB, sysadmin helped setup the install.

I'm confused about MMS the Cloud Manager, I created an account for @admin database

I authenticate successfully but do not see "test" database
LinuxBox> ./mongo myipaddress:27000/admin  -u DBASupport  -p
** if I don't use /admin I default to "test" db

  show dbs;
  admin  0.078GB
  local  0.078GB
  show collections;
  system.indexes
  system.users
  system.version

I am assuming I'm using a GLOBAL DB Admin account I created.. but it looks like it's for the "Admin" database.

Alternatively when I created another account "Test" as the database login "MyTestLogin"  with Admin role...  the Database I default connect to is "Test" database?

I thought since I'm connecting to same port 27000 with same MongoDB instance/service...  I would see admin, local, and test databases??

I know i'm missing something here, but can some one explain

thanks

You created the user correctly, but you missed a parameter when connecting to mongod.

By default, the authentication process assumes that the user is stored in the database you connect to, which in your case simply isn't true.

You need to specify the autheticationDatabase option:

mongo yourMongodIpAddress:27000/targetDb \
--authenticationDatabase admin -u DBASupport -p yourPw


Issue with connecting to the mysql database We have an outstanding issue with VCC in chicago, there is an issue connecting to the mysql database
    If an agent does a  refresh of the monitor they will be logged out.
    This is the error we get when the vcc web server starts.
    [Error: MySQL server is requesting the old and insecure pre-4.1 auth mechanism.Upgrade the user password or use the {insecureAuth: true} option.] code: 'HANDSHAKE_INSECURE_AUTH', fatal: true }
    I have done below step,but this issue still exsit.Please check.
    old_passwords set to off
    ALTER TABLE agent_login_session CHANGE operatorId operatorId VARCHAR( 128 ) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL – done

·         info: socket.io started
###### Session SB Server connected fail! ######
{ [Error: MySQL server is requesting the old and insecure pre-4.1 auth mechanism.Upgrade the user password or use the {insecureAuth: true} option.] code: 'HANDSHAKE_INSECURE_AUTH', fatal: true }

1. update mysql to use long password()
# Default to using old password format for compatibility with mysql 3.x
# clients (those using the mysqlclient10 compatibility package).
old_passwords=0

2. ALTER TABLE `agent_login_session` CHANGE `operatorId` `operatorId` VARCHAR( 128 ) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL

Please help me here.

Based on your old_passwords setting, I would ask you to look at three things

What password functions return
What passwords are stored in mysql.user
What authentication style is the client launching

PASSWORD FUNCTIONS

Please run the following:

show variables like 'old_passwords';
select password('rolando'),old_password('rolando');

Depending on where old_passwords is enabled or not, you should get either the following:

MySQL 5.0 with old_passwords off

mysql> show variables like 'old_passwords';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| old_passwords | OFF   |
+---------------+-------+
1 row in set (0.00 sec)

mysql> select password('rolando'),old_password('rolando');
+-------------------------------------------+-------------------------+
| password('rolando')                       | old_password('rolando') |
+-------------------------------------------+-------------------------+
| *C1868CE781FCB521A03168DE044BF3F64E9E485A | 05e97e95241a4409        |
+-------------------------------------------+-------------------------+
1 row in set (0.00 sec)

mysql>

MySQL 5.1 with old_passwords on

mysql> show variables like 'old_passwords';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| old_passwords | ON    |
+---------------+-------+
1 row in set (0.00 sec)

mysql> select password('rolando'),old_password('rolando');
+---------------------+-------------------------+
| password('rolando') | old_password('rolando') |
+---------------------+-------------------------+
| 05e97e95241a4409    | 05e97e95241a4409        |
+---------------------+-------------------------+
1 row in set (0.00 sec)

mysql>

MySQL 5.5 with old_passwords off

mysql> show variables like 'old_passwords';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| old_passwords | OFF   |
+---------------+-------+
1 row in set (0.00 sec)

mysql> select password('rolando'),old_password('rolando');
+-------------------------------------------+-------------------------+
| password('rolando')                       | old_password('rolando') |
+-------------------------------------------+-------------------------+
| *C1868CE781FCB521A03168DE044BF3F64E9E485A | 05e97e95241a4409        |
+-------------------------------------------+-------------------------+
1 row in set (0.00 sec)

mysql>

MySQL 5.5 with old_passwords on

mysql> show variables like 'old_passwords';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| old_passwords | ON    |
+---------------+-------+
1 row in set (0.00 sec)

mysql> select password('rolando'),old_password('rolando');
+---------------------+-------------------------+
| password('rolando') | old_password('rolando') |
+---------------------+-------------------------+
| 05e97e95241a4409    | 05e97e95241a4409        |
+---------------------+-------------------------+
1 row in set (0.00 sec)

mysql>

PASSWORDS STORED

Please run this query:

SELECT COUNT(1) password_length_count,password_length FROM
(SELECT LENGTH(password) password_length FROM mysql.user) A
GROUP BY password_length;

If you get any password_length of 16, you have to enable old_passwords for the old style authentication to work.

PASSWORDS FROM CLIENT

In some cases, using mysql libraries that came from old yum installs of MySQL, PHP, Perl or Apache may have pre-MySQL4.1 user authentication code. Please upgrade such libraries.


Is it possible for a properly set up MySQL to return invalid UTF-8? I am wondering: assuming everything is set up properly, meaning a column with charset utf8mb4 and a client connecting to the database setting the connection encoding to utf8mb4, is there any way in which said client could SELECT data which contains invalid UTF-8 byte sequences? Is there any way to insert data into a utf8mb4 column which is not in fact valid UTF-8?

Put another way: is it reasonable to assume that SELECTing any non-binary column over a client connection set to utf8mb4 should always return valid UTF-8 byte sequences?

(Let's assume plain SELECT * statements without any CAST shenanigans or such.)

I don't think so.  But I have come close:

mysql> CREATE TABLE invalid (c VARBINARY(22));
Query OK, 0 rows affected (0.02 sec)

mysql> INSERT INTO invalid (c) VALUES (UNHEX('65c3c3c366'));
Query OK, 1 row affected (0.01 sec)

mysql> SELECT HEX(c) FROM invalid;
+------------+
| HEX(c)     |
+------------+
| 65C3C3C366 |
+------------+
1 row in set (0.00 sec)

mysql> ALTER TABLE invalid MODIFY COLUMN c VARCHAR(22) CHARACTER SET utf8mb4;
Query OK, 1 row affected, 1 warning (0.04 sec)
Records: 1  Duplicates: 0  Warnings: 1

mysql> SHOW WARNINGS;
+---------+------+-----------------------------------------------------------------+
| Level   | Code | Message                                                         |
+---------+------+-----------------------------------------------------------------+
| Warning | 1366 | Incorrect string value: '\xC3\xC3\xC3f' for column 'c' at row 1 |
+---------+------+-----------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> SELECT HEX(c) FROM invalid;
+--------+
| HEX(c) |
+--------+
| 65     |
+--------+
1 row in set (0.00 sec)

By starting with BINARY, then converting to CHAR, I can get invalid data into the column.  However, the ALTER gives a warning when I try to convert it.

Note in the final SELECT, the data is truncated.  The truncation happened in the ALTER, not the SELECT, where your Question wanted it to happen.  This can be verified by converting back to BINARY:

mysql> ALTER TABLE invalid MODIFY COLUMN c VARBINARY(22);
Query OK, 1 row affected (0.03 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> SELECT HEX(c) FROM invalid;
+--------+
| HEX(c) |
+--------+
| 65     |
+--------+
1 row in set (0.00 sec)

If you have seen the problem happen, please provide details; perhaps I can dissect the problem.


Get only future records I have the following query:

SELECT master.master_id, master.showname,
MIN(CONCAT(shows.show_date, ' ', shows.show_time)) AS first_show_date,
MAX(CONCAT(shows.show_date, ' ', shows.show_time)) AS last_show_date, master.showinfo
       FROM master LEFT OUTER JOIN
       shows ON master.master_id = shows.master_id
LEFT OUTER JOIN
       realtimeticketing rtt ON master.realtimeticketing_id = rtt.realtimeticketing_id
LEFT OUTER JOIN
       event_types ON master.event_type = event_types.event_types_id
       WHERE   (master.domainlist LIKE '%INTERNET%')
       GROUP BY shows.master_id
       ORDER BY first_show_date, master.showname LIMIT 100;

This query returns the following output:

The requirement is that master_id 281 should be displayed as the first record since show dates lie within current date. And after 6th April 2014, the id 282 should start showing up.

I have tried various WHERE clauses but if I use WHERE shows.show_date >= Now(), it starts showing show dates from current date. I however require the first_show_date i.e. 01-03-2014 for master_id 281

Looks like you want your current date to be before or on last show date. If that is the case try following HAVING clause.

HAVING MAX(CAST(CONCAT(shows.show_date, ' ', shows.show_time) AS DATETIME) >= Now()


NC Index not being used I have a table (9mm records) with a field called filename nvarchar(max)(I didn't decide on this datatype and can't change it).

I'm wanting to count how many records meet the following criteria: Where filename like 'ATT000%'. The results are only 8k records. I thought about adding a filtered index, but of course you can't do that with LIKE. So instead I went with

WHERE FileName >= 'ATT000'
AND filename < 'ATT001'.

I receive the same record count. However, when I add that filtered index SQL refuses to use that index and still goes back to use the clustered index when I use that same criteria (copy and paste) from the index filter.

Its obviously WAY smaller and it works fine if I force the index hint. But I can't force it since this is a vendor application.

Any idea why I can't get SQL to use that index?

Try:

WHERE FileName >= N'ATT000'
AND filename < N'ATT001'

And make sure you have included columns to avoid lookups.


Is there a way to set variable in UPDATE statement in PostgreSQL (plpgsql)? Something like this:

DECLARE
c INTEGER;
UPDATE table SET col = (c := col + 1) WHERE id = 1;

This is wrong:

ERROR:  syntax error at or near ":="

how to do it right?

Assuming the update only affects one row:

DECLARE
c INTEGER;
UPDATE table SET col = col + 1 WHERE id = 1
  RETURNING col INTO c;

Reference.


Multi-Tenant shared Users Tables: Need an advice Am writing multiple Multi-Tenant applications with separate database per each application (I've decided to go with shared database different schema approach). Each application have Staffs Table for tenants' staffs and Customers for, well customers.

The problem am having is all my applications share the two tables and so there are going to be duplicates if each database is going to be completely independent.

I have thought of having a common database with Staffs and Customers table and have a different app (like Oauth2 server) handling the registration and login. And then have stub table Users in each multi-tenant apps with columns id and role(customer/staff) forming Primary key and gets their data from remote Staffs and Customers respectively.

Am not sure this is a good approach so and if there are better ways to handle that.

If am not clear let me know in comments.

So this is the approach I came up with. Store All user information and details to the Oauth2 Server/Login API and use it to login. Then in each of the service tables just store user ID, role picked up from login into Oauth2 server/Login API and save them into "users" table of your a specific service. That way you can preserve service data integrity while preventing duplicates.

As to Oauth2/Login service, you can use Party-Roles model (also known as Super-Type/Sub-Type). This video well illustrates it but in case you cannot watch it, just make People table and then Customers and Staffs table relating with People table with 1-to-1 relationship with a type.

Thanks to @Sahap Asci for pointing that out in the comment.


Trigger syntax on phpmyadmin I can't make this work. I want to create a trigger that deletes the child foreign keys before I delete the parent primary key. This is the code that I am using on phpmyadmin:

delimiter //
CREATE TRIGGER removeforeign
before delete
on products
FOR EACH ROW
BEGIN
delete from sales_details
where product_code=21;
END//
delimiter ;

Right now, I can't make trigger works at all. Not for insert, delete, update. In this code, it will delete where product_code is 21 because it's only an attempt to get the syntax right, I am still to figure it out how to compare with what is being deleted. Please, help me with the syntax. I can't see why it isn't working.

It may be more efficient to simply use a cascading delete via a foreign key. When a row with the same product_code is deleted from products, it will be deleted in sales_detail as well.

ALTER TABLE sales_details
ADD FOREIGN KEY fk_product_code(product_code)
REFERENCES products(product_code)
ON DELETE CASCADE;

But if you'd like to use a trigger to delete rows from the products table that have the same product_code as the row deleted, this should do the trick:

 delimiter //
 CREATE TRIGGER removeforeing
  BEFORE DELETE ON products
  FOR EACH ROW BEGIN
    DELETE FROM sales_details WHERE product_code = OLD.product_code;
 END//
 delimiter ;

Edit

Per documentation,

  Within the trigger body, you can refer to columns in the subject table
  (the table associated with the trigger) by using the aliases OLD and
  NEW. OLD.col_name refers to a column of an existing row before it is
  updated or deleted. NEW.col_name refers to the column of a new row to
  be inserted or an existing row after it is updated.


You will want to use the Lookup Table approach. If absolutely necessary there are tools in most database implementations you can use to turn rows into an array like result for another application to use. But as far as Databases are concerned stick to the row method to maintain a nice clean relational model you can JOIN easily on.


Writing a query to find all employee salaries that are higher than their managers I have two tables with some dummy data. I need to find all employees that make more than their managers. I was led to believe the point of the exercise is to learn how to do subqueries.

I can isolate the managers, their salaries, and the companies they work for but I don't know how to proceed to compare their employee salaries to their managers in SQL. The error that gets kicked back to me is that "Only one expression can be specified in the select list when the subquery is not introduced with EXISTS." Which I understand but if I can't pass at least the manager's name and salary from a subquery then I don't know how to solve the problem.

If someone can point me towards a similar post that was answered or some reading that can provide some insight, I'd be very grateful.

My query getting info on the managers

SELECT DISTINCT w.salary, w.pname, w.cname
FROM WorksFor w
INNER JOIN Manages m
ON w.pname = m.mname

Employee/Company Table

CREATE TABLE WorksFor (
employerID int identity(1,1) primary key,
pname varchar(30),
cname varchar(20),
salary int
);

INSERT INTO WorksFor(pname,cname,salary)
VALUES
('John Smith','BigStore',27500),
('Jane Doe','SmallStore',19000),
('Adam Scott','BigStore',50000),
('Bonnie Noel','SmallMfg',25000),
('Cassie Johnson','BigStore',35000),
('Donald Eckerson','SmallStore',29000),
('Erin Joel','SmallMfg',49000);

CREATE TABLE Manages (
manageID int identity(1,1) primary key,
pname varchar(30),
mname varchar(30)
);

Manager Table

INSERT INTO Manages(pname,mname)
VALUES
('John Smith','Adam Scott'),
('Jane Doe','John Doe'),
('Bonnie Noel','Erin Joel'),
('Cassie Johnson','Adam Scott'),
('Donald Eckerson','John Doe');

By way of an example that addresses all the items @ypercube brought up in his comments on your question, you could redesign your table structure like:

CREATE TABLE Workers
(
    WorkerID INT NOT NULL CONSTRAINT PK_Posts PRIMARY KEY IDENTITY(1,1)
    , ManagedByWorkerID INT NULL CONSTRAINT FK_Workers_WorkerID REFERENCES Workers(WorkerID)
    , Salary NUMERIC(10,2)
);

To add some sample data for demo purposes:

INSERT INTO Workers VALUES (NULL, 1000000);
INSERT INTO Workers VALUES (NULL, 50000);
INSERT INTO Workers VALUES (1, 40000);
INSERT INTO Workers VALUES (1, 1000001);
INSERT INTO Workers VALUES (3, 40002);
INSERT INTO Workers VALUES (2, 29999);
INSERT INTO Workers VALUES (2, 60000);
INSERT INTO Workers VALUES (5, 60000);

And finally, the query to return workers who make more than their boss:

SELECT *
FROM dbo.Workers;

SELECT W.WorkerID
    , W.Salary
    , Bosses.WorkerID AS BossID
    , Bosses.Salary AS BossSalary
FROM dbo.Workers W
    INNER JOIN dbo.Workers Bosses ON W.ManagedByWorkerID = Bosses.WorkerID
        AND Bosses.Salary < W.Salary;

The results:


What are reasonable privileges to grant typical users? I find the list of privileges provided by MySQL to be a bit overwhelming. I'm not sure who should have what privileges. In my mind there are three typical users for my situation:

root
developer
application

root is self explanatory. For developer this user needs to be able to easily access any database, make adjustments to it, etc. For starters I'm setting this user to this privilege set:

SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, FILE, REFERENCES, INDEX, ALTER, SHOW DATABASES, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, EVENT, TRIGGER ON

application has an even more limited set. It should just be limited to manipulating a specific database.

I'm not sure what a reasonable set of privileges is to grant. What are a reasonable set of privileges to grant a developer and an application and why?

A typical user should have:

SELECT, INSERT, DELETE, UPDATE, CREATE TEMPORARY TABLES, EXECUTE

The first four are pretty obvious -- though you may also set up "read only" users with only SELECT.

CREATE TEMPORARY is also handy and typically harmless: temporary tables can help in optimizing queries, breaking them into smaller & faster parts. They are limited to the executing connection, and are automatically dropped when it is closed.

EXECUTE depends on your type of system. Do you have stored routines? Would you like your users to access them? Make sure you also know about the SECURITY=DEFINER/INVOKER definition for stored routines.

At any case, make sure to apply all the above on specific schemas. Avoid using:

GRANT SELECT, INSERT, UPDATE, DELETE ON *.* TO 'some_user'@'some_host';

as the above also grants privileges on the mysql system tables, effectively allowing any user to create new accounts or upgrade their own set of privileges.
Instead, do:

GRANT SELECT, INSERT, UPDATE, DELETE ON some_schema.* TO 'some_user'@'some_host';
GRANT SELECT, INSERT, UPDATE, DELETE ON another_schema.* TO 'some_user'@'some_host';


Mysql slow query log always include "# Time:" I am trying to parse the mysql slow log with logstash so it's important that the format is consistent. I have noticed that if statements execute at the same time, the line # Time: yymmdd is only logged once (see example below). I looked at the setting mysqld_log-long-format however this setting is deprecated and supposedily the default. I have not enabled the log-short-format option.

Is there any setting that can enforce the Time field to either always show, or always not show? I don't actually need the information since there is also a timestamp, I just need the output to be consistent.

# Time: 150419  6:00:43
# User@Host: web[web] @ localhost []
# Query_time: 7.730519  Lock_time: 0.000070 Rows_sent: 167620  Rows_examined: 167620
SET timestamp=1429416043;
SELECT /*!40001 SQL_NO_CACHE */ * FROM `messages`;
# Time: 150419  6:00:45
# User@Host: web[web] @ localhost []
# Query_time: 3.480173  Lock_time: 0.000101 Rows_sent: 0  Rows_examined: 0
SET timestamp=1429416045;
INSERT INTO user (lastupdated, fk_id, user_id) ..
# User@Host: web[web] @ localhost []
# Query_time: 3.388204  Lock_time: 0.000133 Rows_sent: 0  Rows_examined: 0
SET timestamp=1429416045;
INSERT INTO user (lastupdated, fk_id, user_id) ..

The logstash config I'm using looks like this - see logstash multiline codec

input {
  file {
     path => "mysql-slow.log"
     codec => multiline {
        pattern => "^# Time"
        negate => true
        what => previous
     }
  }
}

A lot of things have changed in the slowlog over the versions.  Discover the inconsistencies and code to them!

# Time (and maybe some other things) are specified only when they change.

In 5.7.2 Time will include microseconds.  This is one of many syntax changes that have happened over the years -- be careful.  Percona and MariaDB also have different things that can show in the SlowLog.

Query time used to be just an integer.


What is the best way to purge data from a table with minimum damage to performance? When you purge records from a database table, deleting them, you have impact on the logical and physical structure of the target table, as well as indexes and statistics.

What maintenance should be performed on the table after a massive record deletion?

You can rebuild or defrag, depending on the nature of the data that was deleted, the number of indexes, and how badly they were impacted. If you know the fragmentation before the delete, it would be easy to assess the delta from sys.dm_db_index_physical_stats; if you don't, then you could just apply the same rules you normally would when determining whether to defrag, rebuild, or neither. (You could also take this time to look at sys.dm_db_index_operational_stats and sys.dm_db_index_usage_stats to validate that all of your indexes are necessary and are being used. Sometimes the best thing to do is drop an index.)

You could also break the deletes into chunks (see this article), and perform incremental maintenance in between, so that your maintenance at the end will be minimal. That may be more intrusive than a single operation, but it would be broken into smaller bursts over a longer time.


Best practice for table to table operation I have a table loaded daily (let's call it A_table), with the data from this table I have to load another table (B_table).

A is replaced every day (truncated and loaded anew), B is the final table, which have to stay and it'll be queried.
As far as I know we should have a relatively low number number of row loaded daily in A while B is ever-expanding.
I have to do insert or update (from A to B) based on what I already have on information in B.

I'm pretty new to this, but I think it's not an uncommon operation. Is there a best practice or more simply a common approach to this type of operation ?

This is what the MERGE statement was created for:

merge into table_b
using
(
  select pk_col, col1, col2
  from table_a
) t on (t.pk_col = table_b.pk_col)
when matched then
  update
    set col1 = t.col1,
        col2 = t.col2
when not matched then
  insert (pk_col, col1, col2)
  values (t.pk_col, t.col1, t.col2);


Deploying to SSIS catalog and get permissions error When I attempt to deploy to SSIS 2012 Catalog on new server I get an error.  I have researched it on the web for several hours and all of the information available online does not fix my issue.

Ended up finding an article that mentioned fixing this by running a repair on the Sql Server install. When this was run, the problem was solved, temporarily.   The root cause was a group policy that was overwriting the permission needed... repair fixed the permission until the next group policy update, then it would break again.  Once we identified the group policy and fixed it, the problem went away.


How can I speed up a SELECT statement related with a VIEW in MySQL? I have the query shown bellow:

    SELECT i2016.rid,
           NOW(),
           NOW(),
           1,
           1,
           1,
           0,
           'changeme',
           '',
           '',
           '',
           '',
           i2014.total,
           i2015.total,
           i2016.total,
           '',
           i2016.categoryid,
           i2016.accid
      FROM invreport i2016
 LEFT JOIN invreport i2015
        ON i2016.rid = i2015.rid
 LEFT JOIN invreport i2014
        ON i2016.rid = i2014.rid
     WHERE i2016.period = '2016'
       AND i2015.period = '2015'
       AND i2014.period = '2014'
  GROUP BY i2016.`rid`

It takes over an hour for 1000 rows if not longer. I need help to speed it up as I have over million rows stored in the line table.

It runs from the following VIEW:

 SELECT YEAR(`invoice_lines`.`date_entered`) AS `period`,
        SUM(`invoice_lines`.`quantity`)      AS `total`,
        `invoice_lines`.`pasteldb2`          AS `pasteldb2`,
        `invoice_lines`.`categoryid`         AS `categoryid`,
        `invoice_lines`.`accid`              AS `accid`,
        CONCAT(`invoice_lines`.`pasteldb2`,
               `invoice_lines`.`accid`,
               `invoice_lines`.`categoryid`) AS `rid`
    FROM `invoice_lines`
   WHERE (`invoice_lines`.`id` LIKE 'nem%')
GROUP BY `invoice_lines`.`pasteldb2`,
         YEAR(`invoice_lines`.`date_entered`),
         `invoice_lines`.`accid`,
         `invoice_lines`.`categoryid`

VIEWs are handly.  But they are not necessarily efficient.

In this case, you might be able to make it run 3 times as fast by not calling the VIEW 3 times.  Instead, since the SELECT in the VIEW is already doing the GROUP BY year, take advantage of it.

Essentially the view gives you 3 rows, but you want to turn it into 3 columns.  Before continuing with my answer, please confirm that.  That is, do SELECT * FROM invoice_lines and see if you get all the 'right' info, but laid out 'wrong'.

After that we can talk about "pivoting" the 3 rows into 3 columns.  You can get hints of doing that by searching this forum.

If you need to discuss further, provide SHOW CREATE TABLE invoice_lines,

Or, am I confused?  Do you have one table per year?  Bad.


Moving data between Oracle and PostgreSQL For a future project I need to use the following setup:

Some small (offline) data boxes (collecting data over the time) fill PostgreSQL databases (about 1 TB a year in total).

From time to time I would have the chance to move data out of those boxes (onto a stick or mobile drive) and import that into a huge corporate Oracle database.

So far, this is hopefully not a really bad solution. At the moment I'm looking for things to be prepared for easy use later.

Has anyone ever done things like that?

Experiences, what to do and what to prevent?
Any other compatibility?
Things to do in advance?
How to move the data? Special dump-settings?

Learn and use an ETL tool

You'll save yourself a lot of time and pain if you use ETL tools like Pentaho Kettle or Talend Studio to automate the data transfer and merging. They'll happily connect to the Pg instance and the Oracle instance and do the required copying and merging using quite flexible strategies. They can consume CSV dumps too.

Use CSV dumps

If you need to use dumps, try to dump individual tables using COPY ... FORMAT CSV rather than attempting to wrangle pg_dump into doing Oracle-compatible partial dumps. This will save you a great deal of time when importing them into other DBMses, since pretty much everything understands CSV. Keep the schemas consistent for each instance so you don't need to handle different schemas for different instances.

Use disjoint set or composite keys

Where unique keys are used, make sure you assign disjoint key ranges to different instances (eg the key range is instance_id * 10^7 to (instance_id + 1 * 10^7)-1) or use composite keys of (instance_id, generated_key). If you use disjoint ranges add a CHECK constraint to enforce it; similarly, if using composite keys add a CHECK constraint to force the instance_id part to a constant.

Lowest common denominator data types

Avoid using PostgreSQL arrays, record types, and hstore. They'll all dump as string literals in CSV dumps and you'll have to parse them in order to do something useful with them. For example:

regress=> SELECT ROW( ARRAY[1,2,3], ARRAY['a','b','withquotehere:''inline', 'doublequotehere:"'], ARRAY[ ROW(1,'a'), ROW(2,'b') ] );
                                            row
--------------------------------------------------------------------------------------------
 ("{1,2,3}","{a,b,withquotehere:'inline,""doublequotehere:\\""""}","{""(1,a)"",""(2,b)""}")
(1 row)

You really, really don't want to have to parse that. It's a pretty extreme case, an anonymous record value containing three different arrays, one an array of integers, one an array of strings including quotes, and one an array of anonymous two-field row tuples. But seriously ... use plain relational modelling and CSV export, the convenience of composite types, hstore, and arrays aren't worth the compatibility pain if you know you'll be doing lots of cross-DB work.

It's actually possible to use them in your schema, you'll just need to COPY FROM a set of queries that expands them into appropriate relational structures. Since you're going to have to do that, you might as well use regular relational modelling in the first place and save yourself some pain.

Enums are OK; you can just treat them as strings when loading them. They're just a convenient and performant shortcut for a side table and a FK constraint anyway, allowing you to avoid joins and sorts.

In modern PostgreSQL, bytea is output as hex strings. In older versions, it's output as octal escape sequences. Either way, you can load it into a varchar column in Oracle then convert it if you can't get Oracle to natively understand the representation. I haven't dealt with Oracle binary types, so I can't help much more there.


Data modeling with the goal to get the best performance in Oracle and SQL Server I have a question about how I can model my stock database in order to get the best performance possible.

In SQL Server or in the Oracle, each update executed generates a little lock.

I'd like to know what's the best solution that you could tell me

Solution 1: create a product stock table with quantity column and for each input or output execute a SQL update against this column

Solution 2: create a table for product stock movement where for each input I would execute an insert with a positive quantity and for each output I would execute an insert with a negative quantity.

At the end of the day, I would execute an process for update the quantity of the stock products with the "sum" result of the product stock movement table After that, I would delete all records in the product stock movement table

With solution 1, I would have the advantage that execute an simple select to get the product stock quantity but during the day I would have the disadvantage that have many locks due many quantity updates regarding output sold products

With solution 2, I'd have the disadvantage when, I will need to get the product stock quantity, I'd need to make a query with a join with product stock movement table and make a sum in all inputs and outputs of the consulted product, but in this way, during all day I wouldn't have any locks

What do you think about that two solutions presented?

Is it a good practice to make the modeling described in solution 2?

Thank you so much

I think you should consider your requirements a bit further and see which model fits better long term.

Regarding performance: How fast is fast enough? You need a target or goal to work to. And to do that you need to forecast load and volumes.

Ignoring performance and thinking about data models: personally i like the 2nd option because it can give you an audit trail. You can see which items were sold on which days, and can handle things like returns or stock take adjustments. This makes reporting, accounting and auditing easier.


Oracle Stopping - Can't Login All Of A Sudden - I'm having  a problem with Oracle, it happens randomly around once in three weeks or once a month. The instance that i'm using suddenly hangs, and no logins can be established to it.It won't accept connections and I can't login to it.
I would see no error in the alert logs. I would restart my application but the application still can't connect to it. There were no locks at that time. The thing is, there was no error in the alert logs as well, it would just hang all of a sudden. The only way to recover is to restart it. In my java thread dumps, I would see that the application is hanging at Socket.read part

What are Oracle's problems when I can't login to it at all? I can't initialize a connection that's why I need to resrtart the database. After restarting, I can connect again. There are no error on the alert logs.

What do you think I should look at?

Thanks

Start monitoring memory usage. Does your application terminate old sessions? If not, there is a connection leak that eventually takes away all processes your database instance can use and prevent new connections. There also could be a memory leak, that is why monitoring memory and swap could be smart to do.

Most likely to me seems to be the connection leaking. You can easily monitor this by making counts of v$session or v$process. These counts should not grow unlimited.
Since you use jdbc (thin client I asume) Dead Client Detection could also be useful. This gives the rdbms the ability to clean up unused sessions. Unused sessions are those that do not react upon a ping from the server.


Easy way to replace an YAML array of IDs with strings from a joined table I have a database full of camp data. Each camp has many campers and has many camp_weeks.

In the table of campers, there is a YAML encoded array of IDs for the camper's camp weeks. The project requires a query for display. Here's a sample row from the campers table:

19770,'Daniela',41,'---
- 406
- 407
')

My current query (based on the excellent tip from this blog):

SELECT
  campers.name,
  camps.id,
  camps.title,
  regexp_split_to_array(trim(regexp_replace(campers.camp_weeks, E'[\\n\\r''-]+', ' ', 'g' )), E'\\D+') AS week_array
FROM
  campers
LEFT JOIN camps ON campers.camp_id = camps.id
GROUP BY camps.id, campers.id

returns the campers, with a string/array of camp ids like this:

====================================
|  Daniela | 41 | sports | 406,407 |
====================================

I would like the query to return

============================================
|  Daniela | 41 | sports | cricket, tennis |
============================================

What would be an easy way to do this?

Performance is not a big issue for this query and the simpler the query is the better it will be.

Here's a SQL Fiddle.

Colin 't Hart's comment is 100% correct - your data model fails on the first letter of ACID.  YAML is fine for serialization of objects for application consumption, but since your data layer is a RDBMS, you should stick to the RDBMS paradigms.  Assuming you don't have the permission to migrate to a better structure and probably force a re-code of the application, the good news is that you're halfway there with your original query already:  Instead of going straight to array ( regexp_split_to_array ), split to table ( regexp_split_to_table ) instead, then perform a join on camp_weeks for the names.  You could then group that for the stuffed string listing ( using string_agg if it's available on your version of postgres, like shown below ), or pass the results as-is, letting the application worry about the presentation layer.

SELECT  a.name,
        a.id,
        a.title,
        string_agg( cw.week_name, ', ' )
             AS week_names
FROM (  SELECT  cs.id
                    AS camper_id,
                cs.name,
                c.id,
                c.title,
                regexp_split_to_table( trim(
                    regexp_replace(
                        cs.camp_weeks,
                        E'[\\n\\r''-]+',
                        ' ', 'g' )
                    ), E'\\D+' )
                    AS camp_week_id
        FROM    public.campers cs
        LEFT JOIN public.camps c
            ON  cs.camp_id = c.id ) a
LEFT JOIN public.camp_weeks cw
    ON  CAST( a.camp_week_id AS INTEGER ) = cw.id
GROUP BY a.name, a.id, a.camper_id, a.title
ORDER BY a.id, a.camper_id;


SQL Query not throwing error even if the entered scale value is higher than specified I need to capture numeric values without truncation. But the output value is getting truncated even though I specified the Scale as '2'.

Column 'A' datatype - numeric(5,2)

Case 1:

Entered value: 123.55689335218777665555777778899995544

Output : 123.56

Case 2:

Entered value: 1234.55689335218777665555777778899995544

Output: Arithmetic overflow error converting varchar to data type numeric.

My question is why I am not receiving any error when the scale is higher than '2' but I am getting error when the precision is higher than 3.

I am facing this issue in SSIS Data Conversion task. I need to capture the rows which are truncated in another table. Simplified this question in this way for obvious reasons.

The biggest number you can fit into numeric(5,2) is 999.99. Therefore 123.556... is fine, but 1234.556... is too big.

You can find the rules for truncation, rounding, and errors during implicit conversions in the documentation:

CAST and CONVERT (Transact-SQL)

I don't know SQL-server that well, so I don’t know if you can configure the behaviour. One idea is to load the data into a table with say numeric(7,4). You can then check which values are truncated by a query similar to:

select t1.<key>
from t1 join t2
    on t1.<key> = t2.<key>
where t2.<col> - t1.<col> <> 0


MYSQL fulltext - What's the right way to get the relevance from one column? I want to make one column more valuable than others like it's been done in query bellow.This example  shows that only one fulltext index is needed And this answersuggests that three fulltext indexes are needed to do the same.

Here is the queries from the first link:

ALTER TABLE products ADD FULLTEXT(title, sdescription, ldescription)

SELECT *,
MATCH(`title`) AGAINST ('+iphone +case +4s' IN BOOLEAN MODE) * 10 as rel1,
MATCH(`sdescription`) AGAINST ('+iphone +case +4s' IN BOOLEAN MODE) * 3 as rel2,
MATCH(`ldescription`) AGAINST ('+iphone +case +4s' IN BOOLEAN MODE) as rel3,
FROM products
WHERE MATCH (title, sdescription, ldescription)
  AGAINST ('+iphone +case +4s' IN BOOLEAN MODE)
ORDER BY (rel1)+(rel2)+(rel3) DESC;

I'm confused how many indexes are needed for that, one or three?
I asked this question on stackoverflow but no answer yet.

MyISAM? or InnoDB?

4 FULLTEXT indexes will work in either engine.

You could find out by creating an empty table and running that SELECT against it.  It should give you an error if the set of indexes you tried is wrong.  The ALTER to add/drop indexes.


psql with '-c' option or '-f' option is not terminating properly When I run a command such psql -d database -c 'select * from table' or psql -d database -f sqlcommands psql does not terminate cleanly. Interestingly piping the the contents of the file instead of using the -foption eg, psql -d database < sqlcommands doesn't display this behaviour.

I need to press the q key as required by less or man for the execution to finish and return to the command prompt?

Is that the standard behaviour? Are the some escapes required that will allow it to terminate cleanly?

I need to press the q key as required by less or man for the execution to finish and return to the command prompt?

Sounds like your terminal's pager is taking over the output: you can tell psql not to send its output to the pager when you're using the -c 'select... ' invocation via:

psql --pset pager=off -d database -c 'SELECT * from TABLE'

You may also put the line pset pager OFF in the file ~/.psqlrc, however it seems ~/.psqlrc directives are honored only in interactive mode, not for commands passed in via -c '...'. More great tips for non-interactive psql usage from Peter Eisentraut.


Sort table rows and save the row numbers using UPDATE I have Postgres 9.5 with a table movimientos that has the following data:

| id | concepto | movimiento | numero | orden |
|  1 | AJUSTE 1 |       2542 |      0 |     2 |
|  2 | APERTURA |      12541 |      0 |     1 |
|  3 | AJUSTE 2 |       2642 |      0 |     2 |
|  4 | CIERRE   |      22642 |      0 |     3 |

And I need to number the records based on the orden field and keep these numbers in the numero field, because I need this data to sort and search by numero in reports. Example:

| id | concepto | movimiento | numero | orden |
|  2 | APERTURA |      12541 |      1 |     1 |
|  1 | AJUSTE 1 |       2542 |      2 |     2 |
|  3 | AJUSTE 2 |       2642 |      3 |     2 |
|  4 | CIERRE   |      22642 |      4 |     3 |

I tried to do it using a function with a FOR but is very slow with a million rows.

How to do this using a simple UPDATE?

Join to a subquery that computes numero with the window function row_number():

UPDATE movimientos m
SET    numero = sub.rn
FROM  (SELECT id, row_number() OVER (ORDER BY orden, id) AS rn FROM movimientos) sub
WHERE  m.id = sub.id;

Details for UPDATE syntax in the manual.

If you have concurrent write access you need to lock the table to avoid race conditions.

Note that updating every row in a table is expensive either way. The table typically grows to twice its size and VACUUM or VACUUM FULL may be in order.

Depending on your complete situation it may be more efficient to write a new table to begin with. Related answers with instructions:

Best way to populate a new column in a large table?
Optimizing bulk update performance in PostgreSQL

I am not convinced, though, that you need the column numero in your table at all. Maybe you are looking for a MATERIALIZED VIEW. Recent related answer on SO:

Global row numbers in chunked query


What does it mean when a merge replication generation has no changes? According to MSDN,

  MSmerge_genhistory contains one row for each generation. A generation is a collection of changes that is delivered to a Publisher or Subscriber. Generations are closed each time the Merge Agent runs; subsequent changes in a database are added to one or more open generations.

Reading the above, it seems logical that all generations should have a number of changes. However, when I try to select rows from MSmerge_genhistory, I find a lot of them where the changecount column has a value of 0.

Do these rows indicate a problem? Why do generations exist if no changes are included in them?

A generation with 0 changes is not indication of a problem. It is just indication that the generation contains no changes.

If you look at the statement you quoted from BOL, it reads "Generations are closed each time the Merge Agent runs", so this means that the Merge Agent could run and close the generations with no changes if no changes were performed on the publisher and/or subscribers.


Conversion failed when converting the nvarchar value '0.000000' to data type int I have a code

select CONVERT(NUMERIC,
  CASE ISNULL(FLEXFIELD4,'') WHEN '' THEN 0
  ELSE FLEXFIELD4 END )
FROM DEIT_INDENT_DTL_INBOUND_RXL WHERE IND_INDENT_NO='NEE15SR000003'

which is giving me error

  "Conversion failed when converting the nvarchar value '0.000000' to
  data type int."

i am unable to find the solution.

help me..

Thank you in Advance.

In your code I see a CONVERT(NUMERIC,...) but not a CONVERT(INT,...).  So where is the converting to data type int happening?

SELECT CONVERT(INT, N'0.0000')    -- Conversion failed message
SELECT CONVERT(NUMERIC, N'0.0000')-- Converts to 0

For what it is worth, if you do the following, you also get a '0'.

SELECT CONVERT(INT, CONVERT(NUMERIC, N'0.0000'))

It seems that FLEXFIELD4 is NVARCHAR. If there is some other conversion involved that your code does not show, then that might clarify the issue.


Cloning the database to another server with raw data files I'm trying to clone the database of server 1 to server 2 with raw data files. I'm using Linux 7 and Oracle 11g R2. I have the instance running with same SID in Server 2 but file structure is different. The database is about 85 GB.
I have got the data files, control file, redolog files, spfile and init.ora.  Can someone explain me the way to clone in  this way ?

One can use rman to duplicate a database. Here is the Database Backup and Recovery User's Guide.

But now let's assume that you have a copies of these files that where made when the database was down and where already copied to the places of the target system.

On the target server 1 you have the same type of perating system as on the source server 1. On the target server you have the same Oracle version with the same patch level as on the source server. You can check this by setting the oracle environment and executing the command

$ORACLE_HOME/OPatch/opatch lsinventory

and compare the output of both servers.

If you don't have the right environment you can clone the Oracle software from the source server using Oracle Universal Installer.

So let's assume that you have the correct software installed and set the correct environment of the Oracle instance of the target server.

what happens if you do a startup nomount of the database in sqlplus?

sqlplus first searches for

${ORACLE_HOME}/dbs/pfile${ORACLE_SID}.ora

if such a file does not exist it searches for

 ${ORACLE_HOME}/dbs/init${ORACLE_SID}.ora

and oracle is started with the parameters given in this files. I am not 100% sure about that. Maybe sqlplus starts oracle executable and the oracle executable searches for these files. But that does not matter for our purposes.

So the parameters of these file should have sensible values. This means, the pathes in these files should point to the correct files.

If not, you have to change them. The init.ora file can be modified with a text editor. An spfile must be transformed to a init.ora file by executing

create pfile='/tmp/myfile.txt' from spfile='&absolute_spfile_path';

in sqlplus while the instance is not running. &absolute_spfile_path must be substituted by the correct path.

then you can edit the file /tmp/myfile.tx with a text editor. You can change the pathes, if an instance name is in the file it must be modified, too. Memory parameters can be adapted. Underline parameter written by the instance should be removed, e.g. '_sga_target' and similar parameters.

and then create a modified spfile by issuing

create spfile='&absolute_spfile_path' from pfile='/tmp/myfile.txt';

Before you start you can make a copy of your files so that you can start again when something went wrong.

Make a copy of the init.ora and spfile.ora, the control files, the redo log files and the datafile of th SYS, SYSAUX and undo tablespaces. the datafile of the remaining tablespaces make readonly with the appropriate Linux command(chmod) so they can't be modified by oracle. Tempfiles are not needed from the source database.

Now you can start the instance with

startup mount

check the alert log if everything is ok. If there are errors then repair tthem.

If everything is fine then mount the dtabase

alter database mount;

Now the control file is read. You can rename all files now. In the control file their old name is stored. you can see this if you do a

select name
from v$datafile;

issue an

alter database rename file '&old_name' to '&new_name';

until all datafile are removed.

The same command can be used to rename the log files.

select member
from v$logfile;

shows you the names of the redo log files in the controlfile.

You can put the tempfiles offline to avoid eror messages when openinf the database

select name
from v$tempfile;

shows you then namesof the temp files.

alter database '&tempfileName' offline;

puts the file offline.

Now you can open the database

alte database open;

Now add new temp files with correct path and size to your temp tablespaces.

alter tablespace TEMP add tempfile ...;

and then remove the old ones

alter tablespace TEMP drop tempfile ...;

If you have made the daatafiles read only then shutdown the database, make the files read/write and start the database again.

Now you are done.


Treating (A, B) and (B, A) as same pair and getting max row per pair Following is my table structure:

"id"           = bigint;
"sender_id"    = bigint, FK;
"receiver_id"  = bigint, FK;
"message"      = character varying;
"sent_ts"      = timestamp with time zone;
"delivered_ts" = timestamp with time zone;
"read_ts"      = timestamp with time zone;
"version_no"   = bigint;
"date_created" = timestamp with time zone;
"date_updated" = timestamp with time zone;

For Eg:
If I've following data:

id | sender_id | receiver_id | message | date_created
1  | 1         | 2           | hi      | 2016-05-21
2  | 1         | 2           | hello   | 2016-05-22
3  | 2         | 1           | :)      | 2016-05-23
4  | 1         | 2           | :D      | 2016-05-24
5  | 2         | 1           | hehehe  | 2016-05-25
6  | 1         | 3           | wassup! | 2016-05-26
7  | 3         | 1           | \m/     | 2016-05-27
8  | 3         | 2           | :D:D    | 2016-05-28

I need to find last message record from whichever user (sender, receiver) pair ie considering (1,2) and (2,1) as a single pair.

So, if I pass 2 as sender_id and receiver_id, I need following output:

id | sender_id | receiver_id | message | date_created
5  | 2         | 1           | hehe    | 2016-05-25
8  | 3         | 2           | :D:D    | 2016-05-28

Following is so far what I've tried:

SELECT sender_id, receiver_id, MAX(date_created) date_created
FROM uconvs
WHERE sender_id = 2 OR receiver_id = 2
GROUP BY sender_id, receiver_id
ORDER BY date_created DESC;

Right now I'm getting following output:

sender_id | receiver_id | date_created
1         | 2           | 2016-05-24
2         | 1           | 2016-05-25
3         | 2           | 2016-05-28

I'm unable to consider (1,2) and (2,1) as a single pair and also unable to extract the message as I've used group by.

Let me know if my question is not clear.
I'm using postgresql 9.4+.

What do I need to do to get the above output? I don't know pl/sql much, but if it's the optimal solution that would also do.

Thanks in advance.

First, you can do a lateral cross join to

(VALUES (sender_id), (receiver_id)) AS u (user_id)

That will duplicate each source row but from each duplicate pair you will select only the entry where user_id is not the one specified as the argument:

WHERE
  ...
  AND u.user_id <> 2

Now each row will have a group identifier, which is User 2's counterpart in each row. For your example the row set will look like this:

id | sender_id | receiver_id | message | date_created | user_id
1  | 1         | 2           | hi      | 2016-05-21   | 1
2  | 1         | 2           | hello   | 2016-05-22   | 1
3  | 2         | 1           | :)      | 2016-05-23   | 1
4  | 1         | 2           | :D      | 2016-05-24   | 1
5  | 2         | 1           | hehehe  | 2016-05-25   | 1
8  | 3         | 2           | :D:D    | 2016-05-28   | 3

At this point you could use a MAX(uconv.date_created) OVER (PARTITION BY u.user_id) to get each conversation's last date_created. Once you have obtained it, you can get just the row corresponding to that date.

Here is the full query:

SELECT
  id,
  sender_id,
  receiver_id,
  message,
  date_created
FROM
  (
    SELECT
      *,
      MAX(c.date_created) OVER (PARTITION BY u.user_id) AS last_date
    FROM
      uconv AS c
      CROSS JOIN LATERAL (VALUES (sender_id), (receiver_id)) AS u (user_id)
    WHERE
      2 IN (sender_id, receiver_id)
      AND user_id <> 2
  ) AS derived
WHERE
  date_created = last_date
;

As you can see, this method allows you to include the actual message in the results as well.

Alternatively, you could replace the MAX() OVER () with a ROW_NUMBER() OVER () (and filter on the row number, of course):

SELECT
  id,
  sender_id,
  receiver_id,
  message,
  date_created
FROM
  (
    SELECT
      *,
      ROW_NUMBER() OVER (PARTITION BY u.user_id ORDER BY c.date_created DESC) AS rn
    FROM
      uconv AS c
      CROSS JOIN LATERAL (VALUES (sender_id), (receiver_id)) AS u (user_id)
    WHERE
      2 IN (sender_id, receiver_id)
      AND user_id <> 2
  ) AS derived
WHERE
  rn = 1
;

This is a typical greatest-n-per-group problem. The tricky part was just to determine what to group by.


Which data type should I use to store a 11 digit number Which data type I should use to store an 11 digit number? It must store '0' as the first digit.

For example, if I store 02421402015, it must show 02421402015. I tried using varchar and it shows 2421402015.

I would suggest storing the number as a BIGINT, and simply displaying the number the way you want it.

DECLARE @Num BIGINT;

SET @Num = 2421402015;

SELECT CONVERT(VARCHAR(11), REPLICATE('0', 11 - LEN(@Num)))
    + CONVERT(VARCHAR(11), @Num);

As pointed out by Aaron Bertrand in his comment, it is better performance-wise to not calculate the length of the required string of zeros, and simply truncate the string:

SELECT RIGHT(REPLICATE('0', 11) + CONVERT(VARCHAR(11), @Num), 11);

For your sample number, the concatenation would result in '000000000002421402015', which would then be right-truncated into 11 digits resulting in:

02421402015

In fact, as srutzky pointed out, this can be further optimized to:

SELECT RIGHT('00000000000' + CONVERT(VARCHAR(11), @Num), 11);

If users will pass in values with leading zeros that need to get matched in a WHERE clause,  I would convert the search term to an integer first, then use that in the predicate, so an index can be used.

I've written a blog post1 that details how you can store the number as an integer, while simultaneously providing an elegant method for the number to look like it's being stored in the database with leading zeros.  Check http://www.sqlserver.science/basics/how-do-i-store-a-number-with-leading-zeros/ for the details!  There are several other methods of formatting the leading zeros column, however the performance of each method varies quite drastically; the RIGHT('00000000000' + CONVERT(VARCHAR(11), @Num), 11) method looks like it's the fastest by far.

1 - My blog post was featured in the SQL PASS Community Connector News
2 - Follow up post on performance of FORMATMESSAGE() function in SQL Server 2012+


Restoring database between different version of SQL Server (SoftwareVersionBuild) i have a question about restoring database from newer version of SQL Server to older, when version differs only in SoftwareVersionBuild.
I know that's not possible to restore database from SQL Server 2012 to 2008, or from 2008 R2 to 2008 etc. but how about situation when i have to restore DB from SQL Server 2008 R2 SP2 (10.50.4033) to version 10.50.2500? (2008R2 SP1).
Well, I know that you'll ask why i just won't try to do it, but unfortunately i don't have access to server with 2008R2 SP1 version, and i'll have to deploy my DB on it soon.

I installed SQL Server 2008 R2 on freshly craeted virtual machine, but exact version of freshly installed SQL Server is 10.50.4000. I managed to successfully restore my test DB from version 10.50.4033 to 10.50.4000, so I guess that it'll work to restore it on version 10.50.2500, too, but I would like to just be sure.
I tried to find information about restoring SQL databases when SQL Server version differs only in SoftwareVersionBuild, but i couldn't find answer.

Migrating a database specifically from 2008 R2 SP2 to 2008 R2 SP1 will be fine*.

Migrating up or down service pack levels on the same SQL Server version is generally OK, because the internal database version number usually does not change.

There have been a couple of exceptions to this general rule. The one that might interest you concerns databases where support for 15,000 partitions has been enabled (changing the internal database version). If this scenario might apply to you, download and review the SQL Server Technical Article below:

Support for 15,000 Partitions in SQL Server 2008 SP2 and SQL Server 2008 R2 SP1.

Even so, going directly from a 2008 R2 SP2 instance to SP1 should not present any problems. Always have verified (restored and DBCC-checked) backups just in case.

* Caveat: If the SP2 database had support for 15,000 partitions enabled, it would not be accessible when restored to R2 RTM as an intermediate step. Applying SP1 would then resolve this. Good to know to avoid panic at the RTM stage :)


A way to reference the ID in a multi insert transaction? (postgres) Assuming table "entity.eid" is auto incrementing, I want to be able to reference the autoincrement value assigned later in the same transaction. The way I have been doing this is by doing multiple transactions which I think is not optimal.

START TRANSACTION;
INSERT INTO entity ...;
INSERT INTO t2 (eid, ...) VALUES (?NEW EID REF HERE?, ...), (...), (...);
COMMIT;

There are different ways to do this.

The easiest way is to use the lastval() function which will return the value generated by the "last" sequence nextval.

START TRANSACTION;
INSERT INTO entity ...;
INSERT INTO t2 (eid, ...) VALUES (lastval(), ...), (...), (...);
COMMIT;

If you know the name of the sequence for the entity table you could also use the currval function:

START TRANSACTION;
INSERT INTO entity ...;
INSERT INTO t2 (eid, ...) VALUES (currval('entity_eid_seq'), ...), (...), (...);
COMMIT;

This can be written in  a more general way by using the pg_get_serial_sequence() function, avoiding to hardcode the sequence name:

START TRANSACTION;
INSERT INTO entity ...;
INSERT INTO t2 (eid, ...) VALUES (currval(pg_get_serial_sequence('entity', 'eid')), ...), (...);
COMMIT;

For more details, please see the manual: http://www.postgresql.org/docs/current/static/functions-sequence.html


How to do jUnit testing by PostgreSQL? I found some packages like pgTAP but I would like to get community supported tools.
I am using PostgreSQL 9.4.3 in Debian 8.1.
I have many kinds of tests which I would like to do:

INSERT by purpose wrong thing and demand false
INSERT correctly but demand not doing change to the database

How can you do simple jUnit testing by PostgreSQL?

This is an updated copy of my answer on StackOverflow.

If I have a function public.foo(bar text) returns text...

I create another function like this:

create or replace function test_foo() returns void as $$
begin

  perform assert_equals('stuff', foo('thing'));
  perform assert_null(foo(null));
  ...

end $$ language plpgsql;

I have some assert functions like the following. I purposely used the same names and signatures as JUnit.

CREATE OR REPLACE FUNCTION assert_equals(expected text, actual text) RETURNS void AS $$
begin
        if expected = actual or (expected is null and actual is null) then
            --do nothing
        else
            raise exception 'Assertion Error. Expected <%> but was <%>', expected, actual;
        end if;

end $$ LANGUAGE plpgsql;

I also have a function to run all tests:

CREATE OR REPLACE FUNCTION run_all_tests() RETURNS void AS $$
declare
    skip constant name[] = '{run_all_tests}'; --a list of functions to skip
    test_prefix constant name = 'test_';
    proc pg_catalog.pg_proc%rowtype;
    started timestamptz;
begin

    raise notice 'Time(m)   Name';
    for proc in select * from pg_catalog.pg_proc where proname ilike test_prefix || '%' and not proname = any(skip) order by proname loop
        started = clock_timestamp();
        execute format('select %s();', proc.proname);
        raise notice '% %()', to_char(clock_timestamp() - started, 'MI:SS:MS'), proc.proname;
    end loop;

end $$ LANGUAGE plpgsql;

If you want to not commit data, start a transaction (BEGIN) then roll it back (ROLLBACK).

Looks like you can check code coverage with https://github.com/kputnam/piggly

Also look into:

http://pgtap.org/
http://en.dklab.ru/lib/dklab_pgunit/
http://www.epictest.org/


How do I create a user-defined aggregate function? I need an aggregate function that MySQL doesn't provide.

I would like it to be in MySQL's flavor of SQL (that is, not in C).

How do I do this?  What I'm stuck on is creating an aggregate function -- the docs don't seem to mention how this is done.

Examples of desired usage of a product function:

mysql> select product(col) as a from `table`;
+------+
| a    |
+------+
|  144 |
+------+
1 row in set (0.00 sec)

mysql> select col, product(col) as a from `table` group by col;
+-----+------+
| col | a    |
+-----+------+
|   6 |   36 |
|   4 |    4 |
+-----+------+
2 rows in set (0.01 sec)

I don't know if there is way to define a new aggregate function, not without messing with MySQL source code.

But if your numbers are all positive, you may well derive from the arithmetic identity:

log( product( Ai ) ) = sum( log( Ai ) )

that you can use EXP(SUM(LOG(x))) to calculate PRODUCT(x). Test in SQL-Fiddle:

SELECT EXP(SUM(LOG(a))) AS product
FROM t ;

SELECT col, EXP(SUM(LOG(a))) AS product
FROM t
GROUP BY col ;

When the data can have 0s, it gets a bit more complicated:

SELECT (NOT EXISTS (SELECT 1 FROM t WHERE a = 0))
       * EXP(SUM(LOG(a))) AS p
FROM t
WHERE a > 0 ;

SELECT d.col,
       (NOT EXISTS (SELECT 1 FROM t AS ti WHERE ti.col = d.col AND ti.a = 0))
       * COALESCE(EXP(SUM(LOG(t.a))),1)  AS p
FROM
    ( SELECT DISTINCT col
      FROM t
    ) AS d
  LEFT JOIN
    t  ON  t.col = d.col
       AND t.a > 0
GROUP BY d.col ;

Tested at SQL-Fiddle

For other DBMS, that do not have MySQL's auto-conversion of boolean values to integers, the

(NOT EXISTS (SELECT ...))

should be replaced with:

(CASE WHEN EXISTS (SELECT 1...) THEN 0 ELSE 1 END)

Specifically for Oracle, a few more changes will be needed, without changing the logic of the answer, only because Oracle does not follow strict ANSI standard in some areas. Tested at SQL-Fiddle-2


How to manage a large SQL Server database: transaction log, reduce size, alter table Hello I have some confusion on how to manage a large SQL Server database.

Let's say size is 15GB, with 3GB available, in simple recovery mode.

Server is not managed by me, I cannot change the size, not even temporarily, nor detach/reattach backup/restore.

Let's say I can only manage the data and the structure of the database.

If I want to alter a table, to add a column, or delete only certain rows (before a datetime), or want to NULL a few nvarchar(MAX) columns to save space, how does this impact?

Often I receive the error "no more log space".

Then I shrink the DB, nothing has changed, and I have less space free...

I don't need the transaction log, so what can I do to perform these operations without incurring in error messages regarding the transaction log size?

Thanks!

Points, in no particular order:

You do need the transaction log for a whole bunch of reasons I'm not going to go into. That said, if you're running out of transaction log space while you're in simple recovery mode, that probably means you have long-running transactions. Make your transactions as short as possible (gather all required user input before you start a transaction). (When SQL Server is trying to free log space, it can only free up to the start of the oldest current transaction.)
Making NVARCHAR(MAX) columns nullable will not necessarily save you much space (off the top of my head it'll be 2 bytes per row, per column).
Deleting/archiving old data will help you - with a caveat that it has to pass through the transaction log first.
Shrinking your database will not help you, as a matter of fact it will kill performance (both while it's running and afterward, since DBCC SHRINKDATABASE uses a naive algorithm that almost perfectly fragments your indexes) and won't regain you much if any space.


what's difference between sql_log_bin and log_bin in MySQL？ can anybody tell me what is the difference between them ?

log_bin is a global variable that is set at runtime. It determines if binary logging should take place, and (if set) the basename of the binary logs. It cannot be changed without restarting the database.

http://dev.mysql.com/doc/refman/5.7/en/replication-options-binary-log.html#option_mysqld_log-bin

sql_log_bin is a global OR session variable that can be set after the server is running. It's main use is as a SESSION variable to prevent statements from the current session being written to the binary log. Use as a GLOBAL variable is not recomended.

https://dev.mysql.com/doc/refman/5.7/en/server-system-variables.html#sysvar_sql_log_bin


Mariadb configuration is not loaded from my.cnf My mariadb configuration in my.cnf is not getting loaded for "log-error" and "pid-file". I have checked other configurations params are loaded.

[root@kvm10 ~]# cat /etc/my.cnf
[mysqld]
!includedir /etc/mysqld/conf.d
datadir=/mnt/mgmt/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
tmpdir=/mnt/mgmt/var/lib/mysql_tmp
user=mysql
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
### TRT-3229 #####
sync_binlog=1
innodb_flush_method=O_DIRECT
innodb_support_xa = 1
myisam_repair_threads = 2
myisam_recover_options = FORCE
###################
innodb_file_per_table=1
innodb_log_buffer_size = 8M
table_open_cache=256
max_heap_table_size=256M
### TRT-4685 ###
max_connections=500
################
innodb_log_file_size = 512M

[mysqld_safe]
log-error=/var/log/mariadb/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
[root@kvm10 ~]#

But the configuration value for log-error & pid-file is not picked by MariaDB.

[root@kvm10 ~]# mysql -e "show variables like 'pid_file'"
+---------------+-----------------------------------+
| Variable_name | Value                             |
+---------------+-----------------------------------+
| pid_file      | /mnt/mgmt/var/lib/mysql/kvm10.pid |
+---------------+-----------------------------------+
[root@kvm10 ~]# mysql -e "show variables like 'log_error'"
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| log_error     |       |
+---------------+-------+
[root@kvm10 ~]#

Am I missing somethig or making some mistake. I have checked the files permissions in /var/log/mariadb/mysqld.log & /var/run/mysqld/mysqld.pid.

I am having the same problem in ~/.my.cnf file (MySQL 5.5.5-10.2.6-MariaDB installed via Homebrew).

Apparently it is a recorded bug in both MySQL and MariaDB:

Bug #84172    The pid-file value is ignored in the /etc/my.cnf option file
https://bugs.mysql.com/bug.php?id=84172

pid_file in my.cnf ignored
https://jira.mariadb.org/browse/MDEV-8168


MySQL: Delete all rows older than 30 days, but only if count more than one I need to cleanup my table of over 14.000.000 rows inside. I want to delete all entries, that are older than 30 days, but only if number of grouped items is larger than one. So, at least one item will stay.

Getting items and deleting them by date is not that problem. But it will delete all.

DELETE FROM parsed
WHERE timestamp < UNIX_TIMESTAMP(DATE_SUB(NOW(), INTERVAL 30 DAY))

I thought something like that

SELECT *
FROM parsed
WHERE TIMESTAMP < UNIX_TIMESTAMP(DATE_SUB(NOW(), INTERVAL 8 DAY))
    AND (
        (
            SELECT COUNT(*)
            FROM parsed
            GROUP BY item_id
            ) > 1
        )

But this will not work. Thanks for help!

Any attempt to do this delete in a single query is likely to take hours, maybe days.  If that is OK, then the code by @ypercube is probably good enough.

Otherwise, I would recommend tackling it in stages.

First find the items to delete.  This will be relatively fast and non-invasive.

CREATE TEMPORARY TABLE t
    ( PRIMARY KEY(item_id) )
    SELECT item_id,
           MAX(timestamp) AS keeper
        FROM parsed
        WHERE timestamp < ...
        GROUP BY item_id
        HAVING COUNT(*) > 1;

We will use the PRIMARY KEY for efficiently walking through t.  keeper is the row not to delete.

Now walk through t in chunks, deleting from parsed.  But first, let's look at the DELETE:

DELETE parsed
  FROM parsed
  JOIN t
  WHERE parsed.item_id = t.item_id
    AND parsed.timestamp != t.keeper;

(Please verify that I got the multi-table syntax correct.)

Then put in a loop and add an AND.  In pseudo code:

SELECT @a := MIN(item_id) from t;
loop:
    SELECT @z := item_id FROM t ORDER BY item_id LIMIT 100, 1;
    DELETE ...
      AND t.item_id BETWEEN @a and @z;
    SELECT @a := @z;
go back to loop

(There's still an endcase to deal with -- You need to exit the loop when when the last chunk has less than 100 items, and do the last few items.)  More discussion.


Replicate from master-master setup I have MySQL Master-Master replication on 2 servers.

I want to replicate to another slave server.

When I configure the slave to replicate from master1, only data added on master1 is replicated.   When I added a record on master2, the record is replicated to master1 but not replicated to the slave.

Any idea how to solve this?

m1

server_id = 1
log_slave_updates = OFF

m2

server_id = 23
log_slave_updates = OFF

s3

server_id = 21
log_slave_updates = OFF

The Problem

The server option log_slave_updates needs to be enabled on both m1 and m2

The Solution

Please add the following in /etc/my.cnf on both m1 and m2

[mysqld]
log_slave_updates

then run this on both m1 and m2

service mysql restart

The Reason

Let's use your example above

m1 is Master of m2
m1 is Master of s3
m2 is Master of m1

Here is what happens when you run INSERT INTO mydb.mytable ... on m1

m2 executes INSERT
m2 records INSERT into the current binary log along with server_id=23
m1 retrieves INSERT from m2 via I/O Thread
m1 records INSERT via I/O Thread into lastest relay log
m1 retrieves INSERT from relay log via SQL Thread and examines its server_id
m1 sees that the server_id of the INSERT is 23 which is not the same as m1's server_id (1)
m1 executes INSERT

OK, all well and good. But, why doesn't s3 get to receive anything? Because log_slave_updates is not enabled, m1 does not save the INSERT and the original server_id posting the INSERT into its own binary logs.

If log_slave_updates was enabled on m1, now follow along your example and let's see what should happen:

m2 executes INSERT
m2 records INSERT into the current binary log along with server_id=23
m1 retrieves INSERT from m2 via I/O Thread
m1 records INSERT via I/O Thread into lastest relay log
m1 retrieves INSERT from relay log via SQL Thread and examines its server_id
m1 sees that the server_id of the INSERT is 23 which is not the same as m1's server_id (1)
m1 executes INSERT
m1 records INSERT into the current binary log along with server_id=23
s3 retrieves INSERT from m1 via I/O Thread
s3 records INSERT via I/O Thread into lastest relay log
s3 retrieves INSERT from relay log via SQL Thread and examines its server_id
s3 sees that the server_id of the INSERT is 23 which is not the same as s3's server_id (21)
s3 executes INSERT

This fully explains why s3 never sees anything from m2


How to define User/Permission relationship as M:M? The Resource relates to Action as M:M via Permission join table/separate entity. User can have many permissions. The same permission may be assigned to multiple users.

How to define User-Permission relation as M:M? How would it look like in EF?

Just an idea - should there be a terenary relationship in there?  There seems to be a three way relationship between the resource, permission and the user.


View for backed-up transaction logs Can someone please let me know the view name to see all backed-up transaction logs, their sizes in SQLServer ?

In Oracle, it is v$archived_logs. Similar to this, I want to know the view for sqlserver.

You can view the backup history by using the tables in msdb, backupset and backupmediafamily.  I use this query to see the history of all backups for specific databases.

Note that if you backup to multiple files, each file will appear on it's own line.

SELECT MF.physical_device_name AS BackupFilePath
        , BackupType = CASE WHEN BS.[type] = 'D' THEN 1
                            WHEN BS.[type] = 'L' THEN 2
                            WHEN BS.[type] = 'I' THEN 5
                            ELSE NULL
                            END
        , BackupType_Desc = CASE WHEN BS.[type] = 'D' THEN 'FULL'
                            WHEN BS.[type] = 'L' THEN 'LOG'
                            WHEN BS.[type] = 'I' THEN 'DIFF'
                            ELSE NULL
                            END
        , BS.position AS Position
        , BS.[database_name] AS DatabaseName
        , BS.first_lsn AS FirstLSN
        , BS.last_lsn AS LastLSN
        , BS.checkpoint_lsn AS CheckPointLSN
        , BS.database_backup_lsn AS DatabaseBackupLSN
        , BS.backup_start_date
        , BS.backup_finish_date
        , DATEDIFF(MINUTE, BS.backup_start_date, BS.backup_finish_date) AS BackupDuration_Min
        , ROUND(BS.backup_size/1024.0/1024.0, 3) AS BackupSize_MB
        , ROUND(BS.compressed_backup_size/1024.0/1024.0, 3) AS BackupSizeComprssed_MB
    FROM msdb.dbo.backupset AS BS
        LEFT OUTER JOIN msdb.dbo.backupmediafamily AS MF ON MF.media_set_id = BS.media_set_id;


Login Failed for Domain\ServerName$ when trying to create a new SQL Server Reporting Services Application in SharePoint 2013 Basic setup:
All of the following is installed on Domain\Servername:

SharePoint 2013 Standalone Install (Trial version)
Currently using an instance of SQL Server 2012 SP1 Express for basic
SharePoint databases (upgraded from SQL Server 2008 R2 Evaluation).

Also using an instance of SQL Server 2012 SP1 Evaluation for
Reporting Services for Sharepoint (Express instance doesn't support
Reporting Services for Sharepoint Integration and Express instance
couldn't be upgraded to Evaluation)

Problem:
Ok, so I'm going through the steps to Install Reporting Services Sharepoint Mode for Sharepoint 2013 that can be found at http://msdn.microsoft.com/en-us/library/jj219068.aspx.  When I get to the end of Step 3: Create a Reporting Services Service Application and press OK, I get the following error: Login Failed for user 'Domain\Servername$'.
According to what I've found on the interweb this means that "a process running as NETWORK SERVICE or as LocalSystem has accessed a remote resource, has authenticated itself as the machine account and was denied authorization."
This makes sense since the account that is used for database provisioning for the new Reporting Database that needs to be created behind the scenes as part of Step 3 is the Identity for the Application Pool of SharePoint Central Administration which I currently have set as Network Service.
Anyhoo, the agreed upon solution is to add the login Domain\Servername$ to the SQL Server Instance that's giving me trouble and give it the right permissions ( link for similar problem: http://support.microsoft.com/kb/889646/en-us).
So, I add the login for Domain\Servername$ and give it server roles: dbcreator, public, securityadmin and sysadmin and then just to be on the safe side I give it db_owner permissions for each database in the SQL Server 2012 Evaluation instance I have.
And I still get the same error :S
Just to get some more information I look at the SQL Server Error Logs using Management Studio and I can see that ther error ID is 18456, Severity 14 State 5 and the Reason for the error is  Could not find a login matching the name provided. [CLIENT: ] .
Question
Why am I getting this error if I've already added the login Domain\ServerName$? Any ideas or workarounds?
Thanks!

Add BOTH Domain\Servername$ login and NT AUTHORITY\Network Service login to SQL Server instance giving you above problem and give both logins the right permissions.

If you are working in a Spanish environment or another language try adding NT AUTHORITY\Servicio de Red  or whatever the equivalent is in your language.

For steps on adding logins see links: http://support.microsoft.com/kb/889646/en-us  and http://www.c-sharpcorner.com/UploadFile/specialhost/how-to-fix-login-failed-for-user-nt-authority-iusrs-in-share/ and https://stackoverflow.com/questions/2806438/login-failed-for-user-domain-machinename

If you have a SQL Server Instance with logins that have been working for you when accessed by application pools and the like, you might want to check which logins are not in the troublesome SQL Server instance and that you might need.

Other comments:

Ok, even though the error said: Login Failed for user 'Domain\Servername$' and I added that login to the problematic SQL Server instance per what I'd seen described on the internet, it seems that the login I ALSO needed to add was NT AUTHORITY\Network Service.

I basically decided to add any logins that were in my normal SQL Server instance that I was using for the regular SharePoint databases and that weren't in the SQL Server instance I was using for SharePoint Reporting Services to the latter. Something else that tripped me up when I was looking originally to add the NT AUTHORITY\Network Service login was that I couldn't find it. It turns out I had to add it in Spanish as NT AUTHORITY\Servicio de red, which is wierd because the app pool has its Identity in English. I guess this happens when some systems have English as default language and some others have Spanish


Why did Microsoft kill "English Query" on SQL Server? Given the popularity of the natural language user interface Siri, which was acquired by Apple for $200 million, I've wondered if anyone knows why Microsoft killed "English Query" on SQL Server.

The reason it was dropped was because of lack of use. This is confirmed in the MS response to a Customer asking if it could be re-instated.

  Posted by Microsoft on 1/13/2012 at 1:37 PM

  English Query feature when introduced in SQL Server was not a popular
  feature and we didn't find lot of customers using it. So it was
  eventually removed from the product. We have no plans to invest in
  such technology so I am resolving this as "won't fix". You can look at
  the Semantic Search feature in SQL Server 2012 to see if that meets
  your needs.


Iterative SQL Delete Statement The following is a simple version of a delete I need to perform.

delete from table_name
where order_date < (getdate()-60)
go

This is deleting a large number of blobs and to clean up the table I need to reduce the number of days in small chunks to avoid TEMPDB filling up.

Is there an easy way of getting this to run in passes each time reducing the number of days by 1 starting at say 60 days and ending at 30 days?

Any help or pointers in the right direction would be great.

Depending on the amount of data being deleted, and if you are in the full or bulk-logged recovery model. Then you might want to look into adjusting the TOP N value and/or increasing your t-log backup frequency.

DECLARE @MinDate    DATETIME = DATEADD(DAY,-60, GETDATE())
        ,@Maxdate   DATETIME = DATEADD(DAY,-30, GETDATE())
        ,@RowCount  INT = 1

WHILE   @RowCount > 0

BEGIN

        DELETE  TOP (20000)
                tn
        FROM    table_name tn
        WHERE   order_date >= @MinDate
        AND     order_date <= @Maxdate

        SET @RowCount = @@ROWCOUNT

    RAISERROR('%d rows deleted', 10, 0, @RowCount) WITH NOWAIT

END


A column value depends on the values of two other columns, of which exactly one is nullable In the Person table, either the Citizen_IdentityID or the Foreigner_WorkPermitID will contain NULL marks.

The PersonID_ID holds either the value of the Citizen_IdentityID or Foreigner_WorkPermitID (whichever that does not retain a NULL mark).

In other words, PersonID_ID is dependent on the value of either Citizen_IdentityID or Foreigner_WorkPermitID, and there are other attributes (eg. IDColor) that depends only on PersonnID_ID.

How should I structure the design for normalization?

The above entities are just a simple substitution of the actual use case.

If I'm understanding your description, PersonID_ID is a duplicate of Citizen_IdentityID or Foreigner_WorkPermitID (whichever is not NULL).  If this is true, I'd suggest something similar to what DCook has suggested:

remove Citizen_IdentityID and Foreigner_WorkPermitID (eliminate duplicate data)
add a Person_Type (char(1) not NULL) column; current values could be 'C' or 'F' for Citizen/Foreigner, respectively

Depending on where your data is coming from, this design would allow for the inclusion of future person types (eg, in the US you have citizens, visa holders, green card holders; in some countries there are clear differences in citizen vs resident vs visitor vs non-visitor visa - not to mention different types of visas). Obviously the need for additional person types will depend on what you're trying to model.

Looking at this a bit more ...

Can an individual's type/status change over time (eg, foreigner becomes citizen; citizen relinquishes citizenship and becomes a foreigner)?

The reason I ask is that you'd obviously need to change the value of the Person_Type and PersonID_ID columns ... probably not a big deal ... but based on your model it appears you'll be using PersonID_ID as part of the PK in at least one other table, so that table's PK would also need to be updated ... while certainly doable, this could quickly get messy depending on the number of tables and records that would need their PK modified, plus the hassles of working around RI constraints.  The mess gets a little nastier if you also need to maintain some sort of history/audit-record for a person (eg, having to store/map the changing of PKs ... yuck!).

So now I'm wondering if PersonID_ID should be a separate, truly unique value, independent of the Person_Type? and if so, then you'd probably need to retain the Citizen_IdentityID/Foreigner_WorkPermitID value, but perhaps give it a more generic name.

So, that would change my suggestion to something like:

add a Person_Type (char(1) not NULL) column; current values could be 'C' or 'F' for Citizen/Foreigner, respectively
change PersonID_ID to a be it's own unique value (could be done through something like an identity column or a unique-PK generator); this value would never change, even if Person_Type were to change over time
replace the Citizen_IdentityID/Foreigner_WorkPermitID columns with a single, generic ID that serves as a FK to the Citizen/Foreigner tables, as appropriate); perhaps something like Person_TypeID (int not NULL)?

OK, one more concern/question ...

Can a person ever be associated with more than 1 Person_Type (C/F)?

For example, if a foreigner goes through the naturalization process to become a citizen, how would this be represented in your model?

Would the person now have both a Foreigner_WorkPermitID and a Citizen_IdentityID, thus requiring the storage of both values in the Person table?

Or would you update the Person table to show a change from 'Foreigner' to 'Citizen' (ie, change Person_Type and Person_TypeID)?


PostgreSQL - Create table as select with distinct on specific columns I need a script to create a table . The problem is my select for creating the table contains equal values in some rows. I need to use distinct for these columns. The other columns' values can come from any matching row.

My current result table has data like this:

|   CITY   |   STREET   |   STREET_NUM   |   VAL_X   |   VAL_Y   |
------------------------------------------------------------------
| CityA    | Street abc |   5            | 11.5      |   0.5     |
| CityA    | Street abc |   5            | 15.4      |   1.8     |
| CityA    | Street abc |   5            | 12.4      |   2.8     |
| CityB    | Street xyz |   18           |  5.4      |   1.9     |
| CityB    | Street xyz |   18           |  8.4      |   1.1     |
| CityC    | Street klm |   55           |  9.6      |   0.8     |

But I need data like this:

|   CITY   |   STREET   |   STREET_NUM   |   VAL_X   |   VAL_Y   |
------------------------------------------------------------------
| CityA    | Street abc |   5            | 11.5      |   0.5     |
| CityB    | Street xyz |   18           |  5.4      |   1.9     |
| CityC    | Street klm |   55           |  9.6      |   0.8     |

For columns city, street and street_num I need to apply distinct. val_x and val_y should be used anyone, for example first of that group with same city, street and street_num.

Can you give me advice how to edit this script?

"VAL_X" and "VAL_Y" chosen through some aggregate function

You should consider using GROUP BY for the columns whose values you consider that should be "distinct" (as a group), and, for the rest of columns, choose an appropriate aggregate function (for instance, MIN):

CREATE TABLE my_result AS
SELECT
  city, street, streetnum, min(val_x) AS val_x, min(val_y) AS val_y
FROM
  tableA
WHERE
  true /* your condition goes here */
GROUP BY
  city, street, streetnum

If you need to put together values from several tables, UNION ALL of them before you GROUP BY:

CREATE TABLE my_result AS
SELECT
  city, street, streetnum, min(val_x) AS val_x, min(val_y) AS val_y
FROM
  (
  SELECT city, street, streetnum, val_x, val_y FROM tableA
  UNION ALL
  SELECT city, street, streetnum, val_x, val_y FROM tableB
  UNION ALL
  SELECT city, street, streetnum, val_x, val_y FROM tableC
  ) AS s0
WHERE
  true /* your condition goes here */
GROUP BY
  city, street, streetnum ;

Using always "VAL_X" and "VAL_Y" from same row, using a WINDOW

If you need to make sure your values are always from the same row, the best way is to use a WINDOW in your query: PARTITION BY "CITY", "STREET", "STREET_NUM" and ORDER BY "VAL_X", "VAL_Y", and choose the first row of every partition.

You can do this with two steps:

1) Add the row_num() to every partition:

SELECT
  *,
  (row_number() OVER (PARTITION BY "CITY", "STREET", "STREET_NUM" ORDER BY "VAL_X", "VAL_Y")) AS rn
FROM
  table_a

  |  CITY |     STREET | STREET_NUM | VAL_X | VAL_Y | rn |
  |-------|------------|------------|-------|-------|----|
  | CityA | Street abc |          5 |  11.5 |   0.5 |  1 |
  | CityA | Street abc |          5 |  12.4 |   2.8 |  2 |
  | CityA | Street abc |          5 |  15.4 |   1.8 |  3 |
  | CityB | Street xyz |         18 |   5.4 |   1.9 |  1 |
  | CityB | Street xyz |         18 |   8.4 |   1.1 |  2 |
  | CityC | Street klm |         55 |   9.6 |   0.8 |  1 |

2) At this point, choose only the rows WHERE rn=1 (and ORDER them, if necessary):

SELECT
   "CITY", "STREET", "STREET_NUM", "VAL_X", "VAL_Y"
FROM
  (
  SELECT
    *,
    (row_number() OVER (PARTITION BY "CITY", "STREET", "STREET_NUM" ORDER BY "VAL_X", "VAL_Y")) AS rn
  FROM
    table_a
  ) AS table_a_grouped
WHERE
  rn = 1
ORDER BY
  "CITY", "STREET", "STREET_NUM"

The result is:

|  CITY |     STREET | STREET_NUM | VAL_X | VAL_Y |
|-------|------------|------------|-------|-------|
| CityA | Street abc |          5 |  11.5 |   0.5 |
| CityB | Street xyz |         18 |   5.4 |   1.9 |
| CityC | Street klm |         55 |   9.6 |   0.8 |

You can see the example at SQLFiddle


Use large set of data in multiple databases I have been thinking about this a lot but somehow can't figure out how to deal with it in an efficient matter.

Say you have a large set "X" of data that consists of multiple tables. For the sake of argument, let's say that "X" consists of all entries from an elaborate phone book.

You then create various databases for different applications, and each application takes a subset of "X" to build upon. It has no access to data of "X" outside the subset.

But if one application changes a tuple in its subset, this change should be propagated to the central store of "X" and thus be available to any other application accessing the same tuple.

To stay with the example of the phone book, application 1 might work with a subset of all people who are parents. Application 2 might work with a subset of all people under 30. These two subsets might intersect. So if application 1 changes the phone number of a parent under 30, this changed phone number should be pushed back to "X". And because application 2 uses the same tuple, it should have access to the updated version.

How do you solve this in practice? I'm using PostgreSQL myself but maybe there is a more universal solution to this question.

Use multiple schemas of one database.

You can use multiple databases, too, if you need to. That would allow a more thorough and secure separation, but you need to use functionality for cross-database access like SQL/MED or dblink. So let's keep it simple with separate schemas.

Create a master schema owned by some admin role and no access to other roles. Being logged in as admin:

CREATE SCHEMA x;
REVOKE ALL ON SCHEMA x FROM PUBLIC;

Put all your tables and data there, everything owned by your admin.

Create view(s) owned by the same admin role in each other schema where you need access, only to selected, relevant rows. Basic views are updatable by default in modern Postgres. The manual:

  Access to tables referenced in the view is determined by permissions
  of the view owner. In some cases, this can be used to provide secure
  but restricted access to the underlying tables. However, not all views
  are secure against tampering; see Section 38.5 for details.

This takes care of concurrency automatically.

As for "basic views", this was extended in Postgres 9.4. The release notes:

  E.9.3.4.2. Views

  ...
  Allow views to be automatically updated even if they contain some non-updatable columns (Dean Rasheed)

  Previously the presence of non-updatable output columns such as expressions, literals, and function calls prevented automatic updates.
  Now INSERTs, UPDATEs and DELETEs are supported, provided that they do
  not attempt to assign new values to any of the non-updatable columns.

Read the details at the link in the quote. It should work for your case: Create a "reference table" in each schema owned by the local role and join to the X tables to include only selected rows.

I suggest to additionally use WITH CHECK OPTION to restrict write operations to the scope of each view. The manual:

  The CHECK OPTION may be used to prevent INSERT and UPDATE commands from creating such rows that are not visible through the view.

If you need more sophisticated / secure policies, consider the new row security features introduced with Postgres 9.5.


MS SQL Page split confusion I am a bit confused about page splits in MS SQL and I'm looking for a definitive answer. There seem to be two versions of the story:

1 - Fillfactor only affects how full pages are at the time the index is created/rebuilt. Page splits are always 50/50

2 - Fillfactor also affects how pages are split. So if there is 70% fillfactor and a page overflows, it will split 70/30

Thanks a lot

From Books Online (emphasis mine):

  FILLFACTOR =fillfactor
  Applies to: SQL Server 2008 through SQL Server 2016, SQL Database V12.

  Specifies a percentage that indicates how full the Database Engine should make the leaf level of each index page during index creation or rebuild. fillfactor must be an integer value from 1 to 100. If fillfactor is 100, the Database Engine creates indexes with leaf pages filled to capacity.

  The FILLFACTOR setting applies only when the index is created or rebuilt. The Database Engine does not dynamically keep the specified percentage of empty space in the pages. To view the fill factor setting, use the sys.indexes catalog view.

Fill factor applies to the leaf pages. PAD_INDEX will determine what happens to non-leaf pages. From the same BoL page:

  PAD_INDEX = { ON | OFF }

  ON
   The percentage of free space that is specified by fillfactor is applied to the intermediate-level pages of the index.

  OFF or fillfactor is not specified
   The intermediate-level pages are filled to near capacity

Page splitting can occur at all levels of the BTree, including the root level. Due to fan-out, however, the frequency at which intermediate pages split is (typically) an order of magnitude less than that at which leaves split. Also there are many fewer non-leaf pages than leaf pages. Consequently PAD_INDEX is less concerning than FILLFACTOR. Still worth thinking about, especially on randomly-inserted indexes.

During writes, pages will be filled up to 100%. Then they will split 50/50, or as close as possible to ensure each row is contained entirely on a single page. I believe the split is always 50/50. Subsequent writes may be skewed, however, so one of the new pages fills much quicker than the other.


Arithmetic operation performance in SQL Say I have a table with prices and different percents, and I need to calculate the sum of the percentage.

And I run this query,

SELECT
    i.user_id,
    i.payment_type,
    SUM(id.price),
    SUM(ROUND(id.price / (1 + (id.some_percent / 100)))) AS subtotal,
    SUM(ROUND(id.price - (id.crc_price / (1 + (id.some_percent / 100))))) AS some_percent_sum,
    SUM(ROUND((id.price / (1 + (id.some_percent / 100))) * (id.another_percent / 100))) AS another_percent_sum
FROM
    invoice i
        INNER JOIN
    invoice_details id ON i.invoice_id = id.invoice_id
GROUP BY i.user_id , payment_type

Will this query affect my SQL performance? Assuming all my index are optimized.

Or is it better to return the raw data and do the calculations in the server (whatever language c#, java, etc..)

The answer to this question depends on the server-side language being used.
I ran two simple benchmarks and compared the results using MySQL and PHP.
The database used in the tests contained 500,000 records
Test #1 (Calculation in MySQL):
$start_time = microtime( true );

$sql =  'SELECT
            SUM( ROUND( retail - ( wholesale / ( 1 + ( 8.25 / 100 ) ) ) ) ) AS percent_sum
        FROM
            invoice';

$result  = $mysqli->query( $sql );
$invoice = $result->fetch_assoc();

$end_time = microtime( true );

echo $end_time - $start_time;       // Result: 0.56663717265

Test #2 (Calculation in PHP):
$start_time = microtime( true );

$sql =  'SELECT
            retail,
            wholesale
        FROM
            invoice';

$result = $mysqli->query( $sql );

$percent_sum = 0;

while ( $invoice = $result->fetch_assoc() )
{
    $percent_sum += round( $invoice['retail'] - ( $invoice['wholesale'] / ( 1 + ( 8.25 / 100 ) ) ) );
}

$end_time = microtime( true );

echo $end_time - $start_time;       // Result: 0.76414209361

These results remained typical for all tests performed, so my conclusion is that calculations in MySQL seem to perform faster than PHP for the types of calculations performed in the question given.
Regardless, I would prioritize maintainability over micro-optimizations, so if you are performing calculations on the data extracted from the database in PHP for the majority of the functions, consistency is key.