Aggregation vs Cursor For Reshaping MongoDB Documents

Aggregation vs Cursor

Let's first start from Aggregation. As per MongoDB BOL Here Aggregation operations process data records and return computed results. Aggregation operations group values from multiple documents together, and can perform a variety of operations on the grouped data to return a single result.
The aggregation pipeline can use indexes to improve its performance during some of its stages. In addition, the aggregation pipeline has an internal optimization phase.

The most basic pipeline stages provide filters that operate like queries and document transformations that modify the form of the output document.

  The pipeline provides efficient data aggregation using native
  operations within MongoDB, and is the preferred method for data
  aggregation in MongoDB.

For example here i want to show the aggregation where MongoDB provides three ways to perform aggregation: the aggregation pipeline, the map-reduce function, and single purpose aggregation methods.

Let's here i am going to create orders collection of MongoDB with 4 documents.

> db.orders.insertMany([
... {cust_id: "A123",
... amount: 500,
... status: "A"
... },
... {cust_id: "A123",
... amount: 250,
... status: "A"
... },
... {cust_id: "B212",
... amount: 200,
... status: "A"
... },
... {cust_id: "A123",
... amount: 300,
... status: "D"
... }
... ]
... )
{
        "acknowledged" : true,
        "insertedIds" : [
                ObjectId("5a44c1479adf6e5fc5cea525"),
                ObjectId("5a44c1479adf6e5fc5cea526"),
                ObjectId("5a44c1479adf6e5fc5cea527"),
                ObjectId("5a44c1479adf6e5fc5cea528")
        ]
}

To verify the inserted documents from MongoDB

> db.orders.find().pretty()
{
        "_id" : ObjectId("5a44c1479adf6e5fc5cea525"),
        "cust_id" : "A123",
        "amount" : 500,
        "status" : "A"
}
{
        "_id" : ObjectId("5a44c1479adf6e5fc5cea526"),
        "cust_id" : "A123",
        "amount" : 250,
        "status" : "A"
}
{
        "_id" : ObjectId("5a44c1479adf6e5fc5cea527"),
        "cust_id" : "B212",
        "amount" : 200,
        "status" : "A"
}
{
        "_id" : ObjectId("5a44c1479adf6e5fc5cea528"),
        "cust_id" : "A123",
        "amount" : 300,
        "status" : "D"
}

Aggregation Pipeline

MongoDB’s  aggregation framework is modeled on the concept of data processing pipelines. Documents enter a multi-stage pipeline that transforms the documents into an aggregated result.

> db.orders.aggregate([{$match: {status: "A"}},
... {$group: {_id: "$cust_id",total:{$sum: "$amount"}}}])
{ "_id" : "B212", "total" : 200 }
{ "_id" : "A123", "total" : 750 }
>

Map-Reduce

MongoDB also provides map-reduce operations to perform aggregation. In general, map-reduce operations have two phases: a map stage that processes each document and emits one or more objects for each input document, and reduce phase that combines the output of the map operation. Optionally, map-reduce can have a finalize stage to make final modifications to the result. Like other aggregation operations, map-reduce can specify a query condition to select the input documents as well as sort and limit the results.

> db.orders.mapReduce(
... function() {emit (this.cust_id,this.amount);},
... function(key,values){return Array.sum(values)},
... {
... query:{status: "A"},
... out: "order_total"
... }
... )
{
        "result" : "order_total",
        "timeMillis" : 1178,
        "counts" : {
                "input" : 3,
                "emit" : 3,
                "reduce" : 1,
                "output" : 2
        },
        "ok" : 1
}
>

Note: Starting in MongoDB 2.4, certain mongo shell functions and properties are inaccessible in map-reduce operations. MongoDB 2.4 also provides support for multiple JavaScript operations to run at the same time. Before MongoDB 2.4, JavaScript code executed in a single thread, raising concurrency issues for map-reduce.

Single Purpose Aggregation Operations

MongoDB also provides db.collection.count() and db.collection.distinct().

All of these operations aggregate documents from a single collection. While these operations provide simple access to common aggregation processes, they lack the flexibility and capabilities of the aggregation pipeline and map-reduce.

> db.orders.distinct("cust_id")
[ "A123", "B212" ]

Cursor

As MongoDB BOL Iterate a Cursor in the mongo Shell The db.collection.find() method returns a cursor. To access the documents, you need to iterate the cursor. However, in the mongo shell, if the returned cursor is not assigned to a variable using the var keyword, then the cursor is automatically iterated up to 20 times to print up to the first 20 documents in the results.

The following examples describe ways to manually iterate the cursor to access the documents or to use the iterator index.

Manually Iterate the Cursor

var myCursor = db.orders.find( { Cust_id: "A123" } );

myCursor

You can use the cursor method forEach() to iterate the cursor and access the documents, as in the following example:

var myCursor =  db.orders.find( { Cust_id: "A123" } );

myCursor.forEach(printjson);

  Note : You can use the DBQuery.shellBatchSize to change the number of iteration from the default value 20.

Iterator Index

In the mongo shell, you can use the toArray() method to iterate the cursor and return the documents in an array, as in the following:

var myCursor = db.orders.find( { Cust_id: "A123" } );
var documentArray = myCursor.toArray();
var myDocument = documentArray[3];

The toArray() method loads into RAM all documents returned by the cursor; the toArray() method exhausts the cursor.

Cursor Behaviors

Closure of Inactive Cursors by default, the server will automatically close the cursor after 10 minutes of inactivity, or if client has exhausted the cursor. To override this behavior in the mongo shell, you can use the cursor.noCursorTimeout() method:

var myCursor = db.orders.find().noCursorTimeout();

After setting the noCursorTimeout option, you must either close the cursor manually with cursor.close() or by exhausting the cursor’s results.

To know Cursor Information from MongoDB Server

The db.serverStatus() method returns a document that includes a metrics field.

db.serverStatus().metrics.cursor

The result is the following document:

{
   "timedOut" : <number>
   "open" : {
      "noTimeout" : <number>,
      "pinned" : <number>,
      "total" : <number>
   }
}

As finally, In Aggregation operations group values from multiple documents together, and can perform a variety of operations on the grouped data to return a single result.
The pipeline provides efficient data aggregation using native operations within MongoDB, and is the preferred method for data aggregation in MongoDB.

where in the mongo shell, if the returned cursor is not assigned to a variable using the var keyword, then the cursor is automatically iterated up to 20 times to print up to the first 20 documents in the results.
The toArray() method loads into RAM all documents returned by the cursor; the toArray() method exhausts the cursor.


MySql column value to indicate 2 possible options

You can use the MySQL ENUM type which is stored as a one-byte int internally. From the docs which only hint on implementation,

  Inserting 1 million rows into this table with a value of 'medium' would require 1 million bytes of storage, as opposed to 6 million bytes if you stored the actual string 'medium' in a VARCHAR column.

Though it's documented elsewhere explicitly,

  1 or 2 bytes, depending on the number of enumeration values (65,535 values maximum)

MySQL can use two byte enums if you have 255<x<65,535 potential values to enumerate over.

It looks like this,

CREATE TABLE payment (
    id      int  AUTO_INCREMENT,
    payment ENUM('money', 'online'),
    PRIMARY KEY (id)
);


Huge size on mongodb's gridfs. Should I compact?

You have few options here.

Run DB command 'Compact' on the collection - This will not reclaim disk space, but will perform defragmentation over the specific collection.
Perform a repairDatabase - This reclaims disk space back to the OS. the repairDatabase runs over the entire DB (or shard) and not just one collection (I would read the documentation, as repaireDatabase both locks the DB it's repairing and requires at least x2 of disk space than your DB size)

Please keep in mind that MongoDB always seeks to allocate more data files.
If indeed as you mentioned, you're deleting a lot of data, Mongo will reuse this space but it might contain 'junk' blocks which makes mongo use less than it actually has allocated. this is why Mongo is allocating more and more data files.

You can read more about it here: Managing disk space in MongoDB

Hope this helps a bit.


mysql.sock not generated when starting MySQL server using XAMPP

I have BAD NEWS and VERY BAD NEWS

BAD NEWS

Connecting to MySQL via the socket file is a Unix-only protocol

  This option explicitly specifies a protocol to use for connecting to the server. It is useful when the other connection parameters normally would cause a protocol to be used other than the one you want. For example, connections on Unix to localhost are made using a Unix socket file by default:

shell> mysql --host=localhost

  To force a TCP/IP connection to be used instead, specify a --protocol option:

shell> mysql --host=localhost --protocol=TCP

  The following table shows the permissible --protocol option values and indicates the platforms on which each value may be used. The values are not case sensitive.

--protocol Value   Connection Protocol                              Operating Systems
TCP                TCP/IP connection to local or remote server      All
SOCKET             Unix socket file connection to local server      Unix only
PIPE               Named-pipe connection to local or remote server  Windows only
MEMORY             Shared-memory connection to local server         Windows only

Therefore, MySQL for Windows does not create a socket file and you cannot connect with one.

The closest protocol you want to use like a socket file is the named pipe

  On Windows, you can force a MySQL client to use a named-pipe connection by specifying the --pipe or --protocol=PIPE option, or by specifying . (period) as the host name. If named-pipe connections are not enabled, an error occurs. Use the --socket option to specify the name of the pipe if you do not want to use the default pipe name.

VERY BAD NEWS

I have an old posts where I mentioned that certain builds of mysqld.exe supported named pipes but are no longer being distributed :

Oct 15, 2014 : How to check/change MySQL named pipe settings?
Sep 27, 2011 : MySQL-NT is crashing frequently

As I said in How to check/change MySQL named pipe settings?

  Do yourself a favor and do not enable it since there is so little documentation on using this protocol. It was originally meant for Windows NT (Not There).

SUGGESTION

Connect with TCP/IP instead of trying to work with named pipes


If the first range is allowed to start with 0, you can do something like this:

with ranges (range) as (
  select int4range(i, i + 1000, '[)')
  from generate_series(0, (select max(pos) from varianti), 1000) as i
)
select range,
       count(v.pos) as range_count
from ranges l
  left join varianti v
         on v.pos <@ l.range
group by range
order by range;

For this data:

insert into varianti (pos)
values
(123), (150), (170),
(1025), (1050), (1051), (1052), (1053), (1054), (1054),
(2054), (2051),
(3001), (3002), (3003), (3004), (4004),
(5426);

This returns:

range       | range_count
------------+------------
[0,1000)    |           3
[1000,2000) |           7
[2000,3000) |           2
[3000,4000) |           4
[4000,5000) |           1
[5000,6000) |           1


Best way to automate sql server backup & restore from prod to dev?

Your solution is pretty much the way to go.

Personally I'd consider using SSIS to coordinate the tasks as well as sending emails on failure etc.


How to build a database that stores huge data while no need of frequent query?

I might consider below option if it is in Mysql.

Table will be partitioned by RANGE weekly.
On Master keep latest week's partition. And drop earlier partitions in below way.

  a). set sql_log_bin=0; #Set local session to not log into binlog.

  b). alter table drop partition OLD_PARTITION_NAME;

  c). exit;

On Slave keep old data.
Set this kind of automated/manual job every week to drop old partition on Master.
You might have to once verify the selects going on Master.


Create Meaningful Visualization for Large Set of Tables

It's possible to write a query using the database DMVs, that dynamically tries to guess relations between tables by matching column names.

