I had a similar issue with an off the shelf three tier application where the app server would make a connection to the database when the user logged on.  The user activity was typically to log on in the morning and leave the app open on the desktop all day even if they did nothing for long periods.

The app server was not configured to use a shared pool and would commonly hit the maximum number of sessions after two or three days.  As you found the only resolution was to restart the database every night which is crazy unless the app owners say you can't change anything but you can restart it.  You may wish to verify if you are using shared connections  (pooling) and re examine your code to make sure connections are being dropped.

Here are some things you can check to see if this is a similar situation.  This answer here is also helpful.

Log onto the database and run this from sqlplus with a privileged account
--this gives the maximum amount of sessions

SELECT name, value
  FROM v$parameter
 WHERE name = 'sessions'

--number of users currently connected even if they are not doing anything

SELECT COUNT(*)
  FROM v$session;

I created this procedure on the database and scheduled it for every five or ten minutes.  Then you can examine the file and see if you are hitting the maximum number of sessions.  To use this you must also create a directory PROCESS_LOG_DIR where the user running this has at least write permissions on.

CREATE OR REPLACE PROCEDURE COUNT_PROCESSES
IS
   f                    UTL_FILE.file_type;
   v_processes          NUMBER (10);
   v_current_logons     NUMBER (10);
   v_current_sessions   NUMBER (10);
   v_highwater          NUMBER (10);
BEGIN
   SELECT   COUNT ( * ) INTO v_processes FROM v$process;

   SELECT   VALUE
     INTO   v_current_logons
     FROM   v$sysstat
    WHERE   name = 'logons current';

   SELECT   sessions_current, sessions_highwater
     INTO   v_current_sessions, v_highwater
     FROM   v$license;

   f := UTL_FILE.fopen ('PROCESS_LOG_DIR', 'Process.txt', 'a');

   UTL_FILE.put_line (
      f,
         TO_CHAR (SYSDATE, 'DD-MON-YYYY HH24:MI:SS')
      || ','
      || v_processes
      || ','
      || v_current_logons
      || ','
      || v_current_sessions
      || ','
      || v_highwater
   );
   UTL_FILE.fclose (f);
EXCEPTION
   WHEN UTL_FILE.invalid_path
   THEN
      raise_application_error (
         -20000,
         'ERROR: Invalid path. Create directory or set UTL_FILE_DIR.'
      );
END;


Java application not using Transparent Application failover in Oracle database

The output shows that your sessions use the "Thin" driver (JDBC Thin Client), which is incapable of using TAF.
Transparent Application Failover

Transparent Application Failover (TAF) is a feature of the Java
Database Connectivity (JDBC) Oracle Call Interface (OCI) driver.

Sometimes the JDBC OCI driver is also called as the "Thick" driver.
Here is a blog series about this topic:
https://blog.yannickjaquier.com/oracle/jdbc-client-high-availability-features-part-1.html
Part 2 is about TAF:
https://blog.yannickjaquier.com/oracle/jdbc-failover-highly-available-part-2.html
Make sure you configure swingbench to use oci driver type. It has the -dt oci commandline option for that. Also make sure you use a proper connection string with the failover options and addresses.


CLOB vs Varchar2 performance in this case?

Pre-12c: varchar2 is limited to 4000 bytes. If you need to store more data in a field, go for CLOB.

From 12c: varchar2 is limited to 4000 bytes, or 32767 bytes after enabling Extended datatypes. If you need to store more data in a field, go for CLOB. With Extended datatypes, varchar2 is stored as a LOB internally, where "short" fields go to the table segment, while "long" fields go to the LOB segment.

Both types can be indexed (Oracle Text).

LOBs are designed to be written once, then treat as read-only data. DML operations on existing LOB data typically perform worse than on varchar2.

LOBs support deduplication (requires the Advanced Compression option).

Depending on the use-case, varchar2 can perform better or same.

But based on what you wrote above (read-only data, column is not accessed or searched), CLOB should be fine, and you do not need to fiddle with Extended Datatypes or care about the limitations of varchar2.


Why?

Saying it in short. Because of HASH hint shots to optimizer's leg and optimizer itself shots to the other. Being shot to both optimizer can't cross the finish line.

To better illustrate what is going on, let's rewrite problematic query to join two VALUES and to use merge algorithm instead

DECLARE @id1 int = 101, @id3 int = 103;

SELECT *
FROM (VALUES (@id1)) p([Id])
    FULL MERGE JOIN (VALUES (@id1), (@id3)) d([Id]) ON d.[Id] = p.[Id];

Execution plan of this query is simple. There is the Merge Join operator with two Constant Scan inputs.

These two Constant Scans are different to optimizer though.

The one representing single-row input has column name prefixed with Expr, whereas the other representing multiple-rows input has column name prefixed with Union. Data from multiple-rows Constant Scan is accessed in the Merge Join predicates a kind of "by reference" ([Union1001]), whereas single-row Constant Scan data is accessed a kind of "by value" (see that @id1 is substituted instead of [Expr1000]).

This "by reference"→"by value" substitution is the result of scalar mapping performed at early optimization stages.

One can see (using trace flag 8606) that in the Input Tree join predicate is [Union1001] = [Expr1000]

*** Input Tree: ***
    ...
    LogOp_FullOuterJoin
        ...
        ScaOp_Comp x_cmpEq
            ScaOp_Identifier COL: Union1001
            ScaOp_Identifier COL: Expr1000
    ...

but then in the Simplified Tree it becomes [Union1001] = @id1

*** Simplified Tree: ***
    LogOp_FullOuterJoin
        ...
        ScaOp_Comp x_cmpEq
            ScaOp_Identifier COL: Union1001
            ScaOp_Identifier COL: @id1

Scalar mapping is the part of projection pulling logic and actually performed before simplification stage is entered.

One may did noticed earlier, that Merge Join node has only residual predicate and no join equality predicate. This is because of join equality predicate has been eliminated by scalar mapping. The [Union1001] = @id1 is equality predicate, but it can not serve as a join equality predicate. To be such it has to reference columns from both inputs, but @id1 is variable and not a column.

So, being equijoin ON d.[Id] = p.[Id] originally, the query transformed to non-equijoin (which is special case, and because of that, by the way, optimizer did not introduced sorting below Merge Join for the non-sorted Constant Scan inputs). Fortunately, in case of merge algorithm optimizer has such non-equijoin alternative.

In case of using hash algorithm non-equijoin alternative does not exists, and so, join equality predicate elimination causes optimizer to fail later on.

  Is there a way (besides putting parameter into temporary table) to
  make it work using hash algorithm?

There is no trace flag(*) that prevents scalar mapping, neither query lever nor session level nor start-up. And there is no optimization rule that can be turned off to prevent it, because of it is not performed by a rule.

I was only able to execute problematic query by setting breakpoint in the COptExpr::PexprMapScalar routine

and modifying value of the eax register after call to ScaOp_Identifier::ClassNo to make SQL Server think that second operand of ScaOp_Comp is not identifier.

Here is the Simplified Tree of the problematic query posted in the question

*** Simplified Tree: ***
    LogOp_FullOuterJoin
        LogOp_ConstTableGet (1) COL: Expr1000
            ScaOp_Identifier COL: @id1
        LogOp_Get TBL: #data(alias TBL: d)
        ScaOp_Comp x_cmpEq
            ScaOp_Identifier QCOL: [d].Id
            ScaOp_Identifier COL: Expr1000
*******************

and here is its plan obtained.

It has little sense actually, because of obtained plan cost is 0.0210675 units, whereas running query without HASH hint leads to execution plan with Merge Join (notice no sorting below Merge Join again)

costing 0.0088948 units.

(*) There may exist a combination of trace flags. I think it doesn't though, but I didn't explored all code paths, so I don't know it for certainty.


CLIENT SIDE
Before you run this
LOAD DATA local infile '/home/pi/Downloads/load_data_infile.txt'
INTO TABLE test_tbl FIELDS TERMINATED BY ',' LINES TERMINATED BY '\n';

You need to start mysql client program with --local-file
mysql --local-file -uroot -p

If you wish to have this configured permanantly, add this to ~/.my.cnf
[mysql]
local-infile

This forces the mysql client program to execute.
SERVER SIDE
The mysqld (server side must be started with local_infile) in its my.cnf or my.ini.
Make sure to add this
[mysqld]
local_infile=1

and run
service mysql restart

If you cannot config it and restart it, login to mysql and run
mysql> SELECT @@global.local_infile;

or
mysql> SHOW GLOABL VARIABLES LIKE 'local_infile';

If you get 0, you cannot execute LOAD DATA LOCAL INFILE no matter what you do to the client side.
SUPPLEMENTAL INFO
Running the following command
LOAD DATA local-infile '/home/pi/Downloads/load_data_infile.txt'
INTO TABLE test_tbl FIELDS TERMINATED BY ',' LINES TERMINATED BY '\n';

Gives the following error

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '-infile '/home/pi/Downloads/load_data_infile.txt' INTO TABLE test_tbl FIELDS TER' at line 1

That's just a straight up syntax error.
Get rid of the dash between local and infile so that it reads
LOAD DATA local infile '/home/pi/Downloads/load_data_infile.txt'
INTO TABLE test_tbl FIELDS TERMINATED BY ',' LINES TERMINATED BY '\n';


Why can't I change the isolation levels of my connection in MySQL?

You need to try
SET tx_isolation = 'READ-UNCOMMITTED';

or
SET SESSION tx_isolation = 'READ-UNCOMMITTED';

You could also declare it at the start of the transaction
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

Give it a Try !!!
CAVEAT
Please note that you misspelled READ-UNCOMMITED (Missing T). It should be READ-UNCOMMITTED
You cannot change tx_isolation in the INFORMATION_SCHEMA database because it is an in-memory read-only database.


Why does sql server have a hard time rounding floats?

Reading the SQL Server manual, it appears that the database is using an IEEE float under the hood.

As such, "weird things" are known to happen.

See this FAQ from SQLite (I suspect it applies here also):

(16) Why does ROUND(9.95,1) return 9.9 instead of 10.0? Shouldn't 9.95 round up?


What is the standard way to make a invoice system database tables?

The route I have taken is to use a table for invoices, which cover the transaction data (customer, salesperson, date) and then a table for invoice items (itemID, quantity) which would have a foreign key to the invoice table. This would let you have as many items being attached to an invoice, and each invoice would still only have one record.


Mirroring partner timeout - Not working the way advertised?

I'd suggest that the clean shutdown of the principal is causing it.
From Automatic Failover

How Automatic Failover Works

If the principal server is still running, it changes the state of the principal database to DISCONNECTED and disconnects all clients from the principal database.

One may assume that the principal says "I'm DISCONNECTED" on clean shutdown
To test this correctly, remove the network cables (or power leads) from the node hosting the principal to simulate/invoke a "real" server failure
I assume that the mirror is actually remote and not on the same cluster...


Many one-to-many relationships between two tables

