how does SQL Server 2008 handle joins internally? for example, I have tables: User and Order

User Table:
UserId, Name, Address, Phone, Age

Order Table:
OrderId, UserId, Date, Item, Quantity

SQL:

select User.Name, Order.Date
From Order
JOIN User ON Order.UserId = User.UserId

Will the join produce a virtual table including all fields in the tables or just the two in the select?

The point to this question is if one of my fields is large (> 1GB) then I would like to place it in its own table and not leave it in  a table that gets joined many times all over the place... in order to keep the large fields from being a burden across the board.

The internals of any query operation can be reviewed in the query execution plan.  There are many factors involved in how the data is extracted and queried from the source tables, but for the purposes of your question, there are two elements you need to be concerned about:

Seeks and Scans

The first step to any query is to extract the data from the source tables.  This is performed using one of two actions (and variations within): a seek or a scan.  A seek is where the query engine can leverage an index to narrow down the data retrieval.  If no index is available, then the engine must perform a scan of all records in the table.  In general, seeks typically perform better than scans due because it is more selective.

Physical Joins

Once data is retrieved from the tables, it must be physically joined.  Note, this is not the same as logical joins (INNER and OUTER).  There are three physical join operators that can be used by the engine:

Nested Loop Join
Merge Join
Hash Join

The query engine will evaluate several factors to determine which physical join will be the most efficient for the query execution.  As a simple example, I assembled this SQL Fiddle example, where you can view a basic execution plan using your sample schema. Note, however, that this plan could change with more data being queried.  You will need to review the query plans in your environment to truly understand how your data is being retrieved.

Query plan analysis and performance tuning is beyond the scope of this answer, but I strongly recommend getting a hold of Grant Fritchey' SQL Server 2012 Query Performance Tuning for a comprehensive explanation of query execution internals and tuning approaches.


How to use a record variable in the from clausule I write a SQL function that receives and return a record. I need to use the received record in a from clause. I am using unnest(array[the_variable]).

Is there a specific function or structure to do this? (something like record_to_recordset(the_variable))

Here is my example:

create type calculate_info_type as (super_code text, super_name text);

create function calculate_info(p_country country)
   returns calculate_info_type
   language SQL as
$SQL$
   SELECT super_code, super_name
     FROM unnest(array[p_country]), -- <=== HERE!!
          lateral (select
            prefix is not null as has_prefix
          ) g1,valores que tenga la tabla
          lateral (select
            case has_prefix when true then iso||'-'||prefix else iso end as super_code,
            case has_prefix when true then name else name||' without phone prefix' end as super_name
          ) g2
$SQL$;

I put a complete running example here: https://rextester.com/XIRSW33377

Short answer (edited)

You can write FROM (select p_country.*) some_alias instead of FROM unnest(array[p_country])

Question code with minimal corrections: https://rextester.com/UGKJR74935

Original answer (with many usefull suggestions)

Looks like you want something like this:

create function calculate_info(p_country country)
   returns calculate_info_type
   language SQL as
$SQL$
   select case when has_prefix then iso||'-'||prefix else iso end as super_code,
          case when has_prefix then name else name||' without phone prefix' end as super_name
   FROM (
      select (p_country).*, p_country.prefix is not null as has_prefix
   ) as c
$SQL$;

But you don't really need the from at all:

create function calculate_info(p_country country)
   returns calculate_info_type
   language SQL
as
$SQL$
   select concat_ws(',', p_country.iso, p_country.prefix) super_code,
          case
            when p_country.prefix is not null then p_country.name
            else p_country.name||' without phone prefix'
          end as super_name;
$SQL$;

concat_ws will return only the value from the iso field if prefix is null.

You don't need a lateral to use that function:

select iso, prefix, name, (calculate_info(country)).*
from country

Your updated example: https://rextester.com/GVLK52206


Should I avoid adding a clustered key to a table with high throughput We have a SQL Server solution that has a table dsStaging.Audit that stores audit records created by a third party transactional database.  We use these audits to synchronise CRUD operations from the third party system into our SQL database.

CREATE TABLE [dsStaging].[Audit](
    [SyncExecutionId] [bigint] NOT NULL,
    [AuditDataGuid] [nvarchar](56) NOT NULL,
    [AuditDate] [datetime] NOT NULL,
    [AuditDateTimeZone] [datetimeoffset](3) NULL,
    [AuditEventGroup] [nvarchar](56) NOT NULL,
    [TransactionId] [bigint] NOT NULL,
    [TransactionSequence] [int] NOT NULL,
    .
    ...
    .
 CONSTRAINT [PK_Audit] PRIMARY KEY CLUSTERED
(
    [SyncExecutionId] ASC,
    [TransactionId] ASC,
    [TransactionSequence] ASC
) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF,
    IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
)

When the audits are processed, I want to move the audit records into a separate table Processed.Audit, ready for deletion after x days.

CREATE TABLE [Processed].[Audit](
    [SyncExecutionId] [bigint] NOT NULL,
    [AuditDataGuid] [nvarchar](56) NOT NULL,
    [AuditDate] [datetime] NOT NULL,
    [AuditEventGroup] [nvarchar](56) NOT NULL,
    [TransactionId] [bigint] NOT NULL,
    [TransactionSequence] [int] NOT NULL,
    .
    ...
    .
 CONSTRAINT [PK_Processed_Audit] PRIMARY KEY NONCLUSTERED
(
    [AuditDate] ASC
) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF,
    IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
)

My main aim in moving audits out of staging and into processed is performance.  I need to make sure that the staging table is locked for as short a time as possible, so that any unprocessed audits can be processed as quickly as possible (fewer audits in the staging table = much faster processing).

We're looking at around 1.5m audit records going through this process every hour in batches of about 10k.

The process for moving audits will fire roughly every 20-30 seconds.  The process for deleting Processed.Audit records will execute every hour and delete 1 hour's worth of audits from X days ago (typically around 7 days).

Should I turn the Processed.Audit table into a clustered index?

Minimum version to be supported: SQL Server 2012 Standard Edition.

The main reason I would want a clustered index in this scenario is this line:

  The process for deleting process.Audit records will execute every hour and delete an hours worth of audits from x days ago (typically around 7 days)

When you delete rows from a HEAP, data pages may not be deallocated unless the delete gets a table lock, or you provide a WITH (TABLOCK) hint to the delete query. You can probably imagine what that does to concurrency, though. Not good.

Note that the TABLOCK hint will not have this behavior if you're using RCSI or Snapshot Isolation.

Here's a quick example. Load up a small table:

USE tempdb;
SET NOCOUNT ON;

CREATE TABLE dbo.heap
(
    id INT PRIMARY KEY NONCLUSTERED,
    junk VARCHAR(1000)
);

INSERT dbo.heap (
    id, junk )
SELECT TOP 1000 x.n, REPLICATE('A', x.n % 1000)
FROM   (
           SELECT ROW_NUMBER() OVER ( ORDER BY @@ROWCOUNT ) AS n
           FROM   sys.messages AS m ) AS x;

Run a sanity check query to figure out how many pages are assigned to the heap, and to the nonclustered PK:

SELECT   OBJECT_NAME(i.object_id) AS table_name,
         i.name AS index_name,
         MAX(a.used_pages) AS leaf_me_alone
FROM     sys.indexes AS i
JOIN     sys.partitions AS p
ON p.object_id = i.object_id
   AND p.index_id = i.index_id
JOIN     sys.allocation_units AS a
ON a.container_id = p.partition_id
WHERE OBJECT_NAME(i.object_id) = 'heap'
GROUP BY i.object_id, i.index_id, i.name
ORDER BY OBJECT_NAME(i.object_id), i.index_id;

Results in this:

table_name  index_name  leaf_me_alone
heap        NULL        74
heap        PK__heap__  7

So, 74 pages in the heap, 7 pages in the NC PK.

Do some singleton deletes to clear out the table:

DECLARE @i INT = 1;

WHILE @i < 1000
    BEGIN

        DELETE h
        FROM  dbo.heap AS h
        WHERE h.id = @i;

        SET @i += 1;

        PRINT @i;

    END;

If you re-run the sanity check query, you'll get the same result.

Worse, if you query the table now, SQL will read ALL OF THOSE BLANK PAGES!

SET STATISTICS TIME, IO ON
SELECT *
FROM   dbo.heap AS h;

  Table 'heap'. Scan count 1, logical reads 67

So now not only is our table artificially large, but SQL now has a bunch of blank pages on disk and in memory and in backups and in DBCC CHECKDB and... well, you get the point.

  We're looking at around 1.5m audit records going through this process every hour

Heh heh heh! No fun.

Other options for getting pages deallocated from the heap are:

TRUNCATE TABLE dbo.heap

Which doesn't work for you, because you need to batch delete data.

ALTER TABLE dbo.heap REBUILD;

Which would be painful for you at that table size, because it will rebuild all nonclustered indexes on the table at the same time.

Will the table re-use pages? Sometimes maybe sorta kinda.

DECLARE @id_max INT = (SELECT MAX(id) FROM dbo.heap AS h);

INSERT dbo.heap (
    id, junk )
SELECT TOP 5000 x.n + @id_max, REPLICATE('A', x.n % 1000)
FROM   (
           SELECT ROW_NUMBER() OVER ( ORDER BY @@ROWCOUNT ) AS n
           FROM   sys.messages AS m ) AS x;

Sanity check:

table_name  index_name  leaf_me_alone
heap        NULL        400
heap        PK__heap__  20

SELECT * query:

  Table 'heap'. Scan count 1, logical reads 392

Hope this helps!


SQL How to get multiple values into one column (MS SQL) I have the following Tables:

    CREATE TABLE #table1
    (Batchnumber INT NOT NULL,
     BOXID       TINYINT NOT NULL,
     ErrorID     TINYINT NULL,
     Weight      INT);

    CREATE TABLE #table2
    (ErrorID     TINYINT,
     Description VARCHAR(100));

    INSERT INTO #table1(Batchnumber, BOXID, ErrorID, Weight)
    VALUES (12345, 1, NULL, 1000),
    (12345, 2, 1, 1000),
    (12345, 3, 2, 1000);

    INSERT INTO #table2
    (ErrorID, Description)
    VALUES (1, 'Network Error'),
    (2, 'Keyboard to chair interface problem'),
    (3, 'Unknown Error');

I need to have the following in the temp table

TempTable
Batchnumber | Weight | Remarks
12345         3000     Network Error, Keyboard to chair interface problem

So he needs only 1 row with the Batchnumber, the sum of the weight for all 3 records and 1 field with all the error codes explained

Can someone help me with this please?

The FOR XML PATH solution for creating csv from rows tend to be the most common for 2016 and lower.

WITH cte
     AS (SELECT one.Batchnumber,
                one.Weight,
                two.Description
         FROM #table1 AS one
         LEFT OUTER JOIN #table2 AS two
              ON one.ErrorID = two.ErrorID)

SELECT Batchnumber,  SUM(Weight),
            STUFF((SELECT ',' + inr.Description
         FROM cte AS inr
         WHERE outr.Batchnumber = inr.Batchnumber FOR XML PATH(''), TYPE
     ).value('.', 'VARCHAR(MAX)'), 1, 1, SPACE(0))
FROM cte AS outr
GROUP BY Batchnumber;

In 2017 there is the STRING_AGG function which makes this easier.

WITH cte
     AS (SELECT one.Batchnumber,
                one.Weight,
                two.Description
         FROM #table1 AS one
         LEFT OUTER JOIN #table2 AS two
              ON one.ErrorID = two.ErrorID)

