Performance impact MySQL replication vs table-synchronisation script

First of all about Variant A:

Size of bin-log will not affect to performance if You will use filter to replicate only selected tables, and even if You will replicate all - it also normally not a problem. For compare we replicate 20Gb (and more) between datacenter with real-time loading (telecom)
Keep version of Source and Target Server same - it good practice even if You choose Variant B - it avoid many other troubles

About Variant B - I personally look for tools similar pt-table-sync as for excellent but occasional tools in Your toolbox, and not for permanent solution. I can not explain drawbacks, it just personal vision

Variant C:
Tools like - SymmetricDS

Variant D:
I personally actively work now with CDC variants, when You parse bin-logs and ship them to subscriber over message queue.

I have tested under loading at least 2 working solution (really it more for now):

StreamSets
Maxwell + Talend
Very interesting for some reasons - MariaDB MaxScale, but have not a time for make it work

I personally like this because:

have a lot of integration projects
not always source and target system same. Most resent in my case - MySQL to Redshift, DB2
It also allow realise "event-based" integration - fire Sub-Job when specific data changed
Have more than 1 subscriber with the same producer

Edited for add answer for question in comments:

The problem as medal have 2 (and more) sides:

Size of binary-logs - this is objective, but with good network it not a issue.
for make shipping faster You can enable compression - see more
Time what Slave need to apply all changes from Master. Even if You enable multithread slave it still slower than on Master, and by default with single thread it could be a problem with very high amount of changes on Master.

But as I wrote You can enable filter on Slave side for reduce number of monitored objects - replicate-do-table and Slave will ignore all other changes and apply only for this table (this is case of more wide filter - replicate-do-db), You can repeat this settings for several tables, or use wildcards

Filter logic diagram

Any solution have and benefits and drawbacks, I will prefer Replication because it will apply updates "on the fly" but not only when I run the external program.


How to install RODBC driver for SQL Server 2016 R Services

To install R packages in SQLServer 2016, you first have to launch R GUI (somewhere like D:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\R_SERVICES\bin\x64\Rgui.exe).

If your server is connected to the web, you can launch a :

install.packages("RODBC")

