Why does my query run faster after I force full table scan? I have a MERGE statement that took forever to execute the select as the table it is running from (SUB_DATA) is rather big (read 1.4 Tb, 700 million rows). My SELECT looks like this.

SELECT MIN(TRUNC(DATETIME,'DD'))    AS FIRST_ACTIVITY,
       MAX(TRUNC(DATETIME,'DD'))    AS LAST_ACTIVITY,
       NVL(PHONE_NUMBER, '-1')      AS PHONE_NUMBER,
       MAX(NVL(PHONE_ID, '-1')) KEEP (DENSE_RANK LAST ORDER BY DATETIME) AS LAST_PHONE_ID,
       MAX(TYPE_ID) KEEP (DENSE_RANK LAST ORDER BY DATETIME)             AS TYPE_ID
FROM SUB_DATA   AD,
     SUB_PROF   ICP
WHERE DATETIME BETWEEN minDate AND maxDate
AND   PROFILE_ID = ICP.ICP_SEQID
AND   REF_RT_ID in (13, 63)
AND   DURATION > 3
AND   PHONE_NUMBER like '123%'
GROUP BY NVL(PHONE_NUMBER, '-1')

There are two INDEXES on the table, one on PHONE_NUMBER and one on another column not used here.

I struggled to get the query to run faster and one of the more senior guys at work suggested I force a FULL TABLE SCAN. I did this and it worked wonders.

Even though it worked, it goes against the general concept that full table scans are bad.

Could someone please explain why this is the case?

[...] it goes against the general concept that full table scans are bad.

This has exceptions, like all general concepts. A full table scan can be less expensive than an index scan followed by table access by rowid - sometimes much less expensive.

Remember that:

Index blocks need to be read too, in addition to the table's blocks. If the index is large (a sizable percentage of the table's size), the pressure on the SGA (and associates latches) might be noticeable
If the sort order of the index doesn't match the way the data is stored in the actual table, the number of logical I/Os necessary to fulfill the query could be (potentially much) more than the number of LIOs to do the full table scan. (The index's clustering factor is one of the indicators you can look at to estimate this.)

I find the figures in this article Oracle clustering_factor tips explain the issue pretty well.

Essentially, if scanning via your index makes the data block I/O requests jump all over your table, the overall cost is going to be higher than if you could do large sequential reads. An FTS is more likely to use multi-block direct-path reads, bypassing the SGA entirely, which is also potentially good - no "cache thrashing", less latching.

If you have a covering index, chances are that's going to beat a full table scan all the time. If not, it's going to depend on what percentage of actual blocks (data + index) will need to be processed (the index's selectivity for that query), and how well they're "physically sorted" with respect to each-other.

As to why the optimizer is picking the "wrong" path for you here: hard to tell. Stale statistics on the index or table could be an issue like always, the estimate calculated base on the LIKE might be off for certain patterns, instance parameters could favor indexes a bit too much, ... If this is the only query that's misbehaving, and your stats are up to date, using a /*+ full */ hint doesn't sound too bad.


Enforce constraint name uniqueness I just noticed that PostgreSQL allows repeating foreign key (and possibly other constraint) names across the database as long as they are on a different table.

So if the parent table is Foo and it has two child tables BarX and BarY, the foreign keys on both tables can be named FK_fooid.

This, IMO, is a terrible design because

select * from information_schema.referential_constraints
where constraint_name = 'FK_fooid'

will return two identical rows with no ability to differentiate which one corresponds to which table/key.

Is there a way to disable repetition of constraint names across a database in Postgres and to enforce that each constraint has a unique name?

will return two IDENTICAL rows with no ability to differentiate which one corresponds to which table/key.

Then don't use information_schema:

select nsp.nspname as constraint_schema,
       c.conname as constraint_name,
       format('%I.%I', ts.nspname, t.relname) as target_table,
       pg_get_constraintdef(c.oid) as constraint_definition
from pg_class t
  join pg_constraint c on t.oid = c.conrelid
  join pg_namespace nsp on t.relnamespace = nsp.oid
  join pg_namespace ts on t.relnamespace = ts.oid
where c.contype in ('f')
 and c.conname = 'fk_fooid';

Example: http://rextester.com/DAR30737


Which column type for "direction"? I want to create a table which has a column named "direction".

This column is either "in" or "out". It is not nullable and it can only contain these two values. Never other values.

Which data type is most feasible?

I use in PostgreSQL. It is 100% ok if the data type is PG specific (only known to this database implementation).

You can create an enumerated type for this:

CREATE TYPE direction AS ENUM ('in', 'out');

CREATE TABLE a_table (
  ID        serial,
  direction direction NOT NULL
);

INSERT INTO a_table (direction) VALUES ('in');        -- succeeds
INSERT INTO a_table (direction) VALUES ('sideways');  -- fails


Is SELECT ROW_NUMBER() guaranteed to return results sorted by the generated row numbers? E.g. consider the SQL query:

SELECT
   A.[Name],
   ROW_NUMBER() OVER(ORDER BY A.[Name] ASC)
FROM
   [FooTable] AS A

Here I observe the results being returned sorted by A.[Name]. If I change the sort column defined in the ROW_NUMBER function to another column then again the results become sorted by that column.

I was expecting the row number to be assigned to the rows but I was not expecting the rows to come back sorted by that same criteria. Is this simply a side effect of how the query is being executed (in my case on SQL Server 2008 R2) or is this behaviour guaranteed? (I could find no reference to such a guarantee).

Absolutely not. Proof:

SELECT
   A.[Name],
   ROW_NUMBER() OVER(ORDER BY A.[Name] ASC),
   ROW_NUMBER() OVER(ORDER BY A.[Name] DESC)
FROM
   [FooTable] AS A

The only way to guarantee an order in SQL is to ask for it, use  ORDER BY on the result itself.


Why don't my .my.cnf settings work? I have a .my.cnf file in my home directory on my desktop that includes:

[dbid]
user = myusername
password = mypassword
database = dbname
host = server.location.com

If I recall correctly, before upgrading from Ubuntu 10.04 to 11.04, I was able to use the command

mysql dbid

To connect directly to the database

But today I get this error:

ERROR 1049 (42000): Unknown database 'dbname'

Have I done something wrong?

I'm not sure how your previous .my.cnf used to work, and I actually have never used these files before (mainly because I didn't know about them). So after a bit of research, I found this link and came up with the following ~/.my.cnf that worked for me:

[clientdbid]
password = mypass
database = dbname
host = server.location.com

and the command that reads it:

mysql --defaults-group-suffix=dbid

A couple things to point out (highlights from the article linked):

group has to be preceded by 'client' to be read by mysql
has to go after any [client] groups, otherwise it will be overridden

I tested this on mysql 5.5 on a Mac, worked great. And now that I know about them, I will use them!

UPDATE
After I set this up, I realized that the command line mysql --defaults-group-suffix=dbid was a little hefty. So as added bonus, assuming you're running Linux/Mac/Etc do this:

echo 'alias mysql_dbid="mysql --defaults-group-suffix=dbid"' >> ~/.profile

Where dbid is the name of your suffix group.


SP2-0750: You may need to set ORACLE_HOME I am getting following error message when i write sqlplus on command prompt

SP2-0750: You may need to set ORACLE_HOME to your Oracle software directory

Here are the path details

I restarted my PC and tried setting ORACLE HOME from Command Prompt as SET ORACLE_HOME=... and it worked

Further more, i did following two things

1-ORACLE_HOME should not contain anything after the path i.e. no \, ;,,

2-I added an environment variable named LANG with value American_America.we8iso8859p15


SQL SERVER - Linked Server and query performance I have a performance problem related to SQL Server/linked servers/views.
I hope you can help me understand what is the best way to do what I want =).

I have a database K with 3 linked servers L1,L2,L3 to 3 databases X,Y,Z.
In X,Y,Z i have, respectively, 3 views called V1,V2,V3.
I want to query the union of V1,V2,V3 by database K with linked server L1,L2,L3.

After some testing, this is the situation:

In SSMS, if I run this pseudo query SELECT * FROM (L1.V1 u L2.V2 u L3.V3) WHERE some filters the performance is really great
If I create a view VK in database K, which contains the union of the three views and then i run the query SELECT * FROM VK WHERE some filters the performance is worse than case 1

Questions

Why is the performance so different?
How can I improve performance in case 2?

I'm interested in improving performance in case 2 because I need a view to map with nHbinernate in our software...
Thanks in advance, regards
UPDATE AFTER JOHN ALAN's POST
Ok, I try but with no results.
I'm not a DBA and my skills on DB configuration is really limited.
Can we proceed step by step?

On remote server (called Y) I created a new account (called linkedserver) by security->logins->new login. I select login name then sql authentication and I choose a password. for deafault database I select master. In server roles tab I select public. in User mapping tab I select the databases involved in remote queries and, for each of them, I select db_ddladmin and public role. Then, for each database involved in remote query, I checked the effective permission for linkedserver user and there are a lot of ALTER and a lot of CREATE permissions but not SHOW PLAN (then I selected this one too).

On the database server (called X) where the linked server to Y exists, I created a linked server (called L1). In security TAB, I selected local user sa and remote user linkedserver with its password.

When i run the query across L1 without a VIEW I have good result, If I put the query in a VIEW low performace, nothing have changed...
I think I did some step wrong, but I don't know where...
FOR BETTER CLARITY
This is the query I run without a view using linked server:
select * from
(
    select * from dolph.agendasdn.dbo.vistaaccettazionegrp
    union
    select * from dolph.acampanet.dbo.vistaaccettazionegrp
    union
    select * from municipio.dbnet.dbo.vistaaccettazionegrp
) a
where cognome = 'test' and nome = 'test'

into the view I put only this code
    select * from dolph.agendasdn.dbo.vistaaccettazionegrp
    union
    select * from dolph.acampanet.dbo.vistaaccettazionegrp
    union
    select * from municipio.dbnet.dbo.vistaaccettazionegrp

then I called select * from VIEW where cognome = 'test' and nome = 'test'
So..

1st case 0-1 seconds.
2nd case >15 seconds...

I think this is absurd!
EXECUTION PLAN
The execution plan with plain query, without the use of a view:

the execution plan using the view:

Your problem begins and ends with statistics and estimates. I have reproduced your situation on my servers and found some interesting hints, and a workaround solution.

First things first, let's take a look at your execution plan:
When a view is used we can see that a filter is applied after the Remote Query is executed, while without the view there was no filter applied at all. The truth is that the filter was applied inside the Remote Query, at the remote server, before retrieving the data over the network.
Well, obviously applying the filter at the remote server and thus retrieving less data is a better option, and obviously that only happens when not using a view.

So... what is so intersting...?

Surprisingly, when I changed the filter from cognome = 'test' to cognome = N'test' (unicode representation of the string) the view used the same execution plan as the first query did.
I guess the reason is that somehow when using the view SQL Server estimated that there will be a small number of rows returning from the (remote) query, and that a local filtering will be cheaper, but when SQL Server had to implicit convert NVARCHAR to VARCHAR, statistics could no longer be used and the decision to filter locally was not taken.
I have looked for the statistics locally, but the view had no statistics, so my guess is that the view uses the remote statistics in a way that ad-hoc query does not, and than takes the wrong decision.

