texts: Getting the "flat" query corresponding to a view using nested views?

texts:
I'm programming an application that queries SAP Business One databases, which are incredibly complex, riddled with cryptic table and column names left over from ancient times (including many interesting typos), and in some parts have quite bad database design.
To ease working with these, I use a large "view library" as I call it, which defines views upon views that bring a lot of clarity, eliminate repetition, and allow me to ultimately aggregate all data which my application needs in a specific screen into one or two views that the application needs to query.
This works very well and I'm happy with it.
(Yes really. The performance is usually great, and when it isn't it can usually be fixed without much hassle and is almost always revealed to be related to a bad join or sub-query somewhere that would have caused the issue regardless of whether views are being used or not. I've only ever had one issue related to using views per se, which was MS SQL Server failing to optimize queries against one of the most complex views --perhaps the most complex in fact-- which I was able to solve simply by turning the view into a procedure that materializes the results of a couple intermediate views first.)
Now in some new deployment variants of SAP Business One, I will lose the ability of creating arbitrary SQL views in the database. Bad news. I will however be allowed to run some read-only queries, via a limited subset of the SQL language...
This brought me to the question: is there a way to "flatten" an SQL view via MS SQL Server (any version), so that I automatically get a query that already has all nested views "resolved" to their definitions, recursively?
For instance, consider the following sequence of (highly simplified) view definitions:
create view myItems as
  select itemCode, itemName from Items

create view myCustomers as
  select customerCode, customerName from Customers

create view myOrders as
  select o.orderId, o.orderNumber, c.*
  from Orders o
  join myCustomers c on c.customerCode = o.customerCode

create view myOrderLines as
  select l.lineId, l.lineNumber, o.*, i.*
  from OrderLines l
  join myOrders o on o.orderId = l.orderId
  join myItems i on i.itemCode = l.itemCode

I want the ability to get the "flat version" of let's say myOrderLines, which would be this:
select l.lineId, l.lineNumber,
       o.orderId, o.orderNumber,
       c.customerCode, c.customerName,
       i.itemCode, i.itemName
from OrderLines l
join Orders o on o.orderId = l.orderId
join Customers c on c.customerCode = o.customerCode
join Items i on i.itemCode = l.itemCode

Is such a thing possible?


texts: SQL Server equivalent to functionality of Oracle RAC?

texts:
I did some Googling and couldn't find an answer to this question more recent than a few years ago, so I thought I'd ask. Oracle's RAC feature offers load-balancing for both read and write transactions, as well as scale-out and high-availability without downtime (at least, as I understand it - we're about to deploy our first databases that use RAC, so we'll see how it goes).

Is there any SQL Server feature set (or third party component you could install on top) that delivers equivalent functionality? We've always used Windows clustering, where a failover event causes about 20-30 seconds of SQL downtime - always tolerable, but not ideal. Now, with AlwaysOn in SQL 2012, SQL Server shrinks that to about 15 seconds and adds the concept of read-only-secondary databases, but they still require that write transactions are choked through a single connection point (much improved, since many transactions are just read, but still not really load balancing), and in the case of a node failure or the need to patch, there's still downtime.

I suppose it's just more curiosity - I feel like this is the only area that SQL Server falls behind Oracle (at least among the features I've personally seen used). I wanted to see if there are any options out there to close that gap and possibly improve our own SQL Server deployment while we wait for Microsoft's equivalent feature to be added - maybe in SQL 2014/2015?


texts:
On our dev boxes, our database resides entirely in the PRIMARY filegroup, and everything works fine.

On one of our production servers, recently upgraded from 2005 to 2008, we noticed it was performing slower than it should.  On this machine, there are two filegroups - PRIMARY and INDEXES.    Both filegroups contain 1 file per logical volume, one logical volume per CPU. The filegroups are sharing logical volumes (both of them are spread out across all disks).

We isolated a few queries that were performing fast on the dev boxes and slow (up to 40x slower) on the production machine.  Turned out these queries were using the non-clustered indexes that resided in the INDEXES filegroup.   Tweaking some of the queries to only use clustered indexes that were in the PRIMARY filegroup dropped their times back to normal.

As a final confirmation, we redeployed the same database on the same machine to have everything in PRIMARY, and things went back to normal!

Here's the statistics output of one of the queries, run on the machine in question  (table names changed to protect the innocent):

FAST (everything in PRIMARY filegroup):

(3 row(s) affected)
Table '0'. Scan count 2, logical reads 14, ...
Table '1'. Scan count 0, logical reads 0, ...
Table '1'. Scan count 0, logical reads 0, ...
Table '2'. Scan count 2, logical reads 7, ...
Table '3'. Scan count 2, logical reads 1012, ...
Table '4'. Scan count 1, logical reads 3, ...

 SQL Server Execution Times:
   CPU time = 437 ms,  elapsed time = 445 ms.

SLOW (indexes split into their own filegroup):

(3 row(s) affected)
Table '0'. Scan count 209, logical reads 428, ...
Table '1'. Scan count 0, logical reads 0,...
Table '2'. Scan count 1021, logical reads 9043,....
Table '3'. Scan count 209, logical reads 105754, ....
Table '4'. Scan count 0, logical reads 0, ....
Table '5'. Scan count 1, logical reads 695, ...
**Table '#46DA8CA9'. Scan count 205, logical reads 205, ...**
Table '6'. Scan count 6, logical reads 436, ...
Table '7'. Scan count 1, logical reads 12,....

 SQL Server Execution Times:
   CPU time = 17581 ms,  elapsed time = 17595 ms.

It's pretty clear that having a second file group is making SQL Server batty with choosing an execution plan.  Looking at the corresponding execution plans for the query in each configuration verifies this, they are quite different.

What's going on?  Any insights would be much appreciated.


texts: I am requesting a new server from our Server team. Which is better for SQL Server performance: 2 CPUs with 8 cores per CPU or 1 CPU with 16 cores?


texts: Select records not associated to a specific tag in a many-to-many relation

texts:
as shown in this sql-fiddle I have a bridge table events_tags representing a many-to-many between events and tags.

I need the fastest way to find all events not associated to a given tag t.

The first query in the fiddle shows the incorrect solution where a an event assigned to t and another tag is included in the result set.

The second query makes a anti-join (learned this from an answer by ypercubeᵀᴹ) and is correct, but very slow in my real schema, where it would return 18K rows from the bridge table in ~130ms on the hardware on which our application runs.

I need this functionality in an endpoint of a RESTful API, whose total response time must stay <300ms.

Is there a faster approach I can use or am I limited to the fact that the "real" query has to deal with a derived table of 18K rows?

EDIT
I failed to mention that I will then combine this (as a subquery) with another query selecting only events matching some criteria.
I am not selecting 18K events :)

P.S.: let me know if any other detail on the problem is required

Thanks


texts:
i query this SQL query:

SELECT * FROM [DB].[dbo].[Table]
WHERE [DATE] BETWEEN '01-01-2016' AND '31-03-2016'
AND ([TIME] >= '00:00:00' OR [DATE] > '01-01-2016')
AND ([TIME] <= '00:00:00' OR [DATE] < '31-03-2016');

But in SQL Studio the result to another date and time.

[DATE] = VARCHAR(10) (DD-MM-YYYY)
[TIME] = VARCHAR(10) (HH:MM:SS) 24h

How to fix ?


texts: Is there are way to use Fine Grain Auditing to record Varray bind values?

texts:
I have an application, and would like to use Oracle's FGA (Fine Grained-Auditing) to record varray bind information.  Specifically, I am using Oracle Spatial and need to know at what extent users are actually querying the data.  By default, the only value that is recorded by Oracle for varrays appears to be -1.

begin
dbms_FGA.add_policy (
object_schema => 'GDB',
object_name => 'COUNTRY',
policy_name => 'COUNTRY_Aud',
handler_schema => NULL,
handler_module => NULL,
enable => TRUE,
statement_types => 'SELECT' );
end;
/

The SQL_TEXT and SQL_BIND values that are returned are:

SELECT SHAPE FROM  GDB.COUNTRY WHERE mdsys.sdo_filter(GDB.COUNTRYS.SHAPE,
MDSYS.SDO_GEOMETRY(:gtype1,:srid1,NULL,:elem_info1,:ordinates1), 'querytype=window') =
'TRUE' #1(4):2003 #2(4):4326 #3(-1): #4(-1):

I know that I could create a procedure to handle the auditing, but I don't know how to retrieve the actual varray.

I have tried getting the values out of the DBA_HIST_SQLBIND view, but the varray values are always null.

declare
    test mdsys.sdo_elem_info_array;
    anytest sys.anydata;
    rc pls_integer;
begin
    select VALUE_ANYDATA into anytest from DBA_HIST_SQLBIND where SQL_ID='9vu11pguf5gx9' and datatype = 123 and rownum = 1;

    if(anytest is not null) then
        rc := anytest.getcollection(test);
        DBMS_OUTPUT.PUT_LINE('Got it');
    else
        DBMS_OUTPUT.PUT_LINE('Null value');
    end if;
end;
/


texts:
I'm having difficulty finding 'lay' explanations of how indexes are cached in PostgreSQL, so I'd like a reality check on any or all of these assumptions:

PostgreSQL indexes, like rows, live on disk but may be cached.
An index may be entirely in the cache or not at all.
Whether it is cached or not depends on how often it is used (as defined by the query planner).
For this reason most 'sensible' indexes are going to be in the cache all the time.
The indexes live in the same cache (the buffer cache?) as rows, and therefore cache space used by an index is not available to rows.

My motivation for understanding this follows on from another question I asked where it was suggested that partial indexes can be used on tables where a majority of the data will never be accessed.

Before undertaking this, I'd like to be clear that employing a partial index yields two advantages:

We reduce the size of the index in the cache, freeing up more space for rows themselves in the cache.
We reduce the size of the B-Tree, resulting in a faster query response.


texts:
I have a table called toponim. This table contain field called namspe. I would like to find words in this field that contain similar syllables such as Toli-toli, Bagan Siapi-api, Fakfak, Oro-oro Ombo.

At first I use this query, but it won't work if the word that contains similar syllables is in the middle or in the end of the sentence. Any ideas?

select namlok, namspe
from toponim
where left(namspe,4) = substring (namspe,5,4);


texts:
I have a database which is in InnoDB, because I wanted consistency (cascading delete).

Now, I want to use spatial indexes for selecting rows ordered by geometric distance.

However, spatial indexes require MyISAM. What is the best thing to do?

The best I could think of is to put the location in a separate MyISAM table and join the InnoDB table with it? (Note that I have removed the spatial magic from the statement to simplify things.)

select * from inno join isam using (id) order by isam.distance

Other solutions?


texts: ORA-14224 Deferred Segment Creation is not supported for this schema

texts:
I'm trying to use deferred segment creation and not having any success.

My deferred_segment_creation parameter is set to TRUE, but new tables do not defer.  I've also tried explicitly stating SEGMENT CREATION DEFERRED in my create table statement but receiving the following error:

CREATE TABLE NEWTABLE3 (n1 number) SEGMENT CREATION DEFERRED TABLESPACE MYTBS
*
ERROR at line 1:
ORA-14224: Deferred segment creation is not supported for this schema

Can anyone provide some guidance regarding what I'm doing wrong?

Thanks!


