how to resolve ORA-04042: procedure, function, package, or package body does not exist and ORA-00942: table or view does not exist

That is not how you grant privileges on a directory.
SQL> create or replace directory KIR_DOKUMENT as '/tmp';

Directory created.

SQL> select owner, object_type, status from dba_objects where object_name = 'KIR_DOKUMENT';

OWNER
--------------------------------------------------------------------------------
OBJECT_TYPE             STATUS
----------------------- -------
SYS
DIRECTORY               VALID

SQL> grant read on SYS.KIR_DOKUMENT to bp;
grant read on SYS.KIR_DOKUMENT to bp
                  *
ERROR at line 1:
ORA-00942: table or view does not exist

SQL> grant execute on SYS.KIR_DOKUMENT to bp;
grant execute on SYS.KIR_DOKUMENT to bp
                     *
ERROR at line 1:
ORA-04042: procedure, function, package, or package body does not exist

Use the GRANT privilege ON DIRECTORY ... as:
SQL> grant read on directory SYS.KIR_DOKUMENT to bp;

Grant succeeded.

SQL> grant execute on directory SYS.KIR_DOKUMENT to bp;

Grant succeeded.

SQL>


Imported database very slow on a simple query

Here are the principles.

(I assume id is the primary key of the table and the table is InnoDB.)
SELECT count(id) FROM payments; will read the entire table.  Or it may read some secondary index, which necessarily includes the PK.
The first time you touch a table, the data is sitting on disk must be read into RAM for processing.  Since it needs to read the entire table, that could take some time.
If you run another query on the same table, it can skip the I/O.  This is often a factor of 10 faster.
If the first query used a secondary index, but the WHERE is_held = True needs was not involved in that index, then this query will need to read the entire data BTree.  One of your tables apparently had it cached already (0.32s) and the other did not (37s).
Don't use COUNT(id) unless you need to avoid counting any rows with id IS NULL, which cannot happen.  Instead, use simply COUNT(*).
INDEX(hash_id, id) would make some of your queries run faster because they could run entirely in that index's BTree.
Upgrading/downgrading the server is unlikely to change performance -- unless the table is bigger than the buffer_pool.  In this case, the table cannot be cached to make the second query fast.

For further discussion, please provide SHOW CREATE TABL, SHOW TABLE STATUS, and the value of innodb_buffer_pool_size.


Create a SQL Trace (server-side) and capture the SQL:StmtCompleted event.  You can filter on the TextData column to ensure you're only getting the query that you are looking to ensure is happening.

The easiest way to create a server-side trace would be to create it in SQL Server Profiler (which is innately a client-side trace), do a quick start and stop and script out the trace definition and run it in SSMS.  Please see this TechNet reference on Server-Side Tracing and Collection for an exhaustive reference on the details how to do this.


TNS-01190: The user is not authorized to execute the requested listener command

Yes, this is an issue with owner and group of lot's of files in oracle home. Most files are of oracle:oinstall but not all. Some are owned by root and some owned by nobody.
A safer solution for this would be
find -user {old_uid} |xargs chown {new_uid}
find -group {old_gid}|xargs chgrp {new_gid}

and yes, this should run as root.


How to log custom messages from inside a PostgreSQL transaction?

RAISE NOTICE is the right method.

The reason RAISE NOTICE output does not appear in clog.log is that your standard error redirection is wrong at the shell level.

You want:

psql --log-file=plog.log -f file.sql  >clog.log 2>&1

That way it redirects the standard output into clog.log and then the standard error to that standard output.

The way it's done in the question: 2>&1>clog.log does not achieve that because of the wrong order, as described in the bash manual:

  Note that the order of redirections is significant. For example, the
  command

  ls > dirlist 2>&1

  directs both standard output (file descriptor 1) and standard error
  (file descriptor 2) to the file dirlist, while the command

  ls 2>&1 > dirlist

  directs only the standard output to file dirlist, because the standard
  error was made a copy of the standard output before the standard
  output was redirected to dirlist.


I have now moved from dev to production.
the table FactBackOrder has 4,183,289 rows.

We're in sql server 2012.
Microsoft SQL Server 2012 (SP1) - 11.0.3412.0 (X64)
    Mar  2 2014 01:25:09
    Copyright (c) Microsoft Corporation
    Enterprise Edition (64-bit) on Windows NT 6.3  (Build 9600: )

But I don't want to use Columnstore indexes - these tables are updated very often.
Maybe in sql 2014?

the original query plan of the query I wanted to improve the performance of is:

the only way I found out to improve the orginal query was to add an index to the table.

create index idx_FactBackOrder_OrderNo_OrderItemSeqNo
on Facts.FactBackOrder (OrderNo,OrderItemSeqNo) with (online=on)

When I then run the query, I notice it takes longer than the original without index.
the new query plan, after creating the index is as follows:

I immediately drop the newly created index:

drop index idx_FactBackOrder_OrderNo_OrderItemSeqNo on Facts.FactBackOrder

I then create an indexed view:

--drop view [dbo].[VfactBackOder]

CREATE view [dbo].[VfactBackOder]
WITH SCHEMABINDING
AS
select fo.OrderNo,
       fo.OrderItemSeqNo,
       COUNT_BIG(*) as the_number
from Facts.FactBackOrder fo
group by fo.OrderNo, fo.OrderItemSeqNo
GO

create the clustered index for the indexed view:

CREATE UNIQUE CLUSTERED INDEX [IDX_VfactBackOder] ON [dbo].[VfactBackOder]
(
    OrderNo, OrderItemSeqNo
)
go

The when I run the query using the newly created indexed view instead of the original query referencing the underlying tables:

select fo.OrderNo,
       fo.OrderItemSeqNo,
       fo.the_number
from VfactBackOder fo
where fo.the_number > 1

I get a non satisfactory result.
then I add another index to the view:

CREATE NONCLUSTERED INDEX [<Name of Missing Index, sysname,>]
ON [dbo].[VfactBackOder] ([the_number])
INCLUDE ([OrderNo],[OrderItemSeqNo])
go

Now the query flies like a rocket.
the new query plan is:

And looking at the space used by the indexed view and the underlying table:

sp_spaceused 'Facts.FactBackOrder'
go
sp_spaceused 'VfactBackOder'
go

query is performing well enough, customers happy here.
thanks for all the comments.


SQLite - No such column when column exists

The problem was that I had an space at the end of the name of the columns, solved the problem by deleting such spaces.
Before:
CREATE TABLE IF NOT EXISTS "trackedinfo" ("id" INTEGER PRIMARY KEY, "date_time_start" TEXT, "date_time_end" TEXT, "tracked_name" TEXT, "tracked_origin" TEXT, "tracked_maker" TEXT, "tracked_version" TEXT, "tracked_type" TEXT, "sendok " TEXT, "tracked_id " TEXT);

After:
CREATE TABLE IF NOT EXISTS "trackedinfo" ("id" INTEGER PRIMARY KEY, "date_time_start" TEXT, "date_time_end" TEXT, "tracked_name" TEXT, "tracked_origin" TEXT, "tracked_maker" TEXT, "tracked_version" TEXT, "tracked_type" TEXT, "sendok" TEXT, "tracked_id" TEXT);

In the above examples white spaces whithin "sendok" and "tracked_id" columns has been removed.


Most recent fixed date (i.e. 4th July) before given date

First, let's use MySQL's format for dates:

Start date:  xxxx-04-27
Claim date:  2016-04-26

Now we need to combine the '2016' (or 2016-1) from the Claim date to the '04-27' of some Start date.  It does not matter whether it is the original or current "start date".  To decide on the "-1", we need to compare both mm-dd values, which can be done as simple string.

In the following code, I am assuming DATE datatype was used; using string functions works fine.  For example:

SELECT LEFT(curdate(), 4), RIGHT(curdate(), 5);
+--------------------+---------------------+
| LEFT(curdate(), 4) | RIGHT(curdate(), 5) |
+--------------------+---------------------+
| 2016               | 11-21               |
+--------------------+---------------------+

Here's the expression to generate '2015-04-27', which can be used as a DATE or in DATE_FORMAT() to format it as desired:

CONCAT(
    LEFT(claim_date, 4) -
    IF(RIGHT(claim_date, 5) <=        -- may want just '<'?
       RIGHT(start_date, 5),  1, 0),  -- whether to back up a year
    '-',
    RIGHT(start_date, 5) )  -- tack on proper 'mm-dd'

I thought about using julian dates, but leapday messes up things.


Oracle losing connection ( ORA-03114 ) after some time not using the software

Yes, that will work. The 10 is a value in minutes (documentation link).

  To specify a time interval, in minutes, to send a check to verify that
  client/server connections are active

The official Oracle documentation regarding DCD (Dead Connection Detection) is here.

The primary purpose of SQLNET.EXPIRE_TIME=10 is to clean up dead connections, but (I quote):

  A less common usage scenario for DCD is to keep database connections
  alive when an external firewall timeout is configured to terminate
  idle connections


Table design for “Users & Groups” plus “Groups within Groups”

I'd just go straight for a self-referencing key ( adjacency list, hierarchy relationship, recursive relationship, I have no idea what the "most correct and proper" term for it is, but any of these should get the point across to a sufficiently tuned-in audience ) on what would effectively be a "principal" or "role" table, along with a membership table.  While I'd draw my inspiration from SQL Server, PostgreSQL also does this for it's own security management, like what is shown through \du or \dg.  That said, there may even be value in considering trying to handle all of this with the built-in security management:

CREATE ROLE accounting;
GRANT SELECT ON account TO accounting;
CREATE ROLE executives;
GRANT accounting TO executives;
CREATE USER cto_bill;
GRANT executives TO cto_bill;

"CTO Bill" should now be able to SELECT from the account table, via the executives role through to the accounting role, all completely out of the box.  If an application needs to know the chained permissions, they could be exposed via \du or some form of query against the pg_catalog's pg_auth_members and pg_roles tables:

SELECT  r.rolname, r.rolsuper, r.rolinherit,
        r.rolcreaterole, r.rolcreatedb, r.rolcanlogin,
        r.rolconnlimit, ARRAY(  SELECT  b.rolname
                                FROM    pg_catalog.pg_auth_members m
                                INNER JOIN pg_catalog.pg_roles b
                                    ON  ( m.roleid = b.oid )
                                WHERE   m.member = r.oid ) as memberof
FROM    pg_catalog.pg_roles r
WHERE   r.rolcanlogin = 'f'
ORDER BY 1;

If a total nuts-and-bolts solution is deemed necessary, you could build a schema similar to this:

CREATE TABLE Principal
(
    Principal_PK             SERIAL NOT NULL,
    Name                     VARCHAR( 128 ) NOT NULL,
    PasswordHash             BYTEA,
    Type                     CHAR( 1 ) NOT NULL
);

ALTER TABLE Principal
ADD CONSTRAINT PK__Principal
    PRIMARY KEY ( Principal_PK );

ALTER TABLE Principal
ADD CONSTRAINT UQ__Principal__Name
    UNIQUE ( Name );

