Conversion failed when converting the varchar value 'Jan 1 1900 12:00AM' to data type int I'm getting the following error :

Conversion failed when converting the varchar value 'Jan  1 1900 12:00AM' to data type int.

I have tried casting the problematic field which is in my third CTE(CTE4)

WHEN max(Date_Last_Data_Changed )= '1900-01-01 00:00:00.000' THEN 'Has never been changed'

I have looked at different places

Conversion failed when converting the varchar value to data type int.

Conversion failed when converting the varchar value 'simple, ' to data type int

But still was not able to figure out this problem

 ;with CTE1 AS (
    Select Year_Last_Accesed,
    (Count(Year_Last_Accesed)* 100 / (select Count(*) From _Admin_Database_Objects_Analysis_Tbl)) as Percentage_Result
    From _Admin_Database_Objects_Analysis_Tbl
    Group by Year_Last_Accesed
    ),

     CTE2 AS(
    Select Year_Last_Accesed,
    (Count(Year_Last_Accesed)* 100 / (select Count(*) From _Admin_Database_Objects_Analysis_Tbl)) as [10 Years older Tables]
    From _Admin_Database_Objects_Analysis_Tbl
    Where Date_Created > DATEADD(year,-10,GETDATE())
    Group by Year_Last_Accesed
    )

    ,
    CTE4 AS (

    SELECT Object_Name,
           MAX(Date_Last_Data_Changed) as [Last Time Data Changed]
           ,CASE
                WHEN max(cast(Date_Last_Data_Changed as varchar(255))) >1900 THEN 'Has Been changed recently'
                WHEN max(Date_Last_Data_Changed )= '1900-01-01 00:00:00.000' THEN 'Has never been changed'
                end as New_Date_Last_Data_Changed
           FROM _Admin_Database_Objects_Analysis_Tbl
           GROUP BY Object_Name

    ),

    CTE5 AS (
        SELECT
        ct4.New_Date_Last_Data_Changed
        ,(Count(New_Date_Last_Data_Changed)* 100 / (select Count(*) From _Admin_Database_Objects_Analysis_Tbl)) as [Data Changed %]

        FROM _Admin_Database_Objects_Analysis_Tbl ct5
        Inner Join CTE4 ct4
        on ct4.Object_Name = ct5.Object_Name
        Group by ct4.New_Date_Last_Data_Changed
    )
    Select Percentage_Result,
            case
            When c1.Year_Last_Accesed  = '1900' then 'Never Used'
            When c1.Year_Last_Accesed  = '2018' then '2018'
            end as Year_Last_Accesed
            ,[10 Years older Tables]

     FROM CTE1 c1
     INNER JOIN CTE2 c2
     ON c1.Year_Last_Accesed=c2.Year_Last_Accesed
     INNER JOIN CTE5 CT5
     ON ct5.New_Date_Last_Data_Changed = c1.Year_Last_Accesed

You have:

max(cast(Date_Last_Data_Changed as varchar(255))) >1900

So you're converting to a string, and then comparing that string to an integer. I think you meant:

max(convert(char(8), Date_Last_Data_Changed, 112)) > '19000101'

Or just

max(Date_Last_Data_Changed) > '19000101'

Note that converting to varchar(255) without a style (e.g. 112) yields a string that will not sort the way you expect, since it is sorting as a string, and when you put month name first, it doesn't work. Dec < Jan, for example.


is it problematic to cluster across two datacenters w/aag? In general is it problematic to cluster across two datacenters that are up to 15-miles apart?

For this one particular project the approach is the following?

2-Node Failover Cluster across two datacenters, one node per datacenter
local SAN disks, no shared disks
'Node and FileShare Majority' at third location on-campus
employ AAG, failover will be manual; asynchronous mode

Is anyone implementing something like this with success??!?

Thanks so much for the help!!

PASQLDBA it depends really on how much data is being written to the database and the link between these two DC's and also how far out of sync you can tolerate the two servers keeping your RTO/RPO in mind.

This is by no means any indication of what your milage may be but we have the following setup running quite well.

2 Node Cluster, 1 node in each DC
DC's are a good 20 - 30kms apart
Fiber link between the DC's
Local SAN disks
Node and File Share Majority
Availability Groups setup, auto failover with synchronous commit

This houses multiple AG's with ~50 databases with no noticeable performance hit over the single instance it was on before.

Hope this helps.


Setup Cassandra Database I have a very simple cassandra database with just one table containing the following records:

device ID       - long integer representing the device the 'value'
came from - primary key
tag ID          - long integer representing
the identity of the 'value'
timestamp       - long integer
representing the date/time the 'value' was collected
value          - decimal number or alphanumeric value, which is the data value itself

Each device transmits about 20 values every 30 seconds. we have about 500 devices which will increase to 3000 over the next 18 months or so.
once the data is stored it is never modified or even deleted (at this time).

My cassandra node sits on an AWS EC2 server with the system files on the root disk, cassandra data files on their own disk, and commit files on their own disk. all disks are currently general purpose SSD.

This data is accessed for presentation and analysis (read only) and 90% of all activity is on data that is less than 90 days old.

Question 1:
I want to be able to automatically transfer data records that are more than say 360 days old from SSD onto cheaper magnetic disk (still on EC2).
how should we set up the cassandra database server to achieve this?

Question 2:
I have an existing mysql database with over 5 years worth of very similar data which I want to migrate into the cassandra datbase.
Writing the migration tool is fairly straight forward, however, my concern is that when I execute the migration tool all the data will end up on the SSD because cassandra thinks it is all new data, rather than data that has aged in real time. Is it possible to tell cassandra, from the PHP migration tool, to put data older than 360 days onto the magnetic disk defined in question 1?

Q2: If I understand your question correctly, I don't think you want to do that nor should you use Cassandra if that's what you're trying to do. Cassandra is meant to spread data across nodes so that if one node fails then you don't lose your data or crash the entire cluster. If you are putting only specific data one one single hard drive, than you're compromising how Cassandra works.

What's your use case for Cassandra? Why don't you transfer the SSD drive data to a commodity disk then just unplug the SSD and use it for something else? Why do you need Cassandra specifically? Do you have an insane number of writes? What size data are you working with? If you're read heavy you might be better off with a different database.


Select the latest changed row in a table I have a Mysql table test with the fields id, changedate, name.

some names have multiple changedates.

I can select all rows with

SELECT id, changedate, name FROM `test` ORDER BY changedate;

How can I select only the newest rows of that table for each name?

If you don't need the id (or any other column, not included in the question) but only the name and the newest changedate, then you only need a simple GROUP BY query - and an index on (name, changedate) for efficiency:

SELECT name, MAX(changedate) AS changedate
FROM test
GROUP BY name ;

If you need more columns, one way to solve is to put the above query in a derived table and JOIN to the original table:

-- Query 1
SELECT t.*
FROM test AS t
  JOIN
  ( SELECT name, MAX(changedate) AS changedate
    FROM test
    GROUP BY name
  ) AS g
    ON  g.name = t.name
    AND g.changedate = t.changedate ;

You could also use a LEFT JOIN or a NOT EXISTS solution:

-- Query 2a
SELECT t1.*
FROM test AS t1
  LEFT JOIN test AS t2
    ON  t1.name = t2.name
    AND t1.g.changedate < t2.changedate
WHERE t2.name IS NULL ;

-- Query 2b
SELECT t1.*
FROM test AS t1
WHERE NOT EXISTS
      ( SELECT *
        FROM test AS t2
        WHERE t1.name = t2.name
          AND t1.changedate < t2.changedate
      ) ;


SQL Server - ensuring a complex relation on insert I have some related tables:
Application - stores a list of applications
ApplicationInstall - for each application, multiple installations are present (I have a foreign key ApplicationUid)
SecurityPrincipal - for each ApplicationInstall I have many principals (I have a foreign key ApplicationInstallUid).
I have also a table SecurityPrincipalType, linked to Application through a 1-to-many.
I would like to have a rule that guarantees that when I insert a record in SecurityPrincipal its Type comes from a SecurityPrincipalType linked to the correct Application.
I didn't rename the tables because I usually prefer to have names to think about. But I think the following scenario is equivalent:
Table A n..1 Table B 1..n Table C
And I want to enforce that a column in C contains only values coming from A, but linked via B.
I'm considering:

triggers
foreign keys?
application level checks (but I would prefer to have some check at the db level)

What should I do?

You can use a Foreign Key for this if you add a unique constraint on Application(Id,SecurityPrinicpalType), something like this:
--drop table if exists ApplicationInstallSecurityPrinicpal
--drop table if exists ApplicationInstall
--drop table if exists Application

go

create table Application
(
  Id int identity not null,
  SecurityPrincipalType int,
  constraint pk_Apppliction
    primary key (Id),
  constraint ak_Application
    unique(Id,SecurityPrincipalType)
)

create table ApplicationInstall
(
  ApplicationId int not null,
  Id int identity not null,
  constraint pk_ApplicationInstall
    primary key (ApplicationId,Id),
  constraint fk_ApplicationInstall_Application
    foreign key (ApplicationID)
    references Application(Id)
)

create table ApplicationInstallSecurityPrinicpal
(
  ApplicationId int not null,
  ApplicationInstallId int not null,
  Id int identity not null,
  SecurityPrincipalType int,
  constraint pk_ApplicationInstallSecurityPrinicpal
    primary key (ApplicationId,ApplicationInstallId,Id),
  constraint fk_ApplicationInstallSecurityPrinicpal_ApplictionInstall
    foreign key (ApplicationId,ApplicationInstallId)
    references Application(ApplicationId,Id)
  constraint fk_ApplicationInstallSecurityPrinicpal_Appliction
    foreign key (ApplicationId,SecurityPrincipalType)
    references Application(Id,SecurityPrincipalType)
)


What happens to non clustered index when included column is updated using update statement? Question on non clustered index with included columns (DB - MS SQL Server).
I read blog Optimized Non-clustered Index Maintenance which gives information on query plans when update statements is executed and clustered index and non clustered index are defined for table.
I have question on non clustered index with included columns.
I'm referring same example provided by blogger

CREATE TABLE T (PK INT, A INT, B INT, C INT, D INT, E INT)
CREATE UNIQUE CLUSTERED INDEX TPK ON T(PK)

CREATE INDEX TB ON T(B)
CREATE INDEX TCD ON T(C,D)
CREATE INDEX TE ON T(E)

--  this is new non clustered index with included columns

CREATE INDEX TF ON T(E) INCLUDE(A)

INSERT T VALUES(0, 10, 20, 30, 40, 50)

UPDATE T SET A = 19

If no index TF is defined then only update on clustered index will be performed and nonclustered index insert and delete operations will not be performed.
But what will happen when TF is defined?

Just as when you update a value in an index that's stored in leaf and non-leaf pages, all those pages get updated with the new value. Columns stored at the leaf level only through include are updated when you update values. It's possible for this to lead to spage splits too.


Postgres jsonb complex query I have a table on PostgreSQL 10 that looks like:

CREATE TABLE forms (id serial, form jsonb);
INSERT INTO forms (id, form) VALUES (
  1, '"permissions": [ {"name": "perm-name-1", "who":["group1", "group2",]},
                       {"name": "perm-name-2", "who":["group1", "group3"]}]');
