Unable to change innodb_buffer_pool_size MySQL 5.6 in CentOS 6.7

About     Service mysqld start --defaults-file=..

AFAIK it isn't correct way to pass argument. It should not be accepting that argument.
Never done that; importantly it isn't even necessary for default location!!!

Confirm the which my.cnf is being used:

mysqld –verbose –help | grep -C1 my.cnf

Confirm we have correct entry in my.cnf

grep innodb_buffer_pool_size /etc/my.cnf

Verify your error log, you should see something like

InnoDB: Initializing buffer pool size = ...


SA account locked out then unlocked somehow

Thanks Ross and James for input in the comments. I found that our sa account has enforce password policy turned on, and our local security policy will unlock an account after 15 mins, so that answers how it got unlocked. Regarding future I think server audit of login failed event class will help us catch more information if this happens again. Internally we have a goal to move away from using sa and rename/disable it.


Logs growing and took up entire log drive even with log backups

run this query on your server

SELECT [NAME],
       RECOVERY_MODEL_DESC,
       LOG_REUSE_WAIT_DESC,
       IS_PUBLISHED,
       IS_MERGE_PUBLISHED,
       is_subscribed
FROM SYS.DATABASES

if you have something different than NOTHING in the LOG_REUSE_WAIT_DESC there is where I would start investigating.

There are eight reasons SQL Server might report when it cannot truncate the transaction log

That is probably what is preventing the space within the log to be re-used.

For ACTIVE_TRANSACTION

I like to use the stored procedure called sp_whoisactive it will tell you what is currently running on your server.

or much simpler, just to see if there are open transactions:

DBCC opentran WITH NO_INFOMSGS

For LOG_BACKUP

Why is log_reuse_wait_desc saying LOG_BACKUP after doing a log backup?

  LOG_BACKUP really means “either you need to take a log backup, or the
  log records that were backed up were all in the current VLF and so it
  could not be cleared.”

There are eight reasons SQL Server might report when it cannot truncate the transaction log

  If your database recovery model is "full" SQL Server cannot reuse
  virtual log files unless all contained transaction log records have
  been backed up with a log backup. If a log backup is outstanding the
  log has to grow to accommodate new data changes. During this time you
  will see a log_reuse_wait_desc of LOG_BACKUP. Full or differential
  backups do not backup all transaction log records, so you need to run
  an actual log backup to allow for virtual log file reuse.

In this situation transaction log backups every minute I think it too high.

Find the number of VLFs in the transaction log.

I had this situation:
MY_DB (232 Gb) = 500,000 VLFs

and this is what I did (keeping each VLF = 8GB):

dbcc sqlperf(logspace)
go
use my_db
Go
select size/128,* from sys.sysfiles
dbcc loginfo('my_db')

-- updated my_db autogrowth to 4096Mb (chunk of 4 Gb)
DBCC SHRINKFILE(my_db_log, 1)
select size/128,* from sys.sysfiles

ALTER DATABASE [MY_DB] MODIFY FILE ( NAME = N'my_db_log', SIZE = 8GB )
ALTER DATABASE [MY_DB] MODIFY FILE ( NAME = N'my_db_log', SIZE = 16GB )
ALTER DATABASE [MY_DB] MODIFY FILE ( NAME = N'my_db_log', SIZE = 24GB )
ALTER DATABASE [MY_DB] MODIFY FILE ( NAME = N'my_db_log', SIZE = 32GB )
ALTER DATABASE [MY_DB] MODIFY FILE ( NAME = N'my_db_log', SIZE = 40GB )
ALTER DATABASE [MY_DB] MODIFY FILE ( NAME = N'my_db_log', SIZE = 48GB )
ALTER DATABASE [MY_DB] MODIFY FILE ( NAME = N'my_db_log', SIZE = 56GB )
ALTER DATABASE [MY_DB] MODIFY FILE ( NAME = N'my_db_log', SIZE = 64GB )
ALTER DATABASE [MY_DB] MODIFY FILE ( NAME = N'my_db_log', SIZE = 72GB )
ALTER DATABASE [MY_DB] MODIFY FILE ( NAME = N'my_db_log', SIZE = 80GB )
ALTER DATABASE [MY_DB] MODIFY FILE ( NAME = N'my_db_log', SIZE = 96GB )
ALTER DATABASE [MY_DB] MODIFY FILE ( NAME = N'my_db_log', SIZE = 104GB )
ALTER DATABASE [MY_DB] MODIFY FILE ( NAME = N'my_db_log', SIZE = 112GB )
ALTER DATABASE [MY_DB] MODIFY FILE ( NAME = N'my_db_log', SIZE = 120GB )
ALTER DATABASE [MY_DB] MODIFY FILE ( NAME = N'my_db_log', SIZE = 128GB )
ALTER DATABASE [MY_DB] MODIFY FILE ( NAME = N'my_db_log', SIZE = 136GB )
ALTER DATABASE [MY_DB] MODIFY FILE ( NAME = N'my_db_log', SIZE = 144GB )
ALTER DATABASE [MY_DB] MODIFY FILE ( NAME = N'my_db_log', SIZE = 152GB )
ALTER DATABASE [MY_DB] MODIFY FILE ( NAME = N'my_db_log', SIZE = 160GB )
ALTER DATABASE [MY_DB] MODIFY FILE ( NAME = N'my_db_log', SIZE = 168GB )
ALTER DATABASE [MY_DB] MODIFY FILE ( NAME = N'my_db_log', SIZE = 176GB )
ALTER DATABASE [MY_DB] MODIFY FILE ( NAME = N'my_db_log', SIZE = 184GB )
ALTER DATABASE [MY_DB] MODIFY FILE ( NAME = N'my_db_log', SIZE = 192GB )
ALTER DATABASE [MY_DB] MODIFY FILE ( NAME = N'my_db_log', SIZE = 200GB )
ALTER DATABASE [MY_DB] MODIFY FILE ( NAME = N'my_db_log', SIZE = 208GB )
ALTER DATABASE [MY_DB] MODIFY FILE ( NAME = N'my_db_log', SIZE = 216GB )
ALTER DATABASE [MY_DB] MODIFY FILE ( NAME = N'my_db_log', SIZE = 224GB )
ALTER DATABASE [MY_DB] MODIFY FILE ( NAME = N'my_db_log', SIZE = 232GB )
ALTER DATABASE [MY_DB] MODIFY FILE ( NAME = N'my_db_log', SIZE = 240GB )

Transaction Log VLCs - each is a multiple of 4GB


Is there a way to see the code that creates a view in SQL server management studio?

Right click the view, select Script View as, CREATE To, New query editor window.


What does the term "projection" refer to in an SQL query (or view definition)

In Relational algebra, projection means collecting a subset of columns for use in operations, i.e. a projection is the list of columns selected.

In a query optimiser step, the projection will manifest itself as a buffer or spool area of some description containing a subset of the columns from the underlying table or operator, or a logical view based on those columns that is used by subsequent operations.

In a view, a projection equates to the list of columns selected in the query underlying the view.


SHOW CREATE TABLE is more descriptive than DESCRIBE.  And easier to read.

(I am removing the "bp", which I find as being distracting clutter:)

SELECT  p.id
    FROM  words w
    JOIN  productwords pw  ON pw.wordid = w.id
    JOIN  products p  ON p.id = pw.productid
    WHERE  w.word = 'nike'
      AND  p.price > 0
    ORDER BY  pw.productid DESC
    LIMIT  100

If the tables are not InnoDB, convert to such.

productwords seems to be a many:many mapping table; see here for advice on improving its performance .

18K rows after a moderately complex query could reasonably take 1.4 seconds.  Some more things to ask:

How much RAM?
Value of innodb_buffer_pool_size?
Disk subsystem (HDD/SSD, etc)?
What will you do with 18K ids or even 100?  Turn around and ask for more data from some table?  That will be inefficient.

The complexity prevents scanning fewer than 18K rows, even with the LIMIT 100.  OTOH, if you get rid of all references to products would probably speed it up some.


MySQL: Using DATE_SUB to subtract business days instead of all calendar days

You can use DAYOFWEEK to calculate the right number of days to subtract based on that. This also works for date_add. For the DAYOFWEEK index, 1 is equal to Sunday and 2 is equal to to Monday.

case
when DAYOFWEEK({date}) = 1 then date_sub({date}, Interval 2 Day)
when DAYOFWEEK({date}) = 2 then date_sub({date}, Interval 3 Day)
else date_sub({date}, Interval 1 Day)
end

http://www.w3resource.com/mysql/date-and-time-functions/mysql-dayofweek-function.php


Dimensional Modeling and ETL in Redshift

You are definately on the right track with Kimball rather than inmon for Redshift.

There are a number of patterns for this, I have used them all in different use cases

"ELT" pattern - Load the source tables to redshift fully, do not do any significant
transformations until the data has been loaded. For this you can
either load to s3, then use redshift copy command or I would
recommend using "AWS data migration services", which can sync a
source (e.g.mysql or postgres) to a target (e.g. redshift) Then, on
a regular basis run sql processes within redshift to populate dims
then facts. You can use third part cloud based tools to "simplify"
this process if you want to - such as Matillion (i do not recommend
using a third party tool)
"ETL pattern" - Transform the data in flight, using apache spark. and load the dims and facts into redshift spark->s3->redshift. I have used EMR for this which is good. this is also the approach taken if you use AWS Glue
Do not transform ! - similar to 1) but just use the tables that have
been loaded.

Note that Redshift sometimes works BETTER if you have a wide table with repeated values rather than a fact and  dimensions. The reason for this is that the columnar approach lets Redshift compress the different values down to a level that is pretty efficient. I do not have a formula for when to use many Dimensions vs a flat wide table, the only way is to try it and see!

Some links

AWS DMS for Redshift taret

AWS Glue


AOAG Database went into NOT SYNC\SUSPECT MODE

This situation is caused by the transaction logs not being properly hardened on one of your secondary replicas. This could happen is one of them was offline for a period of time, or was unable to connect correctly.

To help identify the replica having the problem you can query sys.dm_hadr_database_replica_states, in particular pay attention to the log_send_queue_size column.

There are some odd things that happen with AGs sometimes, particulary after a machine has been restarted, whereby one of the databases in the AG will just stop syncing (and there will be no logged errors or issues). Rather than remove it from the AG entirely, and then add it back in you can attempt to restart the hadr_endpoint on the secondary that is being the problem, and then watch to see if traffic starts flowing again.


As @AaronBertrand pointed out in his comment, objects are attached to other objects by object_id, not by object name.  Having said that, there is a way to move data from one table to another without the need to use sp_rename.

Since example code is the cleanest way to show how something works, I've created the following test-bed code to illustrate how to use ALTER TABLE ... SWITCH syntax to enable data migration.

First, we'll create an "existing" Product and ProductAudit table, with a trigger on the Product table, that inserts rows into the ProductAudit table:

USE tempdb;

IF OBJECT_ID('dbo.Product') IS NOT NULL
DROP TABLE dbo.Product;
CREATE TABLE dbo.Product
(
    ProductKey int NOT NULL
        CONSTRAINT PK_Product
        PRIMARY KEY CLUSTERED
        IDENTITY(1,1)
    , SomeData varchar(4000) NOT NULL
    , TriggerUpdated bit NOT NULL
        CONSTRAINT df_Product_TriggerUpdated
        DEFAULT (0)
);
GO
IF OBJECT_ID('dbo.ProductAudit') IS NOT NULL
DROP TABLE dbo.ProductAudit;
CREATE TABLE dbo.ProductAudit
(
    ProductAuditKey int NOT NULL
        CONSTRAINT PK_ProductAudit
        PRIMARY KEY CLUSTERED
        IDENTITY(1,1)
    , ProductKey int NOT NULL
    , SomeData varchar(4000) NOT NULL
);
GO

