postgres point insert I have a table with a point data type that stores the latitude and longitude.  I plan to insert from a function, and the input argument is defined as a float(18) [which is where I suspect the problem comes from].

Let's say the following values are used:

48.277274, -116.5547745

If I insert these with from the command line the database shows those values.  But if I insert them from the function the values in the database are:

48.2772750854492, -116.55477142334

I'm not doing anything to modify the values, as I mention above I believe bringing them in as float is causing the problem, but I've tried various other types and only get other issues.

Would greatly appreciate any help.

CREATE OR REPLACE FUNCTION myfun(
    float(18), --   lat
    float(18)) --  lon
  RETURNS integer AS
$BODY$
DECLARE
    postcnt integer;
BEGIN
   INSERT INTO user(usergeo)
   VALUES (point($1,$2))
   RETURN 1;
END
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;

When executing this:

INSERT INTO table VALUES(point(48.277274, -116.5547745));

the two decimal constants are implicitly casted to double precision numbers,
since it corresponds to the closest matching types for the point() function:

test=> \df point
                                  List of functions
   Schema   | Name  | Result data type |        Argument data types         |  Type
------------+-------+------------------+------------------------------------+--------
 pg_catalog | point | point            | box                                | normal
 pg_catalog | point | point            | circle                             | normal
 pg_catalog | point | point            | double precision, double precision | normal
 pg_catalog | point | point            | lseg                               | normal
 pg_catalog | point | point            | path                               | normal
 pg_catalog | point | point            | polygon                            | normal
(6 rows)

On the other hand, when executing this:

SELECT myfun(48.277274, -116.5547745)

the two decimal constants are implicitly casted to real as the type,
because float(n) is equivalent to real when 1<=n<=24, as explained in the
documentation on datatypes:

  PostgreSQL also supports the SQL-standard notations float and
  float(p) for specifying inexact numeric types. Here, p specifies the
  minimum acceptable precision in binary digits. PostgreSQL accepts
  float(1) to float(24) as selecting the real type, while float(25) to
  float(53) select double precision. Values of p outside the allowed
  range draw an error. float with no precision specified is taken to
  mean double precision

This downcast comes with a loss of precision, because, from the same doc page:

  On most platforms, the real type has a range of at least 1E-37 to
  1E+37 with a precision of at least 6 decimal digits. The double
  precision type typically has a range of around 1E-307 to 1E+308 with a
  precision of at least 15 digits

Conclusion: use double precision instead of float(18), which in fact is a synonym for the less-precise real.


Any way to track who has been executing what queries? who has been on my lawn? I have a database in which most of tables are readable for users in one particular security group.

I know most of the users are from my team, but lately I found that it's been used by more and more people, and most of execution occurs from Excel and/or Access.

Out of curiosity, is there any way for me to track who has been tapping into the SQL server and what query they have been running?

Queries aren't logged by SQL Server, unless they meet some criteria that puts them into the default trace or system_health Extended Events session (some details on what they collect here).

If you can control data access via stored procedures, you can easily add your own logging to those. But that doesn't sound like the case. Otherwise you will have to rely on triggers (for DML, but not SELECT), server-side trace, extended events, auditing, or 3rd party monitoring tools.


MySql date range query not showing all records I am working on mysql query right now. I write a query from fetch data from march 2018 to march 2019. There are more than 5000 records for this year. I write a query with date range from march 2018 to march 2019 but its showing me only 150 records

One more thing here. The date column in database is varchar. I know its not good but it was taken accidently during creation of database and I am not able to change this to date type because of lots of data in database

Here is the query I created

SELECT  a.*,b.*
FROM OrderCalculation a
right join crm_order b on a.orderid = b.orderno
WHERE str_to_date(b.Date,'%Y-%m-%d') >= str_to_date(concat(YEAR(CURDATE()),'-08-01'),'%Y-%m-%d')-INTERVAL 1 YEAR
AND str_to_date(b.Date,'%Y-%m-%d')  <= str_to_date(CONCAT(YEAR(CURDATE()),'-08-01'),'%Y-%m-%d')

Please make this query correct if you think something wrong in it

Thank you

The date column in database is varchar.

You should really fix that.  If the string always looks like yyyy-mm-dd, then it will happen to work as a date.  (And my code below will work.)  That is, the string version of a DATE or DATETIME is handled almost identically the same as the proper datatype.  One major difference is:  "2019-08-01" versus "2019-8-1"

Do not hide a column (Date) inside a function call (str_to_date)

WHERE str_to_date(b.Date,'%Y-%m-%d') >= str_to_date(concat(YEAR(CURDATE()),'-08-01'),'%Y-%m-%d')-INTERVAL 1 YEAR
  AND str_to_date(b.Date,'%Y-%m-%d') <= str_to_date(CONCAT(YEAR(CURDATE()),'-08-01'),'%Y-%m-%d')

-->

WHERE b.Date >= CURDATE() - INTERVAL 1 YEAR
  AND b.Date  < CURDATE() + INTERVAL 1 DAY

Did you really want 365 days, plus today?  This disagrees with your statement of "from march 2018 to march 2019".  (Plus March is 03, not 08.)

Now, INDEX(Date) may help with the speed of the query.  I say only "may" because the index will be ignored if the year is most of the table; it would be faster to ignore the table and simply scan the data.  (And my version will be as slow as yours).

Please rearrange your query to use LEFT JOIN instead of RIGHT JOIN.  My brain has trouble with RIGHT JOIN.


Is a value that does not change calculated for each row? Consider an SQL query with the following WHERE clause:

WHERE expires >= DATE_FORMAT(NOW(), '%Y%m%d')

Will the result of DATE_FORMAT() be recalculated for each row, or will it be calculated once and used for each row? If the latter, I can calculate the value in Python and then inject it into the SQL sent to MySQL.

Due to application restraints, the value is actually a LONGTEXT column and this question is a simplification of the problem. The data must be stored in this column type due to the EAV nature of the table.

This question pertains to MySQL 5.7, but if other database management systems, particularly SQLite or Postgres have different behaviour then I would appreciate knowing that as well. Thank you.

As long as it is run as a single transaction, the value will be re-used on each line, not recalculated.  I'm not 100% sure about Postgres or SQL Lite but I'm fairly confident (98%) they will work the same.

In the past when I was more confused about this question, I would pre-fetch the date value to a variable and then reuse the variable in the where clause.  Now I realize that was pointless unless I have more than one place in a longer set of statements where I would like to use the value.

As a test, move the calculated date value to the select clause and execute against a large table that takes more than a few seconds to return.  You will notice that the first line will have the same date/time value as the last record.


SQL Server domain name and user name formatting I am executing a SQL script in SQL Server Management Studio 2018. In my script I need to specify a user (including the domain - unsure if I need the server name).

So I have created a user sam, set the user type to SQL user without login and set the users role to db_datareader and db_datawriter.

I then execute my script but it gives me the error: User or role 'MHT.sam' does not exist in this database.

But I am almost certain I have added this user to the database (see my images below to double check). Is my user and domain name format correct? What do you think I am doing wrong?

Here's my domain and server:

You are possibly mixing SQL Server Logins and Database Users.

Introduction

A SQL Server Login is an account that allows a user to connect to the SQL Server instance. A SQL Server Login can be either ...

a native SQL Server login which exists in the SQL Server instance
a Windows Account from either the local server or the Active Directory which is linked (mapped) into the SQL Server instance

Examples of such accounts are:

sa (native SQL Server Authenticated login)
NT-Authority\SYSTEM (local Windows Authenticated login)
MHT\sam (Active Directory Windows Authenticated login)

When you create a user in SQL Server Management Studio (SSMS) with the graphical user interface, you are initially creating the SQL Server Login (Windows authentication or SQL Server authentication).

Differences at this stage are:

Windows authenticated account doesn't require a password
SQL Server authenticated account requires a password

When you have filled in the initial information you will normally switch tabs and configure the User Mappings.

In the User Mapping tab, you are mapping the SQL Server login to a database user. In most cases you will use the same name for the database user. In other cases you might be creating a totally different name.

Important: The database user has rights/permissions in the database. The SQL Server Login has access to the SQL Server instance.

In your case...

...you are assigning rights/permissions to the database user sam. However, sam isn't aware that he is linked to the SQL Login MHT\sam and it doesn't really matter, because you assign the database permissions/rights to a database user and not to SQL Server login.

Possible Reason For Discrepancies Between sam and MHT\sam

While you (or somebody else) were creating the MHT\sam SQL Server Windows Authenticated Login and after switching to the User Mappings section, the database user was truncated to sam.

SQL Server Windows Authenticated Login | General

This is where decide if you will be creating a native SQL Server Login or a Windows Authenticated Login.

SQL Server Windows Authenticated Login | User Mappings

This is where you link the SQL Server Login to a newly created database user or where you link the SQL Server Login to an already existing database user.

Solution

exec sp_addrolemember N'RunStoredProcedure', N'sam'
go
exec sp_addrolemember N'MonitoringRole', N'sam'
go

Explanation

The SQL Server Windows Authenticated Login MHT\sam is linked to the database user sam, which has now been granted the database role RunStoredProcedure and MonitoringRole. When MHT\sam logs in to the SQL Server instance and connects to his database, he has the rights/permissions assigned to the database user sam.


Historicizing of database models We want to build a SQL-Database for a system to manage regulations in different countries for different years. The problem is that the regulations change every year for every country. Like for social insurance you need different data in 2015 than you needed in 2014 and we have companies that enter their data to connect with their social insurance informations for every year.

If I want to build my models on this data I would have to create a different model for every year with different attributes. I can't just use migrations, because I have to keep the historical data and information.

Right now I am researching for the right solution and I would like to know if there is a name for this problem to find some solutions?

If you use PostgreSQL probably http://pgxn.org/dist/temporal_tables/ can help you.


Decomposing into 2NF I'm currently studying normalization. I know how to normalize the data for given unnormalized list.

But I found this question in our past papers. I don't know how to answer to it.

Q. Decompose R{a,b,c,d,e,f} into 2NF using following functional dependencies.

a -> b,c,d,e,f
b,c -> a,d,e,f
b -> f
d -> e

Please can anyone tell me how to do this? I went through some earlier questions,
but still have doubts.

For this my answer is:

      R0 = a - > b,c
      R1 = b,c - > a,d,e
      R2 = b - > f

Keep the simple dependencies as is:

b -> f
d -> e

Remove the dependent columns from the other dependencies.

a -> b,c,d
b,c -> a,d

At this point you have two candidate keys a and b,c.  As we already have a dependency on b.  I would keep the b,c dependency and drop the dependency on a.  It would appear that b,c defines a weak entity (or repeating group) related to b.  I would consider creating a unique key on a.


Postgres SELECT query produces exact same explain analyze results but different execution times in the results I have a SELECT query which when running produces the exact same plan in terms of indexes being used when doing an explain analyze however the duration of the run is different (2 seconds vs 30 seconds on average).
Why would this be the case?
Query
explain analyze SELECT SUM ((t0.item_cash_staked - t0.item_cash_won))
FROM item t0, product t1
WHERE (((( t0.item_rejection_code_id IS null)
    AND (t0.item_created_on > '2019-08-01 17:38:33.613+01'))
    AND (t1.customer_id = 123456))
    AND (t1.product_id = t0.product_id));

