SQL SERVER 2014 - View value changes depending on the date

It sounds like the Dt_Execution changes, or is updated over time? If so, that will make it difficult to see "new players on a certain date" as there is no history.

In a scenario like this, the "New_Player" attribute should be calculated based on dates, so that either a current or past date could be used.
It may not be appropriate for this table, but you could create another table that has history of Dt_Execution (for weekly games, for example). Then this example table you listed could be compared to that history; if the player's contract date was within a week of the date you select, the procedure or view would calculate that he's a new player.

Without a historical table, this would be difficult or impossible.


Write a query which add two fields of a document and compare their sum to third field in mongo

Here is one part of the answer; adding two fields together. Still trying to figure out how to compare the total of pgDownloadTime +pgAccessTime with expiredTime

// Add pgDownloadTime and pgAccessTime

db.pageTrendLog.aggregate([
    { $match: { "_id" : ObjectId("538c304b7446cf13431a2c5c") } },
      { $project :
        {
          'pgDownloadTime' : '$pgDownloadTime',
          'pgAccessTime' : '$pgAccessTime',
          'total' : {'$add' : [ '$pgDownloadTime', '$pgAccessTime' ]}
        }
      }]);


Grouping data by year based on two timestamp columns

With generate_series() in a LATERAL join (Postgres 9.3+) and date_trunc(), this can be short & simple:

SELECT EXTRACT(YEAR FROM yr)::text AS year, name, count(*) AS ct
FROM   data
     , generate_series(date_trunc('year', created)  -- LATERAL join
                     , COALESCE(deleted, localtimestamp)
                     , interval '1 year') yr
GROUP  BY yr, name
ORDER  BY yr, ct DESC;

That's all. Returns results for all years between the earliest created and the current year.
The trick is to generate one row for each year the base row overlaps with, before aggregating.

Alternative:

SELECT EXTRACT(YEAR FROM yr) AS year, d.name, count(*) AS count
FROM   generate_series ((SELECT date_trunc('year', min(created)) FROM data)
                       , localtimestamp, interval '1 year') yr
JOIN   data d ON d.created < yr::timestamp + interval '1 year'
            AND (d.deleted > yr::timestamp OR d.deleted IS NULL)
GROUP  BY yr, d.name
ORDER  BY count(*) DESC;

This generates the whole range of years before joining. May be more convenient to compute numbers for a manual selection of years.

Related:

Find overlapping date ranges in PostgreSQL
What is the difference between LATERAL and a subquery in PostgreSQL?

If you need to optimize performance for a small set of years out of a much bigger table, a GiST index on a tsrange type would be an option:

Optimizing queries on a range of timestamps (two columns)


COPY Command spawning multiple processes

You aren't giving us much to go on here.  Like, what is the query?  What do the other columns of pg_stat_activity show?  What does OS command "top" show?

I was thinking parallel workers, but parallel worker processes won't show up with their own row in pg_stat_activity until v13.  So I think the most likely thing to have changed is your workflow is now launching multiple simultaneous COPY commands for unknown reason.  Could you crank up logging in the database (log_statement='all', log_connections='on')?


ERROR 1031 (HY000): Table storage engine for 'proc' doesn't have this option

In MySQL 5.6, five(5) InnoDB tables were introduced to the mysql schema. I mentioned and described those tables in my 4-year-old answer to the post InnoDB: Error: Table "mysql"."innodb_table_stats" not found after upgrade to mysql 5.6
For MySQL 5.7, it went to 19.
mysql> select version();
+-----------+
| version() |
+-----------+
| 5.7.12    |
+-----------+
1 row in set (0.00 sec)

mysql> select table_name from information_schema.tables
    -> where table_schema='mysql' and engine='InnoDB';
+---------------------------+
| table_name                |
+---------------------------+
| engine_cost               |
| gtid_executed             |
| help_category             |
| help_keyword              |
| help_relation             |
| help_topic                |
| innodb_index_stats        |
| innodb_table_stats        |
| plugin                    |
| server_cost               |
| servers                   |
| slave_master_info         |
| slave_relay_log_info      |
| slave_worker_info         |
| time_zone                 |
| time_zone_leap_second     |
| time_zone_name            |
| time_zone_transition      |
| time_zone_transition_type |
+---------------------------+
19 rows in set (0.53 sec)

mysql>

Please note the remaining mysql tables
mysql> select table_name,engine from information_schema.tables
    -> where table_schema='mysql' and engine <> 'InnoDB';
+------------------+--------+
| table_name       | engine |
+------------------+--------+
| columns_priv     | MyISAM |
| db               | MyISAM |
| event            | MyISAM |
| func             | MyISAM |
| general_log      | CSV    |
| ndb_binlog_index | MyISAM |
| proc             | MyISAM |
| procs_priv       | MyISAM |
| proxies_priv     | MyISAM |
| slow_log         | CSV    |
| tables_priv      | MyISAM |
| user             | MyISAM |
+------------------+--------+
12 rows in set (0.00 sec)

mysql>

As shown, mysql.proc (where stored procedures and functions are stored) is supposed to remain as MyISAM. I have mentioned these table counts before (see the subheading "CAVEAT FOR MySQL 5.6/5.7 users" in my answer Will hacking away ibdata1 destroy databases with only MyISAM tables?)
LESSON HERE
You should never convert any table in the mysql schema into any storage engine. Changing them only made matters worse. The solution to this problem may  have been as simple as executing REPAIR TABLE mysql.proc;.


update statements shows update count but not updating the table

If the result of those joins is not unique, sql server will update A.Col10 with every E.Col10 value that matches your query, so at the end there might be the same amount of rows in the second query as in the first statement
For example - there are 81 A values and 11 E values, one A value match one E value, so sql server will update each A value 10 times = 810. The final result is the same(one A value match one E value), so when you select those values you still get 810 results.


Return type of JOINing stored procedure - PostgreSQL

No, I am afraid that's not possible. I have been wishing this was possible myself on several occasions. Either you have a registered row (composite) type that matches the return type or you have to list columns individually.
CREATE or REPLACE FUNCTION select_join()
  RETURNS TABLE (col1 int, col2 date, ...) AS ...

There is a somewhat awkward workaround with returning a nested composite type, but it's not exactly the same. See:

Easy way to have return type be SETOF table plus additional fields?

Or you create a composite type:
CREATE TYPE foo AS (col1 int, col2 date, ...);

CREATE or REPLACE FUNCTION select_join()
  RETURNS SETOF foo AS ...

A VIEW also registers its row type automatically. For the simple test case you present, you might just use a view instead of the function to begin with (like @a_horse commented):
CREATE OR REPLACE VIEW my_view AS
SELECT t1.*, t2.name
FROM t1 JOIN t2 ON t1.t2_id = t2.id;

Or you create the view for the purpose of registering the row type conveniently. You might add LIMIT 0 to document that the view is not meant to return rows, but that's optional.
CREATE or REPLACE FUNCTION select_join()
  RETURNS SETOF my_view AS ...

Be aware that this introduces a dependency (like with any other view) and Postgres will prevent dropping the table and some other manipulations, unless you drop depending views first.


Is there a performance gain by handling data with stored procedures versus feeding it into functions after retrieval?

It depends a lot on what you're doing with the records, and what your network architecture looks like. For example, if the stored procedure is going to reduce the overall amount of data transmitted from MySQL to PHP, and you have a relatively slow link between the PHP instance and the database, a SP could certainly help you.

I would recommend you start with some of the 'heavier' manipulations you do to your data on the PHP side, see what you can write as a SP, and then benchmark it.


Preferred approach to implement multiple many-to-many relationships?

Since the primary key (page_id, user_id) is same for all roles it would be better to put all roles in a single table, the one you named pages_users.

The question on using role_id and a separate roles table is a matter of personal preference. I would use a separate table, at least if I'm going to populate an UI component list based on the choices.


Congratulations, someone has reinvented EAV (Entity Attribute Value).  Please study up on the subject with them.

The short form is: one or more EAV tables can be useful in certain specific cases, usually when accompanied by other tables, but you lose most of the benefit of a relational database when you move to them.

I would also ask: If someone wants to put everything in a single EAV, for what purpose does someone still want to use SQL Server?  What benefits does SQL Server provide at that point over other approaches, particularly transactional databases (one example is a simple structure where you have "key columns" then "data columns" and you always look up data via the key).

Wikipedia has a reasonable entry.

A couple interesting notes from Wikipedia (the second of which applies in particular to the "only one Value column in the whole database" idea you listed someone as having proposed):

an index on the value column of an EAV table is essentially useless
constant data type inter-conversions are required if one wants to do anything with the values

Tony Andrews covers EAV as well as OTLT with an example of an EAV query that gets a bit cumbersome.

Aaron Bertrand argues for the EAV approach in select cases.

Aaron Bertrand has perhaps the best bullet list:

Cons from Aaron:

it is difficult to control the attribute names, and to make them consistent;
it is difficult to enforce data type integrity and referential integrity;
it is difficult (and slow) to pivot and/or self-join the values to make a single row;
it is difficult to make certain attributes mandatory;
and, we've been trained to view this as the "wrong" way to solve the problem.

Pros from Aaron:

But what do you do when you have customers that demand real-time, on-demand addition of attributes that they want to store?


How to set up a user who doesn't have `admin` database access but use it as authentication database?

Not need. You just give user needed rights to the wanted non-admin database. User can use admin database as authentication database even user doesn't have read/write access to admin database.

use products
db.grantRolesToUser("productsUser",[ "readWrite" ])

grantRolesToUser documentation.

UPDATE

Let's create mongodb instance with --auth. Login as admin, create user what can readWrite ONLY test -db, authenticate with that user, check can use list admin database collections and what this user can do with test database.

#> mlaunch init --single --auth
launching: mongod on port 27017
Username "user", password "password"
#> mongo -u user -p password admin
MongoDB shell version v3.4.6
connecting to: mongodb://127.0.0.1:27017/admin
MongoDB server version: 3.4.6
Mongo> db.createUser({user:"test", pwd:"testpwd", roles:[{role:"readWrite", db:"test"}]})
Successfully added user: {
    "user" : "test",
    "roles" : [
        {
            "role" : "readWrite",
            "db" : "test"
        }
    ]
}
Mongo> db.auth("test","testpwd")
1
Mongo> db
admin
Mongo> show collections
2017-09-11T19:06:22.001+0300 E QUERY    [thread1] Error: listCollections failed: {
    "ok" : 0,
    "errmsg" : "not authorized on admin to execute command { listCollections: 1.0, filter: {} }",
    "code" : 13,
    "codeName" : "Unauthorized"
} :
_getErrorWithCode@src/mongo/shell/utils.js:25:13
DB.prototype._getCollectionInfosCommand@src/mongo/shell/db.js:807:1
DB.prototype.getCollectionInfos@src/mongo/shell/db.js:819:19
DB.prototype.getCollectionNames@src/mongo/shell/db.js:830:16
shellHelper.show@src/mongo/shell/utils.js:762:9
shellHelper@src/mongo/shell/utils.js:659:15
@(shellhelp2):1:1
Mongo> use test
switched to db test
Mongo> db.coll.insert({})
WriteResult({ "nInserted" : 1 })
Mongo> show collections
coll
Mongo>

And same from outside:

#> mongo -u test -p testpwd --authenticationDatabase test
MongoDB shell version v3.4.6
connecting to: mongodb://127.0.0.1:27017
MongoDB server version: 3.4.6
2017-09-11T19:17:48.614+0300 E QUERY    [thread1] Error: Authentication failed. :
DB.prototype._authOrThrow@src/mongo/shell/db.js:1461:20
@(auth):6:1
@(auth):1:2
exception: login failed
#> mongo -u test -p testpwd --authenticationDatabase admin test
MongoDB shell version v3.4.6
connecting to: mongodb://127.0.0.1:27017/test
MongoDB server version: 3.4.6
Mongo>


SSAS - what could be a good starting point for memory settings?

Depending on how much RAM is on your server, below is a good starting point (given you have SSAS ONLY running on the server) :
click to enlarge:

Refer to :SSAS Memory Configurations for Common Architectures and Analysis Services Memory Limits

SSAS uses memory limit settings to determine how it allocates and manages its internal memory.  Memory\LowMemoryLimit defaults to 65% of the total available physical memory on the machine (75% on AS2005), and Memory\TotalMemoryLimit (also sometimes called the High Memory Limit) defaults to 80%.  This is the total amount of memory that the SSAS process itself (msmdsrv.exe) can consume.
Once memory usage hits the Low limit, memory cleaner threads will kick in and start moving data out of memory in a relatively non-aggressive fashion.  If memory hits the Total limit, the cleaner goes into crisis mode… it spawns additional threads and gets much more aggressive about memory cleanup, and this can dramatically impact performance.


I want to select rows from products table which have categories 3 and 5 both.

SELECT productId
FROM product_categories
WHERE categoryId IN (3, 5)
GROUP BY productId
-- JOIN products ON products.id = product_categories.productId
HAVING 2 = COUNT(DISTINCT categoryId)

If (productId, categoryId) is set as unique by index, you may remove DISTINCT.

products table not needed at all (except the case when some value of productId exists in product_categories but not exists in products).

  How can I do it with two tables: I have another table similar to product_categories, let's say product_highlights and it has one-to-many relationship as well. How can I query results with values (3, 5) in product_categories and (2, 4) in product_highlights?

SELECT t1.productId
FROM product_categories t1
JOIN product_highlights t2 ON t1.productId = t2.productId
WHERE t1.categoryId IN (3, 5)
  AND t2.highlightId IN (2, 4, 6) -- changed to 3 values
GROUP BY productId
-- JOIN products ON products.id = product_categories.productId
HAVING 2*3 = COUNT(DISTINCT categoryId, highlightId)

If (productId, parameterId) is set as unique by index in BOTH tables, you may remove DISTINCT.

Alternatively you can select productId for each table separately in subqueries, then join them in main query.


Data Migration: Unload/load of GENERATED ALWAYS TIMESTAMP

I'm not sure you can alter a column with that clause. The knowledge center suggests that this syntax should do it,

ALTER TABLE CFFOLIO.FOLIO ALTER COLUMN "TIMESTAMP"
SET GENERATED ALWAYS FOR EACH ROW ON UPDATE AS ROW CHANGE TIMESTAMP;

but it does not appear to work.

One option would be to use LOAD. E.g. https://www.ibm.com/developerworks/community/blogs/IMSupport/entry/DB2_Preserving_existing_timestamp_data_when_moving_tables_which_contain_a_ROW_CHANGE_TIMESTAMP_column?lang=en


MySQL 5.5 not starting after master-master configuration

What's the exact version of 5.5 you're running.  If you're running 5.5.24 you might be running into this bug https://bugs.launchpad.net/percona-server/+bug/1008278.

If you are running 5.5.24 ensure userstat=OFF.  Really though this is annoying so you might look at just upgrading beyond that.


procedure to select the first name of the passed id

anonymous block completed is not an error, just an informational message. Actually, when you use EXECUTE, you implicitly use an anonymous block. If you want to get rid of that message, you can turn it off by:

set feedback off


How to get a surrogate key value in order to put a foreign key in and create relationship?

Assuming your surrogate keys are auto-incrementing integers marked as primary keys they will be the "rowid" for that table so you can read the last value added for a single insert using SELECT last_insert_rowid(). This operates like scope_identity in other databases. You would need to read each one after each row insert as each will replace record of the previous one.

sqlite doesn't seem to have an equivalent to the OUTPUT clause found in SQL Server and the RETURNING clause in Postgres & Oracle, so I don't know of a way to read multiple generated ID meaning that you'll have to do this one row at a time.


Specify Import Row Range for SQL Server Task Wizard

The wizard only has the option to skip the first x rows...more complex than that would need to be a regular SSIS package, or some pre-processing of the file before the wizard picks it up.

Alternatively, import the whole file to a staging table and then trash the rows you don't need, or push the ones you do want into the correct destination table in a T-SQL statement.


How I prevent deadlock occurrence in my application?

DISCLAIMER : Not a Codeigniter Person, Just a MySQL DBA

Looking at the flow of control in the PHP. It says to me:

Check the following mod_id,course_id,user_id,interaction_id
If not found, INSERT the new row
If found, UPDATE all the other columns

There are three(3) mechanisms provided for INSERT and UPDATE scenarios

MECHANISM #1 : REPLACE INTO

REPLACE INTO `scorm_interactions` SET
    `mod_id` =  '4',
    `course_id` =  '5',
    `user_id` =  '185',
    `interaction_id` =  'Question2_1',
    `interaction_type` = 'choice',
    `time` = '10:45:31',
    `weighting` = '1',
    `correct_response` = 'Knees*',
    `learner_response` = 'Knees*',
    `result` = 'correct',
    `latency` = '0000:00:02.11',
    `objectives` = 'Question2_1',
    `description` = ''
;

or

REPLACE INTO `scorm_interactions`
(`mod_id`,`course_id`,`user_id`,`interaction_id`,
`interaction_type`,`time`,`weighting`,`correct_response`,
`learner_response`,`result`,`latency`,`objectives`,`description`) VALUES
('4','5','185','Question2_1','choice','10:45:31','1','Knees*',
'Knees*','correct','0000:00:02.11','Question2_1','');

This mechanically performs DELETE and INSERT on mod_id, course_id , user_id, interaction_id

MECHANISM #2 : INSERT ... ON DUPLICATE KEY

INSERT INTO `scorm_interactions`
(`mod_id`,`course_id`,`user_id`,`interaction_id`,
`interaction_type`,`time`,`weighting`,`correct_response`,
`learner_response`,`result`,`latency`,`objectives`,`description`) VALUES
('4','5','185','Question2_1',
'choice','10:45:31','1','Knees*',
'Knees*','correct','0000:00:02.11','Question2_1','')
ON DUPLICATE KEY UPDATE
    `interaction_type` = 'choice',
    `time` = '10:45:31',
    `weighting` = '1',
    `correct_response` = 'Knees*',
    `learner_response` = 'Knees*',
    `result` = 'correct',
    `latency` = '0000:00:02.11',
    `objectives` = 'Question2_1',
    `description` = ''
;

MECHANISM #3 : SELECT ... FOR UPDATE

Did you know you can perform an exclusive lock on rows you intend to update with precision?

According the MySQL Documentation, you can run a SELECT query on the very row and issue a row lock along the way. For example:

SELECT counter_field FROM child_codes FOR UPDATE;
UPDATE child_codes SET counter_field = counter_field + 1;

This will lock the entire table and allow an UPDATE of the counter_field column.

In your case, simply run the two queries back-to-back

SELECT * FROM `scorm_interactions`
WHERE `mod_id` =  '4',
AND `course_id` =  '5',
AND `user_id` =  '185',
AND `interaction_id` =  'Question2_1'
FOR UPDATE ;
UPDATE `scorm_interactions` SET
    `interaction_type` = 'choice',
    `time` = '10:45:31',
    `weighting` = '1',
    `correct_response` = 'Knees*',
    `learner_response` = 'Knees*',
    `result` = 'correct',
    `latency` = '0000:00:02.11',
    `objectives` = 'Question2_1',
    `description` = ''
WHERE `mod_id` =  '4'
AND `course_id` =  '5',
AND `user_id` =  '185',
AND `interaction_id` =  'Question2_1'
;

EPILOGUE

As a safety cushion, try increasing innodb_lock_wait_timeout if the traffic is write-heavy. See my StackOverflow post : How to debug Lock wait timeout exceeded?

Give it a Try !!!


Storing 5TB web server access log vs OLAP DB

I recommend Vertica.

You can get the free community edition that allows up to 1TB of data. If you normalize your web logs when you load them in, chances are they'd compress down and may fit under 1TB, as Vertica has a pretty powerful data compression engine itself.

If not, I'd still recommend trying out the platform, but the license fee isn't the cheapest thing in the world.


Can duplicate pg_hba entries result in a connection error?

No. Duplicate entries are basically ignored.

The documentation of pg_hba.conf describes how the rules work pretty well:

  The first record with a matching connection type, client
  address, requested database, and user name is used to perform
  authentication. There is no "fall-through" or "backup": if one record
  is chosen and the authentication fails, subsequent records are not
  considered. If no record matches, access is denied.

So the second and any subsequent duplicates will never be considered.
So duplicate entires can not be the cause of your error.


Can't add a foreign key constraint in a table on Postgresql

In the table centresdevote you have a row where the value of objectid is 10. As you are trying to create a FOREIGN KEY CONSTRAINT there is a check that there is a matching value in the column objectid in the table prefectures for all the rows in centresdevote. In prefectures there is no row with objectid = 10.

I suggest you read The PostgreSQL Documentation - 5.3.5. Foreign Keys and the W3Schools page about foreign keys. The whole point of the foreign keys is that they limit what you can insert into the field. From PostgreSQL Documentation:

  A foreign key constraint specifies that the values in a column (or a group of columns) must match the values appearing in some row of another table. We say this maintains the referential integrity between two related tables.

Adding foreign key is not mandatory and you can do every kind of query without it.


Grant to one; then revoke from all: does it work in Oracle SQL?

Yes, frank will be able to perform a select on books. Basically Oracle will use the most permissive privilege, which is the one allowing selection.

Reference: Documentation (under roles).

Note that this is different from you granting privileges with admin option to UserB, who in turn grants privileges to UserC. In this case revoking privileges from UserB would automatically "cascade" revoke them from UserC.


Oracle Procedure to Enable/Disable all constraints

I recommend you put the code in an AUTHID CURRENT_USER package.  You can certainly pass a Boolean, but I think a Varchar2 makes the usage clear.  You should unit test this code, add documentation, formalize the error handling, include instrumentation, and probably modify the reporting method.

CREATE OR REPLACE PACKAGE Maint AUTHID CURRENT_USER IS

Procedure ToggleConstraints (iNewStatus In Varchar2);

END;
/

CREATE OR REPLACE PACKAGE BODY Maint IS

Procedure ToggleConstraints (iNewStatus In Varchar2) Is
Begin
   If (UPPER(iNewStatus) NOT IN ('ENABLED','DISABLED')) Then
      Raise_Application_Error(-20001
         , 'Constraints can only be toggled to ENABLED OR DISABLED.');
   End If;

   For vConstraint In
   (
       SELECT 'alter table ' || table_name
         || DECODE(UPPER(iNewStatus), 'DISABLED',' disable',' enable')
         || ' constraint ' || constraint_name As Statement
       FROM user_constraints
       WHERE constraint_type = 'R'
       AND status = DECODE(UPPER(iNewStatus),'DISABLED','ENABLED','DISABLED')
   ) Loop
      DBMS_Output.Put_Line(vConstraint.Statement);
      execute immediate vConstraint.Statement;
   End loop;
