What is the correct method to pass range of dates to a SQL Server 2016 temporal table query where you need a set returned for each day? I have a SQL Server 2016 temporal table query that I’m having a hard time figuring out an efficient way to write.

Let’s say I have a temporal table called [Users] that each day has the total body of users in a group. Each day users can have a status of ('A', 'B', 'C', 'Y', 'Z')

If you wanted to select the current version of the table I would simply write

SELECT [UserID]
, [Status]  --where the user status would be listed as 'A', 'B', 'C', 'Y', or 'Z'
, [InsertDate]      --record creation date which isn't related to the System-versioning fields
, [SysStartTime]    --<start_date_time> which could be omitted from the simple query
, [SysEndTime]      --<end_date_time> which could be omitted from the simple query
FROM [Users]

If I wanted to look up how the table looked on 6am of October 2nd, 2017 I know could write:

SELECT [UserID]
, [Status]
, [InsertDate]
, [SysStartTime]
, [SysEndTime]
FROM [Users]
FOR SYSTEM_TIME AS OF '2017-10-02 06:00:00'

Easy enough and I could count the number of people with various statuses using a simple SUM function.

Where my problem begins is when I want to analyse trends over a longer period. In theory I want a picture of how the table looked on each day of that extended timespan. Like if I was looking at 20 contiguous days of data with the [Users] table having on average 5000 rows per a day I would hope that my query would return about 100000 records.

My initial thought was a to pass in a subquery to the 'FOR...AS OF' statement but SQL Server does not seem to like that. I have tried a bunch of things many of which look something like this:

SELECT [UserID]
, [Status]
, [InsertDate]
, [SysStartTime]
, [SysEndTime]
FROM [Users]
FOR SYSTEM_TIME AS OF (SELECT Fulldate FROM SetOfDates_Table)

Am I missing something obvious about how to pass a set of date values to 'AS OF'? Should I instead be using the 'SELECT..FROM..FOR..AS OF' as a function, pass a parameter to it then UNION the resulting data sets together?

Just for reference, I'm in a data warehousing environment where I'd want to schedule the query to happen in advance rather than needing it to run on demand.

While SYSTEM_TIME AS OF allows the use of literals and variables, it apparently does not allow you to use a correlated query to pass in different dates from something like a date dimension table.

With that in mind, here is a solution that uses a multi-statement Table-Valued User-Defined Function (TVF).  It takes in a StartDate and EndDate and uses a while loop to populate the return table.  In general, multi-statement TVF's usually don't perform as well as inline TVF's, but since you said you could schedule this query and not have to run on demand, the performance may still be acceptable.

Here, we create a temporal table called Customer.  We load 1 row and then have a series of update statements spaced 2 seconds apart to reflect some history

set nocount on
IF OBJECT_ID('dbo.Customer') IS NOT NULL
BEGIN
    ALTER TABLE dbo.Customer
        SET (SYSTEM_VERSIONING = OFF);
    DROP TABLE DBO.Customer
    DROP TABLE DBO.CustomerHistory
END
GO

CREATE TABLE dbo.Customer (
    Id INT NOT NULL PRIMARY KEY CLUSTERED
    ,NAME NVARCHAR(100) NOT NULL
    ,StartTime DATETIME2 GENERATED ALWAYS AS ROW START HIDDEN NOT NULL
    ,EndTime DATETIME2 GENERATED ALWAYS AS ROW END HIDDEN NOT NULL
    ,PERIOD FOR SYSTEM_TIME(StartTime, EndTime)
    )
    WITH (SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.CustomerHistory))
GO

INSERT INTO dbo.Customer VALUES (1,'Bill_1')
GO
WAITFOR DELAY '00:00:02'
GO
UPDATE dbo.Customer SET Name = 'Bill_2'
GO
WAITFOR DELAY '00:00:02'
GO
UPDATE dbo.Customer SET Name = 'Bill_3'
GO
WAITFOR DELAY '00:00:02'
GO
UPDATE dbo.Customer SET Name = 'Bill_4'
GO

Now, we define the multi-statement TVF

CREATE FUNCTION dbo.TemporalDataAsOf (
    @StartDate DATETIME
    ,@EndDate DATETIME
    )
RETURNS @Table_Var TABLE (
    NAME VARCHAR(100)
    ,StartDate DATETIME
    ,EndDate DATETIME
    )
AS
BEGIN
    DECLARE @DateWork DATETIME = @StartDate

    WHILE @DateWork <= @EndDate
    BEGIN
        INSERT INTO @Table_Var (
            [Name]
            ,[StartDate]
            ,[EndDate]
            )
        SELECT [Name]
            ,[StartTime]
            ,[EndTime]
        FROM [Customer]
        FOR SYSTEM_TIME AS OF @DateWork

        SET @DateWork = DATEADD(Second, 1, @DateWork)
    END

    RETURN
END
GO

Notice that the WHILE loop is incrementing the @DateWork variable by 1 second (easier to demo), but you can defined the granularity as a day.

Also, remember that temporal Start and End dates are UTC, so you'll need to make adjustments for that.

I ran my example at 7:00am Eastern Time (United States) which converts to UTC time of 11:00am

