same table on different commands (inner join) Testing some commands of MySQL, I've come across these two comands resulting on the same table.

Command 1:

select sales.relatory_code, sales.date, destiny.exportation_name from sales, destiny where sales.destiny_exportation_code = destiny.destiny_exportation_code;

Command 2:

select sales.relatory_code, sales.date, destiny.exportation_name from sales inner join destiny on sales.destiny_exportation_code = destiny.destiny_exportation_code;

Basically, instead of using two tables on "from" clause and using the "where" clause, i'm just using "inner join". I would like to know what's the main difference between the two commands and if one is better than the other.

Command 1 is the SQL-89 standard for joining between tables.

Command 2 is the correct method of writing JOINs as per the ANSI-92 SQL standard (yes, that is 92 as in 1992). Some 23 years later and people still don't want to conform to ANSI-join standards.

Theoretically, there is no difference between the two in terms of performance, it's apparently just a hard habit for people to break and some find it more readable if they're used to writing queries in that way.

There's a few more reasons for why people use the old standard as opposed to the new one in this Stack Overflow post.

I also seemingly keep referring back to Aaron Bertrand's posts (even though this one is written about SQL Server). This one may give you some more information on why you should be using the ANSI-92 Standard style joins.


High Availability for postgresql I'm new in PostgreSQL database. Recently our developer needed to do some upgrades in our systems.

Because of that we are planning to implement some method in order to implement database failover.

Based on my reading from postgresql wiki here, we are trying to implement either warm standby or hot standby.  So my questions are:

What are major differences between them?
Which one is better?
Is there any other method that we can consider for making high availability in our Postgres databases?

1a. Warm standby is a "live", incremental backup fed with complete blocks of changes (wal segments) 16 mb each, that are send to the standby node once they're filled. You cannot query a warm standby node. 16 mb of changes (by default) can mean a lot of transactions, should the master fail, they'll be lost.

1b. Hot Standby. (also a "live" incremental backup) .small changes are send to the slave (wal records, which are  tiny parts of a wal segment). You can query (read only) the hot standby node. The window for lost transactions should the master fail is very tiny. There are synchronous and asynchronous hot standby nodes, a synchronous node will force the master to wait for it to confirms the application of changes and then the master will commit the transaction.In asynchronous replication the master sends the wal records and does not wait for confirmation. The former requires a very reliable and fast link between the master and the slave,also adds overhead to the master but guarantees no data loss.

Regarding incremental backups: 1. You take a base copy of your whole database installation. 2. Ship it to the slave. 3. Configure it to catch up for changes.

Streaming Replication (hot standby) is the winner here. I personally prefer asynchronous replication as it does not impose a considerable burden on the master and the replication lag is very small (a couple of seconds in many cases)

One complement to this setup is pg-pool. It acts as a proxy between the application and the servers participating in a replication configuration like the one described above, it has load balancing and parallel query capabilities. It's able also to provide automatic failover.
http://www.pgpool.net/pgpool-web/contrib_docs/simple_sr_setting/index.html


How do you minimize downtime for an ALTER? I've recently inherited a MySQL database that is growing. It's just over a billion records and the performance of one of the tables is abysmal. I would like to change databases eventually, but I do not have the time to do so right now.

The creator of the database decided to use BIGINTs in place of TINYINTs or SMALLINTs, chose poor indices, didn't partition the table (when it could really use one). Unfortunately, every time I try to ALTER this table, it locks it and the alter could run for days, which is something that I can't really have, because the table is constantly growing and needs to be accessible.

I'm using MySQL 5.7.5 and even if I choose an 'In Place' algorithm or no lock, it still seems to affect the performance OR it will run and never finish, like it times-out or something.

Is there a way to quickly alter a table without having much downtime?

Here is the CREATE TABLE:

CREATE TABLE `devicelog` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `site_id` bigint(20) unsigned NOT NULL,
  `system_id` bigint(20) unsigned NOT NULL,
  `device_id` bigint(20) unsigned NOT NULL,
  `field_id` bigint(20) unsigned NOT NULL,
  `val` double DEFAULT NULL,
  `valid` tinyint(1) NOT NULL,
  `timestmp` datetime NOT NULL,
  `unixtime` bigint(20) unsigned DEFAULT NULL,
  `interval5` bigint(20) unsigned DEFAULT NULL,
  `interval15` bigint(20) unsigned DEFAULT NULL,
  `interval60` bigint(20) unsigned DEFAULT NULL,
  `interval1440` bigint(20) unsigned DEFAULT NULL,
  `created` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `site_id` (`site_id`),
  KEY `system_id` (`system_id`),
  KEY `device_id` (`device_id`),
  KEY `field_id` (`field_id`,`device_id`,`system_id`,`site_id`,`valid`),
  KEY `unixtime` (`unixtime`),
  KEY `created_timestmp` (`created`),
  KEY `timestmp` (`timestmp`)
) ENGINE=InnoDB AUTO_INCREMENT=1012220164 DEFAULT CHARSET=utf8 COMMENT='Device log.'

There are two tools you can use to do this.

1) Percona's PT-ONLINE-SCHEMA-CHANGE which can be found here (Percona is a very big hitter in the MySQL world).

and there is also

2) Shlomi Noach's oak-online-alter-table which can be found here. Noach's c.v. is also impressive.

Public awards
Oracle Technologist of the Year: Developer award
Oracle ACE
MySQL Community Member of the Year, 2009 award.

Obviously partitioning could also help your speeds, but if you can't afford to partition now, these tools may be of assistance to you.


"Cannot find data type" restoring a SQL Server 2014 backup on SQL Server 2008 R2 I have been passed a SQL Server 2014 backup that needs to be imported onto one of my SQL Server 2008 R2 servers. The backup is valid as I can restore it onto one of Dev 2014 SQL servers.

I have done the script out task and that works fine for the most part. However one issue I face is that the SQL Server 2014 database had tables using data compression and I get errors when I try to import these to 2008 R2 such as this.

  Column, parameter, or variable #50: Cannot find data type dbo.tCompressedBinary.

What options do I have for this issue? Does anyone recommend any other approach (apart from setting up a SQL Server 2014/2016 prod box) that I could follow?

