MySQL replication: most important config parameters for performance on slave server? I'm setting up a mysql master-slave configuration, where slave is located on a much weaker sever.

Since, if I understand correctly, slave only works on updates/inserts, what are the most critical configuration parameters that I can adjust in slave's my.cnf to ensure best performance and smallest possible latency?

Master: 32Gb RAM, Slave: 4Gb RAM
Mostly innodb tables
Percona-server 5.5 on both master and slave

If your concern is about performance and you don't care about losing a small chunck of data(usually 1 second) in case of server crashes, I would change the follow variables:

innodb_buffer_pool_size - try to use 80% of your total ram (in this case 3.2Gb)
innodb_log_file_size - chose a good value in here to optimize the i/o in your slave
innodb_flush_log_at_trx_commit - set it to 2 or 0
innodb_doublewrite - disable innodb doublewrite
innodb_flush_method - change the flush method to O_DIRECT
sync_binlog - if you have binary log enable on slave, disable sync_binlog
make sure you have sync slave variables disabled sync_master_info sync_relay_log and sync_relay_log_info

A good option is to read this article, it's a general overview to how optimize innodb, it will work for your master and also to your slave.

If you are planning to upgrade to the percona server which implement features from MySQL 5.6, I would recommend that you increase the number of slave sql threads slave-parallel-workers


Stored procedure in SSIS Lookup Transformation Editor Can you use a stored procedure as the source for Lookup Transaction because it seems to fail on the EXEC statement?

I would suggest trying to put the stored procedure logic into a view or table-valued function, which could then be referenced in the lookup.

If that can't be done, then you can use the ugly workaround you already discovered:

SELECT * FROM OPENQUERY(LoopBackServer, N'EXEC db.dbo.procedurename;');


Unable to use undeclared variable DECLARE @LINKEDSERVER VARCHAR(20)= 'LS_CMT';
        DECLARE @ODS_DBNAME VARCHAR(20)= '';
        SET @ODS_DBNAME = DB_NAME();
        DECLARE @OCS_DBNAME VARCHAR(20)= '';
        SET @OCS_DBNAME = 'CMT' + SUBSTRING(@ODS_DBNAME, CHARINDEX('_', @ODS_DBNAME), LEN(@ODS_DBNAME))
SELECT * FROM @ODS_DBNAME.[dbo].CLI_LOCATIONS

You can use EXEC for that purpose
DECLARE @LINKEDSERVER VARCHAR(20)= 'LS_CMT';
DECLARE @ODS_DBNAME VARCHAR(20)= '';
SET @ODS_DBNAME = DB_NAME();
DECLARE @OCS_DBNAME VARCHAR(20)= '';
SET @OCS_DBNAME = 'CMT'
                  + SUBSTRING(@ODS_DBNAME, CHARINDEX('_', @ODS_DBNAME), LEN(@ODS_DBNAME)) ;
 EXEC('SELECT * FROM ' + @ODS_DBNAME + '.[dbo].CLI_LOCATIONS');


Providing SQL access to flat file (proprietary) database We have a "database" that stores historical data in what could effectively be called a highly compressed proprietary "flat file" format.  Typically these are accessed and queried using some C++ code that accesses the files directly.
We would like to provide access to the data to a third party who would like to use SQL queries (e.g. MSSQL).
Are there tools, libraries, or software packages that we can use to present an interface, ideally something that could be connected to using MS SQL Server Management Studio to develop queries that when run, run against our flat file data (short of importing the data into a SQL database).
I have heard rumors of such tools but haven't seen anything yet in practice.

Possible solutions:

Use SSIS package registered as a view.
Downside of this option is that any where conditions are not pushed down to SSIS, so dotNet will have to read all the data. Of course you can create multiple views - like last day only, aggregated values, etc.
Walkthrough: Publish an SSIS Package as a SQL View
Use a CLR Table-Valued Functions
This way you can pass parameters to underlying dotNet code
Use PolyBase.
This would require a lot of added infrastructure and will allow you to split processing the file to multiple servers (or in Azure).

On the other hand I don't think that compression you are using will be much better than columnstore compression, so I would advise to reevaluate storing this in SQL Server itself.


postgresql: mapping json array to string array I have following PostgreSql function:
CREATE OR REPLACE FUNCTION "fn_get_list_invoice_number_by_product_ids"(_product_ids INT[])

RETURNS "pg_catalog"."json" AS $BODY$

BEGIN
    RETURN (SELECT json_agg((record)) FROM
                        (
                                SELECT DISTINCT invoice_number FROM sale.invoice_item
                                WHERE product_id = ANY(_product_ids)
                        ) record
                    );
END;$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;

After using this function I get following result:
[{"invoice_number":"POS20-000043A"},
 {"invoice_number":"POS20-000002E"},
 {"invoice_number":"POS20-000009E"},
 {"invoice_number":"POS20-000003E"},
 {"invoice_number":"POS20-000046A"}]

But if I want result as below, what should I do?
[
      "POS20-000043A",
      "POS20-000002E",
      "POS20-000009E",
      "POS20-000003E",
      "POS20-000046A"
]

Don't aggregate the whole row, just aggregate the column:
SELECT json_agg(invoice_number)
FROM
(
  SELECT DISTINCT invoice_number
  FROM sale.invoice_item
  WHERE product_id = ANY(_product_ids)
) record

Or even simpler:
SELECT json_agg(DISTINCT invoice_number)
FROM sale.invoice_item
WHERE product_id = ANY(_product_ids)


Add ranking to query output I'm new at programming with PHP/mySQL and I have got a little problem here.

This is how my code looks like:

SELECT
 COUNT(*) AS Number , Name
FROM
 table
GROUP BY
 Name
ORDER BY
 Number DESC
LIMIT 100

Out of this table:

Oliver
Michael
Oliver
Steven
Michael
Oliver
Oliver

... it generates:

Oliver  | 4
Michael | 2
Steven  | 1

My question: How can I add ranking numbers so that it looks like this:

1 | Oliver  | 4
2 | Michael | 2
3 | Steven  | 1

Thanks for your help!

SELECT  state, num_cities, @n := @n+1 AS rank
    FROM
      ( SELECT  state, count(*) AS num_cities
            FROM  us
            GROUP BY  state
            ORDER BY  num_cities DESC
      ) AS x
    JOIN
      ( SELECT  @n:=0 ) AS init

+-------+------------+------+
| state | num_cities | rank |
+-------+------------+------+
| CA    |        447 |    1 |
| FL    |        264 |    2 |
| TX    |        238 |    3 |
| IL    |        221 |    4 |
| NY    |        185 |    5 |
| OH    |        185 |    6 |
| MA    |        180 |    7 |


Mac OS X 10.10 mySQL 5.6.26 accessible from CLI but not port 3306 I did a fresh install of mySQL 5.6.26 on my Mac today. I was able to access mySQL from Terminal.app as expected, however I could not get mySQL workbench to attach to 127.0.0.1 port 3306. What really tripped me up was I could not connect to this port even locally (nc -z 127.0.0.1 3306).

long story short, the mySQL system preference pane created a launch Daemon for mySQL on port 3307. /Library/LaunchDaemons/com.oracle.oss.mysql.mysqld.plist

I'm not sure why this is...  every other install I've done was on 3306. Just though I'd drop a note to save others from needlessly digging into why OS X's firewall was "blocking" the port when it really was't. Also, it seems this LD will override the port in my.cnf, even if it is hard coded to 3306.

Hope this helps!

bash-3.2# defaults read /Library/LaunchDaemons/com.oracle.oss.mysql.mysqld.plist
{
    Disabled = 0;
    ExitTimeOut = 600;
    GroupName = "_mysql";
    KeepAlive = 1;
    Label = "com.oracle.oss.mysql.mysqld";
    LaunchOnlyOnce = 0;
    ProcessType = Interactive;
    Program = "/usr/local/mysql/bin/mysqld";
    ProgramArguments =     (
        "/usr/local/mysql/bin/mysqld",
        "--user=_mysql",
        "--basedir=/usr/local/mysql",
        "--datadir=/usr/local/mysql/data",
        "--plugin-dir=/usr/local/mysql/lib/plugin",
        "--log-error=/usr/local/mysql/data/mysqld.local.err",
        "--pid-file=/usr/local/mysql/data/mysqld.local.pid",
        "--port=**3307**"
    );
    RunAtLoad = 1;
    SessionCreate = 1;
    UserName = "_mysql";
    WorkingDirectory = "/usr/local/mysql";
}

I had this same problem too, and changing the port in /Library/LaunchDaemons/com.oracle.oss.mysql.mysqld.plist fixed the issue, with no side effects so far.

Changing the port in my.cnf didn't work. It makes the Preference Pane say that MySQL is not running, and makes it impossible to start from the Preference Pane.


Indexes: Normal tables vs memory optimized If two tables have the same schema but one is "normal" and the other is memory optimized, if I research on how indexes should be for the "normal", can I do a direct extrapolation for the memory optimized table?

Couldn't find anything conclusive so I really appreciate links, topics and so on.

Since the architecture of these two types of tables and indexes is completely different, I would say the answer would be "no". If the table needs to be optimized for point lookups, you should use a HASH index, but that could cause you problems, because if the key is comprised of multiple columns - and you only supply the leading column - you'll get a table scan instead of a seek. In general, it's best to use RANGE indexes, and then only when you can prove you need a HASH index, to possibly deploy that.

Another area is columnstore indexes, which are radically different between the two table types. See my post here: Clustered columnstore: on-disk vs. in-mem.

There is a lot to consider before deploying the In-Memory OLTP feature. I have blogged almost exclusively on this topic for the last two years. I strongly advise you to read my posts....


ms-access concat a string with control source Sorry for this likely noob question but I cannot seem to find the solution. I'm trying to modify an existing field on a report, standard in this field the data is being pulled from the control source Insert under the properties pane and looks fine. I'm trying to change this into a 1D barcode which requires * before and after. I check the syntax for concating strings with inputs and it should be = "*" & [Insert] & '*" but this just throws a #Type! error on the report when it's viewed. What am I doing wrong?

As the users in the comments above pointed me in the right direction, the issue was a circular reference error between the name of the created field (TextBox) and the Control Source. These can be viewed in the Property Sheet of a form or report under All tab. Obviously you don't want to rename the Control Source between this references the table or query the data's being pulled from, so just change the name of the text box. For whatever reason MS Access defaults with the names being the same so this is a common issue. In the image, originally the fields Name and Control Source both contained Insert.


Find all stats that were sampled I am using Ola's scripts for stats updates and for one particular index I want to do full scans when updating stats (to solve for the Ascending Key problem in conjunction with trace flag 2389). We have thousands of identical databases (this is how our vendor software works) and I'd like to make sure that all stats for this index are in fact doing full scans, not sampling. How do I go about running a query to check all DBs?

I can use DBCC SHOW_STATISTICS(), but that returns three result sets -- is it even possible to put just the first result set into a table and ignore the other two? Is there a better option?

You can see how many rows were sampled (among other things) by using the sys.dm_db_stats_properties DMF:

SELECT object_name(s.object_id), s.name,
       sp.last_updated, sp.rows, sp.rows_sampled,
       sp.steps, sp.unfiltered_rows, sp.modification_counter