SELECT * FROM dbo.TemporalDataAsOf('2017-10-24 11:00:00', '2017-10-24
11:00:20') ORDER BY StartDate

| NAME   | StartDate               | EndDate                 |
|--------|-------------------------|-------------------------|
| Bill_1 | 2017-10-24 11:00:04.290 | 2017-10-24 11:00:06.320 |
| Bill_1 | 2017-10-24 11:00:04.290 | 2017-10-24 11:00:06.320 |
| Bill_2 | 2017-10-24 11:00:06.320 | 2017-10-24 11:00:08.347 |
| Bill_2 | 2017-10-24 11:00:06.320 | 2017-10-24 11:00:08.347 |
| Bill_3 | 2017-10-24 11:00:08.347 | 2017-10-24 11:00:10.373 |
| Bill_3 | 2017-10-24 11:00:08.347 | 2017-10-24 11:00:10.373 |
| Bill_4 | 2017-10-24 11:00:10.373 | 9999-12-31 23:59:59.997 |
| Bill_4 | 2017-10-24 11:00:10.373 | 9999-12-31 23:59:59.997 |
| Bill_4 | 2017-10-24 11:00:10.373 | 9999-12-31 23:59:59.997 |
| Bill_4 | 2017-10-24 11:00:10.373 | 9999-12-31 23:59:59.997 |
| Bill_4 | 2017-10-24 11:00:10.373 | 9999-12-31 23:59:59.997 |
| Bill_4 | 2017-10-24 11:00:10.373 | 9999-12-31 23:59:59.997 |
| Bill_4 | 2017-10-24 11:00:10.373 | 9999-12-31 23:59:59.997 |
| Bill_4 | 2017-10-24 11:00:10.373 | 9999-12-31 23:59:59.997 |
| Bill_4 | 2017-10-24 11:00:10.373 | 9999-12-31 23:59:59.997 |
| Bill_4 | 2017-10-24 11:00:10.373 | 9999-12-31 23:59:59.997 |


Handling delayed key info in real time data warehouse Our data warehouse is currently loaded with a traditional daily ETL batch job, but we're looking to soon implement a few stars in real-time (fed by streaming Kafka messages, FWIW).  So only a couple fact tables and dimensions will be in real-time, while the rest will remain loaded daily.  I was wondering how others have dealt with the issue of a real-time fact table with FKs into daily batch-loaded dimensions.

The scenario we have is our business is pushing for our Sales Workflow star schema to be in real-time so we can run intraday workflow analytics, but there are keys in the fact table, like into customers and sales reps (and several others), that'll still be populated daily.  Without turning the entire DW in real-time, what are some best practices for solving the "delayed" key problem?

One possible solution is to create missing dimension members solely based on what is available in the fact data, and then merge (aka upsert) the full dimension data as it arrives later.

For this to work, the attributes of your dimensions will need to be able to accommodate ‘unknown’.


PostgreSQL designing tool I am about to design a database which is going to run with PostgreSQL. I am used to the magnificent tool called MySQL Workbench for MySQL database. It is useful and it looks good which I kind of expect from a database designing software.

If I am about to learn a new database designing tool, I want it to be the most popular one. Therefore, my question is: what are the most popular tools for designing the database in PostgreSQL?

There are several options in the PostgreSQL world, most of them are listed in the PostgreSQL wiki:

http://wiki.postgresql.org/wiki/GUI_Database_Design_Tools

The next version of pgAdmin will include a ER designer as well as far as I know.

PowerArchitect works with PostgreSQL, though in the free version it is somewhat limited. In general it does not support DBMS specific features (or only a very limited set).

In the commercial world, nearly all of the "big names" support PostgreSQL as far as I know (including Sybase PowerDesigner and ERwin).

dbWrench is a not too expensive and seems to be quite nice.

TOAD data modeler is a bit more expensive but has an impressive feature list.

You might also want to check the options listed on this page: http://www.databaseanswers.org/modelling_tools.htm


Populate columns of table with other table that doesn't have relationship Hello I have 2 tables in PostgreSQL: the 1st one

"Products"
id
price
factor1
factor2

the columns factor1 and factor2 they both are empty and I want to populate them with the data of another table

"Article"
id
factor1
factor2

I want to populate the columns where the id of Product is the same as the Article but the tables doesn't have a relationship to each other or FK so how can I accomplish this?

I've tried this:

UPDATE Products
SET factor1 =
  (SELECT factor1
   FROM Article
   WHERE Article.id = Products.id)
WHERE EXISTS
  (SELECT factor1
   FROM Article
   WHERE Article.factor1 = Products.id)

but I get this error:

  SQL Error [42P01]: ERROR: relation "Article" does not exist
    Position: 213

The more compact way to write this would be:

UPDATE Products
SET factor1 = article.factor1
from Article
WHERE Article.id = Products.id and products.factor1 is null


DBF file exist while user has been dropped? I am new to Oracle. I have dropped some users from my database using:

DROP USER username cascade;

The issue is that the .dbf file still exists in the $ORACLE_HOME/dbs/oradata/ directory, which is creating disk space issues.

Can I remove the unused file directly or by some other processes? Also the size of the .trc file has increased to 16 GB. How can I clean that up?

I can delete these file directly but not sure that it may cause any start up or configuration issue, which I don't want.

Simply my concern is to get rid of disk space issue from the above unwanted files.

Datafiles are allocated to tablespaces, not users. Dropping the users will remove the tables etc. from the tablespace, but the underlying storage is not affected.

Don't delete the dbf files from the filesystem directly, you'll get your database into a mess. To remove them, find which tablespace the files belong to using the following statement:

select tablespace_name
from   dba_data_files
where  file_name = <file name with full path>;

You can then remove the file(s) by dropping the tablespace:

drop tablespace <tablespace_name> including contents and datafiles;

Before doing this you should verify that there aren't any objects still allocated to the tablespace for other users however. You can find this by running:

select * from dba_segments
where  tablespace_name = <tablespace to drop>;

If this returns anything, move the objects to another tablespace if you want to keep them before dropping the tablespace.


Transactional Replication to a Version Two Versions Older--Count by Name or Number? According to Microsoft SQL Server documentation, a "Subscriber to a transactional publication can be any version within two versions of the Publisher version." In this context, are versions named versions (e.g. 2005, 2008 and 2008 R2) or numerical versions (like 9.x, 10.x, 12.x)?

I'm trying to figure out whether a 2012 server can publish transactional replication to a 2005 subscriber. If, in the above context, versions are named versions, the answer appears to be no, for 2005 is three versions behind 2012; if they are numbered versions, the answer appears to be yes, for 2005 (9.x) is only two version behind 2012 (11.x).

Based on the example in the documentation, it appears the answer is numerical versions; however, I don't have a system of my own with instances of both versions to test this on and I'd like to be sure I have this down correctly before I advise someone else about it.

If Counting by Named Versions

Versions Back    Can 2012 transactional     Server Version
                 replicate to?
1                Y                          2008 R2
2                Y                          2008
3                N                          2005

If Counting by Numerical Versions

Versions Back    Can 2012 transactional     Server Version
                 replicate to?
1                Y                          10.x (2008 R2 & 2008)
2                Y                           9.x (2005)
3                N                           8.x (2000)

The SQL Server 2012 version of the MSDN page you have linked says (emphasis mine):

  A Subscriber to a transactional publication can be any version within two versions of the Publisher version. For example: a SQL Server 2005 Publisher can have Subscribers running SQL Server 2005, SQL Server 2008 (including SQL Server 2008 R2), or SQL Server 2012; and a SQL Server 2012 Publisher can have Subscribers running SQL Server 2005, SQL Server 2008 (including SQL Server 2008 R2), or SQL Server 2012.

So, apparently you need to look at numerical versions in this case – meaning that for a SQL Server 2012 publisher the subscriber version should be 11.* ± 2.


Finding reason behind high write IOPS on MySQL (RDS) server I am having performance issues with an AWS RDS instance (running MySQL 5.6).

This is the backend db to an XMPP server called Tigase. According to Tigase Inc. this type of db instance should be able to handle at least an order of magnitude more traffic than it currently does. It's very rare that the db is the bottleneck. Usually the Tigase server becomes a bottleneck before the db does.

When looking at our monitoring data in the AWS Management Console we see that it's mainly CPU and Write IOPS that is the issue. Read IOPS is negligible.

Since I haven't written Tigase it's hard for me to know the reason behind the high load. My question is therefore: how should I go about to find the queries and/or config settings that are the root cause behind this? What tools do you recommend that I use? I have fairly extensive DBA experience but I am a newbie when it comes to MySQL.

Below is a screenshot from MySQL Workbench's performance dashboard.

Thanks!

Shown RDS statistics are too global and broad to provide an answer to the question "Who is to blame for the increased load?".

There are serveral ways to profile your queries, but as you are using RDS with MySQL 5.6, I would recommend you to use PERFORMANCE_SCHEMA to do it easily, as you won't need external software (some of which is not fully RDS-compatible). Here you have a video with an introduction to query profiling, with examples like IOPS and temporary table creation monitoring by query pattern, user and table. For a more specific guide (specially for configuration of metrics), you can have a look at the official manual and the sys schema documentation.

For a quick check you can also have a look at your SHOW GLOBAL STATUS like 'com\_%'; and SHOW GLOBAL STATUS like 'Hand%'; at time interval to see if you have an increase on the number of SQL queries per unit of time or on the number of engine row operations per unit of time (the first one does not seem abnormal according to the graphs shown).

Your goal is to detect the offending queries first so that you can modify the SQL, your data structure, the MySQL configuration and/or anything peripheral (like if your RDS instance is still good enough) accordingly. An increase on Write IOPS normally may mean extra SQL load (obviously), but also many other things, such as: too many temporary tables or worse query plans being executed due to a change on the query optimiser plan or on your data cardinality/size. It is critical to identify the underlying cause first before taking any action.


Multiple PostgreSQL instances for one PGDATA folder Is it possible to have multiple PostgreSQL instances for one PGDATA folder, shared in a storage for example?

And if is not, is there is some kind of RDBMS capable of something like this?

No, that is impossible. PostgreSQL protects against that as good as it can, but if you manage to start a second postmaster, instant data corruption will ensue.

This question makes me wonder why you want that:

It cannot be scalability, because you have I/O as a bottleneck.
It cannot be high availability, because the shared storage is a single point of failure.

Such architectures like Oracle RAC are ill-conceived and mostly useless; nobody would design such a system these days.

The way to go is a shared-nothing cluster like patroni.


How can I get a list of dates between two given dates with mysql? I need to generate a list of dates with MySQL between two given dates. This list of dates must be filtered to the dates that correspond to Tuesday and Thursday. Is it possible?

A really easy way to do it is to have a table of numbers. Query this table for numbers less than the number of days between the two dates, and select the smaller date plus the number to return the dates themselves. Easy to stick a predicate in the where clause to handle Tuesdays and Thursdays too.


The request can be optimized? I have this SQL request:

SELECT
    m.id,
    IF(CURRENT_TIMESTAMP BETWEEN bc.start_date AND bc.expiration_date, FALSE, TRUE) AS hasFreeAccount,
    CONCAT(e.id, ' - ', e.name) AS establishment,
    bc.start_date,
    bc.expiration_date
FROM establishment e
    LEFT JOIN establishment_owner eo ON e.id = eo.establishment
    LEFT JOIN member m ON eo.member = m.id
    LEFT JOIN business_command bc ON m.id = bc.member
WHERE eo.member = 103
    AND (bc.start_date <= CURRENT_TIMESTAMP() OR bc.start_date is NULL)
    AND (bc.id NOT IN (
        SELECT bc.id
        FROM member m
            LEFT JOIN business_command bc on m.id = bc.member
            INNER JOIN establishment_owner eo on m.id = eo.member
        WHERE CURRENT_TIMESTAMP() > bc.expiration_date
            AND eo.member IN (SELECT m.id FROM member m LEFT JOIN business_command bc ON m.id = bc.member HAVING COUNT(m.id) > 1)
    ) OR bc.id IS NULL);

With this Explain plan:

The request works, I just want to know if I can optimize it.

Looking at your original query, I see that the only static WHERE clause is

WHERE eo.member = 103

This being the case, you should make establishment_owner the main table in the query:

SELECT
    m.id,
    IF(CURRENT_TIMESTAMP BETWEEN bc.start_date AND bc.expiration_date, FALSE, TRUE) AS hasFreeAccount,
    CONCAT(e.id, ' - ', e.name) AS establishment,
    bc.start_date,
    bc.expiration_date
FROM
    establishment_owner eo
    LEFT JOIN establishment e ON eo.establishment = e.id
    LEFT JOIN member m ON eo.member = m.id
    LEFT JOIN business_command bc ON m.id = bc.member
WHERE eo.member = 103
    AND (bc.start_date <= CURRENT_TIMESTAMP() OR bc.start_date is NULL)
    AND (bc.id NOT IN (
        SELECT bc.id
        FROM member m
            LEFT JOIN business_command bc on m.id = bc.member
            INNER JOIN establishment_owner eo on m.id = eo.member
        WHERE CURRENT_TIMESTAMP() > bc.expiration_date
            AND eo.member IN (SELECT m.id FROM member m LEFT JOIN business_command bc ON m.id = bc.member HAVING COUNT(m.id) > 1)
    ) OR bc.id IS NULL);

In other words, LEFT JOIN from establishment_owner instead of establishment.


How do I join 2 columns in query result (SQL report builder) As the image below shows, on my DB I also have 2 columns that appear in the results pane separately - first name and last name - (lower part of image)

How do I join them together in the report builder?

In SSRS you don't join them in the result set (unless you do it in the data source) but you can use expressions in the design to show them concatenated in your report.

Have a look at Expressions (Report Builder and SSRS).

Your expression would be something like

=Fields!FirstName.Value & Fields!LastName.Value


Passing argument in trigger dynamically CREATE TRIGGER audit_proc_tr
  AFTER INSERT OR UPDATE OR DELETE
  ON "log".hi
  FOR EACH ROW
  EXECUTE PROCEDURE "log".audit_proc(argument);

CREATE OR REPLACE FUNCTION fn_configpurchaseorder(configpurchaseorder, xmode text, xuserno integer)
  RETURNS text AS

 END;

I want to pass xmode is a argument of trigger

If you want to pass data to a trigger that is not provided by one of the predefined variables you could put that data in a temp table before you perform the INSERT/UPDATE/DELETE that will fire the trigger. Then you can retrieve the data from the table inside the trigger function. Temp tables are local to sessions so this is safe to do when multiple clients connect to the same database.


SQL Server : Merge and update multiple rows across columns I am trying to consolidate data in one table and merge it into another.

The data in the source destination table is as follows :

name                |dob                        |city   |occupation
-----------------------------------------------------------------------------------
galileo-galilei     |1900-01-01 00:00:00.000    |rome   |polymath
issac-newton        |1900-01-01 00:00:00.000    |london |mathematician-scientist
leonardo-da-cinci   |1900-01-03 00:00:00.000    |rome   |polymath

The data in the destination source table is :

sl_no   |name               |dob                        |city   |occupation
-----------------------------------------------------------------------------
1       |galileo-galilei    |1900-01-01 00:00:00.000    |       |
2       |galileo-galilei    |1900-01-02 00:00:00.000    |venice |
3       |galileo-galilei    |1900-01-05 00:00:00.000    |       |astronomer

The expected result in the destination table is :

name                |dob                        |city   |occupation
-----------------------------------------------------------------------------------
galileo-galilei     |1900-01-05 00:00:00.000    |venice |astronomer
issac-newton        |1900-01-01 00:00:00.000    |london |mathematician-scientist
leonardo-da-cinci   |1900-01-03 00:00:00.000    |rome   |polymath

My attempts using update-with-join and merge have not been successful.

Update-With-Join :

-- updates data from the first match only
update p
set p.city = s.city,
p.occupation = s.occupation
from person_update_with_join_test_primary p, person_update_with_join_test_secondary s
where p.name = s.name ;

Merge :

-- https://technet.microsoft.com/en-us/library/bb522522(v=sql.105).aspx
/*
The MERGE statement attempted to UPDATE or DELETE the same row more than once.
This happens when a target row matches more than one source row.
A MERGE statement cannot UPDATE/DELETE the same row of the target table multiple times.
Refine the ON clause to ensure a target row matches at most one source row, or use the GROUP BY clause to group the source rows.
*/
begin
merge person_update_with_join_test_primary as p
using person_update_with_join_test_secondary as s
on (p.name = s.name)
when not matched by target
then insert (name, dob, city, occupation)
values (s.name, s.dob, s.city, s.occupation)
when matched
then update set p.dob = s.dob
, p.city=(case when (len(s.city)>0) then  s.city else p.city end)
, p.occupation=(case when (len(s.occupation)>0) then  s.occupation else p.occupation end)
output $action, inserted.*, deleted.*;
end

I believe what I am looking for is similar to what has been posted here and here. However its not quite what I am looking for.

Are there any other ways to accomplish this other than using a cursor and an upsert (assuming that works)?

Update #1 :

Basically as long as the values in the source are not empty the most recent values (having highest id value) in the source are expected to be merged into the destination.

Eg : For #3 row in the source, the city column would not be considered to be merged into destination. Similarly for #2 , the occupation  column would not be considered for being merged into destination.
The the column name is a primary-key in the destination table.

I am trying to achieve the same state in the destination table as would have been expected if I were to iterate over the source data and update only the non-empty values in the destination - by using a query instead of doing it through application.

If you only ever have one row were the city and occupation columns are populated you can achieve it with a windowing function:

E.g:

DECLARE @Source TABLE(
    sl_no       INT
    ,name       NVARCHAR(30)
    ,dob        DATETIME2(3)
    ,city       NVARCHAR(30)
    ,occupation NVARCHAR(30)
);

INSERT INTO @Source
VALUES
    (1, 'galileo-galilei', '1900-01-01 00:00:00.000', NULL, NULL),
    (2, 'galileo-galilei', '1900-01-02 00:00:00.000', 'venice', NULL),
    (3, 'galileo-galilei', '1900-01-05 00:00:00.000', NULL, 'astronomer'),
    (4, 'issac-newton',    '1900-01-01 00:00:00.000', 'london', 'mathematician-scientist')

SELECT DISTINCT
    name
    ,MAX(dob)           OVER(PARTITION BY name) AS dob
    ,MAX(city)          OVER(PARTITION BY name) AS city
    ,MAX(occupation)    OVER(PARTITION BY name) AS occupation
FROM
    @Source

However, I suspect the reality is you could have multiple records and you always want to return the value from the most recent record that has data in those columns.  E.g. if your source was:

DECLARE @Source TABLE(
    sl_no       INT
    ,name       NVARCHAR(30)
    ,dob        DATETIME2(3)
    ,city       NVARCHAR(30)
    ,occupation NVARCHAR(30)
);

INSERT INTO @Source
VALUES
    (1, 'galileo-galilei', '1900-01-01 00:00:00.000', 'rome', NULL),
    (2, 'galileo-galilei', '1900-01-02 00:00:00.000', 'venice', NULL),
    (3, 'galileo-galilei', '1900-01-05 00:00:00.000', NULL, 'astronomer'),
    (4, 'issac-newton',    '1900-01-01 00:00:00.000', 'london', 'mathematician-scientist')

You could achieve what you want with:

SELECT
    s.name
    ,s.dob
    ,sc.city
    ,so.occupation
FROM
    @Source AS s
    CROSS APPLY(
        SELECT TOP 1 city
        FROM @Source AS s2
        WHERE s2.name = s.name
        AND city IS NOT NULL
        ORDER BY sl_no DESC
        ) AS sc
    CROSS APPLY(
        SELECT TOP 1 occupation
        FROM @Source AS s3
        WHERE s3.name = s.name
        AND occupation IS NOT NULL
        ORDER BY sl_no DESC
    ) AS so
WHERE
    s.sl_no = (SELECT MAX(sl_no) FROM @Source AS s4 WHERE s4.name = s.name)

Wrap that up into a merge or update (I'll do a merge for you) and you will get:

WITH src AS (
    SELECT
        s.name
        ,s.dob
        ,sc.city
        ,so.occupation
    FROM
        @Source AS s
        CROSS APPLY(
            SELECT TOP 1 city
            FROM @Source AS s2
            WHERE s2.name = s.name
            AND city IS NOT NULL
            ORDER BY sl_no DESC
            ) AS sc
        CROSS APPLY(
            SELECT TOP 1 occupation
            FROM @Source AS s3
            WHERE s3.name = s.name
            AND occupation IS NOT NULL
            ORDER BY sl_no DESC
        ) AS so
    WHERE
        s.sl_no = (SELECT MAX(sl_no) FROM @Source AS s4 WHERE s4.name = s.name)
)
MERGE INTO Destination AS tgt
USING tgt.name = src.name

WHEN MATCHED THEN UPDATE
SET dob = src.dob
    ,city = src.city
    ,occupation = src.occupation

WHEN NOT MATCHED THEN INSERT(name, dob, city, occupation)
VALUES(src.name, src.dob, src.city, src.occuptaion);

You are going to want to index the column you join on all the time (name in the above examples) for performance. Otherwise you will get many scans.


Postgres: Subquery with GROUP BY I'm trying to optimize a query (instead of repeating it a lot of time), with this NOT FUNCTIONAL CODE above (since subqueries only return 1 column):

SELECT
    e.pageview_identifier,
    e.created_at,
    e.pageview_current_url,
    e.pageview_mobile,
    (
        SELECT event_type, COUNT(event_identifier)
        FROM events v
        WHERE
            v.company_identifier = e.company_identifier AND
            v.user_identifier = e.user_identifier AND
            v.pageview_identifier = e.pageview_identifier
        GROUP BY v.event_type
    )
FROM events e
WHERE
    company_identifier = 'xyz' AND
    user_identifier = '01CDQZVSJFBDA8W444JS2CS3BA' AND
    event_type = 'page:view';

Basically, I want to retrieve the columns as

pageview_identifier, created_at, ..., event_type_a_count, event_type_b_count, ...

A FUNCTIONAL code that works is:

SELECT
    e.pageview_identifier,
    e.created_at,
    e.pageview_current_url,
    e.pageview_mobile,
    (
        SELECT COUNT(event_identifier)
        FROM events v
        WHERE
            v.company_identifier = e.company_identifier AND
            v.user_identifier = e.user_identifier AND
            v.pageview_identifier = e.pageview_identifier AND
            v.event_type = 'mouse:move'
    ) as mouse_move_count
FROM events e
WHERE
    company_identifier = 'xyz' AND
    user_identifier = '01CDQZVSJFBDA8W444JS2CS3BA' AND
    event_type = 'page:view';

But in this case, I would need to repeat a lot of time this subquery for each kind of event_type.

Was solved using window functions, like:

SELECT e.*
FROM (SELECT e.pageview_identifier,
             e.created_at,
             e.pageview_current_url,
             e.pageview_mobile,
             e.event_type,
             COUNT(*) FILTER (WHERE e.event_type = 'mouse:move') OVER (PARTITION BY e.company_identifier, e.user_identifier, e.pageview_identifier) as cnt_mouse_move,
             ...
      FROM events e
      WHERE e.company_identifier = 'xyz' AND
          e.user_identifier = '01CDQZVSJFBDA8W444JS2CS3BA'
     ) e
WHERE e.event_type = 'page:view';


SQLite error: foreign key mismatch when I try to make an INSERT command I'm trying to create an Associative Entity (N:N) in SQLite like this:

  [ Pet ---< VaccinePet >--- Vaccine ]

And, I have the follow code of my associative entity:

CREATE TABLE  VACINAPET (
        vp_date TEXT NOT NULL,
        vp_is_applied INTEGER DEFAULT 0,
        fk_pet INTEGER,
        fk_vaccine INTEGER,
        FOREIGN KEY (fk_pet) REFERENCES pet (pet_id),
        FOREIGN KEY (fk_vaccine) REFERENCES vaccine (vaccine_id),
        PRIMARY KEY (fk_pet, fk_vaccine)
);

BUT, I'm getting an error:

  foreign key mismatch - "VACCINEPET" referencing "vaccine": INSERT INTO
  VACCINEPET (vp_date, vp_is_applied, fk_pet, fk_vaccine) VALUES
  ('23/05/2018', 0, 1, 1);

When I try to use the INSERT command:

  INSERT INTO VACINAPET (vp_data, vp_is_aplicada, fk_pet, fk_vacina)
  VALUES ('23/05/2018', 0, 1, 1);

What could be wrong? I'm not so good in database... :(

MASTER DETAIL: I have data in Pet table and Vaccine table, they are not empty

The error message "foreign key mismatch" does not indicate a constraint violation, but that your database schema is wrong.

Assuming that the vaccine table and the vaccine_id column actually exist, the most likely reason is that the required index is missing, i.e., that vaccine_id is not the primary key (or at least unique).


Which java JDK is compatible with oracle instant client 10g I'm an intern stuck trying to retrieve data from Oracle 8i (a database that is as as I am)

The problem:

I need to write a java script that pulls data from Oracle 8i into a newer server.  The last client that is compatible with Oracle 8i is Oracle client 10g (10g instant client is still available for free on Oracle's website).

Documentation is scarce about 10g client compatibility. My server currently does not have java installed and my question is what version of java is compatible with 10g client?

Java does not use the Instant Client. If you want to connect an Oracle database from Java code, then you need the Oracle JDBC driver, not the Instant Client.

https://www.oracle.com/technetwork/database/application-development/jdbc/downloads/index.html

Example:

https://stackoverflow.com/questions/47966597/how-to-connect-java-with-oracle-8i-database-on-local-network

You will still need to find a place to download such ancient version of these though.


SQL Server 2008 Query Saved in Logs? Every time I create a query and run it to create a report I save the query in my Projects folder in case I have to rerun the query in the future or if I have to modify it for any reason but it seems that for some reason a query which I ran sometime in mid June was not saved. I need to see the code I used in the query because I am looking at the report and some numbers do not look right so I need to check the query to see if there was something written incorrectly in the code.

Is there anyway in some logs maybe where I might be able to see the code for the query I ran (and did not save) back in June? This would be AWSOME if I can. Thanks!

If your SQL Server has not been restarted there may a chance that your query text might have have been cached along with its execution plan. You may want to run the following to see if query text is still in the plan.

SELECT
    cp.objtype AS ObjectType,
    OBJECT_NAME(st.objectid,st.dbid) AS ObjectName,
    cp.usecounts AS ExecutionCount,
    st.TEXT AS QueryText,
    qp.query_plan AS QueryPlan
FROM
    sys.dm_exec_cached_plans AS cp
CROSS APPLY
    sys.dm_exec_query_plan(cp.plan_handle) AS qp
CROSS APPLY
    sys.dm_exec_sql_text(cp.plan_handle) AS st
WHERE
    st.text LIKE  '%query search criteria%'


Does CREATE/DROP VIEW require a SCH-M Lock As part of processing an Invoice, our 3rd party ERP creates, queries, then drops a view on one of the ERP Database tables.

We have separate concerns about this, but I'm genuinely curious: do either of the CREATE or DROP statements require a Schema Modification Lock on the Database or the tables that are involved in the view?

If not, are there any other lock types involved in the process that we should be concerned about?

This is easy enough to test on your own with a dummy table. It doesn't have to be anything fancy.

CREATE TABLE dbo.t1 (id INT)
INSERT dbo.t1 ( id )
VALUES ( 1 )

After the table is created, start a blocking transaction.

BEGIN TRAN
UPDATE dbo.t1 SET id = 2

--ROLLBACK

Over in another SSMS window, create a view.

CREATE VIEW dbo.YourMom
--WITH SCHEMABINDING /*this makes no difference*/
AS
SELECT *
FROM dbo.t1

This completes successfully, immediately, but trying to query the view or table will be blocked.

SELECT *
FROM dbo.YourMom

SELECT *
FROM dbo.t1

It's worth noting that views don't have any special properties. They're just a query.

I'm not sure why your vendor creates and drops them as part of a process, unless the definition needs to be dynamically generated because you have custom tables or something.


Should I use a surrogate key in a mapping table? I am deploying a mapping table which connects Items to ItemGroups

Though an Item will only ever be in a single ItemGroup please accept a priori it is not appropriate in my use case to append a column to the Items table.

So a table will be created with two columns:

ItemId INT NOT NULL
ItemGroupId INT NOT NULL

It is tempting to make ItemId the CI and PK for this table.... but is that appropriate? I think it makes sense as a primary key but since it is neither sequential nor perpetually-increasing it seems incorrect as a CI. I will never execute range queries against ItemId

... But a surrogate key solely for the purpose of a sequential CI also doesn't seem appropriate.

I am equally likely to execute either:

SELECT [ItemGroupId] FROM [Table] WHERE [ItemId] = @id
SELECT [ItemId] FROM [Table] WHERE [ItemGroupId] = @id

So as far as I can tell I have  few options:

ItemId is both the CI and the PK
ItemId is not the CI, but it is the PK
ItemId is neither the CI nor the PK but has a UNIQUE constraint
ItemId, ItemGroupId combination becomes the PK and ItemId has a UNIQUE constraint

Option 5:

(ItemGroupId, ItemId) (note the order) as the primary key (clustered)
Unique constraint (non-clustered) on ItemId

While searching by ItemId is not conducive to range scans (yet?), searching by ItemGroupId is 1, which makes it a good candidate to be the leading column of the clustered index key.

My normal design for this type of table is to index both combinations, i.e., (Column1, Column2) and (Column2, Column1) so I (and others) never have to think about whether it's indexed properly; it just is. In your case here, one combination is degenerate and so only one column goes into the key, but the non-key column is still covered by the index because it's part of the clustered index key. The situation becomes more complicated, of course, if there are non-key columns involved, but that's beyond the scope of what you asked here.

In this case, while you could flip-flop the primary key with the unique constraint, I chose the 2-column primary key on purpose because it will be more stable during the life of the table. This works to your advantage as a DBA (one word: Replication) and also to developers (it could be quite a bit of code updates if the primary key definition changes).

1 There might be an issue with terminology here. Searching by a single ItemId will return 0 or 1 row; searching by a single ItemGroupId (an incomplete key) returns >= 0 rows, and so is a seek + scan (it will show up as an index seek in the execution plan). Obviously this isn't a "range scan" on multiple ItemGroupIds. The idea is to physically group together (and order, in this case) the ItemIds for every ItemGroupId. I hope that's clear.


How do you connect to SQL Server 2008 Express when SA is disabled? I'm trying to create a database in SQL Server 2008 Express (as installed by Visual Studio) but I don't seem to have any permissions to do so. There must be a way to do this, or there would be no point in VS.NET installing it.

Internet articles suggest adding my domain login to the sysadmin role but I'm failing at the first hurdle:

C:\>sqlcmd -S .\sqlexpress
Sqlcmd: '-S': Unknown Option. Enter '-?' for help.

OK... so I then run with "-?" to get help and it says:

C:\>sqlcmd -?
Microsoft (R) SQL Server Command Line Tool
Version 10.0.5500.0 NT x64

usage: Sqlcmd            [-U login id]          [-P password]
  [-S server]            [-H hostname]          [-E trusted connection]
  [-d use database name] [-l login timeout]     [-t query timeout]

Notice how -S is a valid option! I don't understand why the help text doesn't seem to match up with the error text. Is -S an option or not? And if not, what is the command line to specify the server name?

you could start SQL Server in single-user mode.

When you do that, any windows administator can connect to SQL Server as a SQL Server administrator. Then you can re-create the sa user


Live update index in production database I have read Perform Index Operations Online provided by MSFT but I couldn't find any detail information for my questions.

My questions are:

What is the impact when I update (create/drop) non-clustered index in the live database?
If the database cannot be shut down, is there any specific way to reduce the risk and impact?

I mean But during index creation/updating period, will they cause
  data corruption because old index cannot be found

None of the documented T-SQL commands can cause data corruption.

The difference between online and offline index operations is in the fact of index's availability: while you do them online, indexes are still available, and when offline, they are not.

This means that your queries just cannot use nonclustered index for a period of its rebuild offline, and in case of clustered index rebuild the whole table is not available when doing it offline.

Online index operations come at a cost: they are slower, they are fully logged (while offline rebuild can be minimally logged if your db's recovery model is simple/bulk logged), and online index rebuild can result in long time blocking under some conditions described here: A SQL Server DBA myth a day: (8/30) unicorns, rainbows, and online index operations by Paul randal


Is using a JSON data type here appropriate? I am using MySQL 8.0 and I have an ACL table, which has two columns int UserID and tinyint GroupID. They both form a composite primary key.
A user can be in many groups, however there are only a few users who have such groups (administrators, moderators, etc.).
I have to get the user's ACL groups when they log in, but because of that I have to perform two SELECT queries, one of User table and another of ACL table.

Would it be a good choice to change the data type of GroupID to JSON and store all group IDs of a user in one field? I could then use INNER JOIN and get all necessary data with a single query and UserID would become the primary key of ACL table. Are there any drawbacks to such a structure or perhaps better solutions?
I intend to mostly just use the SELECT operation on the GroupID field, UPDATE and INSERT would be rarely used.

Simply no, with a single index on UserID you can easy and fast request the groups and delete also simlpy user from  groups.

That is all posible with json, but needs much more code and so can cause much more problems.

As long you don't need it in your application and also dont change it much, json is good to store a lot of data, but that is xml also.


Getting the count and sum of distinct record from Postgres jsonb attribute I have this Postgres 9.6 table def:

CREATE TABLE research (
   id int,
   data jsonb
);

and have the following sample data in it

 id |data
 ---|--------------------------------------------------------
 1  |{"name": "Sim Ltd", "sector": "business", "personnel": {"headcount": {"total": 100, "male": 50, "female": 50}}}
 2  |{"name": "EcoSmart", "sector": "business", "personnel": {"headcount": {"total": 500, "male": 460, "female": 40}}}
 3  |{"name": "HIDN", "sector": "government", "personnel": {"headcount": {"total": 431, "male": 121, "female": 310}}}
 4  |{"name": "RevDev", "sector": "government", "personnel": {"headcount": {"total": 15, "male": 10, "female": 5}}}
 5  |{"name": "NEFPAN", "sector": "non-profit", "personnel": {"headcount": {"total": 5, "male": 4, "female": 1}}}

I would like to get the COUNT of DISTINCT sectors and the SUM of total, male and female by DISTINCT sectors.I want the out like this:

Sector      |Count  |Total  |Male   |Female
------------|-------|-------|-------|--------
business    |2      |600    |510    |90
government  |2      |446    |131    |315
non-profit  |1      |5      |4      |1

Would greatly appreciate if someone can guide me with writing the SQL for it. I am use Postgres 9.6. Thanks

You can just use:

SELECT
    data->>'sector' AS "Sector",
    count(data->>'sector') AS "Count",
    sum((data->'personnel'->'headcount'->>'total')::integer)  AS "Total",
    sum((data->'personnel'->'headcount'->>'male')::integer)   AS "Male",
    sum((data->'personnel'->'headcount'->>'female')::integer) AS "Female"
FROM
    research
GROUP BY
    data->>'sector'
ORDER BY
    data->>'sector' ;

You use two PostgreSQL JSONB operators:

jsonb -> field  => gets the field out of the json(b), returning a json(b) object
jsonb ->> field => gets the field out of the json(b), returning it as text

And you will just get:

Sector     | Count | Total | Male | Female
:--------- | ----: | ----: | ---: | -----:
business   |     2 |   600 |  510 |     90
government |     2 |   446 |  131 |    315
non-profit |     1 |     5 |    4 |      1

However, for this kind of scenario, where your data is perfectly structured, if would make far more sense to use normalized SQL. This would be the structure of your table (without redundant data):

 CREATE TABLE normalized_research
 (
     id integer PRIMARY KEY,
     name text,
     sector text,
     male_headcount integer,
     female_headcount integer
 ) ;

This is how you would fill it in:

 INSERT INTO
     normalized_research
     (id, name, sector, male_headcount, female_headcount)
 SELECT
     id,
     data->>'name',
     data->>'sector',
     (data->'personnel'->'headcount'->>'male')::integer,
     (data->'personnel'->'headcount'->>'female')::integer
 FROM
     research ;

And this is the (much nicer, faster, safer) query you would make:

SELECT
     sector AS "Sector",
     count(sector) AS "Count",
     sum(male_headcount)+sum(female_headcount) AS "Total",
     sum(male_headcount) AS "Male",
     sum(female_headcount) AS "Female"
 FROM
     normalized_research
 GROUP BY
     sector
 ORDER BY
     sector ;

... that would be giving you exactly the same result.

You can find all the logic at this DBFiddle

Side notes

JSON was mostly invented to transfer and exchange data (through Ajax) between web services and web consumers (normally written in JavaScript).

Quoting JSON.org:

  JSON (JavaScript Object Notation) is a lightweight data-interchange format. It is easy for humans to read and write. It is easy for machines to parse and generate. It is based on a subset of the JavaScript Programming Language, Standard ECMA-262 3rd Edition - December 1999.

(emphasis mine)

It is perfectly well suited for its original purpose. But it has not been designed as a way to store data in a database. That was, somehow, an afterthought, because you can very easIly do it. You can store JSON as text, and you can already use any database, even if it is not aware of JSON.

In some occasions (normally linked to very variable data structures, and data structureS not known in advance) it is a good choice. But when your data is perfectly well structured, your schema (=structure) is well known in advance and doesn't need lots of flexibility: plain old normalized SQL is a better choice: you have data-type safety, consistency, easier indexing, referential integrity, faster access, ...


Is the system column "ctid" legitimate for identifying rows to delete? I have a table with hundreds of millions of rows that I need to delete data from.

The existing indexes are the most efficient.

I can however use the existing indexes to find the rows to delete by using the ctid values:

DELETE FROM calendar_event WHERE ctid IN
(SELECT ctid FROM calendar_event WHERE user_id = 5 LIMIT 100 FOR UPDATE)

What are the risks of relying on the ctid in this case? My worst case scenario is deleting the wrong the row.

The ROW SHARE lock taken by FOR UPDATE prevents concurrent write access that would change the physical location of the row. The manual:

  This prevents them from being locked, modified or deleted by other
  transactions until the current transaction ends. That is, other
  transactions that attempt UPDATE, DELETE, SELECT FOR UPDATE, SELECT FOR NO KEY UPDATE, SELECT FOR SHARE or SELECT FOR KEY SHARE of these
  rows will be blocked until the current transaction ends;

So the ctid should be stable for the duration of the command (or the transaction, even) unless you alter the row within the same transaction yourself. ctid is still a system column for internal use and the project will not offer any guarantees. If you have any unique (combination of) column(s) (including the PK) use that instead of the ctid.

However, I would use a CTE to materialize the selection and avoid unexpected results.

And without ORDER BY you select arbitrary rows for deletion. You might as well add SKIP LOCKED to minimize lock contention with concurrent transactions:

WITH cte AS (
   SELECT ctid
   FROM   calendar_event
   WHERE  user_id = 5
   LIMIT  100
   FOR    UPDATE SKIP LOCKED
   )
DELETE FROM calendar_event WHERE ctid IN (TABLE cte);

Related, with explanation for both considerations:

Postgres UPDATE ... LIMIT 1

About ctid:

How do I decompose ctid into page and row numbers?


Why isn't the objectname and indexname populated in deadlock graph? I got a request to check out some errors on a 2008 sp3 server (10.0.5500.0 - Enterprise Edition (64-bit)) and see if I could find deadlocks that were happening earlier.

Traceflag 1222 wasn't enabled and the server owners did not want to enable it either. I used extended events and pulled out some info using the following query:

    SELECT  CAST(event_data.value('(event/data/value)[1]',
                               'varchar(max)') AS XML) AS DeadlockGraph
FROM    ( SELECT    XEvent.query('.') AS event_data
          FROM      (    -- Cast the target_data to XML
                      SELECT    CAST(target_data AS XML) AS TargetData
                      FROM      sys.dm_xe_session_targets st
                                JOIN sys.dm_xe_sessions s
                                 ON s.address = st.event_session_address
                      WHERE     name = 'system_health'
                                AND target_name = 'ring_buffer'
                    ) AS Data -- Split out the Event Nodes
                    CROSS APPLY TargetData.nodes('RingBufferTarget/
                                     event[@name="xml_deadlock_report"]')
                    AS XEventData ( XEvent )
        ) AS tab ( event_data )

I received a lot of info back that looked like this:

<deadlock>
  <victim-list>
    <victimProcess id="process14846ccbc8" />
  </victim-list>
  <process-list>
    <process id="process14846ccbc8" taskpriority="0" logused="0" waitresource="KEY: 25:72057594099728384 (0800dfc12261)" waittime="2842" ownerId="7035766745" transactionname="UPDATE" lasttranstarted="2012-11-20T07:50:25.023" XDES="0x14d9ade3b0" lockMode="U" schedulerid="8" kpid="17268" status="background" spid="19" sbid="0" ecid="0" priority="0" trancount="2">
      <executionStack>
        <frame procname="" line="53" stmtstart="2674" stmtend="2980" sqlhandle="0x030019002cf5fc35c2449200d2a000000100000000000000" />
      </executionStack>
      <inputbuf />
    </process>
    <process id="process3d9bdc8" taskpriority="0" logused="224" waitresource="KEY: 25:72057594099793920 (2f00faaf5d8e)" waittime="2315" ownerId="7035765940" transactionname="user_transaction" lasttranstarted="2012-11-20T07:50:24.913" XDES="0xdad541970" lockMode="U" schedulerid="6" kpid="15448" status="background" spid="23" sbid="0" ecid="0" priority="0" trancount="2">
      <executionStack>
        <frame procname="" line="549" stmtstart="32736" stmtend="33274" sqlhandle="0x03001900b9667f2bc41ade003ea000000100000000000000" />
        <frame procname="" line="549" stmtstart="34038" stmtend="35374" sqlhandle="0x03001900f28a732c051bde003ea000000100000000000000" />
        <frame procname="" line="81" stmtstart="4284" stmtend="4392" sqlhandle="0x030019002cf5fc35c2449200d2a000000100000000000000" />
      </executionStack>
      <inputbuf />
    </process>
  </process-list>
  <resource-list>
    <keylock hobtid="72057594099728384" dbid="25" objectname="" indexname="" id="lock6b6a4ba00" mode="X" associatedObjectId="72057594099728384">
      <owner-list>
        <owner id="process3d9bdc8" mode="X" />
      </owner-list>
      <waiter-list>
        <waiter id="process14846ccbc8" mode="U" requestType="wait" />
      </waiter-list>
    </keylock>
    <keylock hobtid="72057594099793920" dbid="25" objectname="" indexname="" id="lock74edcf180" mode="U" associatedObjectId="72057594099793920">
      <owner-list>
        <owner id="process14846ccbc8" mode="U" />
      </owner-list>
      <waiter-list>
        <waiter id="process3d9bdc8" mode="U" requestType="wait" />
      </waiter-list>
    </keylock>
  </resource-list>
</deadlock>

How can I found out what the deadlocks are if objectname="" indexname="" ?

I can't answer as to why there is information missing, but I can point out how to extract it.

First, move to that database:

SELECT DB_NAME(25);

Take that result and:

USE <db_name>;

Now find out what these hobt_ids represent:

SELECT OBJECT_SCHEMA_NAME(object_id), OBJECT_NAME(object_id)
FROM sys.partitions
WHERE hobt_id IN (72057594099728384, 72057594099793920);

You can also find the batches involved (if they're still around) using:

SELECT * FROM sys.dm_exec_sql_text(0x03001900b9667f2bc41ade003ea000000100000000000000);
SELECT * FROM sys.dm_exec_sql_text(0x03001900f28a732c051bde003ea000000100000000000000);
SELECT * FROM sys.dm_exec_sql_text(0x030019002cf5fc35c2449200d2a000000100000000000000);

The offsets are there in the ExecutionStack so you can drill deeper into that output if you like (many examples of using offsets online).

EDIT Mr. Sandwiches pointed out the following Connect item (no longer available), where Microsoft has stated that this bug is fixed in SQL Server 2012. It won't be fixed in 2008/R2.

http://connect.microsoft.com/SQLServer/feedback/details/635391/objectname-indexname-not-populated-in-deadlock-graph-in-sql2008-sp1-and-sql2008-r2


psql: id SERIAL PRIMARY KEY not starting with 1? This is something I don't understand, can somebody explain?

I create a table

CREATE TABLE cali (id SERIAL PRIMARY KEY,
    ALK_from char,
    ALK_to char,
    AND_from char,
    AND_to char);

Then I simply try to insert some data from a csv file like so

\COPY cali FROM '/home/.../data/output/id_cali.csv' (FORMAT CSV);

Which gives me this Error:

ERROR:  value "4372840000" is out of range for type integer
CONTEXT:  COPY cali, line 1, column id: "4372840000"

I know that 4372840000 results in an integer overflow. That is why I'm trying to insert the values as chars in the first place.

But my understanding is that id SERIAL PRIMARY KEY is some sort of auto increment why is the value 4372840000 used for the column id?

The file looks like this

4372840012,4372840013,13,14,
4372840014,4372840014,15,15,

The table should be
create table cali (
    id serial primary key,
    alk_from bigint,
    alk_to bigint,
    and_from int,
    and_to int
);

And do
\COPY cali (alk_from, alk_to, and_from, and_to)
FROM '/home/.../data/output/id_cali.csv'
(FORMAT CSV);

So that it knows to not insert into the id serial column. The serial type is not a true type. From the manual:

The data types smallserial, serial and bigserial are not true types, but merely a notational convenience for creating unique identifier columns (similar to the AUTO_INCREMENT property supported by some other databases)
Thus, we have created an integer column and arranged for its default values to be assigned from a sequence generator


What is running on my server every night? My data disk queue is high every night at about 1:00 AM UTC. I did sql profiler trace and found that there are many queries running at the same time similar to these:

Can someone tell me what is going on there and how to stop or to control this?
Thanks in advance.

Version: SQL-Server 2014

EDIT:
I turned off the network, same issue. So, there is something inside SQL server.

From your profiler trace you should be able to see the Login and Host that the queries are coming from. I would then speak to your operations and/or development team to identify the application and function of the queries (or they should at least be able to point you in the direction of where you need to go/who you need to speak to).

Once you have that information you can see if the queries can be tuned in order to reduce the pressure on your disks.


Make a Copy of a Microsoft SQL Server 2005 database from a Mirror Copy I have a SQL mirror database in Microsoft SQL Server 2005. I would like to get a copy of it as a new database so I have the ability to read, write, and backup the database.

I cannot break the mirror and ALTER it to become a live database. This is a business requirement that we always have a copy on the other server.

I need a method to pull the data and all other parts of the database (triggers, stored procedures, etc) out of it into another database without breaking the mirror. I have tried SQL Server backups, which do not work on mirrors or snapshots of mirrors, and running through the copy database wizard which also did not work.

Is there any way to get a copy from the mirror database?

I agree this should be moved to the dba stack exchange, though since I answer there as well I will post it here should it be migrated.

The only way to access a mirrored database from the mirror server is through a database snapshot, which will require enterprise edition of SQL Server. It will also be READ ONLY, so this may not work for what you want.

The other alternative is to take the backups of the primary (these, I would hope, should be available) and restore them for your use somewhere else up to the time you need.

If using already taken backups is not possible, ask for a copy_only backup to be taken on the primary and given to you.


What is the difference between a union and an "isa" disjoint relationship? To me, it seems that a union and an "isa" disjoint relationship are the same. What is the difference between the two?

A isa usually indicates a subset. e.g. a male cat is a cat is a mammal. A union is the combination of two sets e.g. the union between cats and males is all cats and all males. Union is bidirectional whereas isa goes in one direction.


How would I grant select on a table being used in a different database to a user role in SQL? I have a view that's selecting rows from a table on a different database. When calling this view from my application I get an inner exception thrown saying the application user is not able to access the database being referenced. How do I grant permissions to this user on the separate database? I've tried it the traditional way but of course that user role wasn't found of that db. Should I create a user with the same name on the other db, this doesn't seem like it would work. I haven't been able to find a solution for this specific instance.

Open SQL Server Management studio 2008.
Expand the server instance > Security > Logins > and locate the
log in you have having an issue with.
Right click the user and select Properties.
Select User Mappings.
Locate the database you want the user to have access to and check
the checkbox in the map column.
Set the correct database membership for this user and click OK.
Try your code again, be sure to qualify the path properly like
[database_name]..[Table_Name] in your FROM.


MySQL Select rows with same id but different multiple values in another colum Maybe my question will be silly, but i'm new to Mysql

I have a table with user_id and some interest.

+------+------+
|userID|Inter |
+------+------+
|1     |sport |
+------+------+
|2     |it    |
+------+------+
|3     |game  |
+------+------+
|1     |it    |
+------+------+
|1     |game  |
+------+------+
|3     |it    |
+------+------+
|3     |sport |
+------+------+

Amount of interests can be huge(let say 20 or whatever)

Now i need to find all userId's that have interests it, game, sport;

Of course simple AND wont work as because of different rows.

So my question will be how to do it, so the output will be

+------+
|userID|
+------+
|1     |
+------+
|3     |
+------+

Thank you.

You can get it by using GROUP BY and COUNT(*)

create table users (user_id int, interest varchar(20));

insert into users values(1, 'sport');
insert into users values(2, 'it');
insert into users values(3, 'game');
insert into users values(1, 'it');
insert into users values(1, 'game');
insert into users values(3, 'it');
insert into users values(3, 'sport');

SELECT user_id
FROM   users
WHERE  interest IN ('game', 'it', 'sport')
GROUP BY user_id
HAVING count(*) = 3;

| user_id |
| ------: |
|       1 |
|       3 |

dbfiddle here


How to minimize log operations in SQL Server to avoid "log full" error I have a database in production which constantly get log file filled, its a data warehouse and have many jobs/queries running for obvious. Below is the error which I am getting

Msg 9002, Level 17, State 4, Line 7
The transaction log for database  is full due to 'ACTIVE_TRANSACTION'.

Now this make sense and I understand that SQL can not perform action cause its log file is filled up. I have two log files

D drive with unrestricted growth and autogrowth enabled [D drive size 180 GB]
E drive with static size  [E drive 120GB, log file size: 20 GB]

I did some research about this issue and found of possible solution to it: Source

Backing up the log.
Freeing disk space so that the log can automatically grow.
Moving the log file to a disk drive with sufficient space.
Increasing the size of a log file.
Adding a log file on a different disk.
Completing or killing a long-running transaction.

Now, assuming that I have limited space (i.e. is 180 GB + 20 GB) which I believe is good enough for a database in SIMPLE RECOVERY MODE. How possibly can I identify this issue and do a rectification before it occurs?

Replication:

I have tried to replicate this scenario via creating new sample database with below setting

and below query to get million rows and insert them into table

SET NOCOUNT ON;
DECLARE @SET_SIZE INT = 500000000;

CREATE TABLE dbo.Test500Million (N INT  PRIMARY KEY CLUSTERED NOT NULL);

;WITH T(N) AS (SELECT N FROM (VALUES (NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL)) AS X(N))
,NUMS(N) AS (SELECT TOP(@SET_SIZE) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS N FROM T T1, T T2, T T3, T T4, T T5, T T6, T T7, T T8, T T9)
INSERT INTO dbo.Test500Million(N)
SELECT N FROM NUMS;

DROP TABLE dbo.Test500Million

Now, while validating sys.databases and looking on error message I am finding both CHECKPOINTS & ACTIVE_TRANSACTION.

My Questions:

On my observation, I found that this is happening only with insert statement not with select. So, I presume that log entries will only be for INSERT/UPDATE  & not for SELECT statement in any case. Am I correct?
How can I minimize logging while inserting updating records? I already have SIMPLE RECOVERY MODE.
I have found that average throughput during this period went from 25 MB approx. to 1.2 MB. What does it mean?
Is there any other way to troubleshoot this issue, other than increasing disk space?
If only option is to shrink a file, then when should I do it? Can I do it when any active transaction is ON? [Production environment]

Kindly let me know if you need more stats which I can collect.

•On my observation, I found that this is happening only with insert statement not with select. So, I presume that log entries will only be for INSERT/UPDATE & not for SELECT statement in any case. Am I correct?

Select statement as such is not logged as compared to DML statements like insert , update and delete. If you see output of fn_dblog for select statement it wont have any entry. But there would be numerous entries for DML statements. Please note. The transaction log is maintained and information related to transaction is written there so that during crash recovery or recovery SQL Server can know what transactions were committed and what were uncommitted and reading this it can bring database to consistent state THIS IS WHY log is so much necessary. Without transaction log recovery is not possible

For all practical purposes every transaction is logged in SQL Server. So YOU CANNOT have a scenario where you run a transaction and nothing is logged in transaction log. You have no option to disable logging in SQL Server. I suggest you to read Logging and recovery in SQL Server.

  •How can I minimize logging while inserting updating records? I already have SIMPLE RECOVERY MODE

You are mistaken that logging does not happens in simple recovery model it does happen but moment transaction commits the transaction log is truncated and space used by logs generated for transaction is reutalized. As a fact logging in simple recovery is almost same as full recovery and difference lies when log truncation happens. Yes you can have minimal logging in Bulk Logged recovery model for only certain commands. For rest commands even in bulk logged recovery model logging would be full.

  •I have found that average throughput during this period went from 25 MB approx. to 1.2 MB. What does it mean?

I cannot understand this can you please explain it.

  •Is there any other way to troubleshoot this issue, other than increasing disk space?

As already suggested you are most likely to face issue if you keep @SET_SIZE INT = 500000000(even though its dummy) why such a high limit. Can you decrease it make it to 500000 and see how log file behaves. Make sure auotgrowth of log file is in MB and set to some sensible value. Do transaction in batches. Finally if you read Why transaction log keeps growing or runs out of space you can get some good idea.

  •If only option is to shrink a file, then when should I do it? Can I do it when any active transaction is ON? [Production environment], for guiding me but I need to release space. I have a large Data warehouse which deal with millions of data everyday

Please don't shrink any, data or log file. I would not recommend it. If you really want to shrink ONCE to reclaim space and you have no option other than shrinking you might as well try it. But again remember you have to rebuild all fragmented indexes after you are done with shrinking. You can try method given By Paul in This Article which says not to shrink and gives alternative of shrinking.

You can shrink log file only once but please remember log file grew because you ran transaction which forced it to grow so its basically your mistake not transaction log file. Instead of this whole process of growing and shrinking why not preallocate some amount of space to log file so that it can avoid autogrowth events

Another thing

The above configuration is very bad. 1 MB autogrowth for data file is bound to cause issues. To set correct value please read the article Autogrowth settings. This will help you calculate correct autogrowth setting.


Polymorphic Association - is it bad? In the following schema:

Collections
   Upvotes
Reports
   Upvotes
Reviews
   Upvotes

I'm tempted to have a single Upvotes table with a single "entityId" column that stores either the CollectionId, ReportId, or ReviewId.  There will also be an enum for Type - storing either collection, report, or review.

The entityId will always be required, and the logic will always make sure that inserts enforce uniqueness across each type.

The benefits of this is that adding another type is just a matter of expanding the enum.  Everything will live on a single table with no redundancy.

The cost seems to be the added complexity on the logic side, which is contained to the one place in my application logic that inserts new entities into the table.

Practically speaking, is there anything wrong with this approach?  What would be some other reasons to avoid this?

I can't recommend polymorphic associations.

In my experience, this will lead to data consistency issues since you can't use a foreign key constraint in the schema. That means you're reliant on your application's writes to this part of the database being bug-free, and no one manually altering the data.

It can also make the queries which join Upvotes to >1 of the other tables more complicated/harder to understand.


Are there any good and free tools for managing a PostgreSQL database? Before, I have used phpmyadmin to manage an MySQL database, but now I would like to manage a PostgreSQL database. The PostgreSQL database is on a server, but not a webserver, so I don't use PHP.

Are there any good and free tools for managing a PostgreSQL database?

I have tried with pgAdmin III but it was a far from intuitive application to use, compared to phpmyadmin that I have used before. What are PostgreSQL DBAs usually using? do they use graphical tools like pgAdmin III or is it mostly command line tools?

pgAdmin and psql, those are the tools I use. A PHP-script isn't very usefull for database management, too slow and many problems with transactions: What to do when something goes wrong? Commit or rollback? This makes tools like phpmyadmin and phpPgAdmin pretty useless, when the script is done, the database connection will be closed. There is nothing to commit or rollback, your SQL script is already gone.

Learn SQL and any tool that can keep it's databases connections open, will do the job.


Stop SQL Server service(s) before defragmenting drive? Our production SQL Server 2005 database's data files live on a separate physical drive, which Microsoft Windows 2003's Disk Defragmenter tool reports as 99% fragmented.

We scheduled a task to defragment this drive at 3:00 a.m. on a Saturday morning. The job completed after 40 minutes with no apparent errors. However, the drive remains heavily fragmented.

Should we have stopped SQL Server service(s) before defragmenting?

CONTEXT

Per requests for context: We have a Microsoft SQL Server 2005 instance (9.00.5324.00) running 32-bit Windows Server 2003 (SP2) on Dell PowerEdge 2950 hardware, circa 2007, with 4GB RAM. The PowerEdge 2950 has four 68GB drives configured as RAID-1 to create two 68GB virtual disks: (1) C (boot and OS) & D (pagefile, miscellaneous other data); and (2) E (SQL data). To my knowledge, IT staff have never defragmented any of these drives...Disk Defragmenter reports file fragmentation of 66% (C), 77% (D), and 99% (E). Performance Monitor reports the following average results: "Paging file: % usage" = ~6.8%; "SQL Server: Buffer Manager - Page life expectancy" = 20 seconds; and "PhysicalDisk: Avg. disk sec/write, drive E" = between 300 and 1,100 ms. We're due for a much-needed hardware and SQL Server upgrade in a few months time (viz., new hardware, 64-bit Windows Server 2012, 64-bit SQL Server 2012, 12GB RAM), but, due to end-user performance, want to alleviate the issue as much as possible. Thus the thinking a file defrag might help for drive E, the main SQL data drive.

As an aside, last week we pulled two failed drives and rebuilt the array...not sure that matters. We contract with another IT team to maintain the server, so we do not have direct access to the equipment...our organization just pays for services.

We can afford the downtime during regularly scheduled maintenance windows (weekly) as well as out-of-band downtime, as necessary, overnight.

Your problem is not disk fragmentation. Your problem is RAM and application table scans:

  4GB RAM ... 68GB ... Page life expectancy 20 seconds

You need way more RAM. As in your new server should have way, way, way, way more than 12GB. Start with 64 GB, it costs basically dimes. And yes, fix your app to use indexes. 20 seconds is very clear indication of table scans that trash the buffer pool. You need to fix the app, add the required indexes and fix your queries. For your case defragmenting the drives is as much a red herring as red herrings get.

Oh, and please move the log to separate physical spindles from data. Alone.


Lack of space in SYSTEM and SYSAUX tablespace in oracle 11g R2 SYSTEM and SYSAUX Tablespace size of my DB is in the following status now:

SYSTEM  - PCT_USED= 99
SYSAUX  - PCT_USED= 95

I believe the db is in critical situation now. What can I do to bring back it to the normal condition and what are the standard ways that should follow to keep SYSTEM and SYSAUX tablespace files safe.

Any help will be appreciated.

The SYSTEM and SYSAUX tablespaces are reuqired by the RDBMS to function correctly.

The SYSAUX tablespace

A list of objects that reside in the SYSAUX tablespace can be retrieved using the V$SYSAUX_OCCUPANTS view. This view displays the following information:

OCCUPANT_NAME        VARCHAR(64)    Occupant name
OCCUPANT_DESC        VARCHAR(64)    Occupant description
SCHEMA_NAME          VARCHAR2(64)   Schema name for the occupant
MOVE_PROCEDURE       VARCHAR2(64)   Name of the move procedure; null if not applicable
MOVE_PROCEDURE_DESC  VARCHAR2(64)   Description of the move procedure
SPACE_USAGE_KBYTES   NUMBER         Current space usage of the occupant (in KB)

Reference: V$SYSAUX_OCCUPANTS

The SYSAUX table is home for the following components:

SYSAUX Table                         Previous Location
------------------------------------ --------------------------
Analytical Workspace Object Table    SYSTEM
Enterprise Manager Repository        OEM_REPOSITORY
LogMiner                             SYSTEM
Logical Standby                      SYSTEM
OLAP API History Tables              CWMLITE
Oracle Data Mining                   ODM
Oracle Spatial                       SYSTEM
Oracle Streams                       SYSTEM
Oracle Text                          DRSYS
Oracle Ultra Search                  DRSYS
Ora interMedia ORDPLUGINS Comp.      SYSTEM
Ora interMedia ORDSYS Components     SYSTEM
Ora interMedia SI_INFORMTN_SCHEMA C. SYSTEM
Server Manageability Components      -
Statspack Repository                 User-defined
Oracle Scheduler                     -
Workspace Manager                    SYSTEM

Reference: Database Components and the SYSAUX Tablespace

If you can reduce the size of the data being stored by the components, then you can reduce the size of the required SYSAUX tablespace.

The largest portion of the SYSAUX tablespace is occupied by the Automatic Workload Repository (AWR). The following size recommendations are provided by Oracle to determine the AWR size and to a large portion the size of the SYSAUX tablespace:

Number of CPUs                                 2    |     8    |    32
Number of concurrently active sessions        10    |    20    |   100
Number of user objects: tables and indexes   500    | 5,000    |50,000
Estimated SYSAUX size with default config    500 MB |     2 GB |     5 GB

Reference: Controlling the Size of the SYSAUX Tablespace

If your database has been constantly growing, or if you are monitoring a lot of objects with Enterprise Management Console, then your SYSAUX tablespace will slowly fill up.

The SYSTEM tablespace

The SYSTEM tablespace always contains the data dictionary tables for the entire database. So if your database grows in size due to new objects, then your SYSTEM tablespace will require more space too.

Data Dictionary

What does the data dictionary contain:

The definitions of all schema objects in the database (tables, views, indexes, clusters, synonyms, sequences, procedures, functions, packages, triggers, and so on)
How much space has been allocated for, and is currently used by, the schema objects
Default values for columns
Integrity constraint information
The names of Oracle users
Privileges and roles each user has been granted
Auditing information, such as who has accessed or updated various schema objects
Other general database information

Reference: The Data Dictionary

Solutions:

Reduce the amount of monitored objects in EM
Resize the SYSAUX tablespace according to the recommendations in above table.

ALTER DATABASE DATAFILE '/path/systemSID.dbf' RESIZE 1000M;

Resize the SYSTEM tablespace

ALTER DATABASE DATAFILE '/path/sysauxSID.dbf' RESIZE 1000M;


MongoDB Sharding : What should be the Sharding key if we want to find records with more than one field I am having collection ("links") with listed fields:

collection-name: "links"
1) url
2) title
3) score
4) description
5) ratings (embedded_many)
and having index on "url", "title", "score"

because I need to find links based on these ("url", "title", "score") fields. query like : matching "url", "title" and "score" equal, gte, lte.

I would like to apply sharing on links collection.

Please guide me what should be the sharing-key and why ?

1) Cardinality for each field
url: 50,40,059
title: 50,10,105
score: 50,40,059