texts: Which databases does `mysqlpump` ignore by default?

texts:
If I run the following command:

mysqlpump > mysqlpump.sql

Is there any documentation, a SQL query that can be run, or perhaps a config file that contains a complete list of databases that mysqlpump ignores by default?

According to this Percona blog post, the following databases are ignored by default:

INFORMATION_SCHEMA
ndbinfo
performance_schema
sys

However, this list was written in 2017 and seems incomplete. I've seen other documentation which indicates that mysqlpump also ignores tables in the mysql database.


texts:
I have the following XML column in my Oracle 11 G DB:

<?xml version="1.0" encoding="UTF-8"?>
<l-analysis version="2.9">
   <vehicle>
      <odo-reading latest="true">
         <reading value="653463">653,463</reading>
         <reading-date value="2018-12-04">4-Dec-2018</reading-date>
         <reading-unit code="K">Km</reading-unit>
      </odo-reading>
  <odo-reading latest="false">
     <reading value="391264">393,264</reading>
     <reading-date value="2018-12-04">4-Dec-2018</reading-date>
     <reading-unit code="K">Km</reading-unit>
  </odo-reading>
   </vehicle>
</l-analysis>

I need to query the reading value but only where odo-reading latest="true".

How can I do that in a CLOB column?


texts: understanding (and modelling) grocery shopping lists

texts:
I am having a hard time understanding how multiple product orders are mapped out.

What is the best way of modelling a shopping list for multiple customers?

I am developing an C# application that mocks the system we use at work for processing customer grocery orders. After a fair amount of schema research, I am looking at something very simple, like this:

Products {
ProductID,
Name,
Price
}

Customers {
CustomerID,
FirstName,
SecondName
}

Customer_Orders {
OrderID
CustomerID
ProductID
Quantity
}

This does make sense to me, but what seems weird is the Customer_Orders table. As I understand, if a customer places an order with 10 different items, I'll have 10 rows, with the same OrderID and CustomerID yet different ProductIDs, representing the shopping list.

This seems counter-intuitive to me, I imagine ProductID should be made up of a collection of values (how I'd program it) and not separate rows.

Is my thinking way off here, or am I supposed to model it this way?


texts: RSync Database File Deltas in SQL Server 2008

texts:
Alright, I feel like this is a terrible idea, but I need some help understanding why this is bad:

I'm still working on implementing a disaster recovery/business continuity solution for our datacenter. We're running MSSQL 2008 Enterprise and we plan on running a passive instance in the cloud. Currently I'm suggesting Log Shipping, but I've been asked to explore using RSync (or something similar) to push MDF/LDF file deltas to the cloud instead of using the internal tools.

The goal would be to reduce our footprint and not run the cloud SQL instance most of the time due to licensing issues (our Passive/DR license is already in use in our current datacenter, but if that center goes down, the license becomes available).

I've found a solution that uses VSS to create and push deltas even if the files are locked, but I'm wondering what sorts of issues could show up. Can I get some insight? We would be pushing deltas every 15 minutes, the database in question is roughly 2GB with maybe 10 MB of T-Logs generated in a 15 minute window.


texts:
I need help creating the schema for a database to hold information relative to a web user's shopping cart/session. Each session will have a session ID, a time stamp holding time of creation, an expiry time stamp, a boolean indicating whether or not the purchase was completed, and then a list of products, and their associated quantities and prices. Every user will have only one ID, origination time stamp, expiry time stamp, and purchase boolean, but they may reference one or many products with varying quantities and prices.

My initial thought was to have a session table and a product table, but I can't seem to get past thinking the session ID and the product information will become redundant in big orders and across different sessions. In short, if different rows of the product table consistently reference repeat product numbers and session IDs, is that going to get messy?

Am I on the right track? Or can you propose an alternative design pattern that I have yet to consider?

Please let me know if you need more information. Thank you in advance!


texts: Truncate Table With Partitions - aligned/ non-aligned indexes

texts:
SQL Server 2016 introduced a modification to the TRUNCATE TABLE statement which allows you to truncate a specific partition or set of partitions from a table:

  TRUNCATE TABLE < tablename > WITH (PARTITIONS (< partition_id >));

An article about the TRUNCATE TABLE WITH PARTITIONS contains the next note:

  To start using the new TRUNCATE TABLE statement option, make sure that
  all your table indexes are aligned with the source table. If there are
  non-aligned indexes in your table, drop or disable these indexes,
  truncate the partition(s) you want then rebuild the indexes again.

Does SQL Server 2016 provide an easy way (e.g. a query) to determine if the indexes of the partitioned tables are all aligned? (I want to avoid having to drop or disable indexes.)

Update 1

Example output of link:

*One index consists of 2 columns, but only one column is used in the partition function.

Is the index PK_TEST1 aligned or non-aligned?*

index_name: PK_TEST1
partition_function: psTEST1
partition_ordinal: 1
key_ordinal: 1
is_included_column: 0
columnName: COLUMN1

#

index_name: PK_TEST1
partition_function: psTEST1
partition_ordinal: 0
key_ordinal: 2
is_included_column: 0
columnName: COLUMN2


texts: Is there any collection of PowerShell scripts that help the task of SQL Server maintenance?

texts:
I've been studying PowerShell and I've been (slowly) getting it into my day-to-day DBA tasks.

So far I've seen a bunch of disconnected PowerShell scripts for many disparate administration activities.

What I'd like to know is if there is an official (or recognizable) PowerShell scripts collection or repository that people usually go to for SQL Server PowerShell scripts for common DBA activities (something like Ola Hallengren's T-SQL scripts).


texts: Recommendation on changing Azure SQL Database tier weekly

texts:
We have an Azure SQL Database of size 50GB in the S6 tier. On weekends, the usage is very minimal.

Is it okay to change the tier to S0 every weekend for cost savings, and change back to S6 on Monday?

Are there any other steps to perform prior to the tier change? Do you have any other suggestions?


texts:
I got the following error message regarding a SQL query I'm running in a program. SQL Server 2005 T-SQL.

  Heterogeneous queries require the ANSI_NULLS and ANSI_WARNINGS options to be set for the connection. This ensures consistent query semantics. Enable these options and then reissue your query. (severity 16)

Fixing it is easy, set ANSI_NULLS and ANSI_WARNINGS ON, but I wanted to know what a heterogeneous query is. A Google search brings up dozens of results telling me to set ANSI_NULLS and ANSI_WARNINGS, nothing explaining what the term means. The query is:

UPDATE SRV.DB.DBO.TABLE SET Column=
            (SELECT Column
            FROM SRV1.DB.DBO.TABLE)

I'm thinking this is due to connecting to multiple database engines in one query, as I've never gotten this error otherwise.
Does "Heterogeneous" just refer to querying two different database engines in this context?


texts: pgAdmin 4, force "The application has lost the database connection:"