Explain results
 Aggregate  (cost=1984710.64..1984710.65 rows=1 width=32) (actual time=26916.904..26916.905 rows=1 loops=1)
   ->  Nested Loop  (cost=1.14..1983541.69 rows=233789 width=6) (actual time=4531.244..26765.752 rows=453812 loops=1)
         ->  Index Scan using product_idx_01 on product t1  (cost=0.57..172442.67 rows=539262 width=4) (actual time=6.213..4490.454 rows=500133 loops=1)
               Index Cond: (customer_id = 123456)
         ->  Index Scan using item_idx_product_id on item t0  (cost=0.57..3.24 rows=12 width=10) (actual time=0.035..0.044 rows=1 loops=500133)
               Index Cond: (product_id = t1.product_id)
               Filter: ((item_rejection_code_id IS NULL) AND (item_created_on > '2019-08-01 17:38:33.613+01'::timestamp with time zone))
               Rows Removed by Filter: 1
 Planning time: 0.409 ms
 Execution time: 26916.999 ms
(10 rows)

Explain Result Faster
 Aggregate  (cost=1984710.64..1984710.65 rows=1 width=32) (actual time=1786.816..1786.816 rows=1 loops=1)
   ->  Nested Loop  (cost=1.14..1983541.69 rows=233789 width=6) (actual time=289.922..1687.398 rows=453812 loops=1)
         ->  Index Scan using product_idx_01 on product t1  (cost=0.57..172442.67 rows=539262 width=4) (actual time=0.013..202.082 rows=500133 loops=1)
               Index Cond: (customer_id = 123456)
         ->  Index Scan using item_idx_product_id on item t0  (cost=0.57..3.24 rows=12 width=10) (actual time=0.002..0.003 rows=1 loops=500133)
               Index Cond: (product_id = t1.product_id)
               Filter: ((item_rejection_code_id IS NULL) AND (item_created_on > '2019-08-01 17:38:33.613+01'::timestamp with time zone))
               Rows Removed by Filter: 1
 Planning time: 0.275 ms
 Execution time: 1786.866 ms
(10 rows)

I get running a query once will be slower than running it later as it will skip the planning phase, but I have had times where I have run the same query 1 after the other and it still takes approx 25 seconds to run? Why would this be?
Is there anyway of improving the query to make it better performing?
Any help is much appreciated.

You can create an index that is specifically designed for the query:
CREATE INDEX ON product (product_id, item_created_on)
   INCLUDE (item_cash_staked, item_cash_won)
   WHERE item_rejection_code_id IS NULL;

VACUUM product;

That should get you and index-only scan.
With old PostgreSQL versions, you can add the columns to the index instead:
CREATE INDEX ON product (product_id, item_created_on, item_cash_staked, item_cash_won)
   WHERE item_rejection_code_id IS NULL;

VACUUM product;


Performance between Delete In and For Loop I'm trying to find out what is the most efficient way for a procedure to delete hundreds million of records

    For B in (select id from invoice where data < trunc(sysdate)) LOOP
      delete invoice_details where details_id = b.id
    END LOOP;

vs

    delete invoice_details where details_id in (select id from invoice where data < trunc(sysdate))

Is there any performances issue? which one is a better way to delete?

If the task can be performed using native SQL then always go for it. Because SQL and PLSQl are two different languages and if you call SQL from PLSQL block then context switch(exchange of processing control between SQL and PLSQL) occurs.

So I would prefer the native SQL.

Now, you have couple of options such as-

If you are going to delete large percentage of rows from the table, keeping 1 million rows is better that deleting 2 millions rows from table having 3 millions rows,

Then create <new table> as select * from <old table> where <condition>, you can also use parallel here,  drop old table, rename  to , create necessary indexes in parallel.

Down side of this method is that other session can't use this table during the operation.

Another method is to partition the table then drop the unnecessary partitions.

Note: If you still want to do it with PLSQl(for concurrency) then instead of deleting one by one, you can use BULK DELETE.


Optimizing postgres row overlap constraints involving UUIDs and GIST For various reasons all my tables have UUIDs as primary keys and I need to exclude duplicate rows where the combination of area_id, user_id, and finished_at <-> started_at window.

The only way I can see to do this involves casting UUIDs to text inside the overlapping_allocations constraint below.
However this seems stupidly wasteful, am I missing a trick?

This is all running on Postgres 9.3.5 and there is freedom to enable other common extensions.

CREATE EXTENSION IF NOT EXISTS "uuid-ossp";
CREATE EXTENSION IF NOT EXISTS "btree_gist";

CREATE TABLE areas(
    id uuid PRIMARY KEY default uuid_generate_v1(),
    name text NOT NULL UNIQUE,
    -- blah blah, lots of other fields
)

CREATE TABLE allocations(
    id uuid PRIMARY KEY default uuid_generate_v1(),
    area_id uuid references areas,
    user_id text NOT NULL,
    started_at timestamp with time zone NOT NULL,
    finished_at timestamp with time zone DEFAULT NULL,
    created_at timestamp with time zone default NOW(),
    CONSTRAINT valid_times CHECK (started_at < finished_at),
    CONSTRAINT overlapping_allocations EXCLUDE USING GIST (
        -- These two casts are horrid but GIST doesn't understand uuids
        -- see here https://stackoverflow.com/questions/22720130/how-to-use-uuid-with-postgresql-gist-index-type
        cast("area_id" AS text) WITH =,
        user_id WITH =,
        tstzrange(started_at, COALESCE(finished_at, 'infinity'::timestamptz) ) WITH &&
    )
);

As of Postgres 10 the btree_gist extension (in contrib) supports UUIDs, so if you add that extension first, you'll be able to create indexes and exclusion constraints without casting to text. Pre-10, casting to text is the way to go.


Running total to the previous row I need some help with windowing functions. I know you can calculate the sum within a window and the running total within a window. But is it possible to calculate the previous running total i.e. the running total not including the current row?

I assume you would need to use the ROW or RANGE argument. I know there is a CURRENT ROW option but I would need CURRENT ROW - 1, which is invalid syntax. My knowledge of the ROW and RANGE arguments is limited so any help would be gratefully received.

I know that there are many solutions to this problem, but I am looking to understand the ROW, RANGE arguments and I assume the problem can be cracked with these. I have included one possible way to calculate the previous running total but I wonder if there is a better way:

USE AdventureWorks2012

SELECT s.SalesOrderID
    , s.SalesOrderDetailID
    , s.OrderQty
    , SUM(s.OrderQty) OVER (PARTITION BY  SalesOrderID) AS RunningTotal
    , SUM(s.OrderQty) OVER (PARTITION BY  SalesOrderID
                         ORDER BY SalesOrderDetailID) - s.OrderQty AS PreviousRunningTotal
    -- Sudo code - I know this does not work
    --, SUM(s.OrderQty) OVER (PARTITION BY  SalesOrderID
    --                   ORDER BY SalesOrderDetailID
    --                   ROWS BETWEEN UNBOUNDED PRECEDING
    --                                   AND CURRENT ROW - 1)
    -- AS  SudoCodePreviousRunningTotal
FROM Sales.SalesOrderDetail s
WHERE SalesOrderID IN (43670, 43669, 43667, 43663)
ORDER BY s.SalesOrderID
    , s.SalesOrderDetailID
    , s.OrderQty

The answer is to use 1 PRECEDING, not CURRENT ROW -1. So, in your query, use:

    , SUM(s.OrderQty) OVER (PARTITION BY  SalesOrderID
                            ORDER BY SalesOrderDetailID
                            ROWS BETWEEN UNBOUNDED PRECEDING
                                     AND 1 PRECEDING)
    AS  PreviousRunningTotal

Also note that on your other calculation:

    , SUM(s.OrderQty) OVER (PARTITION BY  SalesOrderID
                            ORDER BY SalesOrderDetailID) ...

SQL-Server uses the default * RANGE UNBOUNDED PRECEDING AND CURRENT ROW. I think that there is an efficiency difference and ROWS UNBOUNDED PRECEDING AND CURRENT ROW is to be preferred (after testing of course and if it gives the results you want).

Much more details you can find in the blog article by @Aaron Bertrand, including performance tests: Best approaches for running totals – updated for SQL Server 2012

* this is of course the default range when an ORDER BY is present inside the OVER clause - otherwise, without ORDER BY the default is the whole partition.


Cartesian product of two tables not working as expected in MySQL but fine with SQlitee? I was practising some sql joins and decided to create a schema online rather than using my local database on sql fiddle. My schema is pretty simple

table 1 (5 entries): (emp->{id,name,dep})
table 2 (4 entries): (dep->{id,name})

I was supposed to write one inner join on dep column which worked with SQLite, but when I tried the same query with MySQL it failed. So in order to investigate, I just checked the Cartesian product of two tables for both the databases.

I tried this query

select * from dep a,emp b

in both databases.

In SQLite, it correctly return 20 entries with 5 columns in it. But in MySQL, it is returning correctly 20 entries but only 3 columns in it (id,name,did). So this explains why the join was breaking earlier.

I can't understand how can this Cartesian product be incorrect? Can someone please explain to me, what is going wrong in MySQL case?

MySQL fiddle

SQLite fiddle

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.6.15 MySQL Community Server (GPL)

Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql>  use test
Database changed
mysql> CREATE TABLE emp
    ->  (
    ->      id int primary key,
    ->      name varchar(20),
    ->      did int
    ->     );
Query OK, 0 rows affected (0.45 sec)

mysql>
mysql>
mysql>
mysql> CREATE TABLE dep
    ->  (
    ->      id int primary key,
    ->      name varchar(20)
    ->     );
Query OK, 0 rows affected (0.35 sec)

mysql>
mysql> INSERT INTO dep
    -> VALUES
    -> (1,'lakers'),
    -> (2,'spurs'),
    -> (3,'sixers'),
    -> (4,'pacers'),
    -> (5,'warriors')
    ->
    -> ;
Query OK, 5 rows affected (0.06 sec)
Records: 5  Duplicates: 0  Warnings: 0

mysql>
mysql> INSERT INTO emp
    -> VALUES
    -> (1,'rohit',1),
    -> (2,'amit',2),
    -> (3,'haris',3),
    -> (4,'eti',4)
    -> ;
Query OK, 4 rows affected (0.08 sec)
Records: 4  Duplicates: 0  Warnings: 0

I ran your query without aliases

mysql> select * from dep,emp;
+----+----------+----+-------+------+
| id | name     | id | name  | did  |
+----+----------+----+-------+------+
|  1 | lakers   |  1 | rohit |    1 |
|  1 | lakers   |  2 | amit  |    2 |
|  1 | lakers   |  3 | haris |    3 |
|  1 | lakers   |  4 | eti   |    4 |
|  2 | spurs    |  1 | rohit |    1 |
|  2 | spurs    |  2 | amit  |    2 |
|  2 | spurs    |  3 | haris |    3 |
|  2 | spurs    |  4 | eti   |    4 |
|  3 | sixers   |  1 | rohit |    1 |
|  3 | sixers   |  2 | amit  |    2 |
|  3 | sixers   |  3 | haris |    3 |
|  3 | sixers   |  4 | eti   |    4 |
|  4 | pacers   |  1 | rohit |    1 |
|  4 | pacers   |  2 | amit  |    2 |
|  4 | pacers   |  3 | haris |    3 |
|  4 | pacers   |  4 | eti   |    4 |
|  5 | warriors |  1 | rohit |    1 |
|  5 | warriors |  2 | amit  |    2 |
|  5 | warriors |  3 | haris |    3 |
|  5 | warriors |  4 | eti   |    4 |
+----+----------+----+-------+------+
20 rows in set (0.03 sec)

