Can I read a .bak file without restoring it? A few years ago I had used a 3rd party product to read a full backup file and open it in SSMS just as if it was online. The .bak could be queried like any online database. I need to do that again, but can't remember the company that offered the product.  Does anyone know of a tool that will do this?

The database backup is from a huge database that will take hours to restore, so I didn't want to do that unless absolutely necessary.

After some more searching and a helpful link or two, I finally found that what I was looking for is Red Gate's SQL Virtual Restore.  Problem is, it apparently isn't around anymore. I found an informational link about it in Red Gate's site here. Great write up by Brant Fritchey on it from June 2011 here

One link I found had a comment that similar functionality is in SQL Compare Pro.  If it is, the advertising page for that product, doesn't mention that feature.


sql script hangs in SQLplus on Solaris Running an sql script in SQLplus on Solaris
Any idea why script hangs in line 9 '/' and not executed?

declare
 dummy number;
begin

 select 1
 into dummy from dual ;

end;
/

Have you committed or rolled back the transaction? It seems like transaction isn't committed.

You just need to either add an exit at the end of the script, or run it as sqlplus username/password@server/dbname < Setup.sql (i.e. redirected input, < instead of @).

You can check if that's the issue by just typing 'exit' in the hanging session.


A clarification of MERGE best practices I just wanted to ask a clarification about something on the MSDN from the "Optimizing MERGE Statement Performance" page.

I am working with a data warehouse that takes in records from many different databases and stores the data. All of the tables in my warehouse database basically follow this same pattern:

CREATE TABLE Foo (
    database_guid UNIQUEIDENTIFIER
    ,FooPk BIGINT
    ,Bar NVARCHAR(20)
    ,Qix NCHAR(10)
    ,CONSTRAINT [PK_Foo] PRIMARY KEY CLUSTERED (
        database_guid ASC
        ,FooPk ASC
        )
    )
GO

CREATE PROCEDURE [iv].[LoadSomeTable]
    @databaseGUID UNIQUEIDENTIFIER
AS
BEGIN
    SET NOCOUNT ON

    MERGE Foo
    USING #FooStaging AS Source
    ON Foo.FooPk = Source.FooPk AND Foo.database_guid = @databaseGUID
    WHEN MATCHED THEN
        UPDATE SET Bar = Source.Bar
                  ,Qix = Source.Qix
    WHEN NOT MATCHED THEN
        INSERT (database_guid, FooPk, Bar, Qix)
            VALUES (@databaseGUID, FooPk, Bar, Qix);
END
GO

CREATE TABLE #FooStaging (
    FooPk BIGINT
    ,Bar NVARCHAR(20)
    ,Qix NCHAR(10)
    )

--Data gets loaded in to #FooStaging from a C# call to SqlBulkCopy then calls iv.LoadSomeTable

The thing I am now concerned about is I just read this statement from that MSDN page

  Specify only search conditions in the ON <merge_search_condition>
  clause that determine the criteria for matching data in the source and
  target tables. That is, specify only columns from the target table
  that are compared to the corresponding columns of the source table. Do
  not include comparisons to other values such as a constant.

After reading that I am thinking I did my query wrong and my merge statement should be

MERGE Foo
USING #FooStaging AS Source
ON Foo.FooPk = Source.FooPk
WHEN MATCHED AND Foo.database_guid = @databaseGUID THEN
    UPDATE SET Bar = Source.Bar
              ,Qix = Source.Qix
WHEN NOT MATCHED THEN
    INSERT (database_guid, FooPk, Bar, Qix)
        VALUES (@databaseGUID, FooPk, Bar, Qix);

But that does not "feel" right to me because the database_guid field is part of the primary key so shouldn't it be included with the on? If I had it in the WHEN MATCHED and I upload one database with a FooPk of 1 then I upload a second database with a FooPk and a different @databaseGUID I am not sure if NOT MATCHED will fire or not (Just tested, it does not).

Which way is the correct way to use MERGE?

I would venture that your best approach is to use separate statements for each of the potential actions, and put them in a serializable transaction. You get to use tried and true statements with no funny semantics or "best practices" violations, and you get to avoid all of the problems I outline in this post, including wrong results bugs and potential index corruption:

MERGE with Caution


Performance optimization: Alternative to LEFT JOIN the same table twice I would like to extend the following query with the commented out LEFT JOIN. However, those lines make the query noticeably slower.
The query is used for an API to lookup user IDs by phone number and things need to be fast. So, is there a more performant alternative to joining the same table twice with different ON clauses?

set statistics time on

SELECT
    ivr.ID
FROM ivr
LEFT OUTER JOIN ADDR_MASTER am1
    ON am1.ID = ivr.ID
        AND am1.ADDR_TYPE = 'WORK' --WORK PHONE (only adds 35ms to lookup time)