2) If null values exists on those fields
title: could have null value.

3) Is those fields changing?
only 'score' is changing after period of time.

From what you share the url seems the best candidate for shard key. Has good cardinality, its not monotonically increased  (since is url), does not modified and don't contain null values.

Additionally your reads contain the url will directed to only one shard, which is the best scenario.

We can discuss that further on chat after Xmas, because sharding isn't always easy task.

Antonis


Mysql innoDB write operations are extremely slow I'm having serious performance problems with MySQL and the InnoDB engine. Even the simplest table makes writing operations (creating the table, inserting, updating and deleting) horribly slow, as you can see in the following snippet.

mysql> CREATE TABLE `test` (`id` int(11) not null auto_increment,
   -> PRIMARY KEY(`id`)) ENGINE=InnoDB;
Query OK, 0 rows affected (4.61 sec)

mysql> insert into test values ();
Query OK, 1 row affected (1.92 sec)

mysql> insert into test values ();
Query OK, 1 row affected (0.88 sec)

mysql> insert into test values ();
Query OK, 1 row affected (1.10 sec)

mysql> insert into test values ();
Query OK, 1 row affected (6.27 sec)

mysql> select * from test;
+----+
| id |
+----+
|  1 |
|  2 |
|  3 |
|  4 |
+----+
4 rows in set (0.00 sec)