I ran your query as you gave it, with aliases

mysql> select * from dep a,emp b;
+----+----------+----+-------+------+
| id | name     | id | name  | did  |
+----+----------+----+-------+------+
|  1 | lakers   |  1 | rohit |    1 |
|  1 | lakers   |  2 | amit  |    2 |
|  1 | lakers   |  3 | haris |    3 |
|  1 | lakers   |  4 | eti   |    4 |
|  2 | spurs    |  1 | rohit |    1 |
|  2 | spurs    |  2 | amit  |    2 |
|  2 | spurs    |  3 | haris |    3 |
|  2 | spurs    |  4 | eti   |    4 |
|  3 | sixers   |  1 | rohit |    1 |
|  3 | sixers   |  2 | amit  |    2 |
|  3 | sixers   |  3 | haris |    3 |
|  3 | sixers   |  4 | eti   |    4 |
|  4 | pacers   |  1 | rohit |    1 |
|  4 | pacers   |  2 | amit  |    2 |
|  4 | pacers   |  3 | haris |    3 |
|  4 | pacers   |  4 | eti   |    4 |
|  5 | warriors |  1 | rohit |    1 |
|  5 | warriors |  2 | amit  |    2 |
|  5 | warriors |  3 | haris |    3 |
|  5 | warriors |  4 | eti   |    4 |
+----+----------+----+-------+------+
20 rows in set (0.00 sec)

mysql>

I ran your query with aliases in the SELECT clause as well as the FROM clause

mysql> select a.*,b.* from dep a,emp b;
+----+----------+----+-------+------+
| id | name     | id | name  | did  |
+----+----------+----+-------+------+
|  1 | lakers   |  1 | rohit |    1 |
|  1 | lakers   |  2 | amit  |    2 |
|  1 | lakers   |  3 | haris |    3 |
|  1 | lakers   |  4 | eti   |    4 |
|  2 | spurs    |  1 | rohit |    1 |
|  2 | spurs    |  2 | amit  |    2 |
|  2 | spurs    |  3 | haris |    3 |
|  2 | spurs    |  4 | eti   |    4 |
|  3 | sixers   |  1 | rohit |    1 |
|  3 | sixers   |  2 | amit  |    2 |
|  3 | sixers   |  3 | haris |    3 |
|  3 | sixers   |  4 | eti   |    4 |
|  4 | pacers   |  1 | rohit |    1 |
|  4 | pacers   |  2 | amit  |    2 |
|  4 | pacers   |  3 | haris |    3 |
|  4 | pacers   |  4 | eti   |    4 |
|  5 | warriors |  1 | rohit |    1 |
|  5 | warriors |  2 | amit  |    2 |
|  5 | warriors |  3 | haris |    3 |
|  5 | warriors |  4 | eti   |    4 |
+----+----------+----+-------+------+
20 rows in set (0.00 sec)

mysql>

I just ran these in MySQL 5.6.15 on my Windows 8 Laptop. It works fine with and without aliases. It might be SQL Fiddle that has the problem in this instance with a MySQL Cartesian Product.


Can I see Table I/O per month? I'm using SQL Server 2008, and will migrate DynamoDB some tables.

I want to get I/O statistics of read bytes, and write bytes from table.

I don't know how to do this. What command should I use?

This will give you correct estimates only if you are sure that the server has not been restarted during the time frame you are intending to check..

--to check when server is last restarted

SELECT sqlserver_start_time FROM sys.dm_os_sys_info

--to see stats for  table

SELECT TableName = object_name(s.object_id),
       Reads = SUM(user_seeks + user_scans + user_lookups), Writes =  SUM(user_updates)
FROM sys.dm_db_index_usage_stats AS s
INNER JOIN sys.indexes AS i
ON s.object_id = i.object_id
AND i.index_id = s.index_id
WHERE objectproperty(s.object_id,'IsUserTable') = 1
GROUP BY object_name(s.object_id)
ORDER BY writes DESC

References:https://stackoverflow.com/questions/1575676/how-to-find-out-sql-server-tables-read-write-statistics


Cancel SQL Job execution make automatic rollback I have a SQL SP which has a set a transaction. All the statements handled inside the TRY CATCH block. If we explicitly cancel the job by close the button in the JOb execution window, will it get into the catch block and automaticall rollback all the transaction tried in the TRY block ?

No, it probably won't. I can't say for sure, but client "aborts" normally don't rollback if the connection is returned to the connection pool

Add SET XACT_ABORT ON to your code or stored procedure

See (SO) https://stackoverflow.com/questions/917773/do-i-really-need-to-use-set-xact-abort-on/919279#919279 and SQL Server Transaction Timeout


Lock Escalation happening while inserting few million records into a table in production I want to insert few million records into a table in my production database.
I am not updating any existing rows.I am just doing

INSERT INTO TAB1
SELECT
COL1,COL2,COL3
FROM TAB2

This table has an Identity column as primary key and a non clustered key.

When i do this i get blocking and then it gets escalated to chain of bolocking.
I know that by default SQL Server doesn't do TABLE lock and default isolation level is READ COMMITTED.

Since i am inserting new rows into the table,i am sure that the application is not acquiring any shared lock(select while insert scenario).

I am confused as to why i am getting this blocking and then chain of block.

I am thinking of giving an hint to perform ROW LEVEL lock only or use any other lock hints.

Any suggestion.

Additional Info:

The target table into which i am inserting rows is being replicated using transactional replication.I am not sure whether it is affecting the insert process.

You can check for any Foreign Key,constraint,trigger etc define on table and give detail of it.

You can use TabLock hint].

Also if there is no chance of Getting READ UNCOMMITTED data then you can use NoLock hint in Select .

INSERT INTO TAB1 WITH (TABLOCK)
SELECT
COL1,COL2,COL3
FROM TAB2

Identity column as primary key is not an issue.
Non cluster Index can be and issue.

So as second option,you can disable index then rebuild later.It is also time consuming for million of records.

ALTER INDEX IX_IndexName ON TAB1 DISABLE;
GO

INSERT INTO TAB1 WITH (TABLOCK)
    SELECT
    COL1,COL2,COL3
    FROM TAB2

ALTER INDEX IX_IndexName ON Tab1 REBUILD;
GO


Unable to ping/connect to azure sql server from sql server management studio I created a database in azure and assigned a new server to it. I entered my ip address in the firewall and tried to connect to the server in Sql Server Management Studio but no success. I tried to ping the FQDN of the server server.database.windows.net but no success.
I searched the problem and I found that the server cannot be pinged
click here. Can you please help me in connecting to the SQL server or database from SSMS on my machine?
Thanks

The problem was with our company firewall. The port 1433 was blocked on the firewall. After removing the outgoing and incoming restrictions on firewall the SMSS started working.


Keep empty string instead of null for /copy command When using the \copy command with psql I have a file with empty values. These values are not to be null but instead always an empty string.

My command:

\copy agltransact FROM 'dbo_agltransact.csv' with null as '' CSV HEADER;

The error I get:

ERROR:  null value in column "apar_id" violates not-null constraint

I have this data:

account,amount,apar_id,apar_type
14015,3000000.000,,1
14015,3000000.000,,2

I want the next to last columns to be an empty string.

Figured it out. I need to escape and output the single quotes

\copy agltransact FROM 'dbo_agltransact.csv' with null as E'\'\'' CSV HEADER;


Finding gaps on intersection dates This is a problem I've been trying to work through on and off for the past month, and haven't made any progress. Essentially, I am working with a HR system that stores two major types of position for employees: Primary (P) and Acting (A). The position types aren't hugely relevant, but are important to know to understand the way the data is structured. An employee will always be in a Primary position, but may be put into an Acting position temporarily while it is vacant, or while the Primary person for that role is on leave.

The way that this is represented in the system is diagrammed below (as an example and not to scale):

P (2018-07-28 - 9999-99-99) <------------------------------------------------------------->
A (2018-09-04 - 2018-09-05)             <--->
A (2018-11-12 - 2018-11-13)                                          <--->
A (2018-11-19 - 2018-11-20)                                                  <--->

Due to a set of data I'm trying to join the position information with, I need it to be intersected to look like this, where the Acting position always takes precedence over the Primary position.

P 2018-07-28 - 2018-09-03 <---------->
A 2018-09-04 - 2018-09-05             <--->
P 2018-09-06 - 2018-11-11                  <----------------------->
A 2018-11-12 - 2018-11-13                                           <--->
P 2018-11-14 - 2018-11-18                                                <----->
A 2018-11-19 - 2018-11-20
<--->
P 2018-11-21 - 9999-99-99
     <----->

Below is a representation of the data directly from the database, to give an idea of what it looks like overall:

Employee    Position    Start Date  End Date    Position Type
1           30          2016-12-10  2016-12-11  P
1           30          2016-12-12  2017-12-08  P
1           20          2017-01-31  2017-02-02  A
1           20          2017-03-29  2017-03-31  A
1           20          2017-04-19  2017-04-21  A
1           20          2017-06-22  2017-06-23  A
1           20          2017-06-29  2017-06-30  A
1           20          2017-10-03  2017-10-06  A
1           30          2017-12-09  2018-07-26  P
1           20          2018-02-12  2018-03-02  A
1           20          2018-07-19  2018-07-20  A
1           30          2018-07-27  2018-07-27  P
1           30          2018-07-28  9999-99-99  P
1           20          2018-09-04  2018-09-05  A
1           20          2018-11-12  2018-11-13  A
1           20          2018-11-19  2018-11-20  A

As you can see, there are even multiple entries for the same position where the dates are contiguous. These need to be combined into a contiguous entry until they are intersected/overridden by Acting Positions. I've been scouring different sources on the internet for the past month and haven't even come close to a viable solution. The closest I've come is from SQL – Combining Overlapping Date Rows, which I've converted from Oracle but I don't have enough knowledge of T-SQL to take it all the way to what I need, original Oracle code below:

SELECT
  KEY,
  MIN(TD) MINTD,
  MAX(TD) MAXTD
FROM
  (SELECT
    KEY,
    TD,
    SUM(C) OVER (PARTITION BY KEY ORDER BY TD) SC
  FROM
    (SELECT
      KEY,
      D,
      TD,
      TPD,
      DECODE(SIGN(LTD-TPD),1,1,0) C
    FROM
      (SELECT
        KEY,
        D,
        TRUNC(D,'MM') TD,
        ADD_MONTHS(TRUNC(D,'MM'),-1) LTD,
        LAG(D,1) OVER (PARTITION BY KEY ORDER BY D) PD,
        LAG(TRUNC(D,'MM'),1) OVER (PARTITION BY KEY ORDER BY D) TPD
      FROM
        T1)))
GROUP BY
  KEY,
  SC
ORDER BY
  KEY,
  SC;

Any ideas on a SELECT query to group and intersect the dates as outlined above?

First, I think the link you had supplied doesn't fit for your question because it is about combining overlapped dates, and you need to generate (GAPs) new dates.

I would divide your data in two groups, those corresponding to Primary(s) and those that belong to Acting(s).

SELECT   t1.*,
         ROW_NUMBER() OVER (PARTITION BY Employee ORDER BY Employee, StartDate) P1
FROM     tbl t1
WHERE    t1.PosType = 'P'
ORDER BY t1.Employee, t1.StartDate
;
GO

