Merge row size overflow in SQL Server - "Cannot create a row of size.." The target table to which I'm trying to merge the data has ~660 columns.
The code for the merge:

MERGE TBL_BM_HSD_SUBJECT_AN_1 AS targetTable
USING
        (
SELECT *
FROM TBL_BM_HSD_SUBJECT_AN_1_STAGING
WHERE [ibi_bulk_id] in (20150520141627106) and  id in(101659113)
    ) AS sourceTable
ON (...)
WHEN MATCHED AND ((targetTable.[sampletime] <= sourceTable.[sampletime]))
        THEN UPDATE SET ...
WHEN NOT MATCHED
        THEN INSERT (...)
    VALUES (...)

The first time I ran this (i.e when the table is empty) it resulted in success,
and inserted one row.

The second time I ran this, with the same data set, an error was returned:
Cannot create a row of size 8410 which is greater than the allowable maximum row size of 8060.

Why the second time I tried to merge the same row which already was inserted it resulted in an error. If this row exceeded maximum row size, it would expect for it not to be possible to insert it in the first place.

So I tried two things, (and succeeded!):

Removing "WHEN NOT MATCHED" section from merge statement
Running an update statement with the same row I tried to merge

Why does update using merge is not succeeding, while insert does, and direct update also does?

UPDATE:

Managed to find the actual row size - 4978. I've created a new table that has only this row, and find the row size this way:

And I still don't see something exceeding the allowed limit.

UPDATE(2):

Full reproduce

Made an effort that this reproduce will not require any additional auxiliary objects, and that the data will be (somewhat) obfuscated.

Tried this on several servers, from version 2012, and one from 2008, and was able to fully reproduce in all of them.

Why the second time I tried to merge the same row which already was inserted it resulted in an error. If this row exceeded maximum row size, it would expect for it not to be possible to insert it in the first place.

First, thank you for the reproduction script.

The problem is not that SQL Server cannot insert or update a particular user-visible  row. As you noted, a row that has already been inserted to a table certainly cannot be fundamentally too large for SQL Server to handle.

The problem occurs because the SQL Server MERGE implementation adds computed information (as extra columns) during intermediate steps in the execution plan. This extra information is needed for technical reasons, to keep track of whether each row should result in a insert, update, or delete; and also related to the way SQL Server generically avoids transient key violations during changes to indexes.

The SQL Server Storage Engine requires indexes to be unique (internally, including any hidden uniquifier) at all times - as each row is processed - rather than at the start and end of the complete transaction. In more complex MERGE scenarios, this requires a Split (converting an update to a separate delete and insert), Sort, and an optional Collapse (turning adjacent inserts and updates on the same key into an update). More information.

As an aside, note that the issue does not occur if the target table is a heap (drop the clustered index to see this). I am not recommending this as a fix, just mentioning it to highlight the connection between maintaining index uniqueness at all times (clustered in the present case), and the Split-Sort-Collapse.

In simple MERGE queries, with suitable unique indexes, and a straightforward relationship between source and target rows (typically matching using an ON clause that features all key columns), the query optimizer can simplify much of the generic logic away, resulting in comparatively simple plans that do not require a Split-Sort-Collapse, or Segment-Sequence Project to check that target rows are only touched once.

In complex MERGE queries, with more opaque logic, the optimizer is usually unable to apply these simplifications, exposing much more of the fundamentally complex logic required for correct processing (product bugs notwithstanding, and there have been plenty).

Your query certainly qualifies as complex. The ON clause does not match the index keys (and I understand why), and the 'source table' is a self-join involving a ranking window function (again, with reasons):

MERGE MERGE_REPRO_TARGET AS targetTable
USING
(
    SELECT * FROM
    (
        SELECT
            *,
            ROW_NUMBER() OVER (
                PARTITION BY ww,id, tenant
                ORDER BY
                (
                    SELECT COUNT(1)
                    FROM MERGE_REPRO_SOURCE AS targetTable
                    WHERE
                        targetTable.[ibi_bulk_id] = sourceTable.[ibi_bulk_id]
                        AND targetTable.[ibi_row_id] <> sourceTable.[ibi_row_id]
                        AND
                        (
                            (targetTable.[ww] = sourceTable.[ww])
                            AND (targetTable.[id] = sourceTable.[id])
                            AND (targetTable.[tenant] = sourceTable.[tenant])
                        )
                        AND NOT ((targetTable.[sampletime] <= sourceTable.[sampletime]))
                ),
                sourceTable.ibi_row_id DESC
            ) AS idx
        FROM MERGE_REPRO_SOURCE sourceTable
        WHERE [ibi_bulk_id] in (20150803110418887)
    ) AS bulkData
    where idx = 1
) AS sourceTable
ON
    (targetTable.[ww] = sourceTable.[ww])
    AND (targetTable.[id] = sourceTable.[id])
    AND (targetTable.[tenant] = sourceTable.[tenant])
...

This results in many extra computed columns, primarily associated with the Split and the data needed when an update is converted to an insert/update pair. These extra columns result in an intermediate row exceeding the allowed 8060 bytes at an earlier Sort - the one just after a Filter:

Note that the Filter has 1,319 columns (expressions and base columns) in its Output List. Attaching a debugger shows the call stack at the point the fatal exception is raised:

Note in passing that the problem is not at the Spool - the exception there is converted to a warning about the potential for a row to be too large.

  Why does update using merge is not succeeding, while insert does, and direct update also does?

A direct update does not have the same internal complexity as the MERGE. It is a fundamentally simpler operation that tends to simplify and optimizer better. Removing the NOT MATCHED clause may also remove enough of the complexity such that the error is not generated in some cases. That does not happen with the repro, however.

Ultimately, my advice is to avoid MERGE for larger or more complex tasks. My experience is that separate insert/update/delete statements tend to optimize better, are simpler to understand, and also often perform better overall, compared with MERGE.


Use array of composite type as function parameter and access it I have created a type Books in Postgres which has 2 numeric fields and 2 varchar fields. I want to send an array of Books to a function to INSERT those values in a matching table.

This is my type:

CREATE TYPE Books AS (
V_Book_ID NUMERIC,
V_Row_Num NUMERIC,
V_Book_OWNER TEXT,
V_Book_OWNER_ID TEXT
);

This is my function:

CREATE OR REPLACE FUNCTION Update_Table(row_book Books[]) RETURNS TEXT AS $$
DECLARE
   Status TEXT;
   I_Max integer := array_length(row_book, 1);
BEGIN
FOR I in 1..I_Max
  LOOP
   INSERT INTO books_table(Book_ID,
   Row_Num,
   Book_OWNER,
   Book_OWNER_ID)
   values
   (row_book[I].V_Book_ID,
   row_book[I].V_Row_Num,
   row_book[I].V_Book_OWNER,
   row_book[I].V_Book_OWNER_ID);
END LOOP;

   STATUS:='Saved';
exception when others then
   STATUS:='failure';
   RETURN STATUS;

END;
$$ language plpgsql;

How do I send data to the function or how should I call the function with data?

I answered a similar question on SO some time ago, to suggest the same solution with unnest() like @a_horse did:

Array of composite type as stored procedure input

If books_table has the same row type as your composite type books, you do not need to create the additional type at all, just use the row type of the table:
CREATE TABLE books_table (
  book_id numeric
, row_num numeric
, book_owner text
, book_owner_id text
);

PL/pgSQL function
If you need a plpgsql function for some undeclared reason:
CREATE OR REPLACE FUNCTION update_table_variadic(VARIADIC _books_arr books_table[])
  RETURNS TEXT
  LANGUAGE plpgsql AS
$func$
DECLARE
   b books_table;
BEGIN
   FOREACH b IN ARRAY _books_arr
   LOOP
      INSERT INTO books_table  -- rare case where column list is no improvement
      SELECT b.*;
   END LOOP;

   RETURN 'Saved';

EXCEPTION WHEN others THEN
   RETURN 'Failure';
END
$func$;

Example call with list of row values:
SELECT update_table_variadic('(2,100,Arthur,1)', '(2,50,Zaphod,1)');

Without using VARIADIC, the function call would require a single array parameter.
Either an array literal (optionally with explicit cast):
SELECT update_table('{"(1,100,Arthur,1)","(1,50,Zaphod,1)"}'::books_table[]);

See:

How to call PostgreSQL function with array of rowtype as parameter from Java
How to pass custom type array to Postgres function

Or you can use an array constructor like @a_horse demonstrates.
Array literals are often easier to provide.
Major points:
Use the simpler FOREACH to loop over an array. See:

Postgres - array for loop

Avoid CaMeL-case names in Postgres unless you know what you are doing.
(Optionally) use a VARIADIC parameter to simplify the syntax for the function call. Then you can provide a list of row values. Note the maximum number of function parameters (default 100) if you go with VARIADIC:

Select rows such that names match elements of input array for pgsql function

SQL function
If you don't need to catch the exception, and you also don't need the string 'Saved' / 'Failure' to be returned, simplify:
CREATE OR REPLACE FUNCTION update_table_set(VARIADIC _books_arr books_table[])
  RETURNS void
  LANGUAGE sql AS
$func$
   INSERT INTO books_table
   SELECT * FROM unnest(_books_arr) b;
$func$;

db<>fiddle here
Old sqlfiddle


Avoiding Sysadmin Rights I'm going to start off apologizing since this is my first post and looking at other questions they don't quite match my own situation, also I'm a SysAdmin, not a DBA so my understanding of SQL is rusty and limited.

My organization frequently encounters vendors who in order for their application to run they do what we refer to as lazy programming and request SA credentials or demand that their custom account has sysadmin rights.  My organization also has data protection/compliance policies which require us to grant only required access and rights.

Most recently a vendor that I am working with wants their account to be a sysadmin, but it actually only needs access to a single database.  If we give the AD account (because their application only works with Windows Authentication) sysadmin rights I can use the application's GUI to browse and select the database, but if we only make that same account the DBOwner we can't find anything.

Is there a way to find out if it is trying to poll other databases or determine what the lowest level of rights needed for the required database(s) would be?  When I tried asking the vendor I was told, "It requires sysadmin rights, but its ok because it only accesses the one database."  Which isn't ok when it comes to our policies.

Thanks for any advice.

If the vendor is providing their own database you could make their account apart of the owner role at the database level and all you need to do at the system level is grant the account login permissions to their database. This will allow them to have full control over their database and will prevent them from using the account to access other databases you have. If they tell you they need the sysadmin database role for tempdb access that is not true as every user has all the access they need to tempdb by default no other steps needed unless you made changes to the defaults for tempdb. Setting up their account this way will also prevent them from enabling other extended features in sql like mail and xp_cmd_shell which would give them access to the underlying OS.

There is other hardening you can do at the sql server service level to ensure that any sysadmin accounts in sql don't have admin rights on the OS. These include running the sql service and sql agent service as just a user on the box.  Then adding the windows group mssql$....users to the Disk Maintenance and Log on as a batch policy. This will give the two accounts access to run as a service and will also allow sql server to do instant file initialization when it needs to grow the underlying data/log files.
You will also need to grant full control at the NTFS level of the folders for the same group where the data and log files.  Use the SQL Server configuration tool to change the accounts as it sets most of the needed permissions for you.