Otherwise, download the package (see https://cran.r-project.org/web/packages/RODBC/index.html), put it your server, for instance in a folder D:\R_Packages, and launch the R command :

setwd("D:/R_Packages")
install.packages("RODBC_1.3-14.zip", repos=NULL)

This second method is not very convenient in case of package dependencies : you'll have to download / install manually all the packages needed....

Anyway, in you want RODBC to access SQLServer databases, maybe you don't need it.
Have a look at sp_execute_external_script procedure which help you to connect directly R and SQLServer.


Linked Server to Azure Error: "Access to the remote server is denied because no login-mapping exists."

The other answer is wrong and I want to disprove it with repro code and pictures.

The problem here is the parameter @provstr, it's a known issue and how to solve it is described here: Access to the remote server is denied because no login-mapping exists:

  Problem: Unable to run a query through a linked server SQL Server
  2005. This problem only happens with a non-sysadmin account.

  You got the message below:

  Msg 7416, Level 16, State 2, Line 1 Access to the remote server is
  denied because no login-mapping exists..

  Cause: When creating a linked server with the parameter @provstr and
  you use a local SQL Server non-admin or non-Windows account, you have
  to add the parameter "User Name"   into the @provstr

  Resolution : Add "User ID=Username" into the provider string on your
  linked server

  EXEC master.dbo.sp_addlinkedserver @server = N'LinkServerName',
  @provider=N'SQLNCLI',@srvproduct = 'MS SQL Server',
  @provstr=N'SERVER=serverName\InstanceName;User ID=myUser'

  EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname = N'LinkServerName',
  @locallogin = NULL , @useself = N'False', @rmtuser = N'myUser',
  @rmtpassword = N'*****'

The OP has already done it, he included remote login into provider string in the linked server definition, so I really have no idea what else can cause the error.

But now I want to prove that following the cited guide you get rid of the error, while following the other answer we solve nothing.

Here is my repro code.

I have 2 servers, the default and named instance (hp2\sql_2012_dev), I'm going to link hp2\sql_2012_dev server to the default using @provstr without passing in @provstr remote login rmt that is not admin, this will cause the OP's error for ALL non-admin users.

I will map all the local logins to 1 remote login using OP's code.
Then I'll try to apply the suggestion to map the problematic login individually but this will not solve the problem.

Then I'll recreate my linked server passing the remote login with the provider string and this will fix the issue WITHOUT ANY ADDITIONAL INDIVIDUAL MAPPING.

EXEC master.dbo.sp_addlinkedserver
@server = N'LinkServer2012_rmt',
@provider=N'SQLNCLI',
@srvproduct = '',
@provstr=N'SERVER=hp2\sql_2012_dev';--don't pass User ID=rmt' now in order to cause the error

EXEC master.dbo.sp_addlinkedsrvlogin
@rmtsrvname = N'LinkServer2012_rmt',
@locallogin = NULL ,
@useself = N'False',
@rmtuser = N'rmt',
@rmtpassword = N'*****';

At this point non-admin login l tries to select:

select *
from linkserver2012_rmt.master.dbo.spt_values;

And got the error

  Msg 7416, Level 16, State 2, Line 1 Access to the remote server is
  denied because no login-mapping exists.

Ok, we are trying now to map it individually:

EXEC master.dbo.sp_addlinkedsrvlogin
@rmtsrvname = N'LinkServer2012_rmt',
@locallogin = N'l',
@useself = N'False',
@rmtuser = N'rmt',
@rmtpassword = N'*****';

And login l re-tries to select from linked server but he's got the same error

At this point I drop and re-create linked server, this time passing in rmt login in linked server definition, and map all the local logins to rmt, without doing any individual mapping:

exec master.dbo.sp_dropserver @server=N'LinkServer2012_rmt', @droplogins='droplogins';

EXEC master.dbo.sp_addlinkedserver
@server = N'LinkServer2012_rmt',
@provider=N'SQLNCLI',
@srvproduct = '',
@provstr=N'SERVER=hp2\sql_2012_dev;User ID=rmt'; --now pass in rmt login

EXEC master.dbo.sp_addlinkedsrvlogin
@rmtsrvname = N'LinkServer2012_rmt',
@locallogin = NULL ,
@useself = N'False',
@rmtuser = N'rmt',
@rmtpassword = N'*****';

This time non-admin login l has no problem at all:

I cannot consider this my post a real answer to OP's problem, maybe the cause is Azure and I cannot test it because I have no access to Azure server. But at least it solves the same problem when linking to on-prem instance.

UPDATE

Try to not to use @provstr at all when creating linked server like this:

EXEC sp_addlinkedserver
   @server=N'LincServer',
   @srvproduct=N'',
   @provider=N'SQLNCLI',
   @datasrc=N'myAzureDB.database.windows.net',
   @catalog='MyDB';


If someone is INSERT/DELETE/UPDATE on a table at the same moment your expdp is working on that table, it won't affect that object.  You will get a consistent snap of the table at the moment that expdp got to that table.  Basically expdp is consistent WITHIN a table, but not BETWEEN/AMONG tables, unless ...

Since expdp processes your tables serially, they will not be consistent between each other unless you do your expdp with the FLASHBACK_TIME=SYSTIMESTAMP option.  I typically do this on every expdp unless I have a good reason not to.  This does not require database flashback to be enabled, but makes use of UNDO (rollback segments).  If your data changes too rapidly, your expdp will error with "ORA-01555 snapshot too old" and the table will be skipped. This means that there was not enough space in UNDO to preserve read-consistency across your entire expdp job. Increase UNDO retention in this case.

Finally, if someone or some process does DDL on your table (such as TRUNCATE, DROP, or adding/dropping columns/partitions) while expdp is runnning, then the expdp will fail on this object with ORA-01466: unable to read data - table definition has changed.  Fortunately this doesn't happen too often, but I have seen it with ETL workloads.


Does a single replica gives any performance improvement?

Am I missing something? Is the write work performed by a replica less then the work of the master (considering that each update must be applied to both the machines)?

Probably yes, you are missing something, and write work done on the replica is less than the (total) work of the master.  It is rare for every write on a database to be of the form update foo set bar=592 where pk=127.  What if it were instead update foo set bar=592 where bar is distinct from 592 and pk in (select foo_id from baz where t ilike '%one fish%' and sin(foo.z)<sqrt(baz.y)/7) and tzr @> :something.  How many reads (and CPU time, and memory) would need to happen to figure out which writes end up happening?  Replication, however, just needs to replay the end result.
But if you were setting up read replication just for performance, it would be pretty weird to set up just one of them.  Unless that one was on your LAN, while the master was someplace with more latency.


Why would i get a duplicate write error of auto-generated primary key?

I think this could happen only if manual intervention was done to that primary key. It's possible to manually insert data in the identity column only if option "set identity_insert table_name on" was used, that way you can generate PK errors.
But better check exactly if the PK is only that identity column, and, if yes, if this option is used in any of the stored procedures or scripts that you use.

SQL Server alone would not have any reason to not treat correctly these columns.


Am I breaking something if I set an autoincrement PK to 0?

Although not usual practice, a primary key can be 0 because 0 is a valid integer. If you set auto increment on the column it will normally start from 1, however, manually changing an id on an existing row to 0 will not cause any issues. If you have any foreign keys that referenced ID 4 before you changed it to 0, then you'll need to update those as well.
Note that if you use 0 in an INSERT for a primary key, MySQL interprets that as a request to generate a new key. In those cases, you can use the following to tell MySQL to interpret 0 INSERTs as a true 0, not a PRIMARY KEY generation request.
SET SESSION sql_mode='NO_AUTO_VALUE_ON_ZERO';


The answer to this depends on what date range you pick

EXAMPLE : Ranking Points Since The Beginning of the Month for Competition 'Kids Only'

SELECT * FROM
(
    SELECT
        A.name Athlete,R.ranking_points Rank,C.`date` CompDate
    FROM
    (
        SELECT id,`date` FROM competitions
        WHERE name = 'Kids Only' AND `date` >=
        DATE(NOW()) - INTERVAL (DAY(DATE(NOW()))-1) DAY + INTERVAL 0 SECOND;
    ) C
    INNER JOIN results R ON C.id = R.id_competition
    INNER JOIN athletes A ON R.id_athlete = A.id
) AA
ORDER BY Rank DESC;

EXAMPLE : Teenage Rankings For the Week of 2012-10-08 - 2012-10-15

SELECT * FROM
(
    SELECT
        A.name Athlete,R.ranking_points Rank,C.`date` CompDate
    FROM
    (
        SELECT id,`date` FROM competitions
        WHERE name = 'Teenage'
        AND `date` >= DATE('2012-10-08') + INTERVAL 0 SECOND
        AND `date` <  DATE('2012-10-15') + INTERVAL 0 SECOND
    ) C
    INNER JOIN results R ON C.id = R.id_competition
    INNER JOIN athletes A ON R.id_athlete = A.id
) AA
ORDER BY Rank DESC;

I hope these give you a basis to start...


Connection to SQL Server requires TCP communication on port 50235?

Are you connecting to a named instance?  In which case, the initial connection is to the SQL Server Browser service (UDP 1434) and then that dynamically allocates a port for named instances.  This port is allocated on instance startup.

See this TechNet Resource on how the SQL Server Browser Service allocates a port for named instances.

There is a way to explicitly define a static port that a named instance will be listening on, but it appears that you are seeing a dynamically allocated port.


Designing Students Exams and Classes Database

My thoughts after a quick review (edited):

An Exam should not have a Day/Start Time/End time. The Exam entity represents the EXAM definition, it should have subject, year etc, NOT the time it is to be sat. I assume from your design that a class can have multiple exams. What you are missing is an ExamEvent entity. This would represent an exam event, where everyone sits down and takes it, NOT the definition of an exam (as the same exam can happen in different schools, by your description). An ExamEvent needs to have an ExamID, ClassCode (think about having a surrogate ID for the CLASSEXAM Entity), Time/Date, LanguageID (assuming everyone sits the exam at the same time in the same language), RoomCode. It should also have its own surrogate key, if needed: ExamEventID. Review the name of this entity.
It looks like you are then missing another key entity - a StudentExam entity, which would have: StudentID, ExamEventID and SeatCode. This represents the student attending the exam and where they are sitting. If each student sits the exam in a different language, but at the same time, the Language entity needs to relate to this entity NOT the ExamEvent entity.
Sticking with what I have specified above, you should then remove the 2nd Student/School link (currently representing the place the student is taking the exam). Instead, using the design I specified, the place the student is taking the exam is defined by the StudentExam object as it links via ExamForClass, through to ClassRoom, through to School.


Query PostgreSQL 9.0 table on foreign key value?

"I'm certain every time that I'm bringing a gun to a knife fight, but my googling has been fruitless." What makes you think that? RDBMSs are built to 'join'

If your aim is to make you SQL more readable then you might prefer the 'using' syntax:

SELECT * FROM candies JOIN colors USING(color_code) WHERE color_value = 'blue';

alternatively, seeing as you are hard-coding 'blue' into your query, you could consider hard-coding the code instead:

SELECT * FROM candies WHERE color_code = '02';

but this will decrease readability unless in your real-world scenario the codes are descriptive (eg 'BLU' for blue)


Besides Gary's answer, I came up with the following:

cast(NULLIF([expected_discharge_dttm],'') as datetime)

Can anyone foresee an issue with this code (or Gary's use of CASE)?


The last time I played with Access was when 2003 was the hot new thing, so this may not be entirely accurate to every detail. However, what you need to do is go to the query designer, change the view to "SQL" (i.e. raw text entry) and then you want to UNION your two left-join queries together, e.g.

SELECT ListA.*, ListB.*
FROM ListA LEFT JOIN ListB ON ListA.[StaffID] = ListB.StaffID
UNION
SELECT ListA.*, ListB.*
FROM ListB LEFT JOIN ListA ON ListB.[StaffID] = ListA.StaffID


MariaDB why do I get error "Unknown table x in field list" when it does exist (yes, another one!)

I think this might be the answer and might help others with this problem.
I had a test trigger on the table member
 CREATE  TRIGGER `test` BEFORE UPDATE ON `member` FOR EACH ROW
    BEGIN
     DECLARE addr VARCHAR(100);
     DECLARE        joint INT;
        SET @joint := is_joint_member(member.member_id);
        SET @addr = 'jgkjgkjg' ;
     CALL dummy2;
    END $$

where
is_joint_member()

is a working UDF that I've used for ages and
dummy2()

is an empty, but working stored procedure just to see if I could call a proc inside a trigger. However the trigger might not be working correctly as if I alter it and comment out
SET @joint := is_joint_member(member.member_id);

then my UPDATE works OK, as it does if I drop the trigger all together.
In other words the error
Error Code: 1109 Unknown table 'member' in field list

is misleading as it does not mean that the table doesn't exist, it means that something has gone wrong with a trigger attached to the update event on that table.
Something to watch out for in future!


Workaround to avoid read-only table locking during execution of MySql OPTIMIZE command

Install percona-toolkit. Then you can use pt-online-schema-change.

I usually use it when I have to alter tables. What it does is copy the data from your current table to another table. Triggers are automatically created, that copy incoming data to the copy while copying. This only works flawlessly if you don't have triggers on the table already. After that, the copied table gets renamed to the original table (which gets dropped, unless you specify not to with parameters). Then you'll have your optimized table.

  For InnoDB tables, OPTIMIZE TABLE is mapped to ALTER TABLE, which rebuilds the table to update index statistics and free unused space in the clustered index.

So an

OPTIMIZE TABLE tablename;

is actually

ALTER TABLE tablename ENGINE = InnoDB;

Then use pt-online-schema-change like this (on command line, not in MySQL client):

pt-online-schema-change --dry-run --user=root --host=localhost  --alter "ENGINE=InnoDB" --alter-foreign-keys-method=auto D=databasename,t=tablename,p=userpassword

If this runs well, change --dry-run to --execute.


How to structure this data? (Many columns currently.)

To normalise this table you must remove the repeating groups and multi-value columns. The resulting table will be

objID
LevelNumber
State
Colour1
Colour2

Using the sample data from your question you would see

ObjID   | Level  | State | Color1 | Color2
8374629 |  0     |  mmq  | FFFF00 | FFFF00
8374629 |  1     |  mqm  | 663FBA | 34CF09
...
8374629 | 99     |  mqq  | CDCDCD | 00CDCD
....
8374649 |  1     |  mmm  | BC1A44 | FF00FF
8374649 |  2     |  qqq  | 000000 | FFCFFF
...
8374649 | 99     |  mqm  | 2323DD | 01CD11

The colors can be normalised into a separate table, too, giving two tables:

State              Color
=====              =====
objID              objID
LevelNumber        LevelNumber
State              ColourNumber
                   ColourValue

A table with several hundred columns is within the capabilities of any DBMS. It may be strange but it is not nesessarily wrong.


Indexes don't have to be unique. Primary keys do.

The purpose of a Primary Key is to uniquely identify a single row of data. If you don't have something that is naturally unique then add a column such as an identity column and define it as the primary key. It's standard practice, and will help you later if you need to update a row.

If the table is being queried for analysis or reporting a single table is fine. Do some analysis on the types of queries you are performing and add indexes on relevant key columns. Good indexes will significantly improve query performance.

For example if you are looking for results for a single store, by adding an index on the store id, you could exclude the other 299 stores from the select. This reduces IO and speeds up the query. If you have years worth of data but are only looking for things that happened in the last week then adding an index to a date column may be a big help.

Look at your queries and see if there fields you are regularly filtering on. Start with those.

Ensure the db has updated statistics for the table and see if performance improves.

The results you get will vary depending upon what you are trying to do. If you are trying to aggregate records (i.e total sales for each store) then the query may still have to read the whole table.

Below I've added a script with a basic test example.

USE [YOURDBNAME]
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

-- Create Test Table.

CREATE TABLE [dbo].[rewards](
    [id] [int] IDENTITY(1,1) NOT NULL,
    [fname] [varchar](50) NULL,
    [lname] [varchar](50) NULL,
    [tstamp] [datetime] NULL,
    [card_id] [int] NULL

) ON [PRIMARY]

-- Add Primary key. This can be done at table creation. Or added after as shown below.

ALTER TABLE rewards ADD CONSTRAINT pk_rewards PRIMARY KEY NONCLUSTERED  ([id])

-- Add an index on columns that your query will use.

CREATE CLUSTERED INDEX [IDX01] ON [dbo].[rewards]
(   [card_id] ASC,
    [fname] ASC,
    [lname] ASC
) ON [PRIMARY]

-- Create some test data.

INSERT INTO [dbo].[rewards] VALUES ('Allan2', 'Aadvark2', getdate(), 2345) ;
INSERT INTO [dbo].[rewards] VALUES ('Billy2', 'Babo0n', getdate(), 2356) ;
INSERT INTO [dbo].[rewards] VALUES ('Chester2', 'Cheetah', getdate(), 2367) ;
INSERT INTO [dbo].[rewards] VALUES ('Doodoo', 'Dog', getdate(), 2376) ;
INSERT INTO [dbo].[rewards] VALUES ('Esme', 'Elephant', getdate(), 1235) ;
INSERT INTO [dbo].[rewards] VALUES ('Freddy', 'Fox', getdate(), 1239) ;

GO

-- run the following query repeatedly to generate volume.
INSERT INTO [dbo].[rewards]
           ([fname]
           ,[lname]
           ,[tstamp]
           ,[card_id])
     select [fname]
           ,[lname]
           ,[tstamp]
           ,[card_id]
           from [dbo].[rewards] ;

GO

SET STATISTICS TIME ON
GO

-- Test query. This shows that the card 1234 is use by 2 different customers.

Select r.card_id, count(*)
from (
    select card_id, fname, lname
    from [dbo].[rewards]
    group by card_id, fname, lname ) r

group by r.card_id
having count(*) > 1 ;

GO

SET STATISTICS TIME OFF

GO

-- I just tested this with 3,538,944 rows. And I get SQL Server Execution Times:  CPU time = 1904 ms,  elapsed time = 490 ms.
-- On repeated execution I can reliably get this under .5 of a second.

Your results may vary depending upon machine setup and load, But I think you should resonably expect to get your query down to ~1 sec or less.


MySQL database backup stuck on 'checking permissions'

Rate Per Second = RPS
Suggestions to consider for your my.ini [mysqld] section
innodb_buffer_pool_size=1G  # from 8M for a much better experience
innodb_log_buffer_size=16M  # from 1M to support ~ 30 mn in RAM before write to media
open_files_limit=60000  # from 6209 to better support your opened_* high volume
innodb_open_files=48000  # from 300 to reduce opened_tables RPhr of 24,310
table_open_cache=48000  # from 2000 should always be matched to innodb_open_files
table_definition_cache=6000  # from 1400 to support opened_table_definitions RPhr of 2,152
innodb_io_capacity=1000  # from 200 to use more of your available SSD io capacity

These are just the beginning of tuning for your serious workload.  View profile, Network profile for additional suggestions and free downloadable Utility Scripts to assist with performance tuning.


Start service error code 3417 with SQL Server 2012

That error is usually associated with the Master database, so you are on the right track. Does the service account have access to the directory where the Master database resides?

There should be some additional errors in either the SQL Server error log or the Windows event log that may narrow down the issue. Look for access denied messages or cannot find file messages associated with the master database and other system databases.

One other thing I have found on other sites is folder compression being a problem. Make sure SQL Server does not reside on a compressed folder or drive.

If that doesn't work, then rebuild as suggested.


Decision criteria on when to use a non-dbo schema vs a new Database

I'll start by saying don't consider schemas as namespaces or object domains in the OO sense. Schemas are essentially permission containers with some added value (see below)

Also, "separate schemas" or "separate databases" are 2 different concepts. Data that needs to be transactionally and referentially consistent needs to be in the same database.  See One Database or Ten? blog article for more.

In that database, you may or may not use schemas for organising your objects.

Personally, I'm a fan of schemas and always use them but for things like permissions and logical grouping. For that, I'll refer you to previous questions where you can see the general opinion is in favour of them:

Schema design - best practices?
What standard should I follow when naming tables and views?
Over use/correct use of schemas?

For the case for separate databases, see Aaron's answer, but this all hinge on the "transactionally and referentially consistent" requirement.


It's typically not the count of deadlocks that can cause a serious problem, but the reason to investigate would be to find out what workloads are deadlocking on each other.  After all, in a deadlock there is a victim.  So now you have a process that didn't complete the work that it intended to complete.  And, in good theory applications should have some sort of retry logic in them for situations like this... but in practice this almost never happens.

With that being said, investigate what is deadlocking.  Typically it is the same workload or type of workload that generates the majority of deadlocks.  A few quick fixes (after reading the deadlock XML) could be an easy win to get rid of most of them.

(yes, 100+ deadlocks a day is a lot.  But any recurring deadlocking pattern, whether it is 1 or 100, should be investigated)


It seems your Extended Event session is defined with STARTUP_STATE=OFF so it is not started automatically when SQL Server is restarted.

Change the session to STARTUP_STATE=ON to ensure it's always running (unless stopped manually):

ALTER EVENT SESSION [blocked_process] ON SERVER
    WITH (STARTUP_STATE=ON);


Multiple Set Quoted_identifier within one script takes the last one?

This is by design:

For a top-level ad-hoc batch parsing begins using the session's current setting for QUOTED_IDENTIFIER. As the batch is parsed any occurrence of SET QUOTED_IDENTIFIER will change the parsing behavior from that point on, and save that setting for the session. So after the batch is parsed and executed, the session's QUOTED_IDENTIFER setting will be set according to the last occurrence of SET QUOTED_IDENTIFIER in the batch.

The behaviour of QUOTED_IDENTIFIER is therefore dependent on the parsing of the batch, not the execution. The default at the start of parsing the batch comes from the current connection settings.
@@OPTIONS only shows you the current default, that would be used if a batch was parsed. This is why SSMS always puts SET in a separate batch.
The execution of SET is not relevant to the behaviour of @@OPTIONS, except to change later batches' default.

You can see this in action in a db-fiddle
set QUOTED_IDENTIFIER  on;
create table "select"(i int);
drop table "select";
GO

set QUOTED_IDENTIFIER  off;
create table "select"(i int);
drop table "select";
--  Incorrect syntax near 'select'.
GO

set QUOTED_IDENTIFIER  off;
if (1=0)
begin
    set QUOTED_IDENTIFIER  on;
end
create table "select"(i int);
drop table "select";
GO

set QUOTED_IDENTIFIER  on;
if (1=0)
begin
    set QUOTED_IDENTIFIER  off;
end
create table "select"(i int);
drop table "select";
-- Incorrect syntax near 'select'.
GO


The installer that I was initially using was SQL Server 2016 Developer with SP1. When I would try and apply CU1 for SP1 it was failing. I downloaded the SQL Server 2016 Developer RTM and installed it. I then applied SP1, rebooted and applied CU1 for SP1 and that worked. What I am not sure of is why there is a difference between the two scenarios.


How to insert into 2 tables which are dependant on each other

with correction after comment from author:

example of procedure

CREATE DEFINER=`root`@`%` PROCEDURE `udp_insert_company`(`v_id` double, `v_document_type_id` double)
BEGIN
declare v_error_text varchar(200);
set v_error_text = '';

IF NOT EXISTS (SELECT id FROM document_type where id = v_document_type_id) THEN
        SELECT CONCAT('ERROR - id ', CAST(v_id AS CHAR), ' not found') into v_error_text;
    SELECT v_error_text;
ELSE
    INSERT INTO company VALUES (v_id, v_document_type_id);
END IF;

END

it just example, You can use any other method for return error code

@call `test_db`.`udp_insert_company`(4, 3);

ERROR - id 4 not found


How to Update Date in each row with different dates in MySQL

If the goal is to have the following:

id
date
message

1
2021-05-26
There is no spoon.

2
2021-05-27
I know kung fu.

3
2021-05-28
zOMG! A Nokia 8801!

4
2021-05-29
Anything you'd like, Mr. Reagan.

5
2021-05-30
Follow the white rabbit.

6
2021-05-31
Not like this.

... then I would suggest taking a look at the DATE_ADD() function and using the id value as the INTERVAL amount.


Check the data latency between two Always On Availability Group servers in ASYNC mode

I used this script in a custom report once.

;WITH AG_Stats AS (
            SELECT AGS.name                       AS AGGroupName,
                   AR.replica_server_name         AS InstanceName,
                   HARS.role_desc,
                   Db_name(DRS.database_id)       AS DBName,
                   DRS.database_id,
                   AR.availability_mode_desc      AS SyncMode,
                   DRS.synchronization_state_desc AS SyncState,
                   DRS.last_hardened_lsn,
                   DRS.end_of_log_lsn,
                   DRS.last_redone_lsn,
                   DRS.last_hardened_time, -- On a secondary database, time of the log-block identifier for the last hardened LSN (last_hardened_lsn).
                   DRS.last_redone_time, -- Time when the last log record was redone on the secondary database.
                   DRS.log_send_queue_size,
                   DRS.redo_queue_size,
                    --Time corresponding to the last commit record.
                    --On the secondary database, this time is the same as on the primary database.
                    --On the primary replica, each secondary database row displays the time that the secondary replica that hosts that secondary database
                    --   has reported back to the primary replica. The difference in time between the primary-database row and a given secondary-database
                    --   row represents approximately the recovery time objective (RPO), assuming that the redo process is caught up and that the progress
                    --   has been reported back to the primary replica by the secondary replica.
                   DRS.last_commit_time
            FROM   sys.dm_hadr_database_replica_states DRS
            LEFT JOIN sys.availability_replicas AR
            ON DRS.replica_id = AR.replica_id
            LEFT JOIN sys.availability_groups AGS
            ON AR.group_id = AGS.group_id
            LEFT JOIN sys.dm_hadr_availability_replica_states HARS ON AR.group_id = HARS.group_id
            AND AR.replica_id = HARS.replica_id
            ),
    Pri_CommitTime AS
            (
            SELECT  DBName
                    , last_commit_time
            FROM    AG_Stats
            WHERE   role_desc = 'PRIMARY'
            ),
    Rpt_CommitTime AS
            (
            SELECT  DBName, last_commit_time
            FROM    AG_Stats
            WHERE   role_desc = 'SECONDARY' AND [InstanceName] = 'InstanceNameB-PrimaryDataCenter'
            ),
    FO_CommitTime AS
            (
            SELECT  DBName, last_commit_time
            FROM    AG_Stats
            WHERE   role_desc = 'SECONDARY' AND ([InstanceName] = 'InstanceNameC-SecondaryDataCenter' OR [InstanceName] = 'InstanceNameD-SecondaryDataCenter')
            )
SELECT p.[DBName] AS [DatabaseName], p.last_commit_time AS [Primary_Last_Commit_Time]
    , r.last_commit_time AS [Reporting_Last_Commit_Time]
    , DATEDIFF(ss,r.last_commit_time,p.last_commit_time) AS [Reporting_Sync_Lag_(secs)]
    , f.last_commit_time AS [FailOver_Last_Commit_Time]
    , DATEDIFF(ss,f.last_commit_time,p.last_commit_time) AS [FailOver_Sync_Lag_(secs)]
FROM Pri_CommitTime p
LEFT JOIN Rpt_CommitTime r ON [r].[DBName] = [p].[DBName]
LEFT JOIN FO_CommitTime f ON [f].[DBName] = [p].[DBName]


CockroachDB PM here. I don't have a very satisfying answer for you, unfortunately; Postgres logical decoding is difficult to implement correctly, and I don't know of anything offhand that satisfies your needs.

Postgres links to a number of off-the-shelf logical decoding plugins, but in my experience these are of varying quality. If you use one of these, you'll want to verify that it properly handles columns that use Postgres's TOAST mechanism -- last time I looked at these, they generally did not. After you get your data streaming out of Postgres, you can consume them and upsert them into your Cockroach database on a separate SQL connection.

If a zero-downtime migration is not required, you could use CockroachDB's IMPORT PGDUMP functionality: use Postgres's own dump functionality to dump your data to a file, and then import it into CockroachDB.


Amazon Redshift - The difference between Query Slots, Concurrency and Queues?

I think your understanding of query queues is a little off.

  A queue is like a thread in Java. A query arrives and is designated to the "less loaded" queue, and it waits for its turn to be resolved.

The decision of where to put a query is independent of how busy a queue is; queries are allocated based on the rules you've set up: http://docs.aws.amazon.com/redshift/latest/dg/cm-c-wlm-queue-assignment-rules.html

  We can have as many queues as we like.

Not quite, check out http://docs.aws.amazon.com/redshift/latest/dg/cm-c-defining-query-queues.html. The relevant line is

  The maximum total concurrency level for all user-defined queues, not including the reserved Superuser queue, is 50.

So the concurrency is definitely limited, but that's a reasonable restriction because each concurrency slot reserves some of the cluster's resources.

  A queue has some memory allocated (we guess divided equally?)

By default memory is split equally among queues, but you can assign memory at the granularity of 1% of cluster memory. See the above link about defining query queues.

  In a queue we can assign user groups or queries groups. But in short-term, that's a lot of classification work in our queries we can't do right now.

Creating user groups and setting query groups is actually pretty simple, see http://docs.aws.amazon.com/redshift/latest/dg/r_CREATE_GROUP.html and http://docs.aws.amazon.com/redshift/latest/dg/r_query_group.html.

If you didn't set up user groups or query groups, then my guess is that you didn't see any improvement after adding the extra queues because all the queries still ran in a single query queue. (In fact, the extra queues would simply take resources from the queue running your queries.) Since all your queries are running from the same user and for the same purpose, it makes sense that they should all resolve to the same query queue. A better solution might be to have only a single queue, but increase the concurrency level. (Keep in mind that a query queue's resources are divided equally among all concurrency slots, even if they're not being used, so a concurrency level of 50 means no query gets more than 2% of the total resources.)

All that being said, redshift is a higher-latency system than many other database solutions. If you're only looking to run lots of small queries, then it might not be the best fit for your problem.


pg_restore fails for a pg_dump from a db that worked just fine

I got a notification that my answer was migrated to dba.stackexchange.com. But all that migrated was the question. I'll answer again.

Make sure the character types are the same, or at least that they're compatible. An 80-character UTF8 string might be 240 bytes long.

Collation should probably be the same on both computers, too.


Have you already had a look at the official material?

Reference: Appropriate Uses For SQLite (SQLlite.com)

(emphasis mine)

Situations Where SQLite Works Well

Embedded devices and the internet of things

Because an SQLite database requires no administration, it works well in devices that must operate without expert human support. SQLite is a good fit for use in cellphones, set-top boxes, televisions, game consoles, cameras, watches, kitchen appliances, thermostats, automobiles, machine tools, airplanes, remote sensors, drones, medical devices, and robots: the "internet of things".

Client/server database engines are designed to live inside a lovingly-attended datacenter at the core of the network. SQLite works there too, but SQLite also thrives at the edge of the network, fending for itself while providing fast and reliable data services to applications that would otherwise have dodgy connectivity.
Application file format

SQLite is often used as the on-disk file format for desktop applications such as version control systems, financial analysis tools, media cataloging and editing suites, CAD packages, record keeping programs, and so forth. The traditional File/Open operation calls sqlite3_open() to attach to the database file. Updates happen automatically as application content is revised so the File/Save menu option becomes superfluous. The File/Save_As menu option can be implemented using the backup API.

There are many benefits to this approach, including improved performance, reduced cost and complexity, and improved reliability. See technical notes "aff_short.html" and "appfileformat.html" and "fasterthanfs.html" for more information. This use case is closely related to the data transfer format and data container use cases below.
Websites

SQLite works great as the database engine for most low to medium traffic websites (which is to say, most websites). The amount of web traffic that SQLite can handle depends on how heavily the website uses its database. Generally speaking, any site that gets fewer than 100K hits/day should work fine with SQLite. The 100K hits/day figure is a conservative estimate, not a hard upper bound. SQLite has been demonstrated to work with 10 times that amount of traffic.

The SQLite website (https://www.sqlite.org/) uses SQLite itself, of course, and as of this writing (2015) it handles about 400K to 500K HTTP requests per day, about 15-20% of which are dynamic pages touching the database. Dynamic content uses about 200 SQL statements per webpage. This setup runs on a single VM that shares a physical server with 23 others and yet still keeps the load average below 0.1 most of the time.
Data analysis

People who understand SQL can employ the sqlite3 command-line shell (or various third-party SQLite access programs) to analyze large datasets. Raw data can be imported from CSV files, then that data can be sliced and diced to generate a myriad of summary reports. More complex analysis can be done using simple scripts written in Tcl or Python (both of which come with SQLite built-in) or in R or other languages using readily available adaptors. Possible uses include website log analysis, sports statistics analysis, compilation of programming metrics, and analysis of experimental results. Many bioinformatics researchers use SQLite in this way.

The same thing can be done with an enterprise client/server database, of course. The advantage of SQLite is that it is easier to install and use and the resulting database is a single file that can be written to a USB memory stick or emailed to a colleague.
Cache for enterprise data

Many applications use SQLite as a cache of relevant content from an enterprise RDBMS. This reduces latency, since most queries now occur against the local cache and avoid a network round-trip. It also reduces the load on the network and on the central database server. And in many cases, it means that the client-side application can continue operating during network outages.
Server-side database

Systems designers report success using SQLite as a data store on server applications running in the datacenter, or in other words, using SQLite as the underlying storage engine for an application-specific database server.

With this pattern, the overall system is still client/server: clients send requests to the server and get back replies over the network. But instead of sending generic SQL and getting back raw table content, the client requests and server responses are high-level and application-specific. The server translates requests into multiple SQL queries, gathers the results, does post-processing, filtering, and analysis, then constructs a high-level reply containing only the essential information.

Developers report that SQLite is often faster than a client/server SQL database engine in this scenario. Database requests are serialized by the server, so concurrency is not an issue. Concurrency is also improved by "database sharding": using separate database files for different subdomains. For example, the server might have a separate SQLite database for each user, so that the server can handle hundreds or thousands of simultaneous connections, but each SQLite database is only used by one connection.
Data transfer format

Because an SQLite database is a single compact file in a well-defined cross-platform format, it is often used as a container for transfering content from one system to another. The sender gathers content into an SQLite database file, transfers that one file to the receiver, then the receiver uses SQL to extract the content as needed.

An SQLite database facilitates data transfer between systems even when the endpoints have different word sizes and/or byte orders. The data can be a complex mix of large binary blobs, text, and small numeric or boolean values. The data format can be easily extended by adding new tables and/or columns, without breaking legacy receivers. The SQL query language means that receivers are not required to parse the entire transfer all at once, but can instead query the received content as needed. The data format is "transparent" in the sense that it is easily decoded for human viewing using a variety of universally available, open-source tools, from multiple vendors.
File archive and/or data container

The SQLite Archive idea shows how SQLite can be used as a substitute for ZIP archives or Tarballs. An archive of files stored in SQLite is only very slightly larger, and in some cases actually smaller, than the equivalent ZIP archive. And an SQLite archive features incremental and atomic updating and the ability to store much richer metadata.

Fossil version 2.5 and later offers SQLite Archive files as a download format, in addition to traditional tarball and ZIP archive. The sqlite3.exe command-line shell version 3.22.0 and later will create, list, or unpack an SQL archiving using the .archive command.

SQLite is a good solution for any situation that requires bundling diverse content into a self-contained and self-describing package for shipment across a network. Content is encoding in a well-defined, cross-platform, and stable file format. The encoding is efficient, and receivers can extract small subsets of the content without having to read and parse the entire file.

SQL archives are useful as the distribution format for software or content updates that are broadcast to many clients. Variations on this idea are used, for example, to transmit TV programming guides to set-top boxes and to send over-the-air updates to vehicle navigation systems.
Replacement for ad hoc disk files

Many programs use fopen(), fread(), and fwrite() to create and manage files of data in home-grown formats. SQLite works particularly well as a replacement for these ad hoc data files. Contrary to intuition, SQLite can be faster than the filesystem for reading and writing content to disk.
Internal or temporary databases

For programs that have a lot of data that must be sifted and sorted in diverse ways, it is often easier and quicker to load the data into an in-memory SQLite database and use queries with joins and ORDER BY clauses to extract the data in the form and order needed rather than to try to code the same operations manually. Using an SQL database internally in this way also gives the program greater flexibility since new columns and indices can be added without having to recode every query.
Stand-in for an enterprise database during demos or testing

Client applications typically use a generic database interface that allows connections to various SQL database engines. It makes good sense to include SQLite in the mix of supported databases and to statically link the SQLite engine in with the client. That way the client program can be used standalone with an SQLite data file for testing or for demonstrations.
Education and Training

Because it is simple to setup and use (installation is trivial: just copy the sqlite3 or sqlite3.exe executable to the target machine and run it) SQLite makes a good database engine for use in teaching SQL. Students can easily create as many databases as they like and can email databases to the instructor for comments or grading. For more advanced students who are interested in studying how an RDBMS is implemented, the modular and well-commented and documented SQLite code can serve as a good basis.
Experimental SQL language extensions

The simple, modular design of SQLite makes it a good platform for prototyping new, experimental database language features or ideas.

Situations Where A Client/Server RDBMS May Work Better

Client/Server Applications

If there are many client programs sending SQL to the same database over a network, then use a client/server database engine instead of SQLite. SQLite will work over a network filesystem, but because of the latency associated with most network filesystems, performance will not be great. Also, file locking logic is buggy in many network filesystem implementations (on both Unix and Windows). If file locking does not work correctly, two or more clients might try to modify the same part of the same database at the same time, resulting in corruption. Because this problem results from bugs in the underlying filesystem implementation, there is nothing SQLite can do to prevent it.

A good rule of thumb is to avoid using SQLite in situations where the same database will be accessed directly (without an intervening application server) and simultaneously from many computers over a network.
High-volume Websites

SQLite will normally work fine as the database backend to a website. But if the website is write-intensive or is so busy that it requires multiple servers, then consider using an enterprise-class client/server database engine instead of SQLite.
Very large datasets

An SQLite database is limited in size to 140 terabytes (247 bytes, 128 tibibytes). And even if it could handle larger databases, SQLite stores the entire database in a single disk file and many filesystems limit the maximum size of files to something less than this. So if you are contemplating databases of this magnitude, you would do well to consider using a client/server database engine that spreads its content across multiple disk files, and perhaps across multiple volumes.
High Concurrency

SQLite supports an unlimited number of simultaneous readers, but it will only allow one writer at any instant in time. For many situations, this is not a problem. Writers queue up. Each application does its database work quickly and moves on, and no lock lasts for more than a few dozen milliseconds. But there are some applications that require more concurrency, and those applications may need to seek a different solution.

Checklist For Choosing The Right Database Engine

Is the data separated from the application by a network? → choose client/server

Relational database engines act as bandwidth-reducing data filters. So it is best to keep the database engine and the data on the same physical device so that the high-bandwidth engine-to-disk link does not have to traverse the network, only the lower-bandwidth application-to-engine link.

But SQLite is built into the application. So if the data is on a separate device from the application, it is required that the higher bandwidth engine-to-disk link be across the network. This works, but it is suboptimal. Hence, it is usually better to select a client/server database engine when the data is on a separate device from the application.

Nota Bene: In this rule, "application" means the code that issues SQL statements. If the "application" is an application server and if the content resides on the same physical machine as the application server, then SQLite might still be appropriate even though the end user is another network hop away.
Many concurrent writers? → choose client/server

If many threads and/or processes need to write the database at the same instant (and they cannot queue up and take turns) then it is best to select a database engine that supports that capability, which always means a client/server database engine.

SQLite only supports one writer at a time per database file. But in most cases, a write transaction only takes milliseconds and so multiple writers can simply take turns. SQLite will handle more write concurrency that many people suspect. Nevertheless, client/server database systems, because they have a long-running server process at hand to coordinate access, can usually handle far more write concurrency than SQLite ever will.
Big data? → choose client/server

If your data will grow to a size that you are uncomfortable or unable to fit into a single disk file, then you should select a solution other than SQLite. SQLite supports databases up to 140 terabytes in size, assuming you can find a disk drive and filesystem that will support 140-terabyte files. Even so, when the size of the content looks like it might creep into the terabyte range, it would be good to consider a centralized client/server database.
Otherwise → choose SQLite!

For device-local storage with low writer concurrency and less than a terabyte of content, SQLite is almost always a better solution. SQLite is fast and reliable and it requires no configuration or maintenance. It keeps thing simple. SQLite "just works".


Does RCSI use less or same space than SNAPSHOT? Why?

Read committed snapshot isolation (statement-level snapshot isolation) potentially uses less storage than snapshot isolation (transaction-level snapshot isolation) because usually the row versions aren't required for as long a period of time after they've been generated.

For transaction-level snapshot isolation, the versions have to stick around for the entire duration of a transaction, which could be quite a long time (or quite a long time, as seen here). Of course, statements can run for a long time, too, but it's usually the case that a transaction will take longer.

To emphasise the point: A transaction running under snapshot isolation is guaranteed to see committed data as it existed at the start of the transaction. This means row versions have to be maintained until the whole transaction completes. Where the same transaction is running under RCSI, row versions can potentially be released after each statement. Overall, snapshot isolation tends to result in a larger version store size than RCSI on the same workload.

The same versions are generated with either or both settings enabled. I believe Kimberly was referring to storage impact in the sense of write rate, not storage used, so the two statements you mention aren't contradictory; just a little unclear or unspecific.


Purpose of owning a schema in SQL Server

An owner of a database object has full rights on it automatically and unrevokably. But of course you can give an user all rights to a schema without making him the owner.


mySQL - Cannot add Foreign Key Constraint

I think you've got the FK the wrong way round, and really want:

ALTER TABLE  calltarget ADD
CONSTRAINT `fk_importctbatch_calltarget_idImportCTBatch`
FOREIGN KEY (`idImportCTBatch`)
REFERENCES `importctbatch` (`idImportCTBatch`)
ON DELETE RESTRICT;


How does "Be made using the login's current security context" pass the users password to remote server

Whilst liasing with Microsoft on a different issue (paid support request) i happened to ask them about this & they confirmed the unhashed password is passed to the remote server but the mechanism in which the SQL Engine does this is "hidden and cannot be captured" - but suffice to say, the login is not done using the hash.

Here is there full response:

  How does the SQL Server provide the correct password (when dealing with non-windows credentials) to the other server when all the server has is a Hash?

  Answer: If connected to the local server using SQL Server
  Authentication, login name and password will be used to connect to the
  remote server. In this case a login with the exact same name and
  password must exist on the remote server.

  Be made using the login's current security context:

  Specify that a connection will be made using the current security context of the
   login for logins not defined in the list. If connected to the local
   server using Windows Authentication, your windows credentials will be
   used to connect to the remote server. If connected to the local server
   using SQL Server Authentication, login name and password will be used
   to connect to the remote server. In this case a login with the exact
   same name and password must exist on the remote server.

  How does the SQL Server provide the correct password (when dealing with non-windows credentials) to the other server when all the server has is a Hash?

  Answer: Login name and password is passed to the remote server, this
  mechanism/task is handled by the SQL Engine which is hidden and cannot
  be captured.

  If SQL only has a hash available - can it logon to the remote server
   using the hash? Or is the original (un-hashed) password kept in the
   user’s session / memory for the entire duration of their connection
   which SQL can then retrieve and pass to the remote server for login?
   If the server can login with a hash instead of the password – can this
   be done in normal logins or is it purely an internal feature of linked
   servers?

  Answer: HASH cannot be used to login to the SQL server, SQL Engine is
  responsible for handling login process and we don’t have much
  documentation around this.


I'd say you should have DATETIME fields on the ticket_expected and ticket_status so that it's possible to see when the status of the ticket changed. This would allow you to track changes from start to end.

I would also recommend that use the same name for columns that are used to join tables. So the ticket table should have a ticket_id column that matches the column of the same name in the ticket_expected table. This is standard practice as it avoids a lot of confusion that can happen when writing and bug tracing queries.

Those two points are my only comment as the general design seems to meet your requirements.


SSAS cube fact and dimension relationship

What you are seeing is a typical example of a missing dimension relationship.

Yes it has something to do with the relations in your dsv but easy to solve with dimension relationships.

If you add a dimension to a measure group/cube/fact table the dimension relations are automatically generated based on the relations in the data source view, if a relation isn't present a dimension relation has to be created manually.

So in your example you need to go to the cube where the measure group is residing and select dimension relationships and define the relation between your dimension key and the fact table.

See defining dimension relationships for details on how to do exactly that.

It's actually fairly obvious why SSAS would behave like that. If there is no relation between axis and figures, there is no way a slicer can have impact on the figures.

If you are using a Tabular model, unless it lets you add relations in both directions I fear you'll be out of luck and need to design your data warehouse better using actual fact tables and dimension tables.


Get a specific row from a TSQL common table expression

There are a few ways to do this.  If there is a 1-2-1 relationship between the parents and widgets ( ie a parent only has one widget and vice versa ) you can just add a column in the CTE to count how many rows have passed through.  See the xlevel example below.

If that doesn't work for you ( and I would probably expect a parent to have multiple widgets ) then you could just throw all the results into a temp table using the IDENTITY function, then grab the third row.  Of course you need some kind of meaningful order for this to work, but see the #tmp table version below.

You could also use ROW_NUMBER(), available in SQL Server 2008 to add an arbitrary row number to the resultset.  Again you need some kind of order to give it meaning.  See the ROW_NUMBER example below:

;WITH GroupList(xlevel, WgID,WgParentWgID) AS (
SELECT 1 xlevel, WgID,WgParentWgID
FROM tblplWorkGroup WITH (NOLOCK) WHERE WgID = 283 AND WgActive=1

UNION ALL

SELECT xlevel + 1, B.WgID,B.WgParentWgID
FROM GroupList A
    INNER JOIN tblplWorkGroup B WITH (NOLOCK) ON A.WgParentWgID = B.WgID
WHERE B.WgActive=1
)
SELECT *
FROM GroupList
WHERE xlevel = 3
GO

IF OBJECT_ID('tempdb..#tmp') IS NOT NULL DROP TABLE #tmp

;WITH GroupList(WgID,WgParentWgID) AS (
SELECT WgID,WgParentWgID
FROM tblplWorkGroup WITH (NOLOCK) WHERE WgID = 283 AND WgActive=1

UNION ALL

SELECT B.WgID,B.WgParentWgID
FROM GroupList A
    INNER JOIN tblplWorkGroup B WITH (NOLOCK) ON A.WgParentWgID = B.WgID
WHERE B.WgActive=1
)
SELECT IDENTITY( INT, 1, 1 ) rowId, *
INTO #tmp
FROM GroupList
ORDER BY WgParentWgID, WgID
GO

SELECT *
FROM #tmp
WHERE rowId = 3
GO

;WITH GroupList(WgID,WgParentWgID) AS (
SELECT WgID,WgParentWgID
FROM tblplWorkGroup WITH (NOLOCK) WHERE WgID = 283 AND WgActive=1

UNION ALL

SELECT B.WgID,B.WgParentWgID
FROM GroupList A
    INNER JOIN tblplWorkGroup B WITH (NOLOCK) ON A.WgParentWgID = B.WgID
WHERE B.WgActive=1
)
SELECT *
FROM
    (
    SELECT ROW_NUMBER() OVER( ORDER BY WgParentWgID, WgID ) rowId, *
    FROM GroupList
    ) x
WHERE rowId = 3
ORDER BY rowId
GO

HTH


I wouldn't worry about setting the time component.  Just write your query like this instead:

WHERE SomeColumn >= @StartDate
  AND SomeColumn < DATEADD(Day, 1, @EndDate)

You have @EndDate = Today() which by default does not have a time component (or rather it's already 00:00:00).  Adding a whole day to that gets you tomorrow at midnight.  So the query above asks for any data on or after @StartDate but no later than tomorrow at midnight.

If you're querying a SQL 2K8+ instance you can further do this to be sure you're not erroneously using a time component:

WHERE SomeColumn >= CONVERT(Date, @StartDate)
  AND SomeColumn < CONVERT(Date, DATEADD(Day, 1, @EndDate))


Decode Base64 String Natively in SQL Server

Figured it out:

SELECT
    CONVERT
    (
        VARCHAR(MAX),
        CAST('' AS XML).value('xs:base64Binary(sql:column("BASE64_COLUMN"))', 'VARBINARY(MAX)')
    ) AS RESULT
FROM
    (
        SELECT 'cm9sZToxIHByb2R1Y2VyOjEyIHRpbWVzdGFtcDoxNDY4NjQwMjIyNTcxMDAwIGxhdGxuZ3tsYXRpdHVkZV9lNzo0MTY5ODkzOTQgbG9uZ2l0dWRlX2U3Oi03Mzg5NjYyMTB9IHJhZGl1czoxOTc2NA==' AS BASE64_COLUMN
    ) A

Output:

role:1 producer:12 timestamp:1468640222571000 latlng{latitude_e7:416989394 longitude_e7:-738966210} radius:19764

Just swap out BASE64_COL_NAME for your column name, or you can replace sql:column("BASE64_COLUMN") with sql:variable("@base64variable") if you want to use a declared variable e.g. if you are making a function or something.

It makes use of an XSL transform using built-in XML functionality (since SQL Server 2005)


KNN search two arrays with 2000 elements

You have one option, you can recompile the cube extension to use more than 100 dimensions

KNN Search is done with PostGIS using the <-> operator on two points. You can find out how to do that in the docs.. It doesn't operate in Nd-space. PostGIS isn't an abstract library for math. It's for real world geospatial problems. KNN in 1d, and 4d+ is not a real world problem, or at least not in the domain that PostGIS would be interested in.

I would suggest checking out my other answer here,

Searching in n-dimensional space


Shared locks taken under Read Committed Snapshot Isolation (RCSI)

How can I be seeing shared locks? Is it because of foreign keys?

Yes. SQL Server reverts to the locking implementation of the read committed isolation level when accessing a table for the purpose of validating foreign key constraints. This is required for correctness, and cannot be disabled.

The behaviour applies only to data-modification statements. Shared locks are only taken when checking the foreign-key related data. Other data access in the same execution plan can continue to use row versioning.

If SQL Server did not do this, data-modification statements under RCSI could end up violating the foreign key constraint because the integrity check used out-of-date (versioned) data.

Unfortunately, there is currently no supported way to see this change in locking behaviour in an execution plan. It is possible to see the internal locking hints when trace flag 8607 is active.


PostgreSQL json array of objects, default 0 values for properties if not found

IMHO You can't get this outcome with the current data.
Given this sample data:
create table product (product_id int, product_name text);
create table stock (product_id int, location text, quantity int);

insert into product values (1, 'Product 1');
insert into stock values (1, 'a', 10), (1, 'b', 20);

You can't get locations c and d because there is not such information in your data:
select
    dk.product_id,
    dk.product_name,
    dr.location,
    dr.quantity
from
    product dk
left join
    stock dr
    on dr.product_id = dk.product_id;

product_id | product_name | location | quantity
---------: | :----------- | :------- | -------:
         1 | Product 1    | a        |       10
         1 | Product 1    | b        |       20

You should add a new (or existing) table of locations, or a table valued function, just to be able to get the required data.
create table location (id text);
insert into location values ('a'),('b'),('c'),('d'),('e'),('f');

Now you can use a sub-query to get this information:
select
    p.product_id,
    p.product_name,
    (
    select
        json_agg(
            json_build_object('location', l.id,
                              'quantity', coalesce(s.quantity, 0)))::jsonb stock
    from
        location l
    left join
        stock s
        on s.location = l.id
        and s.product_id = p.product_id
    where
        l.id in ('a', 'b', 'c', 'd')
    ) stock
from
    product p

product_id | product_name | stock
---------: | :----------- | :-----------------------------------------------------------------------------------------------------------------------------------------
         1 | Product 1    | [{"location": "a", "quantity": 10}, {"location": "b", "quantity": 20}, {"location": "c", "quantity": 0}, {"location": "d", "quantity": 0}]

db<>fiddle here
Update
You can use sites table for that purpose in this way:
select
    dk.product_id,
    dk.product_name,
    (
    select
        json_agg(
            json_build_object('location', s.location,
                              'quantity', coalesce(dr.quantity, 0)))::jsonb stock
    from
        sites s
    left join
        stock dr
        on dr.location = s.location
        and dr.product_id = dk.product_id
    where
        s.site_id = 1
    ) stock
from
    product dk;

db<>fiddle here


Is it possible for SQL Server to miss rows on a select * where PK = "value" query

nvarchar(8) PK NOT NULL

  ... their PKs return null ...

This cannot happen, under any condition. Therefore I must conclude that your 'simplified' repro has simplified things beyond the limit of what you understand. Please post the exact table definition (including all indexes) and the exact query you issue.

  Null is what comes back from the linq .FirstOrDefault() query in C#.

OK, that changes things. So, if we take your word for it, you see committed rows being skipped. Under dirty read isolation (or NOLOCK hint) this is a common phenomenon, see Previously committed rows might be missed if NOLOCK hint is used. Under higher isolation levels is rather uncommon to encounter missing rows, but not impossible, as Brent and Martin indicate in their comments.

So what are you to do? After all if you wanted to program against an eventually-consistent model you'd choose something cheaper and maybe faster. First ensure you are not under a dirty read isolation level. Anomalies at higher levels are rather uncommon, so lets make sure you're not in the common bucket. If you are positively satisfied that the read occurs under a higher than dirty reads isolation level, then you need to isolate the phenomenon. Again, post here the exact table definition and the exact query you run.

Read Understanding how SQL Server executes a query.


rt_raster_to_gdal: Could not load the output GDAL driver

It seems likely that you're not using PostGIS 2.2.

Pay special attention to postgis.enable_outdb_rasters.

Taken from my answer on GIS.SE

As explained in the PostGIS blog,

  This has been an issue since our release of 2.0.6/2.13 PostGIS security patches that by default disabled all raster drivers and out-of-db raster storage. Raster is much less useful if you don’t have any raster drivers enabled, so you need to renable them or the selective ones you need.

The interface you're using is referred to as Grand Unified Custom Variables (GUC). It's a PostGIS 2.2 thing.

If using PostGIS 2.1, you do not GUC, you must set them on the command line,

  As of PostGIS 2.1.3, out-of-db rasters and all raster drivers are disabled by default. In order to re-enable these, you need to set the following environment variables POSTGIS_GDAL_ENABLED_DRIVERS and POSTGIS_ENABLE_OUTDB_RASTERS in the server environment.


How to disable SSMS table designer timeout?

"...i'm changing a column in a 130 GB table from text to nvarchar(max)..."

Hi If you change a column and you expect that it can take considerable amount of time - I would recommend writing a Query.

Depending on you situation it could be as simple as "ALTER COLUMN" or as complex as "Creating temp table, coping all data, dropping table, creating table with new structure, copying data from temp table".

In any situation, having a script will allow you to run it without any timeout probably in you maintenance window (if you have one).

Regards,


You could do something like this with row_number(). Performance probably is going to depend on how many active articles are present each day.

This codes creates a seq based on the sort you provide and then finds the sequence of the last Id value, which you can use to grab the top (x) rows.

declare @LastId INT,
        @PageSize INT

set @LastId = 187322
set @PageSize = 2

;with cte
as(
SELECT *, seq = ROW_NUMBER() over(ORDER BY important DESC, release_date DESC, edit_date DESC)
FROM @demo d
WHERE
    status = 1
    AND release_date <= GETUTCDATE()
)
select TOP (@PageSize) *
from cte t1
where t1.seq > (select seq from cte t2 where id = @LastId)
Order by t1.seq


The tables appear as entries in pg_catalog.pg_class  that have relkind='r'
(see the documentation).

table name: pg_class.relname

schema name: pg_namespace.nspname. Join pg_class.relnamespace to pg_namespace.oid to follow the association between the table and its schema.

table size: see pg_relation_size(pg_class.oid)

row count: pg_class.reltuples gives an estimate. For an exact number, run select count(*) from the table.

created date: the creation date is not recorded by postgres.

Some of these are also available through views in information_schema if you prefer. The structures of the views in this schema are dictated by the SQL standard, whereas the pg_catalog tables are postgres-specific.
Example of query
Starting from the query provided as a comment:
  select nspname as schema, relname as tablename,
  reltuples as rowcounts
  from pg_class c JOIN pg_catalog.pg_namespace n
   ON n.oid = c.relnamespace where relkind='r'
   and relname like '%_drop%'
   order by nspname, reltuples desc

remarks:

The underscore character is a wildcard for the LIKE operator (it matches any character), so it needs to be quoted to be interpreted literally. By default, the quote character is backslash.

pg_relation_size() as a function call can be introduced directly in the select list.

Amended query:
  select
    nspname as schema,
    relname as tablename,
    reltuples as rowcounts,
    pg_relation_size(c.oid) AS tablesize
  from pg_class c JOIN pg_catalog.pg_namespace n
   ON n.oid = c.relnamespace where relkind='r'
   and relname like '%\_drop%'
   order by nspname, reltuples desc


Full text search using words stored in another table

To reiterate, you currently have a table with a list of words you want to make sure your users are not searching Table_A with.
You have successfully been able to query Table_A with Table_B which contains your list of bad words.
Your end goal going from the first point, to the second point, is that you want to know if they searched for those bad words, what would be returned. What bad word correlates to a return of a record in Table_A.

What you need to do to resolve point 3 is query sys.dm_fts_parser. There is an excellent Example by Jefferson Elias on SQL Shack. I'll post the relevant bit in case it is removed or broken by the internet later:

  How to check the results of a Full-Text parsing

  There are two ways to check how Full-Text feature parses a given text
  depending on the source of the text.

  Source of the text is a String

  If you want to check fast what keywords you would get for a particular
  string, you might want to use sys.dm_fts_parser built-in function.

  Here is an example of call to that function.

  The first parameter is the string that has to be parsed. The second
  parameter is the language identifier. Here, it’s set to 0, which means
  it’s neutral. The hhird parameter is the identifier of the stoplist.
  Here no stoplist is used. The last parameter tells this function
  whether to be sensitive or not to accents. Here, we asked for
  insensitivity. In other words, this function will take the information
  you would provide when creating a Full-Text Index.

 select * from sys.dm_fts_parser(
    '" dsolkjfdskljfsd dfsd-MMM-236.127 dojfdslfkjds"',
    0,
    NULL,
    0
) ;

  If a table is already created with a Full-Text index, we would use
  another dynamic management function (DMF) called
  sys.dm_fts_index_keywords which takes as a parameter:

  The database identifier in which it should look at The object
  identifier in that database It returns a dataset with a hexadecimal
  representation of the keyword, its corresponding form in the plain
  text, the identifier of the column in which the keyword has been found
  and finally the number of documents where this keyword can be found.

  You will find below a T-SQL query to get back keywords found by
  Full-Text feature in our dbo.DM_OBJECT_FILE table so as its results
  set.

select *
From sys.dm_fts_index_keywords(DB_ID(),OBJECT_ID('dbo.DM_OBJECT_FILE'))

This method should show you what would be returned based on your criteria. To help with point 1, you may want to look into Stopwords, Stoplists, and the Thesaurus features to help change the words from Table_B into usable words. Alternatively, you may need to implement triggers to keep the terms from being searched.

I imagine this code would work:

Declare @AllWords nvarchar(4000) = '';
Select @AllWords = @AllWords + Case When @AllWords = '' Then Word Else ' OR ' + Word End
From Table_B;
Set @AllWords = '''' + @AllWords + '''';
select * from sys.dm_fts_parser(
    @AllWords,
    0,
    NULL,
    0
) ;


Simulating having multiple Control Flows in a SSIS package

Don't use groups, use Sequence Containers. That way, you can have the ones you want to run in parallel in the same container, and then have a subsequent container kicked off with additional data flows.

You can't have multiple Control Flows, but you can have multiple packages, and you can have a ton of containers to give you more control over the flow.


[0-9] is not some type of regular expression defined to just match digits.

Any range in a LIKE pattern matches characters between the start and end character according to collation sort order.

SELECT CodePoint,
       Symbol,
       RANK() OVER (ORDER BY Symbol COLLATE Latin1_General_CI_AS) AS Rnk
FROM   #CodePage
WHERE  Symbol LIKE '[0-9]' COLLATE Latin1_General_CI_AS
ORDER  BY Symbol COLLATE Latin1_General_CI_AS

Returns

CodePoint            Symbol Rnk
-------------------- ------ --------------------
48                   0      1
188                  ¼      2
189                  ½      3
190                  ¾      4
185                  ¹      5
49                   1      5
50                   2      7
178                  ²      7
179                  ³      9
51                   3      9
52                   4      11
53                   5      12
54                   6      13
55                   7      14
56                   8      15
57                   9      16

So you get these results because under your default collation these characters sort after 0 but before 9.

It looks as though the collation is defined to actually sort them in mathematical order with the fractions in the correct order between 0 and 1.

You could also use a set rather than a range. To avoid 2 matching ² you would need a CS collation

SELECT CodePoint, Symbol
FROM #CodePage
WHERE Symbol LIKE '[0123456789]' COLLATE Latin1_General_CS_AS


Must be superuser after GRANT SELECT ON pg_settings(PostgreSQL)

I am not entirely sure, but I think you can't get around the "superuser" check for those settings.

With Postgres 10 you could use the new default role pg_read_all_settings but if upgrading is not an option now, the easiest way would be to create a function that runs with superuser privileges.

Create the following function as the superuser:

create function public.list_settings()
  returns setof pg_catalog.pg_settings
as
$$
  select *
  from pg_catalog.pg_settings;
$$
language sql
security definer;

The  security definer means that the function always runs with the privileges of the user who created the function (the superuser).

Then grant execute on that function to your user:

grant execute on function public.list_settings() to the_user;

The the regular user can do:

select *
from public.list_settings()
where name = 'hba_file';

Of course you could create the function in a different schema, e.g. admin


Deferred Name Resolution for Linked Servers

This is most likely not possible. According to the MSDN page for Deferred Name Resolution and Compilation:

  Note
  Deferred name resolution can only be used when you reference nonexistent table objects. All other objects must exist at the time the stored procedure is created. ...

And in fact, over a Linked Server it does not seem that Deferred Name Resolution works at all. I tried (in both SQL Server 2008 R2 RTM and 2012 SP3) with both a Synonym and a direct Linked Server reference to a non-existent Table, and both failed. So it seems that Linked Servers do not support Deferred Name Resolution at all.

However, one thing you might be able to do is to:

Create a single "template" database on the remote server (the DB should have the tables and any other referenced objects created as well)
Create all of the synonyms to point to the template DB
Create the Stored Procedures
DROP and reCREATE the synonyms when the "real" database has been created


Database schema for job offers with some common attributes

Considering "nice to see skills" and "required skills" as wholly separate attributes is a mistake. They are all just skills. Each Job has Skills then those JobSkills are either Required or not (yes/no). In particular, a skill might be required for one job and optional for another. Further, I'd suggest that a language is just another skill.
Mostly, it would seem like this would fit into a schema that looks something like this, where several of the things you have in separate tables become attributes on the Job-Skill cross reference.

Skill

SkillId
Name
Type (technical, language, etc)

Job

JobId
Name
Description
Overall level
Source website

JobSkill

JobId
SkillId
IsRequired
SkillLevel


Simplest way to edit single entry in XML column?

If you just need to replace the whole piece of XML in once, then you can do a normal UPDATE, eg something like this:

UPDATE yourTable
SET yourXML = '<yourNewValidXML/>'
WHERE rowId = 1

If you need to edit individual attributes or elements then you can use the .modify method of the XML data-type in SQL Server to update single values.  Here's a simple example to get you started:

DECLARE @t TABLE ( rowId INT IDENTITY PRIMARY KEY, yourXML XML )

INSERT INTO @t ( yourXML )
VALUES ( '<Users>
    <User Name="Bob"></User>
    <User Name="Mikhail"></User>
    <User Name="John"></User>
    <User Name="Sue"></User>
</Users>' )

SELECT 'before' s, DATALENGTH(yourXML) dl, yourXML
FROM @t
WHERE rowId = 1

-- Update one attribute
UPDATE @t
SET yourXML.modify('replace value of (Users/User/@Name[.="Bob"])[1] with "wBob"')
WHERE rowId = 1

SELECT 'after' s, DATALENGTH(yourXML) dl, yourXML
FROM @t
WHERE rowId = 1

If you need more help, please post a small sample of your XML and your expected results.


How can we clean up/backup of .DBF files in Oradata folder in ORACLE on windows

In general Oracle doesn't make it easy to reclaim disk space, other than through cleaning up log and trace files. If you haven't already, consider rotating the listener and alert logs, or removing old trace files from the ADR subdirectories. If you have much free space within your data files, also consider reorganizing your tablespaces (online reorg of tables, rebuiling indexes) to defragment them, then attempting to shrink your data files.
These articles - Automatic Diagnostic Repository (ADR) in Oracle Database 11g Release 1 (ADRCI) and Reclaiming Unused Space in Datafiles - will help you get started.


How to take mysqldump with Generated column?

This is a problem when using mysqldump from MariaDB with virtual generated columns.
MariaDB's mysqldump apparently dumps the generated values, but MySQL only accepts DEFAULT as value for a virtual generated column.
It seems like you need to use MySQL's mysqldump to correctly dump and restore virtual generated columns on a MySQL server.


Why this happens has already been answered by @PaulWhite in the SO question: How does this CASE expression reach the ELSE clause?

To solve it, you should calculate the ABS(CHECKSUM(NEWID()))%10 +1 outside/before the INSERT statement so it is calculated once. Something like:

DECLARE @i INT = 1 ;
DECLARE @rand INT ;
 WHILE @i <= 10
   BEGIN
    SET @rand = ABS(CHECKSUM(NEWID()))%10 +1 ;
    INSERT INTO TestEmployeeCountry
    VALUES ( SUBSTRING('ABCDEFGHIJKLMNOP', @i, 1),
        CASE @rand
            WHEN 1 THEN 'USA'
            WHEN 2 THEN 'CANADA'
            WHEN 3 THEN 'MEXICO'
            WHEN 4 THEN 'UK'
            WHEN 5 THEN 'FRANCE'
            WHEN 6 THEN 'SPAIN'
            WHEN 7 THEN 'RUSSIA'
            WHEN 8 THEN 'CHINA'
            WHEN 9 THEN 'JAPAN'
            WHEN 10 THEN 'INDIA'
        END) ;
    SET @i = @i + 1 ;
   END ;

Also notice that with your code, the 10 countries will not be placed in the table with equal probability! The first country (USA) will have 10% chance, the second will have 9% ( (100%-10%)*10% ), the third 8.1%, ((100%-19%)*10%), etc. That leaves a not so small chance (around 1/e) that none of the 10 is chosen and the CASE expression goes to the default ELSE NULL and you get the error. (You can check the probabilities if you allow nulls in the column and run the SQLfiddle script.)

According to the above, another way to solve it would be to change the expressions to comply with how SQL-Server executes the CASE and all 10 cases have the same probability:

    CASE 0
        WHEN ABS(CHECKSUM(NEWID()))%10 THEN 'USA'
        WHEN ABS(CHECKSUM(NEWID()))%9 THEN 'CANADA'
        WHEN ABS(CHECKSUM(NEWID()))%8 THEN 'MEXICO'
        WHEN ABS(CHECKSUM(NEWID()))%7 THEN 'UK'
        WHEN ABS(CHECKSUM(NEWID()))%6 THEN 'FRANCE'
        WHEN ABS(CHECKSUM(NEWID()))%5 THEN 'SPAIN'
        WHEN ABS(CHECKSUM(NEWID()))%4 THEN 'RUSSIA'
        WHEN ABS(CHECKSUM(NEWID()))%3 THEN 'CHINA'
        WHEN ABS(CHECKSUM(NEWID()))%2 THEN 'JAPAN'
        ELSE 'INDIA'
    END


How to read the actual value of @P0 in SQL server profiler

Use rpc_completed to get parameter values. Also, use extended events instead of profiler if you're on SQL 2012 or newer.


Using the second method will result in less locking, transaction log usage and Tempdb resources, because SQL Server doesn't have to perform all of the delete operations in one transaction.

However, instead of the "while exists" statement, you can use a variable that will get the value of @@rowcount after the delete. This can help a little more with performance.


Reusing covering index for fields in FROM, from another query's index

The index IS covering for the second query. However, it is not USEFUL to support the seek. Because neither bar_id nor date_sent is leading the index, the optimiser cannot seek for it.

What you have achieved with the covering index is to make table scan faster for the second query. But you have not supported a better seek strategy.

This covering index would help both queries:

ALTER TABLE foo ADD INDEX (date_sent, user_id, bar_id)

Or if you prioritise the second query:

ALTER TABLE foo ADD INDEX (date_sent, bar_id, user_id)


Mysql used key changes between two consecutive EXPLAINs

It sounds like the table is on the cusp of a cutover for the query optimizer between the estimated total rows and estimated rows being looked up.

The use/ignore index syntax exists for situations like this.

The pitfall to this approach is when the table dynamics change in the future such that it's really not the best choice.


SPIDs stuck indefinitely in suspended state

If you've got SPIDs sitting with the wait type of ASYNC_NETWORK_IO it's either row by row processing within the application, the application is calling huge recordsets which is can't process fast enough, or there's a network capacity problem.


How to identify a client application whose host_process_id points to sqlserver.exe

Since the sessions are using user-created logins and are accessing application tables in user databases, and since the host_process_id aligns with the sqlserver.exe process, it sounds like there is a SQLCLR assembly running code.

Use the following query to look for SQLCLR Assemblies in all databases:

DECLARE @cmd nvarchar(max);
SET @cmd = '';
SELECT @cmd = @cmd + CASE WHEN @cmd = '' THEN '' ELSE '
UNION ALL
' END + 'SELECT DatabaseName = ''' + QUOTENAME(d.name) + ''', a.*
FROM sys.assemblies a
WHERE a.name <> ''Microsoft.SqlServer.Types''
'
FROM sys.databases d
ORDER BY d.name;
SET @cmd = @cmd + ';'
EXEC sys.sp_executesql @cmd;


Can a large number of schemas be an issue?

Since SQL Server can handle up to 2,147,483,647 objects in a database, I suspect that having thousands of schemas will only incrementally cause slowness. (Just like anything else, the more different things you have the more processing you need.)

I imagine that your biggest problem will be remaining organized with thousands of schemas to manage.  Depending on what you are trying to accomplish, there may be other approaches.  For example, row and cell level security.

You might have seen this before, but here is a 2005 paper on implementing row and cell security.  http://technet.microsoft.com/library/Cc966395

I have done row/cell security without implementing everything in this paper.  So, if that looks better to you, then be judicious in choosing the level of complexity.


Effect of Reorganizing and Rebuilding an index

The MSDN page says this about reorganizing and rebuilding indexes:

The SQL Server Database Engine automatically maintains indexes whenever insert, update, or delete operations are made to the underlying data. Over time these modifications can cause the information in the index to become scattered in the database (fragmented). Fragmentation exists when indexes have pages in which the logical ordering, based on the key value, does not match the physical ordering inside the data file. Heavily fragmented indexes can degrade query performance and cause your application to respond slowly.
You can remedy index fragmentation by reorganizing or rebuilding an index. For partitioned indexes built on a partition scheme, you can use either of these methods on a complete index or a single partition of an index. Rebuilding an index drops and re-creates the index. This removes fragmentation, reclaims disk space by compacting the pages based on the specified or existing fill factor setting, and reorders the index rows in contiguous pages. When ALL is specified, all indexes on the table are dropped and rebuilt in a single transaction. Reorganizing an index uses minimal system resources. It defragments the leaf level of clustered and nonclustered indexes on tables and views by physically reordering the leaf-level pages to match the logical, left to right, order of the leaf nodes. Reorganizing also compacts the index pages. Compaction is based on the existing fill factor value.

Doe that cover your question?


TLDR; Copy listener.ora and tnsnames.ora from app/sgupta/product/11.2.0/dbhome_1/NETWORK/ADMIN
to app/sgupta/product/11.2.0/dbclient_1/network/admin

I had the tnsnames.ora and listern.ora files in C:/app/sgupta/product/11.2.0/dbhome_1/NETWORK/ADMIN.

When I used tnsping on the command line, i was getting following error message:

tnsping PM

TNS Ping Utility for 32-bit Windows: Version 11.2.0.1.0 - Product
016 15:10:08

Copyright (c) 1997, 2010, Oracle.  All rights reserved.

Used parameter files:
C:\app\sgupta\product\11.2.0\client_1\network\admin\sqlnet.ora

TNS-03505: Failed to resolve name

I noticed how it was looking in the C:\app\sgupta\product\11.2.0\client_1\network\admin folder. And that the sqlnet.ora looked like following:

# sqlnet.ora Network Configuration File: C:\app\sgupta\product\11.2.0\client_1\network\admin\sqlnet.ora
# Generated by Oracle configuration tools.

# This file is actually generated by netca. But if customers choose to
# install "Software Only", this file wont exist and without the native
# authentication, they will not be able to connect to the database on NT.

SQLNET.AUTHENTICATION_SERVICES= (NTS)

NAMES.DIRECTORY_PATH= (TNSNAMES, EZCONNECT)

So I figured sqlnet.ora is expecting a TNSNAMES file in the same folder.

So I copied listener.ora and tnsnames.ora from app/sgupta/product/11.2.0/dbhome_1/NETWORK/ADMIN
to app/sgupta/product/11.2.0/dbclient_1/network/admin.

After that tnsping worked. And sqlplus also connected.

tnsping PM

TNS Ping Utility for 32-bit Windows: Version 11.2.0.1.0 - Production on 17-FEB-2
016 15:14:40

Copyright (c) 1997, 2010, Oracle.  All rights reserved.

Used parameter files:
C:\app\sgupta\product\11.2.0\client_1\network\admin\sqlnet.ora

Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = bentreki
-E6330.callidussoftware.com)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED)
(SERVICE_NAME = Pm)))
OK (20 msec)

C:\Users\sgupta>sqlplus

SQL*Plus: Release 11.2.0.1.0 Production on Wed Feb 17 15:15:01 2016

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

Enter user-name: SYSTEM@PM
Enter password:

Connected to:
Oracle Database 11g Release 11.2.0.1.0 - 64bit Production


Import data into AWS RDS sql server through SQL Server management studio

There are stored procedures that you can do on Rds

EXEC rds_backup_database 'DBName','.bak file path','','0'

EXEC rds_restore_database 'DBName','.bak file path',''

File path use S3.

Just to add more context for the op.

The RDS instance doesn't give you access to the underlying file system like an ec2 would, hence you have to use s3 buckets.

See here for full details:-
http://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/SQLServer.Procedural.Importing.html


No.

At least in my testing both of them have identical execution plans and identical locking behaviour. Both place an IX lock on tableA and a schema stability lock on TableB straight away and then follow the same behaviour in doing the clustered index scan on TableA taking IU locks on the page, U locks on the key, then in the event of a match converting the page lock to IX then the key lock to X prior to deleting the row. This is exactly the same pattern as if the NOLOCK hint on tableA is removed entirely so it is pointless in this case.

My original results had some differences between the two queries in that the Join version has a series of apparently aquiring and immediately releasing schema stability locks on TableA that didn't show up in the EXISTS version. That seems to be related to whether the "Include Actual Execution Plan" option is enabled in SSMS. Today if I have this option enabled this series appears in the locking info for both of them so not sure why originally it only appeared in one. Maybe some timing issue.

You can see this locking information using TF1200 as below (with "Include Actual Execution Plan" option disabled).

Code

SET NOCOUNT ON;

CREATE TABLE tableA(
colA INT,
colB INT,
PRIMARY KEY(colA,colB));

CREATE TABLE tableB(
colA INT,
colB INT,
PRIMARY KEY(colA,colB));

SELECT OBJECT_ID('tableA') AS tableA,
       OBJECT_ID('tableB') AS tableB;

INSERT INTO tableA VALUES (0,0),(1,1),(2,2),(3,3);
INSERT INTO tableB VALUES (1,1),(2,2),(4,4),(5,5),(6,6),(7,7);

SELECT *, %%lockres%%  AS lockres
FROM tableA

DECLARE @JoinSQLNoLockTableA nvarchar(max) = N'
DELETE tableA
FROM tableA a WITH(NOLOCK)
JOIN tableB b WITH(NOLOCK)
  ON
      b.colA = a.colA
  AND b.colB = a.colB;'

DECLARE @ExistsSQL nvarchar(max) = N'
DELETE FROM tableA
WHERE EXISTS
(
  SELECT *
  FROM tableB b WITH(NOLOCK)
  where
      b.colA = tableA.colA
  AND b.colB = tableA.colB
);'

DECLARE @JoinSQLWithoutNoLockTableA nvarchar(max) = REPLACE(@JoinSQLNoLockTableA,'tableA a WITH(NOLOCK)', 'tableA a')

/*Run the commands first with the trace flag off so the locking
info is less full of irrelevant stuff about plan compilation */
EXEC (@JoinSQLNoLockTableA);
INSERT INTO tableA VALUES (1,1),(2,2);
EXEC (@ExistsSQL);
INSERT INTO tableA VALUES (1,1),(2,2);
EXEC (@JoinSQLWithoutNoLockTableA);
INSERT INTO tableA VALUES (1,1),(2,2);

/*Run with TF1200 on*/

DBCC TRACEON(-1,3604,1200) WITH NO_INFOMSGS;
PRINT '@JoinSQLNoLockTableA - Start';
EXEC (@JoinSQLNoLockTableA);
PRINT '@JoinSQLNoLockTableA - End';
DBCC TRACEOFF(-1,3604,1200) WITH NO_INFOMSGS;

INSERT INTO tableA VALUES (1,1),(2,2);

DBCC TRACEON(-1,3604,1200) WITH NO_INFOMSGS;
PRINT '@ExistsSQL - Start';
EXEC (@ExistsSQL);
PRINT '@ExistsSQL - End';
DBCC TRACEOFF(-1,3604,1200) WITH NO_INFOMSGS;

INSERT INTO tableA VALUES (1,1),(2,2);

DBCC TRACEON(-1,3604,1200) WITH NO_INFOMSGS;
PRINT '@JoinSQLWithoutNoLockTableA - Start';
EXEC (@JoinSQLWithoutNoLockTableA);
PRINT '@JoinSQLWithoutNoLockTableA - End';
DBCC TRACEOFF(-1,3604,1200) WITH NO_INFOMSGS;

DROP TABLE tableA,
           tableB;

ObjectIds

tableA      tableB
----------- -----------
308196148   372196376

LockRes

colA        colB        lockres
----------- ----------- ------------------------------
0           0           (00009620dd9a)
1           1           (02006d47cbee)
2           2           (040060eff172)
3           3           (06009b88e706)

Output (All three queries)

Process 52 acquiring Sch-S lock on OBJECT: 23:372196376:0  (class bit0 ref1) result: OK

Process 52 acquiring IX lock on OBJECT: 23:308196148:0  (class bit2000000 ref1) result: OK

Process 52 acquiring IU lock on PAGE: 23:1:14144 (class bit0 ref1) result: OK

Process 52 acquiring U lock on KEY: 23:72057594051231744 (00009620dd9a) (class bit0 ref1) result: OK

Process 52 releasing lock on KEY: 23:72057594051231744 (00009620dd9a)

Process 52 acquiring U lock on KEY: 23:72057594051231744 (02006d47cbee) (class bit0 ref1) result: OK

Process 52 acquiring IX lock on PAGE: 23:1:14144 (class bit2000000 ref0) result: OK

Process 52 acquiring X lock on KEY: 23:72057594051231744 (02006d47cbee) (class bit2000000 ref0) result: OK

Process 52 releasing lock reference on KEY: 23:72057594051231744 (02006d47cbee)

Process 52 acquiring U lock on KEY: 23:72057594051231744 (040060eff172) (class bit0 ref1) result: OK

Process 52 acquiring X lock on KEY: 23:72057594051231744 (040060eff172) (class bit2000000 ref0) result: OK

Process 52 releasing lock reference on KEY: 23:72057594051231744 (040060eff172)

Process 52 acquiring U lock on KEY: 23:72057594051231744 (06009b88e706) (class bit0 ref1) result: OK

Process 52 releasing lock on KEY: 23:72057594051231744 (06009b88e706)

Process 52 releasing lock reference on PAGE: 23:1:14144

Process 52 releasing lock on OBJECT: 23:372196376:0


As documented in the manual you have to use mi for minutes.  mm  is for the month number

select date,
       TO_CHAR(date,'dd/MM/yyyy HH24:mi:ss')
from user.table
where rownum<10;


Restoring a postgres8.3 backup with postgres8.4

PostgreSQL uses various formats for backups. What's the format of yours?

pg_restore is only needed for backups using one of the archive file formats. The default format of backups produced with pg_dump is an SQL script that you can restore via psql:

psql dbname < /path/to/backup

Start by reading the chapter "Backup and Restore" in the manual.


You should think about it a bit differently probably: every single transaction modifies the balance by some value - it either adds or subtracts depending on the type. And you can compute new balance from current balance and the value and type of new transaction. You usually don't need to recompute everything, because it has been done before. You can use those data to check that there was no error with your computation when something goes wrong.

For keeping the balance right when you might process multiple transactions on the same account at the same time, you should read something about locking. I suggest "pessimistic locking" for cases when working with money or similar stuff, you lock the balance of given user when you read it and then compute and update - and you can be sure that any other thread trying to modify the same account will have to wait until this one is done, so it starts with up-to-date data. This will need transactions and InnoDB or some other transactional engine with row locks, MyISAM won't work this way (only possibe to lock entire table which is generally much worse for performance).

As a note, if you have index on (ID, AMOUNT) in payments and orders, then this index can be used to compute your sums really fast for much bigger number of transactions (supposing those tables have many columns - datetimes, some other references, notes...) because it will be much smaller and organized the right way (it's called "clustering") so always only continuous segment has to be read for single ID.


LEFT JOIN and LEFT OUTER JOIN are synonymous so apologies but it sounds like option 2 :-)

The "Outer" join (which preserves unmatched rows) is as opposed to "Inner" (which doesn't).

There are three flavours of outer join. Left, Right, Full (dependent on which unmatched rows are to be preserved).

As SQL doesn't have direct join syntax for any other types of join (such as left/right anti semi join) there is no ambiguity in allowing the OUTER keyword be optional in the grammar.

The LEFT ANTI SEMI JOIN sounds like what you expected LEFT OUTER to do. You can implement that in a variety of ways. Usually NOT EXISTS is preferred but you can use LEFT OUTER JOIN in conjunction with IS NULL to keep only the non matched ones.

This is all confirmed in the "Join type" section of the documentation

  LEFT [ OUTER ] Specifies that all rows from the left table not
  meeting the join condition are included in the result set, and output
  columns from the other table are set to NULL in addition to all rows
  returned by the inner join.

The square brackets around the [OUTER] just mean that this is optional but nonetheless the same definition applies.


This is obviously a homework question but it seems to me a bit like a trick question from a db perspective.  I don't think there is a simple answer and here the answer may be quite a number of additional questions.  My recommendation in trying to answer the question is to sketch out how pages would work, and the like.  In essence so much depends on implementation (compare disk I/O in MySQL's InnoDB vs PostgreSQL and you will see massive differences due partly to supported  methods of access).

The key problem comes down to indexing.  The heap table could in fact be implicitly indexed the way MySQL's InnoDB does, or it could be unindexed (the way PostgreSQL does it).  If it is indexed, then you have additional issues.  Do you support a physical scan of the first block (to determine where to start)?  If so, how do you determine which starting point to use (if you collect statistics on the data in the tables that would help but that's additional I/O there too)?