Employee | Position | StartDate  | EndDate    | PosType | P1
-------: | -------: | :----------| :----------| :------ | :-
       1 |       30 | 10/12/2016 | 11/12/2016 | P       | 1
       1 |       30 | 12/12/2016 | 08/12/2017 | P       | 2
       1 |       30 | 09/12/2017 | 26/07/2018 | P       | 3
       1 |       30 | 27/07/2018 | 27/07/2018 | P       | 4
       1 |       30 | 28/07/2018 | 31/12/9999 | P       | 5

Next Acting rows are subsumed into the second Principal row: 12/12/2016 | 08/12/2017

SELECT   t2.*,
         ROW_NUMBER() OVER (ORDER BY t2.StartDate) P2
FROM     tbl t2
WHERE    t2.PosType = 'A'
         AND t2.StartDate >= '20161212'
         AND t2.StartDate <= '20171208'
ORDER BY t2.Employee, t2.StartDate
;
GO

Employee | Position | StartDate  | EndDate    | PosType | P2
-------: | -------: | :----------| :----------| :------ | :--
       1 |       20 | 31/01/2017 | 02/02/2017 | A       | 1
       1 |       20 | 29/03/2017 | 31/03/2017 | A       | 2
       1 |       20 | 19/04/2017 | 21/04/2017 | A       | 3
       1 |       20 | 22/06/2017 | 23/06/2017 | A       | 4
       1 |       20 | 29/06/2017 | 30/06/2017 | A       | 5
       1 |       20 | 03/10/2017 | 06/10/2017 | A       | 6

Now let me generate a new dataset, for each Primary I'll add the corresponding Acting(s) using an OUTER JOIN, this outer join returns all Primary(s) even if they haven't subsumed Acting(s).

SELECT   t1.*, COALESCE(t3.P2, 0) P2, P, t3.PrevED, t3.SD, t3.ED, NextSD, t3.PT
INTO #mydata
FROM     (SELECT *,
                 ROW_NUMBER() OVER (PARTITION BY Employee ORDER BY Employee, StartDate) as P1
          FROM   tbl
          WHERE  PosType = 'P'
          ) t1
OUTER APPLY (SELECT   ROW_NUMBER() OVER (ORDER BY t2.StartDate) as P2,
                      LAG(t2.EndDate) OVER (ORDER BY t2.StartDate) as PrevED,
                      LEAD(t2.StartDate) OVER (ORDER BY t2.StartDate) as NextSD,
                      t2.Position as P, t2.StartDate as SD, t2.EndDate as ED, t2.PosType as PT
             FROM     tbl t2
             WHERE    t2.Employee = t1.Employee
                      AND t2.PosType = 'A'
                      AND t2.StartDate >= t1.StartDate
                      AND t2.StartDate <= t1.EndDate
            ) t3
WHERE    t1.PosType = 'P'
ORDER BY t1.Employee, t1.StartDate, t3.SD
;

CREATE CLUSTERED INDEX PK_Mydata ON #mydata(Employee, P1, P2);

SELECT * FROM #mydata ORDER BY Employee, P1, P2;
GO

  NOTE: I've used a temporary table plus and index, but you could try by using a CTE. You should check performance of both options.

This is the new scenario:

Employee | Position | StartDate  | EndDate    | PosType | P1 | P2 |    P | PrevED     | SD         | ED         | NextSD     | PT
-------: | -------: | :----------| :----------| :------ | :- | :- | ---: | :----------| :----------| :----------| :----------| :---
       1 |       30 | 10/12/2016 | 11/12/2016 | P       | 1  | 0  | null | null       | null       | null       | null       | null
       1 |       30 | 12/12/2016 | 08/12/2017 | P       | 2  | 1  |   20 | null       | 31/01/2017 | 02/02/2017 | 29/03/2017 | A
       1 |       30 | 12/12/2016 | 08/12/2017 | P       | 2  | 2  |   20 | 02/02/2017 | 29/03/2017 | 31/03/2017 | 19/04/2017 | A
       1 |       30 | 12/12/2016 | 08/12/2017 | P       | 2  | 3  |   20 | 31/03/2017 | 19/04/2017 | 21/04/2017 | 22/06/2017 | A
       1 |       30 | 12/12/2016 | 08/12/2017 | P       | 2  | 4  |   20 | 21/04/2017 | 22/06/2017 | 23/06/2017 | 29/06/2017 | A
       1 |       30 | 12/12/2016 | 08/12/2017 | P       | 2  | 5  |   20 | 23/06/2017 | 29/06/2017 | 30/06/2017 | 03/10/2017 | A
       1 |       30 | 12/12/2016 | 08/12/2017 | P       | 2  | 6  |   20 | 30/06/2017 | 03/10/2017 | 06/10/2017 | null       | A
       1 |       30 | 09/12/2017 | 26/07/2018 | P       | 3  | 1  |   20 | null       | 12/02/2018 | 02/03/2018 | 19/07/2018 | A
       1 |       30 | 09/12/2017 | 26/07/2018 | P       | 3  | 2  |   20 | 02/03/2018 | 19/07/2018 | 20/07/2018 | null       | A
       1 |       30 | 27/07/2018 | 27/07/2018 | P       | 4  | 0  | null | null       | null       | null       | null       | null
       1 |       30 | 28/07/2018 | 31/12/9999 | P       | 5  | 1  |   20 | null       | 04/09/2018 | 05/09/2018 | 12/11/2018 | A
       1 |       30 | 28/07/2018 | 31/12/9999 | P       | 5  | 2  |   20 | 05/09/2018 | 12/11/2018 | 13/11/2018 | 19/11/2018 | A
       1 |       30 | 28/07/2018 | 31/12/9999 | P       | 5  | 3  |   20 | 13/11/2018 | 19/11/2018 | 20/11/2018 | null       | A

I've added two partitions P1, P2 and I set up an index on them, these partitions will help me to filter information in this new scenario according to 4 types of rows:

|---------------------------------|   Type 1
|--P--|                               Type 2
       |--A--|                        Type 3
              |--P--|                 Type 2
                     |--A--|          Type 3
                            |--P--|   Type 4

Type 1:

Primary(s) that don't have any subsumed Acting.

P2 = 0

Type 2:

If exists, left gaps between Acting(s) plus between first Acting and Primary start date.

P2 <> 0
Actign start date - 1 > Primary start date/Acting end date

Type 3:

All Acting preriods.

P2 <> 0

Type 4:

If exists, right gaps between last Acting and Primary end date.

P2 <> 0
NextSD IS NULL (Last Acting)
Acting end date < Primary end date

Now the selection:

SELECT *
FROM
    (
    -- Primary(s) without Acting(s)
    SELECT Employee, Position, StartDate, EndDate, PosType
    FROM   #mydata
    WHERE  Employee = 1
           AND P2 = 0
    --
    -- plus all Acting(s)
    --
    UNION
    SELECT Employee, P, SD, ED, PT
    FROM   #mydata
    WHERE  Employee = 1
           AND P2 <> 0
    --
    -- plus all (left) Gaps
    --
    UNION
    SELECT Employee,
           Position,
           IIF(PrevED IS NULL, StartDate, DATEADD(day, 1, PrevED)),
           DATEADD(day, -1, SD),
           PosType
    FROM   #mydata
    WHERE  Employee = 1
           AND P2 <> 0
           AND DATEDIFF(day, IIF(PrevED IS NULL, StartDate, DATEADD(day, 1, PrevED)), SD) > 0
    UNION
    --
    -- plus all (last right) Gap
    --
    SELECT Employee,
           Position,
           DATEADD(day, 1, ED),
           EndDate,
           PosType
    FROM   #mydata
    WHERE  Employee = 1
           AND P2 <> 0
           AND NextSD IS NULL
           AND DATEDIFF(day, ED, EndDate) > 0
    ) T
ORDER BY 1, 3;
GO

This is the final result:

Employee | Position | StartDate  | EndDate    | PosType
-------: | -------: | :----------| :----------| :------
       1 |       30 | 10/12/2016 | 11/12/2016 | P
       1 |       30 | 12/12/2016 | 30/01/2017 | P
       1 |       20 | 31/01/2017 | 02/02/2017 | A
       1 |       30 | 03/02/2017 | 28/03/2017 | P
       1 |       20 | 29/03/2017 | 31/03/2017 | A
       1 |       30 | 01/04/2017 | 18/04/2017 | P
       1 |       20 | 19/04/2017 | 21/04/2017 | A
       1 |       30 | 22/04/2017 | 21/06/2017 | P
       1 |       20 | 22/06/2017 | 23/06/2017 | A
       1 |       30 | 24/06/2017 | 28/06/2017 | P
       1 |       20 | 29/06/2017 | 30/06/2017 | A
       1 |       30 | 01/07/2017 | 02/10/2017 | P
       1 |       20 | 03/10/2017 | 06/10/2017 | A
       1 |       30 | 07/10/2017 | 08/12/2017 | P
       1 |       30 | 09/12/2017 | 11/02/2018 | P
       1 |       20 | 12/02/2018 | 02/03/2018 | A
       1 |       30 | 03/03/2018 | 18/07/2018 | P
       1 |       20 | 19/07/2018 | 20/07/2018 | A
       1 |       30 | 21/07/2018 | 26/07/2018 | P
       1 |       30 | 27/07/2018 | 27/07/2018 | P
       1 |       30 | 28/07/2018 | 03/09/2018 | P
       1 |       20 | 04/09/2018 | 05/09/2018 | A
       1 |       30 | 06/09/2018 | 11/11/2018 | P
       1 |       20 | 12/11/2018 | 13/11/2018 | A
       1 |       30 | 14/11/2018 | 18/11/2018 | P
       1 |       20 | 19/11/2018 | 20/11/2018 | A
       1 |       30 | 21/11/2018 | 31/12/9999 | P

db<>fiddle here


What information from v$ views should be included when asking questions about Oracle Problems? I was asking more information about Operating System. Then I wondered if an generic sql can give this information? Following things come to my mind.

SELECT banner from v$version;
SELECT LOG_MODE ,PLATFORM_ID,PLATFORM_NAME FROM V$DATABASE;

My output from test database is below.

SQL> SELECT banner from V$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - Production
PL/SQL Release 11.2.0.2.0 - Production
CORE    11.2.0.2.0      Production
TNS for Linux: Version 11.2.0.2.0 - Production
NLSRTL Version 11.2.0.2.0 - Production

SQL> SELECT LOG_MODE ,PLATFORM_ID,PLATFORM_NAME FROM V$DATABASE;

LOG_MODE     PLATFORM_ID  PLATFORM_NAME
------------ -----------  -------------------
NOARCHIVELOG          10  Linux IA (32-bit)

It depends on what specific problem you are asking. The two queries you provided can give others some general information of your database, which should be provided in almost all your questions.

BTW, I suggest you add *open_mode* column of v$database in your query.


DB design advice - shortcut foreignkeys vs long queries I've designed quite a few databases but there's a problem I come across time and time again. The more information you want to pack into your database and the more fields and different properties you add to stuff, you end up with loads of tables that separate the objects that are semantically very closely related. Example:

The events have a M2M relationship to Identification, which is e.g. an email address, a phone number etc. that identifies a Person. All events happen through an Identification. Not all Identifications have an attached Person (if nothing is known about the Person, then the Person object isn't needed) and not all Persons are Agents (but some of them are).

A commonplace query on that database would be to get all Events related to Agents for example, or get all Events for one Team. With this schema however you have to perform those queries over 4-5 tables which is both cumbersome and probably quite slow once you have rows in the range of millions (you're welcome to comment on the performance as well). On one hand if I wanted to simplify things and speed queries up, I could just connect Event directly to Agent/Team/Person via another M2M table or a Postgres FK Array, but that would be duplicating the relationships.

1) Is there a better design pattern for this database?
2) What are the best practices when it comes to shortcut FK-s for tables that are related but through n+1 other tables?
3) Is this even a legitimate concern or am I optimizing this prematurely? Maybe this schema is fine and I'm worrying needlessly?