It is a one to many from the "Children" table; one child to many staff, one child to one or more "ResponsibleAdults" (who could be any familial relationship). If you change Father & Mother fields to be Responsible1, Responsible2 and add Responsible1Relationship, Responsible2Relationship to "Children", you'll solve that problem. Alternatively, you can create a join or link table using just childID, ResponsibleID and relationship (1 or more records per child ID). You'll want a "FamRelationship" lookup table for consistency in your data entry (might be parent, aunt, grandparent, guardian, etc". This way Child Joe can have Parents Mike and Mary, while his cousin child Jane can have Uncle Mike and Aunt Mary. Both Children records would reference the same 2 what you're currently calling "Parents" records.

If a "staff" member is not a member of Board of Directors and it's important to note that, add a tinyint indicator to "staff" (true/false, 1/0).


ROW_NUMBER() OVER (PARTITION BY B,A ORDER BY C) doesn't use index on (A,B,C)

It seems that there is no good definitive "answer" to the question "what is going on in the optimiser", unless you are its developer and know its internals.

I'll put together the comments here.

Overall, it seems that it would be too harsh to call it a bug, because the end result of the query is correct. In some cases the execution plan is simply not optimal. ypercubeᵀᴹ, Martin Smith and Aaron Bertrand call it "missed optimization".

  Seems like GROUP BY a,b and GROUP BY b,a yields identical plans
  but PARTITION BY can't use the same transformation

  There are other missing optimisations too where window functions with
  the same window specification can have an extra sort operation if
  separated in the select list by one with a different specification.

  Yeah, this seems like another missed optimization, and there are
  plenty of those. The optimizer is written by humans and isn't perfect

There is a somewhat related article Descending Indexes. Index ordering, parallelism, and ranking calculations by Itzik Ben-Gan. There Itzik discusses descending indexes and also gives an example how direction of the index definition affects window functions with partitions. He shows examples of queries and generated plans with ROW_NUMBER that have extra sort operator that optimiser could have avoided.

To me the practical outcome would be to keep this peculiarity of optimiser in mind. When using PARTITION BY in window functions always try to match the order in which you list the columns in PARTITION BY with the order in which they are listed in the index. Even though it should not matter.

Another side of this precaution is when you review your indexes and decide to swap some columns around in the index definition. Be aware that you may inadvertently affect some existing queries that seemingly should not be affected. This is actually how I noticed this peculiarity of the optimiser.

If you don't, optimiser may not be able to use the index to its full potential. Even if optimiser does choose an optimal plan, such plan may change to less optimal with a slightest innocent change to the query, like changing the order of columns in the SELECT statement.


Here is before trigger you can use to guarantee that id1 and id2 are inserted in min/max order

DELIMITER $$
CREATE TRIGGER mytable_bi BEFORE INSERT ON mytable
FOR EACH ROW
BEGIN
    DECLARE sum INT;
    SET sum = NEW.id1 + NEW.id2;
    SET NEW.id1 = (NEW.id1 + NEW.id2 - ABS(NEW.id1 - NEW.id2)) / 2;
    SET NEW.id2 = sum - NEW.id1;
END $$
DELIMITER ;

Here is some Sample Data:

DROP DATABASE IF EXISTS ryanzec;
CREATE DATABASE ryanzec;
USE ryanzec
CREATE TABLE mytable
(
    id1 INT NOT NULL,
    id2 INT NOT NULL,
    mapTypeId INT NOT NULL,
    primary key (id1,id2)
);
DELIMITER $$
CREATE TRIGGER mytable_bi BEFORE INSERT ON mytable
FOR EACH ROW
BEGIN
    DECLARE sum INT;
    SET sum = NEW.id1 + NEW.id2;
    SET NEW.id1 = (NEW.id1 + NEW.id2 - ABS(NEW.id1 - NEW.id2)) / 2;
    SET NEW.id2 = sum - NEW.id1;
END $$
DELIMITER ;
INSERT INTO mytable VALUES (1,2,3);
INSERT INTO mytable VALUES (2,1,3);
INSERT INTO mytable VALUES (5,4,6);
INSERT INTO mytable VALUES (4,5,6);
SELECT * FROM mytable;

Here it is the Sample Data Loaded

mysql> DROP DATABASE IF EXISTS ryanzec;
Query OK, 1 row affected (0.03 sec)

mysql> CREATE DATABASE ryanzec;
Query OK, 1 row affected (0.00 sec)

mysql> USE ryanzec
Database changed
mysql> CREATE TABLE mytable
    -> (
    ->     id1 INT NOT NULL,
    ->     id2 INT NOT NULL,
    ->     mapTypeId INT NOT NULL,
    ->     primary key (id1,id2)
    -> );
Query OK, 0 rows affected (0.10 sec)

mysql> DELIMITER $$
mysql> CREATE TRIGGER mytable_bi BEFORE INSERT ON mytable
    -> FOR EACH ROW
    -> BEGIN
    ->     DECLARE sum INT;
    ->     SET sum = NEW.id1 + NEW.id2;
    ->     SET NEW.id1 = (NEW.id1 + NEW.id2 - ABS(NEW.id1 - NEW.id2)) / 2;
    ->     SET NEW.id2 = sum - NEW.id1;
    -> END $$
Query OK, 0 rows affected (0.10 sec)

mysql> DELIMITER ;

Watch What Happens When I Insert the Rows:

mysql> INSERT INTO mytable VALUES (1,2,3);
Query OK, 1 row affected (0.06 sec)

mysql> INSERT INTO mytable VALUES (2,1,3);
ERROR 1062 (23000): Duplicate entry '1-2' for key 'PRIMARY'
mysql> INSERT INTO mytable VALUES (5,4,6);
Query OK, 1 row affected (0.07 sec)

mysql> INSERT INTO mytable VALUES (4,5,6);
ERROR 1062 (23000): Duplicate entry '4-5' for key 'PRIMARY'
mysql> SELECT * FROM mytable;
+-----+-----+-----------+
| id1 | id2 | mapTypeId |
+-----+-----+-----------+
|   1 |   2 |         3 |
|   4 |   5 |         6 |
+-----+-----+-----------+
2 rows in set (0.00 sec)

mysql>

Now, no matter what order id1 and id2 are entered, id1 will always be less than or equal to id2

This may not be contextually what you want because id1 and id2 are unique identifiers.


FILESTREAM is enabled, but I get a message that it's not. Anyone able to help?

I would suggest double checking all the requirements for enabling FILESTREAM as per:

How to: Enable FILESTREAM


It looks like your varbinary string got injected with some nonsense. All of the sequences of two 00 values are null characters, so this is why your string is terminating upon conversion. The first character is 0x24 (dollar sign), which is why the output is just a dollar sign.

SELECT CONVERT(varchar(60), 0x2400....anything....);
-- is equivalent to:
SELECT CONVERT(varchar(60), 0x24);

Now, if I take your string and strip out all of the 00 sequences:

SELECT CONVERT(VARCHAR(60), 0x248B0189FF32323633393933352D4B434E);

I get something close; again, there is a bunch of garbage in there, but the string you're after is in there:

$‹‰ÿ22639935-KCN

You can ignore the leading garbage by just taking the original value and performing RIGHT() against it, but this assumes that the important part of the string is always the same length (we can't tell you if that's true).

SELECT CONVERT(VARCHAR(60),
       RIGHT(0x24000000008B010000000089FF32323633393933352D4B434E000000, 15));

Or using SUBSTRING, but this assumes that the garbage at the beginning of the string is always the same length:

SELECT CONVERT(VARCHAR(60),
       SUBSTRING(0x24000000008B010000000089FF32323633393933352D4B434E000000, 14, 46));

We also can't possibly tell you why that garbage is there and if it has any additional meaning. You'll need to find out how the values got encoded this way in the first place. The value you wanted encoded, 22639935-KCN, should have looked a little differently as varbinary:

SELECT CONVERT(VARBINARY(32), '22639935-KCN');

--------------------------
0x32323633393933352D4B434E

So, again, you will need to do some research to find out why this value wasn't encoded this way. We can't answer all of this because we didn't design your system or store those values.


Best way to evenly distribute data over partitioned tables

Hashing is a common partitioning mechanism for your use case..  It is NOT available in SQL 2008: http://www.sqlservercentral.com/articles/partition/64740/.

The problem with hash partitioning is that if you add partitions, you need to redistribute the data.  Logically speaking, any partitioning scheme that dynamically determines which partition the data goes into the data would need to redistribute the data after you add partitions (other than perhaps using a hash on the insert time + keeping track of when each partition was added).  This assumes partitioning (distributing data via a predefined scheme to reduce subsequent lookup time) vs sharding (randomly distributing data to multiple boxes reduce processing time of the entire data set).

You can probably simulate a hash partition by generating the hash yourself on insert/update, and using a range based partition over the hashed value.  For instance, lets say you want 10 partitions.  Modulus divide a hash of your key value by 10, and range partition on the remainder (with each range taking one of the 10 possible values).


Unfortunately, Dynamic SQL is not permitted in Triggers.
Notwithstanding, what you are asking for is still possible, but you will have to apply some elbow grease.

I wrote an answer to the post Disable trigger for just one table. What I did was create a user variable @TRIGGER_DISABLED as a flag to control if the code within the trigger was to be executed.

In your particular case, you will need 9 flags. You will also need a BEFORE UPDATE trigger:

DELIMITER $$
CREATE TRIGGER `setup_record_time` BEFORE UPDATE ON `my_table`
FOR EACH ROW
BEGIN
  SET @rec_col1 = IF(NEW.col1=OLD.col1,0,1);
  SET @rec_col2 = IF(NEW.col2=OLD.col2,0,1);
  SET @rec_col3 = IF(NEW.col3=OLD.col3,0,1);
  SET @rec_col4 = IF(NEW.col4=OLD.col4,0,1);
  SET @rec_col5 = IF(NEW.col5=OLD.col5,0,1);
  SET @rec_col6 = IF(NEW.col6=OLD.col6,0,1);
  SET @rec_col7 = IF(NEW.col7=OLD.col7,0,1);
  SET @rec_col8 = IF(NEW.col8=OLD.col8,0,1);
  SET @rec_col9 = IF(NEW.col9=OLD.col9,0,1);
END $$
DELIMITER $$

The AFTER UPDATE trigger looks like this:

DELIMITER $$
CREATE TRIGGER `record_time` AFTER UPDATE ON `my_table`
FOR EACH ROW
BEGIN
  IF @rec_col1 = 1 THEN INSERT INTO `record_changes` (col1) values (CUR_DATE()); END IF;
  IF @rec_col2 = 1 THEN INSERT INTO `record_changes` (col2) values (CUR_DATE()); END IF;
  IF @rec_col3 = 1 THEN INSERT INTO `record_changes` (col3) values (CUR_DATE()); END IF;
  IF @rec_col4 = 1 THEN INSERT INTO `record_changes` (col4) values (CUR_DATE()); END IF;
  IF @rec_col5 = 1 THEN INSERT INTO `record_changes` (col5) values (CUR_DATE()); END IF;
  IF @rec_col6 = 1 THEN INSERT INTO `record_changes` (col6) values (CUR_DATE()); END IF;
  IF @rec_col7 = 1 THEN INSERT INTO `record_changes` (col7) values (CUR_DATE()); END IF;
  IF @rec_col8 = 1 THEN INSERT INTO `record_changes` (col8) values (CUR_DATE()); END IF;
  IF @rec_col9 = 1 THEN INSERT INTO `record_changes` (col9) values (CUR_DATE()); END IF;
END $$
DELIMITER $$

Just to be on the safe side, you should initialize these 9 user variables with 0.

SET @rec_col1 = 0;
SET @rec_col2 = 0;
SET @rec_col3 = 0;
SET @rec_col4 = 0;
SET @rec_col5 = 0;
SET @rec_col6 = 0;
SET @rec_col7 = 0;
SET @rec_col8 = 0;
SET @rec_col9 = 0;
UPDATE mytable ... ;

That way, your session dictates the trigger's behavior.

Give it a Try !!!


What is most efficient way to compare two large result sets in SQL Server 2012

EXCEPT implies a DISTINCT operation.

I would use NOT EXISTS if this is not actually required.

However the problem you are having is likely that you are getting a nested loops on an unindexed table due to the poor cardinality estimates associated with table variables.

select * from @temptableNEW
except
select * from @temptableOLD
OPTION (RECOMPILE)

Will be able to take account that the tables have 100K rows each and give a different plan.

In SQL Server 2012 you can only add indexes to table variables via constraints. If the values are unique you could use

DECLARE @temptableOLD TABLE ([Result1] int UNIQUE CLUSTERED);

to add an index. If done on both tables the plan (after the recompile hint is added) will likely use a merge join instead. Without any indexes I would expect a hash join.


How can I update statistics adding the data of the last day only?

The first thing to try is experimenting with sampling instead of performing a full scan every time.

If lower samples are causing poor plans or still taking too long you could consider partitioning and if you are on SQL Server 2014 or higher you could use Incremental Statistics.

Incremental Statistics allow you to rebuild statistics at the partition level. There is still just one statistics object for the whole table but the statistics for the partition are merged into the histogram.

If you're not running on Enterprise edition or you are on a version prior to 2014 you might consider using partitioned views. This way you can partition your data into completely separate tables with their own statistics objects.

Partitioning and partitioned views bring their own sets of complications but are usually required for very large tables.

You may be suffering from the ascending key problem, see here. If you are then one last option could be to update statistics less often and use the trace flags 2389 and 2390 to improve cardinality estimates in the time between updates.


WITH cte AS
(
SELECT *,
       CASE field WHEN 'ShortName'
                  THEN CASE operator WHEN 'Contains'
                                     THEN ShortName LIKE CONCAT('%', criteria, '%')
                                     WHEN 'Equal'
                                     THEN ShortName = criteria
                                     ELSE 0
                                     END
                  WHEN 'Genre'
                  THEN CASE operator WHEN 'Is'
                                     THEN Genre = criteria
                                     WHEN 'IsNot'
                                     THEN Genre != criteria
                                     ELSE 0
                                     END
                  WHEN 'Time_Mins'
                  THEN CASE operator WHEN 'IsGreaterThan'
                                     THEN Time_Mins > criteria
                                     WHEN 'IsLessThan'
                                     THEN Time_Mins < criteria
                                     ELSE 0
                                     END
                  WHEN 'Action'
                  THEN CASE operator WHEN 'Is'
                                     THEN Action = criteria
                                     WHEN 'IsNot'
                                     THEN Action != criteria
                                     ELSE 0
                                     END
                  ELSE 0
                  END test
FROM movies
CROSS JOIN filters
)
SELECT MovieID, ShortName, Genre, Time_Mins, Action, filterid
FROM cte
GROUP BY MovieID, ShortName, Genre, Time_Mins, Action, filterid
HAVING SUM(test=0)=0
ORDER BY MovieID, filterid;

fiddle

Pay attention to formalized filters storing format.

You may add possible operator/criteria values by adding according variants into CASEs.

For example, if you want to add a filtering variant for to select strict time you must add WHEN 'Equal' THEN Time_Mins = criteria to Time_Mins checking CASE.

But do not forget that criteria datatype is STRING! And Action datatype is string too. Use proper data type convertion when default convertion is not obvious.


SQL Server Max number of Times a table can be referenced as a foreign key?

The short answer is, "No." The longer answer is that there is no explicit restriction, but since those relationships require metadata, the do consume memory and resources: if you have, say, 10,000 foreign key relationships to a single table's primary key, you're likely to find that a delete against the referenced table might be a little...painful.

Here's what the documentation has to say:

Number of FOREIGN KEY Constraints In A Table

SQL Server does not have a predefined limit on either the number of FOREIGN KEY constraints a table can contain (which reference other tables), or the number of FOREIGN KEY constraints owned by other tables that reference a specific table. Nevertheless, the actual number of FOREIGN KEY constraints is limited by your hardware configuration and by the design of your database and application. We recommend that a table contain no more than 253 FOREIGN KEY constraints, and that it be referenced by no more than 253 FOREIGN KEY constraints. Consider the cost of enforcing FOREIGN KEY constraints when you design your database and applications.

It should also be noted, though, that, SQL Server's system views (e.g., sys.objects) use 32-bit integers as identifiers, the domain of which puts an implicit upper limit on the total number of foreign keys the database could contain.

Not that you'd ever come close to exceeding that limit.


Why do people recommend not using the name "Id" for an identity column?

The table name prefix has very good reasons.

Consider:

TableA (id int identity, stringdata varchar(max))

TableB (id int identity, stringdata varchar(max))

We want to DELETE from TableA records that exist in both tables.  Easy enough, we will just do an INNER JOIN:

DELETE a
FROM
  TableA A
INNER JOIN
  TableB B
    ON b.id = B.id

....and we just wiped out all of TableA.  We inadvertently compared B's ID to itself -   every record matched, and every record got deleted.

If the fields had been named TableAId and TableBId this would be impossible (Invalid field name TableAid in TableB).

Personally I have no issue with using the name id in a table, but it's really a better practice to preface it with the table name (or entity name, if TableA were people then PeopleId would work fine too) to avoid accidentally comparing to the wrong field and blowing something up.

This also makes it very obvious where fields come from in long queries with lots of JOINs.


MongoDB replica set - Error in heartbeat request

Actually no! If you shut down one node from your replica set, other nodes, of course, start complaining that they cannot connect that missing node. All nodes are always "talking" to each other.

And error message tells that, that missing node was in the port 27021.


How much memory does the MySQL server variable table_open_cache cause the DBMS to use?

Let's look at it from another point of view -- Is table_open_cache too big or too small?
If Table_open_cache_overflows / Uptime > 1 per second, then increase the setting.
If Table_open_cache_misses / Uptime > 1/sec, then increase table_open_cache
If Table_open_cache_misses / (Table_open_cache_hits + Table_open_cache_misses) > 3%, then increase table_open_cacheand checktable_open_cache_instances`.


Finding the first occurrence where id don't match

How about something like this?

select top 1 snum
from #temp1
group by SNum
having count(*) < (select count(*) from #temp2)
order by snum asc

This could work if you're sure that there are no duplicates vernum for the same snum

This by the way, i think is a more solid solution:

select top 1 coalesce(a.snum, b.snum) as result
from #temp1 a full join #temp1 b on a.SNum = b.SNum and a.VerNum <> b.VerNum
where a.VerNum is null or b.VerNum is null

I didn't test them much, only with the data you provided, so be careful!


My first attempt would be to add btree indexes, not hash ones.

an index on B (attribute4, attribute3) and
an index on A (attribute1, attribute2)

The reasoning behind the suggestion is that the only a small part of the first index on B needs to be read, only the part that has attribute4 = 'String4' and then the values of attribute3 will be used for the join, against the other index on A.


Postgres migration local to remote, issue with terminal command

Having two programs in the same pipeline both trying to prompt for a password at the same time is confusing your terminal.

There may be a shell-specific way to fix this, but I'd fix it at the PostgreSQL level by either changing the local server to use an authentication method that doesn't need a password, or making a .pgpass file to accommodate the local connection (and maybe the remote connection, too).

For what it is worth, a shell way to get around this would be:

pg_dump -C -h localhost ... | (sleep 10; psql -h ...)

Where the sleep gives you enough time to enter the local password.


I would suggest using two tables, one to store the records uploaded and make an id for it, and another to hold the elements for the record.data types are just for illustration purpose only

create table UploadedRecords
(
    id int not null auto_increament,
    uploadtime datetime not null,
    .
    .
    primary key (uploaded_record_id)
);

create table RecordElements
(
    uploaded_record_id int not null,
    element_symbol varchar(2) not null,
    area varchar(20) not null,
    cnt int not null,
    primary key
        (uploaded_record_id, element_symbol),
    foreign key (uploaded_record_id)
        references UploadedRecords (uploaded_record_id)
);


SQL Server Assembly on Failover cluster after Framework update

Are you saying that the one assembly you have references unsupported .NET framework libraries that get imported implicitly when creating your assembly? In which case it would make sense that a Framework update could have this effect. SQLCLR requires that assemblies used in SQL Server that are also in the main OS GAC be the exact same version, and the version in the main GAC is now newer on the secondary due to the Framework update.

As far as I can tell, you will need to apply the same Framework update to the primary, reload the custom assembly, and failover, and then reload the assembly again. Even if the secondary was not read-only and you were able to update the assembly, if that instance failed over to the main node (assuming it still has not received the Framework update), then the SQLCLR object(s) would again fail due to the library version mismatch. And, even right now, if the primary instance fails over to the other node, the SQLCLR object(s) will error due to the library version mismatch.


T-SQL CREATE VIEW returning 'There is already an object named x'

Turns out the target db had a TABLE (not view) with the same name. I didn't create it, I suspect what has happened is that an Entity Framework project which I have created it when I changed the app.config connection string to point at the second database and ran the project.

Naughty Entity Framework.


SQL Server uses only PRIMARY partition by default

No, if you are talking about partitioned tables, sql uses the where clause to filter rows. if your query doesn't have the where clause it will return all rows.
if you can't test your changes you probably shouldn't be making them.


MySql Galera Multi site high availability

Consider:

Make it a 4-node Galera Cluster.  Have gcache set large enough to handle more than 30 minutes' worth of changes.  Then let Galera take care of the rest.

Meanwhile, your clients should not connect just to galera4, but via a proxy to any node.  (You may want to tell the proxy that "galera4" is preferred.)

But the real problem comes when you try to write to galera4 when it has lost connection to the other nodes.  A core principle of Galera says that an isolated node (like that) shall not accept writes.  Otherwise the data can become inconsistent.

Once you face that problem, you realize that "galera4" is not the solution.  Instead, working on the network is the solution, and having the proxy robust enough to find a way to the 3-node datacenter is sufficient.

Or...

NDB Cluster is a different clustering offering from MySQL.  It has a "eventually consistent" model for replication.  You can have any number of disconnected Masters, write to any of them.  The issue comes when you have conflicting writes (for example, two clients write with the same unique key).  The inconsistency is "eventually" caught, but this could be long after the client finished talking to the Master that it happened to reach.  At that point, some rule (such as "oldest timestamp wins") controls which record is kept and which is rejected.

NDB works well if you naturally are not going to write conflicting records.


I have some good news and some bad news.

The good news is that this will work for MySQL. The
bad news is that it won't work for 5.5. In any case,
I would advise moving to 5.6 anyway, in no small part
due to the vast improvements made to the performance
schema (see below).

I compiled and installed 5.6 (am currently running
5.7 - bit of a bleeding edge type of guy :-) ).

After some frustrating Googling &c. and barking
up a forest of wrong trees, I finally came up
with this.

SELECT Table_schema, Table_Name, Column_Name
FROM Information_Schema.Columns
Where (Table_Schema = 'information_schema'
or Table_Schema = 'sys')
AND Column_Name like '%lock%';

But, if you check Result 1 (at end of post), you'll
see I didn't have much luck.

But then I remembered Mark Leith's (Software
Development Senior Manager, Oracle) MySQL
sys schema (and ps_helper). These are available
from here

https://github.com/MarkLeith.

I ran the same SQL as above with

or Table_Schema = 'sys'

added, and I came up with Result 2 (at end
of post).

Innodb_Wait_Locks looked promising, so I ran a DESC.

mysql> DESC sys.innodb_lock_waits;
+---------------------+---------------------+------+-----+---------+-------+
| Field               | Type                | Null | Key | Default | Extra |
+---------------------+---------------------+------+-----+---------+-------+
| waiting_trx_id      | varchar(18)         | NO   |     |         |       |
| waiting_thread      | bigint(21) unsigned | NO   |     | 0       |       |
| waiting_query       | longtext            | YES  |     | NULL    |       |
| waiting_lock_id     | varchar(81)         | NO   |     |         |       |
| waiting_lock_mode   | varchar(32)         | NO   |     |         |       |
| waiting_lock_type   | varchar(32)         | NO   |     |         |       |
| waiting_lock_table  | varchar(1024)       | NO   |     |         |       |
| waiting_lock_index  | varchar(1024)       | YES  |     | NULL    |       |
| blocking_trx_id     | varchar(18)         | NO   |     |         |       |
| blocking_thread     | bigint(21) unsigned | NO   |     | 0       |       |
| blocking_query      | longtext            | YES  |     | NULL    |       |
| blocking_lock_id    | varchar(81)         | NO   |     |         |       |
| blocking_lock_mode  | varchar(32)         | NO   |     |         |       |
| blocking_lock_type  | varchar(32)         | NO   |     |         |       |
| blocking_lock_table | varchar(1024)       | NO   |     |         |       |
| blocking_lock_index | varchar(1024)       | YES  |     | NULL    |       |
+---------------------+---------------------+------+-----+---------+-------+
16 rows in set (0.00 sec)

So, there you have pretty much all the info you could
want about locks, (I hope!).

No, as to logging these, there doesn't appear to be a logging function
for this table - many of the p_s tables have accompanying history
tables. I think that there's a good case for keeping a history of
this one - but you can "roll your own" using MySQL events to
append the contents of this table to a logging table.

Say every one second (or period of your choice) and then check to
see which of your queries are regularlary appearing in the
logging table. It's perhaps not up to Oracle's functionality,
but it's getting there :-)

Result 1

+--------------------+------------------------+----------------------------+
| Table_schema       | Table_Name             | Column_Name                |
+--------------------+------------------------+----------------------------+
| information_schema | PROFILING              | BLOCK_OPS_IN               |
| information_schema | PROFILING              | BLOCK_OPS_OUT              |
| information_schema | INNODB_LOCKS           | lock_id                    |
| information_schema | INNODB_LOCKS           | lock_trx_id                |
| information_schema | INNODB_LOCKS           | lock_mode                  |
| information_schema | INNODB_LOCKS           | lock_type                  |
| information_schema | INNODB_LOCKS           | lock_table                 |
| information_schema | INNODB_LOCKS           | lock_index                 |
| information_schema | INNODB_LOCKS           | lock_space                 |
| information_schema | INNODB_LOCKS           | lock_page                  |
| information_schema | INNODB_LOCKS           | lock_rec                   |
| information_schema | INNODB_LOCKS           | lock_data                  |
| information_schema | INNODB_TRX             | trx_requested_lock_id      |
| information_schema | INNODB_TRX             | trx_tables_locked          |
| information_schema | INNODB_TRX             | trx_lock_structs           |
| information_schema | INNODB_TRX             | trx_lock_memory_bytes      |
| information_schema | INNODB_TRX             | trx_rows_locked            |
| information_schema | INNODB_TRX             | trx_autocommit_non_locking |
| information_schema | INNODB_LOCK_WAITS      | requested_lock_id          |
| information_schema | INNODB_LOCK_WAITS      | blocking_trx_id            |
| information_schema | INNODB_LOCK_WAITS      | blocking_lock_id           |
| information_schema | INNODB_BUFFER_PAGE_LRU | FREE_PAGE_CLOCK            |
| information_schema | INNODB_BUFFER_PAGE     | BLOCK_ID                   |
| information_schema | INNODB_BUFFER_PAGE     | FREE_PAGE_CLOCK            |
+--------------------+------------------------+----------------------------+
24 rows in set (0.02 sec)

Result 2

+--------------------+-------------------------------------+----------------------------+
| Table_schema       | Table_Name                          | Column_Name                |
+--------------------+-------------------------------------+----------------------------+
| information_schema | PROFILING                           | BLOCK_OPS_IN               |
| information_schema | PROFILING                           | BLOCK_OPS_OUT              |
| information_schema | INNODB_LOCKS                        | lock_id                    |
| information_schema | INNODB_LOCKS                        | lock_trx_id                |
| information_schema | INNODB_LOCKS                        | lock_mode                  |
| information_schema | INNODB_LOCKS                        | lock_type                  |
| information_schema | INNODB_LOCKS                        | lock_table                 |
| information_schema | INNODB_LOCKS                        | lock_index                 |
| information_schema | INNODB_LOCKS                        | lock_space                 |
| information_schema | INNODB_LOCKS                        | lock_page                  |
| information_schema | INNODB_LOCKS                        | lock_rec                   |
| information_schema | INNODB_LOCKS                        | lock_data                  |
| information_schema | INNODB_TRX                          | trx_requested_lock_id      |
| information_schema | INNODB_TRX                          | trx_tables_locked          |
| information_schema | INNODB_TRX                          | trx_lock_structs           |
| information_schema | INNODB_TRX                          | trx_lock_memory_bytes      |
| information_schema | INNODB_TRX                          | trx_rows_locked            |
| information_schema | INNODB_TRX                          | trx_autocommit_non_locking |
| information_schema | INNODB_LOCK_WAITS                   | requested_lock_id          |
| information_schema | INNODB_LOCK_WAITS                   | blocking_trx_id            |
| information_schema | INNODB_LOCK_WAITS                   | blocking_lock_id           |
| information_schema | INNODB_BUFFER_PAGE_LRU              | FREE_PAGE_CLOCK            |
| information_schema | INNODB_BUFFER_PAGE                  | BLOCK_ID                   |
| information_schema | INNODB_BUFFER_PAGE                  | FREE_PAGE_CLOCK            |
| sys                | host_summary_by_statement_latency   | lock_latency               |
| sys                | host_summary_by_statement_type      | lock_latency               |
| sys                | innodb_lock_waits                   | waiting_lock_id            |
| sys                | innodb_lock_waits                   | waiting_lock_mode          |
| sys                | innodb_lock_waits                   | waiting_lock_type          |
| sys                | innodb_lock_waits                   | waiting_lock_table         |
| sys                | innodb_lock_waits                   | waiting_lock_index         |
| sys                | innodb_lock_waits                   | blocking_trx_id            |
| sys                | innodb_lock_waits                   | blocking_thread            |
| sys                | innodb_lock_waits                   | blocking_query             |
| sys                | innodb_lock_waits                   | blocking_lock_id           |
| sys                | innodb_lock_waits                   | blocking_lock_mode         |
| sys                | innodb_lock_waits                   | blocking_lock_type         |
| sys                | innodb_lock_waits                   | blocking_lock_table        |
| sys                | innodb_lock_waits                   | blocking_lock_index        |
| sys                | processlist                         | lock_latency               |
| sys                | statement_analysis                  | lock_latency               |
| sys                | user_summary_by_statement_latency   | lock_latency               |
| sys                | user_summary_by_statement_type      | lock_latency               |
| sys                | x$host_summary_by_statement_latency | lock_latency               |
| sys                | x$host_summary_by_statement_type    | lock_latency               |
| sys                | x$innodb_lock_waits                 | waiting_lock_id            |
| sys                | x$innodb_lock_waits                 | waiting_lock_mode          |
| sys                | x$innodb_lock_waits                 | waiting_lock_type          |
| sys                | x$innodb_lock_waits                 | waiting_lock_table         |
| sys                | x$innodb_lock_waits                 | waiting_lock_index         |
| sys                | x$innodb_lock_waits                 | blocking_trx_id            |
| sys                | x$innodb_lock_waits                 | blocking_thread            |
| sys                | x$innodb_lock_waits                 | blocking_query             |
| sys                | x$innodb_lock_waits                 | blocking_lock_id           |
| sys                | x$innodb_lock_waits                 | blocking_lock_mode         |
| sys                | x$innodb_lock_waits                 | blocking_lock_type         |
| sys                | x$innodb_lock_waits                 | blocking_lock_table        |
| sys                | x$innodb_lock_waits                 | blocking_lock_index        |
| sys                | x$processlist                       | lock_latency               |
| sys                | x$statement_analysis                | lock_latency               |
| sys                | x$user_summary_by_statement_latency | lock_latency               |
| sys                | x$user_summary_by_statement_type    | lock_latency               |
+--------------------+-------------------------------------+----------------------------+
62 rows in set (0.04 sec)


Innovative solution required - Remotely create stored procedure

You could simply create a Group wihtin the registered servers pane in SSMS and add all servers to that group. Then you are able to run a query against the whole group. Just run the Create procedure (or whatever you need) there and it will be executed on all servers in the group (that could have connected with success).

Be aware that select queries ran against a group come back with results in a merged resultset that basically shows all results from all servers merged together with a ServerName column added for the origin (that does not allow any filtering by servers). This can be configured more detailed in SSMS at

  Tools -> Options -> Query Results -> SqlServer -> Multiserver Results

In case the tables or views on the different servers have a different definition, the merging of the result will fail (in case you use select *). In that case use a query with defined columnnames that allow SSMS to merge the result.


Postgresql error: failed to re-find parent key in index

Your database is likely corrupted, especially the indexes. You can rebuild all indexes and check all tables on a database by:

REINDEX SYSTEM; -- run once
REINDEX DATABASE <your dbname>; -- run for each database
VACUUM (FULL VERBOSE ANALYZE); -- also run for each database after reindexing


Should I use Log Shipping, Replication, SSIS or something else

From all the techniques that you are thinking of, only replication will suit your requirements.

  Data should be in the application DB within minutes (real time not required)

Use transactional replication.

  The data in the application DB might not be identical in structure to the source table (i.e some columns might be missing, some others might be added)

You can publish what columns you want. Make sure that @replicate_ddl is set to 0.

  The data should ONLY be added, no updates or deletions in the target even if the source database is purged.

This is where T-Rep shines out. You can choose do not replicate DELETE statements in the statement delivery --> Delete delivery format (same for update delivery format as well). This will require reinitialization.

Be careful when generating snapshot as it will overwrite all the data on subscriber side, though there are ways to carefully do it.


MySQL "Out of sort memory" error on a simple GROUP_CONCAT query

It looks like your name column concatenates two items.  Try building the table with the column split.

CREATE TABLE `permissions` (
  `id` INTEGER(11) UNSIGNED NOT NULL AUTO_INCREMENT,
  `object` VARCHAR(255) COLLATE utf8_unicode_ci NOT NULL DEFAULT '',
  `permission` VARCHAR(255) COLLATE utf8_unicode_ci NOT NULL DEFAULT '',
  PRIMARY KEY (`id`),
  UNIQUE KEY `id` (`id`),
  UNIQUE KEY `object_permission` (`object`, `permission`)
d
)ENGINE=InnoDB
AUTO_INCREMENT=11 CHARACTER SET 'utf8' COLLATE 'utf8_unicode_ci';

INSERT INTO `permissions` (`id`, `name`) VALUES
  (1,'ocjena_dobavljaca','view'),
  (2,'ocjena_dobavljaca','edit'),
  (3,'ocjena_dobavljaca','delete'),
  (4,'zaposlenici','view'),
  (5,'zaposlenici','edit'),
  (6,'zaposlenici','delete'),
  (7,'vatrogasni_aparati','view'),
  (8,'vatrogasni_aparati','edit'),
  (9,'vatrogasni_aparati','delete'),
  (10,'organizacijska_shema_drustva','');
COMMIT;

SELECT
    object,
    COUNT(*) as num,
    GROUP_CONCAT(permission)as nn
FROM permissions
GROUP BY object;


That's not possible, because you have different granularity in the two queries. You would have to calculate one average by yourself with a formula like sum(grades) / count(distinct full-student-name). (btw, your use of distinct is not necessary, the group by does that already for you)
With this you would get the count correct, but you would sum too much grades, because they are multiplied by the grouping for classes. You can't do sum(distinct grades) because a student could have the same grade in different classes.
You also can't calculate the average of the averages. That's usually close, but still not correct.


Implications of Changing to a Static Port

The SQL Server Browser service will help the client connect to each instance even with static port numbers.  As long as that service is running on your SQL server, then your web applications should not have any issue connecting.

You can read more about how the Browser service works here.


Can Microsoft Log Parser 2.2 open SQL LDF files?

Log != transaction log

That said, if you were hell bent on torturing yourself or a development team with an arduous time consuming task that would yield minimal value, this would be an ideal project. The cheaper option (if viewing transaction log records is really what you need) would be a 3rd party tool like ApexSQL Log.

Depending on what you're trying to achieve with this, the undocumented fn_dblog command might be of interest to you. Also see Tracking Transaction Log Activity in Denali for a neater way of handling this with extended events in the 2012.

If you expand your question with a better description of why you want to look at log records, we can suggest alternative (less hacky) approaches.


The issue of sp_MScheckIsPubOfSub stored procedure

Please check for orphaned rows for deleted subscriptions in sysmergesubscriptions and delete them.  For example, the subscriber that this is failing on may have duplicate entries in sysmergesubscriptions, one being orphaned from a previously deleted subscription.

This has been the cause of this error and solution for clients of mine in the past, ymmv.  I hope this helps.


Granting Read access to Replica database (via data guard) but need to restrict the same login in production Database

I think you can achieve this by using SQLNET.ORA file found under either in $ORACLE_HOME/network/admin or $GRID_HOME/network/admin or anywhere pointed by TNS_ADMIN variable.

tcp.validnode_checking = yes
tcp.invited_nodes =<list the valid nodes here>

Details are well explained in a blog written by Arup Nanda

If it is for application, you can restrict access by defining SERVICE_NAME of a standby database in TNS Name.

You can also create a logon trigger that will check the name of the user and the ROLE of the database. If the database role is not the 'PHYSICAL_STANDBY' then you can raise an error.


Cloning a Db2 database within a single instance

This is a minimial example of copying a database (test) to a into a new database (test2)

db2 create database test
db2 backup database test to /tmp
db2 restore database test from /tmp into test2
db2 connect to test2

   Database Connection Information

 Database server        = DB2/LINUXX8664 11.1.3.3
 SQL authorization ID   = DB2INST1
 Local database alias   = TEST2


Filter records and find those with minimal date

This query should obtain what you need (assuming "didn't participate in any conversation" means no records exist for that user in the Participants table.)
WITH CTE_Users_SortedByBirthDate AS -- Sort Users by their birthdate
(
    SELECT [uID], birthDate, [name], ROW_NUMBER() OVER (ORDER BY birthDate) AS OldestUser -- Note if two users have the same birthDate they'll both be returned, if you add uID after birthDate in the ORDER BY clause, then it'll guarantee you only one user: the oldest user by birthDate and by user ID
    FROM Users
    WHERE [name] LIKE '%b%' -- User's name contains the letter "b"
)

SELECT U.[uID], U.birthDate, U.[name]
FROM CTE_Users_SortedByBirthDate AS U
LEFT JOIN Participants AS P
    ON U.[uID] = P.[uID]
WHERE P.[uID] IS NULL -- Only users who have no Participant records
    AND U.OldestUser = 1 -- Returns only the oldest user(s) by birthDate


DB_NAME(database_id) return null when executed through linked server

The scope of DB_NAME() is limited to the local instance.  It only applies to databases that exist on the server where you are running the query.

Instead of using DB_NAME(), you should join to sys.databases with four part naming, as in the following example:

SELECT DB.name
FROM [linked_servername].master.sys.database_mirroring DM
    INNER JOIN [linked_servername].master.sys.databases DB ON DM.database_id = DB.database_id
WHERE DM.database_id > 4 and mirroring_role = 1;

I notice you have capitalized portions of the query including the master database, the sys schema, and the database_mirroring system table.  These objects exist in lower case, so you should code them in lower case.  For SQL Servers that have a case-sensitive collation, specifying the correct case is important for many reasons.


By default UNION - merge distinct values (https://www.postgresql.org/docs/10/static/queries-union.html) so if your quarter values same(and looking for code - it is exactly) - one of the results would be omitted.

Use UNION ALL if need keep all results


Doing a point in time restore with CDC enabled; Possible?

Change Data Capture, or CDC, does not require the KEEP_CDC keyword when restoring a database to the original server.    When restoring a database to a server other than the original server, you can use the KEEP_CDC keyword to specify that the change data capture data is retained in the database.  This is by design, and is clearly stated in BOL here under the section entitled "Restoring or Attaching a Database Enabled for Change Data Capture"

This code shows CDC in action both before and after a RESTORE DATABASE ... WITH NORECOVERY

CREATE DATABASE CDC_RESTORE_TEST
ON (NAME='CDC_RESTORE_TEST', FILENAME = 'C:\SQLServer\Data\CDC_RESTORE_TEST.mdf', SIZE = 10MB, FILEGROWTH = 1MB)
LOG ON (NAME = 'CDC_RESTORE_TEST_LOG', FILENAME = 'C:\SQLServer\Logs\CDC_RESTORE_TEST.ldf', SIZE = 1MB, FILEGROWTH = 1MB);
GO
ALTER DATABASE CDC_RESTORE_TEST
SET RECOVERY FULL;
GO
USE CDC_RESTORE_TEST;
GO
EXEC sys.sp_cdc_enable_db;
GO
CREATE TABLE Test
(
    ID INT NOT NULL CONSTRAINT PK_Test PRIMARY KEY CLUSTERED IDENTITY(1,1)
    , DATA NVARCHAR(255) NOT NULL
);
EXEC sys.sp_cdc_enable_table 'dbo', 'Test', 'dbo_Test', 1, NULL;
GO
ALTER TABLE Test ADD Data1 NVARCHAR(255) NOT NULL;
WAITFOR DELAY '00:00:15'; /* wait for event to be captured */
EXEC sys.sp_cdc_get_ddl_history 'dbo_Test';

USE Master;
GO
BACKUP DATABASE CDC_RESTORE_TEST TO DISK='C:\SQLServer\CDC_RESTORE_TEST.BAK';
BACKUP LOG CDC_RESTORE_TEST TO DISK='C:\SQLServer\CDC_RESTORE_TEST_LOG.BAK';
GO
ALTER DATABASE CDC_RESTORE_TEST SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
GO
DROP DATABASE CDC_RESTORE_TEST;
GO
RESTORE DATABASE CDC_RESTORE_TEST FROM DISK='C:\SQLServer\CDC_RESTORE_TEST.bak'
WITH NORECOVERY;
RESTORE LOG CDC_RESTORE_TEST FROM DISK='C:\SQLServer\CDC_RESTORE_TEST_LOG.bak'
WITH RECOVERY;
GO
USE CDC_RESTORE_TEST;
GO
EXEC sys.sp_cdc_get_ddl_history 'dbo_Test';

Restoring the same backup onto the same server as a new database name appears to retain CDC:

USE Master;
GO
RESTORE DATABASE CDC_RESTORE_TEST1 FROM DISK='C:\SQLServer\CDC_RESTORE_TEST.bak'
WITH NORECOVERY
    , MOVE 'CDC_RESTORE_TEST' TO 'C:\SQLServer\Data\CDC_Restore_test1.mdf'
    , MOVE 'CDC_RESTORE_TEST_LOG' TO 'C:\SQLServer\Logs\CDC_Restore_test1.ldf';
RESTORE LOG CDC_RESTORE_TEST1 FROM DISK='C:\SQLServer\CDC_RESTORE_TEST_LOG.bak'
WITH RECOVERY
    , MOVE 'CDC_RESTORE_TEST' TO 'C:\SQLServer\Data\CDC_Restore_test1.mdf'
    , MOVE 'CDC_RESTORE_TEST_LOG' TO 'C:\SQLServer\Logs\CDC_Restore_test1.ldf';
GO
USE CDC_RESTORE_TEST;
GO
EXEC sys.sp_cdc_get_ddl_history 'dbo_Test';


Updating column in underlying table causes massive transaction log growth

One idea might be to create a second view that doesn't persist 2.5 million copies of the same value.

CREATE VIEW dbo.innerview
WITH SCHEMABINDING
AS
  SELECT b.Id, SmallId = s.Id
   FROM dbo.Big AS b
   INNER JOIN dbo.Small AS s
   ON b.SmallId = s.Id;
GO
CREATE UNIQUE CLUSTERED INDEX PK_View On innerview([Id]);
GO

Then you can create a view around this, without an index:

ALTER VIEW dbo.View
WITH SCHEMABINDING
AS
  SELECT v.Id, v.SmallId, s.description AS SmallDescription
    FROM dbo.innerview AS v
    INNER JOIN dbo.Small AS s
    ON v.SmallID = s.Id;
GO

Now you can change the small description and it won't affect all those rows, and the queries against dbo.View should exhibit about the same performance as your current queries against the indexed version. Your indexed view shouldn't contain many, many copies of a value that is likely to change.

Of course this view doesn't need to be indexed at all IMHO. A lot of people think indexing a view magically speeds up the query; in a lot of cases it actually just slows down your overall workload. What is the indexed view gaining you that the clustered index on the source table (Big) doesn't already do? Transaction log madness aside, have you compared queries against the indexed view with a simple query like:

SELECT
  Id, -- please be specific, both tables have a column named Id
  b.SmallId, s.Description AS SmallDescription
FROM dbo.Big AS b
INNER JOIN dbo.Small AS s
    ON b.SmallId = s.Id;

?


I assume you're talking about Relational Algebra here.

A SELECT operation returns whole tuples (rows) based on some predicate.  The predicate is defined in the SQL statement by the WHERE clause.

The PROJECT algebra operation is where you select a subset of attributes from each tuple.  The PROJECT operation is defined in the SQL statement by the columns you specify.  In SQL, the SELECT and PROJECT operations are combined into the SELECT clause.

A JOIN operation is used to combine two sets of tuples by a common and related attribute.  In this example, you are using an INNER JOIN defined in the FROM and WHERE clause.  I generally prefer to declare my joins solely in the FROM clause, but it works either way.

So, the SELECT operation is:

WHERE d.kennel_name = "Holiday Kennels"

The PROJECT operation is:

SELECT d.name, k.city

And the JOIN operation is:

FROM DOG d, KENNEL k
...
AND d.kennel_name = k.name;


How to check if restored database is the same as original

You can use SQL Developer, Tools - Database Diff, this would give you an idea. There is a tutorial here http://www.oracle.com/technetwork/issue-archive/2012/12-sep/o52sqldev-1735911.html. Only use the compare/diff section.


Why an index scan?

It's likely that SQL Server estimated a lower cost for scanning the data (only 1.7MM rows, so a relatively small table) as compared to a loop-seek approach.

Scanning the t_werbeflaechentermine table will process all 1,708,658 rows, and your plan shows that this performs 7,347 logical reads.

A loop-seek is estimated to perform ~70K seeks (the cardinality estimate for the outer side of the loop). If we assume a seek is a binary search, we might therefore estimate that this will have a complexity of 70109.4 * LOG(1708658,2), or 1,451,575.

This is slightly lower than the 1,708,658 rows processed by the scan, but the number of logical reads will be much higher since each of the (estimated) ~70K seeks will perform several logical reads, yielding many more logical reads then the scan that was chosen. This might be the reason that a loop-seek plan yields a higher estimated cost and was not selected.

Viewing the plan without scans

If you'd like to compare this plan to the execution plan that uses a loop-seek is used for both joins, you can try adding the OPTION (LOOP JOIN) query hint to your query so that both joins use a loop join. It would be informative to post this actual execution plan for comparison.

Why no partition elimination?

I believe that partition elimination does not occur for t_werbeflaechentermine because SQL Server does not have a hash join algorithm that performs partition elimination based upon the observed partitions on the build side of a hash join. This would be a nice optimization in some cases, but to my knowledge is not available in the current optimizer: Partition elimination is available for the inner side of a loop join, but not for the probe side of a hash join (unless the query contains an explicit predicate on the partition column).

For further reading, SQL Server does have the concept of a collocated join, in which the hash join is applied independently for each partition of two tables that are partitioned the same way. However, this optimization is only available 2-way joins, so your query does not qualify. Paul White describes this and other partitioned table join considerations in much more detail in Improving Partitioned Table Join Performance.


ROLLBACK make data in INSERTED table is removed in AFTER INSERT TRIGGER

Your row is not being inserted in either case.

For after insert trigger rollback is part of the transaction. See this Q&A for details.

SQL Server - After Insert/ For Insert - Rollback

Quote from the above Q&A.

  You're inserting one (or multiple) row into your table. Then - still
  inside the transaction - the AFTER INSERT trigger runs and checks
  certain conditions - typically using the Inserted pseudo table
  available inside the trigger, which contains the rows that have been
  inserted.

  If you call ROLLBACK TRANSACTION in your trigger, then yes - your
  transaction, with everything it's been doing, is rolled back and it's
  as if that INSERT never happened - nothing shows up in your database
  table.

For instead of trigger the id value of 1 you are seeing is not from the table. It is from the code within your trigger.  This value of 1 is coming from:

SELECT * FROM inserted

Run this code and you will not get a record back.

ALTER TRIGGER [dbo].[insert_a]
ON [dbo].[a] INSTEAD OF INSERT
AS
BEGIN

DECLARE @id INT
SELECT @id=id FROM inserted
IF @id=1
 BEGIN
  RAISERROR('1',12,1)
  ROLLBACK
 END

SELECT * FROM inserted
END
GO
INSERT INTO a VALUES(1);
GO
SELECT * FROM dbo.a;
GO


How to re-declare T-SQL variables if it already exist?

You cannot redeclare variables, nor can you test to see if they have been declared (at least not that I have ever been able to find). BUT, you shouldn't need to do this anyway. If you know the variable names ahead of time, just declare them all at the beginning of the process. Then, use them throughout the script.

For example:

--------------------------------
-- BEGIN script header
--------------------------------
DECLARE @SomeVariable1 INT,
        @SomeVariable2 VARCHAR(50),
        ....;
        SomeVariableN DATETIME;
--------------------------------
-- END script header
--------------------------------

And then add 1 or more script blocks that use these variables:

---- Script Block 1 ---
EXEC dbo.MyProc @InputParam = 1, @OutputParam = @SomeVariable1 OUTPUT;
-----------------------

---- Script Block 2 ---
EXEC dbo.AnotherProc @InputParam = @SomeVariable1;
-----------------------

---- Script Block 3 ---
SET @SomeVariable2 = NULL; -- reset value if you don't want to carry over prior value
..do something here...
-----------------------


Best approach to design a database model for this situation? Clients and records

You should organize the data according to its cardinality.

If you have one header and one footer per client, then these columns can be part of your client table.  Since there are clearly multiple body records per client, these should be kept in another table.

If you can have multiple records per client, then your "with hierarchy" model is not bad, although it could be simplified to collapse the Header and Footer into Record since there is only one header and one footer per record.

Your "without hierarchy" model is less desirable.  If you have multiple records per client it won't tell you which headers/bodies/footers go together.  If you have only one record per client, then having separate tables for header and footer is overkill for the same reason as I noted above for the "with hierarchy" model.

This is what I would recommend, depending on how many records each customer can have:


Where/how do I save extra data per column?

I have a table, and for each field I want to know some sort of state. Lets say 'unchecked', 'ok', 'faulty' etc. I could just double the columns, add a new 'status-field' per column, but that feels wrong.

To me, this feels right ;-) In order to prevent typos, I think that this could (and should) be implemented as a foreign key to another table with just the possible states.

  A different example would be to safe how the data currently came to be, for instance calculated, automatically retrieved of manually entered.

If, necessary, I'd again use foreign keys to other tables for this. These tables would cover the available methods or instruments.

Update 1
Quite often, the number of different states (unchecked, faulty, ok, etc.) is rather small. A foreign key of the type TINYINT would probably suffice and minimizes the memory overhead.


Performance degradation after rebuilding indexes

It's because when you rebuild indexes, you also necessarily rebuild the statistics on each index as it happens.  That means that SQL Server rebuilds internal "tables" of data about the distribution of data in your indexes themselves.  The distribution of data indicated by your statistics govern how the cost-based query optimizer chooses to create plans for your queries.

In essence, it means that SQL Server is probably executing your queries using a different plan than it did before because the distribution of your data changed.

There's no simple fix to this.  It involves real detective work.  Start with SQL Server Profiler and get an idea of what queries are actually running in this job.  Analyze each query plan in detail and find out what parts of the query are taking the most time, and adjust your query, indexing scheme, or even your schema accordingly.


Use Snapshot or Read Committed Snapshot isolation for your reads.

  When a row versioning-based isolation level is enabled, the Database
  Engine maintains versions of each row that is modified. Applications
  can specify that a transaction use the row versions to view data as it
  existed at the start of the transaction or query instead of protecting
  all reads with locks. By using row versioning, the chance that a read
  operation will block other transactions is greatly reduced.

Alternatively:

Read less. If the nature of the application and data is such that you can tolerate a 10-20 seconds delay, cache the response and returned the cached result until your next successful read.
Use replication to distribute data to a set of read-only slaves.
Fix your application. Timeout implies long running transaction; occasionally necessary but typically a design flaw.


It worked after editing the correct pg-hba.conf file. Well, earlier our customer asked us to move postgresql data to other drive partition. So there are two copies of conf files. One is inside postgresql program folder and the other is in data folder. To achieve what I want I needed to edit the one inside data folder.


Is an explicit KEY redundant if it is covered by a UNIQUE KEY?

Yes, you can drop it because baz and baz_3 have identical leading columns. This scenario is known as having redundant indexes.

However, be careful that the key baz is not being used to support a foreign key constraint if the table is InnoDB. See my post Do I have duplicate key indexes?

If the table is MyISAM, go ahead and drop it with confidence. The MySQL Query Optimizer will use the UNIQUE KEY baz_3 for query optimization in its execution plans.

If you want to locate all tables with redundant indexes, see this post : MySql - find redundant indexes


ODBC Curtime() after migration from 2000 to 2012 omits datepart

The short answer is there is no way to fix this without changing the app.

That {fn curtime() } is and ODBC Scalar function that SQL Server does interpret, but really shouldn't be used natively.

Use GetDate() instead.

(I suppose you could use {fn curdate() } combined with {fn curtime() } )

I haven't got a SQL 2000 install anymore (that was 17 years ago!), so can't test what the {fn curtime() } was like then but I would've assumed that it only ever returned the time. Maybe not, but it doesn't make much difference now - you're a bit stuck.

The only option is to change the application. The justification shouldn't be too hard - an app that only works on SQL Server 2000 is not really acceptable, that has been out of support for a long while - it's a 17 year old system.

This earlier post is similar and the answer is a good one and gives some more information.

This is not relevant really but I'll add it (you can just ignore this bit):

curtime() is a builtin function in MySql. Your case is definitely ODBC syntax, but it's similar to the MySql one so thought I'd add it as trivia.


Moved database to new server, not sure about NT AUTHORITY\* users

These users are built in accounts to Windows and they're usually hidden from view. If you know that your application isn't using those users then you can delete them.

However, it sounds like you're moving a database that was used in ASP.Net applications. ASP.Net does use both those accounts under the hood. NT AUTHORITY\IUSR for anonymous access and
NT AUTHORITY\NETWORK SERVICE is used in ASP.Net application pools. If that's the case, make sure that you no longer need to access the database you moved with ASP.Net and you'll be golden.

http://www.iis.net/learn/get-started/planning-for-security/understanding-built-in-user-and-group-accounts-in-iis

https://msdn.microsoft.com/en-us/library/ff647402.aspx


It is not a good idea to put the category_name in the product_information. This way you will 'break' the normalization. Instead of storing the category_name 15 times you store the same name multiple times. You speak about gaining on joining but this will give you, taken the limited number of rows in category_details, not much advantage. The whole category_details table will be in memory and you only need to join if you need the category_name.

You say that you have about 15 categories. I suppose that you have (many) more product. So this makes it a one-to-many relation which is the second case in the answer to MySQL: multiple tables or one table with many columns? that you mentioned in a comment. There it mentions that in the case of a one-to-many relation you must put the information in separate tables.

Also look at the application level. If somebody has to enter each time a category_name you might end up with the same name but spelled different. This will make it hard to search on it.


Understanding two NICs requirement for SQL fail over cluster

First, there's no SQL Server 2013, but the good news is that the version number of SQL Server doesn't matter. It's more based on Windows failover cluster networking, which is explained here in a 3-part series by the Microsoft folks:

http://blogs.technet.com/b/askcore/archive/2010/02/12/windows-server-2008-failover-clusters-networking-part-1.aspx

And then additional info in part 4:

http://blogs.technet.com/b/askcore/archive/2010/04/15/windows-server-2008-failover-clusters-networking-part-4.aspx

Short story - you need some network redundancy, but you don't have to get it with multiple IP addresses. Instead, you get it with network teaming software.


This particular example can be simpler.
You can TRUNCATE multiple tables at once. Aggregate all tablenames and execute a single statement:

CREATE OR REPLACE FUNCTION truncate_tables(_username text)
  RETURNS void AS
$func$
BEGIN
   EXECUTE (
      SELECT 'TRUNCATE TABLE '
             || string_agg(quote_ident(t.tablename), ', ')
             || ' CASCADE;'
      FROM   pg_tables t
      WHERE  t.tableowner = _username
      AND    t.schemaname = 'public'
   );
END;
$func$ LANGUAGE plpgsql;

Call:

SELECT truncate_tables('postgres');

string_agg() requires PostgreSQL 9.0 or later.
In 8.4 you can substitute:

array-to_string(array_agg(quote_ident(t.tablename)), ', ')

For v8.3 you would write your own aggregate function - rather simple, yet not simpler than the looping solution any more.

Performance degrades when deleting or truncating many tables at once in PostgreSQL 9.1. A fix for this in the upcoming version 9.2. I quote the release notes:

  Improve performance of checkpointer's fsync-request queue when many
  tables are being dropped or truncated (Tom Lane)

Related thread at pgsql-hackers.

@Craig's related answer at SO helped me discover this.


Galera Cluster - 3rd Quoroum Only Node - No Replication - Possible?HowTo?

The GALERA ARBITRATOR is exactly what I needed.

The recommended deployment of Galera Cluster is that you use a minimum of three instances. Three nodes, three datacenters and so on.

In the event that the expense of adding resources, such as a third datacenter, is too costly, you can use Galera Arbitrator. Galera Arbitrator is a member of the cluster that participates in voting, but not in the actual replication."

Galera Arbitrator serves two purposes:

When you have an even number of nodes, it functions as an odd node, to avoid split-brain situations.
It can request a consistent application state snapshot, for use in making backups.

HowTo:

http://galeracluster.com/documentation-webpages/arbitrator.html


Where can I get SQL Server Transaction Begin Time from?

You can get the id of the current transaction from sys.dm_tran_current_transaction.
Then you can get the transaction_begin_time column from sys.dm_tran_active_transactions.
So, use one of the following T-SQL statements:
SELECT transaction_begin_time = dtat.transaction_begin_time
FROM sys.dm_tran_current_transaction dtct
    INNER JOIN sys.dm_tran_active_transactions dtat
        ON dtct.transaction_id = dtat.transaction_id;

or
SELECT transaction_begin_time = dtat.transaction_begin_time
FROM sys.dm_tran_active_transactions dtat
WHERE dtat.transaction_id = CURRENT_TRANSACTION_ID();

CURRENT_TRANSACTION_ID() returns the value of the transaction_id column from the sys.dm_tran_current_transaction.


MySQL wrong use of index when a date interval is big

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 = '')


Mongodb: Does the stats command show me the capped size setting?

If you connect to the proper DB and execute a db..stats() in the Mongo shell you will see "capped" : true third from the bottom of the output just after index sizes.

To figure out a collections cap size, you could use the system namespace.

e.g. db.system.namespaces.find({name : "."}, {"options.size":1})


Tool for migrate SQL Server 2005 to SQL 2017

You could use the DMA, Data Migration Assistant DMA Microsoft link
You can do both an assessment of the migration, to have a preview of any features that could cause you issues after migration, and it can also do the migration for you.

Personally I only use the DMA to do the assessment and do the migration myself by using either backup/restore or incase there's a no downtime policy, some kind of mirroring.

I do suggest you make some kind of checklist of everything you have to do, and do a trial migration beforehand so there's less pressure when going to production.
Something like this could be useful to you : Migration steps and checklist


First things first: MS Access was not designed for multi-user access. Every version of Access I've used had a disturbing habit of corrupting tables at a vastly increased frequency if there were >1 users using it.

If the two users are connected to the Internet all the time, I'd recommend shifting your table storage to SQL Server and having the users connect to that (use a VPN or some other form of security! If they're on a company LAN it's even better, you shouldn't need a VPN then). It's a fairly straightforward process to convert to SQL Server.

The users will still use the Access front end, but instead of having the tables stored inside the .accdb file and having to merge them, the Access tables are converted to linked tables to the SQL Server tables.

This is possibly a bit more up-front work, but it'll save you hassle down the road (how often do you need to merge? who's going to do the merging?). Also, if the application ever gets more widely used, you can easily build another front end (in C#, Java, ASP.NET, whatever) and connect it to your SQL Server back end.


AWS DMS creates tables with no auto_increment

If DMS is told to create the table on the destination (the default option) it only creates the most basic structure needed to move the raw data over. So you shouldn't trust that other metadata like default values are moved over either.

You can either migrate all your changes after moving the data, or create the schema first on your destination and tell DMS not to create tables.

Just keep in mind that copying data with indexes at the same time is much slower, so if you have a lot of data you can create them afterwards or simply just wait longer for the sync.


How to read files using the OPENDATASOURCE method?

The Jet.OLEDB.4.0 is data access library for Excel/Access so the code is trying to read data from CSV file into a temporary table.

If you change the source to OPENDATASOURCE('Microsoft.Jet.OLEDB.4.0', 'Data Source=C:\ETU;Extended Properties="Text;HDR=YES;"')...[etu#csv] you will open c:\etu\etu.csv so changing the last parameter to ...EV20150126000001#csv will open your file. See:http://blog.waynesheffield.com/wayne/code-library/ad-hoc-querying/ As this is a CSV file I would prefer to use openrowset bulk as the Jet.OLEDB drivers are "interesting" on 64bit platforms:  see: http://bradsruminations.blogspot.com/2011/01/ for further ideas.


Peer review database design for various reports

I think you are doing well going for the 4th normal form and exploding your data. Now, as you found out, you need to de-normalise to support for historical data support: if the review will always account for a particular department's score whatever the current department of the reviewee_id is, then yes, you definitely need to add a department at the review table and make sure you know what/why it is there for.

Push the logic further: what if there is a 4th and 5th category added in a year? How do you calculate past and future averages then? On 3 or 5 categories? Then another year later, the first category gets removed? Will you delete all scores for that category, or change yet again the average's calculation based on the number of "category" at the time of the "date" of the "score"?

Sometime, too much normalisation is not the best course of action. You have a good core here. Time to custom-fit now. Take a step back, plan for the future, ask the end-users to make sure you support all their foreseeable needs, and look at the impacts of adding/removing/updating departments, employees and categories on historical reporting.

As for reporting, if you are using powerful data tools like IBM Cognos, it will be easy to generate data cubes, then let managers do their own reports. If you are using tools like Microsoft Excel with "external data", then the easiest course of action would be to pre-create Views or Stored Procedures that crunch up the data. The caveat here is that you will have to create a new View/SP for every manager that comes up with a new request/idea/variant.


How to avoid Functions from a Performance Stand-point in Azure SQL Database?

You can rewrite it as an inline TVF returning a single column and row and CROSS APPLY it to get the benefits of inlining now (parallelism, no overhead of switching execution contexts, holistic query costing and optimisation) without having to wait for the work done on inlining of Scalar UDFs to get released.

So your function definition would be

CREATE FUNCTION [dbo].[fnGetLocalDate] (@DateToConvert DATETIMEOFFSET = NULL)
RETURNS TABLE
AS
    RETURN
      SELECT CONVERT(DATETIMEOFFSET, @DateToConvert AT TIME ZONE
                                     (SELECT Value
                                      FROM   LookUp.Config
                                      WHERE  Property = 'TimeZone')) AS
                    LocalDate

With example usage

SELECT o.name, L.LocalDate
FROM sys.objects o
CROSS APPLY [dbo].[fnGetLocalDate](o.modify_date) AS L

You don't really need the CASE as it will return NULL on NULL input anyway and may get better plans without it


Redis doesn't come out of the box with the ability to search/index inside hashes, but it gives you all the tools you need to accomplish your task. Your challenge could be solved, for example, by keeping a key in the database called last_user_registered or similar, and SETting it every time that a user registers to that user's ID. When you want to fetch the last user that had registered at any given moment - just GET it, get it?

But you (and Redis!) can do much better: for example, you could use a Redis list and LPUSH/RPOP members to keep scores with the users who last joined. Or, you could use a sorted set where each member is a user id and the score is the timestamp... ah, the possibilities are endless and you're only limited by your imagination (and time, resources, etc...) ;)

I think this is exactly why Redis 'aficionados' lovingly refer to it as a database building tool rather than as just a database - you can actually make it do whatever you want and if what you want isn't in Redis already, there's always Lua that you can use to extend with or even developing your own fork and putting in a pull request to the github.


How do I change the config path for mysqld?

You need to use the --defaults-file option as follows

mysqld --defaults-file="E:\\db\\my.ini"

or

mysqld --defaults-file="E:/db/my.ini"

If you are running MySQL 5.6 already on the Windows machine and you are trying to run an additional MySQL 5.6 instance, make sure you change the following under the [mysqld] group header in your my.ini

the port number (other than 3306)
datadir (such as E:\db)

perhaps like

[mysqld]
port = 3307
datadir = "E:/db"

Give it a Try !!!


Well, suppose you have a command to execute your backup like this

BACKUP DATABASE [naass]
TO  DISK = N'C:\program files (x86)\Microsoft SQL Server\MSSQL.3\MSSQL\Backup\Naass.bak''
WITH NOFORMAT, INIT,
NAME = N'naas-Complete Database Backup',
SKIP, NOREWIND, NOUNLOAD,  STATS = 10

this will fail if the process that executes this command doesn't have the permissions to write in the mentioned folder. And Windows 7 (for very good security reasons) keeps the C:\program files folders and its subfolders write locked also to an administrator account.

You could mess with the default permissions and give to your folder above write permissions for your account but of course this is not the recommended way.
Much better, simply change the script to work on a different directory on your disks.

BACKUP DATABASE [naass]
TO  DISK = N'D:\BackupSQL\naass.bak'
WITH NOFORMAT, INIT,
NAME = N'naas-Complete Database Backup'
SKIP, NOREWIND, NOUNLOAD,  STATS = 10

Of course this new directory should have the correct permissions.


There are some things you want to think about based on the nature of your items and how you store and track them.

Are your items discrete or are they a commodity?  The way you would track television sets, each of which has its own distinct serial number is different from how you might track boxes of nails.  If your SKUs are discrete you don't have a "quantity" field, instead you track individual items with an intersection table (as suggested by Joe).  Otherwise whether you need an intersection table depends on how many different SKUs can be on a shelf (see below).

Is it important to track inventory movements?  Do you need to see stock deliveries and shipments?  If so you might want to take a kind of double entry accounting approach, treating shelves like GL numbers.  If not, a simpler quantity per shelf may be sufficient.

Can multiple SKUs share a shelf?  If so, you need an intersection table.  If not, you can get by with a foreign key from Shelf to Item.

Does every bin have at least one shelf?  Your application (queries) will be much simpler if every bin has at least one shelf, such that you don't need to track inventory which is in a bin but has no applicable shelf information.

EDIT:

It's important to get the terminology clear. When you say "bin" you mean a box without a lid that sits on a shelf. In a lot of inventory situations, a "bin" is a space on a rack where you could put a skid or one or more shelves.

As long as we're clear, you want a hierarchy of SHELVES each of which has many BINS each of which can have many BIN_CONTENTS each of which is an intersection between BIN and ITEM and which has a quantity. Don't store quantity on your ITEM table, store it on the intersection table, that way you can have multiple bins of the same item, or even many different kinds of items, each in their own distinct bins or even multiple different items mixed within a bin, all on the same shelf.

Consider the following ERD:


Oracle-xe on Ubuntu 18.04 - Installation and impdp

Ubuntu is not a supported distribution for running Oracle databases. Just use a supported distribution.

Operating System Requirements

The database should be configured and created with /etc/init.d/oracle-xe configure. If you do not have a working databases after that, that is an environmental issue: use a supported environment.
I had to check and run createdb.sh.  It created an incomplete database that gave the same error. Instead of it use the above method for creating the database. If it does not work: use a supported environment.

At this point you can start over and try to get the official steps working by folowing random blog posts and forum comments like above. Maybe it will work if you do not use createdb.sh. Or you can save a lot of time by using a supported environment.


select  count(*)
    from  symbol_details
    where  tsetmc_id = 41974758296041288
      and  created_at >  '2020-06-20'
      and  created_at <  '2020-06-31';

Optimal index, in this order:
INDEX(tsetmc_id, created_at)

When you started with the range value, it could not go past that to the 'id'.  The simple rule is:  Put the = columns first in the index definition.  More:  http://mysql.rjweb.org/doc.php/index_cookbook_mysql
Further notes

The optimizer may decide that a table scan is faster than bothering with the INDEX.  This usually happens when it appears that more than about 20% of the table needs to be touched.  (Using the index implies bouncing back and forth between the index's BTree and the data's BTree.)  If it shuns the index, don't worry; it is probably a wise decision.

The order of the columns in the index is critical when a "range" is involved.  The Optimizer will stop considering columns once it gets past the "range".


Can Oracle SE2 RAC works with only a single instance? Is it recommended?

You can just simply shutdown and disable one of the instances.

RAC One Node is optional in this case. If you want to use features like online database instance migration or rolling patching, you may use it, this is a simple configuration change, there is nothing to be converted.

Keep in mind, while RAC and RAC One Node are available without extra cost with SE2 in 12c, starting with 19c, these options are not available anymore in Standard Edition (SE2).


multiple groupings in select clause in postgres

You were nearly there, but you need to group by the year, not the timestamp value:

select item_id, extract(year from created_at), count(*)
from orders
where ....
group by item_id, extract(year from created_at);


Odd behavior of between operator when updating

The SQL statement you've listed, run by itself, would not alter the FLAG column.

I've put together a SQLFiddle that shows what that statement does on its own, and you'll see that FLAG isn't changed by it.

This is why so many have assumed there's a trigger, or another statement, involved in the process.

You've said that there's no trigger on SOME_TABLE. If so, then some other statement is making the change.

The best way to find what that statement is would be to use SQL Profiler (or extended events) to track what's being executed when your job runs. If some other statement is being triggered by the relevant job, this should show you what that is. If you search this site, you'll find information on these options.

If you need to prove to yourself whether or not the UPDATE statement in and of itself is causing the change to FLAG, I'd recommend the following:

During a maintenance window:

Begin a transaction
Run the UPDATE statement inside that transaction
Check the results of the statement on SOME_TABLE (again from within the transaction)
Roll back the transaction.

If there's no transaction, and that SQL Statement (not the whole job, just that statement) does change the value of FLAG, then either this is tied to a bug in SQL Server (not impossible, but somewhat unlikely), or there's something seriously wrong with your SQL Server installation. Or, of course, there really is a trigger - I'm assuming not, based on what you've said, but that's a much more likely solution than a bug or a messed up SQL Server instance.


Where should I store a custom ".my.cnf" file?

To keep the things simple place all custom server settings into the /etc/my.cnf file. Some user-specific options can be stored in the ~/.my.cnf where ~/ means "the homedir for the given system user". Also note the leading dot in the user-specific file name. That file is used for command-line client utilities like mysql or mysqldump only, not for server. Therefore the [mysqld] section can be completely omitted in the user-specific config file.


can I terminate it without causing any problem on the DB

You already caused the problem, simply by running repair_allow_data_loss. Allow data loss is really the very very very last resort, after all other options have been exhausted. The appropriate action is to restore from a correct backup and re-apply missed transactions.

Can DBCC take 27 hours? Yes, I know of DBCC commands that took well over 8 days to complete. You can investigate and see if DBCC is blocked (and Shark give you the right approach for that) and if you're lucky it is blocked and you can unblock it and let it finish. But if is making progress though there is not much you can do but wait it out

If you terminate it, will the database be any worse that it already is? In theory no, because DBCC, like any other operation, is transitionally consistent and any in-flight operation will be rolled back. However, in practice, the very fact that you're running DBCC repair means you're dealing with corrupted structures, and in such case there is always a risk.

  I did take backup before I run it.

Validate the backup by restoring it on a different machine or as a different DB on the same instance. If the backup is correct, then you're in a much better place because you can always return to this backup. Also, make sure no new business transactions occurred after you took the backup.


User cannot select from table but their permission appear to show that they should be able to

I would check if the users are part of the db_denydatareader role, check for deny permissions on the tables schema, and also for a deny permission on the public role. I would suspect these are the most likely culprits.


How to select attribute from two indexes, both pointing at the same table?

You need to joins to the pupils table twice: Once for IDPupil1 and once for IDPupil2:

SELECT
    t.ID AS ID, t.IDPupil1, t.IDPupil2, pupils1.Name AS Name1,
    pupils1.Forename AS Forename1, pupils2.Name AS Name2,
    pupils2.Forename2 AS Forename
FROM teams t
LEFT JOIN pupils pupils1
ON t.IDPupil1 = pupils1.ID
LEFT JOIN pupils pupils2
AND t.IDPupils2 = pupils2.ID


set -x equivalent for the Postgres cli/console/prompt/repl


This is pretty clumsy but will perhaps give you some thoughts. The idea is to number the rows and pick from there using modulo:

select x from (
    select @n := @n + 1 as n, x
    from t, ( select @n := 0 ) as dummy
) as u
where mod(n,6) between 0 and 1 or @n = n

Edit: As observed by @hassansin @n will have the value of the last n. Therefore the union of the last n can be replaced with the predicate @n = n


MySQL: does Index Merge Sort-Union access algorithm use temporary table?

I've performed an experiment: rewrote the query from using 9 ORs in a single query to a UNION DISTINCT of 9 subqueries. As expected, EXPLAIN shows for a rewritten query in the last row: select_type = "UNION RESULT", Extra = "Using temporary". Such change was done to have a query that surely uses temporary table to compare with.

Now, when I launched an app using this query in its original form (9 ORs) - each check Created_tmp_tables increased only by 1 (from the manual: invocation of the SHOW STATUS increments the global Created_tmp_tables value by 1).

When I launched the app using rewritten query Created_tmp_tables grows very fast.

I think this experiment is enough to show that temporary tables are not being created in the case of index_merge Sort-Union.

Conclusion: no, it does not.


"The transaction log for database 'tempdb' is full due to 'ACTIVE_TRANSACTION'." after adding indexes to temp tables

An index create on a temp table will use space in the tempdb transaction log while the index is being built.  So yes, the index could have caused the issue in the general sense that it added enough transactions to the log file that it ran out of space.

Transaction Log Disk Space for Index Operations


Using dynamic sql inside Oracle stored procedure

The only reason why I might do that is if I needed to address an object that might not exist at compile time -- for example if I had code to create new external tables as required.

As this implies, the dynamic SQL statement is not parsed when the PL/SQL compiles, so you have no idea whether it is correct or not, and dependencies are not stored in the database.


I will be crazy. Only 20k line data and 10 online client how can be slow like fax :S
My system uses SSD disk.
SHOW GLOBAL STATUS pastebin.com/gLvTkZ6s
SHOW GLOBAL VARIABLES pastebin.com/UcJkxRKb
SHOW FULL PROCESSLIST pastebin.com/AaWGFurJ
SHOW ENGINE INNODB STATUS pastebin.com/aQYLW1FH
SELECT name, count FROM information_schema.innodb_metrics ORDER BY name pastebin.com/Wdz1WhU6My
My system generates query like : SELECT count(id) FROM gr_msgs WHERE gid = '1-573' AND id > '5989' gr_msgs


How to set ADR Base directory correctly on OracleDB on Linux?

Just set:

diagnostic_dest=/u01/app/oracle

or

diagnostic_dest=/volumeX/app/oracle

The error message you receive indicates that the value for this parameter is currently stored in a format that the database can not interpret. When this happens, the database will search for this directory in $ORACLE_HOME/dbs as seen below the ORA-48140 line.

Or you can just simply delete this line, and the parameter value will default to $ORACLE_BASE.


MySQL Innodb: What is the difference between running 'mysqlcheck --optimize --all-database' compared to running 'optimize table' in the mysql client?

mysqlcheck just runs a bunch of 'optimize table' SQL statements for you, so functionally they do the same thing.

Note that 'optimize table' for innodb just maps to an 'ALTER TABLE foo engine = innodb' statement.  This rebuilds the entire table.  Depending on the size of your table this may take a very long time, during which the table is locked and unavailable.


Why is it not possible to "SELECT" Data as parameters for certain clauses like LIMIT?

this is the way LIMIT can be done in MySQL using variable parameters:

SET @skip=1; SET @numrows=5;
PREPARE STMT FROM 'SELECT * FROM tbl LIMIT ?, ?';
EXECUTE STMT USING @skip, @numrows;

In the place where it says SET @skip=1, One can also write SET @skip=(SELECT 1).