ALTER TABLE Principal
ADD CONSTRAINT CK__Principal__Type
    CHECK ( Type IN ( 'U', 'G' ) );

CREATE TABLE PrincipalMembership
(
    PrincipalMembership_PK   SERIAL NOT NULL,
    Principal_FK             INTEGER NOT NULL,
    IsMemberOfPrincipal_FK   INTEGER NOT NULL,
    DateCreated              TIMESTAMP NOT NULL
);

ALTER TABLE PrincipalMembership
ADD CONSTRAINT PK__PrincipalMembership
    PRIMARY KEY ( PrincipalMembership_PK );

ALTER TABLE PrincipalMembership
ADD CONSTRAINT FK__PrincipalMembership__Principal
    FOREIGN KEY ( Principal_FK )
    REFERENCES Principal ( Principal_PK );

ALTER TABLE PrincipalMembership
ADD CONSTRAINT FK__PrincipalMembership__IsMemberOfPrincipal
    FOREIGN KEY ( IsMemberOfPrincipal_FK )
    REFERENCES Principal ( Principal_PK );

ALTER TABLE PrincipalMembership
ADD CONSTRAINT UQ__PrincipalMembership__Principal__IsMemberOfPrincipal
    UNIQUE ( Principal_FK, IsMemberOfPrincipal_FK );

ALTER TABLE PrincipalMembership
ALTER COLUMN DateCreated SET DEFAULT ( NOW() );

INSERT  INTO Principal ( Name, PasswordHash, Type )
VALUES  ( 'accounting', NULL, 'G' ),
        ( 'executives', NULL, 'G' ),
        ( 'cto_bill', E'\\x00', 'U' );

INSERT  INTO PrincipalMembership ( Principal_FK, IsMemberOfPrincipal_FK )
SELECT  p.Principal_PK, m.Principal_PK
FROM (      SELECT  'executives', 'accounting'
UNION ALL   SELECT  'cto_bill', 'executives' ) pl ( Name, IsMemberOf )
INNER JOIN Principal p
    ON  p.Name = pl.Name
INNER JOIN Principal m
    ON  m.Name = pl.IsMemberOf;

Then use pretty much any tree-traversing method you'd like, such as a Recursive CTE, to determine various membership properties:

;WITH RECURSIVE cte_Membership AS (
    SELECT  p.Principal_PK, p.Name, pm.IsMemberOfPrincipal_FK
    FROM    Principal p
    LEFT JOIN PrincipalMembership pm
        ON  pm.Principal_FK = p.Principal_PK
    UNION ALL
    SELECT  c.Principal_PK, c.Name, pm.IsMemberOfPrincipal_FK
    FROM    cte_Membership c
    INNER JOIN Principal p
        ON  p.Principal_PK = c.IsMemberOfPrincipal_FK
    INNER JOIN PrincipalMembership pm
        ON  pm.Principal_FK = p.Principal_PK )
SELECT  m.Name, p.Name AS IsMemberOf
FROM    cte_Membership m
LEFT JOIN Principal p
    ON  p.Principal_PK = m.IsMemberOfPrincipal_FK
ORDER BY m.Name, IsMemberOf;

Edit: It's probably very much worth mentioning that management of solutions involving self-referencing keys and recursive traversals can go tits up in a hurry if caution isn't taken to ensure no cyclic references are made.  If somebody were to "accidentally" make accounting a member of the executives group, the sample CTE would no longer function, and as the complexity of the hierarchy increases, so to does the likelihood of such an error.  Safeguarding against cyclic references is a bit of a pain in the ass, frankly, but hiding CRUD operations against these tables in stored procedures can help immensely in cases like this one.  Since you're attempting to replicate a preexisting solution, perhaps there's some guidelines in that system which can assist in developing a suitable litmus test for the new one.


SSIS 2017 Scale Out functionality and use cases

I found theses nice posts that explain when and how work SSIS Scale Out feature. Here are some passages  :

"If your ETL processing looks like one of these scenarios, this could be for you.

You have a workloads that peak at certain times
at a specific time of day (overnight load, during business hours)
at specific times of month or year (month end, black Friday)
You have a variety of unrelated ETL jobs owned by different people or business areas running on the same server and competing for resources at the same time
You have an increasing data volume due to business growth, and you want your ETL solution to grow with you
You have a variety of unrelated ETL jobs owned by different people or business areas installed on different servers making it hard to know what's installed where
"

Steph Martin @ coeo
https://blog.coeo.com/getting-started-with-ssis-scale-out

"If you have lots of disparate processes and packages that are unrelated and independent of each other, then enabling scale out will work for you without having to change anything on your existing jobs.

If you are using master packages to handle execution of a set of packages you will probably have two main drivers; simplicity of management, and parallel execution. You can move directly to using scale out and retain both of these things, but you won't necessarily gain anything. If your workload is a single end to end load, all handled by a single master package (for example a data warehouse ETL load that pulls data from multiple source tables, stages the data, transforms the data and then loads to the destination), then all you are going to achieve is moving the same workload to a different server.

In order to benefit from scale out you would need to execute each package separately. If you do that in a single agent job with multiple steps you will lose your parallelism. You could have multiple jobs all executed at the same time, but that just feels wrong, and will get hard to maintain and monitor, especially if you have lots of agent jobs already."

Steph Martin @ coeo
https://blog.coeo.com/executing-packages-with-ssis-scale-out

Hope this will answer your questions


This DMV only maintains statistics since the last SQL Server restart; the view gets wiped out completely and everything starts from scratch.

More importantly, the rows in this view for any specific index are removed when that index is rebuilt (but not when it is reorganized). If you are performing regular index maintenance, it might be useful to look at the maintenance logs and see if any of the indexes you're considering dropping might have been rebuilt recently.

So, making decisions based on low reads since the last restart, when the last restart may have been for last week's patch Tuesday updates or yesterday's service pack, might not be wise. Or when you are performing index maintenance, since the last rebuild. There may be a report that is only run once a month, or once a quarter, or once a year, and it is run by an important and impatient person.

Further, an index might be there for something that will happen in the future that you don't know about - a series of reports being prepared for tax season, let's say.

So, my advice is:

Use the DMV to identify candidate indexes to remove, but don't make that decision in a bubble - you need to do the legwork to determine why an index might exist before dropping it, even if it looks like it isn't currently being used.


Are there tools to perform data comparison between two different schemas?

It's not impossible to compare two different schemas, it's a calculation of how confident you are in the result.  I've essentially borrowed from Bank Reconciliation techniques

Important: This reconciliation isn't about making sure the destination exactly matches the source in it's data context (there's a reason you're migrating to a new system), but you do need to explain any discrepancies!

Basis:

Identify metrics that you will use to measure (ie: total number of users, sum of all their ages, list of user id's and their postcodes...) I try to use several metrics from: Totals, Averages & sample/detailed records
Dump this data into a common location (via views/reports/whatever is reasonable)
Compare your data and make sure any discrepancy can be explained

I've generally split the comparison into several methods (in particular for the detailed metrics):

By Table: Usually using the Source Table as the definition, creating a set of views on the destination DB to try and reproduce the Source Table Data
By Object: Depending on the DB and what you're storing it might make more sense to create a holistic view of the objects (ie: the user) across several tables, similar to above creating a set of views to return that object as a set of results
By Reports: If the Source DB has a good comprehensive set of Management Reports, another technique is to reproduce those reports with the exact same formatting

Techniques:

No matter what method I've used to produce what will be compared, I end up with a set of files/views/dbs that hold the reconcilable Source and Destination data, then depending on the medium I can use one of the commonly available tools to compare them.  My own preferences are:

File Comparison
Create two different folders for SourceDB and DestinationDB (time/version stamped so I know when I did the reconciliation) and dump out the output of my metrics as relevantly named files, then use a comparison tool (such as CSDiff) to identify differences
Excel Comparison
In particular when dealing with Management reports, I'll create a set of Excel of workbooks to compare the reports (in essence making use of VLookups and comparing totals)
Data Comparison
Rather than outputting the reconciliation data to files or reports, output them to separate DBs, then use something like Redgate SQL Data Compare to compare the DBs

Other tools:

Not tried any of these, but a quick google search on 'Data reconciliation tools' gives:

n-Tierfs Data Reconciliation Software
Testing Performance Data Reconciliation

There's others, but those were the ones that looked reasonable


Separate stored procedures for inserts and updates?

As far as I understand it you are not actually talking about an UPSERT here just combining two different CRUD operations in one stored procedure.

CREATE PROC InsertOrUpdateYourTable @Id int = NULL OUTPUT,
                                    @Foo INT,
                                    @Bar VARCHAR(10)
AS
    IF @Id IS NULL
      BEGIN
          INSERT INTO YourTable
                      (Foo,
                       Bar)
          VALUES      (@Foo,
                       @Bar)

          SET @Id = SCOPE_IDENTITY()
      END
    ELSE
      BEGIN
          UPDATE YourTable
          SET    Foo = @Foo,
                 Bar = @Bar
          WHERE  Id = @Id
      END

The benefit I see to this is that you don't have to maintain two separate parameter lists if the table structure changes. The disadvantage is that the single stored procedure now has two responsibilities and is somewhat less easy to understand.

I would generally opt for separating them into two stored procedures.

RE: "Can you elaborate how an upsert will look"

CREATE PROC UpsertYourTable
@Id int,
@Foo int,
@Bar varchar(10)
AS
MERGE YourTable WITH (HOLDLOCK)  AS T
        USING ( VALUES ( @Id, @Foo, @Bar ) )
              AS source ( Id, Foo, Bar)
        ON ( T.Id = source.Id )
        WHEN MATCHED
            THEN
        UPDATE SET
                Foo = source.Foo ,
                Bar = source.Bar
        WHEN NOT MATCHED
            THEN
        INSERT  (Id, Foo , Bar)
               VALUES
               (@Id, @Foo , @Bar);

This assumes that Id is no longer an IDENTITY column. The reason for using HOLDLOCK is explained here.


Error Calling Python module function DbMySQLFE.connect

MySQL Workbench version < 8.x cannot login to a MySQL server when a cached sha2 password is used for that user. This auth method has been made the default only recently right before the 8.0.11 server was released. All new accounts will have this auth method unless you switched this off in your server config.

Download the MySQL Workbench release 8.0.11 RC to be able to use such accounts (this is a development release) or use the command line to change the auth method for that user to native, which will allow older Workbench versions to login.


It's not so hard to do this transformation. Step by step:

DATEADD(DAY
       , ISNULL(a.[due_days], 30) + 30
       , [dbo].[CalcDate]([type], date1, date2, date3, date4, NULL)
       ) < GETDATE()

means:

[dbo].[CalcDate]([type], date1, date2, date3, date4, NULL)
  + (ISNULL(a.[due_days], 30) + 30) DAYS
< GETDATE()

then we have to break the ISNULL() into 2 cases:

    [dbo].[CalcDate]([type], date1, date2, date3, date4, NULL)
      + (a.[due_days] + 30) DAYS
    < GETDATE()
OR
    a.[due_days] IS NULL
  AND
    [dbo].[CalcDate]([type], date1, date2, date3, date4, NULL)
      + (30 + 30) DAYS
    < GETDATE()

which can be written as:

    (a.[due_days] + 30) DAYS
    < GETDATE() - [dbo].[CalcDate]([type], date1, date2, date3, date4, NULL)
OR
    a.[due_days] IS NULL
  AND
    (30 + 30) DAYS
    < GETDATE() - [dbo].[CalcDate]([type], date1, date2, date3, date4, NULL)

so we can use DATEDIFF():

    (a.[due_days] + 30)
    < DATEDIFF( day
              , [dbo].[CalcDate]([type], date1, date2, date3, date4, NULL)
              , GETDATE()
              )
OR
    a.[due_days] IS NULL
  AND
    (30 + 30)
    < DATEDIFF( day
              , [dbo].[CalcDate]([type], date1, date2, date3, date4, NULL)
              , GETDATE()
              )

and finally:

    a.[due_days]
    < DATEDIFF( day
              , [dbo].[CalcDate]([type], date1, date2, date3, date4, NULL)
              , GETDATE()
              ) - 30
OR
    a.[due_days] IS NULL
  AND
    30
    < DATEDIFF( day
              , [dbo].[CalcDate]([type], date1, date2, date3, date4, NULL)
              , GETDATE()
              ) - 30

Corrected, taking care of the time parts:

    a.[due_days]
    < DATEDIFF( day
              , [dbo].[CalcDate]([type], date1, date2, date3, date4, NULL)
              , GETDATE()
              ) - 30
      - CASE WHEN DATEADD( day
                         , DATEDIFF( day
                                   , [dbo].[CalcDate]([type], date1, date2, date3, date4, NULL)
                                   , GETDATE()
                                   )
                         , [dbo].[CalcDate]([type], date1, date2, date3, date4, NULL)
                         ) > GETDATE()
             THEN 1 ELSE 0
        END
OR
    a.[due_days] IS NULL
  AND
    30
    < DATEDIFF( day
              , [dbo].[CalcDate]([type], date1, date2, date3, date4, NULL)
              , GETDATE()
              ) - 30
      - CASE WHEN DATEADD( day
                         , DATEDIFF( day
                                   , [dbo].[CalcDate]([type], date1, date2, date3, date4, NULL)
                                   , GETDATE()
                                   )
                         , [dbo].[CalcDate]([type], date1, date2, date3, date4, NULL)
                         ) > GETDATE()
             THEN 1 ELSE 0
        END

You could simplify it a bit, with the use of CROSS APPLY:

CROSS APPLY
    ( SELECT gdt = GETDATE(),
             calc = [dbo].[CalcDate]([type], date1, date2, date3, date4, NULL)
    ) AS c
CROSS APPLY
    ( SELECT diff = x.diff - CASE WHEN DATEADD( day, x.diff, c.calc ) > c.gdt
                                 THEN 1 ELSE 0
                             END
      FROM
          ( SELECT diff = DATEDIFF( day, c.calc, c.gdt) - 30
          ) AS x
    ) AS y
----
    WHERE (  a.[due_days] < y.diff
         OR  a.[due_days] IS NULL  AND  30 < y.diff
          )


What is the easiest way to use a SQL Server backup to retrieve just a few rows?

There is no way to restore either a single table or a few rows in SQL server without a third party product. If the table for which you are trying to retrieve few rows is in a separate file group you can restore only that filegroup (assuming you have Enterprise edition).

Else use a third party product like Quest Litespeed. I believe it has a feature that lets you to restore just a table from backup.

If your database is huge, use backup compression (available in SQL 2008 R2 standard and enterprise edition). Usually, a compressed backup takes less time to restore.


MS SQL Server create user who can access only those databases which they have created

Welcome to the world of SQL Server Security!

Probably the best place to start is by reading the Microsoft Securing SQL Server Guide. This covers all the basics from securing the physical SQL server to database and user permissions.

An alternative view is the SQL Server Central Stairway to SQL Server Security


I had streaming replication set up using a replication slot, but the slave did not use the slot.
Writing

primary_slot_name = 'replication_slot_1'

in /var/lib/postgresql/9.5/main/recovery.conf on the slave solved the problem.


How to remove wildcard character from field

Apparantly the quote should be \\:

UPDATE name SET name = REGEXP_REPLACE (name, '\\*', '');

Fiddle


Most efficient way of bulk deleting rows from postgres

Your second option is far cleaner and will perform well enough to make that worth it.  Your alternative is to build gigantic queries which will be quite a pain to plan and execute.  In general you are going to be better off letting PostgreSQL do the work here.  In general, I have found updates on tens of thousands of rows in the manner you are describing to perform adequately, but there is one important thing to avoid doing.

The way to do it is to use a select and a join in your delete.

DELETE FROM foo WHERE id IN (select id from rows_to_delete);

Under no circumstances should you as follows with a large table:

DELETE FROM foo WHERE id NOT IN (select id from rows_to_keep);

This will usually cause a nested loop antijoin which will make performance rather problematic.  If you end up having to go that route, do this instead:

DELETE FROM foo
WHERE id IN (select id from foo f
          LEFT JOIN rows_to_keep d on f.id = d.id
              WHERE d.id IS NULL);

PostgreSQL is usually pretty good at avoiding bad plans but there are still cases involving outer joins which can make a big difference between good and bad plans.

This is wandering a little more far afield, but I figure it is worth mentioning because of how easy it is to go from the IN to NOT IN and watch query performance tank.


Do isolation levels only apply to SELECTS and not UPDATES?

In MS SQL Server I can say that it depends on your isolation level.  If you set it more similar to Oracle by making it 'snapshot isolation', the last row that thread A is trying to update will have the 'before' value copied to tempdb, and all reads will read that while it is being updated via a row lock.

If you have the default isolation level of read committed, it will lock that row (or in some cases page) blocking your reader from that particular row or page while it updates it.  This is unless you specify SET ISOLATION LEVEL READ UNCOMMITTED or put a (NoLock) hint on the FROM clause of the query, thus allowing dirty reads.

It all has to do with the Isolation level in ACID, and it's the only ACID property that you have control over in MS SQL Server.


Unable to backup postgresql database. pg_dump memory alloc error

After a lot of help from over at the PostgreSQL irc channel it was determined that the database was corrupted. The most likely cause of which was a hardware error on my ageing PC but also and much less likely a potential PG bug. I'm going to have to start again


Yes, it will definitely add overhead if you have foreign keys pointing to the table that you are trying to clear records from. This is because it needs to ensure that the constraint is not violated once the data has been removed. I've personally seen deletes take days with foreign key checks, but hours if you remove the foreign keys before performing the DELETE.

Disabling the foreign keys will not help in your scenario as you can only disable a foreign key check for INSERT and UPDATE statements. I recommend that you remove any dependent data from the related tables, then remove the foreign key (if possible), and then remove the data from the base table. Once the data has been completely removed you can re-add the foreign key relationship.


Does postgres optimise repeated expressions?

Yes, the expression will be evaluated multiple times. This is easy to test:
test=> \timing
Timing is on.
test=> SELECT pg_sleep(id)
       FROM (VALUES (1), (2), (3)) AS x(id)
       WHERE pg_sleep(id) IS NOT NULL;
 pg_sleep
══════════

(3 rows)

Time: 12013.768 ms (00:12.014)

If you want to avoid that, use a subquery:
test=> SELECT v
       FROM (SELECT pg_sleep(id) AS v
             FROM (VALUES (1), (2), (3)) AS x(id)) AS t
       WHERE v IS NOT NULL;
 v
═══

(3 rows)

Time: 6007.817 ms (00:06.008)


An unknown error occurred while processing the request on the server

I found the answer, the wrong version of the Report Viewer was installed on the server.


Any suggestions on having master-master replications around the world by nosql solutions?

The two NoSQL DBs that come to mind when mentioning cross datacenter replication ("master-master replications around the world") are:

Cassandra
Couchbase

Both of them seem to have fairly decent cross datacenter replication abilities, so which you should go with should be decided upon in a different manner.

If you want a KV-Store (where you can simply look up binary blobs using keys), then you ought to go with Couchbase.

If you want something similar to the SQL concept of rows and tables and whatnot (albeit, without relational functionality such as JOIN), then Cassandra and CQL might suit you better.


SQLITE - Update multiple columns, in multiple rows on a table with a composite PK

Finding each row to update requires an index lookup. Writing a combined query would not change this.
Minimize the transaction overhead by running all UPDATEs inside a single transaction. Further performance improvements are not possible.


Insert values in table from front-end CategoryId

On Button Click, I got to do something like this, as suggested by @MarkSinkinson.

protected void btnSubmit_Click(object sender, EventArgs e)
{
    SqlCommand cmd = new SqlCommand("Insert into CategoriesForMerchant (CategoryName) values (@CategoryName)", conn);
    cmd.Parameters.AddWithValue("@CategoryName", txtCategory.Text);
    conn.Open();
    cmd.ExecuteNonQuery();
    conn.Close();
    Response.Write("<script>alert('File uploaded successfully');</script>");
}


Deadlock from mysqldump --single-transaction, is it possible?

PROBLEM

It is logically possible because --single-transaction gets  "thrown under the bus" if any ALTER TABLE commands are launched intermittently during the mysqldump (See  this post from mysqlperformanceblog.com).

What happens when a dump faces off against an ALTER TABLE ?

If session #1 is running the mysqldump on mydb.mytable, an ALTER TABLE from session #2 may pause because it needs to perform a table lock.
If session #1 is running ALTER TABLE on mydb.mytable, it could produce spurious results from session #2 trying to run mysqldump on that table (Possible empty table [missing data])

It is not so much a deadlock in the traditional sense, but it safe to say that the --single-transaction option can get its loyalty displaced and lose it point-in-time window from the first victimized table and all other tables after it.

SOLUTION

Setup Master/Slave Replication

Use binlog_format = ROW to have changes as granular as possible
Perform mysqldumps of the Data from the Slave

STOP SLAVE;
mysqldump --single-transaction ...
START SLAVE:

You should never encounter deadlocks as long as the Slave is used for reads and backups only.

GIVE IT A TRY !!!


Calculate number of days since from timestamp

You can do this:

SELECT DATE_PART('DAY', date_time - '1899-12-31 00:00:00+01'::TIMESTAMPTZ)
FROM date_test;

(typo in previous answer - had 1899-01-01 instead of 1899-12-31.)

See the dbfiddle here:

Just also note in the 2nd last field in the fiddle (added):

SELECT TIMESTAMP 'epoch' + 1494161691 * INTERVAL '1 second';

Result:

epoch_date
2017-05-07 12:54:51

Which is the date in the main question.

So, it would appear that your field called millisec is, in fact, seconds from 1970 (aka UNIXTIME or similar in other RDBMS). Check out this link. Unless you are storing milliseconds, then I would strongly suggest that you shouldn't call it millisec.

Create and populate table:

CREATE TABLE date_test
(
  id INTEGER NOT NULL,
  millisecs INTEGER NOT NULL,
  date_time TIMESTAMPTZ NOT NULL
);

INSERT INTO date_test
VALUES
( 126952 , 1494161691 , '2017-05-07 13:54:51+01'),
( 126952 , 1494161692 , '2017-05-07 13:54:52+01'),
( 126952 , 1494161693 , '2017-05-07 13:54:53+01'),
( 126952 , 1494161694 , '2017-05-07 13:54:54+01'),
( 126952 , 1494161695 , '2017-05-07 13:54:55+01');

Then run the query above - result:

date_part
    42861
    42861
    42861
    42861
    42861

Links here and here helped!


Linked Server to Mirrored Database Fails

This is a known issue.  Linked servers which are looking for database mirroring logins only work for Windows logins not SQL Logins as talked about on this connect item.  Basically you need to either hard code a username and password in the linked server, which isn't recommended or use Windows authentication to access the linked server.

Another option would be to setup alerts and SQL Agent jobs on the two machines so that when the database becomes active on those machines they automatically reconfigure the linked server with the correct servername.


Backup a database from Azure SQL Managed Instance and restore to on premise SQL Server

I've been able to export data from an Azure Managed Instance (MI) to a SQL Server 2012 system successfully without the need of Visual Studio.

Using SQL Server Management Studio (SSMS) right click on the database in you want to copy choose Tasks > Export Data-tier Application. Choose where to save the .bacpac file that is created.

A .bacpac file is a zip file containing XML that defines your tables and relationships along with BCP files that store the data of each table.

If you have 7zip extract the bacpac file to see the contents. If you don't, you can rename the file extensions from .bacpac to .zip and extract it using File Explorer (right click > Extract All...)
Open the model.xml file in your favourite text editor and look for the attribute called CompatibilityMode. This will have been set to the compatibility level of the MI (currently 150). Change it to the compatibility level you are importing to. I was importing to SQL 2012 so changed it to 110.
Remove any other incompatible features by commenting out the relevant line in model.xml.

For example, SQL 2012 can't do In-Memory tables so I had to comment out this section from the model.xml file:

    <!--<Element Type="SqlFilegroup" Name="[XTP]">
            <Property Name="ContainsMemoryOptimizedData" Value="True" />
    </Element>-->

Setting the Value to false may also work.

Putting it back together. Microsoft have added some simple validation to tell if the bacpac file is corrupt or has been tampered with. If you have made any changes to model.xml file you will need to calculate a new checksum value for it. The value is stored in the Origin.xml file in the bacpac.

You can generate a new checksum value by using PowerShell:

Get-FileHash -Path C:\Path\To\Extracted\bacpac\model.xml | Format-Table -Property Hash

You should get an output like this:

Hash
----
ED8754D205946850F3CED0B033342BF3D88A43E146E57D861191AACC1AC1659A

Open the Origin.xml file from the extracted bacpac files and look for a line like this at the end and replace the existing hash value with the new one:

<Checksums>
  <Checksum Uri="/model.xml">ED8754D205946850F3CED0B033342BF3D88A43E146E57D861191AACC1AC1659A</Checksum>
</Checksums>

Make sure you don't add any spaces or extraneous characters.

Now zip the files back up making sure the top level of the archive is the files and not the folder you extracted everything too. If you open the new file you should see the xml files and not a single folder name.

You should now be able to import the bacpac into your non-Azure SQL database.

Some notes  about bacpac backups:

They may not be transactionally consistent. For an export to be transactionally consistent, you must ensure either that no write activity is occurring during the export, or that you are exporting from a transactionally consistent copy of your Azure SQL database.
If you are exporting to blob storage, the maximum size of a BACPAC file is 200 GB. To archive a larger BACPAC file, export to local storage.


Include all records in between two dates

So if you're trying to find all active/valid records in a time frame, and you are using intervals, then you must use the following criteria:

End date was on/after the start of the window
Start date was on/before the start of the window

This is working on the assumption that a record is still effective as of the end date, as indicated in your example.
So your query becomes:
SELECT
  Id
 ,start_time
 ,end_time
FROM
  <your table>
WHERE
  start_time <= '2019-07-25'
    AND end_time >= '2019-07-22'

A simple truth table helps verify:
Id  | Start_Time  | End_Time    | Start <= 2019-07-25 | End >= 2019-07-22
1   | 2019-07-22  | 2019-07-25  | Y                   | Y
2   | 2019-07-20  | 2019-07-25  | Y                   | Y
3   | 2019-07-20  | 2019-07-21  | Y                   | N
4   | 2019-07-20  | 2019-07-25  | Y                   | Y
5   | 2019-07-22  | 2019-07-22  | Y                   | Y
6   | 2019-07-25  | 2019-07-25  | Y                   | Y
7   | 2019-07-26  | 2019-07-27  | N                   | Y

Other notes:
Those are dates, not times.  Column should be Start_Date/End_Date or Start_Datetime/End_Datetime if you are include hours/minutes/seconds (you can abbreviate with Dt/Dtm if you wish).
From comment:

I assume that if I want to filter only for the startdate, I can do
WHERE end_time >= '2019-07-22 00:00:00' But I am not sure about query
only for the enddate. What is the correct condition? WHERE start_time
<= '2019-07-25 23:59:59' OR WHERE '2019-07-25 00:00:00' BETWEEN
start_time AND end_time

So here we need to be careful with what question is actually be asked, and what answer the query is providing.
The statement WHERE end_time >= '2019-07-22 00:00:00' is the same as saying: any records that end OR start on/after 2019-07-22 00:00:00.
The inverse of that statement would be any records that start AND end before 2019-07-22 00:00:00, which is WHERE end_time < '2019-07-22 00:00:00'.
So if your question is actually "Which records end or start on/before 2019-07-22 00:00:00 the condition is WHERE start_time <= 2019-07-22 00:00:00


Maintenance Plan Wizard generates invalid date parameter in script for delete

It's not related to the date format, the generated T-SQL from Maintenance Plans doubles up the single quotes (') in the generated script. A double quote escapes a single quote inside quoted text.

