How to solve error "ORA:01-006 :bind variable does not exist" when working with bind variables?

You can completely avoid dynamic SQL (you also should never name PL/SQL variables the same as table columns):
create or replace procedure test_bind_variable(res         out sys_refcursor,
                                               p_item_number varchar2,
                                               p_item_name   varchar2,
                                               p_item_col    varchar2,
                                               p_item_pol    varchar2) is

  qry varchar2(8000);

begin

  open res for select * from test_table where
    (p_item_number is null or p_item_number = item_number) and
    (p_item_name is null or p_item_name = item_name) and
    (p_item_col is null or p_item_col = item_col) and
    (p_item_pol is null or p_item_pol = item_pol)

end;

PS. Make sure you take the operator precedence into account, unlike me.


Is using timestamp as primary key good idea - mysql/mariadb?

IMO it's bad because of several reasons. First, blindly assigning a generated p.k. for every relation is bad practice. At least try to identify what candidate keys there are in each relation. A surregate p.k. may be the smartest choice in some situations, but it is definitely not so in all possible situations. Good properties for candidate keys are:

Unique
Irreducible
Non-complex
Stable
Familiar

Often there is a conflict between these criteria and you have to sacrifice one for another.

Second, if the best choice is a surrogate identifier I'll say that timestamp is a poor choice. Especially in MySQL/MariaDB before (MySQL) 5.6.4 where the resolution is seconds. Using a timestamp as a p.k in these DBMS means that you cannot insert more than one row / second (as long as the timestamp should reflect the time of insert).


Postgres foreign data wrappers weird join behavior

You never ANALYZEd the foreign tables, so the estimates are way off, and PostgreSQL chooses a bad execution plan.

ANALYZE tds, dq_infos;

should improve the situation.

Different from regular tables, foreign tables are not handled by autoanalyze.


Best approach to maintain a single set of DDLs and DMLs for multiple database type

The best way to support multiple database types is to optimize the sql statements for each on of them. Otherwise you will one day end up with huge performance problems on one or the other database types.

Additionally to that it's unfair for your customers to tell them "I support Oracle, DB2, MSSQL, etc.) and not optimizing the SQL for each one of them. Why? Because your customer pays a lot for the database and your application does not use the advantages and features the database provides.


Do I need to `mysql_upgrade --verbose -u root -p` each time when `source backup.sql`?

mysql_upgrade may or may not be needed.  It is useful after you upgrade the server version.

In some cases (I don't remember 5.1 to 5.5), there were changes to datatypes that necessitated "fixing" the schema and the data in table(s) affected by such.

If you are dumping from the same major version that you are loading into, there should be no need for running mysql_upgrade.

If you have clues of what it changed, we can discuss further.

(Note:  Even 5.5 is getting rather "old".  I hope you will be moving forward soon.)

As I understand it, mysql_upgrade leaves a file behind after it runs.  The file tells it "I have upgraded to X.YY.ZZ, so I don't need to run again".

Apparently, that file (on your machine) says that it has upgrated to 5.5, so it should not be needed again.

Do you have any symptoms of things not working 'right'?  If so, let's approach the question from there.


Can we export Selected Row with Subquery using data pump

Can we use subquery in datapump?

Yes we can. The following example shows how to do it.

I have created a parameter file to get this job done. In my test case I have two tables namely tbl1 and tbl2 under HR schema. You may change the name of the schema as your requirements.

[oracle@testsrv Desktop]$ cat query.par
Directory=user_dir
DUMPFILE=select_row.dmp
logfile=rows.log
tables=hr.tbl1, hr.tbl2
Query=hr.tbl1:"where name IN (select name from hr.tbl2 where name like '%T%')"

Then I have used the following expdp command to accomplish the task.

[oracle@testsrv Desktop]$ expdp system/oracle parfile=query.par

Export: Release 11.2.0.4.0 - Production on Mon Mar 21 15:59:18 2016

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Data Mining
and Real Application Testing options
Starting "SYSTEM"."SYS_EXPORT_TABLE_01":  system/password parfile=query.par
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 128 KB
Processing object type TABLE_EXPORT/TABLE/TABLE
. . exported "HR"."TBL1"                                     5 KB       0 rows
. . exported "HR"."TBL2"                                 5.007 KB       1 rows
Master table "SYSTEM"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYSTEM.SYS_EXPORT_TABLE_01 is:
  /home/oracle/Desktop/select_row.dmp
Job "SYSTEM"."SYS_EXPORT_TABLE_01" successfully completed at Mon Mar 21 16:00:05 2016 elapsed 0 00:00:41


Is it possible in SQL Server to change default "order by" direction from asc to desc?

No, the default sort order is always ASC.


Does the SQL/JSON Path specification allow accessing the property of a filter expression's result

I do not know about the standard, but it works just fine in 19c.

Script:

[oracle@o73 ~]$ cat x.sql
with d as (select
'{
  "key1": {"id":1000, "price": 42},
  "key2": {"id":2000, "price": 3.14},
  "key3": {"id":3000, "price": 2.7}
}' as c1
from dual
)
select json_value(c1, '$.* ? (@.id == 1000).price') x
from d;

with d as (select
'{
  "key1": {"id":1000, "price": 42},
  "key2": {"id":2000, "price": 3.14},
  "key3": {"id":3000, "price": 2.7}
}' as c1
from dual
)
select json_value(json_query(c1, '$.* ? (@.id == 1000)'), '$.price') x
from d;

exit
[oracle@o73 ~]$

18c (18.10):

[oracle@o73 ~]$ . oraenv
ORACLE_SID = [oracle] ? RYMIN18
The Oracle base has been set to /u01/app/oracle
[oracle@o73 ~]$ sqlplus / as sysdba @x

SQL*Plus: Release 18.0.0.0.0 - Production on Fri Jun 5 10:50:17 2020
Version 18.10.0.0.0

Copyright (c) 1982, 2018, Oracle.  All rights reserved.

Connected to:
Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production
Version 18.10.0.0.0

with d as (select
*
ERROR at line 1:
ORA-40597: JSON path expression syntax error ('$.* ? (@.id == 1000).price')
JZN-00209: Unexpected characters after end of path
at position 21

X
--------------------------------------------------------------------------------
42

Disconnected from Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production
Version 18.10.0.0.0
[oracle@o73 ~]$

19c (19.7):

[oracle@o73 ~]$ . oraenv
ORACLE_SID = [RYMIN18] ? RYMIN19
The Oracle base remains unchanged with value /u01/app/oracle
[oracle@o73 ~]$ sqlplus / as sysdba @x

SQL*Plus: Release 19.0.0.0.0 - Production on Fri Jun 5 10:50:31 2020
Version 19.7.0.0.0

Copyright (c) 1982, 2020, Oracle.  All rights reserved.

Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.7.0.0.0

X
--------------------------------------------------------------------------------
42

X
--------------------------------------------------------------------------------
42

Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.7.0.0.0
[oracle@o73 ~]$


SQL (Postgres): Subquery in WHERE clause with IN operator is slow

Test
select count(*)
from person p
cross join task t
where EXISTS (
    select NULL
    from task_participant tp
    where tp.person_id = p.id
      AND t.id = tp.task_id )
  and p.id = 1234


Get Job id OR Job name from within executing job

You will have to use tokens in your job steps to get your own job id. Details here: Using Tokens in Job Steps.

At the end of the article there's one example with jobid:

SELECT * FROM msdb.dbo.sysjobs
WHERE @JobID = CONVERT(uniqueidentifier, $(ESCAPE_NONE(JOBID))) ;


Modelling products along with their attributes and discounts for an e-Commerce database

regarding question 1):
A table with the following fields
1) date ,to set the date-from which the discount is valid
2) product, to define which product we are talking about
3) discount_type, i.e. 1=percentage, 2=how_many_product_for_this_price, 3=price
4) percentage, needed for discount_type=1
5) quantity, needed for discount_type=2
6) price, needed discount_type=3

Example:
If you have this data (I will leave out date for simplicity):

product, discount_type, percentage, quantity, price
1, 1, 0.5, null, 42
2, 2, null, 2, 42
3, 3, null, null, 25

product 1 has a discount of 0.5*100 = 50%, and a price of $42

product 2 can be sold 2 for the price of one, so 2 for $42

product 3 has a discount for $25


You have to declare your variable as the desired type.  The %TYPE construct is for copying types, therefore it is usable in the DECLARE section only.  In return, the assignment will be simpler:

DECLARE
    v_tmp_host scan.location.alias%TYPE;
    v_ip_address character varying;
BEGIN
    v_ip_address := '127.0.0.1';
    v_tmp_host := v_ip_address;
    [...]

The only change to your code is that I completely omitted the cast from the assignment.  In this case, PostgreSQL won't try to resolve scan as a database name, since cross-database references are not implemented...

If you declared your variable with a certain type, assignments will try to cast the given value to that type.  In case of failure you'll get an error.  Since normal inet input is just a single-quoted string, a varchar will be accepted (if its format fits the inet input format).


I found the answer to my question.

The answer that worked for me was to add the 2nd move File System Task. Then when connecting to previous Data Flow Task, right click on the Precedence Constraint and click the "Failure" option.  I now have my error condition!


In MySQL User table and Login history log table with Left join gives an error for contains nonaggregated column

An alternative method to that suggested above would be to do the following (fiddle here):
CREATE TABLE user
(
  id INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY,
  email VARCHAR (256) NOT NULL
);

The number of fields is irrelevant, since you won't be grouping by them. This is just a sample for demonstration purposes.
Then:
CREATE TABLE login_history
(
  lh_id INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY,
  user_id INTEGER NOT NULL,
  login_dt TIMESTAMP NOT NULL,

  CONSTRAINT lh_user_dt_uq UNIQUE (user_id, login_dt),  -- same user can't login at the same time

  CONSTRAINT lh_uid_fk FOREIGN KEY (user_id) REFERENCES user (id)

);

Populate:
INSERT INTO user (email)
VALUES
('111@xyz.com'),
('222@xyz.com'),
('333@xyz.com'),
('444@xyz.com'),
('555@xyz.com'),
('666@xyz.com');

and:
INSERT INTO login_history (user_id, login_dt)
VALUES
(1, '2011-01-11'), (1, '2011-02-11'), (1, '2011-03-11'),
(2, '2012-01-12'), (2, '2012-02-12'), (2, '2012-03-12'),
(3, '2013-01-13'), (3, '2013-02-13'), (3, '2013-03-13'),
(4, '2014-01-14'), (4, '2014-02-14'), (4, '2014-03-14'),
(5, '2015-01-15'), (5, '2015-02-15'), (5, '2015-03-15');

And the query would be:
SELECT u.*, tab.lh_id, tab.ldt
FROM user u
JOIN
(
  SELECT
    ROW_NUMBER() OVER (PARTITION BY lh.user_id ORDER BY lh.login_dt DESC) AS rn,
    lh_id,
    lh.user_id AS id,
    lh.login_dt AS ldt
  FROM login_history lh
) tab
ON u.id = tab.id
WHERE tab.rn = 1
ORDER BY u.id;

