Oracle procedure sometimes runs endlessy, other times finishes instantly The procedure always compiles instantly and I'm pretty sure I don't have any infinite loops or some other bug in there. When running the procedure in SQL Developer it sometimes finishes instantly (as it should, the tables only have like 5 rows each) and other times it never stops running. Does it have to do with the DB or my code? Sorry if the code is messy, this is my first PLSQL procedure. I'm running the procedure at the Oracle DB my university provides so I don't have admin rights.
CREATE OR REPLACE PROCEDURE integrate_data IS
    rowCount NUMBER;
    currId NUMBER;
    CURSOR ws1_customer_crsr IS SELECT * FROM ws1_customer;
    CURSOR ws1_product_crsr IS SELECT * FROM ws1_product;
    CURSOR ws1_order_crsr IS SELECT * FROM ws1_order o JOIN ws1_order_position p ON o.no = p.order_;
    CURSOR ws2_customer_crsr IS SELECT * FROM ws2_user u JOIN ws2_address a ON u.id = a.user_;
    CURSOR ws2_product_crsr IS SELECT * FROM ws2_product;
    CURSOR ws2_order_crsr IS SELECT * FROM ws2_purchase p JOIN ws2_entry e ON p.code = e.purchase;
BEGIN
    SAVEPOINT beforeProcedure;
    DELETE FROM ts_transaction;
    DELETE FROM ts_customer;
    DELETE FROM ts_product;
    /* Insert customer data from ws1 */
    FOR ws1_customer_rec IN ws1_customer_crsr
    LOOP
        INSERT INTO ts_customer VALUES('ws1_' || to_char(ws1_customer_rec.id), ws1_customer_rec.email, ws1_customer_rec.address);
    END LOOP;
    /* Insert product data from ws1 */
    FOR ws1_product_rec IN ws1_product_crsr
    LOOP
        INSERT INTO ts_product VALUES('ws1_' || to_char(ws1_product_rec.id), ws1_product_rec.name,
            ws1_product_rec.description, ws1_product_rec.price);
    END LOOP;
    /* Insert order data from ws1 */
    FOR ws1_order_rec IN ws1_order_crsr
    LOOP
        INSERT INTO ts_transaction VALUES('ws1_' || to_char(ws1_order_rec.no) || '_' || to_char(ws1_order_rec.pos_no), ws1_order_rec.date_,
            ws1_order_rec.quantity, 'ws1_' || to_char(ws1_order_rec.customer), 'ws1_' || to_char(ws1_order_rec.product));
    END LOOP;
    /* Insert customer data from ws2, if a customer has multiple addresses only the first one gets added */
    FOR ws2_user_rec IN ws2_customer_crsr
    LOOP
        currId := ws2_user_rec.user_;
        SELECT CASE
            WHEN EXISTS(SELECT * FROM ts_customer c WHERE c.id = 'ws2_' || currId) THEN 1
            ELSE 0
        END
        INTO rowCount
        FROM DUAL;
        IF rowCount = 0 THEN
            INSERT INTO ts_customer VALUES('ws2_' || to_char(ws2_user_rec.user_), ws2_user_rec.email, ws2_user_rec.street || ' ' || to_char(ws2_user_rec.street_no) ||
                ', ' || to_char(ws2_user_rec.postal_code) || ' ' || ws2_user_rec.city);
        END IF;
    END LOOP;
    /* Insert product data from ws2 */
    FOR ws2_product_rec IN ws2_product_crsr
    LOOP
        INSERT INTO ts_product VALUES('ws2_' || to_char(ws2_product_rec.ean), ws2_product_rec.title, ws2_product_rec.description, ws2_product_rec.sales_price);
    END LOOP;
    /* Insert order data from ws2 */
    FOR ws2_purchase_rec IN ws2_order_crsr
    LOOP
        INSERT INTO ts_transaction VALUES('ws2_' || to_char(ws2_purchase_rec.code) || '_' || to_char(ws2_purchase_rec.entry_no),
            ws2_purchase_rec.date_, ws2_purchase_rec.qty, 'ws2_' || to_char(ws2_purchase_rec.user_), 'ws2_' || to_char(ws2_purchase_rec.product));
    END LOOP;
    EXCEPTION
        WHEN OTHERS THEN
        dbms_output.put_line('An error has occurred, all work the procedure has done has been rolled backed');
        ROLLBACK TO beforeProcedure;
END;
/

There is no commit in your code. You are likely blocking yourself if you run this twice from different concurrent sessions.
Add a commit at the end:
    /* Insert order data from ws2 */
    FOR ws2_purchase_rec IN ws2_order_crsr
    LOOP
        INSERT INTO ts_transaction VALUES('ws2_' || to_char(ws2_purchase_rec.code) || '_' || to_char(ws2_purchase_rec.entry_no),
            ws2_purchase_rec.date_, ws2_purchase_rec.qty, 'ws2_' || to_char(ws2_purchase_rec.user_), 'ws2_' || to_char(ws2_purchase_rec.product));
    END LOOP;

    COMMIT;

    EXCEPTION
        WHEN OTHERS THEN
        dbms_output.put_line('An error has occurred, all work the procedure has done has been rolled backed');
        ROLLBACK TO beforeProcedure;
END;
/


Innodb issue is preventing MySQL from starting For some reason mysqld refused to start after I rebooted my server. Now when I try to start it I will get the following error:

  [root@vps1 ~]# service mysqld restart  ERROR! MySQL server PID file
  could not be found! Starting MySQL. ERROR! The server quit without
  updating PID file (/var/lib/mysql/vps1.xxx.eu.pid).

When  I checked the mysql error log I found the following

140923 05:23:02 mysqld_safe mysqld from pid file /var/lib/mysql/vps1.xxx.eu.pid ended
140923 05:23:07 mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql
2014-09-23 05:23:08 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2014-09-23 05:23:08 4024 [Note] Plugin 'FEDERATED' is disabled.
2014-09-23 05:23:08 4024 [Note] InnoDB: Using atomics to ref count buffer pool pages
2014-09-23 05:23:08 4024 [Note] InnoDB: The InnoDB memory heap is disabled
2014-09-23 05:23:08 4024 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins
2014-09-23 05:23:08 4024 [Note] InnoDB: Memory barrier is not used
2014-09-23 05:23:08 4024 [Note] InnoDB: Compressed tables use zlib 1.2.3
2014-09-23 05:23:08 4024 [Note] InnoDB: Using Linux native AIO
2014-09-23 05:23:08 4024 [Note] InnoDB: Using CPU crc32 instructions
2014-09-23 05:23:08 4024 [Note] InnoDB: Initializing buffer pool, size = 128.0M
2014-09-23 05:23:08 4024 [Note] InnoDB: Completed initialization of buffer pool
2014-09-23 05:23:08 4024 [Note] InnoDB: Highest supported file format is Barracuda.
2014-09-23 05:23:08 4024 [Note] InnoDB: The log sequence numbers 0 and 0 in ibdata files do not match the log sequence number 63528574 in the ib_logfiles!
2014-09-23 05:23:08 4024 [Note] InnoDB: Database was not shutdown normally!
2014-09-23 05:23:08 4024 [Note] InnoDB: Starting crash recovery.
2014-09-23 05:23:08 4024 [Note] InnoDB: Reading tablespace information from the .ibd files...
2014-09-23 05:23:08 4024 [ERROR] InnoDB: Attempted to open a previously opened tablespace. Previous tablespace mysql/slave_worker_info uses space ID: 5 at filepath: ./mysql/slave_worker_info.ibd. Cannot open tablespace datumklok_web/wp3_postmeta which uses space ID: 5 at filepath: ./datumklok_web/wp3_postmeta.ibd
2014-09-23 05:23:08 7f667c224720  InnoDB: Operating system error number 2 in a file operation.
InnoDB: The error means the system cannot find the path specified.
InnoDB: If you are installing InnoDB, remember that you must create
InnoDB: directories yourself, InnoDB does not create them.
InnoDB: Error: could not open single-table tablespace file ./datumklok_web/wp3_postmeta.ibd
InnoDB: We do not continue the crash recovery, because the table may become
InnoDB: corrupt if we cannot apply the log records in the InnoDB log to it.
InnoDB: To fix the problem and start mysqld:
InnoDB: 1) If there is a permission problem in the file and mysqld cannot
InnoDB: open the file, you should modify the permissions.
InnoDB: 2) If the table is not needed, or you can restore it from a backup,
InnoDB: then you can remove the .ibd file, and InnoDB will do a normal
InnoDB: crash recovery and ignore that table.
InnoDB: 3) If the file system or the disk is broken, and you cannot remove
InnoDB: the .ibd file, you can set innodb_force_recovery > 0 in my.cnf
InnoDB: and force InnoDB to continue crash recovery here.
140923 05:23:08 mysqld_safe mysqld from pid file /var/lib/mysql/vps1.xxx.eu.pid ended

Now unfortunately because of this I have no acces to the mysql console.

I did create user backups through Directadmin daily BUT if I delete all mysql content I assume DirectAdmin itself will no longer be functional. (Which I found out when fixing Roundcube :P).

So I really have no clue on how to fix this. Anyone out there some experience with these issues?

MySQL can not open file ./datumklok_web/wp3_postmeta.ibd . Check if it exists, but most probably it doesn't. It might have gone after file system check. If so, restore the database from backup. If you don't have them - mount MySQL partition read-only or take image from the partition.


Postgresql. Real-time copy of a database to another disk on the same server How to make a real-time copy of the database to another disk?

My database is very small, but WAL-logs are very large (many changes in time). I don't need 'Point-in-Time Recovery', I just need the latest version of the database. How to force a sql server to maintain an additional copy of the database to another disk in case the first disk lost data (hardware failure)?

If the first database "loses data", it's liable to tell the second database to "lose data to match me". (Depends on exactly what you mean by "lose data".) So reduce the chances of your database losing data due to a single disk failure by storing the database on an array: RAID 1, RAID 5, RAID 6, or RAID 10. I recommend RAID 6.

It's not clear whether you've read the docs for PostgreSQL's High Availability, Load Balancing, and Replication. Streaming replication is probably worth looking at.

To mitigate the risk of "losing data", you need to be precise about losing data means. Mitigating the risk of disk failure is different than mitigating the risk of an application deleting data by accident.


Missing Encryption Key when migrating Reporting Services from SQL Server 2008 to SQL Server 2016 Owing to a ransomware attack on our production server Reporting Services went down. This server was installed few years ago and its been in production for long. We have quite a few reports on this server.

It was decided we build a new server and migrate the ReportServer Database. Unfortunately we don't have the Encryption Key. Because the service is down I can't even take the back up now, its too late.

I tried backing up the ReportServer and ReportServerTempDB databases and then restoring those to the new servers. Restore went well, I even changed the database from the reporting services configuration manager.

But, When I tried to go to the web portal URL, It shows the error , The report server isn't configured properly. Check the report server trace log for details. I found out that logs shows the scale out deployment errors. I believe since I don't have the encryption key file and password I am receiving this error.

What can I do to get reporting services up and running...?