PS. This is a redesign of an existing database that I know will have millions of rows, so the "don't waste energy on optimizing stuff that may never be necessary" point is not valid here.

Thanks!

The table structure you show looks fine from a database point of view, and it should work fine for an OLTP workload. Using nested loop joins, it is no problem to fetch a few rows by joining a number of tables.

If the schema is properly normalized, data modifications will be as localized as possible, and consistency is guaranteed.

Things become different when you want to perform analytical queries. Then joining many tables really hurts. This is why for OLAP workloads, you tend to denormalize the data and put them in a star schema or a similar structure. Such a “data warehouse” also usually doesn't hold the data in detail, but pre-aggregated in a way that makes the queries fast and the database not too large.

If you want to run both short transactions and analytical queries on the same database, it is hard or impossible to create a data model that fits both. Either you are ready to suffer with your analytical queries (possibly off-loading them to a streaming replication standby), or you create a second database with an analytical schema and an ETL process that regularly updates it from the live database.


SQL Server 2008 search joining query I have two table as one is the message table and another one is messageUser table.
Now i need to check before insert a new row.

for example,

  M_MessageMessageId=========
                 1
                 2
                 3

  M_MessageUserMessageId | MemberId | BusinessId
  =========|=========|=========
                 1 |               1 |               0
                 1 |               0 |               2
                 2 |               1 |               0
                 2 |               0 |               2
                 2 |               3 |               0
                 2 |               4 |               0
                 3 |               1 |               0
                 3 |               0 |               2
                 3 |               0 |               4

if a member create a new message, i would like to check the user in this message is there exist before. If yes, then attach the message to previous conversation else create a new conversation.

  Scene 1
  Member 1 sent a message to Business 2, from the table we know that there have a previous conversation which is Message 1

  Scene 2
  Member 1 sent a message to Business 2 & Member 3, from the table we know that there is no previous conversation

I've tried before using UNION, IN for the checking query but basically is just get back all the list. Is there anyone can give me a help? Thanks.

UPDATE

I can solve scene 1 by using but failed to suit scene 2

    SELECT MessageId FROM M_MessageUser
    WHERE (MemberId IN (0,1) AND BusinessId IN(0,2))
    GROUP BY MessageId
    EXCEPT
    SELECT MessageId FROM M_MessageUser
    WHERE (MemberId NOT IN (0,1) OR BusinessId NOT IN(0,2))
    GROUP BY MessageId;

I would suggest to handle your scenario using the application rather than using the DB. For example, if a member creates a message:

Check if the user in the message exists. Query the DB for this.
Based on the result from the query above: if the user exists, update
the appropriate row (DB update).
If the user does not  exist, insert the row (DB insert).

Note that in the above scenario, the application is making the logical decisions. If you want the DB to do this logical processing, then you can use an instead of insert trigger.


What are the best ways to thrash SQL Server? I have an issue where a software driven replication system will fail to fully replicate when the main DB is under heavy load. For example if we execute a sproc 500 times, maybe 450 will actually stick. BUT only when we are under crazy DB load (one example is the SAN admins updating firmware during business hours). I'm concerned creating transactions for each message, and waiting for them to timeout, and rollback as there is the potential for the destination SQL server to be on WAN in another datacenter.

Inquiry: I want to thrash my DEV SQL server so hard to replicate massive locking/cxpacket waits in development. But not enough to totally deadlock all access for hours, just maybe to simualate packet loss.

Scenario:
Server B (utility machine) has a local SQL Express instance. It is used for transient data.
It fires off a series of sprocs against the destination database to insert new rows, and then insert new rows into child tables linked by PK's. So on and so on.

Usually it works just dandy. Except the production DBA's don't let the subject matter experts on this system have any control, or visibility into lock stats. I have full control and can profile the system that is propagating data to the destination, but no analysis as to what happens at the other end, other than waiting.. waiting.. waiting... Am I locked? I dunno!

I want to simulate this in the lab and try to tune the sprocs that are used. I'm thinking of a heartbeat update anx xp_cmdshell update to a bookkeeping table and also using ping analysis using xp_cmdshell to query /refine/tune transactions, and rollback transactions if necessary, in the event that all are not committed. Then try again, maybe on another distributed host.

Problem:
The last time this happened was due to the fact that TEMPDB was HUGE (massive temp tables being utilized) and had to page. DBA's said there was nothing wrong, but also reported disk queue lengths obove 50+ for hours at a time. Gotta love it.

Just found out about Hammer DB which is an open-source, configurable load generator.  We're using it for VM overhead evaluation.  You should be able to pick just the right number of simulated users to stress your system without breaking it.


mysql custom query logging for inter-software data exchange Some 2-3 years I had written some triggers (one per table) in SQL server allowing me to log queries in our main software.
ALTER TRIGGER [dbo].[SUIVI_REQUETE_CAT_CATBRUT] ON [dbo].[CAT_CATBRUT] AFTER INSERT, DELETE, UPDATE

AS
DECLARE
  @date as nvarchar(10),
  @heure as nvarchar(15),
  @nomUtilisateur as nvarchar(50),
  @poste as nvarchar(50),
  @requete as nvarchar(MAX)
BEGIN
  set @requete = 'DBCC INPUTBUFFER(' + str(@@SPID) + ')'

  CREATE TABLE temporaire (EventType nvarchar(30), Parameters int, EventInfo nvarchar(MAX))
  insert into temporaire exec(@requete)

  set @requete = (SELECT EventInfo FROM temporaire)
  drop table temporaire

  set @poste = (SELECT HOST_NAME() AS HostName)
  set @nomUtilisateur = (SELECT SUSER_NAME() LoggedInUser)
  set @date =(SELECT CONVERT(VARCHAR(10), SYSDATETIME(), 111) AS [YYYY/MM/DD])
  set @heure =(select CONVERT(VARCHAR,getdate(),108))

  INSERT INTO INF_LOG_REQUETE (REQUETE, POSTE, NOM_UTILISATEUR, [DATE], HEURE) VALUES (LEFT(@requete, 4000), @poste, @nomUtilisateur, @date, @heure)
End

I'm now at the head of a new web division and one of our goal is allow my web-software to share data with our main software. I would like to save some key rows in the tables (customer, vehicle, employee, calendar, etc.) we want to share between both softwares.
I have three questions regarding my problem:

I would like to log the query only for a specific database user (we do not want to get stuck in an infinite loop of sending back and forth the same modification). In SQL server I used that to detect the user:
set @nomUtilisateur = (SELECT SUSER_NAME() LoggedInUser)

Is it possible to detect the user that executed the query in MySQL?

In SQL server, I was able to use the same trigger on insert, update and delete actions.

Is it possible to do the same in MySQL or do I have to write three different trigger from every table?

If the same trigger can be used for multiple action (insert, update, delete), is it possible to detect which action (insert, update, delete) launched the trigger?

Additional note: I want to record only specific fields (containing the key data to share) in the table. I really want to write a trigger for every table so a PHP function can automatically parse the field with data and build an XML file. By example, I want a field in my log table containing:
{table}:[vehicle], {action}:[insert], {field}:[make], {value}:[acura], {field}:[model], {value}:[S7], ...

And produce XML like the following:
<LIST>
    <QUERY>

vehicle

        <ACTION>insert<ACTION>
        <FIELD>make</FIELD>
        <VALUE>acura</VALUE>
        <FIELD>model</FIELD>
        <VALUE>S7</VALUE>
        ...
    </QUERY>
    <QUERY>
        ...
    </QUERY>
    <QUERY>
        ...
    </QUERY>
</LIST>

Thank you

Ok, after some research, here's the answers I found.

Q1: Is it possible to detect the user that executed the query in MySQL?

It is possible to detect with which user by using: select user();

http://dev.mysql.com/doc/refman/5.0/en/information-functions.html#function_user

Beware: In triggers and events current_user() returns the user who defined the object (in SP and views unless if defined with the SQL SECURITY INVOKER). You really need to use the user() function to return the invoker in those contexts.

http://dev.mysql.com/doc/refman/5.0/en/information-functions.html#function_current-user

Q2: Is it possible to fire more than one action (INSERT, UPDATE, DELETE) on the same trigger?

Most unfortunately, at the current time (2016/02/01), it is not possible to fire two actions on the same time in MySQL. The best we can do is to regroup the common coding within a stored procedure.

The following procedure is one possible way to work around this problem. It expects in input:

callingTable: The name of the table for which we want to record the query (must be in the white list).
action: Either 'DELETE', 'INSERT' or 'UPDATE'.
fieldsValues: a string containing a valid pair of field/value in XML. By example: <field>ID</field><value>1616</value>

CREATE PROCEDURE `dataLinkRecorder` (
IN callingTable VARCHAR (25),
IN action ENUM('DELETE', 'INSERT', 'UPDATE'),
IN fieldsValues TEXT
)
BEGIN
DECLARE callingUser VARCHAR(25) DEFAULT '';
DECLARE fieldCount SMALLINT UNSIGNED DEFAULT 0;
DECLARE fieldCCount SMALLINT UNSIGNED DEFAULT 0;
DECLARE valueCount SMALLINT UNSIGNED DEFAULT 0;
DECLARE valueCCount SMALLINT UNSIGNED DEFAULT 0;

IF (TRIM(fieldsValues) <> '') THEN

    /* the calling table must be one in the white list */
    SELECT CASE
        WHEN UPPER(callingTable) IN (
            'CATEGORY',
            'CUSTOMER',
            'MAKE',
            'MODEL',
            'PRODUCT',
            'VEHICLE'
        )
        THEN TRIM(UPPER(callingTable))
        ELSE ''
        END AS myTable
        INTO callingTable;

     SELECT TRIM(
     UPPER(
        IFNULL(USER(), 'CLIENT')
     )
     ) AS myUser
INTO callingUser;

/* do not record modifications from tier software */
IF (callingTable <> '' AND INSTR(callingUser, 'CLIENT') < 1) THEN

    SELECT IFNULL(
        ((CHAR_LENGTH(fieldsValues) - CHAR_LENGTH(REPLACE(fieldsValues, '<field>', ''))) / 7)
    , 0) AS fCount,
    IFNULL(
        ((CHAR_LENGTH(fieldsValues) - CHAR_LENGTH(REPLACE(fieldsValues, '</field>', ''))) / 8)
    , 0) AS fCCount,
    IFNULL(
        ((CHAR_LENGTH(fieldsValues) - CHAR_LENGTH(REPLACE(fieldsValues, '<value>', ''))) / 7)
    , 0) AS vCount,
    IFNULL(
        ((CHAR_LENGTH(fieldsValues) - CHAR_LENGTH(REPLACE(fieldsValues, '</value>', ''))) / 8)
    , 0) AS vCCount
    INTO fieldCount, fieldCCount, valueCount, valueCCount;

    /* validate the field/value XML pair */
    IF (fieldCount > 0 AND
        valueCount > 0 AND
        fieldCount = fieldCCount AND
        valueCount = valueCCount AND
        fieldCount = valueCCount
    ) THEN

        INSERT INTO `pc_datalink`
            (`table_name`, `query_xml`)
        VALUES(
            callingTable,
            CONCAT(
                '<query>',
                    '<table>', callingTable, '</table>',
                    '<action>', action, '</action>',
                    fieldsValues,
                '</query>'
            )
        );
    END IF;