FROM sys.stats s
CROSS APPLY sys.dm_db_stats_properties (s.object_id, s.stats_id) sp
WHERE s.object_id = object_id('TableName')
AND s.name = 'IndexName';

You can add some math to do the comparison for rows_sampled vs rows. Given that there will be data movement in the table after you update stats, I wouldn't expect this to be 100%, but you can assume that if a full scan was done, you'll see at least 80% of the current table size:

SELECT object_name(s.object_id), s.name,
       sp.last_updated, sp.rows, sp.rows_sampled,
       --
       sample_percent = (100*sp.rows_sampled/sp.rows),
       --
       sp.steps, sp.unfiltered_rows, sp.modification_counter
FROM sys.stats s
CROSS APPLY sys.dm_db_stats_properties (s.object_id, s.stats_id) sp
WHERE s.object_id = object_id('TableName')
AND s.name = 'IndexName';

Now, you just have to use some tool to run it against your thousands of databases. I suspect that if you're managing thousands of databases, you already have an established way to do this. As a PSA, I'll mention that sp_MSforeachdb sometimes misses databases, so consider avoiding that stored procedure.


Need help with ODB Database over VPN I use an ODB SQL Database for electronic components.
The Database comes from Omnify which a PLM system that is used to store all the electronic components that we use. The database is accessed using Altium Designer which is a schematic and PCB layout program.
The database connection does work, but there are several issues with it:
I have to access it over VPN which slows everything down.
I cannot change in data on the Omnify side because I am not an administrator so I have to ask someone to do this. Also I do not believe there is a way to mass populate fields that need to have the same value.
So I think it would be better if I had a local copy of the database so I would not have use VPN and I could also quickly and easily make changes to it. I can access the database in Excel save it as an Excel file and make changes to it, but any new Omnify entries would have to added manually. So Ideally I need something that links to the live database but keeps a local copy that I can edit and adds new entries as they become available but does not change the edits I made to existing entries. I am not sure if such a thing exists. Thanks for any advice.

I believe you're referring to a product that uses a Microsoft SQL Server database (but if that's incorrect please add tags to your question with the appropriate database).
In Microsoft SQL Server there a multitude of ways you can move and sync data between databases and servers. It sounds like a solution you'd want would be dependent on one of the options Microsoft offers.
One option that's been around a while is called Replication. Here's the Microsoft docs on it: SQL Server Replication
There's different kinds of replication depending on your needs, but Transactional Replication is pretty popular because of its ability to be near real time in sync. It's also possible to do two-way aka bi-directional replication (so when you make changes at the target server, it re-syncs them back to the source server).
I'm assuming you're an engineer since you mentioned PLM and PCB (I used to be a software developer for an electronics manufacturer in a past life :). You'll likely want help from your IT / Software Dev team setting this up.
Other technologies for moving data in Microsoft SQL Server are:

AlwaysOn Availability Groups
SSIS
Log Shipping

But my personal recommendation from experience and based on your use case would be Transactional Replication.


Problems with Differential Backup CheckpointLSN I’m trying to get my head around how LSNs and Differential Backups work. I’ve read a few articles on here that explain some things that have helped but there is still a missing gap for me.

I get that a differential backup is slightly different to the logs in that all it holds are the extents that have changed since the last full backup and a tiny bit of log to ensure the data is consistent. No problems, I get that. I’m trying to work out how I identify the subsequent logs that need to be applied after that differential.

Below is a cut-down table of some backup entries. The checkpoint_lsn of the diff and transaction log do not match so that doesn’t seem to be the same, neither do the first and last lsn values. My current thinking is the next log to apply after a diff is one where the last_lsn of the diff is contained with the first_lsn and last_lsn value of the log. Is this correct or have I totally pulled that out of my bottom and got the wrong end of the stick here?
I totally understand lots of people use time stamps on their backup files to do this, yup that can work, but… I just need to understand what is going on and why to help me get a good nights sleep.

The checkpoint_lsn of the diff and transaction log do not match so
  that doesn’t seem to be the same

Checkpoint_lsn is the LSN of most recect checkpoint respect to given backup (the closest in time checkpoint that occured prior to given backup), so checkpoint_lsns of different backups are not related to each other.

  neither do the first and last lsn values

This depends on the type of backup.

While for all the backup types these are the first LSN and the (last LSN - 1)* contained in given backup and seem to be independent of other backups, Last_lsn and First_lsn of log backups are always "in relation":

I.e. every next log backup starts exactly where previous log backup stopped.

*
That LSN is not contained in 2 log backups, instead, given current log backup, its last_lsn is not the one contained in this backups, but it's the next lsn respect to the last lsn, contained in this log backup, that's why I wrote that the Last_LSN is Last_lsn - 1.

In other words, it is LSN that will be the starting point of the next log backup and it will be contained in that next log backup.

  My current thinking is the next log to apply after a diff is one where
  the last_lsn of the diff is contained with the first_lsn and last_lsn
  value of the log. Is this correct

Yes it's correct.


Postgres replication not cleaning up WAL files I'm not that great with postgres but i was able to setup replication following this tutorial:
https://www.howtoforge.com/tutorial/how-to-set-up-master-slave-replication-for-postgresql-96-on-ubuntu-1604/
It all works great but the amount of WAL files is getting ridiculous.
I assume the slave should tell them master to remove old files, but I'm not sure how to do that.
I have the same settings as in the tutorial. I've already tried to "manually" clean them, which lead to a ton of problems. But right now the i'm using 30GB of space for the files and i'm afraid to do any more massive imports into my db because i will run out of space

I think you're looking for the archive_cleanup_command setting in your recovery.conf file:

archive_cleanup_command = 'pg_archivecleanup archivelocation %r'

See the docs or this post for more info.


How to PARTITION a table in mysql? I read a lot of articles that PARTITIONing is not helpful in most cases. I believe it should speed up the queries in my case. I have a table with the structure of

