Running a database without constraints We're looking at utilizing a new proprietary high performance database engine for OLAP and OLTP scenarios. One important aspect of it right now is that it does not support foreign key or other constraints. What is your opinion on whether this limitation should be considered a non-starter for OLTP applications?

Would it be crazy to think about implementing our own referential integrity checks for any inserts, updates, and deletes?

Rolling your own referential integrity checks has the following disadvantages:

Speed - Your own checks will never be as fast as database internal checks.
Completeness- There is always the possibility when you roll your own that you will miss something.

The speed issue is acceptable if the high performance of the database engine can make up for the loss, but the potential for a loss of data integrity using your own checks would still be a concern.


Spatial Query using MySQL 8.0 I have a database in MySQL 5.7 where I create a Lat/Long column using "POINT", then I create a Index on this column with main key as second column. So my database is like

PID, SurveyID, GeoPoint

Basically this is a summarize table where PID and SurveyID makes primary key for table. We do a survey on Animals and this summarize table record which Animal is surveyed at given Geo location on map.

My Index was on column GeoPoint and PID. This table store around 400K record which is summarise from main table that has 2M record for faster execution.

Now, we are planning to upgrade to MySQL 8.0 and we found that it doesn't support Spatial column been index with non-spatial column, so our index was not getting created. This results in our Query now take 8 second instead of 0.6 seconds from older version of MySQL.

More over when I create index only on Spatial Column geopoint, ST_Contains, and MBRContains do not use that Index. Basically our query is as simple as we allow user to draw a Square on Google map and then we use those coordinate to find all Animals in that region.

I am not sure how to solve this issue, as no documentation is found by me for it that help tweaking the query.

Query:

SELECT  PID, count(distinct SurveyID) as totalsurvey
    FROM  locationsummary
    where  st_contains(ST_Envelope(ST_GeomFromText(
         'LineString(137.109375 21.47351753335, 87.890625 -22.411028521559)')),
              geopoint )
    group by  PID

Table:

CREATE TABLE locationsummary (
 PID bigint(20) NOT NULL,
 SurveyID bigint(20) NOT NULL,
 Sitelat float NOT NULL,
 sitelong float NOT NULL,
 geopoint point NOT NULL,
 PRIMARY KEY (`PID`,`SurveyID`),
 SPATIAL KEY `idx_geopoint` (`geopoint`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_bin ROW_FORMAT=COMPACT

Edit: 12th July 2019
Today I try to rerun queries and it is now executing in 2 seconds, still not good though, but some how it become better from last run.

SELECT  PID, count(distinct SurveyID) as totalsurvey
    FROM  locationsummary
    where  st_contains(ST_Envelope(ST_GeomFromText(
         'LineString(137.109375 21.47351753335, 87.890625 -22.411028521559)')),
              geopoint )
    group by  PID

This is slow only because ST_Contains in MySQL does not now, nor has it ever used an index. You have to use MbrContains for that. And you could never put anything on a spatial index, except spatial data.

I see you state in your question MBRContains do not use that Index. I don't believe you. ;)

Note, in PostGIS, ST_Contains will use an index.  And, moreover, you can create a spatial index and add to that index non-spatial data (thanks to btree_gist, and btree_gin).


Informix DB export to Microsoft SQL Server 2016 I have an Informix Database and a Microsoft SQL Server 2016.
My task is to dump the whole Informix Database and export it to the Microsoft SQL Server Database.
I installed the Informix SDK 4.5. I also have installed the ODBC drivers 32bit/64bit. The connection is working fine.

I linked the Informix Server within the Microsoft SQL Server Management Studio, but i was not able to get table information, so i found a workaround in IBMs Documentation.

Now I wantet to try an export with Import and Export Data (64-bit). I chose the data source as IBM Informix OLE DB Provider

Data Destination is SQL Server Native Client 11.0

As a test export i choose some tables. The table Mapping looks fine for me

The next step shows the Type Conversion with an error, but i dont know why.

Then I Run immediatly

[Source Information]
Source Location : sospos@sosposdbtest
Source Provider : Ifxoledbc.4
Cannot locate the mapping file to map the provider types to SSIS types

After that I get errors and warnings.

Warning:

Could not connect source component. Warning 0x80202066: Source - auswahl_hoefken [1]: Cannot retrieve the column code page info from the OLE DB provider. If the component supports the "DefaultCodePage" property, the code page from that property will be used. Change the value of the property if the current string code page values are incorrect. If the component does not support the property, the code page from the component's locale ID will be used.

Error:

Error 0xc02020e8: Source 27 - antrzusatz [157]: Opening a rowset fo ""sospos":"sosgx"."antrzusatz"" failed. Check that the object exists in the database.

I get this Warnings for several tables..

I think there is a problem with the table mapping or something like that, but I'm not sure..

Any ideas?

You might try and check if you can get data out of the Informix database via the linked server.
If the direct query fails, you might have some luck with the use of OPENQUERY

SELECT * FROM OPENQUERY ([name of linked server], 'Query text')

If this works, maybe a select into gets the job done.


query optimization: time intervals In the main, I've got two kinds of time intervals:

presence time and absence time

absence time can be of different types (eg breaks, absences, special day and so on) and time intervals may overlap and / or intersect.

It is not for sure, that only plausible combinations of intervals exist in raw data, eg. overlapping presence-intervals don't make sense, but may exist. I've tried to identify resulting presence-time intervals in many ways now - for me, the most comfortable seems to be the follwing one.

;with "timestamps"
as
(
    select
        "id" = row_number() over ( order by "empId", "timestamp", "opening", "type" )
        , "empId"
        , "timestamp"
        , "type"
        , "opening"
    from
    (
        select "empId", "timestamp", "type", case when "types" = 'starttime' then 1 else -1 end as "opening" from
        ( select "empId", "starttime", "endtime", 1 as "type" from "worktime" ) as data
        unpivot ( "timestamp" for "types" in ( "starttime", "endtime" ) ) as pvt
        union all
        select "empId", "timestamp", "type", case when "types" = 'starttime' then 1 else -1 end as "opening" from
        ( select "empId", "starttime", "endtime", 2 as "type" from "break" ) as data
        unpivot ( "timestamp" for "types" in ( "starttime", "endtime" ) ) as pvt
        union all
        select "empId", "timestamp", "type", case when "types" = 'starttime' then 1 else -1 end as "opening" from
        ( select "empId", "starttime", "endtime", 3 as "type" from "absence" ) as data
        unpivot ( "timestamp" for "types" in ( "starttime", "endtime" ) ) as pvt
    ) as data
)
select
      T1."empId"
    , "starttime"   = T1."timestamp"
    , "endtime"     = T2."timestamp"
from
    "timestamps" as T1
    left join "timestamps" as T2
        on T2."empId" = T1."empId"
        and T2."id" = T1."id" + 1
    left join "timestamps" as RS
        on RS."empId" = T2."empId"
        and RS."id" <= T1."id"
group by
    T1."empId", T1."timestamp", T2."timestamp"
having
    (sum( power( 2, RS."type" ) * RS."opening" ) = 2)
order by
    T1."empId", T1."timestamp";

see SQL-Fiddle for some demo data.

The raw data exist in different tables in form of "starttime" - "endtime" or "starttime" - "duration".

The idea was to get an ordered list of every timestamp with a "bitmasked" rolling sum of open intervals at each time to estimate presence time.

The fiddle works and gives estimated results, even if startimes of different intervals are equal. No indices are used in this example.

Is this the right way to achieve questioned task or is there a more elegant way for this?

If relevant for answering: amount of data will be up to several ten-thousand datasets per employee per table. sql-2012 is not available to calculate a rolling sum of predecessors inline in aggregate.

edit:

Just executed the query against larger amount of testdata ( 1000, 10.000, 100.000, 1 million) and can see that runtime increases exponentially. Obviously a warning flag, right?

I changed the query and removed aggregation of rolling sum by a quirky update.

I've added an auxiliary table:

create table timestamps
(
  "id" int
  , "empId" int
  , "timestamp" datetime
  , "type" int
  , "opening" int
  , "rolSum" int
)

create nonclustered index "idx" on "timestamps" ( "rolSum" ) include ( "id", "empId", "timestamp" )

and I moved calculating rolling sum to this place:

declare @rolSum int = 0
update "timestamps" set @rolSum = "rolSum" = @rolSum + power( 2, "type" ) * "opening" from "timestamps"

see SQL-Fiddle here

The runtime decreased to 3 sec regarding 1 million entries in the "worktime"-table.

Question stays the same: What's the most effective way to solve this?

I can't answer your question as to the absolutely best way. But I can offer a different way of solving the problem, which may or may not be better. It has a reasonably flat execution plan, and I think it will perform well. (I'm eager to know so share the results!)

I apologize for using my own syntax style instead of yours--it helps query wizardry come to me when everything lines up in its usual place.

The query is available in a SqlFiddle. I threw in an overlap for EmpID 1 just to be sure I had that covered. If you eventually find that overlaps cannot occur in presence data, then you can remove the final query and the Dense_Rank calculations.

WITH Points AS (
  SELECT DISTINCT
    T.EmpID,
    P.TimePoint
  FROM
    (
      SELECT * FROM dbo.WorkTime
      UNION SELECT * FROM dbo.BreakTime
      UNION SELECT * FROM dbo.Absence
    ) T
    CROSS APPLY (VALUES (StartTime), (EndTime)) P (TimePoint)
), Groups AS (
  SELECT
    P.EmpID,
    P.TimePoint,
    Grp =
      Row_Number()
      OVER (PARTITION BY P.EmpID ORDER BY P.TimePoint, X.Which) / 2
  FROM
    Points P
    CROSS JOIN (VALUES (1), (2)) X (Which)
), Ranges AS (
  SELECT
    G.EmpID,
    StartTime = Min(G.TimePoint),
    EndTime = Max(G.TimePoint)
  FROM Groups G
  GROUP BY
    G.EmpID,
    G.Grp
  HAVING Count(*) = 2
), Presences AS (
  SELECT
    R.*,
    P.Present,
    Grp =
       Dense_Rank() OVER (PARTITION BY R.EmpID ORDER BY R.StartTime)
       - Dense_Rank() OVER (PARTITION BY R.EmpID, P.Present ORDER BY R.StartTime)
  FROM
    Ranges R
    CROSS APPLY (
      SELECT
        CASE WHEN EXISTS (
          SELECT *
          FROM dbo.WorkTime W
          WHERE
            R.EmpID = W.EmpID
            AND R.StartTime < W.EndTime
            AND W.StartTime < R.EndTime
        ) AND NOT EXISTS (
          SELECT *
          FROM dbo.BreakTime B
          WHERE
            R.EmpID = B.EmpID
            AND R.StartTime < B.EndTime
            AND B.StartTime < R.EndTime
        ) AND NOT EXISTS (
          SELECT *
          FROM dbo.Absence A
          WHERE
            R.EmpID = A.EmpID
            AND R.StartTime < A.EndTime
            AND A.StartTime < R.EndTime
        ) THEN 1 ELSE 0 END
    ) P (Present)
)
SELECT
  EmpID,
  StartTime = Min(StartTime),
  EndTime = Max(EndTime)
FROM Presences
WHERE Present = 1
GROUP BY
  EmpID,
  Grp
ORDER BY
  EmpID,
  StartTime;

Note: performance of this query would be improved you combined the three tables and added a column to indicate what kind of time it was: work, break, or absence.

And why all the CTEs, you ask? Because each one is forced by what I need to do to the data. There's an aggregate, or I need to put a WHERE condition on a windowing function or use it in a clause where windowing functions aren't allowed.

Now I'm going to go off and see if I can't think up another strategy for accomplishing this. :)