mysql> delete from test where id = 2;
Query OK, 1 row affected (0.28 sec)

mysql> delete from test where id = 3;
Query OK, 1 row affected (6.37 sec)

I have been looking at htop and the long waiting times are not because of abnormal CPU load. It's almost zero, and memory usage is also normal. If I create the same table using the MyISAM engine, then it works normally. My my.cnf file contains this (if I remember right I haven't changed anything from the default Debian configuration):

[client]
port        = 3306
socket      = /var/run/mysqld/mysqld.sock
[mysqld_safe]
socket      = /var/run/mysqld/mysqld.sock
nice        = 0

[mysqld]
user        = mysql
pid-file    = /var/run/mysqld/mysqld.pid
socket      = /var/run/mysqld/mysqld.sock
port        = 3306
basedir     = /usr
datadir     = /var/lib/mysql
tmpdir      = /tmp
language    = /usr/share/mysql/english
skip-external-locking
bind-address        = 127.0.0.1
key_buffer      = 40M
max_allowed_packet  = 16M
thread_stack        = 128K
thread_cache_size       = 8
myisam-recover         = BACKUP
max_connections        = 100
table_cache            = 64
thread_concurrency     = 10
query_cache_limit   = 1M
query_cache_size        = 40M
log_slow_queries    = /var/log/mysql/mysql-slow.log
long_query_time = 2
log-queries-not-using-indexes
expire_logs_days    = 10
max_binlog_size         = 100M

