Get seconds of day in Postgres In Java we use Joda-Time to get the seconds of a day as an int value (despite the date):

date: 10-10-2014 00:00:30 -> second 30 of day
date: 11-10-2014 00:01:30 -> second 90 of day
date: 12-10-2014 00:02:00 -> second 120 of day

Is there a way to do the same in PostgresSQL?

extract() the epoch from the time component after casting to time (effectively removing the date component):

SELECT extract(epoch FROM ts::time) AS seconds_of_day

You get the "number of seconds", including fractional seconds if there are any.
Very short and fast.

Test (with timestamps in unambiguous ISO format):

SELECT extract(epoch FROM ts::time) AS seconds_of_day
FROM  (
   VALUES
     ('2014-10-12 00:00:30'::timestamp)   --    30
   , ('2014-10-12 00:01:30')              --    90
   , ('2014-10-12 00:02:00')              --   120
   , ('2014-10-12 12:00:00')              -- 43200
   , ('1999-12-23 23:59:59')              -- 86399
   , ('1999-12-23 23:59:59.123456')       -- 86399.123456
  ) t(ts);

db<>fiddle here


assign selected columns to a variable Select
  sum(column_a) as a,
  sum(column_b) as b
  round(a / b)
From
  table
Group by column

In the example above I am summing two column, and I would like to use those 2 results in calculating another result, how can I do this? round(a / b) is not going to work, is just as an example of what I want to do.

You are right, the columns in a SELECT at the same level can't refer to each other. Use a subquery instead:

SELECT
  sum_a as a
  sum_b as b
  round(sum_a / sum_b) as a_over_b
FROM (
  SELECT
    sum(column_a) as sum_a,
    sum(column_b) as sum_b
  From
    table
  Group by column
) as subq


Large rolling update on huge table: Getting slower and slower SQL Server 2014 Standard Edition

We have a table with 100million+ rows.

We need to update values in a couple of columns.

We did the below, and it has been running for 18 hours now, and has only done 17 million rows. It is getting slower and slower.

It is a reasonably hot server and disk system (EMC RAID10 and all that). The CREATE INDEX took about 20 minutes (an unfortunate outage we had to tolerate).

What approach can we use to get through this faster? (on line strongly preferred)

CREATE NONCLUSTERED INDEX RECORD_DELETED_INDEX ON [dbo].[huge-table] ( [deleted] ASC, [deletedDate] ASC );
GO

DECLARE @CHUNK_SIZE int
SET @CHUNK_SIZE = 4000  -- to stay under lock escalation threshhold

UPDATE TOP(@CHUNK_SIZE) [huge-table] set deleted = 0, deletedDate = '2000-01-01'
where deleted is null or deletedDate is null

WHILE @@ROWCOUNT > 0
BEGIN
    UPDATE TOP(@CHUNK_SIZE) [huge-table] set deleted = 0, deletedDate = '2000-01-01'
    where deleted is null or deletedDate is null
END

It probably would have been better to create the index as filter (i.e. with a WHERE clause) as that would get smaller as you updated more records that would then get filtered out of it:

CREATE NONCLUSTERED INDEX RECORD_DELETED_INDEX
  ON [dbo].[huge-table] ( [deleted] ASC, [deletedDate] ASC )
  WHERE deleted is null
  OR    deletedDate is null;

However, you are still going to run into the issue of scanning that index every time that loop runs. With 100 million rows in the table and updating 4000 per iteration, that is 25,000 times you are scanning the table (or index) looking for rows to update. It would be best if you reduced the number of times you queried the table.

Please see my answer to one of your other questions regarding this project for a setup that should make this process go much faster:

sql server: updating fields on huge table in small chunks: how to get progress/status?

In that answer, I show how to query the large table only 100 times. Each time it grabs the Clustered Index Key Field(s) and uses them for all of the UPDATE queries so that the updates are fast. And that setup also allows for getting the current progress of the operation quickly (and without having to query the table!) and cleanly cancelling the process.

It should be noted that this question relates to the following questions (listed in chronological order):

sql server: updating fields on huge table in small chunks: how to get progress/status? (noted above)
Are explicit transactions needed in this while loop?
SQL Server lock_escalation flag on ALTER TABLE -- can I really restructure without a table lock?


self referencing tables with SQL and entity framework I have many tables in a database and the tables are nearly identical. There are limitations currently on what objects reference other objects. For example I have company,contact and project. Contact is a child of company but no relation to project although some of our users would like there to be.

Since the tables are all similar I wondered if it would be a good idea to create a self referencing table with a type property on each record. Each record in the table would be a specific type ie company,contact etc and have X number of foreign key fields which would reference the same table.

This gives our users the ability to create their own record types and link them however they choose.

Is this going to cause me any problems in the future that I haven't thought of? Our system allows users to create their own fields on an object so this seems perfect. But they can't also report on this data. Performance wise I am thinking this should be OK as data would only ever be in 1 table.

Relational modelling starts from the identification of classes of "things." To these we apply normalisation to remove data update anomolies.  The results are implemented as tables with a column for each attribute we identify.  We do not start from a bunch of domains, look around for "things" with the same bunch of domains, shoehorn them into the same table and call it an Entity.  This is an anti-pattern generally called "One True look-up Table".  Implementing it compromises what constraints can be defined & how foreign keys can be established.  Tables can become bloated with NULL-able columns.  This increases the IO required to fulfil queries.  Index depth increases, slowing even key lookups.

Now, at some stage in every project the science of normalisation bumps up against the art of model design.  Decisions have to be made and compromises have to be accepted.  In your case I can see one of three models emerging.

The first is the normalised one you have currently.  This is how RDBMS's are supposed to work.  Mappings are obvious; queries are fast.  Exansion, however, requires schema changes and these can be slow to implement, depending on your company's abilities.

The second is a super type / sub type model.  You treat Company, Contact etc. as specialisations of another type, say LegalPerson.  This super type contains the common attriubtes.  Each sub type has the attributes specific to it.  There is a one-to-one relationship between the super type and the sub type i.e. a given "thing" must have a row in the super type and exactly one sub type.  This can be difficult to enforce in current SQL products.  This allows recognition of common columns between entity types.  Relationships between the types are held in specific foreign key columns or intersection entities. The insertion and manipulation of data is more complex. Again, expansion of the schema will reqire development work.

Finally there is the entity-attribute-value (EAV) model.  Here you are effectively building a database-within-a-databse.  The objects you model are "Table" and "Column" instead of "Customer" and "Company".  Thus your application can ammend the logical schema at will.  The price is application complexity and run-time performance.

EAVs have a bad reputation.  There are many posts explaining why perdition will descend upon you simply by uttering the name aloud.  Others defend the practice.  I myself have an EAV systems holding 300M values.  It works well for us.  But I did this intentionally, in full understanding of the implications, with a DBA team to support me, to solve a specific problem we could not address otherwise.  It was not done because some of the tables looked a bit like others.


Check constraint to require one of two columns I'm using SQL Server 2008 R2 and would like to make one of two columns required on a table; in other words, if the user provides a value for one, the other isn't required but the user can provide a value for both. How do you do this using check constraints? Thanks.

Update: Here's the table definition; the two columns are alternateID and securityID:

CREATE TABLE [dbo].[Employee](
    [employeeID] [int] IDENTITY(9500,1) NOT NULL,
    [alternateID] [nvarchar](50) NULL,
    [securityID] [nvarchar](50) NOT NULL,
    [firstName] [nvarchar](50) NOT NULL,
    [middleName] [nvarchar](50) NULL,
    [lastName] [nvarchar](50) NOT NULL,
    [suffix] [nvarchar](50) NULL,
    [job] [nvarchar](50) NULL,
    [organizationalUnit] [nvarchar](50) NOT NULL,
    [costCenter] [nvarchar](50) NOT NULL,
    [notes] [nvarchar](95) NULL,
    [createdDate] [datetime] NOT NULL,
 CONSTRAINT [PK_Employee] PRIMARY KEY CLUSTERED
(
    [employeeID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF,
ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

Expanding on ypercube's question comments:

The first thing you need to do is make the securityID column nullable:

ALTER TABLE dbo.Employee
ALTER COLUMN securityID nvarchar(50) NULL;

Then the required check constraint is quite straightforward:

ALTER TABLE dbo.Employee
ADD CONSTRAINT [CK dbo.Employee securityID or alternateID must not be NULL]
CHECK (securityID IS NOT NULL OR alternateID IS NOT NULL);

Regarding the 'friendly' name I chose for the check constraint, see:

Naming CHECK and UNIQUE Constraints by Greg Low.


How do I kill a rollback process when I want to drop the database? I want to drop a SQL Server 2008 database, but it is currently performing a rollback for a transaction (that transaction took 3 days to run so far).

How do I stop the rollback because I want to drop the database?

Thank you.

In short you can't. You have to wait for the transaction to rollback before the locks are released.

I suppose you could stop the SQL service and delete the database files. Then when the instance comes back online delete the suspect database.  But this requires an outage and that you are very sure you are deleting the correct files.


COUNT after CROSS JOIN I have a script that cross joins about 4 tables:

SELECT *
FROM Districts
CROSS JOIN Seasons
CROSS JOIN Sectors
CROSS JOIN Sites

The above query will combine all the tables into one, see a sample picture:

I want to add two more tables Group and Client, but not the entire tables, just the COUNT of both tables. so after the print column in the picture below, there should be two more columns, GroupCount for group table count and ClientCount for client table count. How do I do it?

It's probably easiest to add one correlated sub-query for each count:

SELECT D.*, S1.*, S2.*, S3.*
     , ( SELECT COUNT(1) FROM Group G WHERE G.<key> = ... ) as GroupCount
     , ( SELECT COUNT(1) FROM Client C WHERE C.<key> = ... ) as ClientCound
FROM Districts as D
CROSS JOIN Seasons as S1
CROSS JOIN Sectors as S2
CROSS JOIN Sites as S3

Since I don't know how Group and Client relates to the other tables, I left that as an exercise for the reader ;-)

It's quite unusual to do a cartesian product between 4 tables in a query, but perhaps you have your reason for that. Beware that the cardinality of the result is the product of the cardinality for the individual tables. If the cardinality for the tables is 100, the result will contain 100^4=100000000 rows. For a cardinality of 1000, you end up with 1000^4=1000000000000. If each row is 1 byte, the result in the latter case is 1Tb.


What is the query to get the process limit on Oracle 11g? I'm getting the error:

Maximum Number Of Process Exceeded

I want to see (using a query) what the setting is in my database for the process limit.

My question is: What is the query to get the process limit on Oracle 11g?

select max(maxval) as PROCESS_LIMIT
from dba_hist_sysmetric_summary
where METRIC_NAME in ('Process Limit %');


Two left joins where one exists? Basically, I have table a, which has a type and type_id and want to join on either two different tables where there is a hit.

So basically, if table b has a match, return the record or if table c has a match, return that record (essentially two inner joins with an OR); just one of them has to match. The below query seems to work... however I feel like this is a very naive solution that will actually result in me running into some issues.

SELECT
    a.*
FROM
    a
    LEFT JOIN b ON a.type_id = b.id
    AND b.user_id = 123
    AND a.type = 'dog'
    LEFT JOIN c ON a.type_id = c.type_id
    AND c.user_id = 123
    AND a.type = 'cat'
WHERE
    b.id IS NOT NULL
    OR c.type_id IS NOT NULL;

Basically, I want all the records where the user exists in either table.

You could rewrite with OR and two EXISTS subqueries and compare performance with your query:

SELECT
    a.*
FROM
    a
WHERE
    EXISTS
    ( SELECT FROM b
      WHERE a.type_id = b.id
        AND b.user_id = 123
        AND a.type = 'dog'
    )
 OR EXISTS
    ( SELECT FROM c
      WHERE a.type_id = c.type_id
        AND c.user_id = 123
        AND a.type = 'cat'
    ) ;

or:

SELECT
    a.*
FROM
    a
WHERE
    a.type = 'dog'
    AND EXISTS
    ( SELECT FROM b
      WHERE a.type_id = b.id
        AND b.user_id = 123
    )
 OR a.type = 'cat'
    AND EXISTS
     ( SELECT FROM c
       WHERE a.type_id = c.type_id
         AND c.user_id = 123
    ) ;

Your query seems correct, assuming that there are unique constraints or indexes on b (user_id, id) or on b (id) - and c (user_id, type_id).

It does however looks somewhat obfuscated - it is not clear without careful reading that it restricts the result to rows that have WHERE a.type_id IN ('dog', 'cat').


Does SQL Server 2008 store the creation date of execution plans? We recently upgraded an application we use, which involved modifying the schema for the database. These changes could have forced cached execution plans to be discarded. If SQL Server was forced to create a bunch of new plans, this could have slowed down the user experience. I'd like to find out if this was the case.

So, my question is, does SQL Server 2008 store the creation date of cached execution plans? The management view sys.dm_exec_cached_plans doesn't have any date fields, so I suspect not.

It isn't stored in sys.dm_exec_cached_plans, nor is it buried anywhere in the plan XML that I can find. There is useful information in other DMVs however.

For stored procedures we can get the time a plan was cached from sys.dm_exec_procedure_stats:

SELECT TOP(250)
    p.name AS [SP Name]
    , ps.execution_count
    , ps.cached_time
FROM
    sys.procedures p WITH (NOLOCK)
INNER JOIN
    sys.dm_exec_procedure_stats ps WITH (NOLOCK)
ON  p.[object_id] = ps.[object_id]
WHERE
    ps.database_id = DB_ID()
ORDER BY
    ps.cached_time DESC
OPTION
    (RECOMPILE);

For ad-hoc queries, there is the creation time in sys.dm_exec_query_stats:

SELECT TOP(250)
    st.[text] AS [QueryText]
    , qs.execution_count
    , qs.creation_time
FROM
    sys.dm_exec_cached_plans cp WITH (NOLOCK)
INNER JOIN
    sys.dm_exec_query_stats qs WITH (NOLOCK)
ON  qs.plan_handle = cp.plan_handle
CROSS APPLY
    sys.dm_exec_sql_text(cp.plan_handle) st
WHERE
    cp.objtype = N'Adhoc'
ORDER BY
    qs.creation_time DESC
OPTION
    (RECOMPILE);

According to @SqlKiwi... the change from creation_time to cached_time was just because procedures and triggers were added in 2008 and the opportunity was taken to choose a more descriptive name. The created/cached time reflects the last compilation, not the creation time of the original plan.


How to extract the date from this varchar column I need to remove records from this table that are older than 30 days.

We only have 2 columns in this table. The first date after 'Printed_%' is what I will be using to determine if the record should be deleted. Notice some of the dates are in MM/DD/YYYY format while some appear in M/D/YYYY format in the string. How can extract the date so I can determine which rows are older than 30 days?

ID      Command
123232  Printed_9/6/2017 2:36:09 PM W;03;2K;8/6/2013;8/6/2015;ODSI3498
123233  Printed_9/6/2017 2:53:20 PM W;07;2K;8/6/2013;8/6/2015;PWEOIR123
123234  Printed_9/6/2017 2:54:44 PM W;15;2K;11/9/2011;11/18/2005;PWEOIR123
123235  Printed_10/21/2016 10:21:10 AM W;15;2K;11/9/2011;11/18/2005;ODSI3498
123236  Printed_11/15/2015 10:30:13 AM W;02;2K;8/7/2013;8/7/2014;ODSI3498
123237  Printed_12/7/2014 10:30:26 AM W;21;2K;7/30/2013;7/30/2014;ODSI3498


SQL Server - High Performance Record Comparison Scenario : I have a collection of records to be inserted/updated into a table. Each record before getting inserted/updated in the table must be checked with the other rows in the table so as to find whether the same record already exists.

Currently used logic : Used the concept of cursor in an SQL server stored procedure to compare each record with every other record in the table before insertion/updation.

Limitations : As the number of records increases in the target table, performance is considerably decreased.

Can I get a better solution than what I have used?

Have you looked at using the Merge command, this was introduced in SQL Server 2008 and does an update or insert based on your matching criteria.

I use this for merging XML quite a lot and it seems very efficient.

MERGE [Database] AS TargetTABLE
USING (SELECT @Var1 AS [Value1]
    @Var2 AS [Value2],
    @Var3 AS [Value3]) AS SourceTABLE
ON TargetTABLE.[id] = SourceTABLE.[Value1]
WHEN MATCHED THEN
    UPDATE SET TargetTABLE.[Value2] = SourceTABLE.[Value2],
        TargetTABLE.[Value3] = SourceTABLE.[Value3]
WHEN NOT MATCHED THEN
    INSERT (
        [id],
        [Value1],
        [Value2],
        [Value3])
    VALUES (
        SourceTABLE.[Value1],
        SourceTABLE.[Value2],
        SourceTABLE.[Value3]
    );

Merge Command


Mp3 file directly in a Database Without using BLOB I need to store Mp3 files in a Database.

Problem is that the files are going to be accesed with a smartphone. As the Smartphone is going to need to decode the  Blob it would be very slow.

I tried already to do this with a MariaDB but discovered that the data was stored as a Blob...

Im asking for a way to store the files in a Databank without changing the format.
If there is one Please enlighten me!

There is probably no way to have a binary piece of data (file) stored in a database (either relational or non-relational, NoSQL, that is) as "not blob" and without any need to decode it at the same time.

If you don't want to decode it, store it as a BLOB.
If you don't want to store it as a BLOB, store it as a CLOB and then decode it.
If you want neither of the above, consider another means of storage
Accessing a database directly from "outer world" is generally a bad idea.


Approach to take regarding a field that is frequently re-computed I have a few fields with text data in a main table which is processed in a certain way to produce a processed version of that piece of text.

Now, the way in which this text is processed is expected to change over time so that at some point a new version of the processed text would have to be stored in the database. We would keep the newest and previous versions of the processed data, but stop inserting new data into the previous version.

I see a number of options:

Create a table for each version of the processor which stores the text and reference the main table. Drop table as they become redundant.
Add new column to the existing processed table and delete the oldest column.
Clear the oldest column and then insert new data into it.

I am wondering what the best approach is here.

As a sidenote - what would be the requirement on using a vacuum etc.

I am using PostgreSQL 9.4 on Debian and the number of rows are around 56 million and continuously increasing.

I would add another table for versioning of data. It would have composite key of data and timestamp or version number and in table I would hold actual data.
When new data arrives in table data is updated and in versioning table is inserted.

Regarding vacuuming you set auto vacuum for that table.
If table gets bloated despite auto vacuum setting you can run vacuum full,
but it would fully lock your database.
If you are running on linux you can use pg_repack extension.


Query to List all hierarchical children with parent, grandparent etc. from entire table I have a basic sql table with an ID and an optional parentID from the same table. This has multiple levels. I need to get a list of all IDs with each of its parents all the way to the top.

if object_id('tempdb..#table') is not null drop table #table
create table #table (id int identity(1,1), [Name] varchar(16), ParentID int null)
insert into #table
values
('John',null)
,('Doe',1)
,('Mike',1)
,('Neil',3)
,('Sylvia',4)
,('Mary',2)

+----+--------+----------+
| id |  Name  | ParentID |
+----+--------+----------+
|  1 | John   | NULL     |
|  2 | Doe    | 1        |
|  3 | Mike   | 1        |
|  4 | Neil   | 3        |
|  5 | Sylvia | 4        |
|  6 | Mary   | 2        |
+----+--------+----------+

I need to get a list like this:

ID  | Parent | DirectParent
_______________________
2     1       1
3     1       1
4     3       1
4     1       0
5     4       1
5     3       0
5     1       0

It ignores any record that does not have a parent.
For each child there is a record matching it to one of its parents
There is a bit column to know which parent is the one directly above it

How can I query the above table to get this data?

Edit: see what I have been working with

 WITH Recursives AS (
        SELECT   CAST(ID AS VARCHAR(MAX)) + '\' ID,*
        FROM    table
        WHERE   id=@ID
        UNION ALL
        SELECT  r.ID + CAST(t.ID AS VARCHAR(MAX)) + '\', t.*
        FROM    table t
        INNER JOIN Recursives r    ON  t.parentID= r.ID)

    SELECT ID,Name,Parent
    FROM Recursives r

I use this to get a list for a specific parent all the way down.

I can't figure out how to do this for the entire table without
getting an error  The statement terminated. The maximum
recursion 100 has been exhausted before statement completion.
How can I get the same child multiple times. Once with each parent?

you just cannot do recursive on millions of rows.you have to put some filter

Try this,

F object_id('tempdb..#table') IS NOT NULL
    DROP TABLE #table

CREATE TABLE #table (
    id INT identity(1, 1)
    ,[Name] VARCHAR(16)
    ,ParentID INT NULL
    )

INSERT INTO #table
VALUES('John',null)
,('Doe',1)
,('Mike',1)
,('Neil',3)
,('Sylvia',4)
,('Mary',2)

    WITH CTE
AS (
    SELECT id
        ,ParentID,1 DirectP
    FROM #table

    UNION ALL

    SELECT r.ID
        ,t.parentID,0 DirectP
    FROM #table t
    INNER JOIN CTE r ON t.id = r.parentID
    WHERE t.ParentID IS NOT NULL
    )
SELECT r.ID
    ,r.ParentID
   ,DirectP
FROM CTE r

ORDER BY id
    ,ParentID DESC


Create USER and LOGIN permissions What permission should I give to a user or role so that it can Create Login or Create User in database.

As pointed above by Jeremy, permissions can almost always be found on the corresponding reference document within Books Online for SQL Server.  On these reference documents, there is typically a section titled Permissions (typically towards the bottom of the document) which contains your desired data.
CREATE LOGIN (copied directly from the reference)

Requires ALTER ANY LOGIN or ALTER LOGIN permission on the server.
If the CREDENTIAL option is used, also requires ALTER ANY CREDENTIAL permission on the server.

CREATE USER (copied directly from the reference)

Requires ALTER ANY USER permission on the database.


Using Oracle Directories names as same of path We, at our company, have been using a small hack on Oracle Directories names since some time ago.

Before Oracle Directories we have to explicitly set the directory on pfile, by the utl_file_dir parameter. To add a new directory we had to reboot the instance.

After Oracle Directories, everything become easier!

But some legacy scripts still have been using the old style, using utl_File.Fopen('/path',...) instead of utl_File.Fopen('DIRECTORY',...)

The small hack is create a directory in which its name is the exactly path of the desired directory. In a simple example:

create or replace directory "/tmp" as '/tmp';

select * from dba_directories ;

OWNER                          DIRECTORY_NAME                 DIRECTORY_PATH
------------------------------ ------------------------------ ---------------------
SYS                            /tmp                           /tmp

So, the users can normally use a command like that:

l_file_handle := UTL_FILE.FOPEN('/tmp','file.txt','W');

That's really nice.

But I'm facing a problem when using such a directory as the output of a EXPDP dump. EXPDP parser seems to ignore any quotations marks, and is parsing the directory name in uppercase.

$expdp \"/ as sysdba\" parfile= parfile.par

Export: Release 10.2.0.3.0 - 64bit Production on Thursday, 05 April, 2018 10:50:19

Copyright (c) 2003, 2005, Oracle.  All rights reserved.

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options
ORA-39002: invalid operation
ORA-39070: Unable to open the log file.
ORA-39087: directory name /TMP is invalid

[oracle@brux0009 P00OMS /EXPORT/spool]$ cat parfile.par
DIRECTORY='/tmp'
DUMPFILE=dual.dmp
LOGFILE=dual.log
TABLES=SYS.dual

Any tips?

Just because you are able to do something, it does not necessarily mean it is a good idea.

[oracle@o73 ~]$ sqlplus / as sysdba

SQL*Plus: Release 18.0.0.0.0 Production on Thu Apr 5 16:20:15 2018
Version 18.1.0.0.0

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

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

SQL> create directory "/home/oracle" as '/home/oracle';

Directory created.

SQL> Disconnected from Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production
Version 18.1.0.0.0
[oracle@o73 ~]$ expdp \'/ as sysdba\' directory=\\\"/home/oracle\\\" schemas=bp

Export: Release 18.0.0.0.0 - Production on Thu Apr 5 16:20:33 2018
Version 18.1.0.0.0

Copyright (c) 1982, 2018, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production
Starting "SYS"."SYS_EXPORT_SCHEMA_04":  "/******** AS SYSDBA" directory=\"/home/oracle\" schemas=bp
Processing object type SCHEMA_EXPORT/USER
Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
Processing object type SCHEMA_EXPORT/ROLE_GRANT
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Master table "SYS"."SYS_EXPORT_SCHEMA_04" successfully loaded/unloaded
******************************************************************************
Dump file set for SYS.SYS_EXPORT_SCHEMA_04 is:
  /home/oracle/expdat.dmp
Job "SYS"."SYS_EXPORT_SCHEMA_04" successfully completed at Thu Apr 5 16:20:53 2018 elapsed 0 00:00:18


MySQL trigger to update a key if record exists I'm trying to build a trigger in MariaDB to follow this logic:

1. If the primary key val exists, increment the counter column
2. Else, insert the primary key into the table

Here is my prosed trigger:

delimiter //
CREATE TRIGGER volume_manager BEFORE INSERT ON individual_key_log
    FOR EACH ROW
    BEGIN
        IF NEW.reference_key not in (
            select *
            From individual_key_log
            where (NEW.reference_key = 'test')
        ) THEN -- MISSING THEN
           CALL `Insert not allowed`;
        END IF;
    END;
//
delimiter ;

Why write a trigger for this ? I have two reasons why you should not do that in this instance.

REASON #1

Let's assume the following:

reference_key

is not an auto_increment column
is the primary key

counter

is the column to increment
is defined as INT NOT NULL DEFAULT 1

You can write the INSERT as follows

INSERT INTO individual_key_log (reference_key,...)
VALUES (...) ON DUPLICATE KEY UPDATE counter = counter + 1;

This is a much cleaner approach than a trigger

REASON #2

I noticed you have a CALL in the middle of your trigger. Believe me, you do not want to make a trigger that breaks midstream unless it is necessary. Calling anything in a trigger requires overhead and can hamper query performance. I wrote about this back on Jan 13, 2012 : Call a stored procedure from a trigger

I also wrote about how jerryrigging a trigger can halt normal operation if not done properly : See my post from Apr 25, 2011 : Trigger in MySQL to prevent insertion where I demonstrate how to halt a trigger to prevent insertion checking a separate table.


How should I (re) index the table while keeping the transaction log small? I have a 170 GB table in a database. It has 70% fragmentation on its index (index has multiple columns in it). The last time I tried DBCC REINDEX the transaction log blew up in the hundreds of GBs.

Should I drop the index and recreate it or do a DBCC REINDEX?
What is the best way to do either one?
How can I do either one while keeping the transaction log as small as possible?

DBCC REINDEX is minimally logged in the SIMPLE or BULK_LOGGED recovery model.  If the database is currently in the FULL recovery model, consider toggling it to BULK_LOGGED for the index maintenance.

See http://technet.microsoft.com/en-us/library/ms191484(v=sql.105).aspx


Summing Line Items In SQL @SQLZim helped me on my ? earlier and the syntax works for about 90% of my cases, but the 10%, I can not say the same for.  Let's use sample DDL below, and the query produces accurately for emp1 but not for emp2

My Expected output would be emp1 = .4050 and emp2 = .2761 however the query results produce emp1 = .4050 BUT emp2 = .6276

Why the discrepancy in calculations?  For further breakdown, this is the calculation I am trying to achieve

emp1 = (1111-(661+(0*.75)))/1111 = .4050
emp2 = (1954.85-(1120.65+(392.7*.75)))/1954.85 = .2761

Which should lead to the results above, however if you look at my DDL below I do not get that outcome, I get

employee    AnnualSales DeptSales   Tax     SomeCalc
emp1        1111.00     661.00      0.00    0.405040
emp2        1954.85     1120.65     392.70  0.627618

DDL

Declare @Calc Table
(
  employee varchar(100)
  ,annualsales decimal(10,2)
  ,dep1sales decimal(10,2)
  ,dep2sales decimal(10,2)
  ,dep3sales decimal(10,2)
  ,dep4sales decimal(10,2)
  ,dep5sales decimal(10,2)
  ,tax decimal(10,2)
)

Insert Into @Calc (employee, annualsales, dep1sales, dep2sales, dep3sales, dep4sales, dep5sales, tax) Values
('emp1', '1111.00', '26.12', '83.00', '16.55', '21.77', '87.08', null)
,('emp1', null, '28.07', '11.00', '101.24', '47.03', '76.19', null)
,('emp1', null, '35.25', '7.00', '33.64', '43.54', '43.52', null)
,('emp2', '1954.85', NULL, '432.45', NULL, NULL, NULL, NULL)
,('emp2', NULL, NULL, '688.20', NULL, NULL, NULL, '392.70')

Select
employee
,AnnualSales=sum(isnull(annualsales,0))
,DeptSales    =sum(isnull(dep1sales,0)+isnull(dep2sales,0)+isnull(dep3sales,0)+isnull(dep4sales,0)+isnull(dep5sales,0))
,Tax        =sum(isnull(tax,0))
,[SomeCalc] =(sum(isnull(annualsales,0))
         -sum(isnull(dep1sales,0)+isnull(dep2sales,0)+isnull(dep3sales,0)+isnull(dep4sales,0)+isnull(dep5sales,0))
         +sum(isnull(tax,0))
        )
        /nullif(sum(isnull(annualsales,0)),0)
from @Calc
group by employee
order by employee asc

How should my query be altered in order for the above expected output to be produced?

Instead of posting again, you could have updated your previous question.

The issue with the old query is that it wasn't calculating tax *.75 appropriately.

rextester: http://rextester.com/ZGQ51639

Declare @Calc Table
(
  employee varchar(100)
  ,annualsales decimal(10,2)
  ,dep1sales decimal(10,2)
  ,dep2sales decimal(10,2)
  ,dep3sales decimal(10,2)
  ,dep4sales decimal(10,2)
  ,dep5sales decimal(10,2)
  ,tax decimal(10,2)
)

Insert Into @Calc (employee, annualsales, dep1sales, dep2sales, dep3sales, dep4sales, dep5sales, tax) Values
('emp1', '1111.00', '26.12', '83.00', '16.55', '21.77', '87.08', null)
,('emp1', null, '28.07', '11.00', '101.24', '47.03', '76.19', null)
,('emp1', null, '35.25', '7.00', '33.64', '43.54', '43.52', null)
,('emp2', 1954.85, '1120.65', '0', '0', '0', '0', 392.70)

Select
employee
,AnnualSales=sum(isnull(annualsales,0))
,DeptSales  =sum(isnull(dep1sales,0)+isnull(dep2sales,0)+isnull(dep3sales,0)+isnull(dep4sales,0)+isnull(dep5sales,0))
,Tax        =sum(isnull(tax,0))
,[SomeCalc] =(sum(isnull(annualsales,0))
             -(
               sum(isnull(dep1sales,0)+isnull(dep2sales,0)+isnull(dep3sales,0)+isnull(dep4sales,0)+isnull(dep5sales,0))
             +(sum(isnull(tax,0))*.75)
              )
             )
            /nullif(sum(isnull(annualsales,0)),0)
from @Calc
group by employee
order by employee asc


Mapping and Inserting Values into a Specialized table For a system i'm designing,i have one parent entity,Computer

Computer(computer_id,computer_processor,computer_hddrive,computer_memory);

A computer can be of two types,Desktop and Laptop,each having their own specific attributes.

Laptop(laptop_id(Foreign key of computer_id),laptop_color,laptop_battery);

and similarly a table for Desktop.

Now the entries to these tables is via a end-user form,my main question is,
what would the exact INSERT queries be for adding say to the inventory,a laptop if i were to have 3 tables,one superclass and two subclass tables?
I understand i would first how to make an entry to the computer table and then laptop,but how will this be carried out automatically?

It's not always carried on automatically. Depends on the ORM you are using (if any ) on the app side it may or may not be implemented . For instance, it works for Hiberanate (personally I use @Inheritance(strategy=InheritanceType.JOINED) in main entity).

If you don't use any, then you need to do carry PK of main entity yourself - again, depends on RDMS engine, you can use RETURNING (Oracle, Postgres) or OUTPUT (SQLServer ) or some form of LAST_INSERT_ID (mysql, and maybe others ) to get value of auto-generated primary key in master table.

Also, as a side note I would recommend to have discriminator column in all tables (for instance, computer_type_id ) to ensure computer can have exactly one sub-type . E.g.

computer : Computer(computer_id, computer_type_id (FK to computer_type), PK (computer_id), UNIQUE(computer_id,computer_type_id)....)

laptop :   Laptop (computer_id, computer_type_id DEFAULT LAPTOP_TYPE_ID,   FK (computer_id, computer_type_id), CHECK (computer_type_id = LAPTOP_TYPE_ID))

Note : Unique constraint on (computer_id,computer_type_id) may see redundant (and it is from pure theoretical point of view), but it's added for a reason - thus you can refer to it in child tables. In some RDMS , for example Oracle, both constraints (PK and UNIQUE) can share the same index , so no overhead from performance point of view...


Field Conversions With Linked Servers I have a postgres database linked to SQL Server.  I have found you can use OpenQuery to query postgres from the Sql Server side, but one caveat I have found is that when creating views all fields are converted to char despite the actual data type from postgres.  For example, if you look at my query below, userid is an int, and userlogindate & userlastlogindate are date fields in postgres but when the view is created in Sql Server if I expand the plus sign beside the view, and expand the plus sign beside columns, all of the columns are listed as char()?

What do I need to do in order for the Sql Server view to keep the same field type as what postgres has them set-up to be?  Cast/Convert each one?

SELECT      *
FROM            OPENQUERY(LinkedServer,
                     'Select
                      userid As "userid"
                      ,userlogin As "userlogin"
                      ,userlogindate As "userlogindate"
                      ,userlastlogindate As "Lastlogindate"
                      From syscatalog')

I found the solution to this.  Below is how I achieved such:

SELECT
CAST(userid As Int) As userid
,CAST(userlogin As varchar(500)) As userlogin
,CAST(userlogindate as date) As userlogindate
,CAST(userlastlogindate as date) As lastlogindate
FROM OPENQUERY(LinkedServer,
                 'Select
                  userid As "userid"
                  ,userlogin As "userlogin"
                  ,userlogindate As "userlogindate"
                  ,userlastlogindate As "Lastlogindate"
                  From syscatalog')


SELF Join Table Relation I have hotel booking system, Where there are Partitions rooms too and they can be merge into one.

Scenario : 3 rooms (A,B and C) can be merge to Make 1 Big (ABC) room.
Validation : When any one of A, b or c is booked, we cannot booked ABC. Similarly, when ABC is booked, we cannot Booked A, B and C.
So, What I have done, is create a new column parent in the table, add new room named ABC and set ABC identity id in column parent of room A, B and C.

What kind of relation would be useful, One to One self reference, or something else?

I would probably add a boolean column to the rooms table indicating "virtual rooms" (room "ABC", for example, would have this set). Then add another table called VirtualRoomMembers, with two columns, both of which are foreign keys back to your Rooms table's primary key (assuming room_id int here):

CREATE TABLE VirtualRoomMembers (
    room_id int NOT NULL,
    sub_room_id int NOT NULL
)

A many-to-many design would account for the possibility that you could have multiple usable groupings for a set of rooms; perhaps you can book ABC, AB, or BC.

Now you insert records in that table to define which rooms make up which virtual rooms, and you can easily take these into account when anti-joining to the reservations to see what's available:

SELECT
    r.room_id FROM Rooms r
    LEFT OUTER JOIN VirtualRoomMembers vrm --Get the sub-rooms
        ON r.room_id = vrm.room_id
    LEFT OUTER JOIN VirtualRoomMembers vrm2 --Get the parent rooms
        ON r.room_id = vrm2.sub_room_id
    LEFT OUTER JOIN Reservations rs
        ON (
            r.room_id = rs.room_id OR
            (r.virtual_room = 1 AND vrm.sub_room_id = rs.room_id) --Make sure none of the child rooms are booked
            OR (r.virtual_room = 0 AND vrm2.room_id = rs.room_id) --Make sure the combined parent room isn't booked
        )
        AND rs... (filter the reservations for the date range in question)
WHERE rs.reservation_id IS NULL


Can I convert a VARCHAR field body to unquoted JSON result in SQL Server 2016 CTP 2.X? I have downloaded and installed SQL Server 2016 Community Technology Preview 2.3, which if I understand correctly, should contain a built-in function called JSON_VALUE. (Update: CTP 2.4 has shipped and no mention is made of new JSON features yet, either)

Starting with the JSON features that DO already work, the "... FOR JSON AUTO" can be specified in my SELECT statement, like this:

SELECT [ID], [DATA] FROM [dbo].[JSONDEMO] FOR JSON AUTO

The result for my demo query above will be a resultset with one column, with some auto-generatedname like JSON_F123123123 and the value in JSON format, something like:

[{"ID":"IDVALUE", "DATA":"{ {name:'ramsoft', id:1234}, ... }", ... }]

So far so good, but if DATA was containing JSON data, you would be receiving a JSON document containing a data string literal, even though that string literal is valid JSON. What you actually probably wanted, or what I wanted, rather, is:

 [{"ID":"IDVALUE", "DATA":{ {name:'ramsoft', id:1234}, ... }, ... }]

According to some MS Blog posts, to get the JSON value as a JSON value instead of as a VARCHAR(n) string you should write:

SELECT [ID], JSON_VALUE(D.DATA,'$') DATA
FROM [dbo].[JSONDEMO] D FOR JSON AUTO

This does not work, I get an error:

'JSON_VALUE' is not a recognized built-in function name.

Is some kind of JSON value extraction available in CTP 2.3, or is this not going to exist until CTP 3?

You'll need to wait for CTP 3 to use things like OPENJSON and JSON_VALUE. These constructs simply didn't ship in the CTPs up to and including CTP 2.4. That doesn't mean there isn't some way to get the result you want right now, but the amount of time and effort you might spend doing so is probably not worth it, especially if the functionality you need will be available in the next CTP, which should ship soon.


Oracle Secure Backup + Amazon S3 / RMAN-06172: no AUTOBACKUP found or specified handle is not a valid copy or piece I have backup in Amazon S3 that was created without AUTOBACKUP settings.
trying to recover parameter file:
RUN
{
  SET DBID 3427966416;
  ALLOCATE CHANNEL ch1 DEVICE TYPE sbt parms 'SBT_LIBRARY=/home/oracle/S3BCKP/lib/libosbws.so ENV=(OSB_WS_PFILE=/home/oracle/S3BCKP/config/s3backup.cfg)';
  RESTORE SPFILE TO PFILE '/home/oracle/initSUPERPROD.ora' FROM AUTOBACKUP MAXDAYS 12;
  SHUTDOWN ABORT;
}

then I get:
channel ch1: no AUTOBACKUP in 7 days found
released channel: ch1
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of restore command at 04/20/2021 09:21:38
RMAN-06172: no AUTOBACKUP found or specified handle is not a valid copy or piece

Backups are in the bucket. How to restore any of them?
upd. downloaded 0000000001 chunk that is relates to controlfile and spfile backuppiece with awscli utility
restore controlfile from '/home/oracle/0000000001';

channel ORA_DISK_1: restoring control file
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of restore command at 04/20/2021 10:28:40
ORA-19870: error while restoring backup piece /home/oracle/0000000001
ORA-19505: failed to identify file "/home/oracle/0000000001"
ORA-27048: skgfifi: file header information is invalid

Did not setup bucket name that was originally used duting backup creation:
echo "OSB_WS_BUCKET=mycompany-oracle-database-bucket" >> /home/oracle/S3BCKP/config/s3backup.cfg


How to select more than 20 rows in SPHINX engine? I use SphinxSE instead of FULLTEXT. Sphinx settings are for end-users of a search engine. According to the official documentation,

LIMIT clause. Both LIMIT N and LIMIT M,N forms are supported. Unlike
in regular SQL (but like in Sphinx API), an implicit LIMIT 0,20 is
present by default.

Therefore, any query returns only 20 rows
MariaDB [sphinx]> SELECT * FROM t1 WHERE query='test;mode=any';
+------+--------+---------------+
| id   | weight | query         |
+------+--------+---------------+
|  556 |      1 | test;mode=any |
|  864 |      1 | test;mode=any |
| 1329 |      1 | test;mode=any |
| 1781 |      1 | test;mode=any |
| 1832 |      1 | test;mode=any |
| 2157 |      1 | test;mode=any |
| 2388 |      1 | test;mode=any |
| 2889 |      1 | test;mode=any |
| 3118 |      1 | test;mode=any |
| 3155 |      1 | test;mode=any |
| 3255 |      1 | test;mode=any |
| 3485 |      1 | test;mode=any |
| 3495 |      1 | test;mode=any |
| 3623 |      1 | test;mode=any |
| 4034 |      1 | test;mode=any |
| 4525 |      1 | test;mode=any |
| 4563 |      1 | test;mode=any |
| 4659 |      1 | test;mode=any |
| 4736 |      1 | test;mode=any |
| 4807 |      1 | test;mode=any |
+------+--------+---------------+
20 rows in set (0.027 sec)

I experimented with various settings in the sphinx.conf, but am unable to change the setting to return more results in a SQL query.

In docs you can include limit=40. So:
SELECT * FROM t1 WHERE query='test;mode=any;limit=40';

Not a usual SQL way, but SphinxSE isn't quite normal.


Shortcut to select all SQL text in Access When working in the SQL view in MS Access, is there a shortcut to select all the text in a SQL query.

For example, most text editors in Windows follow the {Ctrl+ a} combination, but this doesn't seem to have any affect within Access. Is there some other combination?

It is useful when copying text from an outside editor into the Access SQL query creator.

Thanks!

MS-Access 2016

Create a new SQL view, write some text and then press F2.


Need help on expdp and impdp concept I have a multiple databases i.e (database1,database2) and i need to export a table from database1 to database2 . Here are the commands i tried

expdp hr/oracle@oracle tables=emp directory = ora dumpfile=emp.dmp

This command will export the the emp.dmp file to "ora" directory but i need to import this dump file to another database but the "ora" directory is not accessible to the database2 . Do we have any other method to export the database dump .

So? Just copy a dumpfile to a location accessible to database2.

ora is not a directory, it is a directory object defined in the database, that serves as an "alias" to the real path in the filesystem.

Overview of Oracle Data Pump

File Allocation

  Data Pump is server-based rather than client-based, dump files, log files, and SQL files are accessed relative to server-based directory paths. Data Pump requires that directory paths be specified as directory objects. A directory object maps a name to a directory path on the file system.

  The following example shows a SQL statement that creates a directory
  object named dpump_dir1 that is mapped to a directory located at
  /usr/apps/datafiles.

SQL> CREATE DIRECTORY dpump_dir1 AS '/usr/apps/datafiles';

Or you can import the table directly from database1 to database2 through a database link.

Using Network Link Import to Move Data

  When the Import NETWORK_LINK parameter is used to specify a network
  link for an import operation, SQL is directly used to move the data
  using an INSERT SELECT statement. The SELECT clause retrieves the data
  from the remote database over the network link. The INSERT clause uses
  SQL to insert the data into the target database. There are no dump
  files involved.


Change file types from rows data to log I am looking for small advice. My database has 12 log files and x_log11 is showing file type as Rows data instead of Log and file group as primary. I am using SQL 2014.
I need to change it from rows data to log as it is log file?
will there be any impact if I will keep like this.
If I am trying to shrink(for other purpose) log files x_log11 is showing in data files.
will this settings impact on file growth of log files?

A Rows file stores data and is where your data and meta-data lives.
A Log file stores changes to that data.

You can't change one to the other.


Index physical stats DMV - Object ID/table not found I have installed SQL Server 2012 RC0 on my laptop recently and I was trying a demo which illustrates how shrink database will cause fragmentation. I created a table, a clustered index on the identity column and inserted a few records and then issued the below to check fragmentation:

select avg_fragmentation_in_percent from sys.dm_db_index_physical_stats(DB_ID('databasename'),
  OBJECT_ID('tablename'), 1, NULL, 'limited')

This is the error message I get:

Could not find table or object ID 2139154666. Check system catalog.

I did a select * from sysobjects and the Object exists with that objectID. Why does SQL Server think that it doesn't exist? What am I doing wrong?

You are in the wrong database context.

Try:

select avg_fragmentation_in_percent
from databasename.sys.dm_db_index_physical_stats(DB_ID('databasename'), OBJECT_ID('tablename'), 1, NULL, 'limited')

Alternatively you can just throw a

USE Databasename

on the first line.

I'm guessing that it's executing in the context of master or something similar, which won't have the OBJECTID you are looking for.


SQL relationship for nested groups I'm working on a creating a database schema for a legacy app that currently saves its data to XML files.

I'm having trouble representing this relationship:

Task Group 1 contains
|-- Simple Task 1
|-- Simple task 2
|-- Task Group 1a contains
    |-- Simple Task 3
    |-- Simple Task 4
    |-- Task Group 1b contains
        |--Simple task 5

Basically, a task group may contain one or more tasks.  These may be simple tasks or other task groups.   So the nested groups is what I'm having trouble with.

This is what I have so far:

CREATE TABLE `task_group` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(45) DEFAULT NULL,
  `status` varchar(45) DEFAULT NULL,
  `processing_type` varchar(45) DEFAULT NULL,
  `notes` varchar(255) DEFAULT NULL,
  `parent_id` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `fk_parent_idx` (`parent_id`),
  CONSTRAINT `fk_parent` FOREIGN KEY (`parent_id`) REFERENCES `task_group` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB DEFAULT CHARSET=utf8

It may help to separate the hierarchy from the task-like objects, and implement them as inherited types. There would be four tables.
TaskTrees
  ParentTaskId   references Tasks.TaskId
  ChildTaskId    references Tasks.TaskId

Tasks
  TaskId
  TaskType      simple or group

GroupTasks
  TaskId   references Tasks.TaskId
  <group specific columns>

SimpleTasks
  TaskId   references Tasks.TaskId
  <other specific columns>

This way the Trees become simple hierarchies of "things" and don't much care what those things are. There are other ways to implement trees than this.
The domain-specific information is held in GroupTasks and SimpleTasks. This is where the values required to implement each is held. There are other ways to implement inheritance. I'm not sufficiently versed in MySQL to recommend one over another.
Integrity between the tables must be maintained through the application. Ensuring a simple task does not have children, for example, is not guaranteed through the data model alone.


Use Stored Procedure or View to return Data from Multiple Tables Is it just down to preference for whether to use a View or a stored procedure (or TVF) to simply return data joined from multiple base tables?

Is there any best practice regarding this? Should a stored procedure be used just for manipulating data? And is it best to use a view for the purpose of returning data from multiple tables?

Are there also likely to be any performance issues?

Query:

SELECT
    t.TagID, t.TagName,
    t.IsActive AS 'IsTagActive',
    tk.TaskID, tk.TaskName,
    tk.IsActive AS 'IsTaskActive',
    g.GroupID, g.GroupName
FROM
    Model.[Tag] t
INNER JOIN
    Model.[TagTaskMapping] ttm ON ttm.TagID = t.TagID
INNER JOIN
    Model.[Task] tk ON tk.TaskID = ttm.TaskID
INNER JOIN
    Model.[Group] g ON g.GroupID = t.GroupID

This seems like a strange question to me, but here are some opinionated brief one liner guidelines for each.

stored procedure: If you want use parameters and are not joining to the results.
in-line table-valued function: If you want use parameters and might join the results.
views: If you want to simplify querying a set of tables, and promise not to make views that include other views.

What you have posted looks like it would be a view to me. Unless you want to pass in parameters, then I would say it would probably be a stored procedure.


What can cause delay in SELECT statement execution and high ASYNC_NETWORK_IO even with "disregard results after execution" We are facing performance problems on our SQL Servers.
7 servers, all running the same application, all have the same problem.
SELECT @@VERSION
Microsoft SQL Server 2017 (RTM-CU19) (KB4535007) - 14.0.3281.6 (X64)   Jan 23 2020 21:00:04   Copyright (C) 2017 Microsoft Corporation  Standard Edition (64-bit) on Windows Server 2019 Standard 10.0 <X64> (Build 17763: ) (Hypervisor)

When you look at the acummulated total server wait statistics the show 99% ASYNC_NETWORK_IO. We first assumed that this was caused by the application. However:
When we take query (captured from the application) and run it as a test from within SSMS with disregard results after execution. It takes about 6 seconds when we run it over a TCP connection and 50 seconds over a Named pipes connection. We get very slow results.
(without disregard results after execution the query gives a result of 57k rows 227 columns and a size of 221MB.)
It's core edition so we can't use a local connection. We tested connecting remotely with both TCP and Named pipes.
over TCP
SQL Server parse and compile time:
   CPU time = 0 ms, elapsed time = 0 ms.

 SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 0 ms.
SQL Server parse and compile time:
   CPU time = 4 ms, elapsed time = 4 ms.

(57844 rows affected)
Table 'STORE-CUW01$Item'. Scan count 1, logical reads 14774, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

(1 row affected)

 SQL Server Execution Times:
   CPU time = 1359 ms,  elapsed time = 6868 ms.
SQL Server parse and compile time:
   CPU time = 0 ms, elapsed time = 0 ms.

 SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 0 ms.

So even though though it only takes 0.62 seconds for the clustered index scan to complete, it still takes 6.8 seconds to complete the query even though disregards results after execution is checked
Over Named Pipes
SQL Server parse and compile time:
   CPU time = 0 ms, elapsed time = 0 ms.

 SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 0 ms.
SQL Server parse and compile time:
   CPU time = 0 ms, elapsed time = 0 ms.

(57844 rows affected)
Table 'STORE-CUW01$Item'. Scan count 1, logical reads 14774, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

(1 row affected)

 SQL Server Execution Times:
   CPU time = 1578 ms,  elapsed time = 54202 ms.
SQL Server parse and compile time:
   CPU time = 0 ms, elapsed time = 0 ms.

 SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 0 ms.

With Named pipes it's even worse. Again it only takes half a second for the clustered index scan to complete and it takes 54 seconds for the query to complete.
If I restore the database to my laptop and run the same query I get 0.4 seconds for the clustered index scan and 1375ms CPU time, 1462ms elapsed time.
by using https://www.sqlskills.com/blogs/paul/capturing-wait-stats-for-a-single-operation/
I collected the wait stats for both the test with a TCP connection and Named Pipe connection:
TCP

Named Pipes

The wait's don't add up tot the total elapsed time. Only 0.472 seconds waittime and 6.8 elapsed time on the tcp test and only 40.7 Waittime vs 54.2 elapsed time on the Naped Pipe test.
further relevant info

The SQL Servers are virtual servers VMware
It's windows core edition
4 cores
64GB ram, only 16 GB assigned to SQL Server
The application service is running on the same server as SQL Server
The application itself connects with MARS Sessions

We don't understand where the time delay is coming from. Shouldn't the query complete much faster if you have disgard results checked?

Results are still returned to the client with the discard results option. The large ASYNC_NETWORK_IO wait time with discard results suggests network latency returning 221MB of data. I suggest you focus your troubleshooting on network rather than query plans.
Try a simple test by copying an 200MB file from the SQL machine to the client to see if you get similar times.


DBCC CHECKDB Data Purity errors but no rows found Any idea why the value -96.00 is out of range for the normalization_data column (defined as numeric(14,2) )?

DBCC CHECKDB with DATA_PURITY flags this row:

Msg 2570, Level 16, State 3, Line 1
Page (1:807256), slot 0 in object ID 379148396, index ID 1, partition ID 72057594093830144, alloc unit ID 72057594099269632 (type "In-row data"). Column "normalization_data" value is out of range for data type "numeric".  Update column to a legal value.

Page info for slot 0:

PAGE: (1:807256)

BUFFER:

BUF @0x0B826044

bpage = 0x607FC000                   bhash = 0x00000000                   bpageno = (1:807256)
bdbid = 10                           breferences = 0                      bUse1 = 45874
bstat = 0x3c00009                    blog = 0x9ca22159                    bnext = 0x00000000

PAGE HEADER:

Page @0x607FC000

m_pageId = (1:807256)                m_headerVersion = 1                  m_type = 1
m_typeFlagBits = 0x0                 m_level = 0                          m_flagBits = 0x0
m_objId (AllocUnitId.idObj) = 936    m_indexId (AllocUnitId.idInd) = 256
Metadata: AllocUnitId = 72057594099269632
Metadata: PartitionId = 72057594093830144                                 Metadata: IndexId = 1
Metadata: ObjectId = 379148396       m_prevPage = (0:0)                   m_nextPage = (1:807257)
pminlen = 16                         m_slotCnt = 129                      m_freeCnt = 768
m_freeData = 7166                    m_reservedCnt = 0                    m_lsn = (19457:12767:78)
m_xactReserved = 0                   m_xdesId = (0:0)                     m_ghostRecCnt = 0
m_tornBits = 323122309

Allocation Status

GAM (1:511232) = ALLOCATED           SGAM (1:511233) = NOT ALLOCATED
PFS (1:800712) = 0x40 ALLOCATED   0_PCT_FULL                              DIFF (1:511238) = NOT CHANGED
ML (1:511239) = NOT MIN_LOGGED

Slot 0 Offset 0x60 Length 54

Record Type = PRIMARY_RECORD         Record Attributes =  NULL_BITMAP VARIABLE_COLUMNS

Memory Dump @0x69A5C060

00000000:   30001000 53595302 80250000 00000000 †0...SYS..%......
00000010:   0500e003 0024002b 00360041 43482046 †.....$.+.6.ACH F
00000020:   4f4f4453 30342d32 30303534 36303030 †OODS04-200546000
00000030:   20303233 3130†††††††††††††††††††††††† 02310

Slot 0 Column 0 Offset 0x4 Length 3

company_id = SYS

Slot 0 Column 1 Offset 0x1b Length 9

normalization_set_id = ACH FOODS

Slot 0 Column 2 Offset 0x24 Length 7

time_period_id = 04-2005

Slot 0 Column 3 Offset 0x2b Length 11

code = 46000 02310

Slot 0 Column 4 Offset 0x7 Length 9

normalization_data = -96.00

I suspect that =96.00 is not the actual value in the row. If you look at the memory dump section above, the memory dump stops at the previous column.

Can you post the output from the following (replacing Database Name with the name of the database)?

DBCC Traceon(3604);

DBCC Page ('Database Name', 1, 807256, 3);


fn_LShortNameToLCID in SQL Database My question sound stupid but i didn't understand and searching on google didn't helped either.

I have a SQL query which i use in SCCM Reporting.
I just want to say that my hands are bit tight in SQL and database related stuffs.
Below is the SQL query.

   SELECT top 3 *
 FROM fn_appdtclientsummarizedstate(dbo.fn_LShortNameToLCID(1033)) acs

This is, i get from existing report (SSRS).

I checked the SCCM database but this table, views is not there. Hence i can't make the modifications as needed.

If you can help me then i would be very grateful.

I spent almost 2 days but no luck finding/understanding this query.

Maybe you should install SQL Search

I think you will find it as a 'Table-valued function'  (a function that returns a table )


Employee Shift Check - Nested Cursor I have a query that returns the availability of an employee (only days they are available):

SELECT
    emp_id,
    emp_number,
    area,
    day_date,
    shift_type,
    start_time,
    finish_time
FROM EmployeeAvailability

I also have a query that gives me all available working days between two dates, which I have written as a CTE:

WITH AvailableDates

AS (SELECT DATETIMEFROMPARTS(DATEPART(YEAR,AvailableDate)
                        ,DATEPART(MM,AvailableDate)
                        ,DATEPART(DAY,AvailableDate)
                        ,0,0,0,0) as AvailableDate
FROM (
SELECT  TOP(DATEDIFF(DAY, '2019-01-14 00:00:00.000', '2019-01-21 00:00:00.000') + 1)
        AvailableDate = DATEADD(DAY, ROW_NUMBER() OVER(
            ORDER BY a.object_id) - 1,'2019-01-14 00:00:00.000')
FROM    sys.all_objects a
        CROSS JOIN sys.all_objects b) AvailDates

)

I need to check the employee availability against the dates available and create an unavailable record for the days they don't have a scheduled shift. However when I join on those dates I am getting the following.

My thoughts are to write a nested cursor to check date and employee but is there a better way to get emp_id, emp_Number, area and availableDate to create a record?

Thanks in advance

So you want one row for each (employee,date), with shift details filled in for days on which the employee has a shift?

For this kind of problem use the "cross join, left join" pattern.  Start with a cross join that establishes the granularity of the result set, and then left join to the matching rows.  Something like:

SELECT
    e.emp_id,
    e.emp_number,
    a.day_date,
    ea.area,
    ea.shift_type,
    ea.start_time,
    ea.finish_time
FROM Employees e
CROSS JOIN AvailableDates a
LEFT JOIN EmployeeAvailability ea
   on a.AvailableDate = ea.day_date
   and e.emp_id = ea.emp_id


is it possible to use utl_file_dir = '*' scope = spfile in 12c Oracle 12c has deprecated  utl_file_dir = '*' scope = spfile,
but I need to use this functionality. Is there any option to do it?

Is it possible to give permission using Java classes?

Deprecated does not mean it can not be used, so yes, you can use it:

[oracle@o71 ~]$ sqlplus / as sysdba

SQL*Plus: Release 12.2.0.1.0 Production on Wed Sep 26 15:29:05 2018

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

Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

SQL> alter system set utl_file_dir='*' scope=spfile;

System altered.

SQL>

But keep in mind, it is desupported starting with 18c: Desupport of UTL_FILE_DIR Initialization Parameter, the parameter does not exist anymore.

[oracle@o73 ~]$ sqlplus / as sysdba

SQL*Plus: Release 18.0.0.0.0 - Production on Wed Sep 26 15:28:50 2018
Version 18.3.0.0.0

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

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

SQL> alter system set utl_file_dir='*' scope=spfile;
alter system set utl_file_dir='*' scope=spfile
                                  *
ERROR at line 1:
ORA-02065: illegal option for ALTER SYSTEM

12.2 example:

[oracle@o71 ~]$ sqlplus / as sysdba

SQL*Plus: Release 12.2.0.1.0 Production on Thu Sep 27 11:01:50 2018

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

Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

SQL> show parameter utl_file_dir

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
utl_file_dir                         string      *

SQL> !ls -l /home/oracle/hello.txt
ls: cannot access /home/oracle/hello.txt: No such file or directory
SQL> DECLARE
  L_HANDLER UTL_FILE.FILE_TYPE;
BEGIN
  L_HANDLER := UTL_FILE.FOPEN('/home/oracle', 'hello.txt', 'W');
  UTL_FILE.PUTF(L_HANDLER, 'Hello World!');
  UTL_FILE.FCLOSE(L_HANDLER);
END;
/  2    3    4    5    6    7    8

PL/SQL procedure successfully completed.

SQL> !cat /home/oracle/hello.txt
Hello World!


MDF Backup: "Login failed" Error I'm creating a VC# application using Visual Studio.
On startup, it opens a MDF file (SQL Server Express database).

When I execute

BACKUP DATABASE db.mdf TO DISK=backup.mdf

I get the backup.mdf created.

Then I do the following:

Close the application
Delete the db.mdf
Rename backup.mdf to db.mdf
Run the application

I get the error "Login failed for user 'PC\User'"

How can I solve this?

Backups in SQL Server don't work that way, they aren't just copies of the database files in another location but instead they are backups of the database content stored in a backup file with a different structure than the database files (you could make a backup of just a database file but even in that case the backup format would be different).

To perform a backup the most basic way is doing something like this:

BACKUP DATABASE DBName TO DISK='C:\Backup directory\DB_backup.bak' WITH FORMAT

The backup files can't be used directly by SQL Server, you need to restore it on a database first (either overwriting an existing database or creating a new one):

RESTORE DATABASE DBName FROM DISK='C:\Backup directory\DB_backup.bak' WITH REPLACE

You could need additional backup/restore parameters depending of the DB properties and the exact operation you are doing (also you can perform and restore backups from SQL Server Management Studio)


To Change the Log file location of SQL Server 2014 in AlwaysOn I did not find a way how to change Always On Log file location.

AlwaysOn Logs are saved in

  C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\Log

Even in configuration is set like this:

SQLBACKUPDIR="F:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\Backup"

; Default directory for the Database Engine user databases.

SQLUSERDBDIR="D:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\Data"

; Default directory for the Database Engine user database logs.

SQLUSERDBLOGDIR="E:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\Data"

; Directory for Database Engine TempDB files.

SQLTEMPDBDIR="E:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\Data"

We had a problem now that log file was 40 GB and filled drive C and server crashed.
This is only for Always on logs, all other logs are where they should be.
Databases and everything is ok. Just log files of AO.

Any ideas how to change AlwaysOn Log file location?

Thanks

The Always on logs are written in the same location as the errorlog.

By moving the errorlog you can change this location. You will need to change the service startup parameter by opening the SQL Server configuration manager and changing the -e parameter and restarting the service.


How to prove "A->B,C then A->B and A->C" from Armstrong Axioms I know that this is true but I don't understand how to prove it.

The Problem:

  Prove or disprove that if A->B,C, then  A->B and A->C

This answer is similar but it does not prove the relation using Armstrong's axioms.

Just two steps: you can use reflexivity for B C -> B and then transitivity from A -> B C and the previous dependency. Analogous solution for the second case.


Permissions to see and script objects, but not alter them? I am a software developer.

I have a production database that I need full ddl read access to, but not any ddl write or execute access.

Is there a script I can send my DBA that will grant me full ddl read access without allowing me to affect the database?

You will need VIEW DEFINITION on the database and that will give you access to the DDL.

USE <database>;
GRANT VIEW DEFINITION TO <user>;


SequelizeDatabaseError While Upserting, Wrong Table Being Referenced I'm selecting from a table, and the query runs fine. It even shows up on the client side without a hitch.

`
SELECT * FROM public."roleAllocation"
`

However, when I run an upsert function from a query that pulls data from other tables in the db, it throws a Sequelize error.

 SequelizeDatabaseError: relation "roleAllocations" does not exist

I've tried to find any spelling errors, no luck. From what I've found online it's said to be a trigger in the database that might be changing the name of the table. There seems to be no triggers in the database.

PostgreSQL@9.5.13

So from further searching I was able to solve this issue by adding a...

freezeTableName: true

value to the model file for the table.


Error trying to open database The database is for ms exchange. I am getting the following error. Any ideas guys?

This could be due to one of these issues:

A DNS problem: Your workstation does not ping the IP address associated with this host. resolution: Try to ping the LRSERVER. If it does not ping properly, add the LRSERVER ip address in your host file.
A firewall issue: the network traffic between your workstation and the SQL Server instance goes through a firewall which blocks the ports required by SQL Server. resolution: Ask the firewall administrator to open the required ports.


Are there mysql client environment variables for USER and DB on Linux? I see that http://dev.mysql.com/doc/refman/5.7/en/environment-variables.html has an option for setting the USER on Windows, but is it possible on Linux? You can do it via the MYSQL_TEST_LOGIN_FILE, but it's a bit convoluted, and requires setting up files with just the right permissions and one for every user you want to have access. Also, is there an environment variable for what database to start using?

Not that I know of, but you can make a file called my.cnf in a directory that you specify with the MYSQL_HOME environment variable. For example say you have ~/mysql_home/db/my.cnf:

[mysql]
user = username
password= ****
host = hostname
db = databasename

Then you can call the client like

MYSQL_HOME=~/mysql_home/db/ mysql

and it will use those options. Please keep in mind that your password will be readable by whomever can read that file and it isn't encrypted!


How can I efficiently delete records when a counter reaches zero in PostgreSQL? Consider a record with a counter field, which is to be decremented. When its value reaches zero (which is the common case), I want the record to be deleted. What is the most efficient way to do this in PostgreSQL?
The naive way involves two SQL statements and two searches in the table: a SELECT to fetch the counter value, followed by a DELETE or an UPDATE.
One alternative involves an UPDATE … RETURNING, followed by a DELETE only if the returned value of the counter is zero.  However, this performs an unneeded record change in the case of a counter having a value of one, optimizing the uncommon case (counter has a value higher than one) at the expense of the expected common case.
Another alternative, which indeed optimizes the expected common case, involves a DELETE … WHERE … AND counter = 1, followed by an UPDATE when no deletion takes place.
Both alternatives may require a wasteful second search for the record in the table.
Can perhaps the two table searches be always avoided and the operation's efficiency increased by using a cursor? I haven't seen an example for this use case in the PostgreSQL documentation.

Targeting a single row, this avoids an "unneeded record change", i.e. writing a new row version without need:
DO
$$
BEGIN
   DELETE FROM tbl WHERE … AND counter = 1;  -- common case first!

   IF NOT FOUND THEN
      UPDATE tbl SET counter = counter - 1 WHERE …;
   END IF;
END
$$;

Should also be cheaper than a trigger solution, where a trigger function is executed for every affected row (and may or may not interfere).
However, this will not fly with possible concurrent write operations. When two or more transactions try the same at virtually the same time, the logic can break. Or additional rows could become visible in between the two commands in default READ COMMITTED transaction isolation.
This is inherent to the problem itself, rather than to my solution. (Applies to other solutions all the same.) Under concurrent write load, you'll have to at least write-lock the row to avoid race conditions, so you are back to finding the row twice. You can avoid writing rows without need (like UPDATE + DELETE) in any case, though.


How to get number of active users with one SQL-Query? I am running a site similar to craigslist where users can create classifieds and write/reply messages. In order to get an idea of activity I would like to get the amount of active users per month.

The idea is to define an active user as one who has either registered, created/edited a classified or written a message to another user within the last 30 days.

There are 2 tables:

classifieds

advertiser_id | date_created | date_edited

messages

sender_user_id | recipient_user_id | timestamp | status

I am looking for an output like this:

year; month; n_active_users; n_users_created_classified; n_user_sent_msg; n_user_read_msg

A message is marked as read by status=4

The ids are all referring to the user table. So the same sender_user_id and advertiser_id indicate the same user.

I am not sure if this is even possible with just one query but I guess it is, I just don't know how.

From another post I have learned that this could be pointing into the right direction but I am still stuck:

SELECT
   EXTRACT(YEAR FROM date_created) AS yr,   -- for each year
   EXTRACT(MONTH FROM date_created) AS mth, -- & month combination
   count(*) AS advertiser_id
FROM
   classifieds c
GROUP BY yr, mth
ORDER BY yr DESC , mth DESC

I am using MySQL Galera 5.6.27-1trusty-log - (Ubuntu), wsrep_25.12

I don't know how to translate your tables into any of n_active_users; n_users_created_classified; n_user_sent_msg; n_user_read_msg.  But here are some clues:

A simple query for the immediately preceding 30-ish days:

SELECT COUNT(DISTINCT user_id)
    FROM tbl
    WHERE timestamp > NOW() - INTERVAL 1 MONTH;

(Or maybe you would want - INTERVAL 30 DAY.)

This might work for n_user_read_msg:

SELECT COUNT(DISTINCT recipient_user_id) AS n_user_read_msg
    FROM messages
    WHERE timestamp > NOW() - INTERVAL 1 MONTH;
      AND status = 4;

If you want the last thing for each month, then do:

SELECT LEFT(timestamp, 7) AS yyyy_mm,
       COUNT(DISTINCT recipient_user_id) AS n_user_read_msg
    FROM messages
    WHERE status = 4
    GROUP BY LEFT(timestamp, 7);


Database Table Design I would like to ask couple of questions regarding my schema table design.

I have the following data coming from an external database where I do not have any control over it.

PROD_NO  PROD_NAME         COMPANY_ID   COMPANY_NAME    PROD_MARK_NO    PROD_CATALOGUE  PROD_TYPE   PROUDCT_EXPIRY_DATE
MXTP     PLANT_FERTILIZER   BRZL-98     XYZ CO.         MX-FRT          FERT-1          PERISH-01   30-09-2018
RXLG     FARM_FERTILIZER    BRZL-102    ABC CO.         RX-FRT          FERT-6          PERISH-06   31-05-2016

User would like to verify the product information and add two more fields

Verify_date
Comments

How could I normalize the data I receive and put into relational tables?

PROD_NO, COMPANY_ID, PROD_MARK_NO,PROD_CATALOGUE and PROD_TYPE will be always unique.

This schema would make sense to me:

tblCompany
(
  Company_ID  PK
  Company_Name
)

tblProduct
(
  Prod_No  PK
  Company_ID  FK_tblCompany_CompanyID
  Prod_Name
  Prod_Mark_No
  Prod_Catalogue
  Prod_Type
  Prod_Expiry_Date
  Prod_Verify_Date
  Prod_Comments
)

You could have separate tables for product type and product catalogue to get to 3NF but that's a decision for you to make depending on the amount of different values those columns could have and the way in which this data will be accessed (OLTP only or heavy reporting used here too?).


How to decide the parameter processes count? I am using oracle 11g database and application deployed on weblogic 12.2.1 while I tried to access my application today, I faced an issue  as "Listener refused the connection with the following error:

  ORA-12516, TNS:listener could not find available handler with matching protocol stack".

Verified the process parameter limit which was almost near the max limit so I altered the process value and increment it to 500 using the below alter command:

alter system set processes=500 scope=spfile;

Now, everything is fine. But I provided the process count on a vague idea.

My question is:

How do I decide the processes count. Is that based on my user count?
Do oracle have any calculation method for this or any other dependency?

Yes, it depends on your user count. But not only that, it also depends on which way your users connect to the database and the number of background processes your instance requires.

There are basically two types of server processes that services user processes (client processes): dedicated server process, where there is an one-to-one mapping between user process and server process, and shared server process, where, like the name suggests, a server process can be shared between two or more user processes.

If your application (client) connects directly to the Oracle database, it will spawn a dedicated server process to handle its requests. If your application connects to a dispatcher, then you are using one of many shared processes to handle your requests.

Basically, if you use shared server connections you can service many users with fewer processes, but you may have a performance penalty. More information about dedicated and shared server processes can be found at: https://docs.oracle.com/cd/E11882_01/server.112/e25494/manproc.htm#ADMIN11166

Also, please beware that what prevents you from connecting to an instance is reaching the SESSION limit, which is a derived metric from the number of processes:

sessions =  (1.5 * PROCESSES) + 22

Since every login requires a session, it's the sessions parameter that actually determines the number of concurrent users on the instance. Please refer to the following url for more details: https://docs.oracle.com/cd/E18283_01/server.112/e17110/initparams229.htm


How can I cancel index creation in SQL Server How can I cancel an index creation?

It is still running and will run for a long time, because by accident I have created a clustered index; and non clustered indices are recreating on a really huge table.

One way is to find the process/session Id of the query and kill it. You can learn more here. This might be about the only way if you don't know who or what actually started the index creation.

The following query should show you the session Id of the Insert query:

    SELECT
    r.session_id as SessionID,
    r.start_time as StartTime,
    r.[status] as Status,
    r.wait_type as WaitType,
    r.blocking_session_id as BlockedBySessionID,
    sessions.login_name as BlockedByUser,
    SUBSTRING(qt.[text],r.statement_start_offset / 2,
        (CASE
            WHEN r.statement_end_offset = -1
            THEN LEN(CONVERT(NVARCHAR(MAX), qt.[text])) * 2
            ELSE r.statement_end_offset
            END - r.statement_start_offset) / 2) AS SQLStatement,
    DB_NAME(qt.[dbid]) AS DatabaseName,
    r.cpu_time as CPUTime,
    r.total_elapsed_time as TotalElapsedTime,
    Round(r.total_elapsed_time / 1000.0 / 60.0,1) as TotalElapsedTimeInMinutes,
    r.reads as Reads,
    r.writes as Write,
    r.logical_reads as LogicalReads
FROM sys.dm_exec_requests AS r
    OUTER APPLY sys.dm_exec_sql_text(sql_handle) AS qt
    LEFT OUTER JOIN sys.dm_exec_sessions sessions ON sessions.session_id = r.blocking_session_id
WHERE r.session_id > 50 -- This eliminates system requests
ORDER BY r.start_time


What causes lost updates being observed in my SQL Azure database? I have a SQL Azure database with the following tables:

CREATE TABLE [dbo].[UserBalances]
  (
     [UserId]         UNIQUEIDENTIFIER UNIQUE NOT NULL DEFAULT NEWID(),
     [AvailableMoney] INT NOT NULL DEFAULT(0)
  );

GO

CREATE UNIQUE CLUSTERED INDEX [UserBalancesIndex]
  ON [dbo].[UserBalances]([UserId] ASC);

GO

CREATE TABLE [dbo].[UserBalanceChanges]
  (
     [EntryId]      UNIQUEIDENTIFIER NOT NULL DEFAULT NEWID(),
     [UserId]       UNIQUEIDENTIFIER NOT NULL,
     [PriorBalance] INT NOT NULL,
     [NewBalance]   INT NOT NULL,
     [Time]         DATETIME DEFAULT(GETUTCDATE())
  )

GO

CREATE CLUSTERED INDEX UserBalanceChangesIndex
  ON [UserBalanceChanges]( EntryId )

GO

and the following code being concurrently executed via several connections the database:

CREATE PROCEDURE [dbo].[usp_ChangeUserBalance] @userId UNIQUEIDENTIFIER,
                                               @change INT
AS
    BEGIN TRANSACTION

    DECLARE @priorBalance INT;
    DECLARE @newBalance INT;
    DECLARE @updateTime DATETIME;

    UPDATE dbo.UserBalances
    SET    @updateTime = GETUTCDATE(),
           @priorBalance = AvailableMoney,
           @newBalance = AvailableMoney = AvailableMoney + @change
    WHERE  UserId = @userId;

    INSERT INTO UserBalanceChanges
                (UserId,
                 PriorBalance,
                 NewBalance,
                 Time)
    VALUES     ( @userId,
                 @priorBalance,
                 @newBalance,
                 @updateTime );

    COMMIT TRANSACTION

    RETURN 0

and then I run the following query:

SELECT TOP(1000) PriorBalance,
                 NewBalance
FROM   UserBalanceChanges
WHERE  UserId = SomeSpecificId
ORDER  BY Time DESC

and I often see something like this:

1000 995
1005 1000 <<identical
1005 1000 <<changes
1010 1005
1015 1010

which looks like the two concurrently running updates read the same initial value and then one update is effectively lost.

Lost updates are a known anomaly but lost updates are impossible in SQL Server and presumably in SQL Azure too (at least with a single "SELECT from UPDATE" statement that I have.

Yet it looks like I actually see lost updates.

Why do I observe "impossible" lost updates?

The problem was because of "other interpretation" lost update - when data is first read into local memory and then the table is updated with that data. There was another code that was intended for resetting balances of some users very rarely. It was something like this:

DECLARE @BalancesToReset TABLE
(
   UserId uniqueidentifier,
   PriorBalance int,
   NewBalance int
)

INSERT INTO @BalancesToReset( UserId, PriorBalance, NewBalance )
    SELECT UserId, AvailableMoney, dbo.ufn_ComputeNewBalance(some params)
    FROM UserBalances WHERE SomeIncorrectConditionHere

UPDATE UserBalances SET
    AvailableMoney = BR.NewBalance FROM
       @BalancesToReset BR INNER JOIN UserBalances
       ON UserBalances.UserId = BR.UserId;

 INSERT INTO UserBalanceChanges( PriorBalance, NewBalance )
    SELECT PriorBalance, NewBalance
    FROM @BalancesToReset WHERE NewBalance < PriorBalance;

and this code would run concurrently with the code in question. The code was intended to INSERT from SELECT only a small fraction of user balances and only occasionally but because of an incorrect condition in the SELECT part it would select a huge portion of balances and very often. The balances that were unintentionally selected had NewBalance equal to PriorBalance and so the final INSERT from SELECT would not log the changes.

The UPDATE in the middle of this code would forcibly reset the AvailableMoney for many users to a precomputed value and this would introduce a "second interpretation" lost update which is possible in SQL Server.


How to select a particular element from json array I have a 'notifications' table with a column 'dest_user_id_arr' as json with the following value:

id                     dest_user_id_arr
1                      {"users":[83,84,85]}
2                      {"users":[89,83,92]}
3                      {"users":[87,88,83]}

I would like to select those id where user is 83.

IF column is json:

 SELECT id
  FROM notifications n
  WHERE json_array_elements(n.dest_user_id_arr->'users') = 83;

If column is jsonb:

 SELECT id
  FROM notifications n
  WHERE jsonb_array_elements(n.dest_user_id_arr->'users') = 83;


Rows to Columns (SQL Server) I have a table that has two columns, one with the type of posting and another column with the value.

Source table:

+-----+-------+
|Type | Value |
+-----+-------+
|  C  | 381.22|
|  D  | 25.49 |
|  C  | 25.49 |
|  D  | 25.48 |
|  C  | 705.56|
|  D  | 80.00 |
+-----+-------+

I need to create a view that separates types C and D, and list their values.

I need it to look like this:

+------+-----+
|Credit|Debit|
+------+-----+
|381.22|25.49|
|25.49 |25.48|
|705.56|80.00|
+------+-----+

I tried to create two separate virtual columns with SELECTS, just to list the values for the respective types.

SELECT
    (SELECT [value] FROM [table] WHERE [type] IN ('D')) AS Debit,
    (SELECT [value] FROM [table] WHERE [type] IN ('C')) AS Credit

And the error occurs because both SELECTS return more than one result:

Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

Also try to use the PIVOT statement, but still unsuccessful.

Could you give me a suggestion to solve this problem?

There are a couple ways that you can get the final result that you want. You can use PIVOT or you can also use an aggregate function along with a CASE expression, but in order to return multiple rows for each Type you'll need to create a unique value that you can GROUP BY.

I'd recommend using a windowing function like row_number to first generate a unique value for each row in your table partitioned by the TYPE.  You'd write this similar to:

select
  [Type],
  [Value],
  rn = row_number() over(partition by [Type] order by (select 1))
from yourtable;

This creates a unique value for each row in your table by Type aka Credit orDebit`. This is needed when you aggregate your data so you will return more than one row.

Once you've created this row number, you can convert your data into columns by using either the PIVOT function or CASE with aggregate.

select
  Credit = max(case when [Type] = 'C' then [Value] end),
  Debit = max(case when [Type] = 'D' then [Value] end)
from
(
  select
    [Type],
    [Value],
    rn = row_number() over(partition by [Type] order by (select 1))
  from yourtable
) d
group by rn;

Or:

select
  Credit = C,
  Debit = D
from
(
  select
    [Type],
    [Value],
    rn = row_number() over(partition by [Type] order by (select 1))
  from yourtable
) d
pivot
(
  max(value)
  for [Type] in (C, D)
) p;

Here is a demo. Both of these return the result:

| Credit | Debit |
|--------|-------|
| 381.22 |    80 |
|  25.49 | 25.48 |
| 705.56 | 25.49 |

If you need the Credit or Debit data to appear specifically in certain rows, than you would need to provide some additional data to tie each Credit or Debit together. These versions just convert the rows into columns of data.


Loging queries and their error Text I need to find out the statements that are unsuccessfully executed in sql, along with their error trace. As of now I created a trigger which is triggered on server error, which stores the sql text and the SQLERRM in a table.

Is that the right way to do? Is there a more efficient way? I searched about AWR and Auditing. But they dont seem to serve this purpose.

I use Oracle SQL by the way.

This is a common case where autonomous transactions would be helpful. In a situation where you have a transaction the might or not fail, you'll want to log the error, but as an independent transaction, without affecting the main one.

Example from Tom Kyte:

create or replace procedure log_error( ..... )
as
   pragma autonomous_transaction;
begin
   insert into logging_table (...) values (...);
   commit;
end;

and then in your plsql code you can:

begin
    .... your code
exception
    when others then
           log_error( .... );
           RAISE;  /* this is crucial, you MUST re-raise the exception */
end;

If you need to know more about autonomous transaction, please check:
http://docs.oracle.com/cd/E25054_01/server.1111/e25789/transact.htm#i7733


Update nth element of array using a WHERE clause I have a product table with a jsonb column named 'metadata' in a PostgreSQL 10 database. It's my first time working with documents and Postgres. jsonb values look something like this:

{
  "name" : "l33t shirt",
  "price" : "1200",
  "quantity": "60",
  "options" :
    {
      "type" : "radio",
      "title" : "color",
      "opts" : [
        {"value" : "red" , "price" : "-100" , "qty" : "30"  },
        {"value" : "blue" , "price" : "+200" , "qty" : "10"  },
        {"value" : "green" , "price" : "+300" , "qty" : "20"  }
      ]
    }
}

Two questions:

1. How can I select a specific element in the "opts" array?

select metadata->'options'->'opts'->(element here) from product
where  metadata->'options'->'opts' @> '[{"value" : "blue"}]'

2. How to update the "qty" (subtract from its current "qty") when one or more of them is sold?

Further links to guides / notes are appreciated.

How can I select a specific element in "opts" array?

Use the index of the element number as instructed in the manual. For longer paths, the path notation is shorter:

SELECT metadata -> 'options' -> 'opts' -> 0 AS elem0
     , metadata #> '{options, opts, 0}'     AS elem0_path
FROM   product;

Gets the nth element, like the question title asks (JSON array index starts with 0). But your example indicates you actually want the element with "value":"blue". That's not as trivial. You could unnest the nested JSON array with jsonb_array_elements() in a LATERAL join and filter the one you want.

SELECT opt AS elem_blue
--   , metadata #> '{options, opts}' -> (arr.ord::int - 1) AS elem_blue2
FROM   product
     , jsonb_array_elements(metadata #> '{options, opts}') WITH ORDINALITY arr(opt, ord)
WHERE  opt ->> 'value' = 'blue';

Returns nothing if no matching element is found.

WITH ORDNALITY and elem_blue2 are not needed here, but demonstrate a technique we are going to use in the next step.

More explanation:

PostgreSQL unnest() with element number
Query for element of array in JSON column

  2.how to update the "qty" (subtract from its current qty) when one or more of them is sold?

Since Postgres 9.5, there is jsonb_set(). To update the value of the qty key of the first element:

UPDATE product
SET    metadata =  jsonb_set(metadata, '{options, opts, 0, qty}', '"29"', false)
WHERE  ... -- some filter

Aside: Any reason your integer numbers in qty are stored as strings ("30"), not as numbers (30)?

To update the "blue" element, we determine the array index with the technique demonstrated above, plus some UPDATE magic to make the it fully dynamic:

UPDATE product p
SET    metadata = jsonb_set(p.metadata, path, qty, false)
FROM   product p1
     , LATERAL ( -- move computations to subquery
   SELECT ARRAY['options', 'opts', (ord - 1)::text, 'qty'] AS path  -- fix off-by-one
        , to_jsonb((opt ->> 'qty')::int - 1)               AS qty   -- subtract here!
   FROM   jsonb_array_elements(p1.metadata #> '{options, opts}') WITH ORDINALITY arr(opt, ord)
   WHERE  opt ->> 'value' = 'blue'
-- AND    ...  -- more filters
-- FOR UPDATE  -- see below
   ) opt
WHERE  p1.product_id = p.product_id  -- use PK for match

The last query writes a number to "qty" ('9'), not a string ('"9"'), assuming you fixed that as commented.

We need to list the table product in the FROM clause another time to allow the LATERAL join (which wouldn't be possible otherwise) - and self-join to it.

Note a tiny race condition. Under heavy concurrent write load, you might want to add a lock clause to the subquery (FOR UPDATE) to prevent other transactions from changing the row between the inner SELECT and the outer UPDATE. See:

Postgres UPDATE ... LIMIT 1

But you shouldn't, really. As you can see, jsonb (or any document type for that matter) is ill-suited for regular updates to single attributes. That's cumbersome and comparatively expensive. A new row with a new version of the complete document has to be written every time. Use a normalized DB design instead, where only a comparatively very small row has to be rewritten and other parts as well as indexes remain untouched.

If only the quantity is updated regularly, you might move that to a 1:n table and merge it into the JSON document in a VIEW or MATERIALIZED VIEW. The redesign is beyond the scope of this question.


MariaDB Query slower after mysqldump I have a MariaDB server with several databases.

On my MariaDB server, some queries (with joins between tables of different databases) are slower after the daily mysqdump (one file sql by database).

After the daily mysqldump, the first run for one query will be 1.5 minutes  and the second run 3.2 secondes (this time is coherent with current run on the day).
I understand the second run is faster but why the first run after the daily mysqldump is slower ? And how improve it ?

An extrait of my server.cnf :

key_buffer_size = 384M
max_allowed_packet = 16M
table_open_cache = 2000
sort_buffer_size = 8M
join_buffer_size = 8M
read_buffer_size = 8M
read_rnd_buffer_size = 16M
myisam_sort_buffer_size = 64M
query_cache_limit= 128M
query_cache_size= 256M
query_cache_strip_comments=1
thread_concurrency = 8
tmp_table_size = 32M
max_heap_table_size = 32M
innodb_buffer_pool_size = 3G
innodb_buffer_pool_instances = 3
innodb_log_file_size = 448M
innodb_log_buffer_size = 64M
innodb_flush_log_at_trx_commit = 1
innodb_print_all_deadlocks = 1
innodb_lock_wait_timeout = 210

Thanks for you help.

First query could be slow as the innodb buffer has been filled up with the queries required for the mysqldump and all the data needs to be fetched from disk.

You could run SET GLOBAL innodb_buffer_pool_dump_now=1 before the dump and SET GLOBAL innodb_buffer_pool_load_now=1 after the dump to ensure the buffer pool is what it was prior.


MySQL workbench alter table is incomplete I have just upgraded MySQL 5.6 to 5.7 and workbench from 6.2 to 6.3.6 to have the latest and do some modelling. The upgrade went fine and the app runs perfectly but something is different and I can't figure it out...

When I right click and look at Table Inspector, everything is as expected with columns, indexes and foreign keys. However when right click and select Alter Table the column list is incomplete, there are no indexes  and no foreign keys.

This is only failing since the upgrade but I can't find anything with the config or privileges to get it working. Any help would be greatly appreciated.

*** further observation:
If there is no timestamp datatype everything looks fine. However when there is, the alter table window always stops showing columns at the first timestamp and never shows indexes or FK's even though every other approach proves they exist.

There seems to be an anomaly or bug when a timestamp column has a default value of CURRENT_TIMESTAMP() - with the parenthesis. 5.7 documentation says it is synonymous with CURRENT_TIMESTAMP but workbench must disagree - sort of.

Changing the definition in workbench by removing the parenthesis provides a message that nothing changed. However changing to "CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP" fixes the problems mentioned in the question. And subsequently changing it "CURRENT_TIMESTAMP" only also works.

I'll report this as a workbench 6.3.6 bug.


mariadb replication issues First, I am not a DBA. Second, the person who set up mariadb left company recently.
On a corporate system, we have mariadb 5.5.2 running on two Dell servers, CentOS 7.3. Servers are about 16 months old. Never rebooted until July 2017. Noticed after that that data from server 1 (master) was not updating on server 2, which is slave. Missing some rows.
Following notes on mariadb replication here: https://mariadb.com/kb/en/the-mariadb-library/setting-up-replication/
Can connect slave server 2  to master server 1 with this:
CHANGE MASTER TO
MASTER_HOST='SOMEIP',
MASTER_USER='esg',       <<< -- replication user, i think
MASTER_PASSWORD='*********',
MASTER_PORT=3306,
MASTER_LOG_FILE='mysql-bin.000012',
MASTER_LOG_POS=5067,
MASTER_CONNECT_RETRY=10;

On server 2, SHOW SLAVE STATUS shows a connection to the master SOMEIP.
When I run this command, from the link above:
GRANT REPLICATION SLAVE ON *.* TO esg;

I get:

ERROR 1045 (28000): Access denied for user 'esg'@'localhost' (using password: YES)

Running same command on master server 1 as --skip-grant-tables, user root, says operation not permitted under skip-grant-tables.
Wondering which way to go here. esg is only user I know about.

I set up replication with these directions:
http://www.techrepublic.com/article/how-to-set-up-database-replication-with-mariadb/
Currently see this on slave:

Slave_IO_State: Waiting for master to send event.

All other variables like Seconds_Behind_Master=0 look good. Slave_SQL_Running, Slave_IO_Running are YES. Never see replication tables/databases on slave.
Why do tutorials say to copy databases from master to slave? Replication should handle this.
Background
Added configurations for bind-address=0.0.0.0 to master/slave on both master and slave and create unique user ids for each of two affected servers. Get master_log_pos and master_log_file from master. Run these commands:
GRANT REPLICATION SLAVE ON *.* TO root;
GRANT ALL PRIVILEGES ON *.* TO 'root'@'someIP' IDENTIFIED BY 'somePassword' WITH GRANT OPTION;

On slave, connect to master with command like this:
CHANGE MASTER TO MASTER_HOST='SOMEIP', MASTER_USER='esg',
MASTER_PASSWORD='*********', MASTER_LOG_FILE='master1-bin.000012',MASTER_LOG_POS=5067;
START SLAVE;

If the esg user was already used for replication, you shouldn't need to grant privileges again.  Login to the master server with he esg user, and execute show grants.  This will confirm whether or not the esg user has REPLICATION SLAVE privilege.

If you don't already have it, get the root password set to something you know.  That will be helpful for troubleshooting and setup/resync.

If you are unsure of the replication state, execute SHOW SLAVE STATUS and check the fields SLAVE_SQL_Running and SLAVE_IO_Running.  If either of those are NO, then check the status for Error messages and #'s.  If Replication is down, then that may be a cause of missing data.

If your SLAVE threads are running, check Seconds_Behind_Master.  If that is not 0 (or something relatively close to 0), then your slave is behind, and that could be the missing data.

If the slave is up to date, and your missing some data, another possible cause is that another user with write privileges accidentally inserted/updated/deleted on the Slave instead of the master.  Yes, this does happen.

Another possibility is data inconsistency from using Statement Based Replication, as opposed to Row Based Replication.  Check show global variable like 'bingo_format' to determine if you're using statement or row based replication.

Beyond that, you might need to break your question down a bit more to understand what you're doing (rebuilding rep, vs restarting) and focusing in on one piece at a time.