For amusement I include here a "diagram" I made to help solve the problem:

------------
   -----------------
                ---------------
                           -----------

    ---    ------   ------       ------------

----   ----      ---      -------

The three sets of dashes (separated by spaces) represent, in order: presence data, absence data, and the desired result.


Dynamic change to innodb_flush_log_at_trx_commit This is related to this question. It does help to get better performance for InnoDB tables.

According to MySQL manual, innodb_flush_log_at_trx_commit is a global dynamic variable. Thus, I can change it using SET GLOBAL command and it seems to be working.

mysql> SET GLOBAL innodb_flush_log_at_trx_commit=2;
Query OK, 0 rows affected

mysql> SHOW VARIABLES LIKE 'innodb_flush_log_at_trx_commit';
+--------------------------------+-------+
| Variable_name                  | Value |
+--------------------------------+-------+
| innodb_flush_log_at_trx_commit | 2     |
+--------------------------------+-------+
1 row in set

But, it did not make the actual MySQL setting changed.
When I updated my.cnf and restarted the MySQL server,  it did work. So, I cannot change the global variable at run time?

I prefer the default value innodb_flush_log_at_trx_commit=1, but I need to change it to 2 before I run a restore process for a large database to get faster.
But when the process done, I want to change the value back to 1.
Is it possible to do this at run time?

I don't have access to my.cnf on my shared hosting server.

While I agree with Rolando's recommendation to change innodb_flush_method, I wasn't 100% clear what you meant by:

  it did not make the actual MySQL setting changed

I want to point out the caveat that making a change to the GLOBAL variable affects any new connections, but does not modify the current session (emphasis mine):

  The global variable change does not affect the session variable for any client that is currently connected (not even that of the client that issues the SET GLOBAL statement).

So to check that:

mysql> SHOW GLOBAL VARIABLES LIKE 'innodb_flush_log%';
+--------------------------------+-------+
| Variable_name                  | Value |
+--------------------------------+-------+
| innodb_flush_log_at_trx_commit | 1     |
+--------------------------------+-------+
1 row in set (0.00 sec)

mysql> SHOW SESSION VARIABLES LIKE 'innodb_flush_log%';
+--------------------------------+-------+
| Variable_name                  | Value |
+--------------------------------+-------+
| innodb_flush_log_at_trx_commit | 1     |
+--------------------------------+-------+
1 row in set (0.00 sec)

mysql> SET GLOBAL innodb_flush_log_at_trx_commit=2;
Query OK, 0 rows affected (0.00 sec)

mysql> SHOW GLOBAL VARIABLES LIKE 'innodb_flush_log%';
+--------------------------------+-------+
| Variable_name                  | Value |
+--------------------------------+-------+
| innodb_flush_log_at_trx_commit | 2     |
+--------------------------------+-------+
1 row in set (0.00 sec)

mysql> SHOW SESSION VARIABLES LIKE 'innodb_flush_log%';
+--------------------------------+-------+
| Variable_name                  | Value |
+--------------------------------+-------+
| innodb_flush_log_at_trx_commit | 1     |
+--------------------------------+-------+
1 row in set (0.00 sec)

mysql> connect;
Connection id:    6
Current database: *** NONE ***

mysql> SHOW SESSION VARIABLES LIKE 'innodb_flush_log%';
+--------------------------------+-------+
| Variable_name                  | Value |
+--------------------------------+-------+
| innodb_flush_log_at_trx_commit | 2     |
+--------------------------------+-------+
1 row in set (0.00 sec)


Are most of the NoSQL databases key/value stores technically? This video distinguishes between three (main) types of NoSQL databases:

Key/Value store
Tabular
Document oriented

Do these types differ in how they technically store their data or can they be considered all to be key/value stores since even the tabular ones (like Cassandra) may have variable column count and most likely serialize the column values to a string? And the documents (MongoDB) are also only strings in the end... I suppose all types generate an unique id for their "values/data". So are all the 3 types key/value on low level?

MongoDB is not a key-value store; its documents are not just strings in the end. MongoDB stores its data in a format called BSON (Binary JSON), which allows for complex structures including various data types, strings being just one of them. See the BSON Spec for more details, for example:

Examples
The following are some example documents (in JavaScript / Python style syntax) and their corresponding BSON representations.
Example 1:  {"hello": "world"}

\x16\x00\x00\x00                   // total document size
\x02                               // 0x02 = type String
hello\x00                          // field name
\x06\x00\x00\x00world\x00          // field value
\x00                               // 0x00 = type EOO ('end of object')

Example 1:  {"BSON": ["awesome", 5.05, 1986]}

\x31\x00\x00\x00
\x04BSON\x00
\x26\x00\x00\x00
\x02\x30\x00\x08\x00\x00\x00awesome\x00
\x01\x31\x00\x33\x33\x33\x33\x33\x33\x14\x40
\x10\x32\x00\xc2\x07\x00\x00
\x00
\x00


Designing a bank database schema with multiple balances per account I'm trying to build a schema to keep a record of the transactions made on some accounts, but those accounts are not "user accounts", instead there are orders that are being paid, and money is being transfered by the buyer to a middle man, and there to the seller, so there are two distinct balances to track.

The problem I have is that I have different kind of transactions to track, as the price of the product can change (affecting the balance for both parties), the buyer can pay part of the product (affecting just the first balance), or the buyer can pay directly to the seller (affecting both balances). There are more events to track (like canceling the account, changing the price of the product to 0 or near 0, etc).

I've been reading Writing a simple bank schema: How should I keep my balances in sync with their transaction history? but I'm not sure how does it could apply in this context.

I would like to have a design that provides a transaction history easy to read while being flexible to keep track of different kind of transactions.

Update

I will try to provide more data about the entity types:

The main object is a Reservation, that have one buyer and one seller and a price (the buyer have to pay that price, the seller should be payed). It have two different balances, one for each.

There are different type of Transactions that affect the balances in different ways:

a Buyer can deposit money to pay part of the reservation,
money can be sent to the Seller,
the Buyer can send money directly to the Seller, and
money can be transfered from one Reservation to other Reservation.

What I'm not sure about, is what is a better approach:

Store a complex object on the database, that explicit, the kind of transaction, how much money was added/subtracted from which Reservation, if affect buyer and seller, or which one, etc.
Store multiple simple transactions that just add/subtract from each account. For example, if the buyer send money directly to the seller I would add two transactions, one for each.

Use ENGINE=InnoDB and code like this:

BEGIN TRANSACTION;
Update / insert into one account (one database table, that is)
Update / insert into another account
and any other actions that are tied to this 'transaction'
COMMIT;

In some situations, keep two tables for one account:

"Current" balance, etc
"History" of what happened -- This should only have INSERTs, no UPDATEs or DELETEs.  It is a blow-by-blow list of all the changes made to arrive at the "current" state that is shown in the other table.

So the "transaction" might include one UPDATE to the first table, and one INSERT into the other table.


How to separate Stored Procedures (i.e. all the business logic) from Client Database so that it only contains client data? Scenario: Our Application Database (in SQL Server 2012) contains entire business logic in the Stored Procedures (SPs). Everytime we have to publish the DB to the client, it unnecessarily results in copying the SPs to the Client DB.
Problem: All the Business Logic gets copied at the Client Side and results in proprietary issues.
Solutions Tried Earlier:

CREATE PROCEDURE Proc_Name WITH ENCRYPTION

This method results in encrypted and non-maintainable SP code. We cannot judge which version of code is running at the client side, hence cannot debug. Version control cannot be applied. Moreover, client cannot perform DB replication since encrypted SPs do not get replicated.

CREATE SYNONYM SchemaName.Proc_Name FOR LinkedServerDB.SchemaName.Proc_Name

This allows for creation of references (synonyms) at Client_DB which access the actual SPs residing on a Remote_Linked_Server_DB. In each SP call, entire data is accessed from Client_DB and transmitted to Remote_Linked_Server_DB where the calculations are done and the result is sent back. This results in acute performance issues. Also requires 24x7 internet connectivity to the remote linked server.

Requirement (Edited):

We are looking for a solution whereby the stored procedure code could be compiled (secured) and separated from the Client Database.
The compiled stored procedure code should be available at the client-end so that client does not require 24x7 connection to a remote location for accessing the stored procedures as in the case of using SYNONYMS.
I have a slight hint of using CLR-SQL in Visual Studio Database Projects, but I guess that would require re-writing all the stored procedures (which is nearly impossible since we have more than 100 SPs).

I do not think that your requirements are complete as there are reasons that your prior attempts did not work that are not reflected in the stated Requirements. You mentioned in attempted solution #1 that you need to be able to debug the code / know what version it is, and the client needs to be able to replicate the code. Are these two issues current requirements?

If the need for replication is still a requirement, then that conflicts with the requirement to be "compiled (secured)".

I will assume that the client's server is something they have full sa access to such that DENYing the VIEW ANY DEFINITION permission is not a viable approach.

One thing to try is taking the T-SQL of your Stored Procedures and encapsulating each one, unchanged, into CLR Stored Procedures. You would place one or more related Stored Procedures into an Assembly.  Here are some thoughts about this:

Assemblies conveniently have a version number that can be seen in the sys.assemblies catalog view, SSMS (right-click on the Assembly to view the Properties), and the ASSEMBLYPROPERTY function.
You probably don't want to put all of the Stored Procedures into a single Assembly, but you also don't want to have one Assembly per Stored Procedure. You can have one class file per Stored Procedure and several of those included in a single Assembly.
All of the Assemblies should use WITH PERMISSION_SET = SAFE
The only code in each CLR Stored Procedure would be:

Create a SqlCommand with a CommandType of CommandType.Text and CommandText set to the current T-SQL Stored Procedure content
No SqlConnection should be needed (if it is, then it is just "context connection=true")
Map the SqlParameterCollection of the SqlCommand to the input parameters of the CLR Stored Procedure
Call SqlContext.Pipe.ExecuteAndSend(_YourSqlCommand);

The Assembly and related Stored Procedures would be loaded into each Client DB
CLR Stored Procedures are not able to be replicated, but that could probably be worked around


Insert into multi table View in SQL SERVER I'm asking this question in manner to try to understand a similar situation I have, and to try to understand how insert to view is working.

I have these table:

Create table A([id] int primary key not null, value nvarchar(50) NULL)
Create table B([id] int primary key not null, value nvarchar(50) NULL)

And I create view from the two tables like this:

create View V as (select * from A) UNION ALL (select * from B)

And I have this trigger on the V view:

Create trigger v_trig on V instead of insert AS
  insert into v (id,value) select id,value from inserted

When I try to insert to my view I'm getting the following error:

  Msg 4436, Level 16, State 12, Procedure v_trig, Line 2

  UNION ALL view 'db.dbo.V' is not updatable because a partitioning
  column was not found