What should be my approach..? Can I restore the encryption key some how or create a new one with rskeymgmt..? If not then can I delete it (if it is possible) and try to reconfigure all the reports again..? (There are about 350 different reports on this server).

Please help.

Unfortunately we don't have the Encryption Key.

This is the problem, and you already know what I'm going to say next. I hope your 350 reports were in a source control system.


Replication with encrypted columns, does the 2nd server have to have the same master key and symmetric key or is it done automatically? I have two questions actually:

If you set up a table with encrypted columns and replicate it to another server does the other server have to have the same master key and symmetric key or is it replicated automatically?

And where replication activity is logged? - Is that inside the windows event viewer?

With ordinary column encryption (not AlwaysEncrypted), the columns are simple varbinary columns, with no special configuration or metadata.  So replication of the cyphertext "just works".

To decrypt on the subscriber you would need the symmetric key, either by Creating Identical Symmetric Keys on Two Servers, or by restoring a backup of the publisher database containing the symmetric key before initializing the subscription (and restoring the Certificate or Master Key that encrypts the symmetric key).


What's the best way to structure logging application data in MySQL I may be overthinking this but:

I have an API; I want to log calls to the API for stat/debugging purposes. Ideally the speed of reads should be quick but is not essential (as will primarily be read by me). However, the speed of writes should be good as I don't want to slow the API down.

We'll be starting off from scratch, but the API (and logging) will last years. The older the data gets, the less useful it is. At some point we might want to drop the old data entirely (say, dropping data older than three years).

Running MySql 5.5 at the moment.

What's the best way to structure the table? Partition it by year and just make up a load of future partitions?

PARTITION BY RANGE( YEAR(date) ) (
    PARTITION p0 VALUES LESS THAN (2016),
    PARTITION p1 VALUES LESS THAN (2017),
    PARTITION p2 VALUES LESS THAN (2018),
    PARTITION p3 VALUES LESS THAN (2019),
    PARTITION p4 VALUES LESS THAN MAXVALUE
);

Or is there a way to create automatic rolling partitions?

Or do I just index on year?

Yes, there is. I would change the partition names to something more meaningful.
Let say the table look like this in the mydb database:
CREATE TABLE mytable
(
    id INT NOT NULL AUTO_INCREMENT,
    date DATETIME,
    PRIMARY KEY (id,date),
    KEY (date)
)
PARTITION BY RANGE( YEAR(date) ) (
    PARTITION p2015 VALUES LESS THAN (2016),
    PARTITION p2016 VALUES LESS THAN (2017),
    PARTITION p2017 VALUES LESS THAN (2018),
    PARTITION p2018 VALUES LESS THAN (2019),
    PARTITION p9999 VALUES LESS THAN MAXVALUE
);

To add a partition with the year 2019
ALTER TABLE mytable DROP PARTITION p9999;
ALTER TABLE mytable ADD PARTITION
    (PARTITION p2019 VALUES LESS THAN (2020)),
    (PARTITION p9999 VALUES LESS THAN MAXVALUE)
;

To drop the oldest partition
ALTER TABLE mytable DROP PARTITION p2016;

Want a Stored Procedure to do the following ?

Drop partitions before this year
Append new partition 4 years into the future

Here is the code
DELIMITER $$

DROP PROCEDURE IF EXISTS `mydb`.`sp_mytable_Rotate` $$
CREATE DEFINER=`root`@`localhost` PROCEDURE `mydb`.`sp_mytable_Rotate` ()
This_Stored_Procedure:BEGIN

  SET @year0 = YEAR(NOW());
  SET @year4 = @year0 + 4;
  SET @year5 = @year0 + 5;
  SET @OldPartitionToKeep = CONCAT('p',@year0);
  SET @NewPartitionToMake = CONCAT('p',@year4);

  SELECT COUNT(1) INTO @zapcount FROM information_schema.partitions
  WHERE table_schema = DATABASE() AND table_name = 'mytable'
  AND partition_name < @OldPartitionToKeep;

  IF @zapcount = 0 THEN LEAVE This_Stored_Procedure; END IF;

  SELECT GROUP_CONCAT(partition_name) INTO @partitions_to_drop
  FROM information_schema.partitions  WHERE table_schema = DATABASE()
  AND table_name = 'mytable'  AND partition_name < @OldPartitionToKeep;
  SET @partitions_to_drop= CONCAT(@partitions_to_drop,',p9999');

  SET @sql = CONCAT('ALTER TABLE mytable DROP PARTITION ',@partitions_to_drop);
  PREPARE s FROM @sql; EXECUTE s; DEALLOCATE PREPARE s;

  SET @sql = 'ALTER TABLE mytable ADD PARTITION ';
  SET @sql = CONCAT(@sql,'(PARTITION ',@NewPartitionToMake,' VALUES LESS THAN (',@year5,'))');
  PREPARE s FROM @sql; EXECUTE s; DEALLOCATE PREPARE s;

  SET @sql = 'ALTER TABLE mytable ADD PARTITION (PARTITION p9999 VALUES LESS THAN MAXVALUE)');
  PREPARE s FROM @sql; EXECUTE s; DEALLOCATE PREPARE s;

END $$

DELIMITER ;

From here, just call it
USE mydb
CALL sp_mytable_Rotate();

On January 1, you could backup the old partition's data using mysqldump before rotating like this:
MYSQL_USER=root
MYSQL_PASS=rootpassword
MYSQL_CONN="-u${MYSQL_USER} -p${MYSQL_PASS}"
THISYEAR=`date +%Y`
(( LASTYEAR = THISYEAR - 1 ))
BACKUP_FILE=Data_From_${LASTYEAR}.sql
BACKUP_GZIP=${BACKUP_FILE}.gz
mysqldump ${MYSQL_CONN} mydb mytable --where="date<'%{THISYEAR}-01-01'"| gzip > ${BACKUP_GZIP}
mysql ${MYSQL_CONN} -Dmydb -ANe"CALL sp_mytable_Rotate()"

Based on Vérace's suggestion, if you want to backup the data into a table, an ARCHIVE table can compress the data and have no additional overhead for indexes.
MYSQL_USER=root
MYSQL_PASS=rootpassword
MYSQL_CONN="-u${MYSQL_USER} -p${MYSQL_PASS}"
THISYEAR=`date +%Y`
(( LASTYEAR = THISYEAR - 1 ))
BACKUP_TABLE=mydata_${LASTYEAR}
MKDT="MAKEDATE(${THISYEAR},1)"
SQL="CREATE TABLE ${BACKUP_TABLE} ENGINE=ARCHIVE SELECT * FROM mytable WHERE 0=1;"
SQL="${SQL} INSERT INTO ${BACKUP_TABLE} SELECT * FROM mytable WHERE date < ${MKDT}"
SQL="${SQL} CALL sp_mytable_Rotate();"
mysql ${MYSQL_CONN} -Dmydb -ANe"${SQL}"

You can then move .frm and .ARZ files of the ARCHIVE table to a designated backup volume.
GIVE IT A TRY !!!


What is the benefit of using BOOLEAN over TINYINT(1)? From MySQL manual, it says:

  BOOL, BOOLEAN

  These types are synonyms for TINYINT(1). A value of zero is considered
  false. Nonzero values are considered true:

I created a BOOLEAN column with 0 as the default value. Then I update the value to 2. Logically, I would expect MySQL to accept either 0 or 1 since it is a boolean. However, MySQL did not issue an error or prevent me from performing the update.

If BOOLEAN works exactly the same as TINYINT(1), does it make any difference whether I use TINYINT(1) or BOOLEAN?

Those two are really synonyms, so you can use them interchangeably. You won't see any difference between them.

If you want to allow 0 and 1 only, you can still use the bit type.


How do I fix an Orphaned User in Log Shipped version of a SQL Server 2008 database Forgive my greenness.. but would appreciate some help on this...

We are doing development work on an instance of SQL Server 2008 and have multiple databases on that server that are core to our application along with a particular user that in some cases owns database, and in some cases can just run queries against them.

There is a particular database that resides on a production server that we don't want to touch, so we've set up log shipping to replicate its data to our development box. That database is properly restored on our development box and receiving ongoing updates.

On the production box, we added the same user that's need on our development box to the database that's doing the log shipping

When we try to query the log shipped version of database (read-only) from our application on the development box, our user can't access the database under the "current security context."

I ran a stored procedure that verified that my user is indeed orphaned...

I've run into this scenario before when restoring databases to a different machine, and have run a stored procedure to fix the "orphaned" user; however, because the development box's version of the database is read-only the stored procedure won't work.

Any suggestions on how to deal with this?

If you are running sp_change_users_login 'Report' to get that list of orphaned users it will return the SID expected.

Just take that SID and create your login with the same SID:

CREATE LOGIN [mylogin] WITH PASSWORD='', SID = <SID for orphaned user>

This should allow your login to be mapped to that user in the database and allow you to access the database.

If you want to copy a login from production to your development box you can query sys.syslogins to get the SID, or just use sp_help_revlogin procedure from KB918992.


MySQL import and export data without update on insert I have a table that updates a timestamp column on every insert. This timestamp is indexed uniquely with a few other columns and I am unable to import this table. I guess the mysql dump/import inserts each row and some of the timestamps overlap because they are updated to the current timestamp. Is there a way to import and ignore the on update: current_timestamp() function that I have?

A unique timestamp is a mistake waiting to happen.

If there is also an AUTO_INCREMENT id, you can do this:

PRIMARY KEY(timestamp, id),
INDEX(id)

Then dups are not possible.


How do I show a running total from the previous records? I wonder if someone can please assist.
I have the following code which basically inserts 10 records into a table. From this table I'm trying to show a running total for the amount of sales against the seller. I've tried using the LAG function but that does not appear to work.
Here is the code.

CREATE TABLE Sales
(
    OrderId Int NOT NULL,
    Seller  varchar(10) NULL,
    Amount  money NULL
)

INSERT INTO Sales VALUES
    (1, 'Alex', 10.00),
    (2, 'Sarah', 15.00),
    (3, 'Tracy', 10.25),
    (4, 'Pete', 10.25),
    (5, 'Tracy', 11.00),
    (6, 'Tracy', 10.25),
    (7, 'Alex', 10.25),
    (8, 'Jane', 10.25),
    (9, 'Alex', 20.55)

SELECT Seller, Amount, LAG(Amount,1) OVER (PARTITION BY Seller ORDER BY Seller) AS RunningTotal FROM Sales

Essentially in this example I would expect the following output
Seller|Amount|Running Total
Alex|10.00|10.00
Alex|1.25|20.25
Alex|20.55|40.90
Jane|10.25|10.25
Pete|10.25|10.25
Sarah|15.00|15.00
Tracy|10.25|10.25
Tracy|11.00|21.25
Tracy|10.25|31.50

Hope someone can help and thanks in advance.
Dave

I am guessing you have an sql server, but it should alspo work on other rdms that support window functions

