How to avoid duplications using a function

I have re-tested your function and it seems to work as advertised.

If, in fact a duplicate value was entered, you would get an exception that would roll back the transaction.
The function is designed to trap such an exception (UNIQUE_VIOLATION) and to try again, which would find the (now) existing row and return the existing id.

Your report of "duplications" doesn't seem possible. There must be a misunderstanding somewhere.

I only have minor improvements to offer:

Make that a UNIQUE CONSTRAINT, that's cleaner than a unique index, while doing the same for the case at hand. Since you already have a unique index, you can use the special form:

ALTER TABLE univ.landings ADD CONSTRAINT landings_name_key UNIQUE
USING INDEX index_name univ.uniq_index_landings_on_name;

You can simplify the function a bit. Your additional variable landing_name is just a waste:

CREATE OR REPLACE FUNCTION univ.gc_landing(_name text, OUT landing_id int)
  RETURNS int AS
$func$
BEGIN
LOOP
   BEGIN
   WITH sel AS (
     SELECT id
     FROM   univ.landings
     WHERE  name = _name
     )
   , ins AS (
     INSERT INTO univ.landings (name)
     SELECT _name
     WHERE  NOT EXISTS (SELECT 1 FROM sel)
     RETURNING id
     )
   SELECT id
   FROM   sel NATURAL FULL OUTER JOIN ins
   INTO   landing_id;

   EXCEPTION WHEN UNIQUE_VIOLATION THEN
       RAISE NOTICE 'It actually happened!';
   END;

   EXIT WHEN landing_id IS NOT NULL;
END LOOP;
END
$func$  LANGUAGE plpgsql;

But note that this function is designed for individual inserts. For bulk operations like you mention, a different approach might be much more efficient: fold duplicates in the source before you bulk-insert into the target table.


Thousands of After Insert and Update Triggers Causing Performance Issues

All Triggers fire within the scope of the same Transaction that the INSERT statement that generated them runs in. Therefore if the Transaction of the INSERT statement you executed ran to completion, then so did the After Insert Trigger that fires from that INSERT statement. Here's some straight to the point information and tests that prove this out:

So in short a trigger executes in the context of the calling transaction and a rollback in a trigger will rollback the calling transaction.

So my guess is either your original INSERT is still executing (which can then be aborted and rolled back) or the issue is somewhere between after your Trigger runs and the mechanism you're using to dump the data into RabbitMQ.
You can use sp_WhoIsActive to determine if your INSERT statement is still running and to get the SPID of the process so you can abort it and rollback. To abort you'd have to run KILL 123 (where 123 = the SPID of your INSERT).

Side note, if by 50,000 inserts, you mean 50,000 records in one INSERT statement, then that's small and should be performant. If you actually mean 50,000 separate INSERT statements then that's a different story that can take much longer to complete the INSERT.


Dropping duplicate|redundant Unique Constraint from FILESTREAM table

I was able to recreate this problem.

CREATE DATABASE Archive
ON
PRIMARY ( NAME = Arch1,
    FILENAME = 'c:\data\archdat1.mdf'),
    FILEGROUP FileStreamGroup1 CONTAINS FILESTREAM( NAME = Arch3,
    FILENAME = 'c:\data\filestream1')
 LOG ON  ( NAME = Archlog1,
         FILENAME = 'c:\data\archlog1.ldf')
GO
CREATE TABLE Archive.dbo.Records
(
    [Id] [uniqueidentifier] ROWGUIDCOL NOT NULL UNIQUE,
    [Chart] VARBINARY(MAX) FILESTREAM NULL
)
GO

ALTER TABLE Archive.dbo.Records WITH NOCHECK
  ADD CONSTRAINT testfilestream UNIQUE (ID)
GO
ALTER TABLE Archive.dbo.Records
DROP CONSTRAINT testfilestream

Best I can tell this is a bug and (1) SQL Server has a hard rule relating to tables with FILESTREAM columns that says "if they try to drop ANY UNIQUE constraint on the ROWGUIDCOL column in a table with FILESTREAM, don't let them" and/or (2) There only ever is one UNIQUE constraint on a column and making another is like making an alias.  So when you try and drop one, you're really dropping both.

As to how to solve it. Unfortunately, you can't add a second ROWGUIDCOL type column to a table, so that prevents a fix that's internal to the existing table.  You'll probably have to create a new table with the same schema, (minus the extra unique constraint), copy the data from the current table to the new table, then drop the old table and rename the new one back to the old name.


Did you set this on the database-level?  If so, then unless otherwise overridden in a query then no you don't need to specify READ_COMMITTED_SNAPSHOT again.

Setting transaction Isolation Level:

ALTER DATABASE YourDb
SET READ_COMMITTED_SNAPSHOT ON

I'm guessing you did the above?

Also, I hope you understand what exactly this Transaction Isolation Level entails.  It has to do with row versioning and is quite the hit on tempdb.  It's not always the answer to heavy "writing/reading".  Not to mention it utilizes row versioning, so you may not be getting what you think you should be getting.


Backup data only through command line in SQL Server

mssql-scripter

From https://blogs.technet.microsoft.com/dataplatforminsider/2017/05/17/try-new-sql-server-command-line-tools-to-generate-t-sql-scripts-and-monitor-dynamic-management-views/:

  You can use mssql-scripter on Linux, macOS, and Windows to generate
  data definition language (DDL) and data manipulation language (DML)
  T-SQL scripts for database objects in SQL Server running anywhere,
  Azure SQL Database, and Azure SQL Data Warehouse. You can save the
  generated T-SQL script to a .sql file or pipe it to standard *nix
  utilities (for example, sed, awk, grep) for further transformations.
  You can edit the generated script or check it into source control and
  subsequently execute the script in your existing SQL database
  deployment processes and DevOps pipelines with standard multiplatform
  SQL command line tools such as sqlcmd.

Install instructions on GitHub

Sample usage:

Generate DDL scripts for all database objects and DML scripts (INSERT statements) for all tables in the Adventureworks database and save the script to a file

$ mssql-scripter -S localhost -d AdventureWorks -U sa ––schema-and-data > ./adventureworks.sql


What's a good (free) client for MySQL on Windows?

Check this question on Stackoverflow, you will have some idea.

I use the client provide by Mysql itself, it's maybe not the best, but I can get my job done with it


How to use keys in a table so two columns are unique

If I understand correctly, a given photo will have a maximum of one record in photooftheday, so you could use photo_id as the primary key of that table.  That would ensure uniqueness.

EDIT: Added based on first comment

Basically you will need one more index:

ALTER TABLE `photooftheday`
ADD UNIQUE INDEX `idx_date` (`date`);

That will ensure no two values in the column date will be the same.  As far as optimizing for the query, with the addition of the unique index you will have it covered:  You want indexes on the columns used in joins  (photo_id from each table which, as PKs, are already indexed) and in the WHERE clause (which has only date, which we just indexed).


postgresql prepared statements with JSONB containment

the error we see is "operator does not exist: jsonb @> text"

I assume you are passing the JSON string via PreparedStatement.setString().
Postgres expects a JSONB value, not a text value. You can easily overcome this by casting the parameter to a jsonb:
select *
from tab
where jsonb_obj @> cast(? as jsonb)


Slow query on mid-size tables with good index coverage

In order to make very small temp tables, you need to rewrite the query to do this:

LIMIT first on the 20 most recent created timestamps
then JOIN those 20 ids to visits_views

Here is the proposed query

SELECT A.id FROM
(SELECT id FROM visits ORDER BY created DESC LIMIT 20) A
LEFT JOIN `visits_views` B ON A.id = B.visit_id;

I use LEFT JOIN to preserve the order of the subquery

If you want to count the views

SELECT * FROM
(
    SELECT A.id,A.created,COUNT(1) views FROM
    (SELECT id,created FROM visits ORDER BY created DESC LIMIT 20) A
    LEFT JOIN `visits_views` B ON A.id = B.visit_id
    GROUP BY A.id,A.created
) AA ORDER BY created;

I have addressed making small temp tables in queries like this. See my post Fetching a Single Row from Join Table in StackOverflow. It demonstrates how I did a LIMIT 40 on a subquery and used that 40 row result set to JOIN to other tables.


You will find a mapping file named tablespace_map in your base.tar file.

Here's the documentation:

  When tar format mode is used, it is the user's responsibility to unpack each tar file before starting the PostgreSQL server. If there are additional tablespaces, the tar files for them need to be unpacked in the correct locations. In this case the symbolic links for those tablespaces will be created by the server according to the contents of the tablespace_map file that is included in the base.tar file.

Link: https://www.postgresql.org/docs/9.6/static/app-pgbasebackup.html


Frankly, I probably wouldn't use a database in the first place.  It doesn't sound like you have any need for ACID, which is one the main things which databases provide and which doesn't come cheap in terms of performance.  Nor does it sound like you need complicated indexes.

What I would do (and have done, an awful lot) is just loop over the files with Perl or Python and use their built-in hash table features to generate your aggregates.  Since you have specified that the output is small, you should have little problem holding the intermediate aggregation state in memory for the duration.


PostgreSQL: Run EXPLAIN on all queries in pg_stat_activity

It's not exactly what you are looking for, but the auto_explain extension may be as close as you can get.  See auto_explain


Here is my working theory: Because you have the table data in InnoDB it may subjected to MVCC and Transaction Isolation in the midst of the InnoDB Infrastructure.

Please notice in my original post that I used MyISAM tables, which are not subject to ACID compliance.

Make a copy of the table, convert it to MyISAM, and run GetAncestry against it. You should find better performance.

Give it a Try !!!


What is better - having 10 databases for 10 organisations or 1 table for 10 organisation with org_id?

it all depends.

from my personal experience, if you have one database for each organisation, all the data is neat and clean and it is all in one place for each different organisation.

In case there are changes specific to a particular organisation it will affect only that particular organisation database - as the changes may or may not be welcomed for the other organisations.

On the other hand, it might cause extra overhead if for each new organisation you will need to create a new database, and that involves making sure you have space for backups, and scheduling time to run all the typical database maintenance routines, etc.

Basically different databases give you more flexibility (even the permissions set could be different) but it also might cause overhead.

There are strong points and not so good point in either approach.


Is subscriber_tag a many:many mapping table?  Ditto for segmentation_tag?  If so, there may be several ways to speed up the use of such on both versions.  See here.


Understanding SQL Server encryption (by password)?

There are really only two options:

Automatic key decryption Ie. the key hierarchy includes an encryption by the service master key (usually through the database master key) and the engine is able to decrypt and or encrypt the data when needed. This protects against accidental loss of media, but anyone with access to the running server has access to the encrypted data (subject to access permissions, not subject to cryptographic protection). Transparent Data Encryption TDE is the high-end version of this type of access.
Explicit session provided decryption key The application must ask the user for the key decryption password and open the keys explicitly in each session it uses. The engine itself cannot decrypt the data. This protects against any unauthorized access to the data by cryptographic means (even if one can access the engine and has access rights to read the data, he cannot use the data w/o actually knowing the password). Needless to say having the application prompt the user for password is annoying at best on desktop apps, and down right impossible in web apps (the web apps needs to store the password to use it between requests, and that's a huge problem).

These are the only alternatives. Never ever is there any need to have the password stored in any configuration file or stored procedure. Any scheme that requires to store the access password somewhere is doomed from the get go and you need to rollback to page one of your design and start from scratch.


Patching SQL Server 2014 Versus SQL Server 2014 Express

Are service packs for SQL 2014 different than the service packs for
  SQL 2014 Express?

There is no difference in service packs or cumulative updates between SQL Server Express edition and other editions such as developer, standard or enterprise.

When looking at the latest build of sql server 2014, SP3 CU4 is the last one with build number: 12.0.6329.1 (Latest build). I'm not sure where SP4 is coming from?

When looking at the applies to field from SP3 CU4:

  Applies to: SQL Server 2014 Developer SQL Server 2014 Enterprise SQL
  Server 2014 Enterprise Core SQL Server 2014 Standard SQL Server 2014
  Business Intelligence SQL Server 2014 Express SQL Server 2014 Web

You will find SQL Server 2014 Express

Download SP3 CU4 here

Remember that you would have to apply the service pack first, and then add the CU.
A list of sql server (2014) build versions can be found here, here and an overview of all the recent updates here .

EDIT

  ...I get the impression that I need to update to SP3
  1st THEN apply the CU4 patch. Since I'm starting with the base version
  of SQL Express (SP1 or SP2 not applied), can I just skip right to
  installing SP3 and then CU4 or do I first need to apply SP1 and SP2?

Since Service packs are cumulative you can directly install SP3 while skipping SP1 and SP2 and then install CU4.

  Also If I were to download SP3, I have a number of choices of files to
  download. Not sure which to pick. See options from this link:
  microsoft.com/en-us/download/details.aspx?id=57473 Appreciate any
  additional guidance you can provide...

The link provided is to download sql server express / local db / express with tools / express advanced edition with the SP3 installed when you don't have express installed.

You should download the regular SP3 patch, without express.

If you follow this link, you will see these two options:

Click on the one without express, SQL Server 2014 SP3

The ones with express are complete instances of sql server with the patch added.

Just to show you that I am not making this up, when scrolling down on the patch notes of SP3, go to the part: "Select the correct file to download and install"

Only install this one if you have no 64-bit version of SQL Server 2014 Express.

And for the correct file: (I think SP2 is a mistake, supposed to be SP3)

A patch for any Edition, express included. The Service pack you need, SP3 is this one .

Afterwards, download and install CU4  for SP3 from here


how can I create an where in query that only check on field and ignore another fields?

You could always do an inner join if you are looking for cleaner syntax.  Something like this?  But I wouldn't worry about it too much as long as the query is legible and the performance difference should be negligible.

SELECT U.*
FROM users AS U
    INNER JOIN (
                SELECT T.phone
                    , COUNT(T.id) AS c
                FROM users AS T
                GROUP BY T.phone
                HAVING c > 1
                ) AS PhoneCount ON PhoneCount.phone = U.phone
WHERE U.verified=true;


That can be done with a simple SELECT statement.

SELECT count(*) AS count_ , favorite_color_
FROM person_
GROUP BY favorite_color_
HAVING count(*) > 1
ORDER BY count_ DESC
;

The count(*) command is an aggregate function supported by Postgres and gives the number of rows with duplicate values in a column specified by the GROUP BY clause.

To omit the values without any duplicates, add the HAVING clause. A HAVING is similar to a WHERE but applies to each row being generated to represent the grouping.

To get results to sort with most frequent duplicate values at the top, use the ORDER BY clause. We reference the column alias count_ that we created in the first line with the AS clause.


WITH cte AS ( SELECT col1,
              FIRST_VALUE(col2) OVER (PARTITION BY col1
                                      ORDER BY col3 DESC) fv
              FROM table )
SELECT col1
FROM cte
WHERE fv = 53

If your server is more ancient than 2012, do the same by using ROW_NUMBER() and adding the condition rownumber=1.


How to optimize MySQL for large BLOB updates

This looks like a job for InnoDB Architecture Tuning !!!

Where can BLOB data become a bottleneck for InnoDB? Three places

Place #1 : InnoDB Log File

The size of the InnoDB Log Files (set by innodb_log_file_size) could be a bottleneck if there are many transactions being written that update BLOB data.

Place #2 : InnoDB Log Buffer

The InnoDB Log Buffer (set by innodb_log_buffer_size) can be a bad bottleneck if it it set smaller that the BLOB itself. The default is 8M. You may need to resize it to accommodate multiple rows that have that BLOBs. Perhaps 128M would be a good place to start. That can also reduce disk I/O when writing to the InnoDB Log Files.

Place #3 : The MySQL Packet

What is a MySQL Packet?

According to the Book

Page 99 Paragraphs 1-3 explains it as follows:

  MySQL network communication code was
  written under the assumption that
  queries are always reasonably short,
  and therefore can be sent to and
  processed by the server in one chunk,
  which is called a packet in MySQL
  terminology. The server allocates the
  memory for a temporary buffer to store
  the packet, and it requests enough to
  fit it entirely. This architecture
  requires a precaution to avoid having
  the server run out of memory---a cap
  on the size of the packet, which this
  option accomplishes.

  The code of interest in relation to
  this option is found in
  sql/net_serv.cc. Take a look at my_net_read(), then follow the call to my_real_read() and pay
  particular attention to
  net_realloc().

  This variable also limits the length
  of a result of many string functons.
  See sql/field.cc and
  sql/intem_strfunc.cc for details.

Anytime there is transmission of MySQL Data, the MySQL Packet channels MySQL Data into the OS and network. Like the InnoDB Log Buffer, the MySQL Packet must accommodate multiple BLOBs as well.

EPILOGUE

I have discussed this topics in the past

Nov 22, 2011 : MySQL query 'going away' on executing INSERT ON DUPLICATE UPDATE statement with a 12524 character blob
Aug 01, 2011 : How does max_allowed_packet affect the  backup and restore of a database?
Apr 27, 2011 : Changed max_allowed_packet and still receiving 'Packet Too Large' error

CAVEAT

Compression may have made things a little worse. Why ?

When compress an InnoDB table, any time data and index pages for that table is read, the page and an uncompressed copy of the page coexist in the InnoDB Buffer Pool. If you want to keep the table compressed, you must expand the InnoDB Buffer Pool (set by innodb_buffer_pool_size). I wrote about this as well as an update to an old answer of mine : innodb_file_format Barracuda

UPDATE 2013-07-19 16:11 EST

Based on your last comment

  Initial impressions: while tuning InnoDB buffers helped a bit, it seems like more improvement came from turning off binary logs for this particular table. Still monitoring the performance...

I have a suggestion

You may need to rearchitect your InnoDB files so that all .ibd files are on the data volume, while other MySQL components (ibdata1, ib_logfile0, ib_logfile1, all binary logs) could be mounted on another disk. I wrote about this before in a post about PostgreSQL : Postgres Write Performance on Intel S3700 SSD, The person asking that question said he got a 10% increase in performance.

UPDATE 2013-07-26 15:33 EST

Since I asked

  QUESTIONS : 1) How much RAM is on the DB Server? 2) What is the result of SHOW VARIABLES LIKE 'innodb_file_per_table';; 3) What do you get when you runSELECT SUM(data_length+index_length) FROM information_schema.tables WHERE engine='InnoDB';?

and you said

  8gb RAM, 4gb available to MySQL. 'innodb_file_per_table', 'ON'. SUM is 3316736000

I recommend the following (if you have not already done so)

Set innodb_buffer_pool_size to 3G
Set innodb_log_file_size to 1G

Give it a Try !!!


in the BIDS, open your project and double click on your DSV file, you can right click any where and add a table and then do the rest of steps like adding a relation

look at images

Step 1
Step 2

Is that what you meant


Does the @raise_snmp_trap do anything in @sp_add_alert?

msdb.dbo.sp_add_alert calls msdb.dbo.sp_add_alert_internal, which in turn writes values into the msdb.dbo.sysalerts table.

The sysalerts table insert looks like this:

INSERT INTO msdb.dbo.sysalerts
         (name,
          event_source,
          event_category_id,
          event_id,
          message_id,
          severity,
          enabled,
          delay_between_responses,
          last_occurrence_date,
          last_occurrence_time,
          last_response_date,
          last_response_time,
          notification_message,
          include_event_description,
          database_name,
          event_description_keyword,
          occurrence_count,
          count_reset_date,
          count_reset_time,
          job_id,
          has_notification,
          flags,
          performance_condition,
          category_id)
  VALUES (@name,
          @event_source,
          @event_category_id,
          @event_id,
          @message_id,
          @severity,
          @enabled,
          @delay_between_responses,
          @last_occurrence_date,
          @last_occurrence_time,
          @last_notification_date,
          @last_notification_time,
          @notification_message,
          @include_event_description_in,
          @database_name,
          @event_description_keyword,
          @occurrence_count,
          @count_reset_date,
          @count_reset_time,
          ISNULL(@job_id, CONVERT(UNIQUEIDENTIFIER, 0x00)),
          @has_notification,
          @raise_snmp_trap,
          @performance_condition,
          @category_id)

The @raise_snmp_trap variable is populated and inserted into the table in the flags column.  The documentation for dbo.sysalerts, at Microsoft Docs, says the flags column is "Reserved."

Since there appears to be no way to access SNMP properties, such as the "community name", "trap destination", etc through the user interface, I would guess SNMP is no longer supported.

In fact, according to Sean Gallardy - Microsoft (Premier Field Engineer):

  ...the definite answer is support was removed in 2000. Yes, at one point it existed but it no longer exists and even support when it existed was dubious at best. All extended stored procedures and associated DLLs were removed.


SQL Server AlwaysOn High Availability Maintenance Plans

The best way is to use Ola Hallengren's maintenance plans, because the ability to determine AGs in the maintenance is already "baked in". However, if for some reason you don't want to or can't use his maintenance plans, then you'll need to insert the deterministic function,
If sys.fn_hadr_is_primary_replica ( @dbname ) <> 1
BEGIN
-- If this is not the primary replica, exit (probably without error).
END
-- If this is the primary replica, continue to do the backup.,
to figure out if the database resides in the primary node or not. The example comes from: https://docs.microsoft.com/en-us/sql/relational-databases/system-functions/sys-fn-hadr-is-primary-replica-transact-sql


Total Memory capped far below available memory

