mySQL query with field replacemnt? I'm need to run a query from mySQL 5.6 which involves looking up date from different tables. I'm new to mySQL and I'm not sure what this operation is called, or else I would have just googled the subject... I'm hoping to get someone to point me in the right direction... Please note, I can't change the structure of the tables, as this is pre-populated from the application I am using.

example:

3 tables in the same local database: network_segments, Buildings, Departments

network_segments description:

network_segment_ID, display_name, starting_ip, ending_ip, building, department
1, 10.0.0.1, 10.0.0.254, 1, 2
2, 10.1.0.1, 10.1.0.254, 2, 3
3, 10.4.0.1, 10.4.0.254, 3, 1

building table description:

building_ID, description
1, ACHQ
2, EHQ
3, AHQ

departments table description:

department_ID, description
1, marketing
2, sales
3, hr

I want to "select * from network_segments" but in stead of the building and department IDs coming back, I'd like the description field for those IDs:

1, 10.0.0.1, 10.0.0.254, ACHQ, sales
2, 10.1.0.1, 10.1.0.254, EHQ, hr
3, 10.4.0.1, 10.4.0.254, AHQ, marketing

any advise would be appreciated...

Thank you!

select network_segment_ID, display_name, starting_ip, ending_ip, bld.description, dept.description
from network_segments
join buildings bld on (building = building_ID)
join departments dept on (department = department_ID);


Postgresql vs MySQL - Which is better for join queries & writing data(inserts) I have to design a database which will end up with 50M records in a single table(there will be other tables with lesser number of records). I'm more concerned with join queries & writing data(inserts) to the database. There will be less updates and deletes queries.

I have read this article on performace comparison of Postgresql vs MySQL.

Also I have gone through below links as well.

https://stackoverflow.com/questions/8181604/postgres-9-1-vs-mysql-5-6-innodb

https://stackoverflow.com/questions/110927/would-you-recommend-postgresql-over-mysql

https://stackoverflow.com/questions/724867/how-different-is-postgresql-to-mysql

MySQL vs PostgreSQL Wiki

MySQL vs PostgreSQL: Why MySQL Is Superior To PostgreSQL

My problem is some of the links on the stackoverflow is out dated. Some of the people say that Mysql better & vice-versa.

Since I'm more concerned with join queries & writing data to the database Which is better for me? Postgresql vs MySQL? What approachs should I take to design a database like this?

Given that please don't consider this as another Postgresql vs MySQL question. I have done my research and I'm only concerned with join queries & writing data to the database scenario. I also got to know that PostgreSQL is better for GIS data. .

The databases are different.  In general, the answer depends very much on your specific queries.  As far as a general use case, I would expect PostgreSQL to perform better for a few reasons, but there are cases where I would expect MySQL to perform better.

In PostgreSQL all tables are heap tables.  In MySQL all innodb tables are btree indexes with the tuple in the payload.  This means that primary key lookups are faster on MySQL but general queries are faster on PostgreSQL.  It also means you typically need more indexes on MySQL which will slow down writes.

For example, the following query I would expect to perform better on MySQL than PostgreSQL:

SELECT u.username, p.*
  FROM users u
  JOIN preferences p ON u.id = p.user_id
 WHERE u.id = 123;

Provided that both tables share the same primary key (u.id and p.user_id), there are thousands of rows in both tables, and so forth.

On the other hand, I would expect the following query to perform better on PostgreSQL than MySQL in a db too big to fit in memory, non-cached data, proper indexes, decent-sized tables, etc:

SELECT c.legal_name, a.*
  FROM company c
  JOIN address a on a.company_id = c.id
 WHERE a.zip_code like '95%' and country = 'us';

In this case you are having to utilize other indexes, which means a lot of extra random disk I/O on MySQL.

The second issue I would expect would be write performance.  I would expect PostgreSQL to generally win here due to the fact that heap tables allow inserts wherever it is convenient, and fewer indexes maintained would be helpful too.


Selecting minimum value in top grouping only I have a query built in Oracle 10 which produces a set of data like this:

    EMP_ID  WORK_DT  TIME_TYPE
---------- --------- ---------
     12345 19-DEC-14        10
     12345 18-DEC-14        10
     12345 17-DEC-14        10
     12345 16-DEC-14        20
     12345 15-DEC-14        20
     12345 14-DEC-14        10
     12345 13-DEC-14        10
     54321 19-DEC-14        10
     54321 18-DEC-14        20
[...]

and I'm looking for output like this, only showing the most recent time_type for each employee with the applicable START_DT:

    EMP_ID  START_DT   END_DT  TIME_TYPE
---------- --------- --------- ---------
     12345 17-DEC-14 19-DEC-14        10
     54321 19-DEC-14 19-DEC-14        10

The START_DT has been troublesome for me because the same TIME_TYPE can exist in multiple blocks in the input.

My background is more with other RDBMSs than Oracle. I've spent the last few days searching here and experimenting with Oracle's analytical, aggregate, and subquery functions to get these results. I feel like I'm close but I haven't had much luck.

I'm trying to keep this as an efficient set-based solution rather than resorting to a cursor or function.

Any help is very appreciated!

Not sure about the efficient part, but the following does the trick. You'll want (emp_id, work_dt) indexed at least, having the time_type in there should not hurt either.

Assuming this table:

create table times(emp_id number, work_dt date, time_type number);

You can run this: (SQL Fiddle)

-- get the last work date
with last_dt as (
  select emp_id, max(work_dt) last_dt
  from times
  group by emp_id
),
-- get the last time type
last_rec as (
  select l.emp_id, l.last_dt, t.time_type
  from last_dt l
  join times t on l.emp_id = t.emp_id
              and l.last_dt = t.work_dt
),
-- get the last record for an employee
-- that isn't of the last time type
-- last_ot will be null for employees
-- who always do the same thing
last_other_type as (
  select l.emp_id
  , min(l.time_type) time_type -- "constant" per employee
  , min(l.last_dt) last_dt     -- same
  , max(t.work_dt) last_ot
  from last_rec l
  left join times t on l.emp_id = t.emp_id
              and l.time_type != t.time_type
  group by l.emp_id
)
select f.emp_id
, case when f.last_ot is null
    then (select min(work_dt) from times where emp_id = f.emp_id)
    else (select min(work_dt) from times where emp_id = f.emp_id
                                           and time_type = f.time_type
                                           and work_dt > f.last_ot)
                                           -- use >= if there can be more than
                                           -- one record per day
  end first_dt
, f.last_dt
, f.time_type
from last_other_type f

The idea is to get the latest time and type for each employee, then find the latest date where they worked on something different.

If such a date is found, return the first time they did the last type of time of work that is later than that.
If not, then the employee only did one type of work, so pick the very first date recorded.

For Oracle 12c, there's the match_recognize tool you can use to process this sort of "custom" grouping.

select emp_id, nr
, time_type
, first_dt
, last_dt
from times
match_recognize (
  partition by emp_id order by work_dt desc
  measures match_number() as nr
  , first(a.work_dt)   as first_dt
  , first(a.time_type) as time_type
  , last(a.work_dt)    as last_dt
  pattern (a+)
  define a as (a.time_type = first(a.time_type))
)
-- where nr = 1

This returns:

    EMP_ID         NR  TIME_TYPE FIRST_DT  LAST_DT
---------- ---------- ---------- --------- ---------
     12345          1         10 19-DEC-14 17-DEC-14
     12345          2         20 16-DEC-14 15-DEC-14
     12345          3         10 14-DEC-14 13-DEC-14
     54321          1         10 19-DEC-14 19-DEC-14
     54321          2         20 18-DEC-14 18-DEC-14

Filter on nr == 1 and you're done.


ROUND VS CONVERT FUNCTION What are the performance impact of using Round instead of Convert

DECLARE @example AS FLOAT
SET @example=969.3333
SELECT CONVERT(DECIMAL(4,1),@example)
SELECT ROUND(@example,1)

I really wonder if you got a solid business case for worrying about performance issues in rounding. Unless you are doing thousands of conversions per second, this really is non-issue.

The standard approach is to measure the performance differences. First you got to make sure that your ROUND() and CONVERT() actually do the same thing. Are you sure about that? After you are comfortable with the idea that both algorithms return same result, run your test cases.

On my test system, looping the round/convert 10 million times each, measuring the duration in milliseconds and running the test set five times provides results as such:

convert     round       delta
----------- ----------- -----------
7883        7546        337
7770        7510        260
7733        7563        170
7750        7466        284
7780        7513        267

By this test, it looks like ROUND() performs slightly better on my system. Your mileage is quite likely to vary. For ten million iterations, ROUND() saves on the average 250 milliseconds. That is, ROUND() performs some 250 nanoseconds faster than CONVERT(). I highly doubt if this is relevant to most real-world issues - unless one is developing something like high-frequency trading algorithm or the like.


How do I create an Oracle 11g user with read access to all tables/schemas? I need to create a user that has read access to all the schemas.  Do they just need Select priviliges or is there more that needs to be done?

You can grant SELECT ANY TABLE, however, that is seen by some as "excessive" privileges, and should be questioned.

On the plus side, Oracle 11 keeps key system views hidden even from that grant .. so it's not nearly as bad as it once was ;)

It would be best to build ROLEs and grant the SELECTs to the required tables to the ROLEs ... then grant the ROLE to your users as needed.

If you haven't yet, you should probably read over this guy:

https://docs.oracle.com/cd/B28359_01/network.111/b28531.pdf


SQLServer - Detect if an insert trigger was fired by an Application insert or a Replication insert Using SQLServer 2014+. The database i'm interested in, is part of a Merge Replication schema. Is there a way to detect in an insert trigger, if it was fired by an Application insert statement or a Replication-Agent insert trigger?

One way would be to inspect the APP_NAME() property inside the trigger.

I setup a simple merge publication and subscription, then created a "log" table with a trigger on the subscriber table to log the details.

IF OBJECT_ID(N'dbo.MergeLog', N'U') IS NOT NULL
DROP TABLE dbo.MergeLog;
CREATE TABLE dbo.MergeLog
(
    id int NOT NULL
        CONSTRAINT PK_MergeLog
        PRIMARY KEY CLUSTERED
    , AppName sysname NOT NULL
);
GO
IF OBJECT_ID(N'TestMergeTrigger', N'TR') IS NOT NULL
DROP TRIGGER TestMergeTrigger
GO
CREATE TRIGGER TestMergeTrigger
ON dbo.MergeReplSource
AFTER INSERT, UPDATE
AS
BEGIN
    INSERT INTO dbo.MergeLog (AppName, id)
    SELECT APP_NAME(), i.id
    FROM inserted i;
END
GO

After the merge ran, I saw the following in the dbo.MergeLog table:

╔════╦══════════════════════════════════════════════════════╗
║ id ║ AppName                                              ║
╠════╬══════════════════════════════════════════════════════╣
║ 1  ║ SourceServer-Test-MergeReplTestPublicat-DestServer-1 ║
╚════╩══════════════════════════════════════════════════════╝

The "AppName" column will reflect the names of the servers involved along with the merge replication publication name.  Inspecting the APP_NAME() should allow you to definitively identify inserts against the subscriber from merge replication vs inserts from other apps.

