Is there a way I can use currval() without selecting it more than once?

First of all, you would not need a subquery in your statement. The bare function does the job:

INSERT INTO testschema.requestinfo (requestid, reportid, amount)
VALUES (currval('testschema.requests_requestid_seq'), 10, 40)
     , (currval('testschema.requests_requestid_seq'), 15, 29)
     , (currval('testschema.requests_requestid_seq'), 40, 4);

Next, currval() only makes sense if you have (implicitly or explicitly) called nextval() for the same sequence before in your session. If you have not called nextval() for any other sequences in the meantime, you can simplify with lastval().

INSERT INTO testschema.requestinfo (requestid, reportid, amount)
VALUES (lastval(), 10, 40)
     , (lastval(), 15, 29)
     , (lastval(), 40, 4);

Either way, if you have many rows like this, look to @dezso's answer for a way to avoid typing the function repeatedly.

currval() and lastval() are volatile functions:

SELECT proname, provolatile
FROM   pg_proc
WHERE  proname IN ('currval', 'lastval')

Result:

proname provolatile
currval v
lastval v

That means, they will still be evaluated for every row that way.
To also avoid repeated execution of the function, move it to a separate CTE or subquery and CROSS JOIN to your input values:

INSERT INTO testschema.requestinfo (requestid, reportid, amount)
SELECT *           -- order of rows is maintained from left to right
FROM   lastval()   -- or currval('testschema.requests_requestid_seq')
     , VALUES (10, 40), (15, 29), (40, 4);


How do I find what keys mysql will use for a query?

You are approaching this from the wrong direction.  Any query can deadlock anytime.  So, you must be prepared to replay the entire transaction when a deadlock occurs.

Sure, it is a good idea to try to avoid deadlocks, there there are several things that can decrease their frequency.  The main technique is to make queries faster.

In the case you have mentioned, the Optimizer probably decided that avoiding the 'sort' for the ORDER BY was better than using any other index, or even using no index.  Let's see SHOW CREATE TABLE and EXPLAIN SELECT to further dissect what happened.

Another technique is to include the optimal index -- often a "composite" index over multiple columns.  Sometimes it is even good to have a "covering" index -- one that includes all the columns mentioned in the query.

When using IN, another technique is to sort the values.


Unless and until you try out some more observations and results of same, nothing can assure you 100 %. Although can assure you 99 % but rest you have to test.

Below query can give you what you said for dmv sys.dm_db_index_usage_stats as last modified date since restart on the usage of table.

SELECT last_user_update
FROM   sys.dm_db_index_usage_stats us
       JOIN sys.tables t
         ON t.object_id = us.object_id
WHERE  database_id = db_id()
       AND t.object_id = object_id('dbo.YourTable')

Also, below can give you an idea on same.
Result might be same on last usage date, but depends what you are looking for.
You need to understand the nature of that table as well depending upon the data out there.

SELECT
        [name]
       ,create_date
       ,modify_date
FROM
        sys.views
WHERE
        modify_date > DATEADD(day,-30,GETDATE())

In addition you will need trigger to observer what and when was it changed to analyse further.

Furthermore, if you have the default trace luckily out there , you might get some help that might get you close to what you are aiming for:
Read Collecting the Information in the Default Trace


Does having many databases affect MySQL performance?

The only performance hit I can see is on the INFORMATION_SCHEMA database (See my post How is INFORMATION_SCHEMA implemented in MySQL?)

If you have queries that poll for the existence of tables, gets the table sizes (Example: See my post MySQL Workbench Database Sizes), and things of this nature, MySQL will spend time sifting through the OS for such metadata. Getting metadata on InnoDB tables and partitioned MyISAM tables will make the INFORMATION_SCHEMA take the worst hit as it must open many file handles.

Other than that, you regular queries on your working dataset should be fine.


Error trying to run SSIS Packages in SQL Server 2008 SQL Server Agent

It sounds like your connection manager is setup to use windows Authentication.  If that is the case, when you execute the package via the SQL Server Agent it will run with credentials of the agent.

You need to do one of three things:

Give the account that the SQL Server Agent runs as appropriate permissions to execute SPs in your database.
Setup a proxy account for the agent with the appropriate permissions (as suggested by Jon Seigel).
Use a different authentication method - i.e SQL Server Auth.

I would go with option 1 if possible, but it depends what your company's policies are.


SQL Server: Should the same log records be backed up multiple times?

The archive redo log is roughly equivalent to a transaction log backup in SQL Server, and there are options within SQL Server to take multiple copies of the transaction log backup simultaneously.
When performing your BACKUP LOG operation, you can specify MIRROR TO to take multiple copies of the same backup at different locations, providing protection against disk/tape failure.
There is also some useful information here.


Durability of storage in second generation google cloud sql

You may not find any such information for GCP because Aurora is a storage engine native to Amazon. It can be used with MySQL or PostgreSQL.
You only have three(3) options here:
OPTION #1
You need to read the Amazon Documentation on Aurora and try to locate the same storage options and features in CloudSQL. Thoroughly scour the GCP Documentation for any new storage options for CloudSQL similar to Aurora.
OPTION #2
You should also focus on what Google provides for CloudSQL High Availability

Overview of the High Availability Configuration
Configuring an Instance for High Availability
Instance Locations

OPTION #3
You may want to consider just customizing a solution with ComputeEngine instances with MySQL manually installed and create frequent snapshots of your data folder (which should contain InnoDB tables only along with binary logs). Additional instances (at least 1) for setting up MySQL Replication slaves may be need to still have additional HA.


How can I test sql scripts intended to run with sqlcmd -v in Sql Server Management Studio

The precedence of setvar is the lowest.  Try it out and see if it works.


How to group all linked objects in a undirected cyclic graph

The underlying difficulty with this problem is that a table with a self-referencing key, as a data structure, is an implementation of a vertex ( or node )-based digraph.  One way of enabling bidirectional traversals in a relational database is to break out the self-referencing key as a separate table, so that the vertices of the graph can be represented by an entity table ( for instance, a Customer table, which you likely already have ) and the edges can be represented by the new relationship table ( your GroupCustomers table in this case, except the GroupCustomerNo would likely be moved to be an attribute of the Customer table ).

While the collection of edges can be leveraged in as many ways as you can imagine ( a linked list with "next" and "previous" attributes, an n-ary tree with "branch1", "branch2", ..., "branchn"; whatever it is you need your data model to do at this point ), a simple directed edge is often sufficient, as a directed edge contains enough information to represent both directions without the need for storing an additional record for the representation of the other direction - you merely have to swap the vertices.

With that in mind, in order to perform a bidirectional traversal of your tbl_GroupCustomers, simple recursion will be sufficient, though there is some preparation of the data necessary for the traversal.  For this scenario, simulating the graph from the digraph with a CTE seems reasonable, then a relatively run-of-the-mill recursive CTE to perform the traversal on the simulated graph.

USE tempdb;
GO

CREATE TABLE dbo.tbl_GroupCustomer
(
    CustNo                      INTEGER,
    JtCustNo                    INTEGER,
    GroupCustNo                 INTEGER
);

INSERT  INTO dbo.tbl_GroupCustomer ( CustNo, JtCustNo, GroupCustNo )
VALUES  ( 1, 2, NULL ), ( 2, NULL, NULL ), ( 3, 1, NULL ), ( 4, 1, NULL ),
        ( 4, 5, NULL ), ( 5, 6, NULL ), ( 5, 7, NULL ), ( 6, NULL, NULL ),
        ( 7, NULL, NULL ), ( 8, 5, NULL ), ( 9, NULL, NULL ), ( 10, 9, NULL );
GO

CREATE PROCEDURE dbo.usp_UpdateGroupCustomerNo
     @MainCustNo                INTEGER,
     @GrpNo                     INTEGER
AS BEGIN
    SET NOCOUNT ON;

    ;WITH
    cte_Graph AS (
        -- Simulate bidirectional relationships;
        SELECT  CustNo, JtCustNo
        FROM    dbo.tbl_GroupCustomer
        WHERE   JtCustNo IS NOT NULL
        UNION
        SELECT  JtCustNo, CustNo
        FROM    dbo.tbl_GroupCustomer
        WHERE   JtCustNo IS NOT NULL ),
    cte_Traversal AS (
        -- Regular traversal;
        SELECT  DISTINCT CustNo = NULL, JtCustNo = CustNo
        FROM    cte_Graph
        WHERE   CustNo = @MainCustNo
        UNION ALL
        SELECT  g.CustNo, g.JtCustNo
        FROM    cte_Traversal t
        INNER JOIN cte_Graph g
            ON  g.CustNo = t.JtCustNo
        WHERE ( g.JtCustNo <> t.CustNo
            OR  t.CustNo IS NULL ) )
    SELECT  *
    --UPDATE    gc
    --  SET GroupCustNo = @GrpNo
    FROM    cte_Traversal t
    INNER JOIN dbo.tbl_GroupCustomer gc
        ON  gc.CustNo = t.JtCustNo;

    SET NOCOUNT OFF;
END;
GO

EXECUTE dbo.usp_UpdateGroupCustomerNo @MainCustNo = 1, @GrpNo = 1;
EXECUTE dbo.usp_UpdateGroupCustomerNo @MainCustNo = 10, @GrpNo = 2;
GO

DROP PROCEDURE dbo.usp_UpdateGroupCustomerNo;
DROP TABLE dbo.tbl_GroupCustomer;
GO


How can I create a notification method when someone adds a user/login to have ‘sa’ access?

You would need to setup a job which looks at the list of admins and sends an alert if the list doesn't meet the pre-defined list of admins.  SQL Server Denali has this functionality built in.


MYSql - Ubuntu 20 - When importing DB, it gets duplicated in lowercase instead of writing to file

Edit server's configuration file, set lower_case_table_names parameter value in [mysqld] section to 2 (uncomment if commented, create if not exists), and restart server. See Identifier Case Sensitivity for details.
Remember, that this solution affects on all databases - but, as you show, this is the only user database on the server instance, so this method may be the best solution in your case.

Alternative solutions:
Open your WebApp3DB.sql file in some text editor and comment CREATE DATABASE and USE statements in it. Will fail if lower_case_table_names is set to zero, and database alias is added to tablenames specifications in backup queries.
Re-create the backup disabling "Insert database creation command" option.


First, repeat after me:

  A replica is not a backup

That is your first problem.  Do not use replication as a substitute for backups.  Replication is a useful part of a disaster recovery strategy.  It is not a useful disaster recovery strategy.

For PostgreSQL 9.0.x I would recommend that you take a look at barman, an open source backup and archive manager for PostgreSQL by 2ndQuadrant.  This helps manage some of the details so you don't have to.  (Full disclosure: I am a principal consultant for 2ndQuadrant.)

This being said, barman is only helpful if you are relatively familiar already with the base backup/wal shipping approach (which is what you want so you can roll back human errors like this).  This is necessary for PITR to work.