End;

END;
/

Set serveroutput on size 1000000 format wrapped
EXECUTE Maint.ToggleConstraints(iNewStatus=>'ENABLED');


To restate, you have packages in SQL Server 2008 R2 and you wish to migrate them to SQL Server 2012. The packages are generally export to "file format".

The only way to know if they upgrade successfully is to upgrade them and find out. The biggest challenge I recall was with packages that had Script Components (not Tasks) as the pipeline has some biggish structural changes. The other obvious caveat is if you used a custom third party/in house Task/Component. That'll require manual migration and modification of the packages in visual studio.

You have two options with your upgrade. SSIS now has two deployment models. Package deployment model, a.k.a. Classic/Legacy, which is what you have here. Packages can be deployed to file system or msdb, you control your configuration, logging, versioning, etc.

Project deployment model is the new hotness. There is a dedicated SSISDB catalog that handles all the configuration, logging, versioning etc for you. I generally favor it over the former as it provides a better management and execution experience. Migrating package deployment model projects to project deployment model requires changing the packages themselves. The upgrade wizard will help in that but it will strip out logging and configuration as it wants you to use the new way. This will require more testing than the simple smoke test required with a package deployment model upgrade path. Whether it's worth the effort, especially in a change control heavy environment is best answered by you.

An approach for package deployment upgrade

dtutil.exe is the command line tool for deploying SSIS packages. Assuming standard install path, you'd be looking for the dtutil that is in C:\Program Files\Microsoft SQL Server\100\DTS\Binn\dtutil.exe to extract the packages as is. When a newer version of SSIS tooling touches a previous version package, the first thing that happens is an in memory upgrade to that version of SSIS. I mention this as if you didn't use source control, you might want to extract the packages "as is" out of the existing 2008 R2 instance before deploying to your 2012 instance. I have a script for that