INSERT INTO forms (id, form) VALUES (
  2, '"permissions": [ {"name": "perm-name-1", "who":["group5", "group6"]},
                       {"name": "perm-name-2", "who":["group7", "group8"]}]');

The JSON for column "form" has more key-value pairs which are irrelevant here.

I need to query based on the permissions "name" and one or more "who" values e.g. :

Get all rows whose permission "name" is "perm-name-1" and "who" is any of "group1", "group5" should match all the rows of the example above.
Get all rows whose permission "name" is "perm-name-1" and "who" is just "group1" should match row with id 1.

Also, any insight/comment on a JSON schema which would help the database to perform better is welcomed!

I think the first level of arrays is not useful, especially if you are going to retrieve permissions by name.

I would store it like this:

CREATE TABLE forms (id serial, form jsonb);
INSERT INTO forms (id, form) VALUES
(1, '{"permissions":
         {"perm-name-1": {"who": ["group1","group2"]},
          "perm-name-2": {"who": ["group1","group3"]}}
     }'),
(2, '{"permissions":
         {"perm-name-1": {"who": ["group5","group6"]},
          "perm-name-2": {"who": ["group7","group8"]}}
     }');

Then both your queries are quite easy to write:

For the first one you can use the "any" operator ?|:

select *
from forms
where form -> 'permissions' -> 'perm-name-1' -> 'who' ?| array['group1', 'group5']

For the second one, the ? operator is enough:

select *
from forms
where form -> 'permissions' -> 'perm-name-1' -> 'who' ? 'group1'

You didn't ask for it, but you can also query for rows where a specific permissions contains all specified groups. That can be done using the ?& operator. The following would return the row with ID=1

select *
from forms
where form -> 'permissions' -> 'perm-name-1' -> 'who' ?& array['group1', 'group2'];

The following would return nothing:

select *
from forms
where form -> 'permissions' -> 'perm-name-1' -> 'who' ?& array['group2', 'group3'];


Changing the datatype of the primary key columns of partitioned tables I am currently blessed with the following situation:

We have a production database that is partitioned and uses primary keys of type int
Multiple (~20) tables that each contain > 100.000.000 rows, 2 of these are near to 2.000.000.000 rows
These tables all have a column of type int as primary key which increments by one each row
These tables are partitioned, using a partition function of type int, using RIGHT(0), so effectively all data is in the one and only partition

Since some tables are nearing the int32 maximum value, we must alter these column types to bigint. Here's where the problems start:

Cannot modify the type of a primary key column (this I could dodge by dropping the primary key, modifying the type, and recreating the primary key again), however, I still cannot modify the column type after dropping the primary key constraint, getting the following exception:

  {"The object 'XXX_XXX' is dependent on column
  'ABCD'. ALTER TABLE ALTER COLUMN ABCD failed because one or
  more objects access this column."}

As far as I can see there is a schema bound dependency of the partition scheme (and function) to the table preventing the datatype modification. There are no foreign keys referencing these primary keys.

I'm not seeing a solution to updating the column type on this database, let alone a production database. We can afford some maintenance downtime, say +- 60 minutes, but no longer. What are my options?

Additional information

I'm starting to believe my only option is to create new tables with the correct structure and pump the existing table data into the new ones...
Tables are already partitioned based on their primary key value (currently just 1 partition (RIGHT(0), future partitions should be possible for new values (RIGHT(x)), the boundary x is yet to be determined)
These are data-logging tables, only inserts, no updates or deletes
There is always DML executed on these tables except for maintenance windows, once every 6-8 weeks for about 90 minutes tops.
The indexes based on the primary key are all clustered
There are no non-clustered indexes on these tables

First before I provide a solution, I want to confirm your assumption:

  As far as I can see there is a schema bound dependency of the partition scheme (and function) to the table preventing the datatype modification.

You are correct; the fact that you have a partition function defined for a datatype of INT is preventing you from altering the data type of your column...  Sadly, you're kind of stuck with the current table because there's no way, to my knowledge, to adjust the datatype of a partition function without dropping/recreating it.... which you can't do because you have tables dependent upon it.  So basically this is the chicken-and-egg scenario.

  I'm not seeing a solution to updating the column type on this database, let alone a production database. We can afford some maintenance downtime, say +- 60 minutes, but no longer. What are my options?

One possible solution to your issue is to take advantage of Partitioned Views.  This functionality has been around forever and is often overlooked, but a Partitioned View will provide you with a way to sidestep your INT data limit by adding new data to a different table where the ID column is a BIGINT datatype.  This new table should also have a better partition function/scheme underlying it and hopefully will be maintained a little better going forward.  Any queries that are pointing to the old table would then be pointed to the Partitioned View and you'll not run into a data limit issue.

Let me explain with an example.  The following is a trivial recreation of your current partitioned table, consisting of one partition, as follows:

-- Create your very frustraiting parition scheme
CREATE PARTITION FUNCTION dear_god_why_this_logic (INT)
AS RANGE RIGHT FOR VALUES (0);

-- Scheme to store data on PartitionedData Filegroup
CREATE PARTITION SCHEME dear_god_why_this_scheme
AS PARTITION dear_god_why_this_logic ALL TO ([PRIMARY]);

-- Create Partitioned Table
CREATE TABLE dbo.TestPartitioned
(
    ID INT IDENTITY(1,1) PRIMARY KEY CLUSTERED,
    VAL CHAR(1)
) ON dear_god_why_this_scheme (ID);

--Populate Table with Data
INSERT INTO dbo.TestPartitioned WITH (TABLOCK) (VAL)
SELECT CHAR(ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) % 128)
FROM master.sys.configurations t1
    CROSS JOIN master.sys.configurations t2
    CROSS JOIN master.sys.configurations t3;

Let's say for argument's sake that I'm getting close to the INT limit (even though I'm obviously not).  Because I don't want run out of valid INT values in the ID column, I'm going to create a similar table, but use BIGINT for the ID column instead.  This table will be defined as follows:

CREATE PARTITION FUNCTION lets_maintain_this_going_forward (BIGINT)
AS RANGE RIGHT FOR VALUES (0, 500000, 1000000, 1500000, 2000000);

-- Scheme to store data on PartitionedData Filegroup
CREATE PARTITION SCHEME lets_maintain_this_going_forward_scheme
AS PARTITION lets_maintain_this_going_forward ALL TO ([PartitionedData]);

-- Table for New Data going forward with new ID datatype of BIGINT
CREATE TABLE dbo.TestPartitioned_BIGINT
(
    ID BIGINT IDENTITY(500000,1) PRIMARY KEY CLUSTERED,
    VAL CHAR(1)
) ON lets_maintain_this_going_forward_scheme (ID);

-- Add check constraint to be used by Partitioned View
ALTER TABLE dbo.TestPartitioned_BIGINT ADD CONSTRAINT CK_ID_BIGINT CHECK (ID > CAST(499999 AS BIGINT));

A few notes here:

Table Partitioning on the New Table

This new table is also going to be partitioned based on your requirements.  It's a good idea to think about future maintenance needs, so create some new file groups, define a better partition alignment strategy, etc.
 My example is keeping it simple, so I'm throwing all of my partitions into one Filegroup. Don't do that in production, instead follow Table Partitioning Best Practices, courtesy of Brent Ozar et. al.

Check Constraint

Because I want to take advantage of Partitioned Views, I need to add a CHECK CONSTRAINT to this new table.  I know that my insert statement generated ~440k records, so to be safe, I'm going to start my IDENTITY seed at 500k and create a CONSTRAINT defining this as well.
 The constraint will be used by the optimizer when evaluating which tables can be eliminated when the eventual Partitioned View is called.

Now to Mash the Two Tables Together

Partitioned Views don't particularly do well when you throw mixed datatypes at them when it comes to the partition column in the underlying tables.  To get around this, we have to persist the current ID column in your current table as a BIGINT value.  We're going to do that by adding a Persisted Computed Column, as follows:

-- Append Calculated Columns of New Datatype to Old Table
-- WARNING: This will take a while against a large data set and will likely lock the table
ALTER TABLE dbo.TestPartitioned ADD ID_BIG AS (CAST(ID AS BIGINT)) PERSISTED
GO

-- Add Constraints on Calculated Column
-- WARNING: This will likely lock the table
ALTER TABLE dbo.TestPartitioned ADD CONSTRAINT CK_ID_TestPartitioned_BIGINT CHECK(ID_BIG <= CAST(499999 AS BIGINT));
GO

-- Create a new Nonclustered index on ID_BIG to act as new "pkey"
CREATE NONCLUSTERED INDEX IX_TestPartitioned__BIG_ID__VAL ON dbo.TestPartitioned (ID_BIG) INCLUDE (VAL);

I've also added another CHECK CONSTRAINT on the old table (to aid with Partition Elimination from our eventual Partitioned View) and a new Non-Clustered Index that is going to act like a primary key index (because look ups originating from the Partitioned View are going to be occurring against ID_BIG instead of ID).

With the new Computed Column and Check Constraint in place, I can finally define the Partitioned View, as follows:

-- Build a Partitioned View on Top of the old and new tables
CREATE VIEW dbo.vw_TableAll
WITH SCHEMABINDING
AS
SELECT ID_BIG AS ID, VAL FROM dbo.TestPartitioned
UNION ALL
SELECT ID, VAL FROM dbo.TestPartitioned_BIGINT
GO

Running a quick query against this view will confirm we have partition elimination working (as you don't see any lookups occurring against the new table we've created):

SELECT *
FROM dbo.vw_TableAll
WHERE ID < CAST(500 AS BIGINT);

Execution Plan:

At this stage, you'll need to make a few changes to your application:

Stop inserting new records into the old table, and instead insert them into the new table.  We're not allowed to insert records into the Partitioned View because we're using IDENTITY values in the underlying tables.  Partitioned Views don't allow for this, so you have to insert records directly into the new table in this scenario.
Adjust any SELECT queries (that point to the old table) to point to the Partitioned View.  Alternatively, you can rename the old table (e.g. TableName_Old) and create the view as the old tables' name (e.g. TableName); how fancy you want to get here is up to you.

New Records to the New Table

At this point, new records should be getting inserted into the new table.  I'll simulate this by running the following:

--Populate Table with Data
INSERT INTO dbo.TestPartitioned_BIGINT WITH (TABLOCK) (VAL)
SELECT CHAR(ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) % 128)
FROM master.sys.configurations t1
    CROSS JOIN master.sys.configurations t2
    CROSS JOIN master.sys.configurations t3;
GO

Again, my Identity Seed is configured so I won't have any ID conflicts between the two tables.  The CHECK CONSTRAINTS on both tables should also enforce this.  Let's confirm that Partition Elimination is still occurring:

SELECT *
FROM dbo.vw_TableAll
WHERE ID > CAST(300000 AS BIGINT)
    AND ID < CAST(300500 AS BIGINT);

SELECT *
FROM dbo.vw_TableAll
WHERE ID > CAST(500000 AS BIGINT)
    AND ID < CAST(500500 AS BIGINT);

Execution Plans:

Do take note that most queries will likely span both tables.  While we won't be able to take advantage of partition elimination in these scenarios, the query plans should remain as optimal as possible with the benefit that you don't have to rewrite your underlying queries (if you decided to name the view the same as your old table).