texts:
Whenever I put my computer in sleep mode pgAdmin 4 loses the connection to the remote Postgresql database (and that's fine). When I start up the computer again I have two options. Either restart pgAdmin server on my computer or start a query in a pgAdmin window and wait about 10-15 minutes for this prompt:

The application has lost the database connection:
⁃ If the connection was idle it may have been forcibly disconnected.
⁃ The application server or database server may have been restarted.
⁃ The user session may have timed out.
Do you want to continue and establish a new session?

Is there any way I can force establishing a new session so I can continue my work in the same browser tab without waiting for pgAdmin to timeout and ask me for permission to start a new session?
By the way, is this terrible practice? It is very convenient to just pick-up where I left off but it doesn't seem like a habit that they encourage.


texts: Scalable table structure for periodically updated stats that get aggregated over time

texts:
I receive statistical data every 30 seconds that I want to store in my database so that I can analyze later.  For example, every 30 seconds I could receive the number of oranges sold at a store in the last 30 seconds.  Later, I want to retrieve this data from the database and use it to generate charts showing information like the number of oranges sold for a store over the last 24 hours, last x weeks, last x months, and last x years.

If I just dump everything into one table, it seems like it would grow very quickly, especially if you have lots of data sources (stores).  My thought was that the data could be averaged so that it was less granular over time.  That is, keep detailed records over the last couple of hours (entries in the DB for every 30 seconds), then perhaps averages of 15 minute time spans for the last few weeks, then keep averages of each day for the last few months, etc.

This way you have a large number of recent records, a good number of relatively older records, and a few old records.  However all the data is still there, it's just summed up and averaged into one entry over days or months instead of 30 seconds.

Does this approach make sense?  Is there a better approach?  How would I organize this into a table?  Would it be multiple tables?  Is SQL (probably MySQL) a good fit or would something work better?  Any thoughts on this would be greatly appreciated!


texts: How to make DISTINCT ON faster in PostgreSQL?

texts:
I have a table station_logs in a PostgreSQL 9.6 database:

    Column     |            Type             |
---------------+-----------------------------+
 id            | bigint                      | bigserial
 station_id    | integer                     | not null
 submitted_at  | timestamp without time zone |
 level_sensor  | double precision            |
Indexes:
    "station_logs_pkey" PRIMARY KEY, btree (id)
    "uniq_sid_sat" UNIQUE CONSTRAINT, btree (station_id, submitted_at)

I'm trying to get the last level_sensor value based on submitted_at, for each station_id. There are around 400 unique station_id values, and around 20k rows per day per station_id.

Before creating index:

EXPLAIN ANALYZE
SELECT DISTINCT ON(station_id) station_id, submitted_at, level_sensor
FROM station_logs ORDER BY station_id, submitted_at DESC;

 Unique  (cost=4347852.14..4450301.72 rows=89 width=20) (actual time=22202.080..27619.167 rows=98 loops=1)
   ->  Sort  (cost=4347852.14..4399076.93 rows=20489916 width=20) (actual time=22202.077..26540.827 rows=20489812 loops=1)
         Sort Key: station_id, submitted_at DESC
         Sort Method: external merge  Disk: 681040kB
         ->  Seq Scan on station_logs  (cost=0.00..598895.16 rows=20489916 width=20) (actual time=0.023..3443.587 rows=20489812 loops=$
 Planning time: 0.072 ms
 Execution time: 27690.644 ms

Creating index:

CREATE INDEX station_id__submitted_at ON station_logs(station_id, submitted_at DESC);

After creating index, for the same query:

 Unique  (cost=0.56..2156367.51 rows=89 width=20) (actual time=0.184..16263.413 rows=98 loops=1)
   ->  Index Scan using station_id__submitted_at on station_logs  (cost=0.56..2105142.98 rows=20489812 width=20) (actual time=0.181..1$
 Planning time: 0.206 ms
 Execution time: 16263.490 ms

Is there a way to make this query faster? Like 1 sec for example, 16 sec is still too much.


texts: Can't reduce cost of query plan and get rid of Key Lookup because of cursor

texts:
I have tried to create a non-clustered index on the fields that are in the output list which are created_by and Chk1002 .I don't have a column called Chk1002  anywhere .
I have read here that it has to do with the Cursor .

Is there any way I could keep the Cursor and get reduce the cost of the  Query plan and get rid of the Key Lookup?

     DECLARE @ClaimUniqueNo INT
        DECLARE ClaimAudit CURSOR
                FOR
                    SELECT CA.Claim_Audit_ID, CA.Claim_Audit_Action, CA.Create_Date, CA.Created_By, C.Claim_Status
                    FROM dbo.Claim_Audit_Tbl CA LEFT JOIN Claim_Tbl C
                    ON CA.Claim_Unique_No = C.Claim_Unique_No
                    WHERE CA.Claim_Unique_No = @ClaimUniqueNo
                    ORDER BY Create_Date ASC


texts: Need to backup remote database using mysqldump

texts:
I tried to backup the database of a remote server using automysqlbackup.

From this platform, I learnt it was not possible without ssh and I need to back it up on a remote machine and then rsync or scp to my local machine, so I am now working with mysqldump.

The size of the database is almost 12GB.

mysqldump -h 192.168.10.209 -u user -ppassword testdb   > dump.sql

I tried backing it up and due to internet issues I have been disconnected twice already., and got this error once:

mysqldump: Couldn't execute 'SELECT /*!40001 SQL_NO_CACHE */ * FROM `helmet`': Lost connection to MySQL server during query (2013)

Is there way to continue from where it got disconnected using msyqldump?

I also read that mysqldump is not good for such a big database backup but I need to test it from here only at this moment.

Are there any options that can be used to make it work smoothly and backup my database?


texts: Count total where owner_user_id = some_value and deleted_by_owner = 0 or to_user_id = some_value and deleted_by_to = 0

texts:
I didn't know if this is possible using just a query or if I need some procedural logic on this one.

I have this table:

CREATE TABLE `default_messages_users` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `message_id` int(11) NOT NULL,
  `owner_user_id` mediumint(8) NOT NULL,
  `to_user_id` mediumint(8) NOT NULL,
  `read_message` tinyint(1) NOT NULL DEFAULT '0',
  `date_read` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `deleted_by_owner` tinyint(1) NOT NULL DEFAULT '0',
  `deleted_by_to` tinyint(1) NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`),
  UNIQUE KEY `message_id_owner_user_id_to_user_id_UQ`
      (`message_id`,`owner_user_id`,`to_user_id`)
) ENGINE=MyISAM
  AUTO_INCREMENT=2
  DEFAULT CHARSET=utf8
  COLLATE=utf8_unicode_ci

And a simple record for testing purpose:

INSERT INTO `default_messages_users` (`id`, `message_id`, `owner_user_id`,
    `to_user_id`, `read_message`, `date_read`, `deleted_by_owner`, `deleted_by_to`)
VALUES
    (1, 1, 1, 2, 0, '2012-09-04 14:09:14', 0, 0);

For the query:

SELECT Count(*)
FROM default_message_users
WHERE owner_user_id = @SomeValue and deleted_by_owner = 0
    or to_user_id = @SomeValue and deleted_by_to = 0

If @SomeValue = 1 then the query should return this row if owner_user_id = 1 && deleted_by_owner = 0 or to_user_id = 1 && deleted_by_to = 0, is the same as owner_user_id == to_user_id == 1 but always checking in pairs as explained before.

Is that possible using just a query or do I need programming help here?


texts: NTLM vs Kerberos authentication - questions

texts:
We have 4 servers, all are members of the same Domain:

1. SQL 2012 on Windows Server 2016
2. SQL 2012 on Windows Server 2012
3. SQL 2017 on Windows Server 2016
4. SQL 2017 on Windows Server 2016

I noticed that on first two servers, domain users are connecting using NTLM only (sys.dm_exec_connections DMV, auth_scheme column)

Researching how to change auth_scheme to Kerberos, learned that "Write ServicePrincipalName" privilege must be granted to SQL Server Service Account in the Active Directory Service Interfaces (ADSI Edit)

Right now privilege "Write ServicePrincipalName" is NOT granted to the service account

Before granting that privilege to SQL Service Account, I decided to check auth_scheme on the 3rd and 4th servers, and found out that domain users are actually connecting using Kerberos auth_scheme to those servers

Question is - how can this be that while "Write ServicePrincipalName" privilege is turned off for the SQL service account, domain users are connecting using Kerberos auth to servers 3 and 4, and as NTLM to servers 1 and 2 at the same time ?

Is there some setting somewhere, that makes the difference between servers 1,2 and 3,4 and how users connect to them ?


texts: DROP PROCEDURE IF EXISTS not included in mysqldump

texts:
I'm dumping my stored procedures only using the following command:

mysqldump --routines --no-create-info --no-data --no-create-db --skip-opt databasename -u username -p > outputfile.sql

but the resulting dump file doesn't include a DROP PROCEDURE IF EXISTS before each procedure declaration.

How to add the drop query to my dump?

Thank you.


texts:
I have a cluster of 4 nodes in a data center dc1
Rack1. Now I have to create data center dc2 into same cluster. Will I need to reconfigure whole cluster or is there any way?


texts: Why do SQL Server auto-created statistics sometimes include the primary key?

texts:
I had always understood that SQL Server auto-created statistics only include one column.  In other words, they are not multi-column statistics.
However, today I came across some auto-created statistics that have two columns.  The second column is always the primary key of the table.   There are other auto-created statistics on the same table that don't have a second column.

auto_created = 1 on the sys.stats table.
Also, some statistics have a different naming convention on the exact same tables. Some of them have a column name in the statistic name.  What makes SQL use a different naming convention?
For example,

_WA_Sys_BegEffDate_7363C5E4
_WA_Sys_0000000B_7363C5E4

These are not causing problems, I am just curious about it.


texts: MySQL - Exclude a result if even one foreign key doesn't meet criteria (date availability checker)

texts:
I have three tables, one that lists regions, one that lists hotels and another that lists reservations. The hotels table has a foreign key for the regions table, to show which hotel is within which region, and the reservation table has a foreign key for the hotels table, to list reservations made against a hotel.
I'm trying to make it so that you can input a date, and the hotel isn't returned in the results, if that date is unavailable (though it's also possible to NOT input a date).
Currently, using the query listed at the bottom of the page, it lists out all the hotels even if they don't have a reservation against them. At this time, only one hotel has any reservations, and at that, there are two reservations in total. Before I included the 'DISTINCT' clause, to make it so that duplicate entries aren't listed, it would list out all hotels, including the one with reservations, except this one it would list TWICE as there were TWO reservations. Now, with the DISTINCT clause, it will only ever show one result, but it will ALWAYS show one result. It essentially says "This entry is in the time range specified, so we can't show that, but the other one isn't, so we will show it", even though it's the same hotel.
For reference, my query currently, using 2020-09-02 as the date to check availability for;
SELECT DISTINCT hotels.hotel_name FROM hotels

LEFT JOIN reservations ON hotels.id=reservations.hotel_id

LEFT JOIN regions ON hotels.region_id=regions.id WHERE Cast('2020-09-02' as DateTime) NOT BETWEEN reservations.start_date AND reservations.end_date OR reservations.start_date is NULL OR reservations.end_date is NULL;

Current results (Where date IS available);
Hotel 1 with two reservations
Hotel 2
Hotel 3
Hotel 4
Hotel 5

Current results (Where date ISN'T available);
Hotel 1 with two bookings
Hotel 2
Hotel 3
Hotel 4
Hotel 5

Current results (Where date is available, but 'DISTINCT' ISN'T used);
Hotel 1 with two bookings
Hotel 1 with two bookings
Hotel 2
Hotel 3
Hotel 4
Hotel 5

Current results (Where date isn't available, but 'DISTINCT' ISN'T used);
Hotel 1 with two bookings
Hotel 2
Hotel 3
Hotel 4
Hotel 5

Expected results (If chosen date ISN'T available);
Hotel 2
Hotel 3
Hotel 4
Hotel 5

Expected results (If chosen date IS available);
Hotel 1 with two bookings
Hotel 2
Hotel 3
Hotel 4
Hotel 5

Hopefully the expected results scenarios give an idea of what I'm going for.
Any help is appreciated.
EDIT: Link to a DBFiddle of the issue here: https://www.db-fiddle.com/f/n9Ew6nLCWQHDjYfxzp49xB/16


texts: manual execution of specific remote query with postgresql_fdw

texts:
In 9.4b2, postgresql_fdw doesn't know how to "push down" aggregate queries on remote tables, e.g.

> explain verbose select max(col1) from remote_tables.table1;
                                         QUERY PLAN
---------------------------------------------------------------------------------------------
 Aggregate  (cost=605587.30..605587.31 rows=1 width=4)
   Output: max(col1)
   ->  Foreign Scan on remote_tables.table1  (cost=100.00..565653.20 rows=15973640 width=4)
         Output: col1, col2, col3
         Remote SQL: SELECT col1 FROM public.table1

It would obviously be much more efficient to send SELECT max(col1) FROM public.table1 to the remote server and just pull the one row back.

Is there a way to perform this optimization manually? I would be satisfied with something as low-level as (hypothetically speaking)

EXECUTE 'SELECT max(col1) FROM public.table1' ON remote RETURNING (col1 INTEGER);

although of course a higher-level construct would be preferred.

I'm aware that I could do something like this with dblink, but that would involve rewriting a large body of code that already uses foreign tables, so I'd prefer not to.

EDIT: Here's the query plan for Erwin Brandstetter's suggestion:

=> explain verbose select col1 from remote_tables.table1
-> order by col1 desc nulls last limit 1;
                                            QUERY PLAN
---------------------------------------------------------------------------------------------------
 Limit  (cost=645521.40..645521.40 rows=1 width=4)
   Output: url
   ->  Sort  (cost=645521.40..685455.50 rows=15973640 width=4)
         Output: col1
         Sort Key: table1.col1
         ->  Foreign Scan on remote_tables.table1  (cost=100.00..565653.20 rows=15973640 width=4)
               Output: col1
               Remote SQL: SELECT col1 FROM public.table1

This is better, in that it fetches only col1, but it's still dragging 16 million rows over the network and now it's also sorting them.  By way of comparison, the original query, applied on the remote server, doesn't even have to scan, because that column has an index.  (The core query planner isn't clever enough to do that for the modified query applied on the remote server, but that's minor.)


texts: Installing oracle RAC 10.2.0.1 install and patch to 10.2.0.5 directly or install CRS ASM and RDBMS then patch?

texts:
I'm installing my first RAC and I was wondering if I should install CRS 10.2.0.1 (we are still at 10g for compatibility reasons) then patch to 10.2.0.5 before I install ASM (then patch) and finally rdbms (then patch)

or if I should install CRS / ASM / RDBMS 10.2.0.1 and then patch to 10.2.0.5 in the same order.


texts: Measuring 'data items accessed' by index technique to retrieve item (PostgreSQL)?

texts:
In the example I have attached, a btree is being used to search for the number 19. With PostgreSQL execution plan, buffers read/hit are being shown. Which would they be in the example I have attached?

I am pretty sure it is the nodes of the tree and not having much to do with the green arrows. In the number of pages used to make a search, I expect this to be 3. But in the case of data items accessed, I expect those to be 6 (green arrows as indication). How can I measure this latter statistic?


texts: Active Expensive Queries and Recent Expensive Queries Meaning

texts: I am trying to understand the active expensive queries and recent expensive queries in Activity Monitor. What is expensive means here? Does it means expensive in term of cpu, block, memory, reads?


texts:
Is there any technology to replicate a table from public schema on the master server to a table on the slave server but not in the public schema.

MASTER SERVER                              SLAVE SERVER
pubic.table_name  ----- replicate ----->   schema1.table_name

I tried slony-I but the problem that the table on the slave must in the public schema too.


texts: SQL Management Studio Intellisense - Hide global variables

texts:
I am not sure if this is possible, but when writing a query to select from a table, when I bring up the intellisense drop down with CTRL + Spacebar, it gives me a list of all variables (global, and on the table).

Is there any way to make this only show the variables in the table I am selecting from? Its basically completely useless unless you know the name of the field already, so then what is the point of the intellisense drop down?


texts: Redundant FK on parent relationship is denormalization?

texts:
Let's say I have this database

client
| id | status |

sale
| id | client_id | date |

sale_item
| id | sale_id | *client_id* |

The *client_id* column is redundant, because we can JOIN and get client from sale_item rows. However, if I'd often need to list sales by client, that JOIN could be expensive.

Is this considered denormalization? Or does it have another technical name?

In my understanding the decision to keep *client_id* is about space x speed. Consistency problems introduced by common denormalization examples doesn't apply here because id will never change neither be deleted (if we need to "remove" we can just set status=0).


texts:
I got a problem with permissions during installation of pgpoolAdmin(newest version). The problem is when I get to window with main config that should be all okay to move to next installtion step.

The problem is access to two files: pgpool.conf and pcp.conf. Application says: "Write access denied". I tried "chmod 777" on those config files and "chown apache config_file_name" nothing works. I still see "Write access denied". Whole pgpool application is in /var/www/html/pgpoolAdmin. Location of those two files is /usr/local/etc/. I'm using Centos 6.5.

Sorry for no pictures but I have bo permission to upload


texts: SQL Server 2005 tempdb Time out occurred while waiting for buffer latch type 4 (pagenum)

texts:
Three separate processes have failed in the last two days with very similar errors:

Time-out occurred while waiting for buffer latch type 4 for page (1:1189832),
database ID 2.

Time-out occurred while waiting for buffer latch type 4 for page (1:1189827),
database ID 2.

Time-out occurred while waiting for buffer latch type 4 for page (1:1189827),
database ID 2.

Two of them are exactly the same, the other is accessing a slightly different pagenum.  Database #2 is tempdb. I tried to run DBCC PAGE(tempdb, 1, 1189827, 1) but the output is just gibberish to me. I'm fairly lost.

Does anyone know what the root cause of these timeouts might be? (I've seen dozens of suggestions on other forums, among which was memory corruption)

Any help would be greatly appreciated. If you would like to read the DBCC PAGE output, ask and I will post it.


texts: Explain output difference between this inner and natural join

texts:
A NATURAL JOIN is a JOIN operation that creates an implicit join
  clause for you based on the common columns in the two tables being
  joined. A NATURAL JOIN can be an INNER join, a LEFT OUTER join, or a
  RIGHT OUTER join. The default is INNER join. Source

So a natural join can be a shorthand way of implementing inner join if both tables have a common column.

Consider following table:

SELECT * FROM t1;
/*
        ID PLANET
---------- --------
         1 jupiter
         2 earth
*/

We join the table to itself:

SELECT first.id, first.planet FROM t1 first INNER JOIN t1 second ON first.id=second.id;
/*
        ID PLANET
---------- --------
         1 jupiter
         2 earth
*/

Try to do the same with natural join notation:

SELECT id, planet FROM t1 NATURAL JOIN t1;
/*
        ID PLANET
---------- --------
         1 jupiter
         1 jupiter
         2 earth
         2 earth
*/

Given that natural join here is implemented using inner join why the multiple rows?

Update:

Running same join with table aliases has different output:

SELECT id, planet FROM t1 first NATURAL JOIN t1 second;
/*
        ID PLANET
---------- --------
         1 jupiter
         2 earth
*/


texts: HKHostFreezeCkptTrimming on SQL Server 2017

texts:
Have a SQL Server 2017 Standard edition box with build 14.0.3045.24 with one memory-optimized table. When our 3rd party backups complete the SQL Server 2017 error log shows several [INFO] messages containing HKHostFreezeCkptTrimming(), HKHostBackupDeleteContext(), getMaxUnrecoverableCheckpointID() and with the same user database ID. Any of you familiar with these messages? Is this a bug or is there a fix for these messages?

Additional testing has shown these [INFO] messages appears during full, differential, transaction log, and manual copy-only backups. Performance is good and users aren't complaining, but considering the amount of backups that are required these messages are filling the error log.

Anything I can do to limit the entry of these messages to the error log? Or is this a case of dealing with additional messages?


texts:
So in my database I have items, and those items have categories.

I've created a small sample data.

I'm creating a report where a user will be able to filter the items by colour, category and some other attributes, and then it should simply list items that fit my parameters in separate category columns.

I also don't know how many categories the user will chose and thus I don't know how many columns my report should have.

This is how a pivot looks like when I apply it to my sample data. This is basically the format that I want, however I want to get rid of the nulls. I need the columns to just 'independently' list the items, like so

Since there was no items from the XX category in my sample dataset, the column is not there. The rest gets 'aggregated'.

Okay so this brings me to my main question. Or questions, rather, I think I have two.

1) I am new to SQL and I'm still learning so correct me if I'm wrong, but am I right to think that this is simply a task that 'should not be performed' in relational databases? I feel like this is going against the core rule, that one row is one piece of data, one entry. Here my one row becomes three (or more/less) pieces of data, and there is no explicit relation to between them.

I previously had to make a report where I displayed two items side by side, the user could pick a shop for the left side, another shop for the right side and see the best selling items from the 'left group' and the best selling items from the 'right group'. So I simply joined the two groups by ROW_NUMBER generated ranking and my data changed from 'one item per entry' to 'Nth posixtion in chosen shops per entry'. That made sense.

This here doesn't, at least to my current understanding. But that brings me to my second question:

2)They still want me to do it. How do I?

So far I have thought of two possible solutions.

One is I pick categories separately, then I join them together based on some kind of ROW_NUMBER, to assure one-to-one relationship (I don't really care about ordering)

So if my desired categories were 'OD', 'AL', 'UT', I'd take take them out with 3 separate queries like so

SELECT
    ROW_NUMBER() OVER (ORDER BY (select null)) AS rn
    ,Item
    ,Category
INTO #OD
FROM
    sth
WHERE
    Category = 'OD'

And so on for every category, and then join them together using 'rn' column. This works, but because I do not know how many categories the report user will choose, it gets problematic. Perhaps that could be done with dynamic SQL? I'm not sure and I'm not really a big fan of using it...

I've also thought of a different solution but I am not sure if that's doable. If it were possible to somehow assign numbers like this:

So the row number 1 is labeled '1' because it's the first value for 'OD' it encountered. Row 2 is labeled '1' as well because it's the first falue for 'AL' it encountered.
That way row number 4 is labeled '2' and row number 5 is labeled '3' because it's the second and third value for 'OD' column respectively. And then row number 6 is labeled '2' again because it's the second value for the 'AL' column.

That way I could use MAX() function and group by those assigned numbers. All the rows assigned 1 will get aggregated to one row, all the rows assigned 2 will be aggregated to another row etc. I am certain that there will aways be one item per row in my pivot, because an item cannot be of two categories.

I also feel like that solution would be much easier to do in a dynamic way (unknown number of columns - categories chosen) but I cannot figure out how to get such a numbering and if that is even possible.

Looking forward to your answers guys, I'm stuck!


texts: In Oracle DB 11.2g, is it possible for the audit trail to explicitly mention account locking events?

texts:
As it is, the audit trail will show login failures, and an attempt to log into a locked account (ORA-01017 and ORA-28000). What I would like to know is if it's possible for it to make a mention when the locking itself happens, not just in response to a user's actions.

So ideally it will say something to the effect of:

user login attempt failed
user login attempt failed
user login attempt failed
user account has been locked for multiple login failures

Is something like this possible?


texts: Access Sql Server Linked Server during Logon Trigger

texts:
I am trying to log users logons to my Sql Server (A) in another Sql Server (B, different location physically), but when I try to log in I get the error Login failed ... due to trigger execution and this is the body of my logon trigger:

alter TRIGGER [tgr_blocker]
ON ALL SERVER
FOR LOGON
AS
BEGIN
--
    declare @IP nvarchar(50)
    declare @UN nvarchar(50)
    declare @HostName nvarchar(100)
    declare @ProgramName nvarchar(100)

    set @UN = ORIGINAL_LOGIN()
    select @IP = client_net_address FROM sys.dm_exec_connections WHERE
        session_id = @@SPID
    SELECT @HostName = host_name, @ProgramName = program_name  FROM
        sys.dm_exec_sessions   WHERE session_id = @@SPID

    insert into [LinkedServerIP].SecurityLog.dbo.AAA_Logger
    (IP,HostName,UserName,Session,Date,ProgramName,ServerName,Result)
    values (@IP,@HostName,@UN,@@SPID,GETDATE(),@ProgramName,@@SERVERNAME,'Successful')

end

and when I test the insert in a query it works, but when I log in it does not work and results to the error, I thought maybe Sql Server can not access Linked Servers during Logon Triggers, is that true? If not where else should be the problem?


texts: Sql server service wont start after disabling TLS 1.0 and SSL 3.0 on windows

texts:
We have been hardening our servers for some time now and recently we disabled SSL 3.0 because of the poodle attack. When I did this on one of our test servers SQL Server failed to start up after the restart.

I have been able to reproduce this on Windows Server 2012 and Windows 7 by disabling TLS 1.0 and SSL 3.0 through the registry. I am using SQL Server 2012 on the server machine. On my windows 7 machine sql server 2012 and sql server 2005 will not start with those disabled.

These are the event log errors I get:

Application Logs:

  (28/10/2014 8:38:54 AM) SQL Server could not spawn
  FRunCM thread. Check the SQL Server error log and the Windows event
  logs for information about possible related problems. (28/10/2014
  8:38:54 AM) Could not start the network library because of an internal
  error in the network library. To determine the cause, review the
  errors immediately preceding this one in the error log. (28/10/2014
  8:38:54 AM) TDSSNIClient initialization failed with error 0x80090331,
  status code 0x1. (28/10/2014 8:38:54 AM) TDSSNIClient initialization
  failed with error 0x80090331, status code 0x80.

System Logs

  (28/10/2014 8:38:54 AM) The SQL Server (MSSQLSERVER)
  service terminated with service-specific error %%-2146893007.
  (28/10/2014 8:38:54 AM) A fatal error occurred while creating an SSL
  server credential. The internal error state is 10013.

Done anyone know have we can keep SSL 3.0 and TLS 1.0 disabled and get SQLServer server to start?


texts:
This is probably a simplistic question, but I would like to check on the mechanics of rolling back a transaction.

As far as I understand, the steps are as follows:

BEGIN TRANSACTION (or equivalent)
One or more SQL statements
COMMIT or ROLLBACK

As far as I know, the transaction will be automatically rolled back if one of the SQL statements fails, such as an invalid INSERT or UPDATE.

This would suggest that the above process, up to committing could be in a manually entered collection of statements.

However, at what stage do I make the decision to roll back? Does this suggest that ROLLBACK is more something that would occur, say, in a procedure? In other words, does one ever roll back manually?

By procedure, I’m not necessarily referring to a stored procedure. I suppose some application might be doing the same thing.

I think this question applies to any SQL database in general (provided it supports transactions, of course).


texts: How do I return data from a table where the data is the max date of the previous month's data submissions (-1 of current date)?

texts:
I have a stored procedure where I select the latest/max submit_date data for a tracker_date and client from a source table called Tracker_Live, and insert it into a new table called Tracker_Live_Latest.

I am wanting to do the same as the above, but for last month, where last month is one month less than today's month.

It seems there should be a way to use DATEPART(m, DATEADD(m, -1, getdate()) to get last month and then max of that month.

There are multiple submissions per month to the source table, and the submit_date is recorded as 'submit_date'.

Source table, called Tracker_Live, example (note there are two submit_dates per month for this client):

Assuming today's date is 2014-11-22, and knowing that other client_nbr's may have a different latest submit_date for October, I would want the destination table, called Tracker_Live_Last_Month to have the following data:

The stored procedure code that I use to get the max submit_date to insert into a table called Tracker_Live_Latest is:

delete from Tracker_Live_Latest;

insert into tracker_live_latest
(client_nbr, tracker_date, submit_date)
select client_nbr, tracker_date, max(submit_date)
from tracker_live
group by client_nbr, tracker_date
order by client_nbr, tracker_date;

update tracker_live_latest
set retainer = tl.retainer, live = tl.live, bonus = tl.bonus
from tracker_live as tl
where tracker_live_latest.Client_Nbr = tl.Client_Nbr
and tracker_live_latest.Tracker_Date = tl.Tracker_Date
and tracker_live_latest.Submit_Date = tl.Submit_Date

which will return the following from the above source table:

Any help to insert the correct data into Tracker_Live_Last_Month is appreciated!!

Shara


texts:
I need to update a value in my xml datatype column, currently I am able to pull specific values using

Select AllUserData.tp_ColumnSet.value('nvarchar13[1]', 'varchar(max)') AS AssetId...

My data in the column looks like this, because this is from Sharepoint and I cannot modify the xml format in any way.

<bit1>0</bit1>
<nvarchar1>1995</nvarchar1>
<nvarchar3>Ford</nvarchar3>
<nvarchar5>White</nvarchar5>
<nvarchar6>Van</nvarchar6>
<nvarchar7>123456789</nvarchar7>
<nvarchar9>1234</nvarchar9>
<nvarchar13>623</nvarchar13>
<nvarchar20>Company</nvarchar20>
<nvarchar22>Dept</nvarchar22>
<nvarchar23>Section</nvarchar23>
<nvarchar24>User</nvarchar24>

I have tried to piece together a statement from other questions much like the example with no success and no working where clause as shown below.

update AllUserData
set tp_ColumnSet.Modify('replace value of (nvarchar1)[1] with "1996"')
where AllUserData.tp_ColumnSet.value('nvarchar13[1]', 'varchar(max)') = 623

How can I modify the the "Year" by designating what AssetID it is with a where clause?


texts: load_file() doesn't work but load data local infile works

texts:
I'm trying to load a non-regular file with MySQL:

select load_file ('/proc/cmdline')

It returns NULL

So I checked the documents here,

the file is readable, because as mysql user, I can execute cat /proc/cmdline from the host
the file didn't exceed size limit, because reading of /etc/passwd works, which is larger than that
I didn't set secure_file_priv variable, show variables like 'secure_file_priv' shows an empty result

Any ideas?

load data local infile '/proc/cmdline' works


texts: Storing daily yield curve data in SQL table

texts:
I have daily yield curve data for integer maturities that should be stored in a MSSQL server.
Currency.csv
Currently, the data is stored in CSV files per currency in the format:
date, 1y, 2y, 3y, ...
08/12/2020, 0.4, 0.5, 0.6, ...

I am wondering what the best choice for a table would be.
Same Format Table
The current data could be directly translated to a table with columns:
+------------+------+------+------+------+
|    date    |  1y  |  2y  |  3y  |  ... |
+------------+------+------+------+------+
| 08/12/2020 |  0.4 |  0.5 |  0.6 |  ... |
+------------+------+------+------+------+

Alternate Table Design
However, I could also store it like:
+------------+-----------+-----------+--------+
|    date    |  currency |  maturity |  value |
+------------+-----------+-----------+--------+
| 08/12/2020 |  "EUR"    |         1 |    0.4 |
| 08/12/2020 |  "EUR"    |         2 |    0.5 |
| 08/12/2020 |  "EUR"    |         3 |    0.6 |
+------------+-----------+-----------+--------+

Obviously, the second approach would take more space than the first, but I would see the advantage that I am not restricting the value of the maturity in case in the future it should be changed to non-integer values or to maturities that don't have a column yet.
Which of the two table designs would be better? Are there other designs that would fit better?


texts:
I am using MySQL Community Edition on Amazon RDS service. It is a very large database, 2/3 tables has about 10 millions records and 4-5 tables has 7-8 millions records.
Questions

How much capacity does MySQL Community Edition have?
Should I have to purchase MySQL Enterprise Edition for my application?
Where can I get pricing information for the same?

Thanks


texts: How to accomplish SQL Server 2012+2014 high availability

texts:
Maybe I'm searching for the wrong thing, but I can't see how I can do this.

I have two servers:

Server A (has SQL Server 2012 installed)
Server B (has SQL Server 2014 installed)

Server A provides an API via IIS. The code both inserts and gets data from the database depending on the API call.

Server B does exactly the same for its local database.

I would like to have it so that the two databases are consistent copies of each other. I do believe this is what HA accomplishes.

So a load balancer, for example, might direct API calls to A sometimes, and B sometimes, but the data needs to be consistent either way.

I found some documentation on HA for SQL Server 2012, but not on how to do this for a mixed environment of 2012 and 2014.

Any help or pointers to documentation would be great! Thanks.


texts: What is a suitable index for this query?

texts:
Below query is taking long time - around 540 seconds.
The table did not have any indexes. I created indexes on columns in the WHERE clause and other fields but the query is not using these indexes.
Please suggest good indexes for this query.

Query:

select
    alias_to,
    fake_uri,
    concat(ifnull(parameters,''),ifnull(parameters2,''),ifnull(parameters3,'')) as parameters,
    precedence,
    template_name
from site_map_template
order by precedence, fake_uri desc;

Explain output:

+----+-------------+-------------------+------+---------------+------+---------+------+------+----------------+
| id | select_type | table             | type | possible_keys | key  | key_len | ref  | rows | Extra          |
+----+-------------+-------------------+------+---------------+------+---------+------+------+----------------+
|  1 | SIMPLE      | site_map_template | ALL  | NULL          | NULL | NULL    | NULL | 7616 | Using filesort |
+----+-------------+-------------------+------+---------------+------+---------+------+------+----------------+
1 row in set (0.00 sec)

site_map_template table:

CREATE TABLE `site_map_template` (
  `row_mod` datetime DEFAULT NULL,
  `row_create` datetime DEFAULT NULL,
  `template_name` varchar(50) COLLATE latin1_bin DEFAULT NULL,
  `alias_to` varchar(100) COLLATE latin1_bin DEFAULT NULL,
  `package` varchar(50) COLLATE latin1_bin DEFAULT NULL,
  `fake_uri` varchar(255) COLLATE latin1_bin DEFAULT NULL,
  `precedence` int(11) DEFAULT NULL,
  `parameters` varchar(255) COLLATE latin1_bin DEFAULT NULL,
  `parameters2` varchar(255) COLLATE latin1_bin DEFAULT NULL,
  `parameters3` varchar(255) COLLATE latin1_bin DEFAULT NULL,
  KEY `idx_1372` (`template_name`),
  KEY `idx_n1` (`precedence`),
  KEY `idx_n2` (`fake_uri`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_bin


texts:
I have two MySQL tables:

╔═══════════════════╗
║ wpss_question_set ║
╠═══════════════════╣
║ id                ║
║ quesset           ║
╚═══════════════════╝

╔════════════════╗
║ wpss_questions ║
╠════════════════╣
║ id             ║
║ question       ║
║ ques_set_id    ║
╚════════════════╝

ques_set_id is the foreign key. The query is:

SELECT
    wpss_quesset.quesset,
    wpss_questions.*
FROM wpss_quesset
JOIN wpss_questions
    ON wpss_quesset.id = wpss_questions.ques_set_id;

I am getting duplicate results on ques_set_id and quesset. How do I select unique records from 'ques_set_id and quesset?


texts: I need to create a check constraint, the problem i face is that the list of values accepted by the constraint changes in the future (ex:now "red,green and blue" in the next month "red,green,blue and white"). How to do this ?


texts: Clients require instance name to connect to clustered instance

texts:
We have a clustered instance, say Instance1, assigned its own virtual network name and IP, via Failover Cluster Manager. This instance is configured to listen on port 1433 in SQL Server network configuration (verified in the logs).

Within our intranet, we can connect to this instance by using just the IP address, say 172.0.1.2 (as well as the FQDN) using SSMS. However, some external clients cannot connect unless they specify 172.0.1.2\Instance1 explicitly.

I don't understand why this is the case. From what I understand, it doesn't make sense that the instance name needs to be specified given that the IP is assigned to the instance itself.

I noted in SQL Server network configuration that the IP address assigned via Failover Cluster Manager is not explicitly listed as an active enabled address. Should that be added?


texts: Difficult migration from Postgres 8.1 (postgis 1.5) to Postgres 9.4 (postgis 2.1)

texts:
I am in charge of a system developed 8 years ago. When I make a dump of the postgres 8.1 database, all the postgis 1.5 functions are included. I have successfully migrated it to a postgres 9.1, because it still supports postgis 1.5, but as soon as I try to migrate to 9.4 I encounter many problems because of all the old spatial functions that are defined during the restore process. There are also all sorts of constraints defined using the old postgis 1.5 functions.

Is there an effective way of updating all these references from postgis 1.5 to postgis 2? Do I need to manually edit the backup files? (I have about 300 tables).

Thanks in advance :-)


texts: Proper way to create logins on Always on

texts: What is the proper way to create logins on Always on? An application database  requires an active directory login and I created that on the primary node. The database is still not in the availability group  yet. I plan to move it there once the application is installed. Do I need to create the same login on the secondary node as well ? Or would the login sync across the secondary node once the database is moved into the AG? The only permission which was requested was db_owner . Are there any other permissions which I need to give to the login to properly work within the availability group ? Please let me know. Thanks !


texts:
I'm trying to create a database for work purpose, and I need you guys to help me. I don't know if my idea possible or not but i want to give a chance to apply it.

As the title I want to make database for all policies and procedures in our organization. The purposes of this database are whenever my manager want to review any policy he can search for the policy by keyword, or title, or the code of each policy, also in order to make any change on that policy, he can review, edit the policy and print it. While he doing some changing on a specific policy I want to keep the original policy as well, so if I want to make a historical report for that policy I can generate all changes happens on that policy, the original one, the first change, the second change and so on.

What I want to is creating a " Master Table " Consist of all updated Policies for "  4 Departments " and I want to create a separate tables for each department. And then create a relationship between all those table, (all of them link with the Policy code column -if possible-). So, whenever the user enter data from the displayed form, this data will be record in master table, and the entered data will be saved in the related department table ( if he enter a data related to the department 1, the data will be saved in the master table and the department 1 table, and etc. ). So the result of all of these are whenever I want to make a report for all entered policies I can generate it from the master table, and whenever I want to make a separate report for each policy I can do that too.

My question is, if this database possible to be create, how i can create it, where I faced a lot of problems while I tried to create it, and if it's not possible, What should I do in order to get that features that my manager want?

Waiting your response.

The Features that I want to be exist are:
1- Display the policy in proper formatting (Making a Report)
2- Editing an existing policy.
3- Adding New Policy.
4- Make a historical report for each policy.
5- Searching bar to be search by keyword, title, policy code, and column name
6- Printing that policy
7- all document should be different by the unique version number.


texts: Why can't RDBM's cluster the way NoSQL does?

texts:
One of the big plusses for nosql DBMS is that they can cluster more easily.  Supposedly with NoSQL you can create hundreds of cheap machines that store different pieces of data and query it all at once.

My question is this, why can't relational DBMS do this like mysql or sql server?  Is it that the vendors just haven't figured out a technical way to do this with their existing product, or is there some issue with the relational model that prevents this from being feasible?  What is so great about the NoSQL way of storing and accessing data (key/value, documents, etc) that makes clustering easier, if this is true at all?


texts: Oracle from_tz with Russian timezones shifts the input timestamp?

texts:
On Oracle 11g, I noticed that using from_tz to attach a TZ to a timestamp gives weird results for Russian timezones, but only after 25th October 2014.

select
from_tz(cast( '01-JUL-2015 12:00:00' as timestamp),'UTC'),
from_tz(cast( '25-MAR-2016 12:00:00' as timestamp),'America/New_York'),
from_tz(cast( '20-OCT-2014 12:00:00' AS TIMESTAMP),'Europe/Moscow'),
from_tz(cast( '20-OCT-2015 12:00:00' AS TIMESTAMP),'Europe/Moscow'),
from_tz(cast( '20-OCT-2015 12:00:00' AS TIMESTAMP),'Asia/Magadan')
FROM DUAL;

Expected:

01-JUL-15 12.00.00.000000000 UTC    25-MAR-16 12.00.00.000000000 AMERICA/NEW_YORK   20-OCT-14 12.00.00.000000000 EUROPE/MOSCOW  20-OCT-15 12.00.00.000000000 EUROPE/MOSCOW  20-OCT-15 12.00.00.000000000 ASIA/MAGADAN

Actual:

01-JUL-15 12.00.00.000000000 UTC    25-MAR-16 12.00.00.000000000 AMERICA/NEW_YORK   20-OCT-14 12.00.00.000000000 EUROPE/MOSCOW  20-OCT-15 11.00.00.000000000 EUROPE/MOSCOW  20-OCT-15 10.00.00.000000000 ASIA/MAGADAN

If from_tz is just meant to be adding a TZ to a TZ-less timestamp, why does it also shift the output timestamp backwards for just Russian timezones?

I suspect it's related to the change in base timezone that happened in Russia in late 2014. From http://www.oracle.com/technetwork/java/javase/tzdata-versions-138805.html:

  Russia will subtract an hour from most of its time zones on 2014-10-26
  at 02:00 local time.

However, I'm not expecting from_tz to be doing any timezone maths, just bolting the TZ onto the timestamp. What's going on here?


texts: Is it possible to create materialized view based on another materialized view in postgres?

texts:
I have a very large table and I summarize it in a materialized view. I then create an even smaller materialized view based on the earlier mview. The second mv is always showing zero rows. Is there a limitation in postgres that all mvs must be based off of a real table? I did a quick search of docs and didn't find anything.

table (millions of rows) -> mv A (thousands of rows) -> mv B (zero rows, but expected hundreds)


texts: One MySQL user from 2 hosts not on same subnet

texts:
I've googled around for this with no luck. I have a master database with one slave. The perms for the slave are set and it runs fine. I'd like to add another slave using a clone of the original server. So far, everything is fine except my slave user is only granted access from one host: 10.10.10.10 . The second slave I'd like to bring up has the IP address 10.10.10.20 and I can't allow access across the entire subnet ( I also can't change the internal IP addresses).

So, I'd like to update the hosts the slaves can access the master to both IP addresses and can seem to figure out how.

Any help is greatly appreciated.


texts: When to use a "middle table" instead of storing a JSON with IDs?

texts:
I'm working on a store for a mobile app, the database is mysql.
I have the store items in a table called items and that table has a column called price_point_id (one item has 1 price point) which references another table called price_points.
Now there is one last table called templates which has a one-to-many relationship with the items table (one template has many items).
I could create a middle table with 2 columns item_id and template_id and join them, but wouldn't it be much faster to avoid a 4 tables join (I need data from items, price points and the middle table, but I only have the template ID to start) like in this example:

But what if I kept the item IDs in the templates table, that would avoid a join with big table (not huge but hundreads of thousands), In this case I could query the template first then use an IN statement to get the items + price points, sure it's still a join but in much smaller tables and I'd be querying by primary keys only. Like in this example:

Do you guys see any draw backs with the second approach?


texts: This is completely lame that I cannot find it, so I have to ask. I know in the reportsmanager site where to enable and disable data sources. But where in the tables of the reporting database is this stored? I've found the data source in the Catalog table, type 5, but don't see anything that remotely looks like a disabled/enabled field. I've also checked several of the other tables, like Policies and DataSource and SecData and etc..., but cannot find it. Can someone enlighten me please. I want to programmtically enable and disable data sources for certain reports, hence the reason I ask. SQL 2014 Standard. Thanks.


texts:
I am encountering concurrent update error when using OLE DB command to execute a update procedure in SSIS.

Below is the segment of code which will check for concurrency update. Currently I removed this segment of code in order for my procedure in SSIS to execute successfully. But should I have this segment of code? Otherwise will I encounter concurrency error?

IF @@ROWCOUNT = 0
BEGIN
    RAISERROR('ConcurrentUpdated', 16, 1)
    RETURN @@ERROR
END


texts: What field is the decimal precision and scale?

texts:
I am using dbvisualizer to connect to databases.
I need to know the P/D of a decimal type.
I want to make sure I am not mistaken. From the image bellow, P is "COLUMN_SIZE" and D is "DECIMAL_DIGITS", right ?


texts: Upgrade to SQL Server from 2008 to 2012 - Developer perspective

texts:
We are in the process of migration from SQL Server 2008 to SQL Server 2012.

The entire process of migration will be taken care by dedicated DBA team.

From a developer prespective, I would like to know `what could be the impacts of upgrade.

We have tables, stored procedures, views, functions and few SSIS jobs.

I have searched over google for a while, and could not find out clear information on what could be impacts on SSIS and DB's

It would be great once I would come to know what could be possible impacts :

So, here are the questions :

Will there by any impact on any features of SQL Server that might not work in 2012 ?
What will be impact on SSIS jobs running on 2008 moved to 2012 ?


texts: set variable result in case statement to be used again in the same select statement

texts:
Is it possible to create, inside a single select statement, like this:

SELECT
   name,
   CASE
      WHEN name = 'a' THEN 'a'
      ELSE 'b'
   END tag,
   CASE
      WHEN tag = 'a' THEN 'a2'
      ELSE tag = 'b2'
   END tag2
FROM table_name

My reason is there is a previous column on the single statement where I want to use its case statement result on my other column.

I want to know if it can be done using variables like:

SELECT
   name,
   CASE
      WHEN name = 'a' THEN 'a'
      ELSE 'b'
   END @result = tag,
   CASE  @result
      WHEN 'a' THEN 'a2'
      ELSE 'b2'
   END tag2
FROM table_name

I know the second code is incorrect but that is the logic I need. Thanks guys!


texts: Does mysqld --ndb-cluster need to be run on MySQL Cluster data nodes?

texts:
I have my management node and sql node on the same server, and 2 data nodes (3 machines in total). Do I need to run ./mysqld --ndb-cluster (or /etc/init.d/mysql-server start) on 1) the data nodes, or 2) on the sql node, or 3) on both?