[mysqldump]
quick
quote-names
max_allowed_packet  = 16M

[isamchk]
key_buffer      = 16M
!includedir /etc/mysql/conf.d/

I have also tried to restart the server, but it doesn't solve anything.

The slow queries log doesn't give any extra information.

innodb_flush_log_at_trx_commit = 2 for better speed (=1 for better safety)
sync_binlog = 0 if you are not using the binlog.
"Batch" INSERTs — many rows in a single INSERT statement. (If you have lots, do 100 at a time.)  LOAD DATA is also excellent for loading.
innodb_buffer_pool_size = 70% of available RAM.
auto_commit = 1, or use BEGIN ... COMMIT
thread_stack = 256K I'm surprised it did not crash with your small value.

Still, the times (1s-6s) are grossly unreasonable.  Even with everything set "wrong", 0.1s for a simple INSERT should be expected.

Is this running in a "cloud"?  Is something else going on?


Select statement from 3 tables with join How can I select from 3 tables (task, status, employee)?

Task

TASK_ID (PK),
TASK_NAME ,
DESCRIPTION ,
STATUS_CODE (FK for status table)
START_DATE ,
END_DATE ,
TASK_TYPE_CODE (FK) ,
PRIORITY_CODE(FK) ,
EMPLOYEE_ID (FK for employee table)