CREATE TRIGGER dbo.ProductAuditInsertTrigger
ON dbo.Product
AFTER INSERT
AS
BEGIN
    SET NOCOUNT ON;
    INSERT INTO dbo.ProductAudit (ProductKey, SomeData)
    SELECT i.ProductKey
        , i.SomeData
    FROM inserted i
    UPDATE dbo.Product
    SET TriggerUpdated = 1
    FROM dbo.Product p
        INNER JOIN inserted i ON p.ProductKey = i.ProductKey;
END
GO

Here, we'll insert 100,000 rows into the Product table:

;WITH src AS (
    SELECT *
    FROM (VALUES(0), (1), (2), (3), (4), (5), (6), (7), (8), (9)) v(num)
)
INSERT INTO dbo.Product (SomeData)
SELECT CONVERT(varchar(4000), CRYPT_GEN_RANDOM(4000))
FROM src s1
    CROSS APPLY src s2
    CROSS APPLY src s3
    CROSS APPLY src s4
    CROSS APPLY src s5;

SELECT ProductCount = COUNT(*)
FROM dbo.Product pa
WHERE pa.TriggerUpdated = 1;

SELECT ProductAuditCount = COUNT(*)
FROM dbo.ProductAudit pa;

Results of the SELECT queries above:

ProductCount
------------
100000

ProductAuditCount
-----------------
100000

This will create a "temporary" table to hold the rows we want to retain:

IF OBJECT_ID('dbo.ProductAuditNew') IS NOT NULL
DROP TABLE dbo.ProductAuditNew;
CREATE TABLE dbo.ProductAuditNew
(
    ProductAuditKey int NOT NULL
        CONSTRAINT PK_ProductAuditNew
        PRIMARY KEY CLUSTERED
        IDENTITY(1,1)
    , ProductKey int NOT NULL
    , SomeData varchar(4000) NOT NULL
);

This code "migrates" the data from the existing table into the new table, retaining the triggers present on the original table, without recreating them.  The ALTER TABLE ... SWITCH syntax is a meta-data-only operation that is extremely quick, making this a great way to migrate data.  The code below sets the transaction isolation level to lock the tables involved to prevent other processes inserting or updating data in any of the involved tables.

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE; --lock all row-ranges affected by
                                              --the migration
DECLARE @msg varchar(1000);
BEGIN TRANSACTION
BEGIN TRY
    --disable the trigger temporarily
    DISABLE TRIGGER dbo.ProductAuditInsertTrigger ON dbo.Product;
    --copy the rows we want to retain from the old table to the new table
    INSERT INTO dbo.ProductAuditNew (ProductKey, SomeData)
    SELECT pa.ProductKey
        , pa.SomeData
    FROM dbo.ProductAudit pa
    WHERE pa.ProductAuditKey % 5 = 0; --retain 20% of the rows
    --truncate the "old" table
    TRUNCATE TABLE dbo.ProductAudit;
    --switch the single partition belonging to the "new" table
    --into the "old" table
    ALTER TABLE dbo.ProductAuditNew
    SWITCH TO dbo.ProductAudit;
    --remove the "new" table, which is actually empty now
    DROP TABLE dbo.ProductAuditNew;
    --re-seed the table's identity object; only necessary if an IDENTITY column is present
    DBCC CHECKIDENT('dbo.ProductAudit', RESEED);
    --re-enable the trigger
    ENABLE TRIGGER dbo.ProductAuditInsertTrigger ON dbo.Product;
    COMMIT TRANSACTION
END TRY
BEGIN CATCH
    SET @msg = ERROR_MESSAGE();
    PRINT (@msg);
    ROLLBACK TRANSACTION
END CATCH
SET TRANSACTION ISOLATION LEVEL READ COMMITTED; --return to default mode

SELECT ProductAuditCount = COUNT(*)
FROM dbo.ProductAudit pa;

Results:

ProductAuditCount
-----------------
20000

Here, we'll insert another 10 rows into the dbo.Product table to prove the trigger still works:

;WITH src AS (
    SELECT *
    FROM (VALUES(0), (1), (2), (3), (4), (5), (6), (7), (8), (9)) v(num)
)
INSERT INTO dbo.Product (SomeData)
SELECT CONVERT(varchar(4000), CRYPT_GEN_RANDOM(4000))
FROM src s1;

SELECT ProductAuditCount = COUNT(*)
FROM dbo.ProductAudit pa;

Results:

ProductAuditCount
-----------------
20010


Configuring SQL Server management data warehouse (MDW) for data collection

As mentioned by you, that the repository server from where you running the MDW is in different domain than the target computer and no trust exist, probably the reason you might not see the job running it's step for connecting. Try catching some error if possible from error logs or event viewer.

Try using repository server running on same domain or create a trust between 2 and give it a shot.


Galera cluster slightly slower than single database?

Galera being slower for that benchmark is not surprising.  But is it useful info?
At the end of a transaction, in the COMMIT, the node being written communicates with each other node, asking "will you guarantee that this transaction will succeed?"  (This is slightly weaker, but faster, than actually waiting for the writes to finish on the other nodes.)
What's in the 12 queries that make up a "transaction"?  Does that reflect your usage?
If I munch the numbers correctly, the stand-alone test has 200 transactions running at any moment; 100 for Galera.  Is that realistic?  I hope not -- your quadcore machine will begin to stumble over itself after about 4 transactions.  I would expect it to hit a ceiling long before 200/100 -- This ceiling often characterizes itself by transaction-per-second improving no further, while latency goes through the roof.
Are you hitting all nodes, or just one?
Does a "transaction" have SELECTs in support of the transaction.  I ask this because that is a situation where running transactions on multiple nodes can actually increase throughput.  (On a solo machine, all the selects would have to be done on the Primary.)
Here's an analogy that makes me question benchmarks.  Benchmarks are like testing a hot car -- Drive it for an hour with the accelerator on the floor.  Not realistic.
If you need anywhere near 1768 TPS, neither topology will survive long.


Remove the deeper jsonb element in Postgres

i tried using the #-

What specifically did you try?

Using #- works for me, specifically updatedJson #- '{0,values,0}'


You need a "window" aggregate, i.e. an OVER clause in the aggregate. And because the query already has a GROUP BY, the aggregate needed is the SUM() over the COUNT(d.FK_User) you already have:

SELECT
    u.Username,
    -- COUNT(d.FK_User) AS UserCount,     -- if you need both counts
    SUM(COUNT(d.FK_User)) OVER (PARTITION BY u.Username
                                ORDER BY CAST(d.CreationTimestamp AS DATE)
                                ROWS BETWEEN UNBOUNDED PRECEDING
                                         AND CURRENT ROW)
        AS CumulativeUserCount,
    CAST(d.CreationTimestamp AS DATE) AS CreationDate
FROM data d
    INNER JOIN users u
    ON u.PK_User = d.FK_User
GROUP BY
    CAST(d.CreationTimestamp AS DATE), u.Username
ORDER BY
    CreationDate ;

The ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW is not strictly needed. The default frame for window aggregates when there is an ORDER BY inside OVER () is RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW, which would produce the same results as ROWS but is likely to generate a suboptimal execution plan. See Aaron Bertrand's blog article Best approaches for running totals – updated for SQL Server 2012 for details.
I've simplified the complicated expression that seems to just strip the time part form the datetime column.
Removed the single quotes around the aliases. Aliases and identifiers better not be reserved keywords and not have special characters or spaces. If you do need any of these, then it's preferred to quote them with double quotes "Count" or square brackets [Count].


Moving database from one server to another

Are you getting the error on the destination server?

Is the path present on the destination server?

You need to make sure that the paths defined in your CREATE DATABASE script exist on the destination server or you need to create them there.


How to call a function in regex_replace with matches?

Use regexp_matches.  The output of capturing parentheses will be stuffed into an array slot and can be accessed from there. Each match of the entire pattern will generate one row, which you could re-aggregate if you wanted to.
SELECT concat_ws(' ',regexp_matches[1],regexp_matches[2])
   from regexp_matches( 'foobar[i:89]asdfasdf[s:12]llll[i:9]',
                        '\[([is]?:?)(\d+)\]',
                        'gi'
                      );


Error with restoring from a split .bak file with password

Below will work, as you had syntax error :

USE master
GO
RESTORE DATABASE client_a_database1
  FROM DISK = 'G:\database1\***.bak_1',
       DISK = 'G:\database1\***.bak_2',
       DISK = 'G:\database1\***.bak_3'
  WITH MOVE 'database1_data' TO 'G:\database1\***.mdf'
       MOVE 'database1_log' TO 'G:\database1\***.ldf', REPLACE,
  Password = 'password'
  GO

Also, a RESTORE HEADERONLY output will help understand why you are getting -

  restore was taken from a different MS SQL version.


how to track progress of a large postrgresql dump

You can see a rough progress using the TOC list.

First, get the TOC list of objects to be restored:

pg_restore -l -f list.toc db.dump

Then, you can see the TOC list line by line and compare the output of verbose or query pg_stat_activity to see where in the TOC list is pg_restore in.

It is just a rough estimate though. First because each item from the TOC list may take really different time to load (for instance, schemas are fast, but loading data of big tables and building indexes are not), and if you use -j you'll have an item being restored before a previous one has finished. Also, I'm not 100% sure if pg_restore follows TOC list precisely if you don't use -L, but I think it does.


Hekaton was the internal Microsoft project that references the In-Memory OLTP feature, starting with SQL 2014. Some people still use the word Hekaton generically, to describe the In-Memory feature in any version of SQL server (2014, 2016, 2017).

In-Memory OLTP is a feature that stands alone - it is not part of any other feature, i.e. Analysis Services.

There is tons of info out there on the web about In-Memory OLTP, but I would caution you to avoid anything that references SQL 2014 (as you have above), because that was essentially v1.0, and much has changed since then.

First - have you proven through a valid POC that your workload is likely to benefit from using In-Memory OLTP?

I have blogged extensively about In-Memory OLTP, but you might be better off starting with the documentation, here:

https://docs.microsoft.com/en-us/sql/relational-databases/in-memory-oltp/in-memory-oltp-in-memory-optimization


Finding count of null values in 500 columns of a single table

An alternative that does not use a cursor:

-- Set the target table details here
DECLARE
    @Schema sysname = N'Production',
    @Table sysname = N'Product',
    @SQL nvarchar(max) = N'SELECT ';

DECLARE
    @QName sysname = QUOTENAME(@Schema) + N'.' + QUOTENAME(@Table);

SET @SQL +=
(
    SELECT
        NCHAR(13) + NCHAR(9) +
        QUOTENAME(C.name + N'_NullCount') + ' = ' +
        N'COUNT_BIG(*) - COUNT_BIG(' +
        QUOTENAME(C.name) + N'),'
    FROM sys.columns AS C
    WHERE
        C.[object_id] = OBJECT_ID(@QName)
        AND C.is_nullable = CONVERT(bit, 1)
        AND C.is_filestream = CONVERT(bit, 0)
        AND C.is_column_set = CONVERT(bit, 0)
    ORDER BY
        C.column_id
    FOR XML PATH (''), TYPE
).value('.[1]/text()[1]', 'nvarchar(max)');

SET @SQL =
    STUFF(@SQL, LEN(@SQL), 1,  NCHAR(13) + N'FROM ' + @QName + N';');

PRINT @SQL;

EXECUTE (@SQL);

The SQL generated on the AdventureWorks example given is:

SELECT
    [Color_NullCount] = COUNT_BIG(*) - COUNT_BIG([Color]),
    [Size_NullCount] = COUNT_BIG(*) - COUNT_BIG([Size]),
    [SizeUnitMeasureCode_NullCount] = COUNT_BIG(*) - COUNT_BIG([SizeUnitMeasureCode]),
    [WeightUnitMeasureCode_NullCount] = COUNT_BIG(*) - COUNT_BIG([WeightUnitMeasureCode]),
    [Weight_NullCount] = COUNT_BIG(*) - COUNT_BIG([Weight]),
    [ProductLine_NullCount] = COUNT_BIG(*) - COUNT_BIG([ProductLine]),
    [Class_NullCount] = COUNT_BIG(*) - COUNT_BIG([Class]),
    [Style_NullCount] = COUNT_BIG(*) - COUNT_BIG([Style]),
    [ProductSubcategoryID_NullCount] = COUNT_BIG(*) - COUNT_BIG([ProductSubcategoryID]),
    [ProductModelID_NullCount] = COUNT_BIG(*) - COUNT_BIG([ProductModelID]),
    [SellEndDate_NullCount] = COUNT_BIG(*) - COUNT_BIG([SellEndDate]),
    [DiscontinuedDate_NullCount] = COUNT_BIG(*) - COUNT_BIG([DiscontinuedDate])
FROM [Production].[Product];

Execution plan:

Results (sample):


How to convert memory optimized table into disk based table in sql server

Could anyone please explain the process to convert memory optimized table into disk based table in sql server?

Create a new disk based table and insert the data from the in-memory table or use select into to copy the data over and create the table at the same time. There are various other ways but all are variations on this.

Please note that you cannot remove the in-memory filegroup from the database, so if you're attempting to clean up all traces of in-memory then I'd suggest migrating the data to a completely new database.


Storing a Billing Address Best Practice in Orders Table

Conceptually speaking, although in your business environment Order and Address are ideas that are closely associated, they are in effect two separate entity types, each with its own set of applicable properties (or attributes) and constraints.
Therefore, as previously stated in comments, I agree with @Erik, and you should organize the logical layout of your database declaring among other elements:

one discrete table to keep Address pieces of information;
one table to retain Customer-specific details;
one table to enclose Order data points; and
one table to contain facts about the associations between Customer(s) and Address(es);

as I will exemplify below.
Expository IDEF1X diagram
A picture is worth a thousand words, so I created the IDEF1X diagram shown in Figure 1 to illustrate some of the possibilities opened by my suggestion:

Customer, Address and their associations
As demonstrated, I portrayed an association with a many-to-many (M:N) cardinality ratio between the entity types Customera and Address; this approach would provide future flexibility because, as you know, a Customer can keep multiple Addresses over time, or even simultaneously, and the same Address can be shared by multiple Customers.
A particular Address can be used in several ways by one-to-many (1:M) Customers; e.g., it can be defined as Physical, and/or it can be set for Shipping, and/or for Billing. Perhaps, the same Address instance can serve each of the aforementioned purposes at the same time, or it may be covering two uses while a different Address occurrence covers the remaining one.

a In some business environments, a Customer can be either a Person or an Organization (situation that would imply a slightly distinct arrangement, as detailed in this answer about a supertype-subtype structure) but with the objective of providing a simplified example, I decided not to include that possibility here. In case you need to cover that situation in your database, the previously linked post shows the method to solve said requirement.

Order, Address, CustomerAddress and Address Roles
Commonly, an Order requires only two kinds of Addresses, one for Shipping and one for Billing . In this way, the same Address instance could fill both Roles for an individual Order, but each Role is pictured by the respective property, i.e., ShippingAddressId or BillingAddressId.
Order is connected with Address via the CustomerAddress associative entity type with the aid of two multi-property FOREIGN KEYs, i.e.,

(CustomerNumber, ShippingAddressId), and (CustomerNumber, BillingAddressId),

both pointing to the CustomerAddress multi-property PRIMARY KEY shown as

(CustomerNumber, AddressId)

… which helps to represent a business rule that stipulates that (a) an Order instance must be linked exclusively with (b) Address occurrences previously associated with the specific Customer who made that Order, and never with (c) a random non-Customer-related Address.
History for (1) Address and for (2) the CustomerAddress association
If you want to supply the possibility of modifying Address pieces of information, then you have to keep track of all the data changes. In this manner I depicted Address as an “auditable” entity type that maintains its own AddressHistory.
Since the nature of a connection between a Customer and an Address can also suffer one or more modifications, I have as well depicted the possiblity of handling such an association as an “auditable” one by virtue of the CustomerAddressHistory entity type.
In this respect, various factors dealt with in Q & A no. 1 and Q & A no. 2, —both about enabling temporal capabilities in a database— are really relevant.
Illustrative SQL-DDL logical layout
Consequently, in terms of the diagram displayed and explained above, I declared the following logical-level arrangement (which you can adapt to meet your needs with exactitude):
-- You should determine which are the most fitting
-- data types and sizes for all your table columns
-- depending on your business context characteristics.

-- Also, you should make accurate tests to define the
-- most convenient INDEX strategies based on the exact
-- data manipulation tendencies of your business domain.

-- As one would expect, you are free to utilize
-- your preferred (or required) naming conventions.

CREATE TABLE Customer (
    CustomerNumber      INT      NOT NULL,
    SpecificAttribute   CHAR(30) NOT NULL,
    ParticularAttribute CHAR(30) NOT NULL,
    CreatedDateTime     DATETIME NOT NULL,
    --
    CONSTRAINT Customer_PK PRIMARY KEY (CustomerNumber)
);

CREATE TABLE Address (
    AddressId           INT      NOT NULL,
    SpecificAttribute   CHAR(30) NOT NULL,
    ParticularAttribute CHAR(30) NOT NULL,
    CreatedDateTime     DATETIME NOT NULL,
    --
    CONSTRAINT Address_PK PRIMARY KEY (AddressId)
);

CREATE TABLE CustomerAddress (
    CustomerNumber  INT      NOT NULL,
    AddressId       INT      NOT NULL,
    IsPhysical      BIT      NOT NULL,
    IsShipping      BIT      NOT NULL,
    IsBilling       BIT      NOT NULL,
    IsActive        BIT      NOT NULL,
    CreatedDateTime DATETIME NOT NULL,
    --
    CONSTRAINT CustomerAddress_PK           PRIMARY KEY (CustomerNumber, AddressId),
    CONSTRAINT CustomerAddressToCustomer_FK FOREIGN KEY (CustomerNumber)
        REFERENCES Customer (CustomerNumber),
    CONSTRAINT CustomerAddressToAddress_FK  FOREIGN KEY (AddressId)
        REFERENCES Address  (AddressId)
);

CREATE TABLE MyOrder (
    CustomerNumber      INT      NOT NULL,
    OrderNumber         INT      NOT NULL,
    ShippingAddressId   INT      NOT NULL,
    BillingAddressId    INT      NOT NULL,
    SpecificAttribute   CHAR(30) NOT NULL,
    ParticularAttribute CHAR(30) NOT NULL,
    OrderDate           DATE     NOT NULL,
    CreatedDateTime     DATETIME NOT NULL,
    --
    CONSTRAINT Order_PK                  PRIMARY KEY (CustomerNumber, OrderNumber),
    CONSTRAINT OrderToCustomer_FK        FOREIGN KEY (CustomerNumber)
        REFERENCES Customer        (CustomerNumber),
    CONSTRAINT OrderToShippingAddress_FK FOREIGN KEY (CustomerNumber, ShippingAddressId)
        REFERENCES CustomerAddress (CustomerNumber, AddressId),
    CONSTRAINT OrderToBillingAddress_FK  FOREIGN KEY (CustomerNumber, BillingAddressId)
        REFERENCES CustomerAddress (CustomerNumber, AddressId)
);

CREATE TABLE AddressHistory (
    AddressId           INT      NOT NULL,
    AuditedDateTime     DATETIME NOT NULL,
    SpecificAttribute   CHAR(30) NOT NULL,
    ParticularAttribute CHAR(30) NOT NULL,
    CreatedDateTime     DATETIME NOT NULL,
    --
    CONSTRAINT AddressHistory_PK          PRIMARY KEY (AddressId, AuditedDateTime),
    CONSTRAINT AddressHistoryToAddress_FK FOREIGN KEY (AddressId)
        REFERENCES Address  (AddressId)
);

CREATE TABLE CustomerAddressHistory (
    CustomerNumber  INT      NOT NULL,
    AddressId       INT      NOT NULL,
    AuditedDateTime DATETIME NOT NULL,
    IsPhysical      BIT      NOT NULL,
    IsShipping      BIT      NOT NULL,
    IsBilling       BIT      NOT NULL,
    IsActive        BIT      NOT NULL,
    CreatedDateTime DATETIME NOT NULL,
    --
    CONSTRAINT CustomerAddressHistory_PK                  PRIMARY KEY (CustomerNumber, AddressId, AuditedDateTime),
    CONSTRAINT CustomerAddressHistoryToCustomerAddress_FK FOREIGN KEY (CustomerNumber, AddressId)
        REFERENCES CustomerAddress (CustomerNumber, AddressId)
);

If you want to have a look, I tested it in this db<>fiddle that runs on SQL Server 2017.
The History tables
The following excerpt from your question is very important:

What I'm looking for is how I can set up my addresses so when I edit them, the order is not affected by the fact that a customer updates his address or relocates.

The AddressHistory and CustomerAddressHistory tables aid in ensuring that an Order is not affected by Address changes, as all the “previous” rows should be retained in the respective History table and may be involved in SELECT queries whenever necessary.
The Interval encompassed between the values enclosed in AddressHistory.CreatedDateTime and AddressHistory.AuditedDateTime stands for the entire Period during which a certain “past” Address row was  deemed “present”, “current” or “effective”. Similar considerations apply to the CustomerAddressHistory rows.
In turn, the CustomerAddress.IsActive BIT (boolean) column is meant to point out whether a certain Address row is “usable” by a Customer row or not; e.g., if it is set to ‘false’ it would convey the situation in which a Customer is not using that Address anymore and hence it cannot be used for new Orders.
Data manipulation factors
Modifiying and erasing
If a Customer wants to change one or more pieces of information about a given “current” Address, one must ensure that (a) the corresponding Address row that was “present” until the modification took place is INSERTed into the AddressHistory table, and also that (b) the Address row in question is UPDATEd with the new value(s). I suggest that you carry out this process as a single unit of work inside an individual ACID TRANSACTION.
UPDATE operations on the two History tables should be forbidden (trying to “change” history can even have negative legal implications), since each row retained in that “kind” of tables represents a fact that happened in the past, thus it cannot be modified and a row about it should not be altered.
As for DELETEs on these tables, there are very specific cases where they must be enabled in order to comply with, say, confidentiality laws/policies —but that, as usual, depends entirely on the exact requirements of the business environment of significance—.
Retrieval and physical-level tuning
The “present”, “current” or “effective” version of an Address occurrence must be contained as a row in the Address table, but SELECTing the previous “states” of an Address FROM the AddressHistory (or from CustomerAddressHistory) table is easy, and it may be an interesting exercise to enhance your SQL coding skills.
With respect to one of the situations that you mentioned in comments, if you want to retrieve the “second to last version” of an individual Address row FROM its AddressHistory, you have to take into account the MAX(AddressHistory.AuditedDateTime) and the AddressHistory.AddressId that matches the particular Address.AddressId value at hand.
In this regard —at least when building a relational database—, it is quite convenient to first define the corresponding conceptual schema (based on the applicable business rules) and after that declare its subsequent logical DDL arrangement. Once you obtain stable and reliable versions of these fundamental elements (which, of course, can evolve over time), it is time to analyze and determine the best ways to manipulate (via INSERT, UPDATE, DELETE and SELECT operations or combinations thereof) the concerning data, taking care of the optimization of the respective physical level processing (by dint of, e.g., index tunning, hardware upgrades, proper software settings, etc.).
End-users perception, views and application program(s) assistance
Evidently, at the external level of abstraction, Address information is perceived (by end-users) as being part of an Order —and there is nothing wrong with it—, but that does not necessarily mean that the modelers have to design the significant parts of the database in question in correspondance with said perception. On this point, if there is the need to, e.g., print a “full” Order (very feasible), you can “reproduce” it on-demand with the help of a few JOIN operators and WHERE clauses (considering the concerning validity period, etc.) maybe fixed in views for future consumption, sending the pertinent data set to the related application program(s) that, in turn, can enhance its formatting as necessary.
Of course, the application program(s) will be very helpful too when an Order is being effectuated; e.g., a desktop/mobile app window or a web page can:

display only the Address(es) that the involved Customer has established as “usable” (via CustomerAddress.IsActive);
list together all the Addresses that the Customer has enabled for billing service (via CustomerAddress.IsBilling); and
group all the Addresses that the Customer has defined for shipping service (via CustomerAddress.IsShipping);

facilitating in this manner all the involved processes at the GUI (which, naturally, is part of the external level of abstraction of a computerized system).

Alternative
On the other hand, I have seen some systems in which every time that a new Order is effectuated the relevant Address information has to be entered (sometimes repeatedly), and the Address(es) used for past Orders are never erased, hence the Orders are not affected by Address changes.
This course of action can decidedly involve substantial volumes of data duplication, but it is a possibility that —depending on the exact informational requirements of your business domain— could work, so you might like to evaluate its pros and cons as well.

Comment interactions

Could you explain me the reason we need an Address entity? wouldn't be enough just have CustomerAddress? – @Cristiano, on Mar 11 at 19:38:05Z

In this specific case, the original poster —with a now deleted account— presented a scenario involving an Address entity type that holds four discrete properties of interest, i.e., AddressID, AddressType, AddressLine1 and AddressLine2 —as shown in the question itself—, each of which (a) has a particular set of valid values, (b) involves a potential exclusive set of constraints and (c) implies values that have to be manipulated separately. In addition, (d) any given Address instance —i.e., the proper Address entities— may be associated with n occurrences of entities of other types; therefore, (e) Address is an entity type in its own right.
There very well may be other situations where, depending on the exact business requirements, a Customer.Address should be treated as a single property, in which case its values have to be handled atomically at the logical level of abstraction, i.e., you always manipulate every Address value as a whole, never perform operations (INSERTs, UPDATEs, DELETEs, SELECTs) on certain value part(s) only. Of course, if required, one should first set up constraints for the corresponding atomic Customer.Address column, since these would not be necessary for column “fractions”. One of these situations could be the scenario discussed in the prior section that is entitled “Alternative”.
Suggested reading
You requested (in now removed comments) some pointers about sound database literature; therefore, as for theoretical material, I highly advise that you read all the work written by Dr. E. F. Codd, a Turing Award recipient and, of course, the sole originator of the relational model of data (maybe now more relevant than ever). This list includes some of his tremendously influential articles and papers.
Two important works that are not comprised in the aforesaid list are, precisely, his ACM Turing Award Lecture entitled Relational Database: A Practical Foundation for Productivity, from 1981, and his book denominated The Relational Model for Database Management: Version 2, which was published in 1990.
On the conceptual design front, Integrated Definition for Information Modeling (IDEF1X) is a seriously recommendable technique that was defined as a standard in December 1993 by the U.S. National Institute of Standards and Technology (NIST).


Firebird2.5-superclassic on Ubuntu 16.04 LTS

Ok, so I found functions.sh in /usr/share/firebird2.5-common. Running this creates all of the maintenance scripts and logging files that need to be there. It also creates security2.fdb which stores the security info for users and sets up the password according to the SYSDBA.password file.  You will need to set up two environment variables before running this:

FB_VER

and

FB_FLAVOUR

These need to be set to the point release you are on, ex: 2.5 and the flavor you are running, ex: superclassic.

Note: Mark Rotteveel's solution seemed to work as well, but I think this would be deemed as the more acceptable method.


Mariadb is giving warning for this query and table

You expect the subselect (select meta_value from test3 where meta_key='lead_date') to materialize only rows that contain values in meta_value that can be converted to the DATE data type, but this is not happening: the optimizer inlines the subselect and simultaneously applies two predicates: meta_key='lead_date' and date(meta_value)<CURDATE() to all rows in the table, which obviously fails for rows containing 'facebook' in meta_value.


The setting to disable page locking applies per index, so applying this change to the clustered index only affects execution plans that access the data via that index.  If there are nonclustered indexes on the table, you may have to disable page locking for them as well.  The following script demonstrates this:

CREATE TABLE dbo.LockTest
(
    col1    integer IDENTITY NOT NULL,
    col2    integer NOT NULL,
    col3    integer NOT NULL,

    CONSTRAINT PK_LockTest
        PRIMARY KEY CLUSTERED (col1)
);
GO
CREATE UNIQUE NONCLUSTERED INDEX UQ_LockTest
ON dbo.LockTest (col2);
GO
ALTER TABLE dbo.LockTest SET (LOCK_ESCALATION=DISABLE);
ALTER INDEX PK_LockTest ON dbo.LockTest SET (ALLOW_PAGE_LOCKS=OFF);

Using the clustered index as the access method:

-- Error 651:
-- Cannot use the PAGE granularity hint on the table "dbo.LockTest"
-- because locking at the specified granularity is inhibited.
SELECT col1
FROM dbo.LockTest WITH (INDEX(PK_LockTest), PAGLOCK);

Using the nonclustered index:

-- Succeeds: page locks allowed on index UQ_LockTest
SELECT col2 FROM dbo.LockTest AS lt WITH (PAGLOCK);

-- Succeeds: UQ_LockTest also includes col1
-- NC indexes always include the clustering key
SELECT col1 FROM dbo.LockTest AS lt WITH (PAGLOCK);

-- Fails: cannot retrieve col3 without touching the clustered index
SELECT col3 FROM dbo.LockTest AS lt WITH (PAGLOCK);

Note that disabling page locking can have unexpected side-effects, such as preventing index reorganization (since this process works at the page level):

-- Error 1943:
-- The index "PK_LockTest" on table "LockTest" cannot
-- be reorganized because page level locking is disabled.
ALTER INDEX PK_LockTest ON dbo.LockTest REORGANIZE;

-- Succeeds (page locking available):
ALTER INDEX UQ_LockTest ON dbo.LockTest REORGANIZE;


PostgreSQL: How to list the comments attached to a database

I looks like this has been already answered on SE network. Check out this

In particular, note that the question mentions \l+ in psql.


What is technical meaning of point in time recovery?

Point in time recovery is the concept that a particular set of data can be restored to an exact point in time, rather than just to the time of the last backup file.

In this case with SQL Server, log backups are the usual mechanism for accomplishing this to ensure database consistency.

FULL or BULK recovery modes must be utilised to allow for point in time restore, with the BULK option minimizing the amount of log space needed for certain operations (CREATE INDEX, SELECT INTO, BULK INSERT...). If any of these mechanisms are used, the ability to restore to a point in time using a log backup is dropped, forcing you to restore to before / after the bulk logged operation, for that individual log backup. For more information on this see SQL Server Recovery Models. The reason for this is that the bulk-logged model utilising minimal-logging will not log the data pages to the log file during bulk-logged operations - it will only log the extents that have been changed. Whether you want to use BULK recovery model or not will depend on your individual circumstances and whether you can afford to drop the point in time restores in favour of potentially faster bulk logged operations.

Other than that, FULL recovery model will support your use case.


How to select one row from a table and sum another table

select
  a.name,
  b.sumx
from
  (select name from a where rownum <=1) a,
  (select sum(x) as sumx from b) b
;


What is the difference between ALTER INDEX and DBCC DBREINDEX?

The DBCC command is a consistency checker utility inside of SQL Server whereas ALTER is a DDL SQL command.  The DBCC REINDEX command was deprecated in a previous version so it is not as robust as the new ALTER INDEX command is today.  There is more functionality in the ALTER INDEX than the DBCC REINDEX commands:

http://msdn.microsoft.com/en-us/library/ms188388.aspx

Happy indexing!


SQL Except Statement and Add in One more column

Here is a sample solution (based on sp_BlitzErik's comment):

--demo setup
drop table if exists dbo.CustomerTransaction1;
drop table if exists dbo.CustomerTransaction2;
drop table if exists dbo.CustomerTransactionDiff;
Create table dbo.CustomerTransaction1
(
    CustomerTransactionId int primary key,
    CustomerName varchar(255),
    PurchasePrice decimal(10,2),
    Quantity int,
    Loaddate datetime
);

insert into dbo.CustomerTransaction1(CustomerTransactionId,CustomerName,PurchasePrice,Quantity,Loaddate) values
(1,'Fred',100,5,'2018-10-25'),
(2,'Bob',200,1,'2018-10-25')

Create table dbo.CustomerTransaction2
(
    CustomerTransactionId int primary key,
    CustomerName varchar(255),
    PurchasePrice decimal(10,2),
    Quantity int,
    Loaddate datetime
);
insert into dbo.CustomerTransaction2(CustomerTransactionId,CustomerName,PurchasePrice,Quantity,Loaddate) values
(2,'Bob',200,1,'2018-10-25')

Create table dbo.CustomerTransactionDiff
(
    CustomerTransactionId int primary key,
    CustomerName varchar(255),
    PurchasePrice decimal(10,2),
    Quantity int,
    Loaddate datetime
);

--The solution
INSERT INTO CustomerTransactionDiff (
    c1.CustomerTransactionId
    ,CustomerName
    ,c1.PurchasePrice
    ,c1.Quantity
    ,c1.Loaddate
    )
SELECT c1.CustomerTransactionId
    ,c1.CustomerName
    ,c1.PurchasePrice
    ,c1.Quantity
    ,c1.Loaddate
FROM dbo.CustomerTransaction1 c1
WHERE NOT EXISTS (
        SELECT *
        FROM dbo.CustomerTransaction2 c2
        WHERE c2.CustomerTransactionId = c1.CustomerTransactionId
            AND c2.CustomerName = c1.CustomerName
            AND c2.PurchasePrice = c1.PurchasePrice
            AND c2.Quantity = c1.Quantity
        );

--verify differences
select * from dbo.CustomerTransactionDiff

| CustomerTransactionId | CustomerName | PurchasePrice | Quantity | Loaddate                |
|-----------------------|--------------|---------------|----------|-------------------------|
| 1                     | Fred         | 100.00        | 5        | 2018-10-25 00:00:00.000 |


SQL Server 2012 Page Compression - Determine compression ratio?

How about sp_estimate_data_compression_savings?

It allows you to estimate the gain from compression BEFORE you compress the table, but it's quite accurate.


Could a large log file be the reason cron job keeps dying?

If you are talking about the MongoDB log file becoming too large you should take a look at the log rotation options here:

http://www.mongodb.org/display/DOCS/Logging#Logging-Rotatingthelogfiles

Short version - you can rotate the logs via an admin command within MongoDB or you can send a SIGUSR1 - combine with your favorite log management method and you should be good.

Something else to consider though is why the log is so large - unless it has been running for a long, long time, 15GB is a lot of logging.  You may want to make sure that you are not starting up with logging set too high (-vv etc.).  You can also check the log level with the setParameter command.  For example, to set to 0 (the default), without restarting, just issue it like this:

db.adminCommand({setParameter:1, logLevel:0})


Temporarily disable indexes before updating whole table

When you restore the data, use pg_restore --section=pre-data --section=data.  This will leave off the indexes.

Then run the UPDATEs you want to, then run pg_restore --section=post-data


If your client is unable to give you a proper database dump, then whatever task they're asking of you will take far longer and cost much more than anyone should be willing to tolerate.
Is there a way I can restore the database with only ibdata1 file?
⇢ Yes, but not without a remarkable amount of work. People who are not trying to save a multi-billion dollar company from lawsuits should never have to do this.
Shall I need any other files apart from .frm files and ibdata1 file in order to restore the database?
⇢ the .frm files contain the definition of a database. The ibdata1 file can contain many databases (depending on how MySQL was configured). If you must restore the database with only the ibdata1 and .frm files, you will need all of the files in their correct directories. From there you may be able to restore so long as your version of MySQL is either exactly the same or just slightly newer than the version the client is running.
Note that restoring ibdata and .frm files from a foreign MySQL installation will obliterate any databases you already have configured on the receiving machine.
Suppose I can restore the database using just ibdata1 file or I get .frm files in future, Do I need to install a version that matches the client's MySQL version?
⇢ This was answered in the previous bit. While it's technically feasible to take an ibdata1 and accompanying .frm files from earlier versions of MySQL and upgrade them, there can be problems when jumping a large number of releases. Going from MySQL 5.3.x to 8.0.24, for example, is almost guaranteed to fail unless the source database is structured more like a CSV than a series of relational tables.
If your client is able to access the MySQL server, have them create a proper backup with MySQLDump and provide that file to you. This way you do not have to worry as much about versions or anything else. Everybody will save time and hassle.


How to write a query to find all tables in a db that have a specific column name

Using information_schema

This is the standards-compliant cross-RDBMS way to do it.

SELECT table_catalog, table_schema, table_name, column_name
FROM INFORMATION_SCHEMA.columns
WHERE column_name = '<your column name>';

You can see this documented

PostgreSQL
SQL Server
MySQL 8.0


How to get the port of a connection in mysql

You should be able to use one of the solutions in this StackOverflow: How do I find out my MySQL URL, host, port and username?
Specifically I think this is what you're looking for: SHOW VARIABLES WHERE Variable_name = 'port';


Ubuntu: upgrading postgresql 8.4 database cluster for use with 9.2 server

Don't know about 2) and 3) (I don't use Ubuntu).

