texts:
I am trying to build the multi-level model as demonstrated. When a user creates a campaign they will select some categories and some metros from tables Category and Metro. Those selections will then be placed within two tables as campaign_category and campaign_metro. But upon the creation of those entries I wish for then another table to be populated with the permutation of those combinations within table campaign_category_metro to allow for linking the campaign, the category and metros selected for a user to set a bid price on.

For example say a user creates a campaign that has id 10 and selects category with id 1 and 2, and metros with id 3 and 4. In the tables campaign_category appears:

and in table campaign_metro appears:

In campagin_category_metro I want to have those permuted rows from the tables above as:

Then a user can set a bid on those campaign/metro combinations.

As of now, in creating a campaign, and selecting categories and metros I can get the tables campaign_category and campaign_metro populated but the final table, campaign_category_metro, remains blank. Below is my model as is:

First Level:

class Campaign < ActiveRecord::Base
    has_many :campaign_metros, dependent: :destroy
    has_many :metros, through: :campaign_metros

    has_many :campaign_categories, dependent: :destroy
    has_many :categories, through: :campaign_categories
end

class Category < ActiveRecord::Base
    has_many :campaign_categories, dependent: :destroy
    has_many :campaigns, through: :campaign_categories
end

class Metro < ActiveRecord::Base
    has_many :campaign_metros, dependent: :destroy
    has_many :campaigns, through: :campaign_metros
end

Second Level (populates, but the relation to campaign_category_metro does nothing):

class CampaignCategory < ActiveRecord::Base
    belongs_to :campaign
    belongs_to :category

    has_many :campaign_category_metros, dependent: :destroy
    has_many :campaign_metros, through: :campaign_category_metros
end

class CampaignMetro < ActiveRecord::Base
    belongs_to :campaign
    belongs_to :metro

    has_many :campaign_category_metros, dependent: :destroy
    has_many :campaign_categories, through: :campaign_category_metros
end

Third Level (Nothing shows up upon creation of a campaign):

class CampaignCategoryMetro < ActiveRecord::Base
    belongs_to :campaign_category
    belongs_to :campaign_metro
end

How can I allow for the third level table to be populated with the total permutations of selected category and metro upon the creation of a campaign?


texts: Compare all attributes in M:N relationship

texts:
I am new to database development and I'm sure my current problem is quite common. Unfortunately I couldn't find a solution yet - mainly because I'm not sure how to name it correctly ;) So if there is a solution available already or there is a better way to describe it, please tell me and I'll rename this post.

My problem is that I have a database with an M:N relationship and I'm looking for a way to check if a record already exists.

Example:

Books can have multiple places of publication as well as authors. To avoid duplicates, the database should be checked before a new book is added.
If there is only one author and place of publication the query will return just one record:

But if there are for example two authors, 2 rows of the same book will be returned. To make it even more complicated, let's say there are two books with same title, year of publication and pagecount but different authors, this will be returned:

Thats where my problem starts: I want to check if the record with book_id "2" already exists (obviously without knowing the book_id) and if it exists what its book_id is. In this example the only way to achieve this is to check if both authors names (or IDs to make it a little bit shorter) are associated with one of the books having the destinct values in title, year and pagecount - and I don't have any idea how to do that (ideally in SQL).

So thank you in advance for your help :)


texts: Stored procedure to check for missing fields in a table

texts:
I have a stored procedure that accepts a string parameter, a comma separated list of required column names that the SP should check a particular table and make sure those columns have data in them... If any record has missing data in those columns the sp should return the recordID and which columns having missing data.

I'm having trouble constructing the final query. I already split the comma separated list into a table variable. and am using a cursor to through each required column

DECLARE columnCursor CURSOR
    FOR SELECT * FROM @columnsTable
  OPEN columnCursor

  DECLARE @currentColumnName nvarchar(256);
  FETCH NEXT FROM columnCursor INTO @currentColumnName
  WHILE(@@FETCH_STATUS = 0)
  BEGIN

    ....

     FETCH NEXT FROM columnCursor INTO @currentColumnName
  END


texts: Storing Routes and Locations in Graph using SQL Server 2017

texts:
I'm very new to Graph database, so please ignore my mistakes. My scenario described in following picture:

I have different locations connected to each other with a route between them. Then I have many outlets linked to routes.

I created a RDBMS schema like this:

My goal is to give possible routes and outlets on them between two queried locations.

Recently I heard of Graph database support in SQL Server 2017 and read this article, but can't connect with my current problem.

Please help me to create Graph and node tables in SQL Server 2017.

Thanks!

Update

Now I create a data model:

and some Node and Edge tables as follows:

CREATE TABLE [dbo].[mRoute](
[ID] [int] IDENTITY(1,1) NOT NULL,
[Name] [nvarchar](500) NOT NULL,
PRIMARY KEY CLUSTERED
(
    [ID] ASC
)ON [PRIMARY]
)
AS NODE ON [PRIMARY]
GO

CREATE TABLE [dbo].[mCity](
    [ID] [int] IDENTITY(1,1) NOT NULL,
    [Name] [nvarchar](500) NOT NULL,
PRIMARY KEY CLUSTERED
(
    [ID] ASC
)ON [PRIMARY]
)
AS NODE ON [PRIMARY]
GO

CREATE TABLE [dbo].[mOutlet](
    [ID] [int] IDENTITY(1,1) NOT NULL,
    [Name] [nvarchar](500) NOT NULL,
    [Latitude] [decimal](11, 9) NULL,
    [Longitude] [decimal](11, 9) NULL,
PRIMARY KEY CLUSTERED
(
    [ID] ASC
)ON [PRIMARY]
)
AS NODE ON [PRIMARY]
GO

CREATE TABLE [dbo].[LinkedTo]
AS EDGE ON [PRIMARY]
GO

CREATE TABLE [dbo].[On]
AS EDGE ON [PRIMARY]
GO

Now I am stuck on Between Edge Table, which contains route between two cities.


texts: MySQL read-write Master with many read-only Slaves over shared storage?

texts:
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.


texts: Error 10053 connecting with Management Studio

texts:
While I try to connect to SQL Server, I am getting the following error:

  A connection was successfully established with the server, but then an error occurred during the login process. (provider: TCP Provider, error: 0 - An established connection was aborted by the software in your host machine.) (Microsoft SQL Server, Error: 10053)

How can this be solved? What may be the cause of this issue?


texts: After update triggers that deal with multi-row updates

texts:
Currently working on an database Audit project based on triggers that are fired on update on specific tables. The triggers write the changes into a table; information written are: table name, updated column, timestamp, user, old value and new value.

Triggers work fine with single updates, but when it comes to multi-row updates, it is not working.

My code is like this:

IF (UPDATE(Priority))
BEGIN
    SET @UpdatedColumn = 'Priority'
    INSERT INTO dbo.AuditTable
        ( [TableName] ,
          [Source] ,
          [RecordId] ,
          [User] ,
          [TimeStamp] ,
          [UpdatedColumn] ,
          [OldValue] ,
          [NewValue]
        )
    SELECT
        N'BookingItem' , -- TableName - nvarchar(max)
        (SELECT CODE FROM TBL_LEG_SOURCE
                     INNER JOIN INSERTED INS ON LEG_SOURCE_ID = INS.SourceId) ,
        INS.Id , -- RecordId - bigint
        (SELECT USERNAME FROM INSERTED
                     INNER JOIN TBL_USER
                     ON ModifiedById = USER_ID) , -- User - nvarchar(max)
        GETDATE() , -- TimeStamp - datetime
        @UpdatedColumn , -- UpdatedColumn - nvarchar(max)
        DEL.Priority , -- OldValue - nvarchar(max)
        INS.Priority  -- NewValue - nvarchar(max)
    FROM
        INSERTED INS INNER JOIN DELETED DEL ON INS.Id = DEL.Id
    WHERE
        (
            (INS.Priority <> DEL.Priority)
            OR (INS.Priority IS NULL AND DEL.Priority IS NOT NULL)
            OR (INS.Priority IS NOT NULL AND DEL.Priority IS NULL)
        )
END

Error message:

  Msg 512, Level 16, State 1, Procedure MyTrigger, Line 818
  Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

Any suggestions on how to fix my trigger in order to handle multi-row operations?


texts:
I get this error when trying to create a Job Step:

"Creating an instance of the COM component with CLSID from the IClassFactory failed due to the following error..(Microsoft.SqlServer.ManagedDTS)"

I do not get this error on other machines that have SQL Server.
I checked versions on each machine and all are the same...

Microsoft SQL Server 2008 R2 (SP2) - 10.50.4033.0 (X64)   Jul  9 2014 16:04:25   Copyright (c) Microsoft Corporation  Standard Edition (64-bit) on Windows NT 6.2  (Build 9200: ) (Hypervisor)

I checked this link from Microsoft but does not seem to be a fix for SP2... https://connect.microsoft.com/SQLServer/feedback/details/557402/ssms-can-no-longer-create-or-edit-job-steps


texts: Keepalived vIP as Galera wsrep_cluster_address

texts:
I have a MariaDB Galera cluster.
If some nodes fail, I cannot blindly restart them, I have to determine a good wsrep_cluster_address first.

If I can keep a keepalived virtual IP on one of the healthy nodes, can I use this IP as wsrep_cluster_address on other nodes? So in case of node failure, the joining node would always have a right wsrep_cluster_address? Or are there any other solutions enabling automatic rejoin?

I feel it should be somehow possible to keep the cluster up and automatically rejoin nodes as long as there is at least 1 healthy node (or Primary Component?) up.

(Note: I am aware of the answer in Galera cluster without having to specify all hosts on wsrep_cluster_address, but multicast is unfortunately not an option.)


texts: Cannot start Oracle Enterprise 13C after stopping OMS

texts:
Oracle Software versions installed:

Oracle Grid infrastructure 12C release 2 [12.2.0.1]
Oracle Database 12C release 1 [12.1.0.2.0]
12.1.0.2.0_Database_Template_with_cdbpdb_for_EM13_2_0_0_0_Linux_x64.zip
Enterprise manager 13 [13.2.0.0 with plug-ins]

After successfully installing all of the above packages including the enterprise manager 13C, I decide to stop the enterprise manager services by running the below script:

#!/bin/bash
export ORACLE_HOME=/u01/app/oracle/product/12.2.0.1/db_1
export OMS_HOME=/u01/app/oracle/middleware
export AGENT_HOME=/u01/app/oracle/agent/agent_inst

# Stop everything
$OMS_HOME/bin/emctl stop oms -all
$AGENT_HOME/bin/emctl stop agent
$ORACLE_HOME/bin/dbshut $ORACLE_HOME

Once the enterprise manager 13C services has stopped, I decide to start it again and I get the below error messages:

Oracle Enterprise Manager Cloud Control 13c Release 2
Copyright (c) 1996, 2016 Oracle Corporation.  All rights reserved.
Starting Oracle Management Server...
WebTier Successfully Started
Oracle Management Server Could Not Be Started
Check EM Server log file for details: /u01/app/oracle/gc_inst    /user_projects/domains/GCDomain/servers/EMGC_OMS1/logs/EMGC_OMS1.out
AdminServer Could Not Be Started
Check Admin Server log file for details: /u01/app/oracle/gc_inst/user_projects/domains/GCDomain/servers/EMGC_ADMINSERVER/logs/EMGC_ADMINSERVER.out
Oracle Management Server is Down
JVMD Engine is Down
Starting BI Publisher Server ...
BI Publisher Server Could Not Be Started. Check BIP Server log file for details.
BI Publisher Server Logs: /u01/app/oracle/gc_inst/user_projects/domains/GCDomain/servers/BIP/logs/
AdminServer Could Not Be Started
Check Admin Server log file for details: /u01/app/oracle/gc_inst/user_projects/domains/GCDomain/servers/EMGC_ADMINSERVER/logs/EMGC_ADMINSERVER.out
BI Publisher Server is Down
Oracle Enterprise Manager Cloud Control 13c Release 2
Copyright (c) 1996, 2016 Oracle Corporation.  All rights reserved.
Starting agent ............................................. started.

