Conditions that can make a MySQL COMMIT query fail? I have an application where I run explicit COMMIT query on a MySQL standalone server
COMMIT;

The query works as expected in the vast majority of cases, but sometimes the query seems successful but at the same time seems not commiting the transaction to the db.
So I was digging in the possibility that a COMMIT query could fail. I found this similar question: https://stackoverflow.com/questions/3960189/can-a-commit-statement-in-sql-ever-fail-how
but on the official MySQL doc never mentions that a COMMIT can fail.
I want to understand in which conditions a commit query can fail and how to reproduce it, possibly supported by official doc pages.

This depends entirely on how you setup the transaction.
If you have START TRANSACTION and COMMIT; surrounding a series of INSERT, UPDATE, and DELETE queries, there are some commands that trigger an implicit commit:

ALTER TABLE
CREATE INDEX
DROP DATABASE
DROP INDEX
DROP TABLE
RENAME TABLE
TRUNCATE TABLE
LOCK TABLES
UNLOCK TABLES
SET AUTOCOMMIT = 1
BEGIN (can break another BEGIN or START TRANSACTION)
START TRANSACTION (can break another START TRANSACTION or BEGIN)

I have mentioned this before in the DBA StackExchange

Mar 06, 2018 : Can you rollback a query in state 'committing alter table to storage engine'
Aug 21, 2015 : Transactional DDL workflow for MySQL
Feb 12, 2014 : row locking within ACID transaction innodb
Mar 15, 2013 : MySQL backup InnoDB

You can see the latest list of commands that trigger implicit commits in the MySQL 5.7 Docs. Why consider implicit commits ? Data may commit out of context with other data too soon or too late.
Aside from these commands, terminating a DB Connection will implicitly rollback a transaction. You should monitor the status variables Aborted_connects and Aborted_clients to see if DB Connections are failing on entry or in session.


timestamp with timezone issue with TZR vs TZH:TZM I have a Hibernate with Oracle JDBC based application inserting/updating into the following column;

COLUMN_A TIMESTAMP WITH TIME ZONE DEFAULT systimestamp NOT NULL

but while it automatically appends timezone, it uses TZR, e.g. UTC, and weird thing with this data type in Oracle is that, it retains the format of timezone from insertion when we select it without any formatting, explained here

With this said, we are utilizing Golden Gate that is replicating this data to MongoDB, but afaik it requires these timestamps to contain only TZH:TZM formatting, so I have this issue where one side blocked by Oracle JDBC, where insertion is done with TZR, and one side blocked by Golden Gate where data is expected with TZH:TZM

Are there any way to handle this issue? Can I not forbid a certain formatting for TIMESTAMP WITH TIME ZONE ? Using NLS_TIMESTAMP_TZ_FORMAT works for SELECT formatting, but it is not usable for Golden Gate I was told. Also for INSERT case I was able to utilize default value of the column, and using systimestamp does insert with TZH:TZM, but for UPDATE case, I am unable to achieve this. Totally stuck!

I have this question with focus on application side of this issue. I am wondering if there is anything that can be done in DB side?

You could define your table like this.

CREATE TABLE TEST_A (
    COLUMN_A
        TIMESTAMP(6) WITH TIME ZONE
        DEFAULT SYSTIMESTAMP AT TIME ZONE '+00:00'
        NOT NULL,
        CHECK (EXTRACT(TIMEZONE_REGION FROM COLUMN_A) = 'UNKNOWN')
    );

Table created.

INSERT INTO TEST_A VALUES (TO_TIMESTAMP_TZ('19-JUL-18 01.53.16.381566000 PM +00:00', 'DD-MON-RR HH.MI.SSXFF AM TZH:TZM'));

1 row created.

INSERT INTO TEST_A VALUES (TO_TIMESTAMP_TZ('19-JUL-18 01.53.16.381566000 PM UTC', 'DD-MON-RR HH.MI.SSXFF AM TZR'));

>> INSERT INTO TEST_A VALUES (TO_TIMESTAMP_TZ('19-JUL-18 01.53.16.381566000 PM UTC', 'DD-MON-RR HH.MI.SSXFF AM TZR'))
Error at line 11
ORA-02290: check constraint (SYS_C00241787) violated

Or you create a table with trigger:

CREATE TABLE TEST_B (
    COLUMN_A
        TIMESTAMP(6) WITH TIME ZONE
        DEFAULT SYSTIMESTAMP AT TIME ZONE '+00:00'
        NOT NULL
    );

CREATE OR REPLACE TRIGGER BUIR_TEST_B
    BEFORE INSERT OR UPDATE ON TEST_B
    FOR EACH ROW
BEGIN
    IF EXTRACT(TIMEZONE_REGION FROM :NEW.COLUMN_A) <> 'UNKNOWN' THEN
        :NEW.COLUMN_A := :NEW.COLUMN_A AT TIME ZONE TO_CHAR(:NEW.COLUMN_A, 'tzh:tzm');
    END IF;
END;
/

However, I don't believe this would be a proper solution. You may face issues when you work with time zones having daylight saving times.


UPDATE with CAST(meta->>"$.field") failing due to "null" This issue is affecting MySQL 5.7.25.

So, in a migration I'm moving legacy fields from the table into a JSON column. This part of the process works fine. But, the rollback is failing miserably! At this point, I'm considering moving the JSON field logic out of SQL into PHP.

You can reproduce the issues as follows.

DB Setup

CREATE TABLE `test_table` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `meta` json DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO `test_table` VALUES
(null, '{"test_dt": "2019-03-28 15:43:59", "test_enum": "foo"}'),
(null, '{"test_dt": "2019-03-28 15:44:15", "test_enum": null}'),
(null, '{"test_dt": null, "test_enum": "bar"}');

ALTER TABLE `test_table`
  ADD COLUMN `test_enum` ENUM('foo', 'bar') NOT NULL DEFAULT 'foo',
  ADD COLUMN `test_dt` DATETIME NULL;

The Problem

Is there a workaround for the following query? I would prefer just 1 update query if possible...

-- copy from meta back into legacy fields
UPDATE `test_table` SET
  `test_enum` = `meta`->>"$.test_enum",
  `test_dt` = CAST(`meta`->>"$.test_dt" AS DATETIME);

Row 2 will cause an error: Data truncated for column 'test_enum'. This seems to be because both meta->>"$.test_enum" and JSON_EXTRACT(meta, "$.test_enum") are returning "null" instead of NULL.

Now try again without the enum field update:

-- copy from meta back into legacy fields
UPDATE `test_table` SET
  `test_dt` = CAST(`meta`->>"$.test_dt" AS DATETIME);

Row 3 now causes an error: Incorrect datetime value: 'null'

I know I can work around the errors by breaking the query into two like:

UPDATE test_table
SET test_enum = `meta`->>"$.test_enum"
WHERE JSON_TYPE(JSON_EXTRACT(`meta`, "$.test_enum")) != 'NULL';

UPDATE test_table
SET test_dt = `meta`->>"$.test_dt"
WHERE JSON_TYPE(JSON_EXTRACT(`meta`, "$.test_dt")) != 'NULL';

Confusion

SELECT-ing the data via CAST works fine, even though the ENUM-related column is invalid due to the "null" value. Why doesn't JSON_EXTRACT() return NULL properly?

SELECT
  `meta`->>"$.test_enum" AS `test_enum`,
  CAST(`meta`->>"$.test_dt" AS DATETIME) AS `test_dt`
  FROM test_table

"id","test_enum","test_dt"
1,"foo","2019-03-28 15:43:59"
2,"null","2019-03-28 15:44:15"
3,"bar",NULL

Right now, I'm just wondering why MySQL acts this way with its NULL handling in both JSON and the CAST/CONVERT methods. I feel like I'm missing something obvious.

I think it is a bug. I cannot find any such bug report in MySQL developer zone so you should report it.

demo 1
demo 2


Does this query always correctly retrieve the types of array parameters The following query seems to work, but I am not sure that it is the best/always correct way:

select
parameter_name,
CASE WHEN data_type='ARRAY'
    THEN SUBSTRING(udt_name, 2)||'[]'
    ELSE data_type
END AS data_type
from information_schema.parameters
where specific_schema='my_schema'
and parameter_mode='IN'
and specific_name like 'my_proc_16831'
order by ordinal_position

Anyway, it looks kind of hacky to me. Is there a better way to retrieve, for example, numeric[] as a type if my parameter is an array of numeric?

I think you want format_type(typid oid, typmod integer).

Given:

CREATE SCHEMA my_schema;

CREATE FUNCTION my_schema.my_proc_16831(numeric[])
RETURNS numeric[] LANGUAGE sql AS $$
SELECT $1;
$$;

You can get proc details with:

SELECT
  p.parameter_name,
  format_type((p.udt_schema||'.'||p.udt_name)::regtype, -1)
FROM information_schema.parameters AS p
INNER JOIN information_schema.routines
  USING (specific_catalog, specific_schema, specific_name)
WHERE specific_schema = 'my_schema'
  AND parameter_mode = 'IN'
  AND routine_name = 'my_proc_16831';

... though this removes the portability of using information_schema, since you're relying on Pg specific behaviour. It also assumes the typmod is always -1 because PostgreSQL doesn't keep track of the typmod for functions - e.g. numeric(18,2) becomes just numeric as a function argument.

At least this way you don't need to use your LIKE hack, though, or relying on changeable assumptions about array internal type names.

BTW, you can use pg_proc and pg_type directly instead. This might be worth doing if performance is critical, though otherwise information_schema will be more cross-version portable.

That gives you the equivalent:

SELECT
  proargnames[argpos] AS arg_name,
  format_type(proargtypes[argpos],-1) AS arg_type
FROM pg_proc p
INNER JOIN pg_namespace n ON (p.pronamespace = n.oid)
CROSS JOIN LATERAL generate_subscripts(proargtypes, 1) argpos
WHERE n.nspname = 'my_schema'
  AND p.proname = 'my_proc_16831'
/* AK: suggested additional clause
      AND argpos > 0
*/
    ORDER BY p.proname, argpos;


Where Clause for Results Appearing after Occurence I'm wondering if it's possible to find records that only appear after the first occurrence of a where clause. I'm currently using PostgreSQL for this example but something not PostgreSQL specific would be preferred.
If I had a table such as this for an example
drop table if exists dummy;
create table dummy (
   col_a   varchar(10),
   col_b   varchar(10),
   col_c   varchar(10),
   test    integer
);

insert into dummy values
('A', 'B', 'C', 1),
('B', 'C', 'A', 2),
('C', 'A', 'B', 3),
('D', 'E', 'F', 4);

If I run the bellow query I can get the data in a new order
select * from dummy
order by col_b;

returning
C | A | B | 3
A | B | C | 1
B | C | A | 2
D | E | F | 4

Is it possible to do something so that I can only return the rows occurring after a certain where clause, something like
select * from dummy
/* where row appears after results 'A', 'B', 'C', 1 */
order by col_b;

To get
B | C | A | 2
D | E | F | 4

UPDATE
The reason I am not using where col_b > ? is because it doesn't cater for duplicate values
It would work until I need to match on an additional column like this
select * from dummy
where col_b > 'B'
and col_c > 'C'
order by col_b;

In which case it will return the last row because both values are above the cutoff but it is not cutting off after the first occurrence of the specified row.
I'm trying to have a where clause that can identify the specific row being row
A | B | C | 1

In this case