But to migrate the data from 8.4 to 9.1 pg_dump.

Assuming that that 8.4 runs on port 5432 and 9.1 runs on port 5433 something like the following will copy all the data from 8.4 to 9.1:

/usr/lib/postgresql/9.1/bin/pg_dump -p 5432 -U -c -C postgres my_db | /usr/lib/postgresql/9.1/bin/psql -p 5433

This assumes that your current user can log on to both databases.

The full path to pg_dump is necessary to ensure that the dump is taken using the target version.

Of course you can also use an intermediate file if you prefer that. More details are in the manual: PostgreSQL: Documentation: 9.1: Backup and Restore

Using the pg_dump binary from the new version is intentional.

From the manual: 17.6. Upgrading a PostgreSQL Cluster - 17.6.1. Upgrading Data via pg_dump

  It is recommended that you use the pg_dump and pg_dumpall programs from the newer version of PostgreSQL


What's the point of SQL joins if you have OLAP?

You're basically talking about two entirely different technologies, used for two different purposes.

A relational database stores information, commonly in a normalized fashion which is most suited for day-to-day processing and storage of business information. Given this storage model, you need joins to.. well, join those relational tables together when you want to compile data and properties from those different sources.

OLAP cubes are not really used to store information, but rather duplicate and re-organize the information in a denormalized way so as to allow for typical analytical queries, using MDX for instance. What makes OLAP cubes so fast is that a lot of aggregates are pre-calculated, and this comes at a potentially great cost in storage.

Excel is not a realiable OLAP tool, imho - it can act as an interface to cubes, but if you try to use PowerPivot with any larger set of data, you'll very quickly find your workstation out of memory.

On a final note, I wouldn't say that cubes and tabular models are "omnipresent". I would say that they can often be found for specific end-user applications, and as such they are often stripped down to a minimum of dimensions, attributes and measures in order to avoid confusing the users. Power-users will often go directly to the relational database to get what they need.


You may be running into this:
https://blogs.msdn.microsoft.com/psssql/2017/06/07/you-may-see-out-of-user-memory-quota-message-in-errorlog-when-you-use-in-memory-oltp-feature/


PostgreSQL 9.6, Disable archive log or minimal archive log size when do a pg_restore

The best you can do is to set wal_compression = on to compress whole blocks that are written to WAL, but that will cost CPU.

The information has to be transferred from the master to the slave, so you cannot avoid generating WAL.

However, that WAL need not be stored on disk permanently, it should be streamed from master to slave directly. Perhaps the network is not strong enough to stream all that information in a timely fashion, and it needs to be kept around on disk for a while.


After checking they aren't attached to an active process, ls -la /proc/$(pidof mysqld)/fd, or fuser {filename}, they can be removed with rm.

From MariaDB-10.4 onwards you shouldn't see these created on disk again ( MDEV-15584).


Autogrowth, shrink and database performance

Yes, picking an autogrowth size that makes sure you minimize or eliminate the number of growth events during your business cycle is ideal. I would venture to say that if 300 MB per month is that predictable, you should also - in addition to setting more reasonable autogrowth settings - to grow the file right now by 4 or 8 GB to allow for one or two years of growth without having to worry about interruptive growth events (even with instant file initialization, data file growths still cause transactions to wait while they complete). If you know you're going to grow by about 300 MB per month, why put off all those growth events? What are you going to do with the space in the meantime? Lease it out month-to-month?
You are probably fine shrinking that specific database to 200 MB. Be sure to do this with DBCC SHRINKFILE or ALTER DATABASE MODIFY FILE, not DBCC SHRINKDATABASE.
The Express limitation is on the data file only. You can have a 9.99 GB data file and a 600 GB log file, and SQL Server won't complain. Well, it will in other ways, but not due to exceeding any arbitrary file size limitation. That said, you should either make sure to be in simple recovery or back up your log at a reasonable rate. Log files should never be growing uncontrolled - and if you're worried about the log file on an Express instance, something isn't right. Please see the following posts for plenty of discussion:

SHRINKFILE best practices and experience
Sql Server - Best Practices for Growing Database Files
Why Does the Transaction Log Keep Growing or Run Out of Space?


Quote from the manual

  You can also set the behavior on a function-by-function basis, by inserting one of these special commands at the start of the function text:

#variable_conflict error
#variable_conflict use_variable
#variable_conflict use_column

But for readability (and maintainability) I would strongly suggest to rename the variable to something different.

Many people simply use a prefix for the parameters (e.g. p_staff_id) and also prefixes for local variables (e.g. l_some_var) to make sure they never clash with each other or column names (this is also the de-facto standard in the Oracle world)


Get data from multiple databases at once

One of the methods you can try is sp_msforeachdb. Making some assumptions about your datatypes in the @table below...

declare @results table (
    prac_id int,
    prac_name nvarchar(100),
    prace_surname nvarchar(100),
    prac_IDcard nvarchar(100),
    prac_workplace nvarchar(100)
);

insert @results
exec sp_msforeachdb N'
use [?]

if left(''?'',6) = ''CDNXL_'' -- only execute the query against databases
                              -- that match the naming pattern
    and ''?'' <> ''CDNXL_Configuration''
begin
    select
        prac_id,
        prac_name,
        prace_surname,
        prac_IDcard,
        prac_workplace
    from prac_nag.[?]
    where prac_IDcard = @parameter;
end;
';

select * from @results;


There is no such feature built into PostgreSQL.

You could use the PROGRAM option to do something like this:

copy pgbench_branches to program 'test -f /tmp/foo && exit 1|| cat > /tmp/foo';

But that is pretty undesirable as you have to enter the filename twice so it could get out of sync. And if you can't remember that you already exported the data, you probably can't remember to code it this way in the first place, unless it is encapsulated into a script.

If you compile your own server,  it would be pretty easy to hack the server to have this feature (example for linux):