For example:

SELECT 'Figure 1'

would return the value "Figure 1", whereas:

SELECT ''Figure 1''

Would throw an incorrect syntax error similar to yours.

When you extract the T-SQL for a Maintenance Plan task, you should copy it into a new query window and do a Find & Replace to replace '' with '. You may have to run it twice depending on the generated script.


How does a memcached bucket behave when fully loaded?

If you do not set a TTL, the object will never expire.

The latter part of your questions sounds like you are running into memcached's cache eviction strategy where it will throw out of the objects that have least recently been used (LRU) to make room for newer items.


How to reclaim disk space on PostgreSQL?

Although you did not state it, I assume from your references to documents that you have followed that you have done a VACUUM FULL on the database and/or affected tables. You also didn't specify what postgresql version you are using - I will assume it is > 9.0 (VACUUM FULL behaved differently before this).

VACUUM FULL will rewrite affected tables into new files, then delete the old files. However, if any process still has the old file open, the operating system will not actually delete the file - until the last process has closed it.

If practical, restarting the database would ensure that all open files get closed.

If that is not practical, then you may be able to verify if this is your problem, and find out which process has the files open.

If using Linux (or most other Unix-like systems) you can use the 'lsof' command to get a listing of all files open in all processes. Files which are open but which have since been deleted will have '(deleted)' appended to the filename. So, you can grep the output of lsof, looking for deleted files, like this:

sudo lsof -u postgres | grep 'deleted'

If that identifies processes which still have the old files open, you can use pg_terminate_backend to terminate that process:

SELECT pg_terminate_backend(xxx);

where xxx is the PID of the process, found in the lsof output.

If using Windows, the same principle could apply, because postgres opens files using the FILE_SHARE_DELETE flag, which allows it to delete files that are open in another process. The 'handle' command is the rough equivalent of lsof, although I am not sure if you can tell if the files are deleted or not so some additional work might be required.

It is another question as to why any such processes would be hanging on to old file handles. However in the thread you quoted in your question, Tom Lane seems to imply that it can happen.


Have you looked under Advanced Security Settings?

The arrows point to the modifications I've often had to specifically apply when installing SQL Server on Windows 2012 R2.

For the SQL Server Root Directory, I'll give full control to the SQL Server Process account.

Related official documentation:

Configure File System Permissions for Database Engine Access
Securing Data and Log Files


Select only rows that equal x but not x and y?

The question is not very clear. If what you want is to have as result **every object that is related to name 'orange' but not to name 'banana', here is one way:

SELECT o.*                  -- Change this to only the columns needed.
FROM objects AS o           -- If there are data needed from other tables,
                            -- join here.
WHERE EXISTS
      ( SELECT 1
        FROM name_relationships AS nr
          JOIN name_data AS nd USING (data_id)
          JOIN name AS n  USING (name_id)
        WHERE nc.name = 'orange'
          AND n.object_id = o.object_id
      )
  AND NOT EXISTS
      ( SELECT 1
        FROM name_relationships AS nr
          JOIN name_data AS nd USING (data_id)
          JOIN name AS n  USING (name_id)
        WHERE n.name = 'banana'
          AND nr.object_id = o.object_id
      ) ;


Can I use the same keywords/symbols as DATEADD and DATEPART in user-defined functions

Although you can't pass a datepart keyword as a parameter to a user-defined function, you can pass a string literal and perform the needed conversion. Below is an table-valued function example that leverages a numbers table to return an adhoc range of datetime values you can extend as needed.

CREATE TABLE dbo.Numbers(
    Number int NOT NULL
        CONSTRAINT PK_Numbers PRIMARY KEY
    );