Here's a very simple draft, although it will only work if your primary key column names are unique for each primary key table (i.e. if the primary keys are called "id" in all the tables, this won't fly). Also, you'll have to make a few modifications if you want the script to work with composite keys (multiple columns in the primary key).

WITH pk
AS (
    SELECT ix.[object_id], ixcol.column_id, ix.is_primary_key
    FROM sys.indexes AS ix
    INNER JOIN sys.index_columns AS ixcol ON ix.[object_id]=ixcol.[object_id]
    WHERE ix.is_primary_key=1),

     keycols
AS (
    SELECT tbl.[object_id], tbl.[name] AS tblName, col.[name] AS colName, pk.is_primary_key
    FROM sys.tables AS tbl
    INNER JOIN sys.columns AS col ON tbl.[object_id]=col.[object_id]
    LEFT JOIN pk ON tbl.[object_id]=pk.[object_id] AND col.column_id=pk.column_id)

SELECT pk.tblName AS pkTable, pk.colName AS pkCol, fk.tblName AS fkTable, fk.colName AS fkColumn
FROM keycols AS pk
INNER JOIN keycols AS fk ON
    pk.is_primary_key=1 AND
    pk.colName=fk.colName AND
    pk.[object_id]!=fk.[object_id];

Once you've established relations between the tables, you might be able to identify "clusters" of tables that you can group on separate diagrams. If there are different schemas in the database, this might be what you're looking for.

If you want to draw diagrams in SSMS, you'll have to actually create foreign key constraints between the tables, and I wouldn't really recommend doing that in a vendor database without their approval. Then again, you don't have to save the diagram.

I do all my diagramming in Powerpoint, but that's hardly the answer you're looking for. :)


Fill in the gaps between rows using the previous row's values

with
  date_range as (select min(insp_date) mind, max(insp_date) maxd from road_insp),
  insp_dates as (select add_months(trunc(mind, 'YYYY'), (level - 1)*12) as insp_date
   from date_range connect by level <= extract(year from maxd) - extract(year from mind))
select
   ri.insp_id, ri.road_id,
   nvl(case when extract(year from ri.insp_date) = extract(year from id.insp_date)
     then ri.insp_date else id.insp_date end, ri.insp_date) as insp_date,
   ri.condition
from
  insp_dates id right join
  (select insp_id, road_id, insp_date, condition as condition,
     lead(insp_date) over (partition by road_id order by insp_date) as next_insp_date
  from road_insp) ri
  on id.insp_date >= trunc(ri.insp_date, 'YYYY')
    and id.insp_date < trunc(ri.next_insp_date, 'YYYY')
order by insp_id, road_id, insp_date
;

Generate dates between min and max insp_date.

Find the next insp_date for each inspection to have them in 1 row.

Join the generated dates to the original dataset on date ranges based on year, excluding the upper bound, because that is the next inspection with existing data. Outer join, so the last inspection for a road (where next inspection is null) is included as well.

If there is generated insp_date is in the same year as the original data, use the original data, otherwise use the generated insp_date.

   INSP_ID    ROAD_ID INSP_DATE   CONDITION
---------- ---------- ---------- ----------
         1        100 2004-04-01         .9
         1        100 2005-01-01         .9
         1        100 2006-01-01         .9
         1        100 2007-01-01         .9
         1        100 2008-01-01         .9
         1        100 2009-01-01         .9
         1        100 2010-01-01         .9
         2        100 2011-04-01         .7
         3        100 2012-03-01         .7
         3        100 2013-01-01         .7
         3        100 2014-01-01         .7
         3        100 2015-01-01         .7
         3        100 2016-01-01         .7
         4        100 2017-03-01         .6
         5        100 2018-03-01         .6
         6        200 2010-06-01         .4
         6        200 2011-01-01         .4
         7        200 2012-06-01         .3


DBCA silent database creation rolls back prompt when asked for sys password

Bug 21163674 : DBCA SILENT MODE DELETES LINES FROM SCREEN AND DISPLAYS NO PROMPT

  This happens when
  XTERM is run with both "Enable Reverse Wraparound" & "Enable Auto
  Wraparound" enabled. As soon as one of these is disabled, then dbca
  functions correctly.

  ...

  With putty , settings -> terminal, uncheck 'Auto wrap mode initially on'.

  Another workaround is to use silent installation with a response file.


WITH
cte1 AS ( SELECT id, join_key, ROW_NUMBER() OVER (PARTITION BY join_key ORDER BY id) rn
          FROM src ),
cte2 AS ( SELECT id, join_key, ROW_NUMBER() OVER (PARTITION BY join_key ORDER BY id) rn
          FROM tgt )
SELECT cte1.id src_id, cte2.id tgt_id, COALESCE(cte1.join_key, cte2.join_key) join_key
FROM cte1
FULL OUTER JOIN cte2 ON cte1.join_key = cte2.join_key AND cte1.rn = cte2.rn

fiddle


How can I define a one to many constraint between columns of the same table?

This has to be done by TRIGGER, because it is too complex for CHEKCconstraint
For updating you need a similar trigger/function

CREATE TABLE tabl1
    ("pk" varchar(5), "a" varchar(6), "b" varchar(6), "c" varchar(6))
;

INSERT INTO tabl1
    ("pk", "a", "b", "c")
VALUES
    ('p1', 'a1', 'b11', 'c111'),
    ('p2', 'a1', 'b11', 'c112'),
    ('p3', 'a1', 'b12', 'c121'),
    ('p4', 'a1', 'b12', 'c122'),
    ('p5', 'a2', 'b21', 'c211'),
    ('p6', 'a2', 'b21', 'c212'),
    ('p7', 'a2', 'b22', 'c221'),
    ('p8', 'a2', 'b22', 'c222'),
    ('p9', NULL, 'b31', 'c311'),
    ('p10', 'a3', NULL, 'c312'),
    ('p11', 'a3', 'b31', NULL),
    ('p12', NULL, NULL, 'c314'),
    ('p13', NULL, 'b31', NULL),
    ('p14', 'a3', NULL, NULL)
;

CREATE OR REPLACE FUNCTION check_insert()
RETURNS TRIGGER AS $$
   BEGIN
   IF EXISTS(SELECT 1 FROM tabl1 WHERE "c" = NEW."c" AND "b" = NEW."b" ) then
       raise EXCEPTION 'The column %  already  with %',NEW."c",NEW."b";
   ELSIF EXISTS(SELECT 1 FROM tabl1 WHERE "a" <> NEW."a" AND "b" = NEW."b" ) then
           raise EXCEPTION 'The column %  already  has already a column',NEW."b";
   END IF;
   RETURN NEW;
   END;
$$ LANGUAGE plpgsql;

CREATE  TRIGGER trigger_name BEFORE
 INSERT
ON tabl1
FOR EACH ROW
EXECUTE PROCEDURE check_insert();

INSERT INTO tabl1
    ("pk", "a", "b", "c")
VALUES
    ('p15', 'a1', 'b11', 'c111')

ERROR:  The column c111  already  with b11

CONTEXT:  PL/pgSQL function check_insert() line 4 at RAISE

INSERT INTO tabl1
    ("pk", "a", "b", "c")
VALUES
('p16' , 'a1'   , 'b21'  , 'c213' )

ERROR:  The column b21  already  has already a column

CONTEXT:  PL/pgSQL function check_insert() line 6 at RAISE

INSERT INTO tabl1
    ("pk", "a", "b", "c")
VALUES
('p17',   'a2',   'b11'   ,'c214')

ERROR:  The column b11  already  has already a column

CONTEXT:  PL/pgSQL function check_insert() line 6 at RAISE

SELECT * FROM tabl1

pk  | a    | b    | c
:-- | :--- | :--- | :---
p1  | a1   | b11  | c111
p2  | a1   | b11  | c112
p3  | a1   | b12  | c121
p4  | a1   | b12  | c122
p5  | a2   | b21  | c211
p6  | a2   | b21  | c212
p7  | a2   | b22  | c221
p8  | a2   | b22  | c222
p9  | null | b31  | c311
p10 | a3   | null | c312
p11 | a3   | b31  | null
p12 | null | null | c314
p13 | null | b31  | null
p14 | a3   | null | null

db<>fiddle here


generate unique primary key for all clients from the local database and NOT the server

If there are no natural way of identifying a registration, you may want to have a look at GUID:

https://en.wikipedia.org/wiki/Globally_unique_identifier

The upside is that there are plenty of libraries dealing with them. The downside is that they occupy space which may lead to higher B-Tree indexes, i.e. additional I/O. More read I/O is also required because GUID's are evenly distributed, when you read a page the likelihood that you get a related row is low which causes additional pages to be read.

Another option is to add another attribute representing the host and use this in combination with your sequence as an identifier.

primary key(host_id, seq_id)

Make sure that no two host uses the same host_id.

Yet a third option is to partition the seq_id for different hosts. I.e. host 1 uses seq_id 1..n, host 2 uses seq_id n+1..2n and so forth. Not an alternative for the heart of fainted ;-)


Database Tuning Advisor can't find tables across two databases

With Martin Smith's help I found the answer.

On the dialog where you specify which events to include click Show All Columns. Tick the Database name and Id columns. Then redo the trace. It should import into the Tuning Advisor OK.


You are correct, the only way to "rearrange" the order of the columns is to create a table with the new structure and push the old data into it, drop the old table and then rename the new table (or some variation of that).  It requires copying all the data in the table and some drops and renames.


Sending an automated email through a nightly job

The query will be executed by sp_send_dbmail asynchronously, on a separate session that has msdb as current database. So you must use three-part names in your query:

@query = N'select substring(PostOfficeBox,1,4) as [Postcode]
...
from TestCRM.dbo.AccountExtensionBase as a
JOIN TestCRM.dbo.CustomerAddressBase as b ON a.AccountId = b.ParentId
...'


Well, if there is no ORDER BY clause, the results is a non-ordered set, so the order that the rows are returned should be considered 'random' anyway.
However, PostgreSQL will typically fetch the rows in the most efficient manner, which may be the order they were inserted.
You can 'work around' this by performing a mock update, which will move the updated rows to the 'end' of the line. You can repeat several dissimilar updates using modulo, random or any other form that will filter a 'seemingly random' subset.
Something along the lines of:

DROP TABLE IF EXISTS Foo;

CREATE TABLE Foo (Col1 INT PRIMARY KEY);

INSERT INTO Foo (Col1)
SELECT * FROM generate_series(1, 20);

SELECT * FROM Foo;

UPDATE  Foo
SET     Col1 = Col1
WHERE   Col1%3 = 0;

UPDATE  Foo
SET     Col1 = Col1
WHERE   Col1%5 = 0;

SELECT * FROM Foo;

Do this in development environment only, not in production of course :-)

HTH


Error restoring backup in sql server. Old server version

While you may have SQL Server 2008 R2 on your machine (or at least the management studio), that doesn't stop you having or connecting to a SQL 2008 Server - which it seems you are.

 select @@version

will tell you the server version and its number.

10.50.2500 is SQL 2008 R2 SP1

10.0.2573 is SQL 2008 SP1

NB - SQL 2008 SP2 (Service Pack 2) is different to SQL 2008 R2.

To restore this backup, you need to connect to a 2008 R2 or 2012 server.


How could you implement a custom sort that groups matching records from other source

Here's another couple of possibilities

SELECT [Source-System],
       PersonName,
       PersonPossibleMatchID