In this approach you set up two things.  The first is a base backup (see the pg_basebackup documentation for how).  Note you cannot use a logical backup (i.e. from pg_dump for this).  The second is to set up continuous archiving so that you can take your WAL segments and use them for PITR.   The specifics here depend highly on your environment and so you want to take a look at the docs and design something that works for you.  True to the UNIX way, these are largely tools for building your backup and recovery solution.  There isn't much more that can be said about this without something more specific, but start there and build a solution for your business out from it.


SQL Server Data Tools - Data Link Properties - Server Name Drop Down Empty

Just to close this off - you may see this problem depending on selected provider, if your provider set to "Native OLE DB\Microsoft OLE DB Driver for SQL Server" you may observe empty server drop down in Data Link Properties (exactly what we have on the screenshot in the question above), and just switching your provider to "Native OLE DB\SQL Server Native Client 11.0" resolves this problem. Actually this change changes UI slightly - you won't have "Data Links..." button next to OLE DB Provider which opens up Data Link properties as per screenshot in the question but it instead you will have Server Name drop down directly in Connection Manager window under Provider drop down as shown below:


You can only add logins to server roles, not database users. To remove a login, you use ALTER SERVER ROLE ... DROP MEMBER:
ALTER SERVER ROLE dbcreator DROP MEMBER login_name;

Please stop using the stored procedures; they're all deprecated. For example, from the sp_addsrvrolemember documentation:

This feature is in maintenance mode and may be removed in a future version of Microsoft SQL Server. Avoid using this feature in new development work, and plan to modify applications that currently use this feature. Use ALTER SERVER ROLE instead.


How the Oracle Multitenant Option Affects Privileges

  A common user connected to the root can see metadata pertaining to
  PDBs by way of the container data objects (for example, multitenant
  container database (CDB) views and V$ views) in the root, provided
  that the common user has been granted privileges required to access
  these views and his CONTAINER_DATA attribute has been set to allow
  seeing data about various PDBs.

SQL> create user C##ADMIN identified by adminpass container=all ;

User created.

SQL> GRANT CREATE SESSION, SYSOPER,PDB_DBA,DBA, CREATE PLUGGABLE DATABASE TO c##admin CONTAINER=ALL ;

Grant succeeded.

SQL> conn C##ADMIN/adminpass
Connected.
SQL> select PDB_NAME from cdb_pdbs;

no rows selected

Granting the required privilege:

SQL> conn / as sysdba
Connected.
SQL> alter user c##admin set container_data=all for cdb_pdbs container=current;

User altered.

SQL> conn C##ADMIN/adminpass
Connected.
SQL> select PDB_NAME from cdb_pdbs;

PDB_NAME
---------
PDB$SEED
PDB1


How to secure my data queried from my local MySQL server on a remote machine with root shared with untrusted entity?

Assuming you only need to run 'a C++ program' when you are logged in, maybe use a reverse SSH tunnel when you connect -R /tmp/mysql.remote.socket:/var/run/mysql/mysql.sock (as ssh argument). This would be easier to sort out that SSL and not require firewall changes as its piggybacking on your ssh connection in.

There is no absolute way to run a program on an untrusted machine with 0 risk of malicious intent. Mitigations include:

staticly compiling the executable
on the server sshfs mount a directory on your server with this executable on it (with -o reconnect,idmap=user), make this readonly and make it operate over your reverse ssh tunnel (add another -R remoteport:localserver:localport for the sshfs connection to the ssh server).
protect against gdb attacks using mprotect and madvise(DONT_DUMP) so information isn't stolen for some points during all points at which the program is run
there is some technology supported by hardware where a guest VM can run in a mode where the host cannot access the guest memory (needs further investigation)


Should I be using this to export the data using the SQL Developer 'Data Pump Export Wizard' connected as a DBA?

Data Pump stores definitions and data in dumpfiles with a binary format specific to Oracle. Data Pump can be used to generate DDL SQL statements (create user, table, view, trigger, etc.), but not for the data in tables.
If you want INSERT statements, then yes, use the Database Export feature of SQL Developer instead of Data Pump.


Shrinking the log file does not reduce size

Here is the answer to my own question.

Run the below query to get information about the log file's reuse wait:

SELECT log_reuse_wait_desc
FROM sys.databases
WHERE name = 'DBName'

I got the following output:

log_reuse_wait_desc
-------------------
REPLICATION

There were some replication-related objects remaining in the database, even after removing the replication.

To remove the replication from the database, sp_removedbreplication can be used. But it didn't work for us as replication was not active at the time and actually replication had been removed long before.

The solution was to import the database contents to another database using the import option of SQL Server.


How can I use COUNT in the same query with ROUND() and AVG()?

The current version gives you an error because you're trying to get one aggregate function (average) over another one (count) ; it's not related to round.  As far as I understand, you want to output id_theater, id_spectacle, count of tickets per theater and spectacle, and also average number of tickets per theater.  One way (most likely not the fastest from performance perspective) would be
with t1 as
(SELECT
    t.id_theater
    , s.id_spectacle
   , COUNT(tick.id_ticket) as num_tickets

FROM
    tickets tick
    , theater t
    , spectacles s
WHERE
    tick.id_spectacle = s.id_spectacle
    AND t.id_theater = s.id_theater
GROUP BY
    t.id_theater
    , s.id_spectacle
)
select  id_theater,id_spectacle , num_tickets, ROUND(avg(num_tickets) over (partition by id_theater),2) as avg_per_theater
from t1

Also, I'd suggest using ANSI-92 join instead of ANSI-89 syntax, it's way more readable


MySQL replication working, but data is out of date

due to:

MySQL :: MySQL 5.1 Reference Manual :: 16.1.3.3 Replication Slave Options and Variables

Warning

To specify multiple databases you must use multiple instances of this option. Because database names can contain commas, if you supply a comma separated list then the list will be treated as the name of a single database.

my.cnf's incorrect syntax:

--replicate-do-db=DB1,DB2,DB3,DB4

was replaced with correct syntax:

--replicate-do-db=DB1
--replicate-do-db=DB2
--replicate-do-db=DB3
--replicate-do-db=DB4


Database modelling best practice question

The first method is cleaner.  I would make a few suggestions, however.  You don't need the UserDocumentID surrogate Primary Key.  A composite key of UserID and DocumentID would suffice, while also provide a unique constraint for the junction table.  Also, is HasAccess really necessary?  I believe the existence of the record in the junction table is enough to indiciate that access is granted.

Do you have different levels of access?  Read-only, read/write, delete?

Hope this helps,

Matt


Is it normal for Snapshot Replication job to be constantly running?

This is correct state of Distributor Agent. After delivering from snapshot (which was created at scheduled time by Snapshot Agent) it triggered action that has finished as "Delivered Snapshot from the (...)". Delivery is performed by a Distributor, which is always on, and looking for new actions (Snapshots).
So in reality there are two jobs for Snapshot replication: Snapshot Agent triggered by schedule and Distributor Agent waiting (active) for any new actions. "No replicated transactions are available" means here, that there are no new snapshots to distribute.

By design Distributor Agent needs to run constantly and listen for new snapshots which are scheduled in Snapshot Agent.


Configuring PostgreSQL for write performance

1 Gigabyte a day is not that high of a write load.  Spread out throughout the day, that comes out to about 50kbytes a second. A slow USB thumb drive could handle that.  I'm assuming it's more bursty though.  As a_horse_with_no_name suggests, increase checkpoint segments.  100 or so is not out of the ordinary.

Then increase your checkpoint_timeout to 1 hour, as well as look at increasing your checkpoint_completion_target to something closer to 1.0 (100%).  The completion target tells PostgreSQL how aggressively to write in the background so that it's x% complete before running a checkpoint, which force all the data to be written out at once from the WAL and will slow the system to a crawl while it's happening.

The reason you don't usually set it to 100% is that it's pretty common to write to the same block more than once, and by delaying WAL writes out to the main store, you prevent the same block being written twice for no reason.

If it's unlikely you'll be writing to the same block more than once before your timeout occurs, i.e. all you do is insert then setting it pretty high makes sense to raise it to 0.9 or so.  The worst that'll happen is you'll write a little more often than you might otherwise need to, but checkpoints impact will be greatly reduced.


Can I convert date format for this value '19920000'?

The date type can't store partial dates, like only year, or only day and month.

In one project I had a need to store incomplete dates and I used three separate nullable columns for day (tinyint), month (tinyint) and year (smallint). I don't see in what cases you'd need to use varchar instead of integer types.

In my case it was information about customer birth dates and quite often salespeople didn't know the year, but knew month or day. With this approach it is very flexible and allows the user to enter any bit of information he has. It was also important to know that certain part of the date is missing, such as "we don't know the exact birth date, but we know that it is in June".

Obviously, there were a bunch of custom checks in the application (not at the database level) trying to prevent entering things like 31st of June. In that particular project it was OK to perform the checks in the application. Most likely it would have been possible to implement them in T-SQL as well. In general, it is better to have constraints implemented at the database level, because applications come and go, but data remains.

It was more difficult to use these incomplete dates in queries as well. Different queries/reports required different approaches to dealing with the missing bits.

30K rows is not much at all and performance should not be a problem. So, I'd focus on smart parsing and validation of your Excel data and storing all information that you can extract.


PostgreSQL UNIQUE constraint error message

For instance, you can read these separate fields mentioned in your quote inside exception handlers in PL/pgSQL (as instructed in the manual, like @dezso commented):

DO
$$
DECLARE
   err_constraint text;

BEGIN
   INSERT INTO recipes (title) VALUES ('foo'), ('foo');  -- provoke unique violation

EXCEPTION
   WHEN SQLSTATE '23000' THEN  -- Class 23 — Integrity Constraint Violation
      GET STACKED DIAGNOSTICS err_constraint = CONSTRAINT_NAME;
      -- do something with it, for instance:
      RAISE NOTICE '%', err_constraint;
      RAISE;  -- raise original error

END;
$$

Result:

NOTICE:  title_unique

ERROR:  duplicate key value violates unique constraint "title_unique"
DETAIL:  Key (title)=(foo) already exists.
CONTEXT:  SQL statement "INSERT INTO recipes (title) VALUES ('foo')"
PL/pgSQL function inline_code_block line 5 at SQL statement

Aside: you declare the unique constraints twice in your DDL command. (To my surprise, this results in a single constraint in my test on pg 9.5). Either way, do it only once.

CREATE TABLE recipes (
    id SERIAL,
    title TEXT NOT NULL,  -- remove redundant clause
    description TEXT NOT NULL,
    instructions TEXT NOT NULL,
    image BYTEA,
    CONSTRAINT recipes_pk PRIMARY KEY(id),
    CONSTRAINT title_unique UNIQUE(title)
);


MySQL running fine while data files (disk) are not present. Data folder nowhere to be found

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

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

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

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

My recommendation is:

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


Generate data delta between two databases PostgreSQL

The traditional mechanism if you can

Dump the tables at point-in-time into a CSV (\COPY with ORDER BY)
Modify the tables, or wait for modiications
Dump the tables again at a point-in-time (\COPY with ORDER BY)
Diff the CSVs
Thennn