Actually, tCompressedBinary sounds like it might be a true UDT (a user-defined type implemented using CLR) rather than a UDDT (a user-defined data type, which are just aliases for other types created with syntax like CREATE TYPE creditcardno FROM CHAR(11) NOT NULL;

See the answers to https://stackoverflow.com/questions/35494575/sql-server-udt-versus-uddt for more information and links to MS documentation.

If tCompressed is a UDT then it won't be included in the database scripts you have generated. You will need to install and activate that CLR module in the new SQL Server instance before you can run the scripts to build this schema. Otherwise go with my original answer, below.

If you really can't do that (you don't have permission to turn on CLR on the new instance, the module is a 3rd party one that doesn't support 2008r2 at all, etc.) then you might get away with replacing all references to the type in column and parameter definitions with with what-ever type the application code is compatible with (VARBINARY(MAX) would be my first guess).

If you are using 2008r2 Enterprise Edition then compression is supported so that would not be the problem. In that case, check the definition of the user-defined type tCompressedBinary. Are there errors when it is created? Or is it present in the wrong order (defined in the scripts after it is first used)?

If you are using an edition other then Enterprise, then search the scripts and remove all references to [WITH] COMPRESSION. Of course, there may be other compatibility issues once you have removed all the compression directives which will become apparent once you've tidied up this one.


Is it possible to have the transaction log file write to a disk on a remote machine? For the SQL server transaction log file, it is possible to set its target file to a disk on a remote machine?

I am working on a BI project.  In its current iteration, when the data warehouse loading processes run, the transaction log can balloon (>100 GB) in size before it has a chance to truncate.  There isn't any room to add more disks to this server, and future data loading operations will involve substantially more data and so I don't think there will be enough disk space for the log.  But we have a development server in the same rack with 900+ GB free and I was thinking we could use that free disk space so that bigger loads will work.

It is supported to have database files on network share, but Its inviting a disaster as any network blip might corrupt your database.

  Microsoft recommends that you store database files either on Local Disk or on Storage Area Networks (SANs).

You have to tune your data warehouse loading process. There are many parameters that can be tuned to have minimally logged operations and fast loading of data in sql server. e.g.

Trace Flag 610 - controls minimally logged inserts into indexed tables.
I have listed few items in this answer.

Also, if you want to be proactive, then create an alert for Transaction log full, so you get notified when the log is about to get filled up.

Read up on :

The Data Loading Performance Guide
Load 1TB in less than 1 hour


Data structure design for supporting arbitrary number of columns in table or database I am currently working on a sort-of a meta-modeler to build a free web service so that people can input data and run several models on that data.

The task I am currently struggling is: user needs to enter data column by column, which would consist of a number n of ID's , a number m of attributes and a number k of classes, with the conditions that n, m > 0 and k >= 0. Data is heterogeneous, so indexes can be both numeric or text, and the same goes for attributes and classes. I'm supposing there will be no null in the data for simplicity.

I'm currently thinking on:

Creating a table with more than enough columns (all with null values), so that I can work using only the non-null columns (which will be got from user input). However this would limit the size of the datasets people could input.
Resorting to create an specialized data structure on a programming language, do all the work there and finally, create a table dinamically to store the result data there.
Using a database specialized for this kind of data (maybe a document-based DB).
Create a data structure on the RDBMS itself (I'm using PostgreSQL), let's say a variable size array, so that I can create the table directly from the user input, using only 3 variable arrays (one for indexes, one for attributes and one for classes). However, I keep in mind that attributes and indexes could be of different types, so the array would have to support heterogeneous data type and I don't know if this is possible on a RDBMS or SQL.

I've been looking for information on information but got no result until now. Any guidances to a package, language library, extension or paper, thesis, technical report with relevant information would be appreciated. Also, personal experiences with doing something similar could be useful.

Edit:

Some example records:

ID NAME   CODE  HEIGHT WEIGHT       CLASS1     CLASS2
1  HARRY  100    170    70         SURVIVOR    VICTIM
2  ALBUS  101    185    82
4  REMUS  103    177    60

This is an example dataset where the intention is predicting if a person will survive or die based on its height and weight. The output should be lines consisting of SURVIVOR or VICTIM lines.
The ID's would be ID, NAME and CODE columns; HEIGHT and WEIGHT are the attributes and CLASS1 and CLASS2 are the classes.

The same structure should support something like this:

ID   CODE  INCOME  AGE  NUMBER_OF_HOUSES  CHILDREN   C1    C2   C3
1    101   9000    21       2                NO      HIGH  LOW  MED
2    110   5700    30       1                YES
7    111   4000    37       0                TES

In this example, the idea is to preddict the future level of income of a person, based on it's actual age, income, number of houses and if they have children currently. The result is either HIGH, MED or LOW.

The data in this case would be: ID and CODE represent the ID's, AGE, NUMBER_OF_HOUSES, INCOME and CHILDREN represent the attributes, and C1, C2, C3 represent the classes.

The data structure should support both inputs, even though they have different number of columns for each category. And also, each one of the columns have different data type. The number of columns per category shoudn't be fixed.

Given your "irregular" data, you might want to look at Magneto (see here and here). Originally designed for the clothing industry which can have many different products with many different attributes. It might just be a fit for your needs. There is a community edition so you can look at the code and (esp.) the database table structures and adapt to your own environment.

Be warned though! It is not a panacea - there are problems that come with the added flexibility. See my answer here which explains some of the pitfalls (be sure to follow the links) of this approach (known as EAV).


MySql Event to Delete Data from 2 Tables Older Than 2 Months Im new to MySql events and I never used them before so please to bear with me. With My DB I have order_details and invoices tables which I need to create an Event to delete all the records older than 2 months and where order_details payment_status = 3 and invoices status = 2 and I want the event to make the deleting every day at 3AM How I may accomplish this Any help will be much appreciated
Both tables have timestamp column named created_at
Tables Definition
A - order_dateils :

id
order_bar
description
payment_status
created_at

B - invoices :

id
invoice_id
status
created_at

I tried below command but Im getting errors,
CREATE EVENT IF NOT EXISTS daily_delete ON SCHEDULE EVERY 1 DAY STARTS 2020-09-13 04:00:00 DO BEGIN DELETE FROM bariq_DB.invoice where timestampdiff(MONTH,created_at,NOW()) > 2 and status = 2; delete from bariq_DB.order_details where timestampdiff(MONTH,created_at,NOW()) > 2 and payment_status = 3; END

Errors :
    ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 1

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'END' at line 1

When you have multiple statements you need not only a BEGIN and END
But also a DELIMITER
One error is also dates have to be in quotes as you can see in the code
DELIMITER //
CREATE EVENT
IF NOT EXISTS daily_delete
ON SCHEDULE EVERY 1 DAY STARTS '020-09-13 04:00:00'
DO
BEGIN
    DELETE
    FROM bariq_DB.invoice
    where timestampdiff(MONTH,created_at,NOW()) > 2 and payment_status = 2;
    delete from bariq_DB.order_details
    where timestampdiff(MONTH,created_at,NOW()) > 2 and payment_status = 3;
END;//
DELIMITER ;


Left Join Not returning rows when date is in range of longer than 1 day How do I left join Table A and B?

SELECT dev_hour.hour,rtdogs.delivery_date,rtdogs.delivery_hour,rtdogs.total_offer FROM
(
    SELECT hour FROM
    (
      SELECT * FROM delivery_hour
     ) xx
)dev_hour
LEFT JOIN
(
    SELECT sum(total) as 'total_offer',delivery_hour,delivery_date
                            FROM
                            (
                                SELECT greatest(quan1,quan2,quan3,quan4,quan5,quan6,quan7,quan8,quan9,quan10,quan11) as total,
                                    region_id, resource_id,delivery_date,delivery_hour
                                FROM
                                (
                                    SELECT delivery_hour,delivery_date,resource_id,region_id, quan1,quan2,quan3,quan4,quan5,quan6,quan7,quan8,quan9,quan10,quan11
                                    FROM
                                    (
                                        SELECT ifnull(quantity1,0) as quan1, ifnull(quantity2,0) as quan2,ifnull(quantity3,0) as quan3,ifnull(quantity4,0) as quan4,
                                            ifnull(quantity5,0) as quan5,ifnull(quantity6,0) as quan6,ifnull(quantity7,0) as quan7,ifnull(quantity8,0) as quan8,
                                            ifnull(quantity9,0) as quan9,ifnull(quantity10,0) as quan10,ifnull(quantity11,0) as quan11,delivery_hour,delivery_date,resource_id,
                                            region_id
                                        FROM rtdog  WHERE delivery_date BETWEEN '2016-01-15'  AND '2016-01-15'
                                    )z
                                )y
                             )x  GROUP BY delivery_date,delivery_hour ORDER BY delivery_date
                            ) rtdogs
 ON dev_hour.hour = rtdogs.delivery_hour

I want to show all rows and null rows.
What this code is doing is when you limit the BETWEEN to 1 day it shows the null values but when it's 2 days and more it doesn't show. What's wrong with this code?

Update:
The delivery_hour table has fixed 24 rows.

All these nested derived tables (subselects inside subselects) are useless. You can easily remove all except 1 or 2.

SELECT
    dev_hour.hour,
    rtdogs.delivery_date,
    rtdogs.delivery_hour,
    rtdogs.total_offer
FROM
    delivery_hour AS dev_hour
  LEFT JOIN
    ( SELECT
          sum(greatest(ifnull(quantity1,0), ifnull(quantity2,0), ifnull(quantity3,0),
                       ifnull(quantity4,0), ifnull(quantity5,0), ifnull(quantity6,0),
                       ifnull(quantity7,0), ifnull(quantity8,0), ifnull(quantity9,0),
                       ifnull(quantity10,0), ifnull(quantity11,0) ))
              AS total_offer,
          delivery_hour,
          delivery_date
      FROM rtdog
      WHERE delivery_date BETWEEN '2016-01-15'  AND '2016-01-15'
      GROUP BY delivery_date, delivery_hour
    ) AS rtdogs
  ON dev_hour.hour = rtdogs.delivery_hour ;

Further improvements:

The ORDER BY delivery_date is useless inside a derived table.
Don't use single quotes for quoting table and columns aliases (eg. 'total_offer'). Use backquotes if you have to, if the alias has spaces or other non-allowed characters. In this case, they are not needed at all.

Now the real issue of the question: the result is only 24 rows, no matter what you have in the BETWEEN condition.

The reason is that the delivery_hour table has only 24 rows (it's a guess but judging from the results, it's probably correct.) Therefore, if you want multiple dates in the results, the solution is to use another (say delivery_date) table, with the date values needed or supply one as a derived table. Then CROSS JOIN it to the delivery_hour, so you have all combinations of dates and hours that you need (say 4 days x 24 hours = 96 combinations/rows).

We can then LEFT JOIN to the rtdogs summary result as before, only changing the ON clause to take dates into account as well:

SELECT
    dev_date.date,                         -- add the date to the result
    dev_hour.hour,
    rtdogs.delivery_date,
    rtdogs.delivery_hour,
    rtdogs.total_offer
FROM
    ( SELECT DATE('2016-01-15') AS date UNION ALL
      SELECT '2016-01-16' UNION ALL
      SELECT '2016-01-17' UNION ALL
      SELECT '2016-01-18'
    ) AS dev_date                           -- our added "dates" table
  CROSS JOIN
    delivery_hour AS dev_hour
  LEFT JOIN
    ( SELECT
          sum(greatest(ifnull(quantity1,0), ifnull(quantity2,0), ifnull(quantity3,0),
                       ifnull(quantity4,0), ifnull(quantity5,0), ifnull(quantity6,0),
                       ifnull(quantity7,0), ifnull(quantity8,0), ifnull(quantity9,0),
                       ifnull(quantity10,0), ifnull(quantity11,0) ))
              AS total_offer,
          delivery_hour,
          delivery_date
      FROM rtdog
      WHERE delivery_date BETWEEN '2016-01-15'  AND '2016-01-18'
      GROUP BY delivery_date, delivery_hour
    ) AS rtdogs
  ON  dev_date.date = rtdogs.delivery_date       -- changed here
  AND dev_hour.hour = rtdogs.delivery_hour
ORDER BY                                         -- if we need ordering,
    date, hour ;                                 -- it's done here

You can either build the dates list dynamically (in your PHP/Java/Python code) or have a permanent "dates" table and use it. With a permanent dates table, the FROM would be like:

FROM
    dates_table AS dev_date                      -- the permanent "dates" table
  JOIN
    delivery_hour AS dev_hour
  ON dev_date.date BETWEEN '2016-01-15' AND '2016-01-18'
  LEFT JOIN
    ( SELECT
          -- nothing changes here
    ) AS rtdogs
  ON  dev_date.date = rtdogs.delivery_date
  AND dev_hour.hour = rtdogs.delivery_hour
ORDER BY
    date, hour ;


Improving Oracle SQL Query I'm trying to improve the below code.  The unique identifier in this code is result_label_seq, but I still need to make sure that it's uniquely identified with facility_id and service_code.  The display_seq shows in what order that this is printed on screen, basically in which row.  Row 1, 2, 3, 4....this can go all the way up to 20.

What I'm wanting to do is assign the display_seq + 1 for each entry of the result_label_seq that I have. This is currently how I'm having to write it.

 SELECT * FROM anc_result_seqs WHERE service_code = 'CBC' AND result_label_seq ='87484' AND facility_id = C'

 UPDATE anc_result_seqs SET display_seq = '1' WHERE service_code = 'CBC' AND result_label_seq = '87484' AND facility_id = 'C'
 UPDATE anc_result_seqs SET display_seq = '2' WHERE service_code = 'CBC' AND result_label_seq = '87485' AND facility_id = 'C'
 UPDATE anc_result_seqs SET display_seq = '3' WHERE service_code = 'CBC' AND result_label_seq = '87560' AND facility_id = 'C'
 UPDATE anc_result_seqs SET display_seq = '4' WHERE service_code = 'CBC' AND result_label_seq = '87579' AND facility_id = 'C'

 .....(continued)

Is there a way to do this in Oracle SQL?

Firstly: you should value literals that match the data type of the columns. '1' is a character, where 1 is a number. Avoid any implicit data type conversion.

I assume that display_seq and result_label_seq are really defined as number not varchar in the following statements. If they are indeed varchar columns you should change that. Never store numbers in character columns.

If you just want to increment the values, you can use

UPDATE anc_result_seqs
   SET display_seq = display_seq + 1
WHERE service_code = 'CBC'
  AND facility_id = 'C'

However if you want to renumber the display_seq according to the current ordering that is defined by the column result_label_seq then the following should do:

MERGE INTO anc_result_seqs ars
USING (
   SELECT rowid as rid,
          row_number() over (order by result_label_seq) as rn
   FROM result_label_seq
   WHERE service_code = 'CBC'
   AND facility_id = 'C'
) t ON (t.rid = ars.rowid)
WHEN MATCHED THEN UPDATE
   SET display_seq = rn;


MySQL query to display inserts/updates/deletes/reads per second Within the following query we can see inserts/updates/deletes/reads per second under "ROW OPERATIONS":

mysql> SHOW ENGINE INNODB STATUS\G
*************************** 1. row ***************************
  Type: InnoDB
  Name:
Status:
=====================================
2018-04-03 17:46:05 7f85eadf8700 INNODB MONITOR OUTPUT
=====================================
Per second averages calculated from the last 5 seconds
-----------------
BACKGROUND THREAD
-----------------
...
--------------
ROW OPERATIONS
--------------
0 queries inside InnoDB, 0 queries in queue
0 read views open inside InnoDB
Main thread process no. 22128, id 140215363086080, state: sleeping
Number of rows inserted 436126087, updated 135043544, deleted 45, read 72105720260
51.19 inserts/s, 10.00 updates/s, 0.00 deletes/s, 4545.09 reads/s
----------------------------
END OF INNODB MONITOR OUTPUT
============================

1 row in set (0.00 sec)

Is it possible to perform a query that can easily grab the following data so that it can displayed within an html table some how?

51.19 inserts/s, 10.00 updates/s, 0.00 deletes/s, 4545.09 reads/s

For example to get "reads per second" I've tried querying information_schema.global_status using com_select/uptime but that gives me an all time average. I was hoping to get a per second average if possible..

Maybe I need to store results every so often and compare to those? Not sure..

For example to get "reads per second" I've tried querying information_schema.global_status using com_select/uptime but that gives me an all time average.

Yes, "all time" since the MySQL instance was started.

  Maybe I need to store results every so often and compare to those?

Yes, I think this could be the way to go as I don't think there are any stats in MySQL for historic status variables.

SELECT VARIABLE_NAME, VARIABLE_VALUE
FROM information_schema.global_status
WHERE VARIABLE_NAME IN ('COM_SELECT', 'COM_INSERT', 'COM_UPDATE', 'COM_DELETE');

Then wait for an interval of your preferred length, and then re-run the query, and store the difference somewhere. (Obviously, running the query itself is a SELECT, so it will influence the result, as will an INSERT query be if you store the results in the same MySQL instance.)

For reference: Server Status Variables: Com_xxx


Joins in Update statement in sqlite CREATE TABLE Table_1 (
    Id INTEGER PRIMARY KEY  AUTOINCREMENT  NOT NULL ,
    EmailId VARCHAR
)

INSERT INTO Table_1 VALUES (1,'raj@gmail.com');
INSERT INTO Table_1 VALUES (3,'test@gmail.com');
INSERT INTO Table_1 VALUES (4,'false5@gmail.com');
INSERT INTO Table_1 VALUES (7,'false55@gmail.com');
INSERT INTO Table_1 VALUES (8,'falsefalse@gmail.com');
INSERT INTO Table_1 VALUES (9,'true1@gmail.com');
INSERT INTO Table_1 VALUES (11,'true2@gmail.com');
INSERT INTO Table_1 VALUES (12,'falsefalsefalse@gmail.com');
INSERT INTO Table_1 VALUES (13,'ravi@gmail.com');
INSERT INTO Table_1 VALUES (14,'rak@gmail.com');
INSERT INTO Table_1 VALUES (15,'false@gmail.com');
INSERT INTO Table_1 VALUES (16,'false66@gmail.com');

CREATE TABLE Table_2 (
    Id INTEGER PRIMARY KEY  AUTOINCREMENT  NOT NULL ,
    EmailId VARCHAR
)

INSERT INTO Table_2 VALUES (1,'raj@gmail.com');
INSERT INTO Table_2 VALUES (3,'test@gmail.com');
INSERT INTO Table_2 VALUES (4,'false98@gmail.com');
INSERT INTO Table_2 VALUES (7,'abc@gmail.com');
INSERT INTO Table_2 VALUES (8,'ta@gmail.com');
INSERT INTO Table_2 VALUES (9,'false1@gmail.com');
INSERT INTO Table_2 VALUES (11,'false2@gmail.com');
INSERT INTO Table_2 VALUES (12,'false@gmail.com');
INSERT INTO Table_2 VALUES (13,NULL);
INSERT INTO Table_2 VALUES (14,NULL);
INSERT INTO Table_2 VALUES (15,'false@gmail.com');
INSERT INTO Table_2 VALUES (16,'false66@gmail.com');

I have two tables in sqlite like Table_1 and Table_2

I need to update Entire Table_1 EmailIds Using table_2 EmailIds based on below conditions

condition-1)

need to udpate when Table_1 EmailId<>table_2 EmailId (to all rows)

condition-2)

