Do I need to reenter old data after adding an index to a table? I want to add index to my tables. Some of my tables already have couple thousand rows.

Do I need to reenter my stored data after adding index to columns (to make them aware of index/so the indexing affect them as well  or it's going to take care of old data itself ?

According to the comments posted for your question, @ypercube is right. It is no. Here is why in terms of Storage Engine under /var/lib/mysql (default datadir):
InnoDB (innodb_file_per_table enabled)
With innodb_file_per_table enabled, table data and its indexes live in the database folder next to the .frm file. For example, for the table mydb.mytable, the manifestation of that InnoDB table outside of ibdata1 would be:

/var/lib/mysql/mydb/mytable.frm
/var/lib/mysql/mydb/mytable.ibd

InnoDB (innodb_file_per_table disabled)
With innodb_file_per_table enabled, table data and its indexes live in the system tablespace file better known ibdata1. The data pages and index pages are 16KB blocks where rows and TEXT/BLOB overflow are managed.
MyISAM
For the MyISAM mydb.mytable, three files comprise its logical presence

/var/lib/mysql/mydb/mytable.frm is table structure header
/var/lib/mysql/mydb/mytable.MYD holds the data only
/var/lib/mysql/mydb/mytable.MYI holds the indexes (BTREE, FULLTEXT)

EPILOGUE
Regardless of Storage Engine, data pages and index pages are separate and distinct entities. Under the hood, when you issue the command
ALTER TABLE mydb.mytable ADD INDEX new_index (col1,col2);

a complete copy of the data is made. That complete copy is scanned and all index pages are migrated along with new index pages for the new_index index.


Why is below 2 query generating different execution plan in MYSQL? Only difference in the below query is the order by column. The resultset of both query is same.
However, the 2nd query execution plan shows 'Using temporary; Using filesort' for ordering where as for 1st query it does not require filesort.
Apart from id I have index on c.table_b_id and c.some_indexed_id
SELECT * FROM TableA a
inner join TableB b on a.id = b.id
inner join TableC c on c.table_b_id = b.id
where c.some_indexed_id = 1
order by c.table_b_id asc;

SELECT * FROM TableA a
inner join TableB b on a.id = b.id
inner join TableC c on c.table_b_id = b.id
where c.indexed_id = 1
order by b.id asc;

The explain depends on which column you using in WHERE clause, the column which are large in size that which doesn't fit in SORT BUFFER requires filesort for sorting purpose. And other point is b.id may be primary key which is sorted by default, So it doesn't requires to do sorting & where c.table_b_id requires sorting.


UPDATE trigger causes deadlock I have a After Update trigger where trigger insert a value in same table to find a duration column.My goal is whenever end_time value is inserted then trigger will find duration in hh:mm:ss format and insert into duration column.
Problem- Trigger causes so many deadlock therefore blocking another process.

Column datatype informatiom..
Start_time datetime, End_time datetime, Duration time(3)

ALTER TRIGGER [dbo].[GET_DURATION] ON [dbo].[INBOUND_CALL_xxx]
AFTER UPDATE AS
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
Begin Transaction

               UPDATE INBOUND_CALL_xxx
               SET DURATION = convert(time(0),(END_TIME- START_TIME))
               FROM INBOUND_CALL_xxx with (nolock)
Commit;

You are missing the reference to the special table inserted which holds just the rows that were updated. Currently your trigger is updating all the rows of the tracking table, every time.

ALTER TRIGGER [dbo].[GET_DURATION] ON [dbo].[INBOUND_CALL_xxx]
AFTER UPDATE AS
BEGIN

    IF UPDATE(END_TIME) OR UPDATE(START_TIME)
    BEGIN

               UPDATE C
               SET DURATION = convert(time(0),(I.END_TIME - I.START_TIME))
               FROM
                inserted AS I
                INNER JOIN INBOUND_CALL_xxx AS C ON I.KeyColumn = C.KeyColumn

    END

END

You can also modify the trigger to update the DURATION only if the relevant columns were updated (as of this example).

Another option would be using a computed column (maybe persisted) with an expression of CONVERT(time(0), END_TIME - START_TIME), so you don't need a trigger to keep the values updated.

ALTER TABLE INBOUND_CALL_xxx DROP COLUMN DURATION

ALTER TABLE INBOUND_CALL_xxx ADD DURATION AS CONVERT(TIME(0), END_TIME - START_TIME) -- PERSISTED (if needed)


selecting same column twice for different value I have a table like this;

event_id  pair  versus  score
       1     1      11    100
       1     1      12      0
 ...
       1    11       1      0
       1    12       1     50

Now I want to get the both scores for a given pair on the same result row.
Like;

event_id pair versus score1 score2
       1    1     11    100      0
       1    1     12      0     50

Any ideas?

Assuming you always have pair , the following should give you desired result:

SELECT a.event_id ,a.pair,a.versus,
a.score as score1, b.score as score2
FROM table_name a
INNER JOIN table_name b on
(a.event_id = b.event_id and b.pair =a.versus and b.versus=a.pair and a.pair<b.pair)

Side notes.
1. It makes sense to have a surrogate primary key column in the table.
2. The query would look much cleared if you had one more column which serves as pair identifier  (ie. both 1-11 and 11-1 would share the same pair_identifier).
3. a.pair<b.pair added to avoid duplicates in resultset


Cannot change root access in MySQL to % This is a new MySQL Server install. I created my root password as part of the setup (on Centos 6.4).

When I connect to the server through a terminal, I can connect to MySQL and issue commands, using my root password.

select current_user;

gives me:

+----------------+
| current_user   |
+----------------+
| root@localhost |
+----------------+

If I do:

GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY 'xxxxxx' WITH GRANT OPTION

I get:

Query OK, 0 rows affected (0.00 sec)

But when I do:

SHOW GRANTS;

Here's what I get:

+----------------------------------------------------------------------------------------------------------------------------------------+
| Grants for root@localhost                                                                                |
+----------------------------------------------------------------------------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' IDENTIFIED BY PASSWORD '*fdfgdgdggfggfgfg' WITH GRANT OPTION |
+----------------------------------------------------------------------------------------------------------------------------------------+

Am I not supposed to see another line for root@% in addition to root@localhost?

The real issue I'm having is that I can't connect to MySQL from outside of localhost (as in, I'm logged in using a terminal session), and if the MySQL server is not giving root universal access (root@%), then that would explain the problem.  When I try to connect using PHP (from my local MAC), the following is the returned MySQLi object:

mysqli Object
(
    [affected_rows] =>
    [client_info] =>
    [client_version] => 50008
    [connect_errno] => 2002
    [connect_error] => Connection refused
    [errno] =>
    [error] =>
    [field_count] =>
    [host_info] =>
    [info] =>
    [insert_id] =>
    [server_info] =>
    [server_version] =>
    [stat] =>
    [sqlstate] =>
    [protocol_version] =>
    [thread_id] =>
    [warning_count] =>
)

I realize that granting root access from % is not a great idea, but at this point, I'm trying to figure out why I can't connect to the MySQL server, and when I solve that, I will restrict access.

SHOW GRANTS only gives you back whatever you are connected as, which was root@localhost.
root@localhost and root@'%' and completely different users. Just do
SHOW GRANTS FOR root@localhost;
SHOW GRANTS FOR root@'%';

root@localhost lets you connect from the DB Server via mysql.sock (the socket file)
root@'%' lets you connect via TCP/IP, but you must explicitly connect with that protocol. Otherwise, mysqld connects you as root@localhost. This is true even if you attempted to connect using 127.0.0.1.

SUGGESTION #1
For the sake of security, you should use netblocks instead of '%'. For example, if you web servers at 10.1.2.20, 10.1.2.30, and 10.1.2.40, you should create
GRANT ALL PRIVILEGES ON *.* TO 'root'@'10.1.2.%'
IDENTIFIED BY PASSWORD '*fdfgdgdggfggfgfg' WITH GRANT OPTION;

instead of having a remote root.
SUGGESTION #2
If you really want the remote root, here is what you do
UPDATE mysql.user SET host='%'
WHERE user='root' AND host='localhost';
FLUSH PRIVILEGES;

That will do what you want, but remote root is not recommended.
SUGGESTION #3
Since root@'%' already exists, then remove the root@localhost.
DELETE FROM mysql.user WHERE user='root' AND host='localhost';
FLUSH PRIVILEGES;

This will leave root@'%' as the only root user, but you must connect explicitly with TCP/IP.


Deleting data from a table containing LOBs did not reduce the amount of data reported INTERNALLY by the table or database Running SQL Server 2012 standard, using both replication data and log shipping. Transaction log backups are created every few minutes.

This question is NOT about the amount of space reported by the file system.

We have a log table with four VARCHAR(max) columns. The table currently holds roughly 180 GB of data.  The table grows at a rate of roughly 10 GB per month.

In an attempt to free up space for future growth, I recently deleted (in chunks over a 36 hour period) close to 30% of the data.

Based on the size of the table and the number of rows being removed, I expected the size of the table to be reduced by roughly 55-60 GB.  I verified this by taking a sample and calculating the average row size, and then multiplying that by the number of rows to be removed.  However, after finishing the delete, the table size has not changed.

While I am not looking to shrink the size of the .mdf file, I did check to see if the database had any free space that could be shrunk, but there is 0% free space.

So - I am confused.  I've verified the data that I deleted from the table is no longer there. I even compared it against a backup to get a before & after.  The number of rows in the database has changed, but the data space for the table has not decreased.

My questions then are:
1) Why did deleting data from the table not reduce the size of the table?
2) What can I do to actually reduce the size of the table?

EDIT:

I am determining the space used by the table by calling sp_spaceused 'tablename'.
I have used dbcc updateusage (0, tablename) prior to calling sp_spaceused.
I am NOT trying to free up disk space - although an analysis indicates that there is 0% space available to be reclaimed.
I have run an index reorganize with ( LOB_COMPACTION = ON ). Fragmentation is below 1%.
The table is not a heap. Table definition below.

 CREATE TABLE [dbo].[LARGE_LOG_TABLE](
  [ID] [varchar](36) NOT NULL,
  [DOC_ID] [varchar](36) NOT NULL,
  [REQUEST_DATA] [varchar](max) NULL,
  [REQUEST_DATA_XSLT] [varchar](max) NULL,
  [RESPONSE_DATA] [varchar](max) NULL,
  [RESPONSE_DATA_XSLT] [varchar](max) NULL,
  [ACTIVE_FLAG] [varchar](1) NULL,
  [CREATED_BY] [varchar](100) NULL,
  [DATE_CREATED] [datetime] NULL CONSTRAINT [DF_LARGE_LOG_TABLE_CREATE_DT]  DEFAULT (getdate()),
  [USER_LAST_UPDATED] [varchar](100) NULL,
  [DATE_LAST_UPDATED] [datetime] NULL CONSTRAINT [DF_LARGE_LOG_TABLE_LAST_DT]  DEFAULT (getdate()),
 CONSTRAINT [PK_LARGE_LOG_TABLE] PRIMARY KEY CLUSTERED
 ( [ID] ASC ) ON [PRIMARY] )
 ON [PRIMARY]
 TEXTIMAGE_ON [PRIMARY]

EDIT 2:

SELECT * FROM sys.dm_db_index_physical_stats (db_id(), OBJECT_ID('database..LARGE_LOG_TABLE'), default, default, default)

EDIT 3:

The answers provided to the question at Delete query does not appear to open space on the SQL Server refer to freeing up space on disk / shrinking the physical size of the database. This question refers to removing rows containing LOB data and yet the database (internally) shows the LOB data has not been removed.  We removed 600000 rows of data, but the space used on the table level (not the file system level) was only reduced by a few bytes - not the expected 60 GB. This question is about removing the LOB data.

@edze asked a great question. I've known that LOBs are stored separately from the table data (with a pointer referencing the storage location), but I didn't know specifically where they were stored.

While searching for more information on where LOB data is stored, I found the following links:

http://itknowledgeexchange.techtarget.com/sql-server/deleting-lob-data-and-shrinking-the-database/

The key take away here (focused on a database shrink) is the following:

Do the database deletion as normal.
Then backup and restore the database.
Then do the shrink, followed by rebuilding the clustered indexes in order to fix the fragmentation issue which the shrink will introduce.

https://social.msdn.microsoft.com/Forums/sqlserver/en-US/f80ad8c7-e6c9-4538-a89b-8d6727050b9c/release-space-used-after-sql-server-delete-in-rows-with-blob-varbinarymax-using-textimage-on?forum=sqldatabaseengine

Here the key is:

Rebuild the clustered indexes of the table.
Execute DBCC UPDATEUSAGE (0)

With this being Standard edition, a rebuild will require a LONG lock - but it looks like the only way to recover the space.


sp_BlitzCache throws Table DML warning on temp tables I have been using sp_BlitzCache to troubleshoot some performance issues on a client's SQL Server 2012 instance.  I am running:

EXEC sp_BlitzCache @ExpertMode=1, @SortOrder = 'reads';

And one of the procedures high up on the dummy board is being flagged with a "Table DML" warning.

The only thing that I can see going on is the creation, use and dropping of a temp table named #Fred.

Besides the name of the temp table ;) , should I be concerned about this warning?  Based on the details in the link https://www.brentozar.com/blitzcache/table-dml/ it seems like this is a false positive of sorts.  If not, what concerns are there with temp table usage such that a warning is raised?

Btw #Fred replaced @Fred due to table variable warnings.

Thanks for using sp_BlitzCache!

No, don't worry about that one. It's a more general check that sometimes snags temp tables.

As an aside, make sure you're running the most recent version, if you're not already.

You should also use the GitHub repository I linked to to file issues.

Thanks!


Disk I/O increased when changing RBR to MIXED I'm experiencing some replication issues. I have a Master-Slave replication with mysql; the slave had binlog-format = ROW, Last sunday (3 days ago) I changed the format to MIXED (this is master too; so I expected better performance for his slave). The thing is, it appeared to work fine sunday and monday; but starting tuesday this slave began to lag and kept falling behind for some hours... It catched up around midnight, and today again from nowhere started to lag and kept behind many hours again; trying to understand what's happening I've been asking around and it seems like the workload is the same like past weeks, nothing new that could be causing this behavior. I noticed that the Disk Usage has incremented considerably lately, which is strange, I thought that this change would cause less disk writes...

I'm attaching the graphic, you can see a lot of reads december 18, that was some Cold Backup, so it's not that.... The question is: Could the change of binlog-format be the cause of this behavior? Is it normal that it is writing a lot more to disk? And is there a way to improve this?

Thanks

Server1:

Master
binlog_format=MIXED

Server2:

Server1's slave
binlog_format=MIXED (This is the one changed, and where the I/O is up).

Server3:

Server2's slave

When a complex UPDATE is run on the master, and it results in only a few rows being updated, then one of these happens:

SBR:  Re-execute the UPDATE on the Slave.  This stalls replication, waiting for the complex query to finish.
RBR:  Only the changed rows are sent to the Slave.  These can be applied much more rapidly.

A simple UPDATE that modifies a million rows:

SBR:  Again, slow on the slave -- because of all the I/O do update the million rows.
RBR:  This is also slow, but for a different reason:  A million records are sent through the replication stream.  This involves I/O at both ends, plus network traffic.

DELETE, INSERT...SELECT, and a few other things have similar characteristics.

I can't be more specific without knowing what, exactly, you were replicating.  I hope my examples give you some clues of what could be happening.


ON DELETE CASCADE doesn't work even storage table is InnoDb I tear my hair with the ON DELETE CASCADE Clause. In fact I have two tables
I'm on linux and I have installed lamp with phpmyadmin.

That's the tables :

CREATE TABLE a(
    Idatable bigint(20) PRIMARY KEY NOT NULL AUTO_INCREMENT,
    Nameatable varchar(50) NOT NULL
)ENGINE=InnoDB DEFAULT CHARSET=latin1;

CREATE TABLE anObject(
    IdAnObject bigint(20) NOT NULL AUTO_INCREMENT PRIMARY KEY,
    Nameobject varchar(20) NOT NULL
)ENGINE=InnoDB DEFAULT CHARSET=latin1;

CREATE TABLE linkTable(
    Idatable bigint(20) NOT NULL REFERENCES a (Idatable),
    IdAnObject bigint(20) NOT NULL REFERENCES anObject (IdAnObject) ON DELETE CASCADE,
    PRIMARY KEY (Idatable, IdAnObject),
    CONSTRAINT linkTable_fk1
        FOREIGN KEY (Idatable)
        REFERENCES a (Idatable),
    CONSTRAINT linkTable_fk2
        FOREIGN KEY (IdAnObject)
        REFERENCES anObject (IdAnObject) ON DELETE CASCADE
)ENGINE=InnoDB DEFAULT CHARSET=latin1;

Unfortunately, it doesn't work because when I delete a row in the table 'linktable' the associated row in anObject isn't delete

Thank you

The CASCADE clause works in the opposite direction. From the parent to the child table.

When you delete a row in the parent table, the related rows in the child table are deleted as well.


Alternative to WHERE clause Is there a way to only SELECT rows with certain data in a column, without using WHERE?

e.g. If I had this:

SELECT * FROM Users
WHERE town = 'Townsville'

is there a way to implement the WHERE clause into the SELECT statement?

something like

SELECT *, town('Townsville') FROM Users

It's a bizarre question but it's one I've been asked by my peers

Not sure if this is the kind of crazy thing you were looking for....

Disclaimer: I have absolutely no idea why you would want to use this.

SELECT *
FROM Users AS u
INNER JOIN (SELECT 'Townsville' town) towns
  ON towns.town = u.Town;


Data too long for column MYSQL Error I've been pouring over threads regarding this issue, but ours seems unique in that it's not an issue with the query but the row itself; or at least I couldn't find a similar topic addressing how to fix the row.

A simple query like this:

> update customer set customer_name = 'Health Net of CA' where customer_id = '15484';

Results in:

ERROR 1406 (22001): Data too long for column 'customer_name' at row 1

Checking the character length:

mysql> select char_length(customer_name) from customer where customer_id = '15484';
+----------------------------+
| char_length(customer_name) |
+----------------------------+
|                         54 |
+----------------------------+
1 row in set (0.00 sec)

Describe shows:

 | customer_name      | varchar(255) | YES  |     | NULL    |                |

This database was populated using an import. I'm fairly sure strict mode was on but I didn't handle the import myself so I can't say for certain. This table has 39 columns, and most are Varchar(50) or tinyint so it shouldn't be an issue with the row being too big.

Any suggestions on fixing these bad rows?

Update:

SHOW CREATE TABLE customer;
| customer | CREATE TABLE `customer` (
  `customer_id` int(11) NOT NULL AUTO_INCREMENT,
  `customer_name` varchar(255) DEFAULT NULL,
  `customer_code` varchar(12) DEFAULT NULL,
  `customer_type` text,
  `bill_type` text,
  `attention` varchar(100) DEFAULT NULL COMMENT 'Attention to who we deal biz with',
  `address_id` int(11) DEFAULT NULL,
  `b_bad_debt` tinyint(1) DEFAULT '0',
  `b_fee_approval` tinyint(1) DEFAULT NULL COMMENT 'boolean flag for Fee Approval 1=set, 0=unset',
  `approval_amount` decimal(5,2) DEFAULT NULL,
  `notification` varchar(45) DEFAULT NULL COMMENT 'notified customer by email / fax or ftp',
  `b_tax_exempt` tinyint(1) DEFAULT '0' COMMENT 'Tax Exempt Flag',
  `sales_tax_number` varchar(20) DEFAULT NULL COMMENT 'sales tax/ permit no.',
  `b_prepay` tinyint(1) DEFAULT NULL,
  `create_date` datetime DEFAULT NULL,
  `last_updated` timestamp NULL DEFAULT NULL,
  `active` tinyint(4) DEFAULT '1',
  `created_by` varchar(45) DEFAULT NULL,
  `updated_by` varchar(45) DEFAULT NULL,
  `email` varchar(45) DEFAULT NULL,
  `email_extra` mediumtext COMMENT 'extra emails as per creation',
  `state_tax_code` varchar(2) DEFAULT NULL COMMENT 'this is as CA if CA state and refer to TAX table',
  `email_verified_by` varchar(45) DEFAULT 'NA',
  `fax_verified_by` varchar(45) DEFAULT 'NA',
  `b_always_send` tinyint(1) DEFAULT '0' COMMENT 'there is customer that we need always send',
  `b_project_customer` tinyint(1) DEFAULT '0',
  `b_exception_list` tinyint(1) DEFAULT '0',
  `b_has_inslist` tinyint(1) DEFAULT '0',
  `customer_passwrd` varchar(255) DEFAULT NULL,
  `delivery_opt` varchar(45) DEFAULT NULL,
  `max_fax_pages` int(11) DEFAULT NULL,
  `phone` varchar(20) DEFAULT NULL,
  `fax` varchar(20) DEFAULT NULL,
  `phone_extra` mediumtext,
  `phone_second` varchar(20) DEFAULT NULL,
  `b_multi_suites` tinyint(1) DEFAULT '0',
  `suite_list` varchar(255) DEFAULT NULL,
  `b_portal_download` tinyint(1) DEFAULT '0',
  `b_no_download` tinyint(1) DEFAULT '0',
  PRIMARY KEY (`customer_id`),
  KEY `customer_idx` (`customer_code`,`customer_name`)
) ENGINE=InnoDB AUTO_INCREMENT=18870 DEFAULT CHARSET=utf8 |

EDIT:

To change the mode

This can be done in two ways:

Open your "my.ini" file within the MySQL installation directory, and look for the text "sql-mode".
Find:

Code:

Set the SQL mode to strict

sql-mode="STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"
Replace with:

Code:

Set the SQL mode to strict

sql-mode="NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"
Or

You can run an SQL query within your database management tool, such as phpMyAdmin:
Code:

SET @@global.sql_mode= '';

From post:https://stackoverflow.com/questions/15949038/error-code-1406-data-too-long-for-column-mysql


Store millions of rows of denomalized data or some SQL magic? My DBA experience doesn't go much further than simple storage + retrieval of CMS style data - so this may be a silly question, I don't know!

I have a problem whereby I need to lookup or calculate holiday prices for a certain group size and a certain number of days within a certain time period. E.g.:

How much is a hotel room for 2 people for 4 nights anytime in January?

I have pricing and availability data for, say, 5000 hotels stored like so:

Hotel ID | Date | Spaces | Price PP
-----------------------------------
     123 | Jan1 | 5      | 100
     123 | Jan2 | 7      | 100
     123 | Jan3 | 5      | 100
     123 | Jan4 | 3      | 100
     123 | Jan5 | 5      | 100
     123 | Jan6 | 7      | 110
     456 | Jan1 | 5      | 120
     456 | Jan2 | 1      | 120
     456 | Jan3 | 4      | 130
     456 | Jan4 | 3      | 110
     456 | Jan5 | 5      | 100
     456 | Jan6 | 7      |  90

With this table, I can do a query like so:

SELECT hotel_id, sum(price_pp)
FROM hotel_data
WHERE
    date >= Jan1 and date <= Jan4
    and spaces >= 2
GROUP BY hotel_id
HAVING count(*) = 4;

results

hotel_id | sum
----------------
     123 | 400

The HAVING clause here makes sure that there is an entry for every single day between my desired dates that has the spaces available. ie. Hotel 456 had 1 space available on Jan2, the HAVING clause would return 3, so we don't get a result for hotel 456.

So far so good.

However, is there a way to find out all the 4 night periods in January where there is space available? We could repeat the query 27 times - incrementing the dates each time, which does seem a little bit awkward. Or another way around could be to store all possible combinations in a lookup table like so:

Hotel ID | total price pp | num_people | num_nights | start_date
----------------------------------------------------------------
     123 |            400 | 2          | 4          | Jan1
     123 |            400 | 2          | 4          | Jan2
     123 |            400 | 2          | 4          | Jan3
     123 |            400 | 3          | 4          | Jan1
     123 |            400 | 3          | 4          | Jan2
     123 |            400 | 3          | 4          | Jan3