Result:
id  email        lh_id  ldt
1   111@xyz.com     3   2011-03-11 00:00:00
2   222@xyz.com     6   2012-03-12 00:00:00
3   333@xyz.com     9   2013-03-13 00:00:00
4   444@xyz.com    12   2014-03-14 00:00:00
5   555@xyz.com    15   2015-03-15 00:00:00

which, by inspection, is correct!
Now, in the fiddle, I've done a performance analysis of the query proposed above and @Akina's answer - and you'll see that my query is less performant. "So, what's the point?" you might ask?
Well, imagine a scenario where you want the last 2 logins or 3 or the last x of them... - just change the desired values of rn (put tab.rn <= 2 or tab.rn IN (1,2)- shown at bottom of fiddle)!
Using MySQL's window functions, in this case the ROW_NUMBER() function (examples here), you can answer many more questions of your data.
I would strongly urge you to become familiar with window functions like those used in the second piece of SQL - they are very powerful and well worth the effort to learn - they will repay that effort 10 times over...
EDIT:
Following a query by the OP concerning users who have never logged in, I did the following (fiddle - same as above).
In order to JOIN the user table to the login_history table with a field that doesn't exist in the login_history, we have to do a LEFT [OUTER] JOIN (the left join is automatically an outer one, so the OUTER keyword is optional).
I added a user (6, 666@xyz.com) (see above) who has no records in the login table.
Then, I used the following SQL:
SELECT
  u.id,
  u.email,
  ROW_NUMBER() OVER (PARTITION BY lh.user_id ORDER BY lh.login_dt DESC) AS rn,
  lh.lh_id,
  lh.user_id AS luid,
  lh.login_dt AS ldt
FROM user u
LEFT JOIN login_history lh
ON u.id = lh.user_id
ORDER BY u.id DESC;

Result (snipped for brevity):
id  email         rn    lh_id   luid    ldt
6   666@xyz.com     1    NULL    NULL   NULL
5   555@xyz.com     1      15       5   2015-03-15 00:00:00
5   555@xyz.com     2      14       5   2015-02-15 00:00:00
...
...

So, we see that through the LEFT JOIN we now have the user 6, but the corresponding records from the login history are NULL (as expected)!
Now we simply wrap the above query in another to get the latest (or 2 latest or 3...) login time(s) for a given user as follows:
SELECT * FROM
(
  SELECT
    u.id,
    u.email,
    ROW_NUMBER() OVER (PARTITION BY lh.user_id ORDER BY lh.login_dt DESC) AS rn,
    lh.lh_id,
    lh.user_id AS luid,
    lh.login_dt AS ldt
  FROM user u
  LEFT JOIN login_history lh
  ON u.id = lh.user_id
) AS tab
WHERE tab.rn = 1  -- or tab.rn IN (1,2) - shown at the bottom of the fiddle
ORDER BY tab.id;

Result:
id  email          rn  lh_id    luid    ldt
1   111@xyz.com     1      3       1    2011-03-11 00:00:00
2   222@xyz.com     1      6       2    2012-03-12 00:00:00
3   333@xyz.com     1      9       3    2013-03-13 00:00:00
4   444@xyz.com     1     12       4    2014-03-14 00:00:00
5   555@xyz.com     1     15       5    2015-03-15 00:00:00
6   666@xyz.com     1   NULL    NULL    NULL

I also added the results for EXPLAIN ANALYZE (available from MySQL >= 8.0.18), and I also profiled the query (fiddle).
As with the previous solution using the ROW_NUMBER() window function, it's a bit slower and the plan is more complex but, as I said, it is also potentially far more powerful down the line. I would urge you to test with your own system (H/ware & S/ware) and data to see which solution best suits your requirements.


How can I use a SQL query to retrieve data on a database sever?

In EXCEL, from the Data ribbon, Get External Data tab, select From Other Data Sources -> From SQL Server.

Follow the wizard to connect to your server and create a query.


The pending table is a better concept. It only gets used to create and approve pending elements.

Approval is a transaction:

  BEGIN;

  INSERT INTO title
  SELECT *
  FROM pending_title
  WHERE id = {x};

  DELETE FROM pending_title
  WHERE id = {x};

  COMMIT

This will simplify the lookup logic so you don't need status=approved in every other SQL query.


Ok, it seems I finally understood what you want.
Create a dataset date_parameters with the following query:
select case when month(getdate()) <= 3 then '4/1/' + cast (year(getdate()) - 1 as char(4))
            when month(getdate()) <= 9 then '10/1/' + cast (year(getdate()) - 1 as char(4))
            when month(getdate()) <= 12 then '4/1/' + cast (year(getdate()) as char(4))
       end as from_date,
       case when month(getdate()) <= 3 then '9/30/' + cast (year(getdate()) - 1 as char(4))
            when month(getdate()) <= 9 then '3/31/' + cast (year(getdate()) as char(4));
            when month(getdate()) <= 12 then '9/30/' + cast (year(getdate()) as char(4))
       end as to_date

Then in your default parameter values choose the option Get values from a query
Specify your date_parameters dataset and choose from_date for your FROM DATE parameter and the same dataset but to_date as value for TO DATE parameter

And here is the result:

P.S. Adjust case constants as you need (with padding zeros), only now I noticed that in the question you wrote 10/1/2017 but in the comment it became 10/01/2017. I used your constants from the question, i.e. without zeros


Creating SQL Server Job on each replica in availability group

Why not use a master server for the SQL Server agent? You could even use a third server to create and schedule the jobs on both the servers and you get a central repository and log server for the jobs. A small check in the agent job will make certain that it's running on the active server.


Field not being sorted in ascending order in Visual Studio 2010

Specials thanks to Shark, Aaron Bertrand and Jon Seigel that have enlighten me.

I have use the ORDER BY CONVERT(INT, time) and it now solved the application issue problem. (Fortunately, the change to the SQL statement was a quick way to solve this problem)


You can use DDL triggers for this. There doesn't appear to be a DDL event raised for attach but there definitely is for CREATE DATABASE. You could work around this by denying rights to sp_attachdb and force everyone to use CREATE DATABASE FOR ATTACH instead.

The following example feels like a bit of a hack, so hopefully someone with more DDL trigger experience will propose a neater way of doing this.

IF EXISTS (SELECT NULL FROM sys.server_triggers WHERE name = 'ddl_trig_database')
BEGIN
    DROP TRIGGER ddl_trig_database
    ON ALL SERVER;
END
GO

CREATE TRIGGER ddl_trig_database
ON ALL SERVER
FOR CREATE_DATABASE
AS
    DECLARE
        @DatabaseName NVARCHAR(128)
        , @CreatedBy NVARCHAR(128)
        , @CreatedDate NVARCHAR(23)
        , @SQL NVARCHAR(4000);

    SELECT
        @DatabaseName = EVENTDATA().value('(/EVENT_INSTANCE/DatabaseName)[1]','NVARCHAR(128)')
        , @CreatedBy = EVENTDATA().value('(/EVENT_INSTANCE/LoginName)[1]','NVARCHAR(128)')
        , @CreatedDate = EVENTDATA().value('(/EVENT_INSTANCE/PostTime)[1]','NVARCHAR(23)');

    SET @SQL = '
        USE ' + @DatabaseName + ';
        EXEC sys.sp_addextendedproperty @name = N''Owner'', @value = N''' + @CreatedBy + ''';
        EXEC sys.sp_addextendedproperty @name = N''StartDate'', @value = N''' + @CreatedDate + ''';';

    -- PRINT @SQL;
    EXEC (@SQL);

GO

CREATE DATABASE MyTestDatabase;
GO

DROP TRIGGER ddl_trig_database
ON ALL SERVER;
GO

DROP DATABASE MyTestDatabase;
GO


Conditional ORDER BY based on NULL values in the column

You could add a case when check to the order by to give the NULL's a lower priority and then filter on grades

SELECT *
FROM table
ORDER BY CASE WHEN grades IS NULL
THEN 2 ELSE 1 END ASC, grades ASC;

Since you are using integers you can also do this, which should be more performant

SELECT *
FROM table
ORDER BY -grades DESC;

Source

DB<>Fiddle


Where to start
Start by defining the performance problem that you're experiencing as precisely as you can. I'm going to assume that the query that you have is running too slowly. How quickly does it need to finish? You need that information to figure out if certain trade-offs are worth it. For example, adding an index to an underlying table might improve the performance of this query but it will take up additional space on the database and require extra maintenance during DML.
Notes on EXPLAIN
Next understand as much as you can about the explain plan for the query that you have. Pay particular attention to the row estimates. If you think that Oracle is executing a query too slowly then coming up with an explain plan that you think would finish faster is a great exercise. You may think of an index that needs to be added. Worst case you can add a hint to test out a theory and figure out a way to rewrite the SQL (or keep the hint if absolutely necessary) to improve performance. An explain plan can give good clues but it only has what Oracle thinks will happen. If your database is licensed for the tuning pack then you can use real-time SQL monitoring in SQL Developer. That is a great feature for figuring out why a query is running slowly because it gives you runtime information about the actual versus estimated number of rows, wait times, and the amount of time spent on each step. All you posted is an explain plan so I'll do my best based on that alone.
For your query, the driving table is NB_TSFDETAIL. Oracle expects to filter down 700 M rows to 168k using a full scan on the table. Oracle expects the nested loop join to ITEM_LOC to filter down the result set further to 35k rows. The rest of the joins aren't expected to meaningfully change the cardinality. Every join is a nested loop join except for a hash join on TSFHEAD. That may have been implemented as a hash join simply because there wasn't a good index candidate on that table.
Importance of join order, table access method, and join type
When I'm looking at a query I focus on three main things: join order, table access method, and join type. If any of those seem inefficient or don't match what I expected to happen then I dig deeper into that.
To give an example of when join order can go wrong, if there's a join that eliminates many of the rows from the result set then I generally want that join to be processed as earlier as possible in the query plan. If it's processed as the final join then I may have done a lot of unnecessary work on rows that won't be returned in the final result set. For your query Oracle chose the NB_TSDETAIL table as the first table to access. That seems pretty reasonable because the filters are expected to eliminate over 99.9% of the rows from one of the largest ones in the query. Most of the joins aren't expected to change the cardinality so I'm not that concerned about join order for this query, assuming that the estimated cardinalities are close to reality. If there is an INNER JOIN that you know will eliminate many rows then you may need to give Oracle more information so it can build a better query plan with that knowledge.
To give an example of when table access method can go wrong, sometimes tables aren't indexed properly for queries. It looks like your query requires a full table scan on NB_TSFDETAIL. If that full scan takes longer than the maximum response time that you can tolerate then it doesn't matter how you tune your query. You need to fix the underlying data model problem, perhaps by creating an index. Another example of when table access method can go wrong is if an index is used when it shouldn't be. Using an index can be very inefficient if Oracle needs to access a large performance of rows from a table. In that scenario a full table scan is often more efficient.
To give an example of when the join type can go wrong, a nested loop join can be inefficient if the outer table has too many rows. Here Oracle estimates that only 168k rows will be returned from NB_TSDETAIL. That isn't that many rows relatively speaking so a nested loop join using that result set as an outer table may have a lower cost than doing a hash join or a merge join. However, what if the actual number of rows returned from the table is 168M? That means that the cost of the time will be roughly off by a factor of a thousand. If Oracle had more accurate cardinality information it may have picked hash joins instead which likely would have performed much better.
There are of course other possible issues with query plans that can lead to poor performance. The one most worth mentioning for this query I think is the lack of parallelism. Perhaps this is intentional and your server is too busy to allow for it, but the amount of work required here by the RDBMS makes me think that the query could benefit from parallelism.
Conclusion
In summary, the following are the most likely causes of the performance issue that you're experiencing:

Oracle is estimating the number of rows returned from NB_TSFDETAIL as 168982 which is a significant underestimate. If that estimate is fixed then Oracle will pick a plan with fewer nested loop joins that will perform better. Ways to improve that estimate including creating multi-column statistics (there may be a hidden relationship between the PROCESS_EXPENSE_IND and EXPENSE_IND columns that Oracle doesn't know about), updating statistics on the table, or by using a larger sample size for your dynamic sampling hint.

The number of estimated rows from NB_TSDETAIL is about right but it takes a long time to find them all with a table scan on a 700 million row table. Defining an index on those columns could speed up query performance.

You're running a query on 700 M row tables but not using parallelism. That may have been configured by the DBA and isn't intentional on your part. If your workload allows it and this query is important enough you should consider a PARALLEL(X) hint to see if that improves the execution time.

Some other database or hardware setting is causing the slowness of the query. This could be disk configuration, database parameters, or any number of things.

Further actions
Actionable next steps for you include:

Define your performance problem and response time requirement.

Determine the accuracy of the 168k row estimate on the NB_TSFDETAIL table.

Benchmark the time Oracle needs to perform a full table scan on the NB_TSFDETAIL. You may be able to do this with a carefully constructed COUNT(*) query.

If necessary, talk to your DBA about allowing this query to run in parallel.

You know more about the table structures and data than we do, so try out other theories to see if they improve query performance.


Why is there an aggregate in this UPDATE plan?

This isn't Halloween protection, it's normal UPDATE semantics.

Check out this warning from the docs on UPDATE:

  The results of an UPDATE statement are undefined if the statement includes a FROM clause that is not specified in such a way that only one value is available for each column occurrence that is updated, that is if the UPDATE statement is not deterministic.

A SET statement doesn't accumulate like you expect it to* - it just updates each qualifying row in the Summary table once.

Since the join results in duplicate potential values for collected, the optimizer introduced a Stream Aggregate - essentially it groups by the "primary key" of the heap (the row locator, referred to as Bmk1000 in the execution plan) to get just one value for collected in each row.

Looking at the plan XML, you can see that the internal only "ANY" aggregate was used to choose among the possible values of collected and fact:

<ScalarOperator ScalarString="ANY(@Summary.[collected] as [s].[collected])">
  <Aggregate AggType="ANY" Distinct="false">
    <ScalarOperator>
      <Identifier>
        <ColumnReference Table="@Summary" Alias="[s]" Column="collected" />
      </Identifier>
    </ScalarOperator>
  </Aggregate>
</ScalarOperator>

*this sort of accumulation does work when you add a variable assignment into the mix, although it's still not "supported."  This is referred to as "quirky updates" (reference)


SQL Server not giving back memory - staying at Max Server Memory setting

It is holding that memory because you used it once so, obviously, you will probably use it again. This is the way SQL Server works: it will take the memory it needs (up to the max you've allowed), and will only give it back to the OS if the OS demands it. If you're not demanding memory back from SQL Server, why do you expect it to give it up? Allocating and deallocating memory is inefficient, and SQL Server isn't designed to give you memory back so you can let it sit idle. For similar reasons, it doesn't automatically shrink files for you after you delete or reorganize data: grow/shrink operations are expensive, and if the file is just going to grow again tomorrow...

If you are running SQL Server on your laptop where you need the memory for other tasks, then you should consider setting a lower max server memory setting. This way SQL Server will never encroach on the memory you think you need.

If you are running SQL Server on a server, then it likes to be the only thing on the box. If the server is hosting other applications etc. that need memory, then you can either reduce max server memory further, or move those applications to another server. If there are no other applications that need the memory, then why do you care whether it's using 20 GB, or 16 GB, or 12 GB, or 100 MB? Are you going to lease out the remaining memory temporarily to other servers until SQL Server needs it again? Didn't you give it 20 GB of memory so it will use 20 GB of memory?

There is absolutely no reason you should be worried that SQL Server is using the amount of memory that you've told it is ok to use. This is exactly how it's supposed to work. When users run queries against the server, presumably, they will likely be accessing data that's already in memory (a large portion of SQL Server's memory allocation is buffer pool). If your database is a lot bigger than 20 GB, then you may consider actually raising the max server memory setting to get the best use out of the memory and how much I/O comes from there rather than disk (which is a lot slower). Artificially lowering the max server memory because you think Windows needs 12 GB of memory to operate is like paging to disk - you're forcing more I/O through slow disk subsystems that could be performed in much faster memory.

Again, if nothing else is running on the server, there's no reason you need more than 3 or 4 GB for the OS, leaving everything else to SQL Server. And again, you shouldn't be worried if you have set SQL Server's max memory to 28 GB, and it keeps "using" 28 GB even after all the users have gone home. This just means that in the morning it won't take any work to get back up to 28 GB.


On what basis DMA identifies columns for Dynamic Data Masking?

From what I understand, DMA looks at the column names and tables for keywords that would hold PII, Sensitive Data (such as logins, passwords, etc.), or other unique data attributes. If it finds anything then it should mark it for both dynamic data masking and always encrypted.

If you change the name of the column to not have easily identifiable keywords, the recommendation should not show up for that column/table.


Is it a good approach to design tables according to the queries I'll perform on it?

In most database management systems, data is is stored as pages, not blocks.  Pages are normally  4 or 8 KB, depending on the database and how its been configured.
All else being equal, smaller row size will equate to better reuse of cached pages and less page reads on queries that require a large number of rows - so less I/O and faster read performance.
However
If you vertically partition the table (as you have in your example), there will be a slight increase to overall storage (equal to the primary key length and number of rows, plus the b-tree) and insert performance will be slightly slower as you'll need to maintain a PK-FK relationship between the two tables.
Furthermore, if most of your queries are for single-record lookups, you're still going to be reading a single page.  There's a greater chance that page will be cached, but reading 4 or 8 KB off a modern disk is really not an expensive operation.
Splitting the table would require 2 page reads (and navigating two B-trees) when you require BirthDate/BirthPlace.  Again, not really a big deal on modern hardware.
The only time I would vertically partition a table would be in certain data warehouse situations, or if BirthDate/BirthPlace were nullable and infrequently populated.
Other Considerations
If badge number is relatively small in size (say, under 20-30 bytes), the best thing you can do to increase performance would be to drop the unneeded ID column and make your primary key BadgeNumber since:

You shouldn't have duplicates in that column
Most likely you will primarily lookup on that column, so using BadgeNumber:

Saves you a column, making your table more compact
Removes the need for an index (and associated overhead) on BadgeNumber
Eliminates the need to join to your table to get the BadgeNumber when the table has a PK-FK relationship to another table.

There are other ways to reduce I/O and improve read performance.  Most commercial DBMSs will support some form of data compression. This can fit more rows on a single page without any changes to the structure of the table, at the expense of some CPU overhead to compress/decompress the data as it is written/read.  CPU is usually a cheaper operation than disk, so compression is usually a net benefit.


How can I connect to SQL server by just using local ip address

Create an SQL Client Alias as Goody said. Maybe better to put a name isntead of the IP address on the alias name.

Here below an screenshot:


Subqueries are not allowed in this context. Only scalar expressions are allowed

You could use this code instead - just use the SELECT to provide the values, including a fixed literal:

INSERT INTO dbo.table_one (greeting_column, name_column)
   SELECT
      'hello', column_1
   FROM
      dbo.table_to
   WHERE
      name = 'bob'

You cannot mix the VALUES() and the SELECT methods of providing values for an INSERT - either you can provide literals and SQL variables for a VALUES(.....) list of values - or then you need to use the SELECT instead


MongoDB: Mongoimport server selection error , connection refused, too many files open

Depending on your OS, there's multiple ways to modify the maxopenfile of MongoDB.
On Debian and Ubuntu, in /lib/systemd/system/mongod.service there's the LimitNOFILE argument which can be increased if needed.
You have more infos on other OS on https://docs.mongodb.com/manual/reference/ulimit/


Get Data for user excluding another user blocked with its time interval

The best way to handle this would be to make the default value for the EndBlockDate be an arbitrary date far in the future instead of NULL. For example, if your BlockedUsers table was like this:

BlockedUsers

ID | FromUserId | BlockUserId | StartBlockDate       | EndBlockDate
1  |    A       |   B         | 2017-10-06 11:55:00  | 2017-10-07 11:55:00
2  |    A       |   B         | 2017-10-08 10:05:00  | 2017-10-08 12:07:00
3  |    A       |   B         | 2017-10-09 11:55:00  | 2150-12-31 23:59:59

Then your query above would correctly filter the information.


does LIMIT clause mean constant memory/storage in a PostgreSQL query?

Definitely not. With an order by clause, resource usage can vary between both extremes depending on whether or not a sort needs to be done or an index can be used.

Other factors play a major or minor role too:

querying from a base table, a view or a foreign table
width of the table
data types involved
size of the table / indexes involved
whether the table(s) are partitioned or not and whether partition elimination can be applied for the query
whether a serial or a parallel plan is used (which depends on the structure of the query, the indexes, the partitioning (or not), the DBMS version and possibly other factors)
resource consumption settings in postgresql.conf, like work_mem and replacement_sort_tuples (see Postgres docs: Resource Consumption for details.)


I figured it out. I had to go to Tools > Options > General > Migration and set Extended data migration options to Show.

Then I had a new Data Mig... tab for each table, and had to uncheck Truncate SQL Server table and hit Apply individually for each table. Rather laborious when you have a lot of tables... but it worked.


Autotask 'auto optimizer stats collection' and scheduler jobs logging

Try
select sample_size from dba_tables where table_name='PS_JOB';

What does that return?
If that number is not as expected, is it possible another DBA used something like DBMS_STATS.SET_GLOBAL_PREFS
in this way for example:
EXEC DBMS_STATS.set_global_prefs('estimate_percent', '20');

instead of using the default of DBMS_STATS.AUTO_SAMPLE_SIZE? (which may be more than 10 itself)
That figure of 20 would change it from the traditional default of 10.  However AUTO_SAMPLE_SIZE will have Oracle determine the value and that may have been set, and Oracle chose a value larger than 10.
I am also in the dark about 'auto optimizer stats collection' and exactly how it has been working internally since 11g.  We have some windows though (8 total) that run for longer than 3 hours of stats gathering on our production databases.


Basically, you need to move your @starting_id predicate to the base case of the recursive part:
WITH chainIDsUpwards AS
(
    SELECT id, parent_id FROM foo
    WHERE id = @starting_id

    UNION ALL

    SELECT foo.id, foo.parent_id FROM foo
    JOIN chainIDsUpwards p ON foo.id = p.parent_id
)
SELECT * FROM chainIDsUpwards;

If you put the predicate as:
SELECT * FROM chainIDsUpwards WHERE ...;

it will filter all rows that do not satisfy the predicate.
I also believe that you need to change the join clause in both cte:s if you want the behavior to correspond with the name. I find it easier mentally to start with the cte in the join like:
SELECT foo.id, foo.parent_id
FROM chainIDsUpwards p
JOIN foo
    ON p.parent_id = foo.id

Fiddle


Calculating RAM requirements for an in-memory database

Check out these links - the first is for Michael Stonebraker's VoltDB. He is one of the main drivers behind what he terms NewSQL - he believes that OldSQL (i.e. traditional RDBMS systems, your Oracles, MySQLs and SQL Servers) are doing far to much work buffering and latching &c. In his VoltDB (Java based) system, transactions run to completion and don't compete with other transactions for system resources.

The key thing to bear in mind is that it is a shared-nothing in-memory architecture. He believes that this is good for OLTP, but not for OLAP for which he advocates columnar based stores such as the one he founded - Vertica, since bought by HP.

Stonebraker also believes that NoSQL has thrown the baby out with the bathwater in sacrificing SQL and ACID for performance.

Microsoft's Hekaton is based on very similar ideas (1, 2) which is hardly surprising since there was a Microsoft guy as coauthor of some of Stronebraker's work (can't find reference at the minute). The main difference AFAICS is that Hekaton's stored procedures are compiled to native code.

The references I have given are kind of vague and general, but the first Microsoft link says that your memory should be twice your data. AIUI, this rule is not cast in stone, but will depend on your app's functionality, but it's a good place to start :-). This is a fascinating area and worth reading up on (did an essay on it last year) and there's a lot of stuff out there.

Check out these also (1, 2, 3).


In SQL Server, what is "Partitioned Table Parallelism" that is only available in Enterprise Edition?

TMWFR
This was a documentation error. I blogged about the results of many tests comparing parallel queries against partitioned tables here:

Partitioning And Parallelism

That resulted in the documentation getting updated to reflect that there is no difference

RDBMS scalability and performance


Mysql warning: InnoDB: Ignoring tablespace `foo`.`bar` because the DISCARD flag is set

Why this warning pops up:

You discarded the tablespace and physically removed the .frm file from the data directory. But still, tablespace information is present in the InnoDB data dictionary table. (you can check innodb_sys_tables, innodb_sys_tablespace in information schema).

So whenever the server starts up, InnoDB tries to load all the table present in sys_tables, sys_tablespaces.  So it tries to open foo.bar table but the state is DISCARD. So that warning is issued.

How to remove the table/warning:

Create a fake bar.frm & bar.ibd inside the foo folder of data
directory.
Start the server and give the drop table foo.bar. It
should remove the table from the dictionary and removes the warning too.


Assuming that:
- You have the looping and sleep code in the CLR code
- You know how to call said code from SQL agent (a stored procedure wrapper if nothing else)
- Your process is ok with the occasional sub-minute downtime
- Your process will not lose any data if it fails part way through (i.e. the next run will process the data the failed run was trying to process)

then I would propose a SQL agent job that is sceduled for every minute, and the job step itself have say, 10 retries set (advanced page) so that it will usually restart itself immediately if it fails.
Why just 10? Because if it starts consistently failing, you will want to get notification quickly (via agent job failure notification settings). That will be an email a minute.
Of course, if it fails 10 times sporadically, you'll get a false alarm notification, but you can quickly see from the job history whether it's failing consistently or just sporadically.


Why Does Oracle kill a session at the specific time?

If the sessions can no longer be reached, this sounds a lot like Oracle Dead Connection Detection.

From that paper:


Recovering a lost datafile with no backup available

If you switched your database to ARCHIVELOG mode, all previous backups are invalidated and thus cannot be used for recovery; therefore, you have to do the full backup of the database immediately after switching it to the ARCHIVELOG mode, to make archive redo logs useful.

If you have a full backup(s) of database made before failure and all archive logs, you can easily restore the data file(s) and archive logs from the backup(s) and recover the database or data file(s) to a point of failure applying restored archive logs to the restored data file(s).

If you however don't have backups of database and archive logs made after switching, then, generally, you're out of luck. Though as @Mat pointed out, sometimes you can give the survived bits of data (archive logs and control file) a go at recovering your lost data file.

What can be useful for you is Data Recovery Advisor (DRA, read "Data Recovery Advisor" section in Database Concepts for more info). It can help you to recover database, and is specifically designed for situations like yours when you're not sure how to recover the database or have the time restrictions for the system downtime. It can well save you a lot of time during recovery, reducing downtime caused by failures.

To determine if you can do something about your lost data file, start RMAN:

$ rman target=/

List failures captured by Oracle:

RMAN> list failure;

(The information about failures listed is collected and stored in Automatic Diagnostic Repository (ADR), and is available even when the database is down. Read "Fault Diagnosability Infrastructure Overview" section in Database Administrator's Guide for more info.)

If some open failures are returned (they will actually be returned in your case since you lost one of data files), ask the DRA about advice:

RMAN> advise failure;

Read the output of the last command thoroughly and implement the recommended or required manual actions (if there are). If there are no manual actions listed, and the DRA prepared the repair scirpt, ask the DRA to repair the failures:

RMAN> repair failure;

The DRA is also available from Software and Support tab in EM Database Control.


Searching large dataset, best way to speed this up

Yes, an index on quote.vehicle_id would help this greatly.
As a general rule† it is a good idea to have an index on columns that are foreign keys, and you should define it as a foreign key with:
FOREIGN KEY (vehicle_id) REFERENCES vehicles(id)

This way the database will help verify your data and prevent a family of potential bugs happening.
Also you will find mySQL's query planner/engine produces a more efficient plan if you use WHERE EXISTS. Your sub-select with GROUP BY will force it to run that part once per outer row when more efficient strategies could be available. Also EXISTS can exit early, once the first matching row is found, but a subquery with an aggregate as above will stop that happening so more rows may be read and considered which is not needed:
SELECT *
FROM vehicles v
WHERE v.group = 0
AND EXISTS (
    SELECT id FROM quotes quote
    WHERE quote.vehicle_id = v.id
    AND quote.term = 60
    AND quote.annual_mileage = 8000
    AND quote.deposit = 250
    AND quote.credit_rating = "Excellent"
    )

You still want the index on quote.vehicle_id for this to avoid full table scanning.
† There are valid exceptions to this rule, which is part of why most database engines don't create such an index automatically, but they are fairly rare.


How can I resolve the error "Could not load file or assembly 'Microsoft.DataWarehouse.AS'" in SSIS?

I was able to defeat the error by uninstalling these two extensions for Visual Studio:

Microsoft Reporting Services Projects
Microsoft Analysis Services Projects

Then I reinstalled the latest SSDT for VS 2017.

On installation, I chose all 3 options (Analysis Services, Integration Services, Reporting Services). Although I would bet the only requirement to solve the problem would be AS as the original error referenced AS specifically.


Fact table Primary Key: Surrogate Key or multi-column Natural Key?

What's the context?  It sounds like you're talking about a data warehouse.  If so, I strongly recommend creating a synthetic key.  I can think of three good reasons, offhand:

It will be narrower than the business key.  While adding a column to a fact table should not be done lightly, because it will make your other indices narrower it may actually reduce the total size of your database.
Natural keys get dirty.  Sooner or later someone will reuse a product code for something unrelated, or you'll need to start tracking sub-customers.  If you have a mapping process between your natural keys and your synthetic keys, it's simple to account for these kinds of things.
You can use it to store synthetic values: -1 for "Not applicable", 0 for "Unknown", 1 for "Missing", 2 for "Illegible", etc.  Depending on your natural keys, you might be able to do the same there, but there would always be a risk of collision.

48+ billion records is a lot.  If you haven't already, you should consider aggregating data, perhaps after the current month.  Synthetic keys could make this simpler too: perhaps values 1,000,000,001..1,999,999,999 are reserved for 2017/08 details, and in October the August data gets collapsed into a single record with ID 1,000,000,000 (this probably wouldn't be a good idea, it's just an example of how you may need to disconnect business keys from warehouse keys).

One last note: don't forget that your choices for PK and for clustering key by no means need to be the same!  Each should be evaluated on its own merits.


Primary keys, clustered indexes and partitioning

at no point while processing do we JOIN on or include date in WHERE
  clauses

All partitions will need to be touched when the partitioning column is not specified in query predicates.  Put yourself in SQL Server's shoes - how would you know which partitions to access (or not) without knowing the partitioning column value?  Consider that a singleton index seek without partition elimination will seek against every partition.  Compared to a non-partitioned table, this seek overhead will be more noticeable when few rows are returned.  There won't be as much difference with full scans, though.

  Would it not be more optimal to have a clustered primary key on
  recordID alone, does the partitioning field (date) have to be apart of
  the clustered index?

Yes, recordID alone would be more optimal but the partitioning column must be part of the clustered index key as well as all unique indexes.  The implication is that primary key and unique constraints must include the partitioning column as part of the key.

  it's only recordID that needs to be a primary key. To me date should
  not be present in the primary key or clustered index.

Without knowing your data I can't say if date should be part of the primary key or not.  Assuming not from a data model perspective, you cannot partition the primary key index without it.  One approach is to use a non-clustered non-partitioned primary key, considering the performance implications on queries.

Tiered storage for less frequently accessed data is a use case for table partitioning with different file groups.  You will still need to develop a method to either physical move the underlying files or physically move rows from one filegroup to another.  Partition SWITCH alone cannot do the job.


Getting an error in MySQL Workbench: Cannot Open Administrator

You are using an old MySQL Workbench version. Please update to the latest version: http://mysqlworkbench.org/2014/04/whats-new-in-mysql-workbench-6-1/.

In older versions of MySQL Workbench we had connections and management instances separated. So it could happen that you have a server instance without a connection assigned. You could edit that instance to re-assign a connection to make it work.

Since version 6.0 this separation is gone. You only have a connection with all the necessary settings for management too.


You have to first set expectations - a screen that does such and such activities should complete each action in 1 second and all actions in 5 seconds and so on. For example, a search screen should retrieve results in 3 seconds, the booking actions (ticket booking) should be completed in 30 seconds etc.

Then work towards meeting those targets. That is the "normal" performance you want. Now go about meeting those targets. The database may be your bottleneck, it may not be. To identify issues at the database side, try using a tool like pgbadger. That will tell you which queries are taking time.

By the way, 8 hours for a query is probably not acceptable under any circumstance. Try the tool pgtune and see if there is scope for optimizing parameters.


For starters, your GUI shouldn't matter, but it's normally good to use the CLI - i.e. the mysql client - or at least as a backup in case of results you are having problems interpreting.
Second, have you tried IS NULL instead of the = comparison?
Third, what's wrong with NULL? Normally, you should use the appropriate datatype - why is NULL a problem? If you don't know the date of birth, there's nothing wrong with it. For example, if you want to perform an AVG calculation, you'll have problems. Your population will appear much older than it really is. NULLs however will be ignored in such a calculation, so no anomalous results (the same goes for all such calculations).


DB2 - Why new multiple C000000x subfolders are created in Archive Log folder?

This is completely normal.  Each of these directories are for each unique log chain.  If you are familiar with software version control, each log chain is like a branch.

A new log chain is created each time you restore a database and roll forward to a point in time other than end of logs.

Here is why:  You have a database, SAMPLE.  It has reached the log file S007500.LOG.

If you restore the database back to the point in time that corresponds to the log file S007000.LOG, what should DB2 do with the fact that you now have 2 sets of log files with the names S007001.LOG ... S007500.LOG?  They represent 2 unique sets of transactions, and they are called log chains.

So, when you create a database, DB2 creates the first log chain, C0000001.  If you restore the database, it creates C0000002, etc.

By preserving these log chains, DB2 gives you the ability to restore each unique series of transactions for a database.


How do I generate scripts to drop indexes, constraints, and keys for a database to change columns from int to bigint?

The DDL to make the change is just a text string. The necessary meta data is in the relational database already. This meta data can be queried by SQL. SQL can produce a string as output.

Put this together and you get a meta-SQL-generator ... thing. Something like this:

select
    'ALTER TABLE ' +
    OBJECT_SCHEMA_NAME(c.object_id) +
    '.' +
    OBJECT_NAME(c.object_id) +
    ' ALTER COLUMN ' +
    c.name +
    ' bigint ;'
from sys.columns as c
inner join sys.objects as o
    on o.object_id = c.object_id
where c.name = 'id'
and o.type = 'u'

You may need to add some square brackets, depending on your naming convention.

Write a similar query for each object type you're dealing with - constraints, indexes etc. There is a sys. or INFORMATION_SCHEMA. table to cover all of them. At the end you'll have a script which will capture the current schema. Run it once per target database. Pipe the output to a file (or in SSMS use "results to file" setting). That file will be a new SQL DDL script which will effect your change and restore the database to its previous state, including all customisations. The meta-script and produced scripts can be pushed to source control for tracking.


Agent service account shows as not clustered

No, it is nothing to be worried about - the Agent isn't considered a clustered service to SQL Server simply because it can't fail over on its own.

You should test a failover and be confident that SQL Server Agent will start on the other node and that jobs will run etc. (sometimes there can be local dependencies that won't exist on the other node(s)), but the DMV itself should not be a concern. I checked several clusters in our environment and they all say Y for the SQL Server service and N for the other services:

I can assure you that these clusters fail over correctly and that SQL Server Agent works on all nodes.


SQL Server is unable to connect to server , requires the actual server name

According to Release notes for SQL Server vNext on Linux, under the section Unsupported features and services, Replication is not currently supported.


How do we identify service shutdown time in MySQL

There are three(3) ways using file timestamps:

InnoDB Buffer Pool

If you have innodb_buffer_pool_dump_at_shutdown configured, look for the timestamp of the file that was written. The default filename is ib_buffer_pool. Mentioned this back on Dec 06, 2017 (Control InnoDB buffer pool allocation in MySQL (5.7+) ib_buffer_pool is usually written in folder set by datadir, you can run the following after shutdown:

cd /var/lib/mysql
ls -l --time-style=+"%Y-%m-%d %H:%M:%S" ib_buffer_pool | awk '{print $6,$7}'

Binary Logging

If mysql has binary logging enabled, get the timestamp of the last binary log after shutdown.

Error Log

You are probably saying, "I DON'T WANT TO LOOK INSIDE THE ERROR LOG !!!"

You don't have to. Just get the timestamp of the error log after the shutdown is complete.


What is the behavior of SQL Server Maintenance Plan - Clean Up task for deleting old backups

Does the expiration policy on the backup task "lock" those files from being deleted before they expire?

No. The Backup set will expire setting indicates when the backup set can be overwritten by another backup. It does not cause the file to be deleted, nor does it prevent the file from being deleted.

  In essence, could I set the cleanup task to delete files older than 1 day, and yet not delete backup files that haven't yet expired?

No. The cleanup task will delete the old files. And any backup sets within those files will be gone, regardless of their expiration setting.

  If a FULL backup is deleted, then all the differentials are useless, so they should be deleted as well.

You are correct that the differential backups are then useless. But the cleanup task that deletes the full backup doesn't know that, so it has no way to delete the differentials. You just have to wait until the cleanup task runs the next time, and the next time, and the next time, and it will eventually delete the differential backups, one at a time as they reach the cutoff age (7 days, in your example).


As per MongoDB BOL $unwind  Deconstructs an array field from the input documents to output a document for each element. Each output document is the input document with the value of the array field replaced by the element.

The $unwind stage has the following prototype form:

{ $unwind: <field path> }

To specify a field path, prefix the field name with a dollar sign $ and enclose in quotes.

Behaviors

$unwind has the following behaviors:

If a value in the field specified by the field path is not an array,
db.collection.aggregate() generates an error.
If you specify a path
    for a field that does not exist in an input document, the pipeline
    ignores the input document and will not output documents for that
    input document.
If the array holds an empty array ([]) in an input
        document, the pipeline ignores the input document and will not
        output documents for that input document.

For example

Consider an inventory with the following document:

> db.inventoty.insert({"_id" : 1, "item" : "ABC1", sizes: [ "S", "M", "L"] })
WriteResult({ "nInserted" : 1 })
> db.inventory.find().pretty()
> db.inventory.insert({"_id" : 1, "item" : "ABC1", sizes: [ "S", "M", "L"] })
WriteResult({ "nInserted" : 1 })
> db.inventory.find().pretty()
{ "_id" : 1, "item" : "ABC1", "sizes" : [ "S", "M", "L" ] }
> db.inventory.aggregate( [ { $unwind : "$sizes" } ] )
{ "_id" : 1, "item" : "ABC1", "sizes" : "S" }
{ "_id" : 1, "item" : "ABC1", "sizes" : "M" }
{ "_id" : 1, "item" : "ABC1", "sizes" : "L" }

Each document is identical to the input document except for the value of the sizes field which now holds a value from the original sizes array.


Make a list of stations by checking next_station column value

WITH RECURSIVE
cte AS ( SELECT station, next_station, 1 route_order
         FROM routes
         WHERE station = @source
           AND route_id = @route_id
         UNION ALL
         SELECT cte.next_station, routes.next_station, cte.route_order + 1
         FROM routes, cte
         WHERE routes.station = cte.next_station
           AND cte.next_station != @source
           AND cte.station != @destination
           AND routes.route_id = @route_id )
SELECT *
FROM cte
WHERE @destination = ( SELECT station
                       FROM cte
                       ORDER BY route_order DESC LIMIT 1 )
;

Wrong route selection (including cycles) allowed - in such case the resultset will be empty.


Erwin has an autolayout function that may help.

Another way you can regorganize things is to right click on a relationship line, select go to parent or go to child (whichever is appropriate) and then just drag the entity to where ever you want it.

You can also use the fit to model zoom function.

Hope this helps


View the final query executed by PostgreSQL Function

In psql just do \sf name_of_function and it will show you the source code. Or \ef will load it into an editor.


MySQL XtraDB/Galera Cluster High Availability RamDisk *SuperFast*

OK, 3 Galera nodes, with:

Each having all disk stuff sitting in RAM disks.
Enough RAM to make that possible.
The nodes separated geographically so that a volcano, flood, etc, cannot take out all of them at once.
High speed network.

Notes:

That will allow for full and automatic recovery from one server loss.
The only delay during a failure is switching clients to one of the other two nodes.
But, you may find that the network is the slow point.  (Entanglement has not been perfected yet.)
No snapshots needed.  (You always have 2 other nodes with full copies of the data.)


If you look closely at SpBlitz memory recommendations it says it has two possible meanings

  Memory Dangerously Low or Max Memory Too High

In your case looking at perfmon counters output you have shared their does not seems to be a memory pressure. So we are left with the fact that max server memory is configured incorrectly. And this seems true, on a system having 32 G memory you have given 30 G to SQL Server this would definitely leave OS fighting for more memory.

In this case OS will start paging out SQL Server processes to disk making it very slow. Or if Locked pages in memory is there for SQL Server service account OS process would be paged to disk. Both these conditions are very bad for SQL Server and OS.

I suggest you take help from This SE thread to calculate appropriate value for max server memory. I have given answer on this thread which asks user to take help of perfmon counters to reach to a sensible value.

You can start by giving 27 G to SQL Server and leaving 5G for OS. Then use perfmon counters mentioned in SE thread to reach to sensible value.

EDIT: I forgot to ask you to apply SQL Server 2008 R2 SP3, currently SQL Server is on SP2. This is must if you need extended support. In any case, to be on safer side you should always make sure SQL Server is patched to latest service pack.


Does a database backup include all files?

Yes. Details here: Backup Overview (SQL Server)

This is the section relevant for you:

Full backup

A full backup contains all the data in a specific database or set of filegroups or files, and also enough log to allow for recovering that data.

You should always test the backup (ideally by performing a restore to another location) to ensure you can recover your data in the event of a disaster.


In the end I coded a Python function import_csv_to_dynamodb(table_name, csv_file_name, colunm_names, column_types) that imports a CSV into a DynamoDB table. Column names and column must be specified. It uses boto, and takes a lot of inspiration from this gist. Below is the function as well as a demo (main()) and the CSV file used. Tested on Windows 7 x64 with Python 2.7.5, but it should work on any OS that has boto and Python.

import boto

MY_ACCESS_KEY_ID = 'copy your access key ID here'
MY_SECRET_ACCESS_KEY = 'copy your secrete access key here'

def do_batch_write(items, table_name, dynamodb_table, dynamodb_conn):
    '''
    From https://gist.github.com/griggheo/2698152#file-gistfile1-py-L31
    '''
    batch_list = dynamodb_conn.new_batch_write_list()
    batch_list.add_batch(dynamodb_table, puts=items)
    while True:
        response = dynamodb_conn.batch_write_item(batch_list)
        unprocessed = response.get('UnprocessedItems', None)
        if not unprocessed:
            break
        batch_list = dynamodb_conn.new_batch_write_list()
        unprocessed_list = unprocessed[table_name]
        items = []
        for u in unprocessed_list:
            item_attr = u['PutRequest']['Item']
            item = dynamodb_table.new_item(
                    attrs=item_attr
            )
            items.append(item)
        batch_list.add_batch(dynamodb_table, puts=items)

def import_csv_to_dynamodb(table_name, csv_file_name, colunm_names, column_types):
    '''
    Import a CSV file to a DynamoDB table
    '''
    dynamodb_conn = boto.connect_dynamodb(aws_access_key_id=MY_ACCESS_KEY_ID, aws_secret_access_key=MY_SECRET_ACCESS_KEY)
    dynamodb_table = dynamodb_conn.get_table(table_name)
    BATCH_COUNT = 2 # 25 is the maximum batch size for Amazon DynamoDB

    items = []

    count = 0
    csv_file = open(csv_file_name, 'r')
    for cur_line in csv_file:
        count += 1
        cur_line = cur_line.strip().split(',')

        row = {}
        for colunm_number, colunm_name in enumerate(colunm_names):
            row[colunm_name] = column_types[colunm_number](cur_line[colunm_number])

        item = dynamodb_table.new_item(
                    attrs=row
            )
        items.append(item)

        if count % BATCH_COUNT == 0:
            print 'batch write start ... ',
            do_batch_write(items, table_name, dynamodb_table, dynamodb_conn)
            items = []
            print 'batch done! (row number: ' + str(count) + ')'

    # flush remaining items, if any
    if len(items) > 0:
        do_batch_write(items, table_name, dynamodb_table, dynamodb_conn)

    csv_file.close()

def main():
    '''
    Demonstration of the use of import_csv_to_dynamodb()
    We assume the existence of a table named `test_persons`, with
    - Last_name as primary hash key (type: string)
    - First_name as primary range key (type: string)
    '''
    colunm_names = 'Last_name First_name'.split()
    table_name = 'test_persons'
    csv_file_name = 'test.csv'
    column_types = [str, str]
    import_csv_to_dynamodb(table_name, csv_file_name, colunm_names, column_types)

if __name__ == "__main__":
    main()
    #cProfile.run('main()') # if you want to do some profiling

test.csv's content (must be located in the same folder as the Python script):

John,Doe
Bob,Smith
Alice,Lee
Foo,Bar
a,b
c,d
e,f
g,h
i,j
j,l


What I know - Slony is Master/Slave replication solution with limited possibility copy structure's changes from master to slave. So this is not usable for your purposes. Probably dblink is not for you too - it is able to execute foreign SQL statement - nothing more.

you probably search tools like this or this


WAMP server performance tuning for MySQL

The differences have to do with the expected capacity mysqld is requested to handle

my_huge.ini

does not have max_connections set
has innodb settings commented out
values preset

sort_buffer_size = 2M
read_buffer_size = 2M
read_rnd_buffer_size = 8M
myisam_sort_buffer_size = 64M

my-innodb-heavy-4G.ini

has max_connections set at 100
has innodb settings enabled
values preset

max_allowed_packet = 16M
binlog_cache_size = 1M
max_heap_table_size = 64M
read_buffer_size = 2M
read_rnd_buffer_size = 16M
sort_buffer_size = 8M
join_buffer_size = 8M
thread_cache_size = 8
thread_concurrency = 8
query_cache_size = 64M
query_cache_limit = 2M

Default values are used if not specified. Whichever one you want to use, you must copy it over my.ini and restart mysqld to take effect.

If using my-huge.ini, you must shutdown mysqld, copy my-innodb-heavy-4G.ini to my.ini then startup mysqld.
If using my-innodb-heavy-4G.ini, you must shutdown mysqld, delete the innodb log files (ib_logfile0,ib_logfile1), copy my-innodb-heavy-4G.ini to my.ini then startup mysqld.

Since you said the number of DB Connections is expected to grow, you must raise max_connections to higher values but you must balance the usage of RAM. The best way to balance it would to run some diagnostic program like mysqltuner.pl and get recommendations adjusting settings per connection and per instance.

Since you are running MySQL in a WAMP environment, I would not surpass 25% of RAM for total memory because you must give Windows, Apache, and PHP (or Perl or Python) their due in terms of RAM. (I am glad you are using Apache and not IIS, otherwise this would be a WIMP environment !!!)


how can i write the max value of different consecutive values in the new column by length

If you are doing a difference between first column ([1,]) and d1 you will get something like this:

SELECT id,d1
   ,id-d1 as diff_d1
FROM Table1

output:

id  d1  diff_d1
1   1   0
2   2   0
3   3   0
4   0   4
5   1   4
6   2   4
7   0   7

Doing a max on [d1] and grouping by [diff_d1] column you will get : here

maxD1
3
2
0

Putting all together

;WITH cteSource AS
(SELECT
   id,
   d1,d2,d3
   ,MAX(d1) OVER(PARTITION BY id-d1 ORDER BY id
             ROWS BETWEEN UNBOUNDED PRECEDING
             AND UNBOUNDED FOLLOWING)as maxD1
   ,MAX(d2) OVER(PARTITION BY id-d2 ORDER BY id
             ROWS BETWEEN UNBOUNDED PRECEDING
             AND UNBOUNDED FOLLOWING)as maxD2
   ,MAX(d3) OVER(PARTITION BY id-d3 ORDER BY id
             ROWS BETWEEN UNBOUNDED PRECEDING
             AND UNBOUNDED FOLLOWING)as maxD3
FROM Table1
)
SELECT id
     ,CASE WHEN d1<> 0 THEN maxD1
           ELSE 0  END AS d1
    ,CASE WHEN d2 <>  0 THEN maxD2
           ELSE 0  END AS d2
    ,CASE WHEN d3 <>  0 THEN maxD3
           ELSE 0  END AS d3
FROM cteSource

Output:

id  d1  d2  d3
1   3   0   5
2   3   0   5
3   3   3   5
4   0   3   5
5   2   3   5
6   2   0   0
7   0   1   0

dbfiddle


Yes, if you look at the documentation, you see that you can use the @indexes parameter to specify which indexes to defragment. You can use % wildcards in your object names and you can prefix them with - to exclude indexes.

Using this information, the syntax you want to use is @indexes = 'ALL_INDEXES, -databasename.excludedschema.%', or @indexes = 'ALL_INDEXES, -%.excludedschema.%' if you want to exclude the schema in all databases.

Below you can find a repro documenting this:

First create a database with 2 fragmented tables, one in the dbo schema and one in a schema I called Exclusion. Both should be around 99% fragmented.

USE [master];
GO

CREATE DATABASE [196090];
GO
USE [196090];
GO

SET NOCOUNT ON;
GO

-- Create a table in dbo schema
CREATE TABLE [ProdTable] (
    [c1] INT,
    [c2] CHAR (5000) DEFAULT 'production');
CREATE CLUSTERED INDEX [prod_cl] ON [ProdTable] ([c1]);
GO

-- Fill with random integers to create fragmentation
INSERT INTO [ProdTable] (c1, c2) VALUES  (CRYPT_GEN_RANDOM(8000), 'filler');
GO 12800

-- Check the fragmentation of the production table
SELECT
    [avg_fragmentation_in_percent]
FROM sys.dm_db_index_physical_stats (
    DB_ID (N'196090'), OBJECT_ID (N'ProdTable'), 1, NULL, 'LIMITED');
GO

-- Create a schema to exclude from the rebuild
CREATE SCHEMA Exclusion;
GO

-- Fill with random integers to create fragmentation
CREATE TABLE [Exclusion].[ProdTableExclude] (
    [c1] INT,
    [c2] CHAR (5000) DEFAULT 'production');
CREATE CLUSTERED INDEX [prod_cl] ON [Exclusion].[ProdTableExclude] ([c1]);
GO

INSERT INTO [Exclusion].[ProdTableExclude] VALUES  (CRYPT_GEN_RANDOM(8000), 'filler');
GO 12800

-- Check the fragmentation of the production table
SELECT
    [avg_fragmentation_in_percent]
FROM sys.dm_db_index_physical_stats (
    DB_ID (N'196090'), OBJECT_ID (N'Exclusion.ProdTableExclude'), 1, NULL, 'LIMITED');
GO

Then run Ola's index maintenance this way:

EXECUTE dbo.IndexOptimize
@Databases = '196090',
@indexes = 'ALL_INDEXES, -196090.Exclusion.%'

And you will notice the script will skip the schema, leaving one of the indexes fragmented.


Log in using psql, issue

\d your_table

and look up the referenced table's name. If you set \set ECHO_HIDDEN on beforehand, you'll get a bunch of queries that produce the output, those you can reuse in your own discovery script.

For example, on my test database these look like

test=# \d tfk
       Table "test.tfk"
 Column │  Type   │ Modifiers
────────┼─────────┼───────────
 t_id   │ integer │
Foreign-key constraints:
    "tfk_t_id_fkey" FOREIGN KEY (t_id) REFERENCES t(t_id)

The query you are interested in is:

SELECT conname,
  pg_catalog.pg_get_constraintdef(r.oid, true) as condef
FROM pg_catalog.pg_constraint r
WHERE r.conrelid = '32850' AND r.contype = 'f' ORDER BY 1;

With a minor change, you get the table name like

SELECT confrelid::regclass AS foreign_table_name
FROM pg_catalog.pg_constraint r
WHERE r.conrelid = '32850' AND r.contype = 'f' ORDER BY 1;


Pass values to column to create graphs in excel

You want an Oracle PIVOT.

SELECT * FROM (
  SELECT hss.instance_number AS inst,
         snap.begin_interval_time AS snap_inicial,
         hss.pool,
         hss.name,
         TRUNC(hss.bytes / 1024 / 1024, 2) AS size_in_mb
    FROM dba_hist_sgastat hss
    JOIN dba_hist_snapshot snap
      ON snap.instance_number = hss.instance_number
     AND snap.snap_id = hss.snap_id
   WHERE hss.pool = 'shared pool'
     AND hss.instance_number = 1
     AND hss.pool IS NOT NULL
     AND snap.begin_interval_time BETWEEN SYSDATE - 5 AND SYSDATE
     AND hss.name IN ('KGLHD', 'SQLA', 'KGLS')
)
PIVOT (
SUM(size_in_mb) FOR name IN ('KGLHD', 'SQLA', 'KGLS')
)
ORDER BY inst ASC, snap_inicial ASC;


why we can specify table names enclosing them with []

They're called Delimited Identifiers, and their primary purpose is to allow for special characters and keywords to be used in/as table names

For example, some genius decided the name of the primary table in a database I work with would be master, which is a keyword.

They're really not needed at all except in those cases.


It is Java version 1.8.0.
General availability date of Db2 LUW 11.1 is today by the way....


Quick look at how much RAM is allocated to SQL Server?

You could NEVER, EVER trust Task Manager to tell you how much memory SQL Server is using (maybe you are remembering a 32-bit system with a very small amount of memory). Stop using Task Manager for this, period. Use the performance counter - you can also query the performance counter using DMVs:

SELECT object_name, cntr_value
  FROM sys.dm_os_performance_counters
  WHERE counter_name = 'Total Server Memory (KB)';

You could save that as a query shortcut in Tools > Options > Environment > Keyboard > Query Shortcuts, and get accurate results in a query window much faster than getting inaccurate results from Task Manager.

You can also check for memory pressure (and whether you can do anything about it) using these queries:

SELECT object_name, cntr_value
  FROM sys.dm_os_performance_counters
  WHERE counter_name IN ('Total Server Memory (KB)', 'Target Server Memory (KB)');

-- SQL Server 2012:
SELECT physical_memory_kb FROM sys.dm_os_sys_info;

-- Prior versions:
SELECT physical_memory_in_bytes FROM sys.dm_os_sys_info;

EXEC sp_configure 'max server memory';


Can't rename columns in PostgreSQL views with CREATE OR REPLACE

I can reproduce your error ... in my case, I created a column first as 'date' then as 'x' (was trying to see if it was an issue with a reserved word; it wasn't:

ERROR:  cannot change name of view column "date" to "x"

If you issue a drop view first, it'll let you re-create the view with a changed name.  I have no idea why create or replace won't do it.

Clarification by Colin 't Hart:

The documentation for CREATE VIEW explains it pretty well, I think:

  The new query must generate the same columns that were generated by the existing view query (that is, the same column names in the same order and with the same data types), but it may add additional columns to the end of the list.


Do what I do.  Build PHP web pages with the common stuff.  And have each page do more than the basic.  Don't just show tables, but do show table status and lay it out in a readable table.  And maybe sum up the columns.  And show the Stored Routines.  The cost of a dozen queries to build such a page saves more keystrokes than you concept of aliases.  And it is fast enough to not be a problem if you rarely look at most of the stuff on the page.

The show databases page can have the names hyperlinked to your show table stuff page.  That turns a bunch of keystrokes into a single link.

If you are looking at a table, and you know which columns are "normalized", then automatically do the JOIN to turn ids into values.

For show create table, also do show indexes and look into innodb_table_stats.  Etc.  The Partition info is hidden away; grab it automatically.

For show slave status, have it connect to the Master, too.  And all the Slaves.  And format it so that you can see their states side by side.  Etc.


In a scenario like this, there are often two completely separate kinds of "user accounts".

First you have your database's user account - this is (or at least, should be) required for the webserver to access the database.

Then, you have your application's user accounts. Everyone using the application should have their own account.

While it may be possible to assign individual user accounts for all application users to the database itself, this is often not practical. In most cases, I would keep these two things separate.

So, your application would have its own user table, with passwords and other user information. I'd also have a role table, listing out the types of users you can have: sales, artist, or customer. You would add the id of the appropriate row in the role table into the user table, and would determine what options are provided on-screen based on those roles.

At the database level, you could use just one user. That user has sufficient access to the database to run any commands any user might need; control of which users can run which commands is based on the application-level user.

You could have 4 users. One, "login" (use better names, by the way) would only be able to access the information required to authenticate your application-level users. Once they're authenticated, you could have one user for each of the three roles, with their rights within the database limited by what's needed for that role.

Practically speaking most websites I've worked on (including e-commerce sites) have used a single account for all DB connections, and controlled access at the application level.

If this is for a class assignment, and the class isn't about security, then I'd keep things simple (at first at least - you can play around if you've finished the basics early - you don't want an over-complicated security system to prevent you from completing the basic course requirements). Worry more about SQL injection attacks and that sort of thing; even with the four DB user solution suggested, one artist could potentially use a SQL injection attack to destroy other artists' data.


Concurrency with Select-conditional Insert/Update/Delete - PostgreSQL

Regarding the SERIALIZABLE transactions, be sure to pay attention to the "for optimal performance" tips near the bottom of this section: http://www.postgresql.org/docs/9.1/interactive/transaction-iso.html#XACT-SERIALIZABLE  These can reduce your rollback rates quite significantly.

That said, and not to discourage use of the SERIALIZABLE transaction isolation level, what you describe sounds like exactly the sort of problem that EXCLUSION CONSTRAINTs were designed to solve.  Where there is a declarative constraint to enforce a business rule, that is almost always better than using imperative coding (in triggers or application code).

http://www.postgresql.org/docs/9.1/static/ddl-constraints.html#DDL-CONSTRAINTS-EXCLUSION

http://www.postgresql.org/docs/9.1/static/sql-createtable.html#SQL-CREATETABLE-EXCLUDE

To use this feature with dates you would currently need to use the TEMPORAL module:

http://pgfoundry.org/projects/temporal/

You will probably want to migrate TEMPORAL columns to a RANGE when you move to PostgreSQL version 9.2 (expected to be released this summer).  The 9.2 RANGE feature, the TEMPORAL data type, and the EXCLUSION CONSTRAINT feature were all the work Jeff Davis to address problems like that posed in the question.  They were designed to work together.

Full disclosure: The SERIALIZABLE transaction behavior in 9.1 was developed by myself and Dan R.K. Ports of MIT.


PostgreSQL replication with periodic log shipping

This was most probably an issue on my part. I decided to do everything again, but this time making a full streaming replication following the excellent guide here: https://www.youtube.com/watch?v=Zq4zS0ZbXsc

Then, I stripped this solution from the streaming part (removed connection from hba file and a line from recovery.conf). Just in case someone would want to achieve a similar solution - do not compare WAL files by their 'Last Modified' date! You are interested in copying WALs newer by their 'Create' date. This is due to the fact that PostgreSQL creates some files and then for its own reason changes their 'Last Modified' dates to the ones in the past.


How I can corrupt a data base page and again restore the same

I love this question!

To demo, I'm going to use a local copy of the Fitness database from the Stack Overflow data dump.

First, make sure your database is in FULL recovery, and take a full and log backup.

BACKUP DATABASE [FITNESS]
TO  DISK = N'D:\Backup\FITNESS.bak'
WITH FORMAT, INIT, STATS = 1, COMPRESSION, CHECKSUM
GO

BACKUP LOG [FITNESS]
TO  DISK = N'D:\Backup\FITNESS.trn'
WITH FORMAT, INIT, STATS = 1, COMPRESSION, CHECKSUM
GO

Next, head into your database, and grab a page to corrupt. You should be able to use this query on 2012 plus. It creates a simple dynamic block with DBCC WRITEPAGE

USE FITNESS

SELECT TOP 1
    N'DBCC WRITEPAGE ('
  + CAST([dddpa].[database_id] AS NVARCHAR(10)) + N', '
  + CAST([dddpa].[allocated_page_file_id] AS NVARCHAR(10)) + N', '
  + CAST([dddpa].[allocated_page_page_id] AS NVARCHAR(10)) + N', '
  + N'0' + N', '
  + N'4' + N', '
  + N'0x0000008A'
  + N', 1) WITH NO_INFOMSGS' AS [cmd]
FROM
    [sys].[dm_db_database_page_allocations](DB_ID(), NULL, NULL, NULL, 'DETAILED') AS [dddpa]
WHERE [dddpa].[allocated_page_page_id] > 250;

After that, you'll need to set your database to SINGLE_USER and corrupt the page

ALTER DATABASE FITNESS SET SINGLE_USER WITH ROLLBACK IMMEDIATE

DBCC WRITEPAGE ( INSERT YOUR DBCC WRITEPAGE COMMAND HERE )

ALTER DATABASE FITNESS SET MULTI_USER WITH ROLLBACK IMMEDIATE

Remember to take note of which page you're overwriting. For me, it was page 280. Now I can run a restore sequence like this:

USE master

RESTORE DATABASE FITNESS
PAGE='1:280'
FROM DISK = N'D:\Backup\FITNESS.bak'
WITH NORECOVERY;

RESTORE LOG FITNESS
FROM DISK = N'D:\Backup\FITNESS.trn'
WITH NORECOVERY;

BACKUP LOG [FITNESS]
TO  DISK = N'D:\Backup\FITNESS_1.trn'
WITH FORMAT, INIT, STATS = 1, COMPRESSION, CHECKSUM, NORECOVERY

RESTORE LOG FITNESS
FROM DISK = N'D:\Backup\FITNESS_1.trn'
WITH NORECOVERY;

RESTORE DATABASE FITNESS
WITH RECOVERY;

If you want more practice, I highly recommend Steve Stedman's database corruption challenge.

Enjoy!


What are the real life uses of role based authentication control in a database?

You can group several privileges into one Role. It is simpler to grant roles rather than dealing with a bunch of privileges.

In a multi-tier architecture this might be no so important. You may have a user admin, backup, http - that's it.

However, when your database is used by many (human) users distributed over several departments with different responsibilities then Roles make your life much easier.

You create (or use built-in) roles like backup, clusterAdmin, developer, reporter, powerUser, etc. and grant such roles to users. You would have serious security flaws if every user would get full admin privileges on entire database.


Normalising shared entities and applying constraints

The motivation for piling every kind of address into a single table is usually a misinterpretation and misapplication of the notion of code reuse.

People can make the mistake of assuming that because you have two entities with some common set of attributes, that those attributes belong in their own table.  Sometimes entities have similar or identical columns coincidentally.  One wouldn't create a table for every NAME or DESCRIPTION or EFFECTIVE_DATE in your database, at least I hope one wouldn't be tempted to do this.

Some people mistakenly refer to all instances of removing columns out to their own table as normalisation.  Normalisation involves removing columns to their own tables, but not every instance of removing columns in this way is actually normalisation.  Normalisation prescribes very specific reasons for removing columns from a table.  If none of these reasons are applicable then you aren't normalising you're just making things complicated.

You have two correct ways of thinking about this:  Either your addresses all belong in one pile because you have an entity super-type that incorporates all of the common features of several entity subtypes, including addresses, - or - your addresses belong in separate piles (tables) according to each kind of thing that has an address and you write your procedural code against an IAddress interface which is implemented for each address table.

If you actually have an entity super-type, say LEGAL_ENTITY which has subtypes like CUSTOMER, VENDOR, EMPLOYEE and so forth, then having an ADDRESS table that is a child of LEGAL_ENTITY is a legitimate approach.  It may even be a valuable approach if there is significant overlap between your customers, vendors, employees (or whatever you're tracking) because you can change addresses once instead of in multiple locations when a legal entity moves.  On the other hand, if you don't have such a super-type, then you are going to face the problems pointed out by Richard Tallent.

If you keep your addresses in different tables according to what type of entity owns the address, then you can still achieve code reuse, assuming the language you are using supports interfaces.

As an aside: tvCa pointed out in a comment that addresses may be stored as columns rather than as rows in a separate table.  That will depend largely on how many addresses you need for each entity.  If you are tracking two addresses (physical, mailing) or if you are storing address history then go with an address table.  If you only store one address per addressee, then a separate table is likely overkill.


You only need some small changes. First add a UNIQUE constraint on calendars_entry_types (this is needed for the new foreign key below):

UNIQUE (calendar_id, entry_type_id)

Then remove these two foreign keys from entries:

FOREIGN KEY (calendar_id) REFERENCES calendars (id),
FOREIGN KEY (entry_type_id) REFERENCES entry_types (id),

and replace them with a composite one:

FOREIGN KEY (calendar_id, entry_type_id)
    REFERENCES calendars_entry_types (calendar_id, entry_type_id),


How to handle dimension hierarchies that re-join?

You can have more than one hierarchy on a dimension.  Your time dimension could have a year-quarter-month-day hierarchy and a year-week-day hierarchy.  The hierarchies can share the same 'day' attribute - set up attribute relationships for both of the hierarchies, and 'week' and 'month' can both be attributes of 'day'


SQLAZURE Resource limitations-Max concurrent sessions

When a task goes parallel it is still considered a single session with multiple request.

Hopefully the following will clear you confusion about the 2 metrics. I

Ref: https://social.msdn.microsoft.com/Forums/en-US/c073c554-03a3-43b5-9052-4de63d1f44d7/logins-versus-sessions?forum=ssdsgetstarted

Lets say you have 10 developers working on your app and they all connect to Azure SQL using SSMS. They will create 10 concurrent logins and 10 sessions in Azure SQL.

Now let's say that your app is using one login for connecting to Azure SQL and you have 100 concurrent users on your app. This will create one concurrent login and 100 concurrent sessions in Azure SQL.

If you want to baseline your concurrent session this is a great article. You might have to tweak it for Azure SQL Database.


How badly do SQL Compilations impact the performance of SQL Server?

SQL Compilations/sec is a good metric, but only when coupled with Batch Requests/sec.  By itself, compilations per sec doesn't really tell you much.

You are seeing 170.  If batch req per sec is only 200 (a little exaggerated for effect) then yes, you need to get down to the bottom of the cause (most likely an overuse of ad hoc querying and single-use plans).  But if your batch req per sec is measuring about 5000 then 170 compilations per sec is not bad at all.  It's a general rule of thumb that Compilations/sec should be at 10% or less than total Batch Requests/sec.

If you really want to drill down into what's being cached, run the following query that utilizes the appropriate DMVs:

select
    db_name(st.dbid) as database_name,
    cp.bucketid,
    cp.usecounts,
    cp.size_in_bytes,
    cp.objtype,
    st.text
from sys.dm_exec_cached_plans cp
cross apply sys.dm_exec_sql_text(cp.plan_handle) st

To get all single-use plans (a count):

;with PlanCacheCte as
(
    select
        db_name(st.dbid) as database_name,
        cp.bucketid,
        cp.usecounts,
        cp.size_in_bytes,
        cp.objtype,
        st.text
    from sys.dm_exec_cached_plans cp
    cross apply sys.dm_exec_sql_text(cp.plan_handle) st
)
select count(*)
from PlanCacheCte
where usecounts = 1

To get a ratio of how many single-use count plans you have compared to all cached plans:

declare @single_use_counts int, @multi_use_counts int

;with PlanCacheCte as
(
    select
        db_name(st.dbid) as database_name,
        cp.bucketid,
        cp.usecounts,
        cp.size_in_bytes,
        cp.objtype,
        st.text
    from sys.dm_exec_cached_plans cp
    cross apply sys.dm_exec_sql_text(cp.plan_handle) st
    where cp.cacheobjtype = 'Compiled Plan'
)
select @single_use_counts = count(*)
from PlanCacheCte
where usecounts = 1

;with PlanCacheCte as
(
    select
        db_name(st.dbid) as database_name,
        cp.bucketid,
        cp.usecounts,
        cp.size_in_bytes,
        cp.objtype,
        st.text
    from sys.dm_exec_cached_plans cp
    cross apply sys.dm_exec_sql_text(cp.plan_handle) st
    where cp.cacheobjtype = 'Compiled Plan'
)
select @multi_use_counts = count(*)
from PlanCacheCte
where usecounts > 1

select
    @single_use_counts as single_use_counts,
    @multi_use_counts as multi_use_counts,
    @single_use_counts * 1.0 / (@single_use_counts + @multi_use_counts) * 100
        as percent_single_use_counts

As for durations captured through a SQL Server Trace, it is not available for the Recompile events.  It isn't so significant to see the duration or pain that plan compilation is causing, as there's not much you can do for a case-by-case situation.  The solution is to attempt to limit compilations and recompilations through plan re-use (parameterized queries, stored procedures, etc.).


How are you migrating the database? Copying a database to another server via Backup/Restore or Detach/Attach will include the Assemblies as well as the T-SQL wrapper objects that point to the code in the Assemblies.  Using a tool that lets you select object types might require that you at least verify that Assemblies have been selected to migrate.

If all of your Assemblies have a PERMISSION_SET of SAFE then you should be fine with no additional steps (outside of the obvious enabling of CLR Integration as mentioned in another answer).

If any of the Assemblies have a PERMISSION_SET of either EXTERNAL_ACCESS or UNSAFE then you will need to do a few extra steps:

If you are using the non-preferred but easier and hence more common method of setting the database property of TRUSTWORTHY to ON, then you will need to ensure that it is set to ON on the new server. I would expect the Backup/Restore method to carry this setting over correctly, but other methods might not, especially if they create the database new on the destination.
If you are using the preferred method of having an Asymmetric Key-based Login, then those pieces live in the [master] database and you need to make sure they are copied or recreated on the destination:

Make sure that all Asymmetric Keys (that are used for CLR security) exist in [master] on the destination
Make sure that all Logins based on those Asymmetric Keys exist in [master] on the destination
Make sure that those Logins have the same server-level permissions on the destination: GRANT EXTERNAL ACCESS ASSEMBLY and/or GRANT UNSAFE ASSEMBLY

Regardless of which of the two options noted above you are using, make sure that the database owner SID matches between what is shown in the database properties and what is recorded in the [master] database meta-data. This is usually not an issue if creating the destination DB and then copying objects into it, but if using Backup/Restore then there can be a mismatch in DB owner SIDs, and that will prevent CLR code from running
I am less certain of this one as I haven't encountered it in a few years, but if the DB owner SID does change, then for any Assemblies that have a PERMISSION_SET of either EXTERNAL_ACCESS or UNSAFE, you might need to at least reset the PERMISSION_SET property to SAFE and then back to what it was originally. If that doesn't help then you might need to DROP and reCREATE the Assemblies and the associated T-SQL wrapper objects.

It would also be a good idea to make sure that any of the pre-4.0 versions of the .Net framework that exist on the current / source server also exist on the destination server. It won't matter if additional framework versions exist on the destination, but it will make the transition smoother if there is at least nothing missing. Even though SQL Server 2005 - 2008 R2 is statically linked to the .Net 2.0 series (2012 and 2014 are statically linked to the 4.0 series) it is possible that an Assembly can require 3.0 or 3.5. If that happens and those framework versions have not been loaded then you will get a cryptic message that will not imply that a .Net framework version is missing. But any version that is not on the source server is clearly not needed if the CLR objects currently function.


Altering db schema in production environment

It really depends how big, complicated and active your database is. Bear in mind that while you are making schema changes, many of the tables will be read-only or completely inaccessible until the changes are completed. Also bear in mind that the reverse is true - your schema changes will need to acquire exclusive locks on entire tables and possibly all their relations, which may take a long time or even be impossible to acquire if the application gets in the way. Theoretically this can result in circular locks which will freeze the whole DB. Basically, if there are a lot of reads and writes going on and changes are going to cause significant disruption to your application, then you should schedule downtime for it instead.

Then you have to account for the changes your application will require. Will changing the schema require changes in the application too? Is your application small enough or modular enough that you can make those changes in increments as you change the database? Ultimately, making changes in production is really hard. Unless your app is designed for it from the ground up, it's almost universally not worth the extra effort required for the sake of some small downtime.

The other option to minimise downtime is to build a new database with an improved schema and a new app server connected to it, then build a widget to sync the data between the new database and the old one. When you are ready to deploy you simply direct traffic to the new app server. Again this is a tradeoff between how hard it is to create the system and how hard it is to accept some downtime. There are very few applications that cannot tolerate an hour or two with a "Scheduled Maintenance" message starting 2am on a tuesday night.


MariaDB has a user statistics plugin that provides a breakdown per user.
It can be enabled dynamicly:
SET GLOBAL userstat=1;

And examined:
SELECT * FROM INFORMATION_SCHEMA.USER_STATISTICS\G

It seems like such a increase is potentially a brute force attempt or some abnormal use. I'd look at the SHOW PROCESSLIST and see if the queries being run are extraordinary and/or you'll probably see there a client with more connections than expected.


My personal opinion, is that there isn't much sense in using OODBMS over RDBMS.

Due primarily to a lack of a standard language, comparable to that of SQL, OODBMS are often niche, proprietary, commercial systems. You'll be hard-pressed to find a viable option for a language like PHP because of that. You'd probably have better luck finding something if you were using Java or C++. I did find this comparison table, which might be of interest to you; though it looks like it was last updated in 2009; so be mindful.

Perhaps you could consider instead, a RDBMS + ORM solution using Doctrine.


Why does SQL Server require a private key to encrypt a backup?

I just don't get why the private key is needed during the backup operation.

Short Answer

That's how it's currently implemented.

Longer Answer

I didn't look at all the dependencies to see if it would be required (logically it wouldn't be needed to encrypt the data, just decrypt as you've already stated) for all of the other calls, but it is checked and hence the error given how it is currently written and implemented.

If you're worried about having the keys on the same server or multiple servers in order to do backup operations, you may want to look into using a HSM which does integrate with backup encryption.

  You can integrate encryption keys with Extended Key Management (EKM)
  providers.


Does Amazon Web Services (AWS) RDS support locally redundant storage (LRS) for Microsoft SQL databases?

Amazon doesn't call it LRS, but the Elastic Block Store (EBS) storage which backs RDS (see RDS FAQ, "What is the hardware configuration...") is automatically replicated across multiple servers (see EBS product page) within the same Availability Zone (i.e. locally).

Data resilience is extremely high for RDS databases.


Reporting Services will not connect to SQL Server

Community Wiki answer to document the solution added by the question author in a comment:

The old SQL Server was listening on a port other than 1433.

Once I used the IP connection string with the right port, all was well.


How to drop multiple tables with common prefix in one query?

You can build up a string using the catalog views, e.g.:

DECLARE @sql NVARCHAR(MAX) = N'';

SELECT @sql += '
DROP TABLE '
    + QUOTENAME(s.name)
    + '.' + QUOTENAME(t.name) + ';'
    FROM sys.tables AS t
    INNER JOIN sys.schemas AS s
    ON t.[schema_id] = s.[schema_id]
    WHERE t.name LIKE 'LG_001%';

PRINT @sql;
-- EXEC sp_executesql @sql;

Of course there are potential gotchas, for example if these tables have foreign key relationships, you'll either need to drop them first, or arrange the output to drop the tables in a certain order.

To just get the list of tables, use:

SELECT s.name, t.name
  FROM sys.tables AS t
  INNER JOIN sys.schemas AS s
  ON t.[schema_id] = s.[schema_id]
  WHERE t.name LIKE 'LG_001%';


It's quite a few rows, and the table is quite wide - so I'd be interested to how much data is being pulled back. You can get a good idea by capturing it in SQL Server Profiler, or from identifying the SPID, then watching the logical_reads column in sys.dm_exec_requests just before the query completes on modern versions of SQL Server. This measures 8KB pages, so divide by 128 and 1024 to get the approx size in GB. You'll probably find it's quite a chunky read like 100GB! And 100GB will always take some time to come off disc!

Following a more business approach would be having SLAs. Hopefully, it might be documented that this business action (eg "add customer") should take a maximum of 2 seconds for the system, and that business action (eg "find address") would also take 2 seconds. As long as the app is meeting or exceeding these requirements, a select * from the entire table won't matter.


Problem solved

Very hacky but got the system back

Set the deadlock priority to high to stop it being auto killed

there are a lot of external apps that access the DB which ended up blocking the drop index (information from the master..sysprocesses table) (but not reporting as blocking through all the checks we have), so I just killed until there was nothing more in its way, dropped straight away

cheers for the quick eyes over