END IF;
END IF;
END //

Will insert values in the following table:

CREATE TABLE `pc_datalink` (
    `id` int(10) UNSIGNED NOT NULL,
    `table_name` varchar(25) COLLATE utf8_unicode_ci NOT NULL,
    `query_xml` text COLLATE utf8_unicode_ci NOT NULL,
    `date_added` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    KEY `AK_DATE_ADDED` (`date_added`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

By example, a very simple trigger like this one:

CREATE TRIGGER `dataLinkDeleteVehicle` AFTER DELETE ON `pc_vehicle`
FOR EACH ROW
BEGIN
IF (OLD.original_id <> '') THEN

    CALL dataLinkRecorder(
        'VEHICLE',
        'DELETE',
        CONCAT(
            '<field>ID</field><value>',
            OLD.original_id,
            '</value>'
        )
    );
END IF;
END //

Will generate a line in the table like this one:

Don't hesitate to ask for details if you still have questions,

Good success everyone


MySQL H.A with automatic failover MySQL has a lot of different highly available solutions, I would like to know what are the differences between all the current implementations, here are the implementations that look currently up to date :

MySQL Group Replication (by Oracle)
MySQL NDB Cluster (by Oracle)
Galera Cluster (by Galera)
XtraDB Cluster (by Percona)
MySQL Fabric Cluster (by Oracle)
MySQL Orchestrator (by Github)

What would be the recommended options (or any others) for a high available setup with automatic failover ? (For something equivalent of the SQL Server DB Mirroring with synchronous replication)

Galera == PXC == MariaDB w/Galera -- These are close to the same.
For HA, you need 1 (or more) node in each of 3 datacenters.  (Think earthquakes, tornados, floods, even datacenter outages -- which do happen.)

Oracle's Group Replication is quite new, but has a lot of promise.

Oracle's "Fabric" is probably being abandoned.  (Probably because of a hard-to-solve single-point-of-failure.)

Orchestrator is very good at failing over arbitrary trees (think relay-servers) of lots (even hundreds) of slaves.  Not sure about Master failover.  (See lectures from Booking.com.)

MHA is pretty good -- But when it discovers a "split brain" situation, it requires manual intervention.

NDB Cluster has a number of different things in its "engine".  That is, it is not a 'drop-in' replacement for InnoDB, which all of the rest support.  For one thing, it employs "eventual consistency", wherein you need to specify how to resolve conflicts.

I think the Galera-based solutions, plus Group replication are the only ones that will automatically repair a failed server when it is brought back up.  (I could be wrong here.)

My preference today:  Percona's PXC or MariaDB with Galera -- each builds in and supports Galera.  But I am keeping my eye on Group Replication.


Use the Windowing Function ROW_NUMBER().

SELECT SLIPNO, EMPNO, DT,
ROW_NUMBER() OVER (PARTITION BY EMPNO ORDER BY DT ASC) AS SLIPNUMBER
FROM SLIPS;

DB Fiddle Test to show an example.

Also note that you should not use DATE as a column name, as it is a reserved word.


Clarification on Cloud Computing Database This question can be very generic, but I am trying to understand the concept of Cloud Databases.

Let's say I have a Web Application, a Desktop Application, and Apps for my Web Application.

All of my Apps, Web, and Desktop applications communicate with the Database when it is online.

My question is, if there is a situation to work offline, can I work offline on the Desktop App and later sync with Cloud Database?

Just like Dropbox, we can add files to the Dropbox in an offline mode.When Dropbox goes online, Folder will get sync automatically and update all the systems like iPhone - App or other Computer Dropbox folders.

Nope, you cannot work offline and then sync your changes. But this is a nice idea for a product! :)


Optimizing table for timeseries Postgres data table I have the below table which maintains a timeseries result. The row only becomes relevant when the signal is true, When signal is false, it just marks that for that particular timestamp we got a result but it is not a valid one, so the res and other columns just contains null values. When signal is null, it marks that we are yet to receive result for this timestamp. The signal is very sparse in nature, it is only true for maybe less than 7% of the records. Also the inserts made to this table are not ordered according to timestamp, older dates could arrive at later time.
CREATE TABLE public.res
(
 pid integer NOT NULL,
 aid integer NOT NULL,
 cid integer NOT NULL,
"time" timestamp without time zone NOT NULL,
 signal boolean,
 price numeric,
 res double precision[] NOT NULL,
 ...<Many more columns of numeric/numeric array data types>
 CONSTRAINT res_pkey PRIMARY KEY (pid, aid, cid, "time")
)

This table can contains millions of records and is growing exponentially as my database is growing. I want to optimize this table. So have following questions

Is each row the same size? Or since the row only makes sense if Signal is true, can it be dynamically sized? Hence keeping the overall size of the table low? A min row size would contain (pid,aid,cid,time,signal,price) and the max will additionally contain (res and the remaining columns)? Is it possible to this in Postgres with its dataTypes? I do not want to create separate tables because run time joins could be very expensive when there are millions of records.

This SO answer says "In effect NULL storage is absolutely free for tables up to 8 columns." , I have many more columns

Any other suggestion that you might have to deal with such problems?

I read about timescaleDB, but since the records do not get inserted in order of timestamps does it has any advantage over Postgres in this usecase?

Thanks

If you were seriously considering switching database systems, I could tell you Microsoft SQL Server already has an out of the box feature for sparse columns that fits your use case we'll. But my recommendation would be to not look to change database systems only just to optimize data storage. PostgreSQL is a very capable database system itself.
To answer your question, NULL values only take up 1 bit of data regardless of how many columns there are, and therefore is very lightweight so it reflects your sparseness accurately.


Max number of user connections In SQL Server 2012 Standard edition, I know that the max number of user connections is 32,767. What should I do as a DBA if I am heading towards this number?

Currently there are 30,000 user connections, and this number is expected to increase.

The maximum number of connections across SQL Server versions and editions is 32,767.
You can determine how many connections SQL Server currently has by looking at:
SELECT ConnectionStatus = CASE WHEN dec.most_recent_sql_handle = 0x0
        THEN 'Unused'
        ELSE 'Used'
        END
    , CASE WHEN des.status = 'Sleeping'
        THEN 'sleeping'
        ELSE 'Not Sleeping'
        END
    , ConnectionCount = COUNT(1)
FROM sys.dm_exec_connections dec
    INNER JOIN sys.dm_exec_sessions des ON dec.session_id = des.session_id
GROUP BY CASE WHEN des.status = 'Sleeping'
        THEN 'sleeping'
        ELSE 'Not Sleeping'
        END
    , CASE WHEN dec.most_recent_sql_handle = 0x0
        THEN 'Unused'
        ELSE 'Used'
        END;

If the ratio between used and unused connections from the above query is concerning, it is likely that connection pooling is enabled by client applications connected to the server, and those connections are not being efficiently used.  You may want to have developers modify the connection string for these applications to limit the size of the connection pool, and ensure they are properly disposing connections.  If connections are not being disposed correctly, they will remain open as long as the client application is running.
If you are feeling particularly rabid, and need to get rid of all the connections that haven't performed anything recently (regardless of if they are actually currently performing work), you can run the following code, which will generate a list of sessions that can be killed.  You'd need to copy-and-paste the generated commands into a new SSMS window to actually run the commands.  I'd also recommend having your résumé up-to-date just in case.

DECLARE @cmd NVARCHAR(MAX);
SET @cmd = '';
SELECT @cmd = @cmd +
    CASE WHEN @cmd = '' THEN '' ELSE CHAR(13) + CHAR(10) END
    + 'KILL ' + CONVERT(VARCHAR(MAX), dec.session_id) + ';'
FROM sys.dm_exec_connections dec
WHERE dec.most_recent_sql_handle = 0x0;

PRINT @cmd;

It is possible to linearly scale the number of connections beyond 32,767 by sharding the data across multiple SQL Server nodes.  However, in my opinion, using sharding as a way to get around the limit on the number of connections is similar to using an atom bomb to kill a spider.  It will kill the spider, but you just might have bigger problems at the end of the day.  Not to mention it's pretty darn hard to build an atom bomb, not to mention implement sharding properly.


pglogical for OLAP queries I am looking to setup a replica for OLAP queries. Currently in my prod I have a master (OLTP node) replicating to a slave via streaming replica. I don't want to use streaming replica for OLAP queries because I would probably need different set of indexes and permissions on OLAP db. So I am planning to setup a pglogical replica.

Will there be any performance hit on master because of this?

Also I am planning to have master replicating to both a streaming replica (for HA) and a pglogical replica (for OLAP). Is it possible?

Any other implications/side-effects I should be worried about?

Yes, there's some performance impact on the master because of logical decoding. It's not usually too bad.

You can have both streaming physical replication for HA/failover and pglogical in use at the same time. However, currently if you have to fail over, pglogical can't switch to reading from the promoted replica so you'll have to re-create the subscription before you re-enable writes to the master after failover if you want to ensure consistency.


Guidelines for full-text index maintenance What guidelines should be considered for maintaining full-text indexes?

Should I REBUILD or REORGANIZE the full-text catalog (see BOL)?  What is a reasonable maintenance cadence?  What heuristics (similar to the 10% and 30% fragmentation thresholds) could be used to determine when maintenance is needed?

(Everything below is simply extra information elaborating on the question and showing what I've thought about so far.)

Extra info: my initial research

There are a lot of resources on b-tree index maintenance (e.g., this question, Ola Hallengren's scripts, and numerous blog posts on the subject from other sites).  However, I have found that none of these resources provide recommendations or scripts for maintaining fulltext indexes.

There is Microsoft documentation that mentions that defragmenting the b-tree index of the base table and then performing a REORGANIZE on the full-text catalog may improve performance, but it doesn't touch on any more specific recommendations.

I also found this question, but it is primarily focused on change-tracking (how data updates to the underlying table are propagated into the fulltext index) and not the type of regularly scheduled maintenance that could maximize the efficiency of the index.

Extra info: basic performance testing

This SQL Fiddle contains code that can be used to create a full-text index with AUTO change tracking and examine both the size and query performance of the index as data in the table is modified.  When I run the script's logic on a copy of my production data (as opposed to the artificially manufactured data in the fiddle), here is a summary of the results I am seeing after each data modification step:

Even though the update statements in this script were fairly contrived, this data seems to show that there is a lot to be gained by regular maintenance.

Extra info: Initial ideas

I'm thinking about creating a nightly or weekly task.  It seems that this task could perform either a REBUILD or REORGANIZE.

Because the full-text indexes may be quite large (tens or hundreds of millions of rows), I'd like to be able to detect when indexes within the catalog are sufficiently fragmented that a REBUILD/REORGANIZE is warranted.  I'm a little bit unclear on what heuristics might make sense for that.

I wasn't able to find any good resources online, so I did some more hands-on research and thought it would be useful to post the resulting full-text maintenance plan we are implementing based on that research.

Our heuristic to determine when maintenance is needed

Our primary goal is to retain consistent full-text query performance as data evolves in the underlying tables.  However, for various reasons it would be difficult for us to launch a representative suite of full-text queries against each of our databases each night and use the performance of those queries to determine when maintenance is needed.  Therefore, we were looking to create rules of thumb that can be computed very quickly and used as a heuristic to indicate that full-text index maintenance may be warranted.

In the course of this exploration, we found that the system catalog provides a lot of information about how any given full-text index is divided into fragments.  However, there is no official "fragmentation %" computed (as there is for b-tree indexes via sys.dm_db_index_physical_stats).  Based on the full-text fragment information, we decided to compute our own "full-text fragmentation %".  We then used a dev server to repeatedly make random updates of anywhere between 100 and 25,000 rows at a time to a 10 million row copy of production data, record the full-text fragmentation, and perform a benchmark full-text query using CONTAINSTABLE.

The results, as seen in the charts above and below, were very illuminating and showed the the fragmentation measure we had created is very highly correlated to the observed performance.  Since this also ties out with our qualitative observations in production, this is enough that we're comfortable using the fragmentation % as our heuristic for deciding when our full-text indexes need maintenance.

The maintenance plan

We have decided to use the following code to compute a fragmentation % for each full-text index.  Any full-text indexes of non-trivial size with fragmentation of at least 10% will be flagged to be re-built by our over-night maintenance.

-- Compute fragmentation information for all full-text indexes on the database
SELECT c.fulltext_catalog_id, c.name AS fulltext_catalog_name, i.change_tracking_state,
    i.object_id, OBJECT_SCHEMA_NAME(i.object_id) + '.' + OBJECT_NAME(i.object_id) AS object_name,
    f.num_fragments, f.fulltext_mb, f.largest_fragment_mb,
    100.0 * (f.fulltext_mb - f.largest_fragment_mb) / NULLIF(f.fulltext_mb, 0) AS fulltext_fragmentation_in_percent
INTO #fulltextFragmentationDetails
FROM sys.fulltext_catalogs c
JOIN sys.fulltext_indexes i
    ON i.fulltext_catalog_id = c.fulltext_catalog_id
JOIN (
    -- Compute fragment data for each table with a full-text index
    SELECT table_id,
        COUNT(*) AS num_fragments,
        CONVERT(DECIMAL(9,2), SUM(data_size/(1024.*1024.))) AS fulltext_mb,
        CONVERT(DECIMAL(9,2), MAX(data_size/(1024.*1024.))) AS largest_fragment_mb
    FROM sys.fulltext_index_fragments
    GROUP BY table_id
) f
    ON f.table_id = i.object_id

-- Apply a basic heuristic to determine any full-text indexes that are "too fragmented"
-- We have chosen the 10% threshold based on performance benchmarking on our own data
-- Our over-night maintenance will then drop and re-create any such indexes
SELECT *
FROM #fulltextFragmentationDetails
WHERE fulltext_fragmentation_in_percent >= 10
    AND fulltext_mb >= 1 -- No need to bother with indexes of trivial size

These queries yield results like the following, and in this case rows 1, 6, and 9 would be marked as being too fragmented for optimal performance because the full-text index is over 1MB and at least 10% fragmented.

Maintenance cadence

We already have a nightly maintenance window, and the fragmentation calculation is very cheap to compute.  So we will be running this check each night and then only performing the more expensive operation of actually rebuilding a full-text index when necessary based on the 10% fragmentation threshold.

REBUILD vs. REORGANIZE vs. DROP/CREATE

SQL Server offers REBUILD and REORGANIZE options, but they are available only for a full-text catalog (which may contain any number of full-text indexes) in its entirety.  For legacy reasons, we have a single full-text catalog that contains all of our full-text indexes.  Therefore, we have opted to drop (DROP FULLTEXT INDEX) and then re-create (CREATE FULLTEXT INDEX) on an individual full-text index level instead.

It might be more ideal to break full-text indexes into separate catalogs in a logical way and perform a REBUILD instead, but the drop/create solution will work for us in the meantime.


How do you concatenate an entire column with a group by? Today, I saw that our residents have put a bajillion hours into an excel spreadsheet, tracking everything they need to know about our patients.

The excel sheet is really good for humans to read from, but absolutely horrendous for machines. I'd like to help them out, but I'm stuck on one part: creating a list of every diagnosis per patient - and presenting it in a single field.

I've got this, which will concatenate the entire table:

    select stuff( (
    select top 12  ', ' + icd10 + ' ' + icd10.description   from
    AdmVisitDiagnoses dx
    left join ehs_icd10 icd10 on replace(dx.Code,'.','') = icd10.icd10
    where icd10 is not null
    for xml path ('')),1,2,'')

which will produce a result like this:

I10 Essential (primary) hypertension, E780 Pure hypercholesterolemia, E780 Pure hypercholesterolemia, I10 Essential (primary) hypertension, I8291 Chronic embolism and thrombosis of unspecified vein, I8291 Chronic embolism and thrombosis of unspecified vein, F205 Residual schizophrenia, F209 Schizophrenia unspecified, F259 Schizoaffective disorder unspecified, F259 Schizoaffective disorder unspecified, F259 Schizoaffective disorder unspecified, F259 Schizoaffective disorder unspecified

but I think I'm barking up entirely the wrong tree. Also, the performance is horrendous (not a deal breaker, since it will be automated, but still something to be mindful of).

Question: How would you break this up by a foreign key?

Some sort of pivot? The number of diagnoses is quite variable...

Ideally, it would be something I can easily smush into a larger query, but there's obviously ways to work around that if needed.

Here's my current solution, but I'm pretty sure it's not a good one:

    create function dbo.stuffDx(@VisitID varchar(55))
    returns varchar(max)

    as
    begin
    declare @string varchar(max)

    set @string = (select stuff( (
    select top 500  ', ' + isnull(icd10,'') + ' ' + isnull(icd10.description,'')   from
    AdmVisitDiagnoses dx
    left join ehs_icd10 icd10 on replace(dx.Code,'.','') = icd10.icd10
    where icd10 is not null
    and VisitID = @VisitID
    for xml path ('')),1,2,''))

    return @string

    end

Here's the data:

AdmVisitDiagnoses:

    VisitID             Code
    OL0-20100812131135  250.00
    OL0-20100812131135  295.90
    OL0-20100812134136  250.00
    OL0-20100812134136  401.9
    OL0-20100812134506  250.00
    OL0-20100812134506  401.9
    OL0-20100812135224  295.30
    OL0-20100812135224  250.00
    OL0-20100817085834  295.90
    OL0-20100817085834  272.0

icd10:

    icd10   description
    A000    Cholera due to Vibrio cholerae 01 biovar cholerae
    A001    Cholera due to Vibrio cholerae 01 biovar eltor
    A009    Cholera unspecified
    A0100   Typhoid fever unspecified
    A0101   Typhoid meningitis
    A0102   Typhoid fever with heart involvement
    A0103   Typhoid pneumonia
    A0104   Typhoid arthritis
    A0105   Typhoid osteomyelitis
    A0109   Typhoid fever with other complications

You are "barking up entirely the wrong tree"!

What you want is something like:

CREATE TABLE patient_diagnosis
(
  patient_id INTEGER   NOT NULL, -- FK to patient table
  admission_time TIMESTAMP NOT NULL,
  diagnosis_id INTEGER NOT NULL, -- FK to diagnnosis table. Under **no** circumstances
                                 -- allow this to be free text! You might want to allow
                                 -- the text in here but from a dropdown list!
  initial_diagnosis_time TIMESTAMP NOT NULL,
  condition_cleared_time TIMESTAMP -- nullable because a patient might have an incurable
                                   -- condition like Cystic Fibrosis or Alzheimer's?
);

PRIMARY KEY something like (patient_id, diagnosis_id, initial_diagnosis_time)
or (patient_id, admission_time, diagnosis_id) depending on your needs.

So, for a patient with multiple diagnoses, you will have multiple records - you'll be able to SELECT current diagnoses and history. This is ideal for spreadsheets and much easier than a long comma-separated list.

SQL is not good at manipulating such lists - it's brilliant at "slicing and dicing" small, simple atomic records. For example, "How many people with a diagnosis of bubonic plague were admitted in the first quarter of last year?".

From here (Codd's rules):

  Rule 2: The guaranteed access rule:

  Each and every datum (atomic value) in a relational data base is
  guaranteed to be logically accessible by resorting to a combination of
  table name, primary key value and column name.

Comma separated lists violate this basic rule derived from relational algebra which is the only mathematically sound theory underlying data storage and manipulation!


Duplicate column or foreign key lookup? I have a base user profile table that stores data for users. Each user is able to upload multiple pictures so this is represented by a separate photo table.

When the user profile is queried, the entirety of the user's photos are not necessarily required, only the thumbnail of the first photo (profile picture). Only a further action will require the rest of the photos.

I would like to know if it is optimal to store a duplicate of the thumbnail of the profile photo on the user profile table or should I continue to do a complex nested query to fetch the thumbnail of the profile?

Profile

user_id | avatar --(cached copy of profile[0].thumbnail)

Photo

user_id | image | thumbnail | order

Unless your database is inefficient enough to read the whole row when you ask for just the IDs and the thumbnail, then don't duplicate the data. The join or sub-query to add the thumbnail when needed will not be "complex" for any good database system. You have either

SELECT <stuff>
FROM   Profile
LEFT OUTER JOIN
       Photo
       ON  Photo.user_id = Profile.user_id
       AND Photo.order = 0

or

SELECT <profilestuff>
     , thumbnail = (SELECT thumbnail
                    FROM   Photos
                    WHERE  Photo.user_id = Profile.user_id
                    AND    Photo.order = 0
                )
FROM   Profile

You'll want a the Photos table to have an index on user_id, order but you'll probably have that anyway as it is the only candidate in this table layout for the primary key.

In either case you could create a view that does this instead of including the extra lookup in all other queries that reference the profiles and need to know the thumbnail.

If you duplicate the thumbnail you have to implement keeping that cached copy in sync with the other data, and it will be wasteful of space if you are storing the actual binary file not just the filename or other reference.

You might want to add a thumbnail id to the profile table to allow the user to pick an image that isn't the first one (if they have them ordered chronologically for general display but think one from the middle best represents them, for instance).


Find out what setting were used for a given stored procedure As a follow-up to my previous question:

is there a way to need to find out what the ANSI_NULLS and ANSI_PADDING etc. settings where when a given procedure was created in my database ?

It seems my customer there has several stored procedures and a few triggers that have been "tweaked" - but unfortunately, using a "wrong" set of those database settings. Can I find out which procs and triggers are affected, or do I need to re-create all of them with the proper settings?

This can be determined from sys.sql_modules:

USE tempdb;
GO
SET ANSI_NULLS ON;
GO
CREATE PROCEDURE dbo.foo1 AS SELECT 1;
GO
SET ANSI_NULLS OFF;
GO
CREATE PROCEDURE dbo.foo2 AS SELECT 1;
GO

SELECT name = OBJECT_NAME([object_id]), uses_ansi_nulls
  FROM sys.sql_modules
  WHERE OBJECT_NAME([object_id]) IN (N'foo1', N'foo2');

GO
DROP PROCEDURE dbo.foo1, dbo.foo2;

Results:

name   uses_ansi_nulls
----   ---------------
foo1   1
foo2   0

Some other useful columns:

object_id
definition
uses_ansi_nulls
uses_quoted_identifier
is_schema_bound
uses_database_collation
is_recompiled
null_on_null_input
execute_as_principal_id

Note that some settings (e.g. arithabort, ansi_warnings, ansi_padding) are not stored with the object. Also you will need to recompile the procedures with the correct settings for any that have been identified as "wrong."


Does Mysql locks table while insert operation I am using Mysql(INNODB) and does Mysql locks table when I do some insert operation?

I have a huge table and it has lots of indexes. I do some research and Mysql doc says INNODB only locks rows not the entire table but I have indexes and does it still only locks rows? My insert queries nearly takes 36 seconds.

Query_time: 35.829970

InnoDB will only lock the row.  But it will eventually have to update all the indexes; however this is mostly deferred.

Was something else going on while the INSERT was taking 36 seconds?  Perhaps a SELECT ... FOR UPDATE?  Or even a SELECT can block the INSERT in certain circumstances.

Next time it happens, quickly do SHOW FULL PROCESSLIST to see what else is running.  Then do SHOW ENGINE InnoDB STATUS to (hopefully) get some info specific to the INSERT and why it is blocked.

What version are you running?  Newer versions have tables to help get more info, even after the fact.


How can I filter an extended event session by is_cache attribute? I created an extended event to log all successful logins into my prod server. As you can guess there are hundreds of them coming in one second so I just need to filter some of those according to whether it is a cached connection or not. I couldn't create a filter on is_cached attribute of login event. I already filtered by [sqlserver].[client_app_name] attribute. When I watch Live Data, I can filter on is_cached attribute btw.

Here is the script of event:

CREATE EVENT SESSION deneme ON SERVER
ADD EVENT sqlserver.login(    ACTION(sqlserver.client_app_name,sqlserver.client_hostname,sqlserver.username)
    WHERE ([sqlserver].[client_app_name]<>N'Microsoft SQL Server Management Studio - Transact-SQL IntelliSense')
ADD TARGET package0.event_file(SET filename=N'D:\_VTY\LastLoginAudits\audit_logon.xel')
WITH (MAX_MEMORY=4096 KB,EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS,MAX_DISPATCH_LATENCY=30 SECONDS,MAX_EVENT_SIZE=0 KB,MEMORY_PARTITION_MODE=NONE,TRACK_CAUSALITY=OFF,STARTUP_STATE=OFF)

I tried adding a predicate in where clause like this but it didn't work:

WHERE ([sqlserver].[client_app_name]<>N'Microsoft SQL Server Management Studio - Transact-SQL IntelliSense')
    AND [sqlserver].[is_dac]<>N'True')

Thanks in advance.

You just need to specify is_cached, without prefixing it with "sqlserver.":

WHERE is_cached = 0


how should i do this trigger? I am a newbie to the sql world :)
Right now i'm trying to create my first mysql trigger that would either update a row or insert a new one. Could you help me with that ?

Here is the code :

DELIMITER //
CREATE TRIGGER trigger_vote_question_after_insert AFTER INSERT ON vote_question
FOR EACH ROW
BEGIN
IF ((SELECT question_total_mixed FROM vote_total_question WHERE question_id = NEW.question_id) > 0) AND NEW.vote_question_type = '1')
THEN UPDATE vote_total_question
SET question_total_plus = question_total_plus + 1
SET question_total_mixed = question total_mixed + 1
WHERE question_id = NEW.question_id
ELSEIF ((SELECT question_total_mixed FROM vote_total_question WHERE question_id = NEW.question_id) > 0) AND NEW.vote_question_type = '-1')
THEN UPDATE vote_total_question
SET question_total_plus = question_total_plus - 1
SET question_total_mixed = question total_mixed + 1
WHERE question_id = NEW.question_id
ELSEIF ((SELECT question_total_mixed FROM vote_total_question WHERE question_id = NEW.question_id) = NULL) AND NEW.vote_question_type = '1')
THEN INSERT INTO vote_total_question (question_id, question_total_plus, question_total_minus, question_total_mixed) VALUES (NEW.question_id, 1, 0, 1)
ELSEIF ((SELECT question_total_mixed FROM vote_total_question WHERE question_id = NEW.question_id) = NULL) AND NEW.vote_question_type = '-1')
THEN INSERT INTO vote_total_question (question_id, question_total_plus, question_total_minus, question_total_mixed) VALUES (NEW.question_id, 0, 1, 1)
ENDIF
END//