WITH
     t10 AS (SELECT n FROM (VALUES(0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) t(n))
    ,t1000 AS (SELECT 0 AS n FROM t10 AS a CROSS JOIN t10 AS b CROSS JOIN t10 AS c)
    ,t1000000 AS (SELECT ROW_NUMBER()
        OVER (ORDER BY (SELECT 0)) AS num FROM t1000 AS a CROSS JOIN t10 AS b CROSS JOIN t10 AS c CROSS JOIN t10 AS d)
INSERT INTO dbo.Numbers WITH (TABLOCKX)
SELECT num - 1 AS Number
FROM t1000000;
GO

CREATE FUNCTION dbo.DATESEQUENCE(
      @DatePartName varchar(7)
    , @StartValue datetime2(0)
    , @EndValue datetime2(0)
    )
RETURNS TABLE AS
RETURN(
    WITH UnitOfMeasure AS (
        SELECT CASE @DatePartName
            WHEN 'second' THEN 1
            WHEN 'minute' THEN 60
            WHEN 'hour' THEN 3600
            WHEN 'day' THEN 86400
            WHEN 'month' THEN 1
            WHEN 'year' THEN 12
        END Units
    )
    SELECT
        CASE WHEN @DatePartName IN('month', 'year') THEN
            --use month for month and year
            DATEADD(month, Number * Units, @StartValue)
        ELSE
            --use second for second, minute, hour, and day
            DATEADD(second, Number * Units, @StartValue) END AS DateSequence
    FROM dbo.Numbers
    CROSS JOIN UnitOfMeasure
    WHERE
        Number <= CASE WHEN @DatePartName IN('month', 'year') THEN
            DATEDIFF(month, @StartValue, @EndValue) / Units
        ELSE
            DATEDIFF(second, @StartValue, @EndValue) / Units
        END
);
GO


User privileges error on server maria db Create trigger Drop trigger

Believe it or not, I wrote about this back on Jun 20, 2014. If you look in my post MySQL user without SUPER privilege, I clearly explain how running command like

GRANT ALL PRIVILEGES ON db1.table TO 'user1'@'%';
GRANT ALL PRIVILEGES ON db1.* TO 'user1'@'%';

never gives away SUPER privilege

If you run this query

SELECT table_name FROM information_schema.columns
WHERE table_schema='mysql' AND column_name='super_priv';

it only comes back with user.

+------------+
| table_name |
+------------+
| user       |
+------------+

Here is something interesting

In MySQL 5.6, running DROP TRIGGER requires the TRIGGER privilege. Same is true for MySQL 5.7, MySQL 5.5, and MySQL 5.1
In MySQL 5.0, running DROP TRIGGER requires the SUPER privilege

Running this query

SELECT table_name FROM information_schema.columns
WHERE table_schema='mysql' AND column_name='trigger_priv';

comes back with

+------------+
| table_name |
+------------+
| db         |
| user       |
+------------+

This shows you that globally and at the database level you can grant the TRIGGER privilege, provided the user can access the specified schema. You cannot give away the TRIGGER privilege to a user just for a single table.

Thus, if a user has full access to database db1 and you want that user to drop triggers in another database (such as db2), then simply run

GRANT TRIGGER ON db2.* TO user1@'%';

Be very careful how you assign individual grants like this

In your particular case, DROP TRIGGER requires TRIGGER privilege in MariaDB


How can i detect whom a person reports to using a self-referencing employee table?

Since to me it's already a well known and solved problem, and since the commentary on here seems to agree with me:

https://stackoverflow.com/questions/3859882/sql-recursive-function-to-find-managers

Are you wanting to return the results for each lowest employee in the set, and that's what's making it hard?


First of all I question hiring someone you consider an possible internal threat, but that aside your problem is probably a login vs user problem.  When you do an EXECUTE AS you are executing as a user.  VIEW SERVER STATE is an instance or login level permission.  You can set your database to trustworthy and then it will act as if you are using the login rather than the user.  However if you are using a DBA you don't trust then you would be creating a much larger potential problem than just granting him VIEW SERVER STATE.


The third option is to keep a transaction log of points transactions.

Use the schema from your first option to record the "going rate" for points.  Whenever you have a transaction that either gives or takes points away from a user, write a record (or multiple records) into a points transaction log table.

The transaction log table will have a FK to USERS and a date/time to indicate when the transaction took place.  It could also have FK's to the items which generated the points, such as FK's to your TRANSACTIONS and REPORT_DETAILS tables, that's up to you.

Most importantly, the transaction log table will have a column for the number of points awarded(+) or spent(-).  To get a user's point balance, you run a sum of all transactions for the user up until the current time.  The important thing is to keep a record of how many points were given or taken for that user, at that time regardless of what may happen to other users or at other times.

If performance becomes an issue, you can keep the user's current point total on the USERS table, but this should be denormalized data based on the transaction log details.  If you do this, then you need to have controls in place to make sure you're managing this redundancy appropriately.


Get the average of the last X records for each foreign key

You need to find the three latest rows. That is typically done using a window function. The result of that can then be aggregated:

select yacht_id,
       avg(hcpval) as hcpval
from (
  select yacht_id,
         hcpval,
         row_number() over (partition by yacht_id order by id desc) as rn
  from handicaps
)  t
where rn <= 3
group by yacht_id
order by yacht_id;

Online example: https://rextester.com/DDTU17483


This:

  cast(to_char(total_price, 'C9,999,999.00')as integer),

is wrong.

Assuming total_price is defined as number the result of converting e.g. 1234.56 will be:

EUR1,234.56

(the exact output depends on your NLS settings).

Now the string EUR1,234.56 is not a valid number any more (at least not to a computer). So your cast to an integer fails.

If you only want to display the total price as a formatted string, only to_char() is required. Remove the cast()


A very fast shot: do you need the sorting? Especially in your getUpdatedAgentGroup function? This may can be a quickfix. Another issue are indexes, have you them in place?


Choice of clustered key when almost all columns would be part of a unique index anyway

The big reasons for the "Keep the clustered key sequential" advice is all around insert/update/delete operations and fragmentation.  As you make changes to the values (insert new ones out of order, updating an old value that causes it to move, deleting a value) SQL has to move data around on the pages and do page-splits to keep the data in order (the whole purpose of a clustered index).  This is going to take time and create fragmentation which will slow everything down over time.

If you are doing very few updates (including inserts/deletes) or rebuild your table every time the updates are done (truncate & then batch load) then a clustered index on values that aren't naturally in order can work for you.  Otherwise stick with your sequential value.

As far as your index goes, based on just that one query, I would prefer this:

CREATE INDEX ix1 ON COMM_SHELF_BLOCK_ACCESS(EmployeeGuid, PracticeVid) INCLUDE (ShelfGuid)

You don't want the ShelfGuid up front because then it has to do an index scan to find the EmployeeGuid that it wants.  If you aren't actually going to be searching on ShelfGuid and just want to return the value then take it out of the tree level of the index entirely and just put it in the leaf level (by INCLUDING it).

If you have the room for the index (which I'm guessing you do) then don't worry about it being almost the same size as the table.  It's still a index that sorts your data in a specific order.  I'm guessing your goal here is to speed up your query not to conserve space.  If you are tight on space and can't afford more then you have a different issue and I would consider redoing your table with something other than GUIDs :).


Most efficient data type for a column storing numeric values between 0 and 100

This answer only covers SQL Server. The answer depends on how you define efficient: is it space or CPU? There can be a tradeoff between the two.

Let's start by checking the documentation for data types that store integer data:

╔═══════════╦══════════════════════════════════════════════════════════════════════════╦═════════╗
║ Data type ║                                  Range                                   ║ Storage ║
╠═══════════╬══════════════════════════════════════════════════════════════════════════╬═════════╣
║ bigint    ║ -2^63 (-9,223,372,036,854,775,808) to 2^63-1 (9,223,372,036,854,775,807) ║ 8 Bytes ║
║ int       ║ -2^31 (-2,147,483,648) to 2^31-1 (2,147,483,647)                         ║ 4 Bytes ║
║ smallint  ║ -2^15 (-32,768) to 2^15-1 (32,767)                                       ║ 2 Bytes ║
║ tinyint   ║ 0 to 255                                                                 ║ 1 Byte  ║
╚═══════════╩══════════════════════════════════════════════════════════════════════════╩═════════╝

For your data you could use TINYINT because all of your data fits within 0 to 255 and that would use the least amount of space. Let's do a quick test by inserting 10 million rows into tables with values equally distributed between 0 and 100. Note that we use ten columns for all example tables because a rowstore table has a minimum row size of 9 bytes. If we created a table with just one column we would get misleading results. I am testing against SQL Server 2016 SP1:

DROP TABLE IF EXISTS dbo.X_TINYINT;

CREATE TABLE dbo.X_TINYINT (
    NUM1 TINYINT NOT NULL,
    NUM2 TINYINT NOT NULL,
    NUM3 TINYINT NOT NULL,
    NUM4 TINYINT NOT NULL,
    NUM5 TINYINT NOT NULL,
    NUM6 TINYINT NOT NULL,
    NUM7 TINYINT NOT NULL,
    NUM8 TINYINT NOT NULL,
    NUM9 TINYINT NOT NULL,
    NUM10 TINYINT NOT NULL
);

INSERT INTO dbo.X_TINYINT WITH (TABLOCK)
SELECT TOP (10000000)
  n.n, n.n, n.n, n.n, n.n
, n.n, n.n, n.n, n.n, n.n
FROM master..spt_values t1
CROSS JOIN master..spt_values t2
CROSS JOIN master..spt_values t3
CROSS APPLY
(
    SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) % 101
) n (n);

EXEC sp_spaceused 'dbo.X_TINYINT'; -- data size is 198216 KB

If I run the same code against a table with 10 SMALLINT columns I get 297416 KB for the data size. Based on the quoted size difference in data types I would expect a difference of about 10000000 * 10 * (2 - 1) / 1024 = 97656 KB so that's pretty close to the expected size increase.

Depending on your version and edition of SQL Server you may be able to reduce space usage further. Row compression is first available in SQL Server 2008 Enterprise and is available in all editions of SQL Server as of 2016 SP1. Based on the description of the algorithm we probably won't get a lot of savings with row compression for TINYINT columns. They already use the minimum one byte, but we should get around a 1% space reduction for the data because 0 is optimized to take no bytes. There also may be some reduction in the metadata overhead for the data types.

After applying DATA_COMPRESSION = ROW to a table I get a data size of 187808 KB.

Page compression is available in the same versions and editions as row compression. The page compression algorithm compresses the data in a few additional ways on top of row compression. With a a lot of repeated values on a page we might see significant storage gains.

After applying DATA_COMPRESSION = PAGE to a table I get a data size of 109024 KB, which is a fairly sizable reduction.

Just for fun we can check the space usage when the data is in columnstore format. Columnstore indexes were introduced in SQL Server 2012 and further improved in 2014 and 2016. These should not be used just for the purposes of saving space. You will want to research and test carefully before using them. There are also some restrictions around using them based on your SQL Server version and edition.

DROP TABLE IF EXISTS dbo.X_TINYINT_CCI;

CREATE TABLE dbo.X_TINYINT_CCI (
    NUM1 TINYINT NOT NULL,
    NUM2 TINYINT NOT NULL,
    NUM3 TINYINT NOT NULL,
    NUM4 TINYINT NOT NULL,
    NUM5 TINYINT NOT NULL,
    NUM6 TINYINT NOT NULL,
    NUM7 TINYINT NOT NULL,
    NUM8 TINYINT NOT NULL,
    NUM9 TINYINT NOT NULL,
    NUM10 TINYINT NOT NULL
);

CREATE CLUSTERED COLUMNSTORE INDEX CCI_X_TINYINT_CCI ON dbo.X_TINYINT_CCI;

INSERT INTO dbo.X_TINYINT_CCI WITH (TABLOCK)
SELECT TOP (10000000)
  n.n, n.n, n.n, n.n, n.n
, n.n, n.n, n.n, n.n, n.n
FROM master..spt_values t1
CROSS JOIN master..spt_values t2
CROSS JOIN master..spt_values t3
CROSS APPLY
(
    SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) % 101
) n (n)
OPTION (MAXDOP 1);

EXEC sp_spaceused 'dbo.X_TINYINT_CCI'; -- data size is 160 KB

I can also compress the CCI using COLUMNSTORE_ARCHIVE, which I think is designed for historical data which won't change or be read often. With that compression option applied to the whole table the data size is further reduced to 88 KB.

The rowstore compression options add CPU overhead to queries. The amount of overhead will depend on your workload and data but we can use a simple query to illustrate the basic concept:

SELECT MAX(NUM1), MIN(NUM1)
FROM dbo.X_TINYINT
OPTION (MAXDOP 1);

After one test I got CPU time measurements of 1469 ms for uncompressed data, 1687 ms for data with row compression, and 2000 ms for data with page compression. I didn't do tests for the columnstore data just because they work so differently. MIN and MAX queries can be locally aggregated or even satisfied by metadata operations in some cases.

Here is a summary of the results for the test table and query:

╔═══════════════════╦══════════════════╦══════════════════════╗
║ Table Compression ║ Data Space in KB ║ Query CPU Time in ms ║
╠═══════════════════╬══════════════════╬══════════════════════╣
║ NONE              ║           198216 ║ 1469                 ║
║ ROW               ║           187808 ║ 1687                 ║
║ PAGE              ║           109024 ║ 2000                 ║
║ CCI               ║              160 ║ N/A                  ║
║ CCI ARCHIVE       ║               88 ║ N/A                  ║
╚═══════════════════╩══════════════════╩══════════════════════╝

The exact results that you will see depend on your table structure, data, and workload.


The documentation tells you to send a signal to the master postgres process, or to let pg_ctl do this.

In SQL, you can extract the PID of the master process from pg_read_file('postmaster.pid'), but pg_cancel_backend() does not accept this PID.

However, you should be able to execute these commands with COPY (depending on what rights the postgres OS user has):

COPY (SELECT 1) TO PROGRAM 'kill -INT `head -1 /usr/local/pgsql/data/postmaster.pid`';

COPY (SELECT 1) TO PROGRAM 'pg_ctl -D /usr/local/pgsql/data stop';


Can't use SQL Server database on a networkpath

An MDF files is not the database, as such. It is merely the on-disk storage used by the SQL Server database engine.

When your app "uses" the MDF, it is actually attaching them to an SQL Server install and running queries against that. This is transparent really.

Instead of this, you can install SQL Server on the network (even Express edition), attach tehe database, and connect to that from all your app installations on the network.

This SO answer has way more details: https://stackoverflow.com/q/10366586/27535


Mongo DB sharded cluster fails when one shard is down

You have understood concept cluster and sharding wrong or you are mixing it with ideas of replica set. If cluster misses part of it (shard(s)), it is not whole and cannot function.

Replica set can function when minority of nodes are missing and that's why (sharded) cluster is normally made from replica sets. So, that every shard in the cluster is replica set, at least three nodes. So, in two shard cluster, you have actually six data nodes (2x3) and top of that you have three node config server replica set.


What are the arguments against or for putting application logic in the database layer?

Assorted thoughts...

Your database code will outlive your application client technology.
Think of ADO.NET -> Linq -> EF as well as assorted ORMs. Whereas you can still run SQL Server 2000 code from last millenium against all of the above client technologies.

You also have the multiple client issue: I have .net, java and Excel. That's 3 sets of application logic.

"Business logic" shouldn't be confused with "data integrity logic". If you do have clients starting transactions and doing assorted checks, that's a lot of db calls and a long transaction.

Application logic doesn't scale for high data volumes. We have 50k rows per second using stored procs. A sister team using Hibernate can't get one per second


Help with Transferring Linked Server to another server?

I scripted out everything from there, but not sure what's the next step or how to import them back in the new server?

Just run the scripted out version (from 2005 servers) on the new sql 2008 server.

You need to manually key-in the passwords as when you script out linked servers, passwords are not scripted out - they are grabbled - ########.

You can check the linked server properties on old server and then match them on the new server using below t-sql :

SELECT ss.server_id
          ,ss.name
          ,'Server ' = Case ss.Server_id
                            when 0 then 'Current Server'
                            else 'Remote Server'
                            end
          ,ss.product
          ,ss.provider
          ,ss.catalog
          ,'Local Login ' = case sl.uses_self_credential
                            when 1 then 'Uses Self Credentials'
                            else ssp.name
                            end
           ,'Remote Login Name' = sl.remote_name
           ,'RPC Out Enabled'    = case ss.is_rpc_out_enabled
                                   when 1 then 'True'
                                   else 'False'
                                   end
           ,'Data Access Enabled' = case ss.is_data_access_enabled
                                    when 1 then 'True'
                                    else 'False'
                                    end
           ,ss.modify_date
      FROM sys.Servers ss
 LEFT JOIN sys.linked_logins sl
        ON ss.server_id = sl.server_id
 LEFT JOIN sys.server_principals ssp
        ON ssp.principal_id = sl.local_principal_id

Edit:

I know this is old question dated in 2013, but my edit will be useful for future readers ...

If you want to move all or selected Linked servers from one server to another, a better automated option would be to use  Copy-LinkedServer (part of dbatools written by Chrissy LeMaire) can be very useful.

e.g.

  Copy-LinkedServer -Source YourSourceServerName -Destination YourDestinationServerName -LinkedServers mySQL1, lssvr1 -Force

Remember to remove -LinkedServers parameter if you want to migrate all linked servers from one instance to another.


Switch user to 'postgres' user on macOS results in "su: Sorry" error

sudo su postgres

Having found this closed Question on Stack Overflow and its duplicate, I will post here the solutions found there and there.

➥ You must use the sudo command combined with the su command.

sudo su postgres

When prompted, enter your macOS admin user password rather than the postgres user’s password. At this juncture, your Mac admin user is the one executing the sudo command, not the postgres user.

While I do not understand why we must combine the sudo with the su, it does indeed work for me in macOS Mojave. I did not find this tip among my various BSD and Linux related searching efforts, so perhaps this is a Mac-only Apple thing, perhaps an extra security measure. Or perhaps in the Unix-style OSes, the su command must be run by the root user, and in macOS the root account is disabled by default for security purposes – our Mac admin user account is less powerful than root, and may not be authorized by default to run su? Dunno, don't care, the workaround works.

Modern alternative: sudo -u

According to Craig Ringer and to Erwin Brandstetter, the more modern form is:

sudo -u postgres -i

According to this page:

-u means “run a specified command as this specified user”.
postgres is specifying the username under which we want to run the command.
-i simulates an initial login, meaning the shell will be initialized running that user’s startup scripts.
No command is provided, so an interactive shell is executed.

For more discussion on the older and newer approaches, see this Question on sister site, What's the difference between sudo su - postgres and sudo -u postgres?. The answer there by Craig Ringer advises: (a) Forget about sudo su, and (b) use sudo -u instead.

Run apps as postgres user

To run a psql session as the postgres user:

sudo -u postgres psql

To run the nano text-editor app as the postgres user:

sudo -u postgres nano


index using btree_gin on smallint/bigint

It looks like btree_gin is not as clever about reasoning about datatypes as the regular btree code is.  If you explicitly cast your query condition constant to be of the exact same type as the index, then it will work.

explain select * from test where year = 2000::smallint;
                                QUERY PLAN
----------------------------------------------------------------------------------
 Bitmap Heap Scan on test  (cost=467.50..56278.04 rows=50000 width=12)
   Recheck Cond: (year = '2000'::smallint)
   ->  Bitmap Index Scan on test_year_idx  (cost=0.00..455.00 rows=50000 width=0)
         Index Cond: (year = '2000'::smallint)


WITH RECURSIVE cte1 AS (
SELECT videoid,
       SUBSTRING_INDEX(ips, ',', 1) ip,
       SUBSTRING(ips FROM 1 + LOCATE(',', ips) FOR 255) slack
FROM test
UNION ALL
SELECT videoid,
       SUBSTRING_INDEX(slack, ',', 1) ip,
       SUBSTRING(slack FROM 1 + LOCATE(',', slack) FOR 255) slack
FROM cte1
WHERE LOCATE(',', slack)
),
cte2 AS (
SELECT videoid,
       ip
FROM cte1
UNION
SELECT videoid,
       slack
FROM cte1
WHERE !LOCATE(',', slack)
)
SELECT videoid, COUNT(ip) cnt
FROM cte2
GROUP BY videoid


Issue with adding a database to an Availability Group

Unable to access availability database 'test123' because the database
  replica is not in the PRIMARY or SECONDARY role. Connections to an
  availability database is permitted only when the database replica is
  in the PRIMARY or SECONDARY role.

You reported that the database was not showing under the databases folder when viewing your secondary instance with SSMS.

If you refreshed and it was still not there, then this means that the database does not exist.
The reason would/could be that something went wrong with automatic seeding.

You should try executing these commands on the secondary instance:

ALTER AVAILABILITY GROUP [agname] JOIN;
GO
ALTER AVAILABILITY GROUP [agname] GRANT CREATE ANY DATABASE;
GO

After this you should retry automatic seeding & validate that the database was created and fully seeded on the secondary instance.

To validate the automatic seeding process , you could use the  sys.dm_hadr_automatic_seeding DMV.

SELECT start_time,
    ag.name,
    db.database_name,
    current_state,
    performed_seeding,
    failure_state,
    failure_state_desc
FROM sys.dm_hadr_automatic_seeding autos
    JOIN sys.availability_databases_cluster db
        ON autos.ag_db_id = db.group_database_id
    JOIN sys.availability_groups ag
        ON autos.ag_id = ag.group_id;

Source


Mathematically, disjoint = not intersects.

As to which is faster, according to the documentation on spatial indexes at http://msdn.microsoft.com/en-us/library/bb895265.aspx#methods , a spatial index can be used for a query containing geometry1.STIntersects(geometry2) = 1.

Strangely enough, geometry1.STDisjoint(geometry2) = 0 is not listed.


Changed root password from '' to ''. Now cannot connect to databases in Cloud SQL prompt

Login to MySQL using current password and then you can change password like this

mysql> SET PASSWORD FOR root@'localhost'=PASSWORD('');
Query OK, 0 rows affected (0.00 sec)

Now you can login MySQL using BLANK password.

This is not the most secure way to change password for better approach please have a look at How to Reset the Root Password.


Graphical Actual Execution Plan - Estimated vs Actual number of rows

The number 17 comes from the estimated number of rows (per execution) multiplied by the estimated number of executions.

3.85562 * 4.37149  =  16.8548043  ~=  17

In SentryOne Plan Explorer,* we perform the conversion for you, so that estimated rows simply starts at 17, not 3.85562 (I didn't try this specific plan, but that's how the logic in the code should work). I describe the reasoning behind this in this blog post, back when the fix first appeared in Plan Explorer.

  *Disclaimer: I work for SentryOne.


A full backup only backs up enough of the log to restore to the point in time of the backup. Only those committed transactions needed are included in the full backup.


Making the tables with db generalization

Smells a bit like homework this, but ....

Why do you have an id column on music_videos?
Can one music_videos be multiple videos?
And likewise for lessons?
Those two id columns seems redundant.

Other than that, from the information given, it seems decent enough.

Generally speaking, you'll need to decide on some structure between the data, what belongs to where, what is inherited from others, and then make the tables depending on the cardinalities which arise from those.


How does the disk and file share witness work?

There is a good article on how cluster quorum works in Windows Failover Clustering here.