Status

STATUS_CODE (PK) ,
STATUS_NAME

Employee

EMPLOYEE_ID (PK),
EMPLOYEE_NAME

I want to select task_id, task_name, and end_date "from-to" (any date from the calendar) to (any end date) from the task table, status_ name from the status table, and employee_name from the employee table.


Can I get Oracle to favor particular indexes? Oracle 11.2 R2 RAC

I have a database which has many dynamic and ad hoc queries run again it.  The stats are all up to date, but often because of large numbers of complex joins and heavy parameterization, the CBO picks a less than optimal plan.

There are a couple of tables that are extremely common and if they exist in the query, its almost always a win to force a particular index rather than a full table scan (which is what it tries to pick most often).

Is there a way to artificially get the CBO to favor particular indexes without having to hint individual queries? Not a hint on the queries, but a way to make the index itself look cheaper or such?

The cost of an index depends on its clustering factor.

Clustering factor measures how well aligned are the rows in the table - in respect of the columns of the index in question. It is calculated as: inspect all the rows from table in order (order is defined by the index columns). Every time a row is found to be in a different (not the currently inspected) block, increase the clustering factor by 1. If consecutive values in a table are near each other in the same block, the clustering factor of the index will be low. If consecutive values are scattered across the table, the clustering factor will be high.