;
WITH FOLDERS AS
(
    -- Capture root node
    SELECT
        cast(PF.foldername AS varchar(max)) AS FolderPath
    ,   PF.folderid
    ,   PF.parentfolderid
    ,   PF.foldername
    FROM
        msdb.dbo.sysssispackagefolders PF
    WHERE
        PF.parentfolderid IS NULL

    -- build recursive hierarchy
    UNION ALL
    SELECT
        cast(F.FolderPath + '\' + PF.foldername AS varchar(max)) AS FolderPath
    ,   PF.folderid
    ,   PF.parentfolderid
    ,   PF.foldername
    FROM
        msdb.dbo.sysssispackagefolders PF
        INNER JOIN
            FOLDERS F
            ON F.folderid = PF.parentfolderid
)
,   PACKAGES AS
(
    -- pull information about stored SSIS packages
    SELECT
        P.name AS PackageName
    ,   P.id AS PackageId
    ,   P.description as PackageDescription
    ,   P.folderid
    ,   P.packageFormat
    ,   P.packageType
    ,   P.vermajor
    ,   P.verminor
    ,   P.verbuild
    ,   suser_sname(P.ownersid) AS ownername
    FROM
        msdb.dbo.sysssispackages P
)
SELECT
    -- assumes default instance and localhost
    -- use serverproperty('servername') and serverproperty('instancename')
    -- if you need to really make this generic
    'dtutil /sourceserver ' + @@SERVERNAME + ' /SQL "'+ F.FolderPath + '\' + P.PackageName + '" /copy file;".\' + P.PackageName +'.dtsx"' AS cmd
FROM
    FOLDERS F
    INNER JOIN
        PACKAGES P
        ON P.folderid = F.folderid
 WHERE
     F.FolderPath LIKE '\SERV%';

If you like to live dangerously, you could change the final SELECT to skip the file system and directly deploy to the other server. This assumes you've already created the same folder structure over there.

'dtutil /sourceserver ' + @@SERVERNAME + ' /SQL "'+ F.FolderPath + '\' + P.PackageName + '" /destserver MyShiny2012Box /copy SQL;"'+ F.FolderPath + '\' + P.PackageName + '"' AS cmd

I've eyeballed the above and it looks approximately correct but you should test it. It should render a command similar to the following

dtutil /sourceserver localhost /SQL "\SERV-ABC\Package" /destserv MyShiny2012Box /copy SQL;"\SERV-ABC\Package"

For that to work, you'd need to make sure dtutil is the 2012 version which should be in C:\Program Files\Microsoft SQL Server\110\DTS\Binn\dtutil.exe

From the comments

Don't confuse the tooling with the installed products and/or servers. Starting with 2005, there is a version of SSMS that was released with each SQL Server version (this gets murky as of SQL Server 2016). SSMS has an "object explorer". The Database Engine portion of Object explorer can be used to manage all versions of SQL Server and the common recommendation is to always use the latest version of SSMS in your environment as it'll have the most bug fixes etc.

Where this advice can fail you if you use the "other" options for Object Explorer. SSIS is the only thing I use with regularity so I'll address that.

You must use the version of SSMS that matches your target SQL Server instance for it to "work." In the screenshot, you're attached to the 2008 R2 server's Integration Services ... whatever that is but ignore it. Yes, ignore that blecherous tool as it's useless. It doesn't help you if you have multiple instances running and it only runs packages in ... 32 or 64 bit, I don't remember which.

Instead, use the one Object Explorer type that works across your instances - the database engine one. SSIS packages stored in "the database" are going to be in the msdb. Use the above query or go exploring those tables yourself. All the data is there. There are tsql procedures to move packages about, store them out, etc although I prefer dtutil myself.


Is using MySQL views a good translation mechanism?

Views are a good way to accomplish what you want to do. If your views take advantage of existing indexes or are 1:1 against the underlying tables, then queries against them will use the indexes. Since you're expecting periodic updates, you'll want to script the view creation, and in that same deployment script you can always add indexes if you need more.

You will want a 1:1 view of the columns for each table that simply performs the translation on the column and table names, enabling updates and inserts on those views in your locale. If the database contains stored procedures, you can create wrappers for them in your native language by adding new procedures to accept translated parameters and call the original procedures.

One caveat is the collation used by the database: if it is not suitable for your locale, using COLLATE to a different character set in your SQL statements will negatively impact performance.


Running out of Transaction Log space during Alter Table

You will want to load your data into a new table, doing this in small batches, then drop the existing table. I put together a quick example using the Sales.Customer table in AdventureWorks, something similar should work for you also.

First, create your new table, complete with the new datatype you want to use:

CREATE TABLE [Sales].[Currency_New](
    [CurrencyCode] [nchar](4) NOT NULL,
    [Name] [varchar](128) NOT NULL,
    [ModifiedDate] [datetime] NOT NULL,
 CONSTRAINT [PK_Currency_New_CurrencyCode] PRIMARY KEY CLUSTERED
(
    [CurrencyCode] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
)
GO

Then, insert your records and define your batch. I am using 10 here, but you will likely want to use something larger, say 10,000 rows at a time. For 30MM rows I'd even suggest you go to 100k row batch size at a time, that's the limit I typically used with larger tables:

DECLARE @RowsInserted INT, @InsertVolume INT
SET @RowsInserted = 1
SET @InsertVolume = 10 --Set to # of rows

WHILE @RowsInserted > 0
BEGIN

INSERT INTO [Sales].[Currency_New] ([CurrencyCode]
           ,[Name]
           ,[ModifiedDate])
SELECT TOP (@InsertVolume)
       SC.[CurrencyCode]
           ,SC.[Name]
           ,SC.[ModifiedDate]
FROM [Sales].[Currency] AS SC
LEFT JOIN [Sales].[Currency_New] AS SCN
    ON SC.[CurrencyCode] = SCN.[CurrencyCode]
WHERE SCN.[CurrencyCode] IS NULL

SET @RowsInserted = @@ROWCOUNT
END

I usually do a sanity check and verify the rowcounts are the same before cleaning up:

SELECT COUNT(*) FROM [Sales].[Currency]
SELECT COUNT(*) FROM [Sales].[Currency_New]

Once you are confident you have migrated your data, you can drop the original table:

DROP TABLE [Sales].[Currency]

Last step, rename the new table, so that users don't have to change any code:

EXEC sp_rename '[Sales].[Currency_New]', '[Sales].[Currency]';
GO

I don't know how long this will take. I'd suggest you try doing this when you have a clear maintenance window and users aren't connected.

HTH


CROSS APPLY-ing a parameterised function

CROSS APPLY takes a table valued function and 'applies' parameters from each row in the query you are applying it to.  The function is evaluated once for each row and the output is implicitly joined to the source row in the record set from which the parameters were obtained.  Note that this 'join' can be 1:M - with a TVF one row at source can generate multiple rows of output from the TVF.

You don't need to use any more predicates in the where clause to join the results unless (for some reason) you want to further filter the output of the function by applying an additional predicate.

CROSS APPLY is quite useful with xpath queries if you have to go ferreting around XML fields that may contain repeating groups.


Which database technologies to consider for a data search application?

Fulltext Search is a specialized technology that complements a traditional SQL database. There is a simple full text indexing feature built into MySQL, but you will probably find other alternatives that are more featureful and higher performance.

I did a presentation comparing them here: Full Text Search Throwdown.

The short answer: Sphinx Search.

Re your comment:

It seems your question is not about a specific solution but just asking for categories of performance optimizations.

Indexing. How to Design Indexes, Really is my presentation, which I've given several times. Here's a recording of me presenting a version of this talk: Tools and Techniques for Index Design.
Partitioning. Partitions Performance with MySQL 5.1 and 5.5 by Giuseppe Maxia. This was the one that made partitioning click for me. Partitioning is very powerful when it works, but it works for a small number of cases.
Caching. What's the fastest SQL query? The one you don't have to run at all, because you can fetch a copy of the data from a high-speed cache. This requires more case-by-case application coding to decide on the right data to cache, what format to store it in, and how to refresh it. Lots of work, but can speed up certain data access by orders of magnitude.
Denormalization. Although denormalization creates opportunities for data anomalies, sometimes it's the best choice to optimize for a specific query. Beware though, because denormalization degrades performance for all other queries against the same data.
Sharding. Useful when your data or query traffic grows so large that a single database instance can no longer process enough queries per second. Split your data horizontally, storing a subset on each shard instance. Add code to your application to decide how to send your queries to the appropriate shard. Sharding is used by MySQL Cluster, MySQL Fabric (which is still pretty alpha as of this writing), and Shard-Query.

Finally, the advice I often give about optimizing is that every method listed above optimizes for one query at the expense of others. For example, sharding is great if you read and write individual rows, but sharding makes things worse if you query for ranges of rows that span multiple shards.


SQL to add zeros in front of existing data to meet field length

SELECT LPAD(nationalitycode, 5, '0')
FROM...

See https://firebirdsql.org/refdocs/langrefupd21-intfunc-lpad.html
If you want to update nationalitycode:
UPDATE ...
    SET nationalitycode = LPAD(nationalitycode, 5, '0')
WHERE ...


Read uncommitted from Books Online:

  Implements dirty read, or isolation level 0 locking, which means that no shared locks are issued and no exclusive locks are honored. When this option is set, it is possible to read uncommitted or dirty data; values in the data can be changed and rows can appear or disappear in the data set before the end of the transaction. This option has the same effect as setting NOLOCK on all tables in all SELECT statements in a transaction. This is the least restrictive of the four isolation levels.

So no it doesn't lock tables, rows or pages. However, any SELECT (even with READ UNCOMMITTED set) will issue a Sch-S (schema stability) lock, which basically means no schema changes can happen while the SELECT is executing (schema changes = add/drop column, change datatype of a column, change nullability of a column and a couple other operations I can't think of off the top of my head). A Sch-S lock should not interfere with DML statements (SELECT/INSERT/UPDATE/DELETE).


Here are two possible setups you could try

TOPOLOGY #1

Data Center #1 with two(2) DB Servers (DB1, DB2)
Data Center #2 with two(2) DB Servers (DB3, DB4)
Setup DRBD with Each Data Center
Setup MySQL Replication Between the DRBD Primaries
Setup DBVIP (Linux Heartbeat/ Pacemaker / ucarp)

What this setup provides

Synchronous Disk-Level Replication Within Each Data Center
Asynchronous Replication Between Data Centers

TOPOLOGY #2

Data Center #1

Three(3) DB Servers (DB1, DB2, DB3)
Percona XtraDB Cluster among the three(3) DB Servers

Data Center #2

Three(3) DB Servers (DB4, DB5, DB6)
Percona XtraDB Cluster among the three(3) DB Servers

MySQL Circular Replication

DB3 used as Master to DB6
DB3 used as Slave to DB6
Use Semisync Replication Between Master and Slave

What this setup provides

Virtually Synchronous Replication Within Each Data Center
Asynchronous Replication Between Data Centers

I have discussed setups like this before

Feb 10, 2014 : MySQL High Availability for InnoDB
Jul 11, 2013 : MySQL replication between datacenters
Dec 26, 2012 : MySQL Replication and High Availability
Oct 25, 2012 : mysql read slave to fetch entire database on start
Aug 20, 2012 : Best solution for cross-datacenter MySQL master-slave replication
Mar 26, 2012 : 3 Databases, Best Replication Setup?
Dec 19, 2011 : Best way to setup master to multi master replication
Mar 29, 2011 : MySQL high availability, failover and replication with Latency

GIVE IT A TRY !!!


Client to use the slave when the master is down in MySQL replication

Since you have Master/Slave, you may want to implement the use of two things

Circular Replication
Database Virtual IP (aka DBVIP)

Circular Replication
Circular Replication is nothing more than first setting up Master/Slave then performing the same steps using the Slave as the Master's Master and the Master as the Slave's Slave. It just entails

explicitly user a different server_id on each DB Server
enabling binary logging on both DB servers
making sure the replication user is defined on both DB servers

Database Virtual IP (aka DBVIP)
There are products you can download and install to setup a DBVIP. One such product I use is ucarp. Another product is Linux Heartbeat. I normally do not use such things with MySQL Circular Replication or Master/Slave. Why?
Since those products can perform automatic failover, you do not want to do that in the event a Slave is some number of seconds behind in replication lag.
You should perform manual failovers.
Here is a poor man's approach to implementing DBVIP management.
Suppose you have this setup

DB Server1 has IP 10.1.2.30
DB Server2 has IP 10.1.2.40
You want to use DBVIP 10.1.2.50

Create the Script called /usr/local/sbin/MyAppDBVIP like this
echo echo 10.1.2.50 > /usr/local/sbin/MyAppDBVIP

Create the Script called /usr/local/sbin/dbvip-up
DBVIP=`/usr/local/sbin/MyAppDBVIP`
ip addr add ${DBVIP}/24 dev eth1

Create the Script called /usr/local/sbin/dbvip-down
DBVIP=`/usr/local/sbin/MyAppDBVIP`
ip addr del ${DBVIP}/24 dev eth1

Make sure all scripts are executable
chmod +x /usr/local/sbin/MyAppDBVIP
chmod +x /usr/local/sbin/dbvip-up
chmod +x /usr/local/sbin/dbvip-down

Make sure these script exist on both DB Servers
Simply run dbvip on whichever server you choose. .
So the failover process and protocol are the following:

Run dbvip-down on the DB Server that has the DBVIP. If you cannot
Run dbvip-up on the DB Server that you want to have the DBVIP
Just remember you should not run dbvip-up on both machines
After running dbvip-up, restart apache, JBoss, or any other app server contacting MySQL via the old Master


Calculating Price Based Off Price Break Table

I would approach this using a SQL JOIN that joins both tables on the item, and the appropriate discount.

DECLARE @Parent TABLE
(
    ItemName varchar(100) NOT NULL
    , ItemPrice decimal(18,4) NOT NULL
    , ItemQuantity int NOT NULL
);

DECLARE @Exceptions TABLE
(
    ItemName varchar(100) NOT NULL
    , MinQuantityOrdered int NOT NULL
    , MaxQuantityOrdered int NOT NULL
    , ItemPrice decimal(18,4) NOT NULL
);

INSERT INTO @Parent (ItemName, ItemPrice, ItemQuantity)
VALUES ('rr11', 999999.99, 10)
    , ('rr11', 999999.99, 5)
    , ('rr11', 999999.99, 22)
    , ('mc22', 15.00, 5)

INSERT INTO @Exceptions (ItemName, MinQuantityOrdered, MaxQuantityOrdered, ItemPrice)
VALUES ('rr11', 1, 5, 15.50)
    , ('rr11', 6, 10, 11.00)
    , ('rr11', 11, 999999, 17.00);

SELECT p.ItemName
    , p.ItemQuantity
    , AdjustedPrice = ISNULL(e.ItemPrice, p.ItemPrice)
    , AdjustedTotal = p.ItemQuantity * ISNULL(e.ItemPrice, p.ItemPrice)
FROM @Parent p
    LEFT JOIN @Exceptions e ON p.itemname = e.itemname
        AND p.ItemQuantity >= e.MinQuantityOrdered
        AND p.ItemQuantity <= e.MaxQuantityOrdered;

The results:

+----------+--------------+---------------+---------------+
| ItemName | ItemQuantity | AdjustedPrice | AdjustedTotal |
+----------+--------------+---------------+---------------+
| rr11     |           10 |       11.0000 |      110.0000 |
| rr11     |            5 |       15.5000 |       77.5000 |
| rr11     |           22 |       17.0000 |      374.0000 |
| mc22     |            5 |       15.0000 |       75.0000 |
+----------+--------------+---------------+---------------+

You'll notice in the example above, I have arbitrarily chosen the value 999999.00 as the maximum price for item rr11 in the @Parent table.  That was only because you chose to use 0.00 for the default price of the item.  If you use 0.00 for a default price, and then forget to create an entry in the @Exceptions table for that item, people will buy as many of those items as they can order since they will be essentially free.  I used 999999 as the maximum quantity for a discount in the @Exceptions table since you need to choose some maximum number for the MaxQuantityOrdered column, and 999999 seems like a fairly high number of items to order.  If you believe you'll sell a million or more items in one order, by all means increase that.  Since the quantity columns are int data types, the maximum you could specify would be 2147483647, which is a lot.

A couple of nit-picky things I noticed:

Use the correct case, for both keywords, data types, and column names.

keywords should be in ALL CAPS, that is how they are shown in the documentation.
data types should be lower-case since that is how they are listed in sys.types
column names should be camel case, or in the very least not alllowercapscrammedtogether.  Just for sanity of the reader.

You are putting quotes around numeric values.  Don't.
You should use a numeric range for values that are numeric.  i.e. Don't use a character column like 0 - 15 for the range of quantities, use two columns, MinValue and MaxValue with 0 and 15.
This one is a bit subjective, and many people won't agree fully with me.  Don't use a char value such as ItemName as the key to join rows between tables unless you are certain those values will not change.  Use a surrogate integer key to uniquely identify each item.  Name it ItemID.  Then if you ever need to correct the spelling of an item, you won't need to update every reference to that item in the database.  Yes, you can use relational integrity to cascade those changes throughout all referenced tables, but if you just use a surrogate key from the start, you'll never have to worry about that.


Storing NULL Values in large VARCHAR fields

A note with no contents sounds like a fairly meaningless object to me. I would model this by the absence of such tuple in the notes table!

p.s. a note's title is an attribute. An example of metadata ("data about data") would be the date a note's title attribute was most recently updated.


I guess I had that issue before
double check whether mysql has been started or not
run following command and see

[root@aba ~]# systemctl status mysqld.service

active status should be

 Active: active (running) since Wed 2018-02-7 12:50:46

before set up cluster


Multiserver administration - passing maintenance plans to clustered instances

JohnnyP.
I know that a long time has passed - but maybe someone will need it.
This error is related to the sys.dm_os_enumerate_filesystem() function launched without the @Path parameter.
In my case, the error appeared due to the fact that I did not specify the folder for the job execution log.
If you create a maintenance plan by wizard, then you need to tick  the 'write and report to a text file' and select folder on the "Select Report Options" window.


How to structure Master/Detail tables for performance without denormalizing?

You've got a few different questions in here:

Q: It takes a very long time to access the sales of a given store or a given item for any period.

A: To troubleshoot that, we would need to see the execution plans of the queries involved, plus know a little about the query runtime and the hardware involved. 10mm rows in a header table and 110mm rows in a detail table isn't much at all for SQL Server, so this should be a solvable problem.

Q: (Partitioning) worked great for finding the sales of an item. The problem is that many of the reports require the StoreID in addition to being over a specific date range.

A: Correct, partitioning rarely makes SELECT queries faster. It's more about improving performance of bulk loads, specifically partition switching. I wouldn't think of partitioning as a solution to this problem, and indeed, it will actually make most queries worse.

Q: Is there a preferred, correct, standard, etc. method for dealing with tables in the Master/Detail pattern in order to increase performance?

A: Absolutely - archive older data. Figure out what you're going to let users query online at high speed, and then beyond that, move the data into a separate set of archive tables. You can use a partitioned view over the old and new tables in order to give them a single seamless view into the data for easier reporting too.

There's a lot of advantages to this approach. For example, when you want to add additional fields to the current table, you can do that quickly without having to deal with a large amount of archive data. If you want to add lots of indexes to the old archive data, you can - because it's not getting tons of inserts/updates/deletes anymore. If you split the old and new data into different databases, you can even use different backup/recovery strategies with them - even while the view is in place, and users don't know the data is split.


Stopping SQL Server and deleting the files will most likely cause the database to go into "recovery pending" at next startup, and my experience is that I've never had any problems dropping a database in that state.

Of course assuming that this is indeed an OK operation for you (restore from prior backup).


Boolean check on fields returning any of them except if all have same value

You can use something like this:

where (true in (a,b,c,d)
       and (a,b,c,d) <> (true,true,true,true));

Which can be simplified (suggested by ypercubeᵀᴹ) to:

where true in (a,b,c,d) and false in (a,b,c,d)

Note that it will not handle NULL values properly. If you want that as well, you will need to use coalesce().


As Phil already said 'It was a design decision'. This is the main difference between InnoDB Engine. Because InnoDB Engine is store all databases in one file. And each MyISAM table is stored on disk in three files (if it is not partitioned). The files have names that begin with the table name and have an extension to indicate the file type. MySQL uses a .frm file to store the definition of the table, but this file is not a part of the MyISAM engine; instead it is a part of the server.
The data file has a .MYD (MYData) extension. The index file has a .MYI (MYIndex) extension.

for your ref https://en.wikipedia.org/wiki/MyISAM

Even RolandoMySQLDBA and ypercube has well defined InnoDB Engine with respect to MyISAM engine Why does InnoDB store all databases in one file?


Where are PostgreSQL databases stored on my computer?

Where exactly your data is stored depends on how you installed and configured PostgreSQL. Running the

show data_directory

command as the postgres user (from inside the psql tool or another client) will tell you where it is though. For example, if you installed it from the debian or ubuntu repositories you would probably find your data files are somewhere like /var/lib/postgresql/9.2/main.

There probably isn't anything terribly interesting in there though. Sometimes there are a few configuration files like pg_hba.conf (the windows version puts them there at least) but if you're on debian you'll find those under /etc/postgresql/.

If you're expecting your database to be a single file that you can move around between systems, etc, you're probably better off using a simpler embedded database like sqlite.


Most efficient table structure for multi-tenant database

A typical implementation of "multi-tenant" is to have a database for each client.

Each database has the same few dozen tables.
Security (if you care) is somewhat available at the database level.
If your product becomes insanely popular, sharding is relatively easy to add (each server would have several clients).

A downside is the thousands of tables in the whole system.
There would be an extra database for admin stuff -- list of users, their passwords, etc.
If you implement with a single set of tables, then

Client_id would be in many of the tables.
Most indexes would be composite and start with client_id.
Many:many tables may or may not avoid including client_id, depending on whether the Entities have their own id.

Prototype now; analyze; but plan on a major redesign of the schema 6 months from now.  Without the overhaul, you will forever be swearing at yourself for making the wrong decision today.  And your product will die because it could not flex to meet future needs.


The first thing you want to check is if Remote access is enabled on your SQL Server instance.
If not please configure by following the steps here.


This should do what you need.

DECLARE @Id INT

create table #tmpXmlreturn
(
    Data xml,
)

create table #Ids(
    Id int
)