diff --git a/src/backend/commands/copy.c b/src/backend/commands/copy.c
index 4f04d12..552fb81 100644
--- a/src/backend/commands/copy.c
+++ b/src/backend/commands/copy.c
@@ -1915,7 +1915,7 @@ BeginCopyTo(ParseState *pstate,
            oumask = umask(S_IWGRP | S_IWOTH);
            PG_TRY();
            {
-               cstate->copy_file = AllocateFile(cstate->filename, PG_BINARY_W);
+               cstate->copy_file = AllocateFile(cstate->filename, "wx");
            }
            PG_CATCH();
            {

But running a customized server comes with its own cost (you have to reapply it to ever minor version update, you become reliant on features that don't exist outside your own set up, new people have to be trained on your peculiarities, etc.).


Most efficient way of caching queries / processed data

Have you looked into using SQL Server's support for Indexed Views?  (Generically this is called a materialized view.)  If your code really needs an assist in getting the answers this is a option, though not without its own difficulties.

An Indexed View is a duplicate set of data taken from the main tables and stored within the Indexed View.  There are rules and limitations, but it basically creates a specialized "table" that should be index-tuned to suite your purpose for using it.

This MSDN topic points you to the details that you need:

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

The definition of an indexed view must be deterministic, needs certain settings to be adjusted, and there are actions such as OUTER JOIN and ORDER BY that are forbidden.

EDIT: You should also use the NOEXPAND hint, as explained by Aaron Bertrand, to "ensure the uniqueness guarantee provided by the view indexes is used by the optimizer."  See:  http://sqlperformance.com/2015/12/sql-performance/noexpand-hints


How to monitor for MySQL select queries that return too many records?

SUGGESTION #1 : STATUS VARIABLES
You could check the status variable
SHOW GLOBAL STATUS LIKE 'Innodb_rows_read';

or read it from the performance_schema
SELECT variable_value FROM performance_schema.global_status
WHERE variable_name = 'Innodb_rows_read';

You could see other row metrics as well:
mysql> select version(); show global status like 'InnoDB_rows%';
+-----------+
| version() |
+-----------+
| 5.7.26    |
+-----------+
1 row in set (0.00 sec)

+----------------------+-------+
| Variable_name        | Value |
+----------------------+-------+
| Innodb_rows_deleted  | 0     |
| Innodb_rows_inserted | 0     |
| Innodb_rows_read     | 8     |
| Innodb_rows_updated  | 0     |
+----------------------+-------+
4 rows in set (0.00 sec)

mysql>

SUGGESTION #2 : SLOW LOG
If you need to see the number of rows returned or examined by any given query, you will need to enable the slow query log.
Here is an example of a slow log entry that sees a mysqldump in progress
# Time: 150419  6:00:43
# User@Host: web[web] @ localhost []
# Query_time: 7.730519  Lock_time: 0.000070 Rows_sent: 167620  Rows_examined: 167620
SET timestamp=1429416043;
SELECT /*!40001 SQL_NO_CACHE */ * FROM `messages`;

As shown, the number of rows returned and examined are in the header.


Where is the data stored in MySQL databases?

Does turning on MySQL server turn my local computer into a server and stores databases in my computer when I create them using MySQL ?

Basically yes. Please consider that you are using server with two distinct meanings here. When you talk of your MySQL server which you turn on, you mean a process running in background, listening and fulfilling requests. When you say turn my local computer into a server you mean a computer on which such a process runs. However, you can decide which other computers can talk to your server process. Your local MySQL clients will access it regardless, but it is possible that your MySQL server is configured by default not to listen to external requests, and it is the safest configuration. Opening up to other machines involves security concerns which will have to be addressed when the system goes into production on a real server, not now.

  If so can I locate the database ?

Sure. But where it is depends on the package you have chosen to install MySQL. Have a look at this SO answer for different options. /usr/local/mysql/ and /usr/local/var/mysql/ are usual locations.

  Can I make a offline database using MySQL which can be located through 'Finder'?

Not clear what you are asking. You can locate your DB files through Finder in the locations above, but they will be of little use without a running server process, so they are not what is usually meant by offline database such as SQLite. There are several utilities capable of accessing your data in the files without a running server, but they are meant for backup or emergency recover and they won’t give you a fully functional RDBMS.


Will the source dataset for a cursor update on each iteration if within the cursor loop I'm updating one of the tables that dataset references?

in other words does updating Table2 modify the dataset that uses
  Table2 for the original cursor definition.

You are inserting data into table2 and these data changes could be visible unless you specify the STATIC keyword.

STATIC

  Specifies that the cursor always displays the result set as it was
  when the cursor was first opened, and makes a temporary copy of the
  data to be used by the cursor. All requests to the cursor are answered
  from this temporary table in tempdb

There are other implications to what data is visible to the cursor depending on the declaration of the cursor.

Keywords such as KEYSET, DYNAMIC, FAST_FORWARD,FORWARD_ONLY impact this behaviour.

An example in the documentation on FORWARD_ONLY

  ...All insert, update, and delete statements made by the current user (or
  committed by other users) that affect rows in the result set are
  visible as the rows are fetched. Because the cursor cannot be scrolled
  backward, however, changes made to rows in the database after the row
  was fetched are not visible through the cursor...

When using FORWARD_ONLY, data could be visible depending on what row the cursor is located at.

However, in your example, due to the filter used:

WHERE T2.KeyField IS NULL -- Filter out any records that have the same value in T2 as T1

Even if new rows are added, the inserted data will be filtered out as non nulls are inserted into Table2.

You could test this adaptation of your query to create an endless looping cursor.

DROP TABLE dbo.Table1;
DROP TABLE dbo.Table2;
CREATE TABLE dbo.Table1(KeyField int,ValueField int);

INSERT INTO dbo.Table1
SELECT TOP(10) ROW_NUMBER() OVER( ORDER BY(SELECT NULL)),
ROW_NUMBER() OVER( ORDER BY(SELECT NULL))
FROM MASTER..spt_values;

CREATE TABLE dbo.Table2(KeyField int,ValueField int)
INSERT INTO dbo.Table2
SELECT TOP(10) ROW_NUMBER() OVER( ORDER BY(SELECT NULL)),
ROW_NUMBER() OVER( ORDER BY(SELECT NULL))
FROM MASTER..spt_values;

DECLARE @KeyField INT, @ValueField INT;
DECLARE @counter int = 0;

DECLARE CursorName CURSOR
FOR SELECT T1.KeyField, T1.ValueField
FROM Table1 AS T1
LEFT JOIN Table2 AS T2
    ON T1.ValueField = T2.ValueField
WHERE T2.KeyField IS NOT  NULL; -- CHANGED TO NOT NULL

OPEN CursorName FETCH NEXT FROM CursorName
INTO @KeyField, @ValueField

WHILE (@@FETCH_STATUS = 0)
BEGIN

    INSERT INTO Table2 (KeyField, ValueField)
    SELECT @KeyField, @ValueField
        SELECT @KeyField, @ValueField
    FETCH NEXT FROM CursorName
    INTO @KeyField, @ValueField
    SET @counter += 1
END

CLOSE CursorName
DEALLOCATE CursorName;

SELECT @counter;


Ouch!  I have never seen ON ( SELECT ... ) = ....  Rewrite that somehow.

Perhaps more importantly, remove LEFT.  You are probably forcing the query start with node, even though it would be much faster to start with a subquery.

  sid != old_sid

Which column is in which table?  Please use short aliases for all references.

Please provide SHOW CREATE TABLE so we can check that you have adequate indexes.

Please provide EXPLAIN SELECT ... so we can see what the optimizer decided to do.


Last value in two levels of group by in MySQL

SELECT DISTINCT
    CONCAT(YEAR(`date`),'/',WEEK(`date`)) as `week`,
    fruit,
    FIRST_VALUE(init) OVER (PARTITION BY fruit,`week`
                            ORDER BY `date` ASC) AS 'first',
    FIRST_VALUE(end) OVER (PARTITION BY fruit,`week`
                           ORDER BY `date` DESC) AS 'last'
FROM fruits_flow


A "query" one or more "plans", linked by sys.query_store_plan.  Each "plan" has multiple rows in sys.query_store_runtime_stats.  If you turn on Profiler while running the built-in Query Store reports you can see lots of examples.  EG
exec sp_executesql N'SELECT TOP (@results_row_count)
    p.query_id query_id,
    q.object_id object_id,
    ISNULL(OBJECT_NAME(q.object_id),'''') object_name,
    qt.query_sql_text query_sql_text,
    ROUND(CONVERT(float, SUM(rs.avg_duration*rs.count_executions))*0.001,2) total_duration,
    SUM(rs.count_executions) count_executions,
    COUNT(distinct p.plan_id) num_plans
FROM sys.query_store_runtime_stats rs
    JOIN sys.query_store_plan p ON p.plan_id = rs.plan_id
    JOIN sys.query_store_query q ON q.query_id = p.query_id
    JOIN sys.query_store_query_text qt ON q.query_text_id = qt.query_text_id
WHERE NOT (rs.first_execution_time > @interval_end_time OR rs.last_execution_time < @interval_start_time)
GROUP BY p.query_id, qt.query_sql_text, q.object_id
HAVING COUNT(distinct p.plan_id) >= 1
ORDER BY total_duration DESC',N'@results_row_count int,@interval_start_time datetimeoffset(7),@interval_end_time datetimeoffset(7)',@results_row_count=25,@interval_start_time='2021-04-16 10:13:43.0578184 -05:00',@interval_end_time='2021-04-16 11:13:43.0578184 -05:00'


Where can I find a list of CheckIDs for sp_Blitz?

Sure, it's in Markdown here: https://github.com/BrentOzarULTD/SQL-Server-First-Responder-Kit/blob/master/Documentation/sp_Blitz_Checks_by_Priority.md

If you find any inconsistencies with it, check out the Contributor Guide for how to open issues in the GitHub repo, discuss the changes you want to make, and check in changes.


Duplicate primary key in bulk insert after truncate in SQL Server 2008?

If you've truncated the table, then a primary key violation must be coming from duplicate data in the file. Try bulk inserting into a new table, without the PK constraint, and then check the table for duplicates (probably easier than writing some tool or script to parse the file directly). You can create a mimic table that won't have constraints this way:

SELECT * INTO dbo.new_bulk_source
  FROM dbo.old_source
  WHERE 1 = 0;

Then change your package to reference this table, do the insert, then run:

SELECT key FROM dbo.new_bulk_source
  GROUP BY key
  HAVING COUNT(*) > 1;

I bet a donut the call is coming from inside the house (or the truncate is not succeeding).


Assuming DateTimeValue is a DATETIME column or similar then both '2016-01-01 00:00:000' and '2016-01-03 00:00:000' are going to get cast to that type and the comparison is done using the natural date comparison routine. Of course if you have something odd like dates stored as strings then yes it may perform a string comparison.

Internally DATETIME is essentially a large integer type (it is actually implemented as two integers but for the purpose of this discussion that difference is purely academic) so the natural comparison is integer comparison.

The conversion of your input strings to DATETIME is not going to take significant resources: it will be done once for the whole query and not for every row that gets considered.

If you have a relevant index for DateTimeValue then a ranged query will be used on that index (a seek to 2016-01-01 or the first value above that then a scan down the index until the values seen stop being less than 2016-01-03. This should be fairly efficient though you will of course have heap/cluster lookups going on if that index isn't your clustered one or covers all the columns. If you look at the query plan for such a query in SSMS you should see that index seek and related row lookups clearly.

So it is an integer comparison, but the other way around to the one you call ridiculous (which you are right, would be silly). There will be a few integer comparisons as it works down the B-Tree of the index to find the first relevant row followed by one comparison per subsequent row as it asks "have we hit 2016-01-03 or higher yet?", not one comparison for every possible value in the range. It might help to think of how you would go about following the instruction "list all the words from your dictionary that are between index and iteration" - that is essentially what SQL Server is doing with the datetime range search.


I have a database design for an E-commerce website. Need to know how to display related product for each specific product

You should create a table to hold the used-by relation between the, in this case, laptop and loader:

create table usedby (
parentid number not null,
childid  number not null);


You have a lot of columns and a high average row length (662).
Maybe you even have rows longer than the block size.

With long rows, it is inevitable to have chained rows. Example with 8K block size:

SQL>  create table t1(c1 char(2000 byte), c2 char(2000 byte), c3 char(2000 byte), c4 char(2000 byte), c5 char(2000 byte));

Table created.

SQL> insert into t1 select 'c1', 'c2', 'c3', 'c4', 'c5' from dual connect by level <= 10000;

10000 rows created.

SQL> commit;

Commit complete.

SQL>  exec dbms_stats.gather_table_stats(user, 'T1');

PL/SQL procedure successfully completed.

SQL> select chain_cnt from user_tables where table_name = 'T1';

 CHAIN_CNT
----------
         0

SQL> analyze table t1 compute statistics;

Table analyzed.

SQL> select chain_cnt from user_tables where table_name = 'T1';

 CHAIN_CNT
----------
     10000

SQL> alter table t1 move;

Table altered.

SQL> analyze table t1 compute statistics;

Table analyzed.

SQL> select chain_cnt from user_tables where table_name = 'T1';

 CHAIN_CNT
----------
     10000

Also make sure you run ANALYZE TABLE, because that is what updates CHAIN_CNT.


SQL Server doesn't have (nor would you want it to have) native file system access built into Transact SQL. However, you can use the CLR to build a function that does specifically what you need to do and then call that CLR function from a T-SQL Stored Procedure.
See the Microsoft docs for CLR User Defined Functions for information on how to go about creating a CLR UDF.
Note that security is going to be an issue and you're going to have to make sure that your UDF isn't able to be exploited. Your SQL Agent service is going to need the necessary permissions to execute your UDF and your DBA may shoot down the idea because (a) they don't want the exposure of giving the SQL Agent service more permissions and (b) they may not like CLR UDFs as a matter of principle.
Depending on how dynamic your user permissions are, you might instead consider creating a scheduled process that reads the user permissions from your folders and records the results in a table that can be read using garden variety T-SQL when necessary.  The scheduled process could be a simple .NET Windows service or maybe a PowerShell script which is independent of your SQL Server, except that it writes results into a table. This lets you isolate the security contexts better and will probably be an easier sell to DBAs and your InfoSec folks.


Rather than focus on the scan, try to replace the cursor with a set-based approach.

It's a bit hard to get into the details without your database schema (because I don't get any intellisense, and don't know anything about the constraints, etc), but I will outline the general approach below.  You would want to test this and make sure it's logically equivalent to your current process.

1 - Get your cursor dataset into temp table:

SELECT
     listCt.id_traitement
    ,listCt.[N° Contrat]
    ,listct.[date_debut_contrat] as dt_effet_ct
    ,listAssu.[N° Assuré]
    ,CASE
        WHEN listCt.is_derniere_annee = 1 AND listCt.[date_fin_contrat] <= GETDATE() THEN listCt.[date_fin_contrat]
        WHEN listCt.Annee < YEAR(GETDATE()) THEN CAST('31/12/' + CAST(listCt.Annee AS VARCHAR) AS DATETIME)
        WHEN listCt.Annee = YEAR(GETDATE()) AND listCt.[date_debut_contrat] > GETDATE() THEN listCt.[date_debut_contrat]
        ELSE GETDATE()
    END AS date_lnk
INTO #listAssu
FROM [app_db_commercial].dbo.tmp_listing_assure AS listAssu
    INNER JOIN [app_db_commercial].[dbo].[listing_contrat_valeur] AS listCt
        ON listAssu.[id_traitement] = listCt.[id_traitement];

2 - Get your nb_adulte counts, grouped by the columns you're using later to update (id_traitement and [N° Assuré]) into a different temp table:

SELECT
    COUNT(ac.[N° Assuré]) as nb_adulte,
    la.id_traitement,
    la.[N° Assuré]
INTO #nb_adulte_counts
FROM PRODBDD01.[app_db_mirroir].dbo.[Assurés - Contrats] AS ac
    INNER JOIN PRODBDD01.[app_db_mirroir].dbo.Assurés_Liens AS al
        ON ac.NoAssureParent = al.[N° Assuré] AND ac.[N° Assuré] = al.No_Assuré_Lien
    INNER JOIN #listAssu la
        ON ac.[N° Contrat] = la.[N° Contrat]
        AND ac.NoAssureParent = la.[N° Assuré]
WHERE
    ac.NoAssureParent <> ac.[N° Assuré]
    AND ac.is_assure_principal = 0
    AND al.Type_Lien_Assuré > 0 AND al.Type_Lien_Assuré < 10 AND al.Type_Lien_Assuré <> 2
    AND la.date_lnk BETWEEN ac.[Date d'entrée] AND ISNULL([Dt_Fin], CAST('99991231' AS DATETIME))
GROUP BY
    la.id_traitement,
    la.[N° Assuré];

3 - Do the same thing with your nb_enfant counts:

SELECT
    COUNT(ac.[N° Assuré]) as nb_enfant,
    la.id_traitement,
    la.[N° Assuré]
INTO #nb_enfant_counts
FROM PRODBDD01.[app_db_mirroir].dbo.[Assurés - Contrats] AS ac
    INNER JOIN PRODBDD01.[app_db_mirroir].dbo.Assurés_Liens AS al
        ON ac.NoAssureParent = al.[N° Assuré] AND ac.[N° Assuré] = al.No_Assuré_Lien
    INNER JOIN #listAssu la
        ON ac.[N° Contrat] = la.[N° Contrat]
        AND ac.NoAssureParent = la.[N° Assuré]
WHERE
    ac.NoAssureParent <> ac.[N° Assuré]
    AND ac.is_assure_principal = 0
    AND al.Type_Lien_Assuré = 2
    AND la.date_lnk BETWEEN ac.[Date d'entrée] AND ISNULL([Dt_Fin], CAST('99991231' AS DATETIME))
GROUP BY
    la.id_traitement,
    la.[N° Assuré];

4 - Then you can run two set-based update statements:

UPDATE la
SET
     nb_benef_adulte = ac.nb_adulte
FROM [app_db_commercial].dbo.tmp_listing_assure la
    INNER JOIN #nb_adulte_counts ac
        ON la.[id_traitement] = ac.[id_traitement]
        AND la.[N° Assuré] = ac.[N° Assuré];

UPDATE la
SET
    nb_benef_enfant = ec.nb_enfant
FROM [app_db_commercial].dbo.tmp_listing_assure la
    INNER JOIN #nb_enfant_counts ec
        ON la.[id_traitement] = ec.[id_traitement]
        AND la.[N° Assuré] = ec.[N° Assuré];

I'm sure some of those steps could be combined, but this is just a quick and dirty example of how to get away from the cursor.  Despite the heavy tempdb workload here, I'd expect it to still run faster than loads of individual seeks across databases / linked servers.


If then else case PL/SQL if text is in list

For starters, you could do the following: stick all list items - together with the group names - into a table (Oracle 12.1):

create table grouplists ( item_, group_ )
as
select '01', 'a' from dual union all
select '02', 'a' from dual union all
select '03', 'a' from dual union all
select '04', 'a' from dual union all
select 'Z7', 'b' from dual union all
select 'Z2', 'b' from dual union all
select '05', 'b' from dual ;

You already have the table containing the samples.  For testing here:

create table samples ( value_ )
as
select '01' from dual union all
select '05' from dual union all
select 'Z7, Z4, 01' from dual union all
select '09, 08' from dual ;

The following function will accept a "sample" ie a comma-separated value, which gets split up into "items".  Each item is used in a SELECT ... INTO ... statement, picking up a "count" from the GROUPLISTS table.  The function returns a string, containing the group membership ie (group a, group b, mixed, or none).
The only aspect that is (maybe) not straightforward (in this function's code) is the recursive SELECT ... CONNECT BY.  There are examples of this on SO eg this one.

create or replace function findgroup (
  sample varchar2
) return varchar2
is
  ila pls_integer := 0 ; -- increase if item is found in list a
  ilb pls_integer := 0 ; -- increase if item is found in list b
  ccount pls_integer := 0 ;  -- intermediate count
  retval varchar2( 4000 ) := '' ;
begin
  for s in (
    select unique -- split the sample into items
       trim( regexp_substr( sample, '[^,]+', 1, level ) ) item_
    from ( select sample from dual )
    connect by instr( sample, ',', 1, level - 1 ) > 0
  ) loop
--
    select count(*) into ccount
    from grouplists where item_ = s.item_ and group_ = 'a' ;
    ila := ila + ccount ;
--
    select count(*) into ccount
    from grouplists where item_ = s.item_ and group_ = 'b' ;
    ilb := ilb + ccount ;
--
  end loop;
  retval := case
    when ila > 0 and ilb > 0 then 'mixed'
    when ila > 0 and ilb = 0 then 'group a'
    when ilb > 0 and ila = 0 then 'group b'
    else 'none'
  end ;
  return retval ;
end findgroup ;
/

Testing:

select value_, findgroup( value_ ) from samples ;

-- result
VALUE_      FINDGROUP(VALUE_)
01          a
05          b
Z7, Z4, 01  mixed
09, 08      none

Solution in SQL

Maybe you don't need a function (or the function is not fast enough for you).  Here's another approach, written in SQL.  Ideas: {1} break down the values stored in the SAMPLES table via a view, and {2} use this view in a query that JOINs it to the GROUPLISTS, and applies the CASE expressions. ( You can - of course - also code everything into one query, just write the view's  code instead of the name SAMPLES_EXPANDED - see the "Alternative" query )

{1} view

create or replace view samples_expanded
as
select unique
  value_
, trim( regexp_substr( value_, '[^,]+', 1, level ) ) item_
from samples
connect by instr( value_, ',', 1, level - 1 ) > 0 ;

-- testing the view
SQL> select * from samples_expanded order by value_;
VALUE_      ITEM_
01          01
05          05
09, 08      08
09, 08      09
Z7, Z4, 01  01
Z7, Z4, 01  Z4
Z7, Z4, 01  Z7

7 rows selected.

{2} query

select
  value_
, case
    when sum( in_a ) > 0 and sum( in_b ) > 0 then 'mixed'
    when sum( in_a ) > 0 and sum( in_b ) = 0 then 'group a'
    when sum( in_a ) = 0 and sum( in_b ) > 0 then 'group b'
    else 'none'
  end as groupmembership
from (
  select value_, E.item_, group_
  , case when group_ = 'a' then 1 else 0 end in_a
  , case when group_ = 'b' then 1 else 0 end in_b
  from samples_expanded E
    left join grouplists G on E.item_ = G.item_
)
group by value_
order by value_
;

-- result
VALUE_      GROUPMEMBERSHIP
01          group a
05          group b
09, 08      none
Z7, Z4, 01  mixed

Alternative ( query with inline view )

select
  value_
, case
    when sum( in_a ) > 0 and sum( in_b ) > 0 then 'mixed'
    when sum( in_a ) > 0 and sum( in_b ) = 0 then 'group a'
    when sum( in_a ) = 0 and sum( in_b ) > 0 then 'group b'
    else 'none'
  end as groupmembership
from (
  select value_, E.item_, group_
  , case when group_ = 'a' then 1 else 0 end in_a
  , case when group_ = 'b' then 1 else 0 end in_b
  from (
    select unique
      value_
    , trim( regexp_substr( value_, '[^,]+', 1, level ) ) item_
    from samples
    connect by instr( value_, ',', 1, level - 1 ) > 0
  ) E
    left join grouplists G on E.item_ = G.item_
)
group by value_
order by value_
;

Dbfiddle here.


I would take the approach for diagram 1. When approaching people in a database, the patients are essentially objects. They don't log in, they only have data. A doctor (or any employee) would be able to log in as users, so would have fields in their table like a login_id, password, and such. A point of confusion may come up when you have a doctor that is also a patient, but then they would have an employee_id\user_id AND a separate patient_id.

As a side note, I would move the connection for medications to be attached to the treatment actions. That would associate a drug with a prescription event, which would be connected to a patient and doctor. Drug prescriptions are events in and of themselves, not necessarily a long-term association to just a patient.


time difference between two machines makes problem in service broker communication

SSL requires that the time on the client and server be reasonably close.  See What role does clock synchronization play in SSL communcation.

At first glance, it would seem that your servers are in different time zones, so configuring them to be in the correct time zone should resolve the issue.


SQL Server sync databases between enterprise - web edition

What Neil has suggested is a viable option, but it is geared more towards Cloud environments and will be more complex than what you are trying to achieve.

OPTION 1 :

The simplest solution would be Transactional Replication (if you want near to real time data) or snapshot replication depending on your NEED.

You can use Replication from Enterprise Edison to Web edition. Important to note that web edition does not support PULL Subscription. It only supports PUSH Subscription.

Depending on your business needs, you can schedule to sync every 1 hr or 1 day.

Replication support matrix is here

OPTION 2

Use 3rd party applicaitons like Redgate schema and data compare for syncing data between 2 databases. It also has command line options, so you can write up your own custom scripts that will periodically sync data. You can get started here

SQL Server also has an inbuilt data sync functionality which is commandline called "TableDiff".

As you see, there are many options, which one will suffice your need depends on your Business requirements.


Why is performance non-linear if I do many UPDATEs in a single transaction

(Note: I point out that this question is unpractical. So, it is completely inappropriate in order to evaluate the performance of PostgreSQL.)

I guess it is caused by the MVCC mechanism of PostgreSQL.

As well known, PostgreSQL's MVCC is implemented in over-writing mechanism. I'll show a concrete example using the pageinspector extention bundled in contrib subdirectory.

First, I start a transaction and do the first UPDATE statement:

BEGIN;

SELECT lp as tuple, t_xmin, t_xmax, t_field3 as t_cid, t_ctid FROM heap_page_items(get_raw_page('foo', 0));
 tuple | t_xmin | t_xmax | t_cid | t_ctid
-------+--------+--------+-------+--------
     1 |   2755 |      0 |     0 | (0,1)
(1 row)

UPDATE foo SET count=count+1 WHERE id=1;

SELECT lp as tuple, t_xmin, t_xmax, t_field3 as t_cid, t_ctid FROM heap_page_items(get_raw_page('foo', 0));
 tuple | t_xmin | t_xmax | t_cid | t_ctid
-------+--------+--------+-------+--------
     1 |   2755 |   2756 |     0 | (0,2)
     2 |   2756 |      0 |     0 | (0,2)
(2 rows)

When updating the data,  PostgreSQL reads and updates the fields of the first tuple header (t_xmax and t_ctid),  and then inserts the new (second) tuple.

Next, I do the second UPDATE statement:

UPDATE foo SET count=count+1 WHERE id=1;

SELECT lp as tuple, t_xmin, t_xmax, t_field3 as t_cid, t_ctid FROM heap_page_items(get_raw_page('foo', 0));
 tuple | t_xmin | t_xmax | t_cid | t_ctid
-------+--------+--------+-------+--------
     1 |   2755 |   2756 |     0 | (0,2)
     2 |   2756 |   2756 |     0 | (0,3)
     3 |   2756 |      0 |     1 | (0,3)
(3 rows)

After reading the first tuple, PostgreSQL reads the second tuple since the t_ctid field of the first tuple points to the second tuple (0,2). And then, PostgreSQL updates the fields of the second one and inserts the third one.

In this way, when many UPDATE statements are issued in a single transaction,  PostgreSQL must read and update the header fields of old tuples whenever inserting a new tuple.

This is my hypothesis. An weakness of this hypothesis is that the processing time order is O(n^2), so this may be wrong (it seem that the result of that benchmark doesn't fit with O(n^2)).

In any case, doing many UPDATE statements in single transaction is not a good way because it makes many dead pages that contain only  dead tuples, so you have to do VACUUM FULL (not VACUUM).


Spread SQL Server data files across devices to improve performance?

All performance solutions are based on measurements. As the theory goes, SQL Server requires the transactions to flush to durable storage (disk) before acknowledging commit (WAL). This makes the log write throughput critical for high write loads. Separating the LDF file on a separate physical disk allows the log writes to achieve serial streaming access to disk, which is the fastest way a spinning disk can write (~100-150Mb/s). Having the MDFs on the same physical disk introduces random IO patterns that dramatically reduce the throughput (~10-40Mb/s). This is the background of the separate LDF /MDF disk recommendations. The point is, obviously, mute on SSD storage.

However, to improve the server performance you need to identify the bottleneck and address it appropriately. If your bottleneck is poor indexing, lock contention, CPU, small buffer pool or any other of many more possible causes then separating the LDF and MDF will help nothing. So you should first find the bottlenecks, and I recommend following the Waits and Queues methodology. Once you have measurements that prove a bottleneck that causes performance degradation then and only then you can act appropriately.


It is possible to use databases without knowing anything about FOREIGN KEYs and not using them at all.  I recommend you start that way.
A FOREIGN KEY does three things:

It is a constraint.  It will slap your hand if you try to link from one table to another without there being an appropriate row.  Think of it like filling out a form that calls for your home address, but you mis-type it and there is no house at the address you typed.  If you never make typos, you don't need that check.
It provides an index (aka "key") to make the above check.  But you can create indexes as needed without having FKs.  And if your tables are only a few hundreds of rows each, you might not notice that you are missing the significant performance benefits of indexes.  An index is like a phonebook.  (If you are too young to know what a phonebook is, visit a museum.)
It can "cascade" things.  Don't use that; just do it yourself.  I'm referring to the deletion of a row in one table automatically causing the deletion of a corresponding row in another table.  Analogy:  When a person dies, the DMV, the voter registration, the bank, etc, need to be notified manually so they can delete that person from their lists.  FKs facilitate having that happen automatically.

Since you mentioned a bridging table, I'll ramble on in that direction.  (It sounds like you may have already mastered it.)  There are 3 types of "relations" between "entities":

1:1 -- Don't use that.  Why bother having two tables that march in lock step when one would do.  (There exceptions; but that comes in another lecture.)

1:many -- This is simply implemented with a link ("id") in one table to allow "JOINing" to another table.  Many rows of the first table link to a single row in the second table.

many:many -- This has many names, "bridging" on one of them.  In the college example:  students:classes, classes:teachers, etc.  Bridging requires an extra table.  See this for the optimal indexes:  http://mysql.rjweb.org/doc.php/index_cookbook_mysql#many_to_many_mapping_table

Any of those mappings can degenerate to 1 or to 0.
Rambling on...
SELECT ... FROM tableA JOIN tableB ON ...

delivers rows that show up in both tables (based on the ON condition).
SELECT ... FROM tableA LEFT JOIN tableB ON ...

delivers all the rows of table A, whether are not there is a matching row in table B.  If none exists, then the columns that should have come from tableB will be NULLs.
The ON says how the tables are 'related'.
The WHERE clause, if present, filters out some of the rows.


Since you provide no common field to join on, I'm going to assume you want a  cross join, where every customer will be linked to every product.

SELECT     CUSTOMER_REGISTRATION.SURNAME, CUSTOMER_REGISTRATION.OTHER_NAME,
           CUSTOMER_REGISTRATION.ADDRESS, RECIEVABLE.DATE,  RECIEVABLE.PHONE#,
           RECIEVABLE.SERIAL#, RECIEVABLE.CHEQUE, PRODUCT, MAKE, MODEL
FROM        CUSTOMER_REGISTRATION
INNER JOIN  RECIEVABLE ON CUSTOMER_REGISTRATION.PHONE# = RECIEVABLE.PHONE#
CROSS JOIN  PRODUCTV


Innobackupex apply-log should be done before emergency restore or after each backup?

If you apply the redo log right after a backup copy is taken you can greatly reduce time to restore.

Sometimes --apply-log step fails. Due to a bug or tablespace corruption. In either case you don't want to learn that in the emergency. That's one more argument to do the --apply-log after the backup.

People don't apply the redo log because it brings in more complexity to the backup process. For example, you cannot stream and apply the log.  If you take incremental backups the --apply-log makes it even more complex and you loose any intermediate recovery points (at best you'll have a state as of full backup time and the most current state. It may be OK though).


Do shorter names exist for "one-to-many", "one-to-one", "many-to-many" relationships?

As far as verbal communication, there are none that I'm aware of. There are symbols for writing but those wouldn't be of any help for verbal communication.


sql expiry date use Trigger OR Schedule a Job

something like below should get you started ...

CREATE TABLE [dbo].[patientInfo]
(
id int,
patientid int,
  starttime datetime,
  endtime datetime,
  doctorid int
)
GO

ALTER TABLE patientInfo ADD
    expire AS CASE WHEN endtime < GETDATE()THEN 1 ELSE 0 END
GO

    declare @now datetime=getdate()

INSERT into patientInfo (id, patientid, starttime, endtime, doctorid)
SELECT 1,1,@now,@now-1,1
UNION ALL SELECT 2,2,@now,@now+1,2
UNION ALL SELECT 3,3,@now, @now+2,3
UNION ALL SELECT 4,4,@now, @now-2,4

SQLFiddle here


Removing WALS when replication slot is not busy

One query in case of asynchronous replication with replication slots and no wal archiving if a slave node remains disconnected from master for more than wal_keep_segments, it will not be able to do the replication when it comes online.

This is not the case if the slot is being used correctly.  The whole point of a replication slot is to retain the WAL even when the replica is disconnected.  The setting of wal_keep_segments provides a floor, not a ceiling, to WAL retention (wal_keep_segments is also pretty much obsolete now that slots are a mature technology).

  Now my question is when the slave connects to the master again will the XLOG in master also keep on building as the slaves replication is stuck or postgres will remove the replication slot if it is not busy / active?

PostgreSQL will not automatically remove the slot, you need to do it manually using pg_drop_replication_slot.  However, unless you did something incorrectly (or ran into a bug), you should never be in this situation.  If the replica was correctly using the slot before it got disconnected, then the WAL should still be there when it reconnects.


SQL server migration to new server from 2008 R2 to SQL 2014

You can run upgrade advisor 2014 on a backup of your current to-be-migrated database which in your case is sql server 2008R2.

I have written detailed steps on migration here.

PowerShell based start-sqlmigration is what I highly recommend (I am using it quiet often to migrate server instances to new server) when you have to migrate your entire server instance.

e.g.

  Start-SqlMigration -Source sql2008R2 -Destination sql2014 -BackupRestore -NetworkShare \nas\sql\migration

Make sure that the sql server service account has proper permissions on the NetworkShare


Should I create a Factless Fact Table or Denormalise my table

Merge them.  Sometimes it's worthwhile to have a separate Addresses table, but generally it's simplest to make those fields of a single Customers dimension.  Because addresses are nearly unique, you're not going to save many records by hiving them out to their own table.

If your Customers table is really large, and addresses change relatively often then moving Addresses out as a Type-4 SCD could be a worthwhile optimization, but a small one.

If there's interest in analyzing customers independently of their specific sales (e.g., "20% of our top-100 customers are international"), you can absolutely add a NumCustomers dummy column to this Customers table to turn it into a factless fact table.


"No database selected" or "Table 'DB.table' doesn't exist" failures in pt-upgrade run where queries from multiple DBs were collected with tcpdump

Does it still fail to work if you omit --sample?
(from manual)

  Also note that pt-query-digest may fail to report the database for
  queries when parsing tcpdump output. The database is discovered only
  in the initial connect events for a new client or when  is
  executed. If the tcpdump output contains neither of these, then
  pt-query-digest cannot discover the database.

Consider using the general log instead of tcpdump?


How to check if a client can use windows authentication

You want to check AUTHENTICATION_TYPE in the DBA_USERS view. Users who are authenticated externally (EXTERNAL in the view), are authenticated by the operating system. A user cannot be both internally and externally authenticated, or be altered to switch authentication methods once created. If you haven't deliberately created externally authenticated users, then you won't have a problem.
See here: https://docs.oracle.com/en/database/oracle/oracle-database/18/ntqrf/overview-of-manually-administering-external-users-and-roles.html#GUID-8A209E30-4BA9-4993-80F5-AF0A82ADEB5B


Oracle 11g setup closes without errors on pre-requisite check

I assume you are using the 64 bit version of Oracle to install.

I assume you have Windows 7 x64 - Professional, Enterprise, or Ultimate    edition

In the case of unusual errors it would not be impossible for something silly like the language pack in use on Windows to be the cause. Try starting with a new Windows install that has not been customized or has had any other Oracle products installed or removed

Check the    install logs.  With all the versions and folder name changes I am not  100% sure where but you should look in the c:/Windows/oracle or in  the install folder if located on hard disk


Attach a trigger to 'sys.dm_db_missing_index_details'

No triggers on system DMVs, sorry.

Besides, you wouldn't really want to do this: that DMV is updated every time a query is run with a missing index request. That could be hundreds or thousands of times per second - and performance matters there.

Instead, consider running an Agent job every 5-15 minutes to capture the data you're looking for.


Do SQL service packs effect SQL Compatibility mode?

Most likely the application will work, however, generally when a vendor has this statement, they have not tested their application. They have not certified the application will still work once the SP or CU or version has been applied. Thusly, if you go to an "unsupported" version, they will not support you on any issues.

I would not rely on a vendor's website documentation as the end all statement for supportability. Sometimes, they have not updated their documentation yet. Give them a call to determine what the true support options are, and then get that in writing.


Check if SQL Agent job is being executed manually

Using Kenneth's code, and a few other bits and pieces that I pulled together, I came up with the following code which I think should do the job.  If anybody sees any potential problem with this I'd be interested in knowing!

-- Check if this server is the preferred replica for backups.
    If sys.fn_hadr_backup_is_preferred_replica( @DatabaseName ) <> 1
    BEGIN

        -- Check whether the script is running from SQL Agent.  If it is not then raise an error so that the user knows that the backup hasn't completed.
        -- (If the job is run from the agent then there should be an equivalent job running on the preferred replica, so no need to alert).
        declare @AgentAccount varchar(100)
        set @AgentAccount = (select cast(value_data as varchar(100)) as [AccountName]
                                FROM sys.dm_server_registry
                                WHERE registry_key LIKE '%SQLSERVERAGENT%' --For named instance change to SQLAgent$<instanceName>
                                AND value_name = 'ObjectName'
                            )

        if @AgentAccount = SYSTEM_USER
        begin

            declare @jobSource tinyint
            SELECT  @jobSource = ja.run_requested_source
                    --WHEN 1 THEN 'SOURCE_SCHEDULER'
                    --WHEN 2 THEN 'SOURCE_ALERTER'
                    --WHEN 3 THEN 'SOURCE_BOOT'
                    --WHEN 4 THEN 'SOURCE_USER'  -- This one is manual
                    --WHEN 6 THEN 'SOURCE_ON_IDLE_SCHEDULE'
                    --ELSE 'UNKNOWN' END
            FROM msdb.dbo.sysjobactivity ja
            WHERE ja.session_id = @@SPID
            AND start_execution_date is not null
            AND stop_execution_date is null;

            if @jobSource = 4   -- Job was run manually.
            begin
                RAISERROR ('No backup produced as this server is not the preferred replica for backups.  Please run the script again from the preferred replica.', 16, 1)
                return 1
            end
            else
            begin
                print 'No backup produced as this server is not the preferred replica for backups.'
                return 0
            end
        end
        else
        begin
            RAISERROR ('No backup produced as this server is not the preferred replica for backups.  Please run the script again from the preferred replica.', 16, 1)
            return 1
        end
    END