I have a DB with a similar view (with union all) and for some reason I can insert into it without any problem and I'm trying to understand why.

What should I do in manner to allow insert into such as view? Is there's a way to decide (without changing the trigger) which is the default table of the view in manner to insert into it?

You can only do this (the trigger is irrelevant) when inserting in a partitioned view, which is view that can UNION ALL data in which the underlying tables have proper CHECK constraints that are disjoint together with a primary key, so an INSERT would unmistakably go in one underlying table.

Following your example, if you restrict some values for table A, others for table B and add a compound primary key:

Create table A(
    [id] int,
    value nvarchar(50),
    CHECK (value = 'A'),
    PRIMARY KEY (ID, value))

Create table B(
    [id] int,
    value nvarchar(50),
    CHECK (value = 'B'),
    PRIMARY KEY (ID, value))

The view stays the same:

create View V as (select id, value from A) UNION ALL (select id, value from B)

And now you can successfuly insert directly into the view (no trigger required):

insert into v (id, value) select 1,'B'
-- (1 row(s) affected)

  Is there's a way to decide (without changing the trigger) which is the
  default table of the view in manner to insert into it?

Inserting with value = 'B' matches the CHECK for table B (the partitioned column), so the row goes that way automatically. Since the checked column has to be part of the primary key, the SQL engine knows that this row belongs in this table and not in any other one, since they all have primary keys with the same column and different check values. You can't control this manually.

If you try to insert a value that's not supported in any CHECK constraint...

insert into v (id, value) select 1,'C'
--Msg 4457, Level 16, State 1, Line 1
--The attempted insert or update of the partitioned view failed because the value of the partitioning column does not belong to any of the partitions.
--The statement has been terminated.

Check the DDL of the tables in which the insert of your view works and you will see the disjoint CHECK constraints.


Amazing improvement of script execution speed when split - not understood I have an ETL job that merges data into about 20 columns of a fact table, all integer non-null (default 0) columns. There are 8 merge statements to completely update all the columns and they involve quite a lot of calculation and rows. I put all the merge statements into one long stored procedure and ran it, but it took an impossibly long time. I made some covering indexes and some other changes, which made barely any difference; I was still manually stopping the job after over an hour of running.

Acting on general intuition, I split the long script into seven separate stored procedures and called them one by one with an over-arching stored procedure. The whole lot consistently finishes within 3 minutes (no code changes were made to the scripts).

My question is this - is there a general set of conditions in which it is always so much significantly faster to split a script up into smaller steps - is there some feature of the scripts / server memory or other factor that will mean splitting is occasionally so much more effective than at other times?

Alternatively, is it just that it is always better to split scripts up like this (assuming no problems with naming / SP proliferation etc), and the huge difference in speed is nothing unusual?

My original script was large and would be hard to sanitise sufficiently to show here, but I can give some details:  It is about 550 lines, there were many derived tables, CTEs, and GROUP BY clauses, with a couple of relatively complex WHERE clauses and 1 temp table. There were 8 Merge statements, all targeting the fact table, but sourcing from 3 databases on the same server as the target. Maybe there is something in this which would have particularly slowed things down.

Your comment confirms that the original long script was using variables and these have become parameters.

Probably you are benefitting from parameter sniffing here and better execution plans. The values for variables are not sniffed unless you use the ’option recompile' query hint so you will get the same plan regardless of what the runtime values are.

When you parameterize it SQL Server sniffs the parameter value at the time the plan for the statement is actually compiled and can look in the column statistics using these actual values. Based on that you can get hopefully better row estimates and a more suitable plan.

As a side note parameter sniffing can have its own problems if the plan is cached and the procedure is executed again with a different parameter value as the original plan might not be optimal for the new parameter value.


Using an older MySQL binlog filename (with position zero)? Risks? Downsides? Let's say I'm setting up a new slave. I start with a mysqldump after running mysql -e "STOP SLAVE", transfer that and restore it to the new server.

If I didn't capture the BINLOG filename and position after the STOP SLAVE and before running the mysqldump then what happens if I simply use a previous/older binlog filename (verified to be present on the master and older than the backup), with an position of zero?

It seems to just force the new master to replay all of the events to the new slave, obviously including a number of INSERTs, UPDATEs, and DELETEs which were already represented in the backup (those which happened during the overlapping time period).