it seems you are looking for something like this:
with cte as
    (
    select *
        ,row_number() over(order by col_b) as rn
    from dummy
)
select *
from cte
where rn > (select rn from cte where col_a = 'A' and col_b = 'B' and col_c = 'C' and test = 1 limit 1)


Retrieving data of vehicle drivers that have not been assigned to a job Struggling with something for the past 24 hours. Needed some genius to shine some light please. I am trying to display a weekly job rota/log for drivers, which will display:

Jobs that have not been assigned to drivers
Jobs that have been assigned to drivers
If a job has been assigned to a driver then display drivers that are available for the day
If there is no jobs for the day then display a list of all drivers available

The last 2 points are what I am really struggling with.

I have the following tables:

drivers ('driver_id', 'driver_name')

vehicles ('vehicle_id', 'vehicle_make', 'vehicle_model')

jobs ('job_id', 'collection_address', 'collection_datetime', 'delivery_address', 'deliver_datetime', 'driver_id', 'vehicle_id')

I have attached the output screen below:

For example if you look at 23/09/2016 the driver Keith is on a job but Nick is available. However, if I do a sub-query it displays both drivers, which is not the correct result. My SQL statement is below and if anyone can help with this, I would really appreciate it.

    SELECT listofdays.job_date, j.job_id, IF( j.driver_id > 0 AND j.job_id > 0, (SELECT driver_name FROM t_drivers WHERE driver_id = j.driver_id LIMIT 1), '') as job_driver, IF( j.vehicle_id > 0, (SELECT vehicle_reg FROM t_vehicles WHERE vehicle_id = j.vehicle_id LIMIT 1), 'no') as job_vehicle, j.collection_town, j.collection_postcode, j.delivery_town, j.delivery_postcode, j.job_status
 FROM (
  SELECT '2016-09-19' + INTERVAL seq.seq DAY AS job_date
  FROM seq_0_to_999999 AS seq
  WHERE seq.seq <= TIMESTAMPDIFF(DAY , '2016-09-19', '2016-09-25' )
 ) AS listofdays
LEFT JOIN t_jobs j ON listofdays.job_date = DATE(j.collection_datetime)
ORDER BY DATE(listofdays.job_date), j.job_order ASC

I am also happy to take onboard any improvements to the above query in terms of speed and performance. It is going to be used for an internal system.

REVISED QUERY BELOW:

    SELECT
  sub.job_date,
  j.job_id,
  IFNULL(d.driver_name, '') as job_driver,
  IFNULL(v.vehicle_reg, 'no') as job_vehicle,
  j.collection_town,
  j.collection_postcode,
  j.delivery_town,
  j.delivery_postcode,
  j.job_status,
  IF(j.job_id IS NULL, sub.available_drivers, 'no availability') AS available_drivers
FROM
  (
    SELECT
      listofdays.job_date,
      GROUP_CONCAT(d.driver_name) AS available_drivers
    FROM
      (
        SELECT
          '2016-09-19' + INTERVAL seq.seq DAY AS job_date
        FROM
          seq_0_to_999999 AS seq
        WHERE
          seq.seq <= TIMESTAMPDIFF(DAY, '2016-09-19', '2016-09-25')
      ) AS listofdays
      CROSS JOIN t_drivers AS d
      LEFT JOIN t_jobs AS j ON listofdays.job_date = DATE(j.collection_datetime)
      AND d.driver_id = j.driver_id
GROUP BY listofdays.job_date
  ) AS sub
  LEFT JOIN t_jobs AS j ON sub.job_date = DATE(j.collection_datetime)
  LEFT JOIN t_drivers AS d ON j.driver_id = d.driver_id
  LEFT JOIN t_vehicles AS v ON j.vehicle_id = v.vehicle_id
ORDER BY
  job_date ASC,
  job_order ASC

The output is the following:

If you look at the above picture on the 23rd there is 2 open jobs, which should have a list of the available drivers but it is saying no availability. In our case Nick is not working on that day but it says no availability.

I would start with the list of available drivers. As you appear to want them as a CSV string, it makes sense to use grouping and concatenate the names with GROUP_CONCAT(). Use an outer join of jobs to the cross-product of dates and drivers, then, to get the available drivers for each day, group-concatenate the driver name only if it has no matching job:

SELECT
  listofdays.job_date,
  GROUP_CONCAT(IF(j.job_id IS NULL, d.driver_name, NULL)) AS available_drivers
FROM
  (
    SELECT
      '2016-09-19' + INTERVAL seq.seq DAY AS job_date
    FROM
      seq_0_to_999999 AS seq
    WHERE
      seq.seq <= TIMESTAMPDIFF(DAY, '2016-09-19', '2016-09-25')
  ) AS listofdays
  CROSS JOIN t_drivers AS d
  LEFT JOIN t_jobs AS j ON listofdays.job_date = DATE(j.collection_datetime)
                     AND d.driver_id = j.driver_id
GROUP BY
  listofdays.job_date
;

The next, and last, step would be to use the above as a derived table and outer-join t_jobs to it once more – this time to get the job details (and you would also need to outer-join t_drivers and t_vehicle to get details from those tables as well):

SELECT
  sub.job_date,
  j.job_id,
  IFNULL(d.driver_name, '') as job_driver,
  IFNULL(v.vehicle_reg, 'no') as job_vehicle,
  j.collection_town,
  j.collection_postcode,
  j.delivery_town,
  j.delivery_postcode,
  j.job_status,
  sub.available_drivers
FROM
  (
    SELECT
      listofdays.job_date,
      GROUP_CONCAT(IF(j.job_id IS NULL, d.driver_name, NULL)) AS available_drivers
    FROM
      (
        SELECT
          '2016-09-19' + INTERVAL seq.seq DAY AS job_date
        FROM
          seq_0_to_999999 AS seq
        WHERE
          seq.seq <= TIMESTAMPDIFF(DAY, '2016-09-19', '2016-09-25')
      ) AS listofdays
      CROSS JOIN t_drivers AS d
      LEFT JOIN t_jobs AS j ON listofdays.job_date = DATE(j.collection_datetime)
                         AND d.driver_id = j.driver_id
    GROUP BY
      listofdays.job_date
  ) AS sub
  LEFT JOIN t_jobs AS j ON sub.job_date = DATE(j.collection_datetime)
  LEFT JOIN t_drivers AS d ON j.driver_id = d.driver_id
  LEFT JOIN t_vehicles AS v ON j.vehicle_id = v.vehicle_id
ORDER BY
  job_date ASC,
  job_order ASC
;


Is it possible to synchronize two ms access db? We have a flower store where we use MS Access and a client program to work with the database. But this program can work only with one database.
We are going to open a new store, so now we have to solve the problem of syncing between the two databases. It can be synced once a day.

Is this possible with MS Access 2007 tools, or we have to buy some program?

You can use the import data tools that are part of access to import data from one database into the other. It is under the "External Data" tab. This should work as long as you have network access to both databases.

Some more info : http://office.microsoft.com/en-us/access-help/import-or-link-to-data-in-another-access-database-HA001227658.aspx

Before doing this, have a little think about your table design - do you need to have a flag to indicate which store the data came from? If the data is normalized, you may have have issues importing data that may be resolved by the order that the data is imported in.

Also, remember to regularly backup these database files before trying to import. This way if you break something you can go back and start again. Also store them on physically different disks just in case....


SQL Server 2012 - Know when a SEQUENCE has recycled or is nearing cycle phase In SQL Server 2012, is there a way to know if a Sequence has recycled?

Or some sort of trigger to know if a Sequence is nearing its cycling phase.

I need to update a table whenever a Sequencerecycles.

There is no metadata view that is going to clearly give you this information. Your best bet would be to have a scheduled process that runs every few minutes and looks at sys.sequences and evaluates if the current_value is less than the prior current_value, in which case you would know that it had cycled.

You can also compare the current_value to the maximum_value to see how close you are to hitting the cycle point.

A very basic view of this would be the following with a small sequence, you can see the initial values remaining, what it looks like after using a few sequences, and then how it gets reset as the sequence cycles.

CREATE SEQUENCE dbo.TestSeq
AS SMALLINT
MINVALUE 1
MAXVALUE 10
START WITH 1
INCREMENT BY 1
CYCLE;

SELECT  name ,
        current_value ,
        maximum_value ,
        CAST(maximum_value AS BIGINT) - CAST(current_value AS BIGINT) AS Values_Remaining
FROM    sys.sequences
WHERE   name = 'TestSeq';
GO

SELECT NEXT VALUE FOR dbo.TestSeq;
GO 5

SELECT  name ,
        current_value ,
        maximum_value ,
        CAST(maximum_value AS BIGINT) - CAST(current_value AS BIGINT) AS Values_Remaining
FROM    sys.sequences
WHERE   name = 'TestSeq';
GO

SELECT NEXT VALUE FOR dbo.TestSeq;
GO 6

SELECT  name ,
        current_value ,
        maximum_value ,
        CAST(maximum_value AS BIGINT) - CAST(current_value AS BIGINT) AS Values_Remaining
FROM    sys.sequences
WHERE   name = 'TestSeq';
GO


ssis control flow loop I need an SSIS package that loops back to the initial execute task after the wait for delay block is executed. here is the logic for the below package: execute SQL task executes a set of commands and after that if a condition is met, the second process(preparation sql task 1 and so on) will be executed. if the condition is not met, it will wait for some time and re-run the first task(execute sql task). I am stuck here and cannot go any further. please help

You can create a For Loop Container. For an example, see the mssqltips article Configure the SQL Server Integration Services For Loop Container.


SQL Server 2012 (compability level 110) and SQL Server Native Client 10 I've upgraded my SQL Server 2008R2 to SQL 2012. I will set the compability level to 110 (2012) to all databases.

But I'm wondering if it will create some problems for clients that connect to this server with SQL Server Native Client 10.0 ?

I saw on multiple forums that they only won't be able to use new features of SQL 2012 but they never speak about level of compatibility of databases.

Can someone give me some clarification ?

Thanks a lot!

The client library used to connect does not care at all about the compatibility level of the database.

The code issued by the client may have some issues (for example, you needed 90 to use APPLY many years ago). But the mechanism used to send the code and get results does not care


Search across all columns in a table As the title says, I want to create a query that searches a keyword across all columns in that table.

So I want to simplify this query:

SELECT * FROM tblCustomers
    WHERE (col1 LIKE '%keyword%' OR col2 LIKE '%keyword%' OR col3 LIKE '%keyword%' OR col4 LIKE '%keyword%')
    AND (col1 LIKE '%keyword2%' OR col2 LIKE '%keyword2%' OR col3 LIKE '%keyword2%' OR col4 LIKE '%keyword2%');

To something like:

SELECT * FROM tblCustomers
    WHERE (allColumns LIKE '%keyword%')
    AND (allColumns LIKE '%keyword2%');

Full Text Search

You can use FTS5

CREATE VIRTUAL TABLE customer USING tblCustomers(col1, col2, col3);

Then you just

SELECT * FROM tblCustomers WHERE email MATCH 'keyword AND keyword2';


Multiple COUNTs over the same column I have a table (PostgreSQL 9.6) containing 2.6 million+ timestamped rows associated with account identifiers, and for any given identifier, I want to count the total number of occurrences as well as just the number of occurrences today in a single query.

For reference, this is the same table described in this question, but I have simplified it here to focus on this specific issue:

CREATE TABLE account_test
(
  id integer NOT NULL PRIMARY KEY
);

CREATE TABLE log_test
(
  account integer NOT NULL REFERENCES account_test(id),
  event_time timestamp with time zone NOT NULL DEFAULT now()
);

CREATE INDEX account_test_idx ON log_test USING btree (account,event_time);

INSERT INTO account_test VALUES (1);
INSERT INTO account_test VALUES (2);

INSERT INTO log_test VALUES (1,'2018-01-01');
INSERT INTO log_test VALUES (1,'2018-01-02');
INSERT INTO log_test VALUES (1,'2018-01-03');
INSERT INTO log_test VALUES (1,now());
INSERT INTO log_test VALUES (1,now());

INSERT INTO log_test VALUES (2,'2018-01-01');
INSERT INTO log_test VALUES (2,'2018-01-02');
INSERT INTO log_test VALUES (2,now());

This is my initial attempt, which is producing the same number for both the daily and total counts due to the GROUP BY:

    SELECT a.id,COUNT(d) AS daily,COUNT(t) AS total FROM account_test a
      JOIN log_test d ON a.id=d.account AND d.event_time > now() - interval '1 day'
      JOIN log_test t ON a.id=t.account
     WHERE a.id=1 GROUP BY a.id;

 id | daily | total
----+-------+-------
  1 |    10 |    10
(1 row)

The results I am looking for are:

 id | daily | total
----+-------+-------
  1 |     2 |     5
(1 row)

Specifically, the result of this ugly query:

SELECT qd.id,qd.daily,qt.total FROM
(
    SELECT a.id,COUNT(d) AS daily FROM account_test a
      JOIN log_test d ON a.id=d.account AND d.event_time > now() - interval '1 day'
     WHERE a.id=1 GROUP BY a.id
) qd,
(
    SELECT a.id,COUNT(t) AS total FROM account_test a
      JOIN log_test t ON a.id=t.account
     WHERE a.id=1 GROUP BY a.id
) qt;

I realize this may be a softball question, but in this case my SQL instincts are failing me, and I suspect there might be some clever trick that would eliminate the extra JOIN.

I believe using a SUM + CASE expression would work, because the CASE enables you to do a 'selective count'. Or you can use the newer FILTER:

SELECT a.id,
  count(*) FILTER (WHERE d.event_time > now() - interval '1 day') AS daily,
  count(*) AS total
FROM account_test a
JOIN log_test d ON a.id=d.account
GROUP BY a.id;

With the dataset from your question, this gives the following results:

 id | daily | total
----+-------+-------
  2 |    1  |    3
  1 |    2  |    5


SQL Server Replication w/o network connection Is there a way to use SQL Server Replication without a network connection?

My would-be publisher is an SQL Server 2005. It is on the "outside" as it is connected to my company's primary LAN, which is connected through a firewall to the Internet. My would-be subscriber, however, is an SQL Server 2012 that is on the "inside" - due to security reasons, it must be connected to a network that is completely (i.e. physically) isolated from our primary LAN and the Internet. I need a way to replicate outside server changes on the inside on a daily basis.

Is there a way to use Replication without a network connection between publisher and subscriber (e.g. via an optical-disc-based file transfer)? Our security protocol allows for media to be brought inside from the outside, but any kind of communication from inside to outside is forbidden. Ideally, I would run a script on the outside server, burn the resulting output to a disc, and then import the changes (be them inserts, updates, or deletes) to the inside server.

It seems Merge Replication is close to providing this functionality, as it is intended to support subscribers with sporadic network connectivity, but it doesn't seem to support scenarios in which there is no network connectivity.

Further confounding the situation is the fact that due to the size of the database involved (~50 GB), I need a way to only transfer the differential - I would only like to have to burn the daily changes to disc, not the entire DB (thus eliminating Snapshot Replication as an option).

For now, I have both servers on the outside, so I can use network-based Replication for the initial transfer, but once I deploy the subscriber to the inside, this will no longer be a possibility.

I am also open to other means of solving this problem. I have looked into backup/restore solutions, but have run into trouble as I only need to perform this operation on a subset of the data in the DB, and backing up/restoring only a subset of a DB does not seem to be a trivial exercise.

If you use log shipping, you can do the copy operation of the transaction log backups manually to take them from one server to the other.


Automatically generated numbers I have a field called PropNum which is the proposal number for a marketing pursuit.  I want this field to be automatically generated by gathering data from other fields, automatically generating the last three numbers and automatically restarting back to 001 at the beginning of the next year.  For example, I have the following line in my data:

        Year,        Office,     PropNum,
Record 1 2016       STL         16STL001
Record 2 2016       STL         16STL002
Record 3 2016       CHI         16CHI001
Record 4 2016       CHI         16CHI002

In addition to starting over at the beginning of the year, I would like the number to be sequential based the office.  As you can see in my example the STL office has a list of Proposal numbers and the CHI office has another.  Can anyone help me write the script to do this?

If going down the view approach which would limit disk IO, be more normalized and MAY perform better(depending on the use of the value), it would look something like this;

CREATE VIEW [newviewname]
AS
    SELECT CAST(RIGHT(Year,2) AS VARCHAR(2)) + Office + ROW_NUMBER() OVER (PARTITION_BY Year, Office ORDER BY Record))
    FROM [your table]

Also, you may want to wrap the concatenated values with ISNULL() if any of those values might actually return NULL.  A NULL value concatenated with anything else will always return NULL.

A trigger will actually be written very similarly except would need to be updated each time a value in the table is updated.  Unless there's some performance or data structure related need, I would avoid the trigger approach.
Either way for a "correct" answer we would need to know your objectives. How do you intend to use the generated value.
BTW, this answer assumes you are using MS SQL.


Exploring and optimizing SQL performance of your system (PostgreSQL and Hibernate)? Imagine you have inherited some project with extensive usage of SQL queries.

Let us assume that Hibernate is used as ORM and PostgreSQL as database, but thats not the point.

A question is: could you suggest a plan to sequentially scan the system and optimize the SQL queries performance?

I suggest the following steps of that plan:

check which queries take the longest time to execute - how can we do it in PostgreSQL?
check which queries are executed most often - how can we do it in PostgreSQL?
find out which queries are not using the indexes - how can we do it in PostgreSQL?
find out if the connection pool is operating without any delays -  - how can we do it in PostgreSQL?
check the cache usage of Hibernate: are the queries from the first two points are cached? - how can we do it in Hibernate?
which queries tend to hit the database instead of hitting the cache - how can we do it in Hibernate?

Please suggest your own options.

May be there is some soft to recommend for this task?

May be there are some other database parameters to check, to log, to plot to monitor?

Get the dynamic SQL generated by Hibernate and ask PostgreSQL to EXPLAIN PLAN.
Log the queries to your database using an aspect.  Add in wall time while you're at it so you can calculate mean wall time and standard deviation.
EXPLAIN PLAN and look for TABLE SCAN
Don't know - it's not under PostgreSQL control.  Better to look into your app server.
Aspects and JMX
Aspects and JMX


How to link users and logins in an Availability Group? I've got an Availability Group going and need to map some logins to database users on an active secondary replica. (Node2)  Unfortunately, when I try to exec sp_change_users_login or alter user with login, I get an error message telling me the database is read-only.  That's no surprise, but I'm not sure how to fix it.  I tried failing over last night, fixing the logins on Node2, and then failing back to Node1, but then the login/user mappings just end up wrong on Node1.  When I fix those, Node2's logins get out of whack again.

This is a production system, so I can't do anything I want until later tonight.  I didn't run into this problem in our test lab.  The difference may be that I created the logins before joining the databases before, whereas this time I tried to create them afterwards, and failed to document the process correctly.  Taking the AG down and rebuilding it is something I'd rather avoid if I can.

The problem makes sense to me, I just don't really know how to fix it.

What you will have to do is connect to the instance that has a mis-matched SID, and you'll have to recreate the login and specify an explicit SID.  For instance, on the instance where you have the orphaned user and the following returns the user:

exec sp_change_users_login 'report';
go

Copy the SID from the column UserSID.  And if you already have an existing login that you want to preserve the name from on that instance, you can't.  There is no way to ALTER LOGIN and change the SID.  So, you will need to drop the login and recreate it:

drop login YourLogin;
go

create login YourLogin
with
    password = 'password',
    check_policy = off,     -- simple password and no check policy for example only
    sid = 0xC26909...................;
go

Again, you will want to set the sid parameter of CREATE LOGIN to the UserSID you retrieved from executing sp_change_users_login with the report.

Note:  You will want to set the password on this/these replica(s) to the same so that you don't have a password mismatch between replicas.

I am assuming that your login(s) in question here are SQL Server logins, as this shouldn't be a problem for Windows Auth domain accounts because the SIDs should be the same on each instance.

You could also leverage contained databases here and have the database principal authenticate at the database level.  That way you wouldn't have to worry about orphaned users.

Here is some documentation on this:
BOL reference on Management of Logins and Jobs for the Databases of an Availability Group
KB article on How to transfer logins and passwords between instances of SQL Server

Also, this should go without saying, but test this out in a development/QA environment to verify and ensure proper functionality before hitting production.


SQL Server - How to protect against SQL Injection when dealing with a Dynamic WHERE clause passed to a Stored Procedure Given the following stored procedure, can I protect against SQL injection in the supplied additional WHERE condition?

CREATE PROCEDURE [dbo].[ProtectDynamicWhereClause]
   (@TableName varchar(50),
    @OldestRecordDate varchar(15),
    @WhereCondition varchar(250) = NULL)
AS
BEGIN
    -- Protect the table name from SQL Injection.
    DECLARE @TableNameClean varchar(150) = QUOTENAME(@TableName)

    DECLARE @sql nvarchar(4000)
    SET @sql =
            N'
            SELECT * FROM ' + 'dbo.' + @TableNameClean
            + ' WHERE EntryAge > DATEDIFF(year, @OldestRecordDate, GETDATE()) '

    IF (@WhereCondition IS NOT NULL)
    BEGIN
        SET @sql = @sql + ' AND ' + @WhereCondition
    END

    EXEC  sp_executesql @sql, N'@TableNameClean varchar(50), @OldestRecordDate varchar(15)',
                                @TableNameClean = @TableNameClean, @OldestRecordDate = @OldestRecordDate
END

EXEC ProtectDynamicWhereClause @TableName='CustomerTbl', @OldestRecordDate = '2012-01-01', @WhereCondition = 'CustomerName LIKE ''%Smith%'''
EXEC ProtectDynamicWhereClause @TableName='ProductTbl', @OldestRecordDate = '2010-01-01', @WhereCondition = 'ProductId IN (123, 345, 567)'

Could you please tell me how to protect against SQL injection in this statement when the @WhereCondition is actually passed-into the stored procedure as a parameter as shown?

SET @sql = @sql + ' AND ' + @WhereCondition

Thank you.

You simply don't write it in such a way that a user can pass in a structured WHERE clause. This is a recipe for disaster, and I bet at least half of the companies who have been exploited by SQL injection thought they were protected by checking the input for keywords, stripping out semi-colons and comments, etc. They weren't, and there will always be ways around whatever protection you try to manually write.

Don't be lazy. Write it so that the user picks the possible columns and operations from drop-downs, and only enters the parameter values into free text. Then you construct the dynamic SQL with the columns you know exist and with strongly-typed parameters that can only be treated as such rather than just appending your query with whatever someone typed into a form field on a web page and blindly executing it.