SELECT
Seller
, Amount
, SUM(Amount) OVER (PARTITION BY Seller ORDER BY Seller ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS RunningTotal
FROM Sales
GO

Seller |  Amount | RunningTotal
:----- | ------: | -----------:
Alex   | 10.0000 |      10.0000
Alex   | 10.2500 |      20.2500
Alex   | 20.5500 |      40.8000
Jane   | 10.2500 |      10.2500
Pete   | 10.2500 |      10.2500
Sarah  | 15.0000 |      15.0000
Tracy  | 10.2500 |      10.2500
Tracy  | 11.0000 |      21.2500
Tracy  | 10.2500 |      31.5000

db<>fiddle here


How do I stop a query from running if the row is locked? I have a section of code that locks the database, updates it and then confirms.

This is all working fine, if another user attempts to update the same row they cannot and their changes are discarded.

My problem is that there is a variable that is updated each time the query is run, this increases whether the query was successful or not.  So what I need is the query NOT to run in the first place if the row is locked, how do I do this?

James

you can use GET_LOCK() to avoid the query to run if other query is running on the same lock name.

you will basically do:

SELECT IS_FREE_LOCK('maintenance');
#IF RESULT IS 1
SELECT GET_LOCK('maintenance', '10');#it will get a lock named maintenance
#PERFORM ALL YOUR MAINTENANCE
SELECT RELEASE_LOCK('maintenance'); #it will release a lock named maintence

what will happen is that the thread 1 will get a lock named maintenance, while this thread don't release this lock, the command SELECT IS_FREE_LOCK('maintenance'); won't return 1.


One-to-many dictionary (lookup) table vs varchar vs enum? Imagine we have orders table, and an order has a status. Which of these three options is the best?

Use varchar for status column
Use enum for status column
Use separate status table, which has status_id int and name varchar, and in orders table keep status_id as a foreign key.

Which is the best approach? I suspect that using dictionary table is also better because it'd be faster to search statuses by int, instead of varchar.

Well, I am an advocate of ENUM -- at least in limited use.

I would use it for status with a small, reasonably static, list of possible values.  I would start with unknown to catch things that are typos.  ALTER TABLE has long been optimized to add a new option on the end of an ENUM list.

I would not use ENUM for continents.  If there are standard abbreviations, I would use a short VARCHAR for such.  For countries, I advocate

country CHAR(2) CHARACTER SET ascii

Using a lookup table can be very bad for efficiency.  A "star" schema in Data Warehousing can be terribly inefficient.  This happens when multiple JOINs are needed; ENUM avoids the inefficiency.  VARCHAR is bulky, which is a big issue in DW applications.

An ENUM acts like a string in many situations:  WHERE status = 'OK' is more readable than either of the alternatives.

Comparing two ints versus two strings: Ints is not enough faster to matter.

VARCHAR -- Use when bulkiness is not a problem
ENUM -- Use when bulkiness is a problem, and the number of choices is quite small and stable.
Dictionary lookup -- Use when multiple tables need to reference the same set of stuff, and/or when there is extra info in the Dictionary.


What's the best way to concatenate a multi-column foreign key? We are expanding our data warehouse to include a new source system. In the new system, we want a single column for a primary key, but in the old system it's two columns.

For exmaple:

create table #new_case (case_id varchar(255))

create table #old_case (patient_id int, care_event_counter int)

insert into #old_case (patient_id, Care_event_counter)
values
(55, 1),
(55, 2),
(55, 3),
(56, 1),
(57, 1),
(57, 2)

insert into #new_case (case_id)
select cast(patient_id as varchar(55)) + '_' + cast(care_event_counter as varchar(55))
from #old_case

select * from #new_case

problem: This has got to be super slow on a large scale, and I'd prefer to use int across the board.

Question: How do you merge two integer columns intelligently? is there a math trick you could use to derive the values that would be faster?

It's quite common in Data Warehouse schema designs to assign a new, integer, single-column key to every table, and retain the source-system key (sometimes called the "business key") as well.

Kimball says:

  Natural keys created by operational source systems are subject to
  business rules outside the control of the DW/BI system. For instance,
  an employee number (natural key) may be changed if the employee
  resigns and then is rehired. When the data warehouse wants to have a
  single key for that employee, a new durable key must be created that
  is persistent and does not change in this situation. This key is
  sometimes referred to as a durable supernatural key. The best durable
  keys have a format that is independent of the original business
  process and thus should be simple integers assigned in sequence
  beginning with 1. While multiple surrogate keys may be associated with
  an employee over time as their proﬁle changes, the durable key never
  changes.

Natural, Durable, and Supernatural Keys

So something like:

create table new_case
(
  case_id int identity primary key,
  patient_id int not null,
  care_event_counter int not null,
  constraint uk_case unique(patient_id,care_event_counter),
  ...
)

You need to retain the other key and have it indexed because with new data comes in with the business key, you need to associate it with the new warehouse ID.


Can NOLOCK in a stored procedure be overridden when executing it? Our application interfaces with a partner's application by calling one of their stored procedures.  They insist on sprinkling NOLOCK hints throughout their code and this is a long stored procedure.  We are intermittently getting bad data and suspect a race condition.

Is there any way, short of manually changing their stored procedure, that we can override their NOLOCK hints when executing it?

Note -- our code making the stored procedure call is a .NET application, in case that gives us any other options.

No, query-level hints and options override session and server-level settings. Basically, a hint applied at the query level is the lowest common denominator, and wins (except in the case of MAXDOP and Resource Governor, but that's slightly different than what we're talking about here anyway).

This used to be documented for SQL Server 2000, but this verbiage has been removed from more modern versions of the documentation.


Efficiency of Scalar UDF vs TVF I am trying to optimize the rollup code for my company and ran into a very peculiar issue. I converted many Scalar functions to be TVFs and they all seem to run more quickly than the original, which is great. However, in the queries that call them, they end up running significantly slower than the original. Here is a basic outline of my update:

SELECT col1, ..., colx,
    (CASE WHEN x <= 0 OR y <= 0 OR z <= 0 OR z = x
          THEN output
        WHEN valX <= 0
          THEN output
        WHEN minimum.min < 1.0 THEN 1.0
        ELSE minimum.min
        END) AS Q,
FROM Tbl1...tblx (series of inner joins)
CROSS APPLY dbo.inlinemin(val1, val2) AS minimum

This is a basic outline of the original:

SELECT col1, ..., colx,
    (CASE WHEN x <= 0 OR y <= 0 OR z <= 0 OR z = x
          THEN output
        WHEN valX <= 0
          THEN output
        ELSE maximum(minimum(val1,val2),1.0)
        END) AS Q,
FROM Tbl1...tblx (series of inner joins)

The numbers are more or less the same, as is the logic. The only difference is my function 'inlineMin' is a TVF as opposed to 'maximum' and 'minimum' the original Scalar functions. These functions are exceedingly simple and just return the max or min between the two passed parameters. Even the execution plan is more or less the same. There is a change from merge join to hash match at one point, however, the cost of this difference is minimal and could not account for the drastic change in elapsed time and cpu time.

When I run the functions outside of the rollup query my function is faster than the original for large sets of data. This makes sense given how TVFs work in comparison to scalar UDFs. However, when I call them in the query, my updated version runs roughly 6x slower. The cross apply is (seemingly) not the issue since leaving the cross apply and simply using the old functions

SELECT ...
    ELSE maximum(minimum(val1,val2),1.0)
    END) AS Q,
FROM Tbl1...tblx (series of inner joins)
CROSS APPLY inlinemin(val1, val2) AS NotUsedHere

Is roughly as efficient as the original code. It is only when I include the output of my function in the select that the query becomes significantly slower.

As I understand, the function is called and runs at the cross apply, meaning that it should be calculating a value even if it is not in the select, so why would it be faster not to include it in the select? Further, if the above is false, why would my function itself be faster but run significantly slower when used inside of a query?

Edit:

Here is the Inline TVF that I have written to replace the original

CREATE FUNCTION [dbo].[InlineMin](@val1 FLOAT, @val2 FLOAT)
RETURNS TABLE WITH SCHEMABINDING
AS
RETURN
    SELECT minVal =
    CASE    WHEN @val1 < @val2
            THEN @val1
    ELSE
            ISNULL(@val2,@val1)
END

Here is the anonymized query plan for my rewrite:
https://www.brentozar.com/pastetheplan/?id=ryKR_Q0Em

and the anonymized query plan for the original:
https://www.brentozar.com/pastetheplan/?id=SJsS1-A4X

As I understand, the function is called and runs at the cross apply, meaning that it should be calculating a value even if it is not in the select, so why would it be faster not to include it in the select?

The optimizer is very good at removing subtrees that compute expressions that are not needed in the final result (top-level projection). When you remove the value from the select list, the work needed to compute that value is simply not done.

  Further, if the above is false, why would my function itself be faster but run significantly slower when used inside of a query?

This is difficult to assess in detail from an anonymized plan. Nevertheless, removing the scalar T-SQL functions allows the optimizer to consider parallel plans. You might like to test your rewritten query with an OPTION (MAXDOP 1) query hint to see how the serial plan selected compares with your original.

Parallel plans are not always better (though they are only selected if they appear to be lower cost to the optimizer). Your case has a relatively low-cost, so the optimizer does not believe it is worth exploring a tremendous number of alternatives. There are cases where the time spent considering both serial and parallel plans is counter-productive to final plan quality.

I do apologise if this is a little vague, but anonymized plans really do make it tough to be specific. All things being equal, in-line functions will currently out-perform scalar functions. Sadly, all things are rarely equal.


What would be an efficient way of preventing sequential scan if data is partitioned by list, when it comes to updating multiple partitions? What would be an efficient way of preventing sequential scan if data is partitioned by list, when it comes to updating multiple partitions with a condition that is not referring to the partition key?
Pseudo-schema:
My table contains
ID, GroupName, IndividualName, SomeComputedValue, SomeType, UpdatedAt

the partition key is GroupName.
IndividualName is a search key.
SomeComputedValue is the value that will be updated across different groups.
SomeType is an additional search parameter

I know Searching via GroupName would be faster, but what if my data end up requiring to search for IndividualName plus SomeType?
Is there anything wrong if I end up requiring to update SomeComputedValue across 10 to 100 partitions, with 1 to 10 million rows each?
Should an index be used or avoided?
My search would be something like IndividualName, SomeType in the update statement. But after that most of the time it will be a GroupName search for select and no more update.
I feel like the index would be more detrimental.

If the index would only be used for a single UPDATE and never again after that, it is probably a good idea not to create it.
The UPDATE will then run for a long time, but if that is a one-time occurrence and you can make sure that there are no other big data modifications going on at the same time (which could lead to deadlocks or table bloat), that should be acceptable.
After all, creating an index also takes a long time.


PostgreSQL Partitioning - Constraint exclusion not working I have a table with about 9 million rows where i'm keeping customer's id, a timestamp, and a cost value for the particular date-time.

It looks like this:

customer|t|consumption

I need to split my dataset into smaller tables which will inherit from the table master.

Master table:

CREATE TABLE my_table_master (
 customer TEXT NOT NULL,
 t TIMESTAMP NOT NULL,
 consumption INTEGER NOT NULL
);

Child tables:

CREATE TABLE my_table_2 (
CHECK ( t='2013-07-01 01:00:00')
) INHERITS (my_table_master);

CREATE TABLE my_table_3 (
CHECK ( my_extract(t)=0 )
) INHERITS (my_table_master);