OK, so what solves the problem?

I stated earlier that there is a workaround (at least until someone comes up with a better solution), and no, I don't mean using unicode for your strings.
I wanted to give an answer first, I still have to find why, but when using an Inline Function SQL Server behaves exactly the same as with the query (without view), so replacing the view with the function will give the same result, in a simple query, and with good peformance (at least in my environment).

My code suggestion for you is:

CREATE FUNCTION fn_anagrafiche2()
RETURNS table
AS
RETURN
(
    SELECT *
    FROM dolph2.agendasdn.dbo.vistaanagraficagrp
    UNION
    SELECT *
    FROM dolph2.acampanet.dbo.vistaanagraficagrp
    UNION
    SELECT *
    FROM municipio2.dbnet.dbo.vistaanagraficagrp
)
GO

The query will then be:

SELECT *
FROM fn_anagrafiche2()
WHERE cognome = 'prova'

This works on my servers, but of course test it first.
Note: I do not recommend using SELECT * at all, as it is prone to future errors, I simply used it because it was in your question and there was no need for me to change that when I can add this remark instead :)


MySQL - Delete row that has a foreign key constraint which reference to itself I have a table in which I store all the forum messages posted by the users on my website. The messages hierarchy strucrue is implement using a Nested set model.

The following is a simplified structure of the table:

Id (PRIMARY KEY)
Owner_Id (FOREIGN KEY REFERENCES TO Id)
Parent_Id (FOREIGN KEY REFERENCES TO Id)
nleft
nright
nlevel

Now, the table is looking something like this:

+ ------- + ------------- + -------------- + ---------- + ----------- + ----------- +
| Id      | Owner_Id      | Parent_Id      | nleft      | nright      | nlevel      |
+ ------- + ------------- + -------------- + ---------- + ----------- + ----------- +
| 1       | 1             | NULL           | 1          | 8           | 1           |
| 2       | 1             | 1              | 2          | 5           | 2           |
| 3       | 1             | 2              | 3          | 4           | 3           |
| 4       | 1             | 1              | 6          | 7           | 2           |
+ ------- + ------------- + -------------- + ---------- + ----------- + ----------- +

Note that the first row is the root message, and the tree of this post can be displayed as:

-- SELECT * FROM forumTbl WHERE Owner_Id = 1 ORDER BY nleft;

MESSAGE (Id = 1)
    MESSAGE (Id = 2)
        Message (Id = 3)
    Message (Id = 4)

My problem occurs when I try to delete all the rows under the same Owner_Id in a single query. Example:

DELETE FROM forumTbl WHERE Owner_Id = 1 ORDER BY nright;

The above query fails with the following error:

  Error Code: 1451. Cannot delete or update a parent row: a foreign key constraint fails (forumTbl, CONSTRAINT Owner_Id_frgn FOREIGN KEY (Owner_Id) REFERENCES forumTbl (Id) ON DELETE NO ACTION ON UPDATE NO ACTION)

The reason is that the first row, which is the root node (Id=1), also has the same value in its Owner_Id field (Owner_Id=1), and it causing the query to fail due to the foreign key constraint.

My question is: How can I prevent this foreign key constraint circularity and delete a row that reference to itself? Is there a way to do it without first having to update the the Owner_Id of the root row to NULL?

I created a demo of this scenario: http://sqlfiddle.com/#!9/fd1b1

Thank you.

Besides disabling foreign keys which is is dangerous and can lead to inconsistencies, there are two other options to consider:
Modify the FOREIGN KEY constraints with the ON DELETE CASCADE option. I haven't tested all cases but you surely need this for the (owner_id) foreign key and possibly for the other as well.

ALTER TABLE forum
    DROP FOREIGN KEY owner_id_frgn,
    DROP FOREIGN KEY parent_id_frgn ;
ALTER TABLE forum
    ADD CONSTRAINT owner_id_frgn
        FOREIGN KEY (owner_id)
        REFERENCES forum (id)
        ON DELETE CASCADE,
    ADD CONSTRAINT parent_id_frgn
        FOREIGN KEY (parent_id)
        REFERENCES forum (id)
        ON DELETE CASCADE ;

If you do this, then deleting a node and all the descendants from the tree is simpler. You delete a node and all descendants are deleted through the cascade actions:

DELETE FROM forum
WHERE id = 1 ;         -- deletes id=1 and all descendants

The issue that you stepped into is actually 2 issues. The first is that deleting from a table with self-referencing foreign key is not a serious problem for MySQL, as long as there is no row that references itself. If there is a row, as in your example, the options are limited. Either disable foreign keys or use CASCADE action. But if there are no such rows, deleting becomes a smaller problem.

So, if we decide to store NULL instead of the same id in owner_id, then you could delete without disabling foreign keys and without cascades.

You would then stumble upon the second problem! Running your query would raise a similar error:

DELETE FROM forum
WHERE owner_id = 1 OR id = 1 ;

  Error(s), warning(s):
  Cannot delete or update a parent row: a foreign key constraint fails (rextester.forum, CONSTRAINT owner_id_frgn FOREIGN KEY (owner_Id) REFERENCES forum (id))

The reason for this error would be different than before though. It's because MySQL checks each constraint after every row is deleted and not (as it should) at th eend of the statement. So when a parent is deleted before its child is deleted, we get a foreign key constraint error.

Fortunately, there is a simple solution for this, thnx to the nested set model and to that MySQL allows us to set an order for the deletes. We just have to order by nleft DESC or by nright DESC, which makes sure that all the children are deleted before a parent:

DELETE FROM forum
WHERE owner_id = 1 OR id = 1
ORDER BY nleft DESC ;