to summarise, for File Share or Cloud Witnesses, a witness log file is maintained with state information about the cluster, witnesses and quorum voting. Each node has a cluster service running that periodically checks in with the other nodes and the witnesses (depending on dynamic voting, node weighting etc) to validate the state and health of the cluster. If a node cannot communicate with other nodes, it will then verify connectivity with the witness, assess the witness log (if possible) and proceed from there with either taking control of the cluster or not.

For example, assume we have two nodes, Node A and Node B, and a File Share Witness (Witness A), with Node A the primary node in the cluster. If Node B, during a periodic health check, cannot communicate with Node A because a switch went down then it will try to verify cluster health by contacting the cluster witness (Witness A) and reviewing the witness log to determine health state.

If Witness A can still communicate with both nodes because it has a different path to Node A than Node B does, this information will be in the witness log and Node B will not try to assume the role of the primary node. Node A will also see this information in the witness log and continue normal operations.

On the other hand, if Witness A has also lost communication with Node A, this information will be in the witness log and Node B will try and assume the role of the primary node. Since Node A will not be able to communicate with Node B or Witness A in this scenario, its cluster service will have shut down to prevent split-brain from occurring.

A Shared Disk witness works almost identically, except the witness is highly-available storage connected to the cluster, so instead of a witness log the shared disk maintains a copy of the cluster configuration database on the disk instead of just a witness log. Behaviorally, the witness and node interactions are the same.


Select unique value pairs that occur (count) more than once inside a table

What you have almost works, just remove the distinct and change the > 2 to > 1.  The distinct is not necessary as the grouping handles that and the > 2 is looking for things that have at least three entries rather than just two.

drop table tab1;
create table tab1 as (select 1 column_fk_id1,2 column_fk_id2 from dual);
insert into tab1 values (1,2);
insert into tab1 values (2,1);
insert into tab1 values (3,4);
insert into tab1 values (4,3);
insert into tab1 values (5,5);
insert into tab1 values (6,7);
insert into tab1 values (6,7);
insert into tab1 values (6,8);
commit;

SELECT column_fk_id1, column_fk_id2, COUNT(*) AS unique_pair_repeats
FROM tab1
WHERE column_fk_id1 IS NOT NULL
GROUP BY column_fk_id1, column_fk_id2
HAVING count(*) > 1;

COLUMN_FK_ID1 COLUMN_FK_ID2 UNIQUE_PAIR_REPEATS
------------- ------------- -------------------
            1             2                   2
            6             7                   2

2 rows selected.


PostgreSQL Streaming Replication - corruption on master

Is there issues can happen on Master that can cause persistency problems or full failure of Master and Slave DB both while replicating?

Yes, there are.

Anything that corrupts the transaction logs on the master before they're archived or streamed to the replica will do it - and I've seen that happen personally. The root cause was administrator error, in that the admin kill -9'd the postmaster, deleted postmaster.pid and then started a new postmaster up. Nonetheless, the corrupt WAL was replicated.

Memory errors that corrupt shared memory, flip a bit, etc could do it.

Severe filesystem problems affecting the transaction logs could do it.

A nasty PostgreSQL bug could do it. We actually had one of those in 9.3 too, though it's fixed prior to 9.3.9. To learn more than you ever wanted to about it, search for "multixact wrap-around".

This is why I strongly recommend that you keep point-in-time recovery using WAL archives and periodic base backups. The pgbarman utility helps with this.

Periodic text-format dumps can also be a good idea as extra insurance.

  Am I always will get persistant and ready to work or restore Slave DB while replicating?

No, because some kinds of corruption can be replicated to the slave.

The most obvious one is if you DROP TABLE critical_production_data; the system will obediently replicate that to the slave before you can even say "Oh sh...".

Point in time recovery is important.


Alter Table for Multiple Databases (same table name)

Here's the technique I frequently use to do things like this with dynamic SQL. This also has applications beyond just "looping" across databases, too.

Now, you didn't say which 50 databases, so I just went with everything in the instance, as that's a pretty common thing to do:

DECLARE @sql nvarchar(MAX);

SELECT
    @sql = ISNULL(@sql, N'') +
        N'ALTER TABLE ' + QUOTENAME(d.name) + N'.[dbo].[MyTable] ADD C1 int;'
    FROM sys.databases d
    WHERE
        (d.database_id > 4) AND /* No system databases */
        (d.state = 0) AND /* Online only */
        (d.is_distributor = 0) AND /* Not a distribution database */
        (d.is_read_only = 0) AND /* We can write to it */
        /* TODO: filter me more? */;

BEGIN TRANSACTION;
    EXEC(@sql);
COMMIT TRANSACTION;

You can replace the sys.databases part with a list of constants, or a query from a master database table, or whatever is appropriate for your scenario. There's also no error checking, so you may want to add that.

(Yes, I left the syntax error in the above code on purpose.)


The logical order of execution plan of the SQL query with more than one join

One way to determine the logical order of joins is to replace the first inner join in your example with a left outer join:
SELECT *
FROM user_branch T1
LEFT  JOIN dimcustomer2 T2
   ON T1.BRANCH_CODE = T2.BRANCH_CODE
INNER JOIN customer_guarantee T3
   ON T3.CUSTOMER_NUM = T2.CUSTOMER_NUM

Let us assume that some rows in T1 have no matches in T2. More specifically, let us assume these are the three tables:
    T1                         T2                           T3

BRANCH_CODE          BRANCH_CODE  CUSTOMER_NUM          CUSTOMER_NUM
-----------          -----------  ------------          ------------
11                   11           230                   120
12                   12           235                   170
13                   15           260                   230
14                                                      235
15                                                      245
                                                        250
                                                        260
                                                        270

There are two joins here and two possibilities in which order they are executed.
1. LEFT JOIN, then INNER JOIN
If the left join evaluates first, then its result will have nulls in the T2 columns where T1 rows had no match:

T1.BRANCH_CODE  T2.BARNCH_CODE  T2.CUSTOMER_NUM
--------------  --------------  ---------------
11              11              230
12              12              235
13              (null)          (null)
14              (null)          (null)
15              15              260

Joining that result further with T3 using an inner join on a condition that uses a T2 column will eliminate the non-matches – and, therefore, corresponding T1 rows, – because a null cannot satisfy the join's equals condition:

T1.BRANCH_CODE  T2.BARNCH_CODE  T2.CUSTOMER_NUM  T3.CUSTOMER_NUM
--------------  --------------  ---------------  ---------------
11              11              230              230
12              12              235              235
15              15              260              260

This way some of T1 rows will be excluded from the final result set.
2. INNER JOIN, then LEFT JOIN
Now if the inner join is executed first, then it will produce a result set containing the rows from T2 and T3 that match the inner join's condition:

T2.BARNCH_CODE  T2.CUSTOMER_NUM  T3.CUSTOMER_NUM
--------------  ---------------  ---------------
11              230              230
12              235              235
15              260              260

When this result set is then outer-joined to T1, T1 being on the outer side, you will get a final result containing all the rows from T1 and those from the T2-T3 inner join that match the outer join condition:

T1.BRANCH_CODE  T2.BARNCH_CODE  T2.CUSTOMER_NUM  T3.CUSTOMER_NUM
--------------  --------------  ---------------  ---------------
11              11              230              230
12              12              235              235
13              (null)          (null)           (null)
14              (null)          (null)           (null)
15              15              260              260

Therefore, this second interpretation would mean that all T1 rows should be present in the result.

Since these two interpretations give such different results, it is clear that only one can be true. Executing the query, you will see that actually it is the first one. That means that logically, the joins are executed in the order they are specified in the FROM clause.
Syntax variations
Note that the conclusion above applies to the most conventional join syntax, namely this:
FROM
  T1
  ... JOIN T2 ON ...
  ... JOIN T3 ON ...
  ...

Your example matches that pattern, so the conclusion applies to it as well. However, there are variations worth mentioning where our conclusion does not apply, or at least not as straightforwardly.
1. Nested JOIN syntax
Syntactically, a join may be specified inside another join, like this:
FROM
  T1
  JOIN
    T2
    JOIN T3 ON ..
  ON ...

In the above case, JOIN T2 is encountered before JOIN T3. However the former join's declaration is not complete at that point: its ON subclause is the one at the end and is logically evaluated only after the JOIN T3 ON ... part. So in this case, T2 is joined to T3 first, then the result of the join is joined to T1.
You could still argue that our conclusion stands here, although it is not as clear-cut in this situation. We concluded that the joins are evaluated in the order they are specified in the FROM clause. In this example, the first join we encounter as we parse the FROM clause, is not yet completely specified by the time the second one is.
2. Mixing comma joins and conventional joins
Prior to introduction of the explicit JOIN syntax, joins were specified like this:
FROM
  T1,
  T2,
  T3
WHERE
  <joining conditions, filter conditions>

This type of join, sometimes referred to as comma join, is still supported by most, if not all, platforms, including SQL Server.
Without a joining condition, a comma join is essentially a cross join. A joining condition makes it an inner join. You can see, though, that the joining condition in this case comes in an entirely different clause, the WHERE clause.
Now, SQL Server allows you to mix comma joins and conventional joins in the same FROM clause. When you have a mix of joins like this:
FROM
  T1,
  T2
  JOIN T3 ON ... ,
  T4 ...

SQL Server will evaluate each individual comma-separated item independently of the others before cross-joining them all together. So in the above case, the T2 JOIN T3 ON ... join will be evaluated before its result is cross-joined to T1 (to be further filtered down by any joining condition that might be found the WHERE clause). Our conclusion does not apply at all here. However, you can see that a very different syntax comes into play in this case.
I am discussing the mixed syntax in slightly more detail in my answer on Stack Overflow: The multi-part identifier could not be bound.


No.
Don't do it.

I say not to do it because it usually leads to folly.  If one database grows faster than the other, then it is starved while the other has spare space.  This may hobble the overall system more than the original benefit you envisioned.

Another example:  Having /tmp on a separate filesystem.  I hate to think how many times DBAs find queries crapping out, only to discover that their tiny /tmp fs was starved while the main disk was swimming in space.

On the other hand, there are valid cases for splitting up things -- one disk is an old, slow, spinning drive, another is a fast, low latency, SSD.  Or you add an extra drive, but it is not convenient to get the OS to see all the drives as one larger virtual drive.  These involve spelling out what databases / tables / logs to put where -- and lead to the potential problem that I mentioned above.

If you are referring to RAM, then the general rule is to give most of RAM to innodb_buffer_pool_size, then forget about RAM allocation.  Only a few percent of RAM needs to be allocated for table cache, etc, etc.  The buffer pool is used by all tables 'equally' -- that is on a roughly LRU basis.


PostgreSQL vacuum duration on a big table

Because you haven't specified your PostgreSQL version it's hard to answer. VACUUM has been improved in many ways on newer versions.

Presuming you're on 9.0 or 9.1 - so you don't need a max_fsm_pages setting, you have the improved VACUUM FULL, you have the visibility map, etc - then subsequent VACUUM operations should be quite fast. However, you should not be doing them.