All I did was stop the enterprise manager 13C Oracle Management Service, nothing else and for some reason I can't start the service again.

If anyone has come across this problem, please help.


texts:
I am having trouble selecting all columns in a sub query and can't seem to get anywhere with joining for replacement.

My Table Data is:

CREATE TABLE `w7nwd_com_mtapp_product_engine` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `product_id` bigint(20) NOT NULL,
  `make` varchar(45) DEFAULT NULL,
  `model` varchar(45) DEFAULT NULL,
  `engine_size` varchar(15) DEFAULT NULL,
  `engine_type` enum('P','D') DEFAULT NULL,
  `engine_code` varchar(155) DEFAULT NULL,
  `year` varchar(15) DEFAULT NULL COMMENT 'This isn''t a standard year, this could be 09-12.',
  `bhp` varchar(45) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `fk_w7nwd_com_mtapp_engine_w7nwd_com_mtapp_product1_idx` (`product_id`),
  CONSTRAINT `fk_w7nwd_com_mtapp_engine_w7nwd_com_mtapp_product1` FOREIGN KEY (`product_id`) REFERENCES `w7nwd_com_mtapp_product` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8;

CREATE TABLE `w7nwd_com_mtapp_product_partnumber` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `product_id` bigint(20) DEFAULT NULL,
  `partnumber` varchar(245) DEFAULT NULL,
  `override_price_new` int(11) DEFAULT NULL,
  `override_price_ex` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `partnumber_UNIQUE` (`partnumber`),
  KEY `fk_w7nwd_com_mtapp_product_alias_w7nwd_com_mtapp_product1_idx` (`product_id`),
  CONSTRAINT `fk_w7nwd_com_mtapp_product_alias_w7nwd_com_mtapp_product1` FOREIGN KEY (`product_id`) REFERENCES `w7nwd_com_mtapp_product` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=utf8;

CREATE TABLE `w7nwd_com_mtapp_product` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=utf8;

CREATE TABLE `w7nwd_com_mtapp_product_oem` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `product_id` bigint(20) NOT NULL,
  `oem` varchar(45) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `fk_w7nwd_com_mtapp_product_oem_w7nwd_com_mtapp_product1_idx` (`product_id`),
  CONSTRAINT `fk_w7nwd_com_mtapp_product_oem_w7nwd_com_mtapp_product1` FOREIGN KEY (`product_id`) REFERENCES `w7nwd_com_mtapp_product` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8;

INSERT INTO `w7nwd_com_mtapp_product` VALUES (1);

INSERT INTO `w7nwd_com_mtapp_product_partnumber` VALUES ('1', '1', '708638', NULL, NULL),('7', '1', '708639', NULL, NULL);

INSERT INTO `w7nwd_com_mtapp_product_oem` VALUES ('1', '1', '345474574545'),('2', '1', '453453454543');

INSERT INTO `w7nwd_com_mtapp_product_engine` VALUES ('1', '1', 'AUDI', 'A5', '2.2', 'D', NULL, '2012', '1400'),
('2', '1', 'FORD', 'C5', '2.2', 'D', NULL, '2012', '1400');

And I tried:

SELECT e.*, p.*,(
SELECT GROUP_CONCAT(`partnumber`)
FROM `w7nwd_com_mtapp_product_partnumber` AS `n`
WHERE `p`.`id`=`n`.`product_id`) as `partnumbers`,(
SELECT GROUP_CONCAT(`oem`)
FROM `w7nwd_com_mtapp_product_oem` AS `n`
WHERE `p`.`id`=`n`.`product_id`) as `oems`
FROM `w7nwd_com_mtapp_product` AS `p`
LEFT JOIN `w7nwd_com_mtapp_product_engine` as `e`
    ON `e`.`product_id` = `p`.`id`;

Here is a sql fiddle with details -- http://sqlfiddle.com/#!2/2481e/1

The link is to the schema with my current results, the issues is I only want one engine to come back and not duplicate all the rows.


texts: which design better when use foreign key instead of a string to store a list of id

texts:
Possible Duplicate:
  Is using multiple foreign keys seperated by commas wrong, and if so, why?

I'm building online examination system. I have designed to table, Question and GeneralExam. The table GeneralExam contains info about the exam like name, description, duration,... Now I would like to design table GeneralQuestion, it will contains the id of questions belongs to a general exam.

Currently, I have two idea to design GeneralQuestion table:

It will have two column: general_exam_id, question_id.
It will have two column: general_exam_id, list_question_ids(string/text).

So, I would like to know which designing is better, or Pros and Cons of each designing. I'm using Postgresql database.


texts:
For MySQL, how to INSERT the result of a SELECT statement? I tried

SET @input1 = 1;
SET @input2 = 2;
INSERT INTO
    my_table (col1, col2)
SELECT
    @input1 AS col1,
    @input2 AS col2
WHERE
    @input1 > 0
        AND
    @input2 > 0;

which gives a message:

  [42000][1064] You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'WHERE @input1 > 0 AND @input2 > 0' at line 6

Any idea how to fix it?

Here is a related article I found on this INSERT-SELECT approach to do conditional insert:

https://timmurphy.org/2013/10/23/conditional-insert-in-sql/

But it seems it does not work for MySQL which requires a FROM clause for SELECT?


texts: Update fails on linked server, Select works

texts:
I am trying to understand why my update statement on my local server is failing on my remote server. I am receiving the following error:

OLE DB provider "SQLNCLI11" for linked server "WEBDB" returned
message "Multiple-step OLE DB operation generated errors. Check each
OLE DB status value, if available. No work was done.". Msg 7341, Level
16, State 4, Line 1 Cannot get the current row value of column
"[SQLNCLI11].AdditionalBarcode" from OLE DB provider "SQLNCLI11" for
linked server "WEBDB". [COLUMN_NAME= AdditionalBarcode
STATUS=Unknown DBBINDSTATUS]