Simply put, the lower the clustering factor is, the cheaper the index becomes.

Despite the myth, the clustering factor can not be decreased by rebuilding the index, because it depends on the table data distribution. However, you can set the clustering factor manually by:

begin
  dbms_stats.set_index_stats
  (
   ownname       => 'USER1',
   indname       => 'INDEX1',
   clstfct       => 1234,
   no_invalidate => false
  );
end;
/

Where 1234 is the desired clustering factor. The lowest valid value of the clustering factor is the number of the table blocks. The highest valid value of the clustering factor is the number of the table rows. But this does not mean you can not set it lower or higher.

This setting will be lost on the next index statistics collection.

You can use the above to change other statistics as well that have effect on the cost: blevel (indlevel) or leaf_blocks (numlblks).


What can be the purpose of this trigger? I stumble on this trigger when trying to analyze some DB:

Create trigger [tbl_Details_Trigger] on [tbl_Details]
    Instead of Insert
    As
      Insert into [tbl_Details]
      Select * from inserted

To me it looks like this trigger doesn't do anything special, and if it will be dropped the outcome will be the same. Am I right? Am I missing something?

You are correct; since this trigger is an INSTEAD OF INSERT trigger that simply does an insert of inserted data into the table the trigger is defined on, the only outcome will be to insert the data as if the trigger did not exist.

As with any and all changes, before you delete this trigger in production, ensure you test the change in your development/test environment.

Interestingly, if the table includes an IDENTITY column, you won't be able to create the trigger.  Consider this repro, which runs on SQL Server 2016+:

USE tempdb;
DROP TABLE IF EXISTS dbo.insert_test;
CREATE TABLE dbo.insert_test
(
    i int NOT NULL
        CONSTRAINT PK_insert_test
        PRIMARY KEY CLUSTERED
        IDENTITY(1,1)
    , d varchar(100) NOT NULL
);
GO
CREATE TRIGGER insert_test_trigger ON dbo.insert_test
INSTEAD OF INSERT
AS
BEGIN
    SET NOCOUNT ON;

    INSERT INTO dbo.insert_test (i, d)
    SELECT i, d
    FROM inserted;
END
GO

INSERT INTO dbo.insert_test (d)
VALUES ('test');

SELECT *
FROM dbo.insert_test;

Results:

  Msg 544, Level 16, State 1, Procedure insert_test_trigger, Line 8 [Batch Start Line 24]
  Cannot insert explicit value for identity column in table 'insert_test' when IDENTITY_INSERT is set to OFF.


innobackupex is failing while exporting backup I am using perl script which is working fine in one of my other box which used to take individual schema backups & finally take all schema together using percona innobackupex.

I have following information in the Log(posting One day Log details):

--slave-info is used with --no-lock but without --safe-slave-backup. The
binlog position cannot be consistent with the backup data.
Died at /apps/mysql/scripts/mysql_backup.pl line 214.

  [Fri Oct 26 04:20:01 2012] [14670]
  [Fri Oct 26 04:20:01 2012] [14670] Starting individual backups
  [Fri Oct 26 04:20:01 2012] [14670]
  [Fri Oct 26 04:20:01 2012] [14670] Backing up proddb
  [Fri Oct 26 04:20:01 2012] [14670] Creating /tmp/mysql_backup-14670.fifo
  [Fri Oct 26 04:20:01 2012] [14670] Starting /usr/local/bin/zipmt -s -t 4
  -c - < /tmp/mysql_backup-14670.fifo > /apps/dbbackup/proddb02.backup-
  2012-10-26-042001-proddb.dmp.bz2
  [Fri Oct 26 04:20:01 2012] [14670] Starting /usr/bin/innobackupex
  --slave-info --no-lock --stream=tar --databases proddb ./ >
  /tmp/mysql_backup-14670.fifo
  [Fri Oct 26 04:20:02 2012] [14670] FAIL: /usr/bin/innobackupex
  --slave-info --no-lock --stream=tar --databases proddb ./ >
  /tmp/mysql_backup-14670.fifo failed 256

Full details are in /apps/dbbackup/backup.log.

 Cause:
   /usr/bin/innobackupex --slave-info --no-lock --stream=tar --databases
  proddb ./ > /tmp/mysql_backup-14670.fifo failed 256

Named pipes are existing in the /tmp even after backup getting failed :

  ls -lrth /tmp
  =============

 prw-r--r--. 1 root root    0 Oct 25 04:20 mysql_backup-18215.fifo
 prw-r--r--. 1 root root    0 Oct 26 04:20 mysql_backup-14670.fifo
 prw-r--r--. 1 root root    0 Oct 27 04:20 mysql_backup-11278.fifo
 prw-r--r--. 1 root root    0 Oct 28 04:20 mysql_backup-7163.fifo
 prw-r--r--. 1 root root    0 Oct 29 04:20 mysql_backup-4191.fifo
 prw-r--r--. 1 root root    0 Oct 30 04:20 mysql_backup-595.fifo

Any idea, why the backup is failing?

Try with option: --safe-slave-backup

This is for consistent backup on the slave machines.
This option basically stops the slave SQL activity dueing the backup
of individual DBs.
You will see slave SQL thread error and initilization messages
regularly during the backup.

Hope this helps. Please let us know back on the results...


SQL Server: Identifying object closest to the end of a datafile TL\DR

I'm looking for a way to efficiently identify the object located closest to the end of a SQL Server data file.  This approach needs to remain performant against large data files.

What I have so far

The following query utilizes an undocumented Dynamic Management Function that shipped with SQL 2012: sys.dm_db_database_page_allocations; this DMF provides a rough equivalent to the DBCC IND command.

The following query identifies the last object in a given data file (Warning: Don't run this against a database larger than 25 GB unless you want to cancel it at some point):

-- Return object with highest Extent Page ID
SELECT   files.name as logical_file_name
        , files.physical_name as physical_file_name
        , OBJECT_SCHEMA_NAME(object_id) + N'.' + OBJECT_NAME(object_id) AS object_name
        , alloc.*
FROM sys.dm_db_database_page_allocations(DB_ID(), NULL, NULL, NULL, NULL) alloc
    INNER JOIN sys.database_files files
        ON alloc.extent_file_id = files.file_id
WHERE is_allocated = 1
    AND files.name = 'Logical_FileName'
ORDER BY files.name , files.physical_name, extent_page_id DESC

What's wrong with this approach

As the Warning above implies, this query will run slower as the size of the database increases because the function is really designed for a pointed approach to look at a specific object and not look at a specific data file in question.  When passing in the NULL parameters as I did, this function likely iterates through all objects within the database behind the scenes and spits out the combined output.  This accomplishes what I need, but it does so in a very brute-force way that doesn't lend itself to optimizations.

What I'm asking for

I'm hoping there's a way to iterate through the GAM, SGAM, and/or IAM chains to quickly identify the object at the end of a given data file.  I'm assuming I have to push this approach outside of TSQL to something like PowerShell and go back to using DBCC PAGE calls, or something of that nature, traversing page allocation maps to find what the last object for a given data file is.  ... and I'm hoping someone has already thrown that code together or knows these structures and/or the output of these undocumented procedures better than I do.

Why do I need this?

This is the inevitable question many will ask, so I'm just going to answer it out of the gate.  The project I've been working on is to bring a legacy system up to speed, but after consolidating a bunch of heap tables together (which was a nightmare for other reasons), I am now left with a bunch of free space within my data files.  I want to release this space back to the OS, however, the traditional approach of migrating objects to a different data file isn't viable at this stage because I don't have enough free space to work with on the system (until I am able to release more space from this data file).

I've resorted to disabling file growth and running a DBCC SHRINKFILE TRUNCATEONLY command nightly to free up any open pages toward the end of the data file, but this is a slow and arduous process that may work as often as it doesn't.  I'm hoping to identify what the objects toward the end of the file are so I can rebuild them manually and free up space in a quicker timetable.

In Summary

Is there a way to quickly identify the name of the object located at the end of a given data file?  The method I'm employing now is not meeting my needs and I'm open to using any approach available.

I think this will do it.  This code basically does the following:

Retrieve highest allocated page ID from the last GAM interval in the file
Retrieve highest allocated page ID from the last SGAM interval in the file
Compare two values to find highest page
Identify the last ObjectId (table) from the Last allocated page
Identify the Index defined on the Object as well as it's partition
Provide a DBCC SHRINKFILE command that will release only the remaining white-space at the end of the file back to the OS (which should be immediate) and is effectively equivalent to DBCC SHRINKFILE using TRUNCATEONLY

This is nested in a cursor that iterates through Page IDs of the data files within the database and executes pretty quickly based on my localized testing.  I've also added functionality to identify if the end of a data file is occupied by pages that are not reserved by tables or indexes, such as an IAM or PFS page.

SET NOCOUNT ON;

-- Create Temp Table to push DBCC PAGE results into
CREATE TABLE #dbccPage_output(
      ID                INT IDENTITY(1,1)
    , [ParentObject]    VARCHAR(255)
    , [Object]          VARCHAR(255)
    , [Field]           VARCHAR(255)
    , [Value]           VARCHAR(255)
)
GO