If Table_2 EmailId is null don't need to update that null value in
Table_1 partucular EmailId

as per the Above Tables with my requirement I want below output:--

1) I want to update  4,7,8,9,11,12 Id's

2) I don't want update 1,3,15,16 Id's(because same emailids in both tables)

3) I don't want update 13,14 Id's (because 13, 14  emailid are null values in Table_2)

For the above problem I wrote below query in MYSQL and SQL Server it's working fine, but in sqlite it's not working.

I found Joins will not work in Update statement in sqlite but I want an alternate solution. Can anybody help me please?

UPDATE Newcontacts n
  JOIN DummyContacts d  ON (n.Id=d.Id)
SET n.EmailId=d.EmailId
WHERE n.EmailId<>d.EmailId
  OR d.EmailId IS NOT NULL

UPDATE Table_1
SET EmailId = (SELECT d.EmailId
                 FROM Table_2 d
                 WHERE Table_1.Id = d.Id
                 )
WHERE EXISTS (SELECT d.EmailId
                 FROM Table_2 d
                 WHERE Table_1.Id = d.Id
                   AND Table_1.EmailId <> d.EmailId
                   AND d.EmailId IS NOT NULL
                 );


Sleeping sessions with an open transaction but empty input buffer I have found out recently, that there are hanging several connections on our dev server (SQL Server 2016) with an open transaction. These transactions have been open for several days. I am using the query as follows:

SELECT session_id, se.database_id, status, open_transaction_count
FROM sys.dm_exec_sessions
WHERE open_transaction_count > 0
AND status = 'sleeping'
AND is_user_process = 1

It returns me several sessions with open_transaction_count=1. When I check the returned session_ids in the  sys.dm_tran_session_transactions and sys.dm_tran_active_transactions the existence of these transactions is proved in these views. All of the transactions have transaction_state = 2.

I would like to see the last SQL statement or batch within these sessions to identify what code in our application causes it and that is the issues. When I use:

DBCC INPUTBUFFER([mySPID])

I will receive a query for some of the session_ids, but no result for the others. The same result is when I try the query as follows:

SELECT se.session_id, se.database_id, se.status, se.open_transaction_count, qry.text
FROM sys.dm_exec_sessions AS se
LEFT JOIN sys.dm_exec_connections AS con ON con.session_id = se.session_id
OUTER APPLY sys.dm_exec_sql_text(con.most_recent_sql_handle) AS qry
WHERE open_transaction_count > 0
AND status = 'sleeping'
AND is_user_process = 1

The interesting is that when I switch to the „problematic databases“ (those with no query text returned) and execute DBCC OPENTRAN the result is „No active open transaction“. I assume that it could be a select statement within an explicit unclosed transaction and so such a transaction is not returned with DBCC OPENTRAN. However, I am not sure with this interpretation.

I would like to know, why I cannot get the last statement/batch for these sessions and whether there is another way (then above mentioned) of how I can get it?

This can sometimes happen when applications are using implicit transactions

It doesn't help that sp_who2 is really confusing -- you're better off with sp_WhoIsActive or sp_BlitzWho to find running queries.

One dead giveaway of implicit transactions is seeing the query text like begin tran or IF @@TRANCOUNT > 0.


How to deal with a person that may have multiple alias names? I am designing a neo4j database which will be primarily be used to store information about people. One of the requirements is that it must be able to store an alias name of a person.

I'm having trouble deciding how I should design my database so that it deals with people that have multiple aliases?

I have two solutions:

Store the persons alias names in a single field, comma separated. This is simple, but makes query-ing difficult.
Store a persons alias name in a separate table/entity called alias. This seems to be overly-complex but makes querying easier than option 1.

Which of these, if any, is the correct answer to the problem? Or alternatively, is there a better solution which I am overlooking?

Option 2 is the right way for graph databases, especially if :Alias nodes all point to the :Person node (or whatever label you use to differentiate a person entry from an alias) who has that alias. The :Person node itself can also have an :Alias label, allowing their name property to also be considered when performing queries.

For example, if you had a graph like this:

(:Alias {name:'Joe Shmoe'})-[:ALIAS_FOR]->(joe:Person:Alias {name:'Joe Jackson'})
(:Alias {name:'Joe Anybody'})-[:ALIAS_FOR]->(joe)
(:Alias {name:'Joseph Jones'})-[:ALIAS_FOR]->(joe)

Then you could issue a query with an $alias parameter like:

MATCH (:Alias {name:$alias})-[:ALIAS_FOR*0..1]->(person:Person)
RETURN person

You should have an index on :Alias(name) for quick lookup, this allows lookup of the person for their name or any alias associated with them (remember their :Person node is also an :Alias node, so this will match too).

And if you need to do case insensitive searches, or searches across multiple fields, then you may want to look at leveraging fulltext schema indexes introduced in Neo4j 3.5.x


Why use custom CLR aggregate functions for string concatenation and geometry unions in SQL Server? I often need to do string concatenation or geometry unions over a column in SQL Server 2008 and I'm aware that you can write custom aggregate functions in .NET and register them with  SQL Server to do these things.

However, you can take a very simple approach to the problem using a local variable and a select e.g.

CREATE TABLE Test(
    ID int IDENTITY(1,1) NOT NULL PRIMARY KEY,
    geom geometry NOT NULL,
    attribute nvarchar(max) NOT NULL,
)
GO

INSERT INTO Test(geom, attribute)
VALUES ('POLYGON ((0 0, 1 0, 2 2, 0 0))', 'shape1'),
('POLYGON ((0 0, 0 1, 2 2, 0 0))', 'shape2'),
('POLYGON ((2 2, 3 2, 3 3, 2 3, 2 2))', 'shape3')
GO

-- string concatenation
DECLARE @mytext nvarchar(MAX) = '';

SELECT @mytext = @mytext + ' ' + attribute
FROM Test;

SELECT @mytext;

-- geometry union
DECLARE @mygeom geometry = 'POLYGON EMPTY';

SELECT @mygeom = @mygeom.STUnion(geom)
FROM Test;

SELECT @mygeom;

Nulls etc. aside, these seem to work fine. So I don't understand why these methods aren't suggested at all in most articles.

What is wrong with calculating aggregates values like this?

Thanks very much.

TSQL is very inefficient for string manipulation compared to .NET (where you can use a StringBuilder for example).

The concatenation approach will allocate a whole load of new strings. It might not be so bad if you could just create a big string up front and use STUFF to replace parts of it but I believe from trying this approach previously that this too just creates a new string behind the scenes.

Additionally the behaviour you are relying on for both cases is undocumented and not guaranteed. "The correct behavior for an aggregate concatenation query is undefined."