What to do now?

Well this is wholly dependent upon you.  If the current records are never going to disappear and you're happy with performance from the Partitioned View, pour a celebratory beer because you're done.  Huzzah!

If you want to consolidate the old table into the new table, you're going to have to pick maintenance windows within which to do the next series of operations. Basically, you're going to drop the constraints on the tables (which will break the partition elimination component of the Partitioned View), copy your most recent records over from the old table to the new table, purge these records from the old table, and then update constraints (so the Partitioned View is able to take advantage of partition elimination once again).  Because of the volume of data you have in the existing tables, you may have to go through a few rounds of this process to consolidate everything.  Those steps are summarized as follows:

-- During a maintenance window, transfer old records to new table if you so choose
-- Drop Check Constraint while transferring over records
ALTER TABLE dbo.TestPartitioned_BIGINT DROP CONSTRAINT CK_ID_BIGINT;

-- Retain Identity Values
SET IDENTITY_INSERT dbo.TestPartitioned_BIGINT ON

-- Copy records into the new table
INSERT INTO dbo.TestPartitioned_BIGINT (ID, VAL)
SELECT ID_BIG, VAL
FROM dbo.TestPartitioned
WHERE ID > 300000

SET IDENTITY_INSERT dbo.TestPartitioned_BIGINT OFF

-- Enable Check Constraint after transferred records are complete, ensuring the Check Constraint is defined on the proper ID value
ALTER TABLE dbo.TestPartitioned_BIGINT ADD CONSTRAINT CK_ID_BIGINT CHECK (ID > CAST(300000 AS BIGINT));
GO

-- Purge records from original table
DELETE
FROM dbo.TestPartitioned
WHERE ID > 300000

-- Recreate Check Constraint on original table, ensuring the Check Constraint is defined on the proper ID value
ALTER TABLE dbo.TestPartitioned DROP CONSTRAINT CK_ID_TestPartitioned_BIGINT;
GO
ALTER TABLE dbo.TestPartitioned ADD CONSTRAINT CK_ID_TestPartitioned_BIGINT CHECK(ID_BIG <= CAST(300000 AS BIGINT));
GO

If possible, test this out in a non production environment.  I don't condone testing in production.  Hopefully this answer helps, and if you have any questions, feel free to post a comment and I'll do my best to get back to you quickly.


MongoDB storage of multi-dimensional arrays Using MongoDB 3.0.7 as the primary datastore. I connect to it using ReactiveMongo 0.11 on a JVM-based language.

My question is: how well does Mongo store multi-dimensional arrays? For image analysis, I'm storing multiple images in the form of Array[Array[Double]]. There will be 60 images in a single document, each around 250px width and height.

Is this array structure sufficient enough, or do I need to rethink my schema?

MongoDB can efficiently store these images provided their total size does not exceed the 16MB document limit. If you are likely going to this, you should consider redesigning your schema, otherwise your schema design will be fine.


Rollback the sql server data Is it possible to restore the rollback changes to a database? One of my client has done wrong updates on database so most of the tables have been updated with wrong data.
That database is having with Replication, So can anyone suggest me without or less downtime can i restore the database with last 2days of data. Thanks in advance

It is possible to rollback wrong updates without any downtime using ApexSQL Log. For maximal UPDATE reconstruction, the database should be in the Full recovery model, and a full chain of transaction logs is needed.

A full chain of transaction logs starts with a full database backup and is followed by all subsequent transaction log backups up to a point of the rollback.

How to recover SQL Server data from accidental UPDATE and DELETE operations

How to recover SQL Server data from accidental updates without backups

Disclaimer: I work for ApexSQL as a Support engineer


What does SELECT ~0; do? I've recently stumbled into an interesting Mysql query:

SELECT ~0;
results:
18446744073709551615

Which is the max value of a bigint unsigned. What does the tilde operator do in this case?

It selects the logical negation of 0 and returns it in the implicitly converted datatype.  Rather than selecting 8 bytes containing all 0's, it selects 8 bytes containing all 1's, returning the maximum possible unsigned value.


How to find the most common phrases in a varchar field I have a table which contains support tickets. Each ticket has a title and many of them have common phrases in them (XXX cannot connect to YYY, XXX requires a new YYY, XXX requires upgrading, etc. etc.)

I would like to do some analysis on the data and extract these common phrases along with a count of their usage.

Repeated character sequences would be acceptable, but ideally I would want to split the title into words and identify repeated sequences of words. Here is a simple example:

Title
------------------------------------------------
John cannot connect to SERVER01
The backup service cannot connect to the client
Mary cannot connect her laptop to the network

From this data I would like to be able to extract the following:

Phrase                                             Count
-------------------------------------------------- -------
cannot connect                                     3
cannot connect to                                  2

I have a plan to do this using a c# application and brute force, but can anyone suggest a more elegant solution?

So I ended up solving this with a C# application. I thought I would share my code in case anyone else needed something similar. It's using Entity Framework to do the data access.

        using (var ticketsContext = new CMS.Data.Tickets.TicketsContext("name=CMS"))
        {
            using (var analysisContext = new TicketTitleAnalysis.Analysis())
            {
                int lastTicketID = analysisContext.Phrases.Max(p => (int?)p.LastTicketID).GetValueOrDefault();

                var tickets = (from t in ticketsContext.Tickets where t.TicketID > lastTicketID orderby t.TicketID select new { t.TicketID, t.Title });

                foreach (var ticket in tickets)
                {
                    var titleWords = ticket.Title.ToLower().Split(new char[] { ' ' }, StringSplitOptions.RemoveEmptyEntries).Where(w => char.IsLetter(w[0])).ToArray();

                    for (int startingWord = 0; startingWord < titleWords.Length; startingWord++)
                    {
                        for (int numberOfWords = 1; numberOfWords <= titleWords.Length - startingWord; numberOfWords++)
                        {
                            var words = string.Join(" ", titleWords.Skip(startingWord).Take(numberOfWords));

                            if (words.Length > 5)
                            {
                                var phrase = analysisContext.Phrases.FirstOrDefault(p => p.Words == words);

                                if (phrase == null)
                                {
                                    phrase = new Phrase();
                                    phrase.Words = words;

                                    analysisContext.Phrases.Add(phrase);
                                }

                                phrase.LastTicketID = ticket.TicketID;
                                phrase.Count++;
                            }
                        }
                    }

                    analysisContext.SaveChanges();
                }
            }
        }


What kind of deadlock the deadlock_timeout config parameter detects in Postgresql? My overall goal is to get a 40P01/ERRCODE_T_R_DEADLOCK_DETECTED from postgres when an deadlock is detected. (To be more precise, I would like to get a LockAcquisitionException in Hibernate, which is the exception the 40P01/ERRCODE_T_R_DEADLOCK_DETECTED error code is translated in PostgreSQL81Dialect.java) I use Postgresql 9.6.

For this I thought I should set the deadlock_timeout and log_lock_waits config variables
as suggested by 19.12. Lock Management

  deadlock_timeout (integer) This is the amount of time, in
  milliseconds, to wait on a lock before checking to see if there is a
  deadlock condition.
  [...]
  When log_lock_waits is set, this parameter also determines the length
  of time to wait before a log message is issued about the lock wait. If
  you are trying to investigate locking delays you might want to set a
  shorter than normal deadlock_timeout.

I've set the following values in postgresql.conf

log_lock_waits = on         # log lock waits >= deadlock_timeout
deadlock_timeout = 5s

Now, when I create a deadlock situtation (from Java using Hibernate) I find the following in the postgresql.log

LOG:  process 17493 still waiting for ShareLock on transaction 322815 after 5000.464 ms
DETAIL:  Process holding the lock: 17495. Wait queue: 17493.
CONTEXT:  while updating tuple (132,35) in relation "publication"

However no 40P01/ERRCODE_T_R_DEADLOCK_DETECTED error is generated (and sent to the JDBC driver).

I digged a little but into the postgres source code and found that the dead lock detection done by setting deadlock_timeout/log_lock_waits is a different mechanism than the one generating 40P01/ERRCODE_T_R_DEADLOCK_DETECTED. The deadlock_timeout case is handled in  backend/storage/lmgr/proc.c
, while the 40P01/ERRCODE_T_R_DEADLOCK_DETECTED case in backend/storage/lmgr/deadlock.c

So, my questions are:

Are these actually two different types of deadlocks that are detected?
Is there a way to get an error when deadlock_timeout based deadlock detection happens?
How can actually a ERRCODE_T_R_DEADLOCK_DETECTED error be forced to happen?

UPDATE: the code I use to get into the deadlock situation goes like this (Spring/Java):

// This is in a Transaction managed by spring
Publication p = em.find(Publication.class, id);
p.setComment("Outer "+new Date());
em.flush();