SELECT  Batchnumber,SUM(Weight),STRING_AGG(Description,',')
FROM    cte
group by Batchnumber;


Updating Multiple Tables with single Update query I have two tables TableA, TableB as follows.

Table "db3004db.tableb"

 Column  |  Type   | Collation | Nullable | Default
---------+---------+-----------+----------+---------
 empid   | integer |           | not null |
 salaryb | integer |           | not null |

Indexes:
    "tableb_pk" PRIMARY KEY, btree (empid)

Table "db3004db.tablea"

 Column |  Type   | Collation | Nullable | Default
--------+---------+-----------+----------+---------
 empid  | integer |           | not null |
 salary | integer |           | not null |

Indexes:
    "tablea_pk" PRIMARY KEY, btree (empid)

Before Update:

select * from tableA;

 empid | salary
-------+--------
     1 |   1004
     2 |   2007

select * from tableB;

 empid | salaryb
-------+---------
     1 |       0
     2 |       0

Expected Update Query:

set tableB.salary=tableA.salary%1000,
    tableA.salary= tableA.salary/1000
where (tableA.pk = tableB.pk)

After update:

select * from tableA;

 empid | salary
-------+--------
     1 |   1
     2 |   2

select * from tableB;

 empid | salaryb
-------+---------
     1 |       4
     2 |       7

I'm interested in doing this update in Postgres (with single update statement)? If possible , is this query compatible with SQL standard?

WITH cte
AS ( UPDATE tableB
     SET salaryb = tableA.salary % 1000
     FROM tableA
     WHERE tableB.empid = tableA.empid
     RETURNING *
   )
UPDATE tableA
SET salary = salary / 1000;


Flatten array with OPENJSON: OPENJSON on a value that may not be an array? [ [1] ], vs [1] Frequently in Javascript you'll have something like

[ 7,2, [6,7], 2,10 ]

How would you query that structure with OPENJSON I would like this,

0, 7
1, 2
2, 6,
2, 7,
3, 2
4, 10

I'm having a hard time conditionally unwrapping that JSON array.

Sample Data

declare @ex nvarchar(max) = '[ 7,2, [6,7], 2,10 ]';

My attempt

Find my query here

SELECT *
FROM OPENJSON(@ex, '$') AS j1

This gets you to,

key value   type
0   7       2
1   2       2
2   [6,7]   4
3   2       2
4   10      2

If I try to CROSS APPLY OPENJSON(j1."value", '$') AS j2;, I get an error about the 7, the first non-array being an invalid array,

  Msg 13609 Level 16 State 4 Line 4

  JSON text is not properly formatted. Unexpected character '7' is found at position 0.

How do I use CROSS APPLY OPENJSON to conditionally unwrap the rows that are arrays (type=4) while leaving alone non-arrays (in the above like type=2)? I don't want that [6,7] in there. I want two rows with key=2 that have values 6, and 7 respectively.

If you are strictly confident that your nested arrays will never go deeper than N levels, you can completely unwrap the array-of-arrays with N uses of APPLY. If you need to handle for arbitrary nesting levels, you can unwrap the array-of-arrays recursively using something like the following, which will produce output similar to the following

|----|-------|---------|-----|-------|------|
| id | level | path    | key | value | type |
|----|-------|---------|-----|-------|------|
| 1  | 1     | /0      | 0   | 7     | 2    |
| 1  | 1     | /1      | 1   | 2     | 2    |
| 1  | 2     | /2/0    | 0   | 6     | 2    |
| 1  | 2     | /2/1    | 1   | 7     | 2    |
| 1  | 1     | /3      | 3   | 2     | 2    |
| 1  | 1     | /4      | 4   | 10    | 2    |
|----|-------|---------|-----|-------|------|

DB Fiddle

declare @ex table (
    i int identity primary key,
    r nvarchar(max)
);
insert @ex (r)
values ('[ 7,2, [6,7], 2, 10 ]')
      ,('[ 7,2, [6,7], 2, 10 , [6,[7,8]]]')
;

with j as (
    select
        x.i as id,
        convert(int,1) as [level],
        convert(nvarchar(max),N'/')+convert(nvarchar(max),a.[key]) as [path],
        a.[key],
        a.[value],
        a.[type]
    from @ex x
    cross apply openjson(x.r) a
    union all
    select
        j. id,
        j.[level] + 1,
        j.[path] + convert(nvarchar(max),N'/')+convert(nvarchar(max),a.[key]),
        a.[key],
        a.[value],
        a.[type]
    from j
    cross apply openjson(j.value) a
    where j.type in (4,5)
       and j.level < 50 --maxrecursion
)
select *
from j
where type not in (4,5)
order by id, [path];

Testing against the 2-level deep nested array from the above example produces the following:

|----|-------|---------|-----|-------|------|
| id | level | path    | key | value | type |
|----|-------|---------|-----|-------|------|
| 2  | 1     | /0      | 0   | 7     | 2    |
| 2  | 1     | /1      | 1   | 2     | 2    |
| 2  | 2     | /2/0    | 0   | 6     | 2    |
| 2  | 2     | /2/1    | 1   | 7     | 2    |
| 2  | 1     | /3      | 3   | 2     | 2    |
| 2  | 1     | /4      | 4   | 10    | 2    |
| 2  | 2     | /5/0    | 0   | 6     | 2    |
| 2  | 3     | /5/1/0  | 0   | 7     | 2    |
| 2  | 3     | /5/1/1  | 1   | 8     | 2    |
|----|-------|---------|-----|-------|------|

See this question also for related material.


Is Reorganizing Indexes required after ShrinkDatabase or can I just reogranize index with fragmentation more than 10%? I am a new DBA to a SQL 2005 production Report server. My predecessor created a Maintenance Plan with 4 tasks: CHECKDB; SHRINKDATABASE (N'DB1',10,TRUNCATEONLY); it seems to be running a REORGANIZE on all Indexes for all Tables and Views and "Compact Large Objects" is checked; and then it UPDATE STATISTICS for all tables. There is also a separate job that runs after the Maintenance Plan that if the fragmentation of the same DB1 Indexes is more than 10% they are reorganized. It seems redundant. If they were just reorganized why would there need to be a second job? Also wouldn’t the second job be more efficient since it doesn't use up resources for Indexes less than 10%?

I would recommend you to remove -- shrink database, as it will cause more fragmentation. Basically you are doubling your work by performing Shrink database.

Read this : http://www.sqlskills.com/blogs/paul/why-you-should-not-shrink-your-data-files/

Also, I would suggest to use Ola's maintenance scripts which are "the best" out for SQL Server community http://ola.hallengren.com/

You should never reorganize or rebuild all your indexes as its a costly operation and should be done during maintenance window, rather depending on the level of fragmentation
e.g.
less than 10% ==> dont do anything
10 - 30 % ==> do a reorganize
30% an above ==> rebuild


How to generate DDL of an object which a synonym points to? I want to generate the DDL of an object which a synonym points to. Consider the below example.

I have a synonym named USER which points to USER_1 table. I want to generate DDL of the USER_1 table using the synonym as input.

I need to generate for over 400 synonyms, hence searching for a method to retrieve DDL using synonyms.

Thanks in advance.

For tables:

select dbms_metadata.get_ddl('TABLE', table_name)
from user_synonyms
where synonym_name in ...;

For other object types as well:

select dbms_metadata.get_ddl(o.object_type, s.table_name)
from user_synonyms s join user_objects o on (s.table_name = o.object_name)
where s.synonym_name in ...;


Linking Tables Together If I have a table such as this,