Surely there aren't 8,000 columns in this table, and the number of permutations of clauses that a user can add are not exhaustive. You could always limit it logically to 5 or 10 clauses so that you don't have GB-long WHERE clauses.

Some constructive ideas for dealing with dynamic SQL and flexible WHERE clauses:

Protecting Yourself from SQL Injection - Part 1
Protecting Yourself from SQL Injection - Part 2

I have videos about my solution to "the kitchen sink" here and here as well as a blog post about it.

Other answers where I talk about the kitchen sink procedure:

Tsql Query speed slow due to Or within where clause, causing index scan instead of seek
Plan cache memory: parameterized SQL vs stored procedures
SQL Server procedure optimisation
Strange looking where clause in sql server
Stored procedure with recompile


How can I resolve a database name to a directory? I am connected to a database foo, and I want to resolve where the underlying directory is for that database, what are my options? Obviously I can reason from datadir, and the database name but there are problems with that: it requires me to implement my own encode function to map identifiers to file names.. Which is encoding-dependant.

This encoding process is mandatory, the database foo._234-asdf maps to data directory /var/lib/mysql/foo@002e_234@002dasdf.

Is there a supported way to query the data-directory for a specific database, or for the connected database?

I couldn't find a better way of doing this than rolling my own routine. Currently, I am using this perl code:

s/(\W)/sprintf('@%04x', ord $1)/eg;


Programmatically get parameters required by DACPAC Is there a way to find out what parameters a DACPAC requires? I know they are listed in an error message if you try to publish without parameters, but that takes too long - 10-15 seconds. Also, a database needs to be targeted to get the error message with the list.

The DACPAC is just a zip file, so you could UnZip it and parse the model.xml file contained.

small extract from one of my dacpacs:

<DataSchemaModel FileFormatVersion="1.2" SchemaVersion="2.4" DspName="Microsoft.Data.Tools.Schema.Sql.Sql100DatabaseSchemaProvider" CollationLcid="1033" CollationCaseSensitive="False" xmlns="http://schemas.microsoft.com/sqlserver/dac/Serialization/2012/02">
    <Header>
        <CustomData Category="SqlCmdVariables" Type="SqlCmdVariable">
            <Metadata Name="ABC" Value="" />
            <Metadata Name="XYZ" Value="" />
        </CustomData>

so I need to define the SqlCmdVariables ABC and XYZ for this dacpac


Why does SQL Server not perform constant (UNION ALL) branch elimination with OPTION(RECOMPILE) when selecting the result into a scalar variable? We use some 'aggregate' views to select from multiple tables using a discriminator (note: these views are not partitioned views, because the discriminator is not in the base tables). This normally works well when using option(recompile), as the query planner will eliminate the non-reachable union all paths before selection of a query plan.
However, this constant-folding optimization appears defeated when selecting the result into a scalar variable. Selecting the result into a temporary table variable does not de-optimize the recompilation.
Here is a reproduction case in SQL Server 2017:
-- A table, don't need any data.
create table [test].test_table (col1 int, primary key (col1));

-- A simple 'aggregate' view. Using the same table here is irrelevant and,
-- while the view shows the scenario, it might not be required to reproduce the issue.
create view [test].test_view as
select col1, descrim = 1 from [test].test_table
union all
select col1, descrim = 2 from [test].test_table

Normal query, which results in an optimized query plan touching only one of the union all branches:
declare @descrim int = 2;

select count(col1)
from [test].test_view
where descrim = @descrim
option (recompile) -- explicit recompile here "works"

However, as soon as a "select into scalar variable" is used, the plan becomes de-optimized as it does not eliminate the non-used union. (The plan is still correctly optimized when using a literal value in the query text.)
declare @descrim int = 2;
declare @brokeit int;

select @brokeit = count(col1)
from [test].test_view
where descrim = @descrim
option (recompile) -- explicit recompile here does NOT optimize plan for @descrim!

1. Is this de-optimization "expected"?
2. Where is this significant de-optimization behavior with respect to option(recompile) and/or selecting into a scalar variable documented or otherwise discussed in depth?
3. Is there an simple way to get an recompile-optimized plan with select @x = .. without using a temporary table (variable)?
While during query execution the union all will prevent actual IO access to the secondary artifact, such is still an issue with query plan generation. In the specific error case spawning this question, leaving in multiple tables for consideration prevents SQL Server from choosing an appropriate seek plan and the resulting plan options are very poor choices in the given domain.
The first "good" plan:

The second and "bad" plan:

This "bad" plan also has an implicit conversion warning, making me suspect that the select into a scalar variable might be bypassing many different optimizations - or even ignoring the option(recompile) hint entirely.

Constant folding has a particular meaning in SQL Server. It is not directly involved in your question. The features that combine to produce extensive simplification of the execution plan for you are the parameter embedding optimization (PEO) and contradiction detection.
PEO embeds the literal value of e.g. a parameter or local variable into the query text, in circumstances where it is safe to do so. One of the requirements is that OPTION (RECOMPILE) is specified. This guarantees that the plan generated will never be reused, so it may be safe to replace non-literals with sniffed literals.
The OPTION (RECOMPILE) hint itself only provides that a new plan will be generated on each execution, the sniffed values of any parameters will be used for cardinality estimation, and the one-off plan generated will not be cached for reuse after execution.
PEO was first added to the product in SQL Server 2008, though it was disabled shortly afterwards due to the possibility of incorrect results. It was re-enabled in SQL Server 2008 SP1 CU5 (Microsoft blog post).
A query optimized with PEO applied appears to the query optimizer exactly as if the query had been written with literals instead of parameters or variables. Contradiction detection can remove entire clauses or relational operator subtrees where literal expressions like WHERE 0 = 1 appear. This facility exists because automated tools often generate such SQL.
It is not always safe to apply PEO, but the exceptions are not officially documented. One exception is where variable assignment occurs (others exist, such as where the parameter appears in an OPTIMIZE FOR clause). My understanding is that variable assignment involves a good deal of complex legacy behaviour, with occasionally odd semantics, preserved for backwards compatibility reasons. It would be impractical to guarantee that PEO would operate correctly in all circumstances, so it is disabled for that case.
PEO is an opportunistic facility that goes beyond the documented behaviour of OPTION (RECOMPILE). It can deliver significant performance benefits in many cases, but it is not officially documented. One might usefully regard it as a bonus feature - nice when you get it, but there are no refunds in case of disappointment.
In your example, where PEO cannot be applied, elimination of subtree execution is provided by start-up filters (which are documented). The Filter operators shown in the "unoptimized" plan are start-up filters that only execute their subtree if the start-up predicate evaluates to true.
The lack of a "seek plan", in the PEO context, is often due to optimizations that can only be performed (either due to safety issues or implementation limitations) when a literal value is present. This literal may appear in the original text, or it may have been substituted in via PEO. An example of this is the optimization rule SelOnSeqPrj, which allows a predicate to move past a sequence function like ROW_NUMBER when safe, but only when a literal value is available.
A SQL Server 2017 repro of the code in the question does not produce the extra Compute Scalar and implicit conversion mentioned in the question. It appears the query used to produce that plan was different to that given in the question. Or perhaps the instance or database has some important configuration or option that was not specified. In any case, I was unable to reproduce it.
The OPTION (RECOMPILE) hint is never ignored. The only implicit conversion in the query is from the internal bigint result of COUNT(*) to integer as required by COUNT (as opposed to COUNT_BIG).
Depending on the requirements and restrictions of the real application behind the question, you may need to employ dynamic SQL, or some other solution. Feel free to ask a new question about potential solutions to the underlying problem, if it can be expressed in a way that is suitable for our Q & A format.

The brief answers to your questions are:

Yes.
I cover it in Parameter Sniffing, Embedding, and the RECOMPILE Options.
No.


linked server connection error in Sql server When I tried to execute query I got the below error.

SELECT   *
FROM OPENQUERY([TESTORACLE], 'select *   from TESTTABLE')

  Cannot initialize the data source object of OLE DB provider "MSDASQL"
  for linked server "TESTORACLE".

I have created DSN and used below query to create linked server.

EXEC sp_addlinkedsrvlogin
@rmtsrvname    = 'TESTORACLE'
,@useself       = 'False'
,@locallogin    = NULL
,@rmtuser       = 'USERNAME'
,@rmtpassword   = 'PASSWORD';

EXEC sp_addlinkedserver
@server        = 'TESTORACLE'
,@srvproduct    = 'ORCLELINK'
,@provider      = 'MSDASQL'
,@datasrc       = 'ORCLELINK'

After that I tested the linked server and it worked fine.
When I execute a query it throws access denied exception so I enable Allow In process to the provider.

After that it throws above error and the test connection also not works.

Not sure why the connection fails when I enable Allow in process.

Please help me to resolve the error.

Thanks,

First, install the correct Oracle drivers.  You want the latest version of the Oracle Data Access Components (ODAC), and you want the XCopy deployment.  They are available here:
64-bit Oracle Data Access Components (ODAC) Downloads
http://www.oracle.com/technetwork/database/windows/downloads/index-090165.html

One you download and unzip this into a folder run the following command from that folder:

C:\Users\dbrowne\Downloads\ODAC121010Xcopy_x64>.\install.bat oledb c:\oracle\odac64 odac64 true

Then you need to add two folders to your system path:

c:\oracle\odac64 and c:\oracle\odac64\bin

Then you must reboot for the system path change to be visible by services like SQL Server.

After reboot you're ready to create and test the linked server.
First configure the Oracle OleDB provider to run inside the SQL Server process, and configure it to accept parameters.

exec master.dbo.sp_MSset_oledb_prop 'ORAOLEDB.Oracle', N'AllowInProcess', 1

exec master.dbo.sp_MSset_oledb_prop 'ORAOLEDB.Oracle', N'DynamicParameters', 1

Then create the linked server definition.  Instead of a TNSNames alias, use an EZConnect identifier.  Here I'm specifying an IP address and a SID to connecto to an Oracle Express instance running on a VM:

exec sp_addlinkedserver N'MyOracle', 'Oracle', 'ORAOLEDB.Oracle', N'//172.16.8.119/xe', N'FetchSize=2000', ''
exec master.dbo.sp_serveroption @server=N'MyOracle', @optname=N'rpc out', @optvalue=N'true'
exec sp_addlinkedsrvlogin @rmtsrvname='MyOracle', @useself=N'FALSE', @rmtuser=N'system',@rmtpassword='xxxxxx'

Now you're ready to test.  You configured the linked server for 'rpc out' so we can send a simple passthrough query to test connectivity:

exec ('select 1 a from dual') at MyOracle

That's it.


PostgreSQL pl/perl trigger, differentiate null vs empty I've been trying to speed up a generic auditing trigger that I had written in pl/pgsql some time back.  On update, it generates a list of columns in the table being updated and inserts rows in the auditing table recording any changes (by table, column, data before, data after, etc).  The same trigger function is used across multiple tables.

I'm toying with pl/perl as it seems to be much faster for the task at hand, but I seem to have run into a problem on differentiating between NULL and empty string ('') value in the database.