// This utility method runs the lambda expression in a new Transaction
// using Spring's TransactionTemplate and tries to update
// the same Publication that is about to be updated by the
// "outer" transaction
Utils.runInSeparateTransaction(status -> {
    Publication p2 = em.find(p.getClass(), p.getMtid());
    p2.setComment("Inner "+new Date());
    return p2;
    // Would flush/commit after the return, but will "hang" instead.
    // Here I would expect the deadlock error but only get the
    // deadlock log.
};

// Outer transaction would commit at this point but will

A deadlock is a situation where multiple transactions conflict which each other in the locks they have cross-acquired. That situation is impossible to solve without aborting one of the transactions. The engine aborts such a transaction  with ERRCODE_T_R_DEADLOCK_DETECTED as the error.

The log_lock_waits is not primarily related to deadlocks, it's meant to alert about the situation that some queries are stuck waiting for locks, meaning that other transactions keep locks for too long.

  Are these actually two different types of deadlocks that are detected?

No. It seems you're confusing locks and deadlocks here.

  Is there a way to get an error when deadlock_timeout based deadlock detection happens?

It's automatic. If that error does not arise, it's because there is no deadlock.

  How can actually a ERRCODE_T_R_DEADLOCK_DETECTED error be forced to
  happen?

By creating an actual deadlock with at least two transactions conflicting. See postgres deadlock without explicit locking on S.O. for a simple example.


How to get SQL Server 2012 to use the invariant culture in format()? This has now been posted to Connect: The invariant culture identifier is rejected by the FORMAT() function in SQL Server 2012. (Connect has been retired.)
  This has now been posted to feedback.azure.com.

I'm trying to get the built-in format() function in SQL Server 2012 to use the invariant culture.

It is said in the documentation that the function accepts a .NET culture identifier as the third parameter. The identifier for the invariant culture is a blank string:

  You specify the invariant culture by name by using an empty string ("") in the call to a CultureInfo instantiation method.

That does not work with SQL Server however:

select format(getdate(), N'g', '');

  Msg 9818, Level 16, State 1, Line 1
  The culture parameter '' provided in the function call is not supported.

It is also documented that the invariant culture is associated with the English language, but not with any country/region. One would think this allows to pass 'en' as the identifier, but then, in .NET, CultureInfo.InvariantCulture.Equals(CultureInfo.GetCultureInfo("")) yields true, but CultureInfo.InvariantCulture.Equals(CultureInfo.GetCultureInfo("en")) gives false, so they aren't really the same.

So how do I make SQL Server to use the invariant culture?

(Note: I'm interested in making the built-in thing to work. I already have my own CLR functions to do this, I was going to remove them in favor of the now-built-in functionality).

  Microsoft SQL Server 2012 (SP1) - 11.0.3128.0 (X64)
      Dec 28 2012 20:23:12
      Copyright (c) Microsoft Corporation
      Business Intelligence Edition (64-bit) on Windows NT 6.0  (Build 6002: Service Pack 2) (Hypervisor)

I have found only one way to get FORMAT to use the invariant culture: pass a string starting with the null character for the culture parameter.

-- Setting session language to Traditional Chinese to demonstrate that this
-- isn't just falling back to the session language.
SET LANGUAGE N'Traditional Chinese';
PRINT N'Session Language:  ' + FORMAT(SYSDATETIME(), N'Y');
PRINT N'US English:        ' + FORMAT(SYSDATETIME(), N'Y', N'en-US');
PRINT N'Invariant Culture: ' + FORMAT(SYSDATETIME(), N'Y', NCHAR(0));

  Output:

Session Language:  2013年9月
US English:        September, 2013
Invariant Culture: 2013 September

(The above example is using the "year month" format string of 'Y' since it produces different output between en-US and the invariant culture.)

This works due to what appears to be a quirk of how the .NET implementation of CultureInfo looks up the culture data. The string comparisons it is doing seem to ignore any data following a null character, likely because they are being done within native code in the CLR via functions intended for use with null-terminated strings. It therefore sees a string starting with a null character as being equivalent to the empty string, which as you noted is the documented identifier for the invariant culture.

CultureInfo.InvariantCulture.Equals(CultureInfo.GetCultureInfo("\0foo")) //returns true

DISCLAIMER: This is obviously rather hackish since this behavior is not documented for FORMAT nor for CultureInfo. I have only tried this with SQL Server 2012 running with .NET 4.0.3. I personally would not rely on this behavior for anything that cannot be easily changed if it were to stop working due to a future update.


Apostrophe shows up as "ÔÇÖ" I pulled data out of a database and the apostrophe is showing up as "ÔÇÖ".  What character set is this from?

That specific character is from the character set Chinese_Hong_Kong_Stroke_90

There doesn't seem to be an easy way to "figure it out" from the output.  The most reliable way is to query the collation off the database.

select collation_name from sys.databases;


SQL: Storing Formatting in Data I have always taught that it is incorrect to store formatted data in a table. Examples include:

+----------------+----+
| Data           | OK |
+----------------+----+
| 0370101234     |  ✓ |
+----------------+----+
| 03 7010 1234   |    |
+----------------+----+
| (03) 7010 1234 |    |
+----------------+----+

(Why doesn’t SO support tables?!)

The data is an Australian phone number which is generally 10 digits long, including the area code.

Including the format spacing or characters introduces ambiguity, so it is harder to sort or search.

The question is: is there anything in the standards or in relational theory which covers this issue?

EDIT

This isn’t a question about telephone numbers as such. I was illustrating a point with data that should be stored in its simplest form, even if it is to be displayed with formatting characters.

Other examples include the $ sign which is clearly never stored with a currency value, and the thousands separator which has its own issues.

Formatting is meta-data.  If you include formatting in a column, you are mixing meta-data with data, which is clearly not a great idea.  I'm not a theory-guru, but I'd bet that is listed somewhere as a no-no.

For your specific example, which I know is only an example, you may actually want to store the format in a separate table.  Below is an example for SQL Server.  I must stress, this is only an example of how you might approach the problem by abstracting the format away from the data.  Please note, I'm not saying this is the best way, or even an appropriate way for your scenario.  Simply that it MIGHT work, as in the output shown below.

IF OBJECT_ID('dbo.PhoneNumbers') IS NOT NULL
DROP TABLE dbo.PhoneNumbers;

IF OBJECT_ID('dbo.PhoneFormats') IS NOT NULL
DROP TABLE dbo.PhoneFormats;

CREATE TABLE dbo.PhoneFormats
(
    PhoneFormatID int NOT NULL IDENTITY(1,1)
        CONSTRAINT PK_PhoneFormats
        PRIMARY KEY CLUSTERED
    , FormatDetails varchar(30) NOT NULL
);

INSERT INTO dbo.PhoneFormats (FormatDetails)
VALUES ('(###) ###-####')
    , ('#### ###-###');

CREATE TABLE dbo.PhoneNumbers
(
    ClientID int NOT NULL
    , PhoneNumber bigint NOT NULL
    , PhoneFormatID int NOT NULL
        CONSTRAINT FK_PhoneNumber_Format
        FOREIGN KEY
        REFERENCES dbo.PhoneFormats(PhoneFormatID)
);

INSERT INTO dbo.PhoneNumbers (ClientID, PhoneNumber, PhoneFormatID)
VALUES (1, 2125551212, 1)
    , (2, 2300123456, 2);

SELECT pn.ClientID
    , FormattedNumber = FORMAT(pn.PhoneNumber, pf.FormatDetails)
FROM dbo.PhoneNumbers pn
    INNER JOIN dbo.PhoneFormats pf ON pn.PhoneFormatID = pf.PhoneFormatID;

the output:

+----------+-----------------+
| ClientID | FormattedNumber |
+----------+-----------------+
|        1 | (212) 555-1212  |
|        2 | 2300 123-456    |
+----------+-----------------+

This can be made to support currency symbols and thousands-separators exactly the same way.


How does DBCC SHRINKFILE() affect backups? I have a pile of SQL 2005 databases that have been around for a very long time and, for varying reasons, experienced transaction log autogrowth. Regular backups now keep the transaction log usage under control, but the files are still large and fragmented.

I'd like to reclaim some of that space and boost performance by reducing the number of VLFs. However, I'd also prefer not to disrupt our scheduled backups. How does DBCC SHRINKFILE() affect the transaction log backup chain? Or does it? Does this change if I add TRUNCATEONLY to the command?

Note that this is a one-time fix. I'm well aware that regularly shrinking logfiles is a Bad Thing - had these databases been configured with rational initial sizes and autogrowth settings and had backups run perfectly every day of their history, this would not be an issue.

Per Microsoft Support
http://support.microsoft.com/kb/272318, SQL Server shrinks the log file by removing as many VLFs as it can.  It does so while trying to hit a target size without messing up your backup chain.

However, if you do use TRUNCATE_ONLY, the sequence gets messed up, so you'll need to either do that right before your scheduled backup or do full backup right after that.


How should i index this? i always had this question and i hope you can help me with this, how should i index created_at if i need to order results based on date.

Example:

COMMENTS TABLE:

ID (UUID)
ENTRY_ID (UUID)
USER_ID (UUID)
CONTENT
FEATURED
STATUS
CREATED_AT

I have this indexes:

PRIMARY: ID
INDEX: ENTRY_ID
INDEX: ENTRY_ID | CREATED_AT (to get latest entry comments)
INDEX: USER_ID
INDEX: USER_ID | CREATED_AT (to get latest user comments)
INDEX: STATUS | CREATED_AT (to get comments marked as spam ordered by older first)
INDEX: CREATED_AT (to get latest comments in entire db)

Is it right or i can skip some indexes?

You don't need single column indexes on fields that are also the first column in multi column indexes.

However you shouldn't specify the column order just based on that. For best performance, you want the column that has the highest cardinality (ie "uniqueness") first in the column order.

I think that you should just get rid of the CREATED_AT single column index and keep the other single column ones.

The 3 double column indexes should all  have the CREATED_AT field first ordered DESC for maximum efficiency.


Tracking changes on a table I have a users table. I want to keep track of all the changes I have done, name, phone etc. I've thought to create an extra table changes that will store the id of the previous and the next version of the user. I keep all the users record. Is this a good approach?

Update

I've also thought to use a separate table for the history see below

Or, to use a single table and track its version from the timestamp or the id (since it's increment), but I consider that as a poor solution.

Your design has a big disadvantage: Getting just the current values (which you will work with most of the time) is an expensive operation that requires a join.

Your system would be much faster if you have a table with the current data and an extra table for historical information, consisting of all columns of users and a valid_until timestamp column, with a primary key over (user_id,valid_until).


MS SQL DB design help I'm stuck with table design and query FOR XML

Sync engine for Ads requires XML schema in this format

<attribute1>value</attribute1>
<attribute2>value</attribute2>…
<lookup_list>
<lookup_item code="installations">220</lookup_item>
<lookup_item code="heating">228</lookup_item>
<lookup_item code="permits">233</lookup_item>
<lookup_item code="parking">162</lookup_item>
<lookup_item code="garden">166</lookup_item>
<lookup_item code="electronics">167</lookup_item>
</lookup_list>
<attributeN>value</attributeN>…

That lookup list is separate table that looks like this:

Lookup_item_id  code            lookup_item_title
220         ad_flat.installations   Town gas
221         ad_flat.installations   City water
224         ad_flat.heating_type    gas
226         ad_flat.heating_type    wood

For normal attributes I created this FOR XML query with sub-query for images that are in other table,

    SELECT
    attribute1,
    attribute2,
    (SELECT
    images.image_name AS image
    FROM images
    WHERE images.realestateID = realestates.ID
    FOR XML PATH (''), TYPE
    ) AS 'image_list'
FROM (SELECT DISTINCT
*
FROM realestates) realestates
FOR XML PATH ('ad_item'), ELEMENTS, root ('ad_list')

but for this lookup_list I have no idea how to design table and relations for that and how to write a query FOR XML export, please if someone have an idea I will be very grateful.

The inner part you can create like this

DECLARE @table TABLE(Lookup_item_id INT, code VARCHAR(100),lookup_item_title VARCHAR(100));
INSERT INTO @table  VALUES
 (220,'ad_flat.installations','Town gas')
,(221,'ad_flat.installations','City water')
,(224,'ad_flat.heating_type','gas')
,(226,'ad_flat.heating_type','wood');

SELECT t.code AS [@code]
      ,t.Lookup_item_id AS [*]
FROM @table AS t
FOR XML PATH('lookup_item'),ROOT('lookup_list');

The result

<lookup_list>
  <lookup_item code="ad_flat.installations">220</lookup_item>
  <lookup_item code="ad_flat.installations">221</lookup_item>
  <lookup_item code="ad_flat.heating_type">224</lookup_item>
  <lookup_item code="ad_flat.heating_type">226</lookup_item>
</lookup_list>

This you can integrate into your select within paranthesis like a column (scalar sub-select).


Best way to automatically reset sequence/identity once a year I need to have a identity/sequence/counter of some sort reset annually. What is the best way of doing this automatically (i.e. I don't want the front end application calling a stored procedure or executing SQL to do this).

If it matters the database will be at least SQL Sever 2012.

I will probably be using an identity column, but if there is a better way to do it with sequences instead, I am certainly open to that.

You could make a SQL Agent Job to use DBCC CHECKIDENT with the RESEED option at the right moment.


Facing error during query execution I wrote a query in which I want to merge two tables and also create a log table for the data.

as you can see at first I declared the @DimvisitorLog. This is for data log
I checked all type of fields and the are all ok. I mean there is nothing wrong with matching datatypes but I face this error:

I would guess that you need to also insert the visitorcode; and your spacing looks weird in the image.

merge SalesTrainingDW.dbo.Visitor      as target
  using SalesTrainingStage.dbo.Visitor as source
     on   target.VisitorCode =  source.VisitorCode
      and target.VisitorName <> source.VisitorName
      when not matched then insert (VisitorCode, VisitorName)
        values (source.VisitorCode, source.VisitorName)
      --output $action, deleted.VisitorCode, deleted.VisitorName --changed to reflect M. Ali's point
      output $action, inserted.VisitorCode, inserted.VisitorName
        into @DimVisitorLog (ActionX, VisitorCode, VisitorName);

insert into SalesTrainingDW.dbo.VisitorLOG (VisitorCode, VisitorName, ActionX, stamp)
select VisitorCode, VisitorName, Actionx, stamp=getdate()
  from @DimVisitorLog
  where ActionX = 'insert';  --changed to reflect M. Ali's point

You can also skip the table variable like this:

insert into SalesTrainingDW.dbo.VisitorLOG (VisitorCode, VisitorName, ActionX, stamp)
  select VisitorCode, VisitorName, ActionX, stamp=getdate()
  from (
    merge SalesTrainingDW.dbo.Visitor      as target
      using SalesTrainingStage.dbo.Visitor as source
         on   target.VisitorCode =  source.VisitorCode
          and target.VisitorName <> source.VisitorName
          when not matched then insert (VisitorCode, VisitorName)
            values (source.VisitorCode, source.VisitorName)
          --output $action, deleted.VisitorCode, deleted.VisitorName --changed to reflect M. Ali's point
          output $action, inserted.VisitorCode, inserted.VisitorName
      ) as i (ActionX, VisitorCode, VisitorName)
      where ActionX = 'insert'

Since this operation is only inserting values, you can skip merge and do an insert. Unless you are going to use the extra options merge gives you on output:

insert SalesTrainingDW.dbo.Visitor (VisitorCode, VisitorName)
  output ActionX='insert', inserted.VisitorCode, inserted.VisitorName, stamp=getdate()
    into @DimVisitorLog (ActionX, VisitorCode, VisitorName, stamp)
  select  VisitorCode, VisitorName
    from  SalesTrainingStage.dbo.Visitor s
      where not exists ()
        select 1
          from SalesTrainingDW.dbo.Visitor v
          where s.visitorcode=v.visitorcode
          and s.visitorname=v.visitorname
          );

or with a left join instead of not exists:

insert SalesTrainingDW.dbo.Visitor (VisitorCode, VisitorName)
  output ActionX='insert', inserted.VisitorCode, inserted.VisitorName, stamp=getdate()
    into @DimVisitorLog (ActionX, VisitorCode, VisitorName, stamp)
  select  VisitorCode, VisitorName
    from  SalesTrainingStage.dbo.Visitor s
      left join SalesTrainingDW.dbo.Visitor v on s.visitorcode=v.visitorcode and s.visitorname=v.visitorname
    where v.visitorcode is null;

and you could also skip the table variable with either of those inserts:

insert into SalesTrainingDW.dbo.VisitorLOG (VisitorCode, VisitorName, ActionX, stamp)
  select i.VisitorCode, i.VisitorName,  ActionX = 'insert', stamp=getdate()
  from (
    insert SalesTrainingDW.dbo.Visitor (VisitorCode, VisitorName)
      output inserted.VisitorCode, inserted.VisitorName
      select  VisitorCode, VisitorName
        from  SalesTrainingStage.dbo.Visitor s
        where not exists (
          select 1
            from SalesTrainingDW.dbo.Visitor v
            where s.visitorcode=v.visitorcode
              and s.visitorname=v.visitorname
              )
  ) as i;


Summation of distinct rows - SQL Server I am pretty sure I am making a basic mistake, but I can't figure it out for the life of me.  I am trying to get a simple report of total number of unique employees, number of employees who are male, number of employees who are female, and finally the total gross pay for the entire month

My Query:

SELECT COUNT(DISTINCT prg.PrgEEID) AS 'Emp Total'
  , SUM(CASE WHEN eep.EepGender='M' THEN 1 ELSE 0 END)
  , SUM(prg.prgsuigross) as '2017 Gross'
  , MONTH(prg.prgpaydate) AS '2017 Month'
  FROM PayReg prg
JOIN EmpPers eep
  ON prg.prgeeid=eep.eepEEID
WHERE prg.PrgStateSui like 'HI%'
  AND YEAR(prg.prgpaydate)='2017'
  AND prg.PrgCheckAddMode in ('A','R','M')
GROUP BY MONTH(prg.prgpaydate)
ORDER BY MONTH(prg.prgpaydate)

Results:

33  58  92131.95    1
32  77  127300.16   2
32  88  159734.80   3
32  68  121667.13   4
32  61  96918.07    5
32  71  125784.19   6
33  82  92163.50    7
33  88  114839.29   8
32  92  134167.71   9
30  64  109269.71   10
30  61  93108.24    11
32  77  96319.64    12

I know the male total is 24 for month one, but I am getting 58 .. and that is because i am getting each transaction for the month and not the unique number of males.  I tried using an OVER PARTITION BY Clause, and that only made it worse.

COUNT() ignores NULL values, and you can use DISTINCT in COUNT() as well.

Try:

  , COUNT(DISTINCT CASE WHEN eep.EepGender='M' THEN eep.eepEEID ELSE NULL END)

(assuming that eep.eepEEID is the unique ID for an employee).

You're doing a unique count of the employee's ID (for males) and NULL (for not males); the NULLs are ignored, and the DISTINCT means that each unique ID will only be counted once.


Are heaps considered an index structure or are they strictly a table structure without index? Inspired by this post: https://twitter.com/#!/SQLChicken/status/102930436795285505

Heaps: Are they considered an index structure or are they strictly a table structure without index?

An index implies there is data order to the rows. Given that a heap isn't created with any such order and doesn't maintain an order either, it's just a method of storing table data.

There are a few examples (say, dm_db_index_physical_stats) where to specify a heap, you have to enter an index id of 0. While this seems to contradict what I just said, I think this is just a magic value to simplify the API and its usage; nothing more.


How to find value for specific dates for a process stored in table I have a table called as tab where we store all the process running on machine in below format

proc_name     start_date     Duration
proc1         24-Jul-18      0:0
proc2         24-Jul-18      0:0
proc3         24-Jul-18      0:0
proc4         24-Jul-18      0:0
proc1         25-Jul-18      0:01
proc2         25-Jul-18      0:02
proc3         25-Jul-18      0:04
proc4         25-Jul-18      0:03
proc1         26-Jul-18      01:0
proc2         26-Jul-18      02:0
proc3         26-Jul-18      02:0
proc4         26-Jul-18      04:0

I want to see output as per below format:

Proc_name   Date1  Duration(of date1) Date2 Duration(of date2) Date3 Duration(of date3)
proc1       24_jul-18  0:0            25-Jul-18 0:01           26-Jul-18 01:0
proc2       24_jul-18  0:0            25-Jul-18 0:02           26-Jul-18 02:0
proc3       24_jul-18  0:0            25-Jul-18 0:04           26-Jul-18 02:0
proc4       24_jul-18  0:0            25-Jul-18 0:03           26-Jul-18 04:0

Details will depend on the database engine, but I think Pivot is the key search term you want.  An approach that can probably be adapted for use with many database engines is described on SO here: https://stackoverflow.com/questions/5846007/sql-query-to-pivot-a-column-using-case-when.  SQL Server has had a PIVOT operator since version 2008: https://docs.microsoft.com/en-us/sql/t-sql/queries/from-using-pivot-and-unpivot.

Martin


Add column for subsequent values for a record I have a table structured as follows:

CREATE TABLE t (
  HHID      varchar2(1),
  INDIV_ID  int,
  PRIM      varchar2(1)
);

insert into t values ('A',       10,       'Y');
insert into t values ('A',       11,       'N');
insert into t values ('A',       12,       'N');
insert into t values ('A',       13,       'N');
insert into t values ('B',       14,       'N');
insert into t values ('B',       15,       'Y');
insert into t values ('C',       16,       'Y');
insert into t values ('C',       17,       'N');

What I need is to convert this to the following:

HHID    PRIMARY   SECOND  THIRD  FOURTH FIFTH ....
 A       10        11      12     13
 B       15        14
 C       16        17

Big issue is that the number of possible added columns is unknown, I just need the primary INDIV_ID to show up as the primary and any additionals to be capture in subsequent columns.

Every answer I've looked at right now only suggests answers for the case for a simple pivot of a row id to the column ID however, I haven't seen a pivot that manages to place subsequent records of the same HHID as an additional column.

To get the primary value first, then the values by indiv_id, assign each row a number starting at 1 for each hhid:

row_number() over (
  partition by hhid order by prim desc, indiv_id
) rn

Once you've got that, you pivot by the values 1, 2, 3, ..., N.

with rws as (
  select hhid, indiv_id,
         row_number() over (
           partition by hhid order by prim desc, indiv_id
         ) rn
  from   t
)
  select * from rws
  pivot (
    min(indiv_id) for rn in (
      1, 2, 3, 4
    )
  );

HHID   1    2    3        4
A        10   11       12       13
B        15   14   <null>   <null>
C        16   17   <null>   <null>

If you know (or can make a reasonable guess...) as to the max number of rows/hhid you're done. If you want the columns in the output to change, there's no simple solution.

You could XML pivot:

with rws as (
  select hhid, indiv_id,
         row_number() over (
           partition by hhid order by prim desc, indiv_id
         ) rn
  from   t
)
  select * from rws
  pivot xml (
    min(indiv_id) for rn in ( any )
  );

H
-
RN_XML
------------------------------------------------------------------------------------------------------------------------
A
<PivotSet><item><column name = "RN">1</column><column name = "MIN(INDIV_ID)">10</column></item><item><column name = "RN"
>2</column><column name = "MIN(INDIV_ID)">11</column></item><item><column name = "RN">3</column><column name = "MIN(INDI
V_ID)">12</column></item><item><column name = "RN">4</column><column name = "MIN(INDIV_ID)">13</column></item></PivotSet
>

B
<PivotSet><item><column name = "RN">1</column><column name = "MIN(INDIV_ID)">15</column></item><item><column name = "RN"
>2</column><column name = "MIN(INDIV_ID)">14</column></item></PivotSet>

C
<PivotSet><item><column name = "RN">1</column><column name = "MIN(INDIV_ID)">16</column></item><item><column name = "RN"
>2</column><column name = "MIN(INDIV_ID)">17</column></item></PivotSet>

But that's kinda cheating as you still have one column. And you need to parse the XML...

Or you can fall back on dynamic SQL:

var cur refcursor;
declare
  in_list varchar2(10);
begin
  with rws as (
    select distinct row_number() over (
             partition by hhid order by prim desc, indiv_id
           ) rn
    from   t
  )
    select listagg(rn, ',') within group (order by rn)
    into   in_list
    from   rws;

  open :cur for 'with rws as (
  select hhid, indiv_id,
         row_number() over (
           partition by hhid order by prim desc, indiv_id
         ) rn
  from   t
)
  select * from rws
  pivot (
    min(indiv_id) for rn in ( ' || in_list || ' )
  )';

end;
/

print :cur;

H          1          2          3          4
- ---------- ---------- ---------- ----------
A         10         11         12         13
B         15         14 <null>     <null>
C         16         17 <null>     <null>


How do I find out when a node was added to an availability group? Ideally, I am looking for T-SQL that returns two columns: the node name, and the date/time that node was added to the availability group, for all nodes in a given availability group.

This is as far as I can see not possible with a single T-SQL statement as individual nodes hold only their own create date.

The sys.availability_replicas page makes reference to a column create_date. This is the datetime value of when the node was added.

To find the create_date for each replica you would need to first connect that replica and query the table for that instance only as below.

SELECT [replica_server_name],
    [create_date]
FROM [master].[sys].[availability_replicas]
WHERE replica_server_name = @@SERVERNAME


deleting small number of rows hangs in tsql database So I'm just trying to delete some bad data from a one of our test databases. I've identified the rows to be deleted, and have written a simple query to handle this.

However, when run, the query hangs on 4% complete, and just sits there for hours. Our databases are hosted on Azure - For this test database we're running a Standard SO with 10 DTUs.

What am I missing here?

Might be that cascading foreign key that table [Shift Offer] has pointing to table [Availability], slows down your delete operation

Try following:

1) create nonclustered index on column [Shift Offer][availability_id_fk]
and try running your query again; see if nonclustered index speeds things up