CREATE TABLE my_table_4 (
CHECK ( t <> '2013-07-01 01:00:00' AND my_extract(t) <> 0 )
) INHERITS (my_table_master);

The problem

Constraint exclusion is not working here. Note that i have already execute the command:

SET constraint_exclusion = on;

If for example i run the following query, the planner includes my_table_2 and my_table_3 instead of just my_table_2.

SELECT * FROM my_table_master WHERE t='2013-07-01 01:00:00';

Query Plan:

Append  (cost=0.00..24601.55 rows=967 width=24) (actual time=164.514..164.693 rows=993 loops=1)
->  Seq Scan on public.my_table_master  (cost=0.00..0.00 rows=1 width=44) (actual time=0.010..0.010 rows=0 loops=1)
 Output: my_table_master.customer, my_table_master.t, my_table_master.consumption
 Filter: (my_table_master.t = '2013-07-01 01:00:00'::timestamp without time zone)
 ->  Seq Scan on public.my_table_3  (cost=0.00..24577.43 rows=960 width=24) (actual time=164.472..164.472 rows=0 loops=1)
 Output: my_table_3.customer, my_table_3.t, my_table_3.consumption
 Filter: (my_table_3.t = '2013-07-01 01:00:00'::timestamp without time zone)
 Rows Removed by Filter: 1237954
 ->  Seq Scan on public.my_table_2  (cost=0.00..24.13 rows=6 width=44) (actual time=0.029..0.165 rows=993 loops=1)
 Output: my_table_2.customer, my_table_2.t, my_table_2.consumption
 Filter: (my_table_2.t = '2013-07-01 01:00:00'::timestamp without time zone)
 Planning time: 0.298 ms
 Execution time: 164.731 ms

EDIT:

If i'm only asking two specific queries on the DB that include the following WHERE clauses respectively:

WHERE t='2013-07-01 01:00:00'
WHERE my_extract(t)=0

do you think i should possibly split my dataset in a different way?
If yes, please let me know.

On closer inspection... PostgreSQL is actually functioning as expected:

my_table_master is scanned because there is no constraint on it. The master table can actually have rows, there's nothing preventing it in PostgreSQL inheritance.
my_table_3 is scanned because its check constraint (t='2013-07-01 01:00:00') doesn't have anything to do (in principle) with the condition in the WHERE clause. Even if the definition of the function my_extract makes my_extract(t) = 0 incompatible with t='2013-07-01 01:00:00', the database is clearly not "intelligent enough" to infer it. You could add, if appropriate, even if redundant, NOT (my_extract(t) = 0) in your where clause. That would prevent my_table_3 from being scanned.
my_table_2 is scanned as expected.
my_table_4 is not actually scanned.

So, the constraints prevent one table from being scanned out of the 4.

Alternate query, adding the (my_extract(t) = 0) clause:

EXPLAIN
    SELECT *
    FROM my_table_master
    WHERE t='2013-07-01 01:00:00'
          AND NOT (my_extract(t) = 0)  /* This is the new addition */ ;

returns

Append  (cost=0.00..309.45 rows=7 width=44)
  ->  Seq Scan on my_table_master  (cost=0.00..0.00 rows=1 width=44)
        Filter: ((t = '2013-07-01 01:00:00'::timestamp without time zone) AND (my_extract(t) <> 0))
  ->  Seq Scan on my_table_2  (cost=0.00..309.45 rows=6 width=44)
        Filter: ((t = '2013-07-01 01:00:00'::timestamp without time zone) AND (my_extract(t) <> 0))

By adding the extra condition, the scan of my_table_3 is prevented.

NOTE: To make the trials, I've made up my_extract like this:

CREATE FUNCTION my_extract(_t timestamp without time zone)
RETURNS integer AS
$$
begin
    return extract(day from _t)::integer ;
end
$$
LANGUAGE plpgsql IMMUTABLE /* attention! */ ;

I've used plpgsql to ensure it is not inlined (even if it could be trivially done). Notice that it is necessary that the function be labeled as IMMUTABLE (that is, its result only depends on its parameters), or it won't work as expected.

ADDENDUM

EXPLAIN
    SELECT * FROM my_table_master
    WHERE my_extract(t) = 0 ;

  Append  (cost=0.00..613.25 rows=13 width=44)
    ->  Seq Scan on my_table_master  (cost=0.00..0.00 rows=1 width=44)
          Filter: (my_extract(t) = 0)
    ->  Seq Scan on my_table_2  (cost=0.00..306.62 rows=6 width=44)
          Filter: (my_extract(t) = 0)
    ->  Seq Scan on my_table_3  (cost=0.00..306.62 rows=6 width=44)
          Filter: (my_extract(t) = 0)

Works as well as expected:

my_table_master -> as before
my_table_2 -> is scanned because its constraint doesn't have any immediate relation to
my_table_3 -> is scanned because its constraint actually match the WHERE clause.