INSERT INTO #Ids
select dbo.Workers.Id as Id
from dbo.Workers

WHILE EXISTS(SELECT * FROM #Ids)
BEGIN
  SELECT TOP 1 @Id = Id FROM #Ids

  INSERT INTO #tmpXmlreturn
  Exec dbo.GetBranchByWorkerId @Id

  DELETE FROM #Ids WHERE Id = @Id
END

SELECT * FROM #tmpXmlreturn
DROP TABLE #tmpXmlreturn


Indexes can be combined, an "index intersection". (I'm from SQL Server side, hence that terminology etc.)
For example, you search for lastname = Smith and firstname = Joe, and neither is by itself very selective. So, go and find the keys for lastname and go find the keys for firstname and then join those two sets of keys to find the data you are after.


Query presented by @S4V1N will do the trick. If you have to see it via GUI using SSMS you can select:

Database → Properties → Permissions → Select the user LoginUser1 from Users or roles: list.

Select Explicit, you will see this.

Select Effective, you will see this.


Can I use WHERE IN when I have combined primary key?

Yes, this is possible:

UPDATE
  tablea SET cola = 'newValue'
WHERE (id1,id2) IN ( (1,1), (2,2), (3,3) );


Postgres: querying jsonb array with an array

You can use the @> operator.

From the docs:

  @>    Does the left JSON value contain within it the right value?

So in your case it would look something like this:

select * from products where specs @> '{"spec_options": ["a", "b", "c"]}';


Creating an index on a really busy table

There is no such option as CREATE INDEX WITH (NOLOCK) (and even NOLOCK on a query takes locks, just fewer than without the hint).

The best you're going to do is WITH (ONLINE = ON), which still takes locks at the beginning and end of the operation (both relating to recompiling plans related to the table - see this blog post by Paul Randal for more details).

This will significantly reduce the impact of building the index on your applications, but there is no practical way to completely eliminate that impact. Also, this feature is not free: it requires Enterprise Edition.


Is there a generic framework/design pattern/library for real time replication of MySQL database onto other servers?

Perhaps I am misreading this, but I am having difficulty understanding how your requirement is any different from built-in MySQL replication.

With MySQL replication, the master database writes changes to a binary log file.  The slaves connect to the master, read the binary logs, write them to their local relay log, and play the statements back.

Please understand that realtime copy and backup are not the same thing.  A real-time slave can help you recover from certain types of problems, but cannot resolve data-corruption issues (oops, I dropped that table!).  For that, you need point-in-time backups.  With a backup and a copy of the binary logs, you can recover from almost any type of failure by replaying the binary logs up to the point where the problem occurred.


Performance degradation while updating tables having 10s of millions of records

The first question is: Why is it important that you not use a lot of CPU time? The query will be bottlenecked on some resource; if you could introduce enough additional disk access, the CPU time used per second would go down, but would that really be an improvement?  What resource would you prefer to saturate?  Understanding why you have emphasized this might help guide people to providing an answer that you will find useful.

As suggested in a comment, your query may run faster with a join rather than a correlated subquery.  Something like this:

UPDATE temp
  SET customername = user.customername
  FROM user
  WHERE user.customerid = temp.customerid;

Another important thing to know is whether you want to update all rows in the table.  Are some of the values already correct?  If so, you will get a big performance boost by not updating the rows that don't need it.  Add AND temp.customername is distinct from user.customername to the WHERE clause.

If you limit the number of rows updated in each statement, and VACUUM ANALYZE after each UPDATE, you will avoid table bloat.  If the point of the desire to minimize CPU time is to avoid a performance impact on concurrent transactions, this would give you the opportunity to introduce a short delay (in the form of a sleep or something similar) before you start the next UPDATE of a set of rows.

Even better, why are you redundantly storing the information in the temp table rather than joining to it when needed?  (Sometimes there is a good reason; quite often there isn't.)


MySQL c++ connector: what is the correct way to use SetBlob() to set multiple blob data in a query?

The different std::istream objects point to the same buffer which allocated by test_data. I thought the istream object will copy the value in the char array. The fact is, it is not.

It also worths to point out that if the std::istream object is destroyed before the statement->executeUpdate() executed. The last stream object will be filled into the first column of the table, which seems to be an undefined behavior on my machine (Ubuntu 16.04.3 LTS). It looks like:

+-----+------+------+------------+
| fid | idx  | head | size       |
+-----+------+------+------------+
|   1 | 1    | 1    | 1          |
|   2 | 2    |      |            |
+-----+------+------+------------+

So, if the table has strange results like the previous table, check the code to make sure the std::stream, the StreamBufferData and the pointer pointing to the buffer are still valid until statement->executeUpdate() executed.


Source code of View of Linked server sp_HelpText

There is an answer on SO: SQL using sp_HelpText to view a stored procedure on a linked server

EXEC  [ServerName].[DatabaseName].dbo.sp_HelpText 'storedProcName'


Using a CREATE TABLE AS SELECT how do I specify a WITH condition (CTE)?

It may be awkward, but you have to move the WITH clause from the top into the query. It's a part of the statement to generate the table, and that statement comes after the CREATE TABLE, so you would use this syntax.

CREATE TABLE foo AS
WITH w AS (
  SELECT *
  FROM ( VALUES (1) ) AS t(x)
)
SELECT * FROM w;

Also worth noting that it's not explicit in the official docs, it just falls under query

CREATE [ [ GLOBAL | LOCAL ] { TEMPORARY | TEMP } | UNLOGGED ] TABLE [ IF NOT EXISTS ] table_name
    [ (column_name [, ...] ) ]
    [ WITH ( storage_parameter [= value] [, ... ] ) | WITH OIDS | WITHOUT OIDS ]
    [ ON COMMIT { PRESERVE ROWS | DELETE ROWS | DROP } ]
    [ TABLESPACE tablespace_name ]
    AS query
    [ WITH [ NO ] DATA ]

The docs mention two other use cases for the WITH keyword,

To set storage_options
To set NO DATA

You can see both of them in use like this,

CREATE TABLE foo
WITH (fillfactor = 100)
AS
  WITH w AS (
    SELECT *
    FROM ( VALUES (1) ) AS t(x)
  )
  SELECT * FROM w
WITH NO DATA;

Now you've got a table with nothing.

TABLE foo;
 x
---
(0 rows)


Case insensitive column names in a postgresql trigger function

Use format with the %I placeholder for identifiers:
EXECUTE format('select $1.%I', latcolumn) USING NEW INTO lat;


How to change column name in mysql if column name has space?

Enclose the field name in back-ticks like this:

ALTER TABLE studentrecord CHANGE COLUMN `Full name` Fullname VARCHAR(255) NOT NULL;


this solves it:

postgres=# \connect ip_spotlight
You are now connected to database "ip_spotlight" as user "postgres".
ip_spotlight=# GRANT USAGE ON SCHEMA doc,net TO netops ;
ip_spotlight=# GRANT USAGE ON SCHEMA doc,net TO netopsapp ;

ip_spotlight=# GRANT SELECT ON ALL TABLES IN SCHEMA doc,net TO netops ;
ip_spotlight=# GRANT SELECT ON ALL SEQUENCES IN SCHEMA doc,net TO netops ;
ip_spotlight-# GRANT EXECUTE ON ALL FUNCTIONS IN SCHEMA doc,net TO netops ;

ip_spotlight-# GRANT ALL ON ALL TABLES IN SCHEMA doc,net TO netopsapp ;
ip_spotlight-# GRANT ALL ON ALL SEQUENCES IN SCHEMA doc,net TO netopsapp ;
ip_spotlight-# GRANT ALL ON ALL FUNCTIONS IN SCHEMA doc,net TO netopsapp ;


ORA-00990 error while trying to grant priviliges: Why is that?

Your list of privileges contains a number that don't exist.  There is no ALTER TABLE privilege.  Nor is there a SELECT TABLE or DELETE TABLE or DROP VIEW privilege.  You can grant a user SELECT access on individual tables that are owned by other users.


Howto query all files, their fill level and additional drive info with one query?

To avoid CROSS APPLY the simplest way I can think of is to call sys.dm_os_volume_stats with explicit parameters for database_id and file_id. This means executing a single-row result for every db/file combo.

First, create a #temp table to hold results:

CREATE TABLE #x(dt datetime, srv nvarchar(520), logical_name sysname,
  physical_name sysname, FileSizeMb int, FileUsedMB int, FileFreeMB int,
  SpaceFreePerc decimal(18,2), SizeOnDiskMB int, volume_mount_point nvarchar(256),
  logical_volume_name nvarchar(256), available_bytes bigint, total_bytes bigint);

Now, some variables and a cursor. The only tricky part here really is the @exec variable, which allows each iteration of the dynamic SQL to be executed in the right database context.

DECLARE @database_id int, @file_id int, @logical_name sysname,
  @physical_name nvarchar(520), @size int, @sql nvarchar(max), @exec sysname;

DECLARE c CURSOR LOCAL FAST_FORWARD
  FOR SELECT database_id, [file_id], name, physical_name, size
  FROM sys.master_files;

SET @sql = N'SELECT
     GETDATE() as dt ,@@SERVERNAME as srv, @logical_name, @physical_name
     ,Round(@size * 8 / 1024, 2) as FileSizeMb
     , CAST(FILEPROPERTY(@logical_name, N''SpaceUsed'') AS INT)/128 as FileUsedMB
     ,(@size/128 - CAST(FILEPROPERTY(@logical_name, N''SpaceUsed'') AS INT)/128) AS FileFreeMB
     ,Convert(decimal(18,2), (@size/128 - CAST(FILEPROPERTY(@logical_name, N''SpaceUsed'') AS INT)/128) / (@size * 8 / 1024.1) * 100) as SpaceFreePerc
     ,stat.size_on_disk_bytes / 1024 / 1024 SizeOnDiskMb
     ,drv.volume_mount_point, drv.logical_volume_name, drv.available_bytes, drv.total_bytes
 FROM sys.master_files F
 inner join sys.dm_io_virtual_file_stats(NULL, NULL) stat on F.database_id = stat.database_id AND F.file_id = stat.file_id
 CROSS JOIN sys.dm_os_volume_stats(@database_id, @file_id) drv
 WHERE F.database_id = @database_id AND F.file_id = @file_id;';

OPEN c;
FETCH NEXT FROM c INTO @database_id, @file_id, @logical_name, @physical_name, @size;

WHILE (@@FETCH_STATUS <> -1)
BEGIN
  SET @exec = DB_NAME(@database_id) + N'.sys.sp_executesql ';
  INSERT #x EXEC @exec @sql, N'@database_id int, @file_id int,
    @logical_name sysname, @physical_name nvarchar(520), @size int',
    @database_id, @file_id, @logical_name, @physical_name, @size;

  FETCH NEXT FROM c INTO @database_id, @file_id, @logical_name, @physical_name, @size;
END

SELECT * FROM #x;

CLOSE c; DEALLOCATE c;


Some notes on MaxTimeMS behaviour as at MongoDB 2.6:

MaxTimeMS sets a cumulative time limit in milliseconds for processing operations on a cursor.
Network latency and idle time for a cursor do not count toward MaxTimeMS.
After the MaxTimeMS is reached, the operation will be killed at the next safe interrupt point (for example, when that operation yields). This means that an operation can exceed MaxTimeMS if it is not currently interruptible.

From your profile output the query held a read lock for 334 microseconds and yielded once, so it would appear to be well below the MaxTimeMS threshold. Effectively, this isn't an inherently "slow query" to execute but it is likely being affected by other activity on your system.

A properly indexed query returning a single result is unlikely to be slow; the MaxTimeMS option is much more useful for a poorly indexed query or a long running query that returns many results.

For a forced example of a slow executing query to test MaxTimeMS you could use a JavaScript function, eg:

// Add some test documents in the collection
db.mycoll.insert([{sleepy:1}, {tired:1}, {zzz:1}, {xyzzy:1}])

// A query that sleeps for 1s on each document found
db.mycoll.find({$where: "sleep(1000) || true"}).maxTimeMS(50)

I would suggest looking at the query performance in aggregate as there are should be other performance metrics that correlate. You should ideally track your MongoDB metrics over time using a monitoring system. For monitoring suggestions, see Monitoring for MongoDB in the manual.

Asya's presentation on Diagnostics & Debugging also provides a good overview of performance troubleshooting tools and how they were used to solve some specific performance scenarios.


sql server database size shown different in properties pane

Properties dialog shows data and log file size. It's the same as sp_spaceused without any arguments. Use sp_spaceused table returns size of the table (i.e., part of data file) - it never takes into account transaction log - that's the missing part in the discrepancy you see.


Visual Studio 2013 and up - Database Project Schema Compare File

Short answer, maybe. If being able to open a previously saved .scmp file makes life easier through time savings or consistency, I'd say it's worth saving.

With a schema compare window open, there is a cog wheel icon which opens the Schema Compare Options dialog. There are quite a few options under General and Object Types. Some are selected, others are not selected, by default. If you find yourself repeating compares that require changing the same default settings each time, that would be a compare worth saving.

The compare also saves source and target. If one found that when comparing one's local machine.MyDB to Development.MyDB, one didn't want to see differences in ObjectType X, but one did want to see those differences when comparing Development.MyDB to QA.MyDB, one could save two separate compares.

A .scmp file can also be added to source control, if one found value in doing that as well.

There very well could be other advantages I'm not aware of, but that's about the extent of my limited experience with database projects so far.


Architectural Question about Data analytics

Have your OLAP structures already in place (star schemas, fact and dimension tables).
Populate your OLAP  structures with a feed from your OLTP systems.  This can take quite a while the first time through but this feed is where the transformations happen.  Once all of the data has been transformed and loaded into the fact and dimension tables, you use the same logic to run but only bring in new or updated data.  This is a trickle update.
Once your data is in the OLAP structure, build your cubes.  Only process the updates if you can.

The biggest mistake I see is people relying on tools and wizards to accomplish this.  They end up importing all historical data every day and completely rebuilding their cubes from scratch.  They are the ones that also complain that it takes hours and hours for their data to be ready for reporting.

Wizards will only get you so far.  If you are planning on building a reliable robust reporting system, you have to get your hands dirty and start writing some SQL code.


Use the modern aggregate FILTER syntax in Postgres 9.4 or later:

SELECT userid,
       COUNT(*) FILTER (WHERE version_identifier LIKE '%ios%') AS nr_ios,
       COUNT(*) FILTER (WHERE version_identifier LIKE '%android%') AS nr_android
FROM   gl.user_device
GROUP  BY userid;

It's shorter, clearer and a bit faster. See:

For absolute performance, is SUM faster or COUNT?

But it still results in a sequential scan on the whole table.

If ...

the table is big and performance is important,
a substantial percentage of rows does not contribute to either count,
and you have a matching index - a trigram index on version_identifier in your case, see:

Full Text Search With PostgreSQL

... then run two separate queries instead to tap the full potential of the index. Example with two subqueries in a FULL OUTER JOIN to make it a 100% equivalent drop-in replacement:

SELECT userid
     , COALESCE(nr_ios    , 0) AS nr_ios
     , COALESCE(nr_android, 0) AS nr_android
FROM  (
   SELECT userid, COUNT(*) AS nr_ios
   FROM   gl.user_device
   WHERE  version_identifier LIKE '%ios%'
   GROUP  BY 1
   ) i
FULL JOIN (
   SELECT userid, COUNT(*) AS nr_android
   FROM   gl.user_device
   WHERE  version_identifier LIKE '%android%'
   GROUP  BY 1
   ) a USING (userid);

Related:

PostgreSQL merge two queries with COUNT and GROUP BY in each


The only scenario I can find that no results would be returned is if no row was updated. Architecturally speaking if that is actually your code and I would not bother making Stored Procedures that simplistic, just embed the SELECT query in the original Stored Procedure. Due to how SQL works if you start to get more complex with your queries trying to use certain Object Oriented habits by turning every duplicated or close to duplicated query into a Stored Procedure call can quickly become troublesome.


See entire string from a lengthy text field in pgAdmin?

Open pgAdminIII,
Select your database,
Use the Magnifying Glass button, i.e. "Execute arbitrary SQL queries"
Instead of running your query using the green triangle  "Execute Query", choose
the button two to the right of that - i.e. "Execute Query, write result to file".
Choose your destination, then you can view arbitrary length text in your chosen file destination.
You can also use psql which will show text, but will scroll if that's any use. Also, there would be the possibility of scripting your query output using different delimiters.


sql 2008, select from a view as a different user, like owner

Use a table valued function instead?

It has to be a multistatement function though.
In-line UDFS don't allow EXECUTE AS (I guess because they are simply macros like views)

CREATE FUNCTION dbo.vUsers ()
RETURNS @dbprincipals TABLE (
    name sysname NOT NULL,
    type char(1) NOT NULL
    )
WITH EXECUTE AS OWNER
AS
BEGIN
    INSERT @dbprincipals
    SELECT name, type FROM sys.database_principals;

    RETURN;
END
GO


Alter the name of the file so it ends in .bak or select 'all files' when browsing to the backup file.


T-SQL command to log in as a different user?

You can use the EXECUTE AS command:

http://msdn.microsoft.com/en-us/library/ms181362.aspx


Do I need different linked server remote users?

The technical answer

To my knowledge, there's no problem in using the same user for all "client" machines. The same user account can connect to a SQL Server as many times as the licensing of that SQL Server allows.

The security perspective

However, you may want to review the security aspects of this setup - do all the remote machines have the same access requirements, or should they have different users for security reasons?

Could you perhaps even use windows/AD authentication? Remember that when you hard-code an account into the linked server, pretty much anyone who can log on to the remote server will gain the privileges on the "central" server that you've given to the user account defined on the linked server, which may lead to a privilege escalation.

Tread carefully with linked servers and permissions, is all I'm saying.


Connect MySQL workbench with MySQL container

It looks like you ran the container without setting the -p 3306:3306 parameter. Also, it seems that you mistyped the IP address in of the Hostname in MySQL Workbench. I recommend starting from scratch by following these instructions:

Run the mysql server. (Change the password admin to anything you want)

docker run -e MYSQL_ROOT_PASSWORD=admin --name mysql -d -p=3306:3306 mysql

get the IP address of the default docker virtual machine by running

docker-machine ip default

Copy the IP address that you'll get as the result of the previous command. (in your case, it should be 192.168.99.100)
Open MySQL Workbench and create a new connection. Paste the IP address that you copied into the "Hostname" field.

You should be all set now.


Insert Into Array of object MongoDB Ask Question

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


How can I make SQL Server Agent job history available after failing over an AG?

I have mastered a mechanism to ensure that jobs are disabled on the secondary replica and enabled when a failover takes place […]

Hopefully that's using sys.fn_hadr_is_primary_replica in the job/jobstep and not actually enabling and disabling jobs.

  […] but now realise that, once this happens, no job history will be available.

You are correct. Job history is stored in MSDB which is a system database, by default on SQL Server 2017 and below system databases cannot be part of an availability group. This means the job history will only be local.

The only way, currently, to change that is to either write your own code to insert the job history into a user database (such as a dba tools database) or run the jobs from their own independent job server (which may be some enterprise job scheduler). There is nothing built in to do this automatically.

  Job history is essential to determine what hadn't run or failed on the now defunct primary and needs to be run on the new primary.

I would suggest building your own job logging table and writing the necessary logging as part of the jobsteps in each job so that you can know. This isn't just an AG thing, it's a where should I start and stop my jobs based on what I've already done thing and that's the root issue. Creating your own logging in a user database will solve this problem, though it's not the only way (such as using job scheduling software on another server).

Edit: I debated whether or not to put this in because while it is officially announced, all of the details behind the feature aren't public and are subject to change. However, in SQL Server 2019 (which isn't out yet, CTP 2.3 as of this writing) there are System AGs which do have master and model as part of the availability group. Since this functionality isn't currently available, the public documentation isn't available, and some items are subject to change, I hate to put this as an option but included it for completeness.


InnoDB always saves the previous version of the row. Everything is copy on write therefore every update is a select -> copy -> write. For how long it is being kept is dependent on

the open transactions (obviously as long as you have an open transaction that needs to be able to see an old version it cannot be removed)
how busy your server is (purge lag)

Purge behaviour had been improved a lot in the recent version. Look at dev.mysql.com for more info if you're interested.
I'm not sure what you mean by help. You read first and update next so I don't see too much benefit. You most likely don't want to go below READ-COMMITTED isolation level. That will already eliminate a lot of locking issue (gap locks and next-key locking).
InnoDB redo log contains every change that happens in the innodb tablespace to be able to recover in case of a system failure. The format is: page pointer offset and the changed bytes. So to answer the explicit use case if the previous version is written to the undo space (which it is) then InnoDB will write that change to the log files too.

I hope this helps!

If you want to look into the details more I always recommend people to play with innodb_ruby that is a very handy tool to look into innodb files and understand the basic principles of operations.


What does numeric operations mean in a sum in sql expression?

COUNT(*)*100 in this scenario seems to be how the developer dynamically gets the percentage based on how many records are returned.

Maybe it's better read this way:

((
    SUM(rating IN (4.5,5)) --SUM of ratings of 4.5 and 5
    /  --divided by
    COUNT(*)  --The total number of records
  )
*100) --Multiplied by 100 to make it readable as a percentage
AS pct_5_star

So if the query returns 100 records, it divides the SUM by 100 then multiplies thatresult by 100 to get an easily readable number.

if the query returns 150 records, it divides the SUM by 150 then multiplies that result by 100 to get an easily readable number.


How can I avoid repeating the same condition in a "when matched then update" clause?

I would recommend simply avoiding MERGE in the first place for all of the reasons listed here and just simply use a standard update statement.

UPDATE A
SET
    Col1 = b.Col1 + b.Adjustment
    ,Col2 = b.Col2 + b.Adjustment
    ,Col3 = b.Col3 + b.Adjustment
FROM ATable as A
inner Join TableB as B
    ON a.ID = b.ID
    AND b.NeedsAdjustment = 1
;
UPDATE A
SET
    Col1 = b.Col1
    ,Col2 = b.Col2
    ,Col3 = b.Col3
FROM ATable as A
inner Join TableB as B
    ON a.ID = b.ID
AND b.NeedsAdjustment <> 1
--Or INSNUL(NeedsAdjustment,0) If it's a nullable column
;

In the long run this would be a more stable approach than using MERGE. I've also added the <> because if that's not there your UPDATE will overwrite the first UPDATE.


Can mysql restore a single table from a large mysqldump?

I have found two solutions, one using

grep -n "Table Structure" mydump.sql
# identify the first and last line numbers (n1 and n2) of desired table
sed -n n1,n2p mydump.sql > mytable.sql # (e.g. sed -n 48,112p)

and one using awk

awk '/Table Structure for table .table1./, /Table structure for table .cultivars./{print}' mydump.sql > mytable.sql


pgadmin: can't insert jsonb[] values with strings

The answer is: in pgadmin you have to escape every quotation mark before and after key/value definition.
What a sophisticated solution.

{
    "{\"one\": \"two\"}",
    "{\"three\": \"foure\"}",
    "{\"fivep\": \"six\"}"
}


You can get the average run time since last restart, last run time, shortest run time  and longest run time among all run. One catch here is the plan still have to reside in your plan cache.

  total_elapsed_time

  Total elapsed time, in microseconds, for
  completed executions of this stored procedure.

  last_elapsed_time

  Elapsed time, in microseconds, for the most
  recently completed execution of this stored procedure.

  min_elapsed_time

  Minimum elapsed time, in microseconds, for any
  completed execution of this stored procedure.

  max_elapsed_time

  Maximum elapsed time, in microseconds, for any
  completed execution of this stored procedure.

You can also change ORDER BY clause to order it by read, write etc.

Reference:

https://docs.microsoft.com/en-us/sql/relational-databases/system-dynamic-management-views/sys-dm-exec-procedure-stats-transact-sql

SELECT TOP 10 d.object_id, d.database_id, OBJECT_NAME(object_id, database_id) 'proc name',
    d.cached_time, d.last_execution_time, d.total_elapsed_time,
    d.total_elapsed_time/d.execution_count AS [avg_elapsed_time],
    d.last_elapsed_time, d.execution_count
FROM sys.dm_exec_procedure_stats AS d
ORDER BY [total_worker_time] DESC;


You cannot rsync a running database, but you know that since you said you stopped it.

I'd you rsync with the mysqld not running (or from a snapshot), you have to rsync the ib_logfile* and ibdata* files from the datadir. Then there is no need to DISCARD/IMPORT TABLESPACE either.

For a copy without shutting down the database, xtrabackup is probably the most efficient solution.

If you have existing databases on the target, you will need to mysqldump --no-data dbname and import on the target in order to re-create the table structure metadata, but you will have FLUSH TABLE ... FOR EXPORT in the source before copying across. Then they will be clean and can be imported with ALTER TABLE ... IMPORT TABLESPACE


Postgres Backup queries on AWS after reading Postgres docs

EBS snapshots are atomic, they're a point-in-time. This means that for PostgreSQL, so long as all the datadir including WAL, and all tablespaces are in the same EBS volume, starting from a snapshot is equivalent to starting after an unclean shutdown (crash), which PostgreSQL is designed to do reliably every time.

You do not need to stop I/O to the volume to take an EBS snapshot if everything is in the same EBS volume.

You also don't need to stop I/O if you're using pg_start_backup and pg_stop_backup along with file-level copies, but you must capture all the WAL files up to the one generated after the end of pg_stop_backup. See the manual.

As for S3 storage, etc, check out barman; it can automate most of this for you and supports hook scripts you can use to put backups in S3.


Offsetting reporting Load from one server to another

One issue with your trigger implementation is that if the reporting DB or DB Server goes down for any reason or stops accepting requests, your prod systems will stop as well.  Also if you make schema changes note that you'll need to do that on the reporting table as well.  Your solution will work, it just isn't ideal.  SSIS won't help with the schema changes but at least if the reporting server can't accept requests prod will still keep working.

Replication is nice too if you have a primary key on the table and meet all the replication requirements.  Just note that replication (IMO at least) requires more experience to fully understand than a pure AlwaysOn or SSIS solution.  Replication requires agents and extra security.

SSIS could work at the frequency you're using it for, maybe it just isn't ideal.  If you're OK with losing in flight transactions when the server reboots you can look at Service Broker which will be much more near real time but Service Broker has a even higher learning curve than AlwaysOn.

Personally I would stick with Replication or your current SSIS solution.  Both require some tuning and testing but both have their advantages as well.  SSIS at least won't deal with all the security and agents that replication requires.

Are you open to using 2014 standard edition read only replica in an AG?  It gives you a lot more options, let's you query the entire database, gives you a nice near real time complete copy, and isn't too difficult to setup but absolutely much more difficult to setup than a trigger.  Edit: Sorry I just saw that you tagged SQL 2008 R2.


Enable user access to all DBs and tables in MS SQL server managment

Unless the user is given the system adminstrator (sa) role, you will need to add a user to each database which is mapped to the login and which gives the correct permissions just for that database.

Be very careful creating users with global access to databases, it is the start of a slippery slope to a major hack!


Searching a database safely using SELECT

You have used parametrized query, which is the bare minimum expectation to prevent on SQL-Injection.

I would suggest some points you can take care of as a validation in C# code

I don't see any server side validation on the text you are getting from TextBoxSearch.Text, you can check for some set of character that can be an attempt for SQL Injection.

you can have a look at the set of input for SQL Injection and make sure that those characters are not there in input string before executing sql query.

Other suggestion is that if you are looking to have multiple joins etc in sql query a better choice is to convert it to Stored procedure.


Do I have to retype information every time I make a mistake in SQLPlus

Several ways.

You can set the EDITOR environment variable before running SQL*Plus (Assuming Unix), to allow use of an external text editor (vi, by way of example):

export EDITOR=vi

Then type ed in SQL*Plus to edit the previous query in the vi editor. Of course, you may prefer nano, pico, emacs, vim etc etc.

If you don't wish to set an environment variable, you can add define _editor=vi to your $ORACLE_HOME/sqlplus/admin/glogin.sql file instead.

My favourite way of achieving SQL*Plus edit heaven (in Unix) is by using rlwrap. This adds readline support to SQL*Plus and allows you to easily edit within SQL*Plus itself.

Install rlwrap from your distributions package repository (or download & compile from the website here), then add the following to your shells startup script (.bashrc/.bash_profile or similar):

alias sqlplus="rlwrap -i sqlplus"


Slave (SQL thread) replicates very slowly

(Too many questions for 'comments'.  Anyway something here might help locate the naughty.)

Backup occurring?

sync_binlog - Probably 0 is OK for Slave.

innodb_flush_log_at_trx_commit - may as well be 2 on a Slave.

Differences in OS config, especially relating to I/O?  Swappiness?

Are clients hitting both Slaves?  In the same proportion?

How much RAM?

query_cache_size        = 512M -- This is bad for any system.  Limit it to something like 50M.

tmp_table_size=1G -- This is dangerous.  Some complex queries could chew up RAM and cause swapping, which is really bad for performance, especially I/O.  Keep that down to, say, 1% of RAM.

You have the slowlog turned on -- Look for clues there.  If you don't find anything, then turn ON log_slow_slave_statements also.

Is Seconds_behind_master increasing or oscillating?


Should I split a single table with an AUTO_INCREMENT PRIMARY KEY into two tables that can be joined on that PRIMARY KEY?

It's hard to look at a table that has this many columns, so I
  frequently use a SELECT statement to look at 34 of the fields that I
  mentally categorize as 'group 1' and 12 fields that I categorize as
  'group 2'.

Create a view.


Run:

lsnrctl status LISTENERNAME

... where LISTENERNAME is the name of the listener that is listening on port 1531. You can get this name from your $ORACLE_HOME/network/admin/listener.ora file.

For example:

LISTENER1531 =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS_LIST =
        (ADDRESS = (PROTOCOL = TCP)(HOST = node1)(PORT = 1531))
      )
    )
  )