I could imagine cases where the number of disk block reads could range from n/3 (where n is the number of records) to (n^1/10)/3, and writes to range from 1 to n/3.

In the end questions like this, particularly where you have software assigned for use in class probably should be directed to your instructor.  It isn't clear what the point of the exercise is and so much depends on implementation.


Postgresql Replication - Pacemaker vs DRBD

Usually, you use them both. DRBD to replicate the data synchronously, and Pacemaker to manage where the services become active. Pacemaker will control promoting/demoting DRBD, and starting your database and virtual IP address.

Pacemaker uses resource-agents to manage cluster services. One of those, is a resource-agent for moving a secondary IP between AWS nodes:
https://github.com/ClusterLabs/resource-agents/blob/master/heartbeat/awsvip

Hope that leads you down the right path.


MySQL INNODB SELECT query hanging on copying to tmp table

In my.cnf use this option

tmpdir=/tmp (this is the default anyway, just choose another folder)

Restart of mysql required

BTW since the location.ID is '193' why even involve the location table ???

Try the following :

SELECT DISTINCT
    activity_seismo_info.ID,
    activity_seismo_info.CREATED_AT,
    activity_seismo_info.UPDATED_AT,
    #....
    activity_seismo_info.ASI_EXTRA_5,
    seismo.ID,
    #....
    seismo.SEISMO_NOTES

FROM `activity_seismo_info`
    JOIN activity_info ON (activity_seismo_info.ASI_ACTIVITY_ID=activity_info.ID)
    JOIN (SELECT ID from location WHERE ID='193') location ON (activity_info.ACTIVITY_LOCATION_ID=location.ID)
    LEFT JOIN seismo ON (activity_seismo_info.ASI_SEISMO_ID=seismo.ID)
;