And so on. We'd have to limit max number of nights, and the max number of people we would search for - e.g. max nights = 28, max people = 10 (limited to the number of spaces available for that set period starting on that date).

For one hotel, this could give us 28*10*365=102000 outcomes per year. 5000 hotels = 500m outcomes!

But we'd have a very simple query to find the cheapest 4 night stay in Jan for 2 people:

SELECT
hotel_id, start_date, price
from hotel_lookup
where num_people=2
and num_nights=4
and start_date >= Jan1
and start_date <= Jan27
order by price
limit 1;

Is there a way to perform this query on the initial table without having to generate the 500m row lookup table!? e.g. generate the 27 possible outcomes in a temporary table or some other such inner query magic?

At the moment all data is held in a Postgres DB - if needs be for this purpose we can move the data out to something else more suitable? Not sure if this type of query fits the map/reduce patterns for NoSQL style DBs ...

You can do much with window functions. Presenting two solutions: one with and one without materialized view.

Test case

Building on this table:

CREATE TABLE hotel_data (
   hotel_id int
 , day      date  -- using "day", not "date"
 , spaces   int
 , price    int
 , PRIMARY KEY (hotel_id, day)  -- provides essential index automatically
);

Days per hotel_id must be unique (enforced by PK here), or the rest is invalid.

Multicolumn index for base table:

CREATE INDEX mv_hotel_mult_idx ON mv_hotel (day, hotel_id);

Note the reversed order as compared to the PK. You will probably need both indexes, for the following query, the 2nd index is essential. Detailed explanation:

Is a composite index also good for queries on the first field?
Working of indexes in PostgreSQL

Direct query without MATERIALIZED VIEW

SELECT hotel_id, day, sum_price
FROM  (
   SELECT hotel_id, day, price, spaces
        , sum(price)      OVER w * 2   AS sum_price
        , min(spaces)     OVER w       AS min_spaces
        , last_value(day) OVER w - day AS day_diff
        , count(*)        OVER w       AS day_ct
   FROM   hotel_data
   WHERE  day BETWEEN '2014-01-01'::date AND '2014-01-31'::date
   AND    spaces >= 2
   WINDOW w AS (PARTITION BY hotel_id ORDER BY day
                ROWS BETWEEN CURRENT ROW AND 3 FOLLOWING) -- adapt to nights - 1
   ) sub
WHERE  day_ct = 4
AND    day_diff = 3  -- make sure there is not gap
AND    min_spaces >= 2
ORDER  BY sum_price, hotel_id, day;
-- LIMIT 1 to get only 1 winner;

Also see @ypercube's variant with lag(), which can replace day_ct and day_diff with a single check.

How?

In the subquery, only consider days within your time frame ("in January" means, the last day is included in the time frame).
The frame for the window functions spans the current row plus the next num_nights - 1 (4 - 1 = 3) rows (days). Calculate the difference in days , the count of rows and the minimum of spaces to make sure the range is long enough, gapless and always has enough spaces.