LISTENER1531 would be the listener name for the above listener.ora entry.

If you need to add databases to the listener, add an entry to the listener.ora, as follows:

SID_LIST_LISTENER1531 =
  (SID_LIST =
    (SID_DESC =
      (GLOBAL_DBNAME = YOURDB)
      (ORACLE_HOME = /u01/app/oracle/product/11.2.0/dbhome_1)
      (PROGRAM = extproc)
      (SID_NAME = YOURDB)
    )
  )

Obviously you'll need to add your own database and listener names & change the path to your $ORACLE_HOME.

You can reload the listener config with:

lsnrctl reload LISTENER1531

The above works fine for me with a `tnsnames.ora as follows:

YOURDB =
 (DESCRIPTION =
   (ADDRESS_LIST =
     (ADDRESS = (PROTOCOL = TCP)(Host = node1)(Port = 1531))
   )
 (CONNECT_DATA =
   (SERVICE_NAME = YOURDB)
 )
)


How can I prevent the SQL Server Query Optimiser splitting a remote query?

Without seeing the query plans we can't answer in detail. There is likely a query hint you can use to encourage the query planner to use the alternate plan.

Another option is to control it from the other side: create a view in the remote end that does the work and select directly from that at the local side.

Update Once Query Plans Presented:

The warnings in the plan are often good clues, that is why they are there. In this case: Type conversion in expression (CONVERT_IMPLICIT(varchar(20),[NET2-1\NET2].[Net2].[dbo].[Users].[Field14_50],0)) may affect "Cardinality Estimate" in query plan choice.

It would appear that a difference in types between the source [NET2-1\NET2].Net2.dbo.Users.Field14_50 and the target #Net2Data.UserID is causing confusion. Without knowing the source type I suspect it is NVARCHAR(<something>). Perhaps you could add an explicit cast in the SELECT clause or change the temporary tables definition to match so there is no need for conversion at all. Also, if the source column in question is an NVARCHAR make the literal in the filtering clause explicitly a multi-byte string (LIKE N'[a-z][a-z][a-z]%' in place of LIKE '[a-z][a-z][a-z]%').


I believe you are asking about a table design or schema.

Whether you use 1 or many tables is up to you. The increased complexity of normalisation is a trade off between efficiency and convenience.

The easiest option to begin with is to use a single table to describe the car, and create a field for each piece  of information you want to store. Start with number plate, make, model, year...

If you want to limit or restrict the data that can be entered you can add reference tables with foreign key constraints.

If your model is going to be be detailed enough to describe things like engine models, individual parts or a detailed service history then you will probably want to use seperate tables.

The key question here when thinking about the data is: "When i think about one car, does it have one or many of these things?" That will give you a good indication if you will need to add a table for that 'thing'

Sometimes we dont have all the information we would like to have. Thats just a fact of life. You can either collect the missing info, or design your table(s) so that the data is not mandatory.

You need to decide if the lack of detail is going to be a problem for you. Can you achieve want you want with the data you have? Will more data improve the accuracy or output of your database?


append 0 returned by DATENAME function Sql Server management studio

You could do something like this:

SELECT RIGHT('00' + CONVERT(VARCHAR(2), datename(mi,GETDATE())), 2)


How to backup MySQL accross multiple servers

Backup Solution: There are several tools and methods to perform Backup & restore operations. You can refer official documents for more information. If you want to use third party tool then Percona XtraBackup is the most popular tool.

Automatic Backup: You need to create schedulers to take backup by executing the backup scripts. If you are using the linux based servers then it is recommended to create incremental backup scripts, executed by CRON jobs on scheduled interval.

Failover Handling: To take care of failover event ( i.e. main server is down ) you need to prepare high availability configuration, so that backup server can take place of main server. You can read official guide about it.

I prefer clustering based solution to take care of High Availability:


SQL, list if has at least one value for each property

This is a start

select Milga.*
from Milga
join ( select MilgaId, count(dictinct(PropertyId)) as cnt
       from Milga
       group by(MilgaId) ) m1
  on Milga.MilgaId =  m1,MilgaId
join ( select MilgaId, count(dictinct(PropertyId)) as cnt
       from Milga PropOptionId is not null
       group by(MilgaId) ) m2
  on m2.MilgaId =  m1MilgaId
 and m2.cnt     =  m1cnt

if the count is the same then all PropertyId have a value


Oracle: How to get a list of CTXSYS.CONTEXT indices?

USER_INDEXES (and ALL_INDEXES) contains this information:

select index_name, table_name, index_type
from user_indexes
where ityp_owner = 'CTXSYS'
  and ityp_name = 'CONTEXT'


How do I create a simple SELECT statement as a stored procedure in PL/SQL? - Regardless of the best practises around Stored Procs

So what is your question? You posted your answer already:

CREATE OR REPLACE PROCEDURE getStudents
AS
C1 SYS_REFCURSOR;
BEGIN
OPEN C1 FOR
SELECT * FROM DUAL;
DBMS_SQL.RETURN_RESULT(C1);
END;
/

Procedure created.

SQL> exec getStudents

PL/SQL procedure successfully completed.

ResultSet #1

D
-
X

SQL>


Counting occurrence a record based on consecutive days

I'd argue your results are actually...

stationary_id consecutive_occurrence
3             2
2             1

You have 3 days present. Only two of those days are consecutive (04/02 and 04/03), meaning there was a day preceding them.

I'm making a few assumptions...

Each record in the table is an occurrence; stationary was delivered that day
A record is marked as a consecutive delivery, if (and only if) there was a delivery the prior day.
Since we don't have data for 03/31, we can't say whether deliveries on 04/01 were consecutive.

Generating sample data matching your post...

create table #thinger ([date] date, stationary_id tinyint);
go
insert into #thinger ([date], stationary_id) values ('2019-04-01', 2);
insert into #thinger ([date], stationary_id) values ('2019-04-01', 3);
insert into #thinger ([date], stationary_id) values ('2019-04-01', 4);
insert into #thinger ([date], stationary_id) values ('2019-04-01', 5);
insert into #thinger ([date], stationary_id) values ('2019-04-02', 3);
insert into #thinger ([date], stationary_id) values ('2019-04-02', 2);
insert into #thinger ([date], stationary_id) values ('2019-04-03', 3);
go

Here is a quick and dirty query to retrieve the summation you asked for...

;WITH consecutive_stationary_orders AS (
    SELECT cday.[stationary_id]
         , (
            select 1
              from #thinger pday
             where pday.[date] = DATEADD(day, -1, cday.[date])
               AND pday.stationary_id = cday.stationary_id
           ) AS was_consecutive
      from #thinger cday
)
SELECT [stationary_id], SUM(was_consecutive)
  FROM consecutive_stationary_orders
 GROUP BY [stationary_id]
HAVING SUM(was_consecutive) IS NOT NULL;

Forgive me. I used SQL/T-SQL syntax as I don't have a MySQL instance at this very second to use. But, this should give you an idea of what I'm doing.

First result set returns a list of dates, stationary issued (by ID), and whether that day's delivery was consecutive. The second query (wrapper) aggregates the first query. Whatever RDBMS you use, that's the approach I'd take.


replication filtering replicate-rewrite and do-table Mysql 5.6 (innodb engine )

As you already mentioned documentation that you cannot use table level filtering on replication.
You might like to try another approach instead.
Replicate whole database and change all tables except those you need to use to BLACKHOLE engine.

alter table X engine=BLACKHOLE;


How to change DB2 Demo license to a free license (Express Edition)

DB2 Express edition is (actually was, as it's not available anymore) not free; DB2 Express-C is the free edition.

If you license a paid edition of DB2 its license file will be available for download from the usual source, IBM Passport Advantage. DB2 Express-C license is not available separately, it can only be had by downloading and installing the Express-C package, at which point you might as well just use it for your POC.

To install a license file you'd use the db2licm (license manager) utility by running this as the target instance owner:

db2licm -a <path to license file>

You may want to double-check the demo license agreement though -- it probably says that you must remove the demo installation after its license expiration, which would mean that installing the Express-C license into it, though it might be technically possible, would violate the agreement.

Update: According to the documentation you can continue using the installed product after the trial license expiration by registering a valid license file; it looks like adding the Express-C license should be OK. Note though that if you have used in the trial any of the features unavailable in the Express-C edition, your installation may behave erratically or even fail to start.


Update Table A row if Table B row is changed

I don't think you need to use a trigger for this at all. With an index on B(UserID) INCLUDE(Sales) (or if there is already a clustered index leading with UserID), this query will get what you need, pretty efficiently, without having unnecessary maintenance happening all the time (even when no queries are running to calculate sums):

SELECT a.UserID, a.Name, SalesSum = SUM(b.Sales)
  FROM dbo.[Table A] AS a
  INNER JOIN dbo.[Table B] AS b
  ON a.UserID = b.UserID
  GROUP BY a.UserID, a.Name;

Look ma, no subquery!

This will generate a different plan, likely, but certainly not a more expensive one. I don't think you're going to get as much benefit maintaining this sum using triggers as you think you will. Make sure you test your entire workload, not just the query that gets the sum, if you implement triggers.

That said, here is what a trigger would look like.

CREATE TRIGGER dbo.MaintainRedundantSums
ON dbo.[Table B]
FOR INSERT, UPDATE, DELETE
AS
BEGIN
  SET NOCOUNT ON;

  UPDATE a
    SET a.SalesSum = COALESCE(SUM(b.Sales), 0)
    FROM dbo.[Table A] AS a
    LEFT OUTER JOIN dbo.[Table B] AS b
    ON a.UserID = b.UserID
    WHERE EXISTS (SELECT 1 FROM inserted WHERE UserID = a.UserID)
       OR EXISTS (SELECT 1 FROM deleted  WHERE UserID = a.UserID);
END
GO

Please test this trigger in a test environment; this is fairly off the cuff, partly because it's dinner time and partly because I genuinely don't want you to use a trigger for this. If you experience an actual performance problem when calculating the sums at runtime, let's talk about that, instead of premature optimization.

EDIT

Regarding your "slow" join, how does this perform in comparison?

;WITH d AS
(
  SELECT OrderID, SalesAmount = SUM(Sales), SalesPriceSum = SUM(Sales*Price)
    FROM dbo.OrderDetails
    GROUP BY OrderID
)
SELECT
  u.UserID,
  u.Name,
  o.OrderID,
  o.Comment,
  d.SalesAmount,
  d.SalesPriceSum
FROM
  dbo.Users AS u
INNER JOIN
  dbo.Orders AS o -- meaningful aliases please, not A, B, C etc.
  ON u.UserID = o.UserID
INNER JOIN
  d ON d.OrderID = o.OrderID
-- WHERE ...

-- no GROUP BY needed here
ORDER BY
  u.Name,
  o.Period;

You should look at the query plan and see if the cost is mostly in the sorting. I expect that you don't have an index to support ordering by u.Name first, for example. Also I would verify that you have indexes to support your WHERE clauses and JOINs, and I certainly hope that OrderDetails.OrderID is indexed appropriately. If you want help improving the performance of a query (or set of queries), post the queries and their actual (not estimated) execution plans. Jumping to the conclusion that a trigger must be the way to fix it is kind of like buying a new car when you have a flat tire. Fix the problem, don't try to outfit a solution for the whole system.


Is MySQL replication suitable for a Write Mostly application?

MySQL Replication would probably victimize you in terms of Replication Lag.
If you run SHOW SLAVE STATUS\G on a Slave whose Master is write-heavy, the data you need to read may not be ready to read on the Slave until the Replication Lag (Seconds_Behind_Master) is 0.
SUGGESTION
If you want reads available on a Slave as well as on a Master, consider using Percona XtraDB Cluster. It does synchronized replication so that the same data exists at any point in time on both Master and Slave. It does not require binary logging or MySQL Replication. In addition, you can use a LoadBalanced DBVIP against Master and Slave to distribute reads.
The only drawback is that this might produce slower writes.
Provided all data is InnoDB, mysqldump backups can be done against the Slave using --single-transaction.
Here are my past posts on Percona XtraDB Cluster

Aug 20, 2012 : Best solution for cross-datacenter MySQL master-slave replication
Jul 25, 2012 : Scaling Percona datacenters: setup and replication
Aug 26, 2011 : MySQL Master/Slave Master/Master setup?


Sorry to say it, but best practice is to always qualify object names with the schema anyway.

This is mandatory for things like schemabound views.

Why is it best practice? See this by Tibor Karaszi or this by Midnight DBA or just trust me or the MS SQL Server Best Practice Analyzer

After comment...

Have you considered synonyms to make OtherSchema.mycode point to [DOMAIN\Glen].MyCode?

CREATE SYNONYM OtherSchema.mycode FOR [DOMAIN\Glen].MyCode