Extract specific backupfiles from SQL Server 2008 backup I have a big .bak file containing a full backup and some transaction log backups. Is it possible to extract only the transaction log backups so I can copy them to another PC? (the full backup has already been restored and I don't want to send the big *.bak file again)

Not with the native tools.  Thinking about it, I can see how somebody could write a tool to do it, but there's nothing off-the-shelf.


Find first non-matching character between two strings Posting this with solution provided.

The use case could be framed in a number of ways:

Return all characters that match at the start of two strings, until they do not match
How many characters match between two strings before one character doesn't match?
What is the first character that doesn't match between two strings?
etc.

For example:

If I have the strings 'Interesting' and 'Interested' they are similar for all the characters 'Interest' and then the first string ends in 'ing' while the second ends in 'ed'. Therefore, they have 8 characters in common, the first non-matching character is the 9th and the identical string is 'Interest'.

For the question in the title, the first non-matching character is number 9.

First you should have a Numbers table.

create table dbo.Number(N int not null);
go

alter table dbo.Number add constraint PK_Number_N primary key clustered (N);

go

;with C(N) as
(
  select 0
  union all
  select N+1
  from C
  where N < 11000
)
insert into dbo.Number(N)
select N
from C
option (maxrecursion 0);

Use the numbers table to split your strings into rows. Get the matching characters and position using intersect. Use row_number to get only the matching characters from the start and finally rebuild the string with a for xml trick or string_agg.

declare @Str1 varchar(20) = 'Interesting0a';
declare @Str2 varchar(20) = 'Interested00a';

with C1 as
(
  select N.N,
         substring(@Str1, N.N, 1) as C
  from dbo.Number as N
  where N.N between 1 and len(@Str1)
  intersect
  select N.N,
         substring(@Str2, N.N, 1)
  from dbo.Number as N
  where N.N between 1 and len(@Str2)
),
C2 as
(
  select C1.N,
         C1.C,
         row_number() over(order by C1.N) as rn
  from C1
)
select string_agg(C2.C, '') within group(order by C2.N)
from C2
where C2.N = C2.rn;

/*
select (
       select C2.C as '*'
       from C2
       where C2.N = C2.rn
       order by C2.N
       for xml path(''), type
       ).value('text()[1]', 'varchar(20)');
*/


Cloning mysql installation I need to move a mysql installation to another computer, can I just copy files?
It is vers 4.1, I installed it and copied database but couldn't access it by the program I need. And if I connect to old comp it works fine.

So I need it to be just like on the old one

Full list of steps very depends - what OS (source and target)?
Generally You can copy mysql between 100% similar computers, but You are must copy ALL folders and after - or start mysql manually or manually add MySQL to run as Windows / Linux service.

More easy ways:

If computer Similar - You can install MySQL from installation package and then copy only data folder (name and location depend from OS)
If computer different (like Windows -> Linux) - You can install MySQL on target computer, on Source computer dump all data using mysqldump, on Target computer make restore from dump using command line client


Converting float4 to numeric in PostgreSQL is rounding the values I have a table that has a column storing some coefficients which require high precision. The type of the column is set to float4 and I need to change it to numeric.

But after altering the table, it changes the values I previously had stored in that column.

Eg: 0.0199999996 becomes 0.0200.

The command I used:

ALTER TABLE IF EXISTS table_name ALTER COLUMN column_name TYPE numeric(16, 15) USING column_name::numeric;

Any ideas on how I could change the column type but keep the previous values? I am using postgresql 10.4

The Postgres manual (quoted below) does a good job of explaining some of the rounding issues you might have using floating-point types.
With that in mind, and if we simply accept that the least significant digits of any floating-point type are merely approximate, you have a couple of options for conversion including all the approximated low-precision digits. I've used float4/float8 to keep consistency with your question but we could equally use real/float.:

select '0.0199999996'::float4;

| float4 |
| :----- |
| 0.02   |

select '0.0199999996'::float4::numeric;

| numeric |
| ------: |
|    0.02 |

select '0.0199999996'::float4::float8::numeric;

|            numeric |
| -----------------: |
| 0.0199999995529652 |

select '0.0199999996'::float4::text::numeric;

| numeric |
| ------: |
|    0.02 |

set extra_float_digits = 3;
select '0.0199999996'::float4::text::numeric;

|      numeric |
| -----------: |
| 0.0199999996 |

db<>fiddle here

8.1.3. Floating-Point Types
The data types real and double precision are inexact, variable-precision numeric types. In practice, these types are usually implementations of IEEE Standard 754 for Binary Floating-Point Arithmetic (single and double precision, respectively), to the extent that the underlying processor, operating system, and compiler support it.
Inexact means that some values cannot be converted exactly to the internal format and are stored as approximations, so that storing and retrieving a value might show slight discrepancies. Managing these errors and how they propagate through calculations is the subject of an entire branch of mathematics and computer science and will not be discussed here, except for the following points:

If you require exact storage and calculations (such as for monetary amounts), use the numeric type instead.

If you want to do complicated calculations with these types for anything important, especially if you rely on certain behavior in boundary cases (infinity, underflow), you should evaluate the implementation carefully.

Comparing two floating-point values for equality might not always work as expected.


MySQL Server is Restarting automatically after some minutes I am getting an Issue after everey 2-3 minutes

mysql> show tables;
ERROR 2006 (HY000): MySQL server has gone away
No connection. Trying to reconnect...
Connection id:    57

I am really not able to find why it is happening. I saw to my error log it says

120419  0:36:03  InnoDB: Database was not shut down normally!
InnoDB: Starting crash recovery.
InnoDB: Reading tablespace information from the .ibd files...
InnoDB: Restoring possible half-written data pages from the doublewrite
InnoDB: buffer...
InnoDB: Last MySQL binlog file position 0 93884, file name /EBSmnt/mysql/mysql-bin.000233
120419  0:36:04  InnoDB: Started; log sequence number 1692 2597076235
120419  0:36:04 [Note] Event Scheduler: Loaded 0 events
120419  0:36:04 [Note] /usr/sbin/mysqld: ready for connections.
Version: '5.1.43-enterprise-gpl-advanced-log'  socket: '/var/lib/mysql/mysql.sock'  port: 3306  MySQL Enterprise Server - Advanced Edition (G

Can Anybody please tell How to handle the Scenario.It is on one of the Production Server.

EDIT

RAM is of 7GB.

And InnoDB Configuration in my.cnf are

innodb_additional_mem_pool_size = 16M
innodb_buffer_pool_size = 3G
innodb_data_home_dir = /lvm/mysql
innodb_data_file_path = ibdata1:80G:autoextend
innodb_log_buffer_size = 8M
innodb_log_file_size = 1300M
innodb_log_files_in_group = 3

I suspect your max_allowed_packet and the presence of a large TEXT/BLOB field.

I answered two questions in the past that involved mysql crashing because of the max_allowed_packet being too small.

MySQL server has gone away obstructing import of large dumps
InnoDB table SELECT returns ERROR 2006 (HY000): MySQL server has gone away (after power outage)

I recommend raising your max_allowed_packet to the max value, which is 1GB

If you have TEXT/BLOB fields, you may have entered a TEXT/BLOB field that is too big to pass through a MySQL Packet or through the InnoDB Log Files. You may need to resize your innodb log files to the max value, which is 2047M. Here is what to do:

STEP 01) Add this to /etc/my.cnf

[mysqld]
max_allowed_packet=1G
innodb_log_file_size=2047M

NOTE: In Google Cloud, you can't enter 1G, you must use the value 1073741824

STEP 02) service mysql stop

STEP 03) rm -f /var/lib/mysql/ib_logfile[01]

STEP 04) service mysql start


innoDB row locking - rows locked one by one or atomically When one locks rows for Exclusive access using FOR UPDATE in a SELECT query, are all the rows one-by-one (potentially leading to a hold-and-wait condition for locks) or the lock is acquired atomically on all the rows (which means all rows are locked or none is locked)?

Atomically cannot always be possible.  Suppose you have a transaction that does an UPDATE, SELECTs some stuff, then decides on what to UPDATE next.  If it had not locked as it went along, the state could change, etc, etc.

Suppose you have two transactions proceeding in parallel.  Any of these might happen:

The locks taken out do not conflict.  In this case each transaction proceeds rapidly to a conclusion.
One xaction tries to lock some row that is already locked.  In this case, it waits to see if the other xaction will finish.  Either it will eventually release the lock allowing resumption of activity, or a "timeout" (see innodb_lock_wait_timeout) will cause the blocked xaction to abort.
A deadlock is discovered.  At this point one of the xactions is picked to be killed.

Any killed transaction will have any actions (update/delete/insert) undone.

Note: The above applies to InnoDB tables in a single server.  Galera (and other clustering), NDB, and MyISAM abide by different rules.


How can I manage Primary Key length limit of 900 bytes when using hirarchyid 2 columns? I have to define a Primary key on a table which has following 4 columns and its data types.

GSiteID hirarchyid,,
SiteID varchar(10),
GComID hirarchyid,
ComID nvarchar(10)

As we know there is limit of 900 bytes when we design any key on a table. whereas hirarchyid is a variable data type consumes 892 bytes so naturally my above case exceed limit of 900 bytes. In some of the posts I read we can use ToString() and other binary formatter to reduce the size of key length.

Can any one explain me or refer such article which shows this case where I can use above two alternatives of ToString and binary formatter which is more compressed to define my key? If someone provides me exact syntext to form my above four field as key in the same seq.

It would be helpful to me. I have already seen examples illustrating use of tostring() into select claues but I want explicit example of define a unique key or index key for hirarchyid so I can manage my key in 900 bytes limit.

As I know that max 10 level is max my tree will evolve so please consider this also while providing suggestion.

Are you trying to create a key column that represents the a tree structure? If so, you could use concatenated, 32-bit hash values of each node in the tree to represent tre hierarchy.

For example, assume you wanted to represent the following:

C:\
C:\MyData\
C:\MyData\MyFolder\
C:\MyData\MyFolder\MySubFolder

You would store the following three rows with these keys:

Size = 4B:  Hash("C:")
Size = 8B:  Hash("C:")+Hash("MyData")
Size = 12B: Hash("C:")+Hash("MyData")+Hash("MyFolder")
Size = 16B: Hash("C:")+Hash("MyData")+Hash("MyFolder")+Hash("MySubFolder")

...where + represents binary concatenation. You would then store the concatenated key in a VARBINARY(900), which leaves you space for a tree that is 900 / 4 = 225 nodes deep. Hopefully this would be enough? This can be indexed.

Note that you don't need the hash to be without collisions, as long as you can reduce the number of keys that must be searched.


sqlplus not connecting to Oracle XE 11g I installed Oracle XE 11g on my Ubuntu in vmware fusion. The oracle installation is all went fine but I am not able to connect the oracle database using sqlplus. I am getting this exception:

$ sqlplus

SQL*Plus: Release 11.2.0.2.0 Production on Wed May 27 18:40:24 2015

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

Enter user-name: system
Enter password:
ERROR:
ORA-01034: ORACLE not available
ORA-27102: out of memory
Linux-x86_64 Error: 12: Cannot allocate memory
Additional information: 1
Additional information: 3309588
Additional information: 8
Process ID: 0
Session ID: 0 Serial number: 0

I followed the instructions as per this excellent blog http://blog.whitehorses.nl/2014/03/18/installing-java-oracle-11g-r2-express-edition-and-sql-developer-on-ubuntu-64-bit/ and got everything installed but feels like I am missing something obvious.
My have setup Vmware fusion with 2GB of Memory.

Cannot allocate memory means you have not enough ram.
check the memory requirements  in the oralce guide


Seems to be a rather meaningless info message. It certainly isn't an error or something to worry about.

It is a bit noisy and goes against a principle of the error log being quite clean (and meaningful). I recommend submitting a bug/pr to remove this line of code.


Any merit to MSSQLSERVER Trace from logman as opposed to XEvent Package 0 The general understanding of monitoring SQL Server activity is that using SQL Profiler is very heavy, server side traces are less heavy, but extended events are even less heavy.

Today I discovered you can capture SQL traces with logman.exe

