Transformation of row data based on a grouping of rows preceding it The basis for this question is: Transformation of row data based on preceding row for special cumulative sum which I had asked recently.

I will now raise the level of complexity a bit.

First, I will quickly reiterate over the make up of the data.

This is the initial data:

id      creation                    operation       value       running sum
SyJw-c  2016-09-01 00:11:08.307419  positive_op_1   1.33        28.82
SyJw-c  2016-08-21 08:32:54.431662  negative_op_1   -1          27.49
SyJw-c  2016-08-18 07:38:33.878365  positive_op_2   1           28.49
SyJw-c  2016-08-14 18:12:03.599797  negative_op_1   -1          27.49
SyJw-c  2016-08-02 15:44:29.693303  positive_op_1   1.33        28.49
SyJw-c  2016-07-31 12:08:50.659905  override_op_1   4.66        27.16
SyJw-c  2016-06-26 06:53:54.537603  negative_op_1   -3.5        22.5
SyJw-c  2016-05-31 13:34:08.005687  negative_op_1   -1          26
SyJw-c  2016-05-31 13:34:04.776970  negative_op_1   -1          27
SyJw-c  2016-05-31 11:27:09.502983  override_op_2   28          28

As clarified by @Erwin Brandstetter

This is the definition of the table

CREATE TABLE tbl (  -- no PK
  id text NOT NULL
, creation timestamp UNIQUE NOT NULL
, operation text NOT NULL
, value numeric NOT NULL
, running_sum numeric  -- optional (not needed for task)
);

This is the transformed data:

id      creation    oper      transformed_op   value   transformed_value       running sum
SyJw-c  2016- ...  pos_op_1                    1.33    1.33                    10.98
SyJw-c  2016- ...  neg_op_1                    -1      -1                      9.65
SyJw-c  2016- ...  pos_op_2                    1       1                       10.65
SyJw-c  2016- ...  neg_op_1                    -1      -1                      9.65
SyJw-c  2016- ...  pos_op_1                    1.33    1.33                    10.65
SyJw-c  2016- ...  ovr_op_1   new_rel_op_1     4.66    (4.66-22.5) = -17.84    4.66
SyJw-c  2016- ...  neg_op_1                    -3.5    -3.5                    22.5
SyJw-c  2016- ...  neg_op_1                    -1      -1                      26
SyJw-c  2016- ...  neg_op_1                    -1      -1                      27
SyJw-c  2016- ...  ovr_op_2   new_rel_op_2     28      (28-0) = 28             28

I run on postgresql 9.5

I wish to be able to calculate the running_sum and the transformed_value.

In contrast to the question asked and answered above, the predicate for calculating the transformation is not based solely on the row previous (here the table, as in the other question, is ordered by creation DESC).
The logic is as follows:

If the operation is an override_op_2 then the running_sum gets the value of the value column and the transformed_value is the value of the value column subtracted by the value of the running_sum of the previous row` (Nothing different than the original question)
If the operation is an override_op_1 then the logic follows that of the override_op_2 unless an override_op_2 had previously been encountered (for the id grouping), in this case it should be disregarded (or more explicitly the transformed_value is 0)

What seems more difficult here than in the original question is that the calculation is not based directly on the row preceding it but on a number of rows which is unknown in advance.

While I guess you could do that within a function (that stores a perviously encountered override_op_2 value (or NULL)), I wonder of it could be done in simple sql.

If the operation is an override_op_1 then the logic follows that of the override_op_2 unless an override_op_2 had previously
  been encountered (for the id grouping), in this case it should be
  disregarded (or more explicitly the transformed_value is 0)

In effect, to disregard such rows, change them:

value = 0 - no change in running sum.
operation = 'zero_op' - any non-overriding type (don't start a new partition)

Do that to qualifying rows before applying everything else I already worked out in my previous answer:

SELECT id, creation
     , CASE WHEN operation = 'override_op_1'
            AND  had_op_2 THEN 'zero_op' ELSE operation END AS operation
     , CASE WHEN operation = 'override_op_1'
            AND  had_op_2 THEN 0 ELSE value END AS value
FROM  (
   SELECT id, creation, operation, value
        , bool_or(operation = 'override_op_2')  -- `override_op_2` previously encountered
          OVER (PARTITION BY id ORDER BY creation) AS had_op_2
   FROM   tbl
) t

So the complete query becomes:

SELECT *
     , running_sum - lag(running_sum, 1, numeric '0')  -- data type must match!
                     OVER (PARTITION BY id ORDER BY creation) AS transformed_value
FROM (
   SELECT id, creation, operation, value
        , sum(value) OVER (PARTITION BY id, run ORDER BY creation) AS running_sum
   FROM   (
      SELECT *, count(*) FILTER (WHERE operation LIKE 'override_op_%')
                OVER (PARTITION BY id ORDER BY creation) AS run
      FROM  (
         SELECT id, creation
              , CASE WHEN operation = 'override_op_1'
                     AND  had_op_2 THEN 'zero_op' ELSE operation END AS operation
              , CASE WHEN operation = 'override_op_1'
                     AND  had_op_2 THEN 0 ELSE value END AS value
         FROM  (
            SELECT id, creation, operation, value
                 , bool_or(operation = 'override_op_2')
                   OVER (PARTITION BY id ORDER BY creation) AS had_op_2
            FROM   tbl
            ) t
         ) t
      ) t
   ) t
ORDER  BY id, creation DESC;

Alternatively, you could just delete such rows or move them out of the way (SET id = id * -1). Not sure if you need to preserve these "zero_op" rows in the result.


Is GRANT DELETE TABLE t TO PUBLIC and the revoke the right to DELETE TABLE t FROM user1 possible? I have a production database that has GRANTs TO PUBLIC for INSERT, UPDATE, DELETE on specific tables of the production schema, table t1 being one of these tables.
Say I want to create a user user1 that cannot INSERT, UPDATE, DELETE on table t1. Is there a way to revoke these privileges for user1 while keeping the GRANTs TO PUBLIC?

If something is granted to PUBLIC, that's it.  There is no 'blacklist' or 'grant to public but not to Joe'.  You should manage this with profiles.


What is the name of the part between SELECT and FROM in a SQL query? I am trying to find the right description for the parts of a query:

What is the name of the part between SELECT and FROM? If I only had some columns then I would call it "column identifiers". But if there are any function or subqueries this would be quite a bit more. Is "SELECT clause" a valid description?
Same thing for the FROM part. If I have only one table I would simply call it "table". But if there is a more complicated structure maybe using joins, etc. what would be the correct naming?

To make my questions more clear:

In Postgres I could use a JSON function, e.g. json_array_elements in both parts:

SELECT
    json_array_elements(my_json_column)
FROM
    my_table

or doing this

SELECT
    elem.*
FROM
    my_table,
    json_array_elements(my_json_column) AS elem

If I have to tell somebody that this function can be used within the SELECT clause or as part of the FROM clause which terms are more conventional?

It's usually called a "select list", which contains "select list items".

The FROM clause contains a list of table references.

This is not specific to Postgres, by the way.

So, with respect to your use case you might say that json_array_elements() can be used both as an expression in the select list and as a table reference.


How to pass credentials to a linked server in another domain? I am trying to link two SQL Servers (2008R2 and 2014) which are running in two separate (!) Active Directory domains.

The problem is that I can login into the remote server only with a Windows Account from the remote domain. So the challenge is to pass on the credentials to the remote server, but here I am stuck.

Local Domain: dom8, Server: dom8\sql2008, SQL (!) Login: localuser
Remote Domain: dom14, Server: dom14\sql20148, Windows (!) Login: dom14\import

Server dom8\sql2008 wants to connect to dom14\sql2014 in order to pull some data.

Here is what I have tried on the local server:  (logged into dom8\sql2008 as sa) :

-- Create a credential for the remote Windows login:
create credential cred_import WITH IDENTITY= 'dom14\user14',
       SECRET = 'password' ;

-- Alter the local SQL login and add the created credential:
alter login local_user with credential = cred_import ;

-- Create the Linked Server entry:
exec sp_addlinkedserver
    @server='dom14\sql2014',
    @srvproduct='SQL Server'

-- Add the credential to the linked server:
exec sp_addlinkedsrvlogin
    @rmtsrvname ='dom14\sql2014',
    @useself = 'FALSE',
    @locallogin=local_user,
    @rmtuser = [cred_import],  -- trying to pass on the credential
    @rmtpassword = NULL

However, it does not work. Whenever user localuser tries to connect to the remote server through the local server, then it gets an error login failed

The question is: How to properly pass on the credential to the remote server link? I am stuck.

You probably need to set up Kerberos to handle delegation. This requires setting up an SPN for your SQL Server as well. The following question and sole answer give more details:

SQL Bulk Insert Impersonation issue

The main documentation links in that Question and its Answer are:

How to Configure the Server to be Trusted for Delegation
Register a Service Principal Name for Kerberos Connections
SQL Bulk Insert Impersonation issue

Also, I'm not sure if this info will be useful or not, but it might help to have the Login and Password for the account on both domains to be exactly the same. I know that when you do not have Active Directory set up but want to share folders between two systems without having to log in, it works if you use the exact same Login with the exact same Password.


MS SQL Management Studio - Script of INSERT statements leaves empty chars for shorter than limit NVARCHAR? I have just observed in MS Management Studio that if we want to get the script for table's inserted values the output includes the unused free data space which is in excess for the e.g. NVARCHAR data type. As you can see on the below screenshot, the student names are up to 5 characters which should most equal 10 bytes. However, MS Management Studio has allocated the maximum size to the field as lots of scroll to the right is needed to see the whole insert statement...?

Please advise - is there a way to prevent this?

edit: Please find the advanced scripting options selection from MS Server Management Studio on the below screenshots:

Thanks much in advance!

CREATE TABLE [dbo].[Students](
    [StudentID] [int] NOT NULL,
    [StudentName] [nvarchar](100) NOT NULL,
    [StudentSurname] [nvarchar](100) NOT NULL,
    [Biology] [decimal](3, 2) NOT NULL,
    [Maths] [decimal](3, 2) NOT NULL,
    [Geography] [decimal](3, 2) NOT NULL,
 CONSTRAINT [PK_Students] PRIMARY KEY CLUSTERED
(
    [StudentID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
INSERT [dbo].[Students] ([StudentID], [StudentName], [StudentSurname], [Biology], [Maths], [Geography]) VALUES (1, N'Mike                                                                                ', N'Manson                                                                                ', CAST(2.00 AS Decimal(3, 2)), CAST(2.00 AS Decimal(3, 2)), CAST(1.00 AS Decimal(3, 2)))
GO
INSERT [dbo].[Students] ([StudentID], [StudentName], [StudentSurname], [Biology], [Maths], [Geography]) VALUES (2, N'Timo                                                                                ', N'Torn                                                                                ', CAST(1.00 AS Decimal(3, 2)), CAST(2.00 AS Decimal(3, 2)), CAST(4.00 AS Decimal(3, 2)))
GO
INSERT [dbo].[Students] ([StudentID], [StudentName], [StudentSurname], [Biology], [Maths], [Geography]) VALUES (3, N'Jeffrey                                                                                ', N'Jones                                                                                ', CAST(1.00 AS Decimal(3, 2)), CAST(3.00 AS Decimal(3, 2)), CAST(4.00 AS Decimal(3, 2)))
GO

I believe that the underlying data in your table has additional spaces at the end.  You can verify the actual data length with a query like:

SELECT LEN(StudentName) FROM dbo.Students WHERE StudentID = 1

If it's not 4, then it's storing more than just 'Mike' and SSMS is simply preserving your data.

If you don't want the extra spaces, you can remove it from your data thusly:

UPDATE dbo.Students
SET
    StudentName = LTRIM(RTRIM(StudentName)), -- older db compatibility levels
    StudentSurname = TRIM([StudentSurname]) -- new db compatibility levels


Getting the metadata about a Query and a Stored procedure in SQL Server I would like to get all the names of the columns in a given query [SQL Textual Query] and also the columns that are in the SELECT clause in a Stored Procedure.

Is there any ways other than using REGEX to get these information from the SQL Server. I can give the name of the SP.

Kindly suggest me the best practice for getting this information while using SqlDataReader in C#.

SET FMTONLY ON can be used to return just column information, no rows will be processed or returned.

  Returns only metadata to the client. Can be used to test the format of
  the response without actually running the query. (BOL)


Should SQL Server 2000 throw an error if update statement fails within a SP? I'm troubleshooting an issue on SQL Server 2000 where it appears that a update statement in a stored procedure failed, and the check for @@ERROR <> 0 didn't catch it, so the transaction was committed.

Below is a condensed version of the code. I've removed a lot of details and proprietary information, but this is the gist of it. This code has been running daily for several months and producing the expected result, except for once last week, where the statement:

UPDATE /*app table*/
   set dateprocessed = @currentDate
 where jobname = 'Job_'
       + REPLACE(CONVERT(VARCHAR(10), GETDATE(), 101), '/', '')
       + LEFT(REPLACE(CONVERT(VARCHAR(8), GETDATE(), 108), ':', ''), 4)
       + '_'
       + (SELECT Description FROM #tempTable1 WHERE id = @i)

...apparently did not actually update the referenced table, however the rest of the code executed and the transaction was committed.

If the update statement didn't complete sucessfully, should it have been caught by my @@ERROR check?

DECLARE @startTime DATETIME,
        @procedureName NVARCHAR(50),
        @i INT,
        @currentDate DATETIME,
        @minimumLetterCount INT;

SET @startTime = GETDATE();
SET @procedureName = (SELECT OBJECT_NAME(@@PROCID));
SET @i = 1;
SET @currentDate = GETDATE();

BEGIN TRANSACTION
    IF (--constraints are met)
    BEGIN
        IF OBJECT_ID('tempdb.dbo.#tempTable1') IS NOT NULL
            DROP TABLE #tempTable1;

        IF OBJECT_ID('tempdb.dbo.##globalTempTable1') IS NOT NULL
            DROP TABLE ##globalTempTable1;

        CREATE TABLE #tempTable1 (
            --columns defined here
        );

        CREATE TABLE ##globalTempTable1 (
            --columns defined here
        );

        INSERT INTO #tempTable1
            EXEC /*SP name*/

        --Process pending items by ID
        WHILE (@i <= (SELECT COUNT(*) FROM #tempTable1))
        BEGIN
            DECLARE @CurrentItemID VARCHAR(3);
            DECLARE @CurrentJobName VARCHAR(100);
            SELECT @CurrentItemID = (SELECT LetterID FROM #tempTable1 WHERE id = @i);
            SELECT @CurrentJobName = 'Job_' + REPLACE(CONVERT(VARCHAR(10), GETDATE(), 101), '/', '') + LEFT(REPLACE(CONVERT(VARCHAR(8), GETDATE(), 108), ':', ''), 4) + '_' + (SELECT Description FROM #tempTable1 WHERE id = @i);
            EXEC /*SP name*/ @TableID = @CurrentItemID, @ThroughDate = @currentDate, @JobName = @CurrentJobName, @SeedItem = 1;

            INSERT INTO ##globalTempTable1
            SELECT --some data from other tables into this temp table

            UPDATE /*app table*/ set dateprocessed = @currentDate where jobname = 'Job_' + REPLACE(CONVERT(VARCHAR(10), GETDATE(), 101), '/', '') + LEFT(REPLACE(CONVERT(VARCHAR(8), GETDATE(), 108), ':', ''), 4) + '_' + (SELECT Description FROM #tempTable1 WHERE id = @i)

            UPDATE l
                SET l.LinkedValue1 = lb.LinkedValue2
                FROM ##globalTempTable1 l
                INNER JOIN (SELECT UniqueID, SUM(CAST(REPLACE(REPLACE(REPLACE(REPLACE(value, '$', ''), ',', ''), '(', ''), ')', '') AS DECIMAL(12,2))) AS LinkedValue2 FROM ##globalTempTable1 GROUP BY UniqueID) lb ON l.UniqueID = lb.UniqueID;

            SET @i = @i + 1;

        END

    END

IF @@ERROR <> 0
    BEGIN
        ROLLBACK TRANSACTION

        --log failure
        INSERT INTO JobLog (jobName, success, startTime, endTime) VALUES (@procedureName, 0, @startTime, GETDATE());
    END
ELSE
    BEGIN
        COMMIT TRANSACTION

        --log success
        INSERT INTO JobLog (jobName, success, startTime, endTime) VALUES (@procedureName, 1, @startTime, GETDATE());
    END

--Cleanup temp tables
IF OBJECT_ID('tempdb.dbo.#tempTable1') IS NOT NULL
    DROP TABLE #tempTable1;

IF OBJECT_ID('tempdb.dbo.##globalTempTable1') IS NOT NULL
    DROP TABLE ##globalTempTable1;

I suspect what happened is that the row you expected to find was not found, perhaps because the name of the job was off, or because GETDATE() being evaluated multiple times will, once every so often, be evaluated on different seconds. So I'm not quite sure why you have a new job for every timestamp you're handling, but it's certainly plausible that it didn't match for this reason (just hard to reproduce on modern versions). See if #tmp ever gets populated:

SET NOCOUNT ON;
CREATE TABLE #tmp(i int);
GO

INSERT #tmp(i) SELECT o4.id + 0
 FROM sysobjects AS o0, sysobjects AS o1,  syscolumns AS o2,
      sysobjects AS o3, syscomments AS o4, syscolumns AS o5
WHERE CONVERT(char(8), getdate(), 108) <> CONVERT(char(8), getdate(), 108);
GO 1000

SELECT i FROM #tmp;
GO

DROP TABLE #tmp;

If you get one or more rows, then you know getdate() was evaluated differently. If you don't, you were lucky, this time.

To avoid this possibility, assign getdate() to a variable:

UPDATE /*app table*/
   set dateprocessed = @currentDate
 where jobname = 'Job_'
       + REPLACE(CONVERT(VARCHAR(10), GETDATE(), 101), '/', '')
       + LEFT(REPLACE(CONVERT(VARCHAR(8), GETDATE(), 108), ':', ''), 4)
       + '_'
       + (SELECT Description FROM #tempTable1 WHERE id = @i);

Remember (not just you, but future readers too), try this on SQL Server 2000. If memory serves, older versions were much more susceptible to this, and it is unlikely you will reproduce on modern SQL Server.


Is a table without a primary key normalized? In a lecture, my lecturer showed us a table without a primary key. Upon questioning, he said that in 3NF when you remove transitive dependencies it's ok to then have a table without a primary key.

However, no primary key implies there's no functional dependencies - but 3NF is the removal of transitive dependencies, and I was taught that each table needs to have a primary key for normalization as it's all about functional dependencies.

I know it's fully possible to create a table without a primary key, but is that database considered normalized if that table exists?

I should add, the table doesn't have any “unique key”, no primary, no composite, no foreign.

The table shown has three attributes with none of them labeled as primary or unique. I asked if it was a mistake and he said it's fine to not have one. I questioned the remark as none of the information in the table can be uniquely identified and he claimed it's ok to be like this. This goes against what I was taught about normalisation.

If a relation does not have any candidate key (and the primary key is just one of the candidate keys), then it can have duplicate rows, so in fact it is not a relation! (since relations are always sets).

In that case, it is more correct to call it a table, not a relation, as you did in your question, and note that several RDBMS in effect can manage not-relations, allowing tables without any constraint of uniqueness, even if this case is very uncommon, and produces problem (anomalies) when operating on the data.

But in this case talking about normal forms is not appropriate: all the normalization theory is based on the fundamental assumption that the objects of interest are relations, not multisets. In fact this theory is based on the (somewhat discussed) Universal Relation Assumption, that assumes that all the relations of a database are a subset of a projection of such relation, that contains all the attributes in every relation. And that object is in fact a relation (i.e. a set), not a multiset.

The fact that, when talking about data of the Relational Data Model, sometimes we exchange the two terms, tables and relations, does not means that they are actually synonym, and this difference is fundamental when we talk about normalization theory. Remember that in the books, when some normal form is introduced, it is always said something like:

  A relation is in xxx normal form if...


Looking for tree organisation for database, based on one to many relationship both ways All the tree organisations for databases I've found documentation, papers or blogs about were built on a one parent node can have many children, but a child node can have only one parent, with a single original node - basis.

I am looking for a way to organise a database efficiently on a basis that a parent node can have many children, a child node can have many parents, and there are multiple original nodes.

The data is not going to change or move around much, but there will be insertions in the middle of the tree on a regular basis.

Any ideas or links to papers appreciated.

A tree is, by definition, one parent with many children, each of which can have many children. What you desire is a generalised network, not a tree.

There are many DBMS products which present a graph (i.e. generalised network) data model. Both items and the relationships between items are first-class objects. Typically both can have arbitrary labels and attributes. These products are also optimised for relationship-based processing such as friends-of-friends, shortest path or cluster detection. If navigating the network is more important for your use case than OLTP point updates or calculating bulk aggregates then one of these DBMS may suit.

From comment:

  Is there a way of having a direction to this network though ? It's for storing school programme data, and the prerequisite knowledge for a certain course for example. (And that course can have many prerequisites and can be prerequisite to others, but there is a direction to the structure.)

All graph DBMSs implemented directed graphs. This means there is a "from" and a "to" in each relationship. This is analogous to, but different from, the "parent" and "child" in a relational database foreign key. A relationship's direction is set when the relationship is written to the DB. Relationships can be read in either direction, however, much like a SQL query can follow a foreign key to find the children given the parent, or the parent given a child.

A hypothetical graph for your school prerequisites might look like:

Basic math   <-- requires --  Advanced math  <--- requires ---¬
        ^                                                     |
        └-------  requires -----------¬                    Medicine
                                      |                       |
Basic chemistry  <-- requires --  Advanced chemistry  <-- requires

Medicine has two prerequisites - advanced math & advanced chemistry. Basic math is a prerequisite for two other subjects.


Single data type for imprecise date values, as allowed by ISO 8601 How can I store date and time values with reduced precision in a PostgreSQL type, and have them behave as date and/or time values?

ISO 8601 allows date values with reduced precision. ‘1964’, ‘1964-05’, ‘1964-05-02’ are all valid representations of a value, in increasing precision. The Python ‘datetime’ types also allow values with reduced precision in this way.

PostgreSQL native time types doesn't allow reduced precision

In the native date type, every element of a date must be present or the value is rejected. Setting the elements below the desired precision level to ‘00’ also fails.

=> SELECT CAST('1964-05-02' AS DATE);
    date
------------
 1964-05-02
(1 row)

=> SELECT CAST('1964-05' AS DATE);
ERROR:  invalid input syntax for type date: "1964-05"
LINE 1: SELECT CAST('1964-05' AS DATE);
                    ^
=> SELECT CAST('1964' AS DATE);
ERROR:  invalid input syntax for type date: "1964"
LINE 1: SELECT CAST('1964' AS DATE);
                    ^
=> SELECT CAST('1964-00-00' AS DATE);
ERROR:  date/time field value out of range: "1964-00-00"
LINE 1: SELECT CAST('1964-00-00' AS DATE);
                    ^
HINT:  Perhaps you need a different "datestyle" setting.

Expected behaviour for a reduced-precision date and/or time type

Is there a simple, standard way to support entry of ISO 8601 date values with reduced precision into a PostgreSQL date and/or time type?

Creating a type for this is possible, but I don't know how. Of course, I need the values to be range checked and deal with timezones and compare sensibly with other time values all the other useful things the built-in types do.

What I expect is that, just as the value ‘1964-05-02’ refers to the entire interval between 00:00:00 on that day until 00:00:00 the next day, a reduced-precision value would simply represent a larger interval: ‘1962-05’ refers to the entire interval between 00:00:00 at the beginning of May 1962 until 00:00:00 on the first day of June of 1962.

An example of what I'd like to see:

=> SELECT CAST('1964-05-02 00:00' AS TIMESTAMP) = CAST('1964-05-02 00:00:00' AS TIMESTAMP);
 ?column?
----------
 t
(1 row)

=> SELECT CAST('1964-05' AS TIMESTAMP) = CAST('1964-05-02' AS TIMESTAMP);
 ?column?
----------
 t
(1 row)

Currently the former behaves as above; the latter complains about “invalid input syntax for type timestamp”. To my eye, they're both cases of reduced-precision values behaving sensibly when compared to finer-precision values.

The meaning of 1964-05 in ISO 8601 includes the more-precise values 1964-05-02 and 1964-05-02 18:27 and 1964-05-23. So those should all compare equal.

I have used CHAR and VARCHAR in the past, replacing the missing pieces with question marks or dashes. Question marks mean "not known", and dashes meant "not applicable". This proved to be intuitive enough for the users (secretaries and paralegals in complex litigation), flexible enough for the lawyers, and it sorted sensibly.

"1964------"
"1964-??-??"
"1964-05---"
"1964-05-??"
"1964-05-02"
"1964-06---"
"1964-06-??"

Wrap your declaration and CHECK constraints in CREATE DOMAIN or CREATE TYPE to make maintenance easier. CREATE DOMAIN doesn't require additional coding. CREATE TYPE requires support functions written in a low-level language.


How to count NULL inside CASE? What is the best way to count NULL values inside case?

Using a joined table - I am trying to count the values of the column P.ID (from the joined table), and to categorize the counted values into a new column  via case.

For example:

when count 0 - display 'none'
when count 1-2 - display 'few'.
when count 3-5 - display 'medium
when count 5+ - display 'many'

However, since some of the values received from the join are null - I cannot count them.

What changes should I do to my query in order to refer every NULL as 0?

SELECT
    U.Mail,
    COUNT(P.ID) AS PostCount,
    CASE
        WHEN COUNT(P.ID) = 0 THEN'none' --doesn't work
        WHEN COUNT(P.ID) <= 2 THEN 'few'
        WHEN COUNT(P.ID) <= 5 THEN 'medium'
        ELSE 'many'
    END AS PostCountCategory
FROM
    Users U
LEFT JOIN
    Tag T ON U.Mail = T.Mail
LEFT JOIN
    Post P on T.IDPost = P.ID
GROUP BY U.Mail;

I am not very clear on your question however you can alter your code as below for your null question:

select U.Mail, count(isnull(P.ID, 0)) as PostCount,
    case
        when count(P.ID) = 0 then 'none' --doesn't work
        when (count(P.ID) <= 2 and count(P.ID) > 0) then 'few'
        when (count(P.ID) <= 5 and count(P.ID) > 2 )then 'medium'
        else 'many'
    end PostCountCategory
from Users U
left join Tag T on U.Mail = T.Mail
left join Post P on T.IDPost = P.ID
group by U.Mail

Alternatively you can use count(isnull(P.ID, 0)) inside your case statement. Hope this helps.

As suggested by Mustaccio, you may use coalesce also instead of isnull.


SQL Server Backups-Order of FULL & T-log backups I have an elementary question. I just started as a DBA with no administration experience and upon reviewing the Backup schedule, I noticed on one instance that the maintenance plan that performs the backups is set up with 4 steps. The first and last steps are maintenance, but the second and third are to take Full backups (2nd step) and then take T-log backups (step 3) of all user DB's.

The job that calls the maintenance plan is run at 6AM and 4PM. I am confused why they would take the FULL backups first and then T-log backups directly after the FULL? It would make sense to me if it was the other way around.
This is how it makes sense to me at least:

->at 610am, FULL backup completes

->at 4PM, take t-log backup to backup the t-log since 6AM

->at 410PM, take FULL backup

->at 6AM, take t-log backup for data since yesterdays 410PM FULL backup

->at 610AM, Take full backup

Can anyone think of a good reason for taking a FULL and then a t-log directly after? I tried asking the employees that have been here but they are clueless.

Can anyone think of a good reason for taking a FULL and then a t-log
  directly after? I tried asking the employees that have been here but
  they are clueless.

The reason I can think of is to 'truncate' the Transaction Log since the last full backup happened. If you would not do these log backups, the log would keep on growing.

If truncating the transaction log is the main reason then you should just set your database to the SIMPLE recovery model instead. This means that you will lose data up until the last full/diff backup.

But it would be no different with the current method of taking backups.

Unless some restore procedure is using STOPAT to restore to a point in time after the log backup has happened. (this still means data loss up untill the last backup, possibly +10h)

Another reason could be that the database is part of an AlwaysOn AG, but this should give you even more incentive for more frequent log backups.

The main issue

The main thing is, the log backups cost the same, wether you run them every minute,every 15 minutes or every 10 hours. Take a look at this blog post by Brent Ozar as to why it should not matter how often we schedule this (without getting too crazy).

Running them once before every full backup is not what they where designed to do, you should schedule them more often.

Something I prefer is this backup solution by Ola Hallengren, instead of maintenance plans.

Long story short, I would schedule log backups more often, the default people tend to use is every 15 minutes, but it depends on your RPO.

  But are there any unobvious implications by taking the Full and then
  the log directly afterwards?

There should not be any issues with doing this, other than the remarks mentioned above.


Trigger function using current row and current table name as variables Like detailed in my first question I have multiple tables with identical layout in a Postgres 9.1 DB.
They only vary in their column values:

tbl_log_a
tbl_log_b
tbl_log_c
...

26 tables (from a to z). Each table has a trigger that calls a trigger function named trfn_tbl_log_%letter% (from a to z) which does the exact same thing:

CREATE OR REPLACE FUNCTION trfn_tbl_log_a
  RETURNS trigger AS
$BODY$
DECLARE
v_timeidx real;

BEGIN

IF NEW.timetype = 'start' THEN
  SELECT timeidx FROM tbl_log_a
  WHERE fnname = NEW.fnname AND timetype = 'start'
  ORDER BY stmtserial DESC LIMIT 1 INTO v_timeidx;
    IF FOUND THEN
      NEW.timeidx := floor(v_timeidx) + 1;
    ELSE
      NEW.timeidx := 1;
    END IF;

ELSIF NEW.timetype = 'lap' THEN
  SELECT timeidx FROM tbl_log_a
  WHERE fnname = NEW.fnname AND (timetype = 'start' OR timetype = 'lap')
  ORDER BY stmtserial DESC LIMIT 1 INTO v_timeidx;
    IF FOUND THEN
      NEW.timeidx := v_timeidx + 0.001;
    ELSE
      RAISE EXCEPTION USING MESSAGE = 'There is not any previous row WHERE fnname = NEW.fnname AND (timetype = start OR timetype = lap)';
    END IF;

ELSIF NEW.timetype = 'resume' THEN
  SELECT timeidx FROM tbl_log_a
  WHERE fnname = NEW.fnname AND (timetype = 'start' OR timetype = 'resume')
  ORDER BY stmtserial DESC LIMIT 1 INTO v_timeidx;
    IF FOUND THEN
      NEW.timeidx := v_timeidx + 0.001;
    ELSE
      RAISE EXCEPTION USING MESSAGE = 'There is not any previous row WHERE fnname = NEW.fnname AND timetype = start';
    END IF;

END IF;
return NEW;

END
$BODY$
  LANGUAGE plpgsql;

Trigger definition:

CREATE TRIGGER trfn_tbl_log_a
  BEFORE INSERT ON tbl_log_a
  FOR EACH ROW EXECUTE PROCEDURE trfn_tbl_log_a();

So I have to create 26 trigger functions, one for each tbl_log_%letter%
all of them are exactly the same except for the used table name (tbl_log_a in the example).

Is there a way to write one generic trigger function, with dynamic SQL maybe and parameterize the table name?

My trigger function uses several table columns:

timeidx
fnname
timetype
stmtserial

... and many more that I didn't add for size, but all in the example list all the kinds.

Assuming that, for the same trigger invocation, you take all the values from the same row in the table firing your trigger, your trigger function could look like this:

CREATE OR REPLACE FUNCTION trfn_tbl_log_any()
  RETURNS trigger AS
$func$
DECLARE
   _ct int;
BEGIN

IF NEW.timetype = 'start' THEN

   EXECUTE format($$
      SELECT floor(t.timeidx) + 1
      FROM   %s t
      WHERE  t.fnname = $1
      AND    t.timetype = 'start'
      ORDER  BY t.stmtserial DESC
      LIMIT  1$$
    , TG_RELID::regclass  -- concatenate *identifer* ..
      )
   USING NEW.fnname        -- .. but pass *value* in USING clause
   INTO  NEW.timeidx;

   GET DIAGNOSTICS _ct = ROW_COUNT;

   IF _ct > 0 THEN  -- do nothing
   ELSE
      NEW.timeidx := 1;
   END IF;
END IF;

RETURN NEW;
END
$func$  LANGUAGE plpgsql;

All of this should work in Postgres 9.1. But consider upgrading to a current version anyway (currently 9.4).

NEW is not visible inside EXECUTE. Use the USING clause to pass the value from the new row (NEW.fnname).
Use TG_RELID (or TG_TABLE_SCHEMA and TG_TABLE_NAME) to concatenate the table name like we worked out under your previous question:

PostgreSQL trigger function that selects from %current% table

Use format() and dollar-quoting to simplify the syntax of string-concatenation safely.
You can assign individual columns of the NEW row directly from the dynamic query.
Use GET DIAGNOSTICS _ct = ROW_COUNT; to check if rows were found. Per documentation:

  Note in particular that EXECUTE changes the output of GET DIAGNOSTICS, but does not change FOUND.

Aside: the logic of the dynamic query is only correct for BEFORE trigger. An AFTER triggers would also see the newly inserted row.


Connecting to AWS RDS DBInstance with MySQL workbench I am trying to evaluate AWS RDS to use as possible DB for our application. I have created a DBInstance(micro) and added IP security group. I am able to connect to the DBInstance from Xampp shell command line and i am able to run queries from that.

But when i try to connect with workbench to the same DBInstance, it gives me error that 'MySQL server has gone away'.
Following are the steps i followed to connect with workbench:

Give the Endpoint address of DBInstance as Hostname.
Port : 3306
Set the username and password to master user and its password.

My database is completely new, I have just created a plain DB and trying to connect with workbench right away. I am using workbench version 5.2.25.

Any ideas on what am i missing? And yes, I am just a beginner with MySQL and AWS.

Edit: As asked by RolandoMySQLDBA, i have run the query SELECT USER(),CURRENT_USER(); and in the result i get one row with two columns USER()=ei@xxx.xx.xxx.xxx and CURRENT_USER()=ei@%. ei is the master username for my RDS DBInstance. And the column USER() has my ip address after @.

I tried using both ei@xxx.xx.xxx.xxx and ei@% with workbench, but i get access denied error .

If you have instantiated a MySQL RDS Instance, you want to create a DB Security Group

First Create a DB Security Group
Under "Security Group Details", set one of the following connection types

CIDR/IP
EC2 Security Group

Assign Address/Mask
Make sure you do not have a proxy/firewall causing interference


DB2 Server Table Space Locked At work we keep receiving the following DataException seemingly at random when one of our processes tries to write/access a table for one of our clients:

com.ibm.db.DataException: A database manager error occurred. :
[IBM][CLI Driver][DB2/NT64] SQL0290N  Table space access is not allowed.  SQLSTATE=55039

Has anyone encountered this? I'm not the person who primarily does administrative tasks on our databases, but even they seem to be having difficulty finding the root of this problem. Any suggestions? Anyone encounter this before? This error comes up for only one of our clients at a time, and it generally seems to rotate. We have Rackspace service but they wont be of much help unless we can provide screenshots, etc at the exact moment this occurs.

Apologies if this post may be too vague, please let me know what information to supply to clarify things more. I'm one of the developers in my office, but I don't primarily handle the administrative tasks on our databases.

edit: We spoke with IBM and this could possibly be caused by some sort of virus scan being run by IBM/Rackspace as a part of maintenance? They said this kind of dubiously though, so I am doubting this is the culprit because tables remained locked for variable amounts of time.

First I'd say you want to know what tablespace it is. A lot of times I connect to the database and use LIST TABLESPACES SHOW DETAIL to figure out what is happening. Look at each tablespace and see what value is set for the "State" field on each. You want a "zero" value for the tablespace to be fine.

And here is a handy query:

select substr(tbsp_name,1,30) as tbsp_name, substr(TBSP_STATE,1,18) as TBSP_STATE
from TABLE(MON_GET_TABLESPACE('',-2)) with ur

This gets all tablespaces with a human readable state. Found this off of Ember Crooks' blog, which I follow. Very useful.

Anyway first you want to know which space it is an what state it is in. Once you know that, you can either deal with it or dig further. For example, if the tablespace is quiesced, then you only need to unquiesce it or reset the quiesce. I'd still ask why....Perhaps you have an ETL job or something that quiesces the tablespace and doesn't release it properly if it fails? Or take a peek at your the diaglogs to see what was going on at the time (very key here).

Try those out and let us know what it going on.


To get that every row need to know the value of the next one, but the LEAD function is not in SQLServer 2008, so an auto-join is used in his stead

With I AS (
  SELECT EMPLID, Name, PUNCHDATE, PUNCHTIME, DEVICETYPE
       , ID = Row_Number() OVER (PARTITION BY EMPLID
                                 ORDER BY EMPLID, PUNCHDATE DESC, PUNCHTIME DESC)
  FROM   Table1
)
Select a.EMPLID, a.Name, a.PUNCHDATE, a.PUNCHTIME, a.DEVICETYPE
FROM   I a
       LEFT  JOIN I b ON a.EmplID = b.EmplID And b.ID = a.ID - 1
WHERE  a.DeviceType <> b.DeviceType
   OR  a.ID = 1
ORDER BY a.EMPLID, a.PUNCHDATE, a.PUNCHTIME

SQLFiddle demo

The Row_Number is used to simplify the JOIN condition.


SQL Server 2008R2 - Stored Procedure with 2 updates causing deadlocks I have a web api web service that calls an authentication stored procedure.
This stored procedure calls a different stored procedure that does 1 update:

UPDATE  AccountLicensingDeviceConnectionsPerPeriod
SET     DateLastConnected = GETDATE(),
        ConnectionPermitted = @IsValid
WHERE   AccountId = @AccountId
        AND UserId = @UserId
        AND Device = @Device

And then does a different update:

UPDATE  Users
SET     DateLastLoggedIn = GETDATE(),
        LastLoggedInDevice = @Device,
        InvalidLoginCount = 0
WHERE   Id = @UserId

These updates are always in the same order or at least they should be, is there any way to avoid 2 concurrent processes to deadlock?

The deadlock graph:

Deadlock graph data extracted

GetIsAccountLicenseConnectionValid SP

AuthenticateUser SP - h**ps://dl.dropboxusercontent.com/u/204180/AuthenticateUser.sql

Using SentryOne Plan Explorer I was able to make a little more sense of the deadlock graph (click to embiggen):

The victim also called the stored procedure GetIsAccountLicenseConnectionValid, and I suspect this procedure has some touch against one or both tables affected by the updates in the question. It is certainly feasible that locks taken by that stored procedure could interfere with locks affecting another transaction that hasn't called that stored procedure yet.


How to free space after deleting the millions of rows from a table I performed a delete operation on my table . It deleted around 10 millions of rows from it

How do I free the space associated with the deleted data.

I used the query:

alter table tablename shrink space;

But it did not work for me. Do I need to concern about the index's associated with the table?

If that is the case, how do I need to do it.

alter table TABLENAME move;

followed by rebuilding the indexes on that table should do what you want.

After 'moving' the table around, the indexes for the rows will become unusable (as you can verify with something like):

select index_name, status from all_indexes
 where table_name = 'TABLENAME' and
       owner      = 'SOMEUSER';

So, you would rebuild such an unusable index with a

alter index TABLENAME_IX_01 rebuild;


Best Practice For Data Views with Linked Server in SQL Server In SQL Server, I have to design a View that I can map on a DBML file in order to dynamically retrieve information from the database with multiple conditions to filter.

The problem is that I have to combine the information from multiple tables, and some of them are on another server.

Currently, I have a view with most of the information, composed by multiple joins of several tables on a server (A), and another view in another server (B) that joins that view  on server A with several other tables from the same server B.

So the flux of data as I understand it goes like this:
Final View on Server B = Server A View JOIN Server B Tables

Here is the template for the Server A view:

ALTER VIEW [dbo].[DataView_ServerA]
AS
SELECT
    ss.Field,
    ss.Field,
    ss.Field,
    ss.Field,
    (CASE   WHEN ss.Field is null then 'Field not Assigned' else ss.Field end) as Field,
    mfl.Field as Field,
    St.Field,
    (CASE   WHEN ss.Field=0 THEN CAST(0 AS BIT)
            WHEN ss.Field=1 THEN CAST(1 AS BIT)
            else ss.Field end) as Field,

    fh.Field AS Field,
    ssABC.Field AS Field,
    (CASE WHEN ssABCAB.Field IS NULL OR ssABC.Field IS NOT NULL THEN CAST(0 AS BIT) ELSE CAST(1 AS BIT) END) AS Field,
    (select (CASE WHEN count(Field)>=1 THEN CAST(1 AS BIT) ELSE CAST(0 AS BIT) END) as treatedByAB from table where Field='Example' and id in (select Field from [table] where Field= fh.Field)) as Field

FROM
    dbo.table AS ss WITH (nolock)
LEFT OUTER JOIN
    dbo.table as St  WITH (nolock) on ss.Field!=St.Field
LEFT OUTER JOIN
    dbo.table AS ssABC WITH (nolock) ON ss.Field = ssABC.Field AND ssABC.Field = St.Field
LEFT OUTER JOIN
    dbo.table AS fh WITH (nolock) ON fh.Field= ssABC.Field
LEFT OUTER JOIN
    dbo.table AS ssABCAB WITH (nolock) ON ss.Field= ssABCAB.FieldAND ssABCAB.Station = St.Station
LEFT OUTER JOIN
    dbo.table mfl ON ss.Field=mfl.Field and ss.Field=mfl.Fieldand ss.Field=mfl.Field and mfL.Field='Example'

And the template for the Final View on Server B is:

ALTER view [dbo].[FinalDataView_ServerB] as
select ABCraw.*,
Tch.Field,
cat.Field,
f.Field

FROM [LinkedServer].[Database].[dbo].DataView_ServerA as raw

LEFT OUTER JOIN
    dbo.table AS f ON f.Field=raw.Field
LEFT OUTER JOIN
    dbo.table AS act ON f.Field = act.Field
LEFT OUTER JOIN
    dbo.table  AS cat ON f.Field = cat.Field
LEFT OUTER JOIN
    dbo.table AS Tch ON f.Field= Tch.Field

But the time that it takes to retrieve registers is normally above 7 minutes, with no distinction to the quantity.

I you could provide me of some advice of how to rewrite the query in order to optimize it or configure the servers to load the data more quickly, I would appreciate it a lot.

Thanks in advance.

In my experience, the round-robin joins on both legs of the query certainly make for an expensive plan.  It depends on a few issues, but consider using these options to help you:

Use a temporary or a work table on Server A named #DataTable_ServerB_local to store the results from Server B.
If possible create a stored procedure Prepare_Datatable_ServerB_Data on Server B that you can call remotely to get the Server B data. This may join back to Server A to get some data.
UseOPENQUERY to execute the Remote Stored Procedure Prepare_Datatable_ServerB_Data  to insert its data into Server A's #DataTable_ServerB_local. The code internal to the stored procedure would join back to the local server to get what I suspect are the relatively few rows that it needs.
Join DataView_ServerA with #DataTable_ServerB_local which are now both on Server A to provide the final result.

This strategy assumes that Server B joining back to Server A is the smaller set of joins.  (If this is not the case then you will need to switch directions with this strategy.)

The whole point of this approach is to:

Limit the number of rows passing between the servers.
Avoid as many joins across the linked servers as possible.
To the extent possible (e.g. the Prepare_Datatable_ServerB_Data procedure) process data on the server where the data is stored.

Big joins across a linked server can be very expensive in terms of time and the optimizer may not be able to make good decisions.


Find closest match in two columns, one containing wildcards no script to attach as I can pull the data just fine, I am struggling to join my two tables though.
The column I want to join on has the same information formatted differently in each table, making it worse, one of these columns uses wildcards for the program that takes data from there.
Example of what I mean:
T1.Col1 would contain the actual value, say EXAMPLE   A , EXAMPLE   B , EXAMPLF A
T2.Col1 would have one entry like EXAMPL*[AB]
The Program using that table would treat all values the same if it started in EXAMPL, and ending in EITHER A or B.
Meaning the following would all be a match, this is what I need to recreate for my script:
EXAMPLE A , EXAMPLE B , EXAMPLF A
Can I recreate that logic in SQL? To find a match/closest match based on certain conditions? T2 Column contains about 200 values just like that and all different, so I need a solution that can apply to everything and not just this case.
I've tried formatting T2 values using Replace to be more like T1 but the square brackets are killing me. Making new rows in the output table might help but we're at the limit of what I can do in SQL.

To achieve that result you would have to use the LIKE operator and a REPLACE on the join like this:
CREATE TABLE T1 (Col1 varchar(256));

INSERT INTO T1
VALUES ('EXAMPLE A'), ('EXAMPLE B'), ('EXAMPLF A');

CREATE TABLE T2 (Col1 varchar(256));

INSERT INTO T2
VALUES ('EXAMPL*[AB]');

SELECT T1.Col1, T2.Col1
FROM T1
    INNER JOIN T2 ON T1.Col1 LIKE REPLACE(T2.Col1, '*', '%');

If you check the Arguments section of the logical operator LIKE, you'll see that the * does not stand for 'any string of zero or more characters' in SQL Server that's the role of %. The use of square brackets of your sample on the other hand is the same used in SQL Server, so it will work as expected without any change.


SQL Server 2014-Can we stop sql auto recompile process? Is there a way to check what is causing a stored proc to recompile (other than index rebuild, stats update and parameter sniffing-without running profiler trace or extended events) and can we stop/cancel/ Kill the auto recompile process to reduce CPU load?

You can monitor for statement level recompile events using Extended Events. I have a blog post here that lays out exactly how to do it. You can see that there is a column for the recompile reason. That will tell you explicitly why any given statement was recompiled.
You can, for a plan that stays in cache, prevent it from recompiling while it's in cache by using the query hint KEEPFIXED PLAN. If the plan ages out of cache, it will still have to compile the next time the query is run. This also doesn't prevent recompiles from structural or code changes. Drop an index, change the procedure, you still get a recompile. This is because it can no longer be assured that the plan will work because it may reference the index you dropped, or a different column than the code you submitted. So it has to recompile. However, for statistics updates, KEEPFIXED PLAN will prevent automatic recompiles.
There is also a KEEP PLAN hint that will reduce the number of recompiles due to data changes, but it won't eliminate them the way KEEPFIXED PLAN will.
One more option would be to create plan guides for specific plans where recompilation is taking significant resources. Although, for plan guides and plan forcing using query store, the compilation process still runs, using up those resources. You just end up with a specified plan.


Is it possible to log backup results to table in a different database on a different server I am using Ola Hallengren's maintenance solution to back up a SQL 2012 Express instance in our environment. I am a brand new DBA, only 10 weeks old so I am not too familiar yet but I am learning fast. I am pretty sure I have everything configured the way we want it. I created the stored procedures in the master database of our express instance and I have scheduled batch files to be executed nightly from within Windows Task Scheduler and all seems to be set up and working minus one detail, the logging to table.

First of all, I am not quite sure how to enable it or what database to find the table in if it is enabled?

Second, I would like to log the results to a table in a different database on a different server\instance. Is this possible? If so, does anyone have any direction they can point me to so I can set this up?

Thanks!

The dbo.CommandLog table will be in whatever database you created it. By default it will be in master (here is a portion of the MaintenanceSolution.sql script):

USE [master] -- Specify the database in which the objects will be created.

...

IF NOT EXISTS (SELECT * FROM sys.objects
  WHERE object_id = OBJECT_ID(N'[dbo].[CommandLog]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[CommandLog](
  [ID] [int] IDENTITY(1,1) NOT NULL,
  [DatabaseName] [sysname] NULL,

...

If you want to log on another server, you need to be prepared for things like flaky or dead connections, and the potential latency of writing a lot of information over a linked server.

If you really want to do this, then create the dbo.CommandLog table on that other instance, then in the database where that table exists currently:

-- you could drop, but in case you have history you want to keep:
EXEC sys.sp_rename N'dbo.CommandLog', N'CommandLogBackup', N'OBJECT';

CREATE SYNONYM dbo.CommandLog
  FOR LinkedServerName.DatabaseName.dbo.CommandLog;

In dbo.DatabaseBackup there is a check for the table in the same database as the procedure:

IF @LogToTable = 'Y' AND NOT EXISTS
(
  SELECT * FROM sys.objects objects
    INNER JOIN sys.schemas schemas
    ON objects.[schema_id] = schemas.[schema_id]
    WHERE objects.[type] = 'U'
    AND schemas.[name] = 'dbo'
    AND objects.[name] = 'CommandLog'
  )
  BEGIN
    SET @ErrorMessage = 'The table CommandLog is missing. Download...'
    RAISERROR(@ErrorMessage,16,1) WITH NOWAIT
    SET @Error = @@ERROR
  END

It's only failing because it's explicitly looking for a table - the fact that it is a synonym won't prevent any subsequent logging calls from working (again, as long as everything - including the synonym - live in the same database). You can try just commenting out the RAISERROR line or just removing the check in its entirety. Just remember to do that any time you deploy an update to the procedure. You might consider asking Ola for a feature to support a synonym or view so that the logging table could be kept elsewhere.


Count for many-to-many relationship I have the following tables:

surveys            properties   owners_properties        owners
---+------------   ----------   ------------+---------   ------
id | property_id   id           property_id | owner_id   id

I want to find the count of surveys that have more than one property owners.

Here is a rough stab:

SELECT COUNT(*) FROM surveys
JOIN properties ON surveys.property_id = properties.id
JOIN owners_properties...
HAVING count(properties.id > 1);

You are close.  In SQL Server, you have to GROUP BY before you state HAVING.  Personally, I would do something like this

    SELECT COUNT(id) Surveys_w_2_LandOwners
    FROM surveys s
        JOIN
        (
            SELECT COUNT(1),property_id
            FROM owners_properties
            GROUP BY property_id
            HAVING COUNT(1) > 1
        ) p ON p.property_id=s.property_id


Exponential log file growth on quiet database with log backups I have a small (~3GB) database in Full recovery mode, with full backups at midnight and transaction log backups at noon.
Despite being a testing database with virtually no activity, the size of the log files (and transaction log backups) are growing exponentially:

Date
Size

March 2:
200MB

March 3:
560MB

March 4:
963MB

March 5:
1.5GB

March 6:
2.5GB

March 7:
5.9GB

For comparison, the nigh-identical production version of the same database has transaction log backups (same schedule) under 100MB.
The autogrow is set to 10%, so each new VLF winds up 10% larger than the last, but it's a mystery to me why new VLFs are created several times a day at all!  There are no open transactions, and when I checked DBCC LOGINFO last night, there were numerous large VLFs with status 0, so log backup is truncating fine.  They were all status 2 this morning.
Doing periodic LOGINFO & LOGSPACE checks this morning, I saw the log expand to add a VLF, despite having virtually nothing in the last one:

The previous size would have been ~6369MB, so that VLF had about 25/700MB in use.
Can something cause SQL Server to move onto a new VLF before the active one is remotely full?
EDIT:
Trace shows a 90 minute shrink and grow cycle

Action
duration
start
end

Log File Auto Grow
1473000
2021-03-08 10:50:14.970
2021-03-08 10:50:16.443

Log File Auto Shrink
1000
2021-03-08 10:50:13.763
2021-03-08 10:50:13.763

Log File Auto Grow
1116000
2021-03-08 09:19:36.813
2021-03-08 09:19:37.930

Log File Auto Shrink
1000
2021-03-08 09:19:35.827
2021-03-08 09:19:35.827

Log File Auto Grow
1090000
2021-03-08 06:48:33.760
2021-03-08 06:48:34.850

Log File Auto Shrink
1000
2021-03-08 06:48:32.207
2021-03-08 06:48:32.207

Log File Auto Shrink
1000
2021-03-08 05:17:54.147
2021-03-08 05:17:54.147

Have you checked the error log ?
Run select * from sys.databases & look at the log_reuse_wait_desc column.
Run DBCC OPENTRAN and see if you have any open active transactions ?
Query the default trace and get the timings of when the file growths are happening
https://www.mssqltips.com/sqlservertip/3445/using-the-sql-server-default-trace-to-audit-events/


How to change/add to schema in a SQL Server Always On Availability Group? I have a 2-node Always On Availability group cluster in SQL Server 2016.  I am wondering how should I go about adding a new table, or modifying the schema of an existing table in an Always On Availability group without incurring downtime?

Is this fully automatic?  I.E. I simply go ahead and create my table on the primary in an availability database and it will then get replicated to the secondary?  Or will something weird happen?  I'm worried that there might be cases where it doesn't work and replication between the primary and secondary is halted.  For example, what happens if SQL Server Management Studio decides to drop and recreate the table?  Will this work as expected?  Are there any gotchas to be aware of?

Thanks in advance, and any help is greatly appreciated.  Am a long-time SQL Server user but am new to Availability Groups.

I am wondering how should I go about adding a new table, or modifying the schema of an existing table in an Always On Availability group without incurring downtime?

If you Always On availability group is set up correctly and sync is working no downtime is required. Connect to the primary (preferably using listener) and do your change in the same way you will be doing in a stand-alone server.

Is this fully automatic?

Yes, it is fully automated.

I simply go ahead and create my table on the primary in an availability database and it will then get replicated to the secondary?

Yes, it will get replicated to all secondary nodes (assuming your sync is working correctly).

Few more resources to read on this:

Do schema changes “break” Availability Groups or are they handled transparently?
Whitepaper: Microsoft SQL Server AlwaysOn Solutions Guide for High Availability and Disaster Recovery


How can I use the same primary key across multiple tables as a foreign key? I am working on a school project. I have a table of employees with a given employee ID. The employee ID on this table is the primary key. I have already set the employee ID as a foreign key on another table, and I am trying to use it as a foreign key on another table as well. How can I do this? I am currently getting an error message saying "duplicate foreign key". Thanks!

I suspect you are linking the tables backwards.
You can most definitely have multiple tables' FK point to the same table's PK. The FK is defined on the table doing the pointing, not the table being pointed at.


pg_stat_statements empty with administrator user and unusable for other users I have an issue with pg_stat_statements, the extension is installed and enabled on the database, was working for a long time, however it now seems that it has crashed.

portaldb=> SELECT * FROM pg_available_extensions WHERE name = 'pg_stat_statements';
        name        | default_version | installed_version |                          comment
--------------------+-----------------+-------------------+-----------------------------------------------------------
 pg_stat_statements | 1.4             | 1.4               | track execution statistics of all SQL statements executed
(1 row)

portaldb=# SELECT * FROM pg_available_extension_versions WHERE name = 'pg_stat_statements';
        name        | version | installed | superuser | relocatable | schema | requires |                          comment
--------------------+---------+-----------+-----------+-------------+--------+----------+-----------------------------------------------------------
 pg_stat_statements | 1.4     | t         | t         | t           |        |          | track execution statistics of all SQL statements executed
(1 row)

The extension is enabled on the portaldb database

portaldb=> create extension pg_stat_statements;
ERROR:  extension "pg_stat_statements" already exists

I was considering removing it and then recreating it but I feel a bit scared to apply a cascade drop, because this is a big e-commerce portal and we don't want lose data or cause any outage.

portaldb=# DROP EXTENSION pg_stat_statements;
ERROR:  cannot drop extension pg_stat_statements because other objects depend on it
DETAIL:  function get_pg_stats() depends on type pg_stat_statements
HINT:  Use DROP ... CASCADE to drop the dependent objects too.

If I try with the user that manages the database we receive the following error:

portaldb=> SELECT * FROM pg_stat_statements ORDER BY total_time DESC;
ERROR:  relation "pg_stat_statements" does not exist
LINE 1: SELECT * FROM pg_stat_statements ORDER BY total_time DESC;

However if we execute the same query under root (postgres user) we receive a blank output.

portaldb=# SELECT * FROM pg_stat_statements ORDER BY total_time DESC;
 userid | dbid  |  queryid   |

portaldb=#

root@Serv01:~# cat /app/postgres/postgresql.conf | grep shared_preload_libraries
shared_preload_libraries = 'pg_stat_statements'
root@Serv01:~# cat /app/postgres/postgresql.conf | grep pg_stat_statements.
shared_preload_libraries = 'pg_stat_statements'
pg_stat_statements.max = 10000
pg_stat_statements.track = all
pg_stat_statements.track_utility = on

Has anyone else come across this situation? I Googled but I didn't find any related case.


mysqldump takes forever to dump tables from a remote server I'm trying to pull the database schema from a remote server, but mysqldump is too slow to output (125+) tables' structures.

If I enter the said server and run mysqldump --compact --compress --no-data --add-drop-database --databases --user=myuser --password=123 mydb > dump.sql it's almost instant.
If I run the same thing over ssh it takes 2.5 secs (ssh myserver mysqldump [...] > dump.sql)
However, if I use the --host so I can use the database credentials only, instead of the SSH credentials, it takes 2min40s to run!

What makes mysqldump so slow when running over its own protocol? It dumps each table (all at once) every few seconds, then waits, dumps the next one...
Is there anything I could do to make things faster?

EDIT: I wrote about that db adventure, to show the final solutions.

I ran into the same issue and regarding the "why" this happens, my theory is that it's due to network overhead of opening/closing connections.
My theory is that mysqldump needs to open and closes a lot of connections and that's what's slowing it down.

I ran it on the machine itself and it took 10 seconds.
From another machine, through a VPN, it took like 10 minutes.
From another machine on the same network (connected to the same switch), it took 20 seconds.

It's just a theory but it fits those observations.

Of course to work around that issue, the ssh trick works.


Query: How to get both Question and multi Answers in one query For this question, Question and Answer are just my example tables for one-many relationship. For example: Question(QuestionID, QuestionTitle, QuestionContent) and Answer(AnswerID, AnswerContent, QuestionID)

In one query, I want to get one question and all answers of this question by QuestionID:

How to write a query like that
In case that, I "think" will have each rows is an answer with additional fields of question. This will make duplication because all those answers have same question content --> cost memory to store additional data. So compare to this: get QuestionID and query get Question. get QuestionID and query to get all Answers. Does this way lost performance than first approach but gain more memory ?

Here is my script:

DROP TABLE IF EXISTS `Question` ;

CREATE TABLE IF NOT EXISTS `Question` (
  `QuestionID` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `QuestionContent` VARCHAR(200) NOT NULL,
  PRIMARY KEY (`QuestionID`),
ENGINE = InnoDB;

DROP TABLE IF EXISTS `Answer` ;

CREATE TABLE IF NOT EXISTS `Answer` (
  `AnswerID` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `QuestionID` INT UNSIGNED NOT NULL,
  `ReplyContent` VARCHAR(200) NOT NULL,
  PRIMARY KEY (`AnswerID`),
  INDEX `fk_Answer_Question1_idx` (`QuestionID` ASC),
  CONSTRAINT `fk_Answer_Question1`
    FOREIGN KEY (`QuestionID`)
    REFERENCES `Question` (`QuestionID`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
ENGINE = InnoDB;

Thanks :)

You could make it with a simple JOIN, the question is: Are you going to have others tables?. That could make that you have to redo your design. I suggest EAV if you're going to use more tables with some configuration between Questions and Answers.

With your actual design the query would be like:

mysql> SELECT
    -> q.QuestionID,
    -> q.QuestionContent,
    ->     a.AnswerID,
    ->     a.ReplyContent
    -> FROM test.Question AS q
    -> JOIN test.Answer AS a ON (a.QuestionID=q.QuestionID)
    -> WHERE q.QuestionID=1;
+------------+------------------------------+----------+--------------+
| QuestionID | QuestionContent              | AnswerID | ReplyContent |
+------------+------------------------------+----------+--------------+
|          1 | How many indexes may I have? |        1 | Use 6        |
|          1 | How many indexes may I have? |        2 | Use 1        |
|          1 | How many indexes may I have? |        3 | A or B       |
+------------+------------------------------+----------+--------------+
3 rows in set (0.00 sec)

mysql> EXPLAIN SELECT
    -> q.QuestionID,
    -> q.QuestionContent,
    ->     a.AnswerID,
    ->     a.ReplyContent
    -> FROM test.Question AS q
    -> JOIN test.Answer AS a ON (a.QuestionID=q.QuestionID)
    -> WHERE q.QuestionID=1;
+----+-------------+-------+-------+-------------------------+---------+---------+-------+------+-------------+
| id | select_type | table | type  | possible_keys           | key     | key_len | ref   | rows | Extra       |
+----+-------------+-------+-------+-------------------------+---------+---------+-------+------+-------------+
|  1 | SIMPLE      | q     | const | PRIMARY                 | PRIMARY | 4       | const |    1 |             |
|  1 | SIMPLE      | a     | ALL   | fk_Answer_Question1_idx | NULL    | NULL    | NULL  |    3 | Using where |
+----+-------------+-------+-------+-------------------------+---------+---------+-------+------+-------------+
2 rows in set (0.00 sec)

mysql>

Related:

To EAV or not to EAV?
Efficient query for an advanced EAV model
Eav MySQL Search


Changing column width Will increasing a column (nvarchar) width necessarily drop the table?

In other words, can the width be changed in a production environment with active users?

I figured that if the size is increasing (as opposed to decreasing) this wouldn't be a problem.

If you're doing it through a T-SQL statement such as below, then no table drop will occur and you can safely do it in a production environment:

alter table <table> alter column <column> nvarchar(biggernumber) [not] null

If you do it through the SSMS Design Table GUI, it will depend on what script it decides to use to implement the change. Sometimes it will insert data into a temporary table, drop the original table, create a new version of that table, and insert it back into the new one. An easy way to find out what it will do is to click the "Generate Script" button and look at the T-SQL it plans on executing.


How to find the MySQL query that got rolled back I want to know if a certain transaction got rolled back. If we analyze

  SHOW ENGINE INNODB STATUS\G;

we get the list rolled back transactions with transactionid , but how to get the actual query from this

eg I have

*** WE ROLL BACK TRANSACTION (1)
------------
TRANSACTIONS
------------
Trx id counter 89504501
Purge done for trx's n:o < 88503595 undo n:o < 0 state: running but idle
History list length 2341
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 531230733218221, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 531230733221873, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 531230733213665, not started
0 lock struct(s), heap size 1136, 0 row lock(s)

I get some transcation id = 531230733218221, but what is the INSERT/UPDATE query corresponding to this.
Here is my mysql config:

Version: 5.7
Binary Log: ON
Slow Query Log: ON
General Query Log: OFF
Performance Schema: ON
Profiling: OFF
Transaction Isolation Level: REPEATABLE-READ

Is there any table in performance_schema that we can explore. Since MySQL do not have any mechanism to store the last updated timestamp at row/column level, though it exists at the table level.

Look in the SHOW ENGINE INNODB STATUS output for

LATEST DETECTED DEADLOCK

and between this line and the

WE ROLL BACK TRANSACTION

you will find QUERY content that was involved for each contributor to the deadlock (there may be thousands of lines of detailed info).


How to prevent implicit casting from bytea to text during the current connection in postgres? In Postgres bytea values are automatically converted to text when inserted to text/varchar columns, based on the bytea_output setting.
I am working with some program code which automatically converts certain values (binary strings) in the program to a bytea format. The problem is that users might accidentally try to insert these values into a text column. By default, Postgres will allow this, but this cannot always work smoothly - for example if there are non-ASCII bytes. I think users may not realise the strange insert behaviour is due to their use of a binary string in the calling program. Therefore, if a bytea to text conversion happens, I want Posgres to raise an exception.
I am aware of CREATE CAST, but as I understand, this is a system-wide action. I do not want to change the system behaviour for other connections. I could do CREATE CAST followed by DROP CAST but this seems dirty to me as it still not contained within the connection.
How do I make (implicit) casts from bytea to text throw an exception only within the current connection?
The sql is issued automatically, so I can add a preceding SQL statement before every statement, that's no problem.
I was a little surprised by this behaviour because Postgres usually errs on the side of strictness, which I like.
This question follows from my previous question:

How to prevent different connections treating bytea in same query differently?

for example if there are non-ASCII bytes it cannot work smoothly

Why would you think so? bytea is coerced to either hex (default) or escape format when assigned to a text column. Non-ASCII characters are encoded automatically. Should work "smoothly" at all times - except that you don't want to allow it.

I could do CREATE CAST followed by DROP CAST but this seems dirty
to me as it still not contained within the connection.

True, but it is contained within the transaction if you create and drop the cast within: DDL commands are fully transactional in Postgres, so only your session (within that current transaction) will ever get to see the cast.

How do I make (implicit) casts from bytea to text throw an exception only within the current connection?

... I can add a preceding SQL statement before every statement, that's no problem.

Solution with custom cast
Currently (all versions incl. Postgres 13) the cast from bytea to text has no explicit entry in the system catalog pg_cast. It is provided by basic input/output functions of the respective types. This behavior can be overruled with an explicit entry, created with CREATE CAST.
You need to be the owner of the involved types, so this basically means you have to be superuser install it.
Create this casting function once per database:
CREATE FUNCTION public.text(bytea, int, bool)
  RETURNS text
  LANGUAGE plpgsql STABLE STRICT PARALLEL SAFE AS
$func$
BEGIN
   IF $3 THEN           -- true if the cast is an explicit cast, false otherwise.
     -- no infinite loop because we do the cast manually
     -- honors current setting for bytea_output, hence function not IMMUTABLE
      RETURN textin(byteaout($1));
   ELSE
      RAISE EXCEPTION 'Assignment cast from bytea to text forbidden by custom cast rules in this database!';
      RETURN textin(byteaout($1));  -- we should *never* get here!
   END IF;
END
$func$;

To allow creating / dropping the special cast to unprivileged roles, add wrapper functions. Do this as superuser (or as dedicated daemon role):
CREATE FUNCTION public.f_create_cast_bytea2text()
  RETURNS void
  LANGUAGE sql SECURITY DEFINER AS
'CREATE CAST (bytea AS text) WITH FUNCTION public.text(bytea, int, bool) AS ASSIGNMENT;';

CREATE FUNCTION public.f_drop_cast_bytea2text()
  RETURNS void
  LANGUAGE sql SECURITY DEFINER AS
'DROP CAST IF EXISTS (bytea AS text);';

Now you can do what you asked for:
BEGIN;
SELECT public.f_create_cast_bytea2text();  -- optionally activate your casting rule

INSERT INTO tbl(txt_col)
VALUES ('\000'::bytea::text, 'local bytea_output: hex');    -- explicit cast still works!

INSERT INTO tbl(txt_col)
VALUES ('\000'::bytea); -- but assignment cast forbidden! -> ERROR

SELECT public.f_drop_cast_bytea2text();  -- deactivate your casting rule
END;

db<>fiddle here -- second half does not execute due to missing privileges.
Extended test case
Test table:
CREATE TABLE test(id int, txt_col text, note text);
INSERT INTO test(id, txt_col, note) VALUES
  (-1, 'foo', 'plain text input')
, ( 0, '\000'::bytea, 'default bytea_output: ' || current_setting('bytea_output'));

No exception raised:
BEGIN;
SELECT public.f_create_cast_bytea2text();

SET LOCAL bytea_output = 'hex';
INSERT INTO test(id, txt_col, note)
VALUES (1, '\000'::bytea::text, 'local bytea_output: hex');    -- explicit cast still works

SET LOCAL bytea_output = 'escape';
INSERT INTO test(id, txt_col, note)
VALUES (2, '\000'::bytea::text, 'local bytea_output: escape'); -- explicit cast still works

SELECT public.f_drop_cast_bytea2text();
END;

Also no exception:
BEGIN;
SELECT public.f_drop_cast_bytea2text();

SELECT '\000'::bytea || text 'foo'; -- implicit cast still works

SELECT public.f_drop_cast_bytea2text();
END;

Exception raised:
BEGIN;
SELECT public.f_create_cast_bytea2text();

INSERT INTO test(id, txt_col, note)
VALUES (3, '\000'::bytea, 'must fail!'); -- assignment cast forbidden!

SELECT public.f_drop_cast_bytea2text();
END;

Client side problem?
Your comment seems to reveal a rabbit hole:

Input values must be prepared differently depending on whether they
are strings with encoding or binary strings. Even if we make use of
the currently active encoding. Assuming that binary strings will go
into bytea and strings with encoding will go into text.

The solution cannot work at all once you incorrectly assume data type text on the client side. The cast is only invoked if you hand in typed bytea values. I.e.: use a function or prepared statement with explicit data type or append an explicit cast to string literals handed to an INSERT command like demonstrated above: '\000'::bytea.
Once you pass untyped literals, Postgres has no way to know that it should really be bytea. And how can you (incorrectly) prepare bytea strings for text input and then still (correctly?) add an explicit cast to bytea?


ORA-24033 : No recipient for message has been detected I am using Oracle E-business Suite R12.1, 11G database version.

Error I encountered when trying to create or modify a user or responsibility:

  ORA-24033 : no recipient for message has been detected in
  FND_USER_RESP_GROUPS_API.UPDATE_ASSIGNMENT

My workaround

I have followed Oracle metalink doc (Doc ID 358151.1) to fix the error.  Whenever I try to do the first step (dropping the existing subscriber), it gives this error:

  ERROR at line 1:
  ORA-04020: deadlock detected while trying to lock object
  30x0C2ABE5980x0C798B3F80x0D9CA49D8
  ORA-06512: at "SYS.DBMS_AQADM_SYS", line 7541
  ORA-06512: at "SYS.DBMS_AQADM", line 441
  ORA-06512: at line 5

I have to fix ORA-04020 so I can later recreate the existing subscriber (by dropping the the existing one, then adding a new one).

I tried to bounce the applications and the database.
I compiled the apps schema.
I tried to find out where is the lock and which schema is blocking the other so I can kill that session:

sql> SELECT
 c.owner,
 c.object_name,
 c.object_type,
 b.SID,
 b.serial#,
 b.status,
 b.osuser,
 b.machine

FROM
     v$locked_object a ,
     v$session b,
     dba_objects c
  WHERE
     b.SID = a.session_id
  AND
     a.object_id = c.object_id;

Output:

 OWNER    OBJECT_NAME         OBJECT_TYPE  SID       SERIAL#     STATUS
-----   -------------         ----------   ------    -------    -----------
    APPLSYS  FND_CONCURRENT_QUEUES    TABLE     152       3                INACTIVE
    APPLSYS  FND_CONCURRENT_REQUESTS  TABLE     466       7                INACTIVE

Then tried to find out which session is blocking the other to kill it:

sql>SELECT l1.SID  ||' IS BLOCKING '|| l2.SID
FROM v$lock l1, v$lock l2
WHERE l1.BLOCK =1 AND l2.request > 0
AND l1.id1=l2.id1
AND l1.id2=l2.id2;

But I got no returned rows.

Some of the suggestions on the Internet state that a deadlock occurs when there are invalid objects in the database. So, I recompiled the invalid objects by running $ORACLE_HOME/rdbms/admin/utlrp.sql

That script returned an output. The following is a part of the output:

  ERROR at line 1:
      ORA-04063: package body "SYS.UTL_RECOMP" has errors
      ORA-06508: PL/SQL: could not find program unit being called: "SYS.UTL_RECOMP"
      ORA-06512: at line 4

and this was part of the output also:

OBJECTS WITH ERRORS
-------------------
                  1

Note: I am facing this issue after applying a patch. And it's been over 5 days since the deadlock was detected, even though most of the suggestions regarding to the deadlock says that oracle will manage the release itself. Explicit release not required.

Any idea on how to solve this issue?

I will write the solution here for those who might encounter such error in the future.

I solved the error by re-compiling invalid objects.


Query Performance Help A brief history.  I have an oracle sql query which I'm looking to pull data when a certain preference is enabled.  In this query I had a date range to filter the data to a specific range.  This was changed due to security of the service account having access to specific tables.  My DBA suggested a view.  This query is for this view, but currently takes several minutes to populate.  Any suggestions on how to optimize this query?

SELECT
(SELECT o.SALESFORCE_ID from org_unit o WHERE o.ORG_UNIT_ID = a.vendor_id AND o.delete_flag = '0') as SenderId,
(SELECT o.SALESFORCE_ID from org_unit o WHERE o.ORG_UNIT_ID = a.CUSTOMER_ID AND o.delete_flag = '0') as ReceiverId,
a.invoice_id,
a.INVOICE_DATETIME
FROM invoice a
WHERE a.invoice_id IN (
    SELECT document_id
    FROM oi_action oi
    WHERE oi.action = 'Submit'
    and oi.status='Submitted'
    )
and a.sub_type not in ( 10008,10007)
and EXISTS (
    SELECT 1
    FROM org_preference op
    WHERE op.ORG_UNIT_ID = a.customer_id
    AND op.PREFERENCE_TYPE_ID = 60276
    AND op.preference_value = 1
    AND op.modified_by_id IS NULL
    )
AND EXISTS (
    SELECT 1
    FROM org_unit o
    WHERE o.ORG_UNIT_ID = a.vendor_id
    AND o.delete_flag = '0'
    AND o.salesforce_id IS NOT NULL
)

GROUP BY a.vendor_id, a.customer_id, a.invoice_id, a.invoice_datetime;

If there is a way to only display the view when the preference is turned on
example:

AND a.CREATED_DATETIME >= (SELECT o.CREATED_DATETIME
FROM org_unit o
WHERE o.ORG_UNIT_ID = a.vendor_id
AND o.delete_flag = '0'
AND o.salesforce_id IS NOT NULL
)

Any thoughts suggestions on the best route I can take?

Here is the Explain plan:

UPDATE:

Here is my new query which displays MUCH faster, within a second:

SELECT
sender.salesforce_id as SenderId,
buyer.salesforce_id as ReceiverId,
a.invoice_id,
a.INVOICE_DATETIME
FROM invoice a
INNER JOIN org_unit sender on sender.org_unit_id = a.vendor_id
INNER JOIN org_unit buyer on buyer.org_unit_id = a.CUSTOMER_ID
WHERE EXISTS (
    SELECT 1
    FROM oi_action oi
    WHERE oi.document_id = a.invoice_id
    AND oi.action = 'Submit'
    AND oi.status='Submitted'
    )
and a.sub_type not in ( 10008,10007)
and EXISTS (
    SELECT 1
    FROM org_preference op
    WHERE op.ORG_UNIT_ID = a.customer_id
    AND op.PREFERENCE_TYPE_ID = 60276
    AND op.preference_value = 1
    AND op.modified_by_id IS NULL
    )
AND EXISTS (
    SELECT 1
    FROM org_unit o
    WHERE o.ORG_UNIT_ID = a.vendor_id
    AND o.delete_flag = '0'
    AND o.salesforce_id IS NOT NULL
);

On the explain plan the only difference is in the Nested Loops from 20944 to 15365.

If this looks okay I think this has solved my problem

@ehubba, welcome to stackexchange!

Here are some considerations:

Is there a reason for the GROUP BY?
Why do you use one IN and two EXISTS? Generally you can use IN for smaller and EXISTS for larger results. IN will result in lots of ORs which is not the best idea in large numbers.
I don't like the two sub-selects at the beginning of the statement. Can you replace them with joins?

If you want to improve performance, there are a lot more things to consider...

Are your statistics up-to-date?
constraints help the optimizer to find the best join order
indexes? (please post the explain plan!)

When I write SQL statements I always ask myself "how can I minimize the resultset as fast and efficient as possible".


Easiest way of porting data into a database Recently, I received a lot of information from one of my friends who made a quiz type program.  He asked me to help port it into an Access database.

His program places all the info in a grid; around 3000 fields full of info, and we need to port it over to Access.

Is there a program that will read field-by-field and insert the data into Access, and then automatically continue to the next field?

The grid also has a copy button, but it copyies everything without grid, so every line looks kinda like this:

Info1:herestheinfo Info2:herestheinfo, and so on, is there any easy way of porting this to an Access database?

You can use Microsoft Access' built-in import features to import data from just about any source.

For Access 2007, the "ribbon" menu has an "External Data" tab, where you can select "Text".

From there it is pretty simple to import text-based data.  As you can see from the screenshot, there are a number of other types of data you can import.


How implement CASE statement in query I stuck in here.

How to implement:

CASE
WHEN m.MAXDATE is not null THEN '*'
WHEN m.MAXDATE IS NULL THEN ' '
END

In query:

SELECT Ename, Hiredate, m.MAXDATE[MAXDATE]
FROM EMP
LEFT JOIN (Select max(Hiredate)[MAXDATE]
FROM EMP) m
on EMP.HIREDATE = m.MAXDATE

I've checked few website in order to find the answer, but it's giving me errors in every variant.

More details:

Query is:

SELECT Ename, Hiredate, m.MAXDATE[MAXDATE]
FROM EMP
LEFT JOIN (Select max(Hiredate)[MAXDATE]
FROM EMP) m
on EMP.HIREDATE = m.MAXDATE

This is a result:

SMITH   1980-12-17 00:00:00.000 NULL
ALLEN   1981-02-20 00:00:00.000 NULL
WARD    1981-02-22 00:00:00.000 NULL
JONES   1981-04-02 00:00:00.000 NULL
MARTIN  1981-09-28 00:00:00.000 NULL
BLAKE   1981-05-01 00:00:00.000 NULL
CLARK   1981-06-09 00:00:00.000 NULL
SCOTT   1982-12-09 00:00:00.000 NULL
KING    1981-11-17 00:00:00.000 NULL
TURNER  1981-09-08 00:00:00.000 NULL
ADAMS   1983-01-12 00:00:00.000 1983-01-12 00:00:00.000
JAMES   1981-12-03 00:00:00.000 NULL
FORD    1981-12-03 00:00:00.000 NULL
MILLER  1982-01-23 00:00:00.000 NULL

I want to just put '*' instead of date in last column for ADAMS, and space ' ' where its NULL.

Your syntax for the CASE expression is correct (it's an expression, not a statement!) You can simplify it slightly by removing the second WHEN:

CASE WHEN m.MAXDATE IS NOT NULL THEN '*' ELSE '' END

and the query:

SELECT Ename, Hiredate,
       CASE WHEN m.MAXDATE IS NOT NULL THEN '*' ELSE '' END AS MAXDATE
FROM EMP
  LEFT JOIN (SELECT max(Hiredate) AS MAXDATE
             FROM EMP) m
  ON EMP.HIREDATE = m.MAXDATE ;

And you don't really need the self-join. You could have the same result with a window aggregate function:

SELECT Ename, Hiredate,
       CASE WHEN HIREDATE = max(HIREDATE) OVER ()
           THEN '*' ELSE '' END AS MAXDATE
FROM EMP ;


Fastest Way to Move Data from Current Database for Multiple Tables to New Database? This is my first question and post so let me know if I can improve.  I'll err on the side of providing too much information.

I'm a fairly new DBA at my company, less than two years with the DBA team, and have been tasked with coming up with a way to move data from an existing database for one specific entity to a new database which does not yet exist.  The database is on an instance running SQL Server 2012 SP1 Standard edition.

We currently have a custom PowerShell cmdlet called purge-entitydata.  This cmdlet will perform a delete in all 160 user tables in the current database based upon the entity value.

Example

purge-entitydata -entity <Entity>

However, before we delete all of the entity's data in the current database, we need to move only that entity's data to a new temporary database called [Entity_Backup] in case a downstream process fails.

Here are some caveats:

The 160 user tables may change several times a year.  Some may be deleted or new ones may be added.  I was thinking of going the SSIS route but didn't want to have to update the package with a new data flow task everytime a new table is added or removed.
Just the data and the tables themselves need to be moved.  I won't need to move views, stored procedures, or indexes from the current database to temporary database.
The current database and user tables contain data for over a thousand entities.  I just need to move the data for one entity over to the temporary database.
If the downstream process succeeds, then the temporary database will get dropped.
The database is large, over 200 GB.  Also, this server is the publisher for a pull merge replication subscription and is also the publisher for two transaction replication subscriptions. I'm thinking that taking a backup would slow replication too much.

I was thinking of using sp_msforeachtable 'SELECT * INTO [Entity_Backup].? FROM ? WHERE Entity = $Entity' and Invoke-Sqlcmd but am worried this would take too long since it does not run in parallel.  An entity has approximately three to five million rows.

What be the most efficient/fastest way to move data from the 160 user tables to a new database which can be done from inside the PowerShell cmdlet? Does anyone have any suggestions which may be faster than ms_foreachtable?

You say that you're worried that your proposed solution won't work because it won't run in parallel. You already have a PowerShell cmdlet which deletes from all 160 tables, right? Deleting rows can often take longer than inserting them, especially if you have indexes on the table that you're deleting from. If that cmdlet doesn't run in parallel then why do you need the insert commands to run in parallel? I recommend starting with a relatively simple solution, doing performance testing, and only making it more complex if necessary.

I'm not familiar with the restrictions of what can be run in PowerShell. However, it appears to be possible to execute a stored procedure from PowerShell , so I will provide a T-SQL solution. You should be able to easily translate that into what you need, but I may refer to the code as a "stored procedure".

If your database is about 200 GB and you have about 1000 entries then on average you only need to move 200 MB of data. That's definitely doable if you have suitable indexes on the tables (such as one on the Entity column). I'm going to assume that you already did that analysis and you have the indexes that you need to speed up the SELECT statements.

1) Create the database for the inserts

I recommend that you create your temporary database with a recovery model of simple. That will reduce data written to the transaction log for minimally logged queries. I also see no advantage in using a different recovery model because the data will be so short-lived. I also recommend that you create a database with the right settings and just keep it around. Grow the data and log files to appropriate sizes to prevent auto-growth events. You can just drop all of the tables in the database as they are no longer needed. Do you really need to go through the work each time of creating and dropping the database?

2) Figure out your DML strategy

I can think of three different ways to insert data into the temporary database. Two of them require changes to your delete applet.

All of them insert into heaps and take advantage of minimal logging. Since you just need the data and nothing else that will minimize the overhead of doing the inserts. On SQL Server 2012 you cannot do parallel inserts. You can do them if you upgrade to SQL Server 2014 and use SELECT INTO.

The first method uses the OUTPUT clause to delete the rows from the table and insert the deleted rows in a single query. This will likely be the fastest way because you only need to read the underlying data once per table. You can't use SELECT INTO for the insert statement (but you can run one beforehand to create the target table). Your query could look something like this:

INSERT INTO [other_db]..[Entity_Table_1] WITH (TABLOCK)
SELECT t.*
FROM
(
    DELETE FROM [source_db]..[Entity_Table_1]
    OUTPUT deleted.*
) t;

The second method is similar to the first but you process the insert query for a table right before the delete query. The insert query will in theory move the data that you need from the table into the buffer cache. That will reduce the number of trips to disk that your code needs so it will run faster. This will require changing the code of your delete applet.

The third method is just doing inserts. For this method you won't need to modify the delete applet. If you don't use SELECT INTO you'll want to use the TABLOCK hint to get minimal logging.

3) Figure out the method and order of looping

I don't recommend using sp_MSforeachtable. It's undocumented and could be changed or removed by Microsoft at any time. Instead, you could use a cursor to loop over relevant tables from sys.tables. There should be plenty of examples on how to do that online, but let me know if you need further help.

The order that you process the tables may matter. For example, suppose that you need to have separate applications for the delete statements and the insert statements. If you process the tables in the same order in both procedures then it's possible that the data for the current table will be pushed out of the buffer cache before you process it again. That's because you're querying 159 other tables before you query the same table again. If you did something process the tables in the opposite order that you delete from them, you may reduce physical reads when reading the data.

4) Implement parallelism if necessary.

After testing you may find that your code is too slow and that you need to run multiple concurrent insert statements. For one way to do this, first make the stored procedure always loop over the tables in a deterministic order. Add two input parameters to the procedure. The first should be the total number of threads and the second should be the thread for that procedure call. If you called the procedure with 4 total threads and the active thread as 1 then that procedure would process the 1st, 5th, 9th, 13, ... tables. For N threads you could open N PowerShell windows and change the second parameter for each.

Happy inserting!


MongoDB skipping 20 documents when storing in a variable ids = db.students.find({}, {
    _id: 1
});

It gives the length of ids as 2993 while students count is 2413. It's not storing the 20 documents which are being displayed on the shell.

The shell does not actually return just 20 records, what you get returned when you run a find like the one you list in the shell is a cursor, the shell then by default iterates over and displays the first 20 documents returned by that cursor (for convenience).  For example:

> db.foo.find({}, {_id : 1})
{ "_id" : ObjectId("573c4afbaa6cd35206c16a8d") }
{ "_id" : ObjectId("573c4afbaa6cd35206c16a8e") }
{ "_id" : ObjectId("573c4afbaa6cd35206c16a8f") }
{ "_id" : ObjectId("573c4afbaa6cd35206c16a90") }
{ "_id" : ObjectId("573c4afbaa6cd35206c16a91") }
{ "_id" : ObjectId("573c4afbaa6cd35206c16a92") }
{ "_id" : ObjectId("573c4afbaa6cd35206c16a93") }
{ "_id" : ObjectId("573c4afbaa6cd35206c16a94") }
{ "_id" : ObjectId("573c4afbaa6cd35206c16a95") }
{ "_id" : ObjectId("573c4afbaa6cd35206c16a96") }
{ "_id" : ObjectId("573c4afbaa6cd35206c16a97") }
{ "_id" : ObjectId("573c4afbaa6cd35206c16a98") }
{ "_id" : ObjectId("573c4afbaa6cd35206c16a99") }
{ "_id" : ObjectId("573c4afbaa6cd35206c16a9a") }
{ "_id" : ObjectId("573c4afbaa6cd35206c16a9b") }
{ "_id" : ObjectId("573c4afbaa6cd35206c16a9c") }
{ "_id" : ObjectId("573c4afbaa6cd35206c16a9d") }
{ "_id" : ObjectId("573c4afbaa6cd35206c16a9e") }
{ "_id" : ObjectId("573c4afbaa6cd35206c16a9f") }
{ "_id" : ObjectId("573c4afbaa6cd35206c16aa0") }
Type "it" for more

Note the last line, if you type it you will get 20 more results, and you can repeatedly do so until you reach the end of the results and exhaust the cursor.

When you assign the result of a query to a variable you are storing the cursor in that variable, not the results, and no iteration is done unless you call it.

The reason for the differences in your count is not because of any skipping, it is probably simply because you have 2413 documents with a student field and 580 without a student field for a total of 2993.  You haven't shared your actual doc structure, but to illustrate with a basic example:

// insert 2413 docs with a student field, 580 without
for(var i = 0; i < 2413; i++){db.foo.insert({student : i})};
for(var i = 0; i < 580; i++){db.foo.insert({})};
// now do a count of all records
db.foo.count()
2993
// count only those with student field
db.foo.count({student : {$exists : true}})
2413


MySQL running fine while data files (disk) are not present. Data folder nowhere to be found I'm running MySQL server 5.6 on Windows 2012 R2, and since the installation didn't go as planned, I had to re-install it to change the data directory path.
The installer for some reasons never gave the option back to change the directory upon installation, despite multiple restarts.

I have re-installed MySQL anyway, stopped the service, deleted the "data" folder, and tried changing basedir through my.ini. It never worked. When I type "SHOW VARIABLES WHERE Variable_Name LIKE "%dir"" through SQL it returns that the data folder (datadir) is still "C:\ProgramData\MySQL\MySQL Server 5.6\Data\".

I know I shouldn't have deleted the data folder, but I figured they would create a fresh new DB when I delete it.

What's weird is that my database is working probably through some sort of cache.
I backed up my database through phpmyadmin, and restarted the server machine to see if the database is gone because the data folder is missing.
Nope, I was wrong. The database is still there with no data loss! And the data folder is still missing!

How is this even possible?
Where is the database being stored right now then?. How could I force MySQL server to write what's in the cache into the disk?

In Linux, the MySQL process will keep the files open until it's done with them, even if you delete the files. This can be viewed with lsof -p $(pidof mysqld)

I suspect this is true in Windows as well, as your question indicates. A quick Google indicated something about handler process keeping files open, but don't quote me!

Regardless, the bad news is you can't force MySQL to write it to disk. The data will be gone as soon as you either a) stop/start service or b) issue a FTWRL as is common in many backup strategies

The good news is you can still get the data back if you mysqldump it out.

My recommendation is:

mysqldump data
verify dump is good
restore into a different server to verify it worked
swap out datadir depending on your maintenance window requirements; eg: copying files will be faster than importing a mysqldump file on large datasets.


Unsent log behaviour in database mirroring Need your suggestion on how below works.

Situation: We had unsent log on Principal stuck for almost 10 hours with unsent data around 200 GB.

Action: Paused and resumed the mirroring. This kicked of the stuck mirroring. All 200 GB is now send to mirror and its trying to restore.
But when new transactions are showing up on principal i see that again the records or mirror seems to stuck showing send rate 0 KB/sec.

So my question would be , is this suppose to happen and block unsent log on P till all previous 200 GB is restored on Mirror or there could be different problem here? And can we again PAUSE and RESUME mirroring if mirroring is still synchronizing ?

Please suggest

So my question would be , is this suppose to happen and block unsent log on P till all previous 200 GB is restored on Mirror or there could be different problem here? And can we again PAUSE and RESUME mirroring if mirroring is still synchronizing ?

You might be experiencing a network issue. The PAUSE and RESUME of mirroring should just be your workaround.

Check with your network admin on your network bandwidth and latency (shared network with other apps? 1Mbps NIC?). This can build up your Unsent Log and can adversely impact performance of your database mirroring. 200GB is huge amount of data. You could loss 200GB data. You don't want that to happen. Consider setting up Threshold Alert for Log Send Queue KB. If it reaches 1GB, send an email alert and take action. This way you can minimize your data loss.

Read this KB Article on things to consider when setting up Database Mirroring in SQL Server. The 2 main important points about network:

  To mitigate the impact of network latency on the database mirroring throughput, you should consider the following:

  Gigabit network cards
  Dedicated NICs for mirroring.

Don't wait for 10hrs or 200GB of unsent data, setup an alert and be proactive.

Additionally, consider migrating/upgrade to SQL Server 2016/2017. Use Availability Groups (better than mirroring and faster) because Database Mirroring is now deprecated by Microsoft and you won't be able to get support or updates from them (except you paid for extended support). If you run into Database Mirroring bug it is unlikely Microsoft will patch it for you. You can read the SQL Server 2008 lifecycle support here.