2) if still running too long, try disable the foreign key at all (run sql command)

alter table [Shift Offer] nocheck constraint [paste your constraint name
here I guess its availability_id_fk]

run delete command, and if succeeds and works fast, you will have to manually delete records from [Shift Offer] table, if it had any rows with availability_id_fk values that were deleted from [Availability] table, and  then enable foreign key back


Conditional WHERE in plpgsql function I'm writing function in plpgsql and stuck with following question: my function has parameter service_id_input and query. I want to create additional clause in WHERE section if this variable is not null. For example

SELECT
    MIN(time_from) AS min_0,
    MAX(time_to) AS max_0,
    CEIL((MAX(time_to) * 60 - MIN(time_from) * 60) / reception_duration_input) AS diff_0,
    user_id AS user_id_0,
    workingtimetable.date AS date_0
FROM
    workingtimetable
WHERE
    -- IF service_id_input is not null then add another restriction
    department_id = department_id_input AND date >= NOW() :: DATE
GROUP BY user_id, date
ORDER BY date ASC;

How can I do it?
Full code of function:

CREATE OR REPLACE FUNCTION find_days_business(IN reception_duration_input INTEGER, IN department_id_input BIGINT, IN service_id_input BIGINT)
      RETURNS TABLE(avail_records NUMERIC, busy_records BIGINT, percentage NUMERIC, day_date DATE) AS
    $BODY$
    DECLARE
    BEGIN
      CREATE TEMP TABLE sum_of_avail_records ON COMMIT DROP AS
        SELECT
          SUM(diff_0) AS sum_0,
          date_0
        FROM (
               SELECT
                 MIN(time_from)                                                             AS min_0,
                 MAX(time_to)                                                               AS max_0,
                 CEIL((MAX(time_to) * 60 - MIN(time_from) * 60) / reception_duration_input) AS diff_0,
                 user_id                                                                    AS user_id_0,
                 workingtimetable.date                                                      AS date_0
               FROM workingtimetable
               WHERE
                 -- IF service_id_input is not null then add another restriction
                 department_id = department_id_input AND date >= NOW() :: DATE
               GROUP BY user_id, date
               ORDER BY date ASC
             ) AS perday
        GROUP BY date_0;

      CREATE TEMP TABLE num_of_busy_records ON COMMIT DROP AS
        SELECT
          COUNT(appointments_book_records.id) AS count_1,
          appointments_book_days.date         AS date_1
        FROM appointments_book_records
          INNER JOIN appointments_book_days ON appointments_book_records.appointment_day_id = appointments_book_days.id
        WHERE appointments_book_days.date >= NOW() :: DATE
        GROUP BY appointments_book_days.date
        ORDER BY appointments_book_days.date;

      RETURN QUERY
      SELECT
        sum_0,
        count_1,
        (count_1 / sum_0 * 100),
        date_0
      FROM sum_of_avail_records
        LEFT OUTER JOIN num_of_busy_records ON date_0 = date_1;