Turn autovaccum on. Set it to run frequently. If this table is updated a lot, set autovaccum to process it more often. Vaguely modern PostgreSQL versions work best with lots of very frequent vacuum runs.


Can't access MySQL database with created user through dll

I've eventually figured it out, after gaining a few grey hairs.

I had to give the new user the FILE privilege because my dll was using LOAD DATA INFILE which requires it to work. Here is the info on the FILE privilege.


Do I still need OLAP cubes after a SQL Server Upgrade

on this link: A quick overview of SSAS for DBAs you can find relevant information about SSAS that can help you to see why there are so many advantages using SSAS.

You also need to manage permissions, as you mentioned Power BI, SSAS interacts very well with Power BI, in many environments I restrict the Power BI views by managing the permissions using AD and SSAS.

SSAS also needs maintenance like backups and integrity checks, so yes, there is an overhead if you want to maintain it properly, and include a recover strategy.

You mentioned columnstore indexes and SSAS cubes and you can find a similar question here.

this was a general insight. If you put more focus on your question, you will get more objective and detailed answers.


When I convert the linked tables to local tables I got this error?

Foreign keys can't be connected to non-local objects - they have to reference local tables.


As I wrote in the comment to your previous post:

  the bcp syntax look ok.
  I double checked with BOL and the only limitation for bcp is that
  if you use a procedure all tables have to exist prior to bcp being executed

No temporary tables or bcp will fail!

Update 1:
Here is an example on how to exclude columns in the format file (*.fmt) for the bcp utility:

9.0
3
1       SQLCHAR       0       7      "\t"     1     Col1         ""
2       SQLCHAR       0       0      ""       0     Col2         ""
3       SQLCHAR       0       100    "\r\n"   3     Col3         SQL_Latin1_General_CP1_CI_AS

Note the zero (0) in the length for Col2. You can find this example in the BOL of SQL Server 2008 at:

ms-help://MS.SQLCC.v10/MS.SQLSVR.v10.en/s10de_4deptrbl/html/30e0e7b9-d131-46c7-90a4-6ccf77e3d4f3.htm

Or search for Using a Format File to Skip a Table Column in BOL.


Delete all data for a Single Vendor in the Dabatase

Caveat: Am American that uses Microsoft SQL Server. My opinions are based on my experiences, and may need to be modified to suit your own needs.

I would maybe look at keeping the vendor_id intact, and just replace the information on that record with some filler information. In designing the database, an effort should have been made to keep the vendor information contained to only a few tables, and anywhere else that references that vendor would have been through its id field as a foreign key. This would keep the integrity of the data, especially if you have something like sales records, and really CAN'T delete a bunch of data that is vital to your own business. A vendor table could look like the below.

Vendor_id   Vendor_name   Vendor_phone
----------------------------------------
123         BigSales Co.   555-5555
234         [redacted]     [redacted]


What is Collation Compatibility_60_406_30001 in SQL Server

Looks like you have an old compatibility collation, which comes from SQL Server 2005 or SQL Server 2000.
The format of this compatibility collation name is:

Compatibility_SortOrderDecimal_lcidHex_CompStyleHex

Explanation:

SortOrderDecimal 60 = Case-insensitive Scandinavian dictionary sort order, without case preference
lcidHex (Locale id) 0x406 = 1030 (Danish)
CompSytleHex Default Unicode comparison style 0x30001 = Ignore case, Ignore Kana, Ignore width

You can change the database collation through the following code:

USE master;
GO
ALTER DATABASE YourDatabaseName
COLLATE Danish_Norwegian_CI_AS;
GO

If you must have backward compatibility with the SQL collation, you could use the SQL_Scandinavian_CP850_CI_AS collation.


Can you add indexes? I would first try adding an index on (col, userid).

10 seconds looks too much for a 30M rows table.


SSISDB Start and Stop time for each Package

You can use the internal.executions and internal.operations tables to view this data. I would think this would also work for packages calling other packages as well, if they are both within the same SSISDB Catalog.

SELECT E.execution_id,
       E.folder_name,
       E.project_name,
       E.package_name,
       E.environment_folder_name,
       E.environment_name,
       CASE O.[status]
           WHEN 1 THEN 'Created'
           WHEN 2 THEN 'Running'
           WHEN 3 THEN 'Canceled'
           WHEN 4 THEN 'Failed'
           WHEN 5 THEN 'Pending'
           WHEN 6 THEN 'Ended Unexpectly'
           WHEN 7 THEN 'Succeeded'
           WHEN 8 THEN 'Stopping'
           WHEN 9 THEN 'Completed'
       END AS [Status],
       O.start_time,
       O.end_time
FROM internal.executions AS E
INNER JOIN internal.operations AS O
    ON E.execution_id = O.operation_id;

You can refer to these articles for more information:

https://docs.microsoft.com/en-us/sql/integration-services/system-views/catalog-operations-ssisdb-database?view=sql-server-2017

https://docs.microsoft.com/en-us/sql/integration-services/system-views/catalog-executions-ssisdb-database?view=sql-server-2017


I personally use the latter, more generic version in my systems. I have two tables that I can think of off the top of my head: UniqueIntegerTable and UniqueStringTable - as you can imagine, they are defined as follows:

CREATE TYPE Utils.UniqueIntegerTable AS TABLE (
    [Value] INT NOT NULL PRIMARY KEY
);
CREATE TYPE Utils.UniqueStringTable AS TABLE (
    [Value] NVARCHAR(50) NOT NULL PRIMARY KEY
);

I prefer to have generic TVP's so that I don't clog up my schema's with multiple types that are basically the same. The performance is exactly the same as if you defined an explicit type as in your first example and it has the benefit that it creates less code for me to maintain.

I know one argument that I have previously heard for using explicit types that are bound to tables is that it is easier to understand their usage. I personally don't agree with this. There is nothing preventing me from defining a stored procedure using the wrong type (but has the correct shape for my needs). Instead, I can give the variable a good name to infer usage and the contents of the table:

CREATE PROCEDURE dbo.UpdateEmployees (
    @employeeIds Utils.UniqueIntegerTable READONLY
)
BEGIN
SET NOCOUNT ON;

-- Use table as needed.

END
GO

In terms of naming conventions, I don't know of any official standard but the one I use is to append Table to the end of the type name. I know that this is not really all that different from prefixing things with tbl but I'm ok with it in this instance. As with all naming conventions, pick one that you feel is easy to work with it - but once you do, stick to it. Naming conventions are only useful if you are consistent.


Either your professor made a mistake or you misunderstood what she said. In the context of relational DBMSes, as implemented by various vendors, the aggregate function COUNT(<expression>) returns the number of non-NULL values of <expression> in the result set (or a group).

There is a special case of COUNT(*), which returns the number of rows in the result set or group, not the number of values of anything. This is equivalent to COUNT(<constant expression>), such as COUNT(1).

Many databases support COUNT(DISTINCT <expression>), which will return the number of unique values of <expression>.


Azure Database Link to another Azure database

Cross-database queries are not available in Azure SQL Database. You need to use Elastic Query to query across multiple Azure SQL Databases or move to a Managed Instance, which does support cross-database queries but obviously has a higher cost and other implications.


If you need to work only with years, you can represent them by means of any integer (that is: from smallint to bigint (tinyint is too small) or numeric/decimal) type of a size big enough for your needs.

If you really need date calculations involving years > 9999, you're out luck. MySQL documentation about dates clearly states that

  The DATE type is used for values with a date part but no time part. MySQL retrieves and displays DATE values in 'YYYY-MM-DD' format. The supported range is '1000-01-01' to '9999-12-31'.

If your needs for date ranges are really critical, and you have the possibility of choosing your database, PostgreSQL allows much larger ranges (4713 BC to 5874897 AD for dates, 4713 BC to 294276 AD for timestamps).

Other databases (Firebird, Oracle and MS SQL Server for instance) have date possible values similar to those of mySQL.


Sync postgreSQL with SQL Server database- on data change event

A tool like Daffodil Replicator or SymmetricDS is designed to sync between different brands of databases.
You could try the tds_fdw Foreign Data Wrapper, which may be able to write to SQL Server.


MySQL Replicate Databases without Tables or Content

It isn't possible with replication as has been correctly pointed out, since that's not how replication works, nor can it be configured to work that way.  Replication either does or does not handle any given table (depending on configuration) but when replication does handle a given table, the data is replicated and replication depends on the rows in the tables starting out and remaining identical.

What I'm hearing you say is that you want some of the same schemas and tables on the 2nd server as exist on the master, but they will be populated via external means, and you want those tables to go away when they are dropped from the master.

Here is a high-level view of how this could be accomplished via automatic means using only things built-in to MySQL.

Obviously this is a dangerous operation if not carefully written since we're talking about the automated dropping of tables and databases, so... caveat lector.

enable the event scheduler on the archive machine
enable the federated storage engine on the archive machine
within another schema you create solely for the purpose of this exercise, define 2 FEDERATED tables that connect back to the master.  One of these points to information_schema.tables and the other points to information_schema.schemata.
write a stored procedure that selects from the archive machine's information_schema.schemata table, left join the federated schemata table on catalog_name, schema_name where schema_name not in ('mysql','information_schema','performance_schema','any','other','schemas','you want not','to be deleted','for sure')
take values from the resulting rows, which will be all of the databases that exist on the local machine but not on the master... and, using dynamic sql, drop those databases from the archive machine.
do the same thing with the information_schema.tables table, which will identify tables you can drop.
use the event scheduler to automate the running of this stored procedure.  Don't schedule it too often, perhaps only once a day, because selecting from the information_schema.tables table can be a resource-intensive operation. MySQL gathers stats when you do this that can be costly especially in terms of I/O.


SQL-Server login-error of SQL-Server Agent Account

The user account that is running the SQL Server agent might not have permissions to use the database mail profile you can check that with EXECUTE msdb.dbo.sysmail_help_profileaccount_sp;

But you are having some issues with the agent account security, please try the following steps.

Remove all extra privileges from the Agent user account (or create a new domain account) Running the SQL Server agent with domain admin privs is just terrifying.
Change the user for the Agent service using the SQL Server Configuration Manager. You should never change it any other way as it will set all the necessary permissions. Change the account to network service and then back again and make sure beforehand that the agent user is neither a local or domain administrator.
Check the mail profile on the SQLAgent settings and make sure that it's not private and that the agent user account can use it (The user account will be made sysadmin by the Configuration manager and should be able to use all accounts)


Is it possible (or good practice) to register data upon Deletion of a record? What would be a better solution?

A common practice is to have a status column that indicates whether or not the representative is active. Deleting data that you may wish to analyze in the future isn't a good idea. Instead, you could do something like this:

representativeID      status     statusDate
-------------------------------------------
1                     inactive   2014-03-20
2                     inactive   2014-04-02
3                     active     2014-04-10

The statusDate column could be updated by a trigger. It would give you the date of the last status change. In order to look at past representatives, you would simply write a query that filtered the records by inactive statuses.

/** MySQL example - 10 most recent inactive represenatives **/
select representativeID
from representatives
where status = 'inactive'
order by statusDate desc
limit 0,10