This is my update statement
Update WEBDB SET Additionalbarcode = UDF_ADDITIONALBARCODE FROM
openquery(WEBDB, ' SELECT Id ,[AdditionalBarcode] FROM
...[ProductGroup] where IsActive = 1') WEBDB inner
join LocalDB..Items CI on WEBDB.Id = CI.itemcode where
nullif(UDF_ADDITIONALBARCODE,'') <> isnull(Additionalbarcode,'')

This is my select statement
Select itemcode, UDF_AdditionalBarcode, Id, Additionalbarcode FROM
openquery([remoteserver], ' SELECT Id ,[AdditionalBarcode] FROM
...[ProductGroup] where IsActive = 1') WEBDB inner
join LocalDB..Items CI on WEBDB.Id = CI.itemcode where
nullif(UDF_ADDITIONALBARCODE,'') <> isnull(Additionalbarcode,'')

Why would I receive an error during UPDATE but not select? Any idea on what would be the cause of the error in the update statement?


texts: Is there a tool available that converts queries written as triggers, stored procedures and functions written in MS SQL 2008 to MySql? We switched databases and now I need to rewrite a bunch of queries for MySql and would like to automate the process as much as possible.


texts: Modification_Counter influence the Cardinality Estimator?

texts:
I ran into something that is really mysterious to me and I would need your help to find an explanation (didn't find anything on Google).

Here is the situation. I have 3 tables and in one of them, there is like 40 millions rows with a column having null for all except 3 rows (let's call that column "param". There is a nonclustered index on that column and the statistics are good (something like:

1 NULL 0 3.596004E+07 0 1
2 bcd 0 1 0 1
3 cde 0 1 0 1
4 def 0 1 0 1

I have a query that is executed with "where Param = x".

When I executed the query with a value like "Param = 'cee'", my execution plan does a nonclustered index seek and then a merge join on the other table (with a scan of the cluster index) and it works in a millisecond.

When I run the same query with "where Param = 'abc'", the execution plan changes. It starts with a full scan of the second table (clustered index) and then the merge join on the million rows table (still seek on nonclustered index).

So far so good. Trying different thing, I rebuilt the NC index and right after that, I wasn't able to reproduce the issue. I wasn't gonna stop there! So I start changing values and putting it back to the original value so the Statistics remain good but the modification_counter increased. When the modification_counter reached 10,000, the issue came back!

As the table is huge (40 million rows), 10,000 modifications is not enough to trigger the update of the stats and as I got all of those changes during a day, my maintenance plan can't do anything to prevent the issue.

So what I'm trying to understand is:

How does the modification_counter influence the CE to take a different execution plan.

Please keep in mind that the statistics are always good when I run the query (only 3 rows in the 40 millions with a non null value and always the same 3 values) so it's not the statistics that cause that behavior.

Using SQL Server 2014 but same behavior Under 2016.


texts: Linking Products to Users Via Categories

texts:
I'm trying to match products to users where the users has the same set of categories as the product, here is the schema:

User
———
user_id
...

Users Categories
———
user_id
category_id

Products Categories
———
product_id
category_id

Product
———
product_id
disabled
...

As the the amount of products and users grow the matching query gets slower and slower, this is basically what we have right now:

SELECT *
FROM product
WHERE product.disabled = False
AND EXISTS (
    SELECT 1
    FROM products_categories
    WHERE products_categories.category_id = ANY(
        SELECT category_id
        FROM public.users_categories
        WHERE users_categories.user_id = 'some-user-id')
AND product.id = products_categories.product_id)

This seemed to work ok for some users but is horribly slow for others. This is the explain:

Nested Loop  (cost=4367.50..26918.74 rows=44498 width=1377) (actual
time=694.695..2226.479 rows=87239 loops=1)
  InitPlan 1 (returns $0)
    ->  HashAggregate  (cost=36.01..36.02 rows=1 width=16) (actual time=0.073..0.092
    rows=18 loops=1)
          ->  Bitmap Heap Scan on users_categories  (cost=4.49..35.99 rows=8 width=16)
          (actual time=0.028..0.049 rows=18 loops=1)
                Recheck Cond: (user_id = 'some-user-id'::uuid)
                ->  Bitmap Index Scan on ix_public_users_categories_user_id
                (cost=0.00..4.49 rows=8 width=0) (actual time=0.014..0.014 rows=18
                 loops=1)
                      Index Cond: (user_id = 'some-user-id'::uuid)
  ->  HashAggregate  (cost=4331.06..4413.20 rows=8214 width=16) (actual
  time=694.507..843.826 rows=121099 loops=1)
        ->  Index Only Scan using products_categories_pkey on products_categories
        (cost=0.43..4173.20 rows=63142 width=16) (actual time=0.295..387.771
        rows=234490 loops=1)
              Index Cond: (category_id = ANY ($0))
              Heap Fetches: 117462
  ->  Index Scan using product_pkey on product  (cost=0.42..2.73 rows=1 width=1377)
  (actual time=0.007..0.008 rows=1 loops=121099)
        Index Cond: (id = products_categories.product_id)
        Filter: (NOT disabled)
        Rows Removed by Filter: 0
Total runtime: 2312.743 ms

Is there a better / faster way to perform queries like this? Im guessing I don't have my indexes set up correctly.


texts: SQL Server Restore a SINGLE table from a backup

texts:
on SQL Server 2005, we do a weekly full backup with nightly incremental backups.

I want to know if it is possible to restore a single table from a backup, either to the source database or a different one.

I can not find any clear answer online.

Thanks in advance.


texts:
I've been given credentials to a postgres database that I'm told it has privileges to create new users and grant them access to tables. This is half true.

I wish to create a new user:

create user foo login encrypted password 'bar';
commit;

This is OK. Now I'm trying to that user grant some specific access to selected tables:

grant select, insert, update on table baz to foo;

The response is [42501] ERROR: permission denied for relation baz.

It seems that this user I've been given can't event select from the tables I wish to grant access to. select * from baz also gives a 42501 error.

The query select distinct(grantor) from information_schema.role_table_grants; returns one username (let's call it 'bob'). Should I be speaking with Bob about getting the privs I need?


texts: Get TEXT value of a CLOB OID in Postgresql

texts:
I have a database table that looks like:

create table answers(
   id int not null,
   question_id int not null,
   answer text null
)

This table was originally build by Hibernate using the @Lob attribute for the "answer" column.  I did not realize it at the time, but when setup that way, Hibernate stores an OID in the column instead of the actual text.  Everything works fine when I use Hibernate to retrieve the values since it automatically converts the OID to the CLOB string, however it is becoming a performance problem and I'd like to get rid of the OID.

select * from answers
ID     QUESTION_ID     ANSWER
===============================
1       123             55123
2       234             51614
3       345             56127
should be
ID     QUESTION_ID     ANSWER
===============================
1       123             Male
2       234             203-555-1212
3       345             555 Main St. New York, NY

My desire is to add an extra column to the table "ANSWER_VALUE TEXT" and do something like below to get the actual value into the table, then change Hibernate around to not use the @Lob designator

update answers set ANSWER_VALUE= getValueFromOID(ANSWER)

Does that "getValueFromOID" function exist?  If not, could I get some pointers on how to create one or at least how to fetch the actual value of an OID?

Thanks


texts: Redundancy in table names / Table naming convensions

texts:
I am creating some templates in my database that users would be able to use to help set up some of their data.

For example,

I have the following tables (simplified of course):

store (id, store_name)
product (id, product_name)

product_template(id, template_name)
product_template_product (FK: product_template_id, FK: product_id)

store_product (FK: store_id, FK: product_id)

Ultimately, I am wanting a store to be able to select various products as inventory to their store from the product table. These would be linked to the store in the store_product table.

Rather than the store adding each items one at a time I am also creating a template that will allow them to do this. So for example, they may select the "dairy" template to add a all the dairy products to their store.

In this scenario, "dairy" would be the template name in the product_template table. Then each product linked to this template would be placed in the table product_template_product. So if a user selects this particular template, then all the products in that template would be added to the store_product table.

My question is about the naming of the product_template_product table. It is linking the product_template table with the product table. However, the table name ends up sounding redundant.

Am I just being too pedantic here, or is there a more preferred way to be naming such table structures? I do have a few other tables in my database where similar naming is happening due to templates being used in those places as well.


texts: How to optimize update using join in postgresql

texts:
I'm using PostgreSQL 9.3.5 on a CentOS system. I have a very large table (twenty million rows, 50+ column) that aggregates data from a few hundred systems. Several times a day each of the systems sends me update for that table, and one of those updates is a list of ids for which I need to set a new value in one column. I'm doing that by putting the list of ids in a smaller table and using a join. Most of the time this is pretty fast (under 1 second), but about 10% of the time the update will take anywhere from 3-180 seconds. I'm trying to figure out how to reduce or eliminate those outliers.

remotedata is the very large table; valid_remotedata is the one used for updates.

Table "remotedata"
      Column      |            Type             |                              Modifiers
------------------+-----------------------------+---------------------------------------------------------------------
 system_id        | character varying(32)       | not null default ''::character varying
 id               | bigint                      | not null default 0::bigint
(50 more columns of varying types)
 expire_confirmed | character(1)                | default 'f'::bpchar
Indexes:
    "remotedata_pkey" PRIMARY KEY, btree (system_id, id)

        Table "valid_remotedata"
   Column   |         Type          | Modifiers
------------+-----------------------+-----------
 system_id  | character varying(32) | not null
 id         | bigint                | not null
Indexes:
    "valid_remotedata_pkey" PRIMARY KEY, btree (system_id, id)
    "valid_remote_system" btree (system_id)

Update statement:

UPDATE remotedata r SET expire_confirmed='t'
FROM valid_remotedata vr
WHERE vr.id=r.id and vr.system_id=r.system_id
AND vr.system_id='223344';

explain analyze output:

                                                                   QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------------------
 Update on remotedata r  (cost=353.49..25563.39 rows=11 width=1701) (actual time=8861.469..8861.469 rows=0 loops=1)
   ->  Nested Loop  (cost=353.49..25563.39 rows=11 width=1701) (actual time=15.734..1638.006 rows=71233 loops=1)
         ->  Bitmap Heap Scan on valid_remotedata vr  (cost=352.93..7430.16 rows=2113 width=27) (actual time=15.684..54.007 rows=71233 loops=1)
               Recheck Cond: ((system_id)::text = '223344'::text)
               ->  Bitmap Index Scan on valid_remote_system  (cost=0.00..352.40 rows=2113 width=0) (actual time=15.585..15.585 rows=71233 loops=1)
                     Index Cond: ((system_id)::text = '223344'::text)
         ->  Index Scan using remotedata_pkey on remotedata r  (cost=0.56..8.57 rows=1 width=1695) (actual time=0.020..0.020 rows=1 loops=71233)
               Index Cond: (((system_id)::text = '223344'::text) AND (id = vb.id))
 Total runtime: 8861.651 ms


texts: Optimizing implementation of custom ULID generation in Postgres

texts:
Following the spec github.com/ulid/spec
I'm trying to implement this on Postgres, with some tweaks (without monolitic implementation, no base32):

43 bits allocated to timestamp instead of 48
85 bits allocated to randomness instead of 80

This function is planned to replace uuid generator.
I'm beginner but all theses casts and allocating each byte one by one seems dirty, any way to optimize this ?
Should I switch into C ?
CREATE OR REPLACE FUNCTION gen_custom_ulid() RETURNS bytea AS $$
DECLARE
    unix bit(43);
    --gen_random_bytes from pgcrypto extension
    random_bytes bytea = gen_random_bytes(11);
    ulid bytea = '\x00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00';
BEGIN
    unix := (EXTRACT(EPOCH FROM clock_timestamp())*1000)::bigint::bit(43);

    ulid := SET_BYTE(ulid, 0, (unix)::bit(8)::integer);
    ulid := SET_BYTE(ulid, 1, (unix << 8)::bit(8)::integer);
    ulid := SET_BYTE(ulid, 2, (unix << 16)::bit(8)::integer);
    ulid := SET_BYTE(ulid, 3, (unix << 24)::bit(8)::integer);
    ulid := SET_BYTE(ulid, 4, (unix << 32)::bit(8)::integer);
    ulid := SET_BYTE(ulid, 5, ((unix << 40)::bit(3)||GET_BYTE(random_bytes,0)::bit(5))::bit(8)::integer);
    ulid := SET_BYTE(ulid, 6, GET_BYTE(random_bytes, 1));
    ulid := SET_BYTE(ulid, 7, GET_BYTE(random_bytes, 2));
    ulid := SET_BYTE(ulid, 8, GET_BYTE(random_bytes, 3));
    ulid := SET_BYTE(ulid, 9, GET_BYTE(random_bytes, 4));
    ulid := SET_BYTE(ulid, 10, GET_BYTE(random_bytes, 5));
    ulid := SET_BYTE(ulid, 11, GET_BYTE(random_bytes, 6));
    ulid := SET_BYTE(ulid, 12, GET_BYTE(random_bytes, 7));
    ulid := SET_BYTE(ulid, 13, GET_BYTE(random_bytes, 8));
    ulid := SET_BYTE(ulid, 14, GET_BYTE(random_bytes, 9));
    ulid := SET_BYTE(ulid, 15, GET_BYTE(random_bytes, 10));

    RETURN ulid;
END $$ LANGUAGE plpgsql VOLATILE PARALLEL SAFE;


texts: The database our developers are working on is too large (have a lot of database objects). We have to control db objects changes (change management). Our company cannot have a person who would be responsible for db changes only. So we need a source safe for database objects, something like version control for standard code, but more related to database, that can synchronize database and scripts. What is the best one. Reliable, Cheap, Functional - choose the two ones :)


texts: In sqlite, how are return values chosen for columns that are not used for aggregation or grouping?

texts:
Given an SQLite database with three columns (char, char, int), the following query

select column2, avg(column3) from table_name group by column2;

will return the average values of column3 aggregate for each distinct value of column2.

What will the following query return?

select column1, column2, avg(column3) from table_name group by column2;

I came across this case while playing with Codeacademy's SQL tutorial. This particular query returns a value of column1 for each distinct value of column2, but I can't figure out how these values were chosen.


texts: Is a clustered index locked while a (clustered index) scan is in progress?

texts:
The question is pretty simple, as the title says. However, I cannot find any references to confirm or contradict this.

The problem leading me to this question is that I have a view joining several other views and tables. One of these tables (let's call it table A) did not have a covering non-clustered index on columns in join conditions and a clustered index scan was issued instead.

The effect was that all other queries that also used table A AND used a clustered index scan to be made were waiting after the first query (no deadlock yet, since I have a large timeout). As soon as I created a non-clustered index on table A for the join columns, the clustered index was no longer used and queries started working fine in parallel.

Anyway, if my assumption is correct, is there a way to detect the lock? I haven't tried yet enabling any locking-related trace flags? I only used 'sp_who2' and a few other queries found around here for now.

If you have stumbled across this before, then please share your thoughts.


texts: Using a linked server with OPENQUERY in a database project

texts:
I have a SQL Server 2008 running a database I want to throw in TFS. Therefore I used a Visual Studio 2013 database project where I imported the DB. After fixing a bunch of errors I'm stuck with only one error left:

In one view the devs used OPENQUERY to access a linked server. So I imported a DACPAC which contains the right database and added it to the project by using Add Database Reference using the following reference options.

Initial Script Version

Here is a shorter version of the original view creation:

CREATE VIEW dbo.vwStatus
AS
SELECT     StatusID, StatusName
FROM       OPENQUERY(LinkedServer, 'SELECT * FROM [DB].[dbo].tbStatus') AS derivedtbl_1

This lead to the following error:

  Error 136 SQL71501: View: [dbo].[vwStatus] has an unresolved reference to object [LinkedServer].

First Attempt

So I tried to insert the server name variable

FROM       OPENQUERY($(LinkedServer), 'SELECT * FROM [DB].[dbo].tbStatus') AS derivedtbl_1

Which leads to

  Error 176 SQL46010: Incorrect syntax near $(LinkedServer).

Further Attempts

I fiddled arround a bit and tried the following (with and without having quoted identifiers enabled):

FROM       OPENQUERY("$(LinkedServer)", 'SELECT * FROM [DB].[dbo].tbStatus') AS
FROM       OPENQUERY([$(LinkedServer)], 'SELECT * FROM [DB].[dbo].tbStatus') AS
FROM       OPENQUERY([LinkedServer], 'SELECT * FROM [DB].[dbo].tbStatus') AS
FROM       OPENQUERY("LinkedServer", 'SELECT * FROM [DB].[dbo].tbStatus') AS

I am always getting an error.

I have no clue what I'm overlooking here. Do you? Thanks for your time!

(Sadly I can't add the visual-studio-2013 tag, so I used visual-studio)


texts:
The following query on a foreign takes about 5 seconds to execute on 3.2 million rows:

SELECT x."IncidentTypeCode", COUNT(x."IncidentTypeCode")
FROM "IntterraNearRealTimeUnitReflexes300sForeign" x
WHERE x."IncidentDateTime" >= '05/01/2016'
GROUP BY x."IncidentTypeCode"
ORDER BY 1;

When I execute the same query on normal table, it returns in .6 seconds. The execution plans are quite different:

Normal Table

Sort  (cost=226861.20..226861.21 rows=4 width=4) (actual time=646.447..646.448 rows=7 loops=1)
  Sort Key: "IncidentTypeCode"
  Sort Method: quicksort  Memory: 25kB
  -> HashAggregate (cost=226861.12..226861.16 rows=4 width=4) (actual  time=646.433..646.434 rows=7 loops=1)
     Group Key: "IncidentTypeCode"
     -> Bitmap Heap Scan on "IntterraNearRealTimeUnitReflexes300s" x  (cost=10597.63..223318.41 rows=708542 width=4) (actual time=74.593..342.110 rows=709376 loops=1)
        Recheck Cond: ("IncidentDateTime" >= '2016-05-01 00:00:00'::timestamp without time zone)
        Rows Removed by Index Recheck: 12259
        Heap Blocks: exact=27052 lossy=26888
        -> Bitmap Index Scan on idx_incident_date_time_300  (cost=0.00..10420.49 rows=708542 width=0) (actual time=69.722..69.722 rows=709376 loops=1)
           Index Cond: ("IncidentDateTime" >= '2016-05-01 00:00:00'::timestamp without time zone)

Planning time: 0.165 ms
Execution time: 646.512 ms

Foreign Table

Sort  (cost=241132.04..241132.05 rows=4 width=4) (actual time=4782.110..4782.112 rows=7 loops=1)
  Sort Key: "IncidentTypeCode"
  Sort Method: quicksort  Memory: 25kB
  -> HashAggregate  (cost=241131.96..241132.00 rows=4 width=4) (actual time=4782.097..4782.100 rows=7 loops=1)
     Group Key: "IncidentTypeCode"
     -> Foreign Scan on "IntterraNearRealTimeUnitReflexes300sForeign" x  (cost=10697.63..237589.25 rows=708542 width=4) (actual time=1.916..4476.946 rows=709376 loops=1)

Planning time: 1.413 ms
Execution time: 4782.660 ms

I think I'm paying a high price for the GROUP BY clause, which isn't passed to the foreign server when I EXPLAIN VERBOSE:

SELECT
    "IncidentTypeCode"
FROM
    PUBLIC ."IntterraNearRealTimeUnitReflexes300s"
WHERE
    (
        (
            "IncidentDateTime" >= '2016-05-01 00:00:00' :: TIMESTAMP WITHOUT TIME ZONE
        )
    )

This returns 700k rows. Is there a way around this?

I spent a lot of time reading this documentation page yesterday, and thought I had found my answer with setting use_remote_estimate to true, but it had no effect.

I do have access to the foreign server to create objects if necessary. The timestamp value in the WHERE clause can be anything; it does not come from a list of predefined values.


texts: Why would you distribute replica shards across nodes and not mirror replicas?

texts:
I'm trying to understand how replicas work in Elasticsearch.

This is a very basic question, but can someone explain why one node is not just all replicas? Surely in this example, if one node fails 50% of the shards will be lost?


texts: Why is the auto created statistic on this column empty?

texts:
Info

My question relates to a moderately big table (~40GB data space) that is a heap
(Unfortunately, I am not allowed to add a clustered index to the table by the application owners)

An auto created statistic on an Identity column (ID) was created, but is empty.

Auto create stats & auto update stats are on
Modifications have happened in the table
There are other (auto created) statistics that are getting updated
There is another statistic on the same column created by an index (duplicate)
Build: 12.0.5546

The duplicate statistic is getting updated:

The actual question

To my understanding, all stats could be used and modifications are tracked, even if there are two statistics on exactly the same columns (duplicates), so why does this statistic remain empty?

Stats Info

DB stat info

Table Size

Column Information where the statistic is created on

[ID] [int] IDENTITY(1,1) NOT NULL

Identity column

select * from sys.stats
where name like '%_WA_Sys_0000000A_6B7099F3%';

Auto created

Getting some info on another statistic

select * From sys.dm_db_stats_properties (1802541555, 3)

In comparison with my empty stat:

Stats + Histogram from "generate scripts":

/****** Object:  Statistic [_WA_Sys_0000000A_6B7099F3]    Script Date: 2/1/2019 10:18:19 AM ******/

    CREATE STATISTICS [_WA_Sys_0000000A_6B7099F3] ON [dbo].[table]([ID]) WITH STATS_STREAM = 0x01000000010000000000000000000000EC03686B0000000040000000000000000000000000000000380348063800000004000A00000000000000000000000000

When creating a copy of the stats, no data is inside

CREATE STATISTICS [_WA_Sys_0000000A_6B7099F3_TEST] ON [dbo].[table]([ID]) WITH STATS_STREAM = 0x01000000010000000000000000000000EC03686B0000000040000000000000000000000000000000380348063800000004000A00000000000000000000000000

When manually updating the stat they do get updated.

UPDATE STATISTICS [dbo].[Table]([_WA_Sys_0000000A_6B7099F3_TEST])


texts: How to run INSERT INTO AS SELECT in parallel on Oracle?

texts:
I have a very large table AUD$ that I want to copy it to another empty one with the same structure AUD_BACKUP.

INSERT INTO TEST_USER.AUD_BACKUP
    SELECT * FROM SYS.AUD$;
COMMIT;

How do I use parallel for INSERT as well as for SELECT ?
I can afford to run in 100 parallels. I am on Oracle db 12.2.


texts: Does Postgres offer a feature like “NEWSEQUENTIALID” in MS SQL Server to make UUID as primary key more efficient

texts:
Microsoft SQL Server offers the NEWID command to generate a new GUID (the Microsoft version of UUID) value that can be used as a primary key value (in their uniqueidentifier data type). These are not sequential in nature, so updating an index can be inefficient.

Alternatively, MS SQL Server offers the NEWSEQUENTIALID command. To quote their documentation:

  Creates a GUID that is greater than any GUID previously generated by this function on a specified computer since Windows was started. After restarting Windows, the GUID can start again from a lower range, but is still globally unique. When a GUID column is used as a row identifier, using NEWSEQUENTIALID can be faster than using the NEWID function. This is because the NEWID function causes random activity and uses fewer cached data pages. Using NEWSEQUENTIALID also helps to completely fill the data and index pages.

Is there a way to get the more efficiently-indexed UUID in Postgres?


texts:
In SQL Server you can make sure that the engine uses the whole extent for a single object (i.e. a table). I know how to enable the trace 1118, but that only applies to newly written data as I recall.

How can I make sure that all the data in the database is using uniform extents instead of mixed extents?

Can it be done by rebuilding the clustered indexes, and when not present creating and dropping a clustered index?


texts: Cloning a PDB from one CDB into another CDB with TDE enabled at source and target

texts:
What are the possible ways to Clone a PDB from one CDB into another CDB (in a different host) with TDE enabled at both source and target.

At present i am able to think of the following two

a) Data pump export and import

b) Unplugging in source and plugging into target

Option a) requires creation of tablespaces manually before import at target pdb and is quite slow since the data we have is huge.

Option b) is easy without TDE but with TDE their is a concept of exporting and importing encryption keys , and what ever i have used it till now it becomes quite complex during plugging and importing keys .

Can someone suggest and alternative way to achieve the mentioned requirement or may be a way to do a) or b) with more ease and accuracy .