One should be aware that the app name is simply a connection-string parameter set by the application itself, and as such can be "faked".  Having said that, in order to provide the correct fake value, an attacker would need to know the name of the publication name, along with both the source and destination server names.  The scenario is not very likely, but something to be aware of.


Sql servers are using Kerberros and NTLM Authentication Lately I have been facing failed connection errors mainly due to servers using NTLM Authentication. In our environment the servers have a combination of ntlm and kerberoos. My question is If i ask my Domain admin to manually register all the service accounts to use the kerberoos instead of ntlm authentication will there be any problem or any performance issues that might arise ?

Kerberos authentication should be faster and it is definitely more secure, so if you are at all concerned about security it should be enabled.  The only issue that I can think of that might occur is if the Service Principal Names (SPNs) incorrect, then logins will fail.  Accordingly, if you are adding SPNs to get it set up, I would recommend having the SETSPN commands ready so you can quickly will back out the changes that were made.

Add new SPNs
Test client / application connections (restart applications that connect to force new logins)
Remove SPNs if tests fail and investigate

Ref: What Is Kerberos Authentication?


"Database is in transition" Error Today I was trying to restore a database over an already existing database, I simply right clicked the database in SSMS --> Tasks --> Take Offline so I could restore the database.

A small pop up window appeared and showed Query Executing..... for sometime and then threw an error saying Database is in use cannot take it offline. From which I gathered there are some active connections to that database so I tried to execute the following query

USE master
GO
ALTER DATABASE My_DatabaseName
SET OFFLINE WITH ROLLBACK IMMEDIATE
GO

Again at this point the SSMS showed Query Executing..... for a sometime and then threw the following error:

Msg 5061, Level 16, State 1, Line 1
ALTER DATABASE failed because a lock could not be placed on database 'My_DatabaseName'. Try again later.
Msg 5069, Level 16, State 1, Line 1
ALTER DATABASE statement failed.

After this I could not connect to the database through SSMS. and when I tried to Take it offline using SSMS it threw an error saying:

Database is in Transition. Try later .....

At this point I simply could'nt touch the database anything I tried it returned the same error message Database is in Transition.

I got on google read some questions where people had faced similar issue and they recommended to close the SSMS and open it again,  So did I and
Since it was only a dev server I just deleted the database using SSMS and restored on a new database.

My question is what could have possibly caused this ?? and how I can Avoid this to happen in future and if I ever end up in the same situation in future is there any other way of fixing it other then deleting the whole database ???

Thank you

Repro

Open up SSMS
Type in the following into a new query window

use <YourDatabase>;
go

Go over to Object Explorer (SSMS) and right-click on <YourDatabase> -> Tasks -> Take Offline
Open up a second new query window and type the following:

use <YourDatabase>;
go

You will be prompted with the following message:

  Msg 952, Level 16, State 1, Line 1
  Database 'TestDb1' is in transition. Try the statement later.

The reason this is happening can be found from a similar diagnostic query to the one below:

select
    l.resource_type,
    l.request_mode,
    l.request_status,
    l.request_session_id,
    r.command,
    r.status,
    r.blocking_session_id,
    r.wait_type,
    r.wait_time,
    r.wait_resource,
    request_sql_text = st.text,
    s.program_name,
    most_recent_sql_text = stc.text
from sys.dm_tran_locks l
left join sys.dm_exec_requests r
on l.request_session_id = r.session_id
left join sys.dm_exec_sessions s
on l.request_session_id = s.session_id
left join sys.dm_exec_connections c
on s.session_id = c.session_id
outer apply sys.dm_exec_sql_text(r.sql_handle) st
outer apply sys.dm_exec_sql_text(c.most_recent_sql_handle) stc
where l.resource_database_id = db_id('<YourDatabase>')
order by request_session_id;

For what it's worth, you don't need Object Explorer to reproduce this error.  You just need a blocked request that is attempting the same operation (in this case, take the database offline).  See the below screenshot for the three steps in T-SQL:

What you will most likely see is your Object Explorer session being blocked by another session (shown by blocking_session_id).  That Object Explorer session will be trying to get an exclusive lock (X) on the database.  In the above repro's case, the Object Explorer session was granted an update lock (U) and attempting to convert to an exclusive lock (X).  It had a wait_type of LCK_M_X, blocked by our session that was represented by the first query window (the use <YourDatabase> grabs a shared lock (S) on the database).

And then this error came about from yet another session trying to get a lock, and this error message results in the denial of a session to get access to a database that is attempting to transition into a different state (in this case, state of online to offline transition).

What should you do next time?

First off, don't panic and don't start dropping databases.  You need to take a troubleshooting approach (with a similar diagnostic query like the one above) to find out why you're seeing what you're seeing.  With a message like that, or when something appears "hung", you should automatically assume a lack of concurrency and start digging into blocking (sys.dm_tran_locks is a good start).

As a side note, I truly do believe that you are best to find out the root of a problem before taking any random action.  Not just with this operation, but that goes for all behavior that you don't expect.  Knowing what was really causing your problem, it's obvious it really was no big deal.  You basically had a blocking chain, and the parent blocker was something you most likely could have just issued a KILL on, or if it was a session's request that you didn't want to KILL then you could have waited until it completed.  Either way, you would have had the knowledge to make the right and prudent decision given your particular scenario (rollback or wait for commit).

Another thing worth noting, this is one of the reasons why I always opt for the T-SQL alternative instead of a GUI.  You know exactly what you are executing with T-SQL and what SQL Server is doing.  After all, you issued the explicit command.  When you use a GUI, the actual T-SQL is going to be an abstraction.  In this case, I looked at the blocked Object Explorer's attempt to take the database offline and it was ALTER DATABASE <YourDatabase> SET OFFLINE.  There was no attempt to rollback, which is why it was indefinitely waiting.  In your case, if you wanted to rollback sessions that had locks on that database, your ALTER DATABASE ... SET OFFLINE WITH ROLLBACK IMMEDIATE would have most likely sufficed if you had made the initial determination that rollback was okay.


Can't start oracle after recovery I'm trying to import the database to another instance. So I imported the database with restore controlfile, restore database and recover database command.
When I try to start the database, it complains:
ORA-00603: ORACLE server session terminated by fatal error
ORA-01092: ORACLE instance terminated. Disconnection forced
ORA-00704: bootstrap process failure
ORA-39700: database must be opened with UPGRADE option
Process ID: 14134
Session ID: 969 Serial number: 5700

How can I finish the recover process? I can do startup upgrade, but what next? I'm not running a upgrade job, just migrating the database to a new server.

This happens when you restore the backup with a different version of Oracle software.
Use the same version of Oracle software.
Or you can upgrade the database before opening it normally. For that, use alter database open resetlogs upgrade;, and continue with upgrading the database.


export data from SQl to text file/MongoDB I have some data in multiple tables (ACTTAB, APTTAB, ...)

How do I export this data from SQL and insert into a text file?
How can I import this data into MongoDB?

There is a tool on CodePlex you can use for this, called MSSQL to MongoDB Tool. This tool will migrate all data from MSSQL to MongoDB. It will do this without exporting to a file first.
If you want to export to file first, you are best off using the Export Data wizard which you can find in your SSMS, by right clicking your DB and choose 'Export Data' under 'Tasks'


Implementing a one to zero or one relationship in SQL Let us say that I am designing a database for a scenario where there exists a one-to-zero-or-one (1-0..1) relationship. For example:

There is a set of Users, and some Users may also be Customers.

Thus, I created the two corresponding tables, users and customers, but…

…What is the best way to represent and implement this situation in a given SQL platform? I have considered two possible solutions:

In the users table, add the customer column which may be either a FOREIGN KEY
reference to customers or a NULL mark.
In the customers table, include a user column (set with a UNIQUE constraint) which points to the users table.

I have already asked a similar question in some forums, but the answer
was basically “whatever you need”, “whatever you think convenient”. I
do not like this kind of answer. I want a serious piece of DB theory
instead, a well founded answer. Where can I read about 1-0..1
relationships?

I want a serious piece of DB theory

Modern relational theory rejects nulls, which would seem to immediately invalidate your option 1. However, this strawman may be eliminated by replacing the default null with a default value e.g. a 'dummy' customer created solely to explicitly model the "is not a customer" correspondence.

I think your option 2 is the most theoretically sound because, unlike the modified option 1, the relations can be in sixth normal form (6NF), being a projection-join normal form and the highest normal form attainable.

I have also heard of a design rule of thumb that states that a relation should model EITHER an entity OR the relationship between entities but never both, which seems sensible to me. Again, this would favour option 2. However, I heard of this rule of thumb many years ago, don't recall where and can offer no serious theoretical basis (other than 6NF as mentioned above).


Unexpected results with soundex() function when combined st_distance() select soundex(A.name),
       A.gid,
       A.name,
       soundex(B.name),
       B.gid,
       B.name,
       st_distance(B.the_geom,A.the_geom) * 111.325 as dist
from (select name,
             gid,
             the_geom,
             count(soundex(name)) over (partition by soundex(name)) as cnt
      from poi_new A) A
join poi_new B on st_distance(B.the_geom, A.the_geom) * 111.325 < .2
where cnt > 1
  and A.gid <> B.gid
  and A.name = B.name
group by A.gid, A.name, B.gid, B.name, dist, soundex(A.name), soundex(B.name);

Soundex value is same and also the names are the same, but it doesn't return any similar names when using this query.

I want to retrieve the names which are located in a particular distance, so I used st_distance() here, then I tried with another query using window function. I am getting the similar names with same soundex value.

The output which shows similar names with same soundex value, I want to get the results like this with respect to the st_distance function which I used in the query.

Where do I have to change the code? Is the window function which I used is correct or not?

The condition:

AND    A.name = B.name

only allows identical names, while you obviously want to match names with the same soundex value. So make that:

AND    soundex(A.name) = soundex(B.name)

If gid is the primary key, you can also simplify to:

GROUP BY a.gid, b.gid

However, since there obviously is no aggregation at all, you can simply remove the GROUP BY clause.

As suggested by PostGis, consider ST_DWithin() instead of ST_Distance() in the JOIN clause. The latter cannot utilize a spatial index.


SSIS mySQL ADO.NET connection string I want to make my SSIS project can be configure through project parameters. So I use project parameters to store connection strings. But the connection string which I copied from the mysql connection manager not working. After I assign the parameter value which I copied from the connection manager it self, to connection manager by expression, It says:

  An error has occurred while connecting MySQL Server: Could not create a managed connection manager

How to fix this? I think this is a problem about connection string.

There are many things that can causes this issue:

You are running under 64Bit mode

in the project properties window, go to Debugging tab, and set Run64BitRuntime as false. and try again.

If SSIS Server and DEV Computer have different Connector/.Net Versions

This will occur after deployment

You are missing the provider in the connectionstring

You have to add ;Provider=MySQLProv or ;Provider=MSDASQL or other provider name (if you are not using these providers) to the connection string

References

https://stackoverflow.com/questions/18120800/ssis-error-could-not-create-a-managed-connection-manager-vs-2010-adonet
https://social.msdn.microsoft.com/Forums/sqlserver/en-US/392c89cb-19d1-48c8-8d54-e8ae0e75f8e8/could-not-create-a-managed-connection-manager-ssis-package-from-ssms-windows-7-64-bit?forum=sqlintegrationservices
https://www.connectionstrings.com/mysql/