Unfortunately, the frame clause of window functions does not accept dynamic values, so ROWS BETWEEN CURRENT ROW AND 3 FOLLOWING` cannot be parameterized for a prepared statement.

I carefully drafted all window functions in the subquery to reuse the same window, using a single sort step.
The resulting price sum_price is already multiplied by the number of spaces requested.

With MATERIALIZED VIEW

To avoid inspecting many rows without chance of success, save only the columns you need plus three redundant, calculated values from the base table. Be sure the MV is up to date. If you are not familiar with the concept, read the manual first.

CREATE MATERIALIZED VIEW mv_hotel AS
SELECT hotel_id, day
     , first_value(day) OVER (w ORDER BY day) AS range_start
     , price, spaces
     ,(count(*)    OVER w)::int2 AS range_len
     ,(max(spaces) OVER w)::int2 AS max_spaces

FROM  (
   SELECT *
        , day - row_number() OVER (PARTITION BY hotel_id ORDER BY day)::int AS grp
   FROM   hotel_data
   ) sub1
WINDOW w AS (PARTITION BY hotel_id, grp);

range_start stores the first day of each continuous range for two purposes:

to mark a set of rows as members of a common range
to show the start of the range for possible other purposes.

range_len is the number of days in the gapless range.
max_spaces is the maximum of open spaces in the range.

Both columns are used to exclude impossible rows from the query immediately.

I cast both to smallint ( max. 32768 should be plenty for both) to optimize storage: only 52 bytes per row (incl. heap tuple header and item identifier). Details:

Configuring PostgreSQL for read performance

Multicolumn index for MV:

CREATE INDEX mv_hotel_mult_idx ON mv_hotel (range_len, max_spaces, day);

Query based on MV

SELECT hotel_id, day, sum_price
FROM  (
   SELECT hotel_id, day, price, spaces
        , sum(price)      OVER w * 2   AS sum_price
        , min(spaces)     OVER w       AS min_spaces
        , count(*)        OVER w       AS day_ct
   FROM   mv_hotel
   WHERE  day BETWEEN '2014-01-01'::date AND '2014-01-31'::date
   AND    range_len >= 4   -- exclude impossible rows
   AND    max_spaces >= 2  -- exclude impossible rows
   WINDOW w AS (PARTITION BY hotel_id, range_start ORDER BY day
                ROWS BETWEEN CURRENT ROW AND 3 FOLLOWING) -- adapt to $nights - 1
   ) sub
WHERE  day_ct = 4
AND    min_spaces >= 2
ORDER  BY sum_price, hotel_id, day;
-- LIMIT 1 to get only 1 winner;

This is faster than the query on the table because more rows can be eliminated immediately. Again, the index is essential. Since partitions are gapless here, checking day_ct is enough.

SQL Fiddle demonstrating both.

Repeated use

If you use it a lot, I would create an SQL function and only pass parameters. Or a PL/pgSQL function with dynamic SQL and EXECUTE to allow adapting the frame clause.

Alternative

Range types with date_range to store continuous ranges in a single row might be an alternative - complicated in your case with potential variations on prices or spaces per day.

Related:

Find available apartments given a date range, a duration, and a list of unavailable dates
Native way of performing this hours of operation query in PostgreSQL


How can the LEFT JOIN return more results than the unjoined query? Consider the following queries:

mysql> SELECT count(*) FROM list l WHERE l.source='blink';
+----------+
| count(*) |
+----------+
|     3372 |
+----------+
1 row in set (0.00 sec)

mysql> SELECT count(*) FROM list l LEFT JOIN cardinal c ON l.id=c.id WHERE c.resolution IN ('Left','Right','Up') AND l.source='blink';
+----------+
| count(*) |
+----------+
|     5116 |
+----------+
1 row in set (2.47 sec)

There were no INSERT or other queries run on the database between these two queries running. The second query seems more restrictive than the first query, so how could it return more rows? Note that the list table has over 2 million rows, and the cardinal table has about half a million rows.

EDIT: Adding EXPLAIN output:

mysql> EXPLAIN SELECT count(*) FROM list l LEFT JOIN cardinal c ON l.id=c.id WHERE c.resolution IN ('Left','Right','Up') AND l.source='blink';
+----+-------------+-------+--------+-------------------+---------+---------+-------------+--------+-------------+
| id | select_type | table | type   | possible_keys     | key     | key_len | ref         | rows   | Extra       |
+----+-------------+-------+--------+-------------------+---------+---------+-------------+--------+-------------+
|  1 | SIMPLE      | c     | ALL    | NULL              | NULL    | NULL    | NULL        | 536258 | Using where |
|  1 | SIMPLE      | l     | eq_ref | PRIMARY,id,source | PRIMARY | 8       | direct.c.id |      1 | Using where |
+----+-------------+-------+--------+-------------------+---------+---------+-------------+--------+-------------+
2 rows in set (0.00 sec)

mysql> select count(*) from list;
+----------+
| count(*) |
+----------+
|  2165664 |
+----------+
1 row in set (1.32 sec)

mysql> select count(*) from cardinal;
+----------+
| count(*) |
+----------+
|   537007 |
+----------+
1 row in set (0.23 sec)

Each row in the first table may match more than one row in the second table.

You can check this with:

SELECT l.id,count(*)
FROM list l
LEFT JOIN cardinal c ON l.id=c.id
WHERE c.resolution IN ('Left','Right','Up') AND l.source='blink'
group by l.id
having count(*)>1;

A little demo is on SQL Fiddle here.


Filter created_at and count distinct minutes I have this query to get records from a database:

SELECT camera_id, count(*) AS snapshot_count
FROM   snapshots
WHERE  created_at >= timestamp 'yesterday'
AND    created_at <  timestamp 'today'
GROUP  BY camera_id;

I want to add some filters on created_at to get the count for matching rows only. E.g., the user will provide camera_id , date, time, days, for example:

From date: 2015/01/01
to date: 2015/12/30
Schedule: Monday-Friday
Timings: 9 AM to 5 pm UTC

How to apply all those to created_at to get valid records?

Update:

I also want a count per minute. If 1 minute contains more than 1 image it shall still be counted as one. You could say, only count the first snapshot per minute.

Shortly if it is 1 per 60 minutes then it will be 1 count for 60 minutes between 9 AM to 10AM and so as on to 5PM.

For lack of information, assuming created_at is data type timestamp, saving UTC timestamps:
SELECT camera_id, count(*) AS snapshot_count
FROM   snapshots
WHERE  created_at >= timestamp '2015-01-01'   -- From date: 2015/01/01
AND    created_at <  timestamp '2015-12-31'   -- to date: 2015/12/30
                                              -- chop off '2015-12-31'?
AND    created_at::time BETWEEN time '09:00' AND time '17:00'
                                              -- 9 AM to 5 AM UTC
                                              -- assuming you meant 5 PM
AND    EXTRACT('ISODOW' FROM created_at) < 6  -- Monday-Friday
GROUP  BY camera_id;

The check for date seems trivial - but be careful what to include / exclude exactly with upper lower bound.

For the time component cast to time: created_at::time.

For the day of week use EXTRACT() with the pattern ISODOW (simpler for your kind of pattern than DOW).
In case you are not aware of the difference between the data types timestamp and timestamptz and the role of the timezone settings and DST, now is the time to read up:

Ignoring timezones altogether in Rails and PostgreSQL

Count minutes with multiple rows as one
For your added Update:
SELECT camera_id
     , count(DISTINCT date_trunc('minute', created_at) AS snapshot_minute_count
FROM ...

Effectively, minutes are counted in which at least one (filtered) snapshot was taken. If 7 snapshots were taken in the same minute, they still count as one.
Performance
The filters on time and day of week are not sargable. If your table is big and performance important, create functional indexes to change that - using IMMUTABLE expressions. Depending on your actual data type, you need to create an IMMUTABLE wrapper function that works independently of the timezone setting, because neither the cast to time nor EXTRACT() (internally the function date_part()) are defined IMMUTABLE for various reasons.
CREATE FUNCTION f_to_time(timestamp)
  RETURNS time LANGUAGE sql IMMUTABLE COST 5 AS
'SELECT $1::time';

CREATE OR REPLACE FUNCTION f_to_isodow(timestamp)
  RETURNS int LANGUAGE sql IMMUTABLE COST 20 AS
$$SELECT EXTRACT('ISODOW' FROM $1 AT TIME ZONE 'UTC')::int$$;

Then:
CREATE INDEX snapshots_created_at_time_idx ON snapshots (f_time(created_at));
CREATE INDEX snapshots_created_at_isodow_idx ON snapshots (f_to_isodow(created_at));

To utilize these indexes, the query hast to use the same expressions:
SELECT camera_id
     , count(*) AS snapshot_count
     , count(DISTINCT date_trunc('minute', created_at) AS snapshot_minute_count
FROM   snapshots
WHERE  created_at >= timestamp '2015-01-01'
AND    created_at <  timestamp '2015-12-31'
AND    f_time(created_at) BETWEEN time '09:00' AND time '17:00'
AND    f_to_isodow(created_at) < 6
GROUP  BY camera_id;


sql server batch execution sequence I have a sql server batch file containing a lot of queries like:

    update_query_1;
    insert_query_2;
    update_query_3;

The query sequence matters, meaning they should be executed one by one. My question is, will sql server automatically optimize the query by parallel processing, which might destroy the order. If so, how to ensure the sequence?

No matter how many processors you throw at it, SQL Server will never "parallelize" across multiple statements in the same batch. It will completely finish update_query_1 before it starts insert_query_2, and it will completely finish insert_query_2 before it starts update_query_3.

Nothing about any of the statements in your batch is asynchronous unless you are talking about starting jobs, invoking Service Broker, or triggering some side effect. But none of these things would technically be part of this batch.


How bad is it to delete from a Greenplum AO table? We have a few AO tables (which nowadays means "Append Optimized", not "Append Only"). Normally, we just insert into these tables.

As part of a significant system change, I need to do a fairly large delete from these tables. It's only about 2% of the data volume, and only over a small range of partitions. Eventually those partitions will roll off the end of our retention policy.

I can't find any information on the consequences of executing a delete on an AO table.

The main difference between heap table and AO tables is how the visibility data is stored. In heap tables visibility information is the header of each record, AO tables have a separate visibility map structure.

Once there have been UPDATE or DELETE operations on an AO table this visibility map needs to be processed. This will incur a very small addition load for reading the map and processing it. The processing is just a set/not-set check so it's a lot lighter than the range check for MVCC visibility on heap tables.

With a small percentage of rows being dropped the effects on scan operations will be negligible. If you VACUUM an AO table where there have been UPDATE or DELETE operations then the table will be compacted, this will create a lot of disk IO and may require quite a bit of space.


Understanding many-to-many ternary relationship sets I am new to entity-relationship diagrams. I'm a bit confused when it comes to interpreting ternary relationship sets such as this:

Does this signify that every relationship instance of Party will have one fighter, one mage, and one healer? If we were simply dealing with binary relationship sets without key or total constraints, every instance of the relationship would be linked to an entity from each entity set.
But in the case above, isn't it possible to not have one instance from a particular entity set? E.g. if the Party instance only had one fighter and one healer (and no mage)?

In a diagram that included that, I would call that a simple foreign key constraint. It is likely this diagram is first draft\ over-simplification of how player classes are related to parties.

If I was pushed for a stronger answer, I would have it as a many-to-one (multiple of each class can be in one party) This means that a party can have one or multiple of any class of person, but each person can only be part of one party.

The confusion for this really comes from the fact that a class is not an object in itself, it is a characteristic of a person. If you think of the class as a characteristic of a person, now you have three people in the party. The logic behind calling the party complete would be to check if, of the people in it, there is at least one of each that has the characteristic of mage, fighter, and healer. To go one step further in this, a person's party is also a characteristic of theirs. This would allow multiple people to be assigned a party, but each person can only be assigned ONE party.


Comparing Environments I am a SQL Server dba. I have been asked to compare our production environment to test from a number of different perspectives. These are windows patch level,sql service pack, to database size and compabilty modes. I also need to identify global environmental differences such as max dop settings to memory allocation. Can anyone advise of useful tsql or powershell where this can be captured as a report style format.

Your best bet is to use :

Discover, Diagnose, and Document ALL Your SQL Servers On Your Coffee Break using SQL Power Doc written by  kendal vandyke

This is written in Powershell.


Generate `insert` statements for each entry in a table What is the simplest way to generate insert statements fro every entry in a table? For a table with 3 rows, I need to generate 3 insert statements.

For a table with n rows I need to write to a file n insert statements.

For example, for row (foo, bar, baz) I need to write to that file the statement:

insert into desired_table values (foo, bar, baz)

In SQL Developer, use the /*insert*/ "hint".

select /*insert*/ * from t1;

REM INSERTING into T1
SET DEFINE OFF;
Insert into T1 (C1,C2) values (1,2);
Insert into T1 (C1,C2) values (2,3);
Insert into T1 (C1,C2) values (3,4);

Another useful "hint":

select /*csv*/ * from t1;

"C1","C2"
1,2
2,3
3,4

Starting with version 4.1, the "hint" the below also works:

set sqlformat insert
select * from t1;

The output can be spooled as well:

set sqlformat insert
spool C:\Users\balaz\Desktop\insert.sql
select * from t1;
spool off


Backing up file groups with restore to earlier backup I have a task: I have created a database with a filegroup lamb that has a few tables.

I need to extract a csv into a table in a filegroup named lamb and back up that filegroup.

Then extract another csv file again and back up again.

But I need to be able to restore the first backup.

What is the backup and restore method? Is it possible to restore only the lamb filegroup?

What is the backup and restore method?
It is called Piece Meal Restore and unrestored filegroups can be restored at a later time.

Is it possible to restore only the lamb filegroup?
Yes it is possible to restore only the lamb filegroup.

Below will show you - how you can do it.

Create a database called "FGTest"

create database [FGTest] on primary (
name = N'FGTest'
,FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL10_50.SQL2008R2\MSSQL\DATA\FGTest.mdf'
,SIZE = 3072 KB
,FILEGROWTH = 1024 KB
)
,FILEGROUP [lamb] (
name = N'lamb'
,-- fileGroup1
FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL10_50.SQL2008R2\MSSQL\DATA\FGTest_2.ndf'
,SIZE = 3072 KB
,FILEGROWTH = 1024 KB
)
,FILEGROUP [lamb1] (
name = N'lamb1'
,-- fileGroup2
FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL10_50.SQL2008R2\MSSQL\DATA\FGTest_3.ndf'
,SIZE = 3072 KB
,FILEGROWTH = 1024 KB
) LOG on (
name = N'FGTest_log'
,FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL10_50.SQL2008R2\MSSQL\DATA\FGTest_log.ldf'
,SIZE = 1024 KB
,FILEGROWTH = 10 %
)

go
Now create tables on different file groups - lamb and lamb1

create table TAB1 (
TAB1_ID int IDENTITY(1, 1)
,TAB1_NAME varchar(100)
,constraint PK_TAB1 primary key (TAB1_ID)
) on lamb -- Filegroup we created.
go

create table TAB1_lamb1 (
TAB1_ID int IDENTITY(1, 1)
,TAB1_NAME varchar(100)
,constraint PK_TAB1_lamb1 primary key (TAB1_ID)
) on lamb1 -- 2nd Filegroup we created.
go

Take a Base backup

-- Take a base backup
BACKUP DATABASE [FGTest] to
DISK = N'C:\Program Files\Microsoft SQL Server\MSSQL10_50.SQL2008R2\MSSQL\Backup\FGTest_basebackup.bak'
with init, stats = 10
go

Now insert some values .... You can load a CSV at this point

INSERT INTO FGTest..TAB1(TAB1_NAME)
select ('TAB1')
union all
select ('TAB2')

backup filegroup lamb -- with 2 records

-- backup filegroup lamb - with 2 records

BACKUP DATABASE [FGTest] FILEGROUP = N'lamb'
TO  DISK = N'C:\Program Files\Microsoft SQL Server\MSSQL10_50.SQL2008R2\MSSQL\Backup\FGTest_2Records.bak'
WITH  INIT,  NAME = N'FGTest-Full Filegroup Backup',
STATS = 10
GO

-- log backup filegroup lamb - with 2 records

BACKUP LOG [FGTest] TO DISK = 'C:\Program Files\Microsoft SQL Server\MSSQL10_50.SQL2008R2\MSSQL\Backup\FGTest_2records.trn' WITH INIT

Now insert few more values in lamb and lamb1 file groups

INSERT INTO FGTest..TAB1(TAB1_NAME)
select ('TAB3')
union all
select ('TAB4')

INSERT INTO FGTest..TAB1_lamb1(TAB1_NAME)
select ('TAB3')
union all
select ('TAB4')

Now backup lamb and lamb1 filegroups along with the T-logs

-- -- backup filegroup lamb - with 4 records

BACKUP DATABASE [FGTest] FILEGROUP = N'lamb'
TO  DISK = N'C:\Program Files\Microsoft SQL Server\MSSQL10_50.SQL2008R2\MSSQL\Backup\FGTest_4Records.bak'
WITH  INIT,  NAME = N'FGTest-Full Filegroup Backup',
STATS = 10
GO

-- log backup filegroup lamb - with 4 records

BACKUP LOG [FGTest] TO DISK = 'C:\Program Files\Microsoft SQL Server\MSSQL10_50.SQL2008R2\MSSQL\Backup\FGTest_4records.trn' WITH INIT

-- -- backup filegroup lamb1 - with 2 records

BACKUP DATABASE [FGTest] FILEGROUP = N'lamb1'
TO  DISK = N'C:\Program Files\Microsoft SQL Server\MSSQL10_50.SQL2008R2\MSSQL\Backup\FGTest_lamb1.bak'
WITH  INIT,  NAME = N'FGTest-Full Filegroup Backup',
STATS = 10
GO

-- log backup filegroup lamb1 - with 2 records

BACKUP LOG [FGTest] TO DISK = 'C:\Program Files\Microsoft SQL Server\MSSQL10_50.SQL2008R2\MSSQL\Backup\FGTest_lamb1_log.trn' WITH INIT

Now comes the restore Part :

Restore the database with PARTIAL and NORECOVERY

 use master
 go

 -- restore the base backup with PARTIAL and NORECOVERY

 restore database [FGTest]
 filegroup = 'Primary'
 from disk = 'C:\Program Files\Microsoft SQL Server\MSSQL10_50.SQL2008R2\MSSQL\Backup\FGTest_basebackup.bak'
 with REPLACE, PARTIAL, NORECOVERY
 go

Now Restore the lamb filegroup along with the T-log

 RESTORE DATABASE [FGTest] FILE = N'lamb' FROM
 DISK = N'C:\Program Files\Microsoft SQL Server\MSSQL10_50.SQL2008R2\MSSQL\Backup\FGTest_2Records.bak'
 WITH  FILE = 1, REPLACE,  STATS = 10
 GO

 RESTORE LOG [FGTest] FROM DISK = 'C:\Program Files\Microsoft SQL Server\MSSQL10_50.SQL2008R2\MSSQL\Backup\FGTest_2records.trn'
 WITH FILE = 1, NORECOVERY

-- since you want to restore the database to 1st backup and you already restored the sequence, its time to bring the database back online with 2 records.

restore database [FGTest] with recovery

Since you have not brought the other file group online, when you try to query TAB1_lamb1 table residing on lamb1 filegroup, the query processor will throw an error.


Oracle Developers guide 10g is not connecting to server I have installed Oracle developer's guide 10g on Windows 10; it installed successfully.
While connecting to Oracle EBS host data base it give an error.
Entered detail on connection screen:
User - myuser
Pass - mypass
Connect String - myuser@SID

Error:

ORA-12154: TNS:could not resolve the connect identifier specified
Cause
The service name specified is not defined correctly in the TNSNAMES.ORA file.

I check my TNSNAMES.ORA file at my database host machine. I found entry TNSNAMES.ORA:
SID =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = myhost)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = SID)
    )
  )

Where and what changes do I need to make in the settings file to connect it properly?
Host machine OS is Linux. Listener services is started.
Note:
tnsnames.ora and sqlnet.ora files are present at two places
1. C:\DevSuiteHome_1\NETWORK\ADMIN - Path created after installing oracle developer guide 10g
2. E:\app\product\11.2.0\dbhome_1\NETWORK\ADMIN - previous oracle installation path
is there any catch in this file locations ?
tnsping result:
C:\Users\mymachine>tnsping mymachine
Used parameter files:
C:\DevSuiteHome_1\network\admin\sqlnet.ora

Attempting to contact (DESCRIPTION=(CONNECT_DATA=(SERVICE_NAME=my_machine_name))
(ADDRESS=(PROTOCOL=TCP)(HOST=my_ip)(PORT=1521)))
OK (20 msec)

C:\Users\mymachine>tnsping IP.32.88
Used parameter files:
C:\DevSuiteHome_1\network\admin\sqlnet.ora

Attempting to contact (DESCRIPTION=(CONNECT_DATA=(SERVICE_NAME=IP.32.88))(ADDRESS=(PROTOCOL=TCP)(HOST=IP.32.88)(PORT=1521)))
OK (70 msec)

You said "I check my TNSNAMES.ORA file at my database host machine."  (emphasis mine).  The tnsnames.ora file is used by the client process. There are some other things to check as well. See this. But from what you've shown so far, I'd guess you are looking at the wrong file.  Oh, and since the nature of the problem means oracle couldn't even get as far as sending a request (because he didn't know where to send it), the listener never enters the picture for this error.


GROUP BY needs ORDER BY NULL to avoid filesort I've noticed again and again that when I EXPLAIN queries with a GROUP BY clause, I get filesort as an extra condition. A long time ago I read a suggestion to use GROUP BY NULL in these cases to avoid the filesort, and it does indeed eliminate that unsavoury looking filesort condition.

I would think that if no ORDER BY clause is present that the dbms would just present an arbitrary order or whatever is most efficient rather than ordering by some mysterious column which requires a filesort.  It seems strange to me that I need to include an extra direction which basically amounts to saying "don't do anything stupid".

My question is why is this even necessary and is adding ORDER BY NULL actually helping performance?

MySQL 5.7 Reference Manual  /  ...  /  SELECT Syntax

  If you use GROUP BY, output rows are sorted according to the GROUP BY columns as if you had an ORDER BY for the same columns. To avoid the overhead of sorting that GROUP BY produces, add ORDER BY NULL


SQLcl: set color in sqlprompt (ANSI/VT100 Control sequences) This is my login.sql

SET sqlprompt "_user'@'_connect_identifier > "
SET sqlformat ansiconsole
SET serveroutput on
SET lines 3000

How to set the sqlprompt to color red for Prod (and green color for testing)?

From above link I created a Java Script in sqlcli bin directory(on windows 10)

C:\sqlcl-19.2.1.246.1707\sqlcl\bin>type PromptColor.js
//file name PromptColor.js save in sqlcli bin dir
//login sqlcli and run script PromptColor.js
var dbUser = util.executeReturnOneCol('select user from sys.dual');
if ( dbUser == 'HR' )
  {
   sqlcl.setStmt('set sqlprompt "@|red _USER|@@@|red _CONNECT_IDENTIFIER|@@|red >|@"');
  }
else if ( dbUser == 'SOE')
  {
    sqlcl.setStmt('set sqlprompt "@|green _USER|@@@|green _CONNECT_IDENTIFIER|@@|green >|@"');
  }

else
  {
    sqlcl.setStmt('set sqlprompt "@|blue _USER|@@@|blue _CONNECT_IDENTIFIER|@@|blue >|@"');
  }

sqlcl.run();

login with default prompt

C:\sqlcl-19.2.1.246.1707\sqlcl\bin>sql soe/soe@localhost:1521/orclpdb.localdomain -- pdb database

SQLcl: Release 19.2.1 Production on Tue Nov 12 12:17:13 2019

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

Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0
SQL>script PromptColor.js

Next is to figure how to call javascript from login.sql

Edit:- create a sql file with single line script PromptColor.js place in SQLPATH directory and add that sqlfile name in login.sql it automatically changes color based on user logged in


How to trim the log file that stores information about job history execution? I was having a look at my sql server jobs and proxies on an specific server in the production environment.
I pretty much have an script to check the status of the jobs but in case I have to troubleshoot why a job failed on this particular server it has been using the following settings:

log to table
append output to existing entry in table
include step output in history

But no file has been specified, so when I click on View to check that has been going on, I get and out of memory exception, as the table is too big.

the way I find out the size of this log file (which I am not sure where is stored):
    use msdb
    go

    declare @job_id    UNIQUEIDENTIFIER
           ,@job_name  sysname

    select @job_name= N'DBA - my job name'

    exec sp_help_jobsteplog @job_name=@job_name

there is basically one for each step in the job as you can see on the picture below:

===================================
Exception of type 'System.OutOfMemoryException' was thrown. (Microsoft.SqlServer.Smo)

Program Location:
at Microsoft.SqlServer.Management.Smo.Agent.JobStep.EnumLogs()
at Microsoft.SqlServer.Management.SqlManagerUI.JobStepAdvancedLogging.ReadStepLogToFile(JobStep step)
at Microsoft.SqlServer.Management.SqlManagerUI.JobStepAdvancedLogging.viewTableLog_Click(Object sender, EventArgs e)
TITLE: Microsoft SQL Server Management Studio
Exception of type 'System.OutOfMemoryException' was thrown. (Microsoft.SqlServer.Smo)

BUTTONS:
OK
My question would be:
How do I trim this log file ?
I know I could simply set the long to an external text file, but that would only be my second option.

The information is not in a file, it is a table named sysjobstepslogs. The column for the logged data is nvarchar(max). I don't think there's any trunc option for this table, so if you output lots and lots of data from a job step, then you will have to live with having the last execution's data in this table being huge.
Note, that the regular sysjobhistory table (the one that the history GUI in SSMS uses) is capped at about 4000 characters and it doesn't include result from SELECT (it only includes output from errors and PRINT).
This raises the question why you have this huge amount of output from some of your jobsteps? Are you doing unnecessary SELECT in the job?
In case you can't trim down the output from the jobstep, then I would suggest that you just don't look at the data using the SSMS GUI since it bombs on that amount of output. This can be considered a bug, so report it to MS if you want. Anyhow, you can do SELECT from the sysjobstepslogs table directly and if SSMS chokes on the amount of data returned, then just do some substring to cap the length.


SSIS Package Data Flow Task RollBack on Error I tried running a package and it failed but I noticed in the output box (in visual studio) that it managed to write 2000+ rows to a table. I was wondering if an SSIS package would roll back the transactions in my Data Flow Task if the overall package failed. I found this thread

http://www.sqlservercentral.com/Forums/Topic998096-391-1.aspx#bm998104

which states that in order for the package to rollback DFT changes:

Set the TransactionOption property of the package to "Required"
Set the TransactionOption property of the dft to "Supported"

I had both the Package and DFT Transaction Option property set to supported so I am guessing that the rows written to the table were not rolled back... Do you all agree that this is the best way to make sure table changes are rolled back in the event the package fails? Will this also rollback file System Tasks such as file moves on the local file system?

I would design each package as a self-contained unit that can be rerun without any manual preparation or clean-up.  Relying on SQL transactions can dramatically slow execution and as you noted does not cover all the non-SQL Tasks.


Do triggers cause extra index updates? I am dealing with a db managed by a 3rd party framework and I can't change it... yet. Isn't that always the case.
Long story short they are never setting nullable fields to null. They are putting in defaults for everything. Think 0 for Int and '1900-01-01 00:00:00.000' for DateTime.
While there are millions of rows in the table, the columns are sparse and should be null most of the time.
I have created filtered indexes that have a [Where SomeField is not null].
While analyzing some query plans I noticed I was getting giant numbers back for [Estimated Number of Rows] and upon investigation I noticed my values weren't null like I thought would be.
This meant all of my indexes are bloated with default values.
I would like to create triggers that set the actual values to null if the value is a default value.
My question is, "Do triggers get fired before indexes are updated? Will I be updating my index twice?"

It depends on if you use an INSTEAD OF trigger or an AFTER trigger.
It sounds like you want an INSTEAD OF trigger where you can prevent the default values from being INSERTed or UPDATEd and replace them with NULLs instead.
(An AFTER trigger would effectively cause the index to update twice.)
You can read more on how to implement an INSTEAD OF trigger here.


How to loop through AS400 table with cursor I have a AS400 table that contains multiple rows per person. Each row contains data concerning a transaction by the person including points assigned to each transaction. I read through all rows for one person ordered by the points. The first row with the highest points, I leave alone. On subsequent rows the point values are cut in half and I do an update. I am currently using a cursor to open the table and doing a Loop to read through the rows. I am getting an -508 error stating :

  An UPDATE or DELETE statement with a WHERE CURRENT OF CL was attempted, but the cursor is not positioned on a row or is positioned on a row, but the row is not locked because a COMMIT HOLD or ROLLBACK HOLD statement released the lock on the row. A FETCH statement must be issued to position the cursor on a row and lock the row. Recovery  . . . :   Issue a FETCH statement to position the cursor on a row and lock the row; then, try the request again.

Part of my code is below:

DECLARE V_LNAME CHAR ( 30 ) ;
DECLARE V_LNAMEHOLD CHAR ( 30 ) ;
DECLARE V_FNAME CHAR ( 15 ) ;
DECLARE V_FNAMEHOLD CHAR ( 15 ) ;
DECLARE V_DOB DATE ;
DECLARE V_DOBHOLD DATE ;
DECLARE V_TRANSNMBR CHAR ( 9 ) ;
DECLARE V_TRANSNMBRHOLD CHAR ( 9 ) ;
DECLARE V_POINTS NUMERIC ( 5 ) ;
DECLARE V_POINTSHOLD NUMERIC ( 5 ) ;
DECLARE V_POINTSEQ NUMERIC ( 5 ) ;
DECLARE FIRSTRECORD CHAR ( 1 ) ;
DECLARE CL CURSOR FOR
SELECT LNAME , FNAME , DOB , TRANSNCNMBR , TOPOINTS
FROM DB_TRANSDATA
ORDER BY LNAME ASC , FNAME ASC , DOB ASC , TOPOINTS DESC ;
DECLARE CLHLD CURSOR FOR
SELECT LNAME , FNAME , DOB , TRANSNCNMBR , TOPOINTS
FROM DB_TRANSDATA
ORDER BY LNAME ASC , FNAME ASC , DOB ASC , TOPOINTS DESC ;

OPEN CLHLD ;
FETCH CLHLD INTO V_LNAMEHOLD , V_FNAMEHOLD , V_DOBHOLD , V_TRANSNMBRHOLD ;
close clhld;

OPEN CL ;
SET FIRSTRECORD = 'Y' ;
LOOP
FETCH CL INTO V_LNAME , V_FNAME , V_DOB , V_TRANSNMBR , V_POINTS , V_POINTSEQ ;
IF TRIM ( CHAR ( V_LNAME ) ) = TRIM ( CHAR ( V_LNAMEHOLD ) ) AND TRIM ( CHAR ( V_FNAME ) ) = TRIM ( CHAR ( V_FNAMEHOLD ) ) AND V_DOB = V_DOBHOLD AND V_TRANSNMBR = V_TRANSNMBRHOLD AND FIRSTRECORD = 'N' THEN
SET V_POINTSEQ = V_POINTS * .5 ;

UPDATE DB_TRANSDATA
SET POINTSEQ = V_POINTSEQ
WHERE CURRENT OF CL ;

SET V_LNAMEHOLD = V_LNAME ;
SET V_FNAMEHOLD = V_FNAME ;
SET V_DOBHOLD = V_DOB ;
SET V_TRANSNMBRHOLD = V_TRANSNMBR ;
ELSE

UPDATE DB_TRANSDATA
SET POINTSEQ = V_POINTS
WHERE CURRENT OF CL ;

SET V_LNAMEHOLD = V_LNAME ;
SET V_FNAMEHOLD = V_FNAME ;
SET V_DOBHOLD = V_DOB ;
SET V_TRANSNMBRHOLD = V_TRANSNMBR ;
SET FIRSTRECORD = 'N' ;
END IF ;

END LOOP ;

CLOSE CL;

END  ;

You are not checking for an end of cursor condition.

DECLARE END_CL INT DEFAULT 0;
DECLARE CL CURSOR FOR
SELECT LNAME , FNAME , DOB , TRANSNCNMBR , TOPOINTS
FROM DB_TRANSDATA
ORDER BY LNAME ASC , FNAME ASC , DOB ASC , TOPOINTS DESC ;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET END_CL = 1;
FETCH CL INTO V_LNAME , V_FNAME , V_DOB , V_TRANSNMBR , V_POINTS , V_POINTSEQ ;
WHILE END_CL = 0 DO
    . . .
    FETCH CL INTO V_LNAME , V_FNAME , V_DOB , V_TRANSNMBR , V_POINTS , V_POINTSEQ ;
END WHILE;

For more information:

IBM i 7.1 Information Center > Database > Programming > SQL programming > Routines > Stored procedures

IBM Redbooks > Power Systems > Stored Procedures, Triggers, and User-Defined Functions on DB2 Universal Database™ for iSeries


alwayson read only routing if i'm change connection string to applicationintent:readonly then i setup read only routing list in my SQL server 2016 , does that mean my application will route all write request to primary and then read only request to secondary server which i setup as read intent only?

No, it means connections with applicationintent:readonly will be routed to the readable replica. If one of them tries to modify data it will get an error message. Something like below:

  Msg 3906, Level 16, State 2, Line 2

  Failed to update database "XXX" because the database is
  read-only.

Note: if the secondary readable replica is not available the connection will be able to be established to the primary one depending on Connections in primary Role option.


Reporting table size to grafana I am trying to report partition metric to influxdb so that it can be visualised in grafana. I have partitioned my table using pg_partman. I want to report the size of the default table that is created by partman. This is the table where rows that don't fall into any of the existing partition tables are inserted into.
I had 2 approaches in mind

select pg_size_pretty(pg_total_relation_size('<default table name>'))
select count(*) from <default table name>

If I am not wrong 'count(*)' does a full table scan? So it might slow down the query if there are lot of rows being inserted into the default table.
I am not sure of pg_size_pretty(pg_total_relation_size ..) Is that constant time?
Also if there are other ways of doing this I would glad to know about them:)

pg_total_relation_size is definitely the way to go. It gets the size of the involved files from the file system, which is fast and not invasive.

If my monitoring system would regularly run SELECT count(*) on by big tables, I would kill it. It does a sequential scan and uses lots of resources.

For monitoring, I would not use pg_size_pretty. That is good for human readers, but automatic processing will be easier in bytes.


Set or limit number of connections a single pymongo client will open Is there a way to set or limit the number of connections to Mongo a single pymongo client instance will open? I don't want to modify Mongo config to do this, because I don't want to affect other applications.

Use the following parameter to set the number of maximum connections from your pymongo app.

client = MongoClient(host, port, maxPoolSize=10)

Here is documentation with more details.


calculate date difference in monthwise in sqlserver I am using SQL SERVER. I want to calculate the exact date difference in month wise. I know there is one inbuilt function DATEDIFF for this but i want some custom fractional difference like:

sDate(yyyy-MM-dd)  eDate          output              formula
---------------------------------------------------------------------------------------------------------------
2016-03-03        2016-03-05      0.0967741           3.00/31.00 (daycount/lastday of the month)
2016-03-05        2016-06-05      3.0376343          (27.00/31.00)+(30.00/30.00)+(31.00/31.00)+(5.00/30.00)
2016-02-26        2016-04-03      1.2379310          (4.00/29.00)+(31.00/31.00)+(3.00/30.00)

So how this will be calculated? I can apply this logic by using a function but I don't want to do so. How can I apply this logic in a single query?

Your valuable suggestion will be highly appreciated.

With the help of a mini calendar table, this can be handled pretty easily:

rextester setup: http://rextester.com/ZHTTZ86931

Testing data:

create table tmp (fromdate date,thrudate date,monthdiff decimal(19,7),formula varchar(64))
insert into tmp (fromdate, thrudate, monthdiff, formula) values
 ('2016-02-26','2016-04-03',1.2379310,'(4.00/29.00)+(31.00/31.00)+(3.00/30.00)')
,('2016-03-03','2016-03-05',0.0967741,'3.00/31.00 (daycount/lastday of the month)')
,('2016-03-05','2016-06-05',3.0376343
                         ,'(27.00/31.00)+(30.00/30.00)+(31.00/31.00)+(5.00/30.00)')
,('2016-01-01','2016-12-31',12,'');

Creating a mini calendar table to track each date and its percent of its month.

  /* creating a DayPercentOfMonth table */
declare @FromDate date = '20100101';
declare @ThruDate date = '20301231';
create table dbo.DayPercentOfMonth (
    [Date] date not null primary key
  , DayPercentOfMonth decimal(9,8) not null
);
with n as (select n from (values(0),(1),(2),(3),(4),(5),(6),(7),(8),(9)) t(n))
, cal as (
  select
    Date =convert(date,dateadd(day, row_number() over (order by (select 1)) -1, @fromdate))
    , [DayPercentOfMonth] = convert(decimal(6,4),1)
                           /convert(decimal(6,4)
           ,datepart(day,dateadd(day,-1,dateadd(Month,datediff(Month,0
           ,row_number() over (order by (select 1)) -1)+1
           ,0))))
    from         n as deka
      cross join n as hecto
      cross join n as kilo     /* 2.73 years */
      cross join n as [tenK]    /* 27.3 years */
      --cross join n as [hundredk] /* 273  years */
    )
insert into dbo.DayPercentOfMonth ([Date],[DayPercentOfMonth])
select [Date],DayPercentOfMonth
from cal;

Alternate version if you really do not want to create the helper table, just uses the recursive cte:

/* set from & thru dates for calendar cte */
declare @FromDate date = '20160101';
declare @ThruDate date = '20161231';

with n as (select n from (values(0),(1),(2),(3),(4),(5),(6),(7),(8),(9)) t(n))
, cal as (
  select
    Date =convert(date,dateadd(day, row_number() over (order by (select 1)) -1, @fromdate))
    , [DayPercentOfMonth] = convert(decimal(6,4),1)
                           /convert(decimal(6,4)
           ,datepart(day,dateadd(day,-1,dateadd(Month,datediff(Month,0
           ,row_number() over (order by (select 1)) -1)+1
           ,0))))
    from         n as deka
      cross join n as hecto
      cross join n as kilo     /* 2.73 years */
      cross join n as [tenK]    /* 27.3 years */
      --cross join n as [hundredk] /* 273  years */
    )

Then the query just joins the calendar where the calendar date is between the from and thru dates, and sums the DayPercentOfMonth column.

select
    fromdate=convert(varchar(10),t.fromdate,120)
  , thrudate=convert(varchar(10),t.thrudate,120)
  , monthdiff=format(t.monthdiff ,'f8','en-US')
  , SumDayPercentOfMonth=format(convert(decimal(19,8),sum(DayPercentOfMonth)),'f8','en-US')
  --select fromdate, thrudate, monthdiff, SumDayPercentOfMonth=sum(DayPercentOfMonth)
  from tmp as t
    inner join cal c on c.date >= t.fromdate
                    and c.date <= t.thrudate
  group by t.fromdate, t.thrudate, t.monthdiff, t.formula
  order by fromdate

results in:

+------------+------------+-------------+----------------------+
|  fromdate  |  thrudate  |  monthdiff  | SumDayPercentOfMonth |
+------------+------------+-------------+----------------------+
| 2016-01-01 | 2016-12-31 | 12.0000000  | 12.00000000          |
| 2016-02-26 | 2016-04-03 | 1.2379310   | 1.23793103           |
| 2016-03-03 | 2016-03-05 | 0.0967741   | 0.09677419           |
| 2016-03-05 | 2016-06-05 | 3.0376343   | 3.03763441           |
+------------+------------+-------------+----------------------+

note: Sample data values for output were not as precisely correct as they might appear to be.

(27.00/31.00)+(30.00/30.00)+(31.00/31.00)+(5.00/30.00) = 3.037634408602151 != 3.0376343
(3.00/31.00) = 0.0967741935483871 != 0.0967741 -- truncated


Why is my Postgres database working for a while and then not able to “start server” once restarted? Recently, I've started playing around with an old Raspberry Pi 3 b+, and I thought it would be good practice to host a Postgres database on my local network and use it for whatever I want to work through. I understand that running Postgres on a Raspberry Pi with 1GB of memory is not ideal and can take a toll on the SDcard, but I've updated the postgresql.conf file and specified that the data directory path is to utilize a 1TB SSD. Additionally, I've installed zram and log2ram to try and curb some of the overhead on SDcard.
Overview of tech I'm working with:

Raspberry Pi 4 B
Postgres 12
Ubuntu server 20.04 (no gui, only working from terminal)
1TB SSD

Yesterday, I was writing to the Postgres db from a python notebook without any issue, but once I restarted the Raspberry Pi, I was unable to reach the db from DataGrip and would receive the following error from my terminal in Ubuntu:
psql: error: could not connect to server: No such file or directory
    Is the server running locally and accepting
    connections on Unix domain socket "/var/run/postgresql/.s.PGSQL.5432"?

I checked the status of the postgres server and that seemed to be alright...:
● postgresql.service - PostgreSQL RDBMS
     Loaded: loaded (/lib/systemd/system/postgresql.service; enabled; vendor preset: enabled)
     Active: active (exited) since Thu 2021-01-28 13:34:41 UTC; 20min ago
    Process: 1895 ExecStart=/bin/true (code=exited, status=0/SUCCESS)
   Main PID: 1895 (code=exited, status=0/SUCCESS)

Jan 28 13:34:41 ubuntu systemd[1]: Starting PostgreSQL RDBMS...
Jan 28 13:34:41 ubuntu systemd[1]: Finished PostgreSQL RDBMS.

This is what is provided in the postgresql-12-main.log:
2021-01-28 13:17:23.344 UTC [1889] LOG:  starting PostgreSQL 12.5 (Ubuntu 12.5-0ubuntu0.20.04.1) on aarch64-unknown-linux-gnu, compiled by gcc (Ubuntu 9.3.0-17ubuntu1~20.04) 9.3.0, 64-bit
2021-01-28 13:17:23.362 UTC [1889] LOG:  listening on IPv4 address "0.0.0.0", port 5432
2021-01-28 13:17:23.362 UTC [1889] LOG:  listening on IPv6 address "::", port 5432
2021-01-28 13:17:23.365 UTC [1889] LOG:  listening on Unix socket "/var/run/postgresql/.s.PGSQL.5432"
2021-01-28 13:17:23.664 UTC [1899] LOG:  database system was shut down at 2021-01-28 01:43:38 UTC
2021-01-28 13:17:24.619 UTC [1899] LOG:  could not link file "pg_wal/xlogtemp.1899" to "pg_wal/000000010000000000000002": Operation not permitted
2021-01-28 13:17:24.670 UTC [1899] FATAL:  could not open file "pg_wal/000000010000000000000002": No such file or directory
2021-01-28 13:17:24.685 UTC [1889] LOG:  startup process (PID 1899) exited with exit code 1
2021-01-28 13:17:24.686 UTC [1889] LOG:  aborting startup due to startup process failure
2021-01-28 13:17:24.708 UTC [1889] LOG:  database system is shut down
pg_ctl: could not start server
Examine the log output.

I think it could have something to do with permissions. If I try uploading a ~30MB csv to the postgres db, it will crash and the server will not start again because files cannot be written to the pg_wal directory. The only file in that directory is 000000010000000000000001 and does not move on to 000000010000000000000002 etc. while writing to a table.
Please let me know if you have any questions or if you would like for me to include any additional information. I appreciate any pointers you may have for head ahead of time.

Okay, I think I've figured it out. Might be overkill but it works:

First thing I did was format and mount my 1TB SSD. Here is a good video for a walkthrough for formatting to ext4 and mounting. The difference between the video is that I've updated the fstab file to check my SSD during bootup or "0 2" at the end of the SSD mount options instead of "0 0".

Secondly, I installed Postgres. Here is a good walkthrough for that. The directions provided in that blog were more than I needed, but a good walkthrough nonetheless. I simply installed Postgres with:

sudo apt install postgresql postgresql-contrib

Third, I followed this walkthrough until the end of step two, but before beginning step 2, I added a symbolic link from /var/lib/postgresql/12/main to /YOUR/MOUNT/POSITION/postgresql/12/main by executing:

ln -s /var/lib/postgresql/12/main /YOUR/MOUNT/POSITION/postgresql/12/main

Lastly, before restarting the postgres server, I used this website to help me better configure my server. Enter your specs and it should give you some useful configuration settings.


Unable to alter authorization on an mirroring endpoint to a login with a slash in the name I am trying to run the following statement to alter the authorization on an endpoint as the current login used for authorization is about to be deleted (left the company).

alter authorization on endpoint::Mirroring to domain\sys-admin-group

We use Windows authentication, so all our server logins have the backslash "\" character in it.

Here is the error I received:

Msg 102, Level 15, State 1, Line 1
Incorrect syntax near '\'.

I have also tried to enclose the login in single quotes but received the following error:

Msg 102, Level 15, State 1, Line 1
Incorrect syntax near 'domain\sys-admin-group'.

I can't find any useful info on the MSDN ALTER AUTHORIZATION page (http://msdn.microsoft.com/en-us/library/ms187359.aspx). Any ideas?

Try surrounding it with the quoted identifier:

alter authorization
on endpoint::Mirroring
to [domain\sys-admin-group]

Notice the square brackets.


How can I index a text[] field in postgresql? I have two columns:

alternate_names text[]
first_name character varying

I am not sure how I can write an index on this type of query?

SELECT "people".* FROM "people"
WHERE (lower(first_name) = 'Balzaretti' OR 'Balzaretti' ILIKE ANY (alternate_first_names));

You probably can use the citext module to query things case-insensitively.  But I would rather just impose discipline in the system to only store lower case values, and only query lowercase values. Either one would let you get rid of the
ILIKE, which will then let you use a suitable GIN index on the array values.

This does create an annoying syntax issue, as there is no built-in operator to ask if an array contains a scalar, you have to ask if an array contains another array which just happens to hold one element.

create index on people (first_name);
create index on people using GIN (alternate_names);
select * from people where lower('Balzaretti')=first_name or
    ARRAY[lower('Balzaretti')] && alternate_names;

If you don't want to specify the name twice (and avoid the BitmapOr on execution), you could combine first_name into the alternate_names list, either by changing your code to just do that, or by using an expression index:

create index on people using GIN ((alternate_names || ARRAY[first_name]));
select * from people where
    ARRAY[lower('Balzaretti')] && (alternate_names || ARRAY[first_name]);


Sync MySQL database and Quickbooks Database I use Quickbooks as an accounting software. I also have a MySQL database where I record data on orders, customers, inventory, employees and etc. Is there a way for me to sync the two databases? For example, I would like to enter order details on Quickbooks and would like that order details to be in my MySQL database as well. Can someone please advice?

quickbooks have completely different database, its not SQL based
For quick books Enterprise you should have SQL data optimizer and you should be able integrate with MySQL. QuickBooks pro and premium don't have that feature but there are third party applications that might do database optimization.


Disable password policy for partially-contained database I'm stuck in a situation where I need to create a new user for a partially-contained database (SQL Server 2016). The password is short, so I get an error:

Password validation failed. The password does not meet Windows policy requirements because it is too short.

When creating a login at the instance level, there is an option to untick 'Enforce password policy', 'Enforce password expiration', and 'User must change password at next login'. There is no such option when creating a user for a partially-contained DB.
Is there a way to get around this?

Since the Syntax section of the CREATE USER doc doesn't include the CHECK_POLICY option, I assume it's not a possibility for the CREATE USER command.
It seems the only way to achieve it is a workaround. You should create a normal SQL Server login with CHECK_POLICY = OFF and migrate it with sp_migrate_user_to_contained.
CREATE DATABASE Test;
GO

EXECUTE sp_configure 'contained database authentication', 1;
RECONFIGURE;
GO

ALTER DATABASE Test
SET RESTRICTED_USER
WITH ROLLBACK IMMEDIATE;
GO

ALTER DATABASE Test
SET containment=partial;
GO

ALTER DATABASE Test
SET MULTI_USER;
GO

USE Test;
GO

CREATE LOGIN userWeakPWD
WITH PASSWORD='pwd', CHECK_POLICY = OFF;
GO

CREATE USER userWeakPWD
FOR LOGIN userWeakPWD;
GO

EXEC sp_migrate_user_to_contained
    @username = N'userWeakPWD',
    @rename = N'keep_name',
    @disablelogin = N'disable_login';
GO

I couldn't find any doc from Microsoft stating the existing restriction for creating a new contained user with CHECK_POLICY = OFF, but I had no lucky finding one explaining officially how to achieve it too.

Source: CHECK_POLICY = OFF Contained CREATE USER Statement (Credits to Shulei Chen)


How to check for mutiple rows with same data? Can anyone advise how do I check multiple rows with same data?

E.g.

I have 2 rows with following values:

Cola         Colb  Colc
TYPE_APPLE   123   abc
Colb         Colb  Colc
TYPE_APPLE   123   abc

I want to write an SQL query which will not select data of multiple rows which contain the same data as above. How should I write the query?

DISTINCT will combine identical rows.  GROUP BY will combine them and allow you to know which ones had multiples using COUNT(*).  To meet your "not select data of multiple rows" requirement you will need to add a HAVING clause.  Your query will look something like this:

SELECT cola, colb, colc FROM T1 GROUP BY cola, colb, colc HAVING count(*) = 1;

SQL Fiddle Demonstration


Partition By A Column When The Value Isn't Continuous From One Record To The Next How can I Partition By the PreviousScanIndex column when the value isn't continuous from one record to the next (ordered by the RowIndex column), so that I can rank each continuous group of records with the same ID?

E.g. I want to give the group of rows in red the same ID, and then the group of rows in green to have the next ID, and then the group of rows in blue the next ID after that, etc.

Answer

Try this column definition:

, rank() over (partition by RowIndex - PreviousScanIndex order by example.RowIndex) as Testing

Explanation

This assumes - as you've stated in your question - that the rowset is ordered by RowIndex.

The problem breaks down to: how can you get a value that is constant from row to row (so we can partition by that constant) unless the difference in PreviousScanIndex row value is more than 1.

After playing around with windowed functions, partition clauses, lead and lag, it became clear it's not so easy to reference a prior row - especially with the error "Windowed functions cannot be used in the context of another windowed function or aggregate."

... but then the answer became quite simple - if you accept a couple of assumptions.

The key assumption that makes this work is that RowIndex will always increment by 1 and PreviousScanIndex will always increment by 1 also, within the partition you are attempting to define. If both values always increment by 1 within a partition, then one value minus the other will be a constant - until either value increments by some number other than 1 which, in this case, is your PreviousScanIndex jumping up by two.

The other assumption is RowIndex always increments by 1.

In fact, your PreviousScanIndex column could jump up by any number other than 1. Say it jumps up by 3, but then after this it continues to increment by 1, per row - then that is fine. Your jump by 3 simply yields a different answer to the equation RowIndex - PreviousScanIndex, but after that, the result is again the same, row by row, so long as both columns increment only by 1, from row to row.

Full query including your test data

Here is the full SQL with your test data:

select example.PreviousScanIndex, example.OperatorPartitionIndex, example.RowIndex

, rank() over (partition by RowIndex - PreviousScanIndex order by example.RowIndex) as Testing

from (values (1, 2, 1),(2, 3, 2),(3,4,3),(4,5,4),(6,7,5),(7,8,6),(8,9,7),(9,10,8),(11,12,9),(12,13,10),(13,14,11),(14,15,12),(16,17,13),(17,18,14),(18,19,15),(19,20,16),(25,26,17),(26,27,18),(28,29,19),(29,30,20)) as example(PreviousScanIndex, OperatorPartitionIndex, RowIndex) order by RowIndex

Postscript:

Extending the problem:

Order by a column where the value increments unevenly from row to row

We can actually extend this solution.

What if your RowIndex was not guaranteed to increment by exactly 1 from row to row?

Put another way - what if you wanted to sort your rows on some column, like OperatorPartitionIndex, that does not have the same incremental value from row to row?

Well actually you can do it. You simply select your source data in a CTE, then, in a second CTE you select all your source data and rank it based on the column of interest. This creates a new ranking column which does increment by 1, and we are back to the original problem, solved above.

The final step, then, is to select from this second CTE and apply the partition as described above.

Below is a full example. In this case the results will look the same as with the original question - because your OperatorPartitionIndex column is in exactly the same sequence as your RowIndex column. The important thing to note is that in the below solution we are sorting rows on OperatorPartitionIndex - and the values in this column are not evenly incremental - but we can still partition the results as required.

with SourceData as (
    select example.PreviousScanIndex as PreviousScanIndex, example.OperatorPartitionIndex as OperatorPartitionIndex, example.RowIndex as RowIndex
from (values (1, 2, 1),(2, 3, 2),(3,4,3),(4,5,4),(6,7,5),(7,8,6),(8,9,7),(9,10,8),(11,12,9),(12,13,10),(13,14,11),(14,15,12),(16,17,13),(17,18,14),(18,19,15),(19,20,16),(25,26,17),(26,27,18),(28,29,19),(29,30,20)) as example(PreviousScanIndex, OperatorPartitionIndex, RowIndex)
),
ResortedSourceData as (
select
    SourceData.PreviousScanIndex, SourceData.OperatorPartitionIndex, SourceData.RowIndex
    , rank() over (order by OperatorPartitionIndex) as NewRowIndex
from SourceData
)
select ResortedSourceData.PreviousScanIndex, ResortedSourceData.OperatorPartitionIndex, ResortedSourceData.RowIndex, ResortedSourceData.NewRowIndex

, rank() over (partition by ResortedSourceData.NewRowIndex - ResortedSourceData.PreviousScanIndex order by ResortedSourceData.NewRowIndex) as Testing2

from ResortedSourceData order by NewRowIndex

Note: Rows are ordered by NewRowIndex which is defined in the second CTE as:

, rank() over (order by OperatorPartitionIndex) as NewRowIndex

and here you see we've ordered rows by OperatorPartitionIndex, QED.


SQL Query From two Table satisfying a condition Table 1 name: Student
Name      Department
Alex         CSE
Bob          EEE
Briyan       ME
John         CSE
James        ETE
Mike         CE

Table 2 name: Program
Department    Semester
   CSE         Summer
   CSE         Winter
   EEE         Summer
   ETE         Summer
   ME          Winter
   CE          Summer
   CE          Winter

What is the SQL query to print the names and the semester where each student's department have only 1 type of semester?

SQL Query Result should be like this:
Name     Semester
Bob       Summer
Briyan    Winter
James     Summer

Here, CSE and CE have both summer and winter semester. So Alex, John and Mike are not included.

I have tried this far:

SELECT Student.Name,
     Program.Semester
FROM Student
JOIN Program
    ON Program.Department = Student.Department
WHERE ....

You want to run a query like this (see db-fiddle here):

SELECT
  st.student_name,
  -- st.student_dept,  -- <<= uncomment for an insight into
  -- sm.semester_dept, -- <<= how the query works!
  sm.semester_name
FROM student st
JOIN semester sm
ON
  st.student_dept = sm.semester_dept
WHERE st.student_dept IN
(
  SELECT
    sm.semester_dept AS sem_dept
  FROM semester sm
  GROUP BY sm.semester_dept
  HAVING COUNT(sm.semester_dept) = 1
)

Result:

Student name  Semester name
___________________________
         Bob         Summer
      Briyan         Winter
       James         Summer

This query will work (I believe) for any/all of the major RDBMS servers - works on MySQL and (my reference implementation, PostgreSQL here). Oh, yes, just a point to note - the student_name field should be a PRIMARY KEY (or at least UNIQUE with NOT NULL) in order for this to be guaranteed to work properly!

This solution is much more elegant than my original query solution (here) - thanks to @ypercube for the hint!

============== DDL and DML ==================

CREATE TABLE student
(
  student_name VARCHAR (20) NOT NULL,
  student_dept VARCHAR (4) NOT NULL
);

INSERT INTO student VALUES
('Alex',   'CSE'),
('Bob' ,   'EEE'),
('Briyan', 'ME'),
('John',   'CSE'),
('James',  'ETE'),
('Mike',   'CE');

CREATE TABLE semester
(
  department VARCHAR (4) NOT NULL,
  semester   VARCHAR (6) NOT NULL
);

INSERT INTO semester VALUES
('CSE', 'Summer'),
('CSE', 'Winter'),
('EEE', 'Summer'),
('ETE', 'Summer'),
('ME',  'Winter'),
('CE',  'Summer'),
('CE',  'Winter');


How to find unused indexes? I am working on a Data warehouse. I have tables with up to 200M records. Some of these tables have around 20+ indexes (I can't provide a reason why they have been created in the first place). This is making the job of maintaining these indexes too painful and has a direct impact on the DWH import job in both performance and run time.

How can I find the least used indexes on each table? (in order to get rid of them)

Try this script, It has helped me in the past:

-- Unused Index Script
-- Original Author: Pinal Dave
SELECT TOP 25
o.name AS ObjectName
, i.name AS IndexName
, i.index_id AS IndexID
, dm_ius.user_seeks AS UserSeek
, dm_ius.user_scans AS UserScans
, dm_ius.user_lookups AS UserLookups
, dm_ius.user_updates AS UserUpdates
, p.TableRows
, 'DROP INDEX ' + QUOTENAME(i.name)
+ ' ON ' + QUOTENAME(s.name) + '.'
+ QUOTENAME(OBJECT_NAME(dm_ius.OBJECT_ID)) AS 'drop statement'
FROM sys.dm_db_index_usage_stats dm_ius
INNER JOIN sys.indexes i ON i.index_id = dm_ius.index_id
AND dm_ius.OBJECT_ID = i.OBJECT_ID
INNER JOIN sys.objects o ON dm_ius.OBJECT_ID = o.OBJECT_ID
INNER JOIN sys.schemas s ON o.schema_id = s.schema_id
INNER JOIN (SELECT SUM(p.rows) TableRows, p.index_id, p.OBJECT_ID
FROM sys.partitions p GROUP BY p.index_id, p.OBJECT_ID) p
ON p.index_id = dm_ius.index_id AND dm_ius.OBJECT_ID = p.OBJECT_ID
WHERE OBJECTPROPERTY(dm_ius.OBJECT_ID,'IsUserTable') = 1
AND dm_ius.database_id = DB_ID()
AND i.type_desc = 'nonclustered'
AND i.is_primary_key = 0
AND i.is_unique_constraint = 0
ORDER BY (dm_ius.user_seeks + dm_ius.user_scans + dm_ius.user_lookups) ASC

http://blog.sqlauthority.com/2011/01/04/sql-server-2008-unused-index-script-download/


Table with clustered Index consuming max memory causing low PLE alerts For one of my server with below configuration i am continuously receiving alerts for low PLE which ranges between 20- 400 and an average of 90-100 in a day:

Server: SQL server 2008R2

Databases : System and 2 user DB's with max size of DB being 54 GB

RAM: 16 GB

Max server system memory: 12.24 GB

Once i got a hint for memory pressure i used RAMMAP to find memory usage and found private process using 13.8 GB indicating a further doubt on lower memory pressure:

I analysed and found that one of the User DB A was using 8 GB RAM as per the blog from Paul Randal:Additionally i analysed for a period of time and saw  tempdb using at an avg of 3-4 GB of RAM

Then with further investigation i went and found that in that database A there was a table having a clustered index consuming on average 6-6.5 GB of the memory.( Fragmentation is not an issue as its been checked)

Now from here i am unsure how to proceed? should i go and create the missing indexes or can drop the unused? or there is something else i need to dig in before making any conclusion.

Please suggest!

First and foremost thing, SQL Server in picture is SQL Server 2008 R2 RTM which is not supported by Microsoft in any way please apply SQL Server 2008 R2 SP3 to at least get extended support

  I analyzed and found that one of the User DB A was using 8 GB RAM

I don't think there is any issue because of this, this is totally normal looking at this you cannot draw any conclusion.

  A there was a table having a clustered index consuming on average 6-6.5 GB of the memory

I guess this table is biggest table of database which is consuming most part of buffer pool. I cannot upfront say about why table is holding so much memory so again you should first apply SP3 to rule out any possibility of leaks and then move with troubleshooting.

  For one of my server with below configuration i am continuously receiving alerts for low PLE which ranges between 20- 400 and an average of 90-100 in a day:

For a system with 12 G RAM PLE should be around 900. But since you say its always around 300-400 which makes me thing SQL Server has to work hard and move pages frequently out of memory to disk because of memory pressure. To confirm memory pressure you have to rely on other counters not just PLE you can open perfmon and add following counters. It would be best Create a Data Collector Set to Monitor Performance Counters

SQLServer:memory Manager--Target Server Memory: This is amount of memory SQL Server is trying to acquire.
SQLServer:memory Manager--Total Server memory This is current memory SQL Server has acquired.

( Ideally Target value should be less than or equal to Total)
Page reads/sec – Number of physical database page reads that are issued per second. This statistic displays the total number of physical page reads across all databases. Because physical I/O is expensive, you may be able to minimize the cost, either by using a larger data cache, intelligent indexes, and more efficient queries, or by changing the database design
Free Pages – Total number of pages on all free lists (free lists track all of the pages in the buffer pool that are not currently allocate to a data page, and are therefore available for usage immediately). Undoubtedly this value should be high
Page Life Expectancy – Number of seconds a page will stay in the buffer pool without references> if you have NUMA system analyze PLE for each node as mentioned in this article
Free List Stalls/sec – Number of requests per second that had to wait for a free page. Ideally stalls should be as zero or as minimum as possible
SQLServer:Memory Manager--Memory Grants Pending: If you see memory grants pending in buffer pool your server is facing SQL Server memory crunch and increasing memory would be a good idea. For memory grants please read this article: If you see a non zero value of memory grant pending with Low PLE and High free List stalls you definitely have a memory pressure and should consider providing more RAM.

Please note: the value for above counters should be collected for at least 3-4 hours and when load on system is relatively very high. If possible before collecting the values please flush buffer cache , although I am not saying you to do it I know for Prod system it would not be possible. If you would produce the values I could analyze it for you.

I have no idea about workload of the system and just giving you option how to proceed. You should also analyze costly queries. Look for one that involves hash joins and sorts.

A bad query with does lots of joins and scans on large table has all capacity to bring counters like PLE,memory grants, free list stalls to value which make syou believe its memory pressure, ya it is but the cause is poor query and missing indexes and incorrect join. You should also consider this.

Edit:

From The output of performance data collector as requested for above counters

Below are some notable points

Target server memory and total server memory remained constant and there value were equal throughout the data collector process( from 10 AM in morning to 3 PM in afternoon). which points to fact that SQL Server database engine was happy with its current memory requirement
Memory grants pending was always zero.
Free List stalls/Sec was always zero. Which means no request had to wait for free   pages
There was considerable decrease in PLE from 2:15 PM to 3:18 PM and at the same time pages read/sec were very high which makes me believe some query/process started after 2:00 PM which required too much pages in memory and hence causing flurry of activity in Buffer pool.

You need to find out what query is running after 2:00 PM and it was still running so it makes me think there is process.job starting at 2:00 PM. This process/job/query is trying to read too many pages which is causing lazywriter to flush too many pages because query is requesting space for such pages

It can be that such query is missing index, can be that its creating a bad plan due to skewed statistics, it can be that query needs to be written to get/read only subset of data not whole data.

it would be incorrect to say that its a memory pressure, its only that particular process is creating scenario which seems like memory pressure.


Single sql query to get the transpose of a matrix I am facing a problem in writing a query for this question.Consider two random 3x3 (n=3) matrices A and B,having the following schema:

A(i,j,val) and B(i,j,val) where

i int:Row Index

j int:Coloumn Index

val int:Cell Value

Write a single query to get the matrix transpose(A) in the same format as A ie.,output tuples should be of format (i,j,val) where i is row, j is coloumn and val is cell value.

All you need to do is swap the indexes. Original:

with A as
(
  select ceil(rownum/3) as i, mod(rownum -1 , 3) + 1 as j, rownum as val from dual connect by level <= 9
)
select
  *
from
  A
order by
  1, 2
;

  I   J VAL
--- --- ---
  1   1   1
  1   2   2
  1   3   3
  2   1   4
  2   2   5
  2   3   6
  3   1   7
  3   2   8
  3   3   9

Transposed:

with A as
(
  select ceil(rownum/3) as i, mod(rownum -1 , 3) + 1 as j, rownum as val from dual connect by level <= 9
)
select
  j as i,
  i as j,
  val
from
  A
order by
  1, 2
;

  I   J VAL
--- --- ---
  1   1   1
  1   2   4
  1   3   7
  2   1   2
  2   2   5
  2   3   8
  3   1   3
  3   2   6
  3   3   9


Advice on fine-tuning query and index for performance Currently my query is as below:

select id_request,username_request
  from darkhast_follower
 where darkhast_follower.id_request != '9762952594'
   AND darkhast_follower.status =0
   AND NOT exists ( select null
               from log_follow
               where log_follow.other_id = darkhast_follower.id_request
               AND log_follow.id= '9762952594' LIMIT 1)
 LIMIT 1

Table log_follow has 30 milion rows and darkhast_follower has 1000 rows respectively.

log_follow is indexed on column (other_id) and (id), darkhast_follower is indexed on column (id_request) and (status).

The performance seems to be slow and looking for expert advice on fine-tuning the query and index suggestion. Currently I have 40k active users.

-- Server version: 5.7.23-log
-- PHP Version: 5.6.31

SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
SET AUTOCOMMIT = 0;
START TRANSACTION;
SET time_zone = "+00:00";

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8mb4 */;

-- --------------------------------------------------------

--
-- Table structure for table `darkhast_follower`
--

CREATE TABLE `darkhast_follower` (
  `IDD` int(11) NOT NULL,
  `id` varchar(20) NOT NULL,
  `username` varchar(65) NOT NULL,
  `id_request` varchar(20) NOT NULL,
  `username_request` varchar(65) NOT NULL,
  `token_id` text NOT NULL,
  `darkhasti` int(5) NOT NULL,
  `daryafti` int(5) NOT NULL,
  `date` text NOT NULL,
  `status` tinyint(1) NOT NULL,
  `gsm_sender` tinyint(1) NOT NULL,
  `error_report` tinyint(3) NOT NULL,
  `bot` tinyint(1) NOT NULL,
  `back` tinyint(1) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--
-- Indexes for dumped tables
--

--
-- Indexes for table `darkhast_follower`
--
ALTER TABLE `darkhast_follower`
  ADD PRIMARY KEY (`IDD`),
  ADD KEY `status` (`status`),
  ADD KEY `id_request` (`id_request`);

--
-- AUTO_INCREMENT for dumped tables
--

--
-- AUTO_INCREMENT for table `darkhast_follower`
--
ALTER TABLE `darkhast_follower`
  MODIFY `IDD` int(11) NOT NULL AUTO_INCREMENT;
COMMIT;

/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;

SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
SET AUTOCOMMIT = 0;
START TRANSACTION;
SET time_zone = "+00:00";

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8mb4 */;

 -- --------------------------------------------------------

--
-- Table structure for table `log_follow`
--

CREATE TABLE `log_follow` (
  `IDD` int(11) NOT NULL,
  `id` varchar(20) NOT NULL,
  `username` varchar(65) NOT NULL,
  `other_user` varchar(65) NOT NULL,
  `other_id` varchar(20) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--
-- Indexes for dumped tables
--

--
-- Indexes for table `log_follow`
--
ALTER TABLE `log_follow`
  ADD PRIMARY KEY (`IDD`),
  ADD KEY `id` (`id`),
  ADD KEY `other_id` (`other_id`);

--
-- AUTO_INCREMENT for dumped tables
--

--
-- AUTO_INCREMENT for table `log_follow`
--
ALTER TABLE `log_follow`
  MODIFY `IDD` int(11) NOT NULL AUTO_INCREMENT;
COMMIT;

/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;

The query seems ok (although the LIMIT 1 inside the NOT EXISTS subquery is not needed) but you are probably missing some index. I suggest:

add a composite index on log_follow (id, other_id):

ALTER TABLE log_follow
  ADD INDEX id_other_id_idx
    (id, other_id) ;

an index on darkhast_follower (status, id_request) might help as well but with such a small table, probably not much.
the datatype of the various id columns (id, other_id, id_request) are varchar(20) with charset UTF8. If you only have ASCII characters there, it would be better to use Latin charset. This will lower the column width from 60 (20x3) bytes to 20, and thus reduce the index size, both in disk and memory. If you only store integers in these columns, it would be even better to use BIGINT, reducing the size to 8 bytes.