Here is a basic guide from MSDN while I try to find a more step by step on of the basics that I've used in the past


Getting the latest timestamp in Oracle I am trying to create a report that has the latest member id in the first column and the old member id in the second column based on the timestamp in audits table below. Member ids are stored in master table and the members table. audit_id from the audits table is a foreign key in members table.
I tried to write the query below after researching but I am getting syntax errors.
SELECT
  (SELECT mem.member_id
   FROM members mem
   JOIN audits aud ON mem.audit_id = aud.audit_id
   ORDER BY aud.update_time DESC FETCH FIRST 1 ROWS ONLY) AS latest_id,
       mem1.member_id AS old_id
FROM members mem1
JOIN master ma ON ma.member_id = mem1.member_id

audits
audit_id |update_time
656566   |2021-01-15 17:01:34
656565   |2020-05-13 17:01:34
545245   |2020-08-11 17:01:34

members
member_id  |audit_id
5655656    |656566
653363     |656565
545454     |545245

master
id | member_id
545634 | 5655656
545634 | 653363
545634 | 545454

Expected output
latest_id | old_id
5655656    |653363
5655656    |545454

With A CTE and ROW_NUMBER in conhunction with Cross JOIN you can get your wanted result.
The CTE creates a temporary table, which has the row number for all all master ids and of course the wanted member id.
The SELECTmakes a Cartesian product of the first (latest) member id and the rest of the rows(member ids) for every master id

CREATE TABLE master
    ("id" int, "member_id" int)
;

INSERT ALL
    INTO master ("id", "member_id")
         VALUES (545634, 5655656)
    INTO master ("id", "member_id")
         VALUES (545634, 653363)
    INTO master ("id", "member_id")
         VALUES (545634, 545454)
SELECT * FROM dual

CREATE TABLE members
    ("member_id" int, "audit_id" int)
;

INSERT ALL
    INTO members ("member_id", "audit_id")
         VALUES (5655656, 656566)
    INTO members ("member_id", "audit_id")
         VALUES (653363, 656565)
    INTO members ("member_id", "audit_id")
         VALUES (545454, 545245)
SELECT * FROM dual

CREATE TABLE audits
    ("audit_id" int, "update_time" DATE )
;

ALTER SESSION SET NLS_DATE_FORMAT='YYYY-MM-DD HH24:MI:SS';

INSERT ALL
    INTO audits ("audit_id", "update_time")
         VALUES (656566, '2021-01-15 17:01:34')
    INTO audits ("audit_id", "update_time")
         VALUES (656565, '2020-05-13 17:01:34')
    INTO audits ("audit_id", "update_time")
         VALUES (545245, '2020-08-11 17:01:34')
SELECT * FROM dual;

WITH CTE AS (SELECT ma."id",me."member_id",
   Row_NUMBER() OVER (
        PARTITION BY ma."id"
        ORDER BY "update_time" ASC
                ) "rn"
from master ma
    INNER JOIN members me on ma."member_id" = me."member_id"
    INNER JOIN audits au ON me."audit_id" = au."audit_id")
SELECT a."member_id", b."member_id"
FROM (SELECT "member_id","id" FROM CTE WHERE "rn"= 1) a
CROSS JOIN (SELECT "member_id","id", "rn" FROM CTE WHERE "rn" > 1)  b
WHERE a."id" = b."id"
ORDER BY b."rn";

member_id | member_id
--------: | --------:
   653363 |    545454
   653363 |   5655656

db<>fiddle here


Assuming:

the base portion is always 15 characters
the suffix is always 3 numeric digits
the suffix is never '000' [I'm going to use '000' as a default for names with no suffix]

Setup:

CREATE TABLE forms (name nvarchar(50), moddate datetime);

INSERT INTO forms VALUES
('abc123v1-123456'    ,'2018-08-19 00:00:00.000'),
('abc123v1-123456-001','2018-08-19 00:00:00.000'),
('abc123v1-123456-001','2018-08-21 00:00:00.000'),
('abc123v1-123456-002','2018-08-03 11:00:00.000'),
('abc123v1-123456-002','2018-08-03 10:00:00.000'),
('abc123v1-666666-092','2018-08-15 00:00:00.000'),
('abc123v1-000000'    ,'2018-08-15 00:00:00.000');

Break the name into 2 components, defaulting to '000' if the name does not have a suffix:

select -- strip off first 15 characters as part1
       substring(name, 1,15)        as part1,
       -- strip out positions 17-19; for forms that don't have 17-19 we'll add a default '-000'
       substring(name+'-000',17, 3) as part2
from   forms
order by 1,2;

part1            part2
===============  =====
abc123v1-000000  000      -- add default '000' for part2
abc123v1-123456  000      -- add default '000' for part2
abc123v1-123456  001
abc123v1-123456  001
abc123v1-123456  002
abc123v1-123456  002
abc123v1-666666  092

At this point we can place the above in a CTE and perform a basic max()\group by operation to find the name with the highest number ... remembering to strip off our default '000' when displaying our results:

with parts as
(
select substring(name, 1,15)        as part1,
       substring(name+'-000',17, 3) as part2
from   forms
)
select part1 +
       -- ignore '000' otherwise append a '-' and the numeric portion
       case when max(part2) = '000' then '' else '-' + max(part2) end  as maxname
from   parts
group by part1
order by 1;

maxname
===================
abc123v1-000000
abc123v1-123456-002
abc123v1-666666-092

And lastly we'll join these max names back to the original table to find the max() modification date, against using a CTE to manage our intermediate max/name logic:

with parts as
(
select substring(name, 1,15)        as part1,
       substring(name+'-000',17, 3) as part2
from   forms
),
first_max as
(
select part1 +
       -- ignore '000' otherwise append a '-' and the numeric portion
       case when max(part2) = '000' then '' else '-' + max(part2) end  as maxname
from   parts
group by part1
)
select fm.maxname     as 'FormName',
       max(f.moddate) as 'ModifiedDate'
from   first_max fm
join   forms f
on     fm.maxname = f.name
group by fm.maxname
order by 1;

FormName            ModifiedDate
=================== ===================
abc123v1-000000     15.08.2018 00:00:00
abc123v1-123456-002 03.08.2018 11:00:00
abc123v1-666666-092 15.08.2018 00:00:00

NOTE: Tweak the order by to get the desired ordering.

NOTE: Dates are displayed in the default format of this particular db fiddle, so your SQL Server instance may display differently, ymmv.


SSMS 2017 Changing SSIS Package Version During Deployment Steps taken, can be reproduced:

Develop SSIS package in SSDT 2012, project deployment model.
Deploy project to server A, SQL Server 2012. Package version
internal.packages is 6.
Deploy project from server A to server B, SQL Server 2012. Package
version internal.packages is 8 on server B.

If I deploy directly to both servers from SSDT 2012 there is no problem, but deploying from SSMS 2017 is bumping the version number.

Is there a way to stop that from happening during project deployment in SSMS 2017?  Has anyone else encountered this?

I'm not surprised but haven't tested it myself.

Project deployment model packages are deployed using ISDeploymentWizard.exe and there is a version associated to each version of SQL Server (and bit-edness).
For example, one laptop here has VS 2013/2015 installed and SSDT for 2012/2014/2016 installed. If I look for that executable, I see

C:\Program Files (x86)\Microsoft SQL Server\130\DTS\Binn\ISDeploymentWizard.exe
C:\Program Files (x86)\Microsoft SQL Server\120\DTS\Binn\ISDeploymentWizard.exe
C:\Program Files (x86)\Microsoft SQL Server\140\DTS\Binn\ISDeploymentWizard.exe
C:\Program Files\Microsoft SQL Server\120\DTS\Binn\ISDeploymentWizard.exe

My new machine only has bits for 2017 and looks like

C:\Program Files (x86)\Microsoft SQL Server\140\DTS\Binn\ISDeploymentWizard.exe
C:\Program Files\Microsoft SQL Server\140\DTS\Binn\ISDeploymentWizard.exe

The other thing to factor into all this is that using a newer version of the SSIS tooling (ISDeploymentWizard, dtexec, dtutil) will happily roll your version forward to match its version. Which is helpful as a consultant to upgrade things but the devil when you want to use new tools against old code.

My guess is that SSMS is defaulting to an ISDeploymentWizard that is not in the 120 folder. Unfortunately, I don't see a parameter in the deployment wizard to address version.

/Silent[+|-]                  Default value:'-' (short form /S)
/SourceType:{File|Server}     Default value:'File' (short form /ST)
/ModelType:{Project|Package}  Default value:'Project' (short form /MT)
/SourcePath:<string>          (short form /SP)
/SourceServer:<string>        (short form /SS)
/Packages:<string>            (short form /PK)
/ProjectPassword:<string>     (short form /PP)
/DestinationServer:<string>   (short form /DS)
/DestinationPath:<string>     (short form /DP)
/SkipSource[+|-]              Default value:'-'
@<file>                       Read response file for more options

Your best, yet icky, bets are to

Keep a copy of SSMS for SQL server 2012 lying about for deployment
Deploy from SSDT 2012
Deploy from the commandline explicitly defining the source exe

The last option is actually that bad. Use the GUI to click through the deployment but on this screen, stop

That highlighted box is selectable/copyable. DO THAT. The command line deploy has been built out for you (excluding the part that says "Command line:")

Thus you'd have something like

"C:\...\1XX\DTS\BINN\ISDeploymentWizard.exe" /SILENT /SourcePath:"C:\MyProject.ispac" /DestinationServer:"localhost" /DestinationPath:"/SSISDB/Folder/Project/MyProject"


Azure SQL Database vs. error 8152 enhancement How can I take advantage of the enhancement for error 8152 (https://blogs.msdn.microsoft.com/sql_server_team/string-or-binary-data-would-be-truncated-replacing-the-infamous-error-8152/) in Azure SQL Database? Even when logged in as the Server admin, I can't set the trace flag.

You cannot enable traceflags in azure sql database (single database).

You can enable traceflags when using azure sql database managed instances:

DBCC TRACEON documentation

Since the azure sql database service is based on the latest stable sql server version

  It's (azure sql database) based on the latest stable version of the
  Microsoft SQL Server database engine.

Azure sql database service documentation

And  in your linked source it is stated that:

  ...Note that for now, even in SQL Server 2019 CTP 2.0 the same trace
  flag 460 needs to be enabled. In a future SQL Server 2019 release,
  message 2628 will replace message 8152 by default....

We should hope to see this more detailed error message enabled by default for the azure sql database service in the future, once that build is deemed stable.


Adding second SQL Server to create 2 Node cluster I am not a dba. I have general knowledge of SQL Server and T-SQL. Our company currently has a SQL Server 2008 R2 hosting about 30 databases.

I've been asked to research adding a second server for HA/Failover. My question is, should I be considering upgrading the current server to at least 2012, and build the new server the same? I had initially thought about building another 2008 R2 server, but it looks like clustering and HA is much improved in 2012.

Also, not being an expert, is this going to be something a journeyman administrator can accomplish (obviously you can't gauge my skill level) or will we be looking at engaging a consultant? I know that question may be hard to answer.

Get us HA is often spoken by management but the clarity isn't there.  You'll want to first cover your butt to make sure you're both on the same page as what "HA" is.  First set acceptable data loss policies and acceptable downtime in case of an emergency.  Answer 1 will be 0 data loss and 0 seconds down time.  Great, 6 9's uptime will require their entire budget and then some.  Once they realize that they will decide that they can probably be down x minutes or hours per year.  They will also help you define 'maintenance hours' where you can have extended outages if possible.

Once you know if you can have maint. hours and how much downtime is acceptable, you can pick an HA solution.  Remember that clustering shares storage, so you have 1 single point of failure.  It's probably not what you're looking for in this case.  You will probably want to do async mirroring which is sadly only available in enterprise.  If you don't have enterprise you will have to go with Sync. mirroring.  This requires your write to finish on the 2nd server before it calls it complete on the first server.  Unless you're monitoring the full stack and have a witness server for auto fail over, this will probably cause you more outage time where users can't write, than it'll save.

The easiest and cheapest option is log shipping.  This doesn't allow you to auto failover like mirroring with a witness or clustering (albeit not with storage failure as the whole cluster goes down).  It is however easy to setup, maintain, and can ensure you're only 1 or 2 transaction log backups behind.  Then you can practice 'tail of log' backups and automate it if you still have access to the primary to get ALL the data out and bring up the secondary.

Come back with uptime/data loss reqs and we'll help you dig deeper if you need it.


Is Oracle 12g free to download for learning purposes? I am a software developer that supports and develops an application that connects to both Oracle databases and SQL Server databases.

I want to learn more about database administration.  Therefore I am planning to purchase SQL Server Developer edition and install it on my PC at home (it costs around: £60).

I am also planning to download and install Oracle 12C: http://www.oracle.com/technetwork/database/enterprise-edition/downloads/index.html.  I believe it is completely free to do this after reading the license agreement (assuming that I do not use if for commercial purposes i.e. I use it for learning purposes only).  Is that correct?

I have read many questions on here stating that previous versions are free e.g. this one: Is Oracle free to download?.  However, I want to make sure that it is free? - the questions I have read are a few years old at least.

Also when I register on the Oracle website; what do I put as my job title etc.  Is this the company I am employed by? - bear in mind that I am not downloading it for them - I am downloading it for me.

Yes, for learning, it is free.

Software Downloads

  All software downloads are free, and most come with a Developer
  License that allows you to use full versions of the products at no
  charge while developing and prototyping your applications, or for
  strictly self-educational purposes. (Unless otherwise specified, our
  technical support organization will not provide technical support,
  phone support, or updates to you for the programs licensed under this
  agreement.) You can buy products with full-use licenses at any time
  from the online Store or from your sales representative.

Filling the registration form:

Terms of Use

  Security, Passwords and Means of Accessing the Site and Content

  If any of the Services require you to open an account, you must
  complete the registration process by providing us with current,
  complete and accurate information as required by the applicable
  registration form.


Are there other sites still using SQL Server with codepage 850 or codepage 437 for their sql scripts? Using one of these codepages is a disaster when scripts are edited by Visual Studio or TFS, but sometimes it is difficult to change old standards.

What must be observed, when changing to the current windows defaults?

I'm not sure this is an answerable question - yes, there are always sites on the web using wacko codepages for their defaults.

The most obvious problem when dealing with different collations is joining them together (cross-database queries), especially TempDB.  If TempDB has a different collation than your database, you can run into problems just creating a temp table and joining it to your data.

http://www.sqlskills.com/BLOGS/KIMBERLY/post/Changing-Database-Collation-and-dealing-with-TempDB-Objects.aspx


Database Users and Server Logins and the Mapping between them I have an instance of SQL Server 2008 R2 Express Edition at home and another on a server at a clients premises which I am using to develop an application using Access as the front end. This application has 5 separate logins which I can use in the connection string (based on the Access Group the user is in). The home instance has several databases - some of which have nothing to do with this applications

I have manually created the logins in each instance over time (and probably in a different order).

I am hoping to be able to (more than once) take a backup from my home machine of the database I am working on and load it on the instance at the clients premises using restore, just moving the files in the backup set to the actual files used on this server during the restore process.  The piece I don't understand is the mapping between the database user and the login.

I had thought that the correspondence of name should be enough, but in trying to fix an issue on which users of the restored database didn't seem to be mapped to the server login I came across the Transact-SQL ALTER USER statement and in particular its reference to using WITH LOGIN and some discussion about a "SID".

Since (I presume) the Server Login names have difference SID's in each instance, does it mean that after I load a backup between instances that I have to reconnect the mapping again with a series of ALTER USER WITH LOGIN commands?

Logins or server logins exists at the SQL server level, and the database user is at the database level. They need not have the same name, but generally do. In an instance, you cannot have a database user function properly without a corresponding SQL server login.

Let us say you have an instance: instanceA with a sever login userA on your computer. You can map this server login userA to a database user: userA for a database DB_A. They are both named userA, but one is the server login and one is the database user. The database user: userA resides in DB_A. When you backup and restore DB_A from instanceA to another instanceB, the database user: userA is moved, but the server login: userA still remains on instanceA. To move the logins from instanceA to instanceB, you can use sp_help_revlogin (Click to check out KB page).

If you had previously created the SQL server login userA on instanceB, then SQL will try to map this login with database user: userA, when DB_A is restored from instanceA to instanceB. If this fails, you can use the sp_change_users_login (Click for BOL link) procedure to fix the broken mappings. Hope this clears things up.


Show values from list that are NOT returned by query I have been given a list of values that may or may not be found in a table's column. I just need the subset of the list that is NOT found in a SELECT query.

Pretending that the list I was given is: 'something_a', 'something_b', 'something_c', 'something_d'...

My intuition is to do something like this:

SELECT item FROM ('something_a', 'something_b', 'something_c', 'something_d')
WHERE item NOT IN (
    SELECT appropriate_column FROM some_table
    WHERE appropriate_column IN ('something_a', 'something_b', 'something_c', 'something_d')
);

I would expect to get back the following result if say two of the items are missing from the table:

item
---
something_a
something_c

What is a valid SQL approach to accomplishing what I'm after?

You can use the VALUES clause to create a "constant table":

Let's assume this is your table:

CREATE TABLE some_table
(
    appropriate_column TEXT
) ;

populated with:

INSERT INTO
    some_table (appropriate_column)
VALUES
     ('something_b'),
     ('something_d') ;

This is how you would make the query you're looking for:

SELECT
    v
FROM
    (
    VALUES
        ('something_a'),
        ('something_b'),
        ('something_c'),
        ('something_d')
    ) AS vs(v)
WHERE
    v NOT IN
    (SELECT
        appropriate_column
    FROM
        some_table
    ) ;

You can check everything at dbfiddle


Slow inserts in oracle 12c inmemory table We are running some tests to check the in-memory performance on a 12c (12.1.0.2.4 EE) RAC. Servers have 56GB memory and 20core CPUs.

Our plan is to have a few read-performance critical tables in memory and the rest in disk. The test was to first populate the tables using our insert tool and then run queries on it using JMeter (web application benchmark tool).

The insert tool basically reads records from a file and then inserts records to the DB in blocks and commits.

We started testing with one table and observed slow insert rates straightaway. But when the table is made a no inmemory table the insert rates were fine.

The table has 90 columns, 1 trigger, 15 indexes.

The test preparation and results are given below.

Preparation

1) Create the table, trigger, indexes.

2) Make table in-memory using "alter table test_table inmemory priority critical"

Results

Without Inmemory option (~7000 recs/sec)

Avg time to read 1 record  = [0.0241493] ms
Avg time to insert 1 record  = [0.141788] ms
Avg time to insert 1 block of 500 number of rows  = [70.894] ms
Avg time to commit 2 blocks(500 rows per block)  = [3.888] ms
Total time for 2000 blocks of inserts  = [141.788] s, at [7052.78] recs/s
Total time for 1000 number of commits  = [3.888] s
Total time for 2000 blocks of inserts + 1000 number of commits  = [145.676] s
Total time to read 1000000 number of records from file  = [24.1493] s
Total time to read 1000000 number of records + 2000 blocks of inserts + 1000 number of commits  = [169.825] s

With Inmemory option (~200 recs/sec)

Avg time to read 1 record  = [0.0251651] ms
Avg time to insert 1 record  = [4.62541] ms
Avg time to insert 1 block of 500 number of rows  = [2312.7] ms
Avg time to commit 2 blocks(500 rows per block)  = [3.32] ms
Total time for 200 blocks of inserts  = [462.541] s, at [216.197] recs/s
Total time for 100 number of commits  = [0.332] s
Total time for 200 blocks of inserts + 100 number of commits  = [462.873] s
Total time to read 100000 number of records from file  = [2.51651] s
Total time to read 100000 number of records + 200 blocks of inserts + 100 number of commits  = [465.39] s

The memory parameters of the DB are given below.

NAME                            TYPE        VALUE

lock_sga                        boolean     FALSE
pre_page_sga                    boolean     TRUE
sga_max_size                    big integer 30G
sga_target                      big integer 30G
unified_audit_sga_queue_size    integer     1048576
inmemory_clause_default         string
inmemory_force                  string      DEFAULT
inmemory_max_populate_servers   integer     8
inmemory_query                  string      ENABLE
inmemory_size                   big integer 10G
inmemory_trickle_repopulate_    integer     1
servers_percent
optimizer_inmemory_aware        boolean     TRUE
buffer_pool_keep                string
buffer_pool_recycle             string
db_block_buffers                integer     0
log_buffer                      big integer 1048552K
use_indirect_data_buffers       boolean     FALSE
memory_max_target               big integer 0
memory_target                   big integer 0
optimizer_inmemory_aware        boolean TRUE
pga_aggregate_limit             big integer 8G
pga_aggregate_target            big integer 4G

We are also tried the following, but the results were the same.

1) Stop one instance on the RAC (2 node RAC)

2) Change the inmemory priority to "high" then "low".

Hope someone can point me in the right direction.

The DML in Inmemory involves new features like Journal & Inmemory Transaction Manager. When there is an insert operation on a table enabled for Inmemory, these new features get updated to bring the consistency in Transaction. Sometimes, there are overheads involved in keeping Inmemory Column Unit (IMCU) transactionally consistent.

Following are some of the factors which affect the performance:

The rate of change
The inmemory compression level chosen for a table
The location of the changed rows
The type of operations being performed

For example:  Tables with higher compression levels will incur more overhead than tables with lower compression levels.

Following are few other deciding factors which can be fine-tuned:

Inmemory Coordinator (IMCO) Process kicks-in every two minutes to
load the data into inmemory
The number of IMCUs repopulated via trickle repopulate in a given 2 minute window is limited by the new initialization parameter INMEMORY_TRICKLE_REPOPULATE_SERVERS_PERCENT
The overhead to update the indexes during DML
DISTRIBUTE or DUPLICATE clause used for the table while using in RAC environment