-- Variables to hold pointer information for traversing GAM and SGAM pages
DECLARE @GAM_maxPageID INT, @SGAM_maxPageID INT, @maxPageID INT,
        @GAM_page INT, @SGAM_page INT
DECLARE @stmt VARCHAR(2000)

-- Final Output Table
DECLARE @myOutputTable TABLE
(
      LogicalFileName   VARCHAR(255)
    , ObjectID          BIGINT
    , IndexID           BIGINT
    , PartitionID       BIGINT
    , MaxPageID         BIGINT
)

-- Cursor to iterate through each file
DECLARE cursorFileIds CURSOR
FOR
        SELECT file_id, size
        FROM sys.database_files
        WHERE type = 0

-- Variable to hold fileID
DECLARE @fileID INT, @size INT, @interval INT

-- Inject the data into the cursor
OPEN cursorFileIds
FETCH NEXT FROM cursorFileIds
INTO @fileID, @size

-- Enter the While Loop.  This loop will end when the
--  end of the data injected into the cursor is reached.
WHILE @@FETCH_STATUS = 0
BEGIN
        -- Truncate table (mainly used for 2nd pass and forward)
        TRUNCATE TABLE #dbccPage_output

        -- Referenced if we need to step back a GAM/SGAM interval
        STEPBACK:

        -- # of pages in a GAM interval
        SET @interval = @size / 511232
        -- Set GAM Page to read
        SET @GAM_page = CASE @interval WHEN 0 THEN 2 ELSE @interval * 511232 END
        -- Set SGAM page to read (always the next page after the GAM)
        SET @SGAM_page = CASE @interval WHEN 0 THEN 3 ELSE (@interval * 511232) + 1 END

        -- Search Last GAM Interval page
        SET @stmt = 'DBCC PAGE(0, ' + CAST(@fileID AS VARCHAR(10)) + ', ' + CAST(@GAM_page AS VARCHAR(20)) + ', 3) WITH TABLERESULTS, NO_INFOMSGS' -- GAM on Primary Datafile
        PRINT @stmt

        INSERT INTO #dbccPage_output ([ParentObject], [Object], [Field], [Value])
        EXEC (@stmt)

        -- Get Last Allocated Page Number
        SELECT TOP 1
                @GAM_maxPageID = REVERSE(SUBSTRING(REVERSE(Field), CHARINDEX(')', REVERSE(Field)) + 1, CHARINDEX(':', REVERSE(Field)) - CHARINDEX(')', REVERSE(Field)) - 1))
        FROM #dbccPage_output
        WHERE [Value] = '    ALLOCATED'
        ORDER BY ID DESC

        -- Truncate Table
        TRUNCATE TABLE #dbccPage_output

        -- Search Last SGAM Interval page
        SET @stmt = 'DBCC PAGE(0, ' + CAST(@fileID AS VARCHAR(10)) + ', ' + CAST(@SGAM_page AS VARCHAR(20)) + ', 3) WITH TABLERESULTS, NO_INFOMSGS' -- SGAM on Primary Datafile
        PRINT @stmt

        INSERT INTO #dbccPage_output ([ParentObject], [Object], [Field], [Value])
        EXEC (@stmt)

        -- Get Last Allocated Page Number
        SELECT TOP 1
                @SGAM_maxPageID = REVERSE(SUBSTRING(REVERSE(Field), CHARINDEX(')', REVERSE(Field)) + 1, CHARINDEX(':', REVERSE(Field)) - CHARINDEX(')', REVERSE(Field)) - 1))
        FROM #dbccPage_output
        WHERE [Value] = '    ALLOCATED'
        ORDER BY ID DESC

        -- Get highest page value between SGAM and GAM
        SELECT @maxPageID = MAX(t.value)
        FROM (VALUES (@GAM_maxPageID), (@SGAM_maxPageID)) t(value)

        TRUNCATE TABLE #dbccPage_output

        -- Check if GAM or SGAM is last allocated page in the chain, if so, step back one interval
        IF(@maxPageID IN (@GAM_page, @SGAM_page))
        BEGIN
            SET @size = ABS(@size - 511232)
            GOTO STEPBACK
        END

        -- Search Highest Page Number of Data File
        SET @stmt = 'DBCC PAGE(0, ' + CAST(@fileID AS VARCHAR(10)) + ', ' + CAST(CASE WHEN @maxPageID = @SGAM_maxPageID THEN @maxPageID + 7 ELSE @maxPageID END AS VARCHAR(50)) + ', 1) WITH TABLERESULTS, NO_INFOMSGS' -- Page ID of Last Allocated Object
        PRINT @stmt

        INSERT INTO #dbccPage_output ([ParentObject], [Object], [Field], [Value])
        EXEC (@stmt)

        -- Capture Object Name of DataFile
        INSERT INTO @myOutputTable
        SELECT (SELECT name FROM sys.database_files WHERE file_id = @fileID) AS LogicalFileName
            , CASE WHEN (SELECT [Value] FROM #dbccPage_output WHERE Field = 'm_type') IN ('1', '2') THEN -- If page type is data or index
                        CAST((SELECT [Value] FROM #dbccPage_output WHERE Field = 'Metadata: ObjectId') AS BIGINT)
                   ELSE CAST((SELECT [Value] FROM #dbccPage_output WHERE Field = 'm_type') AS BIGINT)
              END AS ObjectID
            , NULLIF(CAST((SELECT [Value] FROM #dbccPage_output WHERE Field = 'Metadata: IndexId') AS BIGINT), -1) AS IndexID
            , NULLIF(CAST((SELECT [Value] FROM #dbccPage_output WHERE Field = 'Metadata: PartitionId') AS BIGINT), 0) AS PartitionID
            , @maxPageID + 7 AS MaxPageID

        -- Reset Max Page Values
        SELECT @GAM_maxPageID = 0, @SGAM_maxPageID = 0, @maxPageID = 0

     -- Traverse the Data in the cursor
     FETCH NEXT FROM cursorFileIds
     INTO @fileID, @size
END

-- Close and deallocate the cursor because you've finished traversing all it's data
CLOSE cursorFileIds
DEALLOCATE cursorFileIds

-- page type values pt. 1: https://www.sqlskills.com/blogs/paul/inside-the-storage-engine-using-dbcc-page-and-dbcc-ind-to-find-out-if-page-splits-ever-roll-back/
-- page type values pt. 2: https://www.sqlskills.com/blogs/paul/inside-the-storage-engine-anatomy-of-a-page/
-- ObjectIDs of either 0 or 99: https://www.sqlskills.com/blogs/paul/finding-table-name-page-id/
-- Output Object Closest to the End
SELECT  t.LogicalFileName
    ,   CAST(CASE WHEN t.IndexID IS NULL THEN
                CASE t.ObjectID
                    WHEN 0 THEN  '>>No MetaData Found<<'  -- This isn't m_type, rather ObjectID
                    WHEN 1 THEN  '>>Data Page<<'
                    WHEN 2 THEN  '>>Index Page<<'
                    WHEN 3 THEN  '>>Text Mix Page<<'
                    WHEN 4 THEN  '>>Text Tree Page<<'
                    WHEN 6 THEN  '>>DCM Page<<<'
                    WHEN 7 THEN  '>>Sort Page<<'
                    WHEN 8 THEN  '>>GAM Page<<'
                    WHEN 9 THEN  '>>SGAM Page<<'
                    WHEN 10 THEN '>>IAM Page<<'
                    WHEN 11 THEN '>>PFS Page<<'
                    WHEN 13 THEN '>>Boot Page<<'
                    WHEN 15 THEN '>>File Header Page<<'
                    WHEN 16 THEN '>>Diff Map Page<<'
                    WHEN 17 THEN '>>ML Map Page<<'
                    WHEN 18 THEN '>>Deallocated DBCC CHECKDB Repair Page<<'
                    WHEN 19 THEN '>>Temporary ALTER INDEX Page<<'
                    WHEN 20 THEN '>>Pre-Allocated BULK LOAD Page<<'
                    WHEN 99 THEN '>>Possible Page Corruption/Run DBCC CHECKDB<<'  -- This isn't m_type, rather ObjectID
                    ELSE CAST(t.ObjectID AS VARCHAR(50))
                END
            ELSE QUOTENAME(OBJECT_SCHEMA_NAME(t.ObjectID)) + '.' + QUOTENAME(OBJECT_NAME(t.ObjectID)) END AS VARCHAR(250)) AS TableName
    ,   QUOTENAME(i.name) AS IndexName
    ,   p.partition_number AS PartitionNumber
    ,   'DBCC SHRINKFILE(' + t.LogicalFileName + ', ' + CAST(CEILING((t.MaxPageID + 8) * 0.0078125) AS VARCHAR(50)) + ')' AS ShrinkCommand_Explicit
    ,   'DBCC SHRINKFILE(' + t.LogicalFileName + ', TRUNCATEONLY)' AS ShrinkCommand_TRUNCATEONLY
FROM @myOutputTable t
    LEFT JOIN sys.indexes i
        ON t.ObjectID = i.object_id
        AND t.IndexID = i.index_id
    LEFT JOIN sys.partitions p
        ON t.ObjectID = p.object_id
        AND t.PartitionID = p.partition_id

-- Cleanup
DROP TABLE #dbccPage_output
GO


MySQL - OPTIMIZE TABLE for partitions I would like to perform OPTIMIZE TABLE opearation , this table is a partitioned table.

Which method can be better in this scenario:

  OPTIMIZE TABLE tblname;

or

  ALTER TABLE tblname OPTIMIZE PARTITION p0, p1;

From the MySQL Manual:

  Some MySQL storage engines, including InnoDB, do not support
  per-partition optimization; in these cases, ALTER TABLE ... OPTIMIZE
  PARTITION rebuilds the entire table.

There is a reason to optimize separately each partition, in order to avoid locking and performance issues. However, if you are using Innodb, you will get the following warning:

Table does not support optimize on partitions. All partitions will be rebuilt and analyzed

Which basically makes the OPTIMIZE PARTITION syntax useless. Also, if you only have 2 partitions (p0 and p1), you are basically executing OPTIMIZE TABLE.

My questions is why you are running optimize to start with and if it is justified? You should also add the MySQL version and engine used. In any case, you are probably executing the first option implicitly or explicitly.