Also i might try this trigger with postgresql should i change something ? (ELSEIF to ELSE IF I guess)

I would really advise to not have a separate "totals" table that you have to maintain. There would be many downsides to doing so:

under high volume, you might get transactional locks
it's possible that your totals get "out of sync" with the source table. If/when this happens, you'd have to take an outage or block insertion of new votes while you're trying to get the totals correct.

Anyhow, I believe you can get a high-performance, real-time vote totals with simple code, without storing separately any totals information and therefore without needing a trigger. You also won't have to worry about any INSERT vs. UPDATE logic.

Make sure your vote_question table has an index (nonunique) on the question_id column. This will ensure performance for real-time.

Here's a totals query (all questions, summarized):

SELECT question_id
   , SUM((vote_question_type)='1') question_total_plus
   , SUM((vote_question_type)='-1') question_total_minus
   , count(*) question_total_mixed
FROM vote_question
GROUP BY question_id;

If you don't want to have to put that code in the application side, then no problem, just create it as a database view:

CREATE VIEW vote_question_totals
  AS
SELECT question_id
   , SUM((vote_question_type)='1') question_total_plus
   , SUM((vote_question_type)='-1') question_total_minus
   , count(*) question_total_mixed
FROM vote_question
GROUP BY question_id;

Then from your application/client side, you can reference the view just as though it were a table (and provide a question_id).