--LEFT OUTER JOIN ADDR_MASTER am2
--  ON am2.ID = ivr.ID
--      AND am2.ADDR_TYPE = 'HOME' --HOME PHONE (fallback if not in IVR, adds 100+ ms :(
WHERE (PHONE1 = '0123456789'
OR PHONE2 = '0123456789'
OR PHONE3 = '0123456789'
OR am1.PHONE = '0123456789'
--OR am2.PHONE = '0123456789'
)

set statistics time off

Here are the plans:

Old
New

I think this will do what you are asking.

SELECT
    ivr.ID
FROM ivr
LEFT OUTER JOIN ADDR_MASTER am1
    ON am1.ID = ivr.ID
        AND am1.ADDR_TYPE IN ('WORK','HOME')
WHERE (PHONE1 = '0123456789'
OR PHONE2 = '0123456789'
OR PHONE3 = '0123456789'
OR am1.PHONE = '0123456789'
)

That said, there are a few things you could do differently.

This might be better, not sure, but as long as it's not slower it's probably easier to maintain.

SELECT
    ivr.ID
FROM ivr
LEFT OUTER JOIN ADDR_MASTER am1
    ON am1.ID = ivr.ID
        AND am1.ADDR_TYPE IN ('WORK','HOME')
WHERE '0123456789' IN (PHONE1, PHONE2, PHONE3, am1.PHONE)

Now, assuming that you do in fact only need the ivr.ID (at least not anything from ADDR_MASTER) you could do this:

SELECT
    ivr.ID
FROM ivr
WHERE '0123456789' IN (PHONE1, PHONE2, PHONE3)
    OR EXISTS ( SELECT 1 FROM ADDR_MASTER am1
                WHERE am1.ID = ivr.ID
                    AND am1.ADDR_TYPE IN ('WORK','HOME')
                    AND am1.PHONE = '0123456789' )

I'd try various combinations to see which if any of these are actually faster.


PostgreSQL, How to keep only one schema? I don't want some users be available to create schemas, and only use the public schema.

I'd like to remove the permission of creating schemas, but I can't found anything on PotsgreSQL 9.1 documentation. How can I do this?

The documentation of GRANT says the following:

  CREATE

For databases, allows new schemas to be created within the database.

For schemas, allows new objects to be created within the schema. To rename an
existing object, you must own the object and have this privilege for the containing schema.

So let's simply try this.  I created a user for this purpose, and if you look at it, it has the CREATEDB attribute, just for clarifying which CREATE privilege refers to what.

CREATE USER schematest WITH LOGIN CREATEDB PASSWORD 'secure';

REVOKE CREATE ON DATABASE access_test FROM schematest;

GRANT CREATE ON SCHEMA public TO schematest;

These result in the following behaviour:

CREATE SCHEMA other_schema;

ERROR: permission denied for database access_test
SQL state: 42501

So far so good: the user cannot create a new schema.  Let's see if it can create objects in the public schema:

CREATE public.simple_table(id integer);

Query returned successfully with no result in 181 ms.

Hooray!


Need to move backup folder We have a new SQL server (Dell PowerEdge R730xd). It has a RAID 5 for the OS and the database files for SQL, and also has two SSDs. It is running Windows Server 2012 R2 Standard and SQL Server 2014 Standard.

One of the SSD drives holds the log files for the SQL databases. The other holds the backup folders and files for the same.

We only have one instance.

One of the SSDs is the subject of troubleshooting, so we need to remove it and replace it with the replacement SSD.

The drive to replace is the drive that holds the SQL backup files.

What steps do I need to take to switch out the SSD with a new one so there is no corruption, lost data, etc?

I have been researching this and I think that I should be able to shut down the SQL services, copy over the backup folders to another drive temporarily, switch the SSDs (they are supposed to be hot swappable, but I may need to reboot if they don't show), copy the backup folders back over to the new SSD, start the SQL services and everything should work fine.

Is that correct? Do I need to take any other steps, like detaching databases or something? I am extremely new to SQL Server. Advice from an experienced DBA would be greatly appreciated.

Also, would these steps be the same if I needed to switch out the other SSD that currently holds the LDF files?

I did do some Google searches, but all I found were topics on how to move the backup folder location to a different drive. I have not done that before. I could change the backup files location in SQL Server Management Studio 2014 to a different drive, then go about switching SSDs, then change the location back, but I am not sure if there are "gotcha" steps that I would need to be aware of in that process, like checking for changing Windows permissions, disabling SQL service accounts, etc.

Also, I am asking because I am not sure if it is simple as it seems. I could just move the backup folders and files, but I am not sure what steps I need to take to shut down/disconnect/detach the SQL databases properly.

For backup folder :

If you mean the default backup folder as configurable in server properties --> database settings

You can easily change that location to the one you want. If your SSD has only the backup files, then it should be ok to hot swap when your backups are not running. Just change the default location to the new one in the above stated location. A restart of sql server service may be required for the new values to get reflected.

A more easy and managable approach will be to explicitely specify the backup location in your backup scripts (or just use Ola's backup solution).

For LDF (or MDF) files :

You need to use alter database .. modify file to first modify the location in the system tables.

Then offline the database/s and then physically copy the LDF (or MDF) to the new location.

Once above is done, bring the database online.

See this answer for more detail steps by Mike Fal.


Using Case with no column result I made this solution but it continues not returning any value If the user not exists.

select
    case when TypeId = 1 then 1
         when TypeId = 0 then 2
         when count(1) < 1 then 3
         end
    from Type
    where mail = 'name@test.com'
    group by Type

Application would be much better place to deal with such cases as Aaron Bertrand said but you can do it with SQL as well .

select
    case when TypeId = 1 then 1
         when TypeId = 0 then 2
         end
    from Type
    where mail = 'name@test.com'
UNION ALL
SELECT 3
FROM DUAL
 WHERE NOT EXISTS (SELECT NULL FROM Type WHERE mail = 'name@test.com')

**Depends on RDMS you're using you may or may not need specify "FROM DUAL".


Concept, ER model, "employee tracking" I have to achieve 3NF with following specifications:
Database where I will determine priority skills for specific jobs and track the employees ability to complete them. Train employees.

Example of job position and employee:
JP Constructor / needed skill AUTOCAD - excellent
Name Surname / JP constructor / skill AUTOCAD - not trained

Catalog of skills(group, code, title), groups of skills, for example:
-general skills (foreign languages, administrative skills,...)
-specialized software (AutoCAD, CADSS,...)

ID in catalog of skills is generated according to model:

Specialized skills..   X(group of skill)
RBR                    1001
ID                     X.1001

For job position skill and employee, there are 3 levels:
-familiar
-intermmediate
-excellent

Track skills for job position in department.

Output documents:
-overview of employees with specific skill
-comparing of skills for job position in department and skills of employees on that job position, to get the plan of acquiring knowledge for specific skills (list the employees who have skills with LEVEL not sufficient enough that job position requires)

I wrote detailed info above so you get the concept idea.

This is done in Oracle. I have .csv file with rows and data which I'll import but they're not in 3NF.
Now my attempt to apply this to tables/ER model:

I already have DEPARTMENT table pre-generated, so thats done. I thought about creating 3 tables to achieve model:

Primary key will be marked inside [], and foreign with ()

EMPLOYEES([ID], Name, Last_Name)
JOB_POSITION([ID], (EMP_ID), JP_Title, Group, [Department])
SKILLS([ID], (EMP_ID), SK_Title, Level)

Now, this is what I got as suggestion on my first model. And another, that I put the rest as materialized views. But somehow I don't know if this is the right way to do it.

In short, I'll have to track employees on certain job positions with certain skills(if they are sufficient or not).

I get kind of blurry on realization.

I'd really appreciate some direction here, thank you.

You'll probably end up with something looking a little like this:

This may be a little more involved than you first expected, but I'll take you through the whys and hows of it all ( doing this is generally much easier than explaining it, by the way ), starting with identifying objects.  Using "Noun-Verb" analysis, which is a fairly common OOAD technique, we start picking apart your requirements:
Right away, it becomes evident that at least Employee, Position / Job and Skill are needed.  Further along, a need for Department and Skill Group emerges, then finally Skill Level.  From here, things like Employee Surname are noted as well.
Some liberties are then taken here ( which is not abnormal in any way, considering the quality of the list of requirements you'd normally be extracting from a client ), but the noun list to be used for the general structure, in no particular order, is as follows:

Department ( D )
Department Code ( DC )
Department Name ( DN )
Position ( P )
Position Code ( PC )
Position Title ( PT )
Skill Group ( G )
Skill Group Code ( GC )
Skill Group Name ( GN )
Skill ( S )
Skill Code ( SC )
Skill Name ( SN )
Employee ( E )
Employee Code ( EC )
Employee First Name ( EF )
Employee Surname ( EL )
Level ( L )
Level Code ( LC )
Level Name ( LN )

Using these nouns, on a relatively simple level, relationships between them can be established using "is-a / has-a," that is, "Noun A is a Noun B" or "Noun A has a Noun B."  This is particularly useful for classifying our nouns into two categories, being an "attribute", an atomic entity, later called a column, or a "container," which is a collection, later called a table.  During the first round of this "is-a / has-a," the goal is generally to simplify the list, effectively removing the attributes from the picture, grouping them to their containers.
The naming convention used above goes a fairly long way to making this process simple.  For instance, an Employee ( E ) has an Employee First Name ( EF ) and an Employee Last Name ( EL ), forming one-to-one relationships between Employee ( E ) and the Employee First Name ( EF ) and Employee Last Name ( EL ) entities.  In this way, Employee ( E ) can be classified as a container, whereas Employee First Name ( EF ) and Employee Last Name ( EL ) become attributes.  Focusing on this reduction of nouns via one-to-one relationships, we can reduce the list above to the following, where the notation ( A: B, C ) means that entity A is a "container" with "attributes" B and C.  It follows that:

Department ( D: DC, DN )
Position ( P: PC, PT )
Skill Group ( G: GC, GN )
Skill ( S: SC, SN )
Employee ( E: EC, EF, EL )
Level ( L: LC, LN )

With this reduction, the one-to-many relationships can now be considered.  A Department ( D ) may have any ( a pluralized has a ) number of Positions ( P ) available, implicitly indicating a one-to-many relationship.  Extending the above notation, in addition to the attribute list, we can now add "compound" attributes.  More simply, a compound attribute is basically a container treated as an attribute.  This can be written as ( P: PC, PT, ( D: DC, DN ) ), which begins to highlight the places where foreign keys will materialize.  Similarly, Skill Group ( G ) may refer to a number of differing Skills ( S ).

( P: PC, PT, ( D: DC, DN ) )
( S: SC, SN, ( G: GC, GN ) )

The many-to-many relationships can then be addressed.  Position ( P ) may require any number of Skills ( S ), each with it's own Skill Level ( L ).  Conversely, a specific Skill ( S ) can be required for any number of Positions ( P ), at any particular Skill Level ( L ).  These associations manifest as a ( is a ) Position Skill ( PS ), effectively a "surrogate" container, having a compound attribute of Skill Level ( L ).  To use the phrasing above, each of these newly established Position Skills ( PS ) has a required Skill Level ( L ).  To maintain this many-to-one relationship between Position Skill ( PS ) and Level ( L ), a unique key is to be placed on the foreign keys Position ( P ) and Skill ( S ).

( PS: ( { U1 }( P: PC, PT, ( D: DC, DN ) ), { U1 }( S: SC, SN, ( G: GC, GN ) ), ( L: LC, LN ) )

A quick note: The { Un } is used to indicate both attributes ( themselves containers ) are part of a single unique key.  If the attributes were independently unique, we could use { U1 } and { U2 }.
Next, an Employee ( E ) can occupy any number of Positions ( P ), indicating that there is a Employee Position noun to be added to our list.  This particular relationship manifests over some period of time, thus having ( has a ) Start Date ( T1 ) and End Date ( T2 ).  However, there is no way to assert that a normally part-time Employee ( E ) could not be serving under two separate Positions ( P ) in a full-time manner simultaneously in the interim before a new hire, nor that a previous Employee ( E ) could be re-hired or otherwise return to a Position ( P ) previously held by that Employee ( E ).  This dilemma results in a true many-to-many relationship, with Start Date ( T1 ) and End Date ( T2 ) as attributes.  As Start Date ( T1 ) and End Date ( T2 ) are not functionally dependent on anything other than the relationship between Employee ( E ) and Position ( P ), this structure is not in violation of 3NF.

( EP: ( E: EC, EF, EL ), ( P: PC, PT, ( D: DC, DN ) ), T1, T2 )

The final piece comes in the form of the relationship between an Employee's ( E ) specific term of employment, Employee Position ( EP ), the relevant Position Skills ( PS ) and the quality at which those specific Position Skills ( PS ) are realized, or simply, the achieved Level ( L ).  Much like the relationship established between Position ( P ) and Skill ( S ), there is a Employee Position Position Skill ( EPPS ) which has a compound attribute of Level ( L ) in a many-to-one relationship.  This again requires a unique key, leaving our noun list ( now expanded ) in the following state:

( EPPS:
    { U2 }( EP:
        ( E: EC, EF, EL ),
        ( P: PC, PT, ( D:
            DC, DN ) ),
        T1, T2 ),
    { U2 }( PS:
        ( { U1 }( P:
            PC, PT, ( D:
                DC, DN ) ),
        { U1 }( S:
            SC, SN, ( G:
                GC, GN ) ),
        ( L:
            LC, LN ) ),
    ( L:
        LC, LN ) )

That, of course, is a bit on the crazy side to read, so we extract the definitions we've reduced the problem to in an actionable way.  Here, I've added additional unique keys simply for the purpose of completeness.  Note that there are no soon-to-be tables which contain more than one independently unique key, so { U1 } is used throughout, per table:

( D: { U1 }DC, DN )
( P: { U1 }D, { U1 }PC, PN )
( G: { U1 }GC, GN )
( S: { U1 }G, { U1 }SC, SN )
( L: { U1 }LC, LN )
( PS: { U1 }P, { U1 }S, L )
( E: { U1 }EC, EF, EL )
( EP: { U1 }E, { U1 }P, T1, T2 }
( EPPS: { U1 }EP, { U1 }PS, L }


oracle 11g seems to die every 2 days, how can i debug what's happening? I installed oracle 11g on ubuntu 11.10, but it seems that every couple of days I'll get to work and the database isn't running but the listener is.

To fix it, all I have to do is login and start it up:

su - oracle
cd /path/to/oracle11g/bin
./sqlplus sys as sysdba
startup;
exit

and then everything's ok. Taking a look at the alert log at:
/u01/app/oracle/diag/rdbms/mydb/mydb/alert/log.xml

It looks like the problem started here:

<msg time='2012-03-09T22:00:02.570+01:00' org_id='oracle' comp_id='rdbms'
 msg_id='kesaiTuneSqlDrv:4182:2579917519' client_id='' type='NOTIFICATION'
 group='sqltune' level='16' module='DBMS_SCHEDULER'
 pid='4460'>
 <txt>Begin automatic SQL Tuning Advisor run for special tuning task  &quot;SYS_AUTO_SQL_TUNING_TASK&quot;
 </txt>
</msg>
<msg time='2012-03-09T22:00:31.273+01:00' org_id='oracle' comp_id='rdbms'
 msg_id='3646992671' type='INCIDENT_ERROR' group='Generic Internal Error'
 level='1' prob_key='ORA 600 [kgkprrpicknext1]' upstream_comp=''
 downstream_comp='' ecid='' errid='11150'
 detail_path='/u01/app/oracle/diag/rdbms/mydb/mydb/trace/mydb_ora_29268.trc'>
 <txt>Errors in file /u01/app/oracle/diag/rdbms/mydb/mydb/trace/mydb_ora_29268.trc  (incident=11150):
ORA-00600: internal error code, arguments: [kgkprrpicknext1], [18], [2], [], [], [], [], []
 </txt>
</msg>

Oracle has an alert log which should tell you when and why it shut down. Depending on how the database was configured, that will be under a directory identified by either the diagnostic_dest or background_dump_dest parameters, and will be called alert.xml or alert.log (in previous versions). If you're running it, you can get information from Enterprise Manager too.

Has the server it's running on rebooted as well? If so the listener may be configured to start  automatically, but the database not; there's a flag in the oratab file that says whether each instance should start, e.g. when dbstart is run, which could be done from an init.d script on boot. You could try changing the flag for your database from N to Y to see if it stops you having to start it manually, at least.

If the server hasn't rebooted then the database may have crashed, in which case I'd expect to see errors in the alert log - search backwards, particularly for ORA-00600 errors which can be fatal, but also for anything close to the time it crashed.

Based on the ORA-600 in the alert log, this looks like it might be bug 6651027, which is related to the resource manager and is supposedly fixed in 11.1.0.7 and 11.2. If you have access to the Oracle Support website you could look at note 559251.1, and the patch set notes. You can also look at the trace file referenced in the alert log for more detail on exactly where it's failing.

If you're on an earlier release I'd recommend you patch up and see if the problem goes away. If it doesn't then you'll need to raise a support assistance request with Oracle I'm afraid. ORA-600s that aren't already fixed in a patch sometimes have a workaround, but often need Oracle's input.


Parse a unix/epoch value as a `TIMESTAMP` in PostgreSQL PostgreSQL's COPY command is useful to quickly import lots of data, and the data must be in the textual representation for the datatype.

I am importing lots of data, includes a timestamp column, but it's stored as "unix time", i.e. seconds since the epoch. I can convert it to ISO 8601 (e.g. 2010-01-01 00:00:00, and PostgreSQL accept that for a timestamp. It does not accept the raw epoch value integer.

Is it possible to get postgres to accept the epoch integer value and interpret/convert that to a timestamp? That would make my code simpiler (and maybe faster).

This works with psql:

create temporary table test1 ( v1 timestamp );
copy test1 from stdin ;
Enter data to be copied followed by a newline.
End with a backslash and a period on a line by itself, or an EOF     signal.
>> 2010-01-01 00:00:00
>> \.
COPY 1

But these don't:

copy test1 from stdin ;
Enter data to be copied followed by a newline.
End with a backslash and a period on a line by itself, or an EOF signal.
>> 946684800
>> \.
ERROR:  date/time field value out of range: "946684800"
HINT:  Perhaps you need a different "datestyle" setting.
CONTEXT:  COPY test1, line 1, column v1: "946684800"
copy test1 from stdin ;
Enter data to be copied followed by a newline.
End with a backslash and a period on a line by itself, or an EOF signal.
>> epoch + 946684800
>> \.
ERROR:  time zone displacement out of range: "epoch + 946684800"
CONTEXT:  COPY test1, line 1, column v1: "epoch + 946684800"

The to_timestamp function is able to accept an 'integer' or the string text of that integer. I can't see an option like this for DateStyle setting. I have tried with the column being a timestamp with time zone type, with the same results.

I'm currently runing PostgreSQL 10 on Ubuntu Linux, but upgrading to 11 (or 12 soon) is possible if needed.

There is no way to do that with COPY alone. You either have to pre-process or post-process the data.

Perhaps UNLOGGED tables can make this a little bit faster:

/* no WAL logging for better performance */
CREATE UNLOGGED TABLE test1 (v1 double precision);

COPY test1 FROM STDIN;
Enter data to be copied followed by a newline.
End with a backslash and a period on a line by itself, or an EOF signal.
>> 946684800
>> \.

/* this will rewrite the table */
ALTER TABLE test1
   ALTER v1 TYPE timestamp without time zone
      USING (to_timestamp(v1) AT TIME ZONE 'UTC');

/* this will dump the table to the transaction log */
ALTER TABLE test1 SET LOGGED;

This will allow you to keep the intermediate manipulations out of the transaction log, which is good for performance.


Selecting the max date out of a text group This query selects the questions with the same subject

select
  new_subject,
  message_id,
  product_id,
  si,
  question_date
from (
       select
         trim(both ' ' from regexp_replace(a.subject, 'RE:|Re:', '', 'g')) as new_subject,
         *,
         a.seller_id                                                       as si
       from messages a
         INNER JOIN sellers b
           ON a.seller_id = b.seller_id
       WHERE a.product_id = 0
     ) as x

GROUP BY new_subject, product_id, message_id, question_date, si
ORDER BY new_subject, question_date DESC;

It gives me result something like

new subject message_id product_id si question_date
subject1    65299   0   5   2018-03-11 11:03:02
subject1    64522   0   5   2018-03-10 14:53:05
subject1    64511   0   5   2018-03-10 14:51:18
subject2    65214   0   5   2018-03-11 15:40:36
subject2    64625   0   5   2018-03-11 13:28:25
subject2    65245   0   5   2018-03-11 13:03:41
subject2    64365   0   5   2018-03-11 12:49:01
subject3    65007   0   5   2018-03-29 19:58:15
subject3    64043   0   5   2018-03-29 18:42:20
subject4    65562   0   5   2018-03-09 14:23:44

But just want to get the recent(only 1 ) subject1,subject2,subject3,subject4

I added these lines to get it

  WHERE question_date IN (
    select max(question_date) FROM messages
  )

Which has become

select
  new_subject,
  message_id,
  product_id,
  si,
  question_date
from (
       select
         trim(both ' ' from regexp_replace(a.subject, 'RE:|Re:', '', 'g')) as new_subject,
         *,
         a.seller_id                                                       as si
       from messages a
         INNER JOIN sellers b
           ON a.seller_id = b.seller_id
       WHERE a.product_id = 0
     ) as x
  WHERE question_date IN (
    select max(question_date) FROM messages
  )

GROUP BY new_subject, product_id, message_id, question_date, si
ORDER BY new_subject, question_date DESC;

But it doesn't return me 1 record from each, but no result. What am I missing?
Also it would have been great if you can provide a more performant query.

This is my table description

                                                Table "public.messages"
     Column      |              Type              | Collation | Nullable |                   Default
-----------------+--------------------------------+-----------+----------+----------------------------------------------
 message_id      | integer                        |           | not null | nextval('messages_message_id_seq'::regclass)
 product_id      | integer                        |           |          |
 subject         | text                           |           |          |
 body            | text                           |           | not null |
 customer_id     | integer                        |           | not null |
 message_type    | integer                        |           | not null |
 question_date   | timestamp(0) without time zone |           | not null |
 seller_id       | integer                        |           | not null |
 deleted_at      | timestamp(0) without time zone |           |          |
 created_at      | timestamp(0) without time zone |           |          |
 updated_at      | timestamp(0) without time zone |           |          |
 market_place_id | bigint                         |           | not null |
 answer_source   | integer                        |           |          |
 answer_id       | bigint                         |           |          |
 hide_question   | boolean                        |           | not null | false

You need to choose max(question_date) per subject. Something like:

WHERE question_date IN (
    select max(question_date)
    FROM messages
    WHERE subject = a.subject
)

Another option is to enumerate using OLAP functions and choose most recent subject using that:

select
  new_subject,
  message_id,
  product_id,
  si,
  question_date
from (
       select
         trim(both ' ' from regexp_replace(a.subject, 'RE:|Re:', '', 'g')) as new_subject,
         *,
         a.seller_id as si

       , row_number() over (partition by a.subject
                            order by question_date desc) as rn

       from messages a
         INNER JOIN sellers b
           ON a.seller_id = b.seller_id
       WHERE a.product_id = 0
     ) as x

WHERE rn = 1

GROUP BY new_subject, product_id, message_id, question_date, si
ORDER BY new_subject, question_date DESC;

The first alternative will return several rows per subject in case they have the same question_date. The second alternative will return only one
 row per subject. Using rank() instead of row_number() makes the second alternative to behave like the first one.

You may have to add another level of nesting to take into consideration that several subjects may map to the same new_subject:

select new_subject, message_id, product_id, si, question_date
from (
    select
      new_subject,
      message_id,
      product_id,
      si,
      question_date,
      row_number() over (partition by new_subject
                         order by question_date desc) as rn
    from (
           select
             trim(both ' ' from regexp_replace(a.subject, 'RE:|Re:', '', 'g')) as new_subject,
             *,
             a.seller_id as si
           from messages a
             INNER JOIN sellers b
               ON a.seller_id = b.seller_id
           WHERE a.product_id = 0
         ) as x
) where rn = 1
ORDER BY new_subject, question_date DESC;


Postgresql - Distinct array aggregation This is my current query
SELECT
project_id,
release_id,
array_agg(DISTINCT version_bom_id) AS version_bom_ids,
sum(case when review_status='REVIEWED' then 1 else 0 end) AS reviewed_count,
sum(case when review_status='NOT_REVIEWED' then 1 else 0 end) AS not_reviewed_count,
sum(case when severity='BLOCKER' then 1 else 0 end) AS policy_blocker_count,
sum(case when severity='CRITICAL' then 1 else 0 end) AS policy_critical_count,
sum(case when severity='UNSPECIFIED' then 1 else 0 end) AS policy_unspecified_count,
array_agg(DISTINCT ignored) as ignored_array
--problem aggregating the policy_ids
--array_agg( DISTINCT policy_ids) FILTER (where policy_ids is not null) AS policy_ids,

FROM (

   SELECT vbc.review_status,
   vbc.version_bom_id AS version_bom_id,
   vbc.project_id AS project_id,
   vbc.release_id AS release_id,
   vbc.ignored as ignored,
   min(prrd.severity)  FILTER (where vbap.status = 'IN_VIOLATION') AS severity,
   array_agg(DISTINCT vbap.policy_id::UUID) FILTER (where vbap.status = 'IN_VIOLATION') AS policy_ids
   FROM
    version_bom_component vbc
    LEFT JOIN version_bom_entry vbe ON vbc.id = vbe.version_bom_component_id
    LEFT JOIN version_bom_active_policies vbap ON vbap.version_bom_entry_id = vbe.id
    LEFT JOIN policy_rule_rule_definition prrd ON prrd.id = vbap.policy_id
    GROUP BY version_bom_id, project_id, release_id, review_status, vbc.ignored
 ) t GROUP by project_id, release_id

The sub-query is giving me this result:
-[ RECORD 1 ]--+----------------------------------------------------------------------------
review_status  | NOT_REVIEWED
version_bom_id | 27
project_id     | 5468e2d4-e43b-4d83-b832-b654280584d0
release_id     | c29d7004-5678-49a1-8e4e-56ad3831a5a3
ignored        | f
severity       | BLOCKER
policy_ids     | {2074e6d1-3507-4fec-8a9e-f8b3c8c81caa,a9893b83-6444-400e-9ce0-f987156bbee2}
-[ RECORD 2 ]--+----------------------------------------------------------------------------
review_status  | NOT_REVIEWED
version_bom_id | 28
project_id     | 5468e2d4-e43b-4d83-b832-b654280584d0
release_id     | c29d7004-5678-49a1-8e4e-56ad3831a5a3
ignored        | f
severity       | CRITICAL
policy_ids     | {a9893b83-6444-400e-9ce0-f987156bbee2}
-[ RECORD 3 ]--+----------------------------------------------------------------------------
review_status  | NOT_REVIEWED
version_bom_id | 28
project_id     | 4859fdaf-5de6-42b7-9a6f-70354f24676c
release_id     | 1eb1ee65-49fc-44a9-b673-8b85b67eaf6d
ignored        | f
severity       |
policy_ids     |

I want my final output this way:
-[ RECORD 1 ]------------+----------------------------------------------------------------------------
project_id               | 5468e2d4-e43b-4d83-b832-b654280584d0
release_id               | c29d7004-5678-49a1-8e4e-56ad3831a5a3
version_bom_ids          | {27,28}
reviewed_count           | 0
not_reviewed_count       | 2
policy_blocker_count     | 1
policy_critical_count    | 1
policy_unspecified_count | 0
policy_ids               | {2074e6d1-3507-4fec-8a9e-f8b3c8c81caa,a9893b83-6444-400e-9ce0-f987156bbee2}
ignored_array            | {f}
-[ RECORD 2 ]------------+----------------------------------------------------------------------------
project_id               | 4859fdaf-5de6-42b7-9a6f-70354f24676c
release_id               | 1eb1ee65-49fc-44a9-b673-8b85b67eaf6d
version_bom_ids          | {28}
reviewed_count           | 0
not_reviewed_count       | 1
policy_blocker_count     | 0
policy_critical_count    | 0
policy_unspecified_count | 0
policy_ids               |
ignored_array            | {f}

I'm having trouble aggregating the policy_ids from the sub-query.
Notice that the policy_ids in the final result should be distinct. Can you anyone please help me out!
Thanks in advance

You could create your own aggregate to append arrays:
CREATE AGGREGATE array_append_agg(anyarray) (
   SFUNC = pg_catalog.array_cat,
   STYPE = pg_catalog.anyarray
);

Then use that aggregate function in your outer query.


What is the best way to store files metadata in DB? I have a task to create somethig like corporate data catalogue. Starting from the idea of data catalogue seems a little bit vague to me, but the question is  what is a good way to store files metadata like member_id, version etc? Usual row-wise format or JSON? I guess that json is not atomic and might be prone to  corruption, but db rows might be slightly more difficult to migrate to different instances due to the quanity of rows.

What is approx. size of the data? Your no. of rows being a concern here
How often does this data get updated? Consider data access and ACID transactions computation costs in your architecture.
Files metadata will have directories and subdirectories - which are dynamic in nature. Hence schematic representation(RDBMS table) may not be suitable. My initial impression says NoSQL may be a better candidate.
Choose the NoSQL database that suits best - Document DB, Key-Value pair, or Graph database if the files are interlinked (refer to each other) and you need to retrieve that relation
If you need to use RDBMS unless it is too old, many of them offer JSON capabilities inbuilt, please consider the same.


What all to start for oracle database server I am a Db guy familiar with Postgresql and Mysql. I have just started with Oracle and I have installed oracle 11g R2 on a RHEL-6 server. The installation was successful (the runInstaller script). During the install I had opted for Create and configure a database and I gave the DB the name "orcl".

After everything finished successfully, when I issued "ps aux | grep -i 'oracle'" in my shell (bash), I had got a log of processes (dont' remember the names of all of them). However, I stopped the server and now I want to know how I can start the oracle service. I have seen many commands to start Oracle(this link, so I am confused what are the necessary things that I must start. If it was Postgres or MySQL, it would have been only /etc/init.d/(mysqld|postgres) start.

So what are the things that I should start ? and please give me a brief a description of what that command does.

If you have the entries for your instance added to /etc/oratab (that should be "orcl:/your/oracle/home:Y" ) and you did run the root.sh in the end of that installation and the root.sh placed the environment scripts in /usr/locla/bin (the default), you can issue:

ORACLE_SID=orcl
ORAENV_ASK=NO
. /usr/local/bin/oraenv
unset ORAENV_ASK
$ORACLE_HOME/bin/dbstart

What oraenv does is mainly setting environment variables like ORACLE_HOME, ORACLE_BASE and PATH. PATH is made up of $ORACLE_HOME/bin. You could add the environment setup to your .profile and of course, you can start the database manually using sqlplus.

The dbstart script starts all databases that are registered in the oratab file, that have a 'Y' in the third column.

Manually starting, after the environment has been setup for a specific instance as shown above:

sqlplus / as sysdba
startup

If you want to switch to a different database just issue . oraenv (if /usr/local/bin is in the PATH) and give it the ORACLE_SID of the database that you want to manage.

Most databases will be accessed using a client from an other machine/VM. In that case you also might want to start the listener: lsnrctl start


Efficient workflow/queries to save unique information? I am saving company information in a database. There is a single table with an auto-incrementing key that is unique to each company. There are separate tables for emails, websites, phone numbers, and company names. When a new company is added to the database, a check should be performed across the various tables to see if unique data already exists.

For instance, if a new phone number, email, and website are added, a check should to be performed to see if they are all already present in the database. If the phone number and email are present (a unique combination), then I want to grab the unique company id for those two entries and add a row to the website table indicating that the website is now associated with that company id.

However, if the phone number and email exist for one company id while the email and website exist for a different company id, I want to merge the two ids into one.

I personally hate this setup, but I can't think of a cleaner way to save all this data. For instance, it's not guaranteed a phone number, email, or website will always be passed. Moreover, some of the data is fairly subjective. I can easily query email addresses and phone numbers from multiple tables:

SELECT id FROM companies LEFT JOIN emails using(id) LEFT JOIN phones using(id) WHERE phone = "123" AND email = "john@smith.com"

However comparing organization names, a WHERE query won't return correct results. I wrote a function in PHP to compare businesses names using similar_text and soundex, but those can't easily be incorporated into a MySQL query. Similarly, one check I have in place to differentiate businesses is a longitude/latitude distance check. Consequently, my envisioned workflow (as of now) is:

Loop through each unsaved company profile in PHP
Get all saved, unique information from the database:

SELECT id, email, position, address, zip, organization_name, phone, website FROM companies left join emails using(id) left join locations using(id) left join organization_names using(id) left join phones using(id) left join websites using(id)
In PHP, loop through each row, attempting to find a match between the saved values and unsaved value.
If a unique entry exists, update the database. If a unique entry does not exist, add the appropriate rows to the database.

I can see this being incredibly inefficient given the number of companies saved in the database, which is why I am here asking for help. Database design is not my forte, so any advice is appreciate.

My solution of this would be use relation tables instead of merging ids, use uniqueness for tables phone number, email, and website etc. and insert with IGNORE command like

Insert ignore into emails values (5,a.a@a.com);

  If you use the IGNORE keyword, errors that occur while executing the
  INSERT statement are ignored. For example, without IGNORE, a row that
  duplicates an existing UNIQUE index or PRIMARY KEY value in the table
  causes a duplicate-key error and the statement is aborted. With
  IGNORE, the row is discarded and no error occurs. Ignored errors may
  generate warnings instead, although duplicate-key errors do not.

or if you want o update on duplicates, use Replace instead of Insert ignore

  REPLACE works exactly like INSERT, except that if an old row in the table has the same value as a new row for a PRIMARY KEY or a UNIQUE index, the old row is deleted before the new row is inserted.

This way you don't need to check for duplicates. If you want more complicated duplicate checks, you need o do it in the code, not in the database


Does a backup include dropped, hidden columns still in a table's physical structure? If I have a table with an NVARCHAR(MAX) column and I drop it, the standard way to reclaim the space it used is to either:

Rebuild the clustered index or
Run DBCC CLEANTABLE

If I didn't perform either of these, the column still remains (visible
 via sys.system_internals_partition_columns) in the physical structure of the table, and did a full backup & restore of the database, would the restored copy still have dropped column in its physical structure and take up the same amount of space as before I had dropped the column?

For my own use case I am looking at SQL Server 2012, but I presume this functions the same across all relatively modern versions. I am aware this is easy to test, but I am hoping someone has more background knowlegde on this as I haven't been able to find much myself.

References:

Clean unused space when a SQL Server table with a variable length column is dropped
SQL Server table columns under the hood

If I have a table with an NVARCHAR(MAX) column and I drop it

I wrote about how you can use DBCC CLEANTABLE to reclaim the space.
Under the hood, sql server just deallocates the pages and then a background thread called ghost clean up will clean up the data.
Another aspect that I am assuming you are asking is physical data security - how can I be sure that my data is not recoverable once I delete/drop it ?
To answer that, SQL Server provides - sp_clean_db_free_space

in environments in which the physical security of the data or backup files is at risk, you can use sp_clean_db_free_space to clean these ghost records.
The length of time required to run sp_clean_db_free_space depends on the size of the file, the available free space, and the capacity of the disk. Because running sp_clean_db_free_space can significantly affect I/O activity, we recommend that you run this procedure outside usual operation hours.

Now that being said, its really hard to destroy the data esp. for drop column

if your column data type is defined as fixed length e.g. char(10) (Index Rebuild is required ; for heap create a clustered index and then drop it as there is not other option)
if your column data type is defined as variable length e.g. varchar(10) / nvarchar(10) (which can be taken care by dbcc cleantable)

Michael J. Swart has a very interesting post on It’s Hard To Destroy Data - esp the summary table with different options.


Match elements of a JSON array This is a sample table :

create table test(
    id int,
    json_key json[]
);

insert into test (id,json_key) values
    (1,array['{"key1":"123"}'::json,'{"key2":"456"}'::json]),
    (2,array['{"key1":"789"}'::json,'{"key2":"456"}'::json]);

json_key output is :

{{"key1":"123"},{"key2":"456"}}

I am trying to execute

select * from test
where json_key->>'key2'='456';

but it does not work :(
how can I run this query?

One way of trying to match something against array elements is using ANY.  The only thing is to find the right operator that can be used with it.  In this case, <@ will be our friend (you can find it and others at the right page of the docs).  When that's done, it becomes easy:

SELECT * FROM test WHERE '{"key2": "456"}'::jsonb <@ ANY(json_key::jsonb[]);

 id │                  json_key
────┼─────────────────────────────────────────────
  1 │ {"{\"key1\":\"123\"}","{\"key2\":\"456\"}"}
  2 │ {"{\"key1\":\"789\"}","{\"key2\":\"456\"}"}

To prove it really does what we expect:

test=# SELECT * FROM test WHERE '{"key1": "456"}'::jsonb <@ ANY(json_key::jsonb[]);

 id │ json_key
────┼──────────

test=# SELECT * FROM test WHERE '{"key1": "123"}'::jsonb <@ ANY(json_key::jsonb[]);

 id │                  json_key
────┼─────────────────────────────────────────────
  1 │ {"{\"key1\":\"123\"}","{\"key2\":\"456\"}"}

Watch it in action on dbfiddle.


Convert query for count and updated date I currently run the below query in Snowflake for some of my reconciliations and have never worked in PostgreSQL, which we just adopted.
Select count(1), min (LAST_UPDATED_DATE), max(LAST_UPDATED_DATE)
from "SOURCE"."SCHEMA"."TABLE"

I'm looking to do the same thing in PostgreSQL. It's one that we can run against a table that will give us the last updated date and a count when checking that movements completed.
I know Postgres can be time heavy on counts and some of these tables are massive. That aside I'm not sure how to rewrite this to begin with.

Starting point, mostly unchanged:
SELECT count(*), min(last_updated_date), max(last_updated_date)
FROM   schema.table;

Be aware of (not) case sensitive behavior of identifiers in Postgres. If in doubt use legal, unquoted, lower-case names exclusively. See:

Are PostgreSQL column names case-sensitive?

A table can optionally be schema-qualified (schema.table), but not database-qualified. A name like from "SOURCE"."SCHEMA"."TABLE" would raise:

ERROR:  cross-database references are not implemented: "SOURCE.SCHEMA.TABLE"

Table names without schema-qualification are resolved according to the current search_path. See:

How does the search_path influence identifier resolution and the “current schema”

count(*) is a bit faster than count(1).

Min and max can be read from a matching index in next-to-no time. Only the count can be slow. If your count does not have to be exact, consider:

Fast way to discover the row count of a table in PostgreSQL


Scaling out SQL Server and syncing data across multiple machines I don't have expertise in architecting databases, and I've been teaching myself new stuff every day. I'd like to make an Internet-scale application using SQL Server as the data store. I haven't found any good information online with regards to scaling out SQL Server.

My understanding is that scaling out is great for write throughput, but it doesn't necessarily scale reads. A simple example (which is relevant in my case) is, if data is sharded by posting user id, status 1 posted by user X living in shard A will have all its likes and comments across the whole federation. So, if I need to fetch the comments on this status, I need to hit every database and merge and sort/filter results in application memory. This is bad for the databases because they are kept busy and bad for the web servers because I will be using CPU and RAM for post processing the objects. Ideally, I'd like to write to one database and read from one database for maximum scalability.

Now, what I'm thinking of doing is, instead of sharding by posting user id, shard by receiving user id. So, if user X posts status 1, user Y living in shard B can insert a comment in shard A, and I can enforce a parent-child relationship between the status and the comment. User Z living in shard C can insert a like in shard A for the comment, so the comment and the like can constitute a parent-child relationship. The benefit of this approach is I query only one database to get all the comments and likes for a specific status rather than naively querying every single shard.

However, I need to get results like "comments on status 1 by people who are male or 18+ years old". This is a crucial functionality I want to implement. I still have to hit other databases to get information about the users. In order to eliminate this, I'm thinking of creating a sync group where one database (hub) syncs all user deltas to all shards (every 5 minutes). I'm okay with eventual consistency though it has its own problems for example, if a user deletes their account, from the time the account is deleted to the time the delta is persisted to a shard, other users will not see the change potentially adding child objects to objects created by that user. This seems to me a data integrity issue.

I'm also aware of replication and caching to increase read throughput.

My question is, which approach should I pursue? If I choose the second one, will I have trouble syncing data across potentially hundreds or thousands of servers? Not to mention the hub is essentially a single point of failure.

Creating a Scale Out database at Internet scale is pretty huge step. You will face a lot of issues that are not critical on a single big database. From your notes I see that you understand some of the basic issues you face.

Since Microsoft has papers on using SQL Server for scale out, I suggest that you study those first. Your scale out strategy will need to take into account the database server you choose.

For Microsoft SQL Server you should first study: http://msdn.microsoft.com/en-us/library/aa479364.aspx

This paper discusses the decisions that you need to make and why they are important. It offers 5 SQL Server strategies for scaleout:

  • Scalable Shared Databases

  • Peer-to-Peer Replication

  • Linked Servers

  • Distributed Partitioned Views

  • Data-Dependent Routing

As you go down the stack, things get more complicated, but also provide more powerful ways of scaling out.


Performance tuning for multi-tenant database The database, MS SQL 2014 Standard Edition, is becoming a bottleneck in our multi-tenant web application. Aware that most performance issues can be solved by making code more efficient, however looking beyond that now.

When load is high, the database seems to grind to a halt. At seemingly random times, it takes a massive slowdown (thinking table locking when one tenant inserts a load of data). Other times, we see deadlocked transactions. In the latest slowdown, a big chunk was deleting single rows from this table with queries like DELETE FROM tableX WHERE ID = x, resulting in a lot of Transaction (Process ID 183) was deadlocked on lock resources with another process and has been chosen as the deadlock victim errors.

Which of the below strategies would you recommend, if any?

Changing clustered indexes from the auto-incrementing int PK to
being tenantID + id. (note all tables have tenantID column and
already a non-clustered index on tenantID):

This would be an attempt to stop page/table locks due to activities from one tenant from affecting all other tenants. This seems recommended practice for multi-tenant DBs. Otherwise if a tenant inserts 4,000 rows or similar this seems to lock the table.
Changing from default
isolation level to Read Committed Snapshot so writes don't block
reads and vice versa
Moving to faster physical disks - currently
on a RAID 10 array of SSDs, looking at the Intel NVMe PCI-e SSDs

We use SQL Mirroring with High Safety Mode as we cannot lose a single booking/row due to a hardware error. Was under the impression all writes are written to disk, and the transaction won't complete till written to disk on the mirror. Networking between the two servers is sub 1ms and no contention. There's plenty RAM in the machine (64GB and doubling to 128GB in a few days), but does host a second multi-tenant DB (~40GB) for a different product.

Your comment:

  Possibly, but there's a lot of queries to one table of bookings with
  many people trying to get bookings at the same time. It's more complex
  than that, with queries needing to join other tables, with data being
  loaded in to these tables by other tenants at the same time, etc

So, it's a database! Great, let's answer some questions.

  Changing clustered indexes from the auto-incrementing int PK to being
  tenantID + id. (note all tables have tenantID column and already a
  non-clustered index on tenantID)

You know that Simpsons episode where they bring in lizards to catch pigeons, snakes to catch the lizards, and snake eating gorillas to catch the snakes? That's what you're doing here. The identity column clustered index is not the problem.

  Changing from default isolation level to Read Committed Snapshot so
  writes don't block reads and vice versa

This is a wonderful option that you should fully explore, but you have to make sure that you don't run into any race conditions with queueing type queries that depend on locking and blocking to assign work. Since your database is so small now (20 GB), you're in a great place to start exploring optimistic isolation levels. If parts of your code can't handle RCSI, Snapshot Isolation may be easier to approach because you can apply it to specific queries.

  Moving to faster physical disks - currently on a RAID 10 array of
  SSDs, looking at the Intel NVMe PCI-e SSDs

That's silly all the way around. You have a 20 GB database. Buy 32 GB of RAM. If it's all in memory, disk doesn't matter, and 32 GB of RAM is way cheaper than all that SSD. Though I'm totally on board with you grabbing some NVMes for tempdb, memory is the better approach.

Of course, no change means anything without some basic troubleshooting. Try a free script like sp_BlitzFirst (full disclosure, I contribute to this OSS) to figure out what your queries are actually waiting on.

Run EXEC sp_BlitzFirst @SinceStartup = 1 and look at your wait stats. Pay attention to the avg ms per wait column.

If you have any questions about those, post back here with them.

Update

  Cluster indexes to include tenantID - this was in an attempt to stop page/table locks due to activities from one tenant from affecting
  all other tenants. This seems recommended practice for multi-tenant
  DBs. Otherwise if a tenant inserts 4,000 rows or similar this seems to
  lock the table.

You likely won't get much different locking behavior with this over proper nonclustered indexes. If you really want one tenant to not bother another, you can try a partitioned view (or if you updgrade to 2016 SP1, actual partitioning), or separate tenants out to their own databases.

Lock escalation will occur regardless of if locks are against clustered or nonclustered indexes. I'm not sure what you've done for index tuning, but missing indexes can definitely be a factor if lock escalations are inappropriately frequent. Same full disclosure about contributing as above, sp_BlitzIndex can help you identify both aggressively locked indexes, as well as missing indexes.

  Disk IO vs RAM. We use SQL Mirroring with High Safety Mode as cannot lose a single booking/row due to a hardware error. Was under
  the impression all writes are written to disk, and the transaction
  won't complete till written to disk on the mirror. Networking between
  the two servers is sub 1ms and no contention. There's plenty RAM in
  the machine (64GB and doubling to 128GB in a few days), but does host
  a second multi-tenant DB (~40GB) for a different product.

This is the sort of important information that should have been in your original question. If the network is fine, then your two culprits for Mirroring latency will likely be transaction size or transaction frequency. Some of that might be mitigated by putting the log file on faster disks, but ultimately controlling the size or frequency of transactions is a smarter approach.

  Just to add, in the latest slowdown, a big chunk was deleting single
  rows from this table with queries like DELETE FROM tableX WHERE ID =
  x, and a lot of Transaction (Process ID 183) was deadlocked on lock
  resources with another process and has been chosen as the deadlock
  victim.

Theoretically two write queries won't deadlock, they'll just block each other until one is done. Are you using foreign keys or other constraints that touch other tables with cascading actions? Have you identified which queries they deadlocked with?

RCSI won't help with write query blocking, though Snapshot can if you're willing to spend a decent amount of time around error handling and query retries.

But again, this is all hand-wavey without wait stats or other diagnostic information.

Best of luck with everything.


Postgres Proper Index for Sorting and Join I have a simple schema and query, but am experiencing consistent awful performance with certain parameters.

Schema

CREATE TABLE barcodes (
  id    integer PRIMARY KEY,
  value citext  NOT NULL
);
CREATE INDEX index_barcodes_on_value ON barcodes (value);

CREATE TABLE locations (
  id         integer PRIMARY KEY,
  barcode_id integer NOT NULL REFERENCES barcodes(id)
);
CREATE INDEX index_locations_on_barcode_id ON locations (barcode_id);

Query

EXPLAIN ANALYZE
SELECT *
FROM locations
JOIN barcodes ON locations.barcode_id = barcodes.id
ORDER BY barcodes.value ASC
LIMIT 50;

Analysis

Limit  (cost=0.71..3564.01 rows=50 width=34) (actual time=0.043..683.025 rows=50 loops=1)
  ->  Nested Loop  (cost=0.71..4090955.00 rows=57404 width=34) (actual time=0.043..683.017 rows=50 loops=1)
        ->  Index Scan using index_barcodes_on_value on barcodes  (cost=0.42..26865.99 rows=496422 width=15) (actual time=0.023..218.775 rows=372138 loops=1)
        ->  Index Scan using index_locations_on_barcode_id on locations  (cost=0.29..5.32 rows=287 width=8) (actual time=0.001..0.001 rows=0 loops=372138)
              Index Cond: (barcode_id = barcodes.id)
Planning time: 0.167 ms
Execution time: 683.078 ms

500+ ms for the number of entries in my schema (500,000 barcodes and 60,000 locations) doesn't make sense. Can I do anything to improve the performance?

Note:

Even stranger is the execution time depends on the data. In drafting this question I attempted to include seeded random data, but the seeds seem to be performant:

Seed

INSERT INTO barcodes (id, value) SELECT seed.id, gen_random_uuid() FROM generate_series(1,500000) AS seed(id);
INSERT INTO locations (id, barcode_id) SELECT seed.id, (RANDOM() * 500000)  FROM generate_series(1,60000) AS seed(id);

Analysis

Limit  (cost=0.71..3602.63 rows=50 width=86) (actual time=0.089..1.123 rows=50 loops=1)
  ->  Nested Loop  (cost=0.71..4330662.42 rows=60116 width=86) (actual time=0.088..1.115 rows=50 loops=1)
        ->  Index Scan using index_barcodes_on_value on barcodes  (cost=0.42..44972.42 rows=500000 width=41) (actual time=0.006..0.319 rows=376 loops=1)
        ->  Index Scan using index_locations_on_barcode_id on locations  (cost=0.29..5.56 rows=301 width=8) (actual time=0.002..0.002 rows=0 loops=376)
              Index Cond: (barcode_id = barcodes.id)
Planning time: 0.213 ms
Execution time: 1.152 ms

I'm looking for help diagnosing the slow performance of the query. I can reproduce the problem - I just cannot replicate it with random seed data.

I am running PostgreSQL 9.6.2. Running VACUUM FULL on all the tables in the query didn't do anything.

The reason for the poor performance is that the barcodes occurring earlier in the alphabet have been preferentially depleted from the location table.

It is easy to reproduce this with your random data seeds by just populating locations with twice as many records, and then deleting the "first" half of them:

INSERT INTO locations (id, barcode_id) SELECT seed.id, (RANDOM() * 500000)
    FROM generate_series(1,120000) AS seed(id);

DELETE from locations c where id in (
    select d.id from locations d
        JOIN barcodes ON d.barcode_id = barcodes.id
        ORDER BY barcodes.value ASC limit 60000
);

There is not much hope for getting PostgreSQL to understand this weird cross-table relationship.

You might be able to get somewhat better performance by doing something like

set local enable_nestloop TO off;

But the real solution is probably to rethink how you are organize your data, or what you are trying to do with it.  Usually barcodes serve as their own primary keys.  Why do you need a separate table just to map a barcode to a primary key?  And why do you need to know the lowest barcodes which exist somewhere (anywhere) on a regular basis?  It sounds like an X-Y problem.


is it possible to trim the table output for a specific job? I have a routine to save the logins and apply them on a different server , and I run this routine from a job that I have scheduled to run everyday.

On that job I have specified these 3 things:

Log to table
Append output to existing entry in table
Include step output in history

as can be seen on the picture below:

I am happy with that, however, I was wondering if there is a way to trim the table output because when I open it I see records from over 4 months ago, which are no longer relevant now, as shown on the picture below:

when I click View:

I get:

and the table has over 61 thousand lines.

Every time the job runs it adds about 600 new lines to the bottom of the table.

Can I keep the last let's say 10 thousand lines only,getting rid of the older lines?

Yes.

The CommandLog cleanup is from Ola Hallengren's Maintenance Solution, so doesn't affect msdb history directly.

Use sp_purge_jobhistory, like so:

sp_purge_jobhistory
   {   [ @job_name = ] 'job_name' |
     | [ @job_id = ] job_id }
   [ , [ @oldest_date = ] oldest_date ]

Per the documentation, 'job_name' is the name of the job for which to delete the history records. Either job_id or job_name must be specified, but both cannot be specified.


How to create a MongoDB index with isodate? I've create and index for a query and its running is less then 1 second to retrieve millions of rows, but as soon as I add greater than ISODATE, it doesnt use indexes anymore.
the query is this one:
db.getCollection("Coll").find({"Field1.date" : {
                    $gte: ISODate("2019-11-27T00:00:00.000Z"),
                    $lte: ISODate("2019-11-28T00:00:00.000Z")
                }},{
    _id: 1,
    "Field2": 1,
    "Field3":1,
     Field4: 1
})

and i created an index like this:
//    db.Coll.createIndex(
//    {
//           _id: 1,
//    "Field2": 1,
//     Field4: 1,
//    "Field1.date":1
//    },
//    {background:true , name: "idx_name_date"})
//

but it seems this "field1.date" doesnt work with ISODate.

The order of the fields in the index definition is important.
The indexed values are sorted in the order the fields appear in the spec, so in the index: { _id: 1, Field2: 1,Field4: 1, "Field1.date":1}, the values will be sorted first by _id, then by Field2, etc.
This means that in order to find documents with dates in the range you specified, it would have to examine every index entry.
It that case is probably just as efficient to scan all of the documents.
Instead build an index on { "Field1.date":1, _id: 1, Field2: 1,Field4: 1}.  That will allow the query executor to find all of the documents by scanning a single subset of the index, and it will have all of the projected fields available to avoid fetching any documents.


SQL Server: specifying a unique optional column SQL Server has a quirk with optional unique columns: you cannot have more than one null in a table.

For example:

create table employees (
    id int identity(1,1) primary key,
    givenname nvarchar(24) not null,
    familyname varchar(24) not null,
    tfn char(9) unique  --  optional, but unique
);

(In Australia, we have a Tax File Number, which is unique to the tax payer).

The following is a workaround:

create table employees (
    id int identity(1,1) primary key,
    givenname nvarchar(24) not null,
    familyname varchar(24) not null,
    tfn char(9)
);
CREATE UNIQUE NONCLUSTERED INDEX uq_employees_tfn ON employees(tfn)
WHERE tfn IS NOT NULL;

Is it possible to name a unique constraint and give it the above condition is a manner similar to the first example? That is, is it possible to include the more complex constraint inline with the column definition?

Is it possible to name a unique constraint and give it the above condition is a manner similar to the first example? That is, is it possible to include the more complex constraint inline with the column definition?

Yes, the following works (only in SQL Server 2016 onwards):

create table employees (
    id int identity(1,1) primary key,
    givenname nvarchar(24) not null,
    familyname varchar(24) not null,
    tfn char(9) NULL
        INDEX uq_employees_tfn
        UNIQUE (tfn)
        WHERE tfn IS NOT NULL
);

The inline syntax results in the same unique filtered index as for the separate CREATE UNIQUE INDEX. Subsequent DDL will need to manage it like an index rather than a constraint.

It is not brilliantly clear in the CREATE TABLE documentation, but is illustrated in Example S.


Backup SQL Server To NAS I have seen that there are similar questions like this but I tried all of them and my problem still exist. I want to backup a db.

I created a shared folder on NAS
I can reach this shared folder from windows explorer on DB server like \\10.1.1.x\yedek_db. I can create and delete files in this folder.
I checked all the services related to SQL Server. Previously they were running using network service account and I changed it to domain administrator (which has full access to the NAS folders)  and restarted the services.
I executed the following command on command line

net use H: \\10.1.1.x\yedek_db

to map the NAS folder to H:. After this I can reach that folder by typing h:.
I created a backup device on SQL Server which is named NAS_YEDEK_DB. The drop and create script is below.

IF  EXISTS (SELECT name FROM master.dbo.sysdevices WHERE name = N'NAS_YEDEK_DB')
EXEC master.dbo.sp_dropdevice @logicalname = N'NAS_YEDEK_DB'
GO

EXEC master.dbo.sp_addumpdevice  @devtype = N'disk', @logicalname = N'NAS_YEDEK_DB', @physicalname = N'H:\'
GO

I try to backup using this backup device by the following code:

BACKUP DATABASE evrak
TO NAS_YEDEK_DB
WITH DESCRIPTION = 'Test DB Backup';

But I get this error :

  Msg 3201, Level 16, State 1, Line 1
  Cannot open backup device 'NAS_YEDEK_DB(H:\)'. Operating system error 3(failed to retrieve text for this error. Reason: 15105).
  Msg 3013, Level 16, State 1, Line 1
  BACKUP DATABASE is terminating abnormally.

I will be very happy if I can solve this problem.

The likelihood is that the H: drive is unavailable to the SQL service account as it will be local to your administrator account.

Create the backup device using the UNC path rather than the drive letter mapping. i.e. \\10.1.1.x\yedek_db.

Alternatively you could try logging in as the SQL Service account and mapping the drive letter, but this is a less optimal approach.


How to group by week even when the count is 0 My below example works fine, the only challenge i am facing is that weeks with 0 results do not show. Here is a sample of my code:

My current output:

week      | amount
4        |  354
6        |  222
7        |  144
8        |  354
9        |  45
10       |  55
11       |  76
12       |  98
13       |  45
14       |  344

The result above is missing many weeks (1,2,3 and 15,16,17 etc.) How do i get to show those with 0 count?

TEST EXAMPLE:

CREATE TABLE TABLE1 ( DATE datetime, COMPANY_ID varchar);
INSERT INTO TABLE1(DATE, COMPANY_ID)
VALUES
('2019-01-24 00:00:00.000','Arla'),
('2019-01-24 00:00:00.000', 'B/S'),
('2019-01-24 00:00:00.000', 'NAaN'),
('2019-02-20 00:00:00.000', 'SuperBest'),
('2019-02-20 00:00:00.000', 'GS'),
('2019-04-29 00:00:00.000', 'Aldi'),
('2019-04-29 00:00:00.000', 'Netto'),
('2018-01-24 00:00:00.000', 'Arla');

;with ResultsTable as
(

SELECT
DATENAME (WK, DATE) AS WEEK,
COUNT (DISTINCT COMPANY_ID) AS AMOUNT
FROM
(
SELECT COMPANY_ID, DATE = MIN(DATE)
FROM TABLE1 A

WHERE YEAR(A.DATE) = '2019' AND

COMPANY_ID NOT IN(SELECT COMPANY_ID  FROM TABLE1 WHERE DATE < '2019-01-01')
GROUP BY COMPANY_ID) d

GROUP BY dateadd(wk, datediff(wk, 0, DATE), 0),  DATENAME(WK, DATE)

)
,WeekNumbers (WeekNumber)
AS (
SELECT 1 AS Number

UNION ALL

SELECT WeekNumber + 1
FROM WeekNumbers
WHERE WeekNumber < (
        SELECT datename(week, '2019-12-31')
        )
)
--join cte to original results
--when no match on week number, set amount to 0
SELECT wn.WeekNumber AS [week]
,COALESCE(rt.[week], 0) AS amount
FROM WeekNumbers wn
LEFT JOIN ResultsTable rt ON rt.[week] = wn.WeekNumber

Could the solution be as simple as using a recursive CTE to generate a numbers table of the weeks in the year and then left joining to your main query?  Using coalesce to output either the real amount or zeros when no match on week number is found?

--demo setup
Declare @ResultsTable table (
  [week] INTEGER,
  amount INTEGER
);

INSERT INTO @ResultsTable
  ([week], amount)
VALUES
  ('4', '354'),
  ('6', '222'),
  ('7', '144'),
  ('8', '354'),
  ('9', '45'),
  ('10', '55'),
  ('11', '76'),
  ('12', '98'),
  ('13', '45'),
  ('14', '344');

--recursive cte to generates week numbers
;WITH WeekNumbers (WeekNumber)
AS (
    SELECT 1 AS Number

    UNION ALL

    SELECT WeekNumber + 1
    FROM WeekNumbers
    WHERE WeekNumber < (
            SELECT datename(week, '2019-12-31')
            )
    )
--join cte to original results
--when no match on week number, set amount to 0
SELECT wn.WeekNumber AS [week]
    ,COALESCE(rt.amount, 0) AS amount
FROM WeekNumbers wn
LEFT JOIN @ResultsTable rt
    ON rt.[week] = wn.WeekNumber

| week | amount |
|------|--------|
| 1    | 0      |
| 2    | 0      |
| 3    | 0      |
| 4    | 354    |
| 5    | 0      |
| 6    | 222    |
| 7    | 144    |
| 8    | 354    |
| 9    | 45     |
| 10   | 55     |
| 11   | 76     |
| 12   | 98     |
| 13   | 45     |
| 14   | 344    |
| 15   | 0      |
| 16   | 0      |
| 17   | 0      |
| 18   | 0      |
| 19   | 0      |
| 20   | 0      |
| 21   | 0      |
| 22   | 0      |
| 23   | 0      |
| 24   | 0      |
| 25   | 0      |
| 26   | 0      |
| 27   | 0      |
| 28   | 0      |
| 29   | 0      |
| 30   | 0      |
| 31   | 0      |
| 32   | 0      |
| 33   | 0      |
| 34   | 0      |
| 35   | 0      |
| 36   | 0      |
| 37   | 0      |
| 38   | 0      |
| 39   | 0      |
| 40   | 0      |
| 41   | 0      |
| 42   | 0      |
| 43   | 0      |
| 44   | 0      |
| 45   | 0      |
| 46   | 0      |
| 47   | 0      |
| 48   | 0      |
| 49   | 0      |
| 50   | 0      |
| 51   | 0      |
| 52   | 0      |
| 53   | 0      |

So, using your original query incorporated into a Common Table Expression, the final solution might look something like this:

;with ResultsTable as
(
SELECT
DATENAME (WK, DATE) AS WEEK,
COUNT (DISTINCT COMPANY_ID) AS AMOUNT
FROM
(
SELECT COMPANY, DATE = MIN(DATE)
FROM TABLE1 A INNER JOIN TABLE2 B
ON A.ID = B.ID
WHERE YEAR(A.DATE) = '2019' AND COMPANY_ID NOT IN(SELECT COMPANY_ID  FROM
TABLE1 A INNER JOIN TABLE2 B  ON A.ID = B.ID AND DATE < '2019-01-01') GROUP
BY COMPANY_ID) d

GROUP BY dateadd(wk, datediff(wk, 0, DATE), 0),  DATENAME(WK, DATE)
)
,WeekNumbers (WeekNumber)
AS (
    SELECT 1 AS Number

    UNION ALL

    SELECT WeekNumber + 1
    FROM WeekNumbers
    WHERE WeekNumber < (
            SELECT datename(week, '2019-12-31')
            )
    )
--join cte to original results
--when no match on week number, set amount to 0
SELECT wn.WeekNumber AS [week]
    ,COALESCE(rt.amount, 0) AS amount
FROM WeekNumbers wn
LEFT JOIN ResultsTable rt
    ON rt.[week] = wn.WeekNumber


Schedule suggestion and index rebuild / database integrity progress for Ola Hallengren’s Maintenance Script I have to configure Ola Hallengren's Maintenance Scripts for my SQL Server 2017. SQL Database size about total 400GB.Total 10 Dbs. But I have some questions.
1- I will use these schedules like below. What is the best practices for this ? or What do you recommended?
DatabaseBackup – SYSTEM_DATABASES – FULL job – Daily at 12:05AM
DatabaseIntegrityCheck – SYSTEM_DATABASES job – Daily at 12:10AM
IndexOptimize – USER_DATABASES job – Daily at 1:00AM

2-  How can I check the progress / status when index optimize  and databasSee integrity job ?
Kind Regards,

We can't answer the first two since that is for you to decide. Or rather, your Recovery Point Objective. How much data can you afford to lose? That is the deciding factor for the first two.
As for IndexOptimize, You might end up defragmenting indexes without seeing much gain from it. If it doesn't hurt you, then sure do it every day. But if it does, then perhaps less frequently. As for defrag, some consider not doing it at all on modern disk subsystems (I've wrote about this here: http://sqlblog.karaszi.com/fragmentation-the-final-installment/).
Updating stats might be more beneficial than defrag. However, Ola's default job focuses on defrag and not update stats (you'll only get new stats for the indexes where you have more than 30% fragmentation as a side effect of index rebuild). OTOH, some consider updating stats not being smart since you'll end up with new plans after that and plan generation costs, and you might get a worse plan than before. OTTH better stats can give better quality plans - so there's no one consensus on how to do these tings.
You can create a secondary job to update statistics only.
Ola logs what we does in the CommandLog table, checkin in on that is probably the best progress you can get, aside from sys.dm_exec_requests (some commands will show progress in this DMV).


Need to convert 21:45 to 21.75 I have a datetime column with a time portion of 21:45; I need to convert it to 21.75. I tried:

CONVERT(VARCHAR(5), DATE,108)

This converts it to 21:45, now the client wants this be 21.75.

DECLARE @x TABLE(col DATETIME);

INSERT @x SELECT '20150901 21:45';

SELECT col,
  d = CONVERT(CHAR(10), col, 101), -- AMBIGUOUS, REGIONAL, NOT RECOMMENDED!
  t = CONVERT(DECIMAL(4,2), DATEDIFF(MINUTE, CONVERT(DATE,col), col)/60.0)
FROM @x;

Results:

col                        d             t
-----------------------    ----------    -----
2015-09-01 21:45:00.000    09/01/2015    21.75


how to change nls_language in nls_instance_parameters Oracle 12c (12.1.0.2.0) database

Issue: I wanted to change NLS_LANGUAGE and NLS_TERRITORY in NLS_INSTANCE_PARAMETERS. I know how to change for session, but I wanted to change it for the instance. The reason why is because stored procedure scripts weren't running correctly due to timestamp. I was getting this error:

start_date=>TO_TIMESTAMP_TZ('12-MAY-2015 02.00.00.000000000 AM +05:30','DD-MON-RRRR HH.MI.SSXFF AM TZR','NLS_DATE_LANGUAGE=english'), repeat_interval=>
ORA-01855: E necessario AM/A.M. ou PM/P.M.

Reasononing: The NLS_DATE_FORMAT and the NLS_TIMESTAMP_FORMAT were the same across. I even tried unset LANG and unset NLS_LANG. Despite all of this I was still getting date conversion errors, so I decided to update all of the NLS-related parameters to the same values across all views. My database (nls_database_parameters) and session (nls_session_parameters) were showing the same NLS_LANGUAGE, but my instance (nls_instance_parameters) was not.

Solution: To fix this I had to update the init<db>.ora and the spfiles<db>.ora
I didn't really see this explicitly specified anywhere except for maybe on Burleson Consulting's website, so I decided to put my findings here to help others with the same issue.

I updated NLS_LANGUAGE and NLS_TERRITORY in NLS_INSTANCE_PARAMETERS, restarted the database, and it fixed my issue.

I had to force restart because for some reason I couldn't restart normally.

Example below on how to update this:

[oracle@mydbserver dbs]$ unset LANG
[oracle@mydbserver dbs]$ sqlplus "/ as sysdba"

SQL*Plus: Release 12.1.0.2.0 Production on Fri Jan 27 13:18:26 2017
Copyright (c) 1982, 2014, Oracle.  All rights reserved.
Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

SQL> SELECT sys_context('userenv','language') from dual;

SYS_CONTEXT('USERENV','LANGUAGE')
--------------------------------------------------------------------------------
BRAZILIAN PORTUGUESE_BRAZIL.AL32UTF8

SQL> quit
Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

[oracle@mydbserver dbs]$ unset NLS_LANG
[oracle@mydbserver dbs]$ sqlplus "/ as sysdba"

SQL*Plus: Release 12.1.0.2.0 Production on Fri Jan 27 13:18:51 2017

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

Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

SQL> SELECT sys_context('userenv','language') from dual;

SYS_CONTEXT('USERENV','LANGUAGE')
--------------------------------------------------------------------------------
BRAZILIAN PORTUGUESE_BRAZIL.AL32UTF8

SQL> quit
Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

This is where I edited the init and spfile - you might want to take a backup !!

[oracle@mydbserver dbs]$ ls
hc_scpp.dat  init.ora  initscpp.ora  lkSCPP  orapwmydb  spfilemydb.ora  teste.ora

edit initscpp.ora and spfilemydb.ora with the values you want for the NLS parameters
Then I proceeded to restart

[oracle@mydbserver dbs]$ sqlplus "/ as sysdba"
SQL*Plus: Release 12.1.0.2.0 Production on Fri Jan 27 13:25:30 2017
Copyright (c) 1982, 2014, Oracle.  All rights reserved.
Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

SQL> shutdown
^CORA-01013: o usuario solicitou o cancelamento da operac?o atual

shutdown was slow so I [ctrl+c] and tried to force restart then restart normally !!

SQL> startup force
ORACLE instance started.

Total System Global Area 2,0724E+10 bytes
Fixed Size                  3158000 bytes
Variable Size            3070230544 bytes
Database Buffers         1,7645E+10 bytes
Redo Buffers                5230592 bytes
Database mounted.
Database opened.
SQL> shutdown
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.

Total System Global Area 2.0724E+10 bytes
Fixed Size                  3158000 bytes
Variable Size            3070230544 bytes
Database Buffers         1.7645E+10 bytes
Redo Buffers                5230592 bytes
Database mounted.
Database opened.
SQL> quit
Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
[oracle@mydbserver dbs]$ sqlplus "/ as sysdba"

SQL*Plus: Release 12.1.0.2.0 Production on Fri Jan 27 13:31:17 2017

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

Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

SQL>  SELECT sys_context('userenv','language') from dual;

SYS_CONTEXT('USERENV','LANGUAGE')
--------------------------------------------------------------------------------
AMERICAN_AMERICA.AL32UTF8

SQL> quit
Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production


Merge two tables or keep separate? A little background

We are adding a new feature to our software which allows users to enter text for a specific Customer or Location.  The software will then read these from the database when a Customer or Location is chosen, and display the appropriate text in a pop-up window.  Locations must have a Customer but a Customer can have no Locations.  There are two scenarios that we would show these notifications:

A Customer is selected from a drop down control and the software will look up any notifications for that Customer.
A Location is selected from a drop down control and the software will automatically select the associated Customer and display any notifications for both.

I originally thought about creating two tables: CustomerNotifications and LocationNotifications, but when I added the fields for both, I realized the only difference is one table references a CustomerId while the other references a LocationId:

CustomerNotifications

LocationNotifications

I was thinking of combining the two to look like this:

Combined

Allowing NULL on both CustomerId and LocationId in case the notification is for one specifically.  Is this the best approach or should I keep the table separate?  Will I run into join issues if trying to retrieve both notifications but one is NULL?

My past tells me I have more flexibility with two tables but the "normalization" in me wants to try to condense them.

This is often done as a single Note table, and then seperate join tables to allow for multiple records.

So the tables you end up having are Customer, Location, CustomerNote (CustomerId, NoteId), LocationNote (LocationId, NoteId), Note.

I can't tell you the benefits or negatives of it but that's how it's done.


Why does using a Real data type in a mathematical equation return an incorrect result? I was trying to figure out the source of what appeared to be a rounding error today, and discovered that one of the columns used in a mathematical equation was using the real data type, and this is causing my equation to return an incorrect value.

Can someone explain to me why

SELECT 776.2384 * cast(100 as real) / 100

equals 776.2385 while

SELECT 776.2384 * 100 / 100

equals 776.2384?

See Using decimal, float, and real Data, and Data Type Precedence

In your second query, 776.2384 is a decimal number (and so are the two other values). So the calculation is done in that type, and the result is mathematically exact.

In the first, you introduce a real. The other literals are promoted to that type, and the calculation is done according to IEEE 754 rules, in round up mode. IEEE 754 floating points are not exact, so you will generally not obtain the "mathematically exact" result you're expecting.

Quote from the articles above:

  Because of the approximate nature of the float and real data types, do not use these data types when exact numeric behavior is required, such as in financial applications, in operations involving rounding, or in equality checks. Instead, use the integer, decimal, money, or smallmoney data types.


Database Indexing - Maintenance Jobs I have created a script that runs every night to rebuild & re-organize indexes based on the fragmentation, Indexes with Fragmentation > 30% are rebuilt, Indexes with Fragmentation 10% - 30% are re-organized.

After running the script I noticed we have about 400 indexes that are still reflecting a fragmentation count of > 10%, and after more investigation i came across some posts which mentioned any indexes with a page count < 1000 would not be re-organized by SQL.

I investigated further why my script was not updating all my indexes and found out that the results from the query

sys.dm_db_index_physical_stats(DB_ID(DB_NAME()),NULL,NULL,NULL, 'DETAILED')

show page counts of 5, 6 for tables that have > 1 mill records in, the record_count column had about 16 000 records, I then forced an index rebuild on the index by running

ALTER INDEX [indexname] ON [schema].[table] REBUILD WITH (FILLFACTOR = 85, STATISTICS_NORECOMPUTE = OFF)

and my index now shows 1 mill + record count.

My question is: why would a re-organize of he index not correct the record count in the index and the fragmentation & what would have cause this record count value to become so out dated on the index in the first place?

My plan now is to force a rebuild of all indexes tonight & then run my script as per normal going forward. Should I worry about the index record count becoming out dated again?

Reorganizing an index does not update the statistics on an index.  Reorganize will ensure that the B+ tree is balanced, since SQL Server doesn't auto balance trees during operation.  Rebuilding an index will recreate the index, allowing the engine to update the statistics on an index.

You should consider periodically reorganizing and updating statistics on lightly fragmented queries granted you can spare the maintenance time.  You can also try to update statistics periodically using samples, if your data is homogenous enough.

You can read more about Update Statistics here

http://msdn.microsoft.com/en-us/library/ms187348.aspx

Kimberly Tripp blogs heavily on index maintenance on http://sqlskills.com

Check out her links for Statistics and Indexes.


Can we change the database code page after the database creation? Can we change below configuration of DB2 Database after DB is created.

 Database territory                                      = US
 Database code page                                      = 1208
 Database code set                                       = UTF-8
 Database country/region code                            = 1

Can some one give me the steps to do.

You cannot change the database code set after creation.


Recommended best practice for server naming We're currently in the process of upgrading to SQL Server 2012, in the past all servers have been installed with a naming convention which contains a "-" in the middle i.e. TS-DBL01

I'm trying to convince people that it would be better to remove this is, but I'm starting to question if it really is best practice to change this to TSDBL0.

Any advice welcome

I would say, if you have a naming convention, stick to it. Having 3 naming conventions due to chaninging best naming conventions over time, is not OK.


Why does this Entity Framework query perform so bad in MySQL? Let me give you a little setup.  We have a InnoDB table on MySQL 5.1 with close to 20 million records, no foreign keys, and proper indexes for queries we do.  We are using the latest 6.3.5 MySQL release for the .NET Entity Framework.  Normally we are use to dealing with SQL Server and the Entity Framework, but on this project we decided to give MySQL a try.

I have some theories on the issue but let me first do the code setup

EF LINQ Query

var failsForAcct1001 = db.Failures.Where(x => x.AccountId == 1001);
/* farther down and later on in the code */
return failsForAcct1001.OrderBy(x => x.FailureId).Take(50);

MySQL Code Generated

Please ignore the column names they aren't nessisary to understand the problem

select
    External1.FailureId,
    External1.col2,
    External1.col3,
from (select
          Inner1.FailureId,
          Inner1.col2,
          Inner1.col3,
      from Failures Inner1
      where External1.AccountId = 1001
    ) External1
order by External1.FailureId
limit 50

This SQL being generated is very similar to what happens in SQL Server, and SQL handles it with out a problem.  And this isn't just an Entity Framework issue, when I take this query and execute in MySQL Workbench it also times out.  However the following query returns in under a half second.

select
    External1.FailureId,
    External1.col2,
    External1.col3,
from Failures External1
where External1.AccountId = 1001
order by External1.FailureId
limit 50

I assume this performance problem has to do with the fact that MySQL is trying to execute the inner query which pulls back all the records in the database and then tries to sort and take 50, instead of doing this all as one operation like the second query does.

I really just want to confirm my observations, and ask if there is anything I can do to get the first SQL statement to execute faster, without modifying the query in any way.

Step 1 - get an explain plan, in particular EXPLAIN EXTENDED will show you what SQL the query optimizer actually generated. Maybe additional indexes will help - but if you "outsource" your SQL generation then you really are at the mercy of whatever was easier for the ORM developer, not what was best for your application. Another thing you might try is in-memory temp db, as this can help with sorts, and derived tables, which you do have...


Mongodb Products schema with proper indexes I'm totally new to mongodb with a MYSQL background.

I'm working on an e-commerce website with some millions of products
So we decided to migrate from MYSQL to mongodb.

So far this is the products structure that i came up with as a final schema

{
    id: 12, // filterable + sortable
    sku: 'some-sku', // filterable
    status: 'enabled', // filterable by default to get enabled products only
    createdAt: {
        date: '22-11-2018',
        timestamp: 41231312
    },
    locales: {
        en: {
            title: 'Product Title', // searchable + sortable
            description: 'Some Rich description',
            shortDescription: 'Some short description',// searchable
            category: 'Category name',
            brand: 'Brand name',
            tags: ['tag1', 'tag2', 'tag3'],// searchable
            attributes: {
                // filterable
                color: 'red',
                size: 'XL'
            },
            specifications: {
                // list of specifications
            }
        }
    },
    images: {
        main: {
            large: 'path-to-image',
            medium: 'path-to-image',
            small: 'path-to-image'
        },
        all: [
            {
                large: 'path-to-image',
                medium: 'path-to-image',
                small: 'path-to-image'
            }
        ]
    },
    categoryId: 562, // filterable
    brand: {
        id: 13, // filterable
        logo: 'path-to-log'
    },
    price: {
        original: 200,
        discount: {
            value: 40,
            percentage: 20 // filterable + sortable
        },
        salePrice: 160 // filterable with a min-max price range + sortable
    },
    rating: 4.5, // filterable + sortable
    reviews: [
        {
            customer: {
                id: 644121,
                name: 'Customer Name',
                review: 'Customer review',
                rating: 4,
                createdAt: {
                    date: '22-11-2018'
                }
            }
        }
    ],
    children: [
        // list of children products same schema
    ],
    similarProducts: [
        // list of similar products ids
    ]
}

Quick notes about the schema:

There are many fields will be used in search/filter like:

By default, Only enabled products will be displayed.
title, shortDescription and tags as full text search.
Also customer can filter products by sku category id, brand id, salePrice, rating and attributes.

Customer can sort products display using any of the following in asc/desc order:

Latest/Oldest: using the creation date
Price: High to low or Low to high
Rating: High to low or Low to high
Discount Percentage: High to low or Low to high

So Here are my questions:

1- Based on the earlier info, customer may combine many filters in same time combined with different sort based on his selection, so should i've to create indexes for all use cases?

2- Products could be in more than on category and also any product in child category will be implicitly added to its parent categories recursively

For example if we've the following category tree:

Sports >> Clothes >> Men >> Shoes

if a product in the Shoes category, it should be also in all previous categories.

In MYSQL i had a product_categories table that holds product_id and category_id so i can retrieve directly all products for the current category without looping through all children to get its ids.

Now should i add another field called categoriesList that will contain all the ids of the categories that the product is included in?

Sorry for the long talk and thanks in advance.

Based on your requirements, following is the solutions:

You must find the frequent query areas. At initial lookup it looks like  adding an index for each of the use case is good idea. But you may need to revise it over the period of time and observation of index utilization. Following statement from the official document describes the most important point about the cost of indexing:

  Before you build indexes, map out the types of queries you will run so that you can build indexes that reference those fields. Indexes come with a performance cost, but are more than worth the cost for frequent queries on large data set. Consider the relative frequency of each query in the application and whether the query justifies an index.

You can find more details here.
You are correct on this point.


What is '/Record/SchedulerMonitorEvent/SystemHealth/ProcessUtilization' telling me? I am trying to use dm_os_ring_buffers in order to get an approximation of CPU usage. I've found a few of queries online which get values from /Record/SchedulerMonitorEvent/SystemHealth/ProcessUtilization but I'm not sure what this value means. When I run it against one of my servers I'm getting 1, can someone explain this value?

Here's a sample from the query I'm using for context:

WITH SQLProcessCPU
AS(
   SELECT TOP(30) SQLProcessUtilization AS 'CPU_Usage', ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) AS 'row_number'
   FROM (
         SELECT
           record.value('(./Record/@id)[1]', 'int') AS record_id,
           record.value('(./Record/SchedulerMonitorEvent/SystemHealth/SystemIdle)[1]', 'int') AS [SystemIdle],
           record.value('(./Record/SchedulerMonitorEvent/SystemHealth/ProcessUtilization)[1]', 'int') AS [SQLProcessUtilization],
           [timestamp]
         FROM (
              SELECT [timestamp], CONVERT(xml, record) AS [record]
              FROM sys.dm_os_ring_buffers
              WHERE ring_buffer_type = N'RING_BUFFER_SCHEDULER_MONITOR'
              AND record LIKE '%<SystemHealth>%'
              ) AS x
        ) AS y
)

Thanks!

Here is a sample output of XML from sys.dm_os_ring_buffers where WHERE ring_buffer_type = N'RING_BUFFER_SCHEDULER_MONITOR'. I will explain each item from this XML.

<Record id = "38446" type ="RING_BUFFER_SCHEDULER_MONITOR" time ="2313297649">
<SchedulerMonitorEvent>
    <SystemHealth>
        <ProcessUtilization>2</ProcessUtilization>
        <SystemIdle>91</SystemIdle>
        <UserModeTime>310468750</UserModeTime>
        <KernelModeTime>35781250</KernelModeTime>
        <PageFaults>7004</PageFaults>
        <WorkingSetDelta>0</WorkingSetDelta>
        <MemoryUtilization>100</MemoryUtilization>
    </SystemHealth>
</SchedulerMonitorEvent>

ProcessUtilization Indicates the amount of CPU SQL Server was using at the time of the snapshot.
SystemIdle Amount of Idle CPU that nothing is using. Available for any process that requires CPU.
(100-ProcessUtilization-System Idle) CPU being used by processes other than SQL Server.
UserModeTime Indicates the amount of CPU worker thread (Running in user mode) used during the period it did not yield. You need to divide this value by 10,000 to get time in milliseconds
KernelModeTime Indicates the amount of CPU worker thread (Running in Windows kernel) used during the period it did not yield. You need to divide this value by 10,000 to get time in milliseconds.
PageFaults - Number of page faults at the time of the snapshot. A page fault occurs when a program requests an address on a page that is not in the current set of memory-resident pages.
WorkingSetDelta Difference in working set between last and current snapshot.
MemoryUtilization Indicates the percentage of memory SQL Server is using based on max server memory (MB) setting. 100% is normal in this case as SQL OS is based on a greedy algorithm. It will consume all memory unless it is forced to give up memory due to external factors.

  When I run it against one of my servers I'm getting 1, can someone
  explain this value?

Means you are using 1% of total CPU (guessing all core are visible to SQL operating system) at the time of the snapshot. You want to collect this for over a period of time and set your baseline.  That way at any time you know if your running high or low compare to your baseline.

Some additional resource:

One of the best article by Denzi Ribeiro about troubleshooting high CPU issue using sys.dm_os_ring_buffers
How to understand the RING_BUFFER_SCHEDULE_MONITOR by Jack Vambas