I am following this which doesn't seem to specify.
http://dev.mysql.com/doc/refman/5.1/en/mysql-cluster-install-first-start.html

Thanks.


texts: Why we can backup postgresql database backup without password?

texts:
I want to backup my PostgreSQL database with pg_dump command that takes a lot of parameters.
One of them is --no-password.

Why this parameter is provided?

I mean if anyone can backup database, so what about security?


texts: Complicated SSRS layout - is that possible?

texts:
I have been ordered to do a report with a pretty sophisticated table layout. Okay, it's not that sophisticated, but we all know how SSRS is with those things. I am pretty certain that layout cannot be achieved, but I want some feedback before I start simplifying it.

This is the table layout. I've replaced everything with 'header' and 'data' accordingly. What gives me a tiny bit of hope is that the dataset will always return one row, so I may ignore the header and detail regions assigned by SSRS. I only need to somehow achieve that layout and I'm good.

I have tried using matrix, it lets me merge cells vertically but then it only lets me merge them horizontally if it is the upppermost row. So guys, is there any chance to insert the photo the way it is done in the image (merging 10 rows vertically and 2 rows horizontally altogether in the middle of the table)?

Kind regards,
Mash


texts: Would it be better for query plans to be split by statement for re-usability?

texts:
From my limited knowledge of how query plans are compiled, stored and retrieved by queries I understand that a multi statement query or stored procedure will generate it's query plan which will be stored in the query plan cache to be used by the query in future executions.