SELECT question_id, question_total_plus, question_total_minus, question_total_mixed
  FROM vote_question_totals
  WHERE question_id = '???' ;


Postgresql: Modify one value inside of a nested json column I have a json column called "Parameters" in a "Assets" table. The jsons are nested and have the following structure:
{
"params1": {"k": 1.1, "efficiency": 0.7},
"params2": {"k": 1.9, "C_max": 100.0, "C_min": 10.0}
}

I would like to update only one value "k" in "params2" and keep the rest of the values unchanged. I tried with the following code:
update "Assets"
set "Parameters" = "Parameters"::jsonb || '{"params2":{"k":2.2}}'::jsonb
where "Id" = 185;

This results in
{
"params1": {"k": 1.1, "efficiency": 0.7},
"params2": {"k": 2.2}
}

So the rest of the parameters in "params2" are dropped which is not desired. What would be a way to modify "k" and leave the rest as it is?

You are looking for jsonb_set()
update "Assets"
  set "Parameters" = jsonb_set("Parameters"::jsonb, '{params2, k}', to_jsonb(2.2))
where ...


One of the Database Ownerhas been Removed and corresponding property changes in sql server A database has been created on SQL 2008 by a user named user1 ,on dec 2013.Today ,we are going to remove that user(/owner) from the server.What will be the owner property of the database show then?

You have to change the ownership of each database that that user owns before you can delete the user.

Change ownership using SSMS or you can use T-SQL

use database_name
EXEC sp_changedbowner 'newowner'

Following query allows you to query all databases owned by a particular login.

use master
select database_id, name, suser_sname(owner_sid) from sys.databases where suser_sname(owner_sid) = 'logintoremove'

You could then use this information to bulk update ownership.

use master
DECLARE @name VARCHAR (50) -- database name
DECLARE @oldOwner VARCHAR (50)
DECLARE @newOwner VARCHAR (50)

SET @oldOwner = 'oldownerlogin'
SET @newOwner = 'newownerlogin'

BEGIN
    DECLARE db_cursor CURSOR FOR
    select name from sys.databases where suser_sname(owner_sid) = @oldOwner
END

OPEN db_cursor
FETCH NEXT FROM db_cursor INTO @name

WHILE @@FETCH_STATUS = 0
BEGIN
    BEGIN
        EXEC(
            'USE '+@name +';

            EXEC sp_changedbowner ''' +@newOwner+ '''
            ' )
        END

    FETCH NEXT FROM db_cursor INTO @name
END

CLOSE db_cursor
DEALLOCATE db_cursor


How can I check the status of transactional replication? How can I check if there are commands waiting for being executed in the subscribers and find if there is any error occurring in my publication.

I want to be able to do this programmatically so the replication monitors is not an option.

The objective is to give some feedback to a C# program, so that the program can adapt his behaviour, depending on the possible problems.

The application to do this with is the replication monitor.  To use it log into SQL Server Management Studio and connect to the publisher.  Right click on Replication in the tree and select Launch Replication Monitor (it might not be labeled exactly that).  Connect that to the distributor and you can see the replication status.

This app uses a funky set of custom queries as well as some stored procedures to figure out what's going on.  If you needed to see what's going on you'll want to run SQL profiler against the distributor so you can see how the data is flowing.  That way you can see all the commands that the replication monitor is using.


Proper way to reset/cleanup a connection I have a very simple connection pooler in my application. When handing back a connection I need to ensure that no variables are set and no transaction is still on going. I understand that ROLLBACK; DISCARD ALL; should be enough.

In most cases, if the transaction was successful, it will be committed before handing it over to the pooler and at this point, I will have a warning such as WARNING:  there is no transaction in progress.

Is there any way to execute a ROLLBACK only if we are still in a transaction?
Or is there an other preferred method to cleanup a connection?

Assuming libpq, you can call PQtransactionStatus to find out if there
's a transaction open.

If you only have SQL then you can call

select xact_start <> query_start from pg_catalog.pg_stat_activity where pid=pg_backend_pid();

That round-trip will add latency to the cleanup process, but this will not be a problem so long as there's no process blocked waiting for the freed connection.


Oracle multiple databases only able to access one database I am new to oracle. I've installed oracle 12c(required to be this specific version) in my windows pc with no problem. I created one database named LOCAL using Database Configuration Assistant. Then i created one more database named SIAKDB with same method.
I tried accessing both db using sqlplus system/12qwasZX@localhost/local for the LOCAL db, and sqlplus system/12qwasZX@localhost/siakdb for the SIAKDB db. I can connect to either db without problem. And i also imported dmp files to both db just fine.
However, problem arises when i restarted my pc. I can only connect to SIAKDB. When i try to connect to LOCAL db, i got this error
ORA-12514: TNS:listener does not currently know of service requested in connect descriptor

I have checked that both database are running in Services.msc.

My tnsnames.ora file are as follows:
# tnsnames.ora Network Configuration File: D:\deployed\oracle\product\12.2.0\dbhome_1\network\admin\tnsnames.ora
# Generated by Oracle configuration tools.

SIAKDB =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = siakdb)
    )
  )

LISTENER_SIAKDB =
  (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))

LOCAL =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = local)
    )
  )

LISTENER_LOCAL =
  (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))

LISTENER_ORCL =
  (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))

ORACLR_CONNECTION_DATA =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
    )
    (CONNECT_DATA =
      (SID = CLRExtProc)
      (PRESENTATION = RO)
    )
  )

ORCL =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = orcl)
    )
  )

I've noticed that i created a database named ORCL when installing oracle, tried to access that database but got same error.
ORA-12514: TNS:listener does not currently know of service requested in connect descriptor

Somehow i only managed to access the latest DB i've made. How can i access the SIAKDB? And why does it works first time but not working after i restarted my PC?
Thanks
Edit : running tnsping local i got this:
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = local)))
OK (20 msec)

Database Windows Service Status "Running" does not necessarily mean the database is open and available. The database can be in all states "down|nomount|mount|open" while the Windows Service says "Running".
- C:\>lsnrctl status # will tell if the database service has been exposed/made available.

You should validate the state of each database by:
set ORACLE_SID=<SID>
sqlplus / as sysdba (OS auth. Make sure your user is member of local group ORA_DBA)
SQL>select * from v$instance;

Check the alert_.log for errors. You will find the alert log in your Oracle Software trace folder.
Best of luck.