So, all these combined decide the runtime for an insert. Also, do look at the bundle path updates which are targeted for OLTP & DW performance improvements.

To improve performance in your case you can try changing the compression level used for the table to memcompress for dml:

alter table test_table inmemory priority critical memcompress for dml duplicate all

Regarding the methods you had tried:

Stop one instance on the RAC (2 node RAC)

Stopping one instance doesn't affect the performance as RAC nodes are mainly used for Inmemory Fault Tolerance through the DUPLICATE/DISTRIBUTE clause used for the inmeomory table. So, it won't help improve the insert performance.

Change the inmemory priority to "high" then "low".

Changing the priority clause is mainly used while populating/repopulating the table into inmemory. When there are many tables enabled for inmemory, the IMCO picks up the tables based on the priority setting (CRITICAL, HIGH, MEDIUM, LOW & NONE) and loads them into inmemory area. So, keeping the table priority to high or more has an impact when the expectation is to immediately see the updated version of table after an insert. For this, the IMCO duration needs to be fine-tuned.


Matching Custom Exported CSV File with Main MySQL Database I recently selected certain columns from a table from a huge MySQL db >10GB into a csv file and then filtered through the rows.

I would now like to retrieve the rest of the data matching the filtered rows. How could I go about doing that.

E.g.

MyTable (All columns are example names only)

+----+---------------+---------+-----------+
| ID | User          | Country | Etc..     |
+----+---------------+---------+-----------+
| 1  | JohnAppleSeed | Canada  | More Info |
+----+---------------+---------+-----------+
| 2  | ExampleUser   | Britain | More Info |
+----+---------------+---------+-----------+
| 3  | Admin         | Canada  | More Info |
+----+---------------+---------+-----------+

Exported.csv (unfiltered)

"JohnAppleSeed","Canada","More info"
"ExampleUser","Britain","More info"
"Admin","Canada","More info"

Exported.csv (filtered)

"JohnAppleSeed","Canada","More info"
"Admin","Canada","More info"

How do I get a file with the following information:

"1","JohnAppleSeed","Canada","More info"
"3","Admin","Canada","More info"

I know I could've selected columns where the value matched the country, but during the filtering process there were changes made to the 'more information' columns.

Load the Filtered output into a Temp table with columns User, Counter, ...  Then do something like

SELECT ID, User, Country, ...
    INTO OUTFILE ...
    FROM MyTable AS m
    LEFT JOIN Temp AS t  ON USING(User, Country, ...)
    WHERE t.User IS NULL;


Database cannot be upgraded because it is read-only or has read-only files. Make the database or files writeable, and rerun recovery I have received a database file and the instructions for loading it is to install SQL Server 2005 and then attach it using SQL Server Management Studio.

After installing everything, I tried to attach the MDF file but then it tells me:

  the directory lookup for the file "D:{folderName}{filename}.LDF" failed with the operating system error 21 (error not found)

An LDF file did not come with the database, so presumably it should be generated automatically.

Now, D drive is where my CD drive is, so it's not going to find anything there. Nor is it going to have any luck trying to create anything there.

Why is it trying to look for a log file at a specific path? Why not where the database file is?
How can I attach this database?

I realized that when I select a database to attach, three entries appear under "database details". an MDF, NDF, and LDF. The LDF's "current file path" points to the D drive path above, so I removed it.

This time, when I hit "OK", I get a different error message:

  Database cannot be upgraded because it is read-only or has read-only files.
  Make the database or files writeable, and rerun recovery.

  File activation Failure. The physical name D:{folder}{file}.LDF may be incorrect.
  New log file "..." was created. (Microsoft SQL Server, Error: 3415)

So now it creates a new log file in the same folder as the database file, which is great, but it seems like there are security issues.

Additional information:

The instructions require me to use the login name "sa", which appears to be the sysadmin account. I am connected to my SQL Server instance using that login.
I have checked the file properties that it is not read-only. The directory is not read-only either. All ACLs are allowed.
I am unable to attach the database. When I try to attach it, it throws an error message with "attach database failed".
Closing SSMS and reopening it as an Administrator made no difference.
select SERVERPROPERTY('ProductVersion') returns 9.00.4035.00. There's another file that comes with the database called "dbdata.ini" which says "IsSql2000=1" so presumably it is meant to be loaded in SQL Server 2000. I'll see if I can get it working on 2000.

I just found a post that answered this question:

This may not work in all cases, but I was trying to install the Semantic Search Database and got the same error.

You have to run SQL Server Management Studio as an administrator, and it works.


Varchar2 datatypes are longer than expected When I run the following script :

create table tbl1 (
col1 varchar2(30),
col2 varchar2(50),
col3 number(4,2)
);
desc tbl1;
alter table tbl1 modify (col1 varchar2(35));
desc tbl1;
drop table tbl1;

Here is the output I get :

Table created.
TABLE tbl1
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 COL1                                               VARCHAR2(120)
 COL2                                               VARCHAR2(200)
 COL3                                               NUMBER(4,2)
Table altered.
TABLE tbl1
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 COL1                                               VARCHAR2(140)
 COL2                                               VARCHAR2(200)
 COL3                                               NUMBER(4,2)
Table dropped.

Can somebody explain me why the column lengths for varchar2 would be 4 times larger than they should ?

My guess would be a combination of your NLS_LENGTH_SEMANTICS = 'CHAR' system parameter and a character set defined that is 4 bytes per character.

Can you check on what your NLS settings are?


updating associated records' tsvectors when the primary record is updated I have the following trigger function that updates the object's tsvector column when its details change:

CREATE OR REPLACE FUNCTION customer_search_trigger() RETURNS trigger AS $$
    BEGIN
        NEW.customers_search_vector :=
            setweight(to_tsvector('english', CONCAT(NEW.first_name, ' ', NEW.last_name)), 'A') ||
            setweight(to_tsvector('english', NEW.email), 'A') ||
            setweight(to_tsvector('english', coalesce(NEW.physical_address,'')), 'D');
        RETURN NEW;
    END;
$$ LANGUAGE plpgsql

Customer records are associated with Project records, via the customer_id column on the projects table. The projects table also has a tsvector column, named projects_search_vector, and that column contains tsvectors for not just the project details, but also the project's customer:

CREATE OR REPLACE FUNCTION project_search_trigger() RETURNS trigger AS $$
    DECLARE
        customer record;

    BEGIN
        SELECT first_name, last_name, email, physical_address INTO customer FROM customers_customers WHERE id = NEW.customer_id;
    NEW.projects_search_vector :=
        setweight(to_tsvector('english', coalesce(NEW.name,'')), 'A') ||
        setweight(to_tsvector('english', coalesce(NEW.description,'')), 'B') ||
        setweight(to_tsvector('english', CONCAT(customer.first_name, ' ', customer.last_name)), 'D') ||
        setweight(to_tsvector('english', customer.email), 'D') ||
        setweight(to_tsvector('english', coalesce(customer.physical_address,'')), 'D');
        RETURN NEW;
    END;
$$ LANGUAGE plpgsql

This way, when the user searches for "Bob Green", any projects that belong to Bob Green (the customer) are also returned.

QUESTION: I now want to make it so that when a customer's details change, the associated projects' tsvector columns are also updated. In other words, if the customer's last name changes from "Green" to "Brown", then when the user searches for "Brown" I want their projects to still get returned in the results.

How can I do this?

Make an after-update trigger on "customer" which does a dummy update on "project", which will then fire the trigger on project to do its thing:

CREATE OR REPLACE FUNCTION customer_depend_trigger() RETURNS trigger AS $$
    BEGIN
        update project set customer_id=NEW.id where customer_id=NEW.id;
        RETURN NEW;
    END;
$$ LANGUAGE plpgsql;

create trigger foobar after update ON customer
    for each row execute procedure customer_depend_trigger() ;


Why is Database Administration so hard? I know a lot of Database Administrators and they are all over 28-29 years old.

Is all database administration like that? I mean, is this about getting experience more than at least 7-8 years?

Or is being a database administrator so hard?

The position requires a broad spectrum of knowledge ranging from development to system administration and even management.  Not only must a DBA know about backup, recovery, internal operations, memory and security, but also how to communicate with both developers and management.  A DBA could be giving a high level presentation to management, helping a developer tune a query, provisioning disk space for a new system and restoring data from backup all within the same hour.  These responsibilities require a wealth of knowledge with little overlap.

The consequences of failure are usually greater for a DBA than a developer.  DBAs often support dozens, even hundreds of different applications and systems most of which are vital to the success of the company.  A security breach, recovery failure, or performance problem could have far reaching and devastating ramifications.  This requires a level of knowledge and experience that can’t be gained in a short amount of time.

The better a DBA does their job the less visibility they have.  A DBA with a database that is secure, recoverable, available, and performing well will lack recognition.  DBAs get noticed when there are problems.  Not only do they get noticed when their problems are self-inflicted, they also get blamed when the database has problems due to poor coding, improper network setup, or incorrectly configured storage.

I switched from developer to DBA when I was 29.  For me the things that make being a DBA difficult also make it rewarding.  I enjoy absorbing and using a wide spectrum of knowledge, and the greater opportunity for failure makes the avoidance thereof all the more meaningful whether others see that or not.


How can I "merge" two queries into a single one? I have the two queries shown below:

declare @myTag1
select @myTag1= TagID
from tag.Tag
where TagName = 'MyTag1'

declare @myTag2
select @myTag2= TagID
from tag.Tag
where TagName = 'MyTag2'

How can I "merge" these queries into only one? I tried the below way:

SELECT CASE TagName
        WHEN 'MyTag1' THEN @MyTag1
        WHEN 'MyTag2' THEN @MyTag2
   END,
   @myValue = TagID
FROM   tag.Tag
WHERE  TagName IN ('MyTag1', 'MyTag2')

But it gives the following error.

  Msg 141, Level 15, State 1, Line 9
   A SELECT statement that assigns a value to a variable must not be combined with data-retrieval operations.

Any help would be much appreciated

To fix the syntax of your second attempt:

SELECT @MyTag1 = CASE WHEN TagName= 'MyTag1' THEN TagID ELSE @MyTag1 END
     , @MyTag2 = CASE WHEN TagName= 'MyTag2' THEN TagID ELSE @MyTag2 END
FROM   tag.Tag
WHERE  TagName IN ('MyTag1', 'MyTag2')