Minor note, we could (or should) use a condition that considers the nested model as well. This is equivalent (and might use an index on (nleft, nright) to find which nodes to delete:

DELETE FROM forum
WHERE nleft >= 1 AND nright <= 8
ORDER BY nleft DESC ;


SQL Server - redirect synonym without downtime I have multiple application databases that use synonyms point to a shared database with immutable data that needs to be updated periodically by restoring an external backup. We now have a new requirement where we can no longer take the application down for the duration of the update, so the plan to update the database is to:

Restore the updated shared database as shared_temp
Redirect all synonyms in application databases to shared_temp
Restore the updated shared database onto the "main" shared database
Redirect synonyms back to shared
Drop shared_temp

Since there doesn't seem to be an alter synonym statement, how would I perform these synonym redirects without a race condition where queries performed between drop synonym and create synonym fail to reference the shared database?

Is there any way to lock the synonyms and allow them to be recreated during the lock?

Alternatively is there a way to lock the whole database (or schema) in a way that incoming queries will be queued rather than rejected like in single user mode?

If all else fails, is there a reliable way to update the contents of all tables in a database from another without slowing down queries? Note that there are internal references in the data. Existing references are guaranteed to remain valid with the new data. As long as we don't downgrade to a previous version it should be safe.

Modifying statements that reference these synonyms is not an option.

Minimum SQL Server version is 2008 R2, although if newer versions make this possible then upgrading is an option.

We don't really care how long the process takes, just that there is no downtime. Dropping and renaming is not atomic and likely not lockable, otherwise that would be an option.

Answer left in comments by Dan Guzman

DROP/CREATE all the synonyms within a transaction in steps 2 and 4. There may be some (hopefully) short-term blocking, but no downtime.


Are there alternatives to Microsoft Access? In my office, we often use MS Access for many applications (eg creating reports, data analysis and data processing) because it is convenient to use (easy data import, fast query building, easy automatization with VBA)

Unfortunately, it starts to be insufficient for me due to the size limitations of the database file. This is one thing. Another thing is that Access is often unable to execute my complex SQL queries (I omit that the SQL used by Access is limited).
Is anyone out there aware of any tools similar to MS Access that can be used with databases over 2GB?

Possibly, I am considering using Access as a front-end, but what about the back-end? Can you recommend something?

Check out Linked Tables to SQL Server if you're running up against complexity requirements for your data.  Basically, as you are expecting, Access would still work as your front-end and SQL Server would work as the back-end.  For complex SQL statements, you can make use of Stored Procedures that you call from Access as well.  If nothing else, this could give you some more time to rewrite the Access application in something a little more robust like .NET (language/platform of your choosing).  Honestly though, if there's no pressing need to change out the Access application, keep it around if it's doing the job.  I support clients that run tons of business functionality off of an Access front end tied to a basic availability group on SQL Server Standard Edition for high availability.  It does the job just fine and it doesn't cost an arm and a leg.


What alternatives exist when a table requires too many foreign keys? We have a base table that defines parts and holds information like part number, description, price, weight, etc.  We also have approximately 400 tables that reference the base table and provide additional information about the parts based on their type / category.

We started by using foreign key constraints so that a part cannot be deleted from the base table if it is being referenced in one of the 400 part specific tables but we quickly reached the maximum 253 recommended foreign keys for SQL Server 2005.

Are there any alternatives to foreign keys in this situation that will ensure data integrity?  We haven't seen performance issues when accessing the data but updating an existing part in the base table will fail as the query plan is too complex.

If there's any way to group parts, you might be able to introduce intermediate tables as a workaround.  This won't work.

Parts
+ Table 1
+ Table 2
+ ...
+ Table 400

But something along these lines might.

Parts
+ RedOrangeYellow parts
  + Table 1
  + Table 2
  + ...
  + Table 200

+ GreenBlueIndigoViolet parts
  + Table 201
  + Table 202
  + ...
  + Table 400

I'd want to take a hard look at your DDL before I recommended doing this, though.  And if you do this, don't start throwing ID numbers all over the place. You ought to be able to join "Table 400" directly to "Parts" without including "GreenBlueIndigoViolet parts".


Group and order output into a single row I want to display each person's contact info and their commissions in a single row as opposed to multiple rows.

Commission Table

ID          COMMISSION  DATE
N12545233    774         4/1/15
N12545233    361         5/1/14
N12545233    685         6/1/15
N14521746   3384        10/1/14
N14521746   1188        11/1/14
N14521746   1256        12/1/14
N15981070   1188         1/1/15
N15981070    616         2/1/15
N15981070    936         3/1/15

Sales_Staff Table

ID          LASTNAME    FIRSTNAME   STREET          CITY        STATE   ZIP
N12545233   Jones       Mark        123 Main St     Brooklyn    NY      11377
N14521746   Greene      Phil        345 Broadway    Queens      NY      11237
N15981070   Stahl       Barbara     567 Lexington   New York    NY      10018

Desired Output

ID         LASTNAME FIRSTNAME   STREET        CITY      STATE   ZIP     COMMISSION  DATE      COMMISSION    DATE           COMMISSION   DATE
N12545233  Jones    Mark        123 Main St   Brooklyn  NY      11377   774          4/1/15    361           5/1/14          685         6/1/15
N14521746  Greene   Phil        345 Broadway  Queens    NY      11237   3384        10/1/14   1188          11/1/14         1256        12/1/14
N15981070  Stahl    Barbara     567 Lexington New York  NY      10018   1188         1/1/15    616           2/1/15          936         3/1/15

If you're running on MySQL:

SELECT
    c.ID,
    ss.LASTNAME,
    ss.FIRSTNAME,
    ss.STREET,
    ss.CITY,
    ss.STATE,
    ss.ZIP,
    GROUP_CONCAT(c.COMMISSION ORDER BY c.DATE SEPARATOR '\n') AS COMMISSION,
    GROUP_CONCAT(c.DATE ORDER BY c.DATE SEPARATOR '\n') AS DATE
FROM db1.Sales_Staff AS ss
JOIN db1.Commission AS c ON (c.ID=ss.ID)
ORDER BY ss.LASTNAME ASC,ss.FIRSTNAME ASC;


Select only those rows based on specific condition of user id Below is an example of child's activity table. I only want to see all rows after 1st Tv activity for each user id. I tried grouping but it is not working for me

Table

ID          Timestamp               Activity
567     1541300537817000            Games
567     1541300584959000            Tv
567     1541300711884001            Play
567     1541300749548000            Tv
567     1541300804865000            Cartoon
678     1542073550481000            Computer
678     1542073551422000            Tv
678     1542073551478000            Study
678     1542073561885000            Tv
678     1542073567663000            Sleep
908     1543580126943000            Tv
908     1543580212409000            Sleep
908     1543580245227000            Play

Expected Output

567  Play
567  Tv
567  Cartoon
678  Study
678  Tv
678  Sleep
908  Sleep
908  Play

SELECT DISTINCT t1.*
FROM `table` t1, `table` t2
WHERE t1.id = t2.id
  AND t1.timestamp > t2.timestamp
  AND t2.activity = 'Tv';


Make a query fail in SSMS for higher versions of SQL Server? I have SQL server 2014 running on my computer and a database that has a compatibility_level of 100 (SQL Server 2008). I thought that that would be sufficient for writing queries in SSMS that only work for SQL Server versions 2008 and lower but for some reason SSMS runs queries that use OVER (PARTITION BY ...) clauses (with the BETWEEN argument) which are only for 2012 and up.

Our software needs to be compatible with 2008 R2 and up. Is there a setting that I can change that will cause my queries to fail in SSMS if I write a query that is not compatible with 2008 R2?

Besides doing your research on what features you should deliberately not use, the only reasonable solution here is to have a robust testing structure where you run your application against all possible versions of SQL server that you're intending to be compatible with.

Even for queries that might work on multiple SQL versions, they might behave slightly differently.

99 times out of 100, you're unlikely to see new versions of SQL break existing code, but there is a difference between "(shrug) it'll probably work" vs "my application is fully tested and supported on the following SQL versions:..." If you're throwing a helpful script on your blog, the former is probably ok. But if you're selling and supporting a commercial application, you definitely want the latter.


Relating two fields of one table to one field of another I have two tables that look as such:

Table1               Table2
ID | Appname         ID  | App  | Dependency
------------         -----------------------
1  | App1            1   | 2    |  3
2  | App2            2   | 3    |  1
3  | App3            3   | 2    |  1
etc....               etc...

Basically an application can have one or more dependencies that must be complete prior to executing. In Table2, both App and Dependency relates to the ID field of Table1, but I'm having a difficult time linking these together with SQL without getting errors.

What is the correct way to join the tables so that I get results that look like this:

Dependencies:
App2: App3, App1
App3: App1

(doesn't have to be laid out like that, but if it can be, that would be great)

Use view designer, add Table2, then add Table1 two times.

Select Table1.ID and drag till Table2.App and do the same with Table1_1 and Table2.Dependency.

SELECT Table2.Id,
       Table2.App,
       Table2.Dependency,
       Table1.Appname,
       Table1_1.Appname
FROM
       Table1 AS Table1_1
INNER JOIN
       (Table1 INNER JOIN Table2 ON Table1.Id = Table2.AppId)
       ON Table1_1.Id = Table2.Dependency;


Return average using same table data I've got this query I'm testing with:
select sum(units) / (select count(distinct week) from record_sales_data where customer_country_id=2 group by customer_country_id) as average
from record_sales_data
where customer_country_id=2
group by week,customer_country_id

Which returns what I want for country 2 of course. Now I'd like to return the average for ALL countries, but I don't seem to find a way to return by all countries, grouped by country id..
In the example above, here's what country 2 looks like in the raw data: (there are many more countries of course)
year|month|week|customer_country_id|units|
----|-----|----|-------------------|-----|
2020|    5|  21|                  2|    1|
2020|    5|  21|                  2|   36|
2020|    5|  22|                  2|   -1|
2020|    5|  22|                  2|   -1|

I apologize if this is a simple to resolve question but I'm still learning SQL and can't figure out an easy way to come up with a solution to this.
Thanks!

I just solved it by doing this:
select sum(units) / (select count(distinct week) from record_sales_data rs2 where rs.customer_country_id=rs2.customer_country_id group by customer_country_id) as average
from record_sales_data rs
group by customer_country_id


Necessity of a DB-replication as extra security layer I am confronted with a given infrastructure as following:
A "main" DB has a master-slave replication DB, which only contains certain tables.
A certain DB-user has read-only access to this replication.
A certain application currently uses this user to perform read-operations on the replication.
To my understanding this concept was introduced as security measurement. I am thinking that it might not be neccesary at all and a simple Read-Only user on the "main" DB would be enough.
I am not sure whether I am overlooking a crucial advantage of the current setup. The pros and cons I see:
Pro replica:

Extra layer of security?
If the application (publicly accesable) is attacked / DDoS´d, only the connected replica DB will be affected and the main DB can operate normally

Con replica:

Extra hardware ressources needed. These could be used to increase the main DB peformance
Currentness of data will always be slightly lagged (a few seconds)
Higher maintenance

I know this information might not be enough to properly evaluate the situation, but I feel like I am missing a crucial point. Currently I would like to remove the replication DB, setup Read-Only users on the main DB and increase its performance.
Any thoughts?

If the application (publicly accessible) is attacked / DDoS´d, only the connected replica DB will be affected and the main DB can operate normally

That's the reason.
If the Application running against the replica database is publicly accessible, then it can be attacked.  If it is attacked then, as you say, the replica database will bear the brunt of that attack.  The remainder of the company, running against the primary database, can continue operating [largely] as normal.
As long as the web application is of secondary importance to your company, then this arrangement makes sense.  If you work for eBay or Amazon?  Not so much!
That's not to say that the primary database is completely unaffected by this!  The replication processes, trying to write to the over-loaded replica, may be impacted too, which may feed back into poorer performance on the primary or, depending on how the replication system works, potential disk space problems with logs not being shipped and cleared down as quickly as they should be.


SQL Server Error while creating User I tried creating a user with all privileges but I am getting this error:

  Microsoft SQL Server Management Studio

  Create failed for User 'Hru'. (Microsoft.SqlServer.Smo)

  Additional information:

  An exception occurred while executing a Transact-SQL statement or batch.
  (Microsoft.SqlServer.ConnectionInfo)

  'Hru' is not a valid login or you do not have permission. (Microsoft SQL Server, Error: 15007)

Can anybody help me solve this?

The error message is stating that SQL Server cannot locate a login named Hiru, or you do not have the privilege required to create a user.

The SQL Server security model consists1 of a server-level login that provides access at the instance level.  Logins are then associated to a user inside each database.  The user typically has the same name as the login, although that is not required.

Creating a login and user is easy to do with T-SQL, as in:

USE master;
CREATE LOGIN [Hiru] WITH PASSWORD = 'pwd';
USE mydb;
CREATE USER [Hiru] FOR LOGIN [Hiru];

Using a T-SQL script provides more direct control over the process, and allows better troubleshooting.

1 - unless you're using the contained database security model


Is there a disk space impact when choosing NULL vs empty array {} for the default of an ARRAY[] column? My intuition is NULL will take up less space than {} when used as the default value of an ARRAY[] column in PostgreSQL.

Am I correct?

Why don't you check by yourself?

1 - Create 2 tables:

CREATE TABLE TABLE1 (column1 int not null, column2 int[] default {});
CREATE TABLE TABLE2 (column1 int not null, column2 int[]);

2  - make 1 million inserts for each table with:

insert into TABLE1(colulmn1) SELECT generate_series(1,1000000);
insert into TABLE2(colulmn1) SELECT generate_series(1,1000000);

3  - check table disk usage using this query :
(found in https://wiki.postgresql.org/wiki/Disk_Usage )

SELECT *, pg_size_pretty(total_bytes) AS total
    , pg_size_pretty(index_bytes) AS INDEX
    , pg_size_pretty(toast_bytes) AS toast
    , pg_size_pretty(table_bytes) AS TABLE
  FROM (
  SELECT *, total_bytes-index_bytes-COALESCE(toast_bytes,0) AS table_bytes FROM (
      SELECT c.oid,nspname AS table_schema, relname AS TABLE_NAME
              , c.reltuples AS row_estimate
              , pg_total_relation_size(c.oid) AS total_bytes
              , pg_indexes_size(c.oid) AS index_bytes
              , pg_total_relation_size(reltoastrelid) AS toast_bytes
          FROM pg_class c
          LEFT JOIN pg_namespace n ON n.oid = c.relnamespace
          WHERE relkind = 'r' and relname in ('TABLE1','TABLE2')
  ) a
) a;

4 - whenever you are convinced just drop the tables:

DROP TABLE TABLE1;
DROP TABLE TABLE2;


Is there any way to change the size of preallocate data file in mongodb? In mongodb data files are preallocates to a particular size,like first data file .0, the next .1, etc. The first file mongod allocates is 64 megabytes, the next 128 megabytes, and so on, up to 2 gigabytes, at which point all subsequent files are 2 gigabytes. Is there any way the default size can be change to 2GB? if so what will be the impact on the performance.

There is no way to specify your own sizes, the only way to alter the sizes of data files is to specify (or not) the smallfiles option.  That will bring the file sizes down to 25% of the default (max of 512MB rather than 2GB etc.)

You can pre-allocate the files yourself in advance (with dd, fallocate or similar).  I have a sample bash script using fallocate for just such a purpose here on GitHub. You still have to do the progression of sizes up to 2GB first, then allocate the remainder at 2GB though.  If nothing else, full pre-allocation would let you determine if allocation is having any kind of impact on your testing.


Is a clustered index on a child table in a parent/child relationship the most optimal index? A clustered index will sort row data in a table according to the ordering of the fields in the index. If so, why would you (or would you not) want to create a clustered index on the child table in a parent/child relationship?

Lets assume the classical order-with-orderlines example where you never fetch the orderlines without first going through the associated order. I would think that having a clustered index on both the orderid and the orderlineid column (as clustered indexes must be unique) forces all orderlines for a single order to be physically located adjacent to each other, which would make reads very performant. Is this correct? And if so, are there downsides to this approach?

You assumption about adjacency is correct.

If we use TPC-H as an example: Clustering the LINEITEMS table on on ORDERID will locate all order lines belonging to the same LINEITEM physically adjacent on disk. This speeds up queries that fetch all order lines for a given ORDERID. Clustering on the foreign key to the parent also allow fast merge joins between the child and parent.

There are a few downsides to the clustering approach:

The entire table must be kept sorted on disk. If you are expecting a
great many inserts with ORDERID not being sequentially generated,
page splits will be more expensive. This is something you can throw
hardware at.
If ORDERID is generated sequentially, you will
create a hotspot at the end of the table. In some database engines
(For example SQL Server) this is a problem at high insert speed. In SQL Server, this typically kicks in around 5K-10K inserts/sec.
The cluster index keys either have to be unique (ex: ORDERID, LINENUMBER) or padded with some
hidden column to make them unique. Since the composite cluster key must be present in all other indexes, this makes the secondary, non-clustered indexes larger.
Storing the table clustered will force a B-tree traversal when you want to locate data via a secondary index (unless the secondary index is covering the query). If you instead kept the table as a heap, all other indexes would only have an 8B overhead and your B-tree traversals are cut in half.

The vast majority of cases, you will want to cluster both the parent and the child on the same leading key. But if you expect the child table to be accessed via many different indexes - it may be worth considering the alternatives.


Why does querying for records that violate a foreign key return incorrect results? While doing some pre-checking on a table before adding a new Foreign Key, we were querying to see how many current rows would violate the new key. This is a fairly active database with near constant inserts on the tables in question.

The FK would be from MessagePatientIdentifier.MessageID to Message.MessageID.

The query we used was as simple as could be:

select * from MessagePatientIdentifier as mpi
where
    MessageID not in (select MessageID from Message)

The issue that we are seeing, is that rows are being returned from MessagePatientIdentifier

MessagePatientID                     | MessageID
553bde76-47d4-4ec3-96d1-b5d2e98931e1 | 7d45464d-8cc4-4a2e-8828-020722165b39

in this case and when you then select * from Message where MessageID = 7d45464d-8cc4-4a2e-8828-020722165b39, the record does in fact exist.

We then moved on and tried the same query, but on a table that DOES have a FK in place, referencing Message table in the same way. The same results...the query is reporting that Child table records exist without parent the corresponding parent table (Message) record.

MessageRecipID                       | MessageID
26d6d632-87b3-407e-aeb0-04552981e5f8 | 750f0fb4-3e6c-485d-996e-f061f8caa360

Then, once again if you select * from Message where MessageID = 750f0fb4-3e6c-485d-996e-f061f8caa360, this returns the record.

This data comes in via stored procedures from a Mirth Server as well as through a BizTalk WCF-SQL send port. The proc inserts the message record, gets the new uniqueidentifier as an output variable and then uses that to call auxiliary stored procs to insert into both the MessagePatient.MessageID and MessageRecip.MessageID.

Is this expected behaviour and I'm just not educated on the inner workings of SQL? Technically I believe BizTalk runs everything in a transaction, so it shouldn't get out of sync, even if it did where would it get the MessageID value if not from the Message insert?

What am I missing here?

This is a fairly active database

If you can restore a static backup somewhere, and do analysis on that unchanging copy, I think you'll find that the weird behavior you're seeing goes away.

Read Committed

The default isolation level in SQL Server is READ COMMITTED. Under this isolation level, you read the data that is currently committed to the database. The only guarantee that it provides is that when SQL Server reads a given page, it will never return an uncommitted result (known as a dirty read).

In your case you're scanning two tables and comparing them. While SQL Server is doing that scan, there is data movement.

You read the first few pages of data and have the first bit of data.
Some of the rows you haven't read yet are updated. Perhaps the MessageSubject or some other column is updated to a longer value, and this results in a page split. Or perhaps the key column is updated and the row is physically moved "up" the index into the portion you already read.
In both cases, it's possible for a row (or many rows) to move from a place you haven't yet read to a place you already read. You'll never see the row because it moved around. This causes the row to be "missing" from your scan.
It's also possible a row moves in the other direction: from a place you've already read to a spot you haven't gotten to yet. In this case, you'll see that row twice and your results will have a mysterious double result.

Your examples use integers, but in your question you mention uniqueidentifier. Since uniqueidentifiers are random, the randomness means rows are constantly being inserting into random places on the table. This increases the chances that a page fills up and has to be split to accommodate the new page, and increases the chances you see these phenomena.

Read the section titled "Locking Read Committed Behaviours" in the above linked article for a thorough explanation.

The fix?

If you want to avoid these off phenomena, don't use the default isolation level in SQL Server. My favorite & default isolation level is READ COMMITTED SNAPSHOT, which avoids these problems. (Read about all the isolation levels here to decide which one is right for you.)

Or if it's just for a one-time analysis, you could use a database snapshot to create a static image to query, or you could stop writing to the database, or restore a copy elsewhere. Stopping writes will stop data movement, and you won't run into the problem.


Foreign Key NULL vs Foreign Key to an empty string This is a table for a catalog of medicines. Some have a pharmaceutical brand, others are generic (i.e. they will never have brand information)

CREATE TABLE medicine (
   id serial PRIMARY KEY,
   name text NOT NULL,
   brand_id integer
   CONSTRAINT brand_fk FOREIGN KEY (brand_id) REFERENCES brand (id)
);

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

For storing generic medicines, e.g. abc & xyz, there are 2 options:

Use NULL for the Foreign Key brand_id

INSERT INTO medicine (name, brand_id) VALUES ('abc', NULL)
INSERT INTO medicine (name, brand_id) VALUES ('xyz', NULL)
Insert just 1 empty string in brand name and use that for all generic medicine's brand_id

INSERT INTO brand (id, name) VALUES (1, '')
INSERT INTO medicine (name, brand_id) VALUES ('abc', 1)
INSERT INTO medicine (name, brand_id) VALUES ('xyz', 1)

From what I've read on StackExchange, it seems like 1 is the general way of doing this. However if I want a unique index on medicine name & brand_id, I'll have to use partial indexes (i.e. 2 indexes respectively where brand_id IS NULL & brand_id is NOT NULL).

If I go with the second approach, I can achieve it with a single index.

Is there any other advantage / disadvantage of either of these approaches. I feel the second method is unconventional and there might be some trouble that I'll discover later along the way.

P.S. I've used the example of medicines to illustrate my query but I want to understand the technical merits of the solution purely from a database standpoint i.e. without going into a discussion of which approach would be better for medicine example.

The answer lies in the ambiguity of your question:

However if I want a unique index on medicine name & brand_id ...

You do not actually want "a unique index". You want to enforce certain rules. A unique index is a means to an end; a tool. The question is: What exactly do you want?
Do you want that there can only be a single instance of a name with an unknown brand? Or can there be multiple instances? That depends on what "unknown" or "missing" or "empty" or NULL is supposed to mean for you.
The canonical meaning of NULL is "unknown". We simply do not know, what goes here, it could be anything, including nothing (empty ('') in string types, 0 in numeric types).
If you want to allow one generic brand, include an entry in brand, call it "generic" or "ACME" or whatever. I like to use the id value 0 for such a catch-all entry.
Then your unique index or constraint is doing what it's supposed to do: Allow only one instance of (name, brand_id) in the table medicine. But multiple entries with (name, NULL) are still possible. Set brand_id NOT NULL, too, if you don't want that.
The best solution also depends on typical queries and details of your setup. Be sure to document exactly what NULL / empty / 0 is supposed to mean and why you chose to design it like that. It may seem crystal clear at the moment you implement it, but may be confusing later.
I added another link to the answer you referenced:

PostgreSQL multi-column unique constraint and NULL values
Create unique constraint with null columns


Listing indexes and constraints I am looking at a SQL Server database for an application I have inherited. I have not looked into SQL Server for approximately 10 years, so please bear with me.

The database table I am looking at has a bigint NOT NULL column called id, yet, when I check for constraints, I don't see any, and the same holds true for all database tables.

Am I right in assuming that there is no primary key & no indexing (clustered or nonclustered) on these tables?

I ran the following queries and the results appear to confirm my suspicion:

//**returns 0**
select count(*) from INFORMATION_SCHEMA.TABLE_CONSTRAINTS;

//**returns no rows**
select * from sys.indexes
where object_id = (select object_id from sys.objects where name = 'NAME-OF-TABLE');

//**returns all tables in database**
SELECT name
FROM sys.tables
WHERE OBJECTPROPERTY(object_id,'IsIndexed') = 0;

These two queries may help you.  The first will list all of the tables and indexes on those tables in your database.  If the table does not appear in the list is does not have any indexes defined on it.  These queries assume SQL Server version 2005 or newer.

SELECT
    IndexName = QUOTENAME(I.name),
    TableName =
        QUOTENAME(SCHEMA_NAME(T.[schema_id])) +
        N'.' + QUOTENAME(T.name),
    IsPrimaryKey = I.is_primary_key
FROM sys.indexes AS I
INNER JOIN sys.tables AS T
    ON I.[object_id] = T.[object_id]
WHERE
    I.type_desc <> N'HEAP'
ORDER BY
    TableName ASC,
    IndexName ASC;

The second query will report for each table the identity column, if any on each table in your database.

SELECT
    TableName =
        QUOTENAME(SCHEMA_NAME(T.[schema_id])) +
        N'.' + QUOTENAME(T.name),
    IdentityColumn = COALESCE(QUOTENAME(C.name), N'No identity column')
FROM sys.tables AS T
LEFT OUTER JOIN sys.columns AS C
    ON T.[object_id] = C.[object_id]
    AND C.is_identity = 1
ORDER BY
    TableName ASC;

To limit the queries to a specific table add a WHERE clause similar to:

WHERE T.name = N'NAME-OF-TABLE'


How do I execute an Oracle SQL script without sqlplus hanging on me? For an automated task I would very much like to run some SQL scripts and make sure that sqlplus does not hang under any circumstancees, i.e.:

If the script contains any named substitution variable that has to be entered by the user, sqlplus should return with an error instead of prompting for the variable -- I cannot use set define off, as some of these scripts need to accept command line parameters that have to be resolved with &1
The script must not "hang" when it doesn't contain an exit; at the end.

Solved: I think now that I can achieve this by wrapping the sql-script in a secondary "caller script". I.e., the caller script calls the other script with @ and then has a fixed exit; after that. The other script doesn't need an exit that way.
Anything else: If it would require a prompt, it should return with an error.

How can i do this with Oracle (and sqlplus or something else)?

You can do this under Linux/Unix when you embed the SQL commands in a shell script and pipe them into a SQL*Plus instance.


Does partitioning sometimes require denormalization? Specific example of what I'm asking:

Say that a SaaS app has an Order table with CustomerID and a child OrderItem table.

We want to partition both tables and we want to use CustomerID as the partition key.

The issue is that the OrderItem table does not have a CustomerID column (its only foreign key is to the Order table).

Would it be a best-practice to denormalize the OrderItem table and add a CustomerID column to it for the sake of partitioning?

Yes, if you want to partition by CustomerID, then CustomerID must exist in the table (even if it is redundant).

But first, IMHO, you need to ask yourself a couple of questions:

Do I need partitioning? What am I using it for? What problem am I solving?
If the answer to 1. is yes, then am I partitioning on the right column? How - specifically - will partitioning on CustomerID help in my environment?

I find that a lot of people turn to partitioning as a knee-jerk reaction to a performance problem of some kind that partitioning simply can't (completely) solve.

Similar to indexed views - people create views on top of views on top of views, then assume that all they need to do is create an index on each view and that will speed everything up. Then they actually learn the hard way about what indexed views are, how they work, and the much more limited set of scenarios that comprise their "sweet spot."


SQL encrypted connection home experiment I have a home SQLserver 2012 that I managed to enable "force encryption" on, using a self-signed certificate and configuration manager. Now I find two things I can't explain:

Once "Force Encryption" is enabled, I expect other computers on my home network to NOT be able to connect unless the certificate has been transferred and loaded on the client machine. That worked for one client computer, but a second client computer seems to be able to connect WITHOUT the certificate being imported (using mmc, certlm.msc or certmgr.msc).

When I "clear" the certificate in Configuration Manager, I can still use encrypted connections. It's only when I set "force encryption" to No that connections are NOT encrypted. But then, when I set it to Yes and restart the SQLserver (without certificate selected in Protocol Properties), I can again use encrypted connections, according to: SELECT DISTINCT encrypt_option FROM sys.dm_exec_connections WHERE session_id = @@SPID

Question 1 is: why can I use encrypted connections although the certificate has been Cleared in Protocol Properties?
Question 2 is: why can the second client computer connect to the SQLserver although Force Encryption is enabled, and, more importantly, what is preventing any other computer to connect, why use encryption at all?
SQL Server 2012 (SP3) (KB3072779) - 11.0.6020.0 (Intel X86).  NO Active Directory, NOT explicitly configured in any DNS, just my ISP router.

Maybe you have set up 'trust server certificate' option in the client SSMS. With this option the client accept the certificate of the server as is, without verofying it with the private key stored in the certificate repository.


Postgres server is not responding to a nodejs request I have access to a remote postgres DB from pgAdmin4 and I also could access from nodejs using a Mac. Right now I'm using the same code to access the DB in Windows. The code for my connection is the following:

const { Client } = require('pg'); //Importing the Postgres package
const hosts= require('../hosts'); //Using the file containig all hosts
const connectionData = { //Begin creating the connection settings object
   host: hosts.DBHost, //DB host
   port: hosts.DBPort, //DB hosts port
   database: hosts.DB, //DB
   user: hosts.DBUser, //DB user
   password: hosts.DBPassword, //DB user password
 }

My test is the following:

var client = new Client(connectionData); //New client instance using the above connection settings
client.connect(); //Open the connection to the database()
sql = "select * from myTable";
client.query(sql)
  .then(response => {
    console.log ({"data": response}); //This isn't shown
  })
  .catch(err => {
    console.log({"error": err}); //This isn't shown neither
  })

No error, no exception, the DB server doesn't respond!

Why isn't the server responding?

The problem is that I have Nodejs 12 in my Mac, but I have NodeJS 14 in my Windows, so it's a compatibility problem.

Since I updated my postgres (pg) to 8.0.3 or superior (i.e. npm install pg), now it works!

One guy helped me to realise that issue in https://stackoverflow.com/questions/61611039/postgres-server-is-not-responding-to-a-nodejs-request?noredirect=1#comment108987882_61611039.


Restrict access in SQL server I am using SQL server. I have a view (VIEW1) in DB1.

Now, I have created another (VIEW2) in DB2:

Create VIEW2 as Select * from DB1.VIEW1 where id not in (select ID from DB2.Table2)

I want a user to be able to select from DB2.VIEW2, but not from DB1.VIEW1 directly.

In other words, this would work:

SELECT * FROM DB2.VIEW2

and this would not:

SELECT * FROM DB1.VIEW1

Is it possible? If yes how?

Since your view2 selects from view1 in another database, as it configured now the permissions on view1 will be checked, so if you just do

DENY SELECT ON [dbo].[ViewName] TO [UserWithoutAccess]

your select from view2 will fail with

  The SELECT permission was denied on view1, schema dbo, database db1.

What you should do is to enable your databases to cross-db chaining:
Enabling Cross-Database Access in SQL Server

i.e. execute this in both databases:

ALTER DATABASE db1 SET DB_CHAINING ON;
ALTER DATABASE db2 SET DB_CHAINING ON;

This way ownership chain will not be broken when you pass to another database and the permissions on view1 will not be checked.

Database owners of db1, db2 should be the same.

More on ownership chaining here: Ownership Chains


How to grant only functions while revoking tables? I am a newbie to PostgreSQL, and configuring a new database.

I wrote several functions for indirect access(read/write) to data, and I want to prohibit all direct access to table.

GRANT CONNECT ON DATABASE db1 TO role1;
GRANT USAGE ON SCHEMA schema1 TO role1;

REVOKE ALL ON table1 FROM role1;
GRANT ALL ON ALL FUNCTIONS IN SCHEMA schema1 TO role1;

If I don't REVOKE table privilege, direct table access will be allowed. If I REVOKE table privilege, and GRANT functions, Users access the functions but see table access restriction error.

psql:./test1.psql:6: ERROR:  permission denied for relation table1

How can I make user role1 can access data using only functions while disallowing all direct access to table? I think I missed something, but I can't figure it out.

P.S. I am using Postgres 9.2.x.

You want to make your functions SECURITY DEFINER and have them owned by a user that does have the requisite rights.

Be very careful when coding SECURITY DEFINER functions. Don't make them owned by a superuser and read the manual carefully. Create a role that has only the rights required and no more; give it ownership of the SECURITY DEFINER functions. Where appropriate create multiple roles for different access levels.

See CREATE FUNCTION.


Cassandra encryption at rest I need to store some sensitive data in Cassandra and require it to be encrypted at rest. Is it likely Cassandra would function normally on top of a Linux encrypted file system (something like the open source TrueCrypt product)?

The other alternative available is to do the encryption at the application layer before sending the data to Cassandra but that entails writing plumbing code, and security plumbing code at that, which it would be good to avoid if at all possible.

Great question! There's a handful of
ways of doing this, depending on how deeply you want to be involved in
the configuration and long term maintenance.  You certainly could use
Cassandra on top of an encrypted filesystem, such as TrueCrypt or
eCryptfs, and it should function normally.

While possible, it's a little complicated to set up encrypted
filesystem and enforce a secure key management scheme.  Gazzang offers
a commercial product built on top of eCryptfs which provides a turnkey
Encryption & Key Management Platform that would keep all of your
Cassandra data secure, without requiring any changes to your existing
database applications.  Gazzang has a custom-tailored "jumpstart"
documentation for Cassandra that simplifies and assist in your
encryption/security implementation for Cassandra.

Full disclosure: I am employed by Gazzang.


How do I connect to a database with a blank password using a shell script? ## connect to mysql and source backup file ##
USER="root"
PASS=""

mysql -u$USER -p$PASS database_name < backup.sql

Above is my shell script which I used to source database but still its asking for password. I just want to know how can I source database in case of blank password. If root password is not blank than it works fine.

If root has no password you could just do this:
mysql -u$USER database_name < backup.sql

If you want to code so that root can be given a password later, do this in the script
USER="root"
PASS=""
if [ "${PASS}" == "" ]
then
    PASSWORD=""
else
    PASSWORD="-p${PASS}"
fi
mysql -u${USER} ${PASSWORD} database_name < backup.sql

Give it a Try !!!
CAVEAT
If is usually not a good idea to have root without a password. Please give it one.


Are availability groups required for AlwaysOn Beginning a project to create an HA SQL server environment.  We are limited to using SQL Server Standard edition, currently 2016.  From what I have found, AO(AlwaysOn) for standard edition limits you to basic AG (availability group) and thus  only 1 database per AG.  This is going to be tough since most of our SQL instances contain 30+ databases.  Can you implement AO without AGs?  Are there other ways to configure AO to address our needs?  All databases on any of our instances are managed similarly and thus wouldn't need the flexibility that managing at an AG provides.

If you have an underlying Windows Cluster, and shared storage you could look at using Failover Cluster Instances - this would work at an instance level. Standard edition supports this for two nodes.


How do I install pgcrypto on Windows? I would like to install pgcrypto in Windows. I have a file C:\Program\PostgreSQL\9.0\share\contrib\pgcrypto.sql but when I try to run it in psql I get an error:

C:: Permission denied

How do I solve this?

Depending on the full error message you receive (I have a hunch you didn't post it in full) you probably are trying to run the script by using a login role which is not superuser:

C:\Users\Administrator>c:\PostgreSQL\9.0\bin\psql.exe -U milen -f C:\PostgreSQL\9.0\share\contrib\pgcrypto.sql milen
SET
psql:C:/PostgreSQL/9.0/share/contrib/pgcrypto.sql:9: ERROR:  permission denied for language c
psql:C:/PostgreSQL/9.0/share/contrib/pgcrypto.sql:14: ERROR:  permission denied for language c
psql:C:/PostgreSQL/9.0/share/contrib/pgcrypto.sql:19: ERROR:  permission denied for language c
...


Endless Query Loop In Mysql I will delete millions records from a table in mysql dbms, but i don't want to delete all records at one time not to force database server cpu. That's why i want to use my query many times. My whille loops doesn't work.I don't know why. How to do that? My query is below:

while 1=1 DO
delete  FROM customer.generallogs  where DateEnter < '2019-04-03 16:13:28' limit 100000;
end while;

SP can be like (draft code):

CREATE PROCEDURE DeleteFromGeneralLogs (IN DeleteTill DATETIME)
BEGIN
DECLARE done INT DEFAULT 0;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done:=1;
-- IF DeleteTill IS NULL THEN LEAVE;
WHILE done = 0 DO
    SELECT SLEEP(1) INTO done; -- small pause between chunks
    DELETE
        FROM customer.generallogs
        WHERE DateEnter < DeleteTill
        LIMIT 100000;
END WHILE;
END;

And then

CALL DeleteFromGeneralLogs('2019-04-03 16:13:28');


add mobile prefix in existing mobile field in mysql I have mysql table 'USERS' which have a column name 'MOBILE" which contains customer mobile numbers in following format

333123456
333123457

I want to add 092 in starting of every number so that it should be like this

092333123456
092333123457

there are hundreds of mobile records I have to update, so is tehre any query I can run to accomplish this task?

A simple UPDATE should suffice. Make sure your column isn't a numeric type or you will lose leading zeroes.

UPDATE USERS SET MOBILE = CONCAT('092', MOBILE)

Also think about the possibility of keeping the prefix on a separate column, it would be easier to update and it won't be mixed up with the actual mobile number.


Using subquery within IF condition Hello i am having a weird problem currently i have the following query

SELECT *
FROM `txt_campaign_message` `cm`
         LEFT JOIN `txt_campaign` `c` ON c.id = cm.campaign_id
         LEFT JOIN `txt_message` `m` ON m.id = cm.message_id
         LEFT JOIN `txt_campaign_subscriber` `cs` ON cs.campaign_id = cm.campaign_id
WHERE ((`c`.`user_id` = 2) AND (`m`.`message_type` = 'blast') AND (`c`.`active` = 1))
  AND (NOT (`cs`.`subscriber_id` IS NULL))
  AND cs.subscriber_id = (
    (IF
        (cm.date_range_from IS NOT NULL,
         (SELECT cs1.subscriber_id
           from `txt_campaign_subscriber` cs1
           WHERE DATE(cs.created_at) BETWEEN DATE(cm.date_range_from) AND DATE(cm.date_range_to)
         ),
         (cs.subscriber_id)
        )
    )
  )
  AND (`cm`.`campaign_id` = 1386)

which gave me an error

  Sub-query returns multiple rows

and i changed the query part AND cs.subscriber_id = (IF to use IN rather than = before the condition like below

SELECT *
FROM `txt_campaign_message` `cm`
         LEFT JOIN `txt_campaign` `c` ON c.id = cm.campaign_id
         LEFT JOIN `txt_message` `m` ON m.id = cm.message_id
         LEFT JOIN `txt_campaign_subscriber` `cs` ON cs.campaign_id = cm.campaign_id
WHERE ((`c`.`user_id` = 2) AND (`m`.`message_type` = 'blast') AND (`c`.`active` = 1))
  AND (NOT (`cs`.`subscriber_id` IS NULL))
  AND cs.subscriber_id IN (
    (IF
        (cm.date_range_from IS NOT NULL,
         (SELECT cs1.subscriber_id
           from `txt_campaign_subscriber` cs1
           WHERE DATE(cs.created_at) BETWEEN DATE(cm.date_range_from) AND DATE(cm.date_range_to)
         ),
         (cs.subscriber_id)
        )
    )
  )
  AND (`cm`.`campaign_id` = 1386)

But the error remains the same, to my surprise if i remove the if condition and just use the sub query all runs fine

SELECT *
FROM `txt_campaign_message` `cm`
         LEFT JOIN `txt_campaign` `c` ON c.id = cm.campaign_id
         LEFT JOIN `txt_message` `m` ON m.id = cm.message_id
         LEFT JOIN `txt_campaign_subscriber` `cs` ON cs.campaign_id = cm.campaign_id
WHERE ((`c`.`user_id` = 2) AND (`m`.`message_type` = 'blast') AND (`c`.`active` = 1))
  AND (NOT (`cs`.`subscriber_id` IS NULL))
  AND cs.subscriber_id IN (
         (SELECT cs1.subscriber_id
           from `txt_campaign_subscriber` cs1
           WHERE DATE(cs.created_at) BETWEEN DATE(cm.date_range_from) AND DATE(cm.date_range_to)
         )
  )
  AND (`cm`.`campaign_id` = 1386)

and when i add the condition back it starts throwing the same error, what am i doing wrong or what do i need to change if i am using the IF condition for the sub-query, i already changed the = to IN as i needed multiple rows to be compared.

I am using MariaDB (10.3) where i am facing the problem.

The problem is that the IF statement cannot contain a set, it needs to be a scalar. Let's illustrate with:

create table T (x int not null);
insert into T (x) values (1),(2);
-- invalid since `SELECT x FROM T` is a set of rows
SELECT * FROM T WHERE 1 IN ( IF (1=1, SELECT x FROM T, 1) );

Fiddle

Let's see if your query can be reformulated (I changed it slightly to make it more readable):

SELECT *
FROM txt_campaign_message cm
LEFT JOIN txt_campaign c
    ON c.id = cm.campaign_id
LEFT JOIN txt_message m
    ON m.id = cm.message_id
LEFT JOIN txt_campaign_subscriber cs
    ON cs.campaign_id = cm.campaign_id
WHERE c.user_id = 2
  AND m.message_type = blast
  AND c.active = 1
  -- AND cs.subscriber_id IS NOT NULL, redundant and can be removed
  AND cs.subscriber_id IN (
    IF (cm.date_range_from IS NOT NULL
       , (SELECT cs1.subscriber_id
          FROM txt_campaign_subscriber cs1
          WHERE DATE(cs.created_at) BETWEEN DATE(cm.date_range_from)
                                        AND DATE(cm.date_range_to)
         )
       , cs.subscriber_id
       )
  )
  AND cm.campaign_id = 1386;

This is the problematic part:

AND cs.subscriber_id IN (
    IF (cm.date_range_from IS NOT NULL
       , (SELECT cs1.subscriber_id
          FROM txt_campaign_subscriber cs1
          WHERE DATE(cs.created_at) BETWEEN DATE(cm.date_range_from)
                                        AND DATE(cm.date_range_to)
         )
       , cs.subscriber_id
       )
  )

Two cases:

a) cm.date_range_from IS NOT NULL

AND cs.subscriber_id IN (
       SELECT cs1.subscriber_id
       FROM txt_campaign_subscriber cs1
       WHERE DATE(cs.created_at) BETWEEN DATE(cm.date_range_from)
                                     AND DATE(cm.date_range_to)
)

b) cm.date_range_from IS NULL

cs.subscriber_id = cs.subscriber_id

i.e. TRUE

This is equivalent to:

(
AND cs.subscriber_id IN (
       SELECT cs1.subscriber_id
       FROM txt_campaign_subscriber cs1
       WHERE DATE(cs.created_at) BETWEEN DATE(cm.date_range_from)
                                     AND DATE(cm.date_range_to)
    )
AND cm.date_range_from IS NOT NULL
)
OR cm.date_range_from IS NULL

All and all we end up with:

SELECT
    *
FROM
    txt_campaign_message cm
LEFT JOIN txt_campaign c ON
    c.id = cm.campaign_id
LEFT JOIN txt_message m ON
    m.id = cm.message_id
LEFT JOIN txt_campaign_subscriber cs ON
    cs.campaign_id = cm.campaign_id
WHERE c.user_id = 2
  AND m.message_type = blast
  AND c.active = 1
  AND (
      cs.subscriber_id IN (
          SELECT cs1.subscriber_id
          FROM txt_campaign_subscriber cs1
          WHERE DATE(cs.created_at) BETWEEN DATE(cm.date_range_from)
                                        AND DATE(cm.date_range_to)
        ) AND cm.date_range_from IS NOT NULL
      OR
      cm.date_range_from IS NULL
  )
  AND cm.campaign_id = 1386;

Totally untested, but it should give you an idea

You may also consider rewriting the in-predicate as a JOIN


SQL Server vCPUs and Performance I'm hoping to get some information on how vCPU performance changes (if any) based on guest vCPU configuration in a VMWare environment. Not really worried about the licensing aspect, or SKU specific limitations here (that's a whole other can of worms I don't want to get into).

Most of my SQL Servers are configured as:

CPU: 4
Cores per Socket: 1 (Sockets: 4)

I noticed one of my servers was configured differently with more 'Cores per Socket' to achieve 6 vCPUs:

CPU: 6
Cores per Socket: 3 (Sockets: 2)

I know there are CPU/Memory limitations based on SKU but for simplicity's sake I'm really just wondering which might be the better configuration:

A: more 'CPU'
B: more 'Cores per Socket'
C: neither, it does not matter! :-o

Here's the output of SELECT node_id, online_scheduler_count FROM sys.dm_os_nodes:

On the 4 vCPU system

NODE_ID  ONLINE_SCHEDULER_COUNT
0        4
64       1

And on the 6 vCPU system

NODE_ID  ONLINE_SCHEDULER_COUNT
0        6
64       1

Community wiki answer:

  I'm really just wondering which might be the better configuration

On the low end workload spectrum, you probably won't notice a difference. On the higher workload spectrum I'd copy or come close to the underlying hardware setup.

Note that depending on the version of ESXi you use, having less than 8 vCPUs won't actually change the cores/node and you may have to edit the virtual machine config file to override the changes in default behaviour.

The output from the sys.dm_os_nodes query shows that VMWare overrode your config and gave you a single NUMA node.


SQL Server - Data Warehouse large log file, how to safely shrink? My DW database is about 20GB size in data file and nearly 120GB in log file.

Data staleness is until midnight (so "yesterday's news") and multiple databases are being replicated daily from production servers. I'm also transforming the data at this time to get more analytical/synthetic/ information in one place for various consumers (accounts, marketing, management reports), so even complete data loss means about 30 min setback for all Stored Procs to re-run and repopulate all tables again. That means, yes, that point in time recovery is not (and will not be) a requirement.

Obviously then, the schema itself is much more valuable than the data and that's what must be protected (and is). Nonetheless, the DB is in full recovery mode and backups are being run daily, in addition to ad-hoc ones when I expect to be "breaking things".

It's possible the Snapshot isolation I've enabled on it had big impact on the size as well, but ultimately it proved pointless as I'm also using data across 12 databases (for now) and setting them all in similar way is impossible (various compatibility levels; don't want to meddle with that). So happy to switch it off again.

But, having said all that, I can't afford having the database inaccessible for long, as a number of people use it to do their work, even if it's still in early development phase. SSRS runs several dozens of subscriptions off it as well (RS is on a different server, if that's helps). It is also being exposed to a BI software for a number of web users (Tableau), so if we don't disrupt that, too, it would be good.

Obviously - again - large log files are due to large number of queries being cancelled, erroring out and/or simply crashing. And since I'm the only user who has insert/update/delete permissions, which is mostly done outside of working hours anyway, transactions are committed already for a quite a while.

So, my question is: how do I safely shrink the database to something more compact? And definitely in a "rinse and repeat" pattern for foreseeable future? No point wasting space to backup data I'm not really required to keep.

Additional info: this also may apply to the remaining 12 DBs (replicated from production servers).

Main reason for asking is that I may need the database to be more portable so that I can work on it offline and since other DBs are already quite large space really counts. And I'm not really a DBA, so I'm not sure of the best course here.

EDIT:

After reading linked articles and answers to similar question there is one, additional question that bugs me: If i do a log backup (as only then log files will stop growing) and then delete those backups (as I don't really need them - or do I?) will the log file start growing again? Do I understand correctly that I need to shrink and switch to simple recovery model afterwards to prevent that?

Switch to SIMPLE recovery mode.

FULL recovery mode is for when you need point-in-time recovery, and requires that you take frequently transaction log backups.

Since you've stated (in the comments) that you don't need point-in-time recovery, and that you can rebuild the entire thing in a matter of 30 minutes, just switch to simple.

The transaction log file will still be used by in-process transactions, but when those complete/commit (or are cancelled/rolled back) the tran log space is marked for re-use.

Once you've made the change, you can recover some of the additional space by using SHRINKFILE:

DBCC SHRINKFILE (NAME = 'mydatabase_log', 5000)


Decode Function in Oracle I have a table named as transaction,in that table trans_type column is there.I want to check the trans_type like bellow

if(trans_type = 'AR','AD','AF','AG') then
trans_type:='A';
elsif trans_type = 'BA','BS','BD','BG' then
trans_type:='B';
end if;

How to write this one by using decode function? Kindly tell me answer.

Note that for some cases, an if statement can be turned into a case expression which is somewhat similar to the ? operator in C and Java, but more generic. Your pseudo-code:

if(trans_type = 'AR','AD','AF','AG') then
trans_type:='A';
elsif trans_type = 'BA','BS','BD','BG' then
trans_type:='B';
end if;

becomes:

case
  when trans_type in ('AR','AD','AF','AG') then 'A'
  when trans_type in ('BA','BS','BD','BG') then 'B'
end case

as a case expression.


Logging all the queries in postgres I am using Postgres as my Database. I need to know whether I can extract all the queries that got executed with out logging them as logging all the queries will degrade the performance of database. So, provide a way I can get all the queries that got executed with out logging the queries.

You can try by capturing the traffic with tshark and some filters :
https://www.wireshark.org/docs/dfref/p/pgsql.html

It will not be very proper but should work.


Which PowerShell technique should I use to talk to SQL Server? I'd ultimately like to use PowerShell to replace the old KornShell scripts we use for SQL instance monitors. I'm having a hard time, though, getting my brain around all the different ways that PowerShell can actually talk to SQL server. Not sure if this is all of them, but here are 5 entirely different ways I can query the version of a SQL server:

1. SQLConnection .NET Class

$SqlConnection = New-Object System.Data.SqlClient.SqlConnection
$SqlConnection.ConnectionString = "Server=MyServer;Database=Master;Integrated Security=True"
$SqlCmd = New-Object System.Data.SqlClient.SqlCommand
$SqlCmd.CommandText = "Select @@version as SQLServerVersion"
$SqlCmd.Connection = $SqlConnection
$SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter
$SqlAdapter.SelectCommand = $SqlCmd
$DataSet = New-Object System.Data.DataSet
$SqlAdapter.Fill($DataSet)
$SqlConnection.Close()
$DataSet.Tables[0]

2. WMI Provider

$sqlProperties = Get-WmiObject
    -computerName "MyServer"
    -namespace root\Microsoft\SqlServer\ComputerManagement10
    -class SqlServiceAdvancedProperty
    -filter "ServiceName = 'MSSQLSERVER'"
$sqlProperties.VERSION

3. SMO

[System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SMO') | Out-Null
$smo-var = New-Object ('Microsoft.SqlServer.Management.Smo.Server') 'MyServer\instancename'
$smo-var.VersionString

4. PSDrive

Set-Location SQLSERVER:\SQL\MyServerName\
$server = Get-Item Default
$server.get_VersionString()

5. Invoke-SQLCMD

Invoke-Sqlcmd -Query "SELECT @@version" -ServerInstance "MyServer"

How should I go about deciding which of these techniques to use for different scenarios? Are there pros/cons of each? Are some of these powershell 1.0 techniques that were superceeded in 2.0? Will some of them not work to communicate with SQL 2000 or 2005 servers?

On one level, I'm sure the answer is "use whatever works", but for someone new to Powershell, its very confusing to see so many examples written like #1 above, when that is the longest and (in my mind) least "powershell-like" example.

Slightly more info in case it is relevant: the SQL server that will actually be running the monitor scripts is SQL 2005, but it is used to connect to multiple instances from SQL 2000 up to 2008R2.

Obviously, a lot of this devolves to simple personal choice. Here are my own, personal, rationalizations.

I've been using Powershell with SQL SQL since PSH v 1.0, and before SQL Server started officially integrating it. (When I started with PSH, I was administering SQL Server 2000 and 2005 servers.) So, I learned with SMO (or it's slightly older incarnation, the name of which escapes me at the moment) and .Net and I'm used to them. I'd generally lean towards SMO, since it makes some things a lot easier, like scripting out objects. My own code uses SMO some times and .Net some times. I think it's handier to use .Net to get simple result sets, for instance.

I think that Invoke-SQLCMD makes more sense if you have lots of existing TSQL scripts. If you are creating strings and executing them through -Query, that's going to be messy. If you have a good grasp of how Powershell works with .Net and SMO, using Invoke-SQLCMD occasionally, when you have a script file to run, is easy.

I've always found the PSDrive thing clunky and felt that they implemented it because they got caught up in the "everything can look like a file system" idea. I know that the *nix guys love \proc and such, but I feel that this implmentation feels sort of forced. I think that PSDrive is OK, maybe even good if you hate the UI, for exploring things but I've never written a script that uses it.

I have never seen anyone use the WMI provider. So, that would be my last choice.

So, I'd lead with SMO and fall back to .Net when it's handier to.


How do I set the option use_remote_estimate? Where do I set the "Cost Estimation Options" for postgres_fdw. Specifically I want to add use_remote_estimate.

test=# SET use_remote_estimate=true;
ERROR:  unrecognized configuration parameter "use_remote_estimate"

postgres_fdw doesn't use GUC variables. The settings for postgres_fdw are actually set with options under CREATE SERVER,

CREATE SERVER [IF NOT EXISTS] server_name [ TYPE 'server_type' ] [ VERSION 'server_version' ]
    FOREIGN DATA WRAPPER fdw_name
    [ OPTIONS ( option 'value' [, ... ] ) ];

Or with ALTER SERVER

  Change options for the server. ADD, SET, and DROP specify the action to be performed. ADD is assumed if no operation is explicitly specified. Option names must be unique; names and values are also validated using the server's foreign-data wrapper library.

Like,

-- Set it if it's never been set
ALTER SERVER flybase
  OPTIONS (ADD use_remote_estimate 'true');

-- Change it if it has been set
ALTER SERVER flybase
  OPTIONS (SET use_remote_estimate 'false');

-- Drop it if it has never been set
ALTER SERVER flybase
  OPTIONS (DROP use_remote_estimate);


How can I backup my large SQL Server table? Environment: SQL Server 2008 R2 Standard Edition on Windows Server 2008 R2 (yeah - I know).  VMWare, Commvault.

We have a 1.4 TB DB with one table.  Here is the schema:

CREATE TABLE [dbo].[FileManager_FilesContent](
    [Id] [bigint] IDENTITY(1,1) NOT NULL,
    [GUID] [uniqueidentifier] NOT NULL,
    [FileContent] [varbinary](max) NOT NULL,
    [Extension] [varchar](20) NULL,
    [IsDeleted] [bit] NULL,
 CONSTRAINT [PK_FileManager_FilesContent] PRIMARY KEY CLUSTERED
(
    [Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

CREATE UNIQUE NONCLUSTERED INDEX [IX_FileManager_FilesContent] ON [dbo].[FileManager_FilesContent]
(
    [GUID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO

We have a network share where we backup the other DBs.  This one won't backup there because it would take more than 40 hours.  My thought is a local drive (or drives) plus backup compression, striping, and dialing in maxtransfersize and buffercount might help.

Any other advice on how to get this DB backed up?

Thanks in advance,
-Peter

You could partition the table. Put each partition in a separate filegroup via the partition scheme. Each filegroup can be backed up individually. From SQL Server 2019 there is a DMV that makes available information on which pages, and hence files, are dirty.

There will still be 1.4TB to backup so a complete backup cycle will still take at least 40 hours. However, each individual filegroup will take much less time avoiding downtime, network congestion and all the other worries.

If writes typically touch only a few partitions you could establish some form of change tracking - either built-in or application code - and only backup the dirty filegroups.

Depending on when rows are written you may be able to make some filegroups read-only. These can then be omitted from the on-going backup cycles once a final backup is taken, reducing elapsed.

Potentially backups and transfers to network shares could happen in parallel reducing the amount of local workspace required (backup partition 1, transfer that over the network while partition 2's backup is prepared, then transfer 2 while 3 is backed up and so on; each local backup can be deleted as soon as it is on the network drive).

If you can arrange things appropriately you may be able to implement piecemeal restores which will certainly improve your RTO from 40hr.

Of course the scripting and restores will be that bit more complicated.


Actual Number of rows is too high even with new tables I have a query based on two newly created tables in SQL Server 2005. I have index created on these tables. But when I write a query with JOIN condition, the number of actual rows returned is too high whereas the estimated number of rows is less. And the query plan uses a nested loop. [Query plan diagram is given below.] Since these are new tables, I think the usual stale statistics is not the reason here.

I can rewrite this query by using NOT EXISTS (as shown in Query 2) and the actual number of rows is reduced. But I have other requirements to fetch details from LWTest table using INNER JOIN – there the high number of actual rows is a problem.

So, any clue why is the actual number of rows is so high even though there is index and statistics? What can be done to bring it down?

Note: Number of rows in TransmittedManifests –904. Number of rows in LWTest -- 829785

UPDATE

Note2: Compatibility_Level is 90. Elapsed time for Query 1 is 64 ms. Query 2 elapsed time is 6 ms.

Note3: Tried OPTION (RECOMPILE), rebuild index and UPDATE STATISTICS WITH FULLSCAN on these two tables. But the actual number of rows is still high.

Thansk to Martin Smith for details of difference in count per execution (estimate) and total count (actual).The actual rows is count across all executions, the estimated is count per execution. Estimated rows, actual rows and execution count.

For Query 1, the ActualExecutions="904" for LWTest table.

Query

DBCC FREEPROCCACHE
GO
DBCC DROPCLEANBUFFERS
GO

SET STATISTICS IO ON
PRINT 'BEGIN -----------------------------------------'

--Query 1
SELECT  T.[Manifest]
FROM dbo.TransmittedManifests T
LEFT OUTER JOIN dbo.LWTest LW (NOLOCK)
    ON LW.Manifest = T.Manifest
WHERE LW.Manifest IS NULL

PRINT 'QUERY 1 COMPLETED -----------------------------------------'

--Query 2
SELECT  T.[Manifest]
FROM dbo.TransmittedManifests T
WHERE NOT EXISTS (SELECT Manifest FROM dbo.LWTest L
                  WHERE L.Manifest = T.Manifest
                 )

PRINT 'QUERY 2 COMPLETED -----------------------------------------'

--Query 3
SELECT  T.[Manifest],LineItemID,InvoicedQuantity
FROM dbo.TransmittedManifests T
INNER JOIN dbo.LWTest LW (NOLOCK)
    ON LW.Manifest = T.Manifest

Table Schema

Query Plan 1

Query Plan 2

The number of records in TransmittedManifests table is much less than the number of records in LWTest. In such scenario, good solution is to use NOT EXISTS  approach (as in Query 2) to reduce the number of actual rows. Refer Joins without JOIN - Rob Farley

Now, Martin Smith’s comment helped me demystifying the count per execution (estimate) and total count (actual). For Query 1, the ActualExecutions="904" for LWTest table. So 128385/904 = 142 is the actual rows per execution which is somewhat close to the estimate 104.

In SQL Server 2005, the ActualExecutions can be seen by making a profiler trace adding performance events as shown in the diagram below. [In SQL Server 2012, I could see this information captured in execution plan diagram itself].

XML

SQL Server 2005 Trace Settings


Impact of Log backups restore process after file\file group restore on a database When we restore file\file group(backup1) backup on a database is it necessary to restore all log backups that were taken after backup1 to get database in consistent state??
and also what will be the impact of log restore process on database state and those files?

When we restore file\file group(backup1) backup on a database is it necessary to restore all log backups that were taken after backup1 to get database in consistent state??

Yes - if you want to completely bring your database online (with all file groups) or you want to do a point-in-time restore of your database.

No - if you want to do a piece meal restore for specific file group.

I have explained in detail by giving an example at Backing up file groups with restore to earlier backup.


DB2 Communication link failure issue I would like to get your valuable suggestions for the below problem.

My Perl application connects to a DB2 database and keeps inserting data into a table. The application successfully inserts data for a while and after sometime I start seeing the below Error 1.

Error 1:

[IBM][CLI Driver] CLI0108E  Communication link failure. SQLSTATE=40003

Error 2:

[IBM][CLI Driver] CLI0106E  Connection is closed. SQLSTATE=08003

Error 1 occurs only once, after that Error 2 occurs repeatedly till I restart the application and establish new connection to the database. It's been confirmed that the database is running healthy and able to accept connections and even the network connections are healthy.

Background on Error 1:

Error "CLI0108E" Suggests that "The connection between the driver and the data source failed during execution of this function."

And "SQLSTATE=40003" suggests that "The statement completion is unknown."

Is it possible that something going wrong in an insert statement actually closes the connection? Or leaves the connection stateless?

This actually breaks my Perl application. What are the best options to handle this situation?

Copied from a StackOverflow answer of mine  This answer assumed C# code, but the error message is directly applicable here as well.

CLI0108E simply means the connection was broken.  From the official IBM documentation of this error code:

  CLI0108E Communication link failure.

  Explanation The connection between the driver and the data source failed during execution of this function.

  User response Establish a new connection.

The SQLState (40003) indicates that the DB server can't tell whether or not the statement completed successfully.  (Documentation of SQLState messages and their meaning can be found here: http://publib.boulder.ibm.com/infocenter/db2luw/v9/index.jsp?topic=%2Fcom.ibm.db2.udb.msg.doc%2Fdoc%2Fr0sttmsg.htm

values in this range:

  40001 Deadlock or timeout with automatic rollback occurred.
  40003 The statement completion is unknown.
  40504 A system error has caused the unit of work to be rolled back.
  40506 The current transaction was rolled back because of an SQL error.
  40507 The current transaction was rolled back as a result of a failure creating an index.

CLI0108E could have any number of causes:  Issues in the network, the client or server closing the connection due to a timeout, someone kicking a cable in the computer room, you name it.  I've even seen is in our environment where a virtual server running on a particular VM host got this error because there was a problem with the virtual NIC card, and it was fixed by reinstalling the driver.  The list of possible causes is surprisingly large, but a good Network Admin should be able to help.  (Monitoring packets with a sniffer is a good way to track down the source when all else fails.)

If  you can, it might not hurt to refactor the code to upload smaller sets.  Say you have a 10,000 records, try uploading a thousand at once, ten times to see if that helps.

Another approach I used (once) in a situation where the server was completely unreliable was to just try inserting one record at a time.

pseudo-code: (Assuming a connection object named "connection" and a command object named "cmd" that uses that connection...)

connection.Open()

for each record
   try
      cmd.CommandText  ="whatever changes need to be made"
      cmd.Execute()
   catch(Exception ex)
      if( the exception is a communication link failure)
         connection.Open()
         cmd.Execute()
       else
          handle different errors accordingly
       end if
  end try

next

But that approach has performance issues, is messy, and still could fail of the attempt to reopen the connection fails.  Even though I've used it in a pinch, I don't recommend it long-term.  (I got rid of that approach once the original issue was fixed.)  You're far better off trying to track down the root cause of the broken connections.

In your case, it is possible that the underlying cause may just be that you're trying to upload too much data at once. I doubt it, but it's possible.  Refactoring the code to send the data in smaller chunks would definitely by my first choice were I in your shoes.


SQL left join returns null column I am trying to do a LEFT JOIN on two tables, where one table has a datetime column, and the other table has a date column. As I am working in SQL Server, I used CONVERT to change the datetime to date. I then wrote the join statement:

SELECT CONVERT(date,db1.dbo.table1.datecolumn), db2.dbo.table2.datecolumn
FROM db1.dbo.table1
LEFT JOIN db2.dbo.table2
    ON db1.dbo.table1.datecolumn = db2.dbo.table2.datecolumn

And the query returns the date column from table1, but the column from table2 is populated with NULL values only. I am not sure what I am doing wrong. I tried CAST instead of CONVERT, with no luck. Is there something happening with the conversion? Any help is appreciated.

You're doing the convert() in the wrong place I guess. You have to do it in the equals operation of the ON, it's still a datetime there, regardless of what you do on it in the list of selected columns.

Try:

SELECT CONVERT(date,db1.dbo.table1.datecolumn), db2.dbo.table2.datecolumn
FROM db1.dbo.table1
LEFT JOIN db2.dbo.table2
    ON CONVERT(date,db1.dbo.table1.datecolumn) = db2.dbo.table2.datecolumn


1..1 and 1..n cardinalities on the same relationship I have two tables (companies and users), for which the following rules apply:

A User has a Role ('employee' and 'boss')
There is one and only one 'boss' in a Company.
A User can be in only one Company.

Before thinking about the Boss case, I created only two tables and added a foreign key column company_id on the users table. So every User has a Company and everything is alright.

But now, there is a new rule: there can be only one boss. How do I write this rule in my schema?

Should I create a boss_id column in companies table? If so, should I leave the company_id NULL for this kind of User? (but it seems weird) Or should I add company_id and boss_id in both tables? (but it's redundant).
Should I keep going with only company_id and keep the rule in my head ("hey, remember, the schema does not tell anything about this, but you can't create two bosses for the same Company").
Should I... Hum, I must be tired I forgot the other patterns?

I think I missed something really simple, but I can't figure it!

This is an over-simplifed example, one could say there can be multiple bosses.

In your requirement employee means not boss, so this should work fine.

-- Company (CompanyID) exists
--
Company {CompanyID}
     PK {CompanyID}

-- User (UserID) works for company (CompanyID)
--
User {UserID, CompanyID}
  PK {UserID}
  SK {UserID, CompanyID} -- superkey (unique), needed for FK ..

 FK1 {CompanyID} REFERENCES Company

-- User (UserID) is boss in company (CompanyID)
--
CompanyBoss {UserID, CompanyID}
         PK {CompanyID}
        FK1 {UserID, CompanyID} REFERENCES User

Note: all attributes NOT NULL


"Specify SQL Server administrators" - unusual default value When installing SQL Server, you get to specify SQL Server administrators, i.e., the list of users initially in the sysadmin role. By default, this list contains the current user.

(Image quoted from https://msdn.microsoft.com/en-us/library/dd578652.aspx.)

This default value strikes me as odd. Wouldn't BUILTIN\Administrators be a more sensible choice than the current user? After all, isn't the whole point of Windows authentication not having to manage two separate user/group directories? And local administrators can gain sysadmin rights anyway, if they want to, so it's not really a security feature either.

On the other hand, SQL Server was written by people much smarter than I am, so there might actually be a very good reason for this default value. What is it?

You're right: system administrators can gain sysadmin access, but you will find entries in the default trace for that action. Moreover, it requires restarting the service at least twice, which would hardly go unnoticed.

Not granting sysadmin privileges to BUILTIN\Administrators is a change introduced in SQL Server 2008. In my opinion it's a good idea: it allows the separation of duties between server administrators and SQL Server administrators.

The main undesired effect of having BUILTIN\Administrators in your sysadmin role is the loss of control, which can manifest with one or more of the following annoying things:

New databases appearing on your servers
Unwanted connections from windows admins that end up competing with legitimate connections
Implied permissions to windows admins on databases with sensitive
data
Seemingly innocuous but disrupting "self service" operations,
such as taking full "one-off" backups on databases with differential
backup strategies.

Long story short, windows admins have a different skill set and a different mindset, not to mention different responsibilities: letting them manage your SQL Server instances is not a good idea.

My suggestion is to create a windows group for your DBAs and add that group to the sysadmin role upon each new SQL Server installation.


How to set up last access (timestamptz) in table 1( basic info table) when working with many other tables having foreign key from table 1? Let's say the database have 5 tables for sake of simplicity (there're many more in reality). Table 1 has the basic information of a user which is not changed so often. However, the other tables are accessed very often. All these other tables have the user-id as the foreign key. I need to make a column lastAccess in table 1 such that whenever the user works with any of the table, user's lastAccess in table 1 is updated.

I'm working with [python-socketio] (https://github.com/miguelgrinberg/python-socketio) server connected to PostgreSQL database using python-psycopg2 connector.

It seems like a task which every person working with databases should know. I am new to working with RDBMS and exactly find a way to do it.
A general direction, if a solution is not possible, will also be really appreciated.

I have two solutions:

Create a lastAccess column on all 5 of the tables. Create database triggers on each table to update it's corresponding lastAccess column. This will allow you to track when each table is updated.
Create lastAccess on Table 1. Then create triggers on tables 2,3,4,5 that will update the lastAccess column on Table 1.


How to Know if I have A Restricted or Full Access to Table This just came to me when helping my friend with his homework.
Is it possible to know our role (implicit, not know to us) role grants as full access to a full table or just to a view derived from the table?
 The table [Table]  contained a field[Class] indexed alphabetically, i.e., A,B,C,...,Z . The view
contained just a subset , something like [class] , with values  A,B, E,G,J. Now, if I was given a role with access to the latter table with that indexing , missing letters between B,E , E and G , etc., I would suspect I have access to a view and not the whole table. Still, is there a way, maybe by joining with/to other tables of knowing that my access is restricted?   Maybe joining to a table containing the same PK as [Table] , without  the  Class field, maybe getting some Nulls as outputs? Basically, are there ways of knowing if I am viewing a full table or just a view derived from it? Maybe by trying to do something "full tables" can do but views cannot?

EDIT: Thanks for the answers, I was thinking more of indirect ways of getting an answer, like querying other tables on joins on my present table's PK  ( assuming, of course, I have SELECT access to these tables ) and getting nulls, or by doing a " Where PK is null" query and getting some answers.

Basically, are there ways of knowing if I am viewing a full table or
  just a view derived from it?

The only thing you can see if you have restricted permissions is the type of object:

select type_desc
from sys.objects
where name = 'yourObject'

If you have only permissions on a subset of columns, you'll never figure it out using metadata (system tables/views):

  The visibility of metadata is limited to securables that a user either
  owns or on which the user has been granted some permission.

Related link:
Metadata Visibility Configuration