What PostgreSQL could do (and it doesn't) is eliminate the FILTER my_extract(t)=0 when scanning table 3, because the condition is already known to be true. Let's hope this optimization is implemented at some point in the future.


SSIS data flow to update source table rows after copying to destination I have a simple data flow that copies a subset of data from a source table on an internal database to a table on web-facing database.

If there is a problem, the error is outputted to an errors table.

That's all fine.

In the source table there is a bit column for SSIS_TRANSFERRED that I wish to set to 1 when the copy process completes. However, I'm unsure how to approach this.

My instinct is to craft an SQL Statement that runs against each Unique ID for every row successfully transferred as part of that package - is there a simple approach to this (i.e. as part of the data flow) or do I need to create a new Control Flow with OLE DB Command that queries the web-facing table and marks the corresponding internal rows as 'transferred' accordingly?

With T-SQL there's an output inserted clause which would be really helpful in this scenario.

I'm not aware of a way to do that with SSIS, so your alternate approach of using a second data flow after your first (you can connect the second data flow to the first so your dependency will be honored), querying the web database, and updating the internal one as appropriate will work as long as your workload can tolerate the additional latency involved in waiting to update the SSIS_TRANSFERRED column.


Can not start SQL Server, model db in RESTORING state Quick run down:

First problem started when sysadmin received error that model is recovering.

Sometime later vendor tried to do some upgrades to the database. When they restarted SQL Server it failed to start.

Looking at error log. I found the following error

  Error The database 'model' is marked RESTORING and is in a state that
  does not allow recovery to be run. Error: 927, Severity: 14, State: 2.
  Database 'model' cannot be opened. It is in the middle of a restore.
  Could not create tempdb. You may not have enough disk space available.
  Free additional disk space by deleting other files on the tempdb drive
  and then restart SQL Server. Check for additional errors in the event
  log that may indicate why the tempdb files could not be initialized.

after searching around on the web found this article on MSDN

using steps from article started sql server using Sqlservr.exe -sInstanceName -T3608 -c -f -T3609 when i tried to run Use tempdb got error message. Following advice from article I copied tempdb.mdf and templog.ldf from another server.

Now I get the following error:

  Starting up database 'tempdb'. Error: 3456, Severity: 21, State: 1.
  Could not redo log record (19:45:366), for transaction ID (0:0), on
  page (1:16), database 'tempdb' (database ID 2). Page: LSN = (18:72:8),
  type = 1. Log: OpCode = 18, context 2, PrevPageLSN: (19:45:287).
  Restore from a backup of the database, or repair the database.

After trying large number of alternative steps was able to fix the problem and get the database started.

Here are the steps I took:

On another SQL Server 2008 R2, I moved my model and temp db files to same location as on my problem server. Refer to this http://technet.microsoft.com/en-us/library/ms345408.aspx
Replaced model and tempdb .mdf and .ldf files on my problem server. Old files I saved in another directory just incase.
Tried to start the SQL Server services, verified error message

  The database 'model' is marked RESTORING and is in a state that does
  not allow recovery to be run. Error: 927, Severity: 14, State: 2.
  Database 'model' cannot be opened. It is in the middle of a restore.
  Could not create tempdb. You may not have enough disk space available.
  Free additional disk space by deleting other files on the tempdb drive
  and then restart SQL Server. Check for additional errors in the event
  log that may indicate why the tempdb files could not be initialized.

After that I opened two separate Command Prompt windows.

Below to make it easier I call first command prompt windows CPA, and second one CPB.

in CPA run Sqlservr.exe -sInstanceName -T3608 -c -f -T3609 (note: I had to navigate to binary location) don't forget to replace InstanceName with yours

-T3608 only starts master Trace Flags documentation

-T3609 skips creation of tempdb

in CPB run SQLCMD -E -SADMIN:Servername\InstanceName replace with your ServerName\InstanceName. Using a Dedicated Administrator Connection
in CPB run sp_detach_db 'model' then go
in CPB run the following code, just replace with your file location.

CREATE DATABASE [model] ON
( FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL10_50.R2\MSSQL\DATA\model.mdf' ),
( FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL10_50.R2\MSSQL\DATA\modellog.ldf' ) FOR ATTACH
go

in CPA you should see Starting 'MODEL' messsage
in CPA hit Ctrl+C that will show message if you want to exit hit Y

Using SQL Server Configuration Manager or Services page start SQL Server service.

After I was able to connect using SSMS.


How to find non utf8 data in mysql Ok so I have a mysql database containing data that is input from various customers. The customers are told that their data should only be encoded as utf8 however I have become aware that this is currently not being checked and some people are using characters not encoded in utf8. We intend to add some checking but in order to clean the data already there is there a sql or mysql query (or admin command) that will show me the data that is NOT encoded as utf8.

Sorry if this is a dumb question I'm a Linux Sysadmin and have limited exposure to databases.

Thanks

B

You can check for the existence of (non-)UTF-8 data by comparing byte length to character length on a column, e.g.:

SELECT * FROM MyTable
WHERE LENGTH(MyColumn) <> CHAR_LENGTH(MyColumn)

Multibyte characters will have a greater LENGTH (bytes), so you'll need to look for where that condition isn't met.

Note that MySQL's utf8 character set isn't true Unicode UTF-8 as it only supports a maximum of 3 bytes per character. If your MySQL is later than 5.5.3 you can use utf8mb4 to get 4 bytes per character.


MySQL subquery slows down drastically, but they work fine independently Query 1:

select distinct email from mybigtable where account_id=345

takes 0.1s

Query 2:

Select count(*) as total from mybigtable where account_id=123 and email IN (<include all from above result>)

takes 0.2s

Query 3:

Select count(*) as total from mybigtable where account_id=123 and email IN (select distinct email from mybigtable where account_id=345)

takes 22 minutes and 90% its in the "preparing" state. Why does this take so much time.

Table is innodb with 3.2mil rows on MySQL 5.0

In Query 3, you are basically executing a subquery for every row of mybigtable against itself.

To avoid this, you need to make two major changes:

MAJOR CHANGE #1 : Refactor the Query

Here is your original query

Select count(*) as total from mybigtable
where account_id=123 and email IN
(select distinct email from mybigtable where account_id=345)

You could try

select count(*) EmailCount from
(
    select tbl123.email from
    (select email from mybigtable where account_id=123) tbl123
    INNER JOIN
    (select distinct email from mybigtable where account_id=345) tbl345
    using (email)
) A;

or maybe the count per email

select email,count(*) EmailCount from
(
    select tbl123.email from
    (select email from mybigtable where account_id=123) tbl123
    INNER JOIN
    (select distinct email from mybigtable where account_id=345) tbl345
    using (email)
) A group by email;

MAJOR CHANGE #2 : Proper Indexing

I think you have this already since Query 1 and Query 2 run fast. Make sure you have a compound index on (account_id,email). Do SHOW CREATE TABLE mybigtable\G and make sure you have one. If you don't have it or if you are not sure, then create the index anyway:

ALTER TABLE mybigtable ADD INDEX account_id_email_ndx (account_id,email);

UPDATE 2012-03-07 13:26 EST

If you want to do a NOT IN(), change the INNER JOIN to a LEFT JOIN and check for the right side being NULL, like this:

select count(*) EmailCount from
(
    select tbl123.email from
    (select email from mybigtable where account_id=123) tbl123
    LEFT JOIN
    (select distinct email from mybigtable where account_id=345) tbl345
    using (email)
    WHERE tbl345.email IS NULL
) A;

UPDATE 2012-03-07 14:13 EST

Please read these two links on doing JOINs

http://en.wikipedia.org/wiki/Sql_join
http://dev.mysql.com/doc/refman/5.5/en/join.html

Here is a great YouTube Video where I learned to refactor queries and the book it was based on

http://youtu.be/ZVisY-fEoMw
http://www.amazon.com/Refactoring-SQL-Applications-Stephane-Faroult/dp/B006TR0ZEO


View SQL 2008 sp_cursorexecute Underlying Query and Execution Plan I'm performance tuning a Dynamics AX application and see in a SQL trace a long-running, high-I/O query of the form exec sp_cursorexecute 1073742882 ... When I try and run that query in a new SQL Management Studio window, I get an error Could not find prepared statement with handle 1073742882. I'm not sure, but it seems that a cached plan is connection-specific. I have no sp_cursorprepare in my trace; repeating the use case reveales the same prepared handle ID with a new cursor. Since it's a shared environment I'm connecting to, I think I'd have to reset the app server and trace its startup in order to see it.

Is there any way to use the cursor's prepared handle ID to see the underlying query?
Is there any way to correlate a row in dm_exec_cached_plans to this cursor?
Is there any way to see the execution plan via dm_exec_query_plan or some other way?

I've not had to track down cursor activity since SQL2000 i.e. pre-DMV days. The old way would still be viable I assume, use profiler and include execution plans in the trace.

I can't remember if the plan will be included in the sp_cursorexecute call or if you need to go back through the trace and find the sp_cursorprepexec or sp_cursorprepare event associated with the handle.


What is a good/healthy mysql query execution time? What is a good/healthy mysql query execution time?

To my calculations:

  The site gets 1000 unique visitors per hr

  10 page view for each unique visitor

  5 minutes for each unique visitor (I'm ignoring this. I should,
  right?)

  1000*10=10000/(60*60) = 2.7 views per second.

  I have 3 queries on every page. 2.7*3 = 8.1 queries per second

  1 second/8.1 = 0.123

So average query execution time must be less than 0.123.

Assuming visits don't increase, can we say anything less than 0.123 works for a healthy database?

The question is a bit vague, but I think this answer (a) may provide some useful guidance and (b) is too big to fit in a comment.

Capacity planning a server is a bit more complex than that.  You need to take some basic queuing theory into account.  Your model would describe a server at 100% capacity, which is actually likely to cause performance issues.  In practice you would want to aim for the server running perhaps 25-50% capacity at peak load if you want your site to have consistent response times.

Queuing models state that the average wait time increases hyperbolically as the system approaches saturation.

A system that is 50% saturated has two requests waiting service on average
A system that is 90% saturated has 10 queued requests on average
A system that is 99% saturated has 100 queued requests on average

You should benchmark your queries under load to see how much resource they actually take up.  Then apply a view of your peak load to see how much capacity you need for the system to remain responsive.


does the mere existence of a primary key increase query speed for large data sets? So, I have a table with about 2.4M rows and ~30 columns. There is no unique column within the data. The data is transaction logs from stores where each transaction number is only unique on a per store basis and resets at midnight, but that's besides the point.

As it stands, queries on this table take upwards of one minute and I can't figure out why. I have added indexes on columns that have high selectivity ratio but it hasn't made any difference. So, if I add an auto increment 'id' field for each row, would that speed things up, or are the queries slow because there are so many columns and so much unique data?

Having a primary key per se will not speed up queries. Primary key constraints are usually accompanied by a unique index. If this index matches query predicates or join conditions then those queries are likely to run faster.


Finding similar values in columns For example I have a column id and column email. I would like to find all values from id and column that share similar pattern.

let's say:
in id 'column' I have Alexander and in 'email' column - walex@sample.com.
if I set similarity to 4 - means match 4 character in row

script should find alexander and walex@sample.com cause they have 4 charachters match (alex)

What's the best way to accomplish something like this?

If you have a numbers table populated with integers up to the max length the strings in the data can be

SELECT Y.*
FROM   YourTable Y,
(SELECT 4 AS NumChar) T /* Change this line to alter desired length*/
WHERE  EXISTS (SELECT *
               FROM   Numbers N
               WHERE  N.N < LEAST(LENGTH(name), LENGTH(email)) - FLOOR(NumChar/2)
                      AND GREATEST(LOCATE(SUBSTRING(Name, N, NumChar), email),
                                   LOCATE(SUBSTRING(email, N, NumChar), Name)) > 0)

SQL Fiddle


When do you remove entries from a write-ahead log (WAL)? If I understand correctly, when a user updates a database record and commits the transaction the commit() call will block until the following steps are done:

Database documents the change in the write-ahead log (WAL).
Database updates the actual record.
Database removes the WAL entry.

I have the following questions about the WAL:

When does the database know it's safe to remove the entry from the WAL?
Does a WAL implying writing the record to disk twice (once in the WAL, once in the database file)?
Postgres claims that WAL "significantly reduced number of disk writes, because only the log file needs to be flushed to disk" but (coming back to question #1) don't you have to flush the main database file before removing an entry from the WAL?

First up, well done for trying to get to grips with how WAL works. It's often misunderstood but when people grasp the concept it's a genuine lightbulb moment for understanding databases.

We need to re-word your description of the steps that occur to commit a transaction:

Database documents the change in the write-ahead log (WAL).
Database updates the in-memory record.

That's it, we're done, the transaction is committed. The in-memory record is modified, details of what was modified is stored in the log, negating the need to harden every record change to physical disk.

On to your questions:

  When does the database know it's safe to remove the entry from the WAL?

The one liner explanation would be that the log entry isn't required once modified database records are flushed to disk. When this occurs is dependent on a variety of factors which vary according to the database. SQL Server for instance has a configurable recovery interval which controls the checkpoint frequency (the action of flushing changed records to disk).

  Does a WAL implying writing the record to disk twice (once in the WAL,
  once in the database file)?

Yes, kind of, no, depending on your perspective. Change a record once and the modification will be recorded to the log and when a checkpoint occurs, written to disk. Change the record 15 times between checkpoints and you have 15 changes recorded in the log but the modified record will only be written to disk once, on the next checkpoint.

  Postgres claims that WAL "significantly reduced number of disk writes,
  because only the log file needs to be flushed to disk" but (coming
  back to question #1) don't you have to flush the main database file
  before removing an entry from the WAL?

Yes you do but see above for why WAL reduces disk writes.


Two key attributes in an EER, what does it mean? I'm currently drawing an EER diagram and stumbled across this problem:
Let's say I'm modeling a workplace where all employee's uniquely can be identified by their name (ie no employees share the exact same name) and their employee number. Is this a correct way to model it?

I'm not really sure on whether this is an ok design. Does this design say that Name and Emp_nr together form a unique Employee, or does it say that Name and Emp_nr independently from each other can identify my Employee? I've been searching everywhere and couldn't find a single example on where 2 key attributes is used.

This only tells that an Employee has two attributes which are bith a combined primary key

he has a name and
a employer number

In a Table structure , it will be displayer as two columns for the Table Employee and the would have a PRIMARY KEY(Emp_nr,Name)
Employee
-------
Emp_nr

Name


sql 2008, select from a view as a different user, like owner In sql server 2008, the system views sys.database_role_members and sys.database_principals, only returns the users and group that the current account is allowed to view.

I need to check id a certain account exists, so I need to see all the existing sql account accounts, regardless the permissions of the current account, I mean, just like it used to be in previous versions.

Is there some option like 'EXECUTE AS OWNER' but for views?

Something like

create view dbo.vUsers select as owner as

select * from sys.database_principals

Use a table valued function instead?

It has to be a multistatement function though.
In-line UDFS don't allow EXECUTE AS (I guess because they are simply macros like views)

CREATE FUNCTION dbo.vUsers ()
RETURNS @dbprincipals TABLE (
    name sysname NOT NULL,
    type char(1) NOT NULL
    )
WITH EXECUTE AS OWNER
AS
BEGIN
    INSERT @dbprincipals
    SELECT name, type FROM sys.database_principals;

    RETURN;
END
GO


SQL Server 2016 - write output file to mapped Samba share (CentOS 7) I have a stored proc that's going through the tables, getting data, and write the result into a csv file (mainly to provide up to date data for FTP customers).

When writing it to the local D: drive, everything runs fine. File writes as it should and everyone is happy.

As I'm setting up new FTP server, I decided to try and ditch 12672 batch scripts transferring files from the DB server (Win Server 2012 r2 running SQL Server 2016)
to the FPT server (CentOS 7 + Samba) and have the query outputs written straight there.

SQL Server reports that query was executed successfully, but the output file disappears into a network black hole somewhere along the way.

Slowly losing my mind with this one.

Below list of things that I'm 99% sure are not causing the issue:

Samba - even though security is still mildly paranoid it's
temporarily not user restricted.
Firewall - turning it off / on makes no difference whatsoever.
SELinux - same as firewalld, enforcing, permissive, disabled makes no
difference.
File system permissions on Linux - 777'ed a test share, and su'ed as
MSSQLUSER and successfully written a file in the same path I'm trying
to get SQL server to write into, to check that.

Samba logs absolutely nothing, which leads me to believe SQL isn't even trying to touch that share. There's nothing at all on the Linux side that would suggest SQL tried to make a connection.

My current theory is that it's refusing to do it because it's not SQL Server's service account that mapped the share, so here goes:

Is there a way to map that network drive for all users? I'm reluctant adding extra code to the stored proc to map the drive / delete it afterwards. End game is to have all the internal transfers working this way, meaning quite a number of SP's to alter, and different schedules would at some point inevitably clash. Not to mention the insane number of mappings per day. Countless Google searches point towards a simple answer "No.", but as they say, hope dies last.
What potential consequences am I faced with if I were to change the service accounts (NT SERVICE\MSSQLSERVER) credentials? Unfortunately I haven't got a server I can sacrifice in the name of science and find out myself.
Is there a simple solution I'm missing that'd allow me to do this? Am
I trying to re-invent the wheel?

Any suggestions appreciated. I can do / change whatever I want on the Linux box, Windows side of things is a bit more complicated as my knowledge / experience with Microsoft systems is "mediocre" at best.

In order to map a network drive under the SQL Server Service Account, you need to login to the server as the service account.  Then use net use /persistent:yes to connect the share.  This should make the share available to the SQL Server Service Account automatically each time it logs into that server.

Having said all that, instead of using a drive letter for the share, simply write directly to the Linux SMB share via the UNC name... so \\linux-machine\share\some-file-name.csv

Alternately, as Aaron Bertrand alluded to in a comment, write a utility to query the data from SQL Server and write directly to the samba share on Linux.


Time Difference Between Two Consecutive Rows i need simple SQL to calculate the time diff between Two Consecutive Rows
anyone can help

You can use a window function (aka "Analytic Function") to access values in the rows outside of the "current" row.

If you do that, it is mandatory to specify a criteria that defines the sort order of the rows otherwise there isn't such a thing as "the previous rows".

Given the extremely limited information you have shown us, you are probably looking for something like the following:

select subno,
       contrno, orderby, prepost_paid,
       time_stamp,
       lag(time_stamp) over (order by time_stamp) - time_stamp as timestamp_diff
from the_table
order by time_stamp;


Moving SQL server 2008 to a new Server with all DBs, replication and Jobs We have to move our Production SQL server 2008 to a new Server (with latest SKU and more RAM). So we need to move all DBs, replications setup (publisher) and all jobs, means everything :). Also we want to keep the SQL instance name, so that we do not have to change this SQL instance name in web.config of the all the web servers.

So could anyone please share the steps including verification if done this earlier.

Please let me know if any more information is required from my side

To do this without recreating the publications and subscriptions - backup the publication, msdb, distribution, and the master databases.  Then restore them on the new Publisher that has the same name as the old Publisher.  Use the keep_replication switch when doing the restore.  A DNS flush may be required but afterwards the Subscribers should begin synchronizing.

This may or may not work for you.  I highly suggest testing the migration out in your test environment prior to production deployment.

I would also like to add that you may have to back up and restore the Service Master Key to the new Publisher as well.  The is tied to the distributor_admin login that is created for the linked server when you Configure Distribution.

If this doesn't work for you - you can you can configure Distribution on the new Publisher/Distributor, and recreate the publication and subscriptions from backups.  The subscriptions could be configured as replication support only if done during a maintenance window with no changes occurring.  This would avoid sending down a new snapshot.  If not done during a maintenance window, run a data validation to see how out of sync you are with the Subscriber and use tablediff utility or SQL Data Compare to bring the Publisher and Subscriber back into convergence.  Reinitialization is an option as well.

Also, if you are upgrading the Publisher SQL Server version then there are considerations to make which are covered in Using Multiple Versions of SQL Server in a Replication Topology.  It depends on your replication type.

  For all types of replication, the Distributor version must be no
  earlier than the Publisher version. (Frequently, the Distributor is
  the same instance as the Publisher.)

  For transactional replication, a Subscriber to a transactional
  publication can be any version within two versions of the Publisher
  version. For example, a SQL Server 2000 Publisher can have SQL Server
  2008 Subscribers, and a SQL Server 2008 Publisher can have SQL Server
  2000 Subscribers.

  For merge replication, a Subscriber to a merge publication can be any
  version no later than the Publisher version. For more information
  about compatibility for earlier versions, see "Compatibility Level for
  Merge Publications" later in this topic. For more information about
  replication features that are supported in the various editions of SQL
  Server, see Features Supported by the Editions of SQL Server 2012.


MySQL read-write Master with many read-only Slaves over shared storage? I've asked the same question on the MySQL forum but have also seen lots of great responses here as well so thought I would try my luck here too.

We have a relatively large MySQL environment where we are currently using MySQL Master-Slave replication to achieve our performance requirements. We typically have multiple clusters of 10 hosts, where one is read-write and the others are read-only. We are also very performance sensitive so are currently using FusionIO cards in each host.

We are finding a few challenges with this architecture however, and are looking for other ideas - specifically though we would like to come back to a very high speed shared storage solution that would allow us to have a single copy of the DB, where the master can mount it as read-write and the slaves as read-only.

I understand that by using Clusterware this type of design is possible, however we would prefer to not to use that product as we are okay to use some manual failover processes if need be.

Has anyone ever done anything like this before? I know that MySQL supports mounting as read only, "--innodb-read-only=1", however what other implications are there if using this method compared to their existing Master-Slave configuration? Is it possible to have one as read-write and the others read-only if using this parameter?

Thank you in advance for any thoughts on this.

NO.  Do not attempt to have a single copy of the data with multiple copies of mysqld accessing it.  I don't know if that ever worked; if it did, I suspect it has atrophied over the years as changes have been made.

Think about it...  Each copy of the server has lots of stuff cached in RAM.  To coordinate that with other servers would require writing things to disk; this defeats the performance goal.  When a write occurs; all traces of it would need to be immediately flushed to disk, and all the other servers would need to know to read it and replace whatever is cached in their RAMs.

With FusionIO, check to see that you can safely turn off innodb_doublewrite.

How many queries/second?  Do you have any 'slow' queries?  (That is, there are other ways to beef up performance, than just hardware.)


Install SQL Server Express without administration rights Is there a way to install SQL Server Express without administration right?

Is there a portable version?

(I need to test SQL Server on my computer before using the server but I don't have administration right. so I cannot install it. I search a way to bypassed my problem)

Unfortunately, no. You are installing a program and making service and registry changes, etc. You need to run setup as an account that has local administrative rights. No workaround to this that wouldn't involve some back door into administrative rights.

Best bet is to ask for someone to temporarily give you the rights or install it for you.

Source: The Microsoft SQL Server books online article for how to install SQL Server 2008 R2Express


Ola Scripts installation in "Multi-server" Jobs Folder I am installing the Ola scripts on a Central Management Server for multi-server administration. How can I get the jobs to show up under "Multi-server" Jobs Folder instead of the "Local Jobs" folder?

The first part is to understand that a Central Management Server and a Master Job Server are not the same. CMS is used to centrally manage instances, the master job server is used to deploy standardised jobs across mutiple servers. These two can be linked but are not directly related.

To have the jobs appear in Multi-Server Jobs, the jobs need to be configured as multi-server jobs. To do this, open the job and navigate to the Targets page. Select "Target Multiple Servers" and then select the target job servers to which you wish to deploy the job.

See this link for more info.

If you're deploying Ola's maintenance jobs on a Master Job Server, there are several considerations you should make before deploying:

All jobs have the same configuration, so your output file path must be accessible or identical on all servers. If it varies, you will get errors on the servers that don't match. NOTE: in later versions (2014+) of SQL, you can use the Agent macro SQLLOGDIR to use a local path for output log files that vary between servers in a master job server.
Backup jobs must be configured to backup to an identical path, either a local path that exists on all servers or a shared path that all servers can access.
Exclusions for databases, tables etc must be applied to all servers, as the jobs cannot be individually modified on servers.
Schedules are identical across servers, so if you're backing up to a shared location, you may encounter performance issues due to increased write activity at the same time.


connection to Informix still active despite closing? I have a small C# utility connecting (via the  IBM Data Server Provider for .NET) to a Informix DBS (version 11.7) and sending one or two queries to several databases. Even though I close each connection after the query is done, a call to onstat -u still lists my computer, with the flags Y and P (Waiting on a condition, Preparing/prepared, respectively) being set, one line for each query:

address          flags   sessid   user     tty      wait         tout locks nreads   nwrites
56413170         Y--P--- 704      user      MYWS  59dce6e8         0    1     64       0

The connections disappear when I end the program, or after a few minutes. I would expect my client not to show up immediately after closing the connection.

Is this a normal occurrence, or a sign that I might be doing something incorrectly?

Check your connection pooling (settings in IBM Data Server Provider).
I never used it, but the docs on IBM Data provider say:

  When a connection is first opened against a database, a connection pool is created. As connections are closed, they enter the pool, ready to be reused within the same process by other applications that need connections.

So even if you close the "session" from you app to Informix DB, a connection is still active between IBM Server Provider and the Informix instance.

Hope this helps.


Get value of math operations SQL SERVER I have a problem on this query, how do I use the result of the query contained in CostoUnitario field.

I see better explain, after: ... as CostoUnitario..

I should insert ,CostoUnitario* Quantita

How can I do?

CODE:

SELECT Capitolo.Descrizione,SottoCapitolo.Descrizione,
SottoCapitolo.Immagine,
SottoCapitolo.DescrizioneEstesa,
SottoCapitolo.Quantita,
ROUND(((SottoCapitolo.CostoUnitario)+(((SottoCapitolo.CostoUnitario)/100)*PercentualeMargine)+(((SottoCapitolo.CostoUnitario)/100)*PercentualeUtile)+(((SottoCapitolo.CostoUnitario)/100)*PercentualeSpeseGenerali)),0)
 as CostoUnitario
FROM Preventivo inner join Capitolo on Preventivo.IdPreventivo = Capitolo.IdPreventivo inner join SottoCapitolo on SottoCapitolo.IdCapitolo = Capitolo.IdCapitolo order by Capitolo.Descrizione

IMAGE:

You must repeat last operation and add * Quantita.

  SELECT A, B, C, (A + B) AS AB, (A + B) * C AS ABC

SELECT Capitolo.Descrizione,SottoCapitolo.Descrizione,
SottoCapitolo.Immagine,
SottoCapitolo.DescrizioneEstesa,
SottoCapitolo.Quantita,
ROUND(((SottoCapitolo.CostoUnitario)+(((SottoCapitolo.CostoUnitario)/100)*PercentualeMargine)+(((SottoCapitolo.CostoUnitario)/100)*PercentualeUtile)+(((SottoCapitolo.CostoUnitario)/100)*PercentualeSpeseGenerali)),0)
 as CostoUnitario,
ROUND(((SottoCapitolo.CostoUnitario)+(((SottoCapitolo.CostoUnitario)/100)*PercentualeMargine)+(((SottoCapitolo.CostoUnitario)/100)*PercentualeUtile)+(((SottoCapitolo.CostoUnitario)/100)*PercentualeSpeseGenerali)),0) * SottoCapitolo.Quantita
FROM Preventivo inner join Capitolo on Preventivo.IdPreventivo = Capitolo.IdPreventivo inner join SottoCapitolo on SottoCapitolo.IdCapitolo = Capitolo.IdCapitolo order by Capitolo.Descrizione

You can avoid it by calculating this values in presentation layer.


Postgres read from master after slave failure I'm new to postgres and have setup a master:slave database. Everywhere I look I see slave promoting to master failover, but what I can't seem to find anywhere is what happens if the slave fails.

How do I make the queries select the right master/slave db incase the slave has gone down? I'm using pgbouncer for my connection manager for each instance. Should I be using something different?

Usually the replica server is used for HA (that's why everyone is talking about promoting it) and for read scaling.
From your question it seems you're interested in the later.
As you use the replica for read scaling, you can use any load balancer to spread the read load between the master and slave.
If the slave goes down, most load balancers will stop sending traffic to it.


Auto generated query not working I'm testing knowage system, my backend is MSSQL while knowage server is my sql.
I found on dataset audit log this query:

select "ATC_1" AS "ATC_1",  SUM("Importo_Scarto") AS "Importo_Scarto", SUM("Q_Scarto") AS "Q_Scarto"
from (SELECT
      [ATC_1]
      ,[MESE]
      ,[ANNO]
      ,[Q_Scarto]
      ,[Importo_Scarto]
  FROM [dbo].[MyTable]) T
  where (("ANNO","MESE") IN ('2017','10'),('2017','11'),('2018','01'),('2018','02'),('2018','03')
  and ("Invio") IN ('3'),('1'),('2') )
  group by "ATC_1"

the error I get is this:

  com.microsoft.sqlserver.jdbc.SQLServerException: An expression of non-boolean type specified in a context where a condition is expected, near ','

The condition:

where ( ("ANNO","MESE") IN ('2017','10'),('2017','11'),
                           ('2018','01'),('2018','02'),('2018','03')
   and  ("Invio") IN ('3'),('1'),('2')
      )

has 2 issues:

first, it needs parentheses around the IN lists (and the ones you have around the two sub-conditions are redundant):

where   ("ANNO", "MESE") IN ( ('2017','10'), ('2017','11'),
                              ('2018','01'), ('2018','02'), ('2018','03')
                            )
   and  "Invio" IN ( '3', '1', '2' )

second, even if you fix the above, it won't work in SQL Server as this type of syntax (the first sub-condition, that combines IN with tuples) has not yet been implemented. You can rewrite in various ways, eg. using a JOIN and VALUES syntax:

from
      ( ...
      ) T
    join
      ( values ('2017','10'), ('2017','11'),
               ('2018','01'), ('2018','02'), ('2018','03')
      ) as V ("ANNO", "MESE")
    on
      V."ANNO" = T."ANNO" and V."MESE" = T."MESE"
where  "Invio" IN ( '3', '1', '2' )
---

by the way, the subquery (aliased T) is not really needed. It ony adds complexity for those trying to read it:

select
    T."ATC_1",
    SUM(T."Importo_Scarto") as "Importo_Scarto",
    SUM(T."Q_Scarto") as "Q_Scarto"
from
      dbo.[MyTable] as T
    join
      ( values ('2017','10'), ('2017','11'),
               ('2018','01'), ('2018','02'), ('2018','03')
      ) as V ("ANNO", "MESE")
    on
      V."ANNO" = T."ANNO" and V."MESE" = T."MESE"
where
    T."Invio" in ( '3', '1', '2' )
group by
    T."ATC_1" ;


Hierarchy query with 3 tables I am not able to understand how to achieve a hierarchy accessing 3 tables. That is, I have Table1, Table2 and Table3 where:

Table1 (ID_table1, name_c, size)

Table2 (ID_table2, ID_table1, name_l, size)

Table3 (ID_table3, ID_table1, ID_table2, name_d, size)

How do I make a hierarchy with all this information? I've looked for it but only found solutions with information into a single table.

*EDIT: I edited the information in the tables because they do not express myself

I've never tried anything like that, but what if you UNION your three tables into one, and then do a "one-table hierarchical query" on the result of that?

WTIH alltabs as
    (Select 'T1' as src, Table1.ID, NULL AS ID_table1, NULL as ID_table2, Table1.name, Table1.size
    from Table1
    union
    Select 'T2' as src, Table2.ID, Table2.ID_table1, NULL as ID_table2, Table2.name, Table2.size
    from Table2
    union
    Select 'T3' as src, Table3.ID, Table3.ID_table1, Table3.ID_table2, Table3.name, Table3.size
    from Table3)
Select *
from alltabs
/*you can have an optional STARTING WITH clause here, but it's not clear from your question what that would be*/
connect by (id = id_table1 and (src = 'T1' or src = 'T2'))
        or (id = id_table2 AND (src = 'T2' or src = 'T3'))

(I'm assuming that the relationship is Table1 PARENT OF Table2 PARENT OF Table3).


Where to insert data after sharding? Actual database in localhost:30000 ------------- I used this as Shard1
  another mongod  in localhost:30001 ------------- Shard2
  another mongod  in localhost:30002 ------------- Shard3

Added 1 config server and Router pointing the configdb

Now in Router,

1. mongo admin
2. sh.addShard("localhost:30000")..30002           //Added 3 shards successfully
3. sh.enableSharding("myDatabasename in shard1")  //Enabled successfully
4. sh.shardCollection(...)                       //Shard Collection successfully

I checked each shards and I am able to see that it is split-ed in ascending order like

shard key starting with A-D in shard2 as chunk 1
shard key starting with E-H in shard3 as chunk 1
shard key starting with I-O in shard1 as chunk 1
shard key starting with P in shard1 as chunk 2               //had upto P only

Now I need to insert a new record means in which mongo instance I have to insert?

I tried in shard1 and inserted a new record with sharding key starting with E, which is getting inserted in shard1 itself whereas it has to be inserted in shard3.

With a sharded cluster, you need to insert data via a mongos process. A mongos process acts as a query router: it directs queries/updates to the appropriate shard(s) based on the current cluster configuration and metadata.  Typically you deploy one mongos per application server.

From your driver point of view, you can update your connect string and replace the address of your standalone server or replica set seed lists with one or more mongos servers to use.

For more information, you're best starting with the Sharding Introduction in the MongoDB manual.


New Job and Company uses PostgreSQL I have used MySQL and SQL server all of my professional career.

The company I will be working for uses PostgreSQL.

Can someone who was/is in a similar situation please give me some insight on what the biggest differences are and what types of client tools I would use to connect to and manage the database?

Thanks

In terms of support, there are some very basic differences you should be prepared for that have nothing to do with the databases themselves, but how they are supported.  PostgreSQL support comes from two, maybe three places.  For comprehensive support by the people who write the code, look to the pgsql-general mailing list.  Use the postgresql wiki (like this page: http://wiki.postgresql.org/wiki/SlowQueryQuestions) and the docs to figure out the basics, and then post a question with as much relavent info as you can.  You'll generally get an answer within a few hours to a day, depending on how obscure your problem is.  The faster channel is the IRC channel (google it) which has a few folks on from around the world all the time.  Not as comprehensive an answer usually, but much faster.  Finally you can pay for commercial support and have someone you pay on the other end of the phone line to do anything up to and including log into your servers to fix problems.  While bulletin boards are a common place for MySQL support, they aren't for PostgreSQL so don't expect to find that to be the case.

In terms of code maturity and quality PostgreSQL is pretty high up there, and you will seldom if ever see crashes or data corruption that is PostgreSQL's fault.  It's generally more strict on interpretation of the SQL specification than either MSSQL or MySQL, so if you find a data type from one of those two dbs not working, it's likely not a legit SQL type but something created by those dbs (i.e. datetime or unsigned ints in MySQL).  If you have a query that works in MySQL and not in PgSQL, it's likely that MySQL is more permissive than the SQL spec actually allows, and PgSQL is following the spec.

The majority of PostgreSQL users who need high performance machines run some flavor of unix, i.e. Solaris, BSD, Linux etc.  The Windows port is ok, but is and always will be, a bit of a red-headed step-child in terms of performance and support.  There just aren't as many users of high performance Windows / PgSQL machines out there, and likely never will be.  It's stable on Windows, and a decent performer, but if you want 10k TPS you'd best look to Unix.

The GUI tools built just for pgsql are primitive compared to other dbs.  Most PostgreSQL DBAs use whiteboards or commercial applications like TOAD to do data modelling.  OTOH, PostgreSQL has the absolute best command line interface of any database I have ever used.  Learn the two commands \h and \? and from there you're set.  Complete syntax diagrams for all SQL statements are stored in the psql command line interface, and it has tab completion that is very very good.  The psql user interface is very much like a unix command shell, fast and powerful, but a bit cryptic for a beginner.  Learning it is well worth the effort though.

In terms of replication the latest versions are easily a match for MSSQL server and mostly a match for Oracle.  Note that there isn't just ONE replication solution for postgresql.  There's built in streaming replication, simple PITR as well.  Then there's Londiste from the folks at skype and Slony paid for originally by the folks at Afilias, who happen to run the .info, and .org domains on pgsql with slony replication.  Slony is still in many ways the most mature, but also the most complex, and the least forgiving.  It's not the absolute fastest,  but it is the most flexible.  Expect to spend a few weeks learning it if you need to use it.

PostgreSQL releases on a 12 to 18 month release cycle, and the cycle has been getting officially shorter of late, trying to maintain a one year release.  New releases are officially supported for 5 years.  Each release is often much faster or more capable or both than the last version, and the development is pretty transparent, assuming you're willing to pay attention to the pgsql-hackers mailing list.

One of the beauties of PostgreSQL is the speed with which bugs get patched.  I have, on two occasions, reported a bug to the pgsql-general list, and had a patch within 24 hours.  Both times it took longer to test it, schedule production downtime, and apply the patch than it took to get it.  A well described bug or problem gets almost immediate responses on the pgsql-general mailing list.  A whinging, vague complaint about a poorly defined problem often gets deafening silence.

Two books I'd recommend are Performance PostgreSQL 9.0 by Greg Smith, and the PostgreSQL Admin Cookbook from the same publisher Pakit (or something like that).  both are worth every penny to a pgsql admin / dev / dba type.


Is database performance optimization independent of the size of a database or a table? If by rewriting an operation or a piece of code the performance has increased by 30% in a program reading and writing to a table with 100,000 records, does that optimization remains same if the table grows and would have 10,000,000 records i.e. still 30%?

Given that the hardware has not been modified.

There is no definite answer. It really depends on the data and the change you have performed.

For instance, if you created an index that helped in the 100,000 records table, in many cases it will help with the 10,000,000 records table.

However, if, let's say, you had a cursor and you've rewritten it to a set based solution, it will be good enough for the 100,000 records table, but not good in the 10,000,000 records table because there is no supporting index for the query.

The best thing to do is to monitor the system as it grows and see if queries keep running fast or degrade, and fix accordingly.


On my local computer, where is the data stored for a search on a dynamodb database I am using RazorSQL to access a dynamodb database in a "SQL"-like way.
 One of the things RazorSQL lets you do is the sql command select * from table where ... on all columns. This is significant better than Amazon as Amazon only lets you do the filter on the hash and primary_key columns.

My question is whether the search is performed on my local computer or on the Amazon servers.

This matters because if my database has 100 lines and my search returns 3, I should only get charged for 3 rows of data retrieval if the search happens on the Amazon end, but if the search happens on my end, thus necessitating transfer of the datatable to my computer, I would get charged for 100 retrievals.

The search is made on the server host where the database service is installed. And you will only get the result set sent to you.
  It would make no sense your client to make this, as your client is just an interface that allows your to interact with your database.
 This is available for all databases.


Red Gate SQL Prompt in Visual Studio not working? I have tried out SSDT Database project in Visual Studio, and with the SQL Prompt solution from Red Gate. It seems that the intellisense only works for SQL keywords like SELECT, WHERE etc, it does not provide intellisense for table names, column names etc.

Is there a trick to get this working? (Tried in both VS2015 and VS2017).

Unfortunately SQLPrompt works on the connection to a database and not the database project. If you deploy your DB then SQLPrompt can pick it up the schema.

Its on the backlog as a research item currently https://www.red-gate.com/products/sql-development/sql-prompt/roadmap.

If you want project based intellisense then you need to disable SQL Prompt in VS


Select data from three tables using sub query and group by I would like to write a query for SQL Server to select “Sales” by “month”
  (Sales from one table[sales_fact] and month from another table[time_by_day])
  where sales is grouped by  “ product_name"
  and it is again form another table[product] i.e. sales/product by month. Here is my query:

SELECT
    time_by_day.the_month ,
    ( SELECT sum(store_sales)
      FROM sales_fact
        INNER JOIN product
        ON  sales_fact.product_id = product.product_id
      GROUP BY product_name
    )
    AS total_sales
FROM sales_fact
  INNER JOIN time_by_day
  ON  sales_fact.time_id = time_by_day.time_id
GROUP BY
    time_by_day.the_month
ORDER BY
    DATEPART(mm,CAST(the_month+ ' 1900'  AS DATETIME)) asc;

But it is not working. Getting error for the subquery. Can I get some advice pls?

Your subquery:

SELECT     sum(store_sales)
FROM       sales_fact
INNER JOIN product
ON         sales_fact.product_id = product.product_id
GROUP BY   product_name

returns a list of aggregate sum by product.

But your main query expects only one value due you're grouping by month.

SELECT     time_by_day.the_month
FROM       sales_fact
INNER JOIN time_by_day
ON         sales_fact.time_id = time_by_day.time_id
GROUP BY   time_by_day.the_month
ORDER BY   DATEPART(mm,CAST(the_month+ ' 1900'  AS DATETIME)) asc;

SQL Server returns an error indicating that your subquery returns more than one value.

If you want aggregate sales by product, instead of a subquery, join your products table on your main query on this way:

SELECT     t.the_month,
           p.product_id,
           p.product_name,
           sum(store_sales) as total_sales
FROM       sales_fact
INNER JOIN time_by_day
ON         sales_fact.time_id = time_by_day.time_id
INNER JOIN product p
ON         s.product_id = p.product_id
GROUP BY   t.the_month, p.product_id. p.product_name
ORDER BY   DATEPART(mm,CAST(t.the_month + ' 1900' AS DATETIME)) ASC;

However if you need total sales by month, remove prduct.id and product.name from both, SELECT and GROUP BY statements.

SELECT     t.the_month,
           sum(store_sales) as total_sales_month
FROM       sales_fact
INNER JOIN time_by_day
ON         sales_fact.time_id = time_by_day.time_id
INNER JOIN product p
ON         s.product_id = p.product_id
GROUP BY   t.the_month
ORDER BY   DATEPART(mm,CAST(t.the_month + ' 1900' AS DATETIME)) ASC;

If your time_by_day table has a datetime field, you can use it on your  ORDER BY clause instead of build a datetime value. In fact if you're filtering more than one year, actual ORDER BY clause do not work properly.

ORDER BY   time_by_day.date_field ASC;

If there is no datetime value, you can order by month without cast.

ORDER BY   t.the_month ASC;


Images, PDF, audio and video files in MySQL I am designing a database for an eCommerce website.

How can I store images, audio, video and PDF files which are uploaded from the online users, in a MySQL database?

I am using the InnoDB storage engine. While surfing on the Internet, I got the following answers:

I can store these files using BLOBs.
I can use the Varchar datatype to store the physical path to store these files.

According to my previous experience storing the files on the database has some issues while retrieving that files (sometimes I got broken files).

What is the best practice to store these files in my MySQL database? As I am beginner, I need to know the performance implications for my database in such situations.

My recommendation is, as Max has suggested, to store on the filesystem and store a path in the db as varchar.  This has the advantage of not having to deal with the files in the db.  Even on other db's I think storing frequently used binary files in the db is asking for performance issues (storing less frequently used files in the db, OTOH, is a good thing).

However it does have the disadvantage of requiring multiple backups to get all the data your data needs.  A secondary option might be to store the files in the db as a blob (in a separate table) and then store it on the filesystem too.  This way you can just recreate the file from the db if it is missing and you can count on your database to be a complete set of the files for backup purposes.  This may be needless complexity though.

Also if you are storing video files in the db, one real reason to go to PostgreSQL would be the stream-based LOB interface which would allow you to chunk the output.


Cannot open database requested by the login. The login failed I have a SharePoint environment, but my problem is related to DB as I couldn't solve it in SharePoint ways.

I have a database that suddenly stopped working. It's appearing in SQL management studio, but without the + sign. If I right click on it to see the tasks, they're disabled.

I have full control over the server as am the one who installed SQL and Windows. Any idea how to solve this.

sp_readerrorlog

As pointed out already by @Shanky read the error log (ERRORLOG) by setting off the sp_readerrorlog command:

sp_readerrorlog 0,1,'<your_DB>'

You could also use the official xp_readerrorlog procedure with the same parameters:

xp_readerrorlog 0,1,'<your_DB>'

xp_readerrorlog is the underlying extended stored procedure for the sp_readerrorlog stored procedure which is explained here: SQL Internals : Useful Parameters for xp_readerrorlog

Read ERRORLOG directly

If you are unable to read the ERRORLOG with the stored procedures, then manually read the ERRORLOG files (ERRORLOG.*) to find any errors.

Querying Registry to find location

If you can't find the location of the ERRORLOG file, then retrieve the path from the registry:

DECLARE @regout VARCHAR(255)
exec xp_regread 'HKEY_LOCAL_MACHINE', 'SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQLServer\Parameters', 'SQLArg1', @regout OUTPUT
SELECT @regout

Don't forget to replace MSSQLSERVER with your instance name if you are using a different instance than the default instance (...\MSSQL11.MSSQLSERVER\...).

Query SQL Server directly

SQL Server itself stores some basic information about the status of the databases in sys.databases:

select name, state_desc from sys.databases

You should get an output similar to the following:

name    state_desc
master  ONLINE
tempdb  ONLINE
model   ONLINE
msdb    ONLINE
<your_DB>   ONLINE

Permissions

If your database <your_DB> is online, then you possibly don't have adequate permissions to read the database. Change your user's permissions.

Once you have found more information you will possibly be able to pinpoint the issue.


SQL Server Hardware Specs for Analyzing TBs of Data I am building a workstation with the purpose of analyzing multiple terabytes of data.  Hundreds of tables, hundreds of columns, various data types throughout. This will include slicing-and-dicing the data into various aggregations and writing it back to summary tables (similar to building a cube) as well as processing large queries with lots of joins.

Most of the specs for database servers are (obviously) designed with lots of users in mind hitting the server.  In this case, I'm the only user, so most of the times there will be only one or two big queries going on at once.  Likewise, I may be taking a complex query and then rendering the output of that query (say 500GB+) to another table somewhere.

I want these reads/writes to happen as fast as possible but I also don't want to invest in hardware that is never going to be utilized. What recommendations does anyone have as far as CPU (clock-speed vs core count), memory size, and disk speed?

One setup I had considered was the following:

CPU: i7-6950X
RAM: 128GB (is this enough?)
SSD: Multiple Samsung 960 PRO 2TBs (NVMe sticks)

No one database should exceed 2TB so the various DBs might be spread across multiple of the NVMe drives.  Not sure if I should be going Xeon instead of i7 and, if so, should favor higher clock speed or higher core count. Lastly, wondering if 128GB RAM is enough but I was hoping the blazing fast disks would help make up for that.

Any and all versions of SQL Server are in play (including Enterprise Edition), meaning I can use whatever version best fits the performance needs.

Any advice is appreciated!

Favor the fastest processors you can get, over pure core count.  Get as much memory as you can afford up to the maximum size of data you expect to process in one chunk.  The NVMe SSDs you have specced are exactly what I would recommend.

Without more precise requirements, it is difficult to recommend specifics.

Take a look at this answer for details about clock speed vs core count.  Which edition of SQL Server are you going to use?


PostgreSQL 9.2 EBS Snapshot on AWS I found this post here back from 2011, and wanted to verify that it's still valid and good advice to take a hot snapshot of an ebs volume (PostgreSQL still running) so long as you're snapping all of the data.

The PostgreSQL documentation indicates that a low level snapshot is fine so long as the whole data directory (WAL and all tables, different tablespaces could cause an issue) are taken in the same snapshot.

I'm currently using PostgreSQL v9.2.

Yes, that advice remains valid.

A low level snapshot of a volume that does atomic snapshots is much like a plug-pull or server crash. When restored from the snapshot, PostgreSQL just does normal recovery where it replays the transaction logs.

It's a perfectly sensible way to take a backup, though I recommend also taking periodic dumps. Snapshot backups won't help you in the face of undetected filesystem corruption etc.


Service Broker Error - The conversation handle is not found A new third party application using Service Broker on SQL Server 2017 is causing the error log to report numerous failed query delivery notifications due to conversation handle not found.
I've ensured the Service Broker is enabled from sys.service_queues and have restarted the Service Broker successfully, however the errors continue.
These errors continue every two hours, making me wonder if perhaps it's an App process?  I've read another option is to set the database to Trustworthy, which I haven't tried at this point.
The required components of Service Broker are present, which I've checked via system tables such as Message Types, Service Contracts, Service Queues, Services, and Endpoints. All queues have a status of 'Not Available' (by querying sys.dm_broker_queue_monitors, for example).
No messages exist in the sys.transmission_queue system table.
Querying the sys.service_queues system table and all rows have:
is_activation_enabled = 0,
is_receive_enabled = 1,
is_enqueue_enabled = 1,
is_poison_message_handling_enabled = 1

Could this be due to poison messages?
I know the basics when it comes to Service Broker, but this one has me stumped.
Here are a few questions I have:

Any other system tables or queries I should use to further diagnose this issue?

It seems the messages are stuck and can't be delivered. Any ideas why this is occurring or what is causing this error?

Any possible solutions to resolve these errors?

SQL Server Logs:

You might want to try Microsoft's built-in ssbdiagnose utility.

The ssbdiagnose utility reports issues in Service Broker conversations or the configuration of Service Broker services. Configuration checks can be made for either two services or a single service. Issues are reported either in the command prompt window as human-readable text, or as formatted XML that can be redirected to a file or another program.

And possibly specifically this part:

To correctly analyze the configuration used by a conversation, run a ssbdiagnose configuration report that uses the same options that are used by the conversation. If you specify a lower level of options for ssbdiagnose than are used by the conversation, ssbdiagnose might not report conditions that are required by the conversation. If you specify a higher level of options for ssbdiagnose, it might report items that are not required by the conversation. For example, a conversation between two services in the same database can be run with ENCPRYPTION OFF. If you run ssbdiagnose to validate the configuration between the two services, but use the default ENCRYPTION ON setting, ssbdiagnose reports that the database is missing a master key. A master key is not required for the conversation.

Alternatively you might want to consider contacting Microsoft and opening a case with them.
Reference: ssbdiagnose Utility (Service Broker) (Microsoft | SQL Docs)