CREATE TABLE [dbo].[StreetLight](
    [Id] [int] IDENTITY(1,1) NOT NULL,
    [Description] [nvarchar](50) NOT NULL,
    [Shape] [geometry] NOT NULL,
 CONSTRAINT [PK_StreetLight] PRIMARY KEY CLUSTERED
(
    [Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

GO

What structure is ideal to use to relationally link street lights together. For instance I have street light 1, 2, 3, 4, 5, 6, and I want to say that street light 6 and 2 are related. They might be on the same street for instance.

What are the ways I can do this?

I would recommend adding another table, StreetLightGrouping. This table should have two columns, StreetLightGroupId and StreetLightId.  All the StreetLights under one StreetLightGroupId are part of the same group.

Also, if a StreetLight can belong to a maximum of one group, a unique constraint can be established on the StreetLightId column.


Problem with sp_Updatestats I am having a problem when running sp_updatestats on my database it generates an error when my emp table is updating and disconnects I will paste the error here

Updating [dbo].[emp]
Msg 0, Level 11, State 0, Line 0
A severe error occurred on the current command.  The results, if any, should be discarded.
Msg 0, Level 20, State 0, Line 0
A severe error occurred on the current command.  The results, if any, should be discarded.

Is it because any problem with the table when I run DBCC checktable(emp) , it doesn't showed any problems.Then what will be the cause

Update:
How can we find the root cause of the problem without installing a service pack as mentioned by @gbn ,how can we eliminate it. what if the problem is occurring in version having sp2 installed ,If it  happens in a production server and installing service pack is the only way to resolve it ,as a dba what we can do?

You are running RTM (10.50.1600.1): the latest patch is SP2 + CU3 (10.50.4266)

These include patches like KB 2498786


Installing extension as non superuser on Amazon AWS RDS I would like to allow non super users to install a specific postgres extension. Accordingly to the documentation at https://www.postgresql.org/docs/current/extend-extensions.html that is possible if I set superuser to false  in the extension .control file.

However, in AWS RDS that is not possible, because I don't have access to the filesystem. Would there be a way to set those .control parameters via SQL query? Or did I reach the usability limit that RDS offers me?

Amazon RDS has a custom list of allowed extensions. You can see it with:
SHOW rds.extensions;

See online documentation.
And you should do it as role rds_superuser or as a member of that role (actual superuser is not available). Their website informs:

The rds_superuser role can do the following:

Add extensions that are available for use with Amazon RDS. [...]


PostgreSQL lost connection during massive index operation? What exactly happened? Using PostgresQL 9.4, Ubuntu 14.04, and 32 GB RAM (with modified settings in postgresql.conf file; I think the highest allocation is 16GB).

I ran a ts_vector creation on a text column in a table containing a little more than 1.8 billion rows. It was successful.

However, running an index on the new vector column failed in a strange way.

database_name=# create index tsv_column_idx on table_name using gin(tsv_column);

The query ran for about 56 hours in a screen session, and then I got this message:

FATAL:  terminating connection due to administrator command
server closed the connection unexpectedly
        This probably means the server terminated abnormally
        before or while processing the request.
The connection to the server was lost. Attempting reset: Failed.

Trying another query resulted in:

!> <random query>;
You are currently not connected to a database.

Why would the connection have been lost? Nobody else connected to the database or made updates to the server. Is this an issue with memory allocation?

I ran dmesg | grep -iC 3 "kill" and nothing came up.

It seems like there was some kind of shutdown error... no idea how that occurred though.

2015-10-09 14:55:32 PDT [1506-2] LOG:  received fast shutdown request
2015-10-09 14:55:32 PDT [1506-3] LOG:  aborting any active transactions
...
2015-10-09 14:55:32 PDT [3803-4] postgres@database FATAL: terminating connection
 due to administrator command
2015-10-09 14:55:32 PDT [3803-5] postgres@database STATEMENT:  create index ...;

Checking sudo vim /var/log/apt/term.log, I confirmed that this was caused by a PostgreSQL update hidden in the apt-get upgrade that another user had executed on the system.


Oracle get sql redo from log_history I run into a problem with a customer which had no proper backup.

His last backup (meaning exp-dump) is from 1st Aug 2011.

However he lost due to batch DELETE statements all data.

My plan was now to get the redo-sql from the logs, import the dump and replay all sql from 1st Aug up to 2nd Sept (just before the batch DELETE happened).

However I am stuck because V$LOG_HISTORY shows me logs back to February 2011 but no V$ARCHIVED_LOG entries to know which logs to include into LogMiner.

So: How can i load this V$LOG_HISTORY logs into the LogMiner to get the redo-sql or how else can I get the redo-sql or replay it based on the dump-file fromn 1st Aug?

Side notes: This is a single alone (like an orphant) oracle 9.2 installation on a linux server.

First of all export is not backup. You will not recover a physical copy of a database from exp-dump, only an unconsistent copy of its data (you can make them consistent but the option is rarely used). I'm really not sure you can apply the contents of logMiner on such a copy.

Your best bet would be to apply undo statements to your current copy of the DB to return it to its original state, such as described in the documentation:

  If you fully understand and take into account existing dependencies, it may be possible to perform a table-specific undo operation to return the table to its original state. This is achieved by applying table-specific reconstructed SQL statements that LogMiner provides in the reverse order from which they were originally issued. See Scenario 1: Using LogMiner to Track Changes Made by a Specific User for an example.

Unfortunately it would seem that your customer is running its DB in NOARCHIVELOG mode since the view V$ARCHIVED_LOG is empty. V$LOG_HISTORY only records the switchs of the online redo logs which all DBs have. Only databases that are setup in ARCHIVELOG mode will archive redo logs.

I'm afraid your customer is out of luck.

At this point you should probably consider contacting Oracle support.


How can I find the cause of pg_catalog.pg_attribute constantly being vacuumed? autovacuum seems to constantly run against pg_catalog.pg_attribute for the past week+. This seems pretty odd since we aren't doing constant table changes.

How can I understand why it's doing this and how to stop it? I'm on AWS Aurora and PG10.

Try to set log_statement=ddl to double check DDL statements. Maybe application code is creating temporary tables.


Insert Into Array of object MongoDB Ask Question I have

{
    "_id" : ObjectId("5a25af80d2c4dd065991dccc"),
    "username" : "abc@gmail.com",
    "password" : "$2a$10$ptFyLKtyyrL5gMYdXS6wV.HLUA4Py5iudMJDldf5qsHFS4.9TPCyy",
    "role" : "Admin",
    "__v" : 0,
    "list" : [
        {
            "name" : "We",
            "arr" : [
                "5a26d554677475818a795f75",
                "5a395bb0976d5304c07f7dd4"
            ]
        },
        {
            "name" : "sandeep",
            "arr" : [
                "5a26d554677475818a795f75"
            ]
        }
    ]
}

I want to add an element inside list.arr where name = 'we' and add only if the element does not exist

how do i perform this query.

The answer is the new feature of 3.6, arrayFilters what can be used with findAndModify(), update{One|Many}(), findOneAndUpdate() operations.


Space consumed by a column in non clustered index I m planning to add a key column index in one of the existing non clustered index in a big table which have millions of rows rather than create a new non clustered index. Is that any way for me to know how much the extra space consumed by the index by adding a new key column in existing non clustered index and is that there is any data/index ratio best practices, as I know its better not to have more than 5 index per table.
Just to be clear, I need to explain to storage team extra space that consumed by index after add the new column , then only I can add the column to existing index.

Is that any way for me to know how much the extra space consumed by the index by adding a new key column

Create a new index and check its size:
select schema_name(t.schema_id) schema_name,
       t.name table_name,
       i.name index_name,
       ps.used_page_count / 8. / 1024 size_mb,
       row_count,
       row_count / nullif(ps.used_page_count,0) rows_per_page
from sys.tables t
join sys.indexes i
  on t.object_id = i.object_id
join sys.dm_db_partition_stats ps
  on i.object_id = ps.object_id
 and i.index_id = ps.index_id
 order by table_name, index_name

is that there is any data/index ratio best practices, as I know its better not to have more than 5 index per table

Start with indexes for your candidate keys and foreign keys, then add additional indexes as necessary for query performance, monitoring both missing indexes and unused indexes.


CTE Query copy value if exists into an existing table I got the following query:

WITH table_data AS (
             SELECT UNNEST(array['Small Bus', 'Window', 'Test 1']) AS description,
                    UNNEST(array['1dfsdf4', 'QSF-312', 'EXD-TUH']) AS code,
                    UNNEST(array[NOW(), NOW(), NOW()]) AS updated,
                    UNNEST(array[NOW(), NOW(), NOW()]) AS created,
                    UNNEST(array[0.15, 350.0, 5.0]) AS sales_price,
                    UNNEST(array[0.51, 550.0, 10.0]) AS price,
                    UNNEST(array['Tax0', 'cdc', '54289hijlj']) AS code_p,
                    UNNEST(array['242', 'ded', 'fjDIashdukah']) AS sales_c
           ),
        parts_inserted AS (
         INSERT INTO parts (description, item_code, created, updated, sale_p,
                                    price, code_p_id, s_id_code)
         SELECT table_data.description, table_data.code, table_data.created, table_data.updated,
                table_data.sales_price, table_data.price, rates_p.id, rates_c.id
           FROM table_data
           LEFT JOIN rates AS rates_p
              ON table_data.code_p = rates_p.code
           LEFT JOIN rates AS rates_c
              ON table_data.sales_c = rates_c.code
          JOIN parts p ON p.item_code = table_data.code
          WHERE p.company_id = 65312322
         RETURNING *
       )
       SELECT * FROM parts_inserted;

The query selects the data ( WITH table_data AS... ) and inserts it into the parts table.

Table parts:

CREATE TABLE
    parts
    (
        id INTEGER DEFAULT nextval('p_id_seq'::regclass) NOT NULL,
        company_id INTEGER,
        item_code CHARACTER VARYING,
        price DOUBLE PRECISION,
        code_p_id INTEGER,
        sale_p DOUBLE PRECISION,
        s_id_code INTEGER,
        profit DOUBLE PRECISION,
        margin DOUBLE PRECISION,
        created TIMESTAMP(6) WITHOUT TIME ZONE NOT NULL,
        updated TIMESTAMP(6) WITHOUT TIME ZONE NOT NULL,
        taxable BOOLEAN DEFAULT true,
        origin CHARACTER VARYING,
        origin_id CHARACTER VARYING,
        description TEXT,
        created_by_id INTEGER,
        name CHARACTER VARYING,
        tsv TSVECTOR
    );

If code_p exists on the rates table, the query must insert the rates.id into parts.p_code_id
If sales_c exists on the rates table, the query must insert the rates.id into parts.s_id_code
If none of them exists, the p_code_id and s_id_code must be blank.

Table rates:

CREATE TABLE
    rates
    (
        id INTEGER DEFAULT nextval('r_id_seq'::regclass) NOT NULL,
        company_id INTEGER,
        name CHARACTER VARYING,
        rate DOUBLE PRECISION,
        created TIMESTAMP(6) WITHOUT TIME ZONE NOT NULL,
        updated TIMESTAMP(6) WITHOUT TIME ZONE NOT NULL,
        type CHARACTER VARYING,
        origin CHARACTER VARYING,
        origin_id CHARACTER VARYING,
        code CHARACTER VARYING,
        is_payable BOOLEAN DEFAULT false,
        description CHARACTER VARYING,
        active BOOLEAN DEFAULT true,
        has_component BOOLEAN DEFAULT false
    );

Currently only valid code_p and sales_c row gets inserted, if  code_p/sales_c exists:
(
LEFT JOIN rates AS rates_p
          ON table_data.code_p = rates_p.code
       LEFT JOIN rates AS rates_c
          ON table_data.sales_c = rates_c.code
) it is inserted into parts table.
If code_p or sales_c does not matches with the rates table, the row must be inserted with blank(NULL) code_p/sales_c... and this is not happening.

What should the query do?

if code_p or sales_c matches with the rates table:

LEFT JOIN rates AS rates_p
              ON table_data.code_p = rates_p.code
           LEFT JOIN rates AS rates_c
              ON table_data.sales_c = rates_c.code

the parts.code_p_id and parts.s_id_code columns must be updated with the rates.id value.

if code_p or sales_c doesn't match with the rates table, the parts.code_p_id and parts.s_id_code columns must be blank/null.

Question:

What am I missing?

What you have so far can be rewritten to this 100 % equivalent query:

INSERT INTO parts
        (description, item_code, created,   updated,   sale_p     ,   price, code_p_id, s_id_code)
SELECT t.description, t.code , t.created, t.updated, t.sales_price, t.price, rp.id    , rc.id
FROM  (
   VALUES
     ('Small Bus', '1dfsdf4', now(), now(), 0.15 , 0.51 , 'Tax0'      , '242')
   , ('Window'   , 'QSF-312', now(), now(), 350.0, 550.0, 'cdc'       , 'ded')
   , ('Test 1'   , 'EXD-TUH', now(), now(), 5.0  , 10.0 , '54289hijlj', 'fjDIashdukah')
   ) t(description, code, updated, created, sales_price, price, code_p, sales_c)
JOIN   parts       p ON pa.item_code = t.code  -- why?
LEFT   JOIN rates rp ON rp.code      = t.code_p
LEFT   JOIN rates rc ON rc.code      = t.sales_c
WHERE  p.company_id = 65312322                 -- why?
RETURNING *;

The query already inserts NULL into code_p_id and s_id_code respectively if no matching row in rates is found.

You don't need CTEs here.
Be aware that the RETURNING clause returns actual resulting values of new rows after applying any rules or triggers there might be.

You description is still unclear.
The join to parts seems highly suspicious. parts.company_id is not defined UNIQUE, the join will probably multiply rows. I seriously doubt that's intended.

You might really mean to UPDATE selected rows in table parts, not INSERT new rows ...


Retrieve XML of large plan from plan cache I'm trying to view (graphically) a large query plan that is in the plan cache and am running into problems where the XML is truncated in the query results.  This is the query I'm using:

SELECT TOP 1
    size_in_bytes,
    query_plan
FROM sys.dm_exec_cached_plans cp
CROSS APPLY sys.dm_exec_query_plan(cp.plan_handle) qp
ORDER BY size_in_bytes DESC

I've tried it in SSMS and when I click on the XML in the results grid I get the Unable to show XML. Unexpected end of file when parsing Name has occurred.  Line 1, position 2097154. If I copy and paste the XML it is indeed truncated.

I've tried manually using SET TEXTSIZE 2147483647 just because, and that didn't do anything.  (I know that's the default value for the SQL Native Client providers, was just trying to force the issue).

I've tried running the statement via SQLCMD from a .sql file and outputting to a text file but that gives me even less.

I've tried using SQLCMD interactively, with :XML ON and got this error:

<?MSSQLError HResult="0x80004005" Source="Microsoft XML Extensions to SQL Server" Description="No description provided"?>

The plan is 157 MB.

Is there any way to get the full plan out so that I can see it?

For 157MB in Tools -> Options -> Query Results -> SQL Server -> Results to Grid screen you will need to set Maximum Characters Retrieved -> XML data to unlimited as below.


ORA-00904 invalid identifer error when using alias I'm trying to return a product of two subqueries, and thought I'd make life easier by aliasing each of the subqueries, then divide the two aliases to get the number I'm after, like this:

select distinct
(select SUM(pest.AREA_HA) from WHSE_FOREST_VEGETATION.PEST_INFESTATION_POLY pest
  where ORG_UNIT_NO IN (1904, 1830,1831, 1902)) as Ha_IN_RKB,
(select SUM(pest.AREA_HA) from WHSE_FOREST_VEGETATION.PEST_INFESTATION_POLY pest) as Ha_total,
ROUND(  (Ha_IN_RKB / Ha_total) ,3)
from WHSE_FOREST_VEGETATION.PEST_INFESTATION_POLY pest

but this results in a 00904. 00000 -  "%s: invalid identifier" error (Oracle doesn't like Ha_IN_RKB or Ha_total ).

Re-writing the query as follows works, but is rather bulky:
select distinct
(select SUM(pest.AREA_HA) from WHSE_FOREST_VEGETATION.PEST_INFESTATION_POLY pest
  where ORG_UNIT_NO IN (1904, 1830,1831, 1902)) Ha_IN_RKB,
(select SUM(pest.AREA_HA) from WHSE_FOREST_VEGETATION.PEST_INFESTATION_POLY pest)  Ha_total,
ROUND(  ((select SUM(pest.AREA_HA) from WHSE_FOREST_VEGETATION.PEST_INFESTATION_POLY pest
  where ORG_UNIT_NO IN (1904, 1830,1831, 1902)) /
  (select SUM(pest.AREA_HA) from WHSE_FOREST_VEGETATION.PEST_INFESTATION_POLY pest)) ,3) as RKB_pct
from WHSE_FOREST_VEGETATION.PEST_INFESTATION_POLY pest

Anybody know why I can't just make this ROUND(  (Ha_IN_RKB / Ha_total) ,3) work ?

Aliases cannot be used directly. You have to abstract them out using a nested subquery or derived table.

with t as (
    select
    (select SUM(pest.AREA_HA)
       from WHSE_FOREST_VEGETATION.PEST_INFESTATION_POLY pest
      where ORG_UNIT_NO IN (1904, 1830,1831, 1902))
         as Ha_IN_RKB,
    (select SUM(pest.AREA_HA)
       from WHSE_FOREST_VEGETATION.PEST_INFESTATION_POLY pest)
         as Ha_total
    from dual)
select t.ha_in_rkb, t.ha_total, ROUND(t.Ha_IN_RKB/t.Ha_total,3) from t;


How to get prior data in a missing dates in sql table How to get prior data from a sql table?

In a table we have data as following and don't have data on holidays and weekends.

rate column         ratevalue

12/29/16              1.2266 (Thursday)
12/30/16              1.2345 (Friday)

01/03/17              1.2240 (Tuesday)

My requirement is to get data based on prior day as below

12/29/16              1.2266 (Thursday)
12/30/16              1.2345 (Friday)
12/31/16              1.2345 (Saturday)
01/01/17              1.2345 (Sunday)
01/02/17              1.2345 (Monday, New Year Holiday)
01/03/17              1.2240 (Tuesday)

Similarly the logic applies to any holidays where the rate doesn't get picked up. It should show the previous day's value.

You should really have a Calendar table for this sort of thing. Using a recursive cte is going to be one of the worst performing options as the size of your date range gets larger.

For only 152kb in memory, you can have 30 years of dates in a table with this:

/* dates table */
declare @fromdate date = '20000101';
declare @years    int  = 30;
/* 30 years, 19 used data pages ~152kb in memory, ~264kb on disk */
;with n as (select n from (values(0),(1),(2),(3),(4),(5),(6),(7),(8),(9)) t(n))
select top (datediff(day, @fromdate,dateadd(year,@years,@fromdate)))
    [Date]=convert(date,dateadd(day,row_number() over(order by (select 1))-1,@fromdate))
into dbo.Dates
from n as deka cross join n as hecto cross join n as kilo
               cross join n as tenK cross join n as hundredK
order by [Date];
create unique clustered index ix_dbo_Dates_date
  on dbo.Dates([Date]);

Without taking the actual step of creating a table, you can use it inside a common table expression with just this:

declare @fromdate date = '20161229';
declare @thrudate date = '20170103';
;with n as (select n from (values(0),(1),(2),(3),(4),(5),(6),(7),(8),(9)) t(n))
, dates as (
  select top (datediff(day, @fromdate, @thrudate)+1)
      [Date]=convert(date,dateadd(day,row_number() over(order by (select 1))-1,@fromdate))
  from n as deka cross join n as hecto cross join n as kilo
                cross join n as tenK cross join n as hundredK
   order by [Date]
)
select [Date]
from dates;

Use either with outer apply() like so:

select
    d.[Date]
  , r.rateValue
from dates d
  outer apply (
    select top 1 rateValue
    from rates i
    where i.rateDate <= d.Date
    order by i.rateDate desc
    ) r;

rextester demo: http://rextester.com/NVY8460

returns:

+------------+-----------+
|    Date    | rateValue |
+------------+-----------+
| 2016-12-29 | 1.2266    |
| 2016-12-30 | 1.2345    |
| 2016-12-31 | 1.2345    |
| 2017-01-01 | 1.2345    |
| 2017-01-02 | 1.2345    |
| 2017-01-03 | 1.2240    |
+------------+-----------+

Number and Calendar table reference:

Generate a set or sequence without loops - 1 - Aaron Bertrand
Generate a set or sequence without loops - 2 - Aaron Bertrand
Generate a set or sequence without loops - 3 - Aaron Bertrand
The "Numbers" or "Tally" Table: What it is and how it replaces a loop - Jeff Moden
Creating a Date Table/Dimension in sql Server 2008 - David Stein
Calendar Tables - Why You Need One - David Stein
Creating a date dimension or calendar table in sql Server - Aaron Bertrand
tsql Function to Determine Holidays in sql Server - Aaron Bertrand
F_table_date - Michael Valentine Jones


How do I analyse a set of SQL Profiler-generated rolling trace files? I am trying to diagnose an issue with a production SQL Server instance. I have asked the DBA to provide a trace and have been given a set of SQL trace files (1185 files to be precise).

When I open the first file, SQL Profiler detects presence of additional trace files and prompts me to load the one by one - this approach does not scale to 1185 files.

Ultimately, what I want is to import all that trace data into SQL server so I can query it.

Can you recommend an approach or tool that would allow me to do this?

The following will insert the entire set of files into a table of your choosing. Then you can query. Please do this on a dev instance.

SELECT *
INTO [Database].[schema].[table]
FROM ::fn_trace_gettable('\path\to\tracefile1.trc', default)


Trying to solve "outside tablespace bounds" MySQL error, but recovering table with mysqldump fails I am running MySQL on a Windows 2008 server. One of the tables (> 1 million records) seems to be corrupted. The mysql error log shows this:

InnoDB: Error: trying to access page number 197105 in space 89,
InnoDB: space name dbname/tablename,
InnoDB: which is outside the tablespace bounds.
InnoDB: Byte offset 0, len 16384, i/o type 10.
InnoDB: If you get this error at mysqld startup, please check that
InnoDB: your my.cnf matches the ibdata files that you have in the
InnoDB: MySQL server.

Based on this error message, I checked my.cnf for the relevant lines:

innodb_data_home_dir = "C:/xampp/mysql/data"
innodb_data_file_path = ibdata1:10M:autoextend
innodb_log_group_home_dir = "C:/xampp/mysql/data"

This is exactly where the data resides. ibdata1 is about 208 MB in size, the tablename.ibd is about 4 GB in size. I've been using this server succesfully for many months.

After searching on this site and elsewhere, I tried to perform recovery as follows:

Set innodb_force_recovery=1 and then up to 6.
Next, dump the specific table with mysqldump. I am using this
command: mysqldump dbname tablename --user=root --password=blahblah.
During this dump, for recovery levels 1 through 3, mysql crashes ('goes away'). At 4, it managed to dump the first 23992 records, but then it said

  Lost connection to MySQL server during query when dumping table tablename at row: 23992.

The log has some additional lines preceding what I showed above:

InnoDB: Failed to find tablespace for table "dbname"."tablename"
in the cache. Attempting to load the tablespace with space id 89.
InnoDB: Error: trying to access page number 0 in space 89,
InnoDB: space name dbname/tablename,
InnoDB: which is outside the tablespace bounds.
It seems as soon as I touch or (select) query this table, either through php, python or phpMyAdmin, mysql crashes.

Does anybody have any other suggestions, possibly based on the tablespace bounds error above?

Thanks in advance!

Let's elaborate the error message

  InnoDB: Error: trying to access page number 197105 in space 89,

InnoDB calles an .ibd file a "table space" or just "space" in this error message. So, there is a space 89 and one of its pages refers to a non-existing page number 197105. The error message doesn't say what table corresponds to the space 89. I think some MySQL version tell that, but it's possible to find from the InnoDB dictionary.

mysql> select NAME from information_schema.INNODB_SYS_TABLESPACES WHERE SPACE=4304;
+----------------------+
| NAME                 |
+----------------------+
| sakila/film_category |
+----------------------+
1 row in set (0.01 sec)

Now, as we know what table is corrupt the question is how to fix it. InnoDB doesn't heal tablespaces (like MyISAM does with REPAIR TABLE), the only way is to drop the table space(=table) and re-create it.

In this particular case it's not necessary to start MySQL in innodb_force_recovery mode. Just start it normally and drop the table. But first, you probably need to save the data in this table.

There are two ways.
First, you can choose ranges of non-corrupt primary key values and dump them into another table or a text dump. I had recovery cases like this one and wrote a script to do that. The script iterates over the PK ranges and dumps as many records as possible into another (MyISAM) table.

Second way is requires more time and skills. You will need a tool (https://github.com/twindb/undrop-for-innodb) and a blog post https://twindb.com/recover-corrupt-mysql-database/ with steps.

Then drop the corrupted table, create an empty one and load the recovered dump.


Insert into linked server fails with "The transaction manager has disabled its support for remote/network transactions" Two servers: sq009 and sq202, both running SQL Server 2012 SP3.

sq202 is the SQL Server hosting the DB for our ERP system, Sage 500.
sq009 is the SQL Server hosting the DB for our IBM Maximo instance.

The situation: a trigger exists on a table in sq202, which should insert a row in the sq009 DB when a row is inserted in the sq202 DB. However, I get this error in Sage 500 dialog when the record is inserted:

  The operation could not be performed because OLE DB provider "SQLNCLI11" for linked server "sq009" was unable to begin a distributed transaction.
  OLE DB provider "SQLNCLI11" for linked server "sq009" returned message "The transaction manager has disabled its support for remote/network transactions.".

Both servers have the other as a Linked Server; both have all connections being made using a specified security context; both have the server option Enable Promotion of Distributed Transactions for RPC set to False.

I have confirmed the Distributed Transaction Coordinator service is running on sq009, and set its permissions appropriately I believe (even though I understand this shouldn't be needed):

but sq009 still is apparently complaining.

Try enabling the DTC on the sq202 box, since that is the server initiating the distributed transaction. You will also need to have Enable Promotion of Distributed Transactions for RPC option set to True as well for the sq009 linked server located on the sq202 box; when this option is TRUE, calling a remote stored procedure starts a distributed transaction and enlists the transaction with MS DTC.

Helpful information on T-SQL Distributed Transactions and the requirements:
https://technet.microsoft.com/en-us/library/ms191156(v=sql.105).aspx

Linked Server Options: https://technet.microsoft.com/en-us/library/ms186839(v=sql.105).aspx


Finding server and instance name I want to know how to find the server and instance name on my computer to connect to the server.

I am using the SQLCMD utility, and I am currently able to connect to the server with the line

>SQLCMD -S "(localdb)\mssqllocaldb"

I want to know if the server and instance name, "(localdb)\mssqllocaldb", is information that is listed anywhere I can access on my computer.

I'm trying to configure another computer to connect its own local server in the same way I am able to connect to (localdb)\mssqllocaldb, and I want to know what "(localdb)\mssqllocaldb" should be on their computer so that they can connect to it with SQLCMD.

If you're really using LocalDB and haven't just named a regular SQL Server instance as if you were, there are definitely some extra hurdles in establishing connectivity from remote machines. This feature is targeted primarily at local, isolated development.

I wrote a pretty lengthy article on LocalDB, including how to get over some of the connectivity hurdles, in this tip (and there is likely some valuable information in some of the comments, too):

Getting Started with SQL Server 2012 Express LocalDB

More recently, I added a tip to handle a more modern version, which I highly recommend if for no other reason that most of the connectivity hurdles have been reduced or eliminated:

Getting Started with SQL Server 2017 Express LocalDB

I would strongly recommend installing a proper instance of SQL Server (even if only the Express Edition), which can be set to always be running (no matter who is or isn't logged into the machine), does not have any of these additional connectivity hurdles, etc.


Is it possible to support multiple snapshots / store a snapshot / restore from a snapshot to a state with another snapshot? So, I would like to have a snapshot structure like the following:
A snapshot 1 is taken. Some code is run, then a snapshot 2 is taken.

Then some more code is run, and the database is reverted to snapshot 2. Then more code, then snapshot 2, N times.

Then, once all that is done, the database is restored to its original state, snapshot 1. Basically, I would like it to be the case that I do not need to delete existing snapshots when restoring a snapshot, but rather that snapshot 1 is "stored" in snapshot 2 somehow, and when snapshot 2 is loaded, snapshot 1 is available again for a subsequent restore.

It seems as though this should be possible, because if snapshot 1 is "frozen" immediately prior to snapshot 2 being created, then once snapshot 2 is reverted to, there should be nothing preventing snapshot 1 from working, since the database state is identical to when it was frozen and snapshot 2 was created.

Is this possible? Do I need to create a snapshot of a system table?

This can't be done, the snapshots are not 'tiered' such that snapshot 2 can revert back to the snapshot 1 state.

In SQL Server, database snapshots preserve the original state of each page only when it is changed in the source database. Snapshot 2 is not preserving changed pages from snapshot 1 but from the original source database.

The documentation highlights this in step 2 under "How to Revert a Database to a Database Snapshot".

Your best bet would be to copy the original database, then take a snapshot of the copied database. Run your "snapshot 2" tests against the snapshot taken of the copied database. You can then easily revert the snapshot for multiple tests and return to your pre-test state and when you're finished, simply drop the copied database. In this fashion, your original database will be unaffected by your testing.


PostgreSQL streaming replication stops updating I have four PostgreSQL 9.5 instances running on EC2 using m4.8xlarge instances with five PIOPS SSD in a RAID0 setup, and a separate XLOG drive.  Until this morning, I never had more than a minute or two of replication lag, but now replication fails completely on all instances after about 30 minutes.

Restarting Postgres fixes the problem for another half hour.

There's no CPU contention and iowait is usually less than 1%.  Blocking reads to the server, thinking that it might be overwhelmed, does nothing. I can't figure out what the problem is here, other than something on Amazon's end.

Can anyone give me some idea how to troubleshoot this problem?  There's nothing in the logs, replay_location (from pg_stat_replication) just stops updating until I restart the slaves.

TL;DR: Do not disable max_standby_streaming_delay in the config.

For the sake of others looking for answers on this one, I thought I would answer my own question since the amount of information out there on this topic via Google is very slim.

Digging around, I managed to discover a book called Troubleshooting PostgreSQL: https://www.packtpub.com/big-data-and-business-intelligence/troubleshooting-postgresql

It comes in ebook format and was useful for helping me to track down the issue I was experiencing.  It describes using a tool called pg_view which has a lot of very useful realtime metrics on what's happening in your PostgreSQL server.

Running it on one of my servers that had halted replication showed the problem occurred during long running queries:

db.server.com up 21:34:25 36 cores Linux 4.4.5-15.26.amzn1.x86_64 load average 1.79 0.94 0.47                                                                                08:58:29
 sys: utime 5.7   stime 0.1   idle 94.3  iowait 0.0   ctxt 580 run 4   block 0
 mem: total 59.0GB free 2.3GB  buffers 41.0MB cached 55.0GB dirty 692KB  limit 29.5GB as 14.7GB left 14.8GB
 /var/lib/pgsql/9.5/data 9.5.2 standby connections: 5 of 500 allocated, 2 active
 type dev     fill   total    left   read  write    await path_size path
 data md127    0.0 499.7GB 382.8GB    0.0    0.1      0.0   116.6GB /var/lib/pgsql/9.5/data
 xlog xvdg     0.0 100.0GB  84.0GB    0.0    0.6     15.7    15.9GB /var/lib/pgsql/9.5/data/pg_xlog
   pid type    s utime stime guest read write   age  uss db   user     query
 26644 backend R 100.2   0.0   0.0  0.0   0.0 03:21 93.4 data postgres WITH query AS ( WITH original_transaction_id AS( SELECT original_transaction_id FROM table WHERE...
 26645 backend R  99.3   0.0   0.0  0.0   0.0 00:01 93.8 data postgres WITH query AS( SELECT COUNT(id) AS total, original_transaction_id FROM table GROUP BY original_tr...

The key there is the age column showing the long running query.  The secret, however, is to show all the processes on the server by pressing the 's' hotkey, which isn't described in the pg_view GitHub page.  This shows the system processes, including the startup and WAL receiver:

   pid type            s utime stime guest read write   age  uss db   user     query
 12169 startup         S   0.0   0.0   0.0  0.0   0.0        1.2               recovering 000000010000070A0000006D...
 12170 checkpointer    S   0.0   0.0   0.0  0.0   1.9        1.2               ...
 12171 writer          S   0.0   0.0   0.0  0.0   0.0        1.0               ...
 12172 stats collector S   0.0   0.0   0.0  0.0   0.0        1.2               ...
 12178 wal receiver    S   0.0   1.0   0.0  0.0   1.1        1.2               streaming 70A/6D998000...

The useful info there is on the startupline where it says recovering.  This is normal, as it is a read-only slave and is technically always recovering as new data comes in via the wal receiver.

The problem is when it has waiting on the end of that line - what this tells you is that the server is stuck waiting for a long running query to release a lock on a table it needs to update, and until it does, replication is halted, or  the max_standby_streaming_delay value is reached.

By default, max_standby_streaming_delay is set to 30ms.  However, it was disabled on my servers, perhaps for some perceived benefit to replication reliability:

max_standby_streaming_delay = -1

Removing that line from the config file and reloading fixed the issue going forward.

Another outcome was for us to move all long-running queries to a separate replica only used my these backend processes to help long-term reliability and avoid possible table locks on customer-facing databases.

Most of the troubleshooting guides say that replication issues are network related, but in this case it was a misconfiguration and poorly-formed SQL queries.


How to implement Draft functionality, with attached files? Should allow for:

User can save an entity as a draft, that would normally not be valid in its table (ie some missing values)
User can attach files to the draft
User can find, sort, and discard their drafts

This is not a homework question. I have some ideas, but wanted to see what is out there first. Thanks!

For the drafts side, what we do (LedgerSMB. PostgreSQL) is use a bool "approved" column or some approval information (where NULL means draft, for example "approved_by" or "approved_at").

I don't know the quirks of MySQL check constraints too well, but in PostgreSQL you can condition check constraints on the value in a field, something like:
CHECK (approved is not true or title is not null).  This makes it impossible to approve something that has not had everything filled on.  We don't do this generally because approval is supposed to be all or nothing, but there are a few areas where something like this is done.


Master to Slave to Slave Configuration in MySQL We currently have 3 MySQL Servers running;

the first which contains production data and is due to be retired

mysqld  Ver 5.5.28-0ubuntu0.12.04.2 for debian-linux-gnu on x86_64 ((Ubuntu))

mysql> SHOW MASTER STATUS;
Empty set (0.00 sec)
the second which contains out of date snapshot of production data and is to take over from the first.

mysqld  Ver 5.5.28-0ubuntu0.12.04.3-log for debian-linux-gnu on x86_64 ((Ubuntu))

Master Status
the third which is already a slave of the second server.

mysqld  Ver 5.5.29-0ubuntu0.12.04.1 for debian-linux-gnu on x86_64 ((Ubuntu))

mysql> SHOW MASTER STATUS;
Empty set (0.01 sec)

My question is would I go about setting up the following?:

First Server (master) -> Second Server (slave) -> Third Server (slave)

Tutorial links welcome! I tried to Google, but I'm sure I've gotten the wrong keywords

Based on our chat conversation, here is what was discussed

Server1 is Stand Alone
Server2 is a Master
Server3 is a Slave to Server2

This implies that binary logging is enabled in Server2.
To Make Server1 a Master of Server2, perform the following:
STEP 01 : On Server2, add this to /etc/my.cnf
[mysqld]
log-slave-updates

STEP 02 : On Server3, run STOP SLAVE;
STEP 03 : On Server2, run service mysql restart
STEP 04 : On Server3, run START SLAVE;
STEP 06 : On Server1, add this to /etc/my.cnf
[mysqld]
log-bin=mysql-bin

STEP 07 : On Server1, run service mysql restart
STEP 08 : Set Replication From Server1 to Server2
See Clarification about master slave configuration in mysql
OPTIONAL
Once you have MySQL Replication Going From Server1 to Server2 to Server3, your can properly load all data into all three MySQL Instances by doing the following on Server1:
mysqldump -u... -p... --all-databases --routines --triggers > mysqldata.sql
mysql -u... -p... < mysqldata.sql

This will do three(3) things

Repopulate everything into Server1
MySQL Replication will handle populating Server2 from Server1
MySQL Replication will handle populating Server3 from Server2

Since your data is 50MB in total, this should be execute very quickly.


mysql 5.6 flushing all dirty pages on ib_logfile rotation creating checkpoint stall Server version: 5.6.23-72.1-log Percona

I have been trying to track down why our mysql server is sharp checkpointing and it seems to correlate with rotating of the ib_logfile.

I inherited this Percona 5.6 mysql server and it was pausing for the sharp checkpoint / full dirty flush once a day with innodb_log_file_size of 1GB. I thought perhaps it was insufficient logfile size and bumped it up to 25GB. Now we just get the same behavior less frequently. It was a sort of mystery to me until I was able to verify it happened when the logfile switched.

Mysql 5.5 docs: https://dev.mysql.com/doc/refman/5.5/en/innodb-checkpoints.html: "...when InnoDB starts to reuse a log file, it has to make sure that the database page images on disk contain the modifications logged in the log file that InnoDB is going to reuse. In other words, InnoDB must create a checkpoint and this often involves flushing of modified database pages to disk."

Mysql 5.6 docs omit this bit and say all it should do is fuzzy checkpointing in normal circumstances. https://dev.mysql.com/doc/refman/5.6/en/innodb-checkpoints.html: "InnoDB implements a checkpoint mechanism known as fuzzy checkpointing. InnoDB flushes modified database pages from the buffer pool in small batches. There is no need to flush the buffer pool in one single batch, which would disrupt processing of user SQL statements during the checkpointing process."

Is there any way to minimize or get out of this behavior if it is unexpected?

| innodb_adaptive_flushing | ON |
| innodb_adaptive_flushing_lwm | 10 |
| innodb_buffer_pool_size | 46170898432 |
| innodb_change_buffer_max_size | 25 |
| innodb_change_buffering | inserts |
| innodb_checksum_algorithm | innodb |
| innodb_checksums | ON |
| innodb_cleaner_lsn_age_factor | high_checkpoint |
| innodb_doublewrite | ON |
| innodb_empty_free_list_algorithm | backoff |
| innodb_file_format | Barracuda |
| innodb_file_per_table | ON |
| innodb_flush_log_at_timeout | 1 |
| innodb_flush_log_at_trx_commit | 1 |
| innodb_flush_method | O_DIRECT |
| innodb_flush_neighbors | 0 |
| innodb_flushing_avg_loops | 30 |
| innodb_force_load_corrupted | OFF |
| innodb_force_recovery | 0 |
| innodb_foreground_preflush | exponential_backoff |
| innodb_io_capacity | 3000 |
| innodb_io_capacity_max | 6000 |
| innodb_log_arch_dir | ./ |
| innodb_log_arch_expire_sec | 0 |
| innodb_log_archive | OFF |
| innodb_log_block_size | 512 |
| innodb_log_buffer_size | 8388608 |
| innodb_log_checksum_algorithm | innodb |
| innodb_log_compressed_pages | ON |
| innodb_log_file_size | 26843545600 |
| innodb_log_files_in_group | 2 |
| innodb_log_group_home_dir | ./ |
| innodb_lru_scan_depth | 2048 |
| innodb_max_changed_pages | 1000000 |
| innodb_max_dirty_pages_pct | 50 |
| innodb_max_dirty_pages_pct_lwm | 0 |
| innodb_max_purge_lag | 200 |
| innodb_max_purge_lag_delay | 0 |
| innodb_old_blocks_pct | 37 |
| innodb_old_blocks_time | 1000 |
| innodb_online_alter_log_max_size | 134217728 |
| innodb_open_files | 384 |
| innodb_page_size | 16384 |
| innodb_purge_batch_size | 20 |
| innodb_purge_threads | 1 |
| innodb_random_read_ahead | OFF |
| innodb_read_ahead_threshold | 56 |
| innodb_read_io_threads | 4 |
| innodb_read_only | OFF |
| innodb_rollback_on_timeout | OFF |
| innodb_rollback_segments | 128 |
| innodb_sched_priority_cleaner | 19 |
| innodb_sort_buffer_size | 1048576 |
| innodb_spin_wait_delay | 6 |
| innodb_sync_spin_loops | 30 |
| innodb_table_locks | ON |
| innodb_thread_concurrency | 0 |
| innodb_thread_sleep_delay | 10000 |
| innodb_use_global_flush_log_at_trx_commit | ON |
| innodb_use_native_aio | ON |
| innodb_use_sys_malloc | ON |
| innodb_version | 5.6.23-72.1 |
| innodb_write_io_threads | 8 |

The sentence about requiring a checkpoint before the log file rotation/reuse may have been removed from documentation, but the logic hasn't changed in version 5.6 -- you cannot overwrite log records that belong to not-yet-persisted data, so you have to checkpoint still. The fact that it is happening in your environment indicates that flushing of dirty pages with fuzzy checkpoints is not aggressive enough for your workload.

I'd try raising the innodb_adaptive_flushing_lwm threshold and lowering innodb_max_dirty_pages_pct to make page cleaning more proactive.

There are more hints and links in this article


Use name of table as input in procedure and store the output of SELECT statement in output variable SQL I am trying to create a procedure, that will accept as input a variable called @tablename and will return as output the value of a variable called @myoutput. As the names of the variables signify, @tablename should be the name of a table and @myoutput should be just be the sum of the the first column of the table specified  by @tablename, divided by the number 3.3. The procedure I have written is the following:

CREATE PROCEDURE sample_proc
@tablename VARCHAR(MAX),
@myoutput DECIMAL(20,5) OUTPUT

AS
SET NOCOUNT ON;

    @myoutput=EXEC('SELECT SUM('+@tablename+'.column1)/3.3)'

When I just use EXEC(), the select statement is executed as it should, but I cannot get the value I want from the table. What I want, is to store the value I calculate in the variable @myoutput, and export that to my main script. The above code produces an error. Does anyone know how I can implement this?

First you're not using a valid syntax due your SELECT command has no FROM clause.

Then you should use sp_executesql just to take care of the output parameters.

create table xx (id int, column1 int);
create table yy (id int, column1 int);
insert into xx values (1, 1),(2,2),(3,3);
insert into yy values (1,10),(3,30),(4,40);
GO

6 rows affected

CREATE PROCEDURE sample_proc
(
    @tablename VARCHAR(MAX),
    @myoutput DECIMAL(20,5) OUTPUT
)
AS
    SET NOCOUNT ON;

    DECLARE @Cmd NVARCHAR(MAX);
    DECLARE @ParmDefinition NVARCHAR(500);

    SET @ParmDefinition = '@Total decimal(20,5) OUTPUT';
    SET @Cmd = 'SELECT @Total = (SUM(column1)/3.3) FROM [' + @tablename + ']';

    -- just to show SQL command.
    SELECT @Cmd;

    EXEC sp_executesql @Cmd, @ParmDefinition, @Total = @myoutput out;
GO

✓

DECLARE @op DECIMAL(20,5);
EXEC sample_proc 'xx', @op OUT
SELECT @op;
EXEC sample_proc 'yy', @op OUT
SELECT @op;
GO

| (No column name)                             |
| :------------------------------------------- |
| SELECT @Total = (SUM(column1)/3.3) FROM [xx] |

| (No column name) |
| :--------------- |
| 1.81818          |

| (No column name)                             |
| :------------------------------------------- |
| SELECT @Total = (SUM(column1)/3.3) FROM [yy] |

| (No column name) |
| :--------------- |
| 24.24242         |

dbfiddle here


What's the recommended way to handle naming collisions with the PK column? I have a data field "Shipment ID" which is a VARCHAR(18) from a number of different vendors. This is going into a table shipment with a surrogate PK shipment_id.

I'm trying to figure out the best way to name this column. Our conventions say:

Only PKs or FKs can end in _id.
PKs are tablename_id. So a table hello_world would have a PK of hello_world_id.
table and columns are lowercase_with_underscores
nothing about name collisions (if I get a good answer here, I'll make sure it's added to the style guide.)

I know it doesn't follow this awesome answer, but that's what we've got

I can't use the "Shipment ID" field as a PK because it's not guaranteed to be unique or even exist. Plus it is a string and I am not a fan of string PKs.

I don't really like shipment_name or vendor_shipment_id. What do you guys use in cases like this?

Given the stated rules, shipment_id_from_vendor would be better than vendor_shipment_id (doesn't end in _id, so not expected to be a PK/FK field).

Generally, for me, a name collision would mean that one or both names require a better definition, to clarify whey they're distinct values. Even near-collisions should be avoided: If you have a requirements date and a request date, using the names req_date and request_date is not particularly helpful; someone may be looking for the request date, see req_date, and assume that they've found what they wanted. requirements_date may be much longer to type, but it's immediately clear what it is, and no one's going to mix it up with request_date.

That said, there's no universal solution to this. I have no issue with long table and column names, if that's what's needed to make them clear; other people would insist brevity is key and make the two columns from the previous paragraph reqr_date and rqst_date.


Is data retrieved from SQL Server compressed for transmission? Is data retrieved from Microsoft SQL Server compressed? If this is controlled by the connection string, is there any simple way to tell if any particular app is using it?

I'm examining analysis tools, and the volume of data can take minutes to transmit over our network. I'm wondering whether I should expect a performance increase if we pull data from a compressed data store on the same remote server.

As long as we're on the topic, I'm curious: is data transmitted in binary or ASCII? For example, if the value 12345 is queried from an INT column, is it transmitted as the five bytes 0x31, 0x32, 0x33, 0x34, 0x35; the two bytes that are required for the value; or four bytes as required for the column?

To be clear, I understand that there are options regarding storing data with compression, and backing it up. I'm asking about how data is transmitted.

The data you are looking to compress is that sent over the wire via TDS. There is some minor compression here but nowhere near the type of compression you get with page/row compression, backup compression or ColumnStore compression.

It has been asked for before:

http://connect.microsoft.com/SQLServer/feedback/details/412131/enable-network-compression-compress-tds-stream

http://connect.microsoft.com/SQLServer/feedback/details/377479/wan-compression-option

The items are still open, so maybe there is some hope. There is no way to control this via the connection string that I've ever seen.

In the meantime there are some products that claim to do this, e.g.

http://www.nitrosphere.com/products/nitroaccelerator/

http://toonel.net/tcpany.htm

You can also potentially configure the network between your SQL Server and the application servers to support compression (and other things like encryption) but you are beyond my scope here, and I'm not sure if this would be supported by every single feature of SQL Server.

And to be honest, I'm not convinced this is the place you want to focus on optimizing. Compressing this stream might actually slow things down and outweigh the benefits of sending fewer bytes. I'd rather plunk the money down on better network connectivity between server and client(s) than to spend time investing in this type of work and testing whether it has any actual benefits - and not being able to do that until afterward. From from 10/100 to gig fiber has a known and predictable impact on network I/O.

I am not sure about the format of the bytes sent over the wire; you will have to set up some kind of packet sniffer for that (or maybe someone has already done that and will chime in).

As for the impact of compression, unless you are on Fusion-IO or other high-end SSD-type solutions, you are almost certainly I/O bound currently, and not CPU-bound. So as long as you have CPU overhead, you should see faster performance with compression enabled (but this won't change network performance, since the data is uncompressed before transmission). I say that knowing nothing about your servers, your application, your data or your usage patterns - you could very well have an edge case where compression actually hurts performance, or where the data just isn't a good candidate for good compression ratios.


Multi-command SQLCMD mode script I am trying to execute a sql procedure for a list of tables in sqlcmd mode to redirect all the output into one file per table.

Unfortunately only the last file of the redirected outputs of all commands gets content.

For example I execute the following:

:OUT  c:\test\tab0.sql
 EXEC [dbo].[sp_generate_merge] @schema = 'dbo', @table_name ='tab0', @include_timestamp = 0, @ommit_images = 1, @CreateProcedure = 1
:OUT  c:\test\tab1.sql
 EXEC [dbo].[sp_generate_merge] @schema = 'dbo', @table_name ='tab1' , @include_timestamp = 0, @ommit_images = 1, @CreateProcedure = 1

Both files are created, but only the last file gets all the content.

Any hints how I can change this in order to get the results in one file per command?

Put a GO (i.e. batch separator) between the two sections. SQLCMD commands and variables are processed per each batch. I tested this and without the GO (i.e. how it is set up in the Question), I get the same behavior that you are seeing: first file exists but is empty, while the second file has the results for both stored procedure executions. This is due to both :out commands being in the same batch, hence the 2nd :out replaces the first one.

But, if I put a GO between them, each output file has just the results for that one execution:

:OUT  c:\test\tab0.sql
 EXEC [dbo].[sp_generate_merge] @schema = 'dbo',
           @table_name ='tab0', @include_timestamp = 0,
           @ommit_images = 1, @CreateProcedure = 1;
GO
:OUT  c:\test\tab1.sql
 EXEC [dbo].[sp_generate_merge] @schema = 'dbo',
           @table_name ='tab1' , @include_timestamp = 0,
           @ommit_images = 1, @CreateProcedure = 1;

P.S. Unrelated to this issue, you shouldn't prefix your Stored Procedure names with sp_ as that is reserved for special use by SQL Server and searches the master Database first, which could lead to a performance issue.


Add a primary key constraint to an existing unique column I've seen lots of information around adding a new primary key column to a large database table, but I have a somewhat different situation.

I have a large table (~90,000,000 records), that has no primary key. However, it DOES have a "RowID" column, which is an identity column. It also has a unique index on the RowID column.

All my research showed that a table without a primary key is terrible design that should be gotten rid of if at all possible. In my particular case; it's causing issues because Entity Framework maps the table as a read only view if it can't find a primary key.

I want to add a primary key constraint to the table on the RowID column. I was hoping that because it's not adding a new column, and there's already a unique index on that column, that it would be a small operation; however, it appears from a test run that it will take a very long time.

Having this table be inaccessible for a long time will cause issues across our business. I know that another option is to create a new table with the new schema, and copy all data from the old to the new. But this will also cause us to need to lock down the table for a long time.

Is there a way to more quickly add the primary key constraint when you already have an identity column with a unique index?

The assumption you have is that a lock will occur when creating a new table. This is false if you use the correct query hints & have a good action plan. I recommend the following:

Create a New table with identical schema but with a primary key constraint added.
INSERT INTO NewTable SELECT * FROM OldTable (this will still take a while)
Use SSIS or write a good function to identify new rows in the old table whilst the insert into the newtable was occuring and write these rows to the new table. (Essentially perform another merge ontop)
Rename/Drop the old table and rename the new table to the old table name.

On the topic of locks. You have a few options as I see:

Specify NOLOCKS or READUNCOMITTED on your query. This has its own implications that are best read here.
Take a full table lock which you've already said is less desirable but would mean step 3 of the action plan is not required.
Take shared page locks (Default for select). This means that the individual pages when being read will lock and no updates or inserts will be allowed. It does mean though that other selects on this page can still occur during this lock.

It really depends on your specific need which of these three is your best COA. I suggest consulting with business to determine if an outage is really such a bad thing and if it is, consult with the developers to determine what lock level is most appropriate for the task.


Simplifying/Improving inner join (self joining a table) I have table which has the following schema:

cid         name        type         notnull     dflt_value  pk
----------  ----------  -----------  ----------  ----------  ----------
0           DatasetID   VARCHAR(15)  0                       1
1           QuestionID  VARCHAR(20)  0                       2
2           PersonID    INTEGER      0                       3
3           answer      text         0                       0

And I have constructed a query that joins several answer rows into one, uses some of them, (age in this case) as filters:

SELECT i.answer AS graduation_year, j.answer AS education, k.answer AS country FROM

(SELECT * FROM answer
WHERE QuestionID = 'year'
AND answer <> ''
AND DatasetID = 'test') i

inner join answer j
    on i.PersonID = j.PersonID
    AND i.DatasetID = j.DatasetID
    AND j.QuestionID = 'education'

inner join answer k
    on i.PersonID = k.PersonID
    AND i.datasetID = k.datasetID
    AND k.QuestionID = 'country'

inner join answer m
    on i.PersonID = m.PersonID
    AND i.datasetID = m.datasetID
    AND m.QuestionID = 'age'
    AND CAST(m.answer as NUMERIC) > 25;

The output is following:

graduation_year  education   country
---------------  ----------  ----------
1995             Bachelors   Finland
1980             PhD eng.    Austria

I'm wondering if the same query can be accomplished in a more efficient way in terms of query time, or if it can be simplified. I'm currently using sqlite3 as the test database, but if there's a way to simplify the query in for example MySQL, that would also be an acceptable solution.

For reference, the answer table contains these values (where datasetID is 'test'), i.e. the results of the query SELECT * FROM answer WHERE datasetID = 'test';

DatasetID   QuestionID  PersonID    answer
----------  ----------  ----------  ----------
test        year        12345       1992
test        country     12345       Austria
test        age         12345       24
test        education   12345       M. Sc.
test        other       12345       boring...
test        year        123456      1995
test        country     123456      Finland
test        age         123456      26
test        education   123456      Bachelors
test        other       123456      meh
test        year        1234567     1980
test        country     1234567     Austria
test        age         1234567     35
test        education   1234567     PhD eng.
test        other       1234567     interesting

When you need to retrieve rather many attributes from an EAV structure, you can try pivoting it with grouping & conditional aggregation as an alternative to multiple self-joins:

SELECT
  PersonID,
  MAX(CASE QuestionID WHEN 'year'      THEN answer END) AS year,
  MAX(CASE QuestionID WHEN 'education' THEN answer END) AS education,
  MAX(CASE QuestionID WHEN 'country'   THEN answer END) AS country,
  MAX(CASE QuestionID WHEN 'age'       THEN answer END) AS age
FROM
  answer
WHERE
  DatasetID = 'test'
GROUP BY
  PersonID
;

That avoids multiple table scans and, as a bonus, makes your query shorter. But how much the former will affect the performance can ultimately be determined only by testing.

Still, a minor (or not – depends on the total number of attributes) improvement to the above could be to reduce the underlying dataset to just the attributes you are actually retrieving, like this:

SELECT
  PersonID,
  MAX(CASE QuestionID WHEN 'year'      THEN answer END) AS year,
  MAX(CASE QuestionID WHEN 'education' THEN answer END) AS education,
  MAX(CASE QuestionID WHEN 'country'   THEN answer END) AS country,
  MAX(CASE QuestionID WHEN 'age'       THEN answer END) AS age
FROM
  answer
WHERE
  DatasetID = 'test'
  AND QuestionID IN ('year', 'education', 'country', 'age')
GROUP BY
  PersonID
;

Logically, the above is still not completely equivalent to the series of inner joins because the latter would give you only entries that have all specified attributes. The method in this answer can return entries that do not have some of the attributes requested. If you would like to exclude those, add a HAVING filter that makes sure the number of attributes matches the WHERE filter:

SELECT
  PersonID,
  MAX(CASE QuestionID WHEN 'year'      THEN answer END) AS year,
  MAX(CASE QuestionID WHEN 'education' THEN answer END) AS education,
  MAX(CASE QuestionID WHEN 'country'   THEN answer END) AS country,
  MAX(CASE QuestionID WHEN 'age'       THEN answer END) AS age
FROM
  answer
WHERE
  DatasetID = 'test'
  AND QuestionID IN ('year', 'education', 'country', 'age')
GROUP BY
  PersonID
HAVING
  COUNT(*) = 4
;

Your query also has a filter on an attribute that in this case is an aggregated column. Such filters would need to be implemented in the HAVING clause. For your example, the filter on age would look like this:

SELECT
  PersonID,
  MAX(CASE QuestionID WHEN 'year'      THEN answer END) AS year,
  MAX(CASE QuestionID WHEN 'education' THEN answer END) AS education,
  MAX(CASE QuestionID WHEN 'country'   THEN answer END) AS country,
  MAX(CASE QuestionID WHEN 'age'       THEN answer END) AS age
FROM
  answer
WHERE
  DatasetID = 'test'
  AND QuestionID IN ('year', 'education', 'country', 'age')
GROUP BY
  PersonID
HAVING
  COUNT(*) = 4
  AND CAST(MAX(CASE questionID WHEN 'age' THEN answer END) AS numeric) > 25
;

Again, you will need to test the final query in your environment to see how much, if at all, these additional modifications affect the performance.


`make install` on a module, I get `pgxs.mk:114: recipe for target 'install' failed` I'm trying to install a PostgreSQL module (pg-bignum). When I try make install, I get the following at the bottom of the log

/usr/bin/install: will not overwrite just-created '/usr/share/postgresql/9.5/extension/bignum--0.8.0.sql' with './/sql/bignum--0.8.0.sql'
/usr/lib/postgresql/9.5/lib/pgxs/src/makefiles/pgxs.mk:114: recipe for
target 'install' failed
make: *** [install] Error 1

Currently using PostgreSQL-9.5 and Ubuntu.

That's a problem with the pg-bignum's Makefile. Filed upstream here.


SQL Server Restart time I am using below command to get SQL Server last restart time :

Select min(Login_time) from master.sys.sysprocesses

select getdate()

Can anyone help me with a query if possible : If the SQL Server last start time is within last 24 Hours from current select getdate() it should be written to another column that SQL Server got restarted last XX hours back along with SQL Server Last restart time.

It's very simple, tempdb database re-creates every time when SQL Server re-started. So, create_date of tempdb database is the date & time when SQL Server restarted/started.

SELECT  case when datediff(mi,create_date,getdate())<1440
        then 'SQL Server got restarted last '+cast(datediff(hh,create_date,getdate()) as varchar)+' hours '+cast(datediff(mi,create_date,getdate())%60 as varchar)+' minutes back. Restart Time is '+Cast(create_date as varchar)
        end
FROM sys.databases where database_id=DB_ID('tempdb')

NOTE: Above query would not return correct result if TF 3609 is enabled in SQL Server

So, in addition to, below query will also help you to find out SQL Serve Start time.

SELECT case when datediff(mi,sqlserver_start_time,getdate())<1440
then 'SQL Server got restarted last '+cast(datediff(hh,sqlserver_start_time,getdate()) as varchar)+' hours '+cast(datediff(mi,sqlserver_start_time,getdate())%60 as varchar)+' minutes back. Restart Time is '+Cast(sqlserver_start_time as varchar)
end
FROM sys.dm_os_sys_info

Thanks


Job Activity Monitor - Log File Viewer is showing Duration for running jobs As you can see in Log file Viewer it has a column Duration which change for a running job.
I want to include this column in my query (from  where I get jobname etc)
Any idea to make a SIMPLE script? My only reseacrh so far was sys.dm_exec_sessions and Total_elapsed_time BUT how to connect this to my query where I have my jobname etc?

My query so far;

SELECT
ja.job_id,
j.name AS job_name,
ja.start_execution_date,
ISNULL(last_executed_step_id,0)+1 AS current_executed_step_id,
Js.step_name
FROM msdb.dbo.sysjobactivity ja
LEFT JOIN msdb.dbo.sysjobhistory jh ON ja.job_history_id = jh.instance_id
JOIN msdb.dbo.sysjobs j ON ja.job_id = j.job_id
JOIN msdb.dbo.sysjobsteps js ON ja.job_id = js.job_id
AND ISNULL(ja.last_executed_step_id,0)+1 = js.step_id
WHERE ja.session_id = (SELECT TOP 1 session_id FROM msdb.dbo.syssessions
ORDER BY agent_start_date DESC) AND start_execution_date is not null
AND stop_execution_date is null;

It seems that, if jobhistory run_duration is null (meaning the job is still running), you could simply calculate the current run_duration using DATEDIFF between jobactivity start_execution_date and sysdatetime().

Since the normal Run_Duration from jobhistory is an int in HHMMSS format, there are some calculations to achieve that.

Something like:

SELECT ja.job_id
    ,j.NAME AS job_name
    ,ja.start_execution_date
    ,ja.stop_execution_date
    ,ISNULL(last_executed_step_id, 0) + 1 AS current_executed_step_id
    ,Js.step_name
    ,CASE
        WHEN jh.run_duration IS NULL
            THEN
                convert(int,RIGHT('0' + CAST(datediff(second, ja.start_execution_date, sysdatetime()) / 3600 AS VARCHAR),2)  +
                RIGHT('0' + CAST((datediff(second, ja.start_execution_date, sysdatetime()) / 60) % 60 AS VARCHAR),2)  +
                RIGHT('0' + CAST(datediff(second, ja.start_execution_date, sysdatetime()) % 60 AS VARCHAR),2))
        ELSE jh.run_duration
        END AS run_duration
FROM msdb.dbo.sysjobactivity ja
LEFT JOIN msdb.dbo.sysjobhistory jh ON ja.job_history_id = jh.instance_id
JOIN msdb.dbo.sysjobs j ON ja.job_id = j.job_id
JOIN msdb.dbo.sysjobsteps js ON ja.job_id = js.job_id
    AND ISNULL(ja.last_executed_step_id, 0) + 1 = js.step_id
WHERE ja.session_id = (
        SELECT TOP 1 session_id
        FROM msdb.dbo.syssessions
        ORDER BY agent_start_date DESC
        )
    AND start_execution_date IS NOT NULL
    AND stop_execution_date IS NULL;


Simple question about schema (is this supposed to happen?) I've created a simple database, a table, a schema, and a table with that schema ( so, 2 tables ).

--sysadmin
create database dba_schema
go

use dba_schema
go

create table tabela1 ( id int )
go

--LOGIN---------------------------------------------
USE [master]
GO
CREATE LOGIN [schema_user] WITH PASSWORD=N'schema_user',
DEFAULT_DATABASE=[dba_schema], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
GO
USE [dba_schema]
GO
CREATE USER [schema_user] FOR LOGIN [schema_user]
GO
USE [dba_schema]
GO
ALTER USER [schema_user] WITH DEFAULT_SCHEMA=[dbo]
GO

( Manually I gave read, write and DLL permissions )
------------------------------------------------

create schema schema1
go

create table schema1.tabela1 ( id int )
go

Then I connected with the user schema_user ( that has now only read, write, and ddl permission ).

But, even not giving permission to that schema, I could select both tables ( dbo schema, and the schema I've created )

select * from dbo.tabela1

select * from schema1.tabela1

Is this supposed to happen? I thought that, when we create a schema, we should GRANT permission to that schema, for a user to select from it, but as I see, after creating a schema, we should DENY permission on it, for user not to select. Is this approach right? Its default schema is DBO.

I would confirm that you are connecting using the correct login, and that you have not incorrectly assigned some other permission.

I executed the script that you provided above, and then used execute as to test the select permissions and received SELECT permission denied errors for both (as I would expect).

Adjusted comments slightly, but the following I received what I expected (SQL Server 2012)

/* Create new database */
CREATE database dba_schema
GO

use dba_schema
GO

/* Create new table in dbo schema */
CREATE TABLE dbo.tabela1 ( id int )
GO

/* Add new login */
USE [master]
GO
CREATE LOGIN [schema_user] WITH PASSWORD=N'schema_user',
DEFAULT_DATABASE=[dba_schema], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
GO

/* Add new user */
USE [dba_schema]
GO
CREATE USER [schema_user] FOR LOGIN [schema_user]
GO

/* Set default schema for user */
USE [dba_schema]
GO
ALTER USER [schema_user] WITH DEFAULT_SCHEMA=[dbo]
GO

/* Create new schema */
CREATE SCHEMA schema1
GO

/* Add new table to new schema */
CREATE TABLE schema1.tabela1 ( id int )
GO

/* Impersonate the new login of schema_user and query the tables */
EXECUTE AS LOGIN = 'schema_user';
/* Confirm we've impersonated correctly */
SELECT SUSER_NAME();
/* Run the selects */
SELECT * FROM dbo.tabela1;
SELECT * FROM schema1.tabela1;
REVERT;


Optimistic Paging and separate query for COUNT? I am using SQL Server 2012 FETCH/OFFSET after searching a lot that this will give me the best performance. But for paging to work, I need a total count.

There are 2 ways to achieve this:

Paging query can include COUNT(*) OVER() column
Separate query

Which one will be best? Here is my SQL:

ALTER PROCEDURE [dbo].[GetAllProducts] (@PageNumber int,
@PageCount int)
AS
BEGIN
    SELECT
        Products.[ID] AS [ID1],
        Products.[Name] AS [Name],
        Products.[Price] AS [Price],
        Products.[NewPrice] AS [NewPrice],
        Products.[ShortDescription] AS [ShortDescription],
        Products.[SKU] AS [SKU],
        Products.[ProductTypeID] AS [ProductTypeID],
        Products.[ImageID] AS [ImageID],
        Products.[Promotion] AS [Promotion],
        Products.[ParentID] AS [ParentID],
        Products.[Attributes] AS [Attributes],
        ROW_NUMBER() OVER (ORDER BY Products.[ID] ASC) AS [row_number]
    FROM [dbo].[Products] AS Products
    INNER JOIN [dbo].[Images] AS Images
        ON Products.[ImageID] = Images.[ID]
    ORDER BY Products.[ID] ASC
    OFFSET ((@PageNumber - 1) * @PageCount) ROWS
    FETCH NEXT @PageCount ROWS ONLY;
END

The Products table has more than 50,000 rows, sometimes static, sometimes changing a lot.

Assuming you need an exact count and not an approximation, and you want this to go fast without index scanning (yes, please!), a good place to start would be to create an indexed view that maintains the count of rows:

CREATE VIEW dbo.MyView
    WITH SCHEMABINDING
AS
    SELECT
        COUNT_BIG(*) AS NumberOfRows
        FROM [dbo].[Products];
GO

CREATE UNIQUE CLUSTERED INDEX IX_MyView
    ON dbo.MyView(NumberOfRows);

(Note: an ID column can't be added to this view and then the clustered index created on it.)

Yes, this will add a small amount of overhead to writes to the base table; it doesn't sound like that's going to be a problem in this case.

Then it's really easy and efficient to read a transactionally-consistent row count:

SELECT NumberOfRows FROM dbo.MyView WITH(NOEXPAND);

which can either be included in the same query, or separately. Personally, I'd try to keep it separate and return the value as an OUTPUT parameter. In either case, for an exact count, I'd strongly recommend using snapshot isolation to avoid out-of-sync issues.

This type of structure could also be created with a user table and a trigger, or a "business logic" approach... but because of the number of moving parts in either of those solutions, I really wouldn't recommend them unless there's no other choice.

If you do go this route, be sure to patch SQL Server to at least:

Cumulative Update 7 for SQL Server 2012 SP2
Cumulative Update 1 for SQL Server 2014 Service Pack 1
Cumulative Update 8 for SQL Server 2014 RTM

See the Knowledge Base article:

Incorrect results are returned in an indexed view after an Insert or Delete operation on the base table occurs in SQL Server 2014

For more details and background, see An Indexed View Bug with Scalar Aggregates by Paul White.


How to retrieve 'y' and 'n' comma separated values as 'yes' or 'no' from a single column I have a table in a database with these columns

id     name    facility_Type
1      Taluka    y,n,n,y,n

Now I want to retrieve the comma separated values like this

id      name     Ambulance   ward  male_doctors  female_doctors  operation_theator
 1    Taluka      yes         no      no             yes              no

Some databases would support something like this (Oracle, MySQL, PostgreSQL, SQLite).

select T.id,
       T.name,
       case substr(T.facility_type, 1, 1) when 'y' then 'yes' else 'no' end as Ambulance,
       case substr(T.facility_type, 3, 1) when 'y' then 'yes' else 'no' end as ward,
       case substr(T.facility_type, 5, 1) when 'y' then 'yes' else 'no' end as male_doctors,
       case substr(T.facility_type, 7, 1) when 'y' then 'yes' else 'no' end as female_doctors,
       case substr(T.facility_type, 9, 1) when 'y' then 'yes' else 'no' end as operation_theator
from T;

If you are on SQL Server you need to type out substring.

select T.id,
       T.name,
       case substring(T.facility_type, 1, 1) when 'y' then 'yes' else 'no' end as Ambulance,
       case substring(T.facility_type, 3, 1) when 'y' then 'yes' else 'no' end as ward,
       case substring(T.facility_type, 5, 1) when 'y' then 'yes' else 'no' end as male_doctors,
       case substring(T.facility_type, 7, 1) when 'y' then 'yes' else 'no' end as female_doctors,
       case substring(T.facility_type, 9, 1) when 'y' then 'yes' else 'no' end as operation_theator
from T;


Problem Insert Data to connected tables postgresql I have 2 existing tables: ‘projects’ and ‘units’.The two tables are connected through the project id. In the table 'projects' the column is called ‘id’ and generates every time a new project is inserted. In the table 'units' the same id is called ‘projekt_id’.
I now want to add new projects from a third table that is called ‘temp_units’. That table also has information that is relevant for the table ‘units’. So for every row in the ‘temp_units’ table I want to create a new row in the ‘projects’ table and also a new row in the ‘units’ table. Those two rows should be connected through the projects id.
I tried the following query but I get the error: more than one row returned by a subquery used as an expression.
Then I read somewhere about adding Limit = 1, so I added that to the query below (it is shown as a comment there). With Limit = 1 the code worked but all of the inserted data in the ‘units’ table is connected to the first project created in the 'projects' table.
At the moment it is like this:
projects
id
--
1
2
3

Units
Id | projekt_id
---------------
 1 | 1
 2 | 1
 3 | 1

But I need it like this:
projects
id
--
1
2
3

Units
Id | projekt_id
---------------
 1 | 1
 2 | 2
 3 | 3

I am using PostgreSQL 9.5.19. I would really appreciate any help
WITH insert_projekte AS
    (
    INSERT INTO projects (name, projekt_status_id, creator_id, editor_id, inserted_at, updated_at)
    SELECT name, projekt_status_id, creator_id, editor_id, inserted_at, updated_at
    FROM temp_units
    RETURNING ID
    )

INSERT INTO units (kgnr, ez, projekt_id, creator_id, editor_id, inserted_at, updated_at)
SELECT t.kg_ez, t.ez, (SELECT id from insert_projekte/*LIMIT = 1*/), t.creator_id, t.editor_id, t.inserted_at, t.updated_at
FROM temp_units t
RETURNING ID

So I figured it out eventually
        WITH insert_projekte AS
            (
            INSERT INTO projects (name, projekt_status_id, creator_id, editor_id, inserted_at, updated_at)
            SELECT name, projekt_status_id, creator_id, editor_id, inserted_at, updated_at
            FROM temp_units
            RETURNING ID, name
            )

INSERT INTO units (kgnr, ez, projekt_id, creator_id, editor_id, inserted_at, updated_at)
    SELECT t.kg_ez, t.ez, insert_projekte.id, t.creator_id, t.editor_id, t.inserted_at, t.updated_at
    FROM temp_units t
    JOIN insert_projekte on
    insert_projekte.name = t.name
    RETURNING ID

I always left the subquery in the second INSERT so it never worked before.
Thank you @CL. for the helpful suggestions.


Does msmdpump.dll pass the authentication info to SSAS? I'm using msmdpump.dll with basic authentication to access a SSAS OLAP cube from the internet. Does this method pass the auth info to SSAS so if I put permissions on the cube they get applied depending of the user I input in my connection string? If not, is there a way to do it?

Yes, msmdpump will pass the authentication through to SSAS.
Here is an article on how to get it all set up for SSAS 2005.
Here is one for IIS7 to SSAS 2008.


Programmatically copy index from one table to another, via a stored procedure? I use a stored procedure to copy the tables from one database to the other database. Right now it does

insert into copy
   select * from original

I would like to copy the index from the original table as well. What would be the best way to go about this ?

This is a really complicated thing to do in SQL code.  It's complicated because indexes have so many options.  Potential issues/complications:

Covering indexes
Included fields
Column order
Filtered indexes
Complicated index types (xml indexes, etc)

You will be much better off either scripting these out in SSMS or using the .NET SMO framework for this.

To appropriately account for all the above issues will require querying a large number of system tables and using CURSORs, WHILE loops, or other iterative constructs to get things in the right order.

It ain't pretty.