If the rows are not present in the second versions, write a DELETE (or batch for a larger delete)
Otherwise write a INSERT ON CONFLICT .. DO UPDATE (or batch for a larger INSERT)

This handles INSERTS, UPDATES and DELETES and only requires you to check for the presence of the surrogate key. If you don't have a surrogate key, things obviously get a lot more complex. And, even more complex if you have no UNIQUE keys at all.

Alternatively, dump the table as a 50gB CSV as an .xz (or other compressed format) and just be done with it. Probably compresses to a few GB, in a few hours.


I am not a MySQL expert (or even a user!) but the ideal index for your query appears to be as shown below:

create table `single` (
  `game_id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `user_id` mediumint(9) NOT NULL,
  `stage` tinyint(4) NOT NULL,
  `highscore` bigint(20) unsigned NOT NULL,
  `played_date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`game_id`),
  KEY `user_id_stage` (`stage`,`user_id`, `highscore`)
)

The plan I get is:

See the SQLfiddle


Update table with sum from other table for the same id

this should do what you want
update dst
   set counter = dst.counter + src.counter
from src
where src.user_id = dst._user_id;

If dst.user_id is the primary key (or defined as unique), you can combine both into one statement:
insert into dst (user_id, counter, text)
select user_id, counter, text
from src
on conflict (user_id)
do update
  set counter = dst.counter + excluded.counter;


How to model a limited history of a table

Not sure I understand your initial design:

create table car
( id      bigint primary key
, ownerId bigint
, regNum  varchar(8)
, shopId  bigint
, price   numeric(10,2)
,    constraint foreign key(ownerId) references user (id)
,    constraint foreign key(shopId) references shop (id)
);

If for example, a car_owner is not mandatory, you may consider moving it to a separate entity:

create table car
( car_id bigint primary key -- vin is a standard identifier for a car
, -- other mandatory car attributes
);

create table car_owner
( car_id bigint not null
      references car (car_id)
, owner_id bigint not null
      references owner (owner_id) -- user?
,   primary key (car_id)
);

etc for other non-mandatory attributes

The history for the owner of a car:

create table car_owner_history
( car_id bigint not null
, owner_id bigint not null
, begin_time timestamp not null
, end_time timestamp
,     primary key (car_id, begin_time)
);

car_owner_history can be maintained via triggers. When a car is assigned it's first owner

insert into car_owner (car_id, owner_id)
values (c_1, o_1);

This results in the following change in history

insert into car_owner_history (car_id, owner_id, begin_time)
values (c_1, o_1, t_0);

When car changes owner from o_1 to o_2

update car_owner set owner_id = o_2
where car_id = c_1 and owner_id = o_1;

The following change is made in history:

update car_owner_history
    set end_time = t_1
where car_id = c_1
  and owner_id = o_1
  and end_time is null;

insert into car_owner_history (car_id, owner_id, begin_time)
values (c_1, o_2, t_1);

When a car no longer has an owner end_time is updated to reflect that.

Who owned car c1 at time t_x?

select owner_id
from car_owner_history
where car_id = c_1
  and t_x between begin_time and coalesce(end_time, t_x)

What owners have car c_1 had?

select owner_id
from car_owner_history
where car_id = c_1
order by begin_time;


Postgresql Deadlock - what does "while rechecking updated tuple" mean?

Even an answer to the following question would help: what's the difference between updating tuple and rechecking updated tuple?

I will take you up on this part.  When PostgreSQL finds a row that meets the WHERE clause to be updated, but that tuple has already been modified by a not-yet-committed transaction, it will wait for that transaction to commit and then recheck that the new version of the row still meets the WHERE clause.