In my mind if a column goes from NULL to an empty string (or vice versa), that's a change that I need to record.  However, using the available new/old column references ($_TD->{new/old}{$columnName}), I can't seem to differentiate between columns that are actually null and those that contain the empty string.  Columns that I know are null are being caught by both the empty and the undef checks, as are columns that I know are empty.

    if($_TD->{new}{$column} eq '') {
        elog(NOTICE, "New value in column $column is empty");
    }
    if($_TD->{old}{$column} eq '') {
        elog(NOTICE, "Old value in column $column is empty");
    }
    if($_TD->{new}{$column} eq undef) {
        elog(NOTICE, "New value in column $column is not defined");
    }
    if($_TD->{old}{$column} eq undef) {
        elog(NOTICE, "Old value in column $column is not defined");
    }

I suspect that I'm doing something silly here, but maybe I'm trying to do something that I simply can't do.  Any advice?

Edit - Using Postgres 8.4.4 for what it's worth

Edit - After looking at filiprem's post below (and lots more testing), I ended up with this, which seems to be working:

    my %newrow = %{$_TD->{new}};
    my %oldrow = %{$_TD->{old}};

    my $valChanged;
    while (($column,$value) = each %newrow) {

        $valChanged = 0;

        if($newrow{$column} ne $oldrow{$column}) {
            $valChanged = 1;
            elog(NOTICE, "Values in column $column differ.  New: $_TD->{new}{$column}, Old: $_TD->{old}{$column}");
        }
        elsif(!defined($newrow{$column}) && defined($oldrow{$column})) {
            elog(NOTICE, "New row contains nulled out field");
            $valChanged = 1;
        }
        elsif(defined($newrow{$column}) && !defined($oldrow{$column})) {
            elog(NOTICE, "New row contains newly populated field");
            $valChanged = 1;
        }

        if($valChanged) {
            ### Update audit table
        }
    }

It catches the difference between empty strings and NULLs and logs them all appropriately in the audit table.