I think this plan is retrieved from the query plan cache with the query hash, which means if the query is edited and executed the hash is different and a new plan is generated as no matching hash can be found in the query plan cache.

My question is: If a user executes a statement that is one of the statements in the multi-statement query can it use that relevant part of the query plan already in the cache for the multi-statement query? I expect the answer is no because the hash values will obviously not match, but would it be better to hash each statement in a multi-statement query so they could be used by users running individual statements from the query?

I expect there are complications that I'm not taking into account (And it's these that I really want to know about) but it seems like we could be storing the same 'statement plan' in many query plans taking up more space and taking more CPU and time to generate.

Could just be showing my ignorance though.


texts: Sort a varchar type column (Alphanumeric Values)

texts:
In my table VARCHAR data type column called CartonNo, that contain either numbers or number with letter it depends on. I want it to be sorted numerically, this would be an acceptable ascending sort order.

Issue No. 1

For example, contains like following data

1-2
1
3-5
6
1-2A
6-12
7-8
9-12D
9-12
17
9-12B
19
20-30
15-18

Now I want to order the data as below

1
1-2
1-2A
3-5
6
6-12
7-8
9-12
9-12B
9-12D
15-18
17
19
20-30

Issue No. 2

the bellow value How to sort alphanumeric?

PT15-8-2