So 31391 was wanting to update a tuple that 25788 had already updated (presumably in a prior statement in 25788's transaction), and was waiting for 25788 to finish so it could recheck that it still wanted to update it.

25788 was waiting or 31391 to finish, but we don't know why it was waiting for as it didn't log a CONTEXT line.

Can you tell if 25788 was implementing the 3rd step of thread #1, or is it implementing thread #2?

Does your thread #2 issue its 25788-like query each in its own transaction?


Table Structure: 2 Related Entities Share the Same Entity

Try it like this:
general table:
general_id/user_id/set_id/workout_id/Reps/Weight
In your select query you can then do something like this:
SELECT concat(u.name, ' , u.last_name) as user,
       s.set,
       w.workout,
       g.reps,
       g.weight
FROM general_tbl g JOIN user u ON u.id_user = g.id_user
                   JOIN set s ON s.id_set = g.id_set
                   JOIN workout w ON w.id_workout = g.id_workout


Simple yet multilingual flashcards - proper database design

This is a good use for a parent-child model.  This can be easily accessed with a join query.  Include the language(s) you want to work with in the where clause of the query.

This model below handles multiple translations between multiple languages.  If you want flashcards for one language as in your example above, remove the lang column from the flashcards table.

CREATE TABLE flashcards (
    entry_id     INT NOT NULL auto_increment,
    entry        VARCHAR(50) NOT NULL,
    lang         VARCHAR(2) NOT NULL,
    PRIMARY KEY (entry_id),
    UNIQUE KEY `flashcard_entry` (entry, lang)
);

CREATE TABLE flashcard_translations (
    entry_id     INT  NOT NULL,
    lang         VARCHAR(2) NOT NULL,
    translation  VARCHAR(50) NOT NULL,
    displayed    INT DEFAULT 0,
    known        INT DEFAULT 0L,
    lastKnown    DATE NULL,
    PRIMARY KEY (entry_id, lang),
    FOREIGN KEY (entry_id) REFERENCES flashcards (entry_id),
);

Depending on your requirements, you may not want the unique key.


mysql workbench *reverse engineer* is not connecting tables graphically. How to fix?

MySQL Workbench does not have the capability to "infer" the relationships from table column names, or MyISAM tables. It requires that the constraints be defined in the database or SQL script being reverse engineered.


How do I store phone numbers in PostgreSQL?

libphonenumber

When possible always use the canonical form. The more normalized the form the better. If there is a standard, use it. For this problem, let's use Google's libphonenumber, by proxy of pg-libphonenumber.

CREATE EXTENSION pg_libphonenumber;

This currently installs the phone_number type which has comparison operators and functions. It stores the number in an international canonical form. This is the best compromise in my opinion.

parse_phone_number('textnumber', 'CountryCode');

Because we can tell when phone numbers equal each other and we provide an internal normal form, we can do this..

SELECT parse_phone_number('03 7010 1234', 'AU') = parse_phone_number('(03) 7010 1234', 'AU');

(returns true). This also means that DISTINCT works so we can do this to get the effect you seem to want above.

CREATE TABLE foo
AS
  SELECT DISTINCT parse_phone_number(ph, 'AU')
  FROM ( VALUES
    ('0370101234'),
    ('03 7010 1234'),
    ('(03) 7010 1234')
  ) AS t(ph);
SELECT 1

That puts in..

 parse_phone_number
--------------------
 +61 3 7010 1234
(1 row)


Does PostgreSQL optimize queries in transaction?

There are a couple ways to do this without too much trouble but PostgreSQL will not leave rows intact when old = new.  So you will need to do it yourself.  The simplest way to do this would be to enter the new data set into a temp table, perhaps manipulate it in your transaction and then upsert the rows using a writable CTE (if on Pg 9.1 or higher).  Something like:

DELETE FROM temp_foo
 WHERE id IN (select foo.id from foo join temp_foo t on foo.id = t.id
               where foo = temp_foo);

WITH upsert as (
     UPDATE foo SET bar = t.bar,
                    baz = t.baz
       FROM temp_foo t WHERE foo.id = t.id
     RETURNING foo.*
 )
 INSERT INTO foo (id, bar, baz)
 SELECT id, bar, baz FROM temp_foo WHERE id NOT IN (select * from upsert);


Is there a way to DENY CRUD operations on connections from linked server?

Have you had a look at setting up a Server wide DDL trigger? I have used this successfully on several production servers to block unauthorized changes made by developers. I joined a company where ALL developers had full SA access.

When we tried to change their rights we had a full blown revolution as the developers all of a sudden 'could not do their work'. So I used a server wide DDL trigger to limit what they could do. It was very simple.

I created a table called AuthorizedDDLUser which had a very simple structure something like this:

 CREATE TABLE [dbo].[AuthorizedDDLUser](
    [Username] [nvarchar](256) NOT NULL,
    CONSTRAINT [PK_AuthorizedDDLUser] PRIMARY KEY CLUSTERED
    (
      [Username] ASC
     )
    WITH (
           PAD_INDEX  = OFF
           , STATISTICS_NORECOMPUTE  = OFF
           , IGNORE_DUP_KEY = OFF
           , ALLOW_ROW_LOCKS  = ON
           , ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
    ) ON [PRIMARY]

  GO

I then inserted the Login names of the developers which I would allow to make changes(i.e. the DBA team and Senior SQL developers).

I then created a server wide DDL trigger similar to this:

CREATE TRIGGER [block_ddl]
ON SERVER
FOR CREATE_PROCEDURE,ALTER_PROCEDURE, DROP_PROCEDURE,
    CREATE_TABLE,ALTER_TABLE, DROP_TABLE,
    CREATE_FUNCTION,ALTER_FUNCTION, DROP_FUNCTION,
    CREATE_INDEX,ALTER_INDEX, DROP_INDEX,
    CREATE_VIEW,ALTER_VIEW, DROP_VIEW,
    CREATE_TRIGGER,ALTER_TRIGGER, DROP_TRIGGER
AS
    IF NOT EXISTS(SELECT UserName FROM AuthorizedDDLUser WHERE UserName=CURRENT_USER)
    BEGIN
            //RAISE CUSTOM ERROR MESSAGE HERE AND ROLLBACK THE TRANSACTION
            // ROLLING BACK EFFECTIVELY CANCELS THE DDL STATEMENT
            ROLLBACK TRANSACTION --
    END

My implementation was a little bit more involved as it also did auditing. However I am trying to keep it simple here.

So in a nutshell you can selectively limit what the linked user can do on the system. To really get started with this I would suggest your read the following articles.

Using a DDL Trigger to Block Schema Changes in SQL Server: http://www.mssqltips.com/sqlservertip/2646/using-a-ddl-trigger-to-block-schema-changes-in-sql-server/
Auditing DDL (Create, Alter, Drop) Commands in SQL Server 2005: http://www.mssqltips.com/sqlservertip/1006/auditing-ddl-create-alter-drop-commands-in-sql-server-2005/
DDL Event List: http://msdn.microsoft.com/en-us/ms189540.aspx
DDL Triggers: http://msdn.microsoft.com/en-us/library/ms175941.aspx
Using the EventData() Function with DDL triggers in SQL Server 2005: http://www.mssqltips.com/sqlservertip/1571/using-the-eventdata-function-with-ddl-triggers-in-sql-server-2005/

So in my case the developers could still do pretty much anything except mess up my system. Eventually we got them weaned off SA access and my life became normal again thanks to DDL triggers.

Hope that helps!

UPDATE:

Thanks to Martin Smith for pointing out that these are after triggers. Something I did not realise until now (you learn everyday). The MSDN article is very confusing in that it indicates it is both a before and after trigger. So yes this could open some potential for performance issues if like Martin said you rollback a index build on a rather large table. However if you are just going to block view and tables from being created then it should be ok.

So use wisely.

They really need to fix this as it limits the usability of this feature.


Finding "words" separated by commas in a single column with the CONCAT_WS() function

WHERE FIND_IN_SET("LK123", CONCAT_WS(",", ...))

It does require the use of "comma" as the separator, and you cannot have commas in the strings.

https://dev.mysql.com/doc/refman/5.7/en/string-functions.html#function_find-in-set

Or... FULLTEXT

ADD FULLTEXT(column1, column2)

WHERE MATCH(column1, column2) AGAINST("+KL123 +OTHER123" IN BOOLEAN MODE)

Yes, your app will need to construct this syntax.


How to use windowing functions to find gaps in sequences grouped by a column, in PostgreSQL

You should use a CTE or a subquery to get the lead order_no first.

select client_id,
       order_no + 1 as c1,
       no - 1 c2
from   (select client_id,
               order_no,
               lead(order_no)
                   over (partition by client_id order by client_id, order_no) as no
from     tbl) ct
where  no is not null
and    no - order_no > 1;

with ct as
(
select   client_id,
         order_no,
         lead(order_no)
             over (partition by client_id order by client_id, order_no) as no
from     tbl
)
select client_id,
       order_no + 1 as c1,
       no - 1 c2
from   ct
where  no is not null
and    no - order_no > 1;

client_id | c1 | c2
--------: | -: | -:
       43 |  2 |  4

db<>fiddle here


Is it good practice to create tables dynamically?

There isn't any definitive answer for this problem.

You've correctly identified some of the possible tradeoffs (single table=simpler/possibly slower/more contention?, multiple tables=more complicated/faster, maybe?/harder to use in other contexts?).

The general advice is to start with the more straight-forward single-table solution, then break them out if testing shows problems.

Most of the time you're going to get good performance on a single table as long as you have decent hardware and proper indexing design.

Depending on the specific platform, you might have other technical solutions like filtered indexed views and stuff like that.


The answer was to create a user who would the super administrator of the database. So that we can distinguish between Windows authentication and Sql Authentication, in case it doesn't work !!!
Thank you !


Loop through key/value pairs of a jsonb object in postgresql function

Use jsonb_each(jsonb) or jsonb_each_text(jsonb) in a FOR loop like:

CREATE OR REPLACE FUNCTION my_function(input jsonb)
  RETURNS jsonb
  LANGUAGE plpgsql AS  -- language declaration required
$func$
DECLARE
   _key   text;
   _value text;
BEGIN
    FOR _key, _value IN
       SELECT * FROM jsonb_each_text($1)
    LOOP
       -- do some math operation on its corresponding value
       RAISE NOTICE '%: %', _key, _value;
    END LOOP;

    RETURN input;
END
$func$;

Call:

SELECT my_function('{"a":1, "b":2, "c":3}');

Related:

Declare variable of table type in PL/pgSQL

Note that for many problems where people used to programming languages like c, java, etc. would tend to use a loop, there is a superior set-based solution in SQL around the corner. Side-by-side code examples:

Postgres FOR LOOP
Syntax error in a Postgres statement with procedural code


How to create index on long data type field

For long (mediumtext) you have to specify how much of the string you want to index. If you want the first 100 bytes in the column to be indexed:

CREATE INDEX ix_logs_create_date ON logs (create_date(100)) USING BTREE;

May I ask why you named a column of type long create_date?


How can I switch to sa account from guest session in SQL server 2012?

When signed in under the guest account, you have very few privileges by default. The sa password is displayed as blank because you do not have the necessary rights to view it. Disabling the guest account is a security best practice, where it is not required.

The real sa password is almost certainly not blank. SQL Server does not allow a blank sa password since the 2005 release.

SQL Server does provide a way to impersonate another login or user (assuming you have sufficient rights). See EXECUTE AS (Transact-SQL) for details.

Note that SETUSER is deprecated, provided only for backward compatibility, and has inferior functionality.


Option 1 is a no; this level of un-normalized data will mean many extra data pages occupying more space on disk, more data pages having to be read/written on operations, ...

Between Option 2 and Option 3 I'd go with something closer to Option 3 - In here you will save many bytes related to users (an Int goes for 4 bytes instead of the 30/50 of the Chars). This will save you again considerable disk space (considering users appear more than once) and get you smaller indexes which will mean better performance.

Now, on Option 3, I might be missing something for I don't understand what entityName and entityId stand for. If this is user information, it will better stay on the virtual_user table so you won't get this info repeated in every meta. Also, on the meta table I'd keep the orderId, it will allow you proper and fast joining of the meta and order tables, providing adequate indexing is present. I don't know the specifics of your requirements but consider if the updated column is necessary on the Order table or if for displaying the desired info on the application you'll be joining with the latest registry from the meta table all of the time.

If you want to keep track of order history, depending on requirements you might want to consider having a separate table (orderHistory or 'orderChanges'), or partitioning the order table. Now, especially for this last case, the order table could have a composite key composed by (orderId, metaId), making it Unique and a candidate index for joining with the meta table.

-- After the explaining comment:
The meta table will be the most heavily used one, so keep everything as short as possible on this one:

Clustered index on metaId
Add a table entityTypes which relates entityName CHAR to entityType int. This will also get you a nice list of entities for anyone new looking at the database to know which entities relate to meta
Try having entityId as int for all entities (If you can't change/add column to the existing tables, add an intermediary table) - This will reduce the field to 4bytes and allow one single data type for all entities.
Add an index to meta table on entityType (now int) and entityId (now int). Your rows on this heavily used table are now so short you should be doing everything lightning fast.


mySQL running on 100% CPU both on RDS and EC2

Your question is about troubleshooting, not about 'fixing' the CPU issue.

Nearly all CPU issues can be traced to bad/missing indexes or poorly written queries.

Do you have the slowlog turned on?  With long_query_time = 2?  Can you run pt-query-digest?  Well, probably you said no to all of those.  Pursue them for future use.

Can you do SHOW FULL PROCESSLIST?  Probably.  Look at the queries that don't say "Sleep" and don't look like they have something to do with replication.  They will probably be the naughty queries.

Show us the naughtiest query, together with SHOW CREATE TABLE for the table(s) that it uses.  Also provide EXPLAIN SELECT ... so we can see how the query is being performed.

If you are lucky, we will say "add INDEX(...)" or "change the WHERE clause in this way...".  If you are less lucky, the solution could be more complex.

(None of the specs you mentioned -- ram, cores, #tables, table size, etc., are important to the question.  The version might be important because the solution might involve a 'new' feature.)

Since you have provided the SELECT, I can make some guesses and proceed without the rest of the data.

Keeping junk in the table and filtering them out hurts performance (='N', ='Y'), but I will live with that for the moment.

INDEX(rep_type, rep_status, pub_id, rep_pub_date) might make things run a lot faster.  But...  The speedup will only work (I think) when the IN clause has only one item.

OFFSET 1200 means that 1200 rows have to be fetched, then tossed.  Let's work on decreasing that overhead...

(Note that JOIN...ON is a better way to write JOINs.)

Oops, I need the PRIMARY KEY of reports; I'll assume it is rep_id.

SELECT  r2.* , d.name AS dom_name, p.pub_name
    FROM
      ( SELECT  rep_id
            FROM  reports
            WHERE  rep_type = 'N'
              AND  pub_id IN ( 3 )
              AND  rep_status = 'Y'
            ORDER BY  rep_pub_date DESC
            LIMIT  1200 , 100
      ) r1
    JOIN  reports AS r2  USING(rep_id)
    JOIN  domains AS d   ON d.dom_id = r2.domain_id
    JOIN  publisher AS p ON p.pub_id = r2.pub_id;

Some notes:
* The subquery looks only at reports, and does not have to haul around 1200 rows to be thrown away.
* Once the subquery is finished, the JOINs are doing only 100 rows each.
* This format will benefit from the above index.
* When the IN has multiple items, the index will work even better for the subquery than the original query.  But still not 'perfectly'.
* My index works well only because it handles all of the WHERE clause, plus all of the ORDER BY.

To get even better performance for "pagination" than you can get through OFFSET, see my blog: mysql.rjweb.org.  That blog also points out two bugs you possibly don't know you currently have.

Another issue... Do users really go as far as the 13th page?  Or is this a search engine crawler causing you grief?


Connection error while connecting to Oracle XE

The ORA-609 error is thrown when a client connection of any kind failed to complete or aborted the connection process before the server process was completely spawned. Beginning with 10gR2, a default value for inbound connect timeout has been set at 60 seconds.  This is also triggered when a DB session is killed/aborted manually from the OS prompt.

  To remove a primary ORA-609 error, you should increase the values for INBOUND_CONNECT_TIMEOUT at both listener and server side sqlnet.ora file as a preventive measure. If the problem is due to connection timeouts, and increase in the following parameters should eliminate or reduce the occurrence of the ORA-609 error.

  Sqlnet.ora: SQLNET.INBOUND_CONNECT_TIMEOUT=180
  Listener.ora: INBOUND_CONNECT_TIMEOUT_listener_name=120

Reference
See: ORA-609 opiodr aborting process unknown ospid tips (Burleson Consulting)


The first one failed because the -E flag is for Trusted Connections (Windows Auth) and -U/-P flags are for SQL Authentication. These are mutually exclusive and cannot be combined.

The third one failed because -Q must be followed immediately by the query you wish to execute. In your provided code, you've included other flags between -Q and the actual query.

The second one should work syntactically, so you need to investigate the cause of the

  The target principal name is incorrect

error. Take a look at some of the answers to this question and also validate that you can connect via SSMS, or you get the same error in SSMS.


Best way to merge tightly coupled SQL Server Instances

I faced an analogous problem where we had different environments using their own linked servers so we could never guarantee that dev was like integration was like test was like production.

The "doh" moment was when we worked out that you can name a linked server whatever you want irrespective of its physical name.

I've not got access to a SQL Server instance to test whether you can create a linked server to itself but if possible then entire redirection would be self contained within your consolidated instance until such time as you were able to weed out the procs that rely on linked servers.


You can achieve something like this using a federated view. After configuring the federated data source as described in the manual, you would create a nickname for the archive table

CREATE NICKNAME Archive_Prod FOR archive_server.archive_schema.Archive_Prod;

After that you would create a view joining the two tables:

CREATE VIEW v_active_prod AS
  SELECT local.aliasapptype, local.aliasappreason, local.pzinskey, remote.pzpvstream
  FROM Active_Prod local
  INNER JOIN Archive_Prod remote
  ON (local.aliasapptype, local.aliasappreason, local.pzinskey)
   = (remote.aliasapptype, remote.aliasappreason, remote.pzinskey)

assuming aliasapptype, aliasappreason, pzinskey is your primary key.

If you want to fetch your BLOBs regardless of where they reside, you may want to choose an outer join instead:

CREATE VIEW v_active_prod AS
  SELECT local.aliasapptype, local.aliasappreason, local.pzinskey,
         COALESCE(local.pzpvstream, remote.pzpvstream)
  FROM Active_Prod local
  LEFT JOIN Archive_Prod remote
  ON (local.aliasapptype, local.aliasappreason, local.pzinskey)
   = (remote.aliasapptype, remote.aliasappreason, remote.pzinskey)

Don't expect performance to be stellar though when reading BLOBs from a federated data source.


It looks to me like you are trying to 'pivot' the data, which can be done with multiple case statements:

test data:

create table timestream(
  idTimestream integer primary key autoincrement
, time int not null
, value float not null
, idSensor integer not null);

insert into timestream(time, value, idSensor) values(1,0.1,100);

insert into timestream(time, value, idSensor) values(1,0.2,101);

insert into timestream(time, value, idSensor) values(1,0.3,102);

insert into timestream(time, value, idSensor) values(2,0.4,101);

query:

select time, sum(case idSensor when 100 then value end) as s100,
             sum(case idSensor when 101 then value end) as s101,
             sum(case idSensor when 102 then value end) as s102
from timestream
group by time;

time | s100 | s101 | s102
:--- | :--- | :--- | :---
1    | 0.1  | 0.2  | 0.3
2    | null | 0.4  | null

dbfiddle here


how to drop oracle 12c instance on linux

$ sqlplus / as sysdba
SQL> startup force mount exclusive restrict
SQL> drop database;


The cost of left out join versus a select

If you already have the PK value of a certain row in A, a simple SELECT is the fastest possible way:

SELECT * FROM b WHERE a_id = ?

If you need to retrieve a_id first, a join is your best options. Make that a LEFT [OUTER] JOIN only if there can be cases where there are no matching rows in B and you still want a_id in that case (or whatever, you didn't clarify).

SELECT a.a_id, b.*  -- often you don't need all columns
FROM   a
LEFT   JOIN b USING (a_id)
WHERE  a.some_column = ?;  -- not defined

One integrated query is typically faster than two separate queries. LEFT [OUTER] JOIN or [INNER] JOIN perform about the same here.


SQL Server : Index Seek with very high Scan Count

I believe this has already been answered and would be helpful for you if you can refer to below discussion:Also, what action where clause is performing needs to be looked into:

Why so many logical reads?

Since Logical Reads are from the data cache (memory) I would think the fact that there is a lot of reads will make little difference and it would seem that the second query is more efficient when reading a lot of data in small chunks while the first query reads the data in large chunks.

Overall, the query performance significantly can be improved using below several aspects as well:

Only the correct subset of rows from the * table to be retrieved, potentially reducing physical IO and locking contention
The CPU intensive hash functions to be eliminated
The memory footprint for both data and hash buckets to be reduced.

Index Seek will be used only if conditions from WHERE or ON clauses are the first in the list of columns of the Index. Also, this fields must be selective which means that the condition must filter just a little percent or rows from a table.

Also, you don't need to include column that is part of the clustered index into list of columns of a nonclustered index, because Nonclustered indexes always contain the clustered index columns if a clustered index is defined on the table.

Actually, this is very complex subject. To know more about using indexes in Sql Server you can read this article https://technet.microsoft.com/en-us/library/jj835095.aspx


How does a SQL Server instance alias work?

1) You don't need to define the alias on the SQL Server itself, unless you need a loopback for some reason.

2) The client doing the connecting does need the alias defined on it.  Make sure you define it in both the 32bit and 64bit version of cliconfg to make sure you don't miss anything there.

C:\Windows\system32\cliconfg.exe
c:\WINDOWS\SysWOW64\cliconfg.exe

3) The Alias defined in cliconfg on the client is different from DNS in that you can specify a port instead of just a redirect.  You could combine the two for maximum flexibility.  So you could have cliconfg on the client using a DNS record to point to the correct host, but cliconfg specify a port number.  Think of it as a etc\hosts file that only database drivers talk to.  Effectively:

Application - Connection string to My_SQL_Alias
CLICONFG - My_SQL_Alias == MySQLHost:1564
DNS - MySQLHost == MyRealSQLHostName
FinalConnection == MyRealSQLHostName:1564

4) If part of a domain, you should be able to specify these things through group policy.

WHY USE IT??

I've not found many use cases in my career for setting these up.  Really, if you need to hide the fact that you are connecting to an instance rather than the default instance then that's the only thing that really springs to mind.  But the challenges with spreading this information out prohibits a lot of these use cases.


Can I loop through a pivoted row in Sql Server?


Most Efficient / Easiest Way to Capture Multiple Entries in Same Field

There are multiple ways of doing this, for example a short and easy version:

drop table bad_design purge;
create table bad_design (c1 varchar2(200 char));
insert into bad_design values ('a@a.com; b@b.com; c@c.com; d@d.com; e@e.com; f@f.com; g@g.com');
insert into bad_design values ('z@z.com; y@y.com; x@x.com; w@w.com; v@v.com; u@u.com; t@t.com');
insert into bad_design values ('o@o.com');
commit;

Then:

select
  trim(column_value) c1
from
  bad_design,
  xmltable(('"' || REPLACE(c1, ';', '","') || '"'))
;

a@a.com
b@b.com
c@c.com
d@d.com
e@e.com
f@f.com
g@g.com
z@z.com
y@y.com
x@x.com
w@w.com
v@v.com
u@u.com
t@t.com
o@o.com

Which you can simply insert into a new table if needed. Other methods:

https://stackoverflow.com/questions/14328621/splitting-string-into-multiple-rows-in-oracle


Updating Properties of an Excel cell, with Sql statement

That sounds mighty painful.

Is there any opportunity to use Conditional Formatting (Home, Conditional Formatting, New Rule) in your template Excel document?

Then apply that rule to the whole worksheet/workbook so that when the row with Total moves from row 20 to 25, it correctly enboldens the proper cells?


ORA-00942: table or view does not exist while creating synonym

In the SQL Language Reference you will find under Prerequesites (emphasized by me):

  The owner of the schema containing the view must have the privileges necessary to either select (READ or SELECT privilege), insert, update, or delete rows from all the tables or views on which the view is based. The owner must be granted these privileges directly, rather than through a role.

So you have to grant the privilges directly to the user that creates the view.

The same is true for PL/SQL procedures and is already discussed in this post.

There are two ways to grant a select privilege directly:

grant it by an object privilege: grant select on A_TABLE to A_USER;
grant it by a system privilege: grant select any table to A_USER;

The latter is not recommended, granted such an ANY system privileges to non DBA users is considered as a security risk.

Such ANY privileges exists for a lot of procedures and actions, e.g. INSERT ANY TABLE, EXECUTE ANY PROCEDURE,...


MDW performance_counter_instances table running out of identity values

When you exhaust the upper bound of INT you will receive, for every new insert:

Msg 8115, Level 16, State 1, Line 1
Arithmetic overflow error converting IDENTITY to data type int.
Arithmetic overflow occurred.

Outside of the MDW use case

Converting to BIGINT is far safer (IMHO) than what some people do - go back to 0 and fill in the gaps, or go to -2 billion and just delay dealing with it until you use the same number of values one more time. If 2 billion rows isn't enough, neither is 4 billion. If you use compression changing the data type for values < 2 billion will actually save you some space.

However you will come across issues if this is part of a primary key constraint, referenced by foreign keys, has other constraints, etc. You'll need to perform some extra work in addition to just changing the data type.

For MDW specifically

The problem with the solution above, for MDW specifically, is that the data collector package (SSIS Packages/Data Collector/PerfCountersUpload) includes some logic that relies on the underlying data type to be INT. So, the collector jobs fail because of this mismatch.

This should buy you some time:

DBCC CHECKIDENT('snapshots.performance_counter_instances', RESEED, -2147483648);

And between now and when you start approaching 0 again (you should set up some kind of monitoring to check the max value and send some kind of alert when you get close), go in and clean up and make sure there are no positive values.

Then, when you start approaching 2 billion again, clean out all the negative values, and reseed.

Is this annoying? Absolutely. I think this might be one of several reasons why you don't see a very large MDW adoption.


how to update new parent node in the middle of the postgresql ltree by some simple update SQL statement?

I just try another stupid solution:

update product_sections
  set section_path = text2ltree('Automation_Solutionz.Test_Case_Execution.treetype') ||
                     subltree(section_path,1,nlevel(section_path)-1)
where section_path ~ 'Automation_Solutionz.Test_Case_Execution.*'::lquery


Enterprise Manager Cloud Control 13c - Email notification should send only once

What you described is the default setting (for repeated notifications). I guess you have a customized environment and someone configured repeated notifications on purpose.
Repeated notifications can be configured globally in Setup / Notifications / Global Repeat settings, or specifically for metric alerts in Setup / Incidents / Incident Rules / "your rule set" / Rules / Create incident for critical metric alerts / Actions / Repeat Notifications.


log send queue size and redo queue size in AG

Both the log send queue and redo queue can be found in sys.sys.dm_hadr_database_replica_states (docs), and also available using the SQLServer:Database Replica Perfmon counters (docs).
Perfmon
With Perfmon counters, you'll need to collect counters individually on each replica. Each replica knows about the Send/Redo(recovery) queue affecting that replica itself.
It sounds like you already found the Log Send Queue perfmon counter, which is defined as:

Amount of log records in the log files of the primary database, in kilobytes, that haven't been sent to the secondary replica. This value is sent to the secondary replica from the primary replica. Queue size doesn't include FILESTREAM files that are sent to a secondary.

The perfmon counter for the Redo Queue is actually the Recovery Queue counter, which is defined as:

Amount of log records in the log files of the secondary replica that have not been redone.

You can also access the Perfmon counters using T-SQL by querying the sys.dm_os_performance_counters DMV.
SELECT *
FROM sys.dm_os_performance_counters
WHERE object_name = 'SQLServer:Database Replica'
AND counter_name IN ('Log Send Queue','Recovery Queue')
ORDER BY cntr_value DESC;

T-SQL / DMVs
From T-SQL, you can query the DMVs to get this info. The below query is from my standard toolbox, and includes both the Send & Redo queue sizes & rates (in the first few columns), plus some other information on the replica states & send/redo status. Note that running this on the Primary Replica will report status information for all replicas, running it on the secondary replica will only report local information for that secondary replica.
The advantage of monitoring via T-SQL is that you can have a single/central collection point to check on the status of all replicas at once.
-- ▶ Run this on **PRIMARY**
SET NOCOUNT ON;
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

SELECT
    --AG info
    AGName                          = ag.name,
    ReplicaServer                   = ar.replica_server_name,
    DbName                          = adc.database_name,
    -- Queue Size & rates
    SendQueueSize                   = drs.log_send_queue_size,
    RedoQueueSize                   = drs.redo_queue_size,
    SendRateKb                      = drs.log_send_rate,
    RedoRateKb                      = drs.redo_rate,
    -- State Info
    IsLocal                         = drs.is_local,
    IsPrimary                       = drs.is_primary_replica,
    SynchronizationState            = drs.synchronization_state_desc,
    SynchronizationHealthState      = drs.synchronization_health_desc,
    -- Last ____ Time
    LastSentTime                    = drs.last_sent_time,
    LastRedoTime                    = drs.last_redone_time,
    LastReceivedTime                = drs.last_received_time,
    LastHardenedTime                = drs.last_hardened_time,
    LastCommitTime                  = drs.last_commit_time,
    -- LSN info
    RecoveryLSN                     = drs.recovery_lsn,
    TruncationLSN                   = drs.truncation_lsn,
    LastSentLSN                     = drs.last_sent_lsn,
    LastReceivedLSN                 = drs.last_received_lsn,
    LastHardenedLSN                 = drs.last_hardened_lsn,
    LastRedoneLSN                   = drs.last_redone_lsn,
    EndOfLogLSN                     = drs.end_of_log_lsn,
    LastCommitLSN                   = drs.last_commit_lsn,
    --Oh yeah, filestream, too
    FileStreamSendRate              = drs.filestream_send_rate
FROM sys.dm_hadr_database_replica_states AS drs
JOIN sys.availability_databases_cluster AS adc
    ON drs.group_id = adc.group_id AND drs.group_database_id = adc.group_database_id
JOIN sys.availability_groups AS ag
    ON ag.group_id = drs.group_id
JOIN sys.availability_replicas AS ar
    ON drs.group_id = ar.group_id AND drs.replica_id = ar.replica_id
ORDER BY
    -- ag.name, ar.replica_server_name, adc.database_name;
    drs.log_send_queue_size + drs.redo_queue_size DESC;

Alerting
If you don't have monitoring product capable of watching the Perfmon Counters or DMVs directly, you can hack your way into alerting using the SQL Server Agent Alerting system.
In Object Explorer --> SQL Server Agent --> Alerts, select to create a new alert

On the General tab of the New Alert window, select the Perfmon counter you want to monitor, as well as the alert threshold.

On the Response tab, you can select to either send an email to notify SQL Agent Operators or execute a SQL Agent Job. You can have the SQL Agent job response run T-SQL or PowerShell that executes just about anything (like logging to the Windows Event Log).


How can I conditionally summarize multiple rows in Microsoft Access?

I'd also like to see the ERD (Entity Relationship Diagram) to get a better understanding of your problem.

I'm thinking if you're creating a report to present the data in this fashion, you can setup a grouping based on an expression, either using built-in functions or use a user-defined function in a VBA module.


Estimated rows very wrong when using declared variables

...SSMS is not running this using sp_execute so I don't think this is being caused by parameter sniffing. What are the possible causes for this behavior?

The optimizer cannot 'sniff' the values of local variables, so the cardinality estimate is based on a guess. If you are using the original cardinality estimator, the fixed guess for BETWEEN is 9% of the table's cardinality.

If using the new cardinality estimator, the guess is computed using exponential backoff, applied to the separate >= and <= comparisons that BETWEEN is shorthand for. The guess for >= and <= is 30% (0.3) each, so the overall guess is 0.3 * SQRT(0.3) = 0.164317 (* table cardinality).

You have to decide between caching and reusing a reasonable plan for all future variable values, or recompiling on each execution to generate a new plan for the specific values each time. The second option makes use of the Parameter Embedding Optimization, which is only applied if you use the query hint OPTION (RECOMPILE). There is an obvious trade-off to be made here, between the cost of recompiling each time (the resulting plan is never cached for reuse) and using a not-quite-optimal reused plan for each execution.

There are additional options for you to consider, including specifying typical values in an OPTIMIZE FOR hint; see my SQLPerformance.com article for details.


SQL Server engine in one place with database files on NAS

To be honest, I wouldn't want my database on a NAS as it is most likely accessed throughout the office and it is also very vulnerable as everyone connects to it. Ideally, your SQL database should be hidden in the deepest parts of your network.

However, I seeing how this doesn't sound like a high end operation, I would see if they can give you the money to get a desktop and install SQL express there to run the database for them. That way all of your clients can just connect to that server from within the network and you have a central point to pull backups from.


How much storage space do indexes and keys take for an SQL Table?

One aspect which I don't think was addressed so far is if your source table is a heap table. SQL server is very bad at re-using space for heaps, and this is one (not the only) reason for not using heaps in the first place. You make the table a non-heap table by having a clustered index on the table.

I.e., this is most likely not at all about nulls, but rather about heaps (or possibly could also be non-deallocated LOB storage).


It sounds like you are trying to add a database to your AG from a node other than the primary (is instance3 even part of your cluster), you can't do that.  You would have to fail over to instance3 and then add it.  You could also take a backup of your database, restore it to the primary, then go through the steps to add it again (you'll have to do a full backup and log backup from the primary).

Your biggest problem is going to be the name.  You are correct, you cannot have two databases with the same name.  It will have to change or you'll have to move it to a different cluster.


Scripting execute permissions on stored procedure

Take a user created Stored Procedure, MySP and as part of the SQL Script to create the SP add a SQL Statement to Grant execute permissions on the SP.

CREATE Procedure MySP
AS
BEGIN
  SELECT 'HELLO';
END
GO
GRANT EXECUTE ON MySP To SomeUser;
GO

When the SQL is run the SP is created and stored in SQL Server. Once the SP is created the Grant execute statement is executed.

When the SP is executed only the contents of the SP are run.

The Grant execute statement is NOT stored with the SP in SQL Sever. This can be verified in a number of ways

By opening SQL Enterprise Manager, locating the database in which the SP resides and viewing the SP under Programmability.
Use sp_helptext 'MySP' which will return SQL Servers stored version of the SP


If you're using the WiredTiger storage engine, data in the database is compressed (using snappy by default). In contrast, mongodump output is not compressed. This is why 2TB may not be enough to backup a 1.5TB database.

To determine the uncompressed size of your database, you can run db.stats().dataSize in the mongo shell (see manual).

To determine the uncompressed collection size, use db.collection.stats().size.

Having said that, there is no built-in way to "resume" a dump. However, you can pass a query into mongodump using --query parameter. If you have some kind of timestamp/ordering field in your collection, you may be able to use this parameter. This could also help you to split up your backup so you don't have to do all 1.5TB at once.

Alternatively, you also can pass --gzip into mongodump as suggested by JJussi to ensure the output is compressed.


How to turn VARBINARY within PeopleSoft into a string?

Ok, I found out how to translate the data.  It is a compressed string stored as VarBinary.  I needed to use the System.IO.Compression.DeflateStream classDeflate
It would be nice to do this all in SQL, but this at least removes the coding roadblock.

... db connection code...
    var query = @"SELECT TOP 10  * FROM PSAPMSGPUBDATA PH WHERE 1=1   AND IBTRANSACTIONID LIKE 'd8d8%'";

    var cmd = new System.Data.SqlClient.SqlCommand(query,conn);

    var dr = cmd.ExecuteReader();

    while (dr.Read())
    {
        dr[5].Dump();
        var stream = dr.GetStream(7);
        var foo = new byte[9000];
        stream.ReadByte();
        stream.ReadByte();
        var output = new System.IO.Compression.DeflateStream(stream, System.IO.Compression.CompressionMode.Decompress);
        var outStream = new MemoryStream();
        var len = (int) stream.Length;
        output.Read(foo,0,9000);
        output.Close();
        var message = System.Text.Encoding.Default.GetString(foo);
        message.Length.Dump();
        message.Dump();
    }


Improving SQL Server performance on VMware under Linux

I had a similar problem. Virtual disks kill performance. Try to get as much data into memory as possible. I eventually put the disk intensive VM's onto its own RAID stack.

Most people in OIT do not recommend putting disk intensive applications into a VM because of this reason. You also didn't specify which VMWare product you are using. ESXi was released for free not to long ago, if you are not using that you may want to try it. It may help you get that last bit of performance you need.


sp_execute expects parameter '@handle' of type 'int'

I think you meant to use sp_executesql:

EXEC sp_Executesql @SQL, @Parameters, @SourceTableIN = @SourceTable, @TableNotExistsOut = @TableNotExists OUTPUT

And as JonSeigel pointed out in the comment, you have mis-spelled a parameter in your statement:

SET @SQL = N'USE [' + @DestDB + '] IF NOT EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = ''BASE TABLE'' AND TABLE_NAME = @SourceTableIN)
BEGIN SET @TableNotExistOUT = 1 END'

That should be @TableNotExistsOUT.


I've created a view for a report, it now takes 30 minutes to run - Any ideas?

Unable to get to the root cause of the performance issue I've backed out the stored procedure changes. These changes introduced mostly left joins on views. Not sure whether the 1.4m row table scan was having an impact on performance.

With the code changes reverted reports are now running every 3 - 4 minutes. This includes the time to write the file to disk. This code will likely not see the light of day again.


Increasing column from VARCHAR(255) in master/slave setup

In a replication environment, for an ALTER that takes a long time, I recommend this:

On one slave:  Take out of rotation (keep clients from using it); perform the `ALTER; put back into rotation.
Repeat for other slaves.
SET @@session.sql_bin_log = 0 so it won't replicate; do the ALTER; set that back to 1.

That way, Slave users will not be impacted; Master user may be impacted.

With Galera / PXC, you would use "RSU" (Rolling Slave Update) and have zero downtime (but have to do it manually in a similar manner).

You need to upgrade to 5.6.  With it many ALTERs (probably including your case) can use the fast ALGORITHM=INPLACE.


Looking for a snippet illustrating ambiguous result of a join in a where clause

This can only be demonstrated on databases set to 80 compatibility level (or SQL Server <= 2000).

CREATE TABLE dbo.splunge(splunge_id INT);

CREATE TABLE dbo.mort(splunge_id INT, name VARCHAR(32));

INSERT dbo.splunge(splunge_id) SELECT 1 UNION SELECT 2 UNION SELECT 3;

INSERT dbo.mort(splunge_id, name) SELECT 1,'hi' UNION SELECT 2,NULL;

-- returns all three rows - should it?
SELECT *
  FROM dbo.splunge AS s, dbo.mort AS m
  WHERE s.splunge_id *= m.splunge_id
  AND m.name IS NULL;

-- returns rows 2 and 3
SELECT *
  FROM dbo.splunge AS s
  LEFT OUTER JOIN dbo.mort AS m
  ON s.splunge_id = m.splunge_id
  WHERE m.name IS NULL;

DROP TABLE dbo.splunge, dbo.mort;

Don't want to use a nullable name column to demonstrate? Ok, how about a nullable foreign key column?

CREATE TABLE dbo.splunge(splunge_id INT);

CREATE TABLE dbo.mort(splunge_id INT);

INSERT dbo.splunge(splunge_id) SELECT 1 UNION SELECT 2 UNION SELECT 3;

INSERT dbo.mort(splunge_id) SELECT 1 UNION SELECT 2 UNION SELECT NULL;

-- returns all three rows - should it?
SELECT *
  FROM dbo.splunge AS s, dbo.mort AS m
  WHERE s.splunge_id *= m.splunge_id
  AND m.splunge_id IS NULL;

-- only returns row 3 (because it was the only row truly missing)
SELECT *
  FROM dbo.splunge AS s
  LEFT OUTER JOIN dbo.mort AS m
  ON s.splunge_id = m.splunge_id
  WHERE m.splunge_id IS NULL;

DROP TABLE dbo.splunge, dbo.mort;

Not sure why you need to demonstrate this today - you just simply shouldn't be using *= / =* joins at all, regardless of whether you can reproduce some ambiguous behavior - they are deprecated and won't work in any database that doesn't support 80 compatibility level. And if you are using compatibility level 80, you need to be prepared for a ton of other functional differences and potential breaking changes when you do finally upgrade.

What is the actual behavior of compatibility level 80?
Bad habits : Clinging to old compatibility levels
Bad habits to kick : Using old-style joins


Query with join and concatenation of one joined table's column

Try with CROSS APPLY , like this:

SELECT
    R.idR AS [idR]
    ,'"'+ REPLACE(STUFF(CA.NameOfZ,1,1,''),'"','') + '"' AS NameOfZ
FROM
    @tableR AS R
    CROSS APPLY
    (  SELECT
            ','+Z.colX
        FROM
            @tableZ AS Z
            INNER JOIN @tableRZ AS RZ
            ON  Z.colZ = RZ.colZ
        WHERE
            R.idR = RZ.idR
        FOR XML PATH('')
    )CA(NameOfZ)
WHERE
    CA.NameOfZ IS NOT NULL

Output:

idR  NameOfZ
R1   "A,B"
R2   "A,C"
R3   "A,B,C,D"


MySQL 8+

SELECT id, name, ROW_NUMBER() OVER (ORDER BY score ASC, dob DESC) rank
FROM score

MySQL 5+

SELECT id, name, @rank := @rank + 1 rank
FROM score, (SELECT @rank := 0) init
ORDER BY score ASC, dob DESC

The students with equal both score and birth will be ranked randomly.


MySQL server won't start with foreign_key_checks=0

Root Cause

foreign_key_checks is not a global option you can preset at startup. Why ?

When you click on that link to the Documentation on foreign_key_checks, there is no chart that says it is global or session scope. Other options will specify the scope in a chart

ft_boolean_syntax says Variable Scope Global ; Dynamic Variable Yes
innodb_doublewrite says Variable Scope Global ; Dynamic Variable No
max_allowed_packet says Variable Scope Global ; Dynamic Variable Yes
sort_buffer_size says Variable Scope Global,Session ; Dynamic Variable Yes

With foreign_key_checks, it is session scope only.

Further proof of this is when you go to the command line and run this

mysqld --help --verbose > /tmp/mysqld.txt
vi /tmp/mysqld.txt

When you look inside the text file, you do not see --foreign-key-checks as a command line startup option. The other four variables I showed you can be used on the command line and in my.cnf.

If you have data to load, you can set that before loading:

SET foreign_key_checks = 0;
source mydata.sql

Proof of this is a standard mysqldump header ( first 10 lines )

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
/*!40103 SET TIME_ZONE='+00:00' */;
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;

Please note line 8

/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;

What does the bottom of a mysqldump look like ? Like this:

/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;

Please note the line

/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;

Epilogue

Bottom Line: foreign_key_checks is not meant of use in my.cnf. That make the most sense because (if allowed) starting mysqld with that would be damaging to referential integrity from startup.


Database upgrade script for removing columns

Code sent to SQL Server is validated, prior to execution, by SQL Server itself.  The query processor checks object names for existence prior to running the query, without regard for any code paths that may or may not be taken.

The only way to avoid the issue you're experiencing is to us sp_executesql exactly as it sounds like you are doing now.  Since you are doing this in an upgrade script that you control, there is virtually zero chance for a SQL injection problem, assuming you aren't taking non-validated input from a user (or via a config file the user has access to).

In case you haven't seen this, read Erland Sommarskog's bible on dynamic SQL. - it's a long read, but well worth it.


You can try the following.

  Stop the server a source
  Change the config file parameters such as datadir,basedir,log-directory etc(All variables which are path specific)
  You may need to change log-bin.index file as well in logs directory
  Move the directory to new path
  Start the server
  verify the services

Hope it helps and share any error message if you face any.


How to group data and write it's group id for each row?

;WITH cteA
AS(SELECT  Name,GroupID,
        DENSE_RANK () OVER(ORDER BY Name) AS New_GroupID
     FROM #T)

UPDATE  cteA
SET GroupID  = New_GroupID

Now , depending of your system , this could take a few seconds or more.
You can split the update , to do in chunks.
Something like A-G , then from G to M ... and you can add , in the DENSE_RANK  something like MaxGroupID + DENSE_RANK () OVER(ORDER BY Name) AS New_GroupID


Delete many rows from a large percona mysql db

Your procedure is excellent if you have the disk space.

Disable foreign keys while doing such.  (And hope that no one creates an FK violation that won't be caught.)

Do the two renames in a single statement: RENAME TABLE real TO old, new TO real;.

Replication should not be an issue -- except that a single SELECT to do the copy will be cause a big replication delay.

If you don't have the disk space for that, you are in deep weeds.  No table structure ever shrinks.

More discussion of big deletes.

That discusses "chunking", which could be adapted to the big SELECT, so that it has less impact on replication.

Or the chunking could be done to do the deletes, without all the rest of the stuff.  This also avoids the FK question and the replication delays.  However, it does not shrink the resulting table size; it leaves lots of free space.  You could then use pt-online-schema-change to merely copy the table over, thereby shrinking the table.  Hmmm... I guess this is how to do the deletes and free up disk space without needing extra space.  But it won't help if you have innodb_file_per_table=OFF.


You want to use a minimally logged inserts. If your temp/scratch database is on the same instance you can simply do.

SELECT * INTO DATABASE.SCHEMA.TargetTable FROM SourceTable;

The above statement create the table in the target database and bulk insert the data.

If you want to copy the data to a different instance you are best off using SSIS.


First off, you would want to install a new instance somewhere—possibly on the same host.

Then you would need to mount the control file (the path to the control file can be specified by setting the control_files parameter value in pfile).

Once you've mounted the control file with alter database mount, you can use RMAN to identify the data (temp, undo) files' location with report schema command.

You would also need to identify the undo files' location. Use V$LOGFILE view.

If you want to move the files around before opening the database, use RENAME FILE .. TO .. clause of the ALTER DATABASE statement.

When you're done with all these tasks, you can try to open the database.

SQL> alter database open;

See also.

Redo Log Data Dictionary Views
Renaming and Relocating Datafiles
Relocating and Renaming Redo Log Members


A table is placed in the reorg pending mode if you alter its physical structure, such as add or drop a column or change the column data type or nullability.

Some operations do not place the table in reorg pending, but reorganization is required for the changes to take effect, for example, changing the table COMPRESS attribute or the inline length of a LOB column.

Adding or dropping a foreign key does not place the table in reorg pending, nor does it require reorganization. However, changing an existing foreign key from not enforced to enforced will put the table in the check pending mode, which, like reorg pending, prevents applications from accessing that table.

I don't know of a single good source to read more about reorg, apart from the DB2 manual, particularly the ALTER TABLE statement and this article:

DB2 10.5 for Linux, UNIX, and Windows: Altering tables


So you're going to want to store your images on a fast CDN like Cloudfront. Normally I recommend storing pictures in-database, but not 240M of them :)

I'd avoid storing the whole URL, as it's likely to be repetitive. Just store the file name (a five-char case-sensitive name using a-z 0-9 would give you ~900M combinations. If your CDN is case-insensitive wrt URLs then use 6 chars ).

Are there really going to be 240M unique images, or can they be "shared" among entries? Do you expect every user to fill every entry with the max # of images?

You'd want some tables like this:

create table users (
  user_id serial primary key,
  ...
);

create table entries (
  entry_id serial primary key,
  user_id int not null references users(user_id), --index me
  description text,
  ...
);

create table pictures (
  file_name char(5) primary key,
  entry_id int not null references entries(entry_id) --index me
);

or perhaps:

create table pictures (
  picture_id serial primary key,
  file_name char(5) not null unique,
  entry_id int not null references entries(entry_id) -- index me
);

Use connection pooling!


I suspect you need to run sp_refreshview

See this on SO "“select * from table” vs “select colA, colB, etc. from table” interesting behaviour in SQL Server 2005"


ORCLCDB and demo0 are 2 seperate instances based on your output..

You have an empty CDB called demo0 with the seed PDB only.

And you have another CDB called ORCLCDB, in which you created a PDB called DEMO0.

Of course DEMO0 PDB is not listed in demo0 CDB, because the DEMO0 PDB was created in the ORCLCDB CDB.


PostgreSQL lost connection during massive index operation? What exactly happened?

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


Is there a server option which limits the amount of rows returned by a remote query?

You can use OFFSET and FETCH to effectively page through results

e.g.

SELECT * FROM MyTable ORDER BY MyColumn OFFSET 100 FETCH NEXT 100 ROWS ONLY

Helpful article here...

Article About OFFSET FETCH

Though i would look into why it fails to return large result sets 50k isn't a lot in modern data.


SQL Server Order SELECT List with condition

For a simple solution based on just the one 'y=2' value (as per comments), the suggested order by case ... construct should suffice.

However, if the requirement is to be able to sort by a varying number of 'y' values, or perhaps order by a combination of columns ...

One idea that comes to mind ... use a table value constructor (TVC) to build an 'inline' table of your desired sort order; we'll then join this TVC back to your original data set to provide the desired ordering sequence.

We'll start by creating our table and populating with some sample data:

drop table if exists t1;

create table t1
(id     int
,x      varchar(30)
,y      int);

insert into t1 (id,x,y) values
(1,'a',1),
(2,'b',3),
(3,'c',2),
(4,'d',2),
(5,'e',1),
(6,'f',3);

For our TVC we'll generate a single-column derived table called dt(y) that includes the y values listed in their desired sort order.  We'll add the row_number() function to provide the column that we'll actually order by.  Finally, we'll wrap all of this in a common table expression (CTE) for ease of reference.

-- order by 'y' values 2, 1, 3:

with myorder as
(select row_number() over (order by (select 1)) orderid,
        y
 from   (values (2),(1),(3) ) as dt(y)  -- desired sort order is 2, 1, 3
)
select *
from   myorder
order by orderid;

 orderid |  y
 ------- | --
 1       |  2
 2       |  1
 3       |  3

-- order by 'y' values 2, 3, 1:

with myorder as
(select row_number() over (order by (select 1)) orderid,
        y
 from   (values (2),(3),(1) ) as dt(y)    -- desired sort order is 2, 3, 1
)
select *
from   myorder
order by orderid;

 orderid |  y
 ------- | --
 1       |  2
 2       |  3
 3       |  1

We can now join our TVC with the original data set (table t1 in this case) to generate the desired sort order:

-- sort solely on the TVC's 'y' column:

with myorder as
(select row_number() over (order by (select 1)) orderid,
        y
 from   (values (2),(3),(1) ) as dt(y)
)

select t1.id,
       t1.x,
       t1.y

from   t1
join   myorder mo
on     mo.y = t1.y

order by mo.orderid;

 id | x  |  y
 -- | -- | --
  3 | c  |  2
  4 | d  |  2
  2 | b  |  3
  6 | f  |  3
  1 | a  |  1
  5 | e  |  1

-- sort by TVC's 'y' column (ascending order), then by t1's 'x' column (descending order):

with myorder as
(select row_number() over (order by (select 1)) orderid,
        y
 from   (values (2),(3),(1) ) as dt(y)
)

select t1.id,
       t1.x,
       t1.y

from   t1
join   myorder mo
on     mo.y = t1.y

order by mo.orderid,
         t1.x desc

 id | x  |  y
 -- | -- | --
  4 | d  |  2
  3 | c  |  2
  6 | f  |  3
  2 | b  |  3
  5 | e  |  1
  1 | a  |  1

The key item to remember is to make sure the TVC's records are listed in an order that matches the desired sort order.

One potential issue is that if you don't know how many 'y' values you have, or perhaps you only need to sort by a handful of known 'y' values (and don't care about ordering of other 'y' values), you could:

limit the TVC to just those 'y' values you're interested in and ...
join the main data set via an outer join to the TVC and ...
then default the the missing TVC's 'orderid' columns to some sufficiently large number to ensure their location in the sort order does not affect the 'y' values of interest

For example, assume we want 'y=2' values to be listed first, and we don't care about the rest of the 'y' values:

with myorder as
(select row_number() over (order by (select 1)) orderid,
        y
 from   (values (2) ) as dt(y)        -- only interested in 'y=2' records
)

select t1.id,
       t1.x,
       t1.y

from   t1                            -- left join w/ TVC/CTE
left
join   myorder mo
on     mo.y = t1.y

order by isnull(mo.orderid, 100),    -- default missing TVC.orderid's to 100
         t1.x desc

 id | x  |  y
 -- | -- | --
  4 | d  |  2   -- 'y=2' records show up
  3 | c  |  2   -- first in our output

  6 | f  |  3   -- while we let the database engine
  5 | e  |  1   -- determine the order of the rest
  2 | b  |  3   -- of the result set; granted, for this
  1 | a  |  1   -- example we also had 't1.x desc'

Obviously you could still add other columns to the 'order by' clause to affect the final ordering.

Here's a dbfiddle for all of the above.

While the example queries (above) are based on hard coding the sort order for the 'y' column, this method could be expanded to allow for hard coding the sort order for any combination of columns.

Let's say for example you want a hard coded sort order based on 'y=2' and 'x in ('a','b','c')', the CTE/TVC would look like:

with myorder as
(select row_number() over (order by (select 1)) orderid,
        x
        y
 from   (values (2,'a'),(2,'b'),(2,'c') ) as dt(x,y)
)

The main query could then be (outer) joined to myorder on the x and y columns:

with myorder as(...)

select ...

from   t1
left
join   myorder mo
on     mo.x = t1.x
and    mo.y = t1.y

order by isnull(mo.orderid, 100);

Again, if you don't need the flexibility then the more simple order by case ... construct should be sufficient.


Is there a set based way to load/read a tree branch with HierarchyId

The function to use is GetReparentedValue but when using only GetReparentedValue the tree may end up in an "inconsistent" state.

Here is some code provided by Microsoft that takes care of that. Moving subtrees.

I guess that related to this is Enforcing a tree. It uses a calculated column for parent id that does a self join to the PK.


SQL Server Database File Groups on a SAN: Relevant or Not?

I am not a SAN admin, but I have a very nice one in my team that takes the time to do his job proprely, and share his knowledge with us when we ask.

The way our SAN is configured makes it so there is a very large amount of cache between the SQL servers and the physical disks. In this situation, having multiple filegroups, or even splitting the DATA and LOG files on different drive letters is a moot point, as it all ends up in the same cache cluster and points to the same large array of 15000RPM disks. The cache is intelligent enough to keep the relevant stuff cached, and write to disk in a "Lazy Writer" method.

That said, althou we handle a very large amount of data, we are not overly heavy in throuput. This setup is lightning fast for our needs, and very safe between the usual backups and SAN automatic redundancy to another server room.

Sit down with your admin, tell him what your needs are, and balance the final setup with his knowledge and architecture. SAN architecture can vary greatly, so some of the "best practices" of SQL server installation may not even apply, like it did for us.


Make Oracle SQL Developer act like PL/SQL Developer

The preferences dictate when the code assist features kick in. In 4.0.3 we filter the advice for the automatic bits when there are more than 10 suggested identifiers. So, typing:

select * from

...on a large schema, you'd likely not see any suggested table or views names, unless you invoked the helper directly via ctrl+spacebar.

As you type more, the more likely the suggested values will be of more value. The default settings will have automatic popups after .6 seconds and the number of hits goes below 10.

Tools > Preferences > Code Editor > Completion Insight

Make sure you don't have the first two items disabled. And try increasing the filter to something higher than 10. Examples of different insight 'helpers' and a video demo here.

You can set your own NULL text, that's in the preferences. I have a bug logged so you can set the NULL coloring AND use the zebra styling (fixed in version 4.1, soon to be released.) And I also have an ER logged for custom colors on the 'zebra' styling.

We fixed that bug, go get v4.1.1 - null color + zebra grids work now.


Upgrade process from SQL Server 2000 to SQL Server 2012

No, as I see it, you didn't miss anything. I have upgraded one of my databases from SQL Server 2000 to SQL Server 2012.

Another way is to upgrade the entire server – in this case you can use the upgrade wizard.

UPDATE: my upgrade process was successful, but be careful about discontinued and deprecated features in SQL Server 2008 and 2012.


sorting groups of related rows by average values while keeping the groups together

As @ypercube pointed out in the comments, my query is quite close to the correct answer. The sort by avg_pval DESC, avg_margstat is actually close to the correct sort, only incorrect if the (avg_pval, margstat) tuple happens to have ties. So one can sort again, for a fixed (avg_pval, margstat), on datafile_id, index which will bring the groups back together. Finally, one can optionally sort within the groups, by pvalue DESC, margstat, Putting that all together, one gets

SELECT datafile_id,
       index,
       pvalue,
       margstat,
       Avg(pvalue)
         OVER (
           partition BY datafile_id, index) AS avg_pval,
       Avg(margstat)
         OVER (
           partition BY datafile_id, index) AS avg_margstat
FROM   data
ORDER  BY avg_pval DESC,
          avg_margstat,
          datafile_id,
          index,
          pvalue DESC,
          margstat;


to pivot or not to pivot - schema design question

MySQL should not have trouble handling a large table like that.  You have it indexed (because of the foreign keys) so most queries against the table should use one of the indexes and be fairly fast even if there are a huge number of rows in the table.


MySQL: SELECT to local variable in stored procedure

The problem was my declared local variable id.
After I changed her name, everything worked just fine.
My previous answer was:
I don't know what the problem was, but I did found a solution.
I wrote:
    SELECT MIN(ID) FROM WORKER WHERE CARDNUMBER = cardUID INTO @workerId;
    SELECT @workerId;

instead of
    SET @workerId = (SELECT MIN(ID) FROM WORKER WHERE CARDNUMBER = cardUID);
    SELECT @workerId;


Divide by Zero Causing Error transaction to fail with Arthimetic Abort and ANSI WARNINGS FALSE after upgrade

The problem is in the expression:

ActualCommission =
    CASE
        WHEN ISNULL(sb.CommissionPercent, 0) = 0
        THEN CAST(((sm.MarkupPercent - 100) / sm.MarkupPercent) * 100 AS decimal(18, 2))
        ELSE ROUND(ISNULL(sb.CommissionPercent, 0) - (100.0 - sm.MarkupPercent), 2)
    END

A divide-by-zero error could occur for any row where CommissionPercent = 0 and MarkupPercent = 0.  Whether the error actually occurs in practice depends on the order of evaluation at runtime, which in turn depends on the shape of the execution plan and the detailed behaviours of the individual plan operators.

For example, some plan shapes might physically eliminate the problematic rows before the expression is evaluated.  Others might defer evaluation of the expression until the result is required by another operator later in the plan.  There are many possibilities and subtleties here, leading to the apparently 'strange' behaviours you observe.

In general, SQL Server makes no guarantees about the order of evaluation of scalar expressions or the number of times a particular expression might be evaluated at runtime.  The exception to this is the CASE statement, which does provide guarantees about the order of evaluation in most circumstances.  From the documentation:

  The CASE statement evaluates its conditions sequentially and stops
  with the first condition whose condition is satisfied. In some
  situations, an expression is evaluated before a CASE statement
  receives the results of the expression as its input. Errors in
  evaluating these expressions are possible. Aggregate expressions that
  appear in WHEN arguments to a CASE statement are evaluated first, then
  provided to the CASE statement.

The way to protect against the error is to modify the CASE expression so the divide-by-zero is explicitly handled using an earlier WHEN clause.  How you choose to account for rows with CommissionPercent = 0 and MarkupPercent = 0 is something only have the information to decide about.  That said, the behaviour with ARITHABORT set to OFF is to return a NULL. The following change to the CASE statement produces that result with ARITHABORT set to the highly-recommended ON:

ActualCommission =
    CASE
        -- New
        WHEN (sb.CommissionPercent = 0 OR sb.CommissionPercent IS NULL) AND sm.MarkupPercent = 0
        THEN NULL

        WHEN ISNULL(sb.CommissionPercent, 0) = 0
        THEN CAST(((sm.MarkupPercent - 100) / sm.MarkupPercent) * 100 AS decimal(18, 2))
        ELSE ROUND(ISNULL(sb.CommissionPercent, 0) - (100.0 - sm.MarkupPercent), 2)
    END

Explicitly accounting for the possibility of a divide-by-zero error is very much better than relying on the ARITHABORT setting.  That link contains the following statement:

  You should always set ARITHABORT to ON in your logon sessions. Setting
  ARITHABORT to OFF can negatively impact query optimization leading to
  performance issues.

ARITHABORT is required to be ON for many newer engine features (e.g. indexes on computed columns and indexed views).  Most client libraries explicitly set ARITHABORT to ON when connecting, which overrides any database- or server- level setting.

My strong recommendation is to always use the recommended SET settings and code defensively against error conditions.

SET ANSI_NULLS,
    ANSI_PADDING,
    ANSI_WARNINGS,
    ARITHABORT,
    CONCAT_NULL_YIELDS_NULL,
    QUOTED_IDENTIFIER
    ON;

SET NUMERIC_ROUNDABORT OFF;