How to call the unix Sendmail from oracle I know that is possible to send mails using the oracle-db by UTL_SMTP, and there are several examples of such usage everywhere.

My question is: Can I call the GNU/Unix/Linux Sendmail to perform a similar action?

Why do you want to use sendmail?  Is it just because it is simpler than UTL_SMTP?  You should really look into UTL_MAIL.

See this: http://www.orafaq.com/wiki/Send_mail_from_PL/SQL

To send mail from an Oracle db, you might:

Use UTL_TCP, way too low-level, old school, not recommended.
Call Unix Sendmail (external program) using DBMS Scheduler. Kind of complex -- you need to setup credentials to run OS processes. Anyone who has tried this knows it's not trivial.  Technically possible, but why put yourself through the pain. I personally wouldn't do this.
Use UTL_SMTP, better than UTL_TCP, was the best approach pre-10g.
Custom mail proc that you write, which makes use of UTL_SMTP.  Okay for 9i, but for 10g+ you'd just be reinventing the wheel, so use UTL_MAIL instead.
Use UTL_MAIL, simplest approach on 10g/11g/12c
If you are really bent on using sendmail and would be open to an asychronous/batch solution, consider creating a DIRECTORY that Oracle can write text files in MIME format into (via UTL_FILE), then set up a cron job that runs every 5 minutes which processes these files through sendmail and deletes them if successful.


Search subkey inside JSONB In a PostgreSQL 9.6 database, I'm using this sql to get 'application' (field) -> 'tour_id.id' (key and subkey in jsonb field)