PT15-12-10

PT15-12-3

PT15-12-572-575

PT15-12-31

PT11-96

PT11-133-36

PT11-133

PT16-136

PT8-644

PT09-655

PT9-655

PT12-752

PT14-752

PT13-752

PT14-753

PT10-778

PT13-977-978

PT13-979-988

Can anyone please tell me how should I write the query?


texts: Simple indexed select query takes 350 seconds (?!) even though Filtered 100.00 "Using index condition"

texts:
My logs show that the query being explained here took 352.19 seconds (and similar times for other similar queries where the contact_id and execute_at were different).

EXPLAIN SELECT
    *
FROM
    `automations`
WHERE
    (`contact_id` = 22638
        AND `job_class_name` = 'App\Jobs\MyJob'
        AND `execute_at` = '2018-12-15 16:43:00')
        AND `automations`.`deleted_at` IS NULL
LIMIT 1

Result of EXPLAIN:

| id | select_type | table       | partitions | type | possible_keys                  | key                            | key_len | ref                     | rows | filtered | Extra                 |
|----|-------------|-------------|------------|------|--------------------------------|--------------------------------|---------|-------------------------|------|----------|-----------------------|
| 1  | SIMPLE      | automations | NULL       | ref  | cId_job_executeAt_delAt_unique | cId_job_executeAt_delAt_unique | 780     | const,const,const,const | 1    | 100.00   | Using index condition |