END;$BODY$
LANGUAGE plpgsql;

@Ypercube's comment basically answers your core question.

But your function is also much slower and more complicated than necessary:

CREATE OR REPLACE FUNCTION find_days_business(_reception_duration int
                                            , _department_id bigint
                                            , _service_id bigint)
  RETURNS TABLE (avail_records numeric
               , busy_records bigint
               , percentage numeric
               , day_date date) AS
$func$
BEGIN
 RETURN QUERY
 SELECT s.sum_diff, n.ct, ((n.ct * 100) / s.sum_diff ), s.date
 FROM  (
    SELECT p.date, sum(p.diff) AS sum_diff
    FROM  (
       SELECT w.date
            , ceil(((max(w.time_to) - min(w.time_from)) * 60) / _reception_duration) AS diff
       FROM   workingtimetable w
       WHERE  w.date >= now()::date
       AND    w.department_id = _department_id
       -- another restriction IF _service_id is not null:
       AND   (_service_id IS NULL OR <another restriction>)
       GROUP  BY w.user_id, w.date
       ) p          -- perday
    GROUP  BY p.date
 ) s                -- sum_of_avail_records
 LEFT   JOIN (
    SELECT d.date, count(*) AS ct
    FROM   appointments_book_records r
    JOIN   appointments_book_days    d ON r.appointment_day_id = d.id
    WHERE  d.date >= now()::date
    GROUP  BY d.date
 ) n USING (date)   -- num_of_busy_records
 ORDER  BY s.date;
END
$func$  LANGUAGE plpgsql;

Major points