SELECT *
FROM "payment" WHERE ("payment"."application"#>>'{}')::text
LIKE '%a43d38d4-9bfb-4ffa-8704-e536f04b0c60%'
ORDER BY "payment"."createdAt" DESC;

My id is unique, but really I'd like to do it the right way in Postgres. (I know how to do it with a key, but not with a subkey.)

Example jsonb value:

[{"tour_id":
   {"id":"a43d38d4-9bfb-4ffa-8704-e536f04b0c60","name":"Terre Sainte 2018"},
   "amount":"500.00",
   "customer_id":{"id":"f492b26d-d260-4bcb-8e96-6532fdf38665","name":"OBAMA John"},
 "id":"5cd8338c-15c2-48c5-b305-9e172bb33a9a"}]

I'm using this sql to get 'application' (field) -> 'tour_id.id'

Assuming you meant to ask:

I'm using this SQL query to retrieve the row where application #>> '{0, tour_id, id}' equals the provided UUID.

.. which already carries half an answer.

If your outer JSON array only has a single element like your example value implies, use this simple query:

SELECT *
FROM   payment
WHERE  application #>> '{0, tour_id, id}' = 'a43d38d4-9bfb-4ffa-8704-e536f04b0c60'
ORDER  BY "createdAt" DESC;

Note how I replaced the (assumingly) unneeded fuzziness of
like '%a43d38d4-9bfb-4ffa-8704-e536f04b0c60%'
with simpler and faster
= 'a43d38d4-9bfb-4ffa-8704-e536f04b0c60'.

Else (for any number of elements in the outer JSON array):

SELECT *
FROM   payment
WHERE  application @> '[{"tour_id": {"id":"a43d38d4-9bfb-4ffa-8704-e536f04b0c60"}}]'
ORDER  BY "createdAt" DESC;

That's the jsonb containment operator @>, which can be supported with a generic index or a more specialized jsonb_path_ops index. Instructions and links:

How to get particular object from jsonb array in PostgreSQL?

The manual hints:

  For a full description of jsonb containment and existence semantics, see Section 8.14.3.


update column without auto timestamp update Hello everyone I have got a product stock table with a time-stamp column so the so any updated to that row will update the time stamp ,I'm happy with this setting but sometimes I want to update the row without updating the time stamp,so how to write mysql statement  that update a specific column without updating the lastUpdate column ?

this my statement:
update productsStock set productName="HP PRINTER 20000" where product_reference="001";

productsStock table description

describe productsStock;

+--------------------+-----------------------+------+-----+-------------------+-----------------------------+
| Field              | Type                  | Null | Key | Default           | Extra                       |
+--------------------+-----------------------+------+-----+-------------------+-----------------------------+
| productID          | int(10)               | NO   | PRI | NULL              | auto_increment              |
| product_reference  | varchar(255)          | NO   | PRI | NULL              |                             |
| productName        | varchar(255)          | NO   | UNI | NULL              |                             |
| brand              | varchar(45)           | NO   |     | NULL              |                             |
| model              | varchar(100)          | NO   | PRI | NULL              |                             |
| productDescription | varchar(255)          | YES  |     | NULL              |                             |
| physicalStock      | int(10) unsigned      | YES  |     | 0                 |                             |
| price              | decimal(7,2) unsigned | YES  |     | 0.00              |                             |
| location           | varchar(45)           | NO   |     | NULL              |                             |
| lowStockAlert      | int(10) unsigned      | YES  |     | 0                 |                             |
| lastUpdateBy       | varchar(45)           | NO   |     | NULL              |                             |
| lastUpdate         | timestamp             | NO   |     | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP |
+--------------------+-----------------------+------+-----+-------------------+-----------------------------+

When you do your update, set the lastUpdate column equal to itself, that way it won't default in the current timestamp:

 update productsStock set productName="HP PRINTER 20000", lastUpdate = lastUpdate where product_reference="001";


SQL Server - Space Available I can't find exact answer on my question. When i right click on any database in SSMS, i see "Space available parameter". Is this free space in mdf file only or it include transaction log file too (free space in mdf + ldf files together)? Thanks

I asked because ... I have a table (size about 450GB with 5 millions rows) with LOB data.
Application support delivered this script to delete some rows (should delete about 300K rows).

declare @cnt int;
declare @cnt_total int = 0;
set nocount off
while 1 = 1
begin
    delete top(5000)    -- PARAM BATCH_SIZE
    from fw.fw_o_clob
    where DT_TEC_CREATED < '2019-01-01T00:00:00.0000000'
        AND ID_CLOB <> -1
    ;
    set @cnt = @@ROWCOUNT;
    set @cnt_total += @cnt;
    if @cnt = 0
        break;
end;
print concat('Total ', @cnt_total, ' rows deleted.');
go

But when we run this, "Size DB" started growing (info from properties) - this is OK, because transaction log started growing but what is interesting "Space available" was getting smaller and smaller so we canceled it. Can someone explain me why? Thanks

From looking at a Profiler trace of the SSMS dialog opening the queries are essentially:

Size:

SELECT sum(df.size) * 8 / 1024.0 Size_MB
FROM sys.database_files df;

and

Space Available:

with q as
(
    SELECT
    (SELECT SUM(CAST(df.size as float)) FROM sys.database_files AS df WHERE df.type in ( 0, 2, 4 ) ) AS [DbSize],
    (SUM(a.total_pages) + (SELECT ISNULL(SUM(CAST(df.size as bigint)), 0) FROM sys.database_files AS df WHERE df.type = 2 )) AS [SpaceUsed]
    FROM sys.partitions p
    join sys.allocation_units a
      on p.partition_id = a.container_id
    left join sys.internal_tables it
      on p.object_id = it.object_id
)
select (DbSize - SpaceUsed) * 8 / 1024.0 SpaceAvailable_MB
from q;

So Size includes the log file, but Space Available does not.  It's only the unallocated space in database files.


SQL server 2012 reports "Missing Index" on column which is covered by "Clustered Index" My table creation script is as follows

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

CREATE TABLE [dbo].[quotation_section](
    [quotation_id] [varchar](24) NOT NULL,
    [section_id] [varchar](24) NOT NULL,
    [name] [nvarchar](100) NOT NULL,
    [description] [nvarchar](1000) NULL,
    [sort_order] [tinyint] NULL,
    [copied_from_library_flag] [bit] NULL,
    [max_sequence] [tinyint] NULL,
    [collaborator_id] [varchar](24) NULL,
    [status] [tinyint] NULL,
    [mandatory_flag] [bit] NULL,
    [read_only_flag] [bit] NULL,
    [draft_flag] [bit] NULL,
    [has_comment_flag] [bit] NULL,
    [revision_time] [datetime] NULL,
    [report_revision_time] [datetime] NULL,
    [last_submitted_max_sequence] [tinyint] NOT NULL,
    [buyer_code] [varchar](4) NULL,
 CONSTRAINT [PK_quotation_SECTION_01] PRIMARY KEY CLUSTERED
(
    [quotation_id] ASC,
    [section_id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO

In one of my database queries the execution planner is reporting the following:

                <MissingIndexGroup Impact="25.8056">
                  <MissingIndex Database="[myschema]" Schema="[dbo]" Table="[quotation_section]">
                    <ColumnGroup Usage="EQUALITY">
                      <Column Name="[section_id]" ColumnId="2" />
                    </ColumnGroup>
                  </MissingIndex>
                </MissingIndexGroup>
              </MissingIndexes>

I was under the impression that Clustered indexes are used by query optimiser even when non-clustered index seek is performed (unless I have messed up something here, sorry in that case). I do admit that other tables I am using for queries do have the columns present in both clustered and non-clustered indexes. So what should the approach be here? Does adding the missing index (as reported by SQL server execution planner) is a good idea?

It is trying to filter[1] by section_id but not quotation_id - it can't do this by seeking on an index on quotation_id,section_id so it will have to scan that index (which as it is your clustered index, is the whole table). It would be able to use an index on section_id,quotation_id for this because the target columns are first in the index's key.

[1] or possibly sort, as we can't see the query or the full plan we can't know which or why. In the case of sorting it will be wanting the index for slightly different reasons, perhaps to replace a clustered index scan and spool to tempdb for sorting by scanning the non-clustered index and pulling the other data out in order more directly.

  I was under the impression that Clustered indexes are used by query optimiser even when non-clustered index seek is performed

That is correct. It will use seeks on the non-clustered index to find the clustering key value, then use that to find other data needed from the row. Unless it only needs data from columns in the index - in that case there is no need to refer to the clustered index as all the information is already available without.

Note that this is essentially the same if you don't have a clustered index. Instead of containing the clustering key each non-clustered index contains the rowid of each record in the heap instead.

  Does adding the missing index (as reported by SQL server execution planner) is a good idea?

For that query as it stands, quite probably (though with the caveat that we don't know what the query is, or the query plan that was used, so it could be that refactoring that would be more useful). It would likely allow the query to be processed by seeking on that index then performing lookups on the clustered index as needed instead of scanning the whole clustered index, which the planner expects to be a significantly more efficient way around.


Why does this ALTER TABLE ADD FOREIGN KEY query create two foreign key constraints I am attempting to add a foreign key constraint to existing tables via an ALTER TABLE statement:

CREATE TABLE `blog` (
  `blogid` int(11) NOT NULL AUTO_INCREMENT,
  `deptid` int(11) NOT NULL DEFAULT '-1',
  `name` varchar(255) NOT NULL DEFAULT '',
  PRIMARY KEY (`blogid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `dept` (
  `did` int(11) NOT NULL AUTO_INCREMENT,
  `title` varchar(50) NOT NULL DEFAULT '',
  PRIMARY KEY (`did`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

ALTER TABLE blog
ADD CONSTRAINT blog_deptid_ibfk_dept
FOREIGN KEY (deptid) REFERENCES dept (did);

The query runs without error but it creates two foreign key constraints and not just one:

CREATE TABLE `blog` (
  `blogid` int(11) NOT NULL AUTO_INCREMENT,
  `deptid` int(11) NOT NULL DEFAULT '-1',
  `name` varchar(255) NOT NULL DEFAULT '',
  PRIMARY KEY (`blogid`),
  KEY `blog_deptid_ibfk_dept` (`deptid`),
  CONSTRAINT `blog_deptid_ibfk_dept` FOREIGN KEY (`deptid`) REFERENCES `dept` (`did`),
  CONSTRAINT `blog_ibfk_dept` FOREIGN KEY (`deptid`) REFERENCES `dept` (`did`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

I'm using MariaDB 5.5[.33a]. I have tested this on MySQL 5.5.32 using SQL Fiddle and the results are the same.

Interestingly, ALTER TABLE blog DROP FOREIGN KEY blog_ibfk_dept; results in both constraints being dropped, but ALTER TABLE blog DROP FOREIGN KEY blog_deptid_ibfk_dept; results in only that one constraint being dropped.

I cannot for the life of me figure out why two constraints are being created. Can someone explain on this unexpected behavior?

This looks like a bug in the lexer/parser logic.

My theory is that it has to do with the name of the constraint. When a foreign key constraint is defined without a name, MySQL assigns automatically a name of its choice. This is usually of the form referencingtable_ibfk_X.

Since the bug appears when we define a name for the constrain that contains the string _ibfk_, we can conclude that the parser gets confused somewhere and adds the 2nd constraint (see sql-fiddle, bug appears in version 5.5.32).

And yes, it's a known bug. It seems that it was fixed in some older minor 5.5 versions but then reappeared. Please check the latest 5.5 and 5.6 versions (5.5.34 and 5.6.22) if it has been fixed (the bug does not appear in 5.6.6).


How can I set a default now() timestamp in a timestamp array? I want to have a timestamp array which tracks a series of events. The first event always happens when the row is created, so I want the array to be instantiated with an initial default value. I've tried these two things and they both result in now() being executed at the time of the statement. Is it possible for me to achieve this with a simple default value, or do I need to write a hook?

# ALTER TABLE messages ADD COLUMN testarray TIMESTAMP[] DEFAULT '{NOW()}';
ALTER TABLE
# ALTER TABLE messages ADD COLUMN testarray2 TIMESTAMP[] DEFAULT '{"NOW()"}';
ALTER TABLE

freedom_development=# \d messages
                                                 Table "public.messages"
    Column     |             Type              |                                Modifiers
---------------+-------------------------------+-------------------------------------------------------------------------
 id            | integer                       | not null default nextval('messages_id_seq'::regclass)
 testarray     | timestamp without time zone[] | default '{"2017-12-30 21:00:09.622951"}'::timestamp without time zone[]
 testarray2    | timestamp without time zone[] | default '{"2017-12-30 21:00:19.936001"}'::timestamp without time zone[]

You need to use the array[] syntax:

create table messages(
    id serial primary key,
    val text not null,
    testarray timestamp[] not null default array[now()]
);

insert into messages(val) values ('blah');

select * from messages;

http://sqlfiddle.com/#!17/c3a31/4


Was my Postgres cluster built with the ICU libraries available for Postgres 10 and later? I wonder if my installation of Postgres 10 Beta 2 has been built to include the new International Components for Unicode (ICU) collations. For background info, see More robust collations with ICU support in PostgreSQL 10 by Peter Eisentraut.
I used the pg_config utility to detect the build info. When running pg_config --configure I get output where the last line is 'ICU_LIBS=-L/opt/local/Current/lib -licuuc -licudata -licui18n'.
➠ Does this mean I have the ICU libraries installed, or not?

/Library/PostgreSQL/10Beta2/bin/pg_config --configure
'--with-icu' '--prefix=/mnt/hgfs/pginstaller.pune/server/staging_cache/osx' '--with-ldap' '--with-openssl' '--with-perl' '--with-python' '--with-tcl' '--with-bonjour' '--with-pam' '--enable-thread-safety' '--with-libxml' '--with-uuid=e2fs' '--with-includes=/opt/local/Current/include/libxml2:/opt/local/Current/include:/opt/local/Current/include/security' '--docdir=/mnt/hgfs/pginstaller.pune/server/staging_cache/osx/doc/postgresql' '--with-libxslt' '--with-libedit-preferred' '--with-gssapi' 'CFLAGS=-isysroot /Applications/Xcode.app/Contents/Developer/Platforms/MacOSX.platform/Developer/SDKs/MacOSX10.8.sdk -mmacosx-version-min=10.8 -arch i386 -arch x86_64 -O2' 'LDFLAGS=-L/opt/local/Current/lib' 'ICU_CFLAGS=-I/opt/local/Current/include' 'ICU_LIBS=-L/opt/local/Current/lib -licuuc -licudata -licui18n'

➠ Is there another way to detect if my Postgres has the ICU collations available? Some way via SQL?
When I execute SELECT * FROM pg_collation ;, of the 845 locale names listed, 575 end in -x-icu. Does that happen only if the ICU libraries are in place?

Does this mean I have the ICU libraries installed, or not?

The configure option that really brings in the library is --with-icu. Currently, checking for this one seems the more reliable way to tell whether ICU has been included in the build.

ICU_CFLAGS and ICU_LIBS are paths that are required only if the library is installed where the compiler does not search by default.

  Does that happen only if the ICU libraries are in place?

It does, but pg_collation also has a new collprovider field indicating that a collation is from ICU (i) or the libc (c)


On pgAdmin 4, how to basically copy the content of one or two cells? I decided to go on pgAdmin 4 instead of the 3 (I'm on Windows 7).
It seems that this software if somehow still in a alpha state...

I would like to only get the content of one cell. So I select this cell and tap Ctrl+C.

But it seems NOT to copy the content to the clipboard (the previous clipboard content stays when I paste...).

I notice that the copy button in the topbar stays disabled...

On the pgAdmin 4 GUI Tool page, it seems the only proposed way to copy is to click on this button :

  Click the Copy icon to copy the currently selected row.

Do anybody has a better way to do this ?

OK I've found.

You have to double click on the cell, then a popup pops up, with the cell content selected, and then you can use ctrl+c...


what is the best query to find missing index in sql server 2008r2 and how to resolve missing index identify missing index and solve indexing problem

You can use sp_BlitzIndex to find the high-value missing indexes, @mode=3 will specifically only show the high-value missing indexes. Target the ones with an "Estimated benefit per day" that are greater than 5,000,000. See if your workload improves after making the changes.

Disclosure: I work at Brent Ozar Unlimited.


Is there a good reason to store big text data outside database? I was analyzing my MySQL database performance and found that tables containing text fields are just huge, larger than 1Gb.

This data is only used when loading single record (like article or blog post) and is updated very rarely.

Is there a reason to save text data to plain files and metadata to database, or let the db engine handle it all?

You might want to consider moving the data outside the database if:

You are having problems with backup durations
It causes disk or network throughput problems on your database server
You have a requirement to access the data without using the database

In general, I'd suggest keeping it in the database unless you can come up with a good reason not to.

There's some overlap between this question and "Files - in the database or not?". You might find some useful thoughts there, though things that are true for binary files don't necessarily hold true for text data.

MySQL offers some useful features where text is stored in the database, such as opportunities to use full-text indexing, and network compression.


Speed up complex join with disjunctive condition I have a table that contains information about orders made by users and their phone numbers. The table looks like this one:

table Orders (
    OrderID int,
    UserName varchar(50),
    PhoneNumber1 varchar(50),
    PhoneNumber2 varchar(50),
    PhoneNumber3 varchar(50)
)

Problem: Fixed a user u, count the number of different users that share at least one phone number with u. In other words, count the number of distinct users v that satisfy one or more of the following conditions:

u.PhoneNumber1 = v.PhoneNumber1
u.PhoneNumber2 = v.PhoneNumber2
u.PhoneNumber3 = v.PhoneNumber3

My solution (OrderID is the primary key, I added the other columns in the group by clause just to display them in the result):

select o1.OrderID,
       o1.PhoneNumber1,
       o1.PhoneNumber2,
       o1.PhoneNumber3,
       count(distinct o2.UserName)
from Orders o1
inner join Orders o2
    on o1.PhoneNumber1 = o2.PhoneNumber1
    or o1.PhoneNumber2 = o2.PhoneNumber2
    or o1.PhoneNumber3 = o2.PhoneNumber3
group by o1.OrderID, o1.PhoneNumber1, o1.PhoneNumber2, o1.PhoneNumber3

The Orders table contains about 300K records, and my query requires too much time to complete (my estimate is about 3 hours, but I didn't let it finish completely).

What strategies can I adopt to speed up the computation? Is there, for example, an equivalent statement that yields the same result?

Note: I tried to create three non-clustered indexes on the PhoneNumber columns but didn't notice significant improvements.

The below creates 300000 dummy orders with 3 random "phone" numbers and then finds the matches, should get you started.

use tempdb
GO

drop table if EXISTS Orders
GO

create table Orders (
    OrderID int primary key,
    UserName varchar(50),
    PhoneNumber1 varchar(50),
    PhoneNumber2 varchar(50),
    PhoneNumber3 varchar(50)
)

-- generate 300000 with randon "phone" numbers

;WITH TallyTable AS (
SELECT TOP 300000 ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) AS [N]
  FROM dbo.syscolumns tb1,dbo.syscolumns tb2
)
insert into Orders
select n, 'user' + cast(n as varchar(10)), cast(CRYPT_GEN_RANDOM(3) as int), cast(CRYPT_GEN_RANDOM(3) as int), cast(CRYPT_GEN_RANDOM(3) as int)
FROM TallyTable;

/*

-- original query, takes a long time

select o1.OrderID,
       o1.PhoneNumber1,
       o1.PhoneNumber2,
       o1.PhoneNumber3,
       count(distinct o2.UserName)
from Orders o1
inner join Orders o2
    on o1.PhoneNumber1 = o2.PhoneNumber1
    or o1.PhoneNumber2 = o2.PhoneNumber2
    or o1.PhoneNumber3 = o2.PhoneNumber3
group by o1.OrderID, o1.PhoneNumber1, o1.PhoneNumber2, o1.PhoneNumber3
*/

-- which users share the same phonenumber1
select o1.UserName as username, 'phonenumber1' as phonenumber, o2.UserName as sharedwith
from Orders o1
inner join Orders o2
    on o1.PhoneNumber1 = o2.PhoneNumber1
    and o1.UserName <> o2.username -- make sure it's a different user
union ALL
-- which orders share the same phonenumber2
select o1.UserName, 'phonenumber2',  o2.UserName
from Orders o1
inner join Orders o2
    on o1.PhoneNumber2 = o2.PhoneNumber2
    and o1.UserName <> o2.username -- make sure it's a different user
union ALL
-- which orders share the same phonenumber3
select o1.UserName, 'phonenumber3',  o2.UserName
from Orders o1
inner join Orders o2
    on o1.PhoneNumber3 = o2.PhoneNumber3
    and o1.UserName <> o2.username -- make sure it's a different user
order by username asc

-- use the above as a cte to do some counting
; with matches as (
    select o1.UserName as username, 'phonenumber1' as phonenumber, o2.UserName as sharedwith
    from Orders o1
    inner join Orders o2
        on o1.PhoneNumber1 = o2.PhoneNumber1
        and o1.UserName <> o2.username -- make sure it's a different user
    union ALL
    -- which orders share the same phonenumber2
    select o1.UserName, 'phonenumber2',  o2.UserName
    from Orders o1
    inner join Orders o2
        on o1.PhoneNumber2 = o2.PhoneNumber2
        and o1.UserName <> o2.username -- make sure it's a different user
    union ALL
    -- which orders share the same phonenumber3
    select o1.UserName, 'phonenumber3',  o2.UserName
    from Orders o1
    inner join Orders o2
        on o1.PhoneNumber3 = o2.PhoneNumber3
        and o1.UserName <> o2.username -- make sure it's a different user
)
SELECT matches.username, COUNT(*) AS matches
from matches
group by username
order by 2 desc


How to connect to Oracle database on VM1 from host with 11gClient I have a VM running Oracle Linux 6.5, I loaded Oracle server x64bit and created a database. The database functions fine and the listener is up and running for the database. I want to connect to the server (VM1) using SQL Developer from host. But I have no idea how to do this.

Can anyone point me to DETAILED instructions on the easiest way to accomplish this?

It depends on the VM software you are using. In case of VirtualBox you have to check what type of connection you set (NAT, bridge, etc); if using NAT you need to enable a port-forward to the listener port: e.g. TCP 21521 on host, TCP 1521 on VM).

Also check if iptables firewall is enabled on your VM.


How to model A Shop Database I want to draw the ER model of a store which The scenario is like this: They have list of Goods and Some Seller which sells them to customers they want to know how much they get profit from their selling and also how Much money they have From Selling The Goods And also they have Some Expenditures Every day That are from these money which they earn from their selling.And Mostly Every week they send these money to the bank, Here is the model which I have Drawn

But The problem is here that I don't know Where should I Store The Total money of the Sales So If I insert a row in Expenditures table The Total money Should be decreased. IF you Guys Have any better Model For This Scenario Please SHARE WITH ME

Not specific to your question but in general do not put sales into an expenditure table. The name of the table suggests that the table contains information about money leaving the business, not coming in - it will confuse the reader and hinder maintainability in the future. I suggest you create a second table called sales and put your receipts in there.

There are very good example database designs that you can find online (I googled bookstore database schema and quite a few hits came up that would be useful to you).

Additionally, if you were going to do this seriously, then I'd look into the general ledger system or otherwise known as double entry book-keeping system which will help you model this a bit better. I'm not an accountant, but wikipedia seems to have a decent link on it:

https://en.wikipedia.org/wiki/Double-entry_bookkeeping_system


MySQL: INSERT INTO SELECT issues Consider the following tables:

=> table1
| aid |
| --- |
| a1  |
| a1  |
| a3  |
| a2  |
| ... |

=> table2
| aid | count | va    | vb    |
| --- | ----- | ----- | ----- |
| a1  | c1    | va1   | vb1   |
| a2  | c2    | va2   | vb2   |
| ... | ...   | ...   | ...   |

Basically, table2 aggregates data from several other tables, including from table1.
One way to update table2 data in MySQL is:

INSERT INTO `table2`(`aid`, `count`)
SELECT `src`.`aid`, `src`.`count` FROM
(
    SELECT `aid`, COUNT(`aid`) AS `count`
    FROM `table1`
    GROUP BY `aid`
) AS `src`
ON DUPLICATE KEY UPDATE `table2`.`count` = `src`.`count`

Although this code works, it seems to be wordy (going around in circle).
At first, a better approach would be:

INSERT INTO `table2`(`aid`, `count`)
SELECT `aid`, COUNT(`aid`) AS `count`
FROM `table1`
GROUP BY `aid` AS `src`
ON DUPLICATE KEY UPDATE `table2`.`count` = `src`.`count`

Unfortunately, due some unknown reason, this more direct approach doesn't work.
Why?

According to the documentation:

  In other words, VALUES(col_name) in the UPDATE clause refers to the value of col_name that would be inserted, had no duplicate-key conflict occurred.

Therefore, a more direct approach would be:

INSERT INTO `table2`(`aid`, `count`)
SELECT `aid`, COUNT(`aid`) AS `count`
FROM `table1`
GROUP BY `aid`
ON DUPLICATE KEY UPDATE `table2`.`count` = VALUES(`count`)


PostgreSQL 9.2 number of days in a tstzrange? How can the number of days contained within a range be found?

For example, with these timestamp ranges, get these (integer) number of days:

tstzrange('2013-10-01 07:00', '2013-10-01 07:15') | 1 (day)
tstzrange('2013-10-01 07:00', '2013-10-01 23:45') | 1 (day)
tstzrange('2013-10-01 02:00', '2013-10-02 23:45') | 2 (days)
tstzrange('2013-10-01 07:00', '2013-10-03 01:00') | 2 (days)
tstzrange('2013-10-01 01:00', '2013-10-03 23:00') | 3 (days)
tstzrange('2013-10-01 23:00', '2013-10-04 01:00') | 4 (days)

In my digging about, I've not found a built in function for this.

Is is necessary pull out the lower() and upper() timestamp elements, then get the date interval between them?

There are probably better ways to do this, but here are a few functions to return days or intervals from a tstzrange/tsrange input parameter.

CREATE OR REPLACE FUNCTION extract_interval(TSTZRANGE) RETURNS interval AS
$func$
select upper($1) - lower($1);
$func$ LANGUAGE sql STABLE;

CREATE OR REPLACE FUNCTION extract_interval(TSRANGE) RETURNS interval AS
$func$
select upper($1) - lower($1);
$func$ LANGUAGE sql STABLE;

CREATE OR REPLACE FUNCTION extract_days(TSTZRANGE) RETURNS integer AS
$func$
select (date_trunc('day',upper($1))::DATE - date_trunc('day',lower($1))::DATE) + 1;
$func$ LANGUAGE sql;

CREATE OR REPLACE FUNCTION extract_days(TSRANGE) RETURNS integer AS
$func$
select (date_trunc('day',upper($1))::DATE - date_trunc('day',lower($1))::DATE) + 1;
$func$ LANGUAGE sql;

WITH tzr AS (SELECT tstzrange('2013-10-01 10:00-07', '2013-10-03 05:15-07') AS dttz, tstzrange('2013-10-01 10:00', '2013-10-03 05:15') AS dt)
SELECT  extract_interval(dttz) as interval_with_tz,
        extract_interval(dt) as interval_no_tz,
        extract_days(dttz) as days_with_tz,
        extract_days(dt) as days_no_tz
FROM tzr;

                      tstzrange                      | interval_with_tz | interval_no_tz | days_with_tz | days_no_tz
-----------------------------------------------------+------------------+----------------+--------------+------------
 ["2013-10-01 07:00:00-07","2013-10-01 07:15:00-07") | 00:15:00         | 00:15:00       |            1 |          1

Edit: I have put up these functions on the Postgresql Wiki so that others can use or edit them if more efficient versions are developed.
https://wiki.postgresql.org/wiki/Extract_days_from_range_type


ODBC can't connect to internal MySQL without external network Running ODBC 5.2 (10.0.0.10) and MySQL 4.x (10.0.0.50). Yes, I know that ODBC 5.x dosen't officaly support MySQL 4.x but it was working fine for ~8 months till now when we have little problems with ISP and external network is not that stable.

Thing is that ODBC can make connection with MySQL fine when external network is working but in a moment when we get problem with external network the connection between 10.0.0.10 (ODBC) and 10.0.0.50 (MySQL) stops.

OLE DB provider "MSDASQL" for linked server "xyz" returned message\
"[MySQL][ODBC 5.2(w) Driver]Lost connection to MySQL server at\
'waiting for initial communication packet', system error: 10060".

Cannot initialize the data source object of OLE DB "MSDASQL" for linked server "xyz".

... and it gets even more interesting I can successfully ping between ODBC and MySQL machines and even make telnet for 3306 from 10.0.0.10 to 10.0.0.50 while external network dosen't work and ODBC connection fails.

This leads to my question... why ODBC can't make connection to MySQL without external network? Have I missed something or should I downgrade to ODBC 3.51?

For each new client connection, the server uses the client IP address to check whether the client host name is in the host cache. If not, the server attempts to resolve the host name. First, it resolves the IP address to a host name and resolves that host name back to an IP address. Then it compares the result to the original IP address to ensure that they are the same.

  https://dev.mysql.com/doc/refman/5.7/en/host-cache.html

(Docs here are 5.x, but this behavior is present pre-5.x).

Assuming loss of the external network also means the MySQL server can no longer reach the DNS server, then the lack of timely DNS responses will stall the handshaking logic until they get back some kind of timeout error... by which point, ODBC has already given up and thrown this error.

The solution is to disable the reverse DNS lookups with each client connection by starting the server using --skip-name-resolve.  Unless you have users in the mysql.user table with actual hostnames in the Host column, the default behavior is of no real value.


Is it possible to use a temp table in a data flow source? I have a data flow in a 2012 SSIS package where I'm trying to use a temp table as the data source.  I'm using an OLE DB data source with a SQL Command Data access mode.

My code looks like this:

CREATE TABLE #Checksums (DBName sysname, CheckSum bigint)
-- Code that loads the Checksum table here
SELECT DBName, CheckSum FROM #Checksums

In order to get the data source to pull the list of columns I temporarily put the following:

SELECT CAST(NULL AS sysname) DBName, CAST(NULL as bigint) CheckSum

That got the columns created and I was able to map to my destination.

I have ValidateExternalMetadata set to false on the data source and DelayValidation set to true on the data flow.

My code works correctly in SSMS however every time I run the package it errors out with the following:

[OLE DB Source [39]] Error: SSIS Error Code DTS_E_OLEDBERROR.  An OLE DB error has occurred. Error code: 0x80004005.
An OLE DB record is available.  Source: "Microsoft SQL Server Native Client 11.0"  Hresult: 0x80004005  Description: "Invalid object name '#qtemp'.".
[OLE DB Source [39]] Error: Unable to retrieve column information from the data source. Make sure your target table in the database is available.
[SSIS.Pipeline] Error: OLE DB Source failed the pre-execute phase and returned error code 0xC020204A.

Is it possible to use a temp table like this in a data source?  If so is there a trick I'm missing?

These steps helped me:

Write the final result set into a table.
Script that table as CREATE into a new New Query Editor Window.
Remove everything except the open and close brackets that define the columns.
Wrap that into another pair of brackets.
Recompose the calling of your SP from

exec p_MySPWithTempTables ?, ?

into

exec p_MySPWithTempTables ?, ? with result sets
(
    (
        ColumnA int,
        ColumnB varchar(10),
        ColumnC datetime
    )
)


Do the searching and inserting capability of Sql Server 2008 engine in a table decreases with an increase in total rows in a table? I am developing a database with multiple tables. One of its tables will have a total of 50.05GB of data inserted as rows. Do the searching and inserting capability of Sql Server 2008 engine in a table decreases with an increase in total rows in a table? Is there per row data size limit?

Do the searching and inserting capability of SQL Server 2008 engine in
  a table decreases with an increase in total rows in a table?

Yes the performance will eventually be effected but you can counter this with the addition of suitable indexes. Indexes will be easily be sufficient for managing the performance of tables that are ~50GB. If you get a lot more data in the future columnstore indexes and partitioning may be worth a look but they do require the Enterprise Edition of SQL Server. Columnstore indexes would require and upgrade to SQL Server 2012 or later.

  Is there per row data size limit?

A table can contain a maximum of 8,060 bytes per row but VARCHAR and NVARCHAR columns can be stored off row to provide more space. More details here


Why is SQL Server Express creating twenty users on my system? I was taking a look into Computer Management->Users and found that SQL Server Express has created a lot of users for me.

Why? Is my system infected?

The descriptions all say, "Local user account for execution of R scripts in SQL Server instance SQLEXPRESS".

Do you have installed the R- and Machine Learning services? According to the documentation below those are generated to run external scripts of those services in an isolated environment. So I would say they are normal if you install those components.

Those are part of the Advanced Services Variant of the SQL Express Edition.

From the documentation: Modify the user account pool for SQL Server machine learning

  By default, the user account pool contains 20 user accounts. In most cases, 20 is more than adequate to support machine learning tasks, but you can change the number of accounts.

  In a default instance, the individual accounts are named MSSQLSERVER01 through MSSQLSERVER20.
  For a named instance, the individual accounts are named after the instance name: for example, MyInstanceName01 through MyInstanceName20.


Is varchar(x) as fast as `text CHECK ( char_length(x) )`? In an Twitter exchange, Simon West asks to Brandur,

  Out of interest, why do use email  TEXT CHECK (char_length(email) <= 255) rather than email VARCHAR(255)? Not a pattern I've seen before

Brandur responds,

  Excellent question!

  (1) VARCHAR and TEXT are equally performant in Postgres (see the "tip" box at https://www.postgresql.org/docs/current/static/datatype-character.html …).
  .

  (2) If you ever want to change the length, ALTER TABLE requires an exclusive lock (see https://www.postgresql.org/docs/current/static/sql-altertable.html …). Changing CHECK is instant.
  When answering a question that called into question text CHECK (char_length(email) <= 255) vs varchar(255)

Is the first claim (bolded) of the two claims made strictly true?

If interested in the second claim check out this question.

No, it's not strictly true. Here is what the linked docs say,

  There is no performance difference among these three types, apart from increased storage space when using the blank-padded type, and a few extra CPU cycles to check the length when storing into a length-constrained column. While character(n) has performance advantages in some other database systems, there is no such advantage in PostgreSQL; in fact character(n) is usually the slowest of the three because of its additional storage costs. In most situations text or character varying should be used instead.

So essentially

There is no reason to use varchar over text when the column is unconstrained. This is not true in some databases, namely ones that implement schemes like VARCHAR(max).
When it's constrained in a column, it's slower. There is a reason to use varchar(x) over text CHECK ( length(x) <= x)

Example

You can easily see this,

\timing 1

CREATE TABLE foo (
  x varchar(255) NOT NULL
);

CREATE TABLE bar (
  x text NOT NULL
    CHECK (char_length(x) <= 255)
);

INSERT INTO foo SELECT 'x' FROM generate_series(1,1e6);
INSERT INTO bar SELECT 'x' FROM generate_series(1,1e6);

INSERT INTO foo SELECT 'x' FROM generate_series(1,1e6);
INSERT INTO bar SELECT 'x' FROM generate_series(1,1e6);

INSERT INTO foo SELECT 'x' FROM generate_series(1,1e6);
INSERT INTO bar SELECT 'x' FROM generate_series(1,1e6);

INSERT INTO foo SELECT 'x' FROM generate_series(1,1e6);
INSERT INTO bar SELECT 'x' FROM generate_series(1,1e6);

Note, char_length and length are the same here. They internally call textlen.

Results

test=# INSERT INTO foo SELECT 'x' FROM generate_series(1,1e6);
Time: 1156.529 ms
test=# INSERT INTO bar SELECT 'x' FROM generate_series(1,1e6);
Time: 1268.869 ms
test=# INSERT INTO foo SELECT 'x' FROM generate_series(1,1e6);
Time: 1107.869 ms
test=# INSERT INTO bar SELECT 'x' FROM generate_series(1,1e6);
Time: 1283.043 ms
test=# INSERT INTO foo SELECT 'x' FROM generate_series(1,1e6);
Time: 1121.788 ms
test=# INSERT INTO bar SELECT 'x' FROM generate_series(1,1e6);
Time: 1238.194 ms
test=# INSERT INTO foo SELECT 'x' FROM generate_series(1,1e6);
Time: 1116.421 ms
test=# INSERT INTO bar SELECT 'x' FROM generate_series(1,1e6);
Time: 1248.272 ms

In each case, the insert into bar is slower

Type-constraints rechecked

I would have though too that the checks would have carried over to tables using the same types. This kind of optimization may be possible some day, but alas no joy,

test=# CREATE TABLE foo2 ( LIKE foo INCLUDING ALL );
Time: 7.143 ms
test=# CREATE TABLE bar2 ( LIKE bar INCLUDING ALL );
CREATE TABLE
Time: 10.762 ms
test=# INSERT INTO foo2 TABLE foo;
Time: 3613.517 ms
test=# INSERT INTO bar2 TABLE bar;
Time: 4061.650 ms

Though the varchar form is still noticeably faster.

Other Notes

As it relates to emails the subject of the tweet, this post may also be useful.


Changing collation type database-wide in MySQL How do I retroactively update the collation type database wide without dropping and recreating it?

I accidentally created a database without specifying UTF8 as the default collation type. I then went and populated it with several gigs of data.

I know the ALTER commands to do this for the database/tables/columns individually...but I have dozens of tables, each with dozens of columns. Is there a simple non-tedious way of converting all these to use UTF8 collation?

I found an answer on this website: http://kb.siteground.com/article/How_to_change_the_collation_for_all_tables_in_db_to_UTF8.html

I cleaned up the code to print out better from a linux shell and added the $new_charset and $new_collation variables to the alter database statement.

vi change_collation_everything.php

<?php

// Configuration Section
$server = 'localhost'; //probably localhost but change if required
$username = 'root';
$password = 'password';
$database = 'database';
$new_charset = 'latin1';
$new_collation = 'latin1_general_cs';

// Connect to database
$db = mysql_connect($server, $username, $password); if(!$db) die("Cannot connect to database server -".mysql_error());
$select_db = mysql_select_db($database); if (!$select_db) die("could not select $database: ".mysql_error());

// change database collation
mysql_query("ALTER DATABASE $database DEFAULT CHARACTER SET $new_charset COLLATE $new_collation");

// Loop through all tables changing collation
$result=mysql_query('show tables');
while($tables = mysql_fetch_array($result)) {
    $table = $tables[0];
    mysql_query("ALTER TABLE $table DEFAULT CHARACTER SET $new_charset COLLATE $new_collation");

    // loop through each column changing collation
    $columns = mysql_query("SHOW FULL COLUMNS FROM $table where collation is not null");
    while($cols = mysql_fetch_array($columns)) {
        $column = $cols[0];
        $type = $cols[1];
        mysql_query("ALTER TABLE $table MODIFY $column $type CHARACTER SET $new_charset COLLATE $new_collation");
        print "changed $table collation of $column to $new_collation\n";
    }

    print "changed collation of $table to $new_collation\n";
}
print 'The collation of your database has been successfully changed!\n';
?>

I tested the answer on my own database changing from charset latin7 to latin1 and latin7_general_cs to latin1_general_cs for the collation.

I chose _cs instead of _ci for the collation because if you choose _ci which is the default collation for charset latin_1, the collation no longer shows up in the show create table tablename column output.

Try it out

$> php change_collation_everything.php

You can see all of the available charsets and their corresponding collations with the show collation sql query


Indexes file damaged, was on ramdisk I have put some of my indexes into a filegroup that contains one file, that file is on the ramdisk, the performance is great, but the problem is that the file was deleted incorrectly (the file contains only index information). I tried to delete that file from database properties, but an error occured:

  Alter failed for Database 'xxxxxx'.  (Microsoft.SqlServer.Smo)

  An exception occurred while executing a Transact-SQL statement or
  batch. (Microsoft.SqlServer.ConnectionInfo)

  The operating system returned error 21(failed to retrieve text for
  this error. Reason: 15105) to SQL Server during a write at offset
  0x0000002c668000 in file 'R:\DBIndexes.mdf'. Additional messages in
  the SQL Server error log and system event log may provide more detail.
  This is a severe system-level error condition that threatens database
  integrity and must be corrected immediately. Complete a full database
  consistency check (DBCC CHECKDB). This error can be caused by many
  factors; for more information, see SQL Server Books Online. (Microsoft
  SQL Server, Error: 823)

I have no problem to re-build the indxes, but I need to fix the database!!

I deleted all of the indexes that stores in that file, And tried to delete the file group, but the same error occured

*UPDATE*

When I detached the database, I couldn't re-attache it, it now saying:
Unable to open the physical file "R:\DBIndexes.mdf". Operating system error 2: "2(failed to retrieve text for this error. Reason: 15105)".

It was working fine before detaching it, but the small problem was in deleting that orphan file!!!

Once the database is detached you will probably not be able to attach the data files.  Since the file operation that occurred is an almost guaranteed data corruption I think your best option here is to perform a restore from a native SQL server backup or export of some kind, or recover any snapshots you might have taken of the environment.


Is this a standard way to design a database? I recently learned about how relationships are defined in the database at work, and was wondering if this is a standard practice.

Let's say we have two processes: Process A, and Process B.  Process B depends on the results from Process A, so there is a relationship that needs to be defined between a Process B run and a Process A run.  This is how the relationship is defined:

TableProcessA:
Id

and

TableProcessB:
Id
ProcessAId

Now, up to this point, things make sense to me, but then things get a little strange to me and my understanding of table design.  Whenever a row is created in TableProcessA or TableProcessB, a function is called that creates a globally unique id for each.  So basically, all of the Id fields in TableProcessA and TableProcessB will not contain any matches because the Id's are not just unique to its table, but to the entire database.

My question is, how standard is this?  I was brought up on the idea that each table should simply have an autoincrementing id that is unique just to the table and not the entire database.

This isn't that weird a practice.  Those are called GUIDs, or Globally Unique IDs.  The idea is that, given a GUID, you can tell exactly what piece of data the id belongs to because it will be unique everywhere.  GUIDs are best used when you will be merging different sources of similar data; for example inventory for different stores.

I would do some research and find out as to why this is a common practice in your environment.  Maybe it's needed, maybe it's not.


How to remove unnecessary symbol in the column in SQL Server 2008 my database got some data with unnecessary symbol in front it like ""

How do I remove the front symbol. I try to do like this but it do not work.

SELECT DISTINCT REPLACE(NEW,'.','')
FROM [FixedAsset Control].[dbo].[NewViewReport]

Can anyone help me on that.

If it is just one symbol for each value returned, then you could do this

It seems like you have some trailing spaces, that is why RTRIM is used, or DATALENGTH() if they need to be preserved. Thanks to   @Denis Rubashkin for pointing that out.

Solution where the spaces are removed

SELECT DISTINCT RIGHT(RTRIM(NEW),(LEN(RTRIM(NEW)) - 1))
FROM [FixedAsset Control].[dbo].[NewViewReport]; -- RTRIM takes care of the trailing spaces

Solution without removing the spaces:

SELECT DISTINCT RIGHT(NEW,((DATALENGTH(NEW) / 2) - 1))
FROM [FixedAsset Control].[dbo].[NewViewReport]; -- Datalength also counts spaces

And if you want to update the column after you validated that the result is correct:

UPDATE[FixedAsset Control].[dbo].[NewViewReport]
SET NEW = RIGHT(RTRIM(NEW),(LEN(RTRIM(NEW)) - 1));

Or

UPDATE[FixedAsset Control].[dbo].[NewViewReport]
SET NEW = RIGHT(NEW,((DATALENGTH(NEW) / 2) - 1));

Testing

CREATE DATABASE [FixedAsset Control]
USE [FixedAsset Control]
GO
CREATE TABLE  [dbo].[NewViewReport] ([NEW] nvarchar(255));

INSERT INTO [dbo].[NewViewReport]([NEW])
VALUES('·OUTGOING QC OFFICE');

SELECT DISTINCT RIGHT(RTRIM(NEW),(LEN(RTRIM(NEW)) - 1))
FROM [FixedAsset Control].[dbo].[NewViewReport];

Result

(No column name)
OUTGOING QC OFFICE


Moving the IBM Informix IFMXDATA directory to another machine I'm a total newbie when it comes to Informix DBs, and I'm stuck with what looks like files that contain an Informix database. I'd like to know if it's possible to add or import these files into an existing installation of Informix.

Probably I should explain myself a bit better so...I have the following Dir\File structure:

C:\IFMXDATA\somename\dbditte_dat.000
C:\IFMXDATA\somename\dbditte_dat.001
C:\IFMXDATA\somename\dbimage_dat.000
C:\IFMXDATA\somename\dbtemp_dat.000
C:\IFMXDATA\somename\llogdbs_dat.000
C:\IFMXDATA\somename\llogdbs_dat.001
C:\IFMXDATA\somename\rootdbs_dat.000

The destination machine is running a much newer Informix version (v12.0), which, I guess, won't make this any easier.
These files are pretty large (500Mb - 2Gb) and, I hope, contain data from an Informix 3.85 installation made quite some time ago. These files is all I have, to restore this DB on another machine.
I already tried by simply replacing them on the destination computer, which didn't work, because the rootsdb_dat file gets overwritten, and I guess it's where the info about tables, and so on, is stored.

After prolonged google-ing I was unable to find information about how I could achieve this, obviously any sane person would export the data properly and then re-import it, which, sadly is not an option right now.

All I need is to access the data, I don't care if the DB name or connection method changes.
Is there any way to import the tables from these files?
Any way to fool the Informix installation into accepting these files?

EDIT
When I try to replace newly generated files on the destination machine with those from the source machine (those that I need), the Informix service crashes and the log states:

17:55:31  Assert Warning: I/O bad request chunk 1, pagenum 87248, pagecnt 1
17:55:31  IBM Informix Dynamic Server Version 12.10.TC3DE
17:55:31   Who: Session(1, informix@WDKGPMOTORS, 0, 00000000)
        Thread(7, main_loop(), 0, 1)
        File: rsbuff.c Line: 5721
17:55:31   Action: Please notify IBM Informix Technical Support.
17:55:31  stack trace for pid 3692 written to C:\PROGRA~1\IBMINF~2\tmp\af.3efbef3
17:55:32   See Also: C:\PROGRA~1\IBMINF~2\tmp\af.3efbef3
17:55:34  I/O bad request chunk 1, pagenum 87248, pagecnt 1
17:55:35  oninit: Fatal error in shared memory initialization

When I try to run oninit -v i get:

Initializing log/checkpoint information...FAILED
oninit: Fatal error in shared memory initialization

Thank you!

First, make sure this chunks/datafiles are from Windows OS too.
Isn't compatible read a datafile froma different plataform (windows VS Unix VS Linux)

You cannot just move the chunks/datafiles and expected the engine just re-adapt to new path. There internal pointers at theirs configurations where the path is saved and need some administration steps to change.
Basically , you can do this only at a Backup / Restore, using ontape or onbar utilities.

Copying the files can be considered like a cold backup and this way is possible to rename the path.

For that you will need to use the ontape -p -e -rename ... command.
More details, check the manual online .

If your datafiles is from older version which is installed at the target server, I'm not sure if this restore will work because you are doing a upgrade at same time...

Maybe you need install the same version of the engine used to copy the files, restore them with ontape , shutdown the engine, install the new version and upgrade ...


Find player's latest match whether player was the winner or loser I have a table of match results with the following fields (and many more):

id
date_played
begin_time
end_time
winner_id   <-----foreign key into user.id
win_start_hcp
win_end_hcp
loser_id    <-----foreign key into user.id
loser_start_hcp
loser_end_hcp

I want to find the win_end_hcp for the last match played by user.id=X, whether he was a winner or a loser of that match.

Are you looking for this,

select top(1)  isWinner,  win_end_hcp
from
(
select 1 isWinner, end_time,  win_end_hcp from table1
where winner_id=X
union ALL
select 0 isWinner, end_time,  win_end_hcp from table1
where loser_id=X
)t4
order by t4.end_time desc


Best way to design tournament database I'm creating a webpage for placing bets on all matches of the upcoming Euro 2012 football tournament. Need some help deciding what approach to take for the knockout phase.

I have created a mockup below, which i'm pretty satisfied with when it comes to storing the results of all "known" group stage matches. This design makes it very easy to check if a user has placed a correct bet or not.

But what is the best way to store the quarter and semi finals? Those matches depend on the outcome in the group stage.

One approach I thought of was adding ALL matches to the matches table, but assign different variables or identifiers to the home/away teams for the matches in the knockout phase. And then have some other table with those identifiers mapped to teams... This could work, but does not feel right.

I think that using the team ID is the right way to go.  Another level of abstraction for all of the finals rounds just adds unnecessary complexity for not much benefit other than pre-loading the matches table with data.

The data structure looks pretty solid to support this.  The quarter and semi finals would need to be added to the matches table once the initial match results are in.  If the matches are assigned randomly then this is a manual operation, however, if they are in a particular order...

   A
match 1 -----+
   B         A
          match 5 -----+
   C         C         |
match 2 -----+         |
   D                   A
                    match 7
   E                   F
match 3 -----+         |
   F         F         |
          match 6 -----+
   G         G
match 4 -----+
   H

...then this could possibly be done with a query.  Again, the complexity of the query may not be worth the effort depending on the number of teams


Filtering SQL Search Query How can I filter results in a SQL query?

Let’s say I have Products A-Z, and each product has a monthly observation for X Months, but for most products the value in the observation stays the same for that particular product, but I need to find the products that have observations with a new value, the moment the value in the observation changes. Also, you can have a double to quadruple entry per month if the value doesn’t only change, but coexists, and I need to find every instance that happens for a product.

Example in pic:

>Product    Value    Date
>---------- -------- --------
>A          123      Jan-99
>A          123      Feb-99
>A          123      Mar-99
>A          123      Apr-99
>A          123      May-99
>A          456      Jun-99
>E          456      Jan-99
>E          456      Feb-99
>E          654      Apr-99
>E          987      Apr-99
>E          873      Apr-99
>E          987      May-99
>E          456      Jun-99
>Z          852      Jan-99
>Z          852      Feb-99
>Z          852      Mar-99
>Z          963      Apr-99
>Z          852      Apr-99
>Z          852      Jun-99

For this data set I would try and use a function like ROW_NUMBER() to partition the data into blocks that I would like to capture and then filter.

For changing values I would try something like

SELECT *
  FROM(
    SELECT * ,
           ROW_NUMBER( )OVER( PARTITION BY t.Product ,
                                           t.[Value] ORDER BY t.Product )AS  RowNumber
           FROM dbo.Table_1 t ) AS Result
  WHERE RowNumber = 1;

 Product    Value   Date    RowNumber
 A          123     Jan-99  1
 A          456     Jun-99  1
 E          456     Jan-99  1
 E          654     Apr-99  1
 E          873     Apr-99  1
 E          987     Apr-99  1
 Z          852     Apr-99  1
 Z          963     Apr-99  1

This should return unique values for each product, and then you should be able to quickly see where changes have occurred.

edit: LAG would be another great way to do this as mentioned by Colin.


Alter a large mysql table 700 gb The production table I want to alter is around 700 Gb. I want to remove a FK from that table. Mysql version is 5.1.61-log.

I found following options:

pt-online-schema-change: alters a table’s structure without blocking reads or writes. This looks most efficient in terms alteration time. This also takes care of server load by dividing table copy into chunks and pauses data copy process in case of high load on the server.

The only problem with tool for me is available space on server is 450 GB and there is no room for another drive. This tool works on a copy of the table which is around 700 GB.

Upgrade to Mysql 5.6: With ONLINE DDL feature of 5.6, most of the ALTER types won’t block writes to a table that is being changed. Also it supports Add/Drop a foreign key constraint without the need of full table copy.

But I am not sure how it takes care of server load and how much time it going to take for alteration.

Is there any other option like pt-online-schema-change which doesn't have to copy table? Any experience/comments on ONLINE DDL of Mysql 5.6?

Thanks in advance!

A little late, but here we go...

If you can't add more disk space and can't promote a replica I think your only option (short of adding more disk space) is upgrading to MySQL 5.6 and using the online alter feature. The third-party tools that I know of all require a second copy of the table to be created before performing the swap. As you mentioned, the good news is that as per the Summary of Online Status for DDL Operations dropping a FK constraint should not require a full copy to be made.

I've had moderate success using the online alter feature but there are a couple caveats to be aware of:

There is no throttling of the operation and this can heavily load the server
Due to the above this can cause large replication spikes to replicas
Adding/dropping a column can cause primary key errors

The last caveat doesn't apply in this situation but it's a big gotcha.

I'd also be sure to carefully read the known limitations of the online alter feature too.


MySQL 5.1: change datadir variable value in my.cnf Below is my system configuration:

OS: RHEL5
MySQL: 5.1.73
Default datadir=/var/lib/mysql/

But, the above directory has space crunch. So, I decided to change the path. I found /u000 directory has enough space. So, I created /lib/mysql/ under /u000 and assign permission (chmod) as 0777.
Then after, I changed the below values in mysql configuration file: /etc/my.cnf:

 innodb_data_home_dir = /u000/lib/mysql
 innodb_data_file_path = ibdata1:10M:autoextend
 innodb_log_group_home_dir = /u000/lib/mysql
 log_error = /u000/lib/mysql/rpc4939.err
 general_log_file = /u000/lib/mysql/rpc4939.log
 slow_query_log_file = /u000/lib/mysql/rpc4939-slow.log
 datadir = /u000/lib/mysql/
 pid_file = /u000/lib/mysql/rpc4939.pid

Then, tried to restart mysql service but it failed.
When, I changed the datadir as  datadir = /var/lib/mysql/ it started working.

Can I know Why am I unable to change the datadir?
Is there any special way to change the datadir?
log_error value is always pointing to /var/lib/mysql/ instead /u000/lib/mysql/. How this variable can be changed?

Changing the datadir requires the following steps: 1) Stopping the service completely mysqladmin shutdown / service mysql stop. 2) Changing the options on the config file for everything that points to the old location to the new one 3) moving the actual files and directories at the old datadir manually to the new location with cp/rsync (make sure that it has the right permissions in the end, normally owned by the mysql user) 4) Starting the server again
You can check that new values on my.cnf have effect by executing: mysqld --print-defaults which will confirm you that the options changed are actually applied and that there are no syntax errors on the file.


Lookup performance of Numeric vs String I'm working on a project that is using the instagram key format.

TL;DR 64-bit integer ids.

They'll be used for lookups, and we also like them for sorting and batching since they will naturally sort by creation time.

The values are between 2^63 and 2^64, so (just) too big to fit inside a BIGINT.

So it seems our options for storage are numeric(20) or varchar. varchar is not as ideal since we'd have to zero pad them for sorting to work, but would there be a performance hit to using a numeric for lookups?

numeric is bigger and slower than bigint (8 bytes) in every respect. Unfortunately, like you said, bigint is just not big enough. Maximum is 2^63-1 = 9223372036854775801, 19 decimal digits.
varchar or text (same thing internally) are bigger and slower, yet. Plus, character types are encumbered with COLLATION rules unless explicitly defined without.
SELECT pg_column_size(numeric '18446744073709551616')  -- 16 bytes in RAM
     , pg_column_size(text    '18446744073709551616')  -- 24 bytes in RAM

(But 13 / 21 bytes on disk.) See:
db<>fiddle here
numeric saves additional space for trailing zeroes, which text cannot.
numeric disallows non-numbers from being stored and enforces some sanity out of the box.
Faced with your requirements, choose numeric and never look back.
Related:

What is the overhead for varchar(n)?

Or you could install the extension pguint by Peter Eisentraut, one of the core hackers of the Postgres project.
Be sure to read the readme first. The extension provides several additional (most of them unsigned) integer types. You might just extract uint8 (unsigned 64-bit integer) and ditch the rest to avoid over-crowding the type system.


Grant execute on specific type in Oracle I have created a package and defined a type for collections as follows:

create or replace package <schema>.<package> as

-------------------------------------
   type <type> is table of <something>;
-------------------------------------

...
   function <func>(
      ...
      <param> in <type>,
      ...
    )
      return pls_integer;
...

I have a user that has to call this function but can't because apparently they don't have the permission to execute type collection. I could solve it by

GRANT EXECUTE ANY TYPE TO <user>;

but I am bound by security protocols that says no user has to have access to any data, schema, blueprint or object unless they certainly have to. So I want to give the user explicit grant on specific type <type>, but calling the following statement results in error:

GRANT EXECUTE TYPE ON <schema>.<package>.<type> to <user>;

Error report -
SQL Error: ORA-00990: missing or invalid privilege
00990. 00000 -  "missing or invalid privilege"
*Cause:
*Action:

You need to grant EXECUTE on package itself. If security is your main concern you may create a new package that has just types, but you cannot specify that a given user can access only some objects defined in package specification - either all or none.


Stored procedure with case statement for 'AND' based on parameter value I have a table in my db that stored both successful and failed log-in attempts. I am creating a stored procedure that allow us to delete records which are older than X days old. So far so good, but I want to raise it a notch (or two) and allow us to specify whether to delete records on whether the [Success] column is true, false or both. I am having some issues with the concatenation of the script that needs to be executed, though.

Here is what I did so far:

-- CREATE PROCEDURE [dbo].[sp_delete_log_attempts]
DECLARE @backDays INT = 1 -- Default to 30 days (one test finishes)
DECLARE @successArg BIT = NULL -- default to both true and false success logins
DECLARE @successAnd VARCHAR(50)
DECLARE @query VARCHAR(MAX)

SET @successAnd = CASE
WHEN @successArg = 'true' THEN
    'AND [Success] = ''true'''
WHEN @successArg = 'false' THEN
    'AND [Success] = ''false'''
ELSE
    'AND [Success] = ''true'' OR [Success] = ''false'''
END

PRINT @successAnd -- just for debugging purposes

SET @query = 'SELECT * FROM [audit].LoginAttempt WHERE [TimeStamp] <= DATEADD(day,-' + @backDays + ', GETDATE())'
EXEC @query

At this point I am just trying to select the rows based on the @backDays variable, but I cannot concatenate the @query string with the variable. Not sure of what I am doing wrong here. I am fairly new to dynamic queries, though.

I don't think you even need dynamic SQL here. You can use variables in the query.

SELECT *
       FROM [audit].[LoginAttempt]
       WHERE [TimeStamp] <= dateadd(day, -1 * @backDays, getdate())
             AND CASE
                   WHEN @successArg IS NOT NULL
                     THEN (SELECT 1
                                  WHERE [Success] = @successArg)
                   ELSE 1
                 END = 1;

(Not tested as no sample data was provided. Just to demonstrate the idea.)

If you look close at it, you'll see, that the entire CASE ... END is the left operand of an = operation. The right side of the operation is 1.

Unfortunately SQL Server knows no Boolean type we could have directly returned from the CASE ... END. (Others, e.g. PostgreSQL do.) So we have to trick it using an expression it can use for a Boolean operation. That's why we use the = operation.

Now we need a way of making this operation evaluate to true when the conditions we actually want to test are met. So the idea is, if we come to the decision, that they are met, we return one designated value for our left side of the =. On the other side, we just use that value literally. The = will be true then. If our conditions don't meet, we return any other value for the left side, and the = won't be true. As a value to indicate the conditions were met, lets choose the 1. It's close to a representation of a Boolean we might have in mind. (But we could have chosen nearly anything (NOT NULL, otherwise we had to change the = operation to IS NULL).).

So how can we make our left expression to return 1, when the conditions are met?

Well, we can use a CASE ... END statement to return a value based on certain conditions. You already know the CASE ... END. It's somewhat similar to a switch or if else constructs in C-like languages (or with different names in other procedural languages).

What we need to test is if the input variable @successArg is null or not. If it's null, that means the caller doesn't care, whether the logged login attempts were successful or not. Otherwise the value of @successArg indicates whether he wants only successful logins (@successArg = 1) or only unsuccessful (@successArg = 0). This will give us to cases on top: Either ignore the successfulness or take it into account. Hence we have two (main) branches in our CASE ... END branching about @successArg IS NOT NULL;

Let's start with the easier case, when @successArg IS NULL. That's the 'ELSE' branch. Here, we don't care about if the login attempt was successful or not. Being successful or not successful (think of it a Boolean expression) is always true, for any row. So we just return our indicator, that this was a match, 1.

When @successArg IS NULL, @successArg holds the value, the rows must have in the column [Success]. This is given only if @successArg = [Success]. As already mentioned, @successArg = [Success] alone won't be something SQL Server could handle in that context. So we need our true indicating value 1 to be returned when @successArg = [Success] for the current row, something else otherwise.

One way of doing this would be another, inner CASE ... WHEN or we might use a correlated subquery. It's "correlated" as it uses values of columns from the current row of the outer query. And it's "sub", well because it's "sub", viewed from the position of the outer query. Furthermore, we use the feature in SQL Server that a SELECT with no FROM will produce one record if the WHERE of that query is true (or if there is none), otherwise an empty set. So let's produce a record with one column holding 1 when @successArg = [Success]. Such a record, with only one column, will implicitly be casted to a scalar if needed, hence it's suitable for our = operation outside. When @successArg <> [Success], that subquery will produce an empty set. The empty set in that scalar context will be implicitly casted to NULL. As NULL = 1 isn't true, this will do it for us.

One side note on this: It also can be done without a CASE ... END but simply a Boolean expression:

SELECT *
       FROM [audit].[LoginAttempt]
       WHERE [TimeStamp] <= dateadd(day, -1 * @backDays, getdate())
             AND (@successArg IS NULL
                   OR [Success] = @successArg);

That follows our thoughts from above. In case @successArg IS NULL we want "true" regardless of anything else. Otherwise we only want "true" when [Success] = @successArg. In any other case "false". (Note: The parenthesis are needed to override the precedence of AND over OR because the previous condition.)

In terms of how the query is executed, hence performance, I don't think this makes a significant difference, if any (but frankly I don't know this for sure). The solution with a CASE ... WHEN though, might be easier to read and therefore maintain. The Boolean only solution, on the other hand, also works in systems, that are not capable of a CASE ... END.


Real-time merge of two databases on different servers One of the requirements our customer has given is to merge two databases running on two different machines in real time. This means that we have to build some sync policy (or use SQL Server tools to manage that). But I have some doubts:

How can I be sure that my databases are totally merged? I mean, we are in a real time environment with n/sec web and local requests, it's impossible to share table updates/merges with the same speed.
Is this a good architecture in spite of a master/slaves one (with an unique DB) topology? I'm pretty sure sync both databases each other will cost too much since our tables store a lot of data in terms of rows and information.
Can anyone provide a good solution to build the best architecture possible?

This sounds like your customer does not entirely understand what is possible with SQL Server. It is also possible that he is confusing SQL Server with Oracle. With Oracle, you can spin up one database and have two database instances running independantly and operating of the same data set.

With SQL Server, the best you are going to probably do is some sort of Merge replication with two application servers and a distribution database. I would re-visit the customer requirements, determine exactly what he wants, not what he thinks he wants, and not how he wants to do it, and then come up with a solution to provide to the customer.

Unless you or your customer are willing to buy a third party product, some form of SQL Server replication is going to have to be used. Here are some links (2008 R2):

Merge replication

Replication overview


Should I install SQL Server 64 bit or 32 bit Sorry if this question is lame.

For production purpose, i wanted to know when to install SQL Server 64 bit package and when to install 32 bit package when the client's PC has: -

32 bit OS and 64 bit Processor
64 bit OS and 32 bit Processor

I am just confused what to install when there is a mix and match in bits of OS and processor.

32 bit OS and 64 bit Processor

If the OS is 32-bit, then your applications would have to be 32-bit as well.

  64 bit OS and 32 bit Processor

You cannot install a 64-bit OS on a 32-bit processor, so this isn't relevant

  when to install SQL Server 64 bit package and when to install 32 bit
  package

SQL Server 2008 R2 is no longer a supported platform, and modern, supported versions of SQL Server are only 64-bit, so the question itself is kind of irrelevent - you should not be installing unsupported versions of SQL Server, however, if you are required to:

On a 32-bit OS, you can only install the 32-bit version of SQL Server.
If you have a 64-bit OS (by definition on a 64-bit processor) then you should install the 64-bit version of SQL Server to take full advantage of available resources.