I am inexperienced at optimizing MySql, but my guess would be that the Explain looks great, right?

Why on earth would queries like this be taking 350+ seconds? How can I diagnose and fix?

P.S. This is related to the E_WARNING: Error while sending STMT_PREPARE packet. PID=* error I frequently see.

CREATE TABLE `automations` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `contact_id` int(10) unsigned NOT NULL,
  `job_class_name` varchar(191) COLLATE utf8mb4_unicode_ci NOT NULL,
  `execute_at` datetime NOT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  `deleted_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `cId_job_executeAt_delAt_unique` (`contact_id`,`job_class_name`,`execute_at`,`deleted_at`),
  CONSTRAINT `automations_contact_id_foreign` FOREIGN KEY (`contact_id`) REFERENCES `contacts` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=1519 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci


texts: Finding interval overlap between two tables

texts:
I have two tables that have top_depth, bottom_depth, lith_type.  One defines the different layers found in a borehole and the other define where samples are extracted in each of the layers.

I would like to find all samples that intersects the layer boundaries. Although, I was able to solve my problem, I was hoping there was a more elegant solution.  This is what I did:

CREATE TABLE lithology ( official_name TEXT, top_depth FLOAT, bottom_depth FLOAT, lith_type TEXT );
INSERT INTO lithology VALUES('B1', 0, 3,'SC');
INSERT INTO lithology VALUES('B1', 3, 11,'SS');
INSERT INTO lithology VALUES('B1', 11, 20,'STS');
INSERT INTO lithology VALUES('B1', 1, 21, 'SS');

CREATE TABLE sample_data ( official_name TEXT, sample_id TEXT, top_depth FLOAT, bottom_depth FLOAT, lith_type TEXT );
INSERT INTO sample_data VALUES('B1', 1, 1, 2,'SC');
INSERT INTO sample_data VALUES('B1', 2, 3, 4,'SS');
INSERT INTO sample_data VALUES('B1', 3, 6, 7,'SS');
INSERT INTO sample_data VALUES('B1', 4, 10.5, 11.5,'SS');
INSERT INTO sample_data VALUES('B1', 5, 14, 15,'SS');
INSERT INTO sample_data VALUES('B1', 6, 19, 20,'SS');

select
    L.official_name, S.sample_id,
    L.top_depth, L.bottom_depth, L.lith_type,
    S.top_depth, S.bottom_depth, S.lith_type
from
    lithology L, sample_data S
where
    L.official_name = S.official_name
and
    (S.bottom_depth > L.top_depth and L.top_depth > S.top_depth or
    S.bottom_depth > L.bottom_depth and L.bottom_depth > S.top_depth or
    S.top_depth < L.top_depth and S.bottom_depth > L.bottom_depth )
order by L.official_name,S.sample_id

This should show that sample_id=4 is the sample to check.


texts: Postgres computed fields (views or triggers)

texts:
I want to have a couple of computed fields on a table, which are aggregations from child tables. For example:

Categories
| id | name |

Items
| id | categoryid | name |

I'd like to have an itemcount column on categories, which is just a sum of the count of items belonging to that category.

In order to achieve this, I'm thinking either a view which appends this column. Then possibly add some rules to redirect inserts/updates/deletes back to the base table.

Alternatively I could create a trigger which rules whenever something is added to the Items table to update the Categories table.

Is there a conventional best practice when requiring such data?


texts: SQL Server error 19456 when adding the listener

texts:
I have three replica same subnet both has a Private and public network. I was able to create the cluster and create the availability group . When I try to add the listener I get the below error

  None of the IP addresses configured for the availability group
  listener can be hosted by the server 'Cluster'. Either configure a
  public cluster network on which one of the specified IP addresses can
  be hosted, or add another listener IP address which can be hosted on a
  public cluster network for this server.

This kind of error comes when there are two subnets and only one was included in the listener . Under the cluster validation there is also a warning that RegisterAllProvidersIP  should be 0 instead of 1. I am clueless as this is in the same data center. I can create the AG and listener when I exclude the 'Cluster' but when I include the server 'Cluster' I get the above error.

Any advice will be greatly appreciated.


texts:
I'm getting a SQL Server error:

Execution fail against sql server. Sql error number: 1236. Error
Message: A transport-level error has occurred when receiving results
from the server.  (provider: TCP Provider, error: 0 - The network
connection was aborted by the local system.)

This occurs when I execute a stored procedure.
The stored procedure has worked well for some months. Some days ago, we moved the database to another server and get this error.
I use this proc in an ADF pine-line, and sometimes I get this error, not always but very often.
I try to remote to the server and execute directly from SSMS but the error still happens.
I have tried to research in many forums, but there is no solution that fixed my case.
How can I resolve this? Any idea? Thanks


texts: table scheme definiation it this way that insert duplicate into another table

texts:
I have a table in which there is a column name with SP varchar(10) NOT NULL. I want that column always to be unique so i created unique index on that column . My table schema as follows :

CREATE TABLE IF NOT EXISTS `tblspmaster` (
  `CSN` bigint(20) NOT NULL AUTO_INCREMENT,
  `SP` varchar(10) NOT NULL,
  `FileImportedDate` date NOT NULL,
  `AMZFileName` varchar(50) NOT NULL,
  `CasperBatch` varchar(50) NOT NULL,
  `BatchProcessedDate` date NOT NULL,
  `ExpiryDate` date NOT NULL,
  `Region` varchar(50) NOT NULL,
  `FCCity` varchar(50) NOT NULL,
  `VendorID` int(11) NOT NULL,
  `LocationID` int(11) NOT NULL,
  PRIMARY KEY (`CSN`),
  UNIQUE KEY `SP` (`SP`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=10000000000 ;

Now i want that if anybody tries to insert duplicate record then that record should be inserted into a secondary table name tblDuplicate.

I have gone through this question https://stackoverflow.com/questions/812437/mysql-ignore-insert-error-duplicate-entry but i am not sure that instead of

INSERT INTO tbl VALUES (1,200) ON DUPLICATE KEY UPDATE value=200;

can i insert duplicate row into another table ?

what changes needed to be done in main table scheme or index column ?

Note : Data will be inserted by importing excel or csv files.


texts: MySQL Difference between BEGIN and START TRANSACTION

texts:
I'm using RDS Aurora (MySQL 5.6)

By default Auto Commit flag is enabled.
I have a transaction as follows

BEGIN;
INSERT INTO .....;// STATEMENT 1
INSERT INTO ......;//STATEMENT 2
COMMIT;

I'm expecting both the Inserts to be commited only when COMMIT is executed.
But Statement 1 is Commiting as soon as it got executed.

So I wanted to know What does BEGIN do here ??

I was assuming that BEGIN does the task of disabling Auto Commit , Can someone also explain the difference between BEGIN and START TRANSACTION as well.


texts:
I have a database structure similar to this,

CREATE TABLE [dbo].[Dispatch](
    [DispatchId] [int] NOT NULL,
    [ContractId] [int] NOT NULL,
    [DispatchDescription] [nvarchar](50) NOT NULL,
CONSTRAINT [PK_Dispatch] PRIMARY KEY CLUSTERED
(
    [DispatchId] ASC,
    [ContractId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

CREATE TABLE [dbo].[DispatchLink](
    [ContractLink1] [int] NOT NULL,
    [DispatchLink1] [int] NOT NULL,
    [ContractLink2] [int] NOT NULL,
    [DispatchLink2] [int] NOT NULL
) ON [PRIMARY]

GO
INSERT [dbo].[Dispatch] ([DispatchId], [ContractId], [DispatchDescription]) VALUES (1, 1, N'Test')
GO
INSERT [dbo].[Dispatch] ([DispatchId], [ContractId], [DispatchDescription]) VALUES (2, 1, N'Test')
GO
INSERT [dbo].[Dispatch] ([DispatchId], [ContractId], [DispatchDescription]) VALUES (3, 1, N'Test')
GO
INSERT [dbo].[Dispatch] ([DispatchId], [ContractId], [DispatchDescription]) VALUES (4, 1, N'Test')
GO
INSERT [dbo].[DispatchLink] ([ContractLink1], [DispatchLink1], [ContractLink2], [DispatchLink2]) VALUES (1, 1, 1, 2)
GO
INSERT [dbo].[DispatchLink] ([ContractLink1], [DispatchLink1], [ContractLink2], [DispatchLink2]) VALUES (1, 1, 1, 3)
GO
INSERT [dbo].[DispatchLink] ([ContractLink1], [DispatchLink1], [ContractLink2], [DispatchLink2]) VALUES (1, 3, 1, 2)
GO

The point of the DispatchLink table is to link two Dispatch records together. By the way I am using a composite primary key on my dispatch table because of legacy, so I cannot change that without a lot of pain. Also the link table may not be the correct way to do it? But again legacy.

So my question, if I run this query

select * from Dispatch d
inner join DispatchLink dl on d.DispatchId = dl.DispatchLink1 and d.ContractId = dl.ContractLink1
or d.DispatchId = dl.DispatchLink2 and d.ContractId = dl.ContractLink2

I can never get it to do an index seek on the DispatchLink table. It always does a full index scan. That is fine with a few records, but when you have 50000 in that table it scans 50000 records in the index according to the query plan. It is because there are 'ands' and 'ors' in the join clause, but I can't get my head around why SQL can't do a couple of index seeks instead, one for the left side of the 'or', and one for the right side of the 'or'.

I would like an explanation for this, not a suggestion to make the query faster unless that can be done without adjusting the query. The reason is that I am using the above query as a merge replication join filter, so I cannot just add in another type of query unfortunately.

UPDATE: For instance these are the types of indexes I have been adding,

CREATE NONCLUSTERED INDEX IDX1 ON DispatchLink (ContractLink1, DispatchLink1)
CREATE NONCLUSTERED INDEX IDX2 ON DispatchLink (ContractLink2, DispatchLink2)
CREATE NONCLUSTERED INDEX IDX3 ON DispatchLink (ContractLink1, DispatchLink1, ContractLink2, DispatchLink2)

So it uses the indexes, but does an index scan across the whole index, so 50000 records it scans 50000 records in the index.


texts: Does a killed query continue rolling back after closing and exiting out of SSMS?

texts:
I have a query that took about 6 hours to run, and I decided to cancel it.

Now, the query appears to still be running somewhere in the background because when I try to expand the database and access tables, its stuck on 'expanding.'

Can a query still be closing (i.e. rolling back) long after you exit out of SSMS? Its been almost 24 hours since I closed the query.

I also have temp tables still in the tempdb that I used in the original query, which are still there, even after I exit out of SSMS. Could this indicate the rollback is still in progress?


texts: sp_refresh_parameter_encryption fails to refresh objects

texts:
I am testing always encrypted feature and once the operation of encrypting column is done I have a file with hundreds objects failed to be refresh by the sp_refresh_parameter_encryption procedure.

Should I investigate these errors and perform the refreshing by myself? What are the risks of not doing this?


texts:
I am pretty sure I am making a basic mistake, but I can't figure it out for the life of me.  I am trying to get a simple report of total number of unique employees, number of employees who are male, number of employees who are female, and finally the total gross pay for the entire month

My Query:

SELECT COUNT(DISTINCT prg.PrgEEID) AS 'Emp Total'
  , SUM(CASE WHEN eep.EepGender='M' THEN 1 ELSE 0 END)
  , SUM(prg.prgsuigross) as '2017 Gross'
  , MONTH(prg.prgpaydate) AS '2017 Month'
  FROM PayReg prg
JOIN EmpPers eep
  ON prg.prgeeid=eep.eepEEID
WHERE prg.PrgStateSui like 'HI%'
  AND YEAR(prg.prgpaydate)='2017'
  AND prg.PrgCheckAddMode in ('A','R','M')
GROUP BY MONTH(prg.prgpaydate)
ORDER BY MONTH(prg.prgpaydate)

Results:

33  58  92131.95    1
32  77  127300.16   2
32  88  159734.80   3
32  68  121667.13   4
32  61  96918.07    5
32  71  125784.19   6
33  82  92163.50    7
33  88  114839.29   8
32  92  134167.71   9
30  64  109269.71   10
30  61  93108.24    11
32  77  96319.64    12

I know the male total is 24 for month one, but I am getting 58 .. and that is because i am getting each transaction for the month and not the unique number of males.  I tried using an OVER PARTITION BY Clause, and that only made it worse.


texts: Does using new ReferenceType in a hibernate query affect performance?

texts:
I have follow code in my java DAO class.

Query query = session.createQuery("select new Question(q.qid, q.qtype) from Question q where q.qlang=:lang ");

Does using new object type in a hibernate query affect performance? In What way is it affected?
Over all code execution (be it the above java code or the sql query it gets converted into and then the assembly of the sql result in a Question object), does it increase or decrease the overall performance of the application?


texts: Difference between [mysql] and [mysqld] in MySQL configuration file

texts:
There are multiple sections in MySQL configuration file:

[mysql]
[mysqld]
[myisamchk]
[mysqld_safe]
[mysqlhotcopy]
[mysqldump]
[client]

I found that same variables with different values have been defined under these sections.

What are the specific purpose behind defining these sections?
Is it necessary to define all?
How MySQL interprets these variables?


texts: How can I get rows after a specific row, from a resultset of a specific sort order?

texts:
I have a table that has an ID column of type varchar(255) as the primary key. The rest of the columns comprise of text, datetime and varchar(255) types. Let's consider the given query:
SELECT ID from `table`
where (certain condition)
order by
    columnX,
    columnY,
    ID
limit 10
;

The ORDER BY clause has the specified columns sorted in an ascending order for this example, but it could contain mixed directions (both ASC & DESC). Anyway, executing the above query gives me a resultset like this:
27087675-38b5-41ea-afa5-9cf24c388545
770a933d-064f-41f5-8d2a-55acaedf38bf
a2e509e3-3a61-44e6-a36e-955d7b1f7b5e
19c47c26-075f-4761-a462-02025f4b29ce
ab3418ee-0b26-40ec-87b8-2b3636bb4e59
24caf7cb-fca5-45f0-81a0-f97175d8e071
285295fb-4ba7-4a70-9aaa-0a93cac058e8
f976e378-394f-42ed-af07-012336660546
5c0201c4-4aec-4e48-95c8-91e73b7843ee
de8f6adf-f880-4758-aeda-9e66a308bbc0

The resultset consists of 10 elements (for the sake of brevity). Now, what I'm trying to achieve, is to get the elements after a certain ID, in the same order. For example, if I want to get the elements after ab3418ee-0b26-40ec-87b8-2b3636bb4e59, the output should be
24caf7cb-fca5-45f0-81a0-f97175d8e071
285295fb-4ba7-4a70-9aaa-0a93cac058e8
f976e378-394f-42ed-af07-012336660546
5c0201c4-4aec-4e48-95c8-91e73b7843ee
de8f6adf-f880-4758-aeda-9e66a308bbc0

What I tried:

I tried the condition AND ID > 'ab3418ee-0b26-40ec-87b8-2b3636bb4e59' in the WHERE clause, without any luck.
It kinda works when I use the first column in the ORDER BY clause with the comparison operator, for example AND columnX > 'someValue', but if the ORDER BY clause contains mixture of ASC & DESC, it doesn't work.

Is there any way to solve this? I basically want to split the resultset, which will always have some kind of ORDER. I just want to load the elements after a certain ID, regardless of the sort order. Could really use some help with this.


texts: MYSQL: Merge data from one column to another

texts:
I have a table named my_jobs that I am trying to copy a column named "location" into two other columns called "city" and "state." The city and state data need to be extracted in their own respective columns. The city and state columns are already created. The "location" column has multiple different city and state information separated by a comma (Mountain View, CA). I've tried using:

Replace INTO my_jobs (city)
SELECT substring_index(location, ", ",1)
FROM my_jobs;

But that adds new rows and doesn't merge the data into the existing fields.


texts:
This below query works as expected:

SELECT
    reports.*
FROM
    reports
    INNER JOIN
        units
        ON units.id = reports.unit_id
WHERE
    reports.unit_id IN (1111, 1112, 1113)
    AND
    (
        reports.id =
        (
            SELECT reports.id
            FROM reports
            WHERE reports.unit_id = units.id
            ORDER BY time desc
            LIMIT 1
        )
    )

It returns the last 3 reports associated with the given units by time. How come that SELECT subquery returns 3 ids rather than just 1 even though we specifiy LIMIT 1? This is what I wanted. I'm just curious how it works.


texts: How to calculate mysql server reads per second,writes per second?

texts: Can anyone let me know how to calculate mysql server writes,reads,deletes,updates per second.


texts:
I am not able to understand concept and usage of schema in PostgreSQL. I have no idea how it can affect my database design.
Why should I use it?

Can it affect me in future if I decide to not think about them right now and worry about them later?

Explanation with an example will be nice.


texts: MySQL row-based replication - Delete Fails

texts:
We have a Master - Master replication setup, MySQL 5.6.
Only one Master is used, the other is for backup and failover (we'll call that the slave).
The binlog_format is set to ROW, autoincrement settings are made to avoid conflicts.

The problem: Slave is halted due to Duplicate key errors.

We debugged the cause to be that bulk deletes made by cron jobs on the Master did not run (completely?!?) on slave. We are talking about tens of thousands of records that are NOT deleted from the slave. We did not find errors in the MySQL error log.

That leads to unsyncronized replica and errors when inserts are made using PK that should have been deleted on the Slave.

The tables are MyISAM.

Any idea why the bulk delete doesn't propagate properly on the replica?


texts:
We have a customer server containing SQL Server 2008R2 with about 50 databases. The issue is that the CPU usage reaches 100% (or close to it) and it lasts for a couple hours. This typically happens in the morning (usually somewhere around 6am - 9am). Average CPU usage is 30%

We looked at the scheduled reports and couldn't find anything - we did decide to schedule them earlier in the morning and that didn't do anything.

I'm going to remote in tomorrow to investigate the issue. We've rebuilt the indexes and that didn't help. I have a set of DMVs to check for queries with highest worker time and longest running time.

Do the SQL Server track all the queries run so that the DMVs can access the information? What other sql snippets would be helpful to diagnose the problem?

edit: Memory usage is pretty high too - about 80-90%. Actually another server is experiencing the same issue at the same time, I believe they're both hosted on a citrix server.

We've also done an index rebuild.


texts: How to Correctly Estimate the Average Number of I/O Operations my RAID 10 disk can offer?

texts:
The average Input/Output Operations per Second a disk can sustain is a very important factor in the tuning of MySQL / InnoDB databases, as it drives the speed with which Dirty InnoDB Buffer Pool pages are flushed back to the disk.

Is there a method or a formula I can use to estimate the average I/O Operations per Second my RAID 10 disk array can run on?


texts: Does any one know which SQL Server 2014/2016 edition/version, supports CDC (Change Data Capture)?


texts:
I have a table with 4 columns: Client , thickness , material and amount.

I want to make a SQL select-query to select everything, but when the client, thickness and material are the same their value of 'amount' should be added.

How would I go about creating such a query?


texts:
CREATE TABLE `table1` (
`id` INT(10) NOT NULL
)
ENGINE=InnoDB;

CREATE TABLE `table2` (
`id` INT(10) NOT NULL,
`value` INT(10) NOT NULL
)
ENGINE=InnoDB;

INSERT INTO table1
VALUES (1), (2), (3), (4);

INSERT INTO table2
VALUES (1, 100), (2, 200);

I want to get table like:

id value
1  100
2  200
3  null
4  null

My query is

SELECT t1.id, t2.value
FROM table1 t1
LEFT JOIN table2 t2
    ON t2.id = t1.id

and I got:

1 100
2 200

How should I query the database to get what I want?


texts: Can you slow down queries for a specific user in a postgres database

texts:
I am assuming it's possible to slow down the query access of a specific user in a postgres database. How would you do It? Is there a way for the user to see that such a setting has been placed on them, and what might the user do to overcome it, if anything at all.

Basically I'm interested in throttling resources on a per user basis to prevent bad actors from abusing the system.


texts: How to find the same salaries and department names?

texts:
Write a query to display last name ,department number,and salary of any
employee whose department number and salary both match the department number and salary of any employee using the following table :

employee_id int,
first_name varchar(20),
last_name varchar(25) not null,
email varchar(25) not null,
phone_number varchar(20),
hire_date date not null,
job_id varchar(10) not null,
salary float,
commission_pct float,
manager_id int,
department_id int,
constraint emp_pk primary key(employee_id)

I have tried this :

SELECT * FROM employees e1
WHERE e1.employee_id = ( SELECT employee_id
                           FROM employees e2
          WHERE e2.department_id = e1.department_id
          AND e2.salary = e1.salary);

What is wrong?


texts: Database suitable for storing annotations?

texts:
I am a novice in database administration and have a design question.
My use-case is storing data annotations(data labels) for Machine Learning, which can run into hundreds of thousands for each client. I will also need to query based on information within those annotations.
Would I be better off using a NoSQL database like MongoDB, or a relational one like PostgreSQL, where I can save each annotation as a json? In case of a relational database, would it make sense to save each client's annotations in separate tables?
PS - I also need to store other data that is usually stored in relational databases, like users, tasks etc.


texts: is it possible to rollback only a partition/subpartition?

texts:
Is it possible to create a savepoint and to rollback only a partition of a table?
Lets assume I created a monthly partition and subpartitions with a group_id for table x.

Now i run some stored procedures to alter the content in table x for a certain group id in a certain month. Before I run the stored procedure, i want to create a savepoint for the partition/subpartition and if necessary I want to rollback only the altered partion/subpartition and not the whole table.

I am asking for oracle and MSSQL.