logman query providers
...
MSSQLSERVER Trace                        {17531BCD-C627-46A2-9F1E-7CC920E0E94A}
...
XEvent Package 0                         {60AA9FBF-673B-4553-B7ED-71DCA7F5E972}
...

Assuming I'm running SQL Server 2008 or later, is there any reason to ever prefer a tracing session as opposed to extended events here? Does doing tracing through logman eliminate any of the performance issues of a traditional server side trace, or am I still just running a server side trace and just writing the results to an etl file with logman?

You can take a look at Jonathan's post here: An XEvent a Day (10 of 31) – Targets Week – etw_classic_sync_target

The provider you see in logman is for using the Event Tracing for Windows. I have never used it but from what I understand it is more for tracking events from your application or the Windows OS back into SQL Server. I do not think it is something that can be used to just track general performance of SQL Server like you would with an XEvent session you create in SQL Server.


Why is my database using Partitioning? ( I know it's not ) Running this query:

select * from sys.dm_db_persisted_sku_features

Shows me that I'm using Partitioning:

| feature_name | feature_id |
|--------------|------------|
| Partitioning | 200        |

I'm pretty sure that I'm not using partitioning, since this database is only mine, and it's for consulting. There are 3 tables that I created using simple "create table" statements. There's no index on tables too. So, why is SQL Server saying that this database is using Partitioning?

According to this documentation, this will return a value if the database has any of the following:

Partitioned tables
Partitioned indexes
Partition schemes
Partition functions

So check for and remove any partition schemes or functions, even if they aren't in use. You can do this in the SSMS UI (Database > Storage > Partition Schemes/Functions), or by querying the following system views:

sys.partition_schemes
sys.partition_functions


Who changed/modified stored procedure in SSMS 2005 or 2008? Possible Duplicate:
  Determining how a schema change occurred?

I have a stored procedure that was modified late last night and just spent the majority of the morning tracing issues back to this change.

Basically no one is owning up to this change, so I am trying to figure out how to find who modified this stored procedure.  I have researched out on the web to see how to find this but it looks like this is not possible?

I figured if anyone knows how to do this it would be someone here.

I did try this. :

"In SSMS, right click on Server Name, choose Reports / Standard Reports / Schema Changes History."  --> Which didn't give me anything but I was at least able to run this which provided me with a time.

select name, create_date, modify_date
from sys.procedures
where name = 'mystoredprocedure'

Thanks for any advice or direction.

I know this doesn't solve your immediate problem, however the best way to pro-actively manage this type of event moving forward may be to create a DDL Database Trigger that logs events into a design log table such as:

CREATE TABLE dbo.DesignLog(
    DesignLogID int NOT NULL PRIMARY KEY CONSTRAINT PK_DesignLog IDENTITY(1,1),
    DateStamp datetime NULL CONSTRAINT DF_DesignLog_DateStamp  DEFAULT (getdate()),
    HostName nvarchar(255) NULL CONSTRAINT DF_DesignLog_HostName  DEFAULT (host_name()),
    UserName nvarchar(255) NULL CONSTRAINT DF_DesignLog_UserName  DEFAULT (suser_sname()),
    ActionType nvarchar(255) NULL,
    StatementExecuted nvarchar(max) NULL
);
GO
CREATE TRIGGER [DesignLogTrigger]
ON DATABASE
FOR DDL_DATABASE_LEVEL_EVENTS
AS
    /*
        Authored By:                    Max Vernon
        Author Date:                    2012-07-05
        Purpose:                        Capture DDL Statements executed against database
    */
    SET NOCOUNT ON;

    DECLARE @data XML;
    DECLARE @ActionType nvarchar(max);
    DECLARE @Statement nvarchar(max);
    DECLARE @msg nvarchar(max);
    SET @data = EVENTDATA();
    SET @msg = cast(@data.query('(/EVENT_INSTANCE/TSQLCommand/CommandText)') as nvarchar(max));
    SET @msg = dbo.xmltostring(@data.query('(/EVENT_INSTANCE/TSQLCommand/CommandText)'));
    SET @msg = dbo.RemoveTag(@msg, '');
    SET @msg = dbo.RemoveTag(@msg, '');
    SET @ActionType = @data.value('(/EVENT_INSTANCE/EventType)[1]', 'nvarchar(255)');
    SET @Statement = @data.value('(/EVENT_INSTANCE/TSQLCommand)[1]', 'nvarchar(max)');
    IF @msg NOT LIKE 'UPDATE STATISTICS' + char(37) + ''
        AND @msg NOT LIKE 'ALTER INDEX' + char(37) + 'REORGANIZE' + char(37) + ''
        AND @msg NOT LIKE 'ALTER INDEX' + char(37) + 'REBUILD' + char(37) + ''
    BEGIN
        BEGIN TRAN;
        INSERT INTO DesignLog
            (
             ActionType
            , StatementExecuted
            )
        VALUES
            (
             @ActionType
            , @Statement
            );
        COMMIT TRAN;
    END
END;
GO
ENABLE TRIGGER [DesignLogTrigger] ON DATABASE;

This particular DDL Trigger does not log certain events such as UPDATE STATISTICS, INDEX REBUILD, AND INDEX REORGANIZE.  We are not interested in logging these events since these events happen during our nightly database maintenance tasks, and therefore crowd out the meaningful changes made by developers.

The advantage of this particular trigger is that it captures the actual SQL text of the event, thereby creating a log of changes made to all objects in the database.  This allows you to revert back to a prior version of any given object - a sort of manual revision control system.


How to write join query in sub query method? This is the question:
What is the most common stackoverflow tag_type? What companies have a tag of that type?
My Solution:
SELECT type, count(tag) AS count
  FROM tag_type
group by type
order by count desc;

Result of this query is:
type       count
cloud       31
database     6

And so on but I selected cloud in the type column as mentioned below and got my result which is correct.
SELECT tag_company.tag, company.name, tag_type.type
  FROM company
       -- Join to the tag_company table
      INNER JOIN tag_company
      ON company.id = tag_company.company_id
      -- Join to the tag_type table
      INNER JOIN tag_type
      ON tag_company.tag = tag_type.tag
  -- Filter to most common type
  WHERE type='cloud';

My Question:
I have got my desired result though but I want to eliminate the manual work by simply typing (type =cloud) and looking for dynamic way of solving this query. How can I do that?
And how can this query be solved with sub query method? Please help.
Or how could I combine these steps in a single query by using a subquery in the WHERE clause instead of the value 'cloud'?

I think this question will be fine here, though it could as well have been asked at SO. One nice property of the relational model is that it is closed under relational operations, that is, the result of a query is a new relation. I'll use Common Table Expressions (CTE), but you may as well use a subquery:
WITH type_counts (type, cnt) AS -- type is a bad identifier,
                                -- but I'll ignore that
( SELECT type, count(tag) AS count
  FROM tag_type
  group by type
), company_tag_info AS
( SELECT tag_company.tag, company.name, tag_type.type
  FROM company
  JOIN tag_company
      ON company.id = tag_company.company_id
  JOIN tag_type
      ON tag_company.tag = tag_type.tag
)
SELECT cti.*
FROM type_counts tc
JOIN company_tag_info cti
    ON cti.type = tc.type

If you just want information related to the top type, you can order by cnt and limit to one:
WITH type_counts (type, cnt) AS -- type is a bad identifier,
                                -- but I'll ignore that
( SELECT type, count(tag) AS count
  FROM tag_type
  group by type
), company_tag_info AS
( SELECT tag_company.tag, company.name, tag_type.type
  FROM company
  JOIN tag_company
      ON company.id = tag_company.company_id
  JOIN tag_type
      ON tag_company.tag = tag_type.tag
)
SELECT cti.*
FROM type_counts tc
JOIN company_tag_info cti
    ON cti.type = tc.type
ORDER BY tc.cnt DESC
LIMIT 1;

This can be further simplified, but I believe that the above is the most relevant part for your question.


When using XE Profiler in SSMS 17.3, the 'Database Name' column shows as either NULL or an empty string I'm just starting to use SSMS 17.3 - and one of the main things I was looking forward to trialing was XE Profiler.

However, when you start a session (either Standard or TSQ) and add the Database Name column, it will either show as blank or NULL.

Adding a Database ID column, will show the correct DB_ID - so it can still be filtered - however, you can't see the name of which database a statement is being executed against.

I saw the same thing you saw. I went into the actual Properties of the Extended Event and noticed that 'collect database_name' was not checked.  I checked that box and restarted the extended event session and the database_name showed up. (I had to go into each 'selected event' and check the box for 'collect database name')

Note:
The XE Profiler extended events are actually under the regular Extended Events node.  They are prefixed with QuickSession - That's where you change the properties. Apparently, the nodes under XE Profiler are simple shortcuts.


MySQL Identifying Connection Attempt I'm seeing the following errors repeatedly in the error log file for our production MySQL server:

  [Warning] Access denied for user 'root'@'remote.appserver.com' (using
  password: NO)

So, I know where the connection attempt is coming from, but I don't know how to track down the application trying to connect.  Is there any way to determine the application, program, client initiating the request?

MySQL does not have a way to determine the information you're looking for (client attempting the connection). The only thing you will get from a direct connection attempt is the username and hostname (as noted above).

MySQL doesn't utilize or pass User-Agent-like data in that manner.


Storing more than 8000 bytes in a column I have a column in one of the tables in my SQL Server database that can have more than 8000 characters. As the maximum allowed character length for a column in SQL Server is 8000, how can I store the data which is having more than 8000 characters data?

There are two things to consider if you want to store values > 8000 bytes in a single column in SQL Server.

First, the column must be capable of holding values of this length. For strings, this is typically done by giving the column a type of varchar(max) (for single-byte characters) or nvarchar(max) (for Unicode). As an example, the following table variable allows > 8000-byte strings in its column:

DECLARE @Example AS table
(
    LongColumn varchar(max) NULL
);

Second, you must ensure that any value you attempt to store in this column is also typed as e.g. varchar(max). For example, the following results in truncation, because the constructed string is implicitly typed as varchar (not varchar(max)) and therefore limited to 8000 bytes:

DECLARE @Example AS table
(
    LongColumn varchar(max) NULL
);

INSERT @Example
    (LongColumn)
VALUES
    (REPLICATE('x', 9000)); -- 'x' is implicitly varchar

SELECT
    E.LongColumn,
    DATALENGTH(E.LongColumn)
FROM @Example AS E;

The following code works correctly because it constructs a string of type varchar(max):

DECLARE @Example AS table
(
    LongColumn varchar(max) NULL
);

INSERT @Example
    (LongColumn)
VALUES
    (REPLICATE(CONVERT(varchar(max), 'x'), 9000));

SELECT
    E.LongColumn,
    DATALENGTH(E.LongColumn)
FROM @Example AS E;

See char and varchar and nchar and nvarchar in Books Online.


SQL | How to sum in same table but referring to different cell values As an example, I've a table like this:

CREATE TABLE Sample
    (`id` int, `productName` varchar(7), `description` varchar(55), `quantity`int(10))
;

INSERT INTO Sample
    (`id`, `productName`, `description`, `quantity`)
VALUES
    (1, 'Cheese', 'GT', 10),
    (1, 'Cheese', 'GE', 10),
    (1, 'Cheese', 'GE', 10),
    (2, 'Ham', 'VD', 10),
    (3, 'Wine', 'EX', 5)
;

and I'm trying to get a result so that the calculation is different if the description is of value GT and GE or VD. I tried with union all, but it does not seem to work:

SELECT x.productName, SUM(x.quantity)
  FROM ( SELECT productName, SUM(quantity*10)
        FROM Sample
        WHERE description IN ('GT', 'GE')
        UNION ALL
        SELECT productName, SUM(quantity*20)
        FROM Sample
        WHERE description IN ('VD')
       ) AS x

Is this possible?

Don't forget GROUP BY clause when using aggregate functions.
Your inner SUMs aren't aliased, so x.quantity won't work.

Try this :

SELECT x.productName, SUM(x.quantity) as quantity
FROM
(
    SELECT productName, SUM(quantity*10) as quantity
    FROM Sample
    WHERE description IN ('GT','GE')
    GROUP BY productName

    UNION ALL
    SELECT productName, SUM(quantity*20) as quantity
    FROM Sample
    WHERE description IN ('VD')
    GROUP BY productName
) AS x
GROUP BY x.productName


Is it bad to have index space larger than data space? Often I need to run queries against large tables that don't have the right index. So I ask the DBA to create such index. The first thing he does is look at the table statistics and see the index space size.

Often he would tell me to find an alternative solution because "the index is already larger than the table". He feels the index has to be smaller than the data, because, he told me "have you ever seen the index in a book? It's much smaller than the book itself, and that's how a table index should be".

I don't feel his philosophy is correct, but I can't challenge him because he's a lead DBA and I'm a developer. I feel if a query needs an index, the index should just be created, instead of finding "workarounds" that just make unreadable and unmaintainable SPs.

I'm selecting only the required columns. The problem is I'm filtering by date so the engine will necessarily do a table scan to match the columns. The query runs once a day, at night, to gather statistics, but it takes 15 minutes to run (we have another hard and fast rule: No procedure should take over 3 minutes).

The DBA showed me the index statistics. There were about 10 indexes on that table, of which only 6 were used (stats showed zero hits to 4 of them).  This is a large system with over 20 developers participating. The indexes were created for whatever reason, and probably no longer used.

We are required to support SQL Server 2008, since that's what the testing DBs run on. But the clients are all on 2014 and 2016.

Think of index design like a sliding switch. You can move this red triangle switch knob anywhere along the line that you want:

I don't usually measure it in terms of size - I usually think of it in terms of index quantity, but size would be fine as well.

It sounds like your DBA thinks the switch is too far over to the right - that you've added too many indexes, and deletes/updates/inserts are performing too slowly.

Rather than arguing about where the switch is, try asking him about the performance problems you're having due to the high number of indexes. Maybe your users are complaining about delete/update/insert speed, or he's seeing lock waits, or he's having a tough time backing up the database due to its size.

My starting point is usually 5 and 5: around 5 indexes per table, with around 5 or less fields per index. There's nothing magical about that number - it just comes from the fact that I have 5 fingers on each hand, so it's easy to hold my hands up and explain the rule.

You may need to have many LESS indexes than 5 when your workload is heavily biased toward delete/update/insert operations, and you don't have enough hardware horsepower to keep up.

You may be able to have many MORE indexes when your workload is mostly read-only, or when you heavily invest in hardware (like cache the entire database in memory, and have all solid state storage underneath it.)


Syntax Error CREATE TABLE Statement i am getting a Syntax error in CREATE TABLE statement.  Microsoft Access 2007.

CREATE TABLE ROOMS
(
RoomNum Int Not Null,
RoomSize Char(6) Not Null,
RoomBedCnt Int Not Null,
RoomRate Numeric(18,2) Not Null,
CONSTRAINT ROOM_PK PRIMARY KEY (RoomNum)
);

Use the NUMBER data type instead of NUMERIC(18,2), which is a SQL Server data type.  Also, the CHAR column should be TEXT...  Use the visual designer to design your table; it will force you to choose from valid data types, and will automatically generate the correct syntax.

See the MSDN Microsoft Access Data Types page for details.

It seems like you are familiar with SQL Server T-SQL syntax, why don't you use SQL Server Express, which is free, instead of Microsoft Access?


Execution Plan is different when Constant Variable is Null I am working on a scenario in which I am providing a variable a value. When I pass Null value, the Query Engine didn't scan the Join tables. As per Logical Query Processing, first FROM Clause Executes, then ON and JOIN executes. But in this Case the Query Engine direct go to Where Clause. Could anyone please explain the Behavior Query Engine when there is a NULL value of a Variable. I am using SQL Server 2016.

When the Value is changed then

You're seeing an example of contradiction detection.  SQL Server's query optimizer is smart enough to perform a few quick checks at the outset of query processing to determine if it can take any shortcuts to return results without doing much (any?!) work.  One such short circuit is to check if the where clause contains an "impossible" limit, such as:
WHERE 1=0

or
WHERE NULL = NULL

SQL Server sees you are comparing something to NULL, which always returns false, and simply returns an empty resultset.  If the items in your WHERE clause were separated with OR instead of AND, then further checks would be performed and you might not see the constant-scan plan.
Comparing any value to NULL using = always returns false.  You may want to use WHERE @c IS NULL instead.  If you rewrite your query using that syntax, you'll see SQL Server does in fact "run" the query.  Consider this:
IF OBJECT_ID(N'tempdb..#t', N'U') IS NOT NULL DROP TABLE #t;
CREATE TABLE #t
(
    i int NOT NULL
);

DECLARE @c int = 1;

The query plan for this query:
SELECT *
FROM #t t
WHERE t.i = 1
    AND @c = NULL;

Versus the query plan for this query:
SELECT *
FROM #t t
WHERE t.i = 1
    AND @c IS NULL;

The problems around comparing NULL values applies to JOIN conditions as well.  Consider this:
IF OBJECT_ID(N'tempdb..#t', N'U') IS NOT NULL DROP TABLE #t;
CREATE TABLE #t
(
    i int NULL
);

IF OBJECT_ID(N'tempdb..#s', N'U') IS NOT NULL DROP TABLE #s;
CREATE TABLE #s
(
    i int NULL
);

I'll insert a row into each table with the value of i set to NULL:
INSERT INTO #t (i) VALUES (NULL);
INSERT INTO #s (i) VALUES (NULL);

Now, if we try to JOIN the two tables in a "simple" query, we get no results returned, since NULL cannot be compared to anything, not even another NULL value!
SELECT *
FROM #t t
    INNER JOIN #s s ON t.i = s.i;

Both tables are scanned by the query processor, as shown in the query plan:

In other words, be careful using NULL values.


Datetime as date only I understand SQL Server 2005 doesn't support DATE and so casting a datetime as a date is not possible.

What I want to do is list all records with a datetime within an entire day of the current week.

So I need all records created on Monday of current week ignoring the timestamp. Currently I am using the below, which does show Monday-Friday of current week, but includes the timestamp, which means I'll see different output depending on when the query is run:

set datefirst 1

select
    DATEADD(day, 1 - DATEPART(dw, GETDATE()), CONVERT( DATETIME, GETDATE())) as Monday,
    DATEADD(day, 2 - DATEPART(dw, GETDATE()), CONVERT( DATETIME, GETDATE())) as Tuesday,
    DATEADD(day, 3 - DATEPART(dw, GETDATE()), CONVERT( DATETIME, GETDATE())) as wednesday,
    DATEADD(day, 4 - DATEPART(dw, GETDATE()), CONVERT( DATETIME, GETDATE())) as Thursday,
    DATEADD(day, 5 - DATEPART(dw, GETDATE()), CONVERT( DATETIME, GETDATE())) as Friday

Can anyone assist?

My expected output would be a column for each day of the current week, where the week starts on Monday (i.e.: Datefirst 1). Ultimately I'm going to use it in a case statement like:

CASE
WHEN DateColumn >= DATEADD(day, 1 - DATEPART(dw, GETDATE()), CONVERT( DATETIME, GETDATE())) and DateColumn < DATEADD(day, 2 - DATEPART(dw, GETDATE()), CONVERT( DATETIME, GETDATE()))
THEN IntegerColumn
ELSE 0
END AS Monday

...Repeating for each day of the week. I need to accommodate for IntegerColumn values which are added throughout Monday. If no DateColumn entries occur within the hours of Monday, a zero is displayed.

Today's date is 2016-02-11 and this query returns 2016-02-11 00:00:00.000:

SELECT curDay = DATEADD(day, DATEDIFF(dd, 0, getdate()), 0)

It can then be included into the DATEADD statement:

SELECT curDay = GETDATE()
    , DATEADD(day, 1 - DATEPART(weekday, getdate()) + DATEDIFF(dd, 0, getdate()), 0) as Monday
    , DATEADD(day, 2 - DATEPART(weekday, getdate()) + DATEDIFF(dd, 0, getdate()), 0) as Tuesday
    , DATEADD(day, 3 - DATEPART(weekday, getdate()) + DATEDIFF(dd, 0, getdate()), 0) as wednesday
    , DATEADD(day, 4 - DATEPART(weekday, getdate()) + DATEDIFF(dd, 0, getdate()), 0) as Thursday
    , DATEADD(day, 5 - DATEPART(weekday, getdate()) + DATEDIFF(dd, 0, getdate()), 0) as Friday

This will give this output:

curDay                  | Monday                    | Tuesday                   | Wednesday                 | Thursday                  | Friday
2016-02-11 11:54:24.947 | 2016-02-08 00:00:00.000   | 2016-02-09 00:00:00.000   | 2016-02-10 00:00:00.000   | 2016-02-11 00:00:00.000   | 2016-02-12 00:00:00.000

This query returns Sunday's date (2016-02-06 this week):

SELECT Sunday = DATEADD(day, DATEDIFF(dd, 0, getdate()) - DATEPART(weekday, GETDATE()), 0)

It can be used with this query and it gives the same output:

SELECT curDay
    , DATEADD(day, 1, Sunday) as Monday
    , DATEADD(day, 2, Sunday) as Tuesday
    , DATEADD(day, 3, Sunday) as wednesday
    , DATEADD(day, 4, Sunday) as Thursday
    , DATEADD(day, 5, Sunday) as Friday
FROM (SELECT Sunday = DATEADD(day, DATEDIFF(dd, 0, getdate()) - DATEPART(weekday, GETDATE()), 0)) as cd

For records with a date on Monday, this query can be used:

SELECT ...
FROM ...
WHERE colDate >= DATEADD(day, 1 - DATEPART(weekday, getdate()) + DATEDIFF(dd, 0, getdate()), 0)
    AND colDate < DATEADD(day, 2 - DATEPART(weekday, getdate()) + DATEDIFF(dd, 0, getdate()), 0)

It outputs rows where the date is bigger or equal to Monday at 00:00 and before Tuesday at 00:00.

It is better to use date >= '20160101 00:00' and date < '20160102 00:00' than BETWEEN. See: Why does my query search datetime not match?

Full sample (SQL Fiddle):

CREATE TABLE test(id int, [date] datetime)

INSERT INTO test values(1, '2016-02-07 23:23')
INSERT INTO test values(2, '2016-02-08 01:01')
INSERT INTO test values(3, '2016-02-08 12:00')
INSERT INTO test values(4, '2016-02-08 23:23')
INSERT INTO test values(5, '2016-02-09 00:00')

DECLARE @date datetime = '2016-02-11 12:34'
set datefirst 1

-- Query 1
SELECT id, [date]
    , IntegerColumn = CASE WHEN [date] >= DATEADD(day, 1 - DATEPART(weekday, @date) + DATEDIFF(dd, 0, @date), 0)
            AND [date] < DATEADD(day, 2 - DATEPART(weekday, @date) + DATEDIFF(dd, 0, @date), 0)
        THEN IntegerColumn
        ELSE 0
    END
FROM test

-- Query 2
SELECT id, [date], IntegerColumn
FROM test
WHERE [date] >= DATEADD(day, 1 - DATEPART(weekday, @date) + DATEDIFF(dd, 0, @date), 0)
    AND [date] < DATEADD(day, 2 - DATEPART(weekday, @date) + DATEDIFF(dd, 0, @date), 0)

Query 1:

id  | date                      | IntegerColumn
1   | 2016-02-07 23:23:00.000   | 0
2   | 2016-02-08 01:01:00.000   | 2
3   | 2016-02-08 12:00:00.000   | 3
4   | 2016-02-08 23:23:00.000   | 4
5   | 2016-02-09 00:00:00.000   | 0

Query 2:

id  | date                      | IntegerColumn
2   | 2016-02-08 01:01:00.000   | 2
3   | 2016-02-08 12:00:00.000   | 3
4   | 2016-02-08 23:23:00.000   | 4


Are there any risks to granting users SQL Server SHOWPLAN permission? I'm doing some performance tuning on a large SQL server 2008 database, and the IT group is unwilling to give SHOWPLAN permission. In the past, "Show Execution Plan" has been the most effective way to understand the performance of individual queries and procedures.

What risks are inherent on granting this permission? Is there a legitimate justification for this restriction on a development copy of the database?

Note: This SQL IT group has 200+ databases under a single SQL Server instance. Thanks.

Answer: I am taking the lack of response to mean that there are no significant security risks, other than the one noted below. Basically, restricting this on a development database is counterproductive.

I will update this if anyone comes up with a better answer. Thanks for your comments!

Take a look at Showplan Security in Books Online, which says:

  Users who have SHOWPLAN, ALTER TRACE, or VIEW SERVER STATE permission
  can view queries that are captured in Showplan output. These queries
  may contain sensitive information such as passwords. Therefore, we
  recommend that you only grant these permissions to users who are
  authorized to view sensitive information, such as members of the
  db_owner fixed database role, or members of the sysadmin fixed server
  role. We also recommend that you only save Showplan files or trace
  files that contain Showplan-related events to a location that uses the
  NTFS file system, and that you restrict access to users who are
  authorized to view sensitive information.

  For example, consider the following query:

SELECT COUNT(*)  FROM table_1  WHERE column_1 < 10

  If a malicious user produces Showplan output for a set of queries like
  this example, and replaces the value "10" in the predicate with
  different constants each time, the user could infer an approximate
  data distribution of the column values for column_1 in table_1 by
  reading the estimated row counts.

This risk does seem negligible, especially given this is a development server, where all queries would be from devs and IT people, not users.


Add credentials & set a specific role for a database How can we add login credentials & roles for a particular database?

There is ServerObject,replication & management folders in MS SQLmanagement studio. What's the significance of these folders?

How can we add roles in MS SQLmanagement studio not for a particular database?

What are the main things that we should give more care when creating a database?

See the Database Security Tutorial on ToadWorld (originally SQLServerPedia)


Getting timepart from timestamp Hello my little snowflakes, how can I isolate the correct time part of a timestamp?

SELECT CURRENT_TIMESTAMP()                                                                                AS a,
       CURRENT_TIMESTAMP() :: timestamp_ntz                                                                                AS b,
       CONVERT_TIMEZONE('UTC', CURRENT_TIMESTAMP)                                                                                AS c,
       TO_TIME(CURRENT_TIMESTAMP)                                                                                AS d,
       TIME_FROM_PARTS(DATE_PART(hour, CURRENT_TIMESTAMP()), DATE_PART(minute, CURRENT_TIMESTAMP()), DATE_PART(second, CURRENT_TIMESTAMP())) AS e

Produces

A                   B                   C                   D        E
------------------- ------------------- ------------------- -------- --------
2017-06-11 20:33:00 2017-06-11 18:33:00 2017-06-11 20:33:00 12:33:00 12:33:00

I just want 20:33:00

Running SHOW PARAMETERS LIKE 'TIMEZONE'; returns in my case:

key         value               default
TIMEZONE    America/Los_Angeles America/Los_Angeles

SELECT CURRENT_TIMESTAMP() results in 2019-11-04 07:48:47.289 -0800.

Then I can alter the session parameter like this ALTER SESSION SET TIMEZONE = 'Europe/Amsterdam';

Now SHOW PARAMETERS LIKE 'TIMEZONE'; gives:

key         value               default
TIMEZONE    Europe/Amsterdam    America/Los_Angeles

And SELECT CURRENT_TIMESTAMP() results in 2019-11-04 16:49:45.987 +0100

That hopefully shows the logic behind CURRENT_TIMESTAMP.

Answer to actual question: To get the timepart simply use TIMESTAMP::TIME

SELECT
CURRENT_TIMESTAMP() :: TIME AS Amsterdam_Time,
CONVERT_TIMEZONE('America/Los_Angeles', CURRENT_TIMESTAMP)::TIME AS LA_Time,
CONVERT_TIMEZONE('UTC', CURRENT_TIMESTAMP)::TIME AS UTC_Time;

Results in (with have Amsterdam as default):

AMSTERDAM_TIME  LA_TIME     UTC_TIME
16:55:37        07:55:37    15:55:37


Finding the highest quarter of the year I'm sitting here and do some practise but I just can't find the Solution. Maybe someone could give me some help?

I have a table and should find the highest sale amount of a quarter per year.

Data table:

My attempt:

SELECT Year, Quarter, MAX(SUM(Revenue)) OVER(PARTITION BY Year)
FROM Sale
GROUP BY Year, Quarter
ORDER BY Year, Quarter;

Output:

As you can see the numbers are correct.
But my expected output should be:

2012  |  4  |  25
2013  |  2  |  35

Would be great if someone has an idea.
Thanks in advance!

UPDATE:
Solution is found.
Forgot to mention. I used Oracle XE.

SELECT Year, Quarter, QMax
FROM (
  SELECT Year, Quarter,
    MAX(SUM(Revenue)) OVER(PARTITION BY Year) AS QMax,
    RANK() OVER (PARTITION BY Year ORDER BY SUM(Revenue) DESC) AS QRank
  FROM Sale
  GROUP BY Year, Quarter) QResult
WHERE QRank = 1
ORDER BY Year, Quarter;

Assuming the answer is 25 and 30, then this should work

SELECT Year, MAX(Quarter) Quarter, MAX(Revenue)
FROM Sale
GROUP BY Year
ORDER BY Year, Quarter


Performing SQL counts with and without a WHERE clause from the same table This is my first post so I apologize if I am not concise enough. I am trying to come up with an SQL query to identify data quality issues.
Here's the sample table:
DeviceOS    Bytes
Roku        10,000
AppleTV     -50000
SamsungTV   -100000
Roku        -100000
AppleTV      30000
Roku        -90000
AppleTV     -20000
AppleTV     -10000
SamsungTV   -100000

Output table:
DeviceOS   Total Count  bad_count
Roku       3            2
AppleTV    4            2
SamsungTV  1            1

Total_count field aggregates based on deviceOS and bad_count picks up only all the rows for which megabytes field is negative.
Essentially trying to do this->
select DeviceOS, count(*) from table group by DeviceOS
select DeviceOs, count(*) from table DeviceOS where megabytes < 0
How can I combine the above two queries and have the result of both of them displayed together similar to the output table?

You can do the following for SQL Server:
SELECT DeviceOS                  AS DeviceOS
     , COUNT(*)                  AS TotalCount
     , SUM(IIF(Bytes < 0, 1, 0)) AS BadCount
FROM (
         VALUES ('Roku', 10000)
              , ('AppleTV', -50000)
              , ('SamsungTV', -100000)
              , ('Roku', -100000)
              , ('AppleTV', 30000)
              , ('Roku', -90000)
              , ('AppleTV', -20000)
              , ('AppleTV', -10000)
              , ('SamsungTV', -100000)
     ) AS t1 (DeviceOS, Bytes)
GROUP BY DeviceOS

We are grouping by the DeviceOS column and getting the Name and the Count.
The only tricky part is using SUM(IIF(Bytes < 0, 1, 0)) AS BadCount to get the bad count.
What this does is it checks if the byte size is less than 0 (meaning it is negative):

if it is, then +1 is added to the sum
if it is not, then +0 is added to the sum


azure db export to blob storage failed i am trying to simply export BACPAC file from azure db to azure blob storage
as mentioned here : https://docs.microsoft.com/en-us/azure/azure-sql/database/database-export
but it is failed with error :

The Azure SQL Server firewall did not allow the operation to connect.
To resolve this, please select the "Allow All Azure"checkbox in the
Sql Server's configuration blade.

what's the cause of this error i am not sure, do you think i have to enable allow all azure service checkbox in azure db firewall? ( before i took backup and i was not doing this step)
also wanted to mention that both resource azure blob and azure db are in private virtual network.
also i am scared of this : https://www.fmsinc.com/microsoft-azure/sql-server/security.htm
Thanks,

you have to allow azure services and applications to connect to your database.
Take a look at the firewall rules in Azure sql database:
https://docs.microsoft.com/en-us/azure/azure-sql/database/firewall-configure
You can eventually allow azure services to connect to your database in this way:

You can disable this option after the export activity.
Please, remember that this is not an option of export task but this is a firewall rule of your Azure SQL Database Server.
Consider the export task like an azure service against your database so you have to allow azure to connect as written here:
https://docs.microsoft.com/en-us/azure/azure-sql/database/firewall-configure#connections-from-inside-azure


get stuck trying to copy or backup database in microsoft SQL server I have a website running on a windows hosting package with my provider.
I bought a new windows hosting package, and now i want to copy my 'older' site into the new pack.

SO I can acces the old db and the new db (which is still totaly empty, no tables, ..) through sql server. So they both connect to an online server, it's not local.

Now I want to copy old db to new.
First option is to use: tasks > copy database
In this wizard i enter my source db data, next, i enter my destination db data, next, now i get the message 'copy database wizard - object reference not set to an instance of an object'.
So here i get stuck..

Alternative option I think is to export old db, and later import into new.
So i go to: tasks > backup
I select my old db, full backup type, destination: disk
--> i'm not sure what 'disk' means. Does it mean create a backup file on my local disk on my laptop from where i do all of this?
--> The standard value that is there looks like an url to a server though, it starts with an ip adress:
\172.20.101.253\sql008\TRN\ID97655_..............tm
--> since i do not know how to access that place, i remove it and i add a new path, something like: D:\MSSQL\BACKUP\backuptest.Bak
--> that .bak file does not exist yet there, the path does exist
--> then i get the warning: 'cannot verify the existence of the backup file location. do you want to use the backup file location anyway?'
--> i choose 'yes', then the backup completes succesfully to 100%
--> there is no bak file to be found on that directory.
so now i'm stuck here to

for the disk location i entered, is it still searching that location on the server instead of localy?
when i google for this issue i find a lot of threads saying it's some rights issue. So I go to my D drive, richtclick , properties, security.
Here I find 4 standard users (verified users, SYSTEM, administrators, users), i make sure all of them have all access.
But maybe this is not enough? do i need to create a new user? how do i know which one?

Thanks for helping me further with this,
have a nice day,

sandy

It doesn't matter if you're running SSMS from your laptop or any other computer.

When you successfully connect to a SQL Server instance using SSMS, you have to get the mindset that you are actually on the server that hosts that SQL Server instance.

So, when you use SSMS to connect to 'OldInstance' (located on Server1) and issue commands like BACKUP DATABASE ... TO DISK D:\MSSQL\BACKUP\backuptest.bak, you are referring to the D-drive on Server1 where that SQL Server instance is located.  If no D-drive exists on Server1, the backup command will fail.  However, your backup completed successfully, so the bak file would be on the D-drive of Server1.

The same idea applies to RESTORE DATABASE FROM DISK ....  'DISK' refers to disks available to that specific server (which could be D-driver, E-Drive, etc. or even a file share).

You don't go into much detail about whether OldInstance and NewInstance are hosted on the same server or not.

Somehow, you'll need to get the bak file that you created against OldInstance to a place where NewInstance can access it.

If 'OldInstance' and 'NewInstance' are on the same physical server (or VM),
you might be able to work with your hosting provider to help you access the backup created from OldInstance and restore it to NewInstance.

If OldInstance and NewInstance are on completely different servers and they both can't access a given file share, you will probably have to FTP the backup from OldInstance down to your computer (if that is even possible) and the FTP that backup file to the NewInstance server and issue the RESTORE DATABASE on NewInstance pointing the the backup file you just FTP'd


Return true if any value in an specific key inside a jsonb array exists The rows in my table look like

 facility | events
----------+---------------------------------------------------------------
  1       | [{ "id" : "1", "count" : "4", "severity" : "bad" }, { "id" : "3", "count": "4", "severity" : "good"}]

I want to get all the rows who have a severity = bad

I tried

select facility, events::jsonb @> '"severity"' as bad from tbl limit 10;

and I got (same data as before)

 facility | bad
----------+-------
  1       | f

Thanks in advance

I think you can get it by using jsonb_array_elements function and the ->> operator.

create table test (facility int, data jsonb);

insert into test values
(1, '[{ "id" : "1", "count" : "4", "severity" : "bad" }, { "id" : "3", "count": "4", "severity" : "good"}]'),
(2, '[{ "id" : "2", "count" : "4", "severity" : "good" }, { "id" : "3", "count": "4", "severity" : "good"}]'),
(3, '[{ "id" : "3", "count" : "4", "severity" : "good" }, { "id" : "3", "count": "4", "severity" : "bad"}]');

SELECT t1.facility, obj->>'severity' severity
FROM   (SELECT facility, jsonb_array_elements(data) obj
        FROM   test) t1
WHERE  obj->>'severity' = 'bad'

facility | severity
-------: | :-------
       1 | bad
       3 | bad

dbfiddle here

In case there is more than one "severity" : "bad" for the same facility you could avoid duplicates using DISTINCT.

create table test (facility int, data jsonb);

insert into test values
(1, '[{ "id" : "1", "count" : "4", "severity" : "bad" }, { "id" : "3", "count": "4", "severity" : "good"}]'),
(2, '[{ "id" : "2", "count" : "4", "severity" : "good" }, { "id" : "3", "count": "4", "severity" : "good"}]'),
(3, '[{ "id" : "3", "count" : "4", "severity" : "bad" }, { "id" : "3", "count": "4", "severity" : "bad"}]');

SELECT distinct t1.facility, obj->>'severity' severity
FROM   (SELECT facility, jsonb_array_elements(data) obj
        FROM   test) t1
WHERE  obj->>'severity' = 'bad'
ORDER BY t1.facility

facility | severity
-------: | :-------
       1 | bad
       3 | bad

dbfiddle here


How to increase the number of activity in pg_stat_activity? I am working with Postgresql(Data Grip) and I am trying to run many activities at once but pg_stat_activity let me only do 100 activities and from the other it ignores.

How can I increase the number of activities or are there other solutions?

pg_stat_activity has one row per server process.
See PostgreSQL documentation in table 28.1:

pg_stat_activity One row per server process, showing information related to the current activity of that process, such as state and current query.

and later down:

The pg_stat_activity view will have one row per server process, showing information related to the current activity of that process.

If you want more, you need to increase the number of connections (backends) which default at 100, see this other part of the documentation

max_connections (integer)
Determines the maximum number of concurrent connections to the database server. The default is typically 100 connections, but might be less if your kernel settings will not support it (as determined during initdb). This parameter can only be set at server start.


Slow query when using ORDER BY in Postgres This query

SELECT *
FROM
  ( SELECT event_id
   FROM events_201806
   WHERE registry_name='$VCZN://externalcenter/ninaExtCenter_ZN'
   UNION ALL SELECT event_id
   FROM events_201805
   WHERE registry_name='$VCZN://externalcenter/ninaExtCenter_ZN'
   UNION ALL SELECT event_id
   FROM events_201804
   WHERE registry_name='$VCZN://externalcenter/ninaExtCenter_ZN'
   UNION ALL SELECT event_id
   FROM events_201803
   WHERE registry_name='$VCZN://externalcenter/ninaExtCenter_ZN') AS ids
NATURAL INNER JOIN events;

Returns 456 results and takes about 20 ms.

Adding ORDER BY

SELECT *
FROM
  (SELECT event_id
   FROM events_201806
   WHERE registry_name='$VCZN://externalcenter/ninaExtCenter_ZN'
   UNION ALL SELECT event_id
   FROM events_201805
   WHERE registry_name='$VCZN://externalcenter/ninaExtCenter_ZN'
   UNION ALL SELECT event_id
   FROM events_201804
   WHERE registry_name='$VCZN://externalcenter/ninaExtCenter_ZN'
   UNION ALL SELECT event_id
   FROM events_201803
   WHERE registry_name='$VCZN://externalcenter/ninaExtCenter_ZN') AS ids
NATURAL INNER JOIN events
ORDER BY event_ts DESC;

causes the query to take a very long time to complete, about 10 minutes.

Any pointers on how to fix this?

Try putting the original (fast) query into a common table expression:

with my_events as (
  select *
  from (
    ...
  ) as ids
     join events on ids.event_id = events.id
)
select *
from my_events
order by event_ts desc;

CTEs are optimized independently from the rest of the query, so that should run the base query with the fast plan. Ordering the 456 rows should be fairly quick.


Postgres/pgloader: setting a delimiter for multiple line text records I wish to import a 5 gigabyte text file with pgloader.
The individual records are paragraphs of html. The records include carriage returns and white space. So this not an import where it is one record per line.
Reading the pgloader howto I get the impression I can import these records by using setting lines terminated by to a special character to demark the end of a record. The options is described as:

lines terminated by
Takes a single character as argument, which must be found inside single quotes, and might be given as the printable character itself, the special value \t to denote a tabulation character, or 0x then an hexadecimal value read as the ASCII code for the character.
This character is used to recognize end-of-line condition when reading the CSV data.

So, am I right?  Can I can import these records by using setting lines terminated by to a special character?
UPDATE: I had to get the project done, so I used some perl commands to modify the data to be one record per line. It's an undesired option because I wanted a multi-line text field.
But I still would like a way to do this. Something that allows a custom record-delimiter would probably be the answer.

I don't believe you can use pgloader in this way if any of your HTML contains character sequences that would be interpreted in the context of CSV. For example, any backslash followed by digits is perfectly valid (and not-special) HTML, but would be transformed to something else by pgloader while importing. I can't even estimate how pgloader will handle the double-quote and single-quote symbols scattered around in your documents.

You would also need a byte that doesn't exist anywhere in your content to serve as a row delimiter; you could use some low-value control byte for this purpose assuming your HTML is valid and doesn't contain any of these special bytes. This naturally depends on your chosen character encoding, and is rendered worse if you have multiple encodings among your documents.

Assuming everything is UTF-8, the NUL byte should not appear in any UTF-8 stream. Assuming you aren't concerned about special CSV character sequences, you could attempt to do this import using NUL as your separator value:

--with "lines terminated by '\0x00'"

I wouldn't recommend this avenue though for fear of subtle corruption of your documents (as noted above).


Column as an Included column or as a key column I have a query which has an optional where condition on column classID(not unique).
This same classID is being displayed as a select column in various other queries.
Currently i have classID as an included column in an index idx,so i think that the idx will be used when classID is in select statement.
Do i have to create a a non clustered index for the classID as a key column when the query has where condition for classID.?

Confused on this whether to create separate non-clustered indexes for all the where clause columns.?

Currently i have classID as an included column in an index idx, so i
  think that the idx will be used when classID is in select statement.

No, not really. The index that is selected in the query plan depends on the query optimiser deciding which index best satisfies the query requirements. If your index key columns match, or are very similar to, your query predicates then it will likely select that index.

Having ClassID in the INCLUDE columns simply means the query plan will not require key lookups to retrieve this additional data about the rows returned in the query. If ClassID is a predicate, having it in the INCLUDE columns of an index will not cause the optimiser to definitely select that index.

  Do i have to create a a non clustered index for the classID as a key
  column when the query has where condition for classID.?

You need to create indexes that best satisfy the most common data access. For example, if you create a bunch of indexes with single key columns and many include columns, but your queries have many predicates and few select columns, then your indexing strategy is unlikely to be as efficient as it should be.

A good starting point/rule of thumb while you're getting started with index tuning\design is no more than 5 indexes per table, and no more than 5 columns per index. This helps you to be more selective about what indexes you create and how you design them to satisfy the most queries.

It is not a hard and fast rule, it just helps you to think about your index strategy relative to your queries.

  Confused on this whether to create separate non-clustered indexes for
  all the where clause columns.?

You don't create separate indexes for each predicate because the optimiser will likely only use one of them and then do a bunch of key lookups. Design your indexes with this basic guideline:

WHERE clause and JOIN columns in your query should be Key Columns in your Index.
SELECT columns in your query should be Include columns in your index.

These aren't hard and fast rules, and again don't design your indexes to satisfy a single query. You need to profile your data access characteristics and develop an indexing strategy to satisfy the most queries with the fewest indexes for maximum efficiency.

Some good resources:

SQL Server Index Basics

SQL Server Index Architecture and Design Guide

SQL Server Index Basic Design and Guidelines

Designing Effective Non-Clustered Indexes