(I'm not sure what @myValue was doing in there as it wasn't mentioned in the first example, so I've left that out.)

You may find this inefficient (it might scan, or at least do a partial scan) due to WHERE ... IN ... so watch the query plan. If that is the case then the sub-query option would be more efficient as you'll get two seeks instead.

When using the sub-query method if there might be many rows matching, add in TOP 1 so that the query engine will stop on finding the first value:

SELECT @myTag1 = (
            SELECT TOP 1
                   TagID
            FROM   tag.Tag
            WHERE  TagName = 'MyTag1'
            )
     , @myTag2 = (
            SELECT TOP 1
                   TagID
            FROM   tag.Tag
            WHERE  TagName = 'MyTag2'
            )

(it feels logical to assume that the query planner will be clever enough to know this is what you want, but I have seen it not do so in more complex examples and the explicit TOP 1 encourage a more efficient plan)

If you need many more properties pulling out than just the one or two, then you might find a PIVOT based solution more efficient (see Scott's answer for more detail on that). Again, check the resulting query plans for confirmation (and make sure you test with realistic data sizes/patterns or you may get a different plan and therefore performance profile in Dev/Test than in Production).

A question though: is there a reason why you need this to happen in a single statement? The sub-query version should be exactly equivalent to the two statement version and some may find the time simple statements slightly clearer to read. If you are concerned about data changing between the two statements, then wrap them in an explicit transaction (or the larger process if they are part of one) to try enforce consistency.


Global only scoped trace flags in MS SQL Server If the scope of the trace flag is global only does it mean I can't use it only for one connection while not affecting the performance of others?

If the scope of the trace flag is global only does it mean I can't use it only for one connection while not affecting the performance of others?

The scope signifies the affect of trace flag, a global scope means it will be applicable to all connections of SQL Server, it cannot be made to work only for particular connection/session. On the other hand trace flags whose scope are limited to session will be applicable to particular session.

If you refer to Trace Flags documentation it says

  Trace flag behavior may not be supported in future releases of SQL Server.

If you are using SQL Server 2008 and above you have extended events to get deadlock information. Please see

Getting historical deadlock info using extended events
Graphically Viewing Extended Events Deadlock Graphs


Table contains 4 indexes inside InnoDB, which is different from the number of indexes 3 defined in MySQL The database is an old version of MariaDB, the equivalent of MySQL 5.5. The table contains those columns

id
auth
old_auth
device_id_fk

This error is showing

Table contains 4 indexes inside InnoDB, which is different from the number of indexes 3 defined in MySQL

The results of SHOW CREATE TABLE

CREATE TABLE `wtb_device_auths` (

  `id` int(11) NOT NULL AUTO_INCREMENT,

  `device_id_fk` int(11) NOT NULL,

  `auth` varchar(255) COLLATE utf8_unicode_ci NOT NULL,

  `old_auth` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,

  PRIMARY KEY (`id`),

  KEY `auth_index` (`auth`),

  KEY `auth1_index` (`device_id_fk`),

  KEY `auth2_index` (`old_auth`)

) ENGINE=InnoDB AUTO_INCREMENT=627642 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci

How to fix it?

The error is related to the difference between Innodb definition and the frm file definition.

It probably occurred because of a crash during an ALTER TABLE or similar statement at some point.

To restore the consistency an ALTER TABLE wtb_device_auths FORCE will recreate the table (while exclusively locking it). This recreation should make both the innodb and frm definitions consistent.


db_owner unable to drop database - Error 615, SQL Server I have a SQL Server 2012 database running on amazon EC2. I've created a user to be able to create, edit and drop databases. I gave the new user with dbcreator server role.

My user can connect remotely and successfully runs the create database foo; command. But when the user attempts to drop the database again with drop database foo; the command fails with the following error:

Warning: Fatal error 615 occurred at Feb  1 2014  5:15PM.
   Note the error and time, and contact your system administrator.
ErrorCode: 21

Even though the selected database is master (so I don't think it's because it's in use). And the command succeeds when I run it again logged in as an administrative user.

I checked the newly created database, and my user has been assigned the db_owner role in the database as I expected so my understanding is that this should be sufficient permissions for that user to be able to drop the database they just created.

According to http://technet.microsoft.com/en-us/library/ms178613.aspx the db_owner role should be sufficient permissions. "Requires the CONTROL permission on the database, or ALTER ANY DATABASE permission, or membership in the db_owner fixed database role."

I've looked up error 615 and found "Could not find database table ID %d, name '%.*ls'." which makes no sense to me. http://technet.microsoft.com/en-us/library/aa937592(v=sql.80).aspx

Sql server version info: Microsoft SQL Server 2012 (SP1) - 11.0.3368.0 (X64) /n    May 22 2013 17:10:44 /n Copyright (c) Microsoft Corporation/n   Express Edition (64-bit) on Windows NT 6.2 <X64> (Build 9200: ) (Hypervisor)/n - from select @@version.

I can guess you have the AutoClose option for database set to True. This is the default behavior when you create a database with Express Editions.

The mentioned error can occur exactly in this case. Actually the complete error message 615 states: "Could not find database ID %d, name '%.*ls'. The database may be offline. Wait a few minutes and try again." ... So it points that database could be closed during dropping.

So, go to DB properties, switch it to False and try again dropping it or use below script before dropping

ALTER DATABASE [MyDB] SET AUTO_CLOSE OFF
GO

Many point out that is better to have AutoClose set to False. I found this article explaining a bit more about AutoClose: http://sqlmag.com/blog/worst-practice-allowing-autoclose-sql-server-databases

Small extension of the answer:

-- this works in standard SQL Server Editions, but NOT with Express Editions:
CREATE DATABASE [MyDB]
GO
DROP DATABASE [MyDB]
GO

-- this works in ALL SQL Server Editions
CREATE DATABASE [MyDB]
GO
ALTER DATABASE [MyDB] SET AUTO_CLOSE OFF
GO
DROP DATABASE [MyDB]
GO


Why is dropping a primary key expensive? I have a table with a clustered PK. No foreign keys reference it. When I drop it I expect it to be more or less instantaneous, but it is instead very slow.

My assumption was that we'd just be removing some metadata and not having a constraint or clustering for future updates and inserts.

What is actually happening internally to make this expensive?

One of the reasons dropping the PK constraint, and the underlying clustered index, is expensive is that the underlying data is transformed into a HEAP. This affects all of the non-clustered indexes which have to update the pointers back to the row data from the old clustered index key to the new HEAP row. From Docs:

  The pointer from an index row in a nonclustered index to a data row is
  called a row locator. The structure of the row locator depends on
  whether the data pages are stored in a heap or a clustered table. For
  a heap, a row locator is a pointer to the row. For a clustered table,
  the row locator is the clustered index key.

So once you drop the PK and clustered index, the non-clustered indexes have their row locators updated from the clustered index key to the heap pointer. Depending on the size and number of indexes, there is potentially a substantial cost involved in this update.

This is also explained in this article:

  Dropping a clustered index can take time because in addition to
  dropping the clustered index, all nonclustered indexes on the table
  must be rebuilt to replace the clustered index keys with row pointers
  to the heap.


Select on multiple tables with group by Using Postgres 9.3, I have problem with a select over few tables.
I have simcards stock and I would like to get something like this:

| network | supplier | total topups (EUR) | 1st topup qty|
| three   | three    | 500                | 34           |
| three   | core     | 300                | 4            |
| o2      | o2       | 100                | 3            |

| network | supplier | total topups (EUR) | 2nd topup qty|
| three   | three    | 200                | 4            |

| network | supplier | total topups (EUR) | 3rd topup qty|
| three   | three    | 200                | 4            |

my table schemas are:

simcard(id, iccid(unique), network_id, supplier_id)
topup(id, date, amount, simcard_id)
network(id, name)
supplier(id, name)

at the moment I am able to select all first topups, but I don't know how to display them in a format as I would like (network| supplied by| topups sum| 1st topup qty)
Then I have no idea how to select 2nd, 3rd topup for every simcard. I tried with limit 1 offset 1 or offset 2 but it didn't work.

select
    distinct topup.iccid
    , s.created_date
    , n.name as "network"
    , p.name as "supplier"
from
(
select
    s.iccid as "iccid"
    , t.date

from
    simcard_topup as t
    join simcard_simcard as s
        on t.iccid_id = s.id
--where
--  CAST(t.date AS DATE) = date '2015-03-13' - integer '14'

group by
    s.iccid
    , t.date
order by
    t.date asc
) as topup
join simcard_simcard as s
    on topup.iccid = s.iccid
join simcard_network as n
    on s.network_id = n.id
join simcard_supplier as p
    on s.supplier_id = p.id

SELECT st.topup_nr, n.name AS network, p.name AS supplier
     , st.topup_sum, st.topup_qty
FROM  (
   SELECT t.topup_nr, s.network_id, s.supplier_id
        , sum(t.amount) AS topup_sum
        , count(*) AS topup_qty
   FROM  (
      SELECT simcard_id, amount
           , row_number() OVER (PARTITION BY simcard_id, ORDER BY date) AS topup_nr
      FROM   simcard_topup
      ) t
   JOIN   simcard_simcard s ON s.id = t.simcard_id  -- or so I assume
   GROUP  BY 1,2,3
   ) st
JOIN   simcard_network  n ON n.id = st.network_id
JOIN   simcard_supplier p ON p.id = st.supplier_id
ORDER  BY st.topup_nr, st.topup_qty DESC NULLS LAST
                     , st.topup_sum DESC NULLS LAST;

Notes

The core feature is the window function row_number() in the inner subquery to number topups per simcard. This makes aggregating for 1st, 2nd etc. topup  in the outer query simple.
For lack of information, assuming to join simcard_simcard to simcard_topup on s.id = t.simcard_id, though you also have on t.iccid_id = s.id in your code, which seems backwards ...
Don't use a basic type name like date as column name.
Why do you have CAST(t.date AS DATE) in your code? A date should be a date, so no need to cast it to date. Also, it seems we only need the date for ranking topups.
Counting per (network_id, supplier_id) and sorting the result by ... topup_qty DESC, topup_sum DESC, so the combination with the most topups comes first. NULLS LAST is just to be safe against NULL values.


Generate and Insert 1 million rows into simple table Description:

I try to insert 1 million rows into empty table on MSSQL 2012 Express.
Here my script:

-- set statistics time off
drop table t1
create table t1 (id int, a text, b text)
go

-- #1 - 1,000,000 - 30s -> 45s
with ID(number) as
(
    select 1 as number
    union all
    select number + 1
    from ID
    where number < 1000000 + 1
)
insert into t1
    select number, 'a_' + cast (number as varchar), 'b_' + cast (number/2 as varchar)
    from ID
    option(maxrecursion 0)

-- #2 - 1 million rows => ~140,000 rows = 120s (have to cancel query)
declare @count int
set @count = 0
while @count < 1000000
begin
    set @count = @count + 1
    insert into t1
        values(@count, 'a_' + cast (@count as varchar), 'b_' + cast (@count/2 as varchar))
end

-- #3 - ~1,300,000 rows - 18s -> 20s

with temp as
(
    SELECT  ROW_NUMBER() OVER(ORDER BY a.object_id) as tcount
    from sys.all_columns a,  sys.all_columns b
    where a.object_id = b.object_id
)
insert into t1
    select tcount, 'a_' + cast (tcount as varchar), 'b_' + cast (tcount/2 as varchar)
    from temp
go

declare @count int
set @count = 0
while @count < 3
begin
    with temp as (select max(id) + 1 as max_id from t1)
    insert into t1
        select max_id, 'a_' + cast (max_id as varchar), 'b_' + cast (max_id/2 as varchar)
        from t1, temp
    set @count = @count + 1
end

-- #4 -- 1,000,000 = 3s -> 4s (have to drop t1 first)
with a(k) as
(
select 1 as k
union all
select k + 1 from a where k < 99 + 1
) ,
t2 as (
select row_number() over(order by x.k) as k
from a x , a y , a z
)
select k as id , 'a_' + cast (k as varchar) as a, 'b_' + cast (k/2 as varchar) as b into t1
from t2

Question:

After researching , I found 4 solutions. Are there any better solution (not using copy data from files) ?

Itzik Ben-Gan uses following approach
This is probably the fastest way he found and he's quite smart :-)

WITH
  L0   AS (SELECT c FROM (SELECT 1 UNION ALL SELECT 1) AS D(c)), -- 2^1
  L1   AS (SELECT 1 AS c FROM L0 AS A CROSS JOIN L0 AS B),       -- 2^2
  L2   AS (SELECT 1 AS c FROM L1 AS A CROSS JOIN L1 AS B),       -- 2^4
  L3   AS (SELECT 1 AS c FROM L2 AS A CROSS JOIN L2 AS B),       -- 2^8
  L4   AS (SELECT 1 AS c FROM L3 AS A CROSS JOIN L3 AS B),       -- 2^16
  L5   AS (SELECT 1 AS c FROM L4 AS A CROSS JOIN L4 AS B),       -- 2^32
  Nums AS (SELECT ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) AS k FROM L5)

select k as id , 'a_' + cast (k as varchar) as a, 'b_' + cast (k/2 as varchar) as b into t1
from nums
where k <= 1000000


Why are query results shortened in pgAdmin 1.18.1? I've recently installed pgAdmin 1.18.1 and noticed a strange thing:

Long json query results are shortened to 256 symbols and then ' (...)' is added.

Could someone help me disable this shortening?

There is a setting for that in the options: Max characters per column - useful when dealing with big columns. Obviously your setting is 256 characters.

Set it higher or set it to -1 to disable the feature.
Consult the fine manual here for more details.


"Data truncated" vs "Incorrect string value" I'm trying to figure out the difference between some warning messages related to data truncation.  Consider the following table:

CREATE TABLE `txttest` (
     `mycol` text NOT NULL )
ENGINE=InnoDB DEFAULT CHARSET=utf8;

Pretty much expected behavior:

mysql > insert into txttest (mycol) values (repeat('a',65535));
Query OK, 1 row affected (0.17 sec)

mysql > insert into txttest (mycol) values (repeat('a',65536));
Query OK, 1 row affected, 1 warning (0.16 sec)

mysql > show warnings;
+---------+------+--------------------------------------------+
| Level   | Code | Message                                    |
+---------+------+--------------------------------------------+
| Warning | 1265 | Data truncated for column 'mycol' at row 1 |
+---------+------+--------------------------------------------+
1 row in set (0.00 sec)

When I'm using multi byte characters however, the expected warning state occurs but the message is different:

mysql > insert into txttest (mycol) values (repeat('é',65536/2-1));
Query OK, 1 row affected (0.17 sec)

mysql > insert into txttest (mycol) values (repeat('é',65536/2));
Query OK, 1 row affected, 1 warning (0.16 sec)

mysql > show warnings;
+---------+------+----------------------------------------------------------------+
| Level   | Code | Message                                                        |
+---------+------+----------------------------------------------------------------+
| Warning | 1366 | Incorrect string value: '\xC3\xA9' for column 'mycol' at row 1 |
+---------+------+----------------------------------------------------------------+
1 row in set (0.00 sec)

Google attempts aren't very useful as I'm mostly running across pastes of this message for different contexts.

Basically what I'm wanting to know is: Is this just simply the wording/error code Mysql uses when truncating a sequence of multibyte characters, or is there something more telling I should be gleening from this message?

I was initially thinking it meant the byte sequence was getting split in such away it was resulting in malformed characters.  Attempts to try and make it do this didn't work (e.g. mysql seemed good about recognizing proper byte boundaries for a character encoding).

Edit:

After relooking over it does look to be the char splitting I initially dismissed.  I had an off by one brain fart looking at it initially.

It is an attempt to store a 2-byte string into one byte

Since the maximum length of TEXT is 65535, it can safely hold 32767 (65536/2 - 1) 2-byte characters without an error message.

Any attempt to add 32768 2-byte characters will result in Incorrect string value: '\xC3\xA9' for column 'mycol' at row 1 because the 32768th character does not have the room to be inserted into mycol.

In reality, you do not get a malformed character. You really can a TEXT field whose 2-byte character count and length is 32767. The last character is simply lost and not considered .

To make sure, run this

select length(mycol) from txttest;

The last INSERT will not be length 65536 by 65534.


IF statement in ON clause Can you help correct the syntax for this code?

INNER JOIN Users u
ON case when c.sender = ? then c.reciever = u.id else c.sender = u.id

How do I do I put a CASE statement in a JOIN clause?

You need to keep the comparison column outside of your CASE statement.

INNER JOIN Users u
ON u.id = CASE WHEN c.sender = ? then c.reciever else c.sender END


powershell to get sql server memory counters and show value I am writing a powershell script to capture the following SQL server counters:

SQL Server: Memory Manager: Total Server Memory (KB)
SQL Server: Memory Manager: Target Server Memory (KB)

My machine has 3 instances of SQL servers, so I want this script to capture all counters dynamically and report the value for 1 sample only. i tried writing the following:
Get-counter -List *SQL*Memory* | Select paths, counter | format-list # doesn't display full list

Get-counter -List *SQL*Memory* | Select paths, counter | where {_.counter -like "*server memory*"} |format-list # displays nothing

eventually I want to run this across multiple servers with -computername parameter and hence I want it to capture dynamically.
Can anyone please help me in finding what is missing?
Following is the exact script that I am running:
Function checkTransactionsPerSecond([string] $Hostname )
{
    (Get-Counter -ListSet "*Databases").Counter | Where {$_ -like "*\Transactions/sec"} #this returns nothing
#   $listofmetrics = (Get-Counter -ListSet "*Databases").Counter | Where {$_ -like "*\Transactions/sec"}
#   $listofmetrics | Get-Counter
}

clear

foreach ($Hostname in Get-Content "D:\TEMP\machines.txt")
{
    Write-Host $Hostname
    checkTransactionsPerSecond($Hostname)
}

thanks in advance

Aaron Bertrand wrote a good post on it that is pretty detailed...How I use PowerShell to collect Performance Counter data.

Then Laerte Junior has an excelent walk through on how he finds the counters he wants in a Simple-Talk article: Gathering Perfmon Data with Powershell. This might be where you want to start. It has some cmdlets that he uses to capture the counters for a particular instance I believe.

See if this is what you  need:

$listofmetrics = (Get-Counter -ListSet "*Databases" -ComputerName $hostname).Counter | Where {$_ -like "*\Transactions/sec"}
$listofmetrics | Get-Counter


Variables in DB data to avoid redundancy? "Avoid redundancy" is important to me.

I want to store the input data for configuration management in a relational database.

My input data:

project_name
linux_user
db_user
db_name

In 95% of all cases all attributes are equal:

project_name: foo
linux_user: foo
db_user: foo
db_name: foo

If I would store this data in YAML, then I could use variables to avoid repetition.

AFAIK this does not work in relational databases.

I could store this in DB, but AFAIK I need to evaluate it myself:

project_name: foo
linux_user: {project_name}
db_user: {project_name}
db_name: {project_name}

How can I avoid redundancy if I store this in a relational database? Unfortunately these values are not always equal, only in most cases.

I use PostgreSQL

There is no built-in way of doing this in SQL. If the default is always the project_name, I would probably allow nulls in the other columns, and create a view that uses coalesce() to return a default value:

create view real_data
as
select project_name,
       coalesce(linux_user, project_name) as linux_user,
       coalesce(db_user, project_name) as db_user,
       coalesce(db_name, project_name) as db_db_name
from base_data;

Another option would be a complicated case statement for each column:

create view real_data
as
select project_name,
       case linux_user
           when '{project_name}' then project_name
           when '{db_name}' then db_name
           when '{db_user}' then db_user
           else linux_user
       end as linux_user,
       ... repeat the above for the other columns ..
from base_data;

You could hide the case statement in a function or use a set returning function and dynamic SQL, so you don't need to hardcode each column name and possible variable.

If you only need to make sure you have a default value when creating the rows, then just replacing that in a trigger during insert might also be an option. That way there is no need for the view.


SQL Server - best practice before applying trace flags? We are considering enabling trace flags 1118, 4199, 8048 on a server to fix some some performance issues

We are running into performance issues since migrating from a physical server running SQL Server 2008 to a virtual server running SQL Server 2012 (2 sockets 24 core, 40GB RAM) particularly around one of the ETL process.

As it is a difficult service to get downtime for, is it safe to apply without any downtime?  Or, is it wise to restart the service with these as startup parameters, I'm particularly concerned with the trace flag 8048

As for Trace Flag 8048, this MSDN blog says:

  HOW DO I KNOW IF I NEED THE TRACE FLAG?

  The issue is commonly identified by looking as the DMVs dm_os_wait_stats and dm_os_spinlock_stats for types (CMEMTHREAD and SOS_SUSPEND_QUEUE).   Microsoft CSS usually sees the spins jump into the trillions and the waits become a hot spot.

  Caution: Use trace flag 8048 as a startup parameter.   It is possible to use the trace flag dynamically but limited to only memory objects that are yet to be created when the trace flag is enabled.  Memory objects already built are not impacted by the trace flag.

Have you identified the need for trace flag 8048 as per the instructions above?

Since 8048 needs to be implemented as a startup trace flag, I'd configure 1118 at the same time.  I'd also consider adding 1117 if you are concerned about tempdb performance.

4199 does not need to be implemented as a startup trace flag, although that is certainly recommended once you've established that you really need it, and your workload will benefit from the great many changes it introduces.

On that note, I'd recommend enabling these trace flags only after extensive testing with representative load on a test server.


How can I search a column in MySQL containing unix timestamps with a query referencing a different format? I have a mysql table that contains something like:

+-------------+-----------------+
| FOO1        | FOOTIME         |
+-------------+-----------------+
|        blah |   1234567890000 |
+-------------+-----------------+

FOOTIME uses unix timestamp.

I want to run a query that essentially says "how many rows contain foo time older than January 1st 2012?

I thought I'd want to do something like select count(*) from foodb.FOOTABLE where FOOTIME <= TIMESTAMP('2012-01-01'); but that didn't work.

What am I missing?

Try this:

SQL code:

SELECT COUNT(FOO1)
FROM footable
WHERE FOOTIME >= UNIX_TIMESTAMP('2012-01-01');

SELECT COUNT(FOO1)
FROM footable
WHERE FROM_UNIXTIME(FOOTIME)>='2012-01-01';

Sample data:

CREATE TABLE footable (
  FOO1 varchar(30) primary key,
  FOOTIME int(11)
  );

INSERT INTO footable (FOO1, FOOTIME)
VALUES ('blah', UNIX_TIMESTAMP(now() - INTERVAL 1 YEAR)),
('Blahblah', UNIX_TIMESTAMP(now() - INTERVAL 2 YEAR)),
('BLablahblah', UNIX_TIMESTAMP(NOW() - INTERVAL 5 YEAR));

SQLFiddle demo


PostgreSQL how to get case insensitive schema name I created a schema with uppercase letters like this:

CREATE SCHEMA "requeteSQL"  AUTHORIZATION ......;

Now when I try to GRANT some privileges like :

GRANT SELECT ON ALL TABLES IN SCHEMA requetesql  TO ....;

I get the following error :

ERROR:  schema "requetesql" does not exist

Double quotes forces case sensitive names.
From this blog: Don’t use double quotes in PostgreSQL

Now, there is a way around this, namely by using double quotes.  Whereas single quotes in PostgreSQL are used to create a text string, double quotes are used to name an identifier without changing its case.
Let me say that again, because so many people get this wrong: Single quotes and double quotes in PostgreSQL have completely different jobs, and return completely different data types.  Single quotes return text strings.  Double quotes return (if you can really think of them as “returning” anything) identifiers, but with the case preserved.

In your question you should change:
GRANT SELECT ON ALL TABLES IN SCHEMA requetesql  TO ....;

by
GRANT SELECT ON ALL TABLES IN SCHEMA "requeteSQL"  TO ....;


How to access an Oracle database from linux We have a legacy Oracle database hosting all of the information for a now shutdown website, we need to keep the data on the DB available for a period of time due to GDPR etc. We have had some requests from previous users to have there information deleted.
The problem...
This database was last in use 5-6 years ago (for legal reasons we need to maintain access for 10 years), none of the staff still with the company have DB accounts capabale of performing delete actions, we can only read the data.
We have full root access to the linux server hosting the DB but otherwise we only have DB accounts capable of reading information. Old accounts which are able to perform deletes etc exist on the DB but the passwords for these are lost in time.
Is there any way by which we can create a new database user with suitable access? or to some how trigger a pwd reset of an old DB User (given our limited access to the DB itself)?
Edited for clarity:
oracle linux 6.5,
oracle db 12.1.0.2
After using su - oracle (and ensuring the machine is powered on) i can now run sqlplus!
is there something i need to do to get sqlplus to run using the os creds? I found some online info around this, which suggested some setting in the sqlnet.ora file, but the only instance of this file on the server is the sample file which i presume is not the correct one to be updating e.g.
/u01/app/oracle/product/12.1.0.2/network/admin/samples/sqlnet.ora

I tried:
sqlplus / as sysdba

which gives me the SQL prompt, but if i attempt to run:
conn /

Then it responds with invalid username/pwd - i assume conn is meant to connect you to an actual db (not sure, found it in some blog)
final edit:
fixed my final issue, needed to include the schema name when connecting! thanks all.

Since you have root access to the server, you can su to the Oracle instance owner (typically oracle), connect to the database locally using OS authentication (e.g. sqlplus / as sysdba), and create new (or modify existing) Oracle users or roles as needed.

In case you're not familiar how su and login shells work on Linux, you will need to run su - oracle to ensure the Oracle user profile gets executed, setting up the shell environment properly. If the database was installed by an experienced person, that would include configuring all the necessary Oracle environment variables, allowing you to call its utilities, such as sqlplus.
If an attempt to run sqlplus fails with "command not found" or something similar, try to set the Oracle environment yourself by running, as the oracle user, source /usr/local/bin/oraenv. Note that oraenv may be elsewhere, not necessarily in /usr/local/bin, depending on your Oracle version, Linux flavour, and on how Oracle was actually installed.

Thanks to Michael Kutz  and EdStevens for valuable critique.


Oracle 11.2.0.3 to 11.2.0.4 I'm planning to upgrade 3 instances of Oracle (all same Oracle Home) from 11.2.0.3 to 11.2.0.4. I'm going through the installer and I get to the question about Upgrade or install just the database software. I only want to upgrade one instance initially and leave the others running on 11.2.0.3 in parallel. My question is, what's the difference between:

Install database software only
Upgrade an existing database

I really don't want to touch the other SIDs on there.

Choose Install database software only, then later you can upgrade your databases one by one with DBUA (Database Upgrade Assistant) from the new Oracle Home, or by performing a manual upgrade.


Reasons for incremental backup on Progress OpenEdge In the case that storage is not a problem: are there actually any good reasons for doing incremental backups instead of just doing full backups?

Edit
This could actually refer to any database with full & incremental backups. In this case we use a Progress OpenEdge RDBMS with support for both backup plans as well as real time transactional log archiving. Apart from that I don't think the question must relate to a single vendor. The choice of full/incremental backup can apply to lots of different database engines.

Time for the backup to run is a reason for doing incremental backup on Progress. My backup is running fast enough that I did not need to use this function and I'm still doing only full backups.

It also depends on your requirements. For example, if you have heavy financial transactions and you want to keep a backup every hour or something like that, you would need to do incremental (or real time).

But unless you have something forcing you to do incremental, I would do full backup, I find that easier to restore.

In terms of performance impact, if you have a fast disk array, I haven't seen much impact of doing a backup even during moderately heavy usage. Obviously it depends also on the size of your system. I'm talking about a 37Gb DB.


Is there a way to get the query results (rows affected/time taken) after every query I run multiple MySQL queries together in phpMyAdmin, I basically want to be able to see the results of each query (rows affected/time taken etc) as it completes. Say I have the following sample queries

UPDATE PC1 SET `A1` = 'C1' WHERE `A1` = 'B1';
UPDATE PC1 SET `A2` = 'C2' WHERE `A2` = 'B2';
UPDATE PC1 SET `A3` = 'C3' WHERE `A3` = 'B3';

When I run these and say a few more what happens is at the end of the third/last query I get the results together

163 rows affected. (Query took 0.0015 seconds.)
445 rows affected. (Query took 0.0065 seconds.)
779 rows affected. (Query took 0.0215 seconds.)

I currently use SHOW FULL PROCESSLIST but I would like to be able to see the query results as they complete.

Is there a way to for me to run all of the queries together and see the query results as they complete?

I want to run the above three update queries. I can run it one by one, run the first one and wait for it to complete, review the rows affected/time taken and then run the second and wait for it to complete and the same for the third. Alternatively, I can just write all three together and wait for all of them to run and complete. I am doing the second, however what happens is all of the three run together and at the end of the third, I see the rows affected for all three together. I am trying to see if I can see the output of each query as it completes. In reality, I have tens of queries.

I get all three stats, however I get all of them at once on completion of the third query. I am trying to see if I can get the stats as they complete, say first query completes and I get the stats on screen instead of waiting for all three to complete and then seeing all three stats at once.

You send one command, one line (which consists from 3 queries). So PHPMyAdmin waits until this command finished, and then shows the result of this one command which consists from 3 separate results. I think he has this feature - it is PHPs input buffering.

Use CLI interface, for example - it shows the result for each query in a command immediately after this result is formed and sent to server's output / client's input. - Akina


General strategy for updating a full text search index I am using a SQL Server 2008 R2 DB with FTS enabled on it. I applied an FTS index on a table. The table is frequently updated (I mean the CUD operations). Keeping this in mind, I came up with a strategy for updating the above index:

I run an update population of the index when any delete occurs on that table
I run an update population of the index once an hour to track the newly inserted texts

I would be grateful for any constructive criticism of my ideas. Should any additional details be necessary, I will provide them.

Thanks in advance

Any reasoning for not using Change Tracking-Based Population and letting SQL Server take care of the updates automagically?

Unless you are depending on WRITETEXT or UPDATETEXT to change LOB columns (which are not tracked), I'd suggest accepting the slight overhead associated with change tracking, enable automatic population (which is the default) and rest easy.


ORA-02292: integrity constraint violated - child record found while creating procedures in ORACLE SQL Developer? I have written a procedure that has as input the CUSTOMER_ID. Then the procedure deletes the corresponding customer from the table CUSTOMERS.

CREATE OR REPlACE PROCEDURE DELETE_CUSTOMER
(CUSTOMER_ID NUMBER) AS
TOT_CUSTOMERS NUMBER;
BEGIN
    DELETE FROM CUSTOMERS
    WHERE CUSTOMERS.CUSTOMER_ID = DELETE_CUSTOMER.CUSTOMER_ID;
    TOT_CUSTOMERS := TOT_CUSTOMERS - 1;
    END;
/

I have to execute the procedure to delete customer with id 1.

EXECUTE DELETE_CUSTOMER(01);

When I do this, I get an error

Error starting at line : 120 in command -
BEGIN DELETE_CUSTOMER(01); END;
Error report -
ORA-02292: integrity constraint (TUG81959.ORDERS_FK_CUSTOMERS) violated - child record found
ORA-06512: at "TUG81959.DELETE_CUSTOMER", line 5
ORA-06512: at line 1
02292. 00000 - "integrity constraint (%s.%s) violated - child record found"
*Cause:    attempted to delete a parent key value that had a foreign
           dependency.
*Action:   delete dependencies first then parent or disable constraint.

I know this is because there is a foreign key for CUSTOMER_ID on the table ORDERS, which means the customer cannot be deleted because he has placed an order.
How do I write the code so that I can first delete the corresponding ORDER_DETAILS and then delete the corresponding ORDERS so that I can finally be able to delete a record from CUSTOMERS?

I tried rewriting the code but I am just lost now:

CREATE OR REPlACE PROCEDURE DELETE_CUSTOMER
(CUSTOMER_ID_IN NUMBER) AS
TOT_CUSTOMERS NUMBER;
CURSOR C1 IS
DELETE FROM ORDERS
WHERE ORDERS.ORDER_ID = CUSTOMER_ID.ORDER_ID;
CURSOR C2 IS
DELETE FROM ORDER_DETAILS
WHERE ORDER_DETAILS.ORDER_ID = CUSTOMER_ID.ORDER_ID;
CURSOR C3 IS
DELETE FROM CUSTOMERS
WHERE CUSTOMERS.CUSTOMER_ID = DELETE_CUSTOMER.CUSTOMER_ID;
BEGIN
    OPEN C1;
    OPEN C2;
    OPEN C3;
    IF C1%FOUND AND C2%FOUND AND C3%FOUND
    THEN TOT_CUSTOMERS := TOT_CUSTOMERS - 1;
    END IF;
    CLOSE C1;
    CLOSE C2;
    CLOSE C3;
END;
/

Here are the tables for reference:

No code needed for this, just define the constraint with ON DELETE CASCADE option.

alter table orders drop constraint ORDERS_FK_CUSTOMERS;
alter table orders add constraint ORDERS_FK_CUSTOMERS
  foreign key (customer_id) references customers(customer_id) on delete cascade;

Same for order_details.


BCP woes: Unexpected EOF encountered in BCP data-file I have a table defined as such:

CREATE TABLE [dbo].[IpMetadata](
    [StartIp] [bigint] NOT NULL,
    [EndIp] [bigint] NOT NULL,
    [CountryCode] [char](10) NOT NULL,
    [ProxyType] [varchar](50) NULL,
    [ProxyDescription] [varchar](50) NULL,
    [IspName] [varchar](100) NULL,
    [MobileCarrier] [varchar](50) NULL,
    [MobileCarrierCode] [varchar](50) NULL,
    [Latitude] [varchar](50) NULL,
    [Longitude] [varchar](50) NULL,
    [PostalCode] [varchar](50) NULL,
    [City] [varchar](50) NULL,
    [Region] [varchar](50) NULL,
    [Country] [varchar](50) NULL,
    [GmtOffset] [varchar](50) NULL,
    [SupportsDaylightSavings] [char](10) NULL,
    [MetroCode] [varchar](50) NULL,
    [AddressCount] [int] NOT NULL,
     CONSTRAINT [PK_IpMetadata] PRIMARY KEY CLUSTERED
    (
        [StartIp] ASC,
        [EndIp] ASC
    )
)

I have a UTF-8 encoded sampling file (D:\data\ipsnip.csv) with tab-delimited, CRLF terminated rows to insert into this table like so:

#start-ip   end-ip  edge-two-letter-country proxy-type  proxy-description   isp-name    mobile-carrier  mobile-carrier-code edge-latitude   edge-longitude  edge-postal-code    edge-city   edge-region edge-country    edge-gmt-offset edge-in-dst edge-metro-code address-count
0   0   **                  0   0   0   0   reserved    *** *** +9999   n   -1  0
1   255 **                  0   0   0   0   reserved    *** *** +9999   n   -1  254
256 16777215    **                  0   0   0   0   reserved    *** *** +9999   n   -1  16776959
16777216    16777343    au                  0   -37.7596    145.134 3106    templestowe vic aus +1000   n   36211   127
16777344    16777407    au                  0   -37.7596    145.134 3106    templestowe vic aus +1000   n   36211   63
16777408    16777471    au                  0   -37.7596    145.134 3106    templestowe vic aus +1000   n   36211   63
16777472    16778239    cn          chinanet fujian province network        0   26.0786 119.298 350000  fuzhou  35  chn +800    n   156115  767
16778240    16779263    au          big red group       0   -37.8387    144.99  3141    south yarra vic aus +1000   n   36206   1023
16779264    16781311    cn          chinanet guangdong province network     0   30.6611 104.082 510000  guangzhou   44  chn +800    n   156196  2047
16781312    16785407    jp          i2ts inc.       0   35.6838 139.754 100-0001    tokyo   13  jpn +900    n   -1  4095

I run the BCP command like so:

bcp MyDatabase.dbo.IpMetadata in D:\data\ipsnip.csv -F2 -Slocalhost -n -T

I get a response back like so:

Starting copy...
SQLState = S1000, NativeError = 0
Error = [Microsoft][ODBC Driver 11 for SQL Server]Unexpected EOF encountered in BCP data-file

BCP copy in failed

I have tried explicitly specifying the column and row terminators.  I have tried using unicode column definitions.  I have tried changing the line ends to LF instead of CRLF.  I have tried replacing the field terminators with semicolons/ pipes.  I have tried -n and-N.  I don't know what else to try.  Can anyone assist?

So it turns out that the -n and the -N are not what I wanted;  I had assumed it used the db metadata to implicitly convert the incoming data, but it looks like it's expecting binary data with that.

I changed it to -c and it worked without issue.


Tools to monitor Oracle Database Performance on Standard Edition Here is what I am looking for:

Works on Standard Edition i.e. No Diagnostic Pack.
Provides both current and historical performance data.
Good visuals for easy spotting of trends and presentation to management.

If you have used the tool, I would be interested in pros and cons you have discovered.  I can come up with a list, but the insight of actually having used the tool is worth far more.

Note: I've never used this, so please edit this answer if you have.

Lab128

  Lab128 provides additional savings by implementing ASH functions and
  not requiring the purchase of ODP.

Pros:

Response Time Analysis
RAC support
Agentless
Only $299 for a named user connecting to two instances and only $499 for a named user connecting to an unlimited number of instances.

Cons:

Interface isn't very polished.
Lack of alerts.
One writer/maintainer.
No web interface.
Not server based.


What are all the XTP_THREAD_POOL processes doing? I just ran sp_who on my SQL Server 2016 instance that uses MEMORY_OPTIMIZED tables, and I see several XTP_THREAD_POOL processes running:

Some other details about the output:

There are exactly 6 XTP_THREAD_POOL rows in the resultset
My system has two logical cores

Specifically, it's a VM with 2 sockets - each socket housing a 1-core processor

All of these tasks are running as background (sa)

What are these processes doing?  I'm not finding much on Google about this task name.

TIA

Information about these tasks is, indeed, hard to come by.

The docs (Creating and Managing Storage for In-Memory OLTP -> Scalability) say that

  In SQL Server 2016 (13.x), there are multiple concurrent threads responsible to persist changes to memory-optimized tables.

And while the docs don't mention the "cmd" value that might be associated with these concurrent threads, there is a PowerPoint from Bob Ward that links to the doc page above and adds more details about this process:

  You may see these as command = XTP_THREAD_POOL or XTP_PREEPMTIVE_TASK

So these tasks are keeping the data and delta files used by in-memory OLTP up-to-date.  They run in the background all the time.  While they are idle, they rack up DISPATCHER_QUEUE_SEMAPHORE waits.

The presentation also mentions that:

  Pools should get no bigger than # logical CPUs...

So it's a little surprising to see 6 of these processes running on a 2-processor box.  This might be a bug, or things could have changed since Bob's PowerPoint presentation.  Hopefully someone with more Hekaton smarts than me will come by and weigh in.

HTH


Without knowing what you mean by dynamic days interval, the following is an example of how I might attempt this.

USE tempdb;
GO
CREATE TABLE dbo.ShiftStatii
(
    ShiftStatusID INT NOT NULL
        CONSTRAINT PK_ShiftStatii
        PRIMARY KEY CLUSTERED
        IDENTITY(1,1)
    , StatusName VARCHAR(255) NOT NULL
);

INSERT INTO dbo.ShiftStatii (StatusName)
VALUES ('Present'), ('Absent'), ('Holiday');

CREATE TABLE dbo.Employees
(
    EmpID INT NOT NULL
        CONSTRAINT PK_Employees
        PRIMARY KEY CLUSTERED
        IDENTITY(1,1)
    , EmployeeName VARCHAR(255)
);

INSERT INTO dbo.Employees (EmployeeName)
VALUES ('John'), ('Jane'), ('Joe');

CREATE TABLE dbo.Shifts
(
    ShiftID INT NOT NULL
        CONSTRAINT PK_Shifts
        PRIMARY KEY CLUSTERED
        IDENTITY(1,1)
    , EmpID INT NOT NULL
        CONSTRAINT FK_Shifts_EmpID
        FOREIGN KEY REFERENCES dbo.Employees(EmpID)
        ON UPDATE NO ACTION ON DELETE NO ACTION
    , InDate DATE
    , ShiftStatusID INT NOT NULL
        CONSTRAINT FK_Shifts_ShiftStatusID
        FOREIGN KEY REFERENCES dbo.ShiftStatii(ShiftStatusID)
        ON UPDATE NO ACTION ON DELETE NO ACTION
);

INSERT INTO dbo.Shifts (EmpID, InDate, ShiftStatusID)
VALUES (1, '20130201', 1)
    , (2, '20130201', 2)
    , (3, '20130201', 1)
    , (1, '20130202', 1)
    , (2, '20130202', 2)
    , (3, '20130202', 1)
    , (1, '20130203', 1)
    , (2, '20130203', 1)
    , (3, '20130203', 2)
    , (1, '20130205', 1)
    , (2, '20130205', 3)
    , (3, '20130205', 2);

SELECT E.EmployeeName, S.InDate, SS.StatusName
FROM dbo.Shifts S
    INNER JOIN dbo.Employees E ON S.EmpID = E.EmpID
    INNER JOIN dbo.ShiftStatii SS ON S.ShiftStatusID = SS.ShiftStatusID
ORDER BY E.EmployeeName, S.InDate

The data for my test, therefore, is:

SELECT E.EmployeeName, S1.InDate, S2.InDate
FROM (
        SELECT ROW_NUMBER()
            OVER (PARTITION BY S.EmpID ORDER BY S.InDate) AS EmpRowNum, *
        FROM dbo.Shifts S) S1
    INNER JOIN (
        SELECT ROW_NUMBER()
            OVER (PARTITION BY S.EmpID ORDER BY S.InDate) AS EmpRowNum, *
        FROM dbo.Shifts S
        ) S2 ON S1.EmpID = S2.EmpID AND S1.EmpRowNum = S2.EmpRowNum - 1
INNER JOIN dbo.Employees E ON S1.EmpID = E.EmpID
WHERE S1.ShiftStatusID = 2 AND S2.ShiftStatusID = 2

The results of this are:


How do I see where postgres privileges are coming from for a role? Im using google cloudsql but this is a more generic postgres question.
I have a role (that is already created and managed by cloudsql) called cloudsqliamserviceaccount.
The cloudsqliamserviceaccount role is not a member of any other roles.
Whenever I create a new postgres user (through GCP console or gcloud) they are added to this role. I CANNOT remove them from the role because it will break the cloudsql IAM integration.
The cloudsqliamserviceaccount role has very sweeping permissions. It can connect to and do CRUD on all DBs. But I'm not sure how to see its full list of permissions.
In short I have this role "cloudsqliamserviceaccount". How do I see what it has access to? I gets CRUD automatically for every new database I create, but its not a member of any other role. Where is this permission coming from and how do I see that?

The permissions are stored on the individual objects, and there is no central view to see them all.
You could use the access privilege inquiry functions for each type of object to get that information.
For SELECT on tables, views, sequences, composite types or materialized views that could be:
SELECT oid::regclass, relkind
FROM pg_class
WHERE  has_any_column_privilege(
          'cloudsqliamserviceaccount'::regrole,
          oid,
          'SELECT'
       );

You need similar queries for other object types.
Note that such a query will only give information about objects in the current database, so you would have to run it on each database in turn.
Another crude method is to run
DROP ROLE cloudsqliamserviceaccount;

That will give you an error message and list some of the privileges the role has in the current database.


Audit everything coming from an ip adress in an Oracle database I need to audit all activity coming from a specific ip adress. Whats the best method to do this in an Oracle Database 19c?

With Unified Auditing.
Oracle Database Auditing

Oracle Database Unified Auditing enables selective and effective
auditing inside the Oracle database using policies and conditions. The
new policy based syntax simplifies management of auditing within the
database and provides the ability to accelerate auditing based on
conditions. For example, audit policies can be configured to audit
based on specific IP addresses, programs, time periods, or connection
types such as proxy authentication.

Example:
CREATE AUDIT POLICY mypolicy
ACTIONS ...
WHEN 'SYS_CONTEXT(''USERENV'', ''IP_ADDRESS'') = ''1.2.3.4'''
EVALUATE PER SESSION;