Temporary tables are more expensive and not necessary here. Simple subqueries do the job.
Don't include columns in the SELECT list that you don't need for anything. Dead weight. (You also don't need to include GROUP BY expressions in the SELECT list.)
Don't ORDER BY in subqueries. While Postgres typically keeps the order in the outer SELECT for simple queries, this is completely arbitrary and not reliable. There is no provision in the SQL standard that would require propagation of ORDER BY in subqueries to the outer query.

If your are looking for fully dynamic query predicates or arbitrary dynamic query elements with optimized query plans, you'll have to use dynamic SQL and EXECUTE:

PostgresSQL dynamic execute with argument values in array
Robust approach for building SQL queries programmatically


Any reasons at all *not* to use MySQL in this scenario I want to know if there is any reasons at all not to use MySQL in this scenario.

  2 servers behind a virtual IP with MySQL master-master replication

Goal: redundancy, want this to run 24/7 servicing a liferay cluster

I have plenty of reasons why to use this (1 I want your most pessimist thoughts.

(1

We are using this already in an similar situation and are considering using something else for a new setup. We have access to s MSSQL cluster that are more than capable of handling the 24/7 demand.

Top reasons to use MySQL (as you see they are manly "political")

It's how we have done it in the past, so less surprises expected
We(2 will have more control over performance as we can add memory/cpu to the (virtual) db-servers ourselves when we want
No need to migrate export data from MySQL to a new DB

(2

The MSSQL cluster is run by the "windows"-crew whilst I belong to the "*nix"-crew. So while the cooperation between us is good there is naturally a bit more fuss/delay when you have to ask someone else to do something rather than doing it yourself.

Update

Two days after this question was asked the decision what solution to use was made, so any answers(3 that would have helped make that decision is now not needed (this time anyhow).

If anyone want to generalize this question (as Jeff suggested) please do.

(3

When this update was written there was only one answer

Here are the cons that I see, this being a somewhat subjective question:

There are political reasons for and against this setup.

I don't know if I can be any more clear than that.

I would think this would be a huge benefit, so I would use this as my main selling point, because otherwise I don't see why not use the existing infrastructure to do what you need:

No need to migrate export data from MySQL to a new DB

Note that that's really going to turn into a con for you pretty quickly, cos everyone will say "just put a stub in that converts the MySQL requests into MSSQL" and that's going to be the negative all over again.


You can combine a GROUP BY to find the max() value and a window function:

select date_trunc('hour', "time") as "time",
       sum(max(value)) over (order by date_trunc('hour', "time"))
from the_table
group by date_trunc('hour', "time")
order by date_trunc('hour', "time")

This works, because the window function is applied after the GROUP BY. Using an order by in the window definition turns a "regular" sum into a running total.

To round the timestamp to other intervals, see these two questions on Stackoverflow

What is the fastest way to truncate timestamps to 5 minutes in Postgres?
postgresql date_trunc to arbitrary precision?


Can't restart or stop Firebird 1.5 on linux I have an ubuntu server running firebird 1.5. It was working fine, but recently it started hanging (firebird does not respond anymore). Even when trying to restart or stop the server, it shows an error:

>sudo service firebird1.5-super stop
 * Stopping Firebird 1.5 server manager...
Unable to complete network request to host "localhost".
-Failed to establish a connection.
-Connection reset by peer
can not attach to server

What can be happening?

I think I found the solution, but I believe it is more related to software then database (So if someone knows how to move this to stack overflow please do this).

The problem was that when hibernate (the persistence layer) works with Firebird 1.5, it doesn't create the temporary tables used by hibernate like in other new databases, instead it creates actual tables with the prefix HT_ (HT_SOMETABLE). I tried to deactivate this with a "hack", until now it's working fine, no more hanging and nothing unusual has happened because of this hack.

This is what I did:

I created a custom Firebird dialect to stop hibernate from creating these tables:

import org.hibernate.dialect.FirebirdDialect;

public class FirebirdHackedDialect extends FirebirdDialect {

    @Override
    public String getCreateTableString() {
        String sql = "HackParaNaoCriarTabelasNoFirebird: ";
        return sql;
    }

}

And set this dialect to the persistence.xml:

<property name="hibernate.dialect" value="com.yourpackage.etc.FirebirdHackedDialect"/>


Resources for SQL Server developer migrating to Oracle What resources can you recommend for a SQL Server developer wanting to learn Oracle basics?

I am looking for a comprehensive whitepaper or blog post describing differences between these systems and answering questions like How to create an IDENTITY column? or What data type is equivalent of float?.

Speaking more from a DBA perspective than a developer, this course is very comprehensive; there may be some things in there that are of value to you, particularly the Data Objects and Data Access chapters. It's written from an Oracle → SQL Server perspective but it works either way, it's about mapping concepts you already know to a slightly different vocabulary.

But, speaking from experience here, the biggest factor is not learning what datatypes correspond to what, but the shift in thinking about your application and particularly how to optimize it. For example, if you have come from SQL Server, you most likely believe that cursors are expensive and to avoid using them. Well, in Oracle every SELECT is a cursor "under the hood"; all using a cursor does is give you a handle to it. Similarly in SQL Server you will be used (unless you have been running in optimistic mode, which few people do) to readers blocking writers and writers blocking readers and coding to avoid that scenario; Oracle behaves very differently as it only supports MVCC anyway. Clustered indexes in SQL Server are use far more often than IOTs in Oracle (I don't actually know why this is, it's just something I've observed, Oracle people are missing a trick there).

Things like this are why apps ported from one to the other run into trouble... It's not just as simple as changing the SQL syntax (if it was you might as well have just used MySQL as you won't be getting the advantage of all these features you've paid for!)


Innodb_buffer_pool_reads ,Innodb_buffer_pool_read_requests reset Except from restart,when Innodb_buffer_pool_reads , Innodb_buffer_pool_read_requests parameters reset to zero?

I don't think you reset two specific status variables.

You can only reset all of them using FLUSH STATUS;

  This option adds the current thread's session status variable values to the global values and resets the session values to zero. Some global variables may be reset to zero as well. It also resets the counters for key caches (default and named) to zero and sets Max_used_connections to the current number of open connections. This information may be of use when debugging a query. See Section 1.7, “How to Report Bugs or Problems”.

Try running FLUSH STATUS; and see if this works.


pg_dump with ssl certificates I would like to run the pg_dump command from a remote host off my database server that only allows fully verified certificate based connections. How should I go about doing this? The man pages for 9.1 state the instructions for connecting via password from what I could see.

The following worked for me:

pg_dump "port=<port> host=<host> user=<user> dbname=<db> sslcert=<cert> sslkey=<key> sslrootcert=<ca.crt> sslmode=verify-ca" -f <file>


Clustered Index Misunderstanding I've read in couple of articles that clustered index is as the same as table, or if you read using clustered index, you have the whole record.
Some say, clustered index stores the whole records in deepest leaf of index.

It is foggy for me, does clustered index has a copy of data?!!
for sure not, but I don't why people say that.

I read couple of articles but still it is not clear to me. Is clustered index is something virtual? Does it use B-Tree structure (like non-clustered index)?

Is there any schematic or model that describes how SQL Server stores/manages clustered index?

some say, cluster index stores the whole records in deepest leaf of index.

Yes this is correct. The leaf pages of the clustered index contain the actual table rows. This is the actual data. There is no other primary copy of it held elsewhere.

The leaf pages also allocate a few bytes to hold the address (file and page) of the next and previous pages in index key order to form a doubly linked list. Following the index in key order may or may not be the same as following it in physical order depending on the level of fragmentation.

The rows on the individual leaf pages may not be written physically exactly in index key order but the slot array on the page, with pointers to the rows it contains, is ordered by key order.

  Does it use B-Tree structure (like non-clustered index)?

As long as the table doesn't fit on a single page then there will be one or more levels of the B-tree above the leaf. These higher levels contain index key values and pointers to pages in the level below to allow efficiently looking up key values. This is exactly the same principle used in nonclustered indexes too.

The leaf level pages of a clustered index are categorised as data pages and non-leaf pages as index pages. But the data pages are still part of the index.

(Image Source)

Also see my answer to a related question on Stack Overflow:

What do Clustered and Non clustered index actually mean?


Caching intermediary CTEs for multiple uses I'm working on this huge query that I chose to use CTEs. Due to the complexity of this query, I ended up having to reuse intermediary query results more than once in subsequent queries. The problem I'm facing can be summarized by the following toy code:

WITH cte1 AS (
    SELECT id, name
    FROM Manager)
, cte2 AS (
    SELECT id, name
    FROM Employee
    LEFT OUTER JOIN cte1 ON Employee.id = cte1.id)
SELECT *
FROM cte1

I have notice an unusual amount of indexes seeks in a fairly small table that was being queried in one the first queries which, according to the execution plan, were taking about 30% of the whole query. This led to me believe the base table was being queried multiple times as it was being referenced more than once in subsequent queries.

I was under the impression that intermediary results in a CTE were cached until the final statement. Reading Microsofts docs on CTEs, I couldn't find any reason to think otherwise.

Are intermediary CTEs cached for muliple uses within an WITH statement? If not, is there a way to cache it without rewriting the query?

Thanks!

No, there is no way to cache a CTE; it will typically be executed multiple times if it is referenced multiple times. If you want to avoid this, you can cache the results using a #temp table instead.

In some cases, the execution plan may be simple enough that the CTE is only materialized once (this doesn't technically mean it was "cached"), or for the results to be reused with a spool. I don't have any examples at hand and, even if I did, you couldn't rely on that to always be the case. Even something as simple as this:

CREATE TABLE dbo.OneTuple(id int PRIMARY KEY);
INSERT dbo.OneTuple(id) VALUES(1);
GO

;WITH cte AS
(
  SELECT id FROM dbo.OneTuple
)
SELECT id FROM cte
UNION ALL
SELECT id FROM cte;

Yields two identical index scans:

Also see this great Martin Smith answer on Stack Overflow and it is discussed on this feedback item.


How do you call an external C function from a SQL stored procedure? In Teradata 17, I am trying to call their own hash_md5 external function from my own SQL stored procedure:
replace procedure p()
sql security creator
begin
  declare l_hash varchar(32);
  set l_hash = hash_md5('abc');
end;

call p();

I get:

Failed [3706 : 42000] P:Syntax error: expected something between '(' and the string 'abc'.

I can call this function successfully in plain SQL:
select hash_md5('abc');

900150983CD24FB0D6963F7D28E17F72

I tried to pass a parameter in, instead of a hardcoded literal, but it made no difference.

hash_md5 is a C-UDF, which must be installed. Without qualified object name it will only be found if it's created in TD_SYSFNLIB or SYSLIB or the current session default database.
This should work:
set l_hash = mydb.hash_md5('abc');


PHP/MongoDB: Unset all fields in a document I want to use PHP to remove documents from a MongoDB collection, but it's a capped collection (and it's stuck that way until I can get more memory), which means I can't remove documents. I have to do the next best thing. How can I unset all the fields in a document?

$collection->update(array('name' => 'Amy'), array('$unset' => array(WHAT GOES HERE?)));

Of course, I could list all the fields I want to unset, but then I'd have to change the code any time a new field was added. Can I unset all fields without knowing the names of all the fields?

To remove all fields (aside from _id), you can just update with an empty document:

<?php
    $collection->update(array('name' => 'Amy'), array());
?>

That will update the document leaving only the _id field.

Note that this won't free up any of the preallocated space for your capped collection.


INSERT ... SELECT on a huge MySQL table I have isolated MySQL 5.5 with a huge table inside ( 3.5TB of data and yes, it is only 1 table ).

The goal is to copy a half of this table into another one on the same server. How to do that better and quicker? :

1) Just INSERT INTO new_table SELECT * FROM old_table WHERE ....
What will be MySQL behavior in this case? I expect that connection to MySQL will be broken by time out, however the query itself should run until all data are copied.
Or the MySQL will be just crashed with such query?

2) Using the same INSERT ... SELECT but selecting by chunks ( for example by 10000 rows). This should be definitely safer but much slower I guess.

What is the best way? Thank you in advance!

P.S. Before the insertion I will drop FK and indexes on new table + disable sql_log_bin and

Your option 2 will be the way to go, smaller chunks. Depending on the size of your rows, You could increase the rows to around 1M.


Turning a multiple row resultset into columns with multiple rows I currently have a resultset that looks like this:

Title                   Value
Start Time              1998-01-04 01:38:00.000
End Time                1998-01-04 01:43:00.000
Product Name            TESR
Run Time                325
Delay Time              0
Shutdowns               0
Shift                   2
Gross MSF               3.791682
Start Time              1998-08-12 12:43:00.000
End Time                1998-08-13 16:48:00.000
Product Name            FCC
Run Time                320
Delay Time              0
Shutdowns               0
Shift                   1
Gross MSF               3.791682
Start Time              1999-11-04 02:43:00.000
End Time                1999-11-04 03:48:00.000
Product Name            1/2" TESR
Run Time                325
Delay Time              0
Shutdowns               0
Shift                   2
Gross MSF               3.791682

What I would like to end up with is this:

Start Time              1998-01-04 01:38:00.000      1998-08-12 12:43:00.000
End Time                1998-01-04 01:43:00.000      1998-08-13 16:48:00.000
Product Name            TESR                         FCC
Run Time                325                          325
Delay Time              0                            0
Shutdowns               0                            0
Shift                   2                            2
Gross MSF               3.791682                     3.121

Basically looking like a spreadsheet with each group of records being put in a column to the right.

The original result is from a pivot of a flat table joined to a sub table that puts a title to the property fields. It is running on SQL Server 2008 R2. The data is coming from an HMI system and the client wants to see the data in the "spreadsheet" format.

You could try Pivot:  I think you'll need to order on some key that ties these records together (how do you know that the starttime of "TESR" is related to the EndTime of "TESR".  That probably needs to be in the ORDER BY clause below.

SELECT
    Title
    ,[1]
    ,[2]
    ,[3]
FROM
    (
        SELECT
            Title,
            Value,
            ROW_NUMBER() OVER (PARTITION BY Title ORDER BY Title) AS RN
        FROM
            YourResult
    ) RunInformation
    PIVOT (MIN(Value) FOR RN IN ([1],[2],[3])) AS PivotTable


postgres simple number to string mapping I have a an Enum at the programming language level which is stored as a simple integer on the table. Think:

APPLE = 1
GOOGLE = 2
MSFT = 3
AMAZON = 4
 ... (100s more)

I just wanna query the table and instead of the number return the corresponding string value. Is there an easier way of doing this without using case statement or a temp table:

SELECT
  CASE WHEN type = 1 THEN "APPLE"
  CASE WHEN type = 2 THEN "GOOGLE"
  CASE WHEN type = 3 THEN "MSFT"
  CASE WHEN type = 4 THEN "AMAZON"
  ...
  ELSE "UNKNOWN"
FROM t

Basically it's just a key look-up in a dictionary.

IMHO the easiest way is by using a lookup table.

create table lk (id int, name text);

insert into lk values
(1, 'apple'),(2, 'google'),(3, 'msft'),(4, 'amazon');

create table t (id serial, lk int);
insert into t (lk) values (2),(1),(3),(4);

select
    t.id,
    lk.name
from   t
join   lk
       on lk.id = t.lk;

id | name
-: | :-----
 2 | apple
 1 | google
 3 | msft
 4 | amazon

db<>fiddle here


Postgresql function equivalent to Excel's NORMSINV function? I need Excel's NORMSINV equivalent function for PostgreSQL, preferably plpgsql function. Can somebody help?
For reference, here is Excel's normsinv function explained:
https://support.microsoft.com/en-us/help/826772/excel-statistical-functions-normsinv


Oracle client in Windows 7: how to change looking path for sqlnet.ora For some security reasons I'm not able to amend the file sqlnet.ora, must meet security policies applied. Is it possible to change the search path of this file to some other drive/folder?

Thank you!

Did you try to set the environment variable TNS_ADMIN to the directory in which you have your sqlnet.ora file?


Improving Speed of Cross Apply I posted a question on how to create a relation between 2 tables based on a spatial field
https://stackoverflow.com/questions/63769885/t-sql-passing-column-to-subquery-for-filtering
The performance is terrible when the city has only 5k records and listing has 12k
(takes 54 seconds to run on sql server 2019, dual core i5)
Anything I can do to improve it? Execution plan is https://www.brentozar.com/pastetheplan/?id=BkiRk-74P
2nd Plan (based on the query in the answer by Kumar)
https://www.brentozar.com/pastetheplan/?id=HJSLs_L4D
This is the query
select l.*, city.*
from  listings l
cross apply (
     select  top (1)  c.UnicodeName, c.name, r.code as region, cn.code as country from cities c
     inner join regions r on r.regionid = c.regionid
     inner join Countries cn on cn.CountryId = r.countryid
     where c.location.STDistance(l.location) is not null
     order by c.Location.STDistance(l.location)
 ) as city

Indexes on cities and listings
CREATE SPATIAL INDEX [256_HHHH] ON [dbo].[Listings]
(
    [Location]
)USING  GEOGRAPHY_GRID
WITH (GRIDS =(LEVEL_1 = HIGH,LEVEL_2 = HIGH,LEVEL_3 = HIGH,LEVEL_4 = HIGH),
CELLS_PER_OBJECT = 256, PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO

CREATE SPATIAL INDEX [16_HHHH] ON [dbo].[Listings]
(
    [Location]
)USING  GEOGRAPHY_GRID
WITH (GRIDS =(LEVEL_1 = HIGH,LEVEL_2 = HIGH,LEVEL_3 = HIGH,LEVEL_4 = HIGH),
CELLS_PER_OBJECT = 16, PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO

Updated Query based on Kumar's Suggestion, which does not bring back any results.  it seems this code is assuming that listing and city have the exact same position.  They dont, I just want to pick the nearest city for each listing.
I also had to change = to STEquals as there were syntax errors otherwise.
;With CTE as
(
select l.*, c.RegionId, c.UnicodeName, c.name,c.location.STDistance(l.location) Locationdistance
from  listings l
inner join  cities c on l.location.STEquals(c.Location)=1

)

select c.*,r.code as region, cn.code as country
from CTE C
inner join regions r on r.regionid = c.RegionId
     inner join Countries cn on cn.CountryId = r.countryid
     where Locationdistance is not null
order by Locationdistance

Always verify if the join is returing correct number of rows from each join.
Try this,
;With CTE as
(
 select l.*, city.*
from  listings l
cross apply (
     select  top (1)  c.UnicodeName, c.name,c.regionid
     --, r.code as region, cn.code as country
     from cities c
     --inner join regions r on r.regionid = c.regionid
     --inner join Countries cn on cn.CountryId = r.countryid
     where c.location.STDistance(l.location) is not null
     order by c.Location.STDistance(l.location)
 ) as city
 )

 select c.*
  ,r.code as region, cn.code as country
 from CTE C
 inner join regions r on r.regionid = c.regionid
 inner join Countries cn on cn.CountryId = r.countryid
 OPTION (MAXDOP 1, RECOMPILE);

Has Estimated number of rows decrease.
Is difference between Actual number of rows and Estimated number of rows decrease.


Understanding MongoDB Aggregation Pretend that our collection has 1M document
Now if i do aggregation like this:

db.users.aggregate({
    $group: {
        _id : "$gender",
        avgAge : { $avg : "$age" }
    }
})

then how it will prepare the data for me?  Say i have a index on the id but not on the gender so is it gonna read all the 1M documents? if so then how does it scale?

Indices can speed up an aggregation when utilized in a $match or a $sort stage at the beginning of a pipeline.

With your example, no index will be used, since you don't use either. So every document will be accessed.


Relationship between stock and product I have a database should store sales, I created a table for products and one more table for stock I felt the relationship between them is many-to-many because a product in one stock may have different price from another stock, and one stock can have many products, so I created another table for create a relationship between these two tables by name of Stock_Product, but Now I fell it is wrong, because every time when I want to query from Sales table which contains the sales records I need to join 4 tables for selecting the details of product and stock.

So a single product can potentially have multiple prices at once? If this is the case then I don't see the need for the extra table. Create the products table, including product name, description plus whatever else you need. Then create the stock table, with a FK relationship to the products table product ID with quantity and price per unit. When a sale is made it can take the price from the stock table and enter it as a sale price in the sales table and update the quantity in the stock table.

The only thing that isn't clear is where you said a stock can have multiple products. The above seems to be a better method of handling this rather than having a product with multiple stock records and stock records with multiple products.


Best practice for database where the values can't change Suppose I have a table Inspection with a field for Place (the location where the Inspection occurred). The field is a foreign key to a Place table, where each place has a Name, among other things. The Place names can change over time (for instance the Place could be a restaurant where the name changes when its ownership changes), but we would like the Inspection to continue to hold the original value it was given when it was created.

Is there a best practice for something like this? One thought is to have two fields, one to store the foreign key, regardless of whether it's been updated, and another to hold a string value for the original Place name. Another thought is to simply make Places immutable. You would have to create a new Place every time the name changed, rather than updating existing Places. Perhaps you could even related one or more Places somehow so that you know that multiple rows in the table actually relate to the same physical location.

I suggest you to create a new table, InspectionHistory or something like this, with the following structure:

InspectionID|PlaceID|DateStart|DateEnd|Status

Here you would have foreign keys with your Inspection table and Place table and the information, you want to store. With such structure you can store several record about your Inspection places.


Having two data folder in my postgresql setup- Postgresql I am using centos 6 and installed Postgresql_9.4 using the following commands

yum  install postgresql postgresql-contrib postgresql-client pgadmin3
yum install postgresql-server

After that i verified my config file or "Show data_directory" command to verify that data folder path.
Its showing /var/lib/pgsql/9.4/data but am also having the another data folder in this location /var/lib/pgsql/data.

data path 1 --> /var/lib/pgsql/9.4/data

data path 2 --> /var/lib/pgsql/data

My question is which is my original data folder ??.
And Also my exact data folder is configured in config file means what is the use of another data folder ?

On a terminal, type

systemctl cat 'postgresql*'

That will show you where the PGDATA environment variable is pointing to when you start the service normally via systemd, and all the parameters used for starting and stopping the service.


Strategies to import big data into Oracle What strategies are available to import about 500 million small transactions every day from an external system?

Oracle becomes very slow with very frequent commits.

The transfer process which is reading the data from the external system can be implemented individually.

A NoSQL based solution is possible.

Update:
The datasource is a 'transaction journal' of a different DB system.

You didn't mention how the data comes in, but I would hazard a guess that it is through flat files. If this is the case, I worked at an order processing company that used Oracle for the back-end of the WMS we used. We regularly received 10 of millions of records a day.
We used SQL loader scripts to load the data. The caveat to that is that the data needs to be in the proper format. See here:

http://knoworacle.blogspot.com/2012/05/oracle-sql-loader-with-example.html

And of course from the makers:

http://docs.oracle.com/cd/B10501_01/text.920/a96518/aload.htm


How can I speed up a query that orders by a calculated field? I will try and give an example - this is not my table structure - I'm simply trying to outline the issue in order to find a solution...

Person
Id, Name

BrothersNames
Id, Name

SistersNames
Id, Name

PersonBrothers (join table)
PersonId, BrotherNameId

PersonSisters (join table)
PersonId, SisterNameId

OK - so imagine this database holds every person from a small country. The database holds a record of the names of everyone's brothers and sisters (it does not map a person to their brother or sister - just their names) so that we can find out statistics about names.

Obviously lots of names are shared so the join tables normalise this for us.

What I want to do is take one user and find out the number of matches of brother's names and number of matches of sister's names with every other user in the system, then add those two matches together and order by that descending.  So this would give us a list of users who have the most number of brothers and sister's names in common.

I'm really only interested in the top ten matches but I think I have to get the whole result set to work out the top ten matches.

Please note that in my actual data a person can have a million brothers or a milllion sisters.  This is where I'm getting performance issues.

This is how I'm calculating the matches for brothers and I do the same for sisters

select p.id, matches
FROM Person p
LEFT JOIN
        (
            SELECT
            COUNT(*) AS Matches,
            pbn.PersonId
            FROM PersonBrothersNames pbn
            INNER JOIN Brothersnames bn on pbn.BrothernameId =bn.Id
            inner join PersonBrothersName otherpbn on otherpbn.BrothernameId = bn.Id

            WHERE pbn.PersonId= @PersonId and pbn.PersonId <> otherpbn.personid
            GROUP BY  pbn.PersonId

        ) As BrothersNamesJoin ON BrothersNamesJoin.Person = p.Id

Please let me know if I should specify more info...
I am using SQL Server 2008 but is probably platform agnostic..

If you don't really need zero-second actuality, you could just run your query time to time and cache the results.

If you still need to have real-time data on this (sacrificing insert performance), I would do this:

Since self-joins are not allowed in indexed views, you need to create two copies of each table:

CREATE TABLE personBrother
        (
        personId INT NOT NULL,
        brotherName INT NOT NULL
        )

CREATE TABLE personBrother2
        (
        personId INT NOT NULL,
        brotherName INT NOT NULL
        )

Create an indexed view on their join:

CREATE VIEW
        commonBrothers
WITH SCHEMABINDING
AS
        SELECT  p1.personId AS p1,
                p2.personId AS p2,
                COUNT_BIG(*) AS cnt
        FROM    dbo.personBrother p1
        JOIN    dbo.personBrother2 p2
        ON      p1.brotherName = p2.brotherName
        WHERE   p1.personId < p2.personId
        GROUP BY
                p1.personId, p2.personId

CREATE UNIQUE CLUSTERED INDEX
        ux_commonBrothers_p1_p2
ON      commonBrothers (p1, p2)

CREATE INDEX
        ix_commonBrothers_cnt
ON      commonBrothers (cnt)

Same for sisters.

You should manually maintain these tables to have same data (write a trigger, insert/update/delete both etc).

Now we can easily get pairs with the most brothers and most sisters:

SELECT  TOP 1 WITH TIES
        *
FROM    commonBrothers
ORDER BY
        cnt DESC

All we need now is to fetch a greatest sum. Unfortunately, we cannot index a join of these views (it's a pure implementation flaw, there's no theoretical limitation for this).

So we need to do the following: the top pair cannot have less brothers than the top sis pair. Same holds for the sisters. So we have this query:

SELECT  TOP 1 WITH TIES
        cb.p1, cb.p2, cb.cnt + cs.cnt AS totalCnt
FROM    commonBrothers cb
JOIN    commonSisters cs
ON      cs.p1 = cb.p1
        AND cs.p2 = cb.p2
WHERE   cs.cnt >=
        (
        SELECT  MAX(cst.cnt)
        FROM    (
                SELECT  TOP 1 WITH TIES
                        p1, p2
                FROM    commonBrothers
                ORDER BY
                        cnt DESC
                ) cbt
        JOIN    commonSisters cst
        ON      cst.p1 = cbt.p1
                AND cst.p2 = cbt.p2
        )
        AND cb.cnt >=
        (
        SELECT  MAX(cbt.cnt)
        FROM    (
                SELECT  TOP 1 WITH TIES
                        p1, p2
                FROM    commonSisters
                ORDER BY
                        cnt DESC
                ) cst
        JOIN    commonBrothers cbt
        ON      cbt.p1 = cst.p1
                AND cbt.p2 = cst.p2
        )
ORDER BY
        totalCnt DESC

If the numbers of common brothers and sisters are correlated, this query will be very fast.

This solution has two drawbacks:

DML performance: if you insert or delete a record for a name shared by million brothers, the indexed view will get 2M inserts or delete. This is the price you pay for real-time query: the kind of data you are asking for cannot be easily indexed.
Persons with 0 brothers or 0 sisters will not be indexed. If there's a chance that top pair will not have brothers or sisters, you should amend the last query a little.