texts:
I have been given a database dump that I am trying to rebuild. When I import I get the following error:

Too many key parts specified; max 16 parts allowed

There are indeed more than 16 key parts in the dump.

I have read and understand the limitations of InnoDB, what I don't understand is how the original database was able to create a key with more than 16 parts.

Here is the relevent part of the dump:

CREATE TABLE `v1_view` (
  `view_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `view_date` date NOT NULL,
  `account_id` int(9) unsigned NOT NULL DEFAULT '0',
  ....
  PRIMARY KEY (`view_id`),
  UNIQUE KEY `viewUnique` (`view_date`,`account_id`,`player_id`,`video_id`,`order_id`,`orderlist_id`,`feed_id`,`domain_id`,`location_id`,`city_id`,`region_id`,`dma_id`,`country_id`,`continent_id`,`browser_id`,`platform_id`,`os_id`,`size_id`,`autoplay`,`volume`,`pacing`,`ad_spend`,`ad_revenue`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

A couple of thoughts I had:

It was created with an older version of MySQL that allowed more key parts
It is using a different ENGINE even though the dump says InnoDB

Not sure how valid either of those are. I don't have direct access to the original DB but can probably get more info if needed.

How is it that the original database has a unique key with so many parts? How do I replicate this database?


texts: Incorrect uniqueidentifier works without a Primary Key but fails with one

texts:
So we were recently running some logic tests on an insert into a table with a uniqueidentifier as a primary key. Here's my test query;

IF OBJECT_ID('tempdb..#DatabaseTable') IS NOT NULL DROP TABLE #DatabaseTable
CREATE TABLE #DatabaseTable
        ([ID] [uniqueidentifier] NOT NULL PRIMARY KEY,
        [LastUpdate] [datetime] NOT NULL,
        [Locked] [tinyint] NOT NULL)

IF OBJECT_ID('tempdb..#temptable') IS NOT NULL  DROP TABLE #temptable
CREATE TABLE #temptable (ID varchar(36), Data nvarchar(max))

INSERT INTO #temptable (ID, Data)
VALUES ('g078f19e-e150-4bb9-b5f4-b20b3fc64016', 'text goes here')

SELECT tmp.ID ,tmp.Data
FROM #temptable tmp
LEFT JOIN #DatabaseTable db
    ON tmp.ID = db.ID
WHERE db.ID is null

This fails because the uniqueidentifier is not valid as it contains an incorrect value of 'g' that sits outside the acceptable range for this data type. This happened as a developer had altered it manually for testing as he knew that it wouldn't exist in the destination table but wasn't aware of the valid character list.

This is the error that it throws;

(1 row(s) affected)
Msg 8169, Level 16, State 2, Line 13
Conversion failed when converting from a character string to uniqueidentifier.

This is the behaviour that I would expect. However, when messing about in testing I removed the primary key on #DatabaseTable and it runs totally fine. Now I examined the execution plan expecting it to convert the uniqueidentifier to match the varchar(36), however I see this compute scalar;

CONVERT_IMPLICIT(uniqueidentifier,[tempdb].[dbo].[#temptable].[ID] as [tmp].[ID],0)

I can't seem to find any documentation from Microsoft on the actual syntax of CONVERT_IMPLICIT so I am assuming that the final zero is what it is replaced with if the convert fails, would this be a correct assumption (same as ISNULL/COALESCE)? Also, why does this work fine without the PK but fails with it?


texts:
I have two tables:

Contact (ID, Type);
Address (ID).

Is it possible to define a foreign key from Address (ID,'A') to Contact (ID,Type)?


texts: Will my sqlite database occupy less space if I insert nulls instead of zeros

texts: I have a table where most of the values inserted in one of the columns are 0. Should the database be smaller if I insert nulls instead of zeros?


texts: SQL Server 2008 ignoring QUERYTRACEON 4136 and ARITHABORT ON

texts:
A slow Stored Procedure is extremely faster (going from 2 secs to 8 millisecs) with QUERYTRACEON 4136 and ARITHABORT ON, on the local SQL Server 2008 I use to develop. The "fixed" SP, on another SQL Server 2008, goes back to slowness: with or without QUERYTRACEON 4136 and ARITHABORT ON, it doesn't make any difference.

Removing any fix and just adding WITH RECOMPILE gives me back the fast performances, so it's clear that the server is ignoring both fixes.

I didn't install that second server, while I installed my local dev server and it's "out of the box": I didn't touch any configuration. What I should check on the other server, what could cause it to ignore QUERYTRACEON and ARITHABORT ON?

An additional weirdness with ARITHABORT ON

Maybe this could help to figure out what's going on. Or maybe it will simply mud things even more.

This way:

SET ARITHABORT ON
SET NOCOUNT ON

ARITHABORT is ignored. Even using WITH RECOMPILE.

This way:

SET NOCOUNT ON
SET ARITHABORT ON

ARITHABORT is not ignored.


texts:
I've start using temporal tables on SQL Server 2017 and I've noticed that rapid updates aren't recorded in the history table. Does anyone know if this is configurable or do I just have to live with this limitation.

More info...

For testing I have a Table setup with a single row

In SSMS If I do something like this:

UPDATE MyTable SET MyCol = 'a'
UPDATE MyTable SET MyCol = 'b'
UPDATE MyTable SET MyCol = 'c'

The History Table doesn't have any records for 'a' and 'b' changes.

I repeated this with a short delay between the updates like this:

UPDATE MyTable SET MyCol = 'a'
WAITFOR DELAY '00:00:00.400'
UPDATE MyTable SET MyCol = 'b'
WAITFOR DELAY '00:00:00.400'
UPDATE MyTable SET MyCol = 'c'

And this results in the 'a' and 'b' being recorded in the history. However the lower the delay the less likely the records are to appear.

UPDATED:

Here a full script to show the issue. With the delays in place the select return 4 rows, with the delays commented out only one row is returned

-- Create Table
CREATE TABLE [dbo].[MyTable](
    [Id] [uniqueidentifier] NOT NULL,
    [MyCol] [nvarchar](max) NOT NULL,
 CONSTRAINT [PK_MyTable] PRIMARY KEY CLUSTERED
(
    [Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY];

-- Add History
ALTER TABLE  [dbo].[MyTable] ADD
    SysStartTime datetime2(0) GENERATED ALWAYS AS ROW START HIDDEN NOT NULL,
    SysEndTime datetime2(0) GENERATED ALWAYS AS ROW END HIDDEN NOT NULL,
    PERIOD FOR SYSTEM_TIME (SysStartTime, SysEndTime);

ALTER TABLE  [dbo].[MyTable] SET (SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.MyTable_History));

-- Add a Single Row
INSERT INTO [dbo].[MyTable] (Id, MyCol)
VALUES ('2a2979d2-529a-442b-b272-3662feda731b', 'init');

-- Update the Row Multiple times,  Comment out the Delays or lower them to under around 400ms to see inconsistent results
UPDATE MyTable SET MyCol = 'a';
WAITFOR DELAY '00:00:01'
UPDATE MyTable SET MyCol = 'b';
WAITFOR DELAY '00:00:01'
UPDATE MyTable SET MyCol = 'c';

-- Query the history
SELECT * From [dbo].[MyTable] FOR SYSTEM_TIME ALL;

-- Remove the test tables
ALTER TABLE [dbo].[MyTable] SET(SYSTEM_VERSIONING = OFF);
DROP TABLE [dbo].[MyTable_History];
DROP TABLE [dbo].[MyTable];


texts: how to install PL/SQL Developer on centos

texts: Can we install and run PL/SQL Developer on CentOS 7 server? I couldn't find installation documents anywhere. If the installation is possible can someone guide me on this.


texts: How to distinguish the current operation is done from system or from sql server management studio

texts:
We want to record who insert or update a table, so we add "CreatedBy" and "ModifiedBy" columns.

When the data is changed through the application, we can easily get the person. But when someone uses SQL Server Management Studio, how can I get the right person who operated on it?

Can I use trigger to catch it?


texts: Filter mysql group by negative having contains

texts:
How can I get all of the values from 1 column, which do not (or do) have a specific value in one of their rows?

For instance, consider example table:

| ColA | ColB |
---------------
| aaa1 | zzz1 |
| aaa1 | zzz2 |
| aaa1 | zzz1 |
| aaa2 | zzz3 |
...

I want to get each of the values from ColA, where the set group by ColA does not contain a row with a specific value in ColB. i.e.

select ColA from test group by ColA not containing ColB like 'zzz2'

Unfortunately, as far as I know there isn't a containing keyword in MySQL. So what can I do instead? Is there some way to tell having to filter by another column?


texts:
I am wondering what is the best practice to use the right query in my case.

The problem is I tried the LIKE and ILIKE and it was faster then pg_trgm and the postgres full text search, because they often run in a timeout or throw an error:

NOTICE:  word is too long to be indexed
DETAIL:  Words longer than 2047 characters are ignored.

So I am not sure, if I did it right and my table looks like this and has over 10.000 entries:

  Column   |     Type      | Modifiers
-----------+---------------+-----------
 id        | integer       | not null
 doc       | bytea         | not null
 textdoc   | text          | not null

So I have the document (PDF or DOCX) and I give it to the converter which makes a huge string out of the documents and put it into the textdoc. A document can have 150 pages or 40.000 words(strings). So in each textdoc can be a huge amount of words. And when I have over 10.000 entries in the database and each of this entry has a huge amount of words in textdoc, how can I speed this up.

So my actual query looks (simplified) like this:

SELECT * FROM mytable WHERE textdoc ILIKE '%word from input mask%'

When I am searching the web, the standard message is don't use ILIKE. I used pg_trgmand it wasn't really faster. It was all about three minutes for a query.

Required information:

Database: "PostgreSQL 10.4 on x86_64-pc-linux-gnu, (Red Hat), 64-bit"

Analyze (hope I did it right).. I run:

EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM mytable WHERE textdoc ILIKE '%word from input mask%'

Here is the output: https://explain.depesz.com/s/wqLh

Another question is, the notice of the 2047 characters: Does this mean, that a the text in the column textdoc is over 2047 or a string in the textdoc is over 2047 characters?


texts: Attaching / creating database on network drive - Access is denied

texts:
I have two computers, computer A is with SQL Server but has no space left, the other is computer B which has free space.
I'd like to create a new database on computer B. This is what I've done so far:

I've created a shared folder called shared on computer B, and mapped it on computer A as a network drive X:/ (or also \ADMIN-PC\shared).
set privileges on the folder so that Everyone can do everything
I've checked that I can access the drive X from cmd
I've run SQL Server Management Studio as Administrator, and logged in using Windows Authentification
then I've tried to create database

CREATE DATABASE [networked] ON  PRIMARY
( NAME = N'networked', FILENAME = N'\ADMIN-PC\shared\networked.mdf' , SIZE = 3072KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB )
LOG ON
( NAME = N'networked_log', FILENAME = N'\ADMIN-PC\shared\networked_log.ldf' , SIZE = 1024KB , MAXSIZE = 2048GB , FILEGROWTH = 10%)
GO

Which gives me this

Directory lookup for the file "\\ADMIN-PC\shared\networked.mdf" failed with the operating system error 5(Access is denied.)

I've also tried to attach existing database on the network drive, with same result. How should I set the privileges so that I can create/connect to database on network drive?


texts: Effects of turning the transaction safety OFF

texts:
I am considering to turn off the transaction safety to maximize the performance.

Before that i am interested in finding the current status of transaction safety. Is there any command that we can get the status ?

I want to know the real use of turning this off. I found that it could increase performance. please let me know if there are any other advantages

http://msdn.microsoft.com/en-IN/library/ms189061.aspx

and also I would like to know if there are any disadvantages with turning  off the transaction safety.


texts: How to safely take an SQL job out of the schedule?

texts:
There is a table | jobID | status | startTime | endTime | and a job, that is scheduled to run every X seconds, it will take the first undone job ID, put it in stored proc and run it. Stored proc takes 1-2 minutes to run.

The users can:

Add jobs to the table described above
Take them out of the schedule

I can think of at least 2 risks that need to be avoided:

Start a job just after someone has requested to take the job with this very ID out of the schedule
Stop the wrong job: job stopping request was placed when the 'requested-to-stop' job was about to be completed, and is being executed while the next job, the one that we want to complete, is already running.

Performace is not too much of an issue, but, for some reason, locking the whole| jobID | status | startTime | endTime | table takes more than 30sec, and it times out.

Updating status column is the obvious solution, but, if the timings of status changing and job staring coincide, there will be a problem (I guess my problem is simuilar to multithreading problem).

What is the best way to solve it? I think is this must be a very common problem, but I cannot think of the right key words to google it. Many thanks in advace!


texts:
I have an index reorganize job on SQL Server 2012 that fails because page level locking is disabled on it.  My three questions are:

Why would page level locking be disabled on an Index?

 If I go ahead and make changes and allow page level locking, it is not going to break anything, right?

Is there a script to make all the indexes allow page level locking?


texts: What does 'Could not find a log backup file that could be applied' means on log shipping?

texts:
Skipped Log Backup File ..... Could not find a log backup file that
  could be applied to secondary Database

What does this mean?

I've configured a log shipping. To test, I created 2 tables, put values on them, created another 2 and etc.

During the first run of the job, is says the message above.

Later, let's say after 2 or 3 runs, the log shipping starts to modify the secondary database.

Why this happens during the firsts runs?

Even if I use checkpoint I see that error.

In this post Here Says something:

  The Restore job finds no suitable log files to apply in location B
  because they are all being backed up to location A and NOT being
  copied over to B (due to the Copy job being disabled).

I'm with 2 windows oppened and I can see both jobs working ( I can see the backup log job ( because I can see files being created )), and I can see the copy is working too ( files are being created on the second window too).

And, a bonus question...I want to log shipping from SQL 2012 to SQL 2014. So, I need to use "no recovery" option right? because I can't use stand by with a higher version on the secondary. Or am I wrong?


texts: Any disadvantage to using oraToolkit for installing oracle

texts: I am going to install oracle 11gR2 on a redhat enterprise 5.6 machine. Is there any disadvantage to using oraToolkit? For example, could it cause any problem or difficulty for future complimentary installations, like data guard, or RAC?


texts: SQL Server logical consistency-based I/O error

texts:
I have a job on my live environment DB and the job has failed again this morning reporting SQL Server Error 824 (Soft I/O error, where the OS could read the page but SQL has treated it as corrupt):

  SQL Server detected a logical consistency-based I/O error: incorrect checksum it occurred during a read of page (1:9545681) in database ID 5 at offset 0x0000123615a000 in file 'D:XXX\XX.mdf'

I have had a look and ran DBCC CHECKDB with all_errormsgs and saw the error didn't mention CHECKSUM failure, however it reports the repair level needed to repair this error is repair_allow_data_loss.

Is there anything else that can be done other than running the repair SQL Server has recommended since I don't want any data loss?

I have a good backup policy in place, however the backup that was last working when the job ran without any problems has now been deleted due to space issues. Is there anything else I can do now?


texts:
I have this data:

CREATE TABLE tickets(user_id int NOT NULL);
INSERT INTO tickets VALUES (1);
INSERT INTO tickets VALUES (2);
INSERT INTO tickets VALUES (3); -- 3 times
INSERT INTO tickets VALUES (4); -- 10 times

Now I want to display the percentage of the number of tickets per user.

I tried this:

WITH number_of_tickets AS (
  SELECT user_id, COUNT(user_id) AS number_of_tickets_per_user
  FROM tickets
  GROUP BY user_id
)
SELECT
  number_of_tickets_per_user,
  ROUND((COUNT(user_id) * 100.0) / (SELECT COUNT(DISTINCT(user_id)) FROM tickets), 3) -- No no no no
FROM number_of_tickets
GROUP BY number_of_tickets_per_user
ORDER BY number_of_tickets_per_user;

But I probably don't handle well the percentage calculation. The result always show me 25% for each of number of ticket per user.

Thanks


texts: Restrict SQL Server 2008 R2 User to restore only one database (but not others)

texts: It is my understanding that a user has to be a "dbcreator" in order to restore databases. However, what if I want a specific user to restore only one specific database, but not gain access or restore any other database on the server? Doesn't "dbcreator" allow that user to essentially restore any database?


texts: Database Design: separating reusable & instance data

texts:
I'm developing an app which is basically a collection of checklists that have to be processed. I obviously need a database in which the information has to be stored. I just can't wrap my head around designing the database model.

Following should be considered:

A checklist-item describes what needs to be checked and should be able to take in different types of input, like simply checking off, radio input (yes/no), some textinput or a dropdownlist of possible values
A category consists of 0..* sub-categories and/or 0..* items
A checklist consists of 1..* categories

Obviously these 3 entities have more information like title and description for all of them or modified_by and modified_on for the items, but I'm going to leave that out to keep it short.

The categories, items and some checklists will be predefined and managed from a webinterface

When an item or a category is updated or deleted, this changes should effect all active checklists that contain these
A collection of checklist-templates has to be stored, to be provided in the app

In the app:

Every user can create a checklist by selecting a checklist-template or combining pre-defined categories in the app
The created checklist can then be worked on, which just means finishing all items

This is a rough mockup of the first draft I came up with.

Eventually I realized, that this doesn't work because there is no separation between the predefined data for reuse and the values that originate from working on a checklist. I figured I need to split the data. this is what I came up with.

This way I'd save the changing data for the checklists and items separately from the information that is being reused in multiple checklists. Also I'd add a is_template column to the checklist which would enable me to save all predefined and user-created checklists in one table and possibly mark a checklist as a template afterwards, if proven useful.

Now to finish off with some questions:
Is there a flaw that I'm missing in this approach?
Does somebody know a better way to design this?
Did it even become clear what I'm trying to do and why I'm having issues with this?

Please let me know. It's been a while since I did database modeling and this is supposed to be only the beginning of a larger tool, so it needs a good foundation.


texts: How to find locking schema for set of tables in database?

texts:
There are some 20 tables in a database of Sybase instance, where i need to find the locking schema of tables.
Once locking schema is found, i need to compare it in other database which also have same 20 tables.
build-ASE16 SP2


texts: How do you unescape "C-like backslash escape sequence" that come from a function?

texts:
This will unescape the \n\n sequence.

SELECT E'\n\nFOO';

But if that same sequence comes from a function, what do you do.

CREATE FUNCTION asdf() RETURNS text
AS $$
  SELECT '\n\nFOO'::text
$$ LANGUAGE 'sql';


texts: What is the best way to find out how many records do not have a proper phone number in SQL Server?

texts:
I have a field that people historically could use to freely supply a value. I need to identify the records which do not have an appropriate value so I can clean them.

I've been looking around in SSIS for this. My inclination is to use a script component with a regex expression in some C# code, followed by a redirect. Still, I was wondering if there was a way in SSIS to do this without resorting to C#.

I haven't had a lot of luck finding a way to find out how many records do not have appropriate phone numbers.


texts: How to Add AFTER Delete Trigger in My case?

texts:
I am Making a Database Management System of Hospital in MySQL WorkBench, and i Want to Make a After Delete Trigger in my case. The scenario is i have two Tables

1) The Patients which are currently admitted in the Hospital, that Table is represented as resident

and

2) This is The Table where i keep Records of the Outgoing Patient (i.e the Patient who successfully Undergoes treatment and walks out of Hospital) as outpatient.

Now, in the Resident table i have a column called residentID which distinguishes the Patients who are there by Just there Unique Number, and In outpatient i keep the records by OutpatientID of which patient who left Hospital.

So I want to make a Trigger in Such a way that Whenever i Write a DELETE FROM resident where residentID = 1 than the Person's ID who just got Deleted from resident table comes into Outpatient Table. So how do we make Such Trigger which does this, I am pretty much quizzed here. This is what i want, any Inputs would be highly appreciated.


texts: Storing and retrieving raw XML in Oracle table

texts: I need to store and retrieve raw SOAP XML in Oracle. What is the simplest way to do that? Please give your suggestions.


texts:
A fictional case: a participant is asked which days of the week s/he goes jogging or swimming. The answer can be Mon, Tue, Wed, Thu, Fri, Sat, Sun as well as None, RF (Refuse to answer), DK (Don't know). A participant can choose any day or a combination of them. But if a participant chooses None, RF or DK, other options cannot be selected. How do I model this in an table, ensuring data integrity.

An example:

+-------------+----------+------+
| Participant |   Type   | Day  |
+-------------+----------+------+
| Alice       | Jogging  | Mon  |
| Alice       | Jogging  | Tue  |
| Alice       | Swimming | Wed  |
| Alice       | Swimming | Thu  |
| Bob         | Jogging  | Tue  |
| Bob         | Jogging  | Wed  |
| Bob         | Swimming | Tue  |
| Bob         | Swimming | Wed  |
| Bod         | Swimming | Sun  |
| Chuck       | Jogging  | Mon  | <-- A*
| Chuck       | Jogging  | Mon  | <-- A*
| Chuck       | Jogging  | Tue  |
| Chuck       | Jogging  | Sun  |
| Chuck       | Swimming | Sun  | <-- B*
| Chuck       | Swimming | None | <-- B*
| Dan         | Jogging  | Mon  | <-- C*
| Dan         | Jogging  | RF   | <-- C*
+-------------+----------+------+

A* - A unique constraint on (Participant, Type, Day) can prevent this duplication.

B*, C* - How can these be prevented if a participant choose None or RF, no other options can be entered.

Another example would be diet choices. Options include: low fat, low carb, gluten free, vegan, seafood, etc. A combination of low fat, low carb, gluten free is possible. But a combination of vegan + seafood is not possible.


texts: Why does batch mode window aggregate yield arithmetic overflow?

texts:
The following query performs a windowed SUM over a columnstore table with 1500 total rows, each of which has the value 0 or 1, and it overflows the INT data type. Why is this happening?

SELECT a, p, s, v, m, n,
    SUM(CASE WHEN n IS NULL THEN 0 ELSE 1 END)
        OVER (PARTITION BY s, v, a ORDER BY p) AS lastNonNullPartition
FROM (
    SELECT a, p, s, v, m, n,
        RANK() OVER (PARTITION BY v, s, a, p ORDER BY m) AS rank
    FROM #t /* A columnstore table with 1,500 rows */
)  x
WHERE x.rank = 1
--Msg 8115, Level 16, State 2, Line 1521
--Arithmetic overflow error converting expression to data type int.

Full script

See this file for a fully contained reproduction script.

Query plan

Here is an annotated estimated query plan (full XML on Paste the Plan).

Similar queries that execute successfully

If any of the following modifications are made, the error does not occur:

Use trace flag 8649 to prefer a parallel plan regardless of the cost threshold for parallelism
Use trace flag 9453 to disable batch mode
Use the COUNT aggregation function instead of the SUM function
Remove the WHERE x.rank = 1 predicate

For example, this query executes successfully:

SELECT a, p, s, v, m, n,
    SUM(CASE WHEN n IS NULL THEN 0 ELSE 1 END)
        OVER (PARTITION BY s, v, a ORDER BY p) AS lastNonNullPartition
FROM (
    SELECT a, p, s, v, m, n,
        RANK() OVER (PARTITION BY v, s, a, p ORDER BY m) AS rank
    FROM #t /* A columnstore table with 1,500 rows */
)  x
WHERE x.rank = 1
OPTION (QUERYTRACEON 9453/* Disable batch mode */)


texts: Multiple WHERE conditions on same column =val1 but doesnt=val2 or val3

texts:
I am trying to filter a table which has NON-UNIQUE transaction id's, joined to a products table on 1 common column. The filter needs to be as follows:

equal to :group1 AND
NOT equal to :group2 AND
NOT equal to :group3

I originally started with the below query but without the AND rk_group <> ____ conditions.

SELECT COUNT(DISTINCT txn_id)
                    FROM 1_txns
                    INNER JOIN 2_products USING (sku)
                    WHERE rk_group = :group1
                    AND rk_group <> :group2
                    AND rk_group <> :group3
                    ;

I have also tried

SELECT COUNT(DISTINCT txn_id)
                    FROM 1_txns
                    INNER JOIN 2_products USING (sku)
                    WHERE rk_group NOT IN ( :group2, :group3)
                    ;

I have also tried multiple combinations of joins and IN() and NOT IN() and it's still returning all teams ids including those where the NOT groups are present.

Can someone point me in the right direction please.

Schema info relevant for the query:

table 1_txns
(txn_id, sku)

table 2_products
(sku, rk_group)

Sample data

Txn_id, rk_group

------

    1,group1
    1,group2
    2,group1
    3,group1
    3,group3

If the above were my data, and group1 is my = group, and groups2 and 3 are my != groups, then I should return a row count of 1. Which is txn id '2'. All methods I've tried so far will return a count of 3.

NOTE: there are only 3 distinct rk_groups in the 2_products table.


texts:
i have a table with these columns and the values are coming in these tables from  a csv file at a regular interval of n minutes ( by cron the load infile command) :

 ID (PK)        status      first      last     value
 1                Q          12         15       low
 2                Q          12         15       low
 3                O          12         15       High

now whenever next data comes , it should first check whether ID is present in table , if it is then it simply update that ID's corresponding field , and if new ID come , it simply insert the data.

For this i have created a procedure :

     create procedure test.testproc (IN no int , IN status varchar(10))
     begin if exists (select id from test.testproc where id = no)
     then
     update baseline set status=status where id=no;
     else insert into  tname(id,status) value(no,status);
     end if;
     end$$

Now what i want to know :

should i create two tables for this i.e one is a temporary table and one main table  ?   ** i'm 100% confuse here what should i do here
how should i automate all these i.e data comes from a csv file , then my load infile script runs, then my above procedure should execute and checking that if ID already present it will simply update else it will insert.

OR , if you have any other process , please tell


texts:
I have the following relation and function dependencies.

R = ABCDE
F: C->AB, D->A, BE->CE, E->B

I have to check whether this relation is in BCNF.

Wikipedia states:

  ...if and only if for every one of its dependencies X → Y, at least one
  of the following conditions hold:[4] X → Y is a trivial functional
  dependency (Y ⊆ X) X is a superkey for schema R

So first I went on to find the superkeys.

Since D is not on the right side, I'm assuming the superkey must contain D in it.

What I thought is that the only superkey is DE. Is this correct?

And if the only superkey is DE, then the relation is not in BCNF because none of the functional dependencies have the superkey on the left.

Can anyone point out if I'm mistaken?


texts: Indexing Strategy for the query performance

texts:
I have a question on indexing strategy as i am trying to do indexing for a database.

I understand the basic indexing strategy which are:

Clustered index on PrimaryKey
Add Non Clustered index for other columns as per requirement.
Should have indexes for WHERE/JOIN/GROUP BY/ORDER BY columns
Covering indexes for columns to avoid keylookup.
Check the workload and avoid adding too many indexes
Index Foreign Keys
Index any other unique keys etc..
I still have some questions and i couldn't find an exact answer to my questions.

Here is an example of a table that i have.

USE [DEMODB]
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[Employee_Header](
    [Id] [uniqueidentifier] NOT NULL,
    [PageId] [uniqueidentifier] NOT NULL,
    [Dept] [varchar](256) NULL,
    [DeptName] [varchar](256) NULL,
    [SubUnit] [varchar](256) NULL,
    [ShiftType] [varchar](256) NULL,
    [ReportDate] [varchar](256) NULL,
    [ReportTime] [varchar](256) NULL,
    [AccessNumber] [varchar](256) NULL,
    [AccessOnAfterDate] [varchar](256) NULL,
    [AccessOnAfterTime] [varchar](256) NULL,
    [EmpBand] [varchar](256) NULL,
    [WorkSite] [varchar](256) NULL,
    [Location] [varchar](256) NULL,
    [Location_FieldValue] [varchar](256) NULL,
    [Location_Description] [varchar](256) NULL,
 CONSTRAINT [PK_Employee_Header] PRIMARY KEY CLUSTERED
(
    [Id] ASC
)WITH (PAD_INDEX = ON, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 80) ON [PRIMARY]
) ON [PRIMARY]
GO

As you can see [Id] is the primary key here.[PageID] is the foreign key and it is not unique.
There are few queries which uses whereclause on `[Dept],[SubUnit], [ShiftType] and [ReportDate]'.

One query displays columns  [Dept] and [ShiftType] and other query displays [Dept],[ReportDate],[ReportTime],[EmpBand]

I am confused as to how to create NonClustered indexes with Include
columns so that same indexes can be used to both queries Or
should i create separate clustered indexes for where each columns
then create a separate single NonClustered index with PrimaryKey
    column as unique column and all the display columns in the include
    section.
Should i just keep the display columns in the keycolumn section?

Most of the examples available shows querying to a single table with an Id and one or more select column.
Can someone help me to understand how you approach similar situations?

I created two indexes as shown below.

First one is for the where clauses.I have all the where clause fields added as key columns in this non clustered index

CREATE NONCLUSTERED INDEX [ix_Employee_Filters] ON [dbo].[Employee_Header]
(
    [Dept] ASC,
    [SubUnit] ASC,
    [ShiftType] ASC,
    [ReportDate] ASC,
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO

The second one is for the select columns.Some of the select columns are already part of the first index so i have only two columns that needs to be covered.
So i added them as Include fields with Id(Primary key field) as the key column.

CREATE NONCLUSTERED INDEX [ix_Employee_Include_Fields] ON [dbo].[Employee_Header]
(
    [Id] ASC
)
INCLUDE (   [ReportTime],
    [EmpBand]) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO

Still when i run and see the execution plan, i get the message that there is a missing index.

CREATE NONCLUSTERED INDEX [<Name of Missing Index, sysname,>]
ON [dbo].[Employee_Header] ([ReportDate])
INCLUDE ([Id],[Dept],[ShiftType],[ReportTime],[EmpBand])

I don't understand why query optimizer is suggesting to a date filed as key field and other fields in the include section.
I already have a Nonclustered index with ReportDate among other where fields.

Appreciate for sharing your approach.But can anyone answer/share your perspective on the specific example mentioned?


texts: How to prevent implicit casting from bytea to text during the current connection in postgres?

texts:
In Postgres bytea values are automatically converted to text when inserted to text/varchar columns, based on the bytea_output setting.
I am working with some program code which automatically converts certain values (binary strings) in the program to a bytea format. The problem is that users might accidentally try to insert these values into a text column. By default, Postgres will allow this, but this cannot always work smoothly - for example if there are non-ASCII bytes. I think users may not realise the strange insert behaviour is due to their use of a binary string in the calling program. Therefore, if a bytea to text conversion happens, I want Posgres to raise an exception.
I am aware of CREATE CAST, but as I understand, this is a system-wide action. I do not want to change the system behaviour for other connections. I could do CREATE CAST followed by DROP CAST but this seems dirty to me as it still not contained within the connection.
How do I make (implicit) casts from bytea to text throw an exception only within the current connection?
The sql is issued automatically, so I can add a preceding SQL statement before every statement, that's no problem.
I was a little surprised by this behaviour because Postgres usually errs on the side of strictness, which I like.
This question follows from my previous question:

How to prevent different connections treating bytea in same query differently?


texts: Will this trigger significantly degrade performance?

texts:
I have this trigger to (do my best) to prevent brute forcing logins. It has two seperate functions:

If a failed login has been attempted more than 4 times (so read that exactly 5 times) from the same IP address within the last three minutes, that IP_address is added to the blacklisted_ips table. The IP address then has a "cool down" time period specified by a column in the table.
If a failed login has been attempted more than 3 times (so read that exactly 4 times) from the same username within the last two minutes (so IP address agnostic), then the username is added to the blacklisted_usernames table. The username then has a "cool down" time period specified by a column in the table.

My question is, looking at this trigger, it looks performance inefficient to me. Am I correct in thinking that, or is the performance effect negligible?

CREATE TRIGGER prevent_brute_force
AFTER INSERT ON login_att
FOR EACH ROW
BEGIN
    SELECT ip_add, was_success INTO @ip, @result
    FROM login_att
    ORDER BY login_att_id DESC LIMIT 1;

    SELECT ip_add INTO @check_ip
    FROM login_att
    WHERE login_att_time > DATE_SUB(NOW(), INTERVAL 3 MINUTE)
      AND ip_add = @ip
    HAVING COUNT(ip_add) > 4;

    IF @check_ip IS NOT NULL
    THEN
        IF @result = 0
        THEN
            INSERT INTO blacklisted_ips (ip_add) VALUES (@ip);
        END IF;
    END IF;

    SELECT username_att into @username_att
    FROM login_att
    WHERE login_att_time > DATE_SUB(NOW(), INTERVAL 2 MINUTE)
      AND was_success=0 HAVING COUNT(username_att) > 3;

    IF @username_att IS NOT NULL
    THEN
        INSERT INTO blacklisted_users (username) VALUES (@username_att);
    END IF;
END$$


texts: SQL Server Management Studio for a remote database

texts:
With a SQL Sever database which is on a remote hosted dedicated server I can work using SQL Server Management Studio installed either on the same server or on my local computer. I the first case I should work using Remote Desktop and this makes the work a little bit slower. In the second case I need to open additional port in server's firewall, but I will have more comfortable user experience.

What is the recommended practice of these two?


texts: Recovery of a corrupted database using only the .frm in Windows

texts: Is it possible to restore mysql database using only .frm files in Windows


texts: Skipping VM backups for high workload SQL Server?

texts:
For those who have virtualized SQL Servers with very high volume transaction workload (very high rate of data change), I am curious how you handle VM backups, or if you even do them.

Of course, I manage a separate chain of transactionally-consistent native database backups.  But the standard policy at the virtualization level has always been to backup all VMs.  This is currently using a typical 3rd party VM backup solution (Initiate VM snapshot, copy snapshot data to backup appliance, release snapshot).  However, these servers have an extremely high rate of data change, resulting in the change files growing large very quickly.  And the system is reaching a point where it is choking on the amount of data that needs merged during the snapshot release.

I am toying with the idea of skipping the VM backups altogether.  I don't permit any other apps to be installed on SQL Servers, and the base OS and SQL configuration is pretty vanilla.  So it wouldn't take much in a failure scenario to just deploy a new VM from template, install SQL bits on it, and start restoring database backups.

Thoughts? Alternatives? Pitfalls I'm missing?  Thanks for your input.


texts: Teradata SQL syntax from date to quarter

texts:
I need Teradata SQL syntax to extract quarter from a date:

Date: (YYYY-MM-DD)
2015-09-12
2015-05-22
2014-12-15

Given below is the expected output:

3Q15
2Q15
4Q14

Query which I have tried (wrapped for readability):

SELECT DATE, ((CAST(EXTRACT(MONTH FROM DATE) AS BYTEINT)-1)/3)+1
    || 'Q' || substring(DATE,3,2) quarter

Error:

  Missing parameters


texts: Seed data in mysql for e2e testing using tables backups

texts:
I'm using cypress for e2e tests and I currently seed the database for each test using sql files.
The problem is that seeding many sql files adds up and take a significant amount of time
Currently I'm loading the sql file like so
docker exec lamp-mysql db_name < /path/to/file.sql

I'm looking for a faster way to import data, optimally using only the file system. Is there a mysql engine which allows to simply copy table files.


texts:
i try to design a database with an is-a relation:

Vehicles have some general attributes and the SubVehiclesX have some specialized Attributes.

SubvehicleX is a Vehicle

And a Person has a Vehicle

In the VehicleNames-Table is e.g.:

ID | TableName

1 | SubVehicle1
:

Now I want to find out all Attributes of the Vehicle

  SELECT *
  FROM VEHICLE v
  LEFT JOIN (
             SELECT vn.TableName
             FROM VehicleNames vn
             WHERE vn.ID = (SELECT NameOfVehicle
                        FROM Vehicle v
                        WHERE v.ID = VARIABLE1)
            ) x on x.ID = v.Vehicle
  WHERE v.ID = VARIABLE1

I want it as dynamic as possible, so when i want a new subvehicle i only must insert a row in the VehicleNames and create a new table. I haven't to change my program code.

the part:

            (
             SELECT vn.TableName
             FROM VehicleNames vn
             WHERE vn.ID = (SELECT NameOfVehicle
                        FROM Vehicle v
                        WHERE v.ID = VARIABLE1)
            )

must be handled in the Programm code or is there an function to take the Result as a tablename?? Left Join SubVehicle1 x

Is there any other alternative to this? I find this Version isn't clean...


texts:
I am developing multi-tenancy application and I have no problem with the architecture itself. I find difficult on how to make customers table shareable among tenants. This is because same customer can subscribe to multiple tenants. I don't want to have same duplicate data and so user should register only once and be able to subscribe to any of tenant with the same credentials.

I have tried to look over the internet but could not find any good resource (may be I am using wrong keywords). I would appreciate any help (including the architecture name if any) to solve this problem.

If I am not clear please let me know.


texts: SQL Change Tracking not tracking multi-record updates

texts:
I have SQL Server change tracking switched on a table with about 3mil rows.

Switch on change tracking:

ALTER DATABASE FooDB
SET CHANGE_TRACKING = ON
(CHANGE_RETENTION = 10 DAYS, AUTO_CLEANUP = OFF)

ALTER TABLE [dbo].[fooTable]
ENABLE CHANGE_TRACKING
WITH (TRACK_COLUMNS_UPDATED = OFF)

If I edit rows individually, all fine, a change is stored for each insert/update/delete.

If I run an update query that updates more than one row, I only get a change record for the SECOND row in the update.

So:

UPDATE fooTable SET name = UPPER(name) where id between 100 and 200

followed by (to ensure that an actual update occurs for each row)

UPDATE fooTable SET name = LOWER(name) where id between 100 and 200

results in a Change record for record 101 only.

SQL Express 2014


texts: Totals on multple case conditions - mySql

texts:
I am attempting to translate the query in this question into mySql, as we moved the data from an access db to a new structure.

I have come up with the following, since there are multiple conditions (Value of '' or 'Did Not Compete'), and I was getting the following error (Since resolved, see edit 2 below).

  1064 - You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'then 0 else 1 end) AS 'Total Weapons',
        SUM( case when ( tourney_info.c_we' at line 7

The query is as follows:

SELECT school_num, 'Total Form and Sparring', 'Total Weapons', 'Total Combat', 'Total Form and Sparring' + 'Total Weapons' + 'Total Combat' AS 'Total Entries'
FROM
(
  SELECT
  tourney_info.school_num,
  SUM(  case when tourney_info.t_forms IN ('', 'Did Not Compete') AND tourney_info.spar IN ('','Did Not Compete')
        then 0
        else 1
        end) AS 'Total Form and Sparring',
  SUM( case when tourney_info.t_weps IN ('','Did Not Compete') then 0 else 1 end) AS 'Total Weapons',
  SUM( case when tourney_info.c_weps IN ('', 'Did Not Compete') then 0 else 1 end) AS 'Total Combat'
FROM
  tourney_info
WHERE school_num = '546' AND tourn_date > '2018-04-20' AND tourn_date < '2018-04-22'

) AS derived

Once the error was resolved as per the comments, the query gives the following results:

school_num  Total Form and Sparring     Total Weapons   Total Combat    Total Entries
    546     Total Form and Sparring     Total Weapons   Total Combat    0

Edit #2, after looking back at the field specifications, as a test query showed me that someone set the field to be 10 chars, and my 'Do Not Compete' was truncated on import:

If I take the interior query out as follows, I get individual totals, however I do not get a cumulative total:

  SELECT
  tourney_info.school_num, 'Total Form and Sparring' + 'Total Weapons' + 'Total Combat' AS 'Total Entries',
  SUM(  case when tourney_info.t_forms IN ('', 'Did Not Co') AND tourney_info.spar IN ('','Did Not Co')
        then 0
        else 1
        end) AS 'Total Form and Sparring',
  SUM( case when tourney_info.t_weps IN ('','Did Not Co') then 0 else 1 end) AS 'Total Weapons',
  SUM( case when tourney_info.c_weps IN ('','Did Not Co') then 0 else 1 end) AS 'Total Combat'
FROM
  tourney_info
WHERE tourney_info.school_num = '546' AND tourney_info.tourn_date > '2018-04-20' AND tourney_info.tourn_date < '2018-04-22'

Result set:

 school_num     Total Entries   Total Form and Sparring     Total Weapons   Total Combat
546               0                   36                      17                    25


texts: how to import mysql system "mysql" database

texts:
This question might be already answered but it's almost impossible to google it.

When I perform full backup of mysql server using

mysqldump --all-databases

then reinstall it from scratch I want to import FULL dump of all databases, including "mysql".

I successfully done that by executing

mysql -u -p < dumpfile

but now even if all user records are in mysql.user these accounts are not working.

I can't even change their password, I always get

  ERROR 1133 (42000): Can't find any matching row in the user table

Restarting mysqld is of no help

answer:

It was fixed by executing

MariaDB [mysql]> flush privileges;
Query OK, 0 rows affected (0.26 sec)

now all users work as before


texts: SSIS File System Task Editor closes unexpectedly

texts:
In a package I am developing, I am having an issue using a variable for the destination connection in the File System Task.  I have done the following:

I dragged a new File System Task to the design surface.
I changed the IsDesinationPathVariable parameter to true

When I use the drop down arrow to select a variable, the editor closes.  The same thing happens when I try and paste the variable name.

I have highlighted the drop down that causes the editor to close below:

I am using Visual Studio Ultimate 2012 to develop the package, build 11.0.50727.1. The SQL Server Integration Services Designer is version 11.0.3436.0.


texts: What is the correct way to specify a record as the *current* record?

texts:
The two options I have thought of is either to add a column boolean column called Current onto the table; but I'm sure that would violate some level of normalisation due to more than one record being able to be set as current.

Alternatively having another table that specifies the ID of the current record, but this also doesn't seem like a great way of doing it.

Is there a better way or convention for achieving this kind of thing?

Edit:
I probably should of said there can only be one current record, so when it is changed the previously current record needs it's 'current' status removed.  The system isn't complicated enough to need to worry about concurrency or anything like that.  The main reason for the question is that I wanted to do things in the correct/conventional way if there was one.

I believe the best way is to have a settings table that will hold the ID of the current record.


texts:
I assume that the best approach for many languages (5 and more) is concept based on:

    CREATE TABLE `language` (
    `language_id` char(2) NOT NULL,
    `collation` varchar(64) NOT NULL,
    PRIMARY KEY (`language_id`)
);

CREATE TABLE `product` (
    `product_id` int(11) NOT NULL auto_increment,
    PRIMARY KEY (`product_id`)
);

CREATE TABLE `product_translation` (
    `product_id` int(11) NOT NULL,
    `language_id` char(2) NOT NULL,
    `group_id` int(11) NOT NULL,
    `name` varchar(50) NOT NULL,
    `url` varchar(50) NOT NULL,
    `price` decimal(9, 2) NOT NULL,
    `description` text NOT NULL,
    UNIQUE KEY `language_id_2` (`language_id`, `url`),
    KEY `language_id` (`language_id`, `group_id`, `name`),
    FULLTEXT KEY `name` (`name`, `description`),
    FOREIGN KEY (`product_id`) REFERENCES `product` (`product_id`),
    FOREIGN KEY (`language_id`) REFERENCES `language` (`language_id`),
    PRIMARY KEY (`product_id`, `language_id`)
);

But what if content is filled by user? Should this structure be used only for static content ? Registred user can at any time change his language settings also the data that he is adding (private messages,articles) can be added in any language we cannot force him to add only english content. Should all data be stored in one table and column (body), am I right ?

For example private messages:

    CREATE TABLE `msg_messages` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `thread_id` int(11) NOT NULL,
  `body` text NOT NULL,
  `priority` int(2) NOT NULL DEFAULT '0',
  `sender_id` int(11) NOT NULL,
  `cdate` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8;


texts: SQL Server 2012 Always On, during a massive DB change

texts:
As the topic says, I was wondering if you guys have any advice on potential issues with Always On availability during a massive update?

We're in need of making several massive datamodel changes, removing & adding tables and moving around large amounts of data (some 300+ GB), including creating and rebuilding hundreds of indexes and statistics in a procedure that will take well over 24 hours in total, and schedule-wise can not take more than 30 hours before bringing the DB's back online. And I'm not sure whether the always on availability mode's synchronization is the best option to replicate these changes to the other availability groups. Would it be better to just backup the changes once they're done, restore that to the other groups and re-enable synchronization from there?

Like I said, I don't really have experience working with it, so this is an open question.  And apologies for any misunderstandings or misconceptions here, again since my experience is limited. Please correct me where appropriate. :)

Thanks!


texts:
I found some old notes of mine from 2011 and I also re-read the excellent article 10 Cool SQL Optimisations That do not Depend on the Cost Model by @lukas-eder. So I thought I would take my notes for another spin. The scenario is quite simple Fiddle
create table emp (
    emp_no int not null primary key,
    title varchar(10) not null,
    salary int not null,
        check (emp_no > 0),
        check (title in ('BOSS','WORK'))
);

insert into emp with recursive t (n) as (
    values (1)
    union all
    select n+1 from t where n+1 < 1000
) select n, case when mod(n,10) = 0 then 'BOSS' else 'WORK' end,
            case when mod(n,10) = 0 then 110 else 0 end + mod(n,90)
from t;

Now, given the rules:
 (TITLE = BOSS) implies (SALARY > 100)

and
 (TITLE = WORK) implies (SALARY <= 100)

which can be implemented as:
-- (TITLE = BOSS) => (SALARY > 100)
alter table emp add constraint cc1
    CHECK ( (title <> 'BOSS' OR salary > 100) );
-- (TITLE = WORK) => (SALARY <= 100)
alter table emp add constraint cc2
    CHECK ( (title <> 'WORK' OR salary <= 100) );

and the query:
select *
from emp
where title = 'BOSS' and salary <= 100

can the DBMS spot the contradiction, and return an empty result set without even touching the data?
Let A = ( title = ’BOSS’ ), B = ( salary > 100 )

select * from emp where (A ^ ~B)
select * from emp where (A ^ ~B) ^ (~A v B)           # by cc1
select * from emp where (A ^ ~B ^ ~A) v (A ^ ~B ^ B)
select * from emp where (FALSE ^ ~B) v (A ^ FALSE)
select * from emp where (FALSE) v (FALSE)
select * from emp where (FALSE)

I have tried Postgres 13 (see fiddle above)
Seq Scan on emp  (cost=0.00..26.50 rows=2 width=46) (actual time=0.134..0.134 rows=0 loops=1)
  Filter: ((salary <= 100) AND ((title)::text = 'BOSS'::text))
  Rows Removed by Filter: 999
Planning Time: 0.312 ms
Execution Time: 0.149 ms

and Db2 11.5.4.0:
Optimized Statement:
-------------------
SELECT
  Q1.EMP_NO AS "EMP_NO",
  Q1.TITLE AS "TITLE",
  Q1.SALARY AS "SALARY"
FROM
  DB2INST1.EMP AS Q1
WHERE
  (Q1.SALARY <= 100) AND
  (Q1.TITLE = 'BOSS')

Access Plan:
-----------
        Total Cost:             51.8267
        Query Degree:           1

      Rows
     RETURN
     (   1)
      Cost
       I/O
       |
     90.2441
     TBSCAN
     (   2)
     51.8267
        4
       |
       999
 TABLE: DB2INST1
       EMP
       Q1

but both fail to do so. Any other DBMS that can spot the contradiction and act upon it? This is of course more of an amusement than a real-world problem, but still.
Edit: The constraint suggested by @federico-razzoli in his answer does not work either:
alter table emp add constraint cc1
    check ((title = 'BOSS' and salary > 100) or
           (title = 'WORK' AND salary <= 100));

and the same query still causes table-access
 Rows
 RETURN
 (   1)
  Cost
   I/O
   |
 90.2441
 TBSCAN
 (   2)
 51.8267
    4
   |
   999

TABLE: DB2INST1
EMP
Q1


texts:
I have a table in a Postgres 8.2.15 database.  The table bloated to almost 25GB but after running vacuum full and cluster the table size was dramatically smaller, well under 1GB.  A few weeks later and it's back up to 3.5GB and climbing.

This is not a table that has frequent deletes, so I'm at a loss as to what is causing the bloat.

This only happens to a single table.  I have a separate, structurally identical database, serving the same software; the table in that database has not shown any bloat.

Any ideas?