FROM   #src
ORDER  BY MAX(CASE
                WHEN [Source-system] = 'Driver'
                  THEN PersonName
              END) OVER (PARTITION BY PersonPossibleMatchId) ASC,
          [Source-System] DESC,
          PersonName ASC

(online demo)

- Or -

WITH DriverPersonNames
     AS (SELECT PersonPossibleMatchID,
                PersonName
         FROM   #src
         WHERE  [Source-System] = 'Driver')
SELECT s.*
FROM   #src s
       JOIN DriverPersonNames d
         ON d.PersonPossibleMatchID = s.PersonPossibleMatchID
ORDER  BY d.PersonName ASC,
          [Source-System] DESC,
          s.PersonName ASC

(online demo)

Both will return the same results assuming that there is exactly one Driver record per PersonPossibleMatchID and PersonPossibleMatchID is not nullable.

(DDL for online demos borrowed from jyao's answer)


Clustered Index taking huge amount of space

Changing a table from a heap to a clustered table doesn't "grow the data". I.e., the clustered index doesn't magically require more space just because it is organized as a linked list (sorted by some column combination) compared to a heap.

However, things to watch out for:

Internal fragmentation (less than 100% full pages). If you had a low value for fillfactor configured for the instance (sp_configure) or specified a low value in the CREATE INDEX/ALTER TABLE command, then that can explain it. So, check your page fullness using sys_dm_db_index_physical_stats().

If you are comparing apples to oranges. Are you looking at only the clustered index or at the table level (i.e., including all the other indexes). The other indexes (non-clustered, NC) have as a "row-pointer" the RID for a heap, which is 8 byte. The row-pointer from an NC index for a clustered table is the clustering key. In your case it is 16 bytes (it would have been 4 extra bytes for duplicates if it wasn't unique). I.e., the NC indexes will now be a bit bigger than before. Also, then converting the table from a heap to a clustered index, all NC indexes were rebuilt, and I think the fillfactor will be picked up from the server setting. Could that explain it? I very much doubt it, but perhaps in some extreme case... Assuming you include the NC index size when you do the comparison. So, make sure that you are looking at the right thing (I prefer my sp_indexinfo, which gives pretty details output, but anything that you know an trust).

My bet is on an sp_configure for fillfactor which is weirdly low...


No you don't need. Ops-manager just add there DB where all configuration information and collected data is kept.


Security : SQL Server Object Access From One Schema to another schema

The concept you want to understand is called "ownership chaining"

As long as the owner of the object and database remain the same, SQL Server will not re-evaluate the permissions of a user after the 1st object in the chain.

In your first example you denied USER1 access to TABLE1 but allowed access to VW_TABLE1.  Since USER1 has access to VW_TABLE1 and the ownership chain has not been broken, they can see TABLE1 because permissions are not re-evaluated inside the VIEW.

In your second example I'm assuming the SEC schema must have a different owner which causes SQL Server to re-evaluate permissions when inside the VW_TABLE1 view.

In short, to fix this problem make sure all objects have the same owner


Since you are on S1 you can use two features from Azure:

Query Store
Index Advisor.

With Query Store you'll be able to identify the bottlenecks, the most heavily usage queries and the plans they use. This will allow you to prioritize what queries to optimize, and which existing indexes are important.

Index Advisor is more than missing indexes, see this video. It has some ML modeling that allows it to pick the most impactful index recommendation, eliminate duplicates, and it does A/B testing to measure the difference.

In general tables that already have 5-6 indexes and you find yourself adding more are good candidates for clustered columnstore indexes (supported on Azure and on premise post 2014). Segment elimination can do miracles for you.


Instead of using the (old) datetime datatype in SQL Server, you can use the (newer and better) datetime2. Among other things, it accepts earlier dates, back to year 1.

A tip is to specify how many digits you was for the second fractions. If you don't do it, you get 7 (as in 12:23:45.2314534). If you can get by with less, then specify for instance datetime2(0) and you get zero decimals. Or whatever you want/need.


Restore database with Availability Groups

I think you need to revise your question with clarity:

Olas scripts meant for following, not for restoring database.

Backup
DBCC - Integrity Check
Maintenance - Index and Statistics

Aside from this, you cannot DROP or RESTORE a database while it's part of Availability Group. You can remove a database from availability group with following commands:

PRIMARY Replica:

ALTER AVAILABILITY GROUP AGName remove database DatabaseName;

SECONDARY Replica:

ALTER database DatabaseName set HADR OFF;


SQL Server Active Directory login groups with limitation

This is a common issue, you want to use groups to keep things simple but you don't want to lose visibility of who has access to your databases. The way I see it you have two options:

If you trust your AD admins (If you don't you have bigger problems) then use AD groups.
If you don't trust your AD admins then you will have to manage access on a per user level by giving everyone an individual SQL Server login.


Does CUBRID have Information Schema Views?

Fixed it. It works only if you fully remove any schema or catalog information:

        Configuration configuration = new Configuration()
                .withJdbc(new Jdbc()
                                .withDriver(DRIVER)
                                .withUser(USER)
                                .withPassword(PASSWORD)
                                .withUrl(URI))
                .withGenerator(new Generator()
                                .withGenerate(new Generate()
                                                .withDaos(true)
                                                .withPojos(true)
                                                .withImmutablePojos(true))
                                .withDatabase(new Database()
                                                .withName(DATABASE)
                                                .withIncludes(".*")
                                                .withExcludes("_?db_.*?"))
                                .withTarget(new Target()
                                                .withPackageName(PACKAGE)
                                                .withDirectory(DIRECTORY)
                                                .withClean(true)))
                .withLogging(Logging.TRACE);

        GenerationTool.generate(configuration);

This archived manual page of the CUBRID project was also helpful: https://web.archive.org/web/20160819080919/https://www.cubrid.org/wiki_apps/entry/jooq-cubrid-tutorial


How to TRIGGER/RETURNING this generator function in PostgreSQL?

I am thinking if the database can do the generation task for you i.e.
  how to bind your primary key to any algorithm that can generate such
  shorthand forms

Since you mention a salt, I assume you're looking for short strings
that are hard to guess or reproduce by an outsider, despite
being originated with a database sequence.
The permuteseq extension
essentially does that.

The function that produces a short string from an integer has
to be provided, for instance:

CREATE FUNCTION id_to_alpha(n int) RETURNS text
LANGUAGE plpgsql IMMUTABLE STRICT AS $$
DECLARE
 alphabet text:='abcdefghijklmnopqrstuvwxyz012345678';
 sign_char char:='9';
 base int:=length(alphabet);
 _n bigint:=abs(n);
 output text:='';
BEGIN
 LOOP
   output := output || substr(alphabet, 1+(_n%base)::int, 1);
   _n := _n / base;
   EXIT WHEN _n=0;
 END LOOP;
 RETURN CASE WHEN (n<0) THEN output || sign_char::text ELSE output END;
 RETURN output;
END; $$;

Then you may set the short id slug in a BEFORE INSERT trigger, like this:

CREATE EXTENSION permuteseq;

CREATE TABLE things(
 id serial,
 short_id text,
 name varchar(10) not null
);

-- keep the sequence short for short output strings
ALTER SEQUENCE things_id_seq MAXVALUE 1000000;

CREATE FUNCTION generate_short_id() RETURNS TRIGGER AS $$
DECLARE
 secret bigint := 123456789;  -- change for a different secret sequence
BEGIN
 NEW.short_id := id_to_alpha(range_encrypt_element(NEW.id, 1, 10000000, secret)::int);
 RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER things_trigger BEFORE INSERT ON things
FOR EACH ROW EXECUTE PROCEDURE generate_short_id();

Testing it:

INSERT INTO things(name) SELECT 'foo' FROM generate_series(1,10);
SELECT * FROM things;

 id | short_id | name
----+----------+------
  1 | wf3hg    | foo
  2 | tm6lg    | foo
  3 | riqbg    | foo
  4 | p6jp     | foo
  5 | h3r3c    | foo
  6 | 3sx5d    | foo
  7 | w8ecd    | foo
  8 | km3le    | foo
  9 | llt1e    | foo
 10 | xwtxc    | foo

In this example, there is both id and short_id in the table, but id
is technically redundant with short_id. You could create a sequence
outside of the table without the SERIALsyntax like this, and not
even have id in the table:

CREATE SEQUENCE seq_pk MAXVALUE 1000000;

CREATE TABLE things2(
 short_id TEXT
    DEFAULT id_to_alpha(permute_nextval('seq_pk'::regclass, 123456::bigint)::int)
    PRIMARY KEY,
 name text
);

INSERT INTO things2(name) values('foo');

SELECT * FROM things2;
 short_id | name
----------+------
 znyh     | foo

If you can't use permuteseq, as an alternative still based on
encrypting the ID, you may look at the plpgsql implementation of
SKIP32,
or XTEA for 64-bit (bigint)
which won't require a compilation or being superuser. The drawback is
that you cannot fine-tune the size of the output by changing the
sequence's range.


If you're on SQL Server 2012 or 2014, this is an aggregate that you can calculate with an ordered window function.

SELECT ItemCode, FiscalYear, FiscalMonth,
       SUM(StartingQty+PeriodChangeQty) OVER (
           PARTITION BY ItemCode, FiscalYear
           ORDER BY FiscalMonth
           ROWS UNBOUNDED PRECEDING) AS EndingQty
FROM dbo.theTable;

PARTITION BY defines a partition that spans over all of the rows for the current ItemCode and FiscalYear. Within this partition, you define a window using "rows unbounded preceding" that spans from the first FiscalMonth up to and including the current FiscalMonth. Those are the rows for which you SUM() the starting quantity and the period change.

All of this assumes that the starting quantity is given for the first month of each year and zero for all other months.

Now, all you have to do is apply a PIVOT to this in order to get the output in neat columns.

If you're on SQL Server 2008 R2 or Azure SQL Database, ordered window functions aren't supported. You'll have to resort to a much less pretty join-based solution, which also comes with a potentially hefty performance penalty. Something like:

SELECT a.ItemCode, a.FiscalYear, a.FiscalMonth,
       SUM(b.StartingQty+b.PeriodChangeQty) AS EndingQty
FROM dbo.theTable AS a
INNER JOIN dbo.theTable AS b ON
    a.ItemCode=b.ItemCode AND
    a.FiscalYear=b.FiscalYear AND
    a.FiscalMonth>=b.FiscalMonth
GROUP BY a.ItemCode, a.FiscalYear, a.FiscalMonth;

Note: This query assumes that (ItemCode, FiscalYear, FiscalMonth) is unique.

I haven't tested these queries, so they may contain spelling (or thinking) errors. Best of luck.


How to tell if `CLUSTER table USING index` finished?

To find out whether or not you have any executing CLUSTER commands, simply run this..

SELECT * FROM pg_stat_activity
WHERE query ~ '^CLUSTER'
  AND state = 'active';

However, that doesn't tell you if it already finished on a table. I don't think you can figure that out. You can only see

whether or not a CLUSTER is running.

SELECT * FROM pg_stat_activity AS psa
WHERE query ~* '^CLUSTER'
  AND state = 'active';

if one is currently running, what table it was running on. This requires more work, we look at the locked tables the transaction is using to figure this out.

SELECT *
FROM pg_stat_activity AS psa
JOIN pg_locks AS pl
  ON (pl.pid = psa.pid)
WHERE query ~* '^CLUSTER'
  AND state = 'active'
  -- replace with table
  AND relation::regclass = 'MyTable'::regclass
  AND mode = 'AccessExclusiveLock';


Tips for catching changes in table data over time interval for export to Data Warehouse

The simplest way to handle this may be a bit field like IsDirty that is updated with a trigger, and set to 0 for the whole table when you run your ETL.

If this is set to 1 for every row that is updated, and you give it a default value of 1, then you can make export views for your ETL that filter on IsDirty = 1 so you only interact with new rows.

If you need to know when data was changed and/or have multiple ETL processes hitting the same data, then you will need to have a datetime field and store export dates and times in your config table.


How to do a Join with optionally matching columns

SELECT c1.acreage Maize,
       c2.acreage Paddy,
       COALESCE(c1."date",c2."date") "date",
       COALESCE(c1.region, c2.region) region
FROM Maize c1
FULL OUTER JOIN Paddy c2
    ON c1."date"=c2."date" AND c1.region=c2.region
ORDER BY "date";

fiddle


According to the Microsoft Docs page for the EXISTS operator:

  EXISTS returns TRUE if a subquery contains any rows.

The PostgreSQL documentation page says this about the exists function:

  The argument of EXISTS is an arbitrary SELECT statement, or subquery. The subquery is evaluated to determine whether it returns any rows. If it returns at least one row, the result of EXISTS is "true"; if the subquery returns no rows, the result of EXISTS is "false".

Therefore, an EXISTS statement is used as a boolean expression to determine if some subset of rows exists for a given situation.

Take the following example:

CREATE TABLE table1
(
    key_value int
);

INSERT INTO table1 (key_value)
VALUES (0)
    , (1)
    , (2)
    , (3);

CREATE TABLE table2
(
    key_value int
);

INSERT INTO table2 (key_value)
VALUES (0)
    , (2);

So, table1 contains 4 rows, and table2 contains 2 rows.

This query will show results from table1 where the key_value matches any row in table2 that has the same key_value:

SELECT *
FROM table1
WHERE EXISTS (
    SELECT 1
    FROM table2
    WHERE table2.key_value = table1.key_value
    );

╔═══════════╗
║ key_value ║
╠═══════════╣
║ 0         ║
║ 2         ║
╚═══════════╝

This is similar to a simple INNER JOIN in that only rows where key_value matches in both tables are returned, however there are several key differences:

The results only show columns contained in table1, whereas an INNER JOIN query like the one below returns columns from both tables.

SELECT *
FROM table1
    INNER JOIN table2
        ON table1.key_value = table2.key_value;

    ╔═══════════╦═══════════╗
    ║ key_value ║ key_value ║
    ╠═══════════╬═══════════╣
    ║ 0         ║ 0         ║
    ║ 2         ║ 2         ║
    ╚═══════════╩═══════════╝

If there are multiple rows in table2 that match a single row in table1, only a single row is returned by the EXISTS query, whereas multiple rows (the cartesian product of matching rows in table1 and table2) would be returned by the INNER JOIN query.  Let's insert another row into table2 so we have two rows where key_value is 2:

INSERT INTO table2 (key_value)
VALUES (2);

SELECT *
FROM table1
    INNER JOIN table2
        ON table1.key_value = table2.key_value;

 ╔═══════════╦═══════════╗
 ║ key_value ║ key_value ║
 ╠═══════════╬═══════════╣
 ║ 0         ║ 0         ║
 ║ 2         ║ 2         ║
 ║ 2         ║ 2         ║
 ╚═══════════╩═══════════╝

The EXISTS version of the query still only returns two rows:

SELECT *
FROM table1
WHERE EXISTS (
    SELECT 1
    FROM table2
    WHERE table2.key_value = table1.key_value
    );

        ╔═══════════╗
        ║ key_value ║
        ╠═══════════╣
        ║ 0         ║
        ║ 2         ║
        ╚═══════════╝


Automate SQL Server restore from Prod to Dev

Assuming your full backups are always done WITH INIT:

  SELECT TOP (1) @disk = f.physical_device_name
    FROM msdb.dbo.backupmediafamily AS f
    INNER JOIN msdb.dbo.backupset AS s
    ON f.media_set_id = s.media_set_id
    WHERE s.database_name = @db
      AND s.[type] = N'D'
    ORDER BY backup_finish_date;


Sql Server 2008 R2 - Failed to send an environment change notification to a log shipping partner node

I guessed at several things in the comments above, but the one that hit home was the connection string having the failover partner attribute.

This could actually be what is telling SQL Server to go validate the login on the partner (and yes, I realize that is for mirroring, but this wouldn't be the first time SQL Server's error messages could be misleading).

It is possible that the login no longer has access to the database on the partner (e.g. their rights have been removed) or that the database is offline or no longer exists. Which database? Could be the login's default database (check both instances), the database specifically hard-coded into the connection string, or possibly a database mentioned in a logon trigger or classifier function.


How to quickly startup/shutdown Oracle 11?

You can use the dbstart/dbshut scripts which come with an Oracle install. They are available under $ORACLE_HOME/bin.

After a fresh install you have to edit the /etc/oratab file:

# cat /etc/oratab
# format: $ORACLE_SID:$ORACLE_HOME:N|Y
my_sid:/home/juser/app/juser/product/11.2.0/dbhome_1:N
# sed -i 's/:N$/:Y/' /etc/oratab
# grep my_sid /etc/oratab
my_sid:/home/juser/app/juser/product/11.2.0/dbhome_1:Y

Then you can use the scripts like this:

$ whoami
juser
$ dbstart $ORACLE_HOME
$ # execute DB jobs ...
$ dbshut $ORACLE_HOME

dbstart brings all up which is needed for Pro*C/OCI programs.

Using dbstart/dbshut is an improvement above the custom method mentioned in the question:

method                time    called tools
―――――――――――――――――――――――――――――――――――――――――――――――――――――
dbstart              5.7 s    lsnrctl, sqlplus
dbshut               5.7 s    lsnrctl, sqlplus
custom startup      27.9 s    lsnrctl, sqlplus, emctl
custom shutdown     31.0 s    lsnrctl, sqlplus, emctl

(times on a Core i7/2.8GHz system, slow spinning hard disk.)

How dbstart/dbshut work

A dbstart $ORACLE_HOME$ call is basically equivalent to:

$ lsnrctl start
$ echo -e 'connect / as sysdba\nstartup\nquit'| sqlplus /nolog

And a dbshut $ORACLE_HOME$ is basically equivalent to:

$ lsnrctl stop
$ echo -e 'connect / as sysdba\nshutdown\nquit'| sqlplus /nolog

(you can verify if everything is shutdown via ps aux | grep 'tnsl\|ora')

Note that the order of the commands is important. That means when lsnrctl start is executed after the sqlplus-startup command then the Pro*C/OCI program still complains about an unavailable TNS-listener.

And this is exactly the problem with the command sequence in the question - where the emctl start just workarounds the wrong order because it fixes the TNS-listener setup part.

Also note that for executing Pro*C/OCI programs the EMCTL service is not needed.


No CREATE_TIMEDATE column, can I still export records inserted within last 24 hours?

In SQL Server 2005 there is no hidden column for the date something was added to the table.

the best solution would be to add a new DATETIME column to the tables you are looking to export the last 24 hours if this is to be an ongoing process (you could put an insert trigger on the table to make sure the column is populated).  Entries from before you started tracking the creation date can remain NULL or be set to something like '1900-01-01' (the "0" date in SQL Server).

If changes to the table are not permitted then the only option is to query the table at midnight and then again at midnight the next day (a SQL Agent job would be the best way to accomplish that).  However this only works if the primary key is always increasing in some manner (Identity column for example).  Otherwise you wont know what range you need to select, (IE if your primary key is email for some reason you wont know when a record was inserted based on the sequence).

I would also suggest a table to hold those results from the query every 24 hours so if you need to rerun the export for a specific day you would have the information you needed to do that.


MIN will return the smallest non-NULL value.  The query won't throw an error, it will simply ignore the NULL values

SQL> with x as (
  2    select null dt from dual union all
  3    select sysdate from dual
  4  )
  5  select min(dt)
  6    from x;

MIN(DT)
---------
21-OCT-11


How to make large "in" queries in C# for an indexed field in MSSQL?

select id from table where field in (gigantic list of ordered longs)

Queries with long IN lists are slow, as they are parsed and compiled each call.
Either pass the list as a JSON array:
select id from table where field in (select value from openjson(@values))

Or use a Table-Valued Parameter or bulk load a temp table with the values. Ensure there is an index or primary key. The OPENJSON method will likely be slower, but the upside is that it works with all client drivers.  TVP and Bulk Insert are not always available except in .NET and Java.


How to verify a SSAS Backup and how to perform Integrity Checks in SSAS?

I found a post by Darren Gosbell, a SQL Server MVP (at the time at least).  His post can be found here:

http://geekswithblogs.net/darrengosbell/archive/2008/09/13/ssas-verifying-backups.aspx

He points out that you cannot run this from your desktop, but you can run it on the server to do the verification.  The instructions from 2008 are:

Navigate to:   <Program Files>\Microsoft SQL Server\MSAS10.SQL08\OLAP\bin

Then type:  msmdsrv /?

Look at the commands available with msmdsrv and see what you want to do.  (I am away from a SSAS installation at the moment, so I cannot verify, but it should be worth checking out.)

EDIT:
2) How to run a DBCC CheckDB in SSAS? (database integrity)

Starting with SQL 2016 we have a DBCC CHECKDB command for SSAS for Dimensional as well as Tabular

  DBCC provides on-demand database validation for Multidimensional and
  Tabular databases on an Analysis Services instance. You can execute
  DBCC in an MDX or XMLA query window in SQL Server Management Studio
  (SSMS) and trace the DBCC output in either SQL Server Profiler or
  xEvent sessions in SSMS. The command takes an object definition and
  returns either an empty result set or detailed error information if
  the object is corrupted. In this article, you’ll learn how to run the
  command, interpret results, and address any problems that arise.


How to create Oracle RAC database using dbca silent mode with different db_name and db_unique_name

Since you asked RAC, please see if you can use this non-RAC example as a starting point.
For db_name != db_unique_name != sid
- Non-RAC
- Oracle 12.1
- Custom Database (not seed)

On the command line, set ddbname == parameter db_unique_name, and also set parameter db_name

dbca    \
-silent \
-createDatabase \
-templateName    wailua.dbt     \
-sid wailua20           \
-gdbname wailuaq        \
-initParams db_unique_name=wailuaq,db_name=wailua       \

where wailua.dbt is copied from New_Database.dbt

db_name and db_unique_name are not mentioned in wailua.dbt

Note that variables
SID and DB_UNIQUE_NAME expand in the template.

[oracle@waipio1 wailua20 templates]$ echo $ORACLE_SID
wailua20

[oracle@waipio1 wailua20 templates]$ sqlplus / as sysdba

SQL*Plus: Release 12.1.0.1.0 Production on Wed Jun 11 00:48:22 2014

Copyright (c) 1982, 2013, Oracle.  All rights reserved.

Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Advanced Analytics
and Real Application Testing options

SQL> show parameter db_name

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_name                              string      wailua
SQL> show parameter db_unique_name

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_unique_name                       string      wailuaq

oratab:
wailua20:/app/oracle/product/12.1.0/db_1:N:         # line added by Agent

ASM:
asmcmd ls data/WAILUAQ/
CONTROLFILE/
DATAFILE/
ONLINELOG/
PARAMETERFILE/
TEMPFILE/
spfilewailua20.ora

Full crdb.wailua.ksh dbca script: http://pastebin.com/vx3k5g3U/

dbca log: http://pastebin.com/8UQDEDZn/


Design question about student assessment data

The first thing you want to do to keep your thinking straight is to choose some clear terminology.  You are using the word "assessment" in two different contexts: (i) a Test and (ii) the results for a student taking the test.  I understand that "test" may not be the best term, since you might be assessing based on lots of different kinds of work.  The important thing is that you need to clearly differentiate between a measuring instrument and the results of an individual student against that measuring instrument.

Keeping these concepts separate will help you to keep your thinking straight when designing your database.

I'd recommend that all of your RESULTS be kept in a single table.  That will make things like report cards easier to handle.  Whether the result is a percentage, a letter grade or a pass/fail, you probably want to have a common representation (percentage is a good lowest common denominator) and a weight to help you aggregate marks across assessments.


Backing up my SQL Server System Databases

If you just want to back up the system databases, you should use the "System Databases" group in the drop down menu as opposed to individually selecting the system databases.

If you have not looked at the Ola Hallengren SQL Maintenance solution, I would recommend taking a peek as opposed to using the SQL Server maintenance plans.

More info about maintenance plans: TN Maintenance plans


Calculate Return Value - 1,3,6 Month Returns

I don't think your code will return any rows as the WHERE clause will never return true.

SELECT PriceValue FROM @Output WHERE ValuationDate = DATEADD(MONTH,-3,ValuationDate)

This would only make sense if the ValuationDate value inside the DATEADD function was a literal value or a variable like below:

SELECT PriceValue FROM @Output WHERE ValuationDate = DATEADD(MONTH,-3, '20160930')

or

SELECT PriceValue FROM @Output WHERE ValuationDate = DATEADD(MONTH,-3, @MyValuationDate)

I'm also assuming that ValuationDate is a DATE type column and not a DATETIME or DATETIME2.

But I think I understand your question and the code below should be what you need:

SELECT PriceValue
FROM @Output
WHERE ValuationDate = EOMONTH(DATEADD(MONTH, -3, @MyValuationDate))


Firtsly, I hope you have set the indexes. At the other hand, If you don't use db.repairDatabase() try it when you're in maintanance mode. That command will re-indexing and doing many performance procedures.

If you want to more details; repairDatabase()


MySQL compare values of two large tables

Looks like a mismatch between the CHARSET on the two tables. Try setting the same CHARSET and COLLATION on both tables.


During installation can't change directory

There are two methods available, either use ZIP archive to install or you may have to change the datadir variable in my.ini after installation. You can use following steps

Complete the installation
Identify the config file and then look for related datadir being used, you may used mysql>show variables like ‘datadir’ within mysql to identify datadir or the service details for concerned my.ini
Stop the service if it is already running
Change the value of datadir in my.ini, it is usually placed under C:\ProgramData\MySQL
Copy/move the folder contents to newer location
Make sure permissions are provided to user who owns mysql service
Start service and verify using mysql>show variables like ‘datadir’

Hope it helps


You can add a calculated property to you output like this

Get-Process | Sort CPU -descending | Select -first 10 @{l="Time";e={get-date}},ID,ProcessName,CPU

The syntax for adding a calculated property is

 @{                  - start a script block
   l = 'label';      - choose a label for the property (not mandatory)
   e = {expression}  - formulate the expression you like evaluated
 }                   - close the script block


select rows having condition met for group (without temporary table)

GROUP BY cannot be used alone because it only returns 1 row per group (category).

You can use a sub query with flag = 1 and INNER JOIN:

SELECT d1.ID, d1.category, d1.flag
FROM data d1
INNER JOIN (
    SELECT DISTINCT category FROM data WHERE flag = 1
) d2
    ON d2.category = d1.category ;

You can use the EXISTS clause:

SELECT d.ID, d.category, d.flag
FROM data d
WHERE EXISTS (
    SELECT 1 FROM data WHERE flag = 1 AND category = d.category
) ;

You can use IN clause (although EXISTS is better):

SELECT d.ID, d.category, d.flag
FROM data d
WHERE d.category IN (SELECT category FROM data WHERE flag = 1) ;

You can also use CROSS APPLY with a sub query on flag = 1:

SELECT d.ID, d.category, d.flag
FROM data d
CROSS APPLY (
    SELECT TOP (1) category
    FROM data
    WHERE flag = 1 AND category = d.category
) ca ;

DISTINCT aren't needed if, for each category, only 1 row can have flag = 1.

Output:

ID  category    flag
1       A       1
2       A       0
3       A       0


Right Trimming Binary Data in SQL Server

This looks kind of unsafe, but it turns out that CONTEXT_INFO will eat empty strings and give you back a NULL anyway:

SET @string = LEFT(@string, CHARINDEX(CONVERT(varchar(1), 0x00), @string, 1) - 1);

Or, it turns out you can directly specify a binary value to search for in the REPLACE function:

SET @string = REPLACE(CONVERT(VARCHAR(128), @binary), 0x00, '');

This of course won't trim only trailing null characters, but I assume would be sufficient.

Note: neither of these solutions (nor the one in the comments) work if you switch to using nvarchar(64).


Get list of all tables and columns "touched" by application

It sounds like, for this particular scenario, you should run a trace in SQL Profiler. You can open it from the Tools menu in SSMS or find it in the Start > Applications > SQL Server 2008 R2 > Performance Tools folder.

Create a new trace and connect it to your database. Use the "blank" template and in the Events Selection tab just select Stored Procedures > RPC:Completed and TSQL > SQL:BatchCompleted.

Pause (but not stop) your trace until you're ready to click something, then start the trace back up and see exactly what queries the application produces. If you're still getting a lot of noise in your traces, you can use the column filters and the CientProcessID or SPID fields will probably help you nail it down.


I have had this problem. Sometimes I have had to solve it the hard way. But others I simply take a large enough number and add it to ALL the numeric keys in one of the databases. Then I merge the two databases.
Let's say if my biggest key in BD1 is 1213456, I add 1000000 to all the keys in BD2.
You can prepare your scripts querying information_schema to generate all modifications/alterations/updates. Something like (only PK)
SELECT CONCAT('UPDATE ', U.TABLE_SCHEMA,'.', U.TABLE_NAME, ' SET ', U.COLUMN_NAME, ' = ',U.COLUMN_NAME,' + 100000' )
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS    C
    JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE U
        ON U.CONSTRAINT_NAME = C.CONSTRAINT_NAME
           AND U.CONSTRAINT_SCHEMA = C.CONSTRAINT_SCHEMA
           AND U.CONSTRAINT_NAME = C.CONSTRAINT_NAME
WHERE C.CONSTRAINT_TYPE = 'PRIMARY KEY'

Will return queries like UPDATE dbo.Table1 SET id = id + 100000
And running results queries when you feel confident enough. Good luck!


Allows to select on view, while hiding its definition

You cannot hide the view definition from the user without modifying the database metadata in a way that will break things. But it is no problem to show the user the view definition.

What is more important is to define the view with security_barrier = true so that the user cannot use functions with side effects to get at the underlying tables.

If you want to hide the query used to extract the data, your best choice might be to write a C function that produces the data. If the user has no access to the database server, that should be safe.


A full export/import contains tablespace definitions with datafile paths as well, so impdp tries to create the datafiles with the original path.

Just create the tablespaces manually before running impdp, that is usually what I do. impdp will notice that the tablespaces already exist, and continue with the remaining objects.

You can extract the tablespace creation scripts as well if you want, using the sqlfile clause:

impdp user/password directory=... dumpfile=... sqlfile=ts.sql include=TABLESPACE

The above creates a ts.sql file containing the CREATE and ALTER statements for your tablespaces, which you can edit or use for creating the tablespaces.


Querying for a specific language and if not found use default one instead

Giving next example:

create table a (id int, description text);
create table b (id int, lang text, description text, default_lang int);
insert into a values (1, 'Yo vivo en Barcelona');
insert into b values
(1, 'en-US', 'I am living in Barcelona', 0),
(1, 'es-AR', 'Yo vivo en Barcelona', 1);

If you table have a default text for the default language you could use a LEFT JOIN and COALESCE in this way:

select
    a.id,
    coalesce(b.description, a.description) as description
from
    a
left join
    b
    on b.id = a.id and b.lang = 'en-US';

id | description
-: | :-----------------------
 1 | I am living in Barcelona

Or you could add a default_lang column in translations table and use a LATERAL JOIN with LIMIT 1:

select
    a.id,
    coalesce(b.description, a.description) as description
from
    a
join lateral
    (select id, description
     from   b
     where  lang = 'en-EN' or lang = 'es-AR'
     order by default_lang
     limit 1) b on true

id | description
-: | :-------------------
 1 | Yo vivo en Barcelona

db<>fiddle here


How to use GROUP BY and result with highest id value

For the one row with the highest ID:

ORDER BY ID DESC LIMIT 1

For the "latest" information associated with each subject, the problem is considerably more complex.  Search for "groupwise max".  Here's one link:
http://mysql.rjweb.org/doc.php/groupwise_max


SET IDENTITY_INSERT OFF - Caused Table Lock

SET IDENTITY_INSERT is just a session option allowing you to insert identity values explicitly in a subsequent statement. It doesn't do anything to the transaction log. I presume your first transaction wasn't actually committed and now is rolling back.

This could happen if you have implicit_transactions on

Or a possibly more likely scenario is that you were already in an open transaction unknowingly.

This could happen if your original script contained an error between the begin tran and commit. Executing could then terminate execution leaving an open transaction (If you had used SET XACT_ABORT ON any error would have rollbacked the transaction rather than leaving it open.)

Then after fixing the error and rerunning the script in the same session the begin tran would be called again and increment @@trancount and the COMMIT would just decrement it down to 1, leaving the transaction still open.

Rather than killing the session and causing transaction rollback the correct thing to do in that case (assuming you were happy that everything was OK and the original error condition hadn't caused changes that you wouldn't want to commit) would have been to check @@trancount and commit again as many times as needed (I.e. in the event that there were multiple executions before the error was fixed) until that reached zero.


Assistance creating SQL query in Oracle to split by weeks

To get the week number of a date use:

to_char(sysdate, 'WW')

You can group on this:

select   to_char(ORDER.DATE, 'WW') as WEEK
         Sum(ORDER_TOTALS.ORDER_TOT_NET)  As "ORDER_TOT_NET",
         Sum(ORDER_TOTALS.ORDER_TOT_WGHT) As "ORDER_TOT_WGHT",
         Sum(ORDER_TOTALS.ORDER_TOT_QTY)  As "ORDER_TOT_QTY",
         Sum(ORDER_TOTALS.ORDER_TOT_SQM)  As "ORDER_TOT_SQM"
From     ORDER ORDER, ORDER_TOTALS ORDER_TOTALS
Where    ORDER.ORDER_NO = ORDER_TOTALS.ORDER_NO
group by to_char(ORDER.DATE, 'WW');

Instead of WW you can also use IW. The last one gives the ISO Week. Here the beginning or the end of the year might end up in the week of the previous/next year. Look for it in Wikipedia (https://en.wikipedia.org/wiki/ISO_8601) for more information on this.


It will do a full table scan.

The columns in an index are ordered.  Think about finding a person in a list of people where the list is sorted by last name, but all you know is the first name.

PRIMARY KEY(b,a)

would be somewhat good for WHERE b=1 AND c=2 -- it could at least narrow it down to all the rows where b=1.  That is, it would do a "range scan" of only part of the table.

INDEX(b,c)

would (probably) work better (given that you leave the PK at (a,b)).  This because it would drill down that secondary index's BTree to exactly the row(s) with (1,2), then reach over into Data+PK to find the rest of the columns needed.

No, don't even consider MyISAM these days.  There are more drawbacks than this one advantage -- if it even is an advantage.

See Cookbook for Creating Indexes.

More

The "left" part (b,a in this example) of the index can be directly used.

PRIMARY KEY(b,a,c,d) would benefit where b=1 and a=2 and d=3, but only because of b and a.  The BTree (actually a B+Tree) can efficiently be scanned.  The code would drill down to where b=1 and a=2 starts, then scan forward until b=1 and a=2 no longer applies.  c is in the way of making use of d=3, though it will be checked.

An InnoDB BTree are composed of 16KB block(s).  If you have only a few rows, every thing will be in one block.  If you have a billion rows, there may be many blocks for the b=1 entries, and the BTree may be about 5 physical levels deep of nodes pointing to sub-nodes.  The B+Tree means that it can scan one leaf node, then step right to the next leaf node.  A rule of thumb:  a block has 100 entries in it.  (In reality, it is anywhere from 1 to at least 1000, depending on a lot of things.)

One thing to remember in dealing with a MySQL PRIMARY KEY -- it is UNIQUE.  So don't randomly add/subtract columns from the definition; you will change what it is testing for uniqueness.


Why is ALTER faster than UPDATE (for full table rewrites)?

Both do rather different things under the hood.
ALTER TABLE takes an exclusive lock on the table and rewrites it. The manual:

The rewriting forms of ALTER TABLE are not MVCC-safe. After a table
rewrite, the table will appear empty to concurrent transactions, if
they are using a snapshot taken before the rewrite occurred. See
Section 13.5 for more details.

UPDATE only takes a write lock on affected rows (all in this case) and adds new row versions to the table, roughly doubling its physical size (unless it can reuse dead space). Concurrent readers can proceed happily.
Table-rewriting forms of ALTER TABLE do not write to the WAL. UPDATE, OTOH, also has to write to the WAL first. That's also why UNLOGGED tables can UPDATE in roughly half the time: no WAL by definition.
The fiddle demonstrates some effective differences:
db<>fiddle here


When is data moved during an Oracle partition split?

It won't move the data.

Test case:

SQL> CREATE TABLESPACE myts1 DATAFILE '/u01/app/oracle/oradata/ORA112/myts1_01.dbf' size 50M;

Tablespace created.

SQL> CREATE TABLESPACE mytsmax DATAFILE '/u01/app/oracle/oradata/ORA112/mytsmax_01.dbf' size 50M;

Tablespace created.

SQL> CREATE TABLE mytable (id NUMBER, dt DATE)
PARTITION BY RANGE (dt)
  (PARTITION mytablep1 VALUES LESS THAN (TO_DATE('2013-01-01', 'YYYY-MM-DD')) TABLESPACE myts1,
   PARTITION mytablep2 VALUES LESS THAN (TO_DATE('2013-02-01', 'YYYY-MM-DD')) TABLESPACE myts1,
   PARTITION mytablep3 VALUES LESS THAN (TO_DATE('2013-03-01', 'YYYY-MM-DD')) TABLESPACE myts1,
   PARTITION mytablep4 VALUES LESS THAN (MAXVALUE) TABLESPACE myts1
  );

Table created.

I inserted some data. The results:

SQL> select dt, count(*)
  2  from mytable
  3  group by dt;

DT      COUNT(*)
--------- ----------
11-NOV-12     262272
11-JAN-13     262272
11-FEB-13     262272
11-MAR-13     262272

Now, the best way of working out if a row has moved is to look at its rowid. The rowid for a given row is based on the location of the row in a datafile and block. Therefore, if the rowid changes it means the row has moved! Documentation link.

So, create a table to hold the rowids of the existing rows in the table, and insert them:

SQL> create table myrowids ( r rowid );

Table created.

SQL> insert into myrowids ( select rowid from mytable );

1049088 rows created.

SQL> commit;

Perform the split:

SQL> ALTER TABLE mytable
SPLIT PARTITION mytablep4
AT ( TO_DATE('2013-04-01', 'YYYY-MM-DD') )
INTO ( PARTITION mytablep4 TABLESPACE myts1, PARTITION mytablep5 TABLESPACE mytsmax );

Table altered.

SQL>

Check that the rows haven't moved (could have used an EXISTS instead):

SQL> select count(*) from myrowids where r not in ( select rowid from mytable );

  COUNT(*)
----------
     0

SQL>

They haven't!


Is it possible to copy a dev. database schema over a prod. one, but keep the prod. database' data?

To do this in any automated manner, you need to change your development model. Instead of just keeping the dev database as the current state and using a dump of its structure to build new databases, you should instead create scripts that transform one database version to the next - adding/dropping/altering objects as needed, and transforming data as required by those changes (new columns may need initial values, moving data to a new structure before dropping and old one, etc.). These scripts should be kept in good source control of course.

This way you can take your production database (at, say, version 2.5) and to get it up to the current development release (say, 2.8) apply the upgrade scripts for 2.5->2.6, 2.6->2.7, 2.7->2.8. Of course you can test this process in a test environment first as it should be repeatable (reset test environment to match current production, upgrade, test the application, fix upgrade scripts if something fails, repeat until everything is OK).

At some point this process will become inefficient for creating brand new databases for other instances of the application (i.e. building from the very first version onwards may become unwieldy after time) so you'll want to refactor the process slightly, picking a new starting point and building from there each time.

There are tools to help with the more manual process that you describe, but you will be much better off in the long run not arranging your release process around less automatable actions for managing schema changes. Setting up the process may seem like hassle, but it is a lot easier to do at this stage than after you have real production environments to worry about.


DBMS_RESOURCE_MANAGER limit IOPS or MBPS

By making those processes slower you als stretch the period with pain for both the regular users as well as the etl processes. We normally solve this by assigning a dedicated replica to the etl/reporting processes. That replicate can be tuned to better support the etl processing and it removes the need to put the extra pressure on the production database.
Resource manager can be used to prevent long running queries from starting at all. Problem here is that the estimates it uses are not always correct which might cause regular queries to be rejected. So be careful.


How to count more than one time with different conditions?

You can use SUM(CASE...)

SELECT
    ParentID,
    SUM(CASE WHEN SomeValue = 1 THEN 1 ELSE 0 END) AS CountMin,
    SUM(CASE WHEN SomeValue <> 1 THEN 1 ELSE 0 END) AS CountRest
FROM
    MyTable
WHERE
    ParentID = 123;


MySQL Cluster: Problem Connecting the SQL node

So the solution was the conflicting myslqd. Apparently mysql has a mysqld and mysql cluster has a mysqld and they were conflicting or I was running the wrong one. In the end I uninstalled mysql and reinstalled mysql cluster and it worked perfectly.
Big thanks to RolandoMySQLDBA for helping me troubleshoot this issue.


Searched a bit more and I found the solution:

[mysqld]

tmpdir=[other drive]:/tmp

Now it's writing to the tmp directory on the wanted drive.


T SQL Table Valued Function to Split a Column on commas

I wouldn't do this with a loop; there are much better alternatives. By far the best, when you have to split, is CLR, and Adam Machanic's approach is the fastest I've tested.

Next best approach IMHO, if you can't implement CLR, is a numbers table:

SET NOCOUNT ON;

DECLARE @UpperLimit INT = 1000000;

WITH n AS
(
    SELECT
        x = ROW_NUMBER() OVER (ORDER BY s1.[object_id])
    FROM       sys.all_objects AS s1
    CROSS JOIN sys.all_objects AS s2
    CROSS JOIN sys.all_objects AS s3
)
SELECT Number = x
  INTO dbo.Numbers
  FROM n
  WHERE x BETWEEN 1 AND @UpperLimit
  OPTION (MAXDOP 1); -- protecting from Paul White's observation

GO
CREATE UNIQUE CLUSTERED INDEX n ON dbo.Numbers(Number)
    --WITH (DATA_COMPRESSION = PAGE);
GO

... which allows this function:

CREATE FUNCTION dbo.SplitStrings_Numbers
(
   @List       NVARCHAR(MAX),
   @Delimiter  NVARCHAR(255)
)
RETURNS TABLE
WITH SCHEMABINDING
AS
   RETURN
   (
       SELECT Item = SUBSTRING(@List, Number,
         CHARINDEX(@Delimiter, @List + @Delimiter, Number) - Number)
       FROM dbo.Numbers
       WHERE Number <= CONVERT(INT, LEN(@List))
         AND SUBSTRING(@Delimiter + @List, Number, 1) = @Delimiter
   );
GO

I believe all of these will perform better than the function you have, when you get it working, especially since they are inline instead of multi-statement. I haven't investigated why yours isn't working, because I don't think it's worth it to get that function working.

But that all said...

Since you are using SQL Server 2008, is there a reason you need to split in the first place? I would rather use a TVP for this:

CREATE TYPE dbo.strings AS TABLE
(
  string NVARCHAR(4000)
);

Now you can accept this as a parameter to your stored procedures, and use the contents just like you would use a TVF:

CREATE PROCEDURE dbo.foo
  @strings dbo.strings READONLY
AS
BEGIN
  SET NOCOUNT ON;

  SELECT Asset_ID = string FROM @strings;
  -- SELECT Asset_ID FROM dbo.utvf_split(@other_param, ',');
END

And you can pass a TVP directly from C# etc. as a DataTable. This will almost certainly outperform any of the solutions above, especially if you are building a comma-separated string in your app specifically so that your stored procedure can call a TVP to split it apart again. For a lot more info on TVPs see Erland Sommarskog's great article.

More recently, I've written a series on splitting strings:

http://sqlperformance.com/2012/07/t-sql-queries/split-strings
http://sqlperformance.com/2012/08/t-sql-queries/splitting-strings-follow-up
http://sqlperformance.com/2012/08/t-sql-queries/splitting-strings-now-with-less-t-sql

And if you are using SQL Server 2016 or newer (or Azure SQL Database), there is a new STRING_SPLIT function, which I blogged about here:

Performance Surprises and Assumptions : STRING_SPLIT()
STRING_SPLIT() in SQL Server 2016 : Follow-Up #1
STRING_SPLIT() in SQL Server 2016 : Follow-Up #2


How to check which tables in DB (MYSQL) updated in last 1 hour / last 1 minute?

There are two distinct answers I have to give by Storage Engine

MyISAM

For all MyISAM tables, checking the last time a table was updated is quick and dirty

SET @IntervalMinutes = 2;
SET @IntervalSeconds = @IntervalMinutes * 60;
SET @DBtoCheck = DATABASE();
SELECT table_schema,table_name,update_time
FROM information_schema.tables WHERE engine='MyISAM'
AND table_schema = @DBtoCheck
AND update_date > (NOW() - INTERVAL @IntervalSeconds SECOND)
ORDER BY update_time DESC A;

InnoDB

I have some bad news: you cannot trust the INFORMATION_SCHEMA database because

Transaction Behavior of InnoDB
Location of the Data and Index Pages
Heavy duty locking of the INFORMATION_SCHEMA when reading about an InnoDB table

In order to know the last update by some timestamp, you must have innodb_file_per_table enabled. If not, all the data and index pages will sit inside ibdata1 (system tablespace). It would be anybody's guess when a table experienced an update.

You should first cleanup the InnoDB Infrastructure.

Then, the easiest way to get the timestamp is to ask the OS for it

EXAMPLE

Given this scenario

datadir is /var/lib/mysql
you want timetamp of mydb.mytable

Simply run this in the OS

TS_TAB=`ls -l --time-style="+%s" /var/lib/mysql/mydb/mytable.ibd | awk '{print $6}'`
TS_NOW=`date +%s`
(( TS_DIFF = TS_NOW - TS_TAB ))

TS_DIFF will be the number of seconds elapsed since the last time a write was posted to that table. That write can be anything (INSERT, UPDATE, DELETE, DDL).

That's as fast as I can get it for InnoDB

Give it a Try !!!


How to Replicate data between a hosted database and a local database

There's no replication or out-of-the-box solution to your problem, particularly since you don't have that type of access to the remote machine. As other commenters here have noted, you're probably going to have to build your own ETL logic, perhaps in one of the following ways:

A linked server and stored procedures using OPENQUERY() or OPENROWSET(),
An SSIS package, or
A custom-built local application.

The "last updated" columns in the remote tables will help you considerably, in that you won't have to download the entire database's contents every time you run the ETL process. Without being able to install or modify anything on the remote server, there's no practical way to synchronize in "real time". The frequency and speed that you can synchronize the databases will probably come down to:

Indexing on the remote database (how efficiently you can retrieve new records)
Workload on the remote server (locks, I/O speed, available CPU)
Link speed (how fast your VPN is)

I would set a reasonable expectation of synchronizing the data a few times per day, for example early morning, lunch, late afternoon and/or evening. Make sure to plan those executions so they don't collide unneccessarily with running backup jobs (locally, and if you can, remote). Involve the remote party so you don't overload their servers.

T-SQL example

Supposing that you use stored procedures and linked servers, here's an idea on how to go about it. First off, if existing data never changes, i.e. rows are only added (say, like accounting transactions or something):

DECLARE @lastUpdated   datetime2(3),
        @sql           varchar(max);

--- What's the most recent row we've downloaded?
SELECT @lastUpdated=MAX(lastUpdated)
FROM dbo.tableName;

--- Build dynamic SQL
SET @sql='
    INSERT INTO dbo.tableName (x, y, z, lastUpdated)
    SELECT x, y, z, lastUpdated
    FROM OPENQUERY(linkedServerName, ''
        SELECT x, y, z, lastUpdated
        FROM databaseName.dbo.tableName
        WHERE lastUpdated>={ts '''''+CONVERT(varchar, @lastUpdated, 121)+'''''}
        '');
    ';

--- Execute dynamic SQL
EXECUTE sys.sp_executesql @sql;

... and repeat this process for each table. You may run into problems with foreign key constraints, so you may have to arrange the order of the tables carefully, but in the end you may be better off not having any foreign key constraints at all in the local database.

If your existing rows can change, the SQL script looks similar, except you download the data to a temp table first, assign a unique clustered primary key to the temp table, then MERGE ("upsert") the contents of the temp table to your local table. If you don't like or trust MERGE, you can obviously use INSERT and UPDATE the old fashion way. Note that you won't catch rows that have been deleted from the remote table this way.

Using SSIS

With SSIS (or if you decide to build your own local application), the work is the same, but you won't need linked servers, and instead of using sp_executesql, you'll dynamically build SQL statements and run them through an ADODB or similar database connection.

I've included the "build your own application" option for completeness; I wouldn't go down that road.

Indexing considerations

For the remote query to be as efficient as possible, you may want to investigate if you can get the remote DBA to set up an index on the "last updated" column of each table.

Backup option

In the end, I would probably consider Aaron Bertrand's suggestion and try to set up a solution where you download a backup of the remote database (perhaps a full backup, once per night), restore that backup locally and perform the sync operation locally on your server.

Real-time

Returning to the real-time issue - the driving parameter here has to be the business. If management needs up-to-the-second accuracy in your database, somebody is going to have to accept the considerable increase in work and costs, not to mention maintenance. In the end, you may find that a high-speed leased line may be a lot cheaper, and that you can archive the data with a giant download once a month or so instead.


Merits of inserting to temp table and renaming versus just adding/removing columns

I recommend you read SQL Server table columns under the hood. You'll see that many column DDL operations result in having 'ghost' columns in the table, physical columns that are existing in the table but not visible to the user. A rebuild will remove all these ghost columns.

Most times this is benign, but there are some dark areas that can lead to the issues described in KB2504090:

  This issue occurs because the accessor that SQL Server uses to insert data into different partitions recognizes the metadata changes incorrectly. When data is inserted into the new partition that is created after a column is dropped, the number of the maximum nullable columns in the new partition may be one fewer than the number of the maximum nullable columns in the old partition.

I for one shun all schema compare tools and comparison diff based deployment/upgrades. There simply is no one-size-fits-all correct approach with regard to changing schema. Just like Henrik mentions, I've been burned by the 500GB table 'copy', tyvm, no more diffs for me. Instead I recommend migrations, coded as SQL scripts and tested on relevant data size before being deployed. See Version Control and your Database. Rails ActiveRecord Migrations really grok this.


how to select multiple items using the AND operator

Your query as it is will always return 0 rows . PROF field cannot be equal to 3 different values at the same time .
You need something similar to

select dno
from employe
where prof in ('Ingenieur','Technicien','Vendeur')
group by dno
having count(distinct prof) =3 ;

There are couple more ways to get what you want, you may try them and find which one has better performance .


How to JOIN two table to get missing rows in the second table

Use your existing query to get the opposite of the list you want. That list can then be checked against via NOT IN to get the desired list.

SELECT * FROM elections WHERE election_id NOT IN (
    SELECT elections.election_id from elections
    JOIN votes USING(election_id)
    WHERE votes.user_id='x'
)


Is best practice to Web Application users credentials using same database users credentials

It is certainly possible to use database usernames to authenticate web application users, but that is rather uncommon.
End users are typically authenticated at the enterprise level - meaning they use the same identity in a single-sign-on fashion to identify themselves with multiple enterprise applications.
Then in web applications, those identities are mapped to a small number of specific database users with capabilities matching the roles/profiles of the end users.
Having all enterprise users (and much less end users) defined in all databases becomes a logistic nightmare once the number exceeds a few ten's of users. It is definitely not possible once you start having end-users/customers accounts going rapidly into the millions.


SQL Server 2016 Standard Edition Replication

Replication is not a DR technology in SQL Server, so you should never consider that for DR purposes. In SQL 2016 Standard Edition, you should look at Basic Availability Groups.

Setting up a Basic AG for this database will provide you with DR capabilities and will allow for failover without breaking the flow of data between your replicas.


Changes between Mysql version 5.1 TO 5.7

Do a JOIN instead of AND PRODAV.ATTRIBUTE_VALUE_ID IN (select ID ...

FULL_GROUP_BY needs to be heeded.

p:   INDEX(EAN, IS_DRAFT)  -- in this order
pl:  INDEX(PRODUCT_ID)
PRODAV:  INDEX(PRODUCT_ID, ATTRIBUTE_VALUE_ID)  -- in this order (after doing the JOIN)
ATVALS:  INDEX(ID, ATTRIBUTE_CODE)  -- in either order

PRODAV.ATTRIBUTE_VALUE_ID = ATVALS.ID -- If this is a key-value store, do not normalize the key or the value; it will make for terrible performance.

As for why the performance changed between revisions, I can only guess that things like FULL_GROUP_BY messed up the optimization.  Some of my suggestions may uncork it.


mysql - Waiting for table level lock on a table

Perhaps the users table is type/storage engine MyISAM and the others are InnoDB or perhaps the other tables aren't subject to many INSERT, UPDATE, or DELETE queries (IIRC, locking isn't used for SELECT).

See also:

way to prevent queries from waiting for table level lock
https://stackoverflow.com/questions/6543624/what-is-waiting-for-table-level-lock
https://dev.mysql.com/doc/refman/5.0/en/table-locking.html


SQL Server Configuration on Multiple Instance Server

Resource governor is your best best if you really need to restrict resources for each instance, although if that is your major concern, I would just avoid stacking multiple instances.

If splitting resources isn't a big concern, I would configure total memory no more then 36GB, so 16GB per instance is fine. I would still configure 8 tempdb files for each instance, and would probably set max dop to around 6, to be tested against the work load.

This makes sure neither instance, or the OS is unable to get the memory it needs, but by configuring the maxdop higher then half the cores, it will allow processes to use more then 4 cores if available, while still not allowing a single process to dominate the entire machine.

I would still allocate 8 tempdb files to the instance, because without something in place to provide a hard restriction, sql server will still be able to access all 8 cores, and could potentially generate 8 simultaneous tempdb requests.

One other thing I would verify, is that if this is running on sql server standard edition, that your VM is configured with no more then 4 sockets regardless of the number of cores.  Sql server will restrict the cpu usuage to 4 sockets, and if you use single core cpus, you could wind up with both instances trying to share the first 4 CPU's, while the other 4 are left idle.


Creating Synonym For a Table in PostgreSQL over dblink?

That's exactly what foreign data wrappers and foreign tables were created for:

You create a foreign server pointing to the source server, and then a foreign table that enables access to that remote server.

Some examples for that are in the description of the Postgres foreign data wrapper:

https://www.postgresql.org/docs/current/static/postgres-fdw.html


It's not clear what you mean by "extended period of time" or why you associate deleting rows with "taking down" the system.  If it's acceptable (as a matter of policy) to delete rows while the system is in production use, provided the deletions don't negatively affect performance, then the trick is to nibble away at the data.

Take some unit of the data -- an account, a day, a month, something "reasonable" -- as a "unit of deletion".  The number of rows affected doesn't matter (as long as it's > 0); what matters is that the system can execute the deletion without "taking down the system" in terms of production response time.   Write your procedure as one DELETE for each table, taking the deletion unit value as a parameter.  You don't need to wrap them in a transaction if the process can pick up where it left off after an error. Write a loop to call that procedure for successive values.  If desired, sleep between iterations.

For example, start with the oldest month.  Remove associated data for H, G, F, E, D, C, B, and A.  Get the next oldest month, repeat.

Since these are data you only want to remove, there's no data contention with the production system.  It should be able to cook along happily.  Provided your transactions aren't "too big", each individual deletion won't induce resource contention for very long.  Choose well, and the production system should hardly notice.

But you might instead mean that you can't execute your deletion during working hours, that you'll be given the system for 30 minutes in wee hours to do your deed.  That means removing the maximum row/second, a metric which is highly system- and database-dependent.

Likewise, whether it's a good idea to delete millions of rows all in one go is system dependent.  Twenty years ago, that would have been preposterous on any system I'd ever seen.  These days, it's routine on large databases.  All depends on what you're working with.


postgresql update with a subquery limit 1 that has joins sometimes doesn't respect the limit?

In March Erwin Brandstetter revised his answer to mention "The planner may choose to generate a plan that executes a nested loop over the LIMITing subquery, causing more UPDATEs than LIMIT": https://dba.stackexchange.com/posts/69497/revisions

There appear to be two options that actually work for limiting the number of rows updated:

an uncorrelated scalar subquery when you only need a single row, e.g. UPDATE users SET touched_at = now()  WHERE users.id = (SELECT id FROM users LIMIT 1)

OR

if you need multiple rows then a common table expression (CTE) which is always materialized once and is a fence to the optimizer in postgresql (https://blog.2ndquadrant.com/postgresql-ctes-are-optimization-fences/), e.g. WITH cte AS (SELECT id FROM users LIMIT 10) UPDATE users SET touched_at = now()  WHERE users.id = cte.id


CSV or DB is quicker to read several lines from a file with 1,5 billion lines

Two great and very fast tools for structured text files are:

xsv https://github.com/BurntSushi/xsv
tsv-utils https://github.com/eBay/tsv-utils


In theory, you would need to specify an actual tab, possibly using Bash's ANSI C quoting:

$ DBDELIMITER=$'\t' dbexport stores
Invalid delimiter; Don't use '\\', SPACE or HEX or Multibyte chars.
$

Unfortunately, that looks like you're not allowed to use tab either.  (Test: Informix 12.10.FC5 on Mac OS X 10.11.6.)

Incidentally, I tried some alternatives: $'\a' (alert, aka Control-G) and $'\b' (backspace, aka Control-H) are both accepted, but none of $'\f' (formfeed, aka Control-L), $'\v' (vertical tab, aka Control-K), $'\r' (carriage return, aka Control-M) of $'\n' (newline, aka Control-J) are allowed.  Any other control character from $'\0' through $'\037' (octal numbers, all except 9-13 decimal, 011-015 octal) seems to be OK (and NUL or 0 being OK did surprise me!).  Do you have any tabs in your main data?  If not, you could export with one of the acceptable alternative characters and then map that to tab.  If you have tabs, life is harder because you'd need to protect any tabs that are in a data field with a backslash.

DB-Access allows DBDELIMITER=$'\t' without problem and produces an unload file with tab delimiters, so the problem is not in the shell code I'm using.

If you're certain you must have tabs, then
investigate Art Kagel's myexport which is part of his utils_ak package from the IIUG Software Repository, which also uses my SQLCMD, available from the same source.  Using DBDELIMITER=$'\t' sqlunload -d stores -t customers (or sqlcmd -U -D $'\t' -d stores -t customers, or various other twists of the command line) produces output in a tab-delimited format.  (On its own, SQLCMD does not handle a full export; it will cheerfully unload single tables, though.  It does not yet handle NUL as a field separator or line separator — a design decision or artefact that I need to review.)


If I understand your requirements correctly....

I would just use the natural key, LanguageCode-CultureCode ("en-US," for example).  It's small enough.  (I'm using the entire "en-US" as the primary key to differentiate it from "en-GB," for example.)

CREATE TABLE [dbo].[Language](
    [Language] [char](2) NOT NULL,
    [Culture] [char](2) NOT NULL,
    [LanguageCode]  AS (([Language]+'-')+[Culture]) PERSISTED NOT NULL,
 CONSTRAINT [PK_Language] PRIMARY KEY CLUSTERED
(
    [Language] ASC,
    [Culture] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY],
 CONSTRAINT [LanguageCode] UNIQUE NONCLUSTERED
(
    [LanguageCode] 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

CREATE TABLE [dbo].[Language_Text](
    [LanguageID] [varchar](5) NOT NULL,
    [LanguageCode] [varchar](5) NOT NULL,
    [LanguageName] [nvarchar](20) NULL,
 CONSTRAINT [PK_Language_Text] PRIMARY KEY CLUSTERED
(
    [LanguageID] ASC,
    [LanguageCode] 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

ALTER TABLE [dbo].[Language_Text]  WITH CHECK ADD  CONSTRAINT [FK_Language_Text_Language] FOREIGN KEY([LanguageCode])
REFERENCES [dbo].[Language] ([LanguageCode])
GO

ALTER TABLE [dbo].[Language_Text] CHECK CONSTRAINT [FK_Language_Text_Language]
GO

This should allow you to get all articles in en or sv, and you can also query for en-US or sv-SE.  Presumably, although it wasn't in your example, you could also query for en-CA, fr-CA, en, fr, or CA.

Edit--I'm sorry, you're right, no search by Culture in my old code.  Revamped above, sorry.  Here's a sample of content:

Language:

Language-Culture-LanguageCode
en  US  en-US
sv  SE  sv-SE

Language_Text:

LanguageID-LanguageCode-LanguageName
en-US   en-US   English
en-US   sv-SE   Engelska
sv-SE   en-US   Swedish
sv-SE   sv-SE   Svenska

Searching by Culture (Canada):

SELECT        test.dbo.Language_Text.LanguageID, test.dbo.Language_Text.LanguageCode, test.dbo.Language_Text.LanguageName
FROM            test.dbo.Language_Text INNER JOIN
                         test.dbo.Language ON test.dbo.Language_Text.LanguageID = test.dbo.Language.LanguageCode
WHERE        (test.dbo.Language.Culture = 'CA')

Searching by Language (French):

SELECT        test.dbo.Language_Text.LanguageID, test.dbo.Language_Text.LanguageCode, test.dbo.Language_Text.LanguageName
FROM            test.dbo.Language_Text INNER JOIN
                         test.dbo.Language ON test.dbo.Language_Text.LanguageID = test.dbo.Language.LanguageCode
WHERE        (test.dbo.Language.Language = 'fr')

Searching by LanguageCode (Swedish):

SELECT LanguageName
  FROM [test].[dbo].[Language_Text]
  where (LanguageID = 'sv-SE')


Add Oracle Label Security to an existing Oracle installation

Just guesswork, but do you select "Upgrade an existing database" early on:

and "advanced install" later on:

The docs do look as if they are referring to the 10g installer.


How to create a stored procedure with columns from three different tables in SQL Server 2008?

SELECT
    C.customername
    ,C.customercode
    ,L.[loan type]
    ,S.[account number]
    ,S.status
FROM
    [first table] AS C
    INNER JOIN [second table] AS L
    ON C.customercode = L.customercode
    INNER JOIN  [3rd table] AS S
    ON S.customercode = C.customercode

This is only as a guidance; it is better to post some sample data and definitions.


Despite of using GRANT ... ON ALL TABLES (or ALL SEQUENCES) TO user, user cannot access newly created tables (or sequences)

ALL TABLES/ALL SEQUENCES in that context means all tables/sequences that exist now, at the time the grant is issued. It doesn't include tables/sequences created after the grant was issued.

Your first option is to include the grants explicitly in the migrate scripts. Every time you create a table/sequence also issue the respective grants to api.

Or you can alter the default privileges. In Postgres default privileges can be stored, that will be applied to a newly created object automatically. You can change them with ALTER DEFAULT PRIVILEGES.

In your case your probably after

ALTER DEFAULT PRIVILEGES IN SCHEMA public
                            GRANT SELECT,
                                  INSERT,
                                  UPDATE,
                                  DELETE
                                  ON TABLES
                                  TO api;

and

ALTER DEFAULT PRIVILEGES IN SCHEMA public
                            GRANT USAGE,
                                  SELECT
                                  ON SEQUENCES
                                  TO api;

Note that the migration scripts need to run under the same user, that did the ALTER DEFAULT PRIVILEGES for them to take effect.

The privileges on objects can be obtained via the pg_class catalog table. For example, if you wanted to query all regular tables in the schema public you could do:

SELECT rel.relname,
       rel.relacl
       FROM pg_class rel
            INNER JOIN pg_namespace nsp
                       ON nsp.oid = rel.relnamespace
       WHERE nsp.nspname = 'public'
             AND rel.relkind = 'r';

For sequences the relkind is S instead of r, so

SELECT rel.relname,
       rel.relacl
       FROM pg_class rel
            INNER JOIN pg_namespace nsp
                       ON nsp.oid = rel.relnamespace
       WHERE nsp.nspname = 'public'
             AND rel.relkind = 'S';

would give you the sequences in public.

The column relname displays the object's name and relacl holds an array of the privileges on the object in form of an aclitem. An ACL is represented by a string of the form <grantee>=<privileges>/<grantor>. <grantee> can be empty if the privileges are granted to public. <privileges> encodes the privileges in form of a string of letters. In your case you want to see api=arwd/admin for a regular table and api=rU/admin for sequences.

a is for "append" (INSERT),
r for "read" (SELECT),
w for "write" (UPDATE),
d for DELETE and
U for USAGE.

More details on the ACLs can be found in the manual on GRANT

Explicit granting is more "secure" in the way, that you don't "forget" the default privileges are in place one day when creating a table api shouldn't be able to access. You'd have to explicitly revoke the privileges after the creation of the object in such a case.

Using default privileges is more convenient of course.


You need to filter you subquery with call_type='SALES', then run:

I use your information:

INSERT INTO test.Dash_Agent_Disp
    (id,emp_id,call_start,call_end,call_type)
VALUES
    ('1','20043','20:20:10','20:21:19','SALES'),
    ('2','20043','20:25:34','20:27:12','NON-SALES'),
    ('3','20044','21:25:34','21:27:12','SALES'),
    ('4','20044','22:25:34','22:27:12','SALES'),
    ('5','20044','22:45:34','22:47:12','NON-SALES'),
    ('6','20043','21:25:34','21:27:12','NON-SALES');

Query:

SELECT
    d.emp_id,
    count(d.id) total,
    (SELECT COUNT(*) FROM Dash_Agent_Disp WHERE call_type='SALES') as total_calls,
    d.call_type
FROM Dash_Agent_Disp as d
WHERE d.call_type='SALES'
GROUP BY
    d.emp_id,
    d.call_type;

Result:

mysql> SELECT  d.emp_id,  count(d.id) total,  (SELECT COUNT(*) FROM Dash_Agent_Disp WHERE call_type='SALES') as total_calls, d.call_type FROM Dash_Agent_Disp as d WHERE d.call_type='SALES' GROUP BY  d.emp_id, d.call_type;
+--------+-------+-------------+-----------+
| emp_id | total | total_calls | call_type |
+--------+-------+-------------+-----------+
|  20043 |     1 |           3 | SALES     |
|  20044 |     2 |           3 | SALES     |
+--------+-------+-------------+-----------+
2 rows in set (0.00 sec)

mysql>


Using solid-state drives certainly does reduce the performance impact of fragmentation, primarily because random I/O is so much faster than traditional storage.

Nevertheless, the reduction in page density (and extra logging) caused by page splitting is still undesirable and may still impact performance.

Generally speaking, well-maintained indexes with an appropriate FILLFACTOR are good regardless of physical storage considerations.

See Paul Randal's excellent article on TechNet for details.


It really sounds like you have an XY Problem.

Your Business Requirement can be summed up with this statement:

  I don't care about data, just structural changes.

I'm sure the Business Requirement to capture and manage Schema deltas is part of DevOps.  There are solutions that exist for this part of the entire DevOps process.  (eg Liquibase)  I suggest you don't reinvent the wheel.

Oracle Tools for Schema Deltas

The latest version of Oracle SQLcl (v19.2.1 - $0) has incorporated Liquibase.  This allows you to create schema deltas (and save the result in a Code Repository)

The third example in the documentation appears to be a good starting point for your needs.

capture base Schema
capture changes
(example applies base Schema to new Schema)
apply changes to base Schema

Make sure you keep the deltas in a Code Repository.

More Info?

There are a lot of articles that discuss how automate schema changes...especially with the context of DevOps.  Feel free to google for more information.