SQL Server is not guaranteed to allocate the min server memory. If the load on the server never needs the minimum SQL Server will work with less - BOL http://msdn.microsoft.com/en-us/library/ms178067.aspx

As long as your configurations allow for the use of all memory you have allocated (they do), SQL Server will use what it needs, and generally no more. Since you are running a VM I would make sure you allocate 10% for the OS, remove the minimum as it can cause performance problems and let it run. If your CPU is pegged at 100%, queries are slow, and SQL Server is not allocating all of its available memory then it would be time to further troubleshoot.


Why earthdistance  operator returns different value than earth_distance function?

They do different things,

earth_distance Returns the great circle distance between two points on the surface of the Earth.
<@> Gives the distance in statute miles between two points on the Earth's surface.

So this is the difference between the average of the two (assuming neither is correct) is given as,

SELECT ((d2-d1) / ((d2+d1)/2)) * 100 AS distance_diff FROM results;
   distance_diff
-------------------
 0.112652419817465
(1 row)

Which is 0.11%. Per the Great-circle distance on wikipedia

  The Earth is nearly spherical (see Earth radius) so great-circle distance formulas give the distance between points on the surface of the Earth (as the crow flies) correct to within 0.5% or so.

So which one is more accurate? <@>, clearly. But, we can do better as the docs say,

  In this module, the Earth is assumed to be perfectly spherical. (If that's too inaccurate for you, you might want to look at the PostGIS project.)

SELECT ST_Distance(
  ST_MakePoint(1,1)::geography,
  ST_MakePoint(2,2)::geography
);
   st_distance
-----------------
 156876.14940189
(1 row)

But if your coordinators are SRID 4326, the correct-most distance is 156876.14940189.


Does anyone have a stored procedure to truncate temporal table?

MSSQL documentation has a good sample stored procedure (though far from perfect, as Martin noted in the comments about how it does not use QUOTENAME, for one) for cleaning a temporal table:

DROP PROCEDURE IF EXISTS sp_CleanupHistoryData;
GO

CREATE PROCEDURE sp_CleanupHistoryData
         @temporalTableSchema sysname
       , @temporalTableName sysname
       , @cleanupOlderThanDate datetime2
AS
    DECLARE @disableVersioningScript nvarchar(max) = '';
    DECLARE @deleteHistoryDataScript nvarchar(max) = '';
    DECLARE @enableVersioningScript nvarchar(max) = '';

DECLARE @historyTableName sysname
DECLARE @historyTableSchema sysname
DECLARE @periodColumnName sysname

/*Generate script to discover history table name and end of period column for given temporal table name*/
EXECUTE sp_executesql
    N'SELECT @hst_tbl_nm = t2.name, @hst_sch_nm = s.name, @period_col_nm = c.name
        FROM sys.tables t1
           JOIN sys.tables t2 on t1.history_table_id = t2.object_id
        JOIN sys.schemas s on t2.schema_id = s.schema_id
            JOIN sys.periods p on p.object_id = t1.object_id
           JOIN sys.columns c on p.end_column_id = c.column_id and c.object_id = t1.object_id
                  WHERE
                 t1.name = @tblName and s.name = @schName'
                , N'@tblName sysname
                , @schName sysname
                , @hst_tbl_nm sysname OUTPUT
                , @hst_sch_nm sysname OUTPUT
                , @period_col_nm sysname OUTPUT'
                , @tblName = @temporalTableName
                , @schName = @temporalTableSchema
                , @hst_tbl_nm = @historyTableName OUTPUT
                , @hst_sch_nm = @historyTableSchema OUTPUT
                , @period_col_nm = @periodColumnName OUTPUT

IF @historyTableName IS NULL OR @historyTableSchema IS NULL OR @periodColumnName IS NULL
    THROW 50010, 'History table cannot be found. Either specified table is not system-versioned temporal or you have provided incorrect argument values.', 1

/*Generate 3 statements that will run inside a transaction: SET SYSTEM_VERSIONING = OFF, DELETE FROM history_table, SET SYSTEM_VERSIONING = ON */
SET @disableVersioningScript =  @disableVersioningScript + 'ALTER TABLE [' + @temporalTableSchema + '].[' + @temporalTableName + '] SET (SYSTEM_VERSIONING = OFF)'
SET @deleteHistoryDataScript =  @deleteHistoryDataScript + ' DELETE FROM  [' + @historyTableSchema + '].[' + @historyTableName + ']
     WHERE ['+ @periodColumnName + '] < ' + '''' + convert(varchar(128), @cleanupOlderThanDate, 126) +  ''''
SET @enableVersioningScript =  @enableVersioningScript + ' ALTER TABLE [' + @temporalTableSchema + '].[' + @temporalTableName + ']
    SET (SYSTEM_VERSIONING = ON (HISTORY_TABLE = [' + @historyTableSchema + '].[' + @historyTableName + '], DATA_CONSISTENCY_CHECK = OFF )); '

BEGIN TRAN
    EXEC (@disableVersioningScript);
    EXEC (@deleteHistoryDataScript);
    EXEC (@enableVersioningScript);
COMMIT;

Just add a few extra lines to handle removing data from the parent table and you should be in business.

Bonus: it uses sysname for variables which I am a huge proponent of but is generally an overlooked datatype!


One option is to leave some of this functionality to the application code. It's possible to query for the relevant data with

select * from transactions t
    where exists(
        select 1 from transactions t2
            where t2.coupon = 'bCoupon'
                and t.user_id = t2.user_id
                and t2.date_paid > t.date_paid
    )
    order by date_paid

And then fold over that.


Synonym being recreated randomly - possible to create some kind of trigger/log?

You could do this with a trigger, by logging the login + query executed

Create the logging table

CREATE TABLE DBO.SynonymTriggerLogging( ID int Identity(1,1) PRIMARY KEY NOT NULL,
                                 LoginName varchar(200),
                                 QueryExecuted varchar(max));

Create the trigger (If you only want to log login name + the drop statement)

CREATE TRIGGER SynonymTrigger
ON DATABASE
AFTER DROP_SYNONYM
AS
INSERT INTO DBO.SynonymTriggerLogging(LoginName,QueryExecuted)
SELECT ORIGINAL_LOGIN(),EVENTDATA().value('(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]','nvarchar(max)');

Testing

CREATE SYNONYM TEST
FOR dbo.test;

DROP SYNONYM TEST;

Result

SELECT *
FROM DBO.SynonymTriggerLogging;

ID  LoginName   QueryExecuted
1   rvertongen  DROP SYNONYM TEST

If you also need the hostname, use the following table & trigger

CREATE TABLE DBO.SynonymTriggerLogging( ID int Identity(1,1) PRIMARY KEY NOT NULL,
                                 LoginName varchar(200),
                                 QueryExecuted varchar(max),
                                 HostName varchar(200));
GO
CREATE TRIGGER SynonymTrigger
ON DATABASE
FOR DROP_SYNONYM
AS
INSERT INTO DBO.SynonymTriggerLogging(LoginName,QueryExecuted,HostName)
SELECT ORIGINAL_LOGIN(),EVENTDATA().value('(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]','nvarchar(max)'),HOST_NAME()

Also take a look at example D from this link to prevent SYNONYM's from being dropped.


What is the difference between a connection and a session?

The connection is the physical communication channel between SQL Server and the application: the TCP socket, the named pipe, the shared memory region. The session in SQL Server corresponds to the Wikipedia definition of a session: a semi-permanent container of state for an information exchange. In other words the sessions stores settings like cache of your login information, current transaction isolation level, session level SET values etc etc.

Normally there is one session on each connection, but there could be multiple session on a single connection (Multiple Active Result Sets, MARS) and there are sessions that have no connection (SSB activated procedures, system sessions). There are also connections w/o sessions, namely connections used for non-TDS purposes, like database mirroring sys.dm_db_mirroring_connections or Service Broker connections sys.dm_broker_connections.


Can I not add VIEW SERVER STATE to a role?

Just because you create a role in the master database doesn't make it a server level role. It's still a database role - it just lives in a really important database.

SQL Server 2008R2 simply didn't have user defined server level roles. For that feature, you'll need to upgrade to SQL Server 2012.

You asked for something authoritative, and the best answer there is good ol' Books Online, where they list what's new in SQ Server 2012 security features:

https://technet.microsoft.com/en-us/library/42919d6c-1fd4-466e-ac75-d579248c07df(v=sql.110)


What is difference between is_unique column and is_unique_constraint columns in sys.indexes

Fact is that there is no practical difference between a unique constraint and a unique index.

Querying sys.objects, you will find unique constraint is listed as a constraint object and its related index can be found while querying sys.indexes, where it is marked is_unique = 1 as well as is_unique_constraint = 1. On other hand, for a unique indexes is_unique_constraint value will be zero (0).
While creating unique constraint you can use Fill Factor option as compare to unique index where you have more options like FILLFACTOR, PAD_INDEX, IGNORE_DUP_KEY, DROP_EXISTING, and STATISTICS_NORECOMPUTE.
Unique constraint is maintained through a unique index
Just like Default and Primary Key, a unique constraint can't be disabled using NOCHECK syntax
To delete a unique index with is_unique_constraint = 1, it is must to delete it through DROP CONSTRAINT syntax instead of DROP INDEX because such index is associated with a unique constraint (object), so one must drop CONSTRAINT and index will be dropped automatically.

As best practice, If uniqueness is required then always apply unique constraint, instead of creating only unique index. This way you can't drop a unique index accidentally.Also this way you can document all applied constraints easily.
http://technet.microsoft.com/en-us/library/aa224827(v=sql.80).aspx


Maybe, you can just execute the query in psql and redirect its output to a file after removing all headers. Did you try something like:

$ psql
user=> \o outputfile.txt
user=> \t on
user=> SELECT alll FROM super.a_a;
user=> \q


Creating a copy of production database for reporting purposes

This is what Replication is for.  This is a much easier way to keep a reporting database in sync than backup/restore.


Here is the definition of information_schema.views:

mysql> show create table information_schema.views\G
*************************** 1. row ***************************
       Table: VIEWS
Create Table: CREATE TEMPORARY TABLE `VIEWS` (
  `TABLE_CATALOG` varchar(512) NOT NULL DEFAULT '',
  `TABLE_SCHEMA` varchar(64) NOT NULL DEFAULT '',
  `TABLE_NAME` varchar(64) NOT NULL DEFAULT '',
  `VIEW_DEFINITION` longtext NOT NULL,
  `CHECK_OPTION` varchar(8) NOT NULL DEFAULT '',
  `IS_UPDATABLE` varchar(3) NOT NULL DEFAULT '',
  `DEFINER` varchar(77) NOT NULL DEFAULT '',
  `SECURITY_TYPE` varchar(7) NOT NULL DEFAULT '',
  `CHARACTER_SET_CLIENT` varchar(32) NOT NULL DEFAULT '',
  `COLLATION_CONNECTION` varchar(32) NOT NULL DEFAULT ''
) ENGINE=MyISAM DEFAULT CHARSET=utf8
1 row in set (0.01 sec)

Here is how you can count the views per database per user

SELECT COUNT(1) ViewCount,table_schema,definer
FROM information_schema.views
GROUP BY table_schema,definer;

I am not that sure about access/denial of views. In the information_schema.views table, there is field called SECURITY_TYPE (which have values DEFINER and INVOKER).

If a View has SECURITY_TYPE as INVOKER, everybody and his grandmother can SELECT from that View. My guess is that you would have to set the SECURITY_TYPE to DEFINER to restrict everyone from running a SELECT from that View. Of course any user with SUPER privilege and the definer can a SELECT on that View.

Changing the SECURITY_TYPE is a little painful.

You can mysqldump all views. Here is a post I made back on July 26, 2011 : Modify DEFINER on Many Views. @DTest had an even more concise answer using ALTER VIEW.

Edit the output file in vi or some other editor to change the SECURITY TYPE of any View you wish. Then, jus reimport the view from that text file.

Give it a Try !!!


Mysql: Insert performance INNODB vs MYISAM

The reason is very simple. When you insert a row into MyISAM, it just puts it into the server's memory and hopes that the server will flush it to disk at some point in the future. Good luck if the server crashes.

When you insert a row into InnoDB it syncs the transaction durably to disk, and that requires it to wait for the disk to spin.  Do the math on your system and see how long that takes.

You can improve this by relaxing innodb_flush_log_at_trx_commit or by batching rows within a transaction instead of doing one transaction per row.

I highly recommend reading High Performance MySQL 3rd Edition (I am the author).


This really depends on what type of information you want to audit.  There are a number of ways to collect audit data.

I did an article that overviews audit options here.

But in summary:

Successful/Failed logins: This is an instance level setting and is saved to the logs.
C2 Auditing and Common Criteria Compliance: This goes a bit overboard in my opinion.  It audits a LOT of information.  You'll know if you actually need it.
Audit columns: These are columns in your table that give you information. CreateDate & ModifiedDate for example
Triggers: These are good if you want to log specific information when doing an insert, update or delete on a table.  There can be a lot of overhead here and they can be tricky to get right.
Tracing: Otherwise known as profiler.  This is an older technology that has been deprecated but is still hanging on and will for who knows how long.  You can collect all kinds of information here about what was run, by whome, how long did it take, who logged in, who logged out, just about anything happening on your instance.  Remember though that the more you collect the more overhead.
Extended Events: This is the replacement for traces.  Basically the same but with less overhead and more options for data you can collect.
Audits: These can be created at a database or server level.  They are based on Extended Events but have a different set of information.  For example shutting down the instance can be logged.  These are meant for when you need to collect audit information in the traditional sense. The data that is collected for an auditor.

You can get a bit more detail from the link above and it has further links in at least a few places for even more detail.


Mysql - Rows not updated in transactions without any errors

MyISAM ignores all transaction semantics.  Convert to InnoDB !

But maybe that does not answer the Question.  The one MyISAM table seems to be merely an audit log.

Further head scratching....

Please provide the transaction log for the bad case.


I would not bother implementing it, because others already did it and spent a lot of time with it. SQL Developer can do this for you.

Example: Exporting Metadata and Data for a Table

In SQL Developer, click Tools, then Database Export.

Set the required options:

Select object types, after that, select the tables you want to be exported:

Result will be something like this when choosing to export data only:

--------------------------------------------------------
--  File created - Wednesday-May-10-2017
--------------------------------------------------------
REM INSERTING into BP.T1
SET DEFINE OFF;
Insert into BP.T1 (C1) values (1);
REM INSERTING into BP.T2
SET DEFINE OFF;
Insert into BP.T2 (C1) values (2);


Edit Top 200 Rows is simply a GUI way of performing INSERT, UPDATE or DELETE operations on a table, so you only require the same permissions you would require for performing these actions via T-SQL scripts.

The minimum required is the following:

Contained Database User or SQL Server Login mapped to Database User
VIEW ANY DATABASE on server to Login
GRANT SELECT* on table to User
GRANT UPDATE** on table to User
GRANT INSERT** on table to User
GRANT DELETE** on table to User

* SELECT is required because when you click Edit Top 200 Rows, SSMS must first SELECT the top 200 rows to display them for editing.

** You only need to grant the permission for the action you want users to be able to perform.

This is the minimum requirement for a single table. To allow Edit Top 200 Rows on all tables, you can either grant the permissions to all tables individually or use a built-in role such as db_datawriter.

EDIT: As per David Browne's comment, you also need VIEW ANY DATABASE to see the DB in SSMS. By default, this is granted via membership in the Public server role during login creation, however if the Public role membership has been revoked, you'll need this explicit permission.


Is it possible to configure multiple database mail id in single account name in sql server 2012?

Create Database Email operator

Step 1:

Step 2:

Step 3:

One More step enable Sql Server Agent properties for notifying alerts and restart agent once these properties are settled.

Sql Server Agent  -> Properties ->


Create one row for every user in a Postgres table

This would seem rather simple:

INSERT INTO user_flags (user_id, flag)
SELECT u.id, 'myflag'   -- or whatever to write to this field
FROM   users u
LEFT   JOIN user_flags uf ON uf.user_id = u.id
WHERE  uf.user_id IS NULL;  -- to exclude already existing

How to ..

Select rows which are not present in other table

While this is susceptible to race conditions with concurrent transactions, that should not be a problem since you are not going to execute this INSERT in more than one transaction at a time.

You might limit the operations to n rows per call, say 100000, simply append LIMIT, possibly combined with ORDER BY:

...
ORDER  BY u.id
LIMIT  100000;

Repeat the query until you find < 100000 rows, in separate transactions to release locks. Normally, you would do it all in one command, though, even with millions of rows.

I suggest a FOREIGN KEY constraint to enforce referential integrity - if you don't have one, yet.

ALTER TABLE user_flags
  ADD CONSTRAINT user_flags_user_id_fkey FOREIGN KEY (user_id)
  REFERENCES users(id) ON UPDATE CASCADE ON DELETE CASCADE;

And a UNIQUE or PRIMARY KEY constraint on user_flags.user_id to make the 1:1 relationship complete.


How to display one name in three tables many-to-many relationship?

This is normal behavior in any database. The value "Liza" shows on each line because it's the value from student's_info.s_name for the rows being shown.

You want to suppress the information to improve the presentation of the data (i.e., the way it looks). This is why Michael Kutz calls it a "Display Tier" problem.

The data being returned from the query is exactly correct; you want to modify it when you go to display it to an end user. Generally, it's recommended that this sort of thing be handled by the application that's going to deliver the data to said end user.

There are ways to manipulate the result set to not display the student's name if it's the same as it was on the previous row, if for some reason you cannot do it in the application. However, they result in a more complicated query.

I've put together a query that seems to work, with the sample data at least. You can see it in action at this DB-Fiddle. Note that I've tweaked your names slightly (the apostrophe/single quote character is almost always used to mean something in SQL, so I'd avoid it in an object name).

The fiddle shows the results - as you desired, the first row for each student shows their name, and the following rows for the same student leave the name blank.

Here's the query:

SELECT s_name_display, sub_name, n1, n2, n3
  FROM (SELECT if(@last_s_name = s_name, '', s_name) as s_name_display
              ,@last_s_name := s_name as name_set_to
              ,sub_name
              ,n1,n2,n3
              ,@row_number := @row_number + 1 as sort_id
          FROM (
                SELECT student.s_name
                      ,subject.sub_name
                      ,marks.n1, marks.n2, marks.n3
                  FROM subject
                         RIGHT JOIN (student
                                       RIGHT JOIN marks ON student.s_id = marks.s_id
                                    ) ON subject.sub_id = marks.sub_id
                 ORDER BY student.s_name, subject.sub_name
                ) base
       ) sq
 ORDER BY sort_id
;

The subquery named base is your original query. We sort the data in the desired order here.

The subquery sq uses base with two variables. One, @last_s_name, holds the s_name from the previous row. @row_number is used to set a row number - we want to be certain that the final output is shown in the same order as what we have here, since row order is critical (the first appearance of each student name has to come before the other rows with that student's name).

We determine the name to display by comparing the student name from the previous row with the student name in the current row. If they're the same, we return an empty string as s_display_name; otherwise, we return the actual s_name.

Finally, we select just the columns we want from the sq subquery, and make sure they're sorted correctly in the outermost query.

Like I said - this is much more complicated than your original query. For that matter, I can't guarantee that this will scale - merely that it works fine with your sample data.


MySQL order by is equal, in what order are the results returned?

Consider running the EXPLAIN or EXPLAIN EXTENDED command in front of your SELECT statement. This will produce an explanation of how the query optimiser queries the database to retrieve the data you are asking it for.

Example:

EXPLAIN EXTENDED SELECT * from mytable ORDER BY fooOrder;

This output will display information about how the query optimiser went about retrieving the data.

Example output:

+----+-------------+-------------+------+---------------+------+---------+------+------+----------+-----------------------------+
| id | select_type | table       | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra                       |
+----+-------------+-------------+------+---------------+------+---------+------+------+----------+-----------------------------+
|  1 | SIMPLE      | mytable     | ALL  | NULL          | NULL | NULL    | NULL | 1141 |   100.00 | Using filesort              |
+----+-------------+-------------+------+---------------+------+---------+------+------+----------+-----------------------------+
1 row in set, 1 warning (0.00 sec)

As you can see in my example the EXTRA column displays additional information. Here a Using filesort.

With this information you can then proceed to consult the MySQL documentation for each condition.

ORDER BY Optimization

Depending on what your EXPLAIN EXTENDED results in, you can consult the official ORDER BY Optimization page to retrieve additional information.

I'll proceed with the Using filesort condition from my example which is explained as follows:

  The Original filesort Algorithm

  The original filesort algorithm works as follows:

  Read all rows according to key or by table scanning. Skip rows that do not match the WHERE clause.
  For each row, store in the sort buffer a tuple consisting of a pair of values (the sort key value and the row ID).
  If all pairs fit into the sort buffer, no temporary file is created. Otherwise, when the sort buffer becomes full, run a quicksort on it in memory and write it to a temporary file. Save a pointer to the sorted block.
  Repeat the preceding steps until all rows have been read.
  Do a multi-merge of up to MERGEBUFF (7) regions to one block in another temporary file. Repeat until all blocks from the first file are in the second file.
  Repeat the following until there are fewer than MERGEBUFF2 (15) blocks left.
  On the last multi-merge, only the row ID (the last part of the value pair) is written to a result file.
  Read the rows in sorted order using the row IDs in the result file. To optimize this, read in a large block of row IDs, sort them, and use them to read the rows in sorted order into a row buffer. The row buffer size is the read_rnd_buffer_size system variable value. The code for this step is in the sql/records.cc source file.

  One problem with this approach is that it reads rows twice: Once during WHERE clause evaluation, and again after sorting the value pairs. And even if the rows were accessed successively the first time (for example, if a table scan is done), the second time they are accessed randomly. (The sort keys are ordered, but the row positions are not.)

  The Modified filesort Algorithm

  The modified filesort algorithm incorporates an optimization to avoid reading the rows twice: It records the sort key value, but instead of the row ID, it records the columns referenced by the query. The modified filesort algorithm works like this:

  Read the rows that match the WHERE clause.
  For each row, store in the sort buffer a tuple consisting of the sort key value and the columns referenced by the query.
  When the sort buffer becomes full, sort the tuples by sort key value in memory and write it to a temporary file.
  After merge-sorting the temporary file, retrieve the rows in sorted order, but read the columns required by the query directly from the sorted tuples rather than by accessing the table a second time.

  The tuples used by the modified filesort algorithm are longer than the pairs used by the original algorithm, and fewer of them fit in the sort buffer. As a result, it is possible for the extra I/O to make the modified approach slower, not faster. To avoid a slowdown, the optimizer uses the modified algorithm only if the total size of the extra columns in the sort tuple does not exceed the value of the max_length_for_sort_data system variable. (A symptom of setting the value of this variable too high is a combination of high disk activity and low CPU activity.)

Solution

Run your query with EXPLAIN EXTENDED and see what the query optimiser tells you. Then proceed from there.

If you have an index on the table it might retrieve the data according to the index. If you don't have an index, then it might fall back to the FILESORT algorithm. The conditions dictate the ORDER BY Optimization.

Reference

8.8.2 EXPLAIN Output Format (MySQL Documentation)
8.8.3 Extended EXPLAIN Output Format (MySQL Documentation)
8.2.1.10 ORDER BY Optimization (MySQL Documentation)


As with many things database-y, it's probably easier to extract them as rows and then reformat using your favourite shell-based text processor or, if you still want to try and get them all on one line, make sqlplus's "line" as big as it will go:

set linesize 32767


Database table as a queue, order by another table's column

It seems that preventing the locking of candidate rows is impossible with an ordering on a column not part of the clustered index.
I came up with a workaround, that seems to solve the problem on the minimal example i provided. I'll do some tests to make sure if it solves the actual production problem.
The idea is to split dequeue operation into two steps. First step respects the ordering and selects a small candidate set; second step ignores any orderings and works on this small set. Thus preventing locking of all the rows.

Select more than one rows to be dequeued, without removing the original order by clause. In my case, i changed the CTE to select top(50) rows. And also changed (ROWLOCK, READPAST, UPDLOCK) to read (NOLOCK) as i don't intend to dequeue all these rows, these are all just candidates for the one row i am willing to dqueue. These rows are stored in a table variable, to be joined in the second step, which follows below.

Create a new (second) CTE, this time joining with the table variable (which contains at most 50 rows), where clauses will be repeated here to prevent an unwanted row appearing in the result set. The ordering this time is removed from the new CTE to prevent the locking.

Although not critical for my specific needs, there are a couple of issues with this approach:

If more than 50 users simultaneously try to dequeue, only the lucky 50 of them will succeed, while others will not be able to get an item.
Granularity of the ordering is lost, each item in the candidate list of 50 are treated equally.
Again on the ordering, it might be the case that a user dequeues an item which was not in the top 50, but this won't be a totally off target miss; it will be close to the top 50. This is due to the two step approach: things might change and new items might arrive that have higher priority, from the time first CTE runs until the second CTE which performs the actual update. Anyways, it is highly likely that the item dequeued is within the first 50 items.

Test script for the new approach is as follows:
BEGIN TRAN TRAN1
DECLARE @QUEUEID INT;

DECLARE @CANDIDATE_TABLE TABLE (
    QUEUEID INT NOT NULL
);

;WITH CANDIDATE_CTE AS
(
    SELECT   TOP(50) Q.QUEUEID
    FROM     DBO.QUEUE Q WITH (NOLOCK)
    JOIN     DBO.QUEUE_ORDERING QR WITH (NOLOCK)
    ON       QR.QUEUEID = Q.QUEUEID
    ORDER BY QR.ORDERING_COLUMN DESC
)
INSERT INTO @CANDIDATE_TABLE (QUEUEID)
SELECT QUEUEID FROM CANDIDATE_CTE;

;WITH QUEUE_CTE AS
(
    SELECT   TOP(1) Q.QUEUEID
    FROM     DBO.QUEUE Q WITH (ROWLOCK, READPAST, UPDLOCK)
    JOIN     @CANDIDATE_TABLE CT
    ON       CT.QUEUEID = Q.QUEUEID
)
SELECT @QUEUEID = QUEUEID FROM QUEUE_CTE
PRINT 'processing item # ' + CAST(@QUEUEID AS VARCHAR)

WAITFOR DELAY '00:00:05'
COMMIT

The outputs of two simultaneous sessions are:

(10 row(s) affected)
processing item # 10

(10 row(s) affected)
processing item # 9


Mysql binlogs exists, show binary logs is empty

You may need to try something a little unorthodox
service mysql stop

Open mysql-bin.index in vi
Change the contents to this
./mysql-bin.000313
./mysql-bin.000314
./mysql-bin.000315

Save mysql-bin.index
service mysql start

Login to MySQL and run
SHOW BINARY LOGS;
SHOW MASTER STATUS;

This stunt worked a few times for me.
Give it a Try !!!


Collation determines two things.

The code page used (and hence characters that can be stored) for non unicode data.
The comparison semantics for all textual data.

(NB: SQL Server 2012 also introduces _SC, supplementary character, collations for  UTF-16 encoding rather than UCS-2 but these are not relevant to your question here)

SELECT *
FROM   sys.fn_helpcollations()
WHERE  name LIKE '%Slovak%CI%AS'

Returns the following information

name                      description
------------------------- -------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Slovak_CI_AS              Slovak, case-insensitive, accent-sensitive, kanatype-insensitive, width-insensitive
Slovak_100_CI_AS          Slovak-100, case-insensitive, accent-sensitive, kanatype-insensitive, width-insensitive
SQL_Slovak_CP1250_CI_AS   Slovak, case-insensitive, accent-sensitive, kanatype-insensitive, width-insensitive for Unicode Data, SQL Server Sort Order 94 on Code Page 1250 for non-Unicode Data

So on 2012 there are two windows collations and one SQL Collation. The description doesn't indicate the code page for the windows versions but that they are all the same can be seen from below.

CREATE TABLE #Collations
(
     code TINYINT PRIMARY KEY,
     SQL_Slovak_CP1250_CI_AS CHAR(1) COLLATE SQL_Slovak_CP1250_CI_AS,
     Slovak_CI_AS CHAR(1) COLLATE Slovak_CI_AS,
     Slovak_100_CI_AS CHAR(1) COLLATE Slovak_100_CI_AS,
     /*Included just to show an example of a different code page*/
     Latin1_General_CI_AS CHAR(1) COLLATE Latin1_General_CI_AS
);

WITH E00(N) AS (SELECT 1 UNION ALL SELECT 1),   --2
        E02(N) AS (SELECT 1 FROM E00 a, E00 b), --4
        E04(N) AS (SELECT 1 FROM E02 a, E02 b), --16
        E08(N) AS (SELECT 1 FROM E04 a, E04 b) --256
INSERT INTO #Collations
            (code)
SELECT ROW_NUMBER() OVER (ORDER BY (SELECT 0)) - 1
FROM   E08

UPDATE #Collations
SET    SQL_Slovak_CP1250_CI_AS = CAST(code AS BINARY(1)),
       Slovak_CI_AS = CAST(code AS BINARY(1)),
       Slovak_100_CI_AS = CAST(code AS BINARY(1)),
       Latin1_General_CI_AS = CAST(code AS BINARY(1))

SELECT *
FROM   #Collations

Or, alternatively, using the COLLATIONPROPERTY function

SELECT COLLATIONPROPERTY('SQL_Slovak_CP1250_CI_AS', 'CodePage') AS SQL_Slovak_CP1250_CI_AS,
       COLLATIONPROPERTY('Slovak_CI_AS', 'CodePage')            AS Slovak_CI_AS,
       COLLATIONPROPERTY('Slovak_100_CI_AS', 'CodePage')        AS Slovak_100_CI_AS,
       COLLATIONPROPERTY('Latin1_General_CI_AS', 'CodePage')    AS Latin1_General_CI_AS

Returns

+-------------------------+--------------+------------------+----------------------+
| SQL_Slovak_CP1250_CI_AS | Slovak_CI_AS | Slovak_100_CI_AS | Latin1_General_CI_AS |
+-------------------------+--------------+------------------+----------------------+
|                    1250 |         1250 |             1250 |                 1252 |
+-------------------------+--------------+------------------+----------------------+

So as far as point 1 is concerned the collation makes no difference for SQL_Slovak_CP1250_CI_AS vs Slovak_CI_AS.

For point 2 see Comparing SQL collations to Windows collations. The Collation semantics section has some discussion about how the two differ but skipping ahead to Recommendations

  SQL collations are provided for backward compatibility with earlier
  versions of SQL Server. Windows collations provide consistent string
  comparisons for both Unicode and for non-Unicode text in SQL Server
  that are also consistent with string comparisons in the Windows
  operating system. For all these reasons, Windows collations are
  preferred unless there are backward compatibility issues or specific
  performance issues that require a SQL collation.

So you should be using the Windows Collations anyway unless you have some specific backward compatibility need. Also I would use Slovak_100_CI_AS as this is more recent and avoids issues like this.

To see examples of specifics of how the comparison semantics differ between the three collations you can use the temporary table created earlier and try

SELECT code,
       /*Exclude some characters that will mess up the formatting*/
       CASE WHEN code IN (9,10,12,13) THEN '' ELSE SQL_Slovak_CP1250_CI_AS END AS [Char],
       DENSE_RANK() OVER (ORDER BY SQL_Slovak_CP1250_CI_AS) AS SQL_Slovak_CP1250_CI_AS,
       DENSE_RANK() OVER (ORDER BY Slovak_CI_AS) AS Slovak_CI_AS,
       DENSE_RANK() OVER (ORDER BY Slovak_100_CI_AS) AS Slovak_100_CI_AS
FROM   #Collations
ORDER BY SQL_Slovak_CP1250_CI_AS

That yields the following results

+------+------+-------------------------+--------------+------------------+
| code | Char | SQL_Slovak_CP1250_CI_AS | Slovak_CI_AS | Slovak_100_CI_AS |
+------+------+-------------------------+--------------+------------------+
|    0 |      |                       1 |            1 |                1 |
|    1 |      |                       2 |            2 |                2 |
|    2 |      |                       3 |            3 |                3 |
|    3 |      |                       4 |            4 |                4 |
|    4 |      |                       5 |            5 |                5 |
|    5 |      |                       6 |            6 |                6 |
|    6 |      |                       7 |            7 |                7 |
|    7 |      |                       8 |            8 |                8 |
|    8 |      |                       9 |            9 |                9 |
|   14 |      |                      10 |           10 |               10 |
|   15 |      |                      11 |           11 |               11 |
|   16 |      |                      12 |           12 |               12 |
|   17 |      |                      13 |           13 |               13 |
|   18 |      |                      14 |           14 |               14 |
|   19 |      |                      15 |           15 |               15 |
|   20 |      |                      16 |           16 |               16 |
|   21 |      |                      17 |           17 |               17 |
|   22 |      |                      18 |           18 |               18 |
|   23 |      |                      19 |           19 |               19 |
|   24 |      |                      20 |           20 |               20 |
|   25 |      |                      21 |           21 |               21 |
|   26 |      |                      22 |           22 |               22 |
|   27 |      |                      23 |           23 |               23 |
|   28 |      |                      24 |           24 |               24 |
|   29 |      |                      25 |           25 |               25 |
|   30 |      |                      26 |           26 |               26 |
|   31 |      |                      27 |           27 |               27 |
|  127 |      |                      28 |           28 |               28 |
|  129 |      |                      29 |           29 |              106 |
|  131 |      |                      30 |           30 |              107 |
|  136 |      |                      31 |           31 |              108 |
|  144 |      |                      32 |           32 |              109 |
|  152 |      |                      33 |           33 |              110 |
|   39 | '    |                      34 |           34 |               29 |
|   45 | -    |                      35 |           35 |               30 |
|  173 |      |                      36 |           36 |                1 |
|  150 | –    |                      37 |           37 |               31 |
|  151 | —    |                      38 |           38 |               32 |
|   32 |      |                      39 |            1 |                1 |
|  160 |      |                      40 |           39 |               33 |
|    9 |      |                      41 |           40 |               34 |
|   10 |      |                      42 |           41 |               35 |
|   11 |      |                      43 |           42 |               36 |
|   12 |      |                      44 |           43 |               37 |
|   13 |      |                      45 |           44 |               38 |
|   33 | !    |                      46 |           45 |               39 |
|   34 | "    |                      47 |           46 |               40 |
|   35 | #    |                      48 |           47 |               41 |
|   36 | $    |                      49 |           48 |               42 |
|   37 | %    |                      50 |           49 |               43 |
|   38 | &    |                      51 |           50 |               44 |
|   40 | (    |                      52 |           51 |               45 |
|   41 | )    |                      53 |           52 |               46 |
|   42 | *    |                      54 |           53 |               47 |
|   44 | ,    |                      55 |           54 |               48 |
|   46 | .    |                      56 |           55 |               49 |
|   47 | /    |                      57 |           56 |               50 |
|   58 | :    |                      58 |           57 |               51 |
|   59 | ;    |                      59 |           58 |               52 |
|   63 | ?    |                      60 |           59 |               53 |
|   64 | @    |                      61 |           60 |               54 |
|   91 | [    |                      62 |           61 |               55 |
|   92 | \    |                      63 |           62 |               56 |
|   93 | ]    |                      64 |           63 |               57 |
|   94 | ^    |                      65 |           64 |               58 |
|   95 | _    |                      66 |           65 |               59 |
|   96 | `    |                      67 |           66 |               60 |
|  123 | {    |                      68 |           67 |               61 |
|  124 | |    |                      69 |           68 |               62 |
|  125 | }    |                      70 |           69 |               63 |
|  126 | ~    |                      71 |           70 |               64 |
|  166 | ¦    |                      72 |           71 |               65 |
|  168 | ¨    |                      73 |           72 |               66 |
|  180 | ´    |                      74 |           73 |               67 |
|  184 | ¸    |                      75 |           74 |               68 |
|  161 | ˇ    |                      76 |           75 |               69 |
|  162 | ˘    |                      77 |           76 |               70 |
|  255 | ˙    |                      78 |           77 |               71 |
|  178 | ˛    |                      79 |           78 |               72 |
|  189 | ˝    |                      80 |           79 |               73 |
|  145 | ‘    |                      81 |           80 |               74 |
|  146 | ’    |                      82 |           81 |               75 |
|  130 | ‚    |                      83 |           82 |               76 |
|  147 | “    |                      84 |           83 |               77 |
|  148 | ”    |                      85 |           84 |               78 |
|  132 | „    |                      86 |           85 |               79 |
|  139 | ‹    |                      87 |           86 |               80 |
|  155 | ›    |                      88 |           87 |               81 |
|   43 | +    |                      89 |           88 |               84 |
|   60 | <    |                      90 |           89 |               85 |
|   61 | =    |                      91 |           90 |               86 |
|   62 | >    |                      92 |           91 |               87 |
|  177 | ±    |                      93 |           92 |               88 |
|  171 | «    |                      94 |           93 |               89 |
|  187 | »    |                      95 |           94 |               90 |
|  215 | ×    |                      96 |           95 |               91 |
|  247 | ÷    |                      97 |           96 |               92 |
|  164 | ¤    |                      98 |           97 |               82 |
|  167 | §    |                      99 |           98 |               93 |
|  169 | ©    |                     100 |           99 |               94 |
|  172 | ¬    |                     101 |          100 |               95 |
|  174 | ®    |                     102 |          101 |               96 |
|  176 | °    |                     103 |          102 |               97 |
|  181 | µ    |                     104 |          103 |               98 |
|  182 | ¶    |                     105 |          104 |               99 |
|  183 | ·    |                     106 |          105 |              100 |
|  134 | †    |                     107 |          106 |              102 |
|  135 | ‡    |                     108 |          107 |              103 |
|  149 | •    |                     109 |          108 |              104 |
|  133 | …    |                     110 |          109 |              101 |
|  137 | ‰    |                     111 |          110 |              105 |
|  128 | €    |                     112 |          111 |               83 |
|   48 | 0    |                     113 |          112 |              111 |
|   49 | 1    |                     114 |          113 |              112 |
|   50 | 2    |                     115 |          114 |              113 |
|   51 | 3    |                     116 |          115 |              114 |
|   52 | 4    |                     117 |          116 |              115 |
|   53 | 5    |                     118 |          117 |              116 |
|   54 | 6    |                     119 |          118 |              117 |
|   55 | 7    |                     120 |          119 |              118 |
|   56 | 8    |                     121 |          120 |              119 |
|   57 | 9    |                     122 |          121 |              120 |
|   97 | a    |                     123 |          122 |              121 |
|   65 | A    |                     123 |          122 |              121 |
|  193 | Á    |                     124 |          123 |              122 |
|  225 | á    |                     124 |          123 |              122 |
|  226 | â    |                     125 |          124 |              123 |
|  194 | Â    |                     125 |          124 |              123 |
|  195 | Ă    |                     126 |          125 |              124 |
|  227 | ă    |                     126 |          125 |              124 |
|  185 | ą    |                     127 |          126 |              125 |
|  165 | Ą    |                     127 |          126 |              125 |
|  196 | Ä    |                     128 |          127 |              126 |
|  228 | ä    |                     128 |          127 |              126 |
|   66 | B    |                     129 |          128 |              127 |
|   98 | b    |                     129 |          128 |              127 |
|   99 | c    |                     130 |          129 |              128 |
|   67 | C    |                     130 |          129 |              128 |
|  230 | ć    |                     131 |          130 |              129 |
|  198 | Ć    |                     131 |          130 |              129 |
|  199 | Ç    |                     132 |          131 |              130 |
|  231 | ç    |                     132 |          131 |              130 |
|  232 | č    |                     133 |          132 |              131 |
|  200 | Č    |                     133 |          132 |              131 |
|   68 | D    |                     134 |          133 |              132 |
|  100 | d    |                     134 |          133 |              132 |
|  239 | ď    |                     135 |          134 |              133 |
|  207 | Ď    |                     135 |          134 |              133 |
|  208 | Đ    |                     136 |          135 |              134 |
|  240 | đ    |                     136 |          135 |              134 |
|  101 | e    |                     137 |          136 |              135 |
|   69 | E    |                     137 |          136 |              135 |
|  235 | ë    |                     138 |          137 |              136 |
|  203 | Ë    |                     138 |          137 |              136 |
|  201 | É    |                     139 |          138 |              137 |
|  233 | é    |                     139 |          138 |              137 |
|  236 | ě    |                     140 |          139 |              138 |
|  204 | Ě    |                     140 |          139 |              138 |
|  202 | Ę    |                     141 |          140 |              139 |
|  234 | ę    |                     141 |          140 |              139 |
|   70 | F    |                     142 |          141 |              140 |
|  102 | f    |                     142 |          141 |              140 |
|  103 | g    |                     143 |          142 |              141 |
|   71 | G    |                     143 |          142 |              141 |
|   72 | H    |                     144 |          143 |              142 |
|  104 | h    |                     144 |          143 |              142 |
|  105 | i    |                     145 |          144 |              143 |
|   73 | I    |                     145 |          144 |              143 |
|  237 | í    |                     146 |          145 |              144 |
|  205 | Í    |                     146 |          145 |              144 |
|  206 | Î    |                     147 |          146 |              145 |
|  238 | î    |                     147 |          146 |              145 |
|   74 | J    |                     148 |          147 |              146 |
|  106 | j    |                     148 |          147 |              146 |
|  107 | k    |                     149 |          148 |              147 |
|   75 | K    |                     149 |          148 |              147 |
|   76 | L    |                     150 |          149 |              148 |
|  108 | l    |                     150 |          149 |              148 |
|  229 | ĺ    |                     151 |          150 |              149 |
|  197 | Ĺ    |                     151 |          150 |              149 |
|  188 | Ľ    |                     152 |          151 |              150 |
|  190 | ľ    |                     152 |          151 |              150 |
|  179 | ł    |                     153 |          152 |              151 |
|  163 | Ł    |                     153 |          152 |              151 |
|   77 | M    |                     154 |          153 |              152 |
|  109 | m    |                     154 |          153 |              152 |
|  110 | n    |                     155 |          154 |              153 |
|   78 | N    |                     155 |          154 |              153 |
|  209 | Ń    |                     156 |          155 |              154 |
|  241 | ń    |                     156 |          155 |              154 |
|  242 | ň    |                     157 |          156 |              155 |
|  210 | Ň    |                     157 |          156 |              155 |
|   79 | O    |                     158 |          157 |              156 |
|  111 | o    |                     158 |          157 |              156 |
|  214 | Ö    |                     159 |          158 |              157 |
|  246 | ö    |                     159 |          158 |              157 |
|  211 | Ó    |                     160 |          159 |              158 |
|  243 | ó    |                     160 |          159 |              158 |
|  213 | Ő    |                     161 |          160 |              159 |
|  245 | ő    |                     161 |          160 |              159 |
|  212 | Ô    |                     162 |          161 |              160 |
|  244 | ô    |                     162 |          161 |              160 |
|  112 | p    |                     163 |          162 |              161 |
|   80 | P    |                     163 |          162 |              161 |
|   81 | Q    |                     164 |          163 |              162 |
|  113 | q    |                     164 |          163 |              162 |
|  114 | r    |                     165 |          164 |              163 |
|   82 | R    |                     165 |          164 |              163 |
|  192 | Ŕ    |                     166 |          165 |              164 |
|  224 | ŕ    |                     166 |          165 |              164 |
|  248 | ř    |                     167 |          166 |              165 |
|  216 | Ř    |                     167 |          166 |              165 |
|   83 | S    |                     168 |          167 |              166 |
|  115 | s    |                     168 |          167 |              166 |
|  156 | ś    |                     169 |          168 |              167 |
|  140 | Ś    |                     169 |          168 |              167 |
|  170 | Ş    |                     170 |          169 |              168 |
|  186 | ş    |                     170 |          169 |              168 |
|  223 | ß    |                     171 |          170 |              169 |
|  138 | Š    |                     172 |          171 |              170 |
|  154 | š    |                     172 |          171 |              170 |
|   84 | T    |                     173 |          172 |              171 |
|  116 | t    |                     173 |          172 |              171 |
|  157 | ť    |                     174 |          173 |              172 |
|  141 | Ť    |                     174 |          173 |              172 |
|  254 | ţ    |                     175 |          174 |              173 |
|  222 | Ţ    |                     175 |          174 |              173 |
|  153 | ™    |                     176 |          175 |              174 |
|   85 | U    |                     177 |          176 |              175 |
|  117 | u    |                     177 |          176 |              175 |
|  220 | Ü    |                     178 |          177 |              176 |
|  252 | ü    |                     178 |          177 |              176 |
|  250 | ú    |                     179 |          178 |              177 |
|  218 | Ú    |                     179 |          178 |              177 |
|  217 | Ů    |                     180 |          179 |              178 |
|  249 | ů    |                     180 |          179 |              178 |
|  251 | ű    |                     181 |          180 |              179 |
|  219 | Ű    |                     181 |          180 |              179 |
|  118 | v    |                     182 |          181 |              180 |
|   86 | V    |                     182 |          181 |              180 |
|   87 | W    |                     183 |          182 |              181 |
|  119 | w    |                     183 |          182 |              181 |
|  120 | x    |                     184 |          183 |              182 |
|   88 | X    |                     184 |          183 |              182 |
|   89 | Y    |                     185 |          184 |              183 |
|  121 | y    |                     185 |          184 |              183 |
|  221 | Ý    |                     186 |          185 |              184 |
|  253 | ý    |                     186 |          185 |              184 |
|  122 | z    |                     187 |          186 |              185 |
|   90 | Z    |                     187 |          186 |              185 |
|  191 | ż    |                     188 |          187 |              186 |
|  175 | Ż    |                     188 |          187 |              186 |
|  159 | ź    |                     189 |          188 |              187 |
|  143 | Ź    |                     189 |          188 |              187 |
|  142 | Ž    |                     190 |          189 |              188 |
|  158 | ž    |                     190 |          189 |              188 |
+------+------+-------------------------+--------------+------------------+


can access to a view be controlled by access to a referenced table?

You need to give the views a different owner than the tables so that the ownership chain is broken and the permissions are checked on the base objects.


Something like this?
https://stackoverflow.com/questions/9291255/insert-image-into-database

Please read about File Stream too.


Problem filtering information in MySQL query

This might be closer:

select  e.EmpName,
        GROUP_CONCAT(r.RoleName) AS roles
        CASE
            WHEN FIND_IN_SET("Chief",   GROUP_CONCAT(r.RoleName)) THEN "BOS"
            WHEN FIND_IN_SET("waiter ", GROUP_CONCAT(r.RoleName)) THEN "CHA"
            ELSE                                                       "CUS"
        END  AS Profile
    FROM  EMPLOYEE AS e
    JOIN  EMPROLE  AS er  ON e.EMPLOYEEID = er.EMPLOYEEID
    JOIN  ROLE     AS r   ON er.ROLEID = r.ROLEID
    group by  e.EMPLOYEEID;

Notes:

Please use the JOIN ON syntax instead of commajoin and WHERE.
The GROUP_CONCAT occurs 3 times; that can be avoided with the expense of a nested query.  I did not think it was worth it.
Be sure to have something like this on EMPROLE:

PRIMARY KEY(employeeid, roleid),
INDEX(roleid, employeeid)


PostgreSQL 9.1 - Asynchroneous commits performance boost

Your risk is data loss, not data corruption. An ACID platform guarantees transactions are

atomic,
consistent,
isolated, and
durable.

With asynchronous commits, you give up "durable". For your application, that seems a defensible decision.

If you have some transactions that need to be durable (new user registrations?) you can have that, too. PREPARE TRANSACTION is always synchronous; so is SET synchronous_commit = on. But it will interfere a little with all those asynchronous commits.

  Is there any risk that the queue of uncommitted transactions will keep
  growing

No, not if my understanding is correct. The WAL writer periodically writes all unwritten WAL records to disk. It doesn't see 1000 unwritten WAL records and decide, "Well, I can write 17 of them this time." If there are 1000 unwritten WAL records when the timer fires, it writes all 1000 to disk.

If your database server is constantly and heavily overloaded, I guess users might experience some "jerks" in performance if the WAL writer pushes a huge backlog to disk.

In every useful database application, you eventually have to think about upgrading hardware. Sometimes, the only defensible solution is a faster box.

PostgreSQL 9.1's wal_writer_delay setting.


Error 20598 - The row was not found at the Subscriber when applying the replicated command

What may be the possible reason of it?

There are multiple reasons that could cause this:

It could be someone or a process/apps deleted and inserted the data in subscriber db (while cmds are being applied). Review your subscriber security settings and check what process/apps are accessing the subscriber db.
Multiple publications connected to subscriber db. pub1 (deleted the data first) then pub2 tried to UPDATE/DELETE the data which could cause an error 20598.
Triggers on subscriber tables that could delete/insert/update the data.

HTH


Your question is confusing. Are you looking for an ETL tool? Most connect to heterogeneous sources (i.e. Oracle, SQL Server, flat files, etc.) These tools also allow you to examine the structure of the source system. Informatica and SSIS are two that come to mind. Profiling data may take some manual effort via queries in the source system.

When you say plain txt for DDL: Any RDBMS will allow you to export the DDL of tables. .sql files should be usable, but be aware of datatype differences between RDBMS.

If you are provided dumps or backups you must restore the backups or dumps into the appropriate RDBMS. From the restored databases you will be able to design an ETL into Postgres. Your ETL could be as simple as dump all the source tables to CSV files. Oracle and SQL Server both have excellent command line export utilities.


Deleting rows from Oracle table user_scheduler_job_run_details

You can specify it in days, not number of rows.

DBMS_SCHEDULER.PURGE_LOG

  By default, the Scheduler automatically purges all rows in the job log
  and window log that are older than 30 days. The PURGE_LOG procedure is
  used to purge additional rows from the job and window log.

  log_history

  This specifies how much history (in days) to keep. The valid range is
  0 - 1000000. If set to 0, no history is kept.

DBMS_SCHEDULER.PURGE_LOG (
   log_history             IN PLS_INTEGER  DEFAULT 0,
   which_log               IN VARCHAR2     DEFAULT 'JOB_AND_WINDOW_LOG',
   job_name                IN VARCHAR2     DEFAULT NULL);


Postgres COPY with multiple small chunks

You can use a WHERE condition:
COPY (SELECT * FROM tab WHERE id % 3 = 0) TO '/dir/file1.csv' (FORMAT 'csv');
COPY (SELECT * FROM tab WHERE id % 3 = 1) TO '/dir/file2.csv' (FORMAT 'csv');
COPY (SELECT * FROM tab WHERE id % 3 = 2) TO '/dir/file3.csv' (FORMAT 'csv');

Here id stands for any numerical column. You can also use other data types and split up the data in other ways with an appropriate WHERE condition.


Index strategy for a Queue Table, getting rid of filesort

Let's look at the WHERE clause

WHERE
        finished IS NULL
    AND locked='0'
    AND created <= NOW()
    AND counter <= 5
    AND company_id = 2
ORDER BY
    business_object_priority DESC, created ASC, id ASC

Since company_id and locked are static values, they should be the leading columns of the index. Your ORDER BY has business_object_priority DESC and created ASC. Because of this mixed order, there will always be a filesort to contend with.

Notwithstanding, I would rearrange the column order as follows

ALTER TABLE  `business_queue` ADD INDEX compound_selector_new (
    `company_id`,
    `locked`,
    `finished`
    `business_object_priority`) ;

Notice the following:

I removed created and counter because you have ranges specified in the WHERE clause.
I kept finished because you need all records with company_id=2 and locked=0 grouped with all finished columns that are NULL.

Although there will be a filesort, this should yield a faster collection of rows.

Give it a Try !!!


Changing the default size and AutoGrowth settings for new SQL Server Databases

For lack of a better description. Model is copied to create new databases. This ensure they have the same collation, size, data growth settings, and any other  parameter you want to set.


Load your data in another database.
Run LEFT JOINs one way, then the other way, to see what was deleted/new.
Run a JOIN to see if the rows that exist in both are the same; fix if necessary.
Run a multi-table DELETE to get rid of the the appropriate rows.
Run INSERT...SELECT..LEFT JOIN to copy the missing rows in


mac os - virtualbox - oracle connection error - invalid username/password; logon denied

You can manual check password for HR (if You want - force SET it oracle)
Firewall is not important moment, because Oracle return You exactly error text

if all correct, it must be:

macbookva:instantclient_11_2 vladimir$ sqlplus HR/oracle@XE

SQL*Plus: Release 11.2.0.4.0 Production on Wed Nov 18 19:24:20 2015

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

Connected to:
Oracle Database 11g Express Edition Release 11.2.0.2.0 - 64bit Production

SQL>


Script works but Im not sure this is the best format

(SELECT CASE DoYouWishToP When 0 Then 'Yes, urgently'  When 1 Then 'Not peeing' END)

is a Sub-select of the main select. It's simply a rule of SQL that it should be surrounded by parenthesis.
Ypercube is also right: if the columns, like "DoYouWishToP", are from the same table, you don't need the second and third SELECT. In that case it will work without the parenthesis. You normally only do a Sub-select using:

, (Select xxx **from** another_table where ...)


How to add index to increase speed of mysql statement

You should add an indexes for each table having NULL in the  possible_keys of EXPLAIN. Columns used for index should be the same used for JOINs. In case of countries table you have to add an index for code field while corresponding tables students and camps should be indexed on the citizenship fields each.

The common rule is:

When you join two tables with ON condition, each table should have indexed columns used for ON.

SELECT *
  FROM a
  JOIN b ON a.foo = b.bar;

Here table a should have index (foo) while table b should have index (bar)

When you filter out rows by WHERE clause, columns mentioned in the WHERE conditions should be indexed:

SELECT *
  FROM a
 WHERE a.foo = 1234;

Here table a should have index (foo).

Complex conditions that refers to the multiple columns in the same table require multicolumn indexes:

SELECT *
  FROM a
 WHERE a.foo = 1234
   AND a.bar BETWEEN '2017-07-20' AND '2017-07-21';

Here table a should have an multicolumn index (foo, bar) or (bar,foo), or even  both. It's depends on certain data stored in your table and some performance measurements should be performed. Even though for different queries different indexes can become optimal and day-wide range could be faster with (foo,bar) while month-wide range could be faster with (bar,foo).


No, the wildcard for table names can only be *, and does not permit other characters or patterns.

You could generate the 18 GRANT statements you need:

SELECT CONCAT('GRANT SELECT,INSERT,UPDATE,DELETE ON prod.`', TABLE_NAME, '` TO ...;')
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA = 'prod' AND TABLE_NAME LIKE 'vs\_%';

Capture the output of that query, and then run it as a series of statements.


Getting Nested query result from Cosmos DB with property named "Value"

If property name is the same as reserved word (in this case Value),
following syntax could be used:

["Value"]

In the case above here is working query:

SELECT ed
FROM c
JOIN ed IN c.ExtendedData
WHERE ed.Key = "MembershipTypeId"
AND ed["Value"] = "224"


Is there a limit to the number of database connections from a single machine in PostgreSQL?

You probably don't want more than 100 or so concurrent queries running on the DB. Use a connection pooler like pgbouncer if you need one and your app doesn't have one built-in.

See https://wiki.postgresql.org/wiki/Number_Of_Database_Connections

Setting a high max_connections won't make things go faster. It'll make things go slower.


SQL2542N - No match for a database image file was found based on the source database alias

Thanks all for your answers.

Finally I asked the guy who had give me the copy of the database. It came from a Linux, and I was trying to restore in a Windows, so I think this was the main problem. I tried to do the restore on a Linux-Suse, and it worked.

Once more, it is good to start with the most obvious... sorry, and thanks!


Is there a way that I can export a list of all table triggers on a server?

This will give you a list of tables that have triggers, and the name of the associated trigger:

SELECT t.name, tr.name
FROM sys.triggers tr
    INNER JOIN sys.tables t ON tr.parent_id = t.object_id

This will give you the same list, with a column that shows the actual text of the trigger statement:

SELECT t.name, tr.name, m.definition
FROM sys.triggers tr
    INNER JOIN sys.tables t ON tr.parent_id = t.object_id
    INNER JOIN sys.sql_modules m ON tr.object_id = m.object_id

As Aaron suggested, this would work for SQL Server 2005+ as well:

SELECT t.name, tr.name, OBJECT_DEFINITION(tr.object_id) AS TriggerText
FROM sys.triggers tr
    INNER JOIN sys.tables t ON tr.parent_id = t.object_id

And per his excellent suggestion, the following works for SQL Server 2000+ (including SQL Server 2012):

SELECT [table] = t.name,
    [trigger] = tr.name,
    c.text
FROM sysobjects AS t
    INNER JOIN sysobjects AS tr  ON t.id = tr.parent_obj
    INNER JOIN syscomments AS c  ON tr.id = c.id
WHERE tr.xtype = 'TR'
    AND t.xtype = 'U';


Can I trigger events based on timestamp data or should I use polling?

(This is very similar to a question recently posted on S.O.: Time trigger database updating)

There is no direct way of doing this, at least not in SQL Server. In that similar question linked above I did detail a way of getting very close to this concept in SQL Server, and someone mentioned Service Broker, but these are still indirect and have not really been tested to see how well they meet up to the original desire.

On the other hand, I also mentioned in my answer in that other question that there is a technology that does do exactly this (i.e. time-based expiration): cache severs. There are at least three that I am aware of:

memcached
redis
Microsoft AppFabric

Or, depending on how this question is interpreted, if the desire is to kick off a process based on reaching a specific time, you can implement something like Quartz.NET which is a job scheduler. Using this you could still keep track of the schedules in a table in SQL Server, and when events get kicked off, part of that process would update that table in SQL Server with the status.

A simplistic, general setup might look like:

[ScheduleData] table

Add [StatusID] TINYINT

Values:

1 = Completed/Pending
2 = Running
3 = Error

Add [ErrorCount] TINYINT
Add DEFAULT CONSTRAINT on [ErrorCount] being (0)
Maybe rename [timestamp] field to be [CreatedOnDate] or [ModifiedOnDate]
Add DEFAULT CONSTRAINT on [CreatedOnDate] being (GETDATE() or GETUTCDATE())
Add [TaskOutput] NVARCHAR(MAX)
Add DEFAULT CONSTRAINT on [TaskOutput] being ('')

Stored Procedures:

[ScheduleData_AddNewTask]

Params (@Data NVARCHAR(4000 or MAX), @ScheduleDataID INT OUTPUT)
INSERT INTO dbo.ScheduleData (Data, StatusID) VALUES (@Data, 1);
SET @ScheduleDataID = SCOPE_IDENTITY();

[ScheduleData_UpdateTask]

Params (@ScheduleDataID INT, @StatusID TINYINT, @TaskOutput NVARCHAR(MAX))
UPDATE [ScheduleData]
SET StatusID = @StatusID,
TaskOutput = @TaskOutput,
ErrorCount = [ErrorCount] + (CASE @StatusID WHEN 3 THEN 1 ELSE 0 END)
WHERE ScheduleDataID = @ScheduleDataID;

App code:

When you are creating a new job to be scheduled:
First, call [ScheduleData_AddNewTask] which returns ScheduleDataID
Create the job such that it will:

Start by calling [ScheduleData_UpdateTask], passing in the ScheduleDataID, 2 for @StatusID, and String.Empty for @TaskOutput.
Finish by calling [ScheduleData_UpdateTask], passing in the ScheduleDataID, 1 for @StatusID, and anything relevant for @TaskOutput.
If an error occurs, call [ScheduleData_UpdateTask], passing in the ScheduleDataID, 3 for @StatusID, and anything relevant for @TaskOutput.


How to get SQL Profiler record the user which login to your web application but the SQL username and password is specify in the web.config file?

As a good practice, do not use sa as the log on from you application to the database. Create another user in the database and use that user to interact with the database. That user should have restricted permissions to only be able to access the database objects it needs. I also hope that the password you use is not password. These steps will make your database a little bit more secure from attack/accidental/malicious activity issues.

Profiler is showing the connections to the database based on the user account connecting to the database - that is the user account in the connection string. You could create database accounts for each user, and alter the connection string to use that user account to connect with. This may be a workable solution if you do not have many users.  [It is also useful to do use separate users in conjunction with database permissions as a security/data integrity stratergy (ie user 'boss' has read/write privileges, user 'anonymous' has only read access to the data).]

If you can set up a cross-domain trust that would allow you to use Windows Authentication.  The web app would then need to be configured to impersonate the user that's using it otherwise the web service account will be displayed.

Another option if you are using stored procedures is to add the user ID as an argument to the call to the stored procedures which should show up in the trace.

If you have a data access layer, you could add a routine that logs the user, action and time stamp of each database action. The timestamps should correlate with the profile if you still need to profile information.


What events cause each run_status code to appear in sysjobhistory?

Create a job with a single step. On the advanced tab for that step, change retry attempts from 0 to something greater than 0. In the job step, have it be your SELECT SELECT. Run the job. You will see an entry .

I don't think anyone could provide a full list of all scenarios that could lead to the various possible status values. Did you try anything like this while a job was running (and it's easy to test this with a simple job that has a loop with SELECT TOP (1) name FROM somedb.sys.all_objects;WAITFOR DELAY '00:00:01';); things like:

unplugging the computer
failing over (cluster or AG)
setting somedb to single_user or offline
killing sqlservr.exe process or shutting down the agent service
having the step raising batch- and connection-aborting errors
having the step raise an exception that causes "severe error" or assertion check failures (assuming there are some that exist and you can repro on your version)

run_status is useful, but you can get a better picture from the combination of run_status and message (and, in severe cases, the SQL Server error log). The trouble with message is that the contents can get truncated, so if you have a step that performs 400 successful backups and then one fails, the content will be full of successful messages, and the failure will be missing. For important jobs you may want to log to a table or to a file; you can see a variety of options on the Advanced tab of any job step.


Is there a rule of thumb regarding the size and number of a buffer pool instances?

BUFFER POOL INSTANCES
The rule of thumb I usually use is based on a special program in in the Linux environment
numactl --hardware

When I run this, I get the following output
sh-4.1# numactl --hardware
available: 4 nodes (0-3)
node 0 cpus: 0 1 2 3
node 0 size: 49151 MB
node 0 free: 241 MB
node 1 cpus: 4 5 6 7
node 1 size: 32768 MB
node 1 free: 39 MB
node 2 cpus: 8 9 10 11
node 2 size: 49152 MB
node 2 free: 49 MB
node 3 cpus: 12 13 14 15
node 3 size: 32752 MB
node 3 free: 32 MB
node distances:
node   0   1   2   3
  0:  10  16  16  16
  1:  16  10  16  16
  2:  16  16  10  16
  3:  16  16  16  10

This quickly tells me how many CPUs and cores I have on my DB Server.
In general, I set the innodb_buffer_pool_instances to the number of physical CPUs or the number of cores. In your particular, I set the innodb_buffer_pool_instances to 4 or 16.
BUFFER POOL SIZE
If you make the InnoDB Buffer Pool bigger than 50% of the installed RAM, mysqld causes the OS to start swapping ... BADLY !!!! If you need a Buffer Pool that big, then tuning innodb_buffer_pool_instances becomes even more critical.
EPILOGUE
As soon as innodb_buffer_pool_instances was first introduced, I immediately experimented with it. I had a client that had 192GB DB Server, dual hexacore with a 162GB Buffer Pool. I simply set it to 2 and everything worked out just fine. Please see my old post from Feb 12, 2011 on this : How do you tune MySQL for a heavy InnoDB workload?
GIVE IT A TRY !!!