CREATE TABLE pages
(
page_id int(11) unsigned NOT NULL AUTO_INCREMENT,
category_id smallint(5) unsigned,
title varchar(255),
created datetime,
updated datetime,
FOREIGN KEY(category_id) REFERENCES categories(category_id) ON DELETE CASCADE,
UNIQUE INDEX (category_id,title),
INDEX(title),
PRIMARY KEY(page_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE utf8_general_ci ROW_FORMAT=COMPRESSED

The table is close to 1 billion rows and 200-1000 category_id.

Almost all queries have category_id in it.

I consider PARTITIONing the table as

PARTITION BY KEY(category_id)
PARTITIONS 40; // between 20-50

Is it worthy?

I have bad news with regard to this table: MySQL does not support Partitioning of a Table with Foreign Keys

According to MySQL 5.7 Docs on Partitioning Limitations

  InnoDB storage engine.  InnoDB foreign keys and MySQL partitioning are not compatible. Partitioned InnoDB tables cannot have foreign key references, nor can they have columns referenced by foreign keys. InnoDB tables which have or which are referenced by foreign keys cannot be partitioned.


Finding the last command issued by a blocking process I've developed an interface from ERP SAP Business One to warehouse management, and am facing problems concerning database locks. My program includes direct write operations into the ERP database (a user defined field is filled during asynchronous event processing after creating or changing a sales order). It is suspected to be responsible for blocking situations occurring quite often each day.

When a blocking situation occurs, sp_who2 gives me information about blocking and blocked processes, every time pointing to an spid used by the BPM management software as the "root blocking process". The status of this spid is always shown as "sleeping".

Can a "sleeping" process cause a database lock for other processes, e.g. when transactions weren't committed?
How can I find out the last command of the sleeping process?

2) How can I find out the last command of the sleeping process?

You can use Adam Machanic's sp_whoisActive tool to find the lead blocker.

You can also use DMV's (starting sql 2005 and up) to find out what the spid was executing :

-- You can use "most_recent_sql_handle" in sys.dm_exec_connections to see the last statement that was executed.
SELECT  c.session_id, t.text,
            QUOTENAME(OBJECT_SCHEMA_NAME(t.objectid, t.dbid)) + '.'
            + QUOTENAME(OBJECT_NAME(t.objectid, t.dbid)) proc_name,
            c.connect_time,
            s.last_request_start_time,
            s.last_request_end_time,
            s.status
    FROM    sys.dm_exec_connections c
    JOIN    sys.dm_exec_sessions s
            ON c.session_id = s.session_id
    CROSS APPLY sys.dm_exec_sql_text(c.most_recent_sql_handle) t
    WHERE   c.session_id = -- spid for the spid involved in blocking

-- for any OPEN Transactions, you can use `sys.dm_tran_session_transactions` and `sys.dm_tran_active_transactions`

SELECT  st.transaction_id,
        at.name,
        at.transaction_begin_time,
        at.transaction_state,
        at.transaction_status
FROM    sys.dm_tran_session_transactions st
JOIN    sys.dm_tran_active_transactions at
        ON st.transaction_id = at.transaction_id
 WHERE   st.session_id = -- spid for the spid involved in blocking

Refer to :

Understanding and resolving SQL Server blocking problems
Different Status of a SPID in SQL Server and What do they mean
Locking in Microsoft SQL Server (Part 4 – How to detect blocking)

Alternatively, you can use a mix of Event Notification or Profiler with Blocked process report to detect blocking on your database server.


Help with complicated MySQL query I asked an earlier question about modeling  MySQL data modeling help. The recommendation was the diagram labeled Multiple Tables for Role and Sponsor.

Now I'm trying to write the queries and struggling. It's the point where I kept giving up and looking at maybe easier database models, but now I know that won't work and I need some advice.

When I have an event_id and a user_id, I need to check whether this leader is allowed to edit this event. They are allowed if

Are the user_id and event_id is in the user_event_role table?
If this event also has a project_id, is the user in the user_project_role table for the project that runs this event?
Is the user in the user_organization_role table for the organizations that sponsor this project (check the project_sponsor table)?
Is the user in the user_organization_role table for the organizations that sponsor this event (check the event_sponsor table)?

If this user_id is found in any of these leader positions for the event, project, or organizations sponsoring the event or project, then it's okay for this user/leader to edit.

I'm not sure how to write such a complicated query. The following query insists that the user_id be present in all three user_role tables when it only has to be present in one.

SELECT count(*)
FROM user AS u
JOIN user_event_role AS uer ON (uer.user_id=u.id)
JOIN user_project_role AS upr ON (upr.user_id=uer.id)
JOIN user_organization_role AS uor ON (uor.user_id=upr.user_id)
JOIN event_sponsor AS es ON (es.organization_id=uor.organization_id)
JOIN project_sponsor AS ps ON (ps.project_id=upr.project_id)
WHERE
  uer.term_to >= CURRENT_DATE() AND
  upr.term_to >= CURRENT_DATE() AND
  uor.term_to >= CURRENT_DATE() AND
  u.netid='{$netid}'

Do I need to do something as complicated as this union:

  SELECT count(*)
  FROM user AS u
  JOIN user_event_role AS uer ON (uer.user_id=u.id)
  WHERE
    uer.event_id=$event_id AND
    uer.term_to >= CURRENT_DATE() AND
    uer.role != 'volunteer' AND
    u.netid='{$netid}'
UNION
  SELECT count(*)
  FROM user AS u
  JOIN user_project_role AS upr ON (upr.user_id=u.id)
  JOIN event AS e ON (e.project_id=upr.project_id)
  WHERE
    e.id=$event_id AND
    upr.term_to >= CURRENT_DATE() AND
    u.netid='{$netid}'
UNION
  SELECT count(*)
  FROM user AS u
  JOIN user_organization_role AS uor ON (uor.user_id=u.id)
  JOIN event_sponsor AS es ON (es.organization_id=uor.organization_id)
  WHERE
    e.id=$event_id AND
    uor.term_to >= CURRENT_DATE() AND
    u.netid='{$netid}'
UNION
  SELECT count(*)
  FROM user AS u
  JOIN user_organization_role AS uor ON (uor.user_id=u.id)
  JOIN project_sponsor AS ps ON (ps.organization_id=uor.organization_id)
  WHERE
    uer.event_id=$event_id AND
    uor.term_to >= CURRENT_DATE() AND
    u.netid='{$netid}'

Is this the best approach? Is there a way to simplify it?

Have you considered using a Stored Function ?

You can split the union into your individual queries and if it passes any one of the three, return count(*). Otherwise, return 0

Some restrictions on stored functions

I recommended function over procedure because it's invoked like:

SELECT myfunc(userID, eventID);

and your query doesn't look like it needs a resultset returned, just a single value. If it needs a resultset, you would need a procedure (as noted in the restrictions documentation)


Postgresql: Partition table by hour I have a system that inserts several million records over the course of 24 hours.  Users don't care about the data that's older than 24 hours, and typically query data over a small time frame (5 to 60 minutes).  Applications need to access small timespans of data very quickly (under a second).  Partitioning the table by hour seemed like the best thing to do.

I made a sample / test setup to make sure the partitioning would work correctly.  Here's my main table:

create table test_table(
    test_table_id bigserial primary key,
    "timestamp" timestamp without time zone default now(),
    did_pass boolean not null
);
create index timestamp_idx on test_table("timestamp");

And then created a separate schema to hold the partition tables:

create schema test_table_parition;

And created the hourly partition tables.  There's a total of 24 partition tables.

create table test_table_partition.h0(
    check((extract(hour from "timestamp")) >= 0 and (extract(hour from "timestamp")) < 1)
)inherits (test_table);
create index timestamp_h0_idx on test_table_partition.h0("timestamp");

create table test_table_partition.h1(
    check((extract(hour from "timestamp")) >= 1 and (extract(hour from "timestamp")) < 2)
)inherits (test_table);
create index timestamp_h1_idx on test_table_partition.h1("timestamp");
.
.
.
create table test_table_partition.h23(
    check((extract(hour from "timestamp")) >= 23 and (extract(hour from "timestamp")) < 24)
)inherits (test_table);
create index timestamp_h23_idx on test_table_partition.h23("timestamp");

Created the insert trigger

 create or replace function test_table_insert_function()
 returns trigger as $$
 declare
 currentHour smallint;
 begin
 currentHour := extract(hour from now());
 if ( currentHour = 0 ) then insert into test_table_partition.h0 values(NEW.*);
 elseif ( currentHour = 1 ) then insert into test_table_partition.h1 values(NEW.*);
 .
 .
 .
 elseif ( currentHour = 23 ) then insert into test_table_partition.h23 values(NEW.*);
 end if;
 return null;
 end;
 $$
 language  plpgsql;

 create trigger insert_test_table_trigger
 after insert on test_table for each row
 Execute procedure test_table_insert_function();

Inserted some dummy data.  The data showed up in the main table and in the partition table (as expected)

insert into test_table(did_pass)
values(false),(false), (true), (true);

And then got the execution plan for a simple test query.

explain select *
from test_table
where "timestamp" > '2015-02-20 11:00:30.152';

The query execution plan is going after the main table and all 24 of the partition tables.  From my understanding of partitioning, the execution plan should only be going after the partition tables with an hour value greater than or equal to 11.

How can I partition tables based on hour?

UPDATE

I was playing around with it a bit more today, and I discovered that running a query directly against the hour value of the table will produce the expected query plan.

For example:

explain select *
from test_table
where extract(hour from "timestamp") >= 11
and extract(hour from "timestamp") <13;

will deliver the expected execution plan:

"Append  (cost=0.00..50.54 rows=11 width=17)"
"  ->  Seq Scan on test_table  (cost=0.00..4.46 rows=1 width=17)"
"        Filter: ((date_part('hour'::text, "timestamp") >= 11::double precision) AND (date_part('hour'::text, "timestamp") < 13::double precision))"
"  ->  Seq Scan on h11  (cost=0.00..1.48 rows=1 width=17)"
"        Filter: ((date_part('hour'::text, "timestamp") >= 11::double precision) AND (date_part('hour'::text, "timestamp") < 13::double precision))"
"  ->  Seq Scan on h12  (cost=0.00..44.60 rows=9 width=17)"
"        Filter: ((date_part('hour'::text, "timestamp") >= 11::double precision) AND (date_part('hour'::text, "timestamp") < 13::double precision))"

I think I need to change how the table partitioning is being setup.

For partition pruning to work, it is important that your partition key is simple and part of the where clause of your queries. In your example you can simplify to
check((extract(hour from "timestamp")) = 0  # equality
check((extract(hour from "timestamp")) = 1  # etc.
check((extract(hour from "timestamp")) = 23

At this time it would be a nice use case for timescaleDB which does a lot of this all automatically for you.


How to remove options from mariadb tables? Mariadb 10.2.8
I am trying to convert an aria table to InnoDB. My problem is

MariaDB> alter table materiales engine=innodb;
ERROR 1478 (HY000): Table storage engine 'InnoDB' does not support the create option 'TRANSACTIONAL=1'

I searched for this problem and didn't found a solution. Some people sugests using sql-mode = '', but it only changes the error and the conversion can't be done.
It looks like a bug, because the conversion to innodb should ignore this option but I think it could be walked arround by removing the "transactional=1" option.
Then my question is: I can modify options like ROW_FORMAT or TRANSACTIONAL, but how can I completely remove it? (whitout dumpling and re-creating the table because it is BIG)

Solution:

  alter table materiales row_format=default,transactional=default, engine=InnoDB;


Effects of renaming FC and using its original name for AG network name We have a Failover Cluster, named MainSQL consisting of MainSQL01 and MailSQL02 which are default named instances and we can connect to whichever is the active node with just simply MainSQL.

If we were to rename the FC to MainSQL-NY then the connection would then be MainSQL-NY\MainSQL as far as I can tell, since the instance cannot be renamed. If we then put this cluster into an Availability Group in a DR site with another cluster (let's say  MainSQL-NJ\MainSQL with its own two nodes) would we be able to re-use the MainSQL name when creating the AG Virtual Network Name, thus allowing connections the AG and its underlying active cluster node to still be made simply using MainSQL as it would once again be MainSQL\MainSQL?

The alternative is to rebuild the clusters or just force all our applications to update their connection strings, but I'm curious if this is feasible.

An alternative you might not have considered is DNS. Obviously changing the connection string is easiest, but if that is a problem for some reason, you could leave the connection strings alone and remap using DNS. More importantly, think about these questions:

Why do you need to rename the server/cluster?
Is the rename essential to business processes, or is it just a "tidying up" kind of thing?
Do you really need the geographical location in the name?
Will it really make sense to refer to it that way, since it seems logical
that part of the HA/DR topology might be to fail over to a different
geographical location?

In my experience most people have left that stuff alone, and documented very well that the original primary server was not renamed when they expanded geographically (or wished to rename for other reasons). You could also leave it the same and use DNS or the hosts file to let the applications and other servers/locations think the server was renamed, when really it wasn't.

The actual rename is the hard part IMHO, and I would be looking at any alternative I could to avoid doing that. DNS, hosts files on all relevant machines, aliases, and changing connection strings are all ways you could bypass the actual physical rename.


Data Warehouse Modeling: Sales Order Charges I have two databases systems that I'm trying to dimensional model a sales order business. Both operational data stores have a Sales Order header and detail table. However, one of them has an additional table for Additional Charges that are charges that can be adjusted a either at the sales order header level OR at the line level. The relationships between the tables are:

Header to Detail = 1 to Many
Header to Charges = 1 to Many
Detail to Charges = 1 to Many

I have a few competing dimensional model design thoughts:

Create a Sales Order header + line level granularity fact table and then a separate fact table for adjustments. This keeps the granularity clean, but might repeat many dimension key columns between the two tables.
Flatten all three tables into one single fact table (with allocated measures) for one of the sources and then leave the Charges related set of columns blank for the other source. This technically contains different granularities in a single fact table, a big Kimball no-no.
Another option might be to create some sort of bridge table, maybe? Thought this would make the Charges act as a dimension than a fact which might be not very efficient...

Can someone guide me as to which of these options is the most apt design?

Can someone guide me as to which of these options is the most apt design?

Funilly enough, that question does have an answer: No.  No one can.

Dimensional modeling is always about the questions you want to answer, and typically requires eliminating some information in the transformations.  There's lots of modeling decisions you could make, but the only way to choose between them is comparing the ease and performance of answering questions about your business.

Note that you didn't consider simply using Periodic Snapshot fact, where you would calculate the interesting measures at a grain of something like (day, org, product).  This will give you a very simple dimensional model that probably won't answer every last question, but you can combine that with storing the full transaction details for scenarios that need it.


Is there any way to get the position of a value in a table returned by a SELECT statement? I have the following statement:

SELECT id FROM myTable WHERE aCondition == 1 ORDER BY id

which returns a table like this one for example:

id
1
3
4
5

But what I need is to know the position of a given number. For example 4 has position 2 in the database.

I can do that with normal C code of course but I wonder if I can do everything in SQL, something like this for example:

SELECT ROWNUMBER(id, 4) FROM myTable WHERE aCondition == 1 ORDER BY id

which should return:

ROWNUMBER
2

There is a ROW_NUMBER() analytic function but I don't think it's available in SQLite. So, here's a generic approach (which may be quite slow):

SELECT pos - 1 AS ROWNUMBER     --- the "-1" is to get the C-style row numbering
FROM
  ( SELECT b.id
         , COUNT(*) AS pos
    FROM myTable AS a
      JOIN myTable AS b
        ON a.id <= b.id
    WHERE a.aCondition = 1
      AND b.aCondition = 1
      AND a.id <= 4             --- optional for
      AND b.id <= 4             --- performace
    GROUP BY b.id
  ) AS tmp
WHERE id = 4

Note that you should not use the additional two lines (checks), if your final WHERE checks for something else than id.

Rethinking, there's an even better way, because you only want the Row_Number of the last element in that list. There is no need to number them all, we can just count them:

SELECT CASE WHEN maxid = 4     --- so we don't get a RowNumber for an @id
              THEN cnt-1       --- that does not meet the condition
              ELSE NULL        --- or is not in the table at all
       END AS ROWNUMBER
FROM
  ( SELECT MAX(id)  AS maxid
         , COUNT(*) AS cnt
    FROM myTable AS a
    WHERE a.aCondition = 1
      AND a.id <= 4
  ) AS tmp


Comparing two tables for a UUID change and fix it I have two tables which I'm trying to reconcile the differences of in postgresql.

Table A is old and needs updating.

Table B is an updated, schema identical version of Table A which I have the data for in a temporary table in the database of Table A.

Unfortunately some time after the two databases diverged someone changed the UUIDs of records in table B and I need table A to match table B.

The schema for both tables is:

CREATE TABLE A
(
    uuid VARCHAR(36) NOT NULL,
    template_folder_uuid_parent VARCHAR(36),
    heading VARCHAR(255) NOT NULL,
    image VARCHAR(100),
    downloaded BOOL NOT NULL,
    allow_in TEXT NOT NULL,
    template_group VARCHAR(255) NOT NULL,
    country_filter VARCHAR(1024) NOT NULL,
    user_id INT,
    date_created TIMESTAMP,
    date_modified TIMESTAMP,
    is_modified BOOL NOT NULL,
    image_path VARCHAR(255)
);

I need to search through Table A and Table B and match records based on template_folder_uuid_parent and heading, then set the UUID of the Table A record to the UUID from Table B. Once changed in Table A the UUID will cascade correctly.

UPDATE tbl_a a
SET    uuid = b.uuid
FROM   tbl_b b
WHERE  b.template_folder_uuid_parent = a.template_folder_uuid_parent
AND    b.heading = a.heading
AND    b.uuid <> a.uuid

The last line is to avoid empty updates, which are often forgotten and can be very expensive with big tables.

I would first verify that this update does what you expect it to do before you apply it:

SELECT *
FROM   tbl_a a
JOIN   tbl_b b ON
       b.template_folder_uuid_parent = a.template_folder_uuid_parent
AND    b.heading = a.heading
AND    b.uuid <> a.uuid;


SQL Server - Query Over Linked Server to IBM DB2 throws an error We have a partner that uses a IBM DB2 iAccess database, which we connect to using a linked server on our SQL Server.
It's been working great for a long time, but over the weekend they upgraded their system, and now one of our jobs is failing.

The linked server still connects fine, meaning that if I look at the server under Server Objects->Linked Servers->servername it shows up, and I can see all the tables.

When I run the query through an OpenQuery it works fine:

  Select * From OpenQuery(GLDATA, 'Select WPCPKG, WPCBAC, WPCDEN, WPCPRT from S782D480.GLDATA.GPCKCTL') --This works

But when I use the four part naming that is needed to do an insert into that table, it fails:

  select WPCPKG, WPCBAC, WPCDEN, WPCPRT from GLDATA.S782D480.GLDATA.GPCKCTL -- fails, but used to work last week

The error says:

  OLE DB provider "MSDASQL" for linked server "GLDATA" returned message "Unspecified error".
  OLE DB provider "MSDASQL" for linked server "GLDATA" returned message "[IBM][System i Access ODBC Driver][DB2 for i5/OS]SQL0204 - SYSCOLUMNS in QSYS2 type *FILE not found.".
  Msg 7311, Level 16, State 2, Line 7
  Cannot obtain the schema rowset "DBSCHEMA_COLUMNS" for OLE DB provider "MSDASQL" for linked server "GLDATA". The provider supports the interface, but returns a failure code when it is used.

Per this article I've tried dropping and recreating the linked server, and turning "allow inaccess" off and on in the provider, but that didn't work.

The error is vague enough that I'm not finding much information to try, and I suspect that it is a config problem on their end, but unless I give them the solution on a platter they are very slow to fix things.

Well, I did a lot of searching, found some information on the SQL0204 error, which led me to this support page:

https://www-01.ibm.com/support/docview.wss?uid=nas8N1016377

which has instructions on what commands to run on the server if you see that SQL0204 error.

I forwarded that on to the partners IT guy, who tried it (and a few other commands according to him) and it started working.

I will try to see if I can get him to send me what other commands he ran so I can make this answer more complete.

Edit:
In case the linked page ever disappears, here is a screen shot of the information:


Representing ownership of heirarchical tree structure In an application I'm developing, I have (among others) two structures: Users and Folders.

CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    name TEXT NOT NULL
);

CREATE TABLE folders (
    id SERIAL PRIMARY KEY,
    parent_id INTEGER REFERENCES folders (id),
    name TEXT NOT NULL
);

My problem is that I want each folder tree to have exactly one user "owner", and preferably, the owner would be the same for all folders descending from a particular root node (i.e. WHERE parent_id IS NULL). I would like to maintain referential integrity in the database.

I've thought of several possible solutions, but they all leave something to be desired. For example, I could add an owner_id column to the folders table, referencing users (id). The negative is that the owner would have to be repeated on every row, and ensuring that the entire tree has the same owner would be... difficult. (Maybe a foreign key on (parent_id, owner_id)? That seems... a bit gross.)

Is there some way to do this that I'm not thinking of? I am open to using a different tree structure than an adjacency list, but I'm not sure that others have any additional benefit in this case.

Note: This is a simplified version of my actual use case, but the design is the problem, not the specifics. Similarly, I am using PostgreSQL, if that matters, but I doubt that it will...

I've thought of several possible solutions, but they all leave something to be desired. For example, I could add an owner_id column to the folders table, referencing users (id). The negative is that the owner would have to be repeated on every row, and ensuring that the entire tree has the same owner would be... difficult. (Maybe a foreign key on (parent_id, owner_id)? That seems... a bit gross.)

That's not true. PostgreSQL provides for CHECK constraints which make this really easy.

CREATE TABLE users (
    id   serial PRIMARY KEY,
    name text   NOT NULL
);

CREATE TABLE folders (
    id        serial PRIMARY KEY,
    parent_id int    REFERENCES folders,
    owner_id  int,
    name      text   NOT NULL,
    CHECK (
      (parent_id IS NULL AND owner_id IS NOT NULL)
      OR (parent_id IS NOT NULL AND owner_id IS NULL)
    )
);

INSERT INTO users(id,name) VALUES
  (1, 'Evan Carroll');

INSERT INTO folders(id,parent_id,owner_id,name) VALUES
  (1, null, null, '/usr');
ERROR:  new row for relation "folders" violates check constraint "folders_check"
DETAIL:  Failing row contains (1, null, null, /usr).

INSERT INTO folders(id,parent_id,owner_id,name) VALUES
  (1, null, 1, '/usr');
INSERT 0 1

INSERT INTO folders(id,parent_id,owner_id,name) VALUES
  (2, 1, null, '/usr');
INSERT 0 1

As for the space concern, that's hardly something I would worry about. It's 4 bytes on a row. Joining for that and establishing a 1:1 relationship sounds utterly silly.


Inheritance - What's better: three "equal" tables or one with a type column? I'm having an existencial doubt: I have three conceptual entity types on my logic:

- distributors
- business_groups
- clients

All of three are "companies", and will have the same columns. The relationships are the following:

- 1 distributor => N business_group
- 1 business_group => N clients

In reverse:

- 1 business_group => 1 distributor
- 1 client => 1 business_group

My question

Is it better to create three "equal" tables, one for each entity type, or a single one for the three with distributor_id and business_group_id foreign keys (referencing the same table) that sometimes will be NULL?

Considerations

First approach (three tables): Separation of different concepts, tables with less entries, foreign keys without NULLs, three "equal" tables (with the same columns all)
Seconds approach (one table): Only one table but less intuitive, some NULLs on foreign key columns, one column more for type

This is a very, very common question; people just do not know what to search to get good results. You need to see suggestions on implementing table inheritance (sometimes mentioned as polymorphic associations) for a relational database.

There is no general answer for that problem, there is a few stategies like the ones you mention, and it all depends on your needs, and the focus of later queries (writes and reads). Each option has its advantages and disadvantages- for example, you can sacrifice consistency for better read performance. But you should identify this pattern in order to know the tools to solve it.

You can read what other people has written about it at:

Sql Antipatterns Strike Back (Bill Karwin)- Polymorphic Associations: https://www.slideshare.net/billkarwin/sql-antipatterns-strike-back/32-Polymorphic_Associations_Of_course_some/32
Previous answers on stackexchange: https://stackoverflow.com/questions/3579079/how-can-you-represent-inheritance-in-a-database

Check literature by Bill Karwin and Martin Fowler for a more in depth analysis.


Renaming postgres table I wonder if it is possible to rename Postgres table without cascading type change to dependent objects? For example,

create table test_1(id int);
create function test_1_funct (param test_1[]) returns void as $body$
begin
    return;
end
$body$
language plpgsql;
alter table test_1 rename to test_2 ;
 -- function test_1_funct signature silently changed,
 -- so it now accepts test_2[].

I want to emulate behaviour similar to what Oracle would do - mark function invalid, let me create table with the old name, and recompile the function on demand .  As it is, it doesn't let me drop table test_2.

It isn't. Not should it be. Tables resolve to an oid that represents their relation.

SELECT 'test_1'::regclass::oid;
SELECT oid, relname FROM pg_catalog.pg_class WHERE relname = 'test_1';
SELECT oid, relname FROM pg_catalog.pg_class WHERE oid = 'test_1'::regclass::oid;

Changing their "name" is essentially a catalog update on relname. How would what you're talking about look anyway: what would happen if you called test_1_funct after the rename? What type would it expect?


Postgresql 9.2 won't start after rerunning installer All,

I made a fatal mistake and accidentally ran the core installer for postgresql rather than the stackinstaller. I didn't go all the way through the install process but it still set something that is preventing it from starting at all now. This is a production DB and I really need some insight in to what I can do to troubleshoot this problem.

I am running 9.2 on a Windows 2008R2 server.

Sorry, I don't have enough rep to participate in the comments yet, but I use PostgreSQL a lot on Linux and Windows.

I ran into this same problem the other day. I ran the 9.1.7 installer and it broke my clusters. As Mike Christensen said, the key is running pg_ctl START in a command window to get a useful error message.

I got the command to use from the Services panel. When you view properties on your cluster definition, you'll see the pg_ctl command to start the service. Copy that command and paste it into the command window, then change the "runservice" parameter to "start", and use runas.exe to launch the command as the postgres user (9.1 and below) or LOCALSERVICE account (later versions).

It should already contain the path to your cluster data directory. This is what mine looks like:

C:/PROGRA~1/PostgreSQL/9.1/bin/pg_ctl.exe runservice -N "PostgreSQL Dev Cluster" -D "C:/PROGRA~1/PostgreSQL/9.1/data"

Check the error message you get from that command, and check the database logs also.

In my case I had two problems. First, I couldn't get the installer to work until I ran it under postgres local admin account. But when I did that, my domain account no longer had privileges. I had to re-grant myself full control on PostgreSQL directory tree. ( Edit CR: Don't mess with permissions on the PostgreSQL data directory unless you know exactly what you are doing, you are likely to make any problem much worse not better.)

Then I tried starting it again and got a different message indicating port 5432 was already in use. This turned out to be true. Somehow the installer had left an orphaned postgres.exe process open on port 5432, even though the Services panel showed that service as not started.

I had to use netstat -ano to find the PID and kill the orphan from task manager. Then everything started normally.

Hope that helps.

Brian


ORACLE 11g Know Insert Record Details which Failed to insert I have started auditing insert records by user on failure to any table in my oracle 11g Database. I have used following command to do the same.

AUDIT INSERT ANY TABLE BY SHENA BY ACCESS WHENEVER NOT SUCCESSFUL;

I would like to know whenever the record insert will fail, Can i know what was the records which failed to insert into table.

Where we can see such information. Or if you know any other way of auditing of the same please suggest. One way which i know is to write a trigger on insert. In that trigger handle insert failure EXCEPTION and save those values to some table.

Use SQL Loader Utility with following control file format.

options(skip=1,rows=65534,errors=65534,readsize=16777216,bindsize=16777216)
load data
infile 'c:\users\shena\desktop\1.txt'
badfile 'C:\Users\shena\Desktop\test.bad'
discardfile 'C:\Users\shena\Desktop\test.dsc'
log 'C:\Users\shena\Desktop\test.log'
append
into table ma_basic_bd
fields terminated by '|' optionally enclosed by '"' trailing nullcols
(fs_perm_sec_id,
"DATE" "to_date(:DATE,'YYYY-MM-DD')",
adjdate "to_date(:adjdate,'YYYY-MM-DD')",
currency,
p_price,
p_price_open,
p_price_high,
p_price_low,
p_volume)

You are requested to use the conventional path loading so that we can get the rejected(rejected because of datatype mismatch and business rule violation) records in .bad file. Conventional path loading is a default option.

Following URL can be used for the detailed knowledge.

https://youtu.be/eovTBGAc2RI

Total 4 videos are there. Very helpful.


I created an index approx 5gb in size but the file system doesn't show any difference in free space On a table with many millions of rows and two existing indexes (one clustered and one non-clustered primary key index) I created a third non-clustered non-unique index.   Before creating this index, in table properties the index space showed about 4.9gb.  After creation it showed approximately 9.9gb

I checked the disk free space in the OS of each disk before doing this.   I checked again after and there is virtually no significant change (certainly not to the tune of 5gb) on any of the disks.    I have checked again hours later and still no change.

I'm suspecting/assuming that some data file had a lot of free space inside it, and the new index simply occupied that already allocated space.  Is this likely to be what has happened?

Should I be concerned?

I'm suspecting/assuming that some data file had a lot of free space
  inside it, and the new index simply occupied that already allocated
  space. Is this likely to be what has happened?

Based on your description of events yes this must have been the case.

The free space might have arisen either due to the initial size of the database files leaving more than 5GB free, or last time it needed to grow the increment was big enough to leave at least 5GB free, or space was reclaimed from deleted rows or dropped items in the data file or some combination of these.


MySQL connectivity error I have a MySQL database on a Linux server that I am able to access through putty logging into servers terminal. When I try to connect to the MySQL database remotely, I get the error:

  Lost connection to MySQL server at 'reading initial communication packet, system error: 0

I have added bind-address:127.0.0.1 in my.cnf file and port 3306 is already opened. What other configurations do I need to change to be able to connect to MySQL remotely?

I am using Navicat / HeidiSQL + SSH to connect remotely.

Your bind-address needs to be 0.0.0.0 or at the very least the IP address of the interface you will be connecting to from your remote machine.

If you set it to 127.0.0.1 only loopback connections from the server can be made.


Is the SQL Server IsShutdown property useful to determine whether a database is in a good state? My company has a tool that monitors statuses on servers, services, databases, etc. We monitor a number of on-site servers for our customers. One particular simple check performed is to determine whether a SQL Server database is in a 'good' state by querying for the value of certain database properties. The four database properties we monitor are:

IsSuspect
IsOffline
IsEmergencyMode
IsShutdown

This is the query we use:

SELECT name AS [SuspectDB],
  DATABASEPROPERTY(name, N'IsSuspect') AS [Suspect],
  DATABASEPROPERTY(name, N'IsOffline') AS [Offline],
  ISNULL(DATABASEPROPERTY(name, N'IsShutdown'), 1) AS [Shutdown],
  DATABASEPROPERTY(name, N'IsEmergencyMode') AS [Emergency]
FROM sysdatabases
WHERE (DATABASEPROPERTY(name, N'IsSuspect') = 1)
   OR (DATABASEPROPERTY(name, N'IsOffline') = 1)
   OR (ISNULL(DATABASEPROPERTY(name, N'IsShutdown'), 1) = 1)
   OR (DATABASEPROPERTY(name, N'IsEmergencyMode') = 1)

In testing an upgrade to SQL Server 2008, it seems that quite a few of our databases are returning a 1 (true) value for the IsShutdown property. This was never the case previously with SQL Server 2005. The MSDN documentation for the property simply states "Database encountered a problem at startup".

As far as I can tell, the databases are perfectly fine. They are up, can be queried, etc. No issues.

Does the IsShutdown property really matter for my monitoring purposes, i.e., does it indicate that the database is in a bad state? Or should I just remove it from my query?

NOTE: In talking to one of our resident DBAs, they found that on some of our new SQL Server 2008 databases, the fact that the IsAutoClose property is enabled might have something to do with the reason for these databases having an IsShutdown of true. Disabling IsAutoClose seems to "fix" the IsShutdown being true.

A better practice might be to look at the status of the database in sys.databases or sysdatabases.  If the status is not 0 then there is probably a problem (unless you are using mirroring, log shipping, etc. where the database status shouldn't be 0).


Enable File Stream SQL Server 2017 I am trying to enable file stream on Microsoft SQL Server 2017, on a Windows Core 2019 Server.

I have changed the registry settings at the following location to enable it as there is no GUI. I set the value to 3

ServerName\HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL14.InstanceName\MSSQLServer\Filestream.EnabledLevel

After This I restarted

I have also installed the latest Cumulative update (20) as I know there where issues with Driver signing.

After This I restarted

I am seeting the following Errors in the log file at C:\Program Files\Microsoft SQL Server\MSSQL14.InstanceName\MSSQL\Log

<{781FAE78-09AB-4EE5-B051-67747BDB19E3}>RsFxMgmtInitialize failed (the RsFx device is not found. Check if RsFx driver is started.): Error 0x80070002 (-2147024894)
2020-04-21 08:56:03.94 spid5s      FILESTREAM: failed to connect to kernel driver RsFx0503.
<{1038F43D-3391-45F7-B1B3-BADF26459429}>Failed to initialize CFsaShareFilter: Error 0x80070002 (-2147024894)

<{1038F43D-3391-45F7-B1B3-BADF26459429}>Failed to initialize CFsaShareFilter: Error 0x80070002 (-2147024894)

2020-04-21 08:56:03.94 spid5s      FILESTREAM: effective level = 0, configured level = 2.
2020-04-21 08:56:03.94 spid5s      FILESTREAM feature could not be initialized. The operating system Administrator must enable FILESTREAM on the instance using Configuration Manager.

SELECT @@VERSION

Microsoft SQL Server 2017 (RTM-CU20) (KB4541283) - 14.0.3294.2 (X64)   Mar 13 2020 14:53:45   Copyright (C) 2017 Microsoft Corporation  Standard Edition (64-bit) on Windows Server 2019 Standard 10.0 <X64> (Build 17763: ) (Hypervisor)

I got this working on one server and cannot get it working on the second one.
There is also a version of sql 2019 on this box

Any help? Also any advice on getting this working on a cluster

I'm not a big fan of changing stuff directly in the registry.

You can use "computer management" and remotly connect to your core server. From there, you can enable FileStream and configure it.

I hope this will help


Linked server returned message "Query timeout expired." We have two separate SQL Servers. On one server we have a data warehouse (DWH), on the other we have sales information database.

Now on the DWH server there is an ETL job that collects the information from the sales server. The job runs daily after midnight. The DWH collects the information via linked server from the sales database.

Now, the most time the ETL job runs without any problems. But sometimes it fails because of query timeout. We have found out, that there is a specific pattern: The failure happens every 11th day. So on the 11th day the ETL job fails to collect information.

The following error occurs:

  SQLNCLI11 for linked server “my linked server” returned message “Query timeout expired.”

Note: The job fails usually 10 minutes after start.

We have searched everything and could not found out what this issue causes. Also we know that the amount of data is every time, almost the same. Also there is no any scheduled job that runs all 11 days or something.

The remote query timeout on the linked server is set to 0.

Our next step will be, to turn off the antivirus programm on the sales server, to check if this causes the problem.

Does anyone have any clue or idea, where i can search further to find the problem?

This doesn't answer your question with regards to why the query is slow on the 11th day, but hopefully it helps clarify why it fails after 10 minutes.

You mention:

  The remote timeout on the linked server is set to 0.

Intuitively, this might seem like there is no limit.

What it actually does is use the sp_configure default for remote query timeout, which is 600 seconds (10 minutes).

Setting the query timeout on the linked server to a higher value, perhaps 1200 seconds (20 minutes), will likely allow your job to complete.  And hopefully job completion will provide some insight into why it's taking so much longer on this specific day.

I think the setting is a little confusing, as discussed another question here on the site: Linked server connections to Multi-subnet failover cluster


Insert randomly takes 10+ seconds to complete I have an insert statement generated by the .NET Entity Framework.  In most cases, this particular insert will execute in 0ms according SQL Server Profiler.

One out of every 30 or so inserts will jump to as high as 12 seconds duration, causing the .NET client on the other end to show as "Not Responding" while it waits.  Server load should not be an issue as our server is very, very lightly loaded.

Here is the table the insert is being performed against:

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

CREATE TABLE [dbo].[ProductEvents](
[EventID] [int] IDENTITY(1,1) NOT NULL,
[KID] [int] NOT NULL,
[EventDescription] [varchar](50) NOT NULL,
[EventDate] [datetime] NOT NULL,
[UserName] [varchar](50) NOT NULL,
[Notes] [varchar](max) NOT NULL,
[Version] [timestamp] NOT NULL,
[IsSynchronized] [bit] NOT NULL,
[LastSyncDate] [datetime] NULL,
 CONSTRAINT [PK_ProductEvents] PRIMARY KEY CLUSTERED
([EventID] ASC ) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF,
IGNORE_DUP_KEY = OFF,ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO

ALTER TABLE [dbo].[ProductEvents] ADD  CONSTRAINT [DF_ProductEvents_IsSychronized]
DEFAULT ((0)) FOR [IsSynchronized]
GO

ALTER TABLE [dbo].[ProductEvents]  WITH CHECK ADD  CONSTRAINT
[FK_ProductEvents_Products] FOREIGN KEY([KID])
REFERENCES [dbo].[Products] ([KID])
ON DELETE CASCADE
GO

ALTER TABLE [dbo].[ProductEvents] CHECK CONSTRAINT [FK_ProductEvents_Products]
GO

And the query as seen by SQL Server Profiler (actual example):

exec sp_executesql N'insert [dbo].[ProductEvents]([KID], [EventDescription],
[EventDate], [UserName], [Notes], [IsSynchronized], [LastSyncDate])
values (@0, @1, @2, @3, @4, @5, null)
select [EventID], [Version]
from [dbo].[ProductEvents]
where @@ROWCOUNT > 0 and [EventID] = scope_identity()',N'@0 int,@1 varchar(50),@2
datetime2(7),@3 varchar(50),@4 varchar(max) ,@5
bit',@0=1894,@1='Modified',@2='2013-08-12
08:09:25.4766233',@3='KNEXTION\aellison',
@4='Description changed from Mini Awareness Ribbon Cookie Cutter - RM 1698 to Mini
Awareness Ribbon Cookie Cutter - R&M 1698.',@5=0

Here is the execution plan as a screenshot from SSMS:

Any ideas on how to begin to track this down?

The actual and estimated plans seem to be the same.

It is a multi-user application, and this table would be one of the more active tables.

Is there any easy way in the Profiler or any other tool to see if blocking is the issue? Are there any other issues I should be looking for besides blocking?

Could be other sessions are causing blocking. See the Q& A http://dba.stackexchange.com/questions/34313/identify-blocking-and-send-alert.

If the actual and estimated plans are the same, there's no point providing them, because they can't explain anything (aside from general inefficiencies that should apply to all executions, not just the slow instances). You'll could monitor sys.dm_exec_requests when it is running slowly to see who/what it's waiting on.

You can also monitor wait statistics (it could be severely bottlenecked I/O, for example). Reproducing in Plan Explorer might help here, as you can automatically get the wait statistics for your session (unless you fire up your own extended events session, you can only get system-wide waits on your own).

To capture what is going on during the potential blocking periods, you could also use Service Broker and Event Notifications.  Tony Rogerson back in 2007 posted a sample that reports on 10 second (or longer) blocks that you could use.

Also check auto-growth events and settings for the database and log. Occasional long delays can be caused by having to wait for a data or log file to grow.


MySQL wrong use of index when a date interval is big I have a query that have this statement:

SELECT DISTINCT C.id_produto
  FROM movdiario C
 INNER JOIN PRODUTO P ON C.ID_PRODUTO = P.CODITEM
 INNER JOIN estoques B ON B.id_produto=C.id_produto
                      AND B.id_empresa= 1
  LEFT JOIN ESTATISTICAS_PRODUTO E ON E.ID_PRODUTO = B.ID_PRODUTO
                                  AND E.ID_EMPRESA = B.ID_EMPRESA
 WHERE C.empresa = 1
   AND C.emissao BETWEEN ('2019-10-28') AND ('2019-12-12')
   AND C.tipo = 'V'
   AND (C.situacao IS NULL OR c.situacao = '')
   AND P.ATIVO = 'S'

When I use an interval between 2019-10-28 and 2019-12-12 the query run fast using the correct index.

But when I use the interval 2019-10-27 and 2019-12-12 the query runs slowly and I have the explanation as follow:

What's wrong?
I have indexes on fields c.empresa, c.emissao and have a composite index c.empresa,c.emissao.

The reason:  If the range is large, it is faster to simply ignore the index and scan through the table.  Note that using an index implies flipping back and forth between the index and the data.

 WHERE C.empresa = 1
   AND C.emissao BETWEEN ('2019-10-28') AND ('2019-12-12')
   AND C.tipo = 'V'

The optimal index is

INDEX(empresa, tipo,  -- in either order
      emissao)        -- then the 'range' column

It would help if you decided on either NULL or '' in the following.  That way, that column could be included in the above index to make it even better.

   AND (C.situacao IS NULL OR c.situacao = '')


SQL Server Standard Edition Failover Cluster Installing Windows Updates i have a question concerning the installation of sql server updates on a sql server 2012 standard edition failover cluster.

For example a service pack installation. Is it possible to install the service pack at first on one node? And after the sucessfull installtion do the same thing on the other node? So that i have no downtime for the applications during the restart?

Greetings

Yes, that is how we need to roll out patches in the sql cluster with minimum downtime. The downtime will be during the failover part.

Process:

Patch the passive node first.
Move the resources to the passive node.(This will become active now)
Patch the current passive node.(It was originally active).

Before doing this do test it in a Test environment and prepare a full proof plan.

Also refer to this link :

https://blogs.msdn.microsoft.com/john_daskalakis/2014/09/05/how-to-install-a-service-pack-at-a-sql-server-2012-failover-instance-best-practices/


Oracle Procedure alter pivoted data to unpivot and push into different destination I have a PL/SQL procedure which takes string at runtime which is comma separated,splits it and pushes into table.
Example of procedure and table is given here:
Procedure is push_data.
Current table where data is getting inserted is push_data_temp.
https://dbfiddle.uk/?rdbms=oracle_18&fiddle=fae31c11a4ad6205ebcaa418a0bffffd
Now as per requirement, i need to write a new procedure where logic would remain same of splitting data, but in the new table push_data_pivot(structure inside pivot), should display data pivoted in this format.
Ex: if currently data in push_data_temp is:

The data should go in push_data_pivot in the new procedure as:

The logic should remain same, just that the data getting inserted would go into columns instead of rows, populating id and value columns.
Here column id, is the unique identifier for the string passed in both tables. Basically, it is same as the one used in original table.
PUSH_DATA_PIVOT :
 create table PUSH_DATA_PIVOT
(
id_pk NUMBER,
  id NUMBER,
  label_id number,
  label varchar2(4000),
  value varchar2(4000)
);

insert into push_data_pivot values(1,null,'COL01',null);
insert into push_data_pivot values(1,null,'COL02',null);
insert into push_data_pivot values(1,null,'COL03',null);
insert into push_data_pivot values(1,null,'COL04',null);
insert into push_data_pivot values(1,null,'COL05',null);
insert into push_data_pivot values(1,null,'COL06',null);
insert into push_data_pivot values(1,null,'COL07',null);
insert into push_data_pivot values(1,null,'COL08',null);
insert into push_data_pivot values(1,null,'COL09',null);
insert into push_data_pivot values(1,null,'COL10',null);
insert into push_data_pivot values(1,null,'COL11',null);
insert into push_data_pivot values(1,null,'COL12',null);
insert into push_data_pivot values(1,null,'COL13',null);
insert into push_data_pivot values(1,null,'COL14',null);

The sample data is single string, for single label_id and corresponding set of column label values.However actual table would have multiple such strings inserted on basis of id column, same as original requirement.
The only requirement is, to create new procedure with new destination table as push_data_pivot and data in unpivoted format. Split logic would be same as in existing procedure.

Why do you not simply unpivot the hole table

SELECT * FROM push_data_temp;

ID_PK |  ID | COL1                         | COL2                                                                                | COL3     | COL4       | COL5                | COL6                                 | COL7           | COL8 | COL9 | COL10 | COL11 | COL12    | COL13 | COL14
----: | --: | :--------------------------- | :------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | :------- | :--------- | :------------------ | :----------------------------------- | :------------- | :--- | :--- | :---- | :---- | :------- | :---- | :----
 null | 110 | Project title afor BYU heads | "The values are,
 "exactly" up to the requirement and analysis done by the team.
Also it is difficult to,
 prepare a scenario notwithstanding the fact it is difficult. This user story is going to be slightly complex however it is up to the team" | "Active" | "Disabled" | "25 tonnes of fuel" | "www.examplesites.com/html.asp&net;" | "Apprehension" | null | null | null  | "25"  | "Stable" | null  | null
 null | 111 | Project title for IT Heads   | "The values are,
 "exactly" up to the requirement and analysis done by the team.
Also it is difficult to,
 prepare a scenario notwithstanding the fact it is difficult. This user story is going to be slightly complex however it is up to the team" | "Active" | "Disabled" | "25 tonnes of fuel" | "www.examplesites.com/html.asp&net;" | "Apprehension" | null | null | null  | "25"  | "Stable" | null  | null

create table PUSH_DATA_PIVOT
(
id_pk NUMBER,
  id NUMBER,
  label_id number,
  label varchar2(4000),
  value varchar2(4000)
);

INSERT INTO PUSH_DATA_PIVOT
SELECT 1,ID,1,label,value
FROM
(SELECT * FROM push_data_temp
UNPIVOT INCLUDE NULLS(
    value
    FOR label
    IN (
        col1 AS 'COL01',
        col2 AS 'COL02',
        col3 AS 'COL03',
        col4 AS 'col4',
        col5 AS 'col5',
        col6 AS 'COL06',
        col7 AS 'COL07',
        col8 AS 'COL08',
        col9 AS 'COL09',
        col10 AS 'COL10',
        col11 AS 'COL11',
        col12 AS 'COL12',
        col13 AS 'COL13',
        col14 AS 'COL14'
    )
)) t1;

28 rows affected

select * from push_data_pivot;

ID_PK |  ID | LABEL_ID | LABEL | VALUE
----: | --: | -------: | :---- | :-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
    1 | 110 |        1 | COL01 | Project title afor BYU heads
    1 | 110 |        1 | COL02 | "The values are,
 "exactly" up to the requirement and analysis done by the team.
Also it is difficult to,
 prepare a scenario notwithstanding the fact it is difficult. This user story is going to be slightly complex however it is up to the team"
    1 | 110 |        1 | COL03 | "Active"
    1 | 110 |        1 | col4  | "Disabled"
    1 | 110 |        1 | col5  | "25 tonnes of fuel"
    1 | 110 |        1 | COL06 | "www.examplesites.com/html.asp&net;"
    1 | 110 |        1 | COL07 | "Apprehension"
    1 | 110 |        1 | COL08 | null
    1 | 110 |        1 | COL09 | null
    1 | 110 |        1 | COL10 | null
    1 | 110 |        1 | COL11 | "25"
    1 | 110 |        1 | COL12 | "Stable"
    1 | 110 |        1 | COL13 | null
    1 | 110 |        1 | COL14 | null
    1 | 111 |        1 | COL01 | Project title for IT Heads
    1 | 111 |        1 | COL02 | "The values are,
 "exactly" up to the requirement and analysis done by the team.
Also it is difficult to,
 prepare a scenario notwithstanding the fact it is difficult. This user story is going to be slightly complex however it is up to the team"
    1 | 111 |        1 | COL03 | "Active"
    1 | 111 |        1 | col4  | "Disabled"
    1 | 111 |        1 | col5  | "25 tonnes of fuel"
    1 | 111 |        1 | COL06 | "www.examplesites.com/html.asp&net;"
    1 | 111 |        1 | COL07 | "Apprehension"
    1 | 111 |        1 | COL08 | null
    1 | 111 |        1 | COL09 | null
    1 | 111 |        1 | COL10 | null
    1 | 111 |        1 | COL11 | "25"
    1 | 111 |        1 | COL12 | "Stable"
    1 | 111 |        1 | COL13 | null
    1 | 111 |        1 | COL14 | null

db<>fiddle here


What is a “transition table" in Postgres? The page describing what's new in Postgres 10 mentions “Transition Tables for Triggers”.

Transition Tables for Triggers
This feature makes AFTER STATEMENT triggers both useful and performant by exposing, as appropriate, the old and new rows to queries. Before this feature, AFTER STATEMENT triggers had no direct access to these, and the workarounds were byzantine and had poor performance. Much trigger logic can now be written as AFTER STATEMENT, avoiding the need to do the expensive context switches at each row that FOR EACH ROW triggers require.

What is a transition table?

You know how there are OLD and NEW record-variables for FOR EACH ROW triggers?

Transition tables are the FOR EACH STATEMENT equivalents. They're tables with the old and new tuples, so your triggers can see what changed.


How does SQL Server Startup? I'm running SQL Server 2008 R2 and have it set to start the service in the background on startup of the machine. When this happens, what is the process that it goes through to startup?

I believe that the master database loads first, but what order do the other databases load in? How is this determined?

EX: I have databases a1,b1,c1. Does a1 load before c1?

How would I go about setting the priority of a database (e.g. load c1 before b1...)?

I'm looking for a behind-the-scenes look into what happens when the program starts, I am not running any queries on it. Thanks.

Ah, after you updated your comment I realized that you were asking about how to start another process after all databases have been restored/loaded. You can configure a stored procedure to be called using the sp_procoption. I am pretty sure that runs after all databases are ready. Next, you just need to invoke your external process via sql server.

"...Execution of the stored procedures starts when all databases are recovered and the "Recovery is completed" message is logged at startup...."


Change defaults of SSMS generating script In Microsoft Sql Server Management Studio most of times I need a set of advanced settings when generating script. For example I need to set "Script Drop and create" to true and "script extended properties" to false etc. Is there a way or registery items to change the default settings for ever so I don't set them again any time?

Exactly I mean these settings:

Database > Tasks > Generate Scripts > Advanced Scripting Options

Most of those settings can be found here:

Tools -> Options -> SQL Server Object Explorer -> Scripting

Of the two you specifically asked for, "Script extended properties" is present there. But "Script DROP and CREATE" is not. Perhaps it is called something different from one interface to the other, but I can't see anything that would correspond. It might be because Drop vs. Drop/Create vs. Create isn't so much an option as it is a "what do you want to do with this object?" decision.

(Tested in SSMS 17.9.1.)


Multiple Left Join Fetch Duplicate Data I am having duplicates data with this enormous query.

SELECT        t2.`1st_test` AS agric1, t2.`2nd_test` AS agric2,
              t2.exam AS agricExam3, t2.result_total AS agricTotal,
              t2.result_grade AS agricGrade,  t2.result_remark AS agricRemark
FROM
              result_summary AS t1
              LEFT  JOIN
                     agricultural_science AS t2
                     ON t1.student_id = t2.student_id
WHERE        (    t1.student_id     = 'GCU/16/10414'
              AND t1.result_term    = '1st'
              AND t1.result_session = '2016/2017'
              AND t1.result_level   = 'JSS'
              AND t1.result_class   = '2')

Table Structure

Structure for the Agricultural Science table is like so:

|id|student_id|result_session|result_term|result_arm|
 result_class|result_level|1st_test|2nd_test|exam|result_total|
 result_grade|result_remark|

The table structure for Result_Summary Table look like so:

|id|student_id|result_score|result_average|result_term|
 result_session|result_level|result_class|result_arm

Data that match the criteria exist in some tables only, while others contains two rows each with the same Student_ID but different Session. When the query is executed I get enormous duplicates and even data that does not match the 2016/2017 session.

Please, how can I fix this?

You're joining only on 'student_id', when you need to join using, at least, also result_session, but most probably all of result_term, result_session, result_level,result_class,result_arm.

Try either:

SELECT
    t2.`1st_test` AS agric1, t2.`2nd_test` AS agric2,
    t2.exam AS agricExam3, t2.result_total AS agricTotal,
    t2.result_grade AS agricGrade,  t2.result_remark AS agricRemark
FROM
    result_summary AS t1
    LEFT JOIN agricultural_science AS t2
        ON t1.student_id = t2.student_id
           /* This is what I guess you're missing */
           AND t1.result_session = t2.result_session
WHERE
    t1.student_id = 'GCU/16/10414'
    AND t1.result_term = '1st'
    AND t1.result_session = '2016/2017'
    AND t1.result_level = 'JSS'
    AND t1.result_class = '2'

or

SELECT
    t2.`1st_test` AS agric1, t2.`2nd_test` AS agric2,
    t2.exam AS agricExam3, t2.result_total AS agricTotal,
    t2.result_grade AS agricGrade,  t2.result_remark AS agricRemark
FROM
    result_summary AS t1
    LEFT JOIN agricultural_science AS t2
        ON t1.student_id = t2.student_id
           /* Or most probably you're missing all of this */
           AND t1.result_session = t2.result_session
           AND t1.result_term = t2.result_term
           AND t1.result_level = t2.result_level
           AND t1.result_arm = t2.result_arm
WHERE
    t1.student_id = 'GCU/16/10414'
    AND t1.result_term = '1st'
    AND t1.result_session = '2016/2017'
    AND t1.result_level = 'JSS'
    AND t1.result_class = '2'

As a side comment: this data structure for a database could very easily be improved...

My original trial (on a different DB than mySQL)

WITH agricultural_science(id, student_id, result_session,
    result_term, result_arm, result_class, result_level,
    "1st_test", "2nd_test", exam, result_total, result_grade, result_remark)
AS
(
VALUES
 (1, 'GCU/16/10414', '2016/2017',
    '1st', 'arm', '2', 'JSS',
    93, 92, 'exam', 'result_total', 'result_grade', 'result_remark'),
 (1, 'GCU/16/10414', '2015/2016',
    '1st', 'arm', '2', 'JSS',
     99, 23, 'exam', 'result_total', 'result_grade', 'result_remark')

)
, result_summary (id, student_id, result_score, result_average,
    result_term, result_session, result_level, result_class, result_arm
) AS
(
VALUES
 (1, 'GCU/16/10414', 99.1, 90.1,
    '1st', '2016/2017', 'JSS', '2', 'arm')
)

SELECT
    t2."1st_test" AS agric1, t2."2nd_test" AS agric2,
    t2.exam AS agricExam3, t2.result_total AS agricTotal,
    t2.result_grade AS agricGrade,  t2.result_remark AS agricRemark
FROM
    result_summary AS t1
    LEFT JOIN agricultural_science AS t2
        ON t1.student_id = t2.student_id
           AND t1.result_session = t2.result_session
           AND t1.result_term = t2.result_term
           AND t1.result_level = t2.result_level
           AND t1.result_arm = t2.result_arm
WHERE
    t1.student_id = 'GCU/16/10414'
    AND t1.result_term = '1st'
    AND t1.result_session = '2016/2017'
    AND t1.result_level = 'JSS'
    AND t1.result_class = '2' ;


PL-SQL "LAST_VALUE" function does not give the desired result I have a table with below structure:
create table DOC_AMOUNT_DETAIL
(
  col_id         NUMBER,
  amount         NUMBER,
  operation_date DATE
)

and some example data for this table is :
col_id    |  amount   |  operation_date
---------------------------------------
  1       |  5000     |  11/1/2020
  2       |  1000     |  11/1/2020
  3       |  3000     |  11/1/2020
  1       |  1000     |  11/14/2020
  2       |   500     |  11/14/2020

I'm trying to use last_value function to have below output:
col_id    |  amount
--------------------
  1       |  1000
  2       |  500
  3       |  3000

and the problem is that the query does not give me the desired result :
select t.col_id,
       last_value(t.amount) over(partition by t.col_id order by t.operation_date
        ) amount
from doc_amount_detail t
where t.operation_date <= to_date('11/15/2020', 'mm/dd/yyyy');

Instead, it gives me this :
col_id    |  amount
--------------------
  1       |  5000
  1       |  1000
  2       |  1000
  2       |  500
  3       |  3000

Everything seems fine with my query so can anybody please explain where the exact problem is?
Thanks in advance

Explaination
The ORDER BY clause (in an analytical function), implies a window clause of ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW.
You need ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
Since this will apply to each row, you'll need a DISTINCT clause.
Example
with data(col_id, amount, operation_date ) as (
    select  1,  5000,  to_date( '11/1/2020', 'mm/dd/yyyy' ) from dual union all
    select  2,  1000,  to_date( '11/1/2020', 'mm/dd/yyyy' ) from dual union all
    select  3,  3000,  to_date( '11/1/2020', 'mm/dd/yyyy' ) from dual union all
    select  1,  1000,  to_date( '11/14/2020', 'mm/dd/yyyy' ) from dual union all
    select  2,   500,  to_date( '11/14/2020', 'mm/dd/yyyy' ) from dual
)
select distinct col_id
    , last_value( amount ) over (partition by col_id order by operation_date
                                 rows BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) amt
from data;

Results:


A Huge Database: Picking the right schema and data model We want to store some genomic variant data but there are some problems, more important ones like problem of the data's immense size and variability.

1) Variant data can be huge. For example, a single individuals variant data could feasibly some day require a million rows of data in a table, or require of a gigabyte of raw storage on disk. Multiply this over several thousand individuals, and you could potentially end up with terabytes worth of information that you need to make sense of.

2) Each client and/or system we integrate with, will expose or want to see data slightly differently depending on their needs and use cases. This can potentially lead to hundreds of fields that we might need to store, all of which might need to be in different configurations based on the clients needs. So this variant data model will need to keep this in mind in order to remain easy to use, expandable and most importantly, scalable in the long term.

What do you think is better for such a problem? We were thining of having some coulmns in each table that point to an external database or even a file, where we save the huge BLOB data?

I have not experience such scenario but based on my present knowledge I would suggest you have a table which contains union of all the column which any client can need e.g. say client A wants  column 1 and 2 and client 2 wants column 2 and 3 and you have only these two clients then create a table with column 1,2 and 3 and in case of client 1 keep column 3 as null and in case of client 2 keep column 2 as null. Once you are done with this create view for each client e.g.create View A, it should have column 1,2 and will be used in case of client A similarly create view for each client.

Also I would suggest to keep data in table only and not separatly on disc as it create sync issue.


mysqldump doesn't restore database. why not? Why doesn't my restore operation work?

I created a dumpfile for a database using:

mysqldump -u root -p databasename > /home/databasename_bkup.sql

I then opened the dumfile and confirmed that it contains CREATE TABLE statements for each of the tables in the database.  So I dropped the database and re-created an empty database of the same name before running the following restore command:

mysqldump -u root -p databasename < /home/databasename_bkup.sql

This restore command resulted in the following printing in the terminal:

-- MySQL dump 10.13  Distrib 5.6.23, for Linux (x86_64)
--
-- Host: localhost    Database: databasename
-- ------------------------------------------------------
-- Server version   5.6.23

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
/*!40103 SET TIME_ZONE='+00:00' */;
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;
/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;

/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;

-- Dump completed on 2015-03-05 17:56:29

But then when I log into mysql to check the contents of the database, I found out that the database is empty, as follows:

mysql> use databasename;
Database changed
mysql> show tables;
Empty set (0.00 sec)

So why is the database not being restored?  What specific syntax can I use to ensure that the database does get restored properly?

You have to use the mysql client to reload

mysql -u root -p -Ddatabasename < /home/databasename_bkup.sql

Another way to reload would be

mysql -u root -p -Ddatabasename

then from the MySQL prompt, do this

mysql> source /home/databasename_bkup.sql

If you would like the mysqldump to drop and recreate the database for you, create the dump like this:

mysqldump -u root -p --add-drop-database -B databasename > /home/databasename_bkup.sql

Then, running the script

mysql -u root -p < /home/databasename_bkup.sql

does the DROP DATABASE command for you.


How to change the SQL Server Backup file (.bak) file default icon? As I would like to say that by mistake the SQL Server 2012 backup file (.bak) open with wordpad. Due to this reason all backup files of SQL Server (.bak) file change with wordpad icon in entire the Windows Server 2012 R2 Standard 64-bit O.S.

Now the SQL Server (.bak) file icon looks like as attached below:

To Verify the Backup File through Restore Headeronly TSQL Command

As per MSDN BOL documentation RESTORE Statements - HEADERONLY (Transact-SQL). I have checked the Restore Headeronly from Disk  from verification of my (.bak) file. The

I have checked and verify through the Restore Headeronly TSQL command. The result set returned by RESTORE HEADERONLY includes a row for each of these other backup sets.

I am also attaching that screen shot for persual.

It seems to odd the icon format of SQL Server (.bak) file. The (.bak) file icon format through out the Server has been changed with (wordpad) icon.

  My Question is that: How to revert back the (.bak) file with
  their default icon format? Is it possible or not?

On Windows 10

Right click on the .bak file
Choose properties
On the General tab, click the Change... button next to Opens with:

Under Other Options choose More apps

Scroll to the bottom and click Look for another app on this PC

Go the location of Ssms.exe.  Mine was located here:
C:\Program Files (x86)\Microsoft SQL Server\110\Tools\Binn\ManagementStudio
Choose Ssms.exe
Click the Open button and then the Save button

It should now be associated with SQL Server Management Studio and have the icon you'd like


SQL Server replication with subscriber doesn't know about the publisher I have a case where many SQL Server instances need to be merged and centralized in one master database.

This needs to be near real-time solution. After looking around in Google, I've found that transactional replication can do the trick.

The problem is that I have (n) publisher databases, and they don't even have static IPs. I had the impression that push replication will be enough to send the data to the centralized database without knowing about the publisher connection, but it seems like I was wrong.

My question is: How can I set up an solution to merge/replicate multiple publisher databases which don't have static IPs, and might suffer frequent outage from the network, all that in near real-time?

A VPN connection can be used to connect to each other. But moving a huge data over Internet is done using DHCP connection, And may be slower depending on your internet connection.


SQL Server Indexed View and TOP I'm struggling to persuade a query plan to behave as I think it should. The addition of a TOP clause when querying an indexed view is causing a sub-optimal plan, and I'm hoping for some help in sorting it.
Environment

SQL Server 2019
StackOverflow2013 database (50GB version), Compat Mode 150 (problem is not specific to this version)

The setup:
Firstly, I've created a view to return everyone with a high reputation:
CREATE VIEW vwHighReputation
WITH SCHEMABINDING
AS
SELECT  [Id],
        [DisplayName],
        [Reputation]
FROM    [dbo].[Users]
WHERE   [Reputation] > 10000

Next, since I'll be searching by display name, I've created a couple of indexes on the view:
CREATE UNIQUE CLUSTERED INDEX IX_Users_Id ON [dbo].[vwHighReputation]([Id])
GO
CREATE NONCLUSTERED INDEX IX_Users_DisplayName ON [dbo].[vwHighReputation]([DisplayName]) INCLUDE (Reputation)
GO

If I query via the view, I can see my nonclustered index is being used:
SELECT  *
FROM    [dbo].[vwHighReputation]
WHERE   [DisplayName] LIKE 'J%'

Plan: (https://www.brentozar.com/pastetheplan/?id=Sy2EoJaiv)
So far so good. I can even use my view as part of a more complex query with an OUTER APPLY, and I still get a seek with only 63 reads against my index (this is obviously a contrived example, but helps illustrate the problem that I'll come to):
SELECT  [U].[Id],
        [A].[Reputation],
        [A].[DisplayName]
FROM    [dbo].[Users] AS [U]
        OUTER APPLY (
                        SELECT  *
                        FROM    [dbo].[vwHighReputation] AS [v]
                         WHERE   [v].[Id] = [U].[Id]
                    ) AS [A]
WHERE   [A].[DisplayName] LIKE 'J%';

Plan: https://www.brentozar.com/pastetheplan/?id=HJaw3y6ov
However, if I add a TOP 1 to my OUTER APPLY:
SELECT  [U].[Id],
        [A].[Reputation],
        [A].[DisplayName]
FROM    [dbo].[Users] AS [U]
        OUTER APPLY (
                        SELECT  TOP 1 *
                        FROM    [dbo].[vwHighReputation] AS [v]
                        WHERE   [v].[Id] = [U].[Id]
                    ) AS [A]
WHERE   [A].[DisplayName] LIKE 'J%';

Then the situation gets bad....very, very bad....
Plan: https://www.brentozar.com/pastetheplan/?id=HyOS6yaiw
My logical read count against that view is now almost 5 million. I can see from the plan that SQL Server is now choosing to perform a seek on the clustered index with the User's ID as the predicate, but doing so around 2.5 million times. It is also scanning the whole of the Users table. It no longer seeks on the view's index.
Obviously the optimiser is deciding that this is the most efficient approach, but I can't understand why! I've think it's probably to do with the way the underlying tables are sorted, but I'm not sure.
Incidentally, re-writing it as a simple SUB QUERY rather than CROSS APPLY yeilds the same outcome.
Any help or advice would be great!

Outer Apply
You're using OUTER APPLY, but with a where clause that would reject NULL values.
It's converted to an inner join without the TOP (1):
SELECT
    U.Id,
    A.Reputation,
    A.DisplayName
FROM dbo.Users AS U
OUTER APPLY
(
    SELECT
        v.*
    FROM dbo.vwHighReputation AS v
    WHERE v.Id = U.Id
) AS A
WHERE A.DisplayName LIKE 'J%'
ORDER BY U.Id;

I've formatted your code a little bit, and added an ORDER BY to validate results across queries. No offense.
Outer Apply + TOP (1)
When you use the TOP (1), the join is of the LEFT OUTER variety:
SELECT
    U.Id,
    A.Reputation,
    A.DisplayName
FROM dbo.Users AS U
OUTER APPLY
(
    SELECT TOP (1)
        v.*
    FROM dbo.vwHighReputation AS v
    WHERE v.Id = U.Id
) AS A
WHERE A.DisplayName LIKE 'J%'
ORDER BY U.Id;

The TOP (1) inside the OUTER APPLY apparently makes the optimizer unable to apply the same transformation to an inner join, even with a redundant predicate:
SELECT
    U.Id,
    A.Reputation,
    A.DisplayName
FROM dbo.Users AS U
OUTER APPLY
(
    SELECT TOP (1)
        v.*
    FROM dbo.vwHighReputation AS v
    WHERE v.Id = U.Id
    AND   v.DisplayName LIKE 'J%'
) AS A
WHERE A.DisplayName LIKE 'J%'
ORDER BY U.Id;

Note the residual predicates to evaluate if the Id and DisplayName columns are NULL.
This isn't just a TOP (1) issue either -- you can substitute any values up to the big int max (9223372036854775807) and see the same plan.
It will also happen if you skip the view entirely.
SELECT
    U.Id,
    A.Reputation,
    A.DisplayName
FROM dbo.Users AS U
OUTER APPLY
(
    SELECT TOP (1)
        v.Id,
        v.DisplayName,
        v.Reputation
    FROM dbo.Users AS v
    WHERE v.Reputation > 10000
    AND   v.Id = U.Id
) AS A
WHERE A.DisplayName LIKE 'J%'
ORDER BY U.Id
OPTION(EXPAND VIEWS);

A Rewrite
One way to get the same effect as TOP (1) without the various optimizer side effects of TOP is to use ROW_NUMBER
SELECT
    U.Id,
    A.Reputation,
    A.DisplayName
FROM dbo.Users AS U
OUTER APPLY
(
    SELECT
        v.*
    FROM
    (
        SELECT
            v.*,
            ROW_NUMBER() OVER
            (
                PARTITION BY
                    v.Id
                ORDER BY
                    v.Id
            ) AS n
        FROM dbo.vwHighReputation AS v
    ) AS v
    WHERE v.Id = U.Id
    AND   v.n = 1
) AS A
WHERE A.DisplayName LIKE 'J%'
ORDER BY U.Id;

Which will get you the original plan:


Index that mimics full-text-index I have a database that is retrieving information quite slowly, I asked a question and was told to use full-text-indexing, this worked, but now we switched over to a .sdf file and Sql Server Compact Edition 4.0 does not support full-text-indexing

Is there any index that will mimic the way a full-text-index works?

Update and Insert Statement taking forever

This is the code I am using:

 SELECT TimesUsed, Word FROM Predictions WHERE (Word LIKE @partialWord) ORDER BY TimesUsed DESC

PredTable = PredTA.GetDataByPartialWord(combo)
        If PredTable.Count = 1 Then PredTA.Update(PredTable(0).TimesUsed + 1, combo)
        If PredTable.Count = 0 Then PredTA.Insert(combo, 1)

Because Compact Edition is deprecated and doesn't support full-text search (or anything like it), I think the best solution here is to stop using Compact Edition and instead use SQL Server Express with Advanced Services, which includes full-text search capabilities.

Stop moving database files around between developers. This is just dangerous, regardless of whether you are using Compact Edition or proper editions of SQL Server. You should install a single instance of SQL Server, and everybody manage a single copy of the database. Use source control so that rolling back is easier than "use the copy of the file from Jimmy's machine and then add this change and take this table away..."

In order to convert back from Compact Edition to proper SQL Server, see this question and this question.


MySQL database cannot accept SSL connections (Certs are UTD) Not sure if this is the right place for this kind of question, but I have a MySQL Database running on an Azure VM. Everything was working great, until last week it seemingly randomly stopped allowing SSL connections. From everything I've seen looking through the certificates on the VM and the ones assigned to the VM through the Azure portal, none of them are expired.

Even if I try to connect to the database while Remoting into the VM (so making it a local connection), it won't allow SSL. Not sure where to look from here, so any help would be greatly appreciated!

Edit: Just wanted to add the fact that if I try to connect with No SSL it does connect fine. I just don't know why SSL stopped working all of the sudden. I also wasn't able to find any mention of the SSL cert in the config file, which seemed off to me.

The issue was not with the database, but with the connector. As of v8.0.8 of the MySQL connector, they made it default to use SSL. If you want to not use SSL you just have to add "SslMode=none" to the end of your connection string.


Split a string into multiple rows and get the count for each of them I have a table "Production Database" in Server. I am using MS Access as a front end interface. I have some forms there. I want to create a query which will split records in "Reviewer" column and give me count for each of them. below is an example of the column, how it looks like

Reviewer
-----------------------
Person1,Person2,Person5
Person3,Person1
Person4

Please help. Thanks in advance

You could try something like this if you have a recent version of SQL Server! I've used PostgreSQL for this example - but SQL Server has the STRING_SPLIT function which appears to be very similar to PostgreSQL's regexp_split_to_table function.

I can't appear to get SQL Server's  function to work on dbfiddle.uk (Express Edition issue?). My own dbfiddle of the code below can be found here! There's also an SQL Server UNNEST function but the second SQL doesn't appear to be as compatible!

Create and populate your table -

CREATE TABLE reviewer(
Reviewer TEXT
);

data -

INSERT INTO reviewer
        ( Reviewer )
VALUES ('Person1,Person2,Person5')
,('Person3,Person1')
,('Person4');

and then run this SQL (suitably translated for SQL Server) -

SELECT rev, COUNT(rev)
FROM
(
  SELECT regexp_split_to_table(r.reviewer, ',') AS rev -- use STRING_SPLIT here!
  FROM reviewer r
) AS tab
GROUP BY rev
ORDER BY rev

Result -

Rev     count
Person1     2
Person2     1
Person3     1
Person4     1
Person5     1

You should always include your version of SQL Server when asking questions here. p.s. welcome to the forum!

You could also try something like this - but it would appear to be more difficult to translate the SQL - just an FYI - same result!

SELECT rev, COUNT(rev)
FROM
(
  SELECT rev
  FROM reviewer r,
  unnest(string_to_array(r.reviewer, ',')) AS rev
) AS tab
GROUP BY rev
ORDER BY rev