You did not specify PostgreSQL version. On 9.0.5 I have observed the same behavior (not sure if it's a bug, see comments below).

This is easy to workaround - you can first test for definedness to catch NULL, and if it passed, test for empty string.

if ( not defined $_TD->{ new }{ $column } ) {
    elog( NOTICE, "New value in column $column is not defined" );
}
elsif ( $_TD->{ new }{ $column } eq '' ) {
    elog( NOTICE, "New value in column $column is empty" );
}
if ( not defined $_TD->{ old }{ $column } ) {
    elog( NOTICE, "Old value in column $column is not defined" );
}
elsif ( $_TD->{ old }{ $column } eq '' ) {
    elog( NOTICE, "Old value in column $column is empty" );
}


Type of Foreign Keys for a Dimensional Model Fact Table? I've been reading about dimensional models, and in particular fact tables, but I haven't really seen one in action (i.e. the actual content), just diagrams of the table structures and column names

From my reading it sounds like you only put the primary (i.e. primary auto incremented) keys from the dimension tables into the fact table, and not any of the actual dimensional information in the table

Is this accurate? The example I have in my mind looks like this:

+---------+------------+-------------------+
| date_fk | product_fk | units_sold (fact) |
+---------+------------+-------------------+
|  3      | 12         |  1154             |
|  3      | 50         |  484              |
+---------+------------+-------------------+

Where date_fk is the primary auto incremented key for the dimDate dimension table, product_fk is the primary auto incremented key for the dimProduct table, and units_sold is the actual fact, the number of units sold that day for that product

Please confirm that this is the correct method; my dimension tables also uses numeric natural keys so it's tempting to use those instead of the adjacent primary keys to avoid having to do JOINs for every query, however I am committed to using dimensional modelling so I need to do it right


How to create a new column in SELECT CASE when string contains Arabic words? I have a problem with a select case when statement, I would like to add a new column in the select case when statement, I got the result but with ????? because I have set the column to Arabic words, I have tried to convert the content of the new column to nvarchar(55) unfortunately, I got the same result.
How can I get the correct result?
SELECT case when ( CAST(o.startTime as time(7)) > cast(Start as time(7)))
          then  cast('قدوم' as nvarchar(55))
          else cast('رجوع' as nvarchar(55)) end as stat,
       userId, FirstName
from   Users, TimeTable

Since you are using Unicode / UTF-16 (i.e. NVARCHAR), you do not need either the CAST or even an Arabic_* collation. The only requirement for string literals is to prefix them with a capital-"N":
SELECT 'قدوم' COLLATE Arabic_CI_AS, '-o-' AS [-o-], 'رجوع' COLLATE Arabic_CI_AS;
-- ???? -o- ????

SELECT N'قدوم' COLLATE French_CI_AS, '-o-' AS [-o-], N'رجوع' COLLATE French_CI_AS;
-- قدوم -o- رجوع

(the -o- field is used as a separator to prevent the Bi-Direction formatting, inherent with Arabic and Hebrew code points, from displaying the second column on the left in the T-SQL comment below the query when rendered in HTML)
When using NVARCHAR (including NCHAR and the should-never-be-used NTEXT), the encoding is always UTF-16, hence the character set never changes (it's always Unicode, which is a single character set). In this case, the collation mainly affects sorting and comparisons (which are not being done here).
If you were using VARCHAR, then an Arabic collation would be needed in order to set the code page in order to support the Arabic characters (Windows Code Page 1256).
The reason why your initial 'قدوم' returned ???? is that the string literal was not prefixed with N, making it a VARCHAR string, in which case it was converted to the code page associated with the default collation of the current database you were using, which clearly was not an Arabic collation. This conversion happens as the query is initially parsed, which means that your string was already ???? before the cast operation converted it to NVARCHAR. If you had been using a database that had an Arabic default collation, then your string literal would have worked even without the N prefix, and the CAST to NVARCHAR would have been just as unnecessary.

For more information on working with strings / encodings / Unicode / collations, please visit my site: Collations Info

P.S. I would recommend that you not be doing old-style JOINs such as FROM TableA, TableB WHERE TableA.JoinColumn = TableB.JoinColumn. While that syntax has not been deprecated, the related outer join syntax — *= and =* — was actually removed in SQL Server 2005. So, for consistency (and personally, I would also throw in readability), you should instead use the INNER JOIN clause. Also, it's always best to prefix object names with their Schema name. The end result is:
FROM dbo.TableA
INNER JOIN dbo.TableB
        ON TableB.JoinColumn = TableA.JoinColumn


Flags UPDATE optimization for MVCC We have a table with 200k rows where we change some flags multiple times per day.
If i have understood correctly an UPDATE on postgres is a DELETE+INSERT on disk.

I'm wondering if this is highly inefficient for our use case. What if each tuple has a big size? Are the entire tuples written again?

I was thinking to move those status flags on a separate table so that we rewrite only small tuples reducing useless i/o. Is this a correct approach or i'm on the wrong track?

Community wiki answer:

You're basically right.

It will also help if you define your separate tables with a certain amount of free space (see fillfactor) so that PostgreSQL can perform Heap-only tuples updates.

See Increase the speed of UPDATE query using HOT UPDATE (Heap only tuple) by Anvesh Patel.


Rebuilding indexes on a list of tables I have a large number of tables that may change, may have infrequent changes in index, etc.  I may well hand this off to somebody else and I don't want indexes to become overly fragmented, slowing the system to a crawl.  So, I began hunting and found this:

http://sqlmag.com/querying/querying-sysindexes-system-table

With some changes to the "WHERE" criteria I was able to isolate my table of tables/indexes

SELECT USER_NAME(OBJECTPROPERTY(i.id, 'OwnerID')) OwnerName,
       OBJECT_NAME( i.id ) [TableName],
       i.name AS [IndexName],
       CASE INDEXPROPERTY( i.id , i.name , 'IsClustered')
             WHEN 1 THEN 'YES' ELSE 'NO' END [IsClustered],
       CASE INDEXPROPERTY(i.id, i.name, 'IsUnique')
                WHEN 1 THEN 'YES' ELSE 'NO' END [IsUnique],
      STATS_DATE( i.id , i.indid ) AS LastUpdatedDate
FROM sysindexes i
WHERE USER_NAME(OBJECTPROPERTY(i.id, 'OwnerID')) = 'dbo'
    and INDEXPROPERTY( i.id , i.name , 'IsClustered') = 1
ORDER BY OwnerName, TableName, IndexName

I know that I can loop through this result set using dynamic SQL and apply this:

ALTER INDEX Index_Name ON Table_Name REBUILD PARTITION = ALL WITH (
    PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, ALLOW_ROW_LOCKS  = ON,
    ALLOW_PAGE_LOCKS  = ON, ONLINE = OFF, SORT_IN_TEMPDB = OFF )

Where "Index_Name" and "Table_Name" are substituted for each time the line is run, and while this seems fine and good I can't help but wonder if there is a better solution.  Can I avoid cursors and dynamic SQL in this case?

(Edited to include earlier comments for clarity of the answer.)

A better plan for database maintenance is to implement Ola Hallengren's Maintenance Plan. This is free and can be downloaded from https://ola.hallengren.com

You can configure the settings to suit your needs. Brent Ozar suggests some minor parameter tweaks at:  http://www.brentozar.com/archive/2014/12/tweaking-defaults-ola-hallengrens-maintenance-scripts/

Because you are using SQL Server Express, which has no SQL Server Agent, you will need to use an alternate approach to running those jobs.

If you go to Ola Hallengren's site, https://ola.hallengren.com/frequently-asked-questions.html, you can look at his answer:

Therefore, the execution of the stored procedures must be scheduled by using cmd files and Windows Scheduled Tasks. Follow these steps.

Download MaintenanceSolution.sql.
Execute MaintenanceSolution.sql. This script creates the stored procedures that you need.
Create cmd files to execute the stored procedures; for example a script:

sqlcmd -E -S .\SQLEXPRESS -d master -Q "EXECUTE dbo.DatabaseBackup
@Databases = 'USER_DATABASES', @Directory = N'C:\Backup',
@BackupType = 'FULL'" -b -o C:\Log\DatabaseBackup.txt
In Windows Scheduled Tasks, create tasks to call the cmd files.
Schedule the tasks.
Start the tasks and verify that they are completing successfully.

Mladen Prajdić also wrote a solution some years ago using Service Broker, which is available in SQL Server Express.  See the links at:

http://www.sqlteam.com/article/scheduling-jobs-in-sql-server-express
http://www.sqlteam.com/article/scheduling-jobs-in-sql-server-express-2

If you like that solution then you should be able to keep all the configuration inside the SQL Server.


Partitioned Views - Storage of Indexes I have a single table that contains 4 billions rows that I plan on splitting up in to separate tables for each Quarter of the year. Then I want to create a partitioned view across all tables.

At the moment I have one filegroup for data (where most of the data resides) and one filegroup for non-clustered Indexes (I have been slowly migrating indexes across to this filegroup)

The data filegroup and non-clustered indexes filegroup are located on different disks.

The individual tables will have primary keys and non-clustered Indexes added to them. Where should I put these? (In terms of filegroups)  Should I create filegroups for each table, for each type of index etc?

Note: I can't use Data Partitioning as we don't have Enterprise edition.

If you're going with traditional partitioned views with each table containing a different yearly quarter, then you have a few levels of separation here:

Each Table
Each Index on Each Table

So in other words, say you have 10 tables that the partitioned view accesses, and you have a clustered index and a nonclustered index on each table.  In this case you are looking at 20 different data structures that you need to place accordingly.

As to where to put each of these will largely depend on your workload.  For instance, if only the most recently two quarter tables get the most report queries and data modification, you'll want to ensure those are separate.  But in the same respect, you could benefit from each of those tables having their indexes on separate disks.

I say disks here because filegroups are logical groupings of database data files.  Even if you split each table and each index on their own filegroup (exaggerated to illustrate a point) but all the containing data files are on the same set of physical disks then you won't really see any underlying I/O performance gain.  It's really just administrative at that point.  You can't always divide the physical layer and the logical layer when squeezing performance out of a design.

So you need to visually lay out the physical disk separation, what type of common workload you will be receiving, and design your data placement in that manner.


Select Distinct for Multiple columns SELECT top 50 column1, column2,column3,column4
FROM (Select Distinct column1,column2, column3, column4 from Table
Where Column2>= '2016-07-01' )
Order By Column2;

The result is not what I am expecting. I want to make columns Column1, Column2, Column3, Column4 all distinct, meaning each column should have no two identical values. Is there a way to do this?

I don't think it is possible to get your expected result set using a single Select-statement. IMHO you must create an empty copy of the result table and then use following single-row Insert to prevent any duplicate values. Repeat this until you get the expected n rows.

insert into result_table
select top 1 column1, column2,column3,column4
from base_table as t1
where Column2>= '2016-07-01'
  and not exists
  ( select *
    from result_table as t2
     where Column2>= '2016-07-01'
       and
        (  t1.column1 = t2.column1 -- unique data in each column
        or t1.column2 = t2.column2
        or t1.column3 = t2.column3
        or t1.column4 = t2.column4
        )
  )
 Order By Column2;


pgAdmin3 can't connect properly to Postgres 9.2 I have installed pgadmin3 version 1.10.2, however when it connects to PostgreSQL server version 9.2, it says:

  Warning:

  This version of pgAdmin has only been tested with PostgreSQL version
  8.4 and below and may not function correctly with this server. Please upgrade pgAdmin.

Then it will throw this error:

  An error has occurred:

  ERROR:  column "datconfig" does not exist LINE 1: ...b.dattablespace
  AS spcoid, spcname, datallowconn, datconfig,...

etc.

Also I am quite stuck with my working version of pgAdmin3 as I am using Ubuntu Lucid. So is there a work-around for pgAdmin3 to work properly keeping my curent version of pgAdmin3?

Other than not using features that cause errors, no, there is no workaround. Since in this case one of the features that causes an error appears to be listing tablespaces during startup, you're out of luck.

Just compile a newer version of PgAdmin-III from sources or use the command-line psql client that comes with PostgreSQL 9.2.

PostgreSQL's system catalogs aren't 100% compatible from version to version. That's intentional; if they had to be exactly the same it'd be very hard to make changes and improvements to PostgreSQL. Programs that use the system catalogs should be prepared to require updates when a new PostgreSQL version comes out. Most tools that use the catalogs - like psql, PgAdmin-III, pg_dump, etc - do so because it's the only way to get detailed information about some of the inner workings of the system. You just need to update them when you update the server.


Why doesn't MariaDB server recognise Server Audit settings in config file? I'm trying to implement the Server Audit plugin that is built-in with MariaDB 10.4.  I've added the settings to the my.ini config file, running on Windows 10.

[server]
...
#Audit Settings
server_audit_logging=ON
server_audit_output_type=FILE
server_audit_events=CONNECT, QUERY, TABLE
server_audit_excl_users=    #Default=blank
server_audit_file_path=D:/Databases/MariaDB/server_audit.log
server_audit_file_rotate_now=   #Default=0; choose 1 to create new log file, restart server, then reset to 0.
server_audit_file_rotate_size=  #Default=1000000
server_audit_file_rotations #Default=9 (range 0 to 999)
server_audit_incl_users=    #Default=blank
server_audit_query_log_limit=   #Default=1024
server_audit_syslog_facility=   #Default=LOG_USER; not relevant when using log to file.
server_audit_syslog_ident=  #Default=mysql-server_auditing; not relevant when using log to file.
server_audit_syslog_info=   #Default=blank; not relevant when using log to file.
server_audit_syslog_priority=   #Default=LOG_INFO; not relevant when using log to file.

But I can't start the server because of this error:

2020-04-17 19:26:20 0 [ERROR] D:\Program Files\MariaDB\MariaDB 10.4\bin\mysqld.exe: unknown variable 'server_audit_logging=ON'

I've tried putting the settings under [mariadb] and [mysqld] just in case that helped - it didn't.

What am I doing wrong here?

From the documentation:

  Although the plugin's shared library is distributed with MariaDB by
  default, the plugin is not actually installed by MariaDB by default.

So you need to install the plugin - either execute:

INSTALL SONAME 'server_audit';

Or put it in your option file:

[mariadb]
...
plugin_load_add = server_audit


Choice of clustered key when almost all columns would be part of a unique index anyway I am creating the following table

CREATE TABLE dbo.COMM_SHELF_BLOCK_ACCESS
    (
    KeyId uniqueidentifier NOT NULL ROWGUIDCOL CONSTRAINT DF_COMM_SHELF_BLOCK_ACCESS_KeyId default NEWSEQUENTIALID(),
    EmployeeGuid uniqueidentifier NOT NULL,
    PracticeVid smallint NOT NULL,
    ShelfGuid uniqueidentifier NOT NULL,
    CONSTRAINT PK_COMM_SHELF_BLOCK_ACCESS PRIMARY KEY CLUSTERED (KeyId)
    )  ON [PRIMARY]
GO

CREATE UNIQUE NONCLUSTERED INDEX UX_COMM_SHELF_BLOCK_ACCESS
    ON dbo.COMM_SHELF_BLOCK_ACCESS (ShelfGuid, EmployeeGuid, PracticeVid)

GO

I never use KeyId in my program, it is only for merge replication. The most common query my application will be using this table for will be

--This will be passed in parameters to the SP
declare @practiceVid smallint = 0
declare @employeeGuid uniqueidentifier = 'C413A410-E13E-4647-9D68-2A38FBC45906'

--actual query
select * from COMM_SHELF
where COMM_SHELF.SHELF_GUID not in
    (
        select ShelfGuid
        from COMM_SHELF_BLOCK_ACCESS
        where EmployeeGuid = @employeeGuid
            and PracticeVid = @practiceVid
    )

Is the index structure of my new table appropriate? The big thing on my mind is I am duplicating almost my entire table in that unique index. The other thought I had doing was to structure the table like the following

CREATE TABLE dbo.COMM_SHELF_BLOCK_ACCESS
    (
    KeyId uniqueidentifier NOT NULL ROWGUIDCOL CONSTRAINT DF_COMM_SHELF_BLOCK_ACCESS_KeyId default NEWSEQUENTIALID(),
    EmployeeGuid uniqueidentifier NOT NULL,
    PracticeVid smallint NOT NULL,
    ShelfGuid uniqueidentifier NOT NULL,
    CONSTRAINT PK_COMM_SHELF_BLOCK_ACCESS PRIMARY KEY NONCLUSTERED (KeyId),
    CONSTRAINT CK_COMM_SHELF_BLOCK_ACCESS UNIQUE CLUSTERED (ShelfGuid, EmployeeGuid, PracticeVid)
    )  ON [PRIMARY]
GO

making those 3 columns I am interested in the clustered key. However this violates the "Keep the clustered key narrow" and the "Keep the clustered key sequential" guidelines I have read everywhere.

Which of these approaches is the correct way to implement this? If neither are correct please tell me what the correct way to approach this is.

The big reasons for the "Keep the clustered key sequential" advice is all around insert/update/delete operations and fragmentation.  As you make changes to the values (insert new ones out of order, updating an old value that causes it to move, deleting a value) SQL has to move data around on the pages and do page-splits to keep the data in order (the whole purpose of a clustered index).  This is going to take time and create fragmentation which will slow everything down over time.

If you are doing very few updates (including inserts/deletes) or rebuild your table every time the updates are done (truncate & then batch load) then a clustered index on values that aren't naturally in order can work for you.  Otherwise stick with your sequential value.

As far as your index goes, based on just that one query, I would prefer this:

CREATE INDEX ix1 ON COMM_SHELF_BLOCK_ACCESS(EmployeeGuid, PracticeVid) INCLUDE (ShelfGuid)

You don't want the ShelfGuid up front because then it has to do an index scan to find the EmployeeGuid that it wants.  If you aren't actually going to be searching on ShelfGuid and just want to return the value then take it out of the tree level of the index entirely and just put it in the leaf level (by INCLUDING it).

If you have the room for the index (which I'm guessing you do) then don't worry about it being almost the same size as the table.  It's still a index that sorts your data in a specific order.  I'm guessing your goal here is to speed up your query not to conserve space.  If you are tight on space and can't afford more then you have a different issue and I would consider redoing your table with something other than GUIDs :).


Find weighted random row. Or split 1 row into many based on column value I want to query the DB and pick a random item weighted by the count. Something like so:

SELECT * FROM (-- SUBQUERY THAT GENERATES WEIGHTED TABLE --)
    ORDER BY ct * RAND() LIMIT 1

I'm thinking the following might be a good approach.  I have a table that looks something like this:

| id  | item   | count |
| --- | ------ | ----- |
| 1   | item a | 3     |
| 2   | item b | 2     |
| 3   | item c | 4     |

How can I query the database so that 1 row is shown per count? I.e. like this:

| id  | item   | count |
| --- | ------ | ----- |
| 1   | item a | 3     |
| 1   | item a | 3     |
| 1   | item a | 3     |
| 2   | item b | 2     |
| 2   | item b | 2     |
| 3   | item c | 4     |
| 3   | item c | 4     |
| 3   | item c | 4     |
| 3   | item c | 4     |

In case it helps, I've created a DB fiddle: https://www.db-fiddle.com/f/wRZgBYkDM18c5fk7tgkBkA/0

I'm using MariaDB 10.0.

Instead of proliferating the number of rows, let's add a cumulative total:

| id  | item   | count | cumm_tot
| --- | ------ | ----- |
| 1   | item a | 3     |  3
| 2   | item b | 2     |  5
| 3   | item c | 4     |  9

Then get the last number, 9, for use below.
Index the new column.
Fetch random item thus:

WHERE    cumm_tot > 9 * RAND()
ORDER BY cumm_tot
LIMIT 1

To set the column and get the 9, perform this task:

SET @c := 0;
UPDATE tbl
    SET cumm_tot = (@c := @c + `count`)
    ORDER BY id;
-- @c will now be the total (9)

Whenever you add items or change the weights, rerun the two statements above.

(This code should work on any version of MySQL/MariaDB.  There may be a "window function" that works better in new versions.)


Create rows groups by using subgroups limits I had originally posted this in SO but reasoned that it would be more appropriate here.

I have a view in an sqlite database such as this:

SQL Fiddle
(http://www.sqlfiddle.com/#!5/ae95b/1)

This represents a list of boxes and items that go into each box. The items are always ordered by box_start and the ranges box_start and box_end never overlap.

As shown in the first row there's a box which can store items with codes from 1 inclusive to 3 inclusive. For example the items that would go in the box A would be 'a' and 'b'.
The box-items grouping is denoted by the coloring, rows with the same color means that those are a group.
There are also items without a designated box (box label is null), such as items 'c', 'f', 'g', 'h'.

I need to write a query, if it is possible at all, to create ad-hoc boxes for items without a box and group contiguous items into the same box as follows:

As seen in the image the item 'c' and its corresponding box were labeled with '4-4' and the previously unassigned items 'f', 'g', 'h' are now grouped under the same box labeled '8-10' which corresponds to 'min(box_start of f,g,h)-max(box_end of f,g,h)'

I not sure how to do this in SQLite. I thought about using somekind of recursive query with CTEs but don't have a clue how to do it.

After some work I have the following query:

select
    min(box_start) as box_start,
    box_end,
    box_label,
    is_box,
    item_code,
    item
from
    (
        select
            box_start,
            box_end,
            box_label,
            is_box,
            item_code,
            item
        from
            table1
        where
            box_label is not null

        union all

        select
            table1.box_start as box_start,
            table1.box_end as box_end,
            intervals.A || '-' || intervals.B as box_label,
            table1.is_box as is_box,
            table1.item_code as item_code,
            table1.item as item
        from
            (
                select
                    box_start,
                    box_end,
                    box_label,
                    is_box,
                    A,
                    B,
                    max(max_interval_size) as max_interval_size
                from
                    (
                        select
                            box_start,
                            box_end,
                            box_label,
                            is_box,
                            A,
                            B,
                            max(interval_size) as max_interval_size
                        from
                            (
                                select
                                  fixed_table.box_start as box_start,
                                  fixed_table.box_end as box_end,
                                  fixed_table.box_label as box_label,
                                  fixed_table.is_box as is_box,
                                  fixed_table.box_start as A,
                                  windowed_table.box_end as B,
                                  (windowed_table.box_end - fixed_table.box_start) as interval_size
                                from
                                  table1 fixed_table
                                  join table1 windowed_table on
                                    fixed_table.box_start <= windowed_table.box_end
                                where
                                  interval_size >= 0
                                  and fixed_table.box_label is null
                                  and windowed_table.box_label is null
                                  and fixed_table.is_box = 'FALSE'
                                  and windowed_table.is_box = 'FALSE'

                                except

                                select
                                  without_a_box.*
                                from
                                    (
                                        select
                                          fixed_table.box_start as box_start,
                                          fixed_table.box_end as box_end,
                                          fixed_table.box_label as box_label,
                                          fixed_table.is_box as is_box,
                                          fixed_table.box_start as A,
                                          windowed_table.box_end as B,
                                          (windowed_table.box_end - fixed_table.box_start) as interval_size
                                        from
                                          table1 fixed_table
                                          join table1 windowed_table on
                                            fixed_table.box_start <= windowed_table.box_end
                                        where
                                          interval_size >= 0
                                          and fixed_table.box_label is null
                                          and windowed_table.box_label is null
                                          and fixed_table.is_box = 'FALSE'
                                          and windowed_table.is_box = 'FALSE'
                                    ) as without_a_box
                                    ,
                                    (
                                        select distinct
                                            with_box.box_start as start_with_box
                                         from
                                            table1 with_box
                                         where
                                            with_box.is_box = 'FALSE'
                                            and with_box.box_label is not null
                                    ) as items_inside_a_box
                                where
                                    items_inside_a_box.start_with_box > without_a_box.A
                                    and items_inside_a_box.start_with_box < without_a_box.B
                            ) as without_intervals_that_intersect_boxed_items
                        group by
                            A
                    ) as final
                group by
                    B
            ) as intervals
            join table1 on
                table1.box_start >= intervals.A
                and table1.box_end <= intervals.B
                and table1.box_label is null
    )
group by
    box_label,
    is_box,
    item_code,
    item
order by
    box_start,
    item_code

SQL Fiddle: http://www.sqlfiddle.com/#!7/4a643e/142

Even though it seems to do its job I am not sure is correct in all cases and if it won't be a performance bottleneck.

I hope someone has a better solution


Upgrade SQL 2008R2 to SQL 2016 on windows server 2012R2 I am checking for expertise opinion for IN PLACE UPGRADE from SQL 2008R2 to 2016. We have tested side by side which looks good in dev env's.

Considering production can i have some opinions on how feasible and safe is to use in place upgarde:

for standalone server upgrade
for cluster SQL FCI upgrade

Well, once you upgrade there is no going back. So that's a big danger in doing in place upgrades and why so many people don't do it. Some straight up won't do it for production. In place also has more downtime.

Unless you're on a modern OS (2016 or 2019) I wouldn't do an in-place upgrade as it limits your supportability given WS 2012R2 ended mainstream support last year.


Determine which SQL drivers an application uses to connect to SQL server I want to find out what SQL driver a third-party application (or any application for that matter) uses to connect to a SQL Server instance in production.  It can be tough to get a fast answer from a vendor and we need to know this so that we can enable TLS 1.2 communication.

I've been told that extended events can solve my problem.  Thank you.

The article mentioned is almost what you want, i think

If you join sys.dm_exec_connections with sys.dm_exec_sessions, you get what you need from columns program_name and client_interface_name:

    SELECT c.connect_time
        ,s.login_time
        ,s.host_name
        ,s.login_name
        ,c.protocol_type
        ,driver_version = CASE SUBSTRING(CAST(c.protocol_version AS BINARY(4)), 1, 1)
            WHEN 0x70
                THEN 'SQL Server 7.0'
            WHEN 0x71
                THEN 'SQL Server 2000'
            WHEN 0x72
                THEN 'SQL Server 2005'
            WHEN 0x73
                THEN 'SQL Server 2008'
            WHEN 0x74
                THEN 'SQL Server 2012+'
            ELSE 'Unknown driver'
            END
        ,s.program_name
        ,s.client_interface_name
        ,s.is_user_process
    FROM sys.dm_exec_connections AS c
    JOIN sys.dm_exec_sessions AS s ON c.session_id = s.session_id


SQL Server : database free space vs data file free space I don't understand well why the available space in database is 0% while in file is 98% available. Can you explain or advise? (SQL Server 2019, SSMS the newest, one datafile)

There are apparently errors in the calculations in SSMS.

Shrink Database dialog

Currently allocated space: show the allocated space for both data and log. Correct.

Available free space: show free space for only data, doesn't include log. Incorrect.

The calculating of the free space is off. It divides the full database size (including log) with the free space for the data file (only). Also, it presents the full database size (including log) and for free space, it only show free space for data.

Shrink File dialog

Referring to data file. This is inconsistent. For one of my databases, is show the correct info for the primary data and only file. For a different database (stack overflow 10 GB), it is way off with free space (both number and percentage). For the SO db, it shows only about 600 MB used, when in fact it is about 1.6 GB. My guess is that there is an overflow in a variable in the host language within SSMS causing a wraparound (if it were in TSQL you'd get an error instead)

I.e. disregard the GUI. Collect info from some more correct query/method (what Niels suggested for instance - I use my own sp_dbinfo for these things). and then use the DBCC SHRINK commands directly (preferably DBCC SHRINKFILE).

And, of course don't shrink unless you really really really need that disk space, it is a significant amount of disk space and you are willing to pay the price for shrinking.


How to use column alias to calculate other column value My query is

SELECT
    (`house_rent`+`conveyance`+`medical`+`dearness`+`others_allowances`) AS earnings
    ,(`income_tax`+`pro_tax`+`emp_state_insu`+`absence_fine`+`others_deductions`) AS deductions
   ,(earnings - deductions) AS net_salary
FROM
    salary

and im getting an error that unknown column earnings and deductions because these are column alias not column name ..
Any solution?
Thanks in advance

Additionally to previous answers.

1) You can try to use user-defined variables:

SELECT
    @earnings := (`house_rent`+`conveyance`+`medical`+`dearness`+`others_allowances`) AS earnings ,
    @deductions := (`income_tax`+`pro_tax`+`emp_state_insu`+`absence_fine`+`others_deductions`) AS deductions,
    @earnings - @deductions AS net_salary
FROM
    salary
    -- , (SELECT @earnings:=0, @deductions:=0) vars

Documentation do not guarantee the calculations order, but in practice all calculations are performed from left to right.

2) If your MySQL version is 8+, you can use CTE:

WITH cte AS (
SELECT
    (`house_rent`+`conveyance`+`medical`+`dearness`+`others_allowances`) AS earnings,
    (`income_tax`+`pro_tax`+`emp_state_insu`+`absence_fine`+`others_deductions`) AS deductions
FROM
    salary
)
SELECT earnings, deductions, (earnings - deductions) AS net_salary
FROM cte;


PostgreSQL: how to create both sequence and table simultaneously? I need to create a table with a sequence associated to the gid column that would start from a given value, say 300. I'd like to know if there is a shorter way to achieve that:

CREATE TABLE mytable (gid INTEGER NOT NULL, size INTEGER);

CREATE SEQUENCE mytable_gid_seq MINVALUE 300;

ALTER TABLE mytable ALTER COLUMN gid SET DEFAULT nextval('mytable_gid_seq'::regclass);

Thanks for help!

You can create the sequence implicitely through the serial (pseudo) type and then change its current value:

CREATE TABLE mytable (gid serial NOT NULL, size INTEGER);
select setval('mytable_gid_seq', 100);

or alternatively using

CREATE TABLE mytable (gid serial NOT NULL, size INTEGER);
alter sequence mytable_gid_seq restart with 100;

This is slightly different than defining a minvalue - but that would only matter if the sequence was set to wrap. This version also creates a link between the sequence and the table, so that the sequence is automatically dropped when the table is dropped (in your script you would need to alter sequence mytable_gid_seq owner to mytable.gid);


Is it still best practice to avoid using the default ports for SQL Server? Historically, it has been recommended not to use the default ports for connections to SQL Server, as part of security best practice. On a server with a single, default instance, the following ports would be used by default:

SQL Server service - Port 1433 (TCP)
SQL Server Browser service - Port 1434 (UDP)
Dedicated Admin Connection - Port 1434 (TCP)

QUESTIONS:

Is this advice still relevant?
Should ALL of the above ports be changed?

Historically, it has been recommended not to use the default ports for connections to SQL Server, as part of security best practice.

Which was asinine then and still asinine now. Security through arguably obscurity isn't security at all.

  Is this advice still relevant

IMHO it was never relevant. It was required for some compliance purposes because the people drafting up those compliances did not understand what they were doing, again, IMHO.

  Should ALL of the above ports be changed?

I wouldn't change any.


Prevent deleting last child CREATE TABLE Parent ( ParentID int NOT NULL PRIMARY KEY,
                      ParentName varchar(50) NULL )
CREATE TABLE Child  ( ChildID int NOT NULL PRIMARY KEY,
                      ParentID int NOT NULL REFERENCES Parent (ParentID),
                      ChildName varchar(50) NULL,
                      IsFavorite BIT NOT NULL )

The first desire is that I would like to prevent the situation of childless parents. Obviously when the pair is first created the Parent will have no Child, and I don't want to prevent the insert itself; but I want to outright forbid prevent deleting the last child. (For business reasons Parents will never be deleted.)

As a second desire, I need to prevent more than one Child with IsFavorite=1, which I know I can do with a filtered unique index.  I also would like to discourage having no Children with IsFavorite=1 -- i.e., don't permit setting IsFavorite=0 if this is the only Child of its Parent.  (From the app side, inserts should normally be done correctly with IsFavorite=1 for the first child; but I don't want the database to interfere with that.)

I'm sure I'm not the first with these desires but I couldn't find a matching question on dba or on stackoverflow. If this is a dupe, by all means mark it dupe and point me where I need to be.

You could make (ParentId,FavoriteChildId) a FK on Parent and get most of this.  However in SQL Server there's no practical way to make the relationship required, so you must allow a parent to have no favorite child, or else you wouldn't be able to insert a new parent without disabling the FK.

You can add a trigger to prevent later setting FavoriteChildId to null, but you must allow it at least in the case where a Parent has a single Child, as there would otherwise be no way to delete that Child.  So I'm not sure it's worth bothering with the Trigger.  EG:

--alter table Parent
--drop constraint fk_Parent_favorite_child
--go
--drop table if exists Child
--drop table if exists Parent

go

CREATE TABLE Parent
(
   ParentID int NOT NULL PRIMARY KEY,
   ParentName varchar(50) NULL,
   FavoriteChildId int null
)

CREATE TABLE Child
(
  ParentID int NOT NULL REFERENCES Parent (ParentID),
  ChildID int UNIQUE NOT NULL,
  constraint pk_child primary key (ParentID, ChildID),
  ChildName varchar(50) NULL
)

alter table Parent
add constraint fk_Parent_favorite_child
foreign key (ParentId,FavoriteChildId)
  references Child (ParentId,ChildId)
on delete set null

go
create or alter trigger tg_ensure_favorite_child
  on parent after update
as
begin
   if exists
   (
       select *
       from inserted i
       where i.FavoriteChildId is null
         and (select count(*) from child where ParentID = i.ParentId  ) > 1
    )
    begin
      throw 50001, 'Cannot set ChildID null for Parent with more than one Child.', 10
    end
end

go

insert into Parent(ParentID,ParentName) values (1,'Parent1')
insert into Child(ParentId, ChildId, ChildName) values (1,1,'Child1')
insert into Child(ParentId, ChildId, ChildName) values (1,2,'Child2')
update Parent set FavoriteChildId = 2 where ParentId = 1

go

update parent set FavoriteChildId = null where ParentId = 1 -- Fails
--Msg 50001, Level 16, State 10, Procedure tg_ensure_favorite_child, Line 13 [Batch Start Line 45]
--cannot set ChildID null for Parent with Children

select FavoriteChildId from Parent where ParentID = 1
--FavoriteChildId
-----------------
--2

--(1 row affected)
go

delete from Child where ParentId = 1 and ChildID = 1

go

update parent set FavoriteChildId = null where ParentId = 1 -- Succeeds


How do I dump spatial types, like point, with their SRIDs in MySQL? I am testing the point data type on Mysq 5.7 below is my java insert query.

String insertQuery1 ="INSERT INTO gtest3 SET " +
      "gDateTime='2017-07-16 01:00:00'"+
      ",vehicleID=1"+
      ",alarmTypeID=1"+
      ",latlong=Point("+floatLatitude+","+floatLongitude+")";

The values are inserted accordingly but when I got to the row it shows something like GEOMETERY -25B but when I press to edit it show me this 'POINT(5.19878 100.492)',0 . I would like is this correct with the extra 0 at the back. Can this values be used for later spatial functions.

The 0 in that context is the SRID. Unlike in PostgreSQL, you don't have a method of setting that on a point. You will in version 8 of MySQL. For the purposes of calculation, it's assumed to be 0 on everything and this is horrible. There is likely to be substantial breakage when they fix this.

  In MySQL, all computations are done assuming SRID 0, regardless of the actual SRID value. SRID 0 represents an infinite flat Cartesian plane with no units assigned to its axes. In the future, computations may use the specified SRID values. To ensure SRID 0 behavior, create geometry values using SRID 0. SRID 0 is the default for new geometry values if no SRID is specified. source

So the SRID will almost always be 0 because

it doesn't yet matter
There is no SRID mutator so you have to construct with ST_PointFromText. So the SRID will always be 0 if you construct with a point

That said, to get the SRID (future-safe) in the format provided above, you need to do this

SELECT CONCAT(ST_AsText(x), ',', ST_SRID(x))
FROM ( SELECT POINT(0,0) AS x ) AS t;

+---------------------------------------+
| CONCAT(ST_AsText(x), ',', ST_SRID(x)) |
+---------------------------------------+
| POINT(1 1),0                          |
+---------------------------------------+

This will show differently if your SRID is not 0.

SELECT CONCAT(ST_AsText(x), ',', ST_SRID(x))
FROM ( SELECT ST_PointFromText('POINT(1 1)',4326) AS x ) AS t;

+---------------------------------------+
| CONCAT(ST_AsText(x), ',', ST_SRID(x)) |
+---------------------------------------+
| POINT(1 1),4326                       |
+---------------------------------------+

Compare that to PostgreSQL,

SELECT ST_AsEWKT(x)
FROM ( VALUES (ST_SetSRID(ST_MakePoint(1,1),4326)) ) AS t(x);
      st_asewkt
----------------------
 SRID=4326;POINT(1 1)
(1 row)

Update

Taking a look at your query, there is one glaring mistake in that you do (latitude,longitude) this should be (longitude,latitude)

  So to be safe is my existing insert query is acceptable or should I add the SRID value now.

I would add the SRID value now, but not because your existing query "isn't safe". Only because you're not using placeholders and you're stringifying everything anyway.

",latlong=Point("+floatLatitude+","+floatLongitude+")";

Should be

",latlong=ST_PointFromText(POINT("+floatLongitude+" "+floatLatitude+"), 4326)";

Where 4326 is the SRID of your data..

But again, MySQL doesn't take into consideration SRID yet so be aware.

Objective facts

MySQL and MariaDB have horrible spatial support. Migrate to PostgreSQL and PostGIS, if possible.


Can MySQL show the respective timestamps in each INSERT output message? For example, after an INSERT, I see:

  Query OK, 296 rows affected (0.02 sec)
  Records: 296  Duplicates: 0  Warnings: 0

I would like to see timestamps with these lines, if possible, e.g.:

  2019-02-15 12:00:00.000 - Query OK, 296 rows affected (0.02 sec)
  2019-02-15 12:00:00.020 - Records: 296  Duplicates: 0  Warnings: 0

Can I configure MySQL to do this?


Complex key constraint: allow only if uuid is new or another column matches For my use case I am trying to get something like this to work with Azure Data Factory (ADF)

but I'm also interested in this question from a theoretical perspective. Is it possible to do something like this in a normal SQL engine like postgres?

I want to disallow inserts based on this key: (non_unique_id, timestamp) where timestamp is a unique value for that pair.

for example:

data to insert:

non_unique_id: 0cf6c19c14
timestamp: 1970-01-01 00:00:01

case 1 ALLOWED

before insert:

select non_unique_id, timestamp from tbl where non_unique_id = 0cf6c19c14;

0 results

case 2 ALLOWED

before insert:

select non_unique_id, timestamp from tbl where non_unique_id = 0cf6c19c14;

0cf6c19c14, 1970-01-01 00:00:01
0cf6c19c14, 1970-01-01 00:00:01
0cf6c19c14, 1970-01-01 00:00:01
...

case 3 NOT ALLOWED

before insert:

select non_unique_id, timestamp from tbl where non_unique_id = 0cf6c19c14;

0cf6c19c14, 2038-01-19 03:14:05
0cf6c19c14, 2038-01-19 03:14:05
0cf6c19c14, 2038-01-19 03:14:05
...

Of course there are other columns but this illustrates the simplest cases

This is a normalisation issue as timestamp is functionally dependent on non_unique_id.

So there should be another table with primary key non_unique_id and the corresponding timestamp.

In SQL Server/Azure SQL Database you can enforce this with an indexed view though.

Create a view with schema binding and definition

SELECT non_unique_id,
       timestamp,
       COUNT_BIG(non_unique_id) AS Count
FROM   dbo.YourTable
GROUP  BY non_unique_id,
          timestamp

and then put a unique clustered index on column non_unique_id in the view.


How can I DRY up a series of upserts of the same fields from multiple tables? I have a bunch of tables that all have a storenumber, extracteddate and extractedtime column.

I'm doing ETL on these tables and I want to put the last extracted date and time for each store in a single table, with an additional column for the input table name.  So I've ended up repeating the same upsert a bunch of times and really the only thing that is different is the CTE that I am using for input:

with rows as (
  select distinct on(storenumber)
         storenumber, extracteddate, extractedtime
    from storefinancialdate
  )
  insert into star.stat_etl (storenumber, filename, last_extracted, last_loaded)
    select rows.storenumber,
           'storefinancialdate',
           (rows.extracteddate + rows.extractedtime::time),
           transaction_timestamp()
      from rows
    on conflict(storenumber, filename) do update
      set last_extracted = excluded.last_extracted,
          last_loaded    = excluded.last_loaded;

with rows as (
  select distinct on(storenumber)
         storenumber, extracteddate, extractedtime
    from storeproduct
)
insert into star.stat_etl (storenumber, filename, last_extracted, last_loaded)
  select rows.storenumber,
         'storeproduct',
         (rows.extracteddate + rows.extractedtime::time),
         transaction_timestamp()
    from rows
  on conflict(storenumber, filename) do update
    set last_extracted = excluded.last_extracted,
        last_loaded    = excluded.last_loaded;

-- repeat a bunch more times...

Is there some way that I can DRY this up?  I can't pass a CTE into a function, can I?

(I get that I could call a function on each record of each CTE, one at a time. That would make the code shorter but also much, much slower. So I don't want to do that.)

You could use UNION in the CTE. There is still some duplication of code but less than before:

with etl (storenumber, filename, last_extracted) as
(
    ( select distinct on (storenumber)
          storenumber,
          'storefinancialdate',
          extracteddate + extractedtime::time
      from storefinancialdate
      order by
          storenumber,
          extracteddate desc, extractedtime desc
    )
  union all
    ( select distinct on (storenumber)
          storenumber,
          'storeproduct',
          extracteddate + extractedtime::time
      from storeproduct
      order by
          storenumber,
          extracteddate desc, extractedtime desc
    )
  -- union all ...
)
insert into star.stat_etl
    (storenumber, filename, last_extracted, last_loaded)
select
    storenumber,
    filename,
    last_extracted,
    transaction_timestamp()
from etl
  on conflict (storenumber, filename) do update
    set last_extracted = excluded.last_extracted,
        last_loaded    = excluded.last_loaded ;


Generally speaking, what pecking order should be used when tuning SQL Server? When tuning SQL Server, what generally is the pecking order to use when trying to decide what to tune first? I'm asking in regards to an existing database that would I would have little ability to redesign.

For example, my first thought is to tune clustered indexes first, then nonclustered indexes, and maybe check constraints. Partitioning of tables and indexes would be in there somewhere. I expect that I am missing many items that could go on this list.

I like to follow the Performance Tuning by Waits and Queues methodology introduced by SQLCAT team member Tom Davidson.

You can download and read his seminal white paper from here.

SQLCAT is Microsoft's SQL Customer Advisory Team - the folks who deal with the most demanding, largest SQL Server customers and thus have unique insights into some of the biggest installations and how to make them fly.

Basically, SQL Server collects a lot of statistical data on why and what it waits for. Collecting and interpreting that information can lead you to perf tune in the right spot and fixing the right issues, instead of just bumbling around and not knowing where to start and what to do.

Read that whitepaper! It's really very enlightening!

And of course - you'll need to establish a baseline first (e.g. have a set of queries - if ever possible a reasonable and realistic "workload" from your system in production - and measure how they perform) and then you need to tweak something (based on the insights from collecting and interpreting the wait stats) and then you need to measure again to see if that tweak help anything at all - or not.