Is there anything wrong or risky about that? Is there a downside other than the extra wasted time and CPU cycles (replaying perhaps a day's worth of overlapping transactions)?

I have a surprise for you

Every position within a binary log is based on the filesize of the binary log as of the moment an SQL statement was written.

What is even more interesting about this concept is the fact that header for binary logs differ in start position (There is no such thing as a literal position 0)

These are the starting points for replication from a Master's empty binary log

107 for MySQL 5.5
106 for MySQL 5.1
98 for MySQL 5.0 and back

I first wrote about this back on Feb 4, 2011

Here is a suggested course of action

If the relay logs still exists on the Slave

find the last relay log
run mysqlbinlog against that relay log
locate the timestamp of the moment you performed the mysqldump
Take that timestamp and locate the same timestamp in the master's binary logs\
Near the master's copy of the timestamp in the appropriate  should be the position
Whatever master binary log you selected, use that binary log in the CHANGE MASTER TO command on the Slave
Whatever position you found in the master binary log you selected, use that position in the CHANGE MASTER TO command on the Slave

That's the best way to minimize any data collision due to duplcaiet keys

Just to be sure, on the Slave add this to my.cnf

[mysqld]
slave-skip-errors=1062

Then restart mysql. This will bypass all duplicate key collisions after running START SLAVE;. When Seconds_Behind_Master reaches 0, remove that line and restart mysql on the Slave. Everything in terms of MySQL Replication should be fine from there.


What are some good resources for learning about document oriented databases? I'm interested in learning about NoSQL databases and I decided to start with document oriented databases.  What are some good resources to learn the concepts and good design of this type of database?

Here's a good article comparing document oriented databases:

http://www.mikeperham.com/2009/09/01/comparing-document-oriented-databases/

and here's a good article about designing document oriented databases:

http://ayende.com/Blog/archive/2009/03/08/designing-a-document-database.aspx


Release unused space from SQL Server database We have deleted a large part of the data from a two-digit terabyte sized database, and would now like to release some of the space back to the operating system. The database is currently about 80 % unused space. It will never reach its former size again (the load has been transferred elsewhere).

We have tried running SHRINKDATABASE on a copy of the database on a separate server (non-production). Running for 2 days (on our hardware) it was able to release the space.

I'm assuming it will take much longer on the database in production, as this is heavily used (mostly for querying now though, less so inserts).

Only hours of downtime (at a time) is acceptable on the production system. Some performance degrade over extended periods would be acceptable too.

Any ideas on how to release this amount of unused space, without having excessive downtime, or introduce severe performance degrade during the process?

Just deleted my previous answer as I started a proposed canonical question for this type of question. In case that flagging doesn't work, a short answer for you here.

You have a couple options. Big "Warning" here is - Shrinking isn't always a good thing, it fragments your indexes and takes some time. So proceed cautiously.

You could shrink it in smaller "chunks" using DBCC SHRINKFILE. Pros: No development really required, potentially less downtime than migration. Cons: You are still shrinking a database, you still have to deal with fragmented indexes, but you should do some index rebuilds anyway because you've removed a lot of data.
You could build a new database and select data into it. Pros: You aren't shrinking. You are starting "fresh" and clean. You can use this as a time for other cleanup potentially. Cons: Probably more effort than just shrinking in chunks, may require a larger down window depending on how complex the environment is. Potential risk for missing something in the scripting.
If you chose the shrink route, remember to rebuild your indexes and deal with the resulting fragmentation.


Adding Full Text Search via command line I'm trying to add the full-text search to sql server via command line since we got a core-server.

I already searched the web but all I get is adding it with GUI or re-installing the complete SQL-instance.
Anybody got any idea how to do? (Hope it's on-topic, sorry otherwise: I'm not a DBA, where should I ask?)

I used the GUI version of setup to create a ConfigurationFile.ini that can be used to run the command-line setup to add the necessary Full Text Search components to an installation of SQL Server Core.

The contents of the ini file, which would need to be modified for your scenario, include QUIET="True" which prevents setup.exe from displaying a Graphical User Interface.

Modify the below items for your needs, then save the file as ConfigurationFile.ini in a location where you can refer to it when running setup.exe on the core server.

;SQL Server 2012 Configuration File
[OPTIONS]

; Specifies a Setup work flow, like INSTALL, UNINSTALL, or UPGRADE. This is a required parameter.
ACTION="Install"

; Detailed help for command line argument ENU has not been defined yet.
ENU="True"

; Parameter that controls the user interface behavior. Valid values are Normal for the full UI,AutoAdvance for a simplied UI, and EnableUIOnServerCore for bypassing Server Core setup GUI block.
UIMODE="Normal"

; Setup will not display any user interface.
QUIET="True"

; Setup will display progress only, without any user interaction.
QUIETSIMPLE="False"

; Specify whether SQL Server Setup should discover and include product updates. The valid values are True and False or 1 and 0. By default SQL Server Setup will include updates that are found.
UpdateEnabled="False"

; Specifies features to install, uninstall, or upgrade. The list of top-level features include SQL, AS, RS, IS, MDS, and Tools. The SQL feature will install the Database Engine, Replication, Full-Text, and Data Quality Services (DQS) server. The Tools feature will install Management Tools, Books online components, SQL Server Data Tools, and other shared components.
FEATURES=FULLTEXT

; Specify the location where SQL Server Setup will obtain product updates. The valid values are "MU" to search Microsoft Update, a valid folder path, a relative path such as .\MyUpdates or a UNC share. By default SQL Server Setup will search Microsoft Update or a Windows Update service through the Window Server Update Services.
UpdateSource="MU"

; Displays the command line parameters usage
HELP="False"

; Specifies that the detailed Setup log should be piped to the console.
INDICATEPROGRESS="False"

; Specifies that Setup should install into WOW64. This command line argument is not supported on an IA64 or a 32-bit system.
X86="False"

; Specify the root installation directory for shared components.  This directory remains unchanged after shared components are already installed.
INSTALLSHAREDDIR="C:\Program Files\Microsoft SQL Server"

; Specify the root installation directory for the WOW64 shared components.  This directory remains unchanged after WOW64 shared components are already installed.
INSTALLSHAREDWOWDIR="C:\Program Files (x86)\Microsoft SQL Server"

; Specify that SQL Server feature usage data can be collected and sent to Microsoft. Specify 1 or True to enable and 0 or False to disable this feature.
SQMREPORTING="False"

; Specify if errors can be reported to Microsoft to improve future SQL Server releases. Specify 1 or True to enable and 0 or False to disable this feature.
ERRORREPORTING="False"

; Specify the installation directory.
INSTANCEDIR="C:\Program Files\Microsoft SQL Server"

; Add description of input argument FTSVCACCOUNT
FTSVCACCOUNT="NT Service\MSSQLFDLauncher$INSTANCE"

An example command-line might be:

setup.exe /IACCEPTSQLSERVERLICENSETERMS /CONFIGURATIONFILE configurationfile.ini

See https://docs.microsoft.com/en-us/sql/database-engine/install-windows/install-sql-server-using-a-configuration-file for details about the configuration file.


Using a string with single quotes in a table I'm trying to insert the following string, as written, into a table:

'affinity mask', 0

However I can't figure out how exactly I need to escape the quotes. It always wants to treat the , 0 as a separate column.

How can I escape the comma properly?

You need to double-up apostrophes to embed them within a string, since they also serve as string delimiters:

INSERT dbo.table(column) VALUES('''affinity mask'',0');


How large should my pool size be? And should I use pgbouncer? I have a Rails application with 4 Unicorn workers. How do I determine what postgresql database pool size I should use?

And if I have 6 applications connecting to this postgres database, would that be too many connections? And should I use pgbouncer instead?  If so, why?

There really isn't enough to go on here.  10 concurrent connections isn't a lot for most setups.

The best advice I can give is start simple and when you start to run into cases where performance is starting to become an issue then look at more complex solutions unless you know it will be an issue (say 1000 concurrent users and millions of queries from the web every day).  Otherwise you build in complexity you may never need.  However, by all means monitor and keep an eye out for scaling out solutions that work for you (I recommend keeping an eye on Slony and Postgres-XC in this regard in addition to other solutions). They are at the upper end of complexity but if you need something that can do anything you need it to, they are the solutions.

As it is though, generally for the number of concurrent connections you are talking about, there is no need to consider something complicated until you start to need it.  Then you will have a better idea of what you need in a solution.

Premature optimization is the root of all evil.


How to find out what operator class and access method should be used for an exclusion constraint? Original goal: create a constraint to ensure that only non-overlapping subnets exist in a particular Postgres table.

From reading the docs carefully, I could get as far as this:

create table subnets (
subnet cidr,
exclude (subnet with &&)
);

But this doesn't work.  It produces the inscrutable message:

ERROR:  operator &&(inet,inet) is not a member of operator family "network_ops"
DETAIL:  The exclusion operator must be related to the index operator class for the constraint.

Despite reading up on the doc sections about operator classes and indexes and index types, I retained the feeling that I was missing an entire explanatory section that is taken for granted.  I still don't know what IS an operator class, really, nor an operator family.

I did find posted on a mailing list a code snippet that led to the following working code:

create table subnets (
subnet cidr,
exclude using gist (subnet inet_ops with &&)
);

But I can't truly understand what the "using gist" is for, nor the "inet_ops."

I know that "using gist" relates to types of indexes.  I know an index is automatically created for a "unique" constraint, and I guess that an index may also be automatically created for an "exclusion" constraint.  The only documentation about "operater classes" all relates to indexes, not constraints.

For this or future queries where I want an exclusion constraint, how can I determine what operator class and access method should be specified for the constraint to work?

Note that even with the working code in hand I'm unable to find why it's "gist" and not something else, and why it's "inet_ops" and not "network_ops" or nothing.  \doS and the queries listed in the operator class documentation were unenlightening.

Another error I produced was also unenlightening:

vagrant=# create table subnets (
subnet cidr,
exclude using gist (subnet with &&)
);
ERROR:  data type cidr has no default operator class for access method "gist"
HINT:  You must specify an operator class for the index or define a default operator class for the data type.

(This question is based on the premise that I should not need to resort to copying and pasting incantations; a thorough review of the documentation plus carefully reading error messages should let me resolve problems.  With Postgres, this has always been true before now.)

PostgreSQL Indexes consist of two parts, from the docs on Interfacing Extensions To Indexes

Index Methods (Access Method), for those you're talking about brin, btree, gin, gist, hash, sp-gist (you can see this with \dA+ in psql.)
Operator Class

GIST is the Index Method. For why GIST or SP-GIST is required on EXCLUSION CONSTRAINTS see this post. The Operator Class you need is inet_ops. The problem you're encountering is for cidr and inet two GIST operator classes are provided, and the wrong one is the default,

the operator class gist_cidr_ops is the default. It's part of the "Additional Supplied Modules" and most distros package it as -contrib. It is provided by the btree_gist extension (source)
the operator class inet_ops is core, and not the default.

How would you know this? You probably wouldn't. As far as the access type, good luck. The docs themselves while distributed with PostgreSQL are sorely lacking here, though they do touch on this,

  For historical reasons, the inet_ops operator class is not the default class for types inet and cidr. To use it, mention the class name in CREATE INDEX, for example

CREATE INDEX ON my_table USING GIST (my_inet_column inet_ops);

The best you could do is submit a doc patch to btree_gist where this should probably also be documented.


SQL dumping all pages from buffer cache every few minutes I have a single SQL2012 SP4 node running several databases.

The server has 20GB of memory available, 14GB allocated to SQL (nothing else running on the box).

Every few minutes SQL dumps the whole of the buffer cache. Page life expectancy hits zero, the buffer cache descriptors show that there's nothing in the cache.

I had a look at the resource monitor notifications and the notifications are bouncing around from high/steady/low every few milliseconds:

RESOURCE_MEMPHYSICAL_HIGH
RESOURCE_MEM_STEADY
RESOURCE_MEMPHYSICAL_LOW

With timestamps that are several milliseconds apart. The PLE is essentially a saw-tooth pattern.

I've seen this happen before with SQL2012 SP1 and this question:

SQL Server 2012 Free Pages in Buffer Cache Not Being Used

Seems to be a similar issue, though I've already updated to SP4.

I've tried turning on LPIM for the service account and I've tried messing with the max memory setting. Lowering max memory seems to have caused the buffer cache to empty more frequently.

Any ideas of what to check next?

Server workload is literally nothing (I'm scrolling through lists of items in an ERP system and it gets to about 40-50MB before the cache just drops again).

It's interesting because I upgraded from SP1 to try and fix this - the cache there was getting to around 500MB. Since then I dropped the max memory setting to 14GB which seems to have made it worse.

I'm wondering if Windows is panicking and throwing incorrect notifications for memory pressure at SQL - it follows that the server with max memory set to unbounded seemed to run ok-ish but never fill the cache more than a few hundred MB - but now it barely gets to 50...

More Info: for those that asked

Number of cores: 4

Database Size: 80GB

Error log shows: A significant part of sql server process memory has been paged out. This may result in a performance degradation. Duration: 0 seconds. Working set (KB): 247928, committed (KB): 495656, memory utilization: 50%.

Results of running scripts from this link: https://www.sqlskills.com/blogs/jonathan/identifying-external-memory-pressure-with-dm_os_ring_buffers-and-ring_buffer_resource_monitor/

Not sure how to interpret these - it looks like there is both internal and external memory pressure at various times..

Even more info:

This is a Hyper-V guest sitting on a host with 96GB of total ram of which about half is allocated to the guests.

The symptoms seem similar to this:

SQL Server 2012 x64 - cannot safely allocate more than 50% RAM

However, when I allocated 14GB to SQL the symptoms kicked in immediately (barely 3GB of the server memory was committed)

Last night I bumped the guest memory to 32GB and the issue went away but I see only 14GB commit of total server memory (and the business that runs the DB is busy this morning and this is when they usually have their performance issues).

About 8-9GB of data in the cache at the moment, seems to be stable.

It seems to suggest that 20GB is enough for the workload on this box. I'm happy leaving it with 32GB for now but I'd really like to get to the bottom of this so I can better configure the VMs/SQL.

I'll keep digging and update if I find the answer!

Even more more info:

I didn't restart SQL after turning on LPIM (not realising that was a requirement) but I did leave this setting on and restart to upgrade the memory so now I'm not sure if the increase in memory or LPIM has alleviated the issues.

Will jump on tonight when the server is idle and check how it looks at 20GB again.

Even Further More More Info:

Currently the server is ticking along ok with 32GB allocated and we've not seen the problem since. If this crops up again I'll come back to this question and keep digging.

Currently remains a mystery but my guess is that I'm only masking the issues at the moment.

Although you seem to have solved the issue yourself, here is a summary of the relevant information surrounding the solution.

Server Memory Server Configuration Options

Microsoft write in their article Server Memory Server Configuration Options(Microsoft | SQL Docs) for the section Setting the memory options manually

(emphasis mine)

  Also, setting a min_server_memory value is essential in a virtualized environment to ensure memory pressure from the underlying host does not attempt to deallocate memory from the buffer pool on a guest SQL Server virtual machine (VM) beyond what is needed for acceptable performance.

The section regarding Lock Pages in Memory (same document) has an equalling compelling paragraph which reads as:

(emphasis mine)

  This Windows policy determines which accounts can use a process to keep data in physical memory, preventing the system from paging the data to virtual memory on disk. Locking pages in memory may keep the server responsive when paging memory to disk occurs. The Lock Pages in Memory option is set to ON in instances of SQL Server Standard edition and higher when the account with privileges to run sqlservr.exe has been granted the Windows Lock Pages in Memory (LPIM) user right.

The section LPIM goes on to explain that:

(emphasis mine)

  Setting this option does not affect SQL Server dynamic memory management, allowing it to expand or shrink at the request of other memory clerks. When using the Lock Pages in Memory user right it is recommended to set an upper limit for max server memory as detailed above.

... and in an important comment that:

(emphasis mine)

  Setting this option should only be used when necessary, namely if there are signs that sqlservr process is being paged out. In this case, error 17890 will be reported in the Errorlog, resembling the below example:

A significant part of sql server process memory has been paged out.
This may result in a performance degradation. Duration: #### seconds.
Working set (KB): ####, committed (KB): ####, memory utilization: ##%.

  Starting with SQL Server 2012 (11.x), trace flag 845 is not needed for Standard Edition to use Locked Pages.

Solution

Based on the above findings and your observations the solution to your problem would be to configure the following settings:

min_server_memory (5-10 GB?) based on your comment:

  About 8-9GB of data in the cache at the moment, seems to be stable.

...and Microsoft's recommendation of setting a min_server_memory.
max_server_memory (20-32 GB) based on your observation:

  It seems to suggest that 20GB is enough for the workload on this box. I'm happy leaving it with 32GB for now but I'd really like to get to the bottom of this so I can better configure the VMs/SQL.

...and Microsoft's recommendation of setting a max_server_memory.
Lock Pages in Memory : Enabled for SQL Server Service Account.
Based on the ERRORLOG entry of your SQL Server instance you mentioned and Microsoft's reference in the article.

  Setting this option should only be used when necessary, namely if there are signs that sqlservr process is being paged out.

Before You Continue...

(One of) the benefit(s) of having a virtualised environment is that resources can/should be shared, and possibly even over-commited. However, turning on Lock Pages In Memory (LPIM) could have a negative impact on your Hyper-V environment, if your hardware is hosting multiple instances. An over-commitment of RAM could exhaust other instances.

Before you consider switching all the levers, start off with settings 1. and 2. and if fine tuning these memory settings doesn't work, then consider turning on LPIM if you have enough hardware.


How do I create a new mongo collection with custom indices from an existing mongo db? I have a mongo database collection, that I want to modify. I would like to add new indices (text and geo) to the existing data, and remove unnecessary fields. What would be the best way to do this? There are about 20 million records in my data set.

Your title and question have slightly different goals (copy vs. modify).

To copy a collection between databases you can use the cloneCollection method:

https://docs.mongodb.org/manual/reference/method/db.cloneCollection/

Once you have a collection in the new database that you want to modify you are able to create all the indexes you want to the existing data.

Some of the considerations to be aware of based on the larger data size of the collection is whether you want to block other activities for the creation of the index.  By sending the creation to the "background", you can prevent this:

https://docs.mongodb.org/manual/reference/method/db.collection.createIndex/#options

The other issue, you might want to be aware of, is that index builds in the background generally take longer to build.  One option is, presumably if you are running a replica set, is to build the index on one node at a time, after you remove each node from the replica set.  You can build the indexes once the node is out, and then put it back into the replica set, and move on to the next node.

As far as removing unnecessary fields, you can do that as you go, and just process through the data in batches.  Even if you do it in one query, if it dies, MongoDB will still maintain all the data you already updated, so it can pick up where it left off removing fields that exist in the remaining documents.


How do I return data from a table where the data is the max date of the previous month's data submissions (-1 of current date)? I have a stored procedure where I select the latest/max submit_date data for a tracker_date and client from a source table called Tracker_Live, and insert it into a new table called Tracker_Live_Latest.

I am wanting to do the same as the above, but for last month, where last month is one month less than today's month.

It seems there should be a way to use DATEPART(m, DATEADD(m, -1, getdate()) to get last month and then max of that month.

There are multiple submissions per month to the source table, and the submit_date is recorded as 'submit_date'.

Source table, called Tracker_Live, example (note there are two submit_dates per month for this client):

Assuming today's date is 2014-11-22, and knowing that other client_nbr's may have a different latest submit_date for October, I would want the destination table, called Tracker_Live_Last_Month to have the following data:

The stored procedure code that I use to get the max submit_date to insert into a table called Tracker_Live_Latest is:

delete from Tracker_Live_Latest;

insert into tracker_live_latest
(client_nbr, tracker_date, submit_date)
select client_nbr, tracker_date, max(submit_date)
from tracker_live
group by client_nbr, tracker_date
order by client_nbr, tracker_date;

update tracker_live_latest
set retainer = tl.retainer, live = tl.live, bonus = tl.bonus
from tracker_live as tl
where tracker_live_latest.Client_Nbr = tl.Client_Nbr
and tracker_live_latest.Tracker_Date = tl.Tracker_Date
and tracker_live_latest.Submit_Date = tl.Submit_Date

which will return the following from the above source table:

Any help to insert the correct data into Tracker_Live_Last_Month is appreciated!!

Shara

Expanding on James' answer

Try this code on SqlFiddle.

Also check out @SqlKiwi Paul White's post regarding Delete vs Truncate

create table tracker_live (
  client_nbr int not null, tracker_date date not null, submit_date date not null, live float not null, retainer float not null, bonus float not null
  , constraint pk_tracker_live primary key (client_nbr, tracker_date, submit_date));

insert into tracker_live (client_nbr, tracker_date, submit_date, live, retainer, bonus) values
   (9,'2014-10-01','2014-10-08',537841.5657,1,1)
  ,(9,'2014-11-01','2014-10-08',890293.714,2,2)
  ,(9,'2014-12-01','2014-10-08',45585.4029,3,3)
  ,(9,'2014-10-01','2014-10-17',537841.5657,1,1)
  ,(9,'2014-11-01','2014-10-17',890293.714,2,2)
  ,(9,'2014-12-01','2014-10-17',45585.4029,3,3)
  ,(9,'2014-10-01','2014-11-13',2321938.59,1,1)
  ,(9,'2014-11-01','2014-11-13',907925.1072,2,2)
  ,(9,'2014-12-01','2014-11-13',392917.5237,3,3)
  ,(9,'2014-10-01','2014-11-18',2321938.59,1,1)
  ,(9,'2014-11-01','2014-11-18',795725.1072,2,2)
  ,(9,'2014-12-01','2014-11-18',725835.0474,3,3)

create table tracker_live_last_month (
  client_nbr int not null, tracker_date date not null, submit_date date not null, live float not null, retainer float not null, bonus float not null
  , constraint pk_tracker_live_last_month primary key (client_nbr, tracker_date));

/* example query for procedure */

declare @test date;
  set @test = '2014-11-18';
  --set @test = convert(date,getdate());
declare @LastDayOfLastMonth date;
  set @LastDayOfLastMonth  = dateadd(day,-datepart(day,@test),@test);
declare @FirstDayOfLastMonth date;
  set @FirstDayOfLastMonth = dateadd(day,1-datepart(day,@LastDayOfLastMonth),@LastDayOfLastMonth);

  truncate table tracker_live_last_month;

  /* inserting the data with this combined query should be quicker than creating the table and then updating values */
  insert into tracker_live_last_month (client_nbr, tracker_date, submit_date, live, retainer, bonus)
    select tl.client_nbr, tl.tracker_date, ca.submit_date, ca.live, ca.retainer, ca.bonus
      from tracker_live tl
        cross apply (select top 1 i.submit_date, i.live, i.retainer, i.bonus
          from tracker_live i
          where i.client_nbr=tl.client_nbr
            and i.tracker_date=tl.tracker_date
          order by i.submit_date desc) as ca
      where tl.submit_date between @FirstDayOfLastMonth and @LastDayofLastMonth
      group by tl.client_nbr, tl.tracker_date, ca.submit_date, ca.retainer, ca.live, ca.bonus
      order by tl.client_nbr, tl.tracker_date, ca.submit_date, ca.retainer, ca.live, ca.bonus;


Trigger to prevent insertion of duplicate data I have a table named dbo.Inventory in SQL Server 2016 like this:

ProdCode     UPCCode
   1          11111
   2          11112
   3          11113

When adding a new row, I need to write a trigger that will check for a duplicate in that column. If it is a duplicate I need it to give an error and not change the column. Otherwise, I need it to insert like normal.

I can't use a unique index because I have a lot of rows with a value of NULL.

I'd stay away from triggers as much as you can, cause they're generally pretty bad for performance. I'd suggest using a non clustered index with a filter.

See below example

CREATE TABLE dbo.Inventory( ProdCode INT, UPCCode Char(5) )

INSERT INTO dbo.Inventory(prodcode,UPCCode)
VALUES  ('1','11111'),
        ('2','11112'),
        ('3','11113'),
        ('4','11114'),
        ('5',NULL),
        ('6',NULL)

CREATE UNIQUE NONCLUSTERED INDEX NCIU_UPCCode
ON dbo.Inventory(UPCCode)
WHERE UPCCode IS NOT NULL;

-- Works
INSERT INTO dbo.Inventory
VALUES  ('7','11115')

-- Works
INSERT INTO dbo.Inventory
VALUES  ('8',NULL)

-- Fails
INSERT INTO dbo.Inventory
VALUES  ('9','11115')

DROP TABLE dbo.Inventory

That should give you the requested results, without needing to use a trigger.


INSERT from a SELECT combined with a single value I have this query that returns several rows of item_id:

select item_id from properties where name like 'body';

And I have the second one that returns 1 row of tag_id:

select id from tags where name ilike '%hoax%';

I want to use both results to create new rows in a third table item_tags so that I can insert each item_id from the first query with the tag_id from the second query.

I'll have something like:

INSERT INTO item_tags (item_id, tag_id) VALUES (item_id1, tag_id);
INSERT INTO item_tags (item_id, tag_id) VALUES (item_id2, tag_id);
INSERT INTO item_tags (item_id, tag_id) VALUES (item_id3, tag_id);
 ...

The 1st query returns over 800 rows. How can I automate the insert?
psql 9.1.11, Postgres 9.1.4.

Basic INSERT
INSERT INTO item_tags (item_id, tag_id)
SELECT p.item_id, t.id
FROM  (SELECT item_id FROM properties WHERE name LIKE 'body') p
    , (SELECT id FROM tags WHERE name ILIKE '%hoax%') t

Where the comma (,) in the FROM clause is short notation for CROSS JOIN.
That's building on your assertion that the second query on tags ...

returns 1 row of tag_id

You get a Cartesian Product from a CROSS JOIN, that's n*m combinations.
If both sides of the join return more than one row, they multiply each other.
If either side does not return a row, the overall result is no row and nothing happens.
If you want neither of those effects, one side must return at least one row, and the other side exactly one row.
For your specific combination you can also use a nested subselect:
INSERT INTO item_tags (item_id, tag_id)
SELECT item_id
     , (SELECT id FROM tags WHERE name ILIKE '%hoax%') AS tag_id  -- single result!
FROM   properties
WHERE  name LIKE 'body';

The subtle difference: This inserts all rows returned from the outer SELECT, even if the subselect on tags returns no row, which would be converted to NULL for tag_id.
Bulk INSERT
To deal with lots of such inserts at once, you would use a largely modified query. But the same rules as for the first query above apply for cardinalities in both joined tables.
INSERT INTO item_tags (item_id, tag_id)
SELECT p.item_id, t.id
FROM  (
   VALUES
      ('body',  'hoax')
    , ('body2', 'fun')
    , ('body3', 'love')
  ) AS it(item, tag)
JOIN   properties p ON p.name LIKE it.item
JOIN   tags t ON t.name ILIKE ('%' || it.tag ||'%');

You see the logic, right? Personally, I call this a "CROSS JOIN by proxy".


Should I mark a composite index as unique if it contains the primary key? Given some table with a primary key, e.g.:

CREATE TABLE Customers (
   CustomerID int NOT NULL PRIMARY KEY,
   FirstName nvarchar(50),
   LastName nvarchar(50),
   Address nvarchar(200),
   Email nvarchar(260)
   --...
)

we have a unique primary key on CustomerID.

Traditionally i might then need some additional covering indexes; for example to quickly find a user by either CustomerID or Email:

CREATE INDEX IX_Customers_CustomerIDEmail ON Customers
(
   CustomerID,
   Email
)

And these are the kinds of indexes i've created for decades.

It's not required to be unique, but it actually is

The index itself exists to avoid a table scan; it is a covering index in order to aid performance (the index is not there as a constraint to enforce uniqueness).

Today i remembered a tid-bit of information - SQL Server can use the fact that:

a column has a foreign key constraint
a column has a unique index
a constraint is trusted

in order to help it optimize its query execution. In fact, from SQL Server Index Design Guide:

  If the data is unique and you want uniqueness enforced, creating a unique index instead of a nonunique index on the same combination of columns provides additional information for the query optimizer that can produce more efficient execution plans. Creating a unique index (preferably by creating a UNIQUE constraint) is recommended in this case.

Given that my multi-column index contains the primary key, this composite index will de facto be unique. It's not a constraint that i particularly need SQL Server to enforce during every insert or update; but the fact is that this non-clustered index is unique.

Is there any advantage in marking this de facto unique index as actually unique?

CREATE UNIQUE INDEX IX_Customers_CustomerIDEmail ON Customers
(
   CustomerID,
   Email
)

It seems to me that SQL Server could be smart enough to realize that my index already is unique by virtue of the fact that it contains the primary key.

But perhaps it doesn't know this, and there's an advantage for the optimizer if i declare the index as unique anyway.
Except perhaps that might now lead to slowdowns during inserts and updates, where it must perform uniqueness checks - where before it never had to before.
Unless it knows the index is guaranteed to already be unique, because it contains the primary key.

I cannot find any guidance from Microsoft about what to do when a composite index contains the primary key.

  The benefits of unique indexes include the following:

  Data integrity of the defined columns is ensured.
  Additional information helpful to the query optimizer is provided.

Should i mark a composite index as unique if it already contains the primary key? Or can SQL Server figure out this for itself?

Should I mark a composite index as unique if it already contains the primary key?

Probably not. The optimizer can generally use information about the uniqueness of the contained key column anyway, so there's no real advantage.

There is also an important consequence of marking an index unique on update plans that modify keys of that index to consider:

Setup

CREATE TABLE dbo.Customers
(
   CustomerID int NOT NULL PRIMARY KEY,
   FirstName nvarchar(50),
   LastName nvarchar(50),
   [Address] nvarchar(200),
   Email nvarchar(260)
);

CREATE NONCLUSTERED INDEX
    IX_Customers_CustomerIDEmail
ON dbo.Customers
(
   CustomerID,
   Email
);

-- Pretend we have some rows
UPDATE STATISTICS dbo.Customers
WITH ROWCOUNT = 100000, PAGECOUNT = 20000;

Per-index update plan (non-unique index)

UPDATE dbo.Customers
SET Email = N'New', [Address] = 'New Address'
WHERE Email = N'Old'
OPTION (QUERYTRACEON 8790); -- Per-index update plan

Execution plan:

The optimizer often makes a cost-based decision between updating nonclustered indexes per-row (a 'narrow' plan) or per-index (a 'wide' plan). The default strategy (except for in-memory OLTP tables) is a wide plan.

Narrow plans (where nonclustered indexes are maintained at the same time as the heap/clustered index) are a performance optimization for small updates. This optimization is not implemented for all cases - using certain features (like indexed views) means that the associated index(es) will be maintained in a wide plan.

More information: Optimizing T-SQL Queries that Change Data

In this case, I have used undocumented trace flag 8790 to force a wide update plan: The plan therefore shows the clustered and nonclustered indexes being maintained separately.

The Split turns each update into a separate delete & insert pair; the Filter filters out any rows that would not result in a change to the index.

More information: (Non-updating updates) by the SQL Server QO Team.

Per-index update plan (unique index)

-- Same index, but unique
CREATE UNIQUE INDEX IX_Customers_CustomerIDEmail ON Customers
(
   CustomerID,
   Email
)
WITH (DROP_EXISTING = ON);

UPDATE dbo.Customers
SET Email = N'New', [Address] = 'New Address'
WHERE Email = N'Old'
OPTION (QUERYTRACEON 8790); -- Per-index update plan

Execution plan:

Notice the extra Sort and Collapse operators when the index is marked unique.

This Split-Sort-Collapse pattern is required when updating the keys of a unique index, to prevent intermediate unique key violations.

More information: Maintaining Unique Indexes by Craig Freedman

The Sort in particular can be a problem. Not only is it an unnecessary extra cost, it may spill to disk if estimates are inaccurate.

About nonclustered keys

Another factor to consider is that nonclustered index structures are always unique, at every level of the index, even if UNIQUE is not specified. The clustering key(s) - and possibly a uniquifier if the clustered index is not marked unique - are added to a non-unique nonclustered index at all levels.

As a consequence, the following index definiton:

CREATE INDEX IX_Customers_CustomerIDEmail ON Customers
(
   Email
)
WITH (DROP_EXISTING = ON);

...actually contains the keys (Email, CustomerID) at all levels. It is therefore 'seekable' on both columns:

SELECT *
FROM dbo.Customers AS C WITH (INDEX(IX_Customers_CustomerIDEmail))
WHERE C.Email = N'Email'
AND C.CustomerID = 1;

More information: More About Nonclustered Index Keys by Kalen Delaney


Example of MS Access SQL IIF to T-SQL Given the syntax rules shown below

Access: IF(Condition, ReturnIfTrue, ReturnIfValue)
T-SQL: CASE WHEN Condition THEN ReturnIfTrue ELSE ReturnIfFalse END

What would the below be formatted to T-SQL be?

(((IIf(IsNull(Production!Product_Title) Or Production!Product_Title Like "Process*",
"Not Started", "Started"))="Not Started") And ((Person.Type) Is Not Null))

Seems you don't have to use IIF or CASE

(
    (
    Production.Product_Title IS NULL
    OR Production.Product_Title Like 'Process%'
    )
AND Person.Type IS NOT NULL
)

FYI, Transact-SQL has IIF function starting with 2012.

UPD

If you had to return some value:

SELECT
    CASE WHEN
        Production.Product_Title IS NULL
        OR Production.Product_Title Like 'Process%'
    THEN 'Not Started'
    ELSE 'Started'
    END AS [ProductState]
FROM YourTable


complex query joining multiple tables with aggregate functions I have the following db structure:

Channels and packages table have many to many relations by channel_package table.

User can purchase packages or channels separately which is stored in purchases table. So when a user purchases a channel channel_id is stored as product_id and cat_id=2 and when a package is purchased, package_id is stored as product_id and cat_id=1.

What I want to get is list of channels having maximum expiry for a particular user.

If the purchases table would only store channels it would have been easy like

SELECT
    p.id,c.name,
    max(p.expiry)
FROM
    purchases as p
    LEFT JOIN channels as c
        on c.id=p.product_id
WHERE
    cat_id = 2
GROUP by c.id

So how can i get the same result considering the packages (packages contains channels, if the channel contained in the package has more expiry? The package's expiry should be displayed as max(expiry).

Check the result here: http://rextester.com/RQKTP46266

I made it in postgresql, but I think its the same for mysql.

This is the structure used:

create temp table ch (cid int, name text);
create temp table pk (pid int, name text);
create temp table pkch (id int, pid int, cid int);
create temp table pu (id int, userid int, prid int, cat int, expiry date);

insert into ch values (1, 'channel 1');
insert into ch values (2, 'channel 2');
insert into ch values (3, 'channel 3');
insert into ch values (4, 'channel 4');
insert into ch values (5, 'channel 5');

insert into pk values (1, 'pack 1');

insert into pkch values (1, 1, 1);
insert into pkch values (2, 1, 2);
insert into pkch values (3, 1, 3);

insert into pu values (1, 10, 1, 1, '2016-05-01');
insert into pu values (2, 10, 2, 2, '2016-04-01');
insert into pu values (3, 10, 3, 2, '2016-04-01');

First you should expand channels included in purchased packages, and UNION with the single channels.

The next step is easy, select max(expiry) and goup it by channel.

select userid, channel_id, max(expiry)
from
(
    select pu.id, pu.userid, pkch.cid as channel_id, pu.expiry
    from pu
        join pkch on pu.prid = pkch.pid and pu.cat = 1
    where pu.userid = 10
    UNION
    select pu.id, pu.userid, pu.prid as channel_id, expiry
    from pu
    where pu.userid = 10 and pu.cat = 2
) a
group by userid, channel_id


Creation of spatial index locks the table for hours I used this code to create a spatial index in SQL Server 2008 R2:

BEGIN TRANSACTION
SET QUOTED_IDENTIFIER ON
SET ARITHABORT ON
SET NUMERIC_ROUNDABORT OFF
SET CONCAT_NULL_YIELDS_NULL ON
SET ANSI_NULLS ON
SET ANSI_PADDING ON
SET ANSI_WARNINGS ON
COMMIT
BEGIN TRANSACTION
GO
CREATE SPATIAL INDEX SPATIAL_COMPANIES_GEO ON dbo.ITEMS(GEO) USING GEOGRAPHY_GRID
 WITH(
          GRIDS  = ( LEVEL_1  = HIGH, LEVEL_2  = HIGH, LEVEL_3  = HIGH, LEVEL_4  = HIGH)
        , CELLS_PER_OBJECT  = 100
        , STATISTICS_NORECOMPUTE = OFF
        , ALLOW_ROW_LOCKS = ON
        , ALLOW_PAGE_LOCKS = ON
     )
GO
ALTER TABLE dbo.COMPANIES SET (LOCK_ESCALATION = TABLE)
GO

This query executes in 5 seconds, but then the table ITEMS seems to be locked for hours. In Management Studio I cannot expand the Indexes folder for ITEMS and any query to that table times out. Furthermore, if I restart the  SQL Server service the spatial index I just created disappears.

I've tried this on my production database with 3.000.000 records and also on my test database with 300.000 records with the same result.

Is this normal? What I should do to create the index without locking the database?

You start a transaction but don't commit the 2nd one, so the table will remain locked.
The SQL Server restart will rollback the transaction containing the CREATE INDEX

Remove both BEGIN TRANSACTION calls and theCOMMIT(or add a final COMMIT TRAN)


Joining 3 tables while using MAX Overview

I am attempting to join 3 tables auction, bids, and users. I need several key pieces of information from each table based off the bids table. In the end I want a summary overview of the auctions. To see the latest bid for each auction and the user that placed it.

I am used to doing simple SQL queries and I am not sure what I need to do in order to accomplish this. Not looking for a solution (although I would take it with an explanation) but learning experience.

Table Structures

auctions

id, title, location

bids

id, auction_id, user_id, bid_amount

users

id, name, address

Query So Far

So far this is what I have started with, any guidance on what to do from here is greatly appreciated.

SELECT
    MAX(bids.id) AS 'Bid ID',
    bids.auction_id AS 'Auction ID',
    auctions.title AS 'Auction Title',
    MAX(bids.bid_amount) AS 'Bid Amount'
FROM
    boardman_bidding.bids
        INNER JOIN
    boardman_bidding.auctions ON bids.auction_id = auctions.id
        INNER JOIN
    boardman_bidding.users ON bids.user_id = users.id
WHERE
    bids.auction_id > 1065 AND
    auctions.city_id = '45621'
GROUP BY bids.auction_id
ORDER BY bids.auction_id DESC;

Expected Output

I am hoping for the following:

bid_id, auction.title, auction.location, users.user_id, users.name, users.location, bid_amount

Bids should be two tables:

BidHistory -- containing details about each bid
CurrentBid -- Either some details (redundant) about the latest bid, or where all the details live until moved to the other table.

This adds complexity to the "make a bid" code.  But it simplifies the code that needs to know what the current bid is.  And it avoids the complex code to fetch the latest bid.

The Strawman:

There is a serious flaw in the query that you presented.  MAX(bid.id) and MAX(bids.bid_amount) do not necessarily refer to the same row.  (OK, perhaps they do in a bidding situation, but you should not assume it!)  This is where you need "Groupwise Max" solutions.  See the [greatest-n-per-group] tag.  There are a lot of discussions here and on stackoverflow.com about how to find the row that contains the max (or min) value of a particular column.  Some solutions involve a subquery.  Some solutions are slower than others.

See also http://mysql.rjweb.org/doc.php/groupwise_max


Is there a way I can use currval() without selecting it more than once? I want to be able to use the current value of an auto ID by giving it some kind of name and just referring to it (instead of calling SELECT for each record):

Requests table has a serial column named requestid.

insert into testschema.requests (chid) values
(1);

I want to use select currval() multiple times without having to use SELECT for each line:

insert into testschema.requestinfo (requestid, reportid, amount) values
((select currval('testschema.requests_requestid_seq')), 10, 40),
((select currval('testschema.requests_requestid_seq')), 15, 29),
((select currval('testschema.requests_requestid_seq')), 40, 4);

I've tried placing the select on its own line with no success:

SELECT currval('testschema.requests_requestid_seq') as reqid;
INSERT INTO testschema.requestinfo(requestid, reportid) VALUES
 (reqid, 2);

Bottom line: I want to refer to the currval without having to use SELECT currval() for each line.

First of all, you would not need a subquery in your statement. The bare function does the job:

INSERT INTO testschema.requestinfo (requestid, reportid, amount)
VALUES (currval('testschema.requests_requestid_seq'), 10, 40)
     , (currval('testschema.requests_requestid_seq'), 15, 29)
     , (currval('testschema.requests_requestid_seq'), 40, 4);

Next, currval() only makes sense if you have (implicitly or explicitly) called nextval() for the same sequence before in your session. If you have not called nextval() for any other sequences in the meantime, you can simplify with lastval().

INSERT INTO testschema.requestinfo (requestid, reportid, amount)
VALUES (lastval(), 10, 40)
     , (lastval(), 15, 29)
     , (lastval(), 40, 4);

Either way, if you have many rows like this, look to @dezso's answer for a way to avoid typing the function repeatedly.

currval() and lastval() are volatile functions:

SELECT proname, provolatile
FROM   pg_proc
WHERE  proname IN ('currval', 'lastval')

Result:

proname provolatile
currval v
lastval v

That means, they will still be evaluated for every row that way.
To also avoid repeated execution of the function, move it to a separate CTE or subquery and CROSS JOIN to your input values:

INSERT INTO testschema.requestinfo (requestid, reportid, amount)
SELECT *           -- order of rows is maintained from left to right
FROM   lastval()   -- or currval('testschema.requests_requestid_seq')
     , VALUES (10, 40), (15, 29), (40, 4);


Dump PostgreSQL without DROP DATABASE I want dump a database, but, without DROP DATABASE, and CREATE DATABASE instructions, in .backup / .sql (generate file).

Actually I use this command:

pg_dump --format=c --no-privileges --no-owner --file=/tmp/`hostname`-`date +%d%m%Y`.backup --username=admin --host=127.0.0.1 database_name

But, in top lines of file, I have this:

DROP DATABASE...
CREATE DATABASE...

So, I my case, I want duplicate this database, and, if I use this script with pg_restore I drop the other database (I don't want that).

My understanding of pg_restore (which you'll have to use because you created a pg_dump with format=custom) is that if you supply the --dbname=... flag you will restore into that database (without dropping it).

If you supply the --clean command the db and objects will be dropped before being recreated.

If no --dbname=... flag is supplied, the creation commands will spool to stdout, which you can redirect to a file and load using psql.

To test, try:

pg_dump --format=c --no-privileges --no-owner --file=/tmp/`hostname`-`date +%d%m%Y`.backup --username=admin --host=127.0.0.1 database_name

pg_restore your_dump_file > createdb.sql

psql -d database_name -U postgres -f createdb.sql


Using Multi-table Insert for Parent and Child Table Is it safe to use Oracle's multi-table insert statement to insert into a (foreign key constrained) parent and child table?

With minimal examples, I've found that it works as long as the parent table comes before the child table in the into list. Can I rely on this or should I make the constraint deferrable?

No, you can't depend on this.  SQL is declarative, not procedural, so within a statement you can't guarantee the order of execution.  Since the entire INSERT ALL statement is considered a single statement (doc), you can't guarantee that one INSERT will be before another.

By definition an INSERT FIRST must execute the first INTO passing the evaluated conditions.  We might expect INSERT ALL to behave similarly.  This appears to be the case:

DROP TABLE T1;
CREATE TABLE T1 AS (SELECT 'a' c1, 0 c2, 0 c3 FROM dual WHERE 1=2);
INSERT ALL
   WHEN mod(x,2)<>0 THEN INTO T1 VALUES ('a', x, mod(x,2))
   WHEN mod(x,2)=0 THEN INTO T1 VALUES ('b', x, mod(x,2))
   SELECT Level x FROM dual CONNECT BY Level <=20;
COMMIT;
SELECT rowid, c1, c2, c3 FROM t1;

However, even though we can demonstrate a particular behavior on a particular platform/version/patchset still doesn't make this a guarantee.

Oracle-developer.net says it explicitly:

  the conditions in an INSERT FIRST statement will be evaluated in order
  from top to bottom. Oracle makes no such guarantees with an INSERT ALL
  statement.


Moving postgresql data directory I moved postgresql's data directory by following the following steps:

Stop postgres
cp -a source_data_directory destination_data_directory
export PGDATA=destination_data_directory
Changing data directory to destination_data_directory within postgresql.conf
pg_ctl start

But when I run: "pg_ctl start" I get the following error. Can someone please help me figure out the error.

server starting
postgres@Server:/usr/lib/postgresql/9.1/bin$ postgres cannot access the server configuration file "/home/destination_data_directory/postgresql.conf": No such file or directory

First make sure the file exists really in that location. Run ls $PGDATA if there is a mistake in the environment variable you will either see the wrong files or get an error because the path does not exist.

Then make sure the owner of the parent directory /home/destination_data_directory and all directories and files under match the user you start pg_ctl under. If they are not either use

su postgres_user

to switch to the right user before running pg_ctl or use

chown -R postgres_user /home/destination_data_directory

to change the owner of the directory and everything under it.


Good book on databases for beginners Possible Duplicate:
  Must-read Books for DBA

I'm already reading a book on Introduction to SQL (RDMS). But that only teaches SQL and commands and I feel like my knowledge lacks in database management. Can you recommend me a good book to Database management which teaches about managing the databases instead of just using the queries to get something done?

It really depends on which flavor of RDBMS you're working with.  Oracle database management is significantly different from SQL Server database management, for instance.

I answered a similar question recently here, but one of the best (Oracle) DBA books that I've read is Darl Kuhn's Pro Oracle Database Administration.  I used it as a textbook in graduate school, and it covers everything from install, config, backups, partitioning, etc, as well as common real-life problems and their solutions.

I wouldn't classify it as being for "beginners", but I had never had any database management before either, and I did ok.


Connecting remote MySQL database to local MySQL database? I want to write PHP code to be embedded in a Drupal7 module.

I want to call a procedure which can copy newly generated data in the local MySQL database to a remote MySQL database.

When data is inserted in table A of my local database it should be copied to the specific table B on the remote MySQL database.

Table 'A' is on local host.

Table 'B' is on remote server.

INSERT data on 'A' -> copied to 'B'

Is this possible?

There are 3 methods to set this up
METHOD #1 : MySQL Replication
Setup MySQL Replication where the Slave has this option
replicate_do_table=mydb.mytable

Then, any DML (INSERT, UPDATE, DELETE) or DDL (ALTER TABLE) you execute will go immediately to serverB. This makes Method #1 is the fastest and most granular approach.
METHOD #2 : Copying the table to the other server
Rather than rehash, Here is an earlier post I did May 31, 2011 for this method : How do you copy a table from MySqlServer_A to MySqlServer_B?
METHOD #3 : FEDERATED Table (MyISAM Only)
Suppose mytable on serverA looks like this
CREATE TABLE mydb.mytable
(
...
) ENGINE=MyISAM;

You can a mapping of the target table in serverB by running this on serverA like this
CREATE TABLE mydb.mytable_remote LIKE mytable;
ALTER TABLE mydb.mytable_remote ENGINE=FEDERATED
CONNECTION='mysql://username:password@serverB/mydb/mytable';

I wrote an earlier post back on Jan 4, 2012 : fetching externally hosted db's table from within the local mysql server
Then you would have to bulk copy everything in serverA to serverB running the following on serverA:
INSERT IGNORE INTO mydb.mytable_remote SELECT * FROM mydb.mytable;


How can I get dates that wrap around new year from non-datetime columns? I have a table in my MySQL database that stores daily data (an entry for each day of the year), but does not use a DATETIME column for this. This is because the data is the same for every year (it's climatology data, so the "normal" high temperature for Jan 7, 2015 is the same as for Jan 7, 2016 and so on). I have three fields that represent the day this data is valid for, so my table looks like:

month, day_of_month, day_of_year, value
1      1             1            23
1      2             2            22.95
...
12     31            365          23.12

Say I then want to get the values from this table from a certain date, like 01 Dec 2015 to 60 days from then, which would be 30 Jan 2016. Is there a way to get back the date and value for all of those days, working okay across the new year? Ideally I'd love results that flow like:

valid_date, value
...
2015-12-31  23.12
2016-01-01  23
2016-01-02  22.95
...

(hopefully you get the idea).

If you can think of a better way to store this static daily data so that I can achieve the above goal, I'm open to that as well! Thanks for your help!


How to query with filter classifications Looking for a way to filter clothes based on their labels, the labels need to be considered for their classifications, such that different classification all needs to be satisfied (AND), same classification is joined (OR).
E.g. The following expectation can be satisfied:
Ask: (Blue(classification: Color) or Green(classification: Color)) and On Sale(classification: Discount)
or
Ask: Blue(classification: Color) and (Aloha(classification: Style) or Knit(classification: Style))
Need help understand the best way to achieve this.
SELECT
    "clothes".*
FROM
    "clothes"
INNER JOIN "clothes_labels" ON
    "clothes_labels"."clothes_id" = "clothes"."id"
INNER JOIN "labels" ON
    "labels"."id" = "clothes_labels"."tag_id"
WHERE
    ...

Sample query fiddle (thank you):
https://dbfiddle.uk/?rdbms=postgres_13&fiddle=fd625a0f5b97a6e6b3067758aadcd434

In Postgres, I would aggregate the labels for each clothe into a JSONB array which can then be queried for "contains all of this" quite easily.
Something along the lines:
select *
from clothes c
  join (
     select cl.clothes_id,
            jsonb_agg(to_jsonb(l) - 'id') as class
     from clothes_labels cl
       join labels l on cl.label_id = l.id
     group by cl.clothes_id
  ) cl on cl.clothes_id = c.id
where class @> '[{"name": "Blue", "classification": "color"},{"name": "Aloha", "classification": "style"}]'
   or class @> '[{"name": "Knit", "classification": "style"}]'
;

Online example

In general I would replace EAV models with a JSON approach these days as they tend to be easier to query and work with (again: assuming Postgres, in MySQL this can't be indexed efficiently)


How to restore accidentally truncate mysql table I have accidentally truncate mysql table table1. The truncated data are very very important. I used wampserver latest version. Unfortunately I do not have binary logs. I have wamp folder under drive C in Windows 10. Now I am trying to restore the database folder using Windows data recovery tool. Is there any other possibilities to restore my truncated table?

See this link for the answer. Hope this helps.

If you have binary logging enabled, you can replay changes that went into creating the stats table, up to the point where you did the TRUNCATE.

But you may not have binary logs for all changes to that table from the beginning of time. It's common to have binary logs only back a few days. Any data that was created in that table prior to your oldest binary log is not recoverable.


byte vs char length semantics in sqlplus The following code works fine in SQLDeveloper

declare
x varchar2(5 char);
begin
select 'üüüü' into x from dual;
end;
/

But when I run it in sqlplus I get

declare
*
ERROR at line 1:
ORA-06502: PL/SQL: numeric or value error: character string buffer too small
ORA-06512: at line 4

I get a similar error when I in sqldeveloper changes the variable declaration to

x varchar2(5 BYTE)

The question is: why do I get the error in sqlplus and not in sqldeveloper?

Set NLS_LANG properly. Here is an example.

I use KiTTY for SSH client, with UTF-8 settings.

[oracle@o73 ~]$ file test.sql
test.sql: UTF-8 Unicode text

[oracle@o73 ~]$ cat test.sql
declare
  x varchar2(5 char);
begin
  select 'üüüü' into x from dual;
end;
/
[oracle@o73 ~]$ sqlplus / as sysdba

SQL*Plus: Release 18.0.0.0.0 - Production on Mon May 7 19:04:00 2018
Version 18.2.0.0.0

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

Connected to:
Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production
Version 18.2.0.0.0

SQL> @test
declare
*
ERROR at line 1:
ORA-06502: PL/SQL: numeric or value error: character string buffer too small
ORA-06512: at line 4

SQL> Disconnected from Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production
Version 18.2.0.0.0
[oracle@o73 ~]$ export NLS_LANG=.AL32UTF8
[oracle@o73 ~]$ sqlplus / as sysdba

SQL*Plus: Release 18.0.0.0.0 - Production on Mon May 7 19:04:13 2018
Version 18.2.0.0.0

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

Connected to:
Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production
Version 18.2.0.0.0

SQL> @test

PL/SQL procedure successfully completed.

SQL Developer handles this automatically for you.


Change all databases in "RESTORING" state to ONLINE I have multiple databases that I have a backup script that pulls in logs from another server and restores them. When restoring I leave the database in the RESTORING state so further transaction logs can be restored.  I am trying to find a solution where all databases left in the RESTORING state can be set to ONLINE.  I know how to do this for a single database, just not for multiple dbs.  Can anyone lend a hand?

What I have thus far are two commands, I am just not sure how to combine them.

SELECT name FROM sys.databases WHERE state_desc = 'RESTORING"

and

RESTORE DATABASE "name" WITH RECOVERY

You need to use the WITH RECOVERY option, this seems likely caused by the restore script adding the WITH NO RECOVERY parameter to make the database ready for a transaction log apply after the restore. With your database RESTORE command, to bring your database online as part of the restore process.
This is of course only if you do not intend to restore any transaction log backups, i.e. you only wish to restore a database backup and then be able to access the database.

Your command should look like this,

**RESTORE DATABASE MyDatabase FROM DISK = 'MyDatabase.bak' WITH REPLACE, RECOVERY, STATS = 10**

You may have more success using the restore database wizard in SQL Server Management Studio (SSMS). This way you can select the specific file locations, the overwrite option, and the WITH Recovery option. Sometimes, The restoration process stuck just because of size of the database file.

You can either Use Below one:

How to Change the state from RESTORING To ONLINE

The following will go through each of the lines returned by the query building a RESTORE DATABASE statement around the name, using WITH RECOVERY at the end. The statement is then shown in the messages window which you can then use as needed. We'll make use of the state value to restrict the statement to only altering those that are not at state 1.

DECLARE @SetWithRecovery nvarchar(max) = ''

SELECT @SetWithRecovery += 'RESTORE DATABASE ' + quotename(Name) + ' WITH RECOVERY; ' + Char(10)
FROM sys.databases
WHERE [State] = 1

PRINT @SetWithRecovery

--If you wish to execute it as well

EXEC (@SetWithRecovery)

When you execute the code you'll receive a message for each restore statement showing the processing information.

Before you do this, please make sure you understand the options what you has to do, if not it may cause data loss.

  Check this link if needed:
  https://devondba.blogspot.com/2014/11/how-to-change-sql-database-from.html


Datapump not working in RAC environment Some minutes ago I've tried to do an export but I get the following error (AIX system):

Additional information: 3
ORA-31693: Table data object "XX"."TABLE_NAME" failed to load/unload and is being skipped due to error:
ORA-31617: unable to open dump file "/dir/back/dump/dump_xx_04.dmp" for write
ORA-19505: failed to identify file "/dir/back/dump/dump_xx_04.dmp"
ORA-27037: unable to obtain file status
IBM AIX RISC System/6000 Error: 2: No such file or directory

What could the reason for this behaviour be?

I've read in some blog that using just parallel=N without %U that the export is actually slower. Is that true?

I've found the problem.

The doc ID 1597395.1 says:

  The problem occurs when Datapump Export is being performed on a
  multi-node RAC where the dumpfile destination is not shared to all
  nodes for access.  Since multiple nodes will be running the Datapump
  job, ALL nodes must have access to the mount point where the dump file
  will be written.

So I've just used the parameter cluster=n in export to resolve it.


MySQL Partition by Substring of a Column | columnName |
       --------------
            201
            202
            210
            211

I have 5 million records and I want to partition the table by the substring of it's column.

For example, I want all 20s, 21s, 22s, and so on to be on the same partition.

SUBSTRING(columnName, 1, 2)

If the columnName is an INT, you can partition it as follows

CREATE TABLE mydata
(
    id INT NOT NULL AUTO_INCREMENT,
    myColumn INT NOT NULL,
    PRIMARY KEY (id,myColumn),
    KEY (myColumn)
)
PARTITION BY RANGE (myColumn) (
    PARTITION p000 VALUES LESS THAN (010),
    PARTITION p010 VALUES LESS THAN (020),
    PARTITION p020 VALUES LESS THAN (030),
    PARTITION p030 VALUES LESS THAN (040),
    PARTITION p040 VALUES LESS THAN (050),
    PARTITION p050 VALUES LESS THAN (060),
    PARTITION p060 VALUES LESS THAN (070),
    PARTITION p070 VALUES LESS THAN (080),
    PARTITION p080 VALUES LESS THAN (090),
    PARTITION p090 VALUES LESS THAN (100),
    PARTITION p100 VALUES LESS THAN (110),
    PARTITION p110 VALUES LESS THAN (120),
    PARTITION p120 VALUES LESS THAN (130),
    PARTITION p130 VALUES LESS THAN (140),
    PARTITION p140 VALUES LESS THAN (150),
    PARTITION p150 VALUES LESS THAN (160),
    PARTITION p160 VALUES LESS THAN (170),
    PARTITION p170 VALUES LESS THAN (180),
    PARTITION p180 VALUES LESS THAN (190),
    PARTITION p190 VALUES LESS THAN (200),
    PARTITION p200 VALUES LESS THAN (210),
    PARTITION p210 VALUES LESS THAN (220),
    PARTITION p220 VALUES LESS THAN (230),
    PARTITION p230 VALUES LESS THAN (240),
    PARTITION p240 VALUES LESS THAN (250),
    PARTITION p250 VALUES LESS THAN (260),
    PARTITION p260 VALUES LESS THAN (270),
    PARTITION p270 VALUES LESS THAN (280),
    PARTITION p280 VALUES LESS THAN (290),
    PARTITION p290 VALUES LESS THAN (300),
    PARTITION p300 VALUES LESS THAN (310),
    PARTITION p310 VALUES LESS THAN (320),
    PARTITION p320 VALUES LESS THAN (330),
    PARTITION p330 VALUES LESS THAN (340),
    PARTITION p340 VALUES LESS THAN (350),
    PARTITION p350 VALUES LESS THAN (360),
    PARTITION p360 VALUES LESS THAN (370),
    PARTITION p370 VALUES LESS THAN (380),
    PARTITION p380 VALUES LESS THAN (390),
    PARTITION p390 VALUES LESS THAN (400),
    PARTITION p400 VALUES LESS THAN (410),
    PARTITION p410 VALUES LESS THAN (420),
    PARTITION p420 VALUES LESS THAN (430),
    PARTITION p430 VALUES LESS THAN (440),
    PARTITION p440 VALUES LESS THAN (450),
    PARTITION p450 VALUES LESS THAN (460),
    PARTITION p460 VALUES LESS THAN (470),
    PARTITION p470 VALUES LESS THAN (480),
    PARTITION p480 VALUES LESS THAN (490),
    PARTITION p490 VALUES LESS THAN (500),
    PARTITION p500 VALUES LESS THAN (510),
    PARTITION p510 VALUES LESS THAN (520),
    PARTITION p520 VALUES LESS THAN (530),
    PARTITION p530 VALUES LESS THAN (540),
    PARTITION p540 VALUES LESS THAN (550),
    PARTITION p550 VALUES LESS THAN (560),
    PARTITION p560 VALUES LESS THAN (570),
    PARTITION p570 VALUES LESS THAN (580),
    PARTITION p580 VALUES LESS THAN (590),
    PARTITION p590 VALUES LESS THAN (600),
    PARTITION p600 VALUES LESS THAN (610),
    PARTITION p610 VALUES LESS THAN (620),
    PARTITION p620 VALUES LESS THAN (630),
    PARTITION p630 VALUES LESS THAN (640),
    PARTITION p640 VALUES LESS THAN (650),
    PARTITION p650 VALUES LESS THAN (660),
    PARTITION p660 VALUES LESS THAN (670),
    PARTITION p670 VALUES LESS THAN (680),
    PARTITION p680 VALUES LESS THAN (690),
    PARTITION p690 VALUES LESS THAN (700),
    PARTITION p700 VALUES LESS THAN (710),
    PARTITION p710 VALUES LESS THAN (720),
    PARTITION p720 VALUES LESS THAN (730),
    PARTITION p730 VALUES LESS THAN (740),
    PARTITION p740 VALUES LESS THAN (750),
    PARTITION p750 VALUES LESS THAN (760),
    PARTITION p760 VALUES LESS THAN (770),
    PARTITION p770 VALUES LESS THAN (780),
    PARTITION p780 VALUES LESS THAN (790),
    PARTITION p790 VALUES LESS THAN (800),
    PARTITION p800 VALUES LESS THAN (810),
    PARTITION p810 VALUES LESS THAN (820),
    PARTITION p820 VALUES LESS THAN (830),
    PARTITION p830 VALUES LESS THAN (840),
    PARTITION p840 VALUES LESS THAN (850),
    PARTITION p850 VALUES LESS THAN (860),
    PARTITION p860 VALUES LESS THAN (870),
    PARTITION p870 VALUES LESS THAN (880),
    PARTITION p880 VALUES LESS THAN (890),
    PARTITION p890 VALUES LESS THAN (900),
    PARTITION p900 VALUES LESS THAN (910),
    PARTITION p910 VALUES LESS THAN (920),
    PARTITION p920 VALUES LESS THAN (930),
    PARTITION p930 VALUES LESS THAN (940),
    PARTITION p940 VALUES LESS THAN (950),
    PARTITION p950 VALUES LESS THAN (960),
    PARTITION p960 VALUES LESS THAN (970),
    PARTITION p970 VALUES LESS THAN (980),
    PARTITION p980 VALUES LESS THAN (990),
    PARTITION p990 VALUES LESS THAN MAXVALUE
);

Why partition it this way ??? If it is an INT, here is how the data is grouped

Partition p200 will hold rows with myColumn 200 - 209
Partition p210 will hold rows with myColumn 210 - 219
Partition p220 will hold rows with myColumn 220 - 229

Partitioning works with DATEs and INTs, not CHARs or VARCHARs