Postgresql equivalent to sqlite pragma What would be the equivalent of the sqlite pragma below if I want to get the best performance out of postgresql.

pragma synchronous = OFF;
pragma journal_mode = OFF;
pragma count_changes = OFF;
pragma temp_store = MEMORY;

Most of what these pragmas do for SQLite can be best accomplished by using an UNLOGGED table or a TEMPORARY table.

This also makes it very obvious that these are ephemeral tables, that aren't crash or restart safe. It also highlights that there are very real tradeoffs for blazing speed versus data durability.

Going off of the PRAGMA documentation for SQLite, I've attempted to translate them into equivalent PostgreSQL GUC variables.

pragma synchronous = OFF; is roughly equvalent to setting synchronous_commit off. If you're using a TEMPORARY or UNLOGGED table, this setting will have no real effect because those types of tables aren't actually written into the WAL anyway.

pragma journal_mode = OFF; shuts off the WAL, in PostgreSQL you can only do that with UNLOGGED or TEMPORARY tables.

pragma temp_store = MEMORY; temporary tables in PostgreSQL can write to disk, but setting temp_buffers should let you keep more of the tables in memory for better performance.

pragma count_changes = OFF; is deprecated in SQLite, and doesn't really make sense in PostgreSQL.

Robert Haas has a great blog posting about Temporary and Unlogged Tables.

Whatever you do, be sure to not put any of your tablespaces in RAM. The dangers are spelled out in the following two blog posts.

PostgreSQL no tablespaces on ramdisks

Cannot recover from the loss of a tablespace


MS SQL FileTable CreatedBy and LastModifiedBy When using a SQL FileTable table, I would like to store additional metadata for the file such as CreatedBy and LastModifiedBy. By looking at SQL File Table Schema, this information is not available by default.

I am wondering if it's possible to determine such information by for example adding a trigger to the table and use current_user to determine current user and store it in a separate table?

I exploring the feasibility of SQL FileTable for a project and have never used this tech.

I was able to solve the problem by using instead triggers.

First, a FileTable has a fixed schema therefore new fields are not allowed. I've created a separate table to store authoring information in a 1-to-1 relationship keyed by 'path_locator'.

Using triggers, I was able to detect authoring information and store it.

When copying, deleting and renaming files, the authoring information (created by, modified by) can be detected by using suser_sname(). However when editing the document in Word, Excel, Notepad, there are scenarios where the suser_sname() will return 'dbo' instead of the credentials of windows credentials. I am not sure why this happens but I worked around it by joining to sys.dm_filestream_non_transacted_handles to get username from there. See code snippet below:

CREATE OR ALTER TRIGGER Authoring_Update ON dbo.DBADocuments AFTER UPDATE AS
BEGIN
    IF @@ROWCOUNT = 0 RETURN;

    UPDATE dbo.FileMetadataTable SET ModifiedBy = ISNULL(s.login_name, suser_sname())
    FROM dbo.FileMetadataTable f
    JOIN Inserted i ON i.path_locator = f.PathLocator
    LEFT JOIN sys.dm_filestream_non_transacted_handles s ON f.PathLocator = s.item_id
    LEFT JOIN dbo.FileTable ft WITH (NOLOCK) ON s.item_id = ft.path_locator
END


Export a subset of data, from mysql production database, to make a test database I have a mysql production database, with multiple tables. Tables are related with foreign key constraints. I want to make a small test database, by collecting/exporting only a small subset of data from the whole database (i.e. from all tables), so that foreign key constraints are preserved in the data subset.

How can I do that?

I doubt that there's a cookie-cutter approach, but the way I did it in the past went roughly as follows:

Pick some critical tables that must be populated for proper testing, preferably which have many dependencies, e.g. OrderDetail.
Create a new database with the same schema except no foreign key enforcement.
Randomly pick some rows from the source database, e.g. top 50 ordered by something pseudo-random, and insert into the new database.
Get all rows that they depend on and insert into the new database.
Repeat until no more rows are inserted.
Turn foreign key enforcement on.

It's slow and has many manual steps, but it worked for me.


Will a static MySQL database become bigger in file size over time, even without anyone writing to it? I backed up a database a few weeks ago, then for comparison downloaded it again. It was about 10,000 kB larger. There should not be anyone writing to it, but there are lots of reads.

Will a static MySQL database become bigger in file size over time, even without anyone writing to it?

Back in August 2014, I answered this question : How to start mysqld with read-only data directory?
I mentioned how you could make the InnoDB Architecture completely read-only.
You can also read MySQL Documentation (Configuring InnoDB for Read-Only Operation)
The only reason ibdata1 would grow would be MVCC (needed to support transaction isolation). I wrote about such uncontrolled growth of ibdata1 in my post How can Innodb ibdata1 file grows by 5X even with innodb_file_per_table set?
Turning its features removes any need to write anything related to MVCC inside ibdata1.


How to estimate rate of Trans Log usage We have a moderately sized database (about 500GB) that we're going to switch from Simple to Full Recovery, with Trans Log backups every 15 minutes. Before we make the switch, I want to get an estimate of how much space those backups will take on average. (Actually, our storage folks want to know so they can carve out that space for me.) So how can we get that estimate?

Looking at how much log space is in use won't do it, since it's just using one area in the log while freeing others. So I'm guessing I would have to look instead at the rate at which the transaction log is being used. The plan is to monitor the VLF usage and track which VLFs are used and then freed over time, along with their size.

That's going to be a little challenging to set up, but it's doable. Before I go down that route, though, I'm hoping there's a better/easier way. Any ideas?

You didn't specify which SQL version you are on but you could use a Server Side trace or an Extended events session to capture the traffic that is sent to the SQL server (if you can live with the performance impact it might have) and replay those captured traces on a test environment to see what the daily growth would be for the day where you ran your traces.

See here how to replay a profiler trace or distributed replay for info on how to run an XEvents replay.

That would allow you to rerun the captured workload on a test system and see for real what is happening with your log file.


Specify the account in domain format Error in SQL Server 2016 installation on Windows 10 I am trying to install SQL Server 2016 on Windows 10 Pro. In the Server Configuration tab I need to provide the account names, the services will be running as. Independent whether I provide virtual or local accounts an error is generated and I cannot proceed:

Comments?

Error message you are getting because you did not provide any service account for the following services. You can see those are blank in the picture you provided.

SQL Server PolyBase Engine.
SQL Server PolyBase Data Movement.


MySQL replication - how to read from slaves? manually? MySQL Manual - Replication:

  "Scale-out solutions - spreading the load among multiple slaves to
  improve performance. In this environment, all writes and updates must
  take place on the master server. Reads, however, may take place on one
  or more slaves. "

I see how to setup replication which looks relatively simple, but I haven't seen how an application should communicate to slaves.

I assume the application would have to determine which slave to read from? The application will also have to know which server is doing writes? Or is it possible to have the application send all queries to the Master and have reads proxied to slaves?

A good solution is to define two datasources for the applications. One datasource pointing to master is for writing. One datasouce pointing to slave node is for reading. The applications must be aware that  data can be delayed with respect to the master. There are applications that could tolerate a slight delay on data, and other applications that must be sure that read data are fresh. For example data that are updated infrequently can be always read from slave like bills of the previous month. Data that have just been inserted must be read from the master.

A Virtual IP is assigned to the master. Another vip is assigned to the slave. The applications connect to the master and slave using vip. So if master node changes, the vip can be assigned to the new master, and the application don't have to be reconfigured. Using vips is usefull if slave is promoted to master, and master demoted to slave.

If you have more than one slave, a load balancer (aka haproxy) could be put before slaves. The vip for the slave can be assigned to the load balancer. When a connection toward slave is opened the load balancer hijacks the connection toward one of slaves.


How to copy Azure SQL database to on-premises SQL Server daily for reporting? I have a database as a service SQL instance in Azure, I want to copy this database to a reporting server located in a virtual machine, and this is not SQL as a service, so my COPY statement won't work, this is what I have:

I'm tryin to copy from dbserver-prod to the server marked in the red rectangle

This is the statement I've used to copy from SQL AAS instances and has been working fine but now I get an error: :

CREATE DATABASE Database_Reporting AS COPY OF [dbserver-prod].[database];

My aim is to have prod database copied to this server once a day for reporting. Any thoughts on how this can be done will be very helpful.

You can't copy direct from AzureSQL to on-prem SQL (even on-prem SQL running in an Azure VM) that way.

To copy the database manually you need to export it via SSMS (or the relevant command line tool) or the Azure portal, transfer the resulting bacpak file to the target server and import it there using SSMS or the relevant command-line tools. If this is to be a regular thing (weekly or daily to provide a regularly updated reporting copy?) then you'll want to try automate that via powershell script run from task scheduler or similar.

Is there a reason that you want your extra copy in an on-prem SQL Server install instead of also being in AzureSQL? If not then you might consider changing that as CREATE AS COPY OF is likely to be significantly quicker than export+transfer+import.

Another option after creating your initial copy is to use SSIS to update it if it is easy to work out (from audit trail records, change tracking, etc.) what data has changed since the last update. This may be far more efficient as you only need to transfer new/changed data, though it relies on you keeping the database schema on the on-prem side identical to the production schema in AzureSQL. You may also wish to look into Azure Data Sync (currently in preview): https://docs.microsoft.com/en-us/azure/sql-database/sql-database-sync-data


Loading the databases from previous installation mysql My Windows OS crashed today. Now I have recovered all the files in Program files/MySQL folder.(I don't have the exported .sql file)

With the new installation of MySQL 5.6(same as before), can I get those databases back(Or how to load the data to new installation of MySQL)? Please suggest.

Based on your question, I will assume you know the old data root in Program Files MySQL. If you simply copy the entire thing into the data root of your new MySQL 5.6, and then restart, you should be good to go. The .myi .myd and .frm files are the key parts as I understand it.

If you are unsure where the new data root is, check your new installation's my.ini file.


Does large VARCHAR take up more room in the transaction log On SQL Server, would VARCHAR(500) take up more space in the transaction log than VARCHAR(100) when all the rows have no more than 100 characters?

In the case of a small insert, there is no difference in size of transaction log generated.

Here's a demo script to prove it (SQL Server 2016):

--setup
USE master
GO
CREATE DATABASE LogTest
GO
ALTER DATABASE LogTest
SET RECOVERY SIMPLE
GO
USE LogTest
GO

CREATE TABLE dbo.v100 (junk VARCHAR(100) )

CREATE TABLE dbo.v8000 (junk VARCHAR(8000) )
GO

--initial inserts have extra log activity from initializing the table
INSERT dbo.v100
VALUES ('z')

INSERT dbo.v8000
VALUES ('z')

-----------------------------------------------------------------
--ok, let's test this
CHECKPOINT --checkpoint clears log in SIMPLE recovery, which helps limit it to what we want to see
GO

BEGIN TRAN
INSERT dbo.v100
VALUES ('a')
ROLLBACK

SELECT Operation, Context, [Log Record Length], [Log Reserve]
FROM sys.fn_dblog(NULL,NULL)
WHERE Operation NOT LIKE '%CKPT'

CHECKPOINT
GO

BEGIN TRAN
INSERT dbo.v8000
VALUES ('a')
ROLLBACK

SELECT Operation, Context, [Log Record Length], [Log Reserve]
FROM sys.fn_dblog(NULL,NULL)
WHERE Operation NOT LIKE '%CKPT'


Understanding when to remove Order By or Sort operator from Query/plan While i was reading and per understanding one needs to avoid unwanted ORDER BY in SQL queries if matching index to support query has key columns sorted the same way.

For below DB test schema--

CREATE PARTITION FUNCTION DemoPartitionFunction (datetime)
AS RANGE RIGHT
FOR VALUES (DATEADD(dd, DATEDIFF(dd, 0, GETUTCDATE()), -7),
            DATEADD(dd, DATEDIFF(dd, 0, GETUTCDATE()), -6),
            DATEADD(dd, DATEDIFF(dd, 0, GETUTCDATE()), -5),
            DATEADD(dd, DATEDIFF(dd, 0, GETUTCDATE()), -4),
            DATEADD(dd, DATEDIFF(dd, 0, GETUTCDATE()), -3),
            DATEADD(dd, DATEDIFF(dd, 0, GETUTCDATE()), -2),
            DATEADD(dd, DATEDIFF(dd, 0, GETUTCDATE()), -1),
            DATEADD(dd, DATEDIFF(dd, 0, GETUTCDATE()), 0),
            DATEADD(dd, DATEDIFF(dd, 0, GETUTCDATE()), 1),
            DATEADD(dd, DATEDIFF(dd, 0, GETUTCDATE()), 2),
            DATEADD(dd, DATEDIFF(dd, 0, GETUTCDATE()), 3),
            DATEADD(dd, DATEDIFF(dd, 0, GETUTCDATE()), 4),
            DATEADD(dd, DATEDIFF(dd, 0, GETUTCDATE()), 5),
            DATEADD(dd, DATEDIFF(dd, 0, GETUTCDATE()), 6),
            DATEADD(dd, DATEDIFF(dd, 0, GETUTCDATE()), 7));

CREATE PARTITION SCHEME DemoPartitionScheme
AS PARTITION DemoPartitionFunction
ALL TO ([DEFAULT]);

CREATE TABLE [dbo].[DemoPartitionedTable](
    [DemoID] [int] IDENTITY(1,1) NOT NULL,
    [SomeData] [sysname] NOT NULL,
    [Lastseen] [datetime] NULL,
    [DataKey1] [char] NOT NULL,
    [DataKey2] [char] NOT NULL,
    [RandomColumn] [nvarchar] NOT NULL,
    [CaptureDate] [datetime] NULL,
    CONSTRAINT [PK_DemoPartitionedTable] UNIQUE NONCLUSTERED
    (
        [DemoID] ASC,
        [CaptureDate] ASC
    )
    ON DemoPartitionScheme(CaptureDate)
) ON DemoPartitionScheme(CaptureDate);

If i run the Query with ORDER BY

SELECT [DemoID], [CaptureDate]
FROM [dbo].[DemoPartitionedTable]
WHERE CaptureDate>=CONVERT(datetime, '20190912', 112) AND
CaptureDate < CONVERT(datetime, '20191013', 112)
ORDER BY
DemoID,
CaptureDate

Below are the stats from IO and Time

  (95703 rows affected) Table 'Worktable'. Scan count 0, logical reads
  0, physical reads 0, read-ahead reads 117, lob logical reads 0, lob
  physical reads 0, lob read-ahead reads 0. Table
  'DemoPartitionedTable'. Scan count 16, logical reads 330, physical
  reads 9, read-ahead reads 262, lob logical reads 0, lob physical reads
  0, lob read-ahead reads 0.

  (1 row affected)

  SQL Server Execution Times:    CPU time = 297 ms,  elapsed time = 750
  ms.

As per my understanding in above scenario we do not need order by as index have those columns already sorted, so why do i see a bad plan for below

so if i run removing the sorting above

SELECT [DemoID], [CaptureDate]
FROM
[dbo].[DemoPartitionedTable]
WHERE CaptureDate>=CONVERT(datetime, '20190912', 112) AND
CaptureDate < CONVERT(datetime, '20191013', 112)
--ORDER BY
--DemoID,
--CaptureDate

  (95703 rows affected) Table 'DemoPartitionedTable'. Scan count 16,
  logical reads 330, physical reads 134, read-ahead reads 269, lob
  logical reads 0, lob physical reads 0, lob read-ahead reads 0.

  (1 row affected)

  SQL Server Execution Times:    CPU time = 78 ms,  elapsed time = 2791
  ms. SQL Server parse and compile time:     CPU time = 0 ms, elapsed
  time = 0 ms.

Plan does not have an expensive sort but stats got worst, why?

As per my understanding in above scenario we do not need order by as index have those columns already sorted

That understanding is incorrect, and that plan shows one reason why.  A parallel index scan doesn't output rows in index order, as each thread reads at a different location in the sort order.  You can't expect rows in any particular order without an ORDER BY clause.

And the index does not have the rows already sorted.  The ORDER BY is on (DemoID, CaptureDate), but the index is partitioned by CaptureDate.  So if the query crosses a partition bondary, the DemoID values will start over.

eg the rows in (DemoID, CaptureDate)-order:

(1,'20190912'),(2,'20190913'),(3,'20190912'),(4,'20190913')

may be stored over more than one partition:

partition N
--------------
(1,'20190912')
(3,'20190912')

partition N+1
--------------
(2,'20190913')
(4,'20190913')

So even if the plan used a single thread to scan the index, a downstream sort would be required.

  Plan does not have an expensive sort but stats got worst, why?

No. The stats got better.  The second query has the same 330 logical reads and only 78ms of CPU time vs 297ms of CPU time for the first query.  The difference in elapsed time is related to more parallelism and the physical IO, which is not an attribute of the query plan.  Rather it's dependent on the state of the page cache when you run the query.


Creating a index on a date column that's already populated Is there any downside to creating a index on a already populated date field?

Will the new index perform the same as it would if it was created when the table was?

Would it be costly from a database perspective to add this?

Edit : the table has just under 10 million entries.

There is no downside in creating an index at a later moment. The index will perform the same way like it would when it was created when the table was created.

You mentioned that the table has already 10 million rows. Therefore I would suggest that you create it during a 'calm' period. This because Oracle needs to lock the table. This cannot be done if there is a transaction that does an insert, update, or delete in the table.


Drop Oracle data file with 0 bytes and in offline (needs recovery) state What can I do to drop a data file mistakenly created and currently with 0 byte and in offline (needs recovery) state?

You can not drop the datafile. You can move it to a better location and keep it there. If you want to get rid if it, re-create the tablespace and copy the data to the new tablespace. Drop the old tablespace and rename the new tablespace. Don't forget to give quota to your schemas similar as they have on the old tablespace.


Oracle Migration Upgrade 11g to 12c New Server I'm fairly new to Oracle databases and have been tasked with assessing the implications of upgrading our application's database from 11g to 12c.

I have managed a successful in-place upgrade but a number of stakeholders will be looking to upgrade to new hardware so I need to access the viability of a migration upgrade.

Is it possible to migrate the current active database to a fresh installation of 12c and which would be the most effective method for this?

You can use expdp and impdp utility by Oracle for migration of database from 11g to 12c.

First of all we will use expdp utility to export the whole database into a dumpfile (.dmp) by issuing the following command:

expdp system/password@db11g full=Y directory=TEST_DIR dumpfile=DB11G.dmp logfile=expdpDB11G.log

Here, FULL parameter indicates that the whole database will be exported
      Directory --> The directory on server in which you want to place the dumpfile
      DUMPFILE --> The name of dumpfile
      LOGFILE --> The name of logfile (that logs the status of export process)

After the export is done successfully you will get something like:
Job "SYSTEM"."SYS_EXPORT_FULL_01" successfully completed at 10:35

Now, your whole database is stored in a dumfile(.dmp) in the 'DIRECTORY' mentioned in the expdp command.

We will now start import of the database into 12c database with the help of impdp utility.

impdp system/password@db10g full=Y directory=TEST_DIR dumpfile=DB11G.dmp logfile=impdpDB12c.log

*Before starting import you might need to transfer the dumpfile to the new server (i.e. 12c)

After import has been successfully completed you will get successfull completion message.

Reference link --> https://oracle-base.com/articles/10g/oracle-data-pump-10g

Happy Migration !!


Archivelog redundancy I need to enable archivelog. But sometimes database can't connect archivelog destination (NFS disk) and shutdowns. I can set two archivelog destinations and one of them is not NFS disk (local disk).

How can I configure, if database can't connect to NFS disk (dest1), to just write on local disk (dest2) and continue to work?

Thank you.

You can use ALTERNATE attribute in second archive log destination.

  ALTERNATE

  Specifies an alternate archiving destination to be used when the original destination fails.

Example:

LOG_ARCHIVE_DEST_1='LOCATION=/disk1 MANDATORY ALTERNATE=LOG_ARCHIVE_DEST_2'
LOG_ARCHIVE_DEST_STATE_1=ENABLE
LOG_ARCHIVE_DEST_2='LOCATION=/disk2 MANDATORY'
LOG_ARCHIVE_DEST_STATE_2=ALTERNATE

Reference:
LOG_ARCHIVE_DEST_n Parameter Attributes


Calculation is not correct during in the left join table At the moment My head is bang on the wall as I have used few of dba.stackexchanged.com example but none of them is working.

AS I'm trying to get SKU that it sold on each Country, as I have 5 country table that it sold.

When I get results and it seems working fine, but the calculation seems wrong

One of the SKU in UK "H-ROOT-M012R-B" in return at QTY_sold_in_uk is '15552' which this calculation are wrong, the correct one is '8' which QTY has sold not '15552'

Here is a code I wrote this https://www.db-fiddle.com/f/u7PxDXy8kzSHV5n8VHC3rY/1

but I keep getting when I use this
https://www.db-fiddle.com/f/qENxYhrqhMHvUMFzYoK4ho/3

When I decided to look around and explore in the StackOverflow and I have used this link pages but results of numbers is keeping return same as the wrong one

multiple sum query with joins

second link multiple sum query with joins

MySQL Count rows from another table for each record in table

I have used inner, left and right join or union seems that calculation is never right.
Here is DB Fiddle I has worked on it
Example code

I know this code is a long one but please bear with me,

    SELECT
    ti.sku,
    ti.quantity,
    -- UK
    COALESCE((suo_uk.sku), 'Not Sold') AS Unit_in_uk,
    COALESCE(sum(suo_uk.units_ordered), 0) AS QTY_sold_in_uk,

    -- De
    COALESCE((suo_de.sku), 'Not Sold') AS Unit_in_de,
    COALESCE(sum(suo_de.units_ordered), 0) AS QTY_sold_in_de,

    -- Fr
    COALESCE((suo_fr.sku), 'Not Sold') AS Unit_in_fr,
    COALESCE(sum(suo_fr.units_ordered), 0) AS QTY_sold_in_fr,

    -- ES
    COALESCE((suo_es.sku), 'Not Sold') AS Unit_in_es,
    COALESCE(sum(suo_es.units_ordered), 0) AS QTY_sold_in_es,

    -- It
    COALESCE((suo_it.sku), 'Not Sold') AS Unit_in_it,
    COALESCE(sum(suo_it.units_ordered), 0) AS QTY_sold_in_it
FROM
    tbl_inventory AS ti
        LEFT JOIN
    tbl_sku_units_order AS suo_uk ON suo_uk.sku = ti.sku
        LEFT JOIN
    tbl_sku_units_order_de AS suo_de ON suo_de.sku = ti.sku
        LEFT JOIN
    tbl_sku_units_order_fr AS suo_fr ON suo_fr.sku = ti.sku
        LEFT JOIN
    tbl_sku_units_order_es AS suo_es ON suo_es.sku = ti.sku
        LEFT JOIN
    tbl_sku_units_order_it AS suo_it ON suo_it.sku = ti.sku

-- where ti.sku=" H-ROOT-M012R-B"
GROUP BY ti.sku

Please, Advice and your advice would be greatly appreciated.

"Explode-Implode" syndrome causes the inflated SUM.

The JOINs are done.  This gives an intermediate table with lots of rows.
SUM() and other aggregates are performed.
The GROUP BY shrinks it back down to a small number of rows.

Where practical, this is a convenient solution; note that there is no JOIN or GROUP BY:

SELECT
        ( SELECT SUM(...) ... ) AS sum_1,
        ( SELECT SUM(...) ... ) AS sum_2
    FROM ...

But that won't work here.  Instead, let's compute the aggregates in "derived" subqueries:

SELECT COALESCE(uk.qty, 0) AS QTY_sold_in_uk,
       COALESCE(de.qty, 0) AS QTY_sold_in_de,
       ...
    FROM  tbl_inventory AS ti
    LEFT JOIN ( SELECT sku, SUM(qty) AS qty ) AS uk  USING(sku)
    LEFT JOIN ( SELECT sku, SUM(qty) AS qty ) AS de  USING(sku)

This way, the aggregation will be confined to the the one table.  Also, the LEFT allows for an item not to be sold in some country, then the COALESCE deals with the NULL.  Again, no GROUP BY.

This pattern begs for at least 5.6 because the derived tables have no index.  However 5.6 will dynamically build one.

Final form

SELECT
    ti.sku,
    ti.quantity,
    -- UK
    COALESCE(suo_uk.sku, 'Not Sold') AS Unit_sold_in_uk,
    COALESCE(suo_uk.units_ordered, 0) AS QTY_in_uk,
    -- De
    COALESCE(suo_de.sku, 'Not Sold') AS Unit_sold_in_de,
    COALESCE(suo_de.units_ordered, 0) AS QTY_in_de,
    -- Fr
    COALESCE(suo_fr.sku, 'Not Sold') AS Unit_sold_in_fr,
    COALESCE(suo_fr.units_ordered, 0) AS QTY_in_fr,
    -- ES
    COALESCE(suo_es.sku, 'Not Sold') AS Unit_sold_in_es,
    COALESCE(suo_es.units_ordered, 0) AS QTY_in_es,
    -- It
    COALESCE(suo_it.sku, 'Not Sold') AS Unit_sold_in_it,
    COALESCE(suo_it.units_ordered, 0) AS QTY_in_it
FROM
    tbl_inventory AS ti
    LEFT JOIN ( SELECT sku, sum(units_ordered) as units_ordered
                     FROM tbl_sku_units_order
                     GROUP BY sku ) AS suo_uk
                             ON suo_uk.sku = ti.sku
    LEFT JOIN ( SELECT sku, sum(units_ordered) as units_ordered
                     FROM tbl_sku_units_order_de
                     GROUP BY sku ) AS suo_de
                             ON suo_de.sku = ti.sku
    LEFT JOIN ( SELECT sku, sum(units_ordered) as units_ordered
                     FROM tbl_sku_units_order_es
                     GROUP BY sku ) AS suo_es
                             ON suo_es.sku = ti.sku
    LEFT JOIN ( SELECT sku, sum(units_ordered) as units_ordered
                     FROM tbl_sku_units_order_fr
                     GROUP BY sku ) AS suo_fr
                             ON suo_fr.sku = ti.sku
    LEFT JOIN ( SELECT sku, sum(units_ordered) as units_ordered
                     FROM tbl_sku_units_order_it
                     GROUP BY sku ) AS suo_it
                             ON suo_it.sku = ti.sku
    GROUP BY ti.sku


Login problem to SQL Server I attempt to connect to SQL Server 2008,I receive the following error.

  Login failed for user <>. Reason: Failed to open the explicitly specified database.

By the way, I have a SQL Server 2008 R2 on Windows Server 2008 64-Bit Enterprise Edition.

Thanks,

This means you have logged in correctly to SQL Server, but the database in the connection string (or default database from CREATE LOGIN) is one of

not there
unavailable (eg autoclosed or recovering)
no login-user mapping (eg CREATE USER as not been run)


Functional dependency not detected for some columns Here's a simple example schema:

CREATE TABLE parents (
  parent_id INT,
  type text null,
  PRIMARY KEY (parent_id)
);

CREATE TABLE children (
  child_id INT,
  parent_id INT,
  name text,
  PRIMARY KEY (child_id)
);

INSERT INTO parents (parent_id, type) VALUES (1, null);
INSERT INTO children (child_id, parent_id, name) VALUES (1, 1, 'foo');

This query works:

SELECT child_id
FROM children
JOIN parents USING (parent_id)
GROUP BY child_id, parent_id

Fiddle

If I change that to SELECT child_id, name it still works, as it should because name is functionally dependent on child_id and child_id is in the GROUP BY clause.

Now if I change it to SELECT child_id, type I think it should still work, because type is functionally dependent on parent_id and parent_id is in the GROUP BY clause. But instead I get an error message:

  column "parents.type" must appear in the GROUP BY clause or be used in an aggregate function

What's the difference? Does it have anything to do with the JOIN?

I found out what is the difference.

When I write

SELECT child_id, type
FROM children
JOIN parents USING (parent_id)
GROUP BY child_id, parent_id

Postgres seems to assume I mean GROUP BY child_id, children.parent_id and even though those two are guaranteed to be identical by the JOIN, they don't seem to count as functionally dependent. If instead I write

SELECT child_id, type
FROM children
JOIN parents USING (parent_id)
GROUP BY child_id, parents.parent_id

Postgres recognizes the functional dependency and allows type to be in the SELECT expression.


SQL Server Msg 2812, level 16 state 62 line 30 I want to make automatic backup and restore my database on SQL Server 2012. I have made automatic backup with script in my Windows server.

Now, I want to make auto restore with script. I have found this below script to do that:

USE Master;
GO
SET NOCOUNT ON

-- 1 - Variable declaration
DECLARE @dbName sysname
DECLARE @backupPath NVARCHAR(500)
DECLARE @cmd NVARCHAR(500)
DECLARE @fileList TABLE (backupFile NVARCHAR(255))
DECLARE @lastFullBackup NVARCHAR(500)
DECLARE @lastDiffBackup NVARCHAR(500)
DECLARE @backupFile NVARCHAR(500)

-- 2 - Initialize variables
SET @dbName = 'Customer'
SET @backupPath = 'D:\SQLBackups\'

-- 3 - get list of files
SET @cmd = 'DIR /b ' + @backupPath

INSERT INTO @fileList(backupFile)
EXEC master.sys.xp_cmdshell @cmd

-- 4 - Find latest full backup
SELECT @lastFullBackup = MAX(backupFile)
FROM @fileList
WHERE backupFile LIKE '%.BAK'
   AND backupFile LIKE @dbName + '%'

SET @cmd = 'RESTORE DATABASE ' + @dbName + ' FROM DISK = '''
       + @backupPath + @lastFullBackup + ''' WITH NORECOVERY, REPLACE'
PRINT @cmd

-- 4 - Find latest diff backup
SELECT @lastDiffBackup = MAX(backupFile)
FROM @fileList
WHERE backupFile LIKE '%.DIF'
   AND backupFile LIKE @dbName + '%'
   AND backupFile > @lastFullBackup

-- check to make sure there is a diff backup
IF @lastDiffBackup IS NOT NULL
BEGIN
   SET @cmd = 'RESTORE DATABASE ' + @dbName + ' FROM DISK = '''
       + @backupPath + @lastDiffBackup + ''' WITH NORECOVERY'
   PRINT @cmd
   SET @lastFullBackup = @lastDiffBackup
END

-- 5 - check for log backups
DECLARE backupFiles CURSOR FOR
   SELECT backupFile
   FROM @fileList
   WHERE backupFile LIKE '%.TRN'
   AND backupFile LIKE @dbName + '%'
   AND backupFile > @lastFullBackup

OPEN backupFiles

-- Loop through all the files for the database
FETCH NEXT FROM backupFiles INTO @backupFile

WHILE @@FETCH_STATUS = 0
BEGIN
   SET @cmd = 'RESTORE LOG ' + @dbName + ' FROM DISK = '''
       + @backupPath + @backupFile + ''' WITH NORECOVERY'
   PRINT @cmd
   FETCH NEXT FROM backupFiles INTO @backupFile
END

CLOSE backupFiles
DEALLOCATE backupFiles

-- 6 - put database in a useable state
SET @cmd = 'RESTORE DATABASE ' + @dbName + ' WITH RECOVERY'
PRINT @cmd

The output shows me correct SQL command if you keep PRINT in this query,
but when I change PRINT to EXEC in restore log, it shows me an error:

  Msg 2812, Level 16, State 62, Line 64
  Could not find stored procedure 'RESTORE LOG yogamanual FROM DISK = 'D:\shared241\yogamanual-0954.trn' WITH NORECOVERY'.
  Msg 2812, Level 16, State 62, Line 64
  Could not find stored procedure 'RESTORE LOG yogamanual FROM DISK = 'D:\shared241\yogamanual-1002.trn' WITH NORECOVERY'.

Am I wrong? Can you guys help me to fix it? I have searched it in Google, but nothing can solve it.

It would need to be EXEC(@cmd) not EXEC @cmd.

Without the parentheses it looks to find a stored procedure with the same name as the string inside @cmd.

Hence the error you are seeing of

  Could not find stored procedure 'RESTORE LOG yogamanual FROM DISK =
  'D:\shared241\yogamanual-0954.trn' WITH NORECOVERY'.


SQL: Select all items and add info from left join where condition is met Background

I am creating a news site. Table NEWS has all news with unique ID news_uid.

TABLE NEWS
--------------
news_uid | ...

If a user has read a certain news, that info gets stored in table NEWSREAD:

TABLE NEWSREAD
--------------
user_uid | news_uid | news_read
-------------------------------
A        | N1       | 1
A        | N2       | 1
B        | N3       | 1

So here user A has read News 1 and 2 and user B has read N3.

Question

I am trying to create a SQL query that

Selects all news from the news table
And tells me for a given user, which news have been read

Output:

news_uid | news read by user A
------------------------------
N1       | 1
N2       | 1
N3       | 0 (or NULL)

I have gotten so far, but this does not show N3 in the results.

SELECT NEWS.news_uid, NEWSREAD.news_read FROM NEWS
LEFT JOIN NEWSREAD ON NEWS.uid = NEWSREAD.news_uid
WHERE NEWSREAD.user_uid = 'A' or NEWSREAD.user_uid IS NULL

SELECT NEWS.news_uid, NEWSREAD.news_read
FROM NEWS
LEFT JOIN NEWSREAD ON NEWS.uid = NEWSREAD.news_uid
                  AND NEWSREAD.user_uid = 'A'


NOT NULL if value is true in another column I have a similar table to this one

create table my_table
(
id serial,
attribute boolean,
number integer
)

Is there a way to have it force not null in column number IF attribute value is true?

So if a record is saved with attribute value 'true' then number must be given a value.

EDIT:
After some digging I have tried this

alter table my_table
add constraint number_must_have_value CHECK (attribute = 't' and number IS NOT NULL)

It throw constraint is violated by some row, but if I run:

select * from my_table where attribute = 't' and number IS NOT NULL

I retrive 0 rows. So my data seem to be OK?

To force it anyway I try to use

constraint number_must_have_value NOT VALID CHECK (attribute = 't' and number IS NOT NULL)

But can't get the NOT VALID option to work. I get a syntax error. Is it not in the right place?

This is a rather simple CHECK constraint:

CREATE TABLE my_table
(
id serial,
attribute boolean,
number integer,
CONSTRAINT if_attribute_then_number_is_not_null
   CHECK ( (NOT attribute) OR (number IS NOT NULL) )
) ;

The logic behind the code is that the logical restriction if a then b is written in boolean logic as (not a) or (b). May seem counter-intuitive at first look but if you write the possible boolean combinations it works out.

The constraint could also be written as CHECK ( NOT (attribute AND number IS NULL) ) which may seem a bit more explanatory ("do not allow attribute to be false and number to be null at the same time"). Pick whatever is more readable to you.


Is my subquery inneficient. It is extremely slow I have two tables. Member information and login entries. I am simply trying to display the member information table sorted by the last login access date.  So i created this query, but it is extremely slow. Is there a better way to write it?

SELECT
    menmembers.memb_id,
    menmembers.firstname,
    menmembers.lastname,
    (SELECT Max(trank.datetr) AS MaxOfdatetr
        FROM trank
        where trank.cod_usr=menmembers.memb_id;) AS LastLogin
FROM menmembers;

Your query is slow because of the sub-select.  It is what is known as a correlated sub query.  It will be executed once per row in the outer query.

This question answers a similar problem.  It's looking for the minimum, you for the maximum; it's using one table, you're using one.  The translation shouldn't be too hard, though.


LDF files vs. TRN files vs. Tempdb.MDF+LDF files.: We have a dilemma…
We have all of our MDF and LDF files in the same…\192.168.6.22\d$\Program Files\Microsoft SQL Server\DATA\Production...folder.
We have all of our TRN and BAK files on the…\192.168.6.22\r$...drive.
We have read several articles stating that our Tempdb.MDF+LDF files need to be relocated to a different drive.
We have read several articles stating that our Userdb.LDF files also need to be relocated to a different drive.

http://www.brentozar.com/archive/2009/02/when-should-you-put-data-and-logs-on-the-same-drive/
http://www.tech-recipes.com/rx/19178/sql-server-20082008-r2-move-tempdb-to-improve-performance/

This system is in a virtual environment, so it’s more than likely all of these drives are all on the same array anyway, and if that’s the case would relocation make any difference?
If they are on separate arrays, or if it will make a difference, then should there be four separate locations or five, and do each of these need their own array?.:
a.    One for the MDF files; (1)
b.    One for the LDF files; (2)
c.    One for the BAK and TRN files; (3)
d.    One for the Tempdb.MDF+LDF files; (4) or…
…One for the Tempdb.MDF file, and another for the Tempdb.LDF file; (5)

A few notes here:

You definetely have to separate the files logically using different partitions (I recommend separate partitions for: system, data files (mdf/ndf), transaction log files (ldf) and tempdb files (mdf/ndf). Where you put the tempdb transaction log file is up to you (tempdb drive or log drive), I'd go with log drive.
Backup files should not be on the located same device and especially the same VM (I recommend a different SAN/NAS).
Placing your data, log and tempdb files on different drives is mostly a way of improving performance. Transaction log is heavily written and is probably the slowest component of SQL Server. The second bottleneck is by design the tempdb database. If these 2 components are placed on different drives (especially if they reside on SSD/RAID10 disks), the performance will be definitely better.
You can keep the transaction log and tempdb on the same array as the user database files if you aren’t experiencing performance problems (at least not yet). I would look at the sys.dm_io_virtual_file_stats DMV to verify if there really is a problem with IO performance.
If you want to move these files to a different disk in a virtual environment you can create a separate virtual disk for every partition you want to move and assign these disks to different LUNs in the array.


SQL Server 2017 Backup strategy RTO is 8 hours
RPO is 24 hours
Retention Period: 5 days
Based on above values I have plan to configure below backup stratgey for Production. Please suggest if there is any changes required in below backup policy.
Full Backup - Daily
Log Backup - every 30 minutes (this is just to prevent log grwoth/low disk problems during index rebuild job)
SQL DB size is approx 500-650 GB.
HA/DR solution for above DB: Configured Distributed Always-ON AG from DC1 to DC2 on top of WSFC (DC1- two DB serevrs and DC2-two DB servers)

To start with I do not see any technical issue in your current approach. If I may suggest and if it suits your requirement, since your RPO is 24 hours keep DB in simple recovery take daily full backup and every 8 hours diff backup(optional). The diff backup is not even required but just to give you some restore point in between. Now store these backups locally and also on different server in other DC.
Copy these backup files on different SQL Server in other DC and restore it. So now you have always have a complete data which is less than 24 hours new in other DC. This will meet your RPO but yes you should test this to see if that meets your RTO of 8 hours or not.
The advantage with this approach is its very simple configuration, saves licensing cost of few Windows server. You dont have to worry about complexity of AG, even a new DBA and junior DBA's can work on it easily, etc etc...


How much memory is a table taking up? Is there a way to find out how much memory a table is taking up in SQL Server (2005 and greater)?

For example, suppose I have a table with 30 million records. I'd like to know how many pages belonging to this table are currently on the buffer cache, including index, data and text/image pages.

I found this query by Pinal Dave, but it seems this query only returns pages allocated by indexes (be it clustered or unclustered ones).

with bd as (
    select count(*) as pages_in_memory, bd.allocation_unit_id
    from sys.dm_os_buffer_descriptors bd
    where bd.database_id = db_id()
    group by bd.allocation_unit_id)
select p.object_id,
    p.index_id,
    p.partition_number,
    bd.pages_in_memory,
    au.total_pages as pages_on_disk,
    au.type_desc
from bd
join sys.allocation_units au
    on au.allocation_unit_id = bd.allocation_unit_id
join sys.partitions p
    on p.partition_id = au.container_id


MySQL server upgrade, how to adapt variables? I am about to upgrade my MySQL server because I have currently used it mainly with a couple of people in the development stage. Before we launch, however, we would like more capacity to avoid the server crashing. I currently run on a Ubuntu system with 2 GB of RAM, 2 vCPU's, 60 GB disk and 3 TB of monthly transfer.

My variables are set to:

innodb_buffer_pool_size = 256M
innodb_io_capacity      = 1900
innodb_flush_method     = O_DIRECT
key_buffer_size         = 10M
max_allowed_packet      = 32M
thread_stack            = 192K
thread_cache_size       = 100
myisam-recover-options  = BACKUP
query_cache_limit       = 10M
query_cache_size        = 0
slow_query_log          = 1
slow_query_log_file     = /var/log/mysql/mysql-slow.log
long_query_time         = 2
wait_timeout            = 300
interactive_timeout     = 300

I am about to ugprade my server to 8 GB RAM, 4 vCPU's, 160 GB of disk space and 5 TB of monthly transfer. The size of my table is currently 1.3 MB, but this will of course increase as soon as more users become active. How much this will increase and how fast, I can not really say for sure.

So I am now trying to figure out how I should change my variables to get the best performance. I know I should allocate my memory correctly, and these settings work fine for the setup I currently have, but if I need to change anything for the upgraded system, I would really appreciate some insights.

Cheers!

If your dataset size is only 1,3MB, then buffer_pool_size = 256M gives you a lot of  growing room.

If HDD, change innodb_io_capacity back to the default of 200.

The slowlog (which you have already turned on) will provide info on what to fix if you have performance problems.

Has the server crashed?  If so, let's see details on that.

Do you have other apps crammed into the tiny 2GB of RAM?  Changing to 8GB is likely to help with that.


Ideas for intro SQL presentation / training At my work, I've been asked to teach a short half-hour to an hour course about "select and joins". I'm also thinking about discussing unions, sub queries and temp tables in this course. Are there any core concepts that relate to the topic of "select and joins" that I could include to expand the length of said course?

Sure, start with my Learn SQL with the Stack Overflow Database.

It's designed for you to demo with the Stack Overflow public data dump. (I just updated it today, coincidentally.)


What is the difference between Used and Allocated space? I am learning Database operations and I have found a couple of resources online that have given me a loose understanding, but I still couldn't explain the difference myself. Explain like I am five please. I want to make sure that I have a solid foundation to expand my knowledge upon.

Think of your database like a bucket.

The ALLOCATED space is how big the bucket is, i.e. how much data it can hold.

The USED space is how much water/data is actually inside the bucket.

When the water gets to the top of the bucket, you need to make the bucket bigger by allocating more space.


What is "CSS operation: data query" wait event in an Oracle StatsPack report? Has anyone seen the CSS operation: data query Oracle wait event and know what it means? There was a bug 8299200 in 11.2.0.1 which is fixed in 11.2.0.3.

STATSPACK report for

Database    DB Id    Instance     Inst Num  Startup Time   Release     RAC
~~~~~~~~ ----------- ------------ -------- --------------- ----------- ---
          3816595344 SCRATCHEDOUT        1 04-Aug-15 14:16 11.2.0.3.0  YES

Host                             Name             Platform                CPUs
Cores Sockets   Memory (G)
~~~~ ---------------- ---------------------- ----- ----- ------- ------------
     scratchedout     Solaris[tm] OE (64-bit    48    24       6        192.0

Snapshot       Snap Id     Snap Time      Sessions Curs/Sess Comment
~~~~~~~~    ---------- ------------------ -------- --------- ------------------
Begin Snap:        237 10-Aug-15 09:00:01       42       1.1
  End Snap:        239 10-Aug-15 09:53:54       43       1.0
   Elapsed:      53.88 (mins) Av Act Sess:       0.1
   DB time:       5.45 (mins)      DB CPU:       2.46 (mins)

Cache Sizes            Begin        End
~~~~~~~~~~~       ---------- ----------
    Buffer Cache:     5,792M     5,696M   Std Block Size:         8K
     Shared Pool:     1,168M     1,264M       Log Buffer:    11,592K

Load Profile              Per Second    Per Transaction    Per Exec    Per Call
~~~~~~~~~~~~      ------------------  ----------------- ----------- -----------
      DB time(s):                0.1                0.1        0.00        0.03
       DB CPU(s):                0.1                0.0        0.00        0.01
       Redo size:          354,689.1          341,689.5
   Logical reads:            1,333.7            1,284.8
   Block changes:              158.3              152.5
  Physical reads:               33.7               32.5
 Physical writes:               46.4               44.7
      User calls:                3.6                3.5
          Parses:               13.5               13.0
     Hard parses:                2.0                1.9
W/A MB processed:                0.8                0.7
          Logons:                0.5                0.5
        Executes:               46.3               44.6
       Rollbacks:                0.0                0.0
    Transactions:                1.0

Instance Efficiency Indicators
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
            Buffer Nowait %:   99.97       Redo NoWait %:   99.99
            Buffer  Hit   %:   98.37  Optimal W/A Exec %:   99.96
            Library Hit   %:   93.86        Soft Parse %:   85.14
         Execute to Parse %:   70.93         Latch Hit %:   99.78
Parse CPU to Parse Elapsd %:   37.01     % Non-Parse CPU:   96.21

 Shared Pool Statistics        Begin   End
                               ------  ------
             Memory Usage %:   84.22   41.85
    % SQL with executions>1:   50.76   68.25
  % Memory for SQL w/exec>1:   75.54   68.25

Top 5 Timed Events                                                    Avg %Total
~~~~~~~~~~~~~~~~~~                                                   wait   Call
Event                                            Waits    Time (s)   (ms)   Time
----------------------------------------- ------------ ----------- ------ ------
CPU time                                                       597          40.9
CSS operation: data query                        1,820         337    185   23.1
DFS lock handle                                 32,161          52      2    3.6
log file parallel write                         16,038          51      3    3.5
CSS initialization                                  21          30   1428    2.1
          -------------------------------------------------------------
...

The note you provided is regarding issues with shutdown: Bug 8299200 - Shutdown immediate hangs (Doc ID 8299200.8). Is it the correct doc you wanted to refer?

Have you recently upgraded to 11.2.0.3?

If so, there's this MOSC note that might be helpful, referring to a bug that causes Clusterware Synchronization Services operations to be very slow, leading to the css-related wait events:

Doc ID 1444483.1


What is best practice for confirming success of Update queries If I am doing a SQL Update query that affects, for instance, 1000 rows, how do I check that the query worked as intended and didn't hit any strange edge cases, (short of examining each row visually myself)?
What is best practice for this ?

Additional info requested in comments
I was writing a SQL query to update any fields in a column that were blank with a standard value. That by itself is pretty easy and the number of fields were small enough that I could check them visually. But I figured there could be much more complex queries covering larger numbers of fields where examining wasn't feasible.

You could query before and after looking for the particular conditions you are wanting to update.

For adhoc queries it's a good idea to run the query as a select statement using the same where clause before running as an update statement.

The update should update exactly the same rows select returned unless the data has changed.

create table #test ([id] int identity (1,1), [status] varchar(50) )

insert into #test VALUES ('COMPLETED')
insert into #test VALUES ('FAULTED')
insert into #test VALUES ('COMPLETED')
insert into #test VALUES ('FAULTED')
insert into #test VALUES ('FAULTED')

select status
from #test
where status = 'FAULTED'

select count(*)
from #test
where status = 'FAULTED'

update #test
set status = 'FAILED'
where status = 'FAULTED'

select count(*)
from #test
where status = 'FAULTED'

select status, count(*)
from #test
group by status


The index columns in sys.index_columns different from what are in the index When I run the following I get 3 indexed columns:

SELECT b.name,
       c.name
FROM sys.index_columns a
INNER JOIN sys.indexes b ON a.object_id = b.object_id
AND a.index_id = b.index_id
INNER JOIN sys.columns c ON b.object_id = c.object_id
AND a.column_id = c.column_id
WHERE b.object_id = OBJECT_ID('dbo.MyTableIssue')
  AND b.name = 'IX_MyTableIssue_ColId_Col2Id'

Returns:

IX_MyTableIssue_ColId_Col2Id    ColId
IX_MyTableIssue_ColId_Col2Id    Col2Id
IX_MyTableIssue_ColId_Col2Id    ColDate

But when I do a right click, script index as then create to new query window I get the following:

CREATE NONCLUSTERED INDEX [IX_MyTableIssue_ColId_Col2Id] ON [dbo].[MyTableIssue]
(
    [ColId] ASC,
    [Col2Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
GO

I'm running SQL Sever 2008.  Any advice or direction is greatly appreciated.

If a non-clustered index is created on a partitioned table, that index will be partition-aligned by default unless you explicitly specify that it should not be (e.g., use ON [PRIMARY] to specify the file group).

In such a case, the SSMS script index functionality will not show the partition scheme used, but you can use sp_help on the table to confirm that the non-clustered index is partitioned.

If you expand your query to check all columns on sys.index_columns, you will see that the partition_ordinal column is set to 1 for ColDate, indicating that is a partitioning column, and key_ordinal is set to 0, indicating that it is "Not a key column".

Here is a full repro with a dummy table:

-- Create dummy partition function and scheme
CREATE PARTITION FUNCTION pf_test (INT)
AS RANGE RIGHT
FOR VALUES (1,2)
GO
-- Create dummy partition function and scheme
CREATE PARTITION SCHEME ps_test
AS PARTITION pf_test_transactionId
ALL TO ( [PRIMARY] )
GO

-- Create dummy partitioned table
CREATE TABLE dbo.testPartitionedTable (
    id INT NOT NULL,
    a INT NOT NULL,
    b INT NOT NULL,
    c INT NOT NULL,
    CONSTRAINT PK_testPartitionedTable PRIMARY KEY (a, id) ON ps_test(id)
)
-- Create the non-clustered index
CREATE INDEX IX_testPartitionedTable ON dbo.testPartitionedTable (b)
GO
-- Run your query for inspecting the index columns
SELECT b.name,
       c.name,
       a.*
FROM sys.index_columns a
INNER JOIN sys.indexes b ON a.object_id = b.object_id
AND a.index_id = b.index_id
INNER JOIN sys.columns c ON b.object_id = c.object_id
AND a.column_id = c.column_id
WHERE b.object_id = OBJECT_ID('dbo.testPartitionedTable')
  AND b.name = 'IX_testPartitionedTable'
GO


Max Memory setting AWS EC2 we have move some SQL Server from Physical to AWS EC2 - my question is regarding Max Memory Setting fro SQL Server instance - I used to change the memory setting in Physical server giving some to OS and some to SQL Server but I am not sure for EC2 - do we have to use the same logic like 10-12% to OS and rest to SQL server in AWS Ec2  or we can keep it default .

If there is an OS you should leave some RAM for it.
EC2 is just a VM, so whatever you usually do for VM also applies for EC2.


pgBouncer works great but occasionally becomes unavailable I'm running pgBouncer in front of a busy postgres 9 database.
For most of the time it works fine. But every few hours I'll get an error email from my application with an exception from psycopg2:

OperationalError('could not connect to server: Cannot assign requested address
Is the server running on host "neo-hulk" and accepting
TCP/IP connections on port 6432?')

This is a python app with a bunch of celery workers running tasks. When those errors arrive I check the pgbouncer db and the pool size is within limits.
After some experimentation I've set the pool max size to 400 and pool size to 200. pool mode is "session" (requests are mostly auto-commit, almost no transactions).

What makes pgBouncer 'vanish' like that? its only for short periods of time (and in total we're talking about a tiny amount of requests compared to the sheer volume of requests its handing) but those requests that fail are important.

Thanks!

The "Cannot assign requested address" part in the error message comes from the kernel TCP stack. When encountered intermittently, this typically means that the space of available sockets is exhausted because of too much sockets in wait state (TIME_WAIT, or less probably FIN_WAIT_1 or FIN_WAIT_2)

The range of socket ports can be output by cat /proc/sys/net/ipv4/ip_local_port_range. The default value on a stock Linux kernel is generally 32768 61000.

You may check the result of netstat -ton|grep WAIT on the client(s) and on the pgBouncer's host when the system is busy. The -o flag will show the timeout
counters related to wait states.

If the total number of TCP sockets is close to 61000-32768=28232 then exhaustion of this range is likely your problem. Since a closed socket spends 60 seconds in TIME_WAIT state in normal condition, if  a client host connects more than 28232 times in one minute, new connections will fail with the mentioned error until ports are freed.

As a first workaround, the TCP ports range may be extended:

 # echo "1025 65535" >/proc/sys/net/ipv4/ip_local_port_range

If it's not satisfactory, check the tcp_tw_recycle and tcp_tw_reuse flags, also tunable through /proc/sys/net/ipv4 and sysctl.

They're defined as (from man tcp):

       tcp_tw_recycle (Boolean; default: disabled; since Linux 2.4)
              Enable  fast  recycling  of  TIME_WAIT  sockets.   Enabling this
              option is not recommended since this causes problems when  work‐
              ing with NAT (Network Address Translation).

       tcp_tw_reuse (Boolean; default: disabled; since Linux 2.4.19/2.6)
              Allow  to reuse TIME_WAIT sockets for new connections when it is
              safe from protocol viewpoint.  It should not be changed  without
              advice/request of technical experts.

Personally I had success with tcp_tw_recycle when faced with this problem with a MySQL client app, but don't take this as a recommendation, my understanding of TCP being superficial at best.


Size limit of character varying Postgresql What is the size limit of various data types in postgresql? I saw somewhere that for character varying(n), varchar(n) n must be between 1 to 10485760. Is that true?

What are the valid sizes for character(n), char(n) and text?

The maximum size of limited character types (e.g. varchar(n)) in Postgres is 10485760. You can check this in that way:

create table test(id serial primary key, str varchar(10485761));

ERROR:  length for type varchar cannot exceed 10485760

The limit is defined in the following fragment of source code (htup_details.h), however is not explicitly mentioned in the official documentation:

/*
 * MaxAttrSize is a somewhat arbitrary upper limit on the declared size of
 * data fields of char(n) and similar types.  It need not have anything
 * directly to do with the *actual* upper limit of varlena values, which
 * is currently 1Gb (see TOAST structures in postgres.h).  I've set it
 * at 10Mb which seems like a reasonable number --- tgl 8/6/00.
 */
#define MaxAttrSize     (10 * 1024 * 1024)

The maximum number of characters for variable unlimited length types (text, varchar) is undefined. There is a limit of size in bytes for all string types:

  In any case, the longest possible character string that can be stored is about 1 GB.


Does MySQL InnoDB table perform faster when data is removed I have a 5GB InnoDB table in MySQL. I updated several rows where a column had massive amounts of data to be blank, hoping to make the table smaller and perform quicker.

I then discovered that InnoDB doesn't reduce disk space when you remove data from it.

https://stackoverflow.com/questions/1270944/mysql-innodb-not-releasing-disk-space-after-deleting-data-rows-from-table?rq=1

However, there is less data to search through now, so I'd like to assume it will still perform better, especially when performing a LIKE query.

I tried to do some benchmark tests, but very unfortunately did not take accurate before measurements.

Will my InnoDB table perform faster now that I've removed data from the table, or does it still have to search through the same amount of disk and there will be no difference.

InnoDB may not release the diskspace but your query on your table is actually hitting less data. The size of your actual table has been reduced, but InnoDB will keep the storage for later growth on that table (or any other table in that database).

Your InnoDB table may or may not perform better depending on what percentage of total rows you purged from your table. If you removed 5% of total rows, for example, you probably won't see noticable speedup (up to 5%). If you removed 90%, you'll see it speedup quite a bit.

Some other approaches to increasing performance is writing a better query or indexing the table such that lookups are faster.


Transactional Replication guidance As a real newcomer to Transactional Replication I would appreciate some guidance.

I have a production database on premise and the need to take two tables from here and copy them regularly to a SQL Azure database. This is so that our suppliers can run a very simple label printing program from their premises so they can stick labels on the goods they supply to us.

We raise a Purchase Order in house on our ERP system and send it to our supplier. They produce the goods and print the labels. Stick the labels on the goods and send them to us.

That is it. Nothing fancy in the way of filters or otherwise (This can all be handled by the program).

Is this a fairly easy thing to set up? Is there an idiots guide anywhere that could show me how to go about this?

There are many ways  by which you can transfer data and all depends on this part of question..

  copy them regularly to a SQL Azure database

1.If you want once a day,you can write ssis package and do the logic of checking/merging data in that

2.You also can write C# program which does this for two tables and run this through SQLServer agent..

3.if you are looking for near realtime data transfer,i would go with Transactional replication.This is supported now..

Setting up is very easy task and same as Onpremises ,but with one exception of no pull subsribers .

This article from Joe Sack will help you on how to set up..

There are also some limitations which you need to account for..

The publisher and distributor must be at least at one of the following versions:

SQL Server 2016

SQL Server 2014 SP1 CU3

SQL Server 2014 RTM CU10

SQL Server 2012 SP2 CU8

You can read this link for more details..


Multidimensional indices in databases Which databases use real multidimensional indices? Is oracle ever using several indices to get data from tables, or will it always take the one that seems to have the highest selectivity? How about other dbms?

Oracle has an index type called a Bitmap Index which it describes as...

  A database index in which the database
  stores a bitmap for each index key
  instead of a list of rowids.

If a table has a bitmap index or a hint is used, it can use a bitmap access plan on regular B-tree indexes.  Bitmap indexes can be joined, union-ed, and intersected.

There is an excellent explanation at use-the-index-luke.com where it includes the following implementations of combining multiple B-Tree indexes:

  DB2:
  DB2 supports multiple index access on
  LUW 9r7 (using a dynamic bitmap) and
  on zOS v10.

  MySQL:
  MySQL has an index merge optimization
  starting with release 5.0.

  Oracle
  The Oracle database uses BITMAP
  CONVERSIONs to combine multiple indexes on the fly (introduced with
  9i).

  PostgreSQL
  PostgreSQL uses bitmaps to combine multiple indexes since version 8.1.

  SQL Server
  SQL Server can use multiple indexes ("Index Intersect") starting with V7.0 using a hash algorithm.

See also this StackOverflow question in which the first answer says that SQL Server does something similar to Bitmapped indexes using index intersection.

Efficient and Flexible Bitmap Indexing for Complex Similarity Queries is the closest reference I have found associating Bitmapped indexes with the word multidimensional.  Multidimensional seems to be more of a way to use indexes rather than an attribute of them.


Is it a good idea to create an index just for new data to increase index creation performance? I have a table with about 350 M rows with 45 GB, it has around 10 columns ,simplified version below

CREATE TABLE PAYMENT (
    ID NUMERIC(20) PRIMARY KEY,
    CREATION TIMESTAMP,
    VALUE DECIMAL(10, 2),
    USER_ID NUMERIC(20)
    ....
)

Once known that

Table have a really high concurrency
It receives about 3M INSERTS and 5M UPDATES per day, update uses the PK
By company policies reasons

I must keep old data to grant history
I can't partitioning that table
I'm really going to have this one table with all data and there is not chance to change that

I have to create an index for (CREATION, USER_ID) columns

Is it a good idea to create an index excluding old data to get the index created faster, avoiding table locks and save storage knowing that my queries will just hit the new data?

CREATE INDEX ON PAYMENT(CREATION, USER_ID) WHERE CREATION > TIMESTAMP '2018-03-25 00:00:00';

if you create a partial index then your queries will have also include the condition used to create the index. if they do not the index will be of no use.

when creating the index there will probably need to be a scan of the entire table your only win is a smaller index size.

What index will help queries most depends on what information you want from the table. are you sure that there are enough payments with the exact same creation that including userid as the second term in the index is justified? bear in mind that timestamp is 8 bytes while numeric(20) is 16 bytes, so an index on timestamp only will also be more compact.

An index on payment (userid, creation) will be more useful in the case where you want you find the most recent record, or the payment history, for any particular userid.


Is MySQL more scalable than PostgreSQL due to the difference in how they handle connections? I'm trying to decide if either MySQL or PostgreSQL would be more suitable for an application that will get hit by potentially thousands of simultaneous requests at a time.
During research, one fact that stands out is that PostgreSQL forks a new process for each connection, whereas MySQL creates a new thread to handle each connection.

Does this mean that MySQL is more efficient than PostgreSQL at handling many concurrent connections?

How much of an impact does this difference have on how well both systems scale? Is it something that I should worry about to begin with?

I'll use a dated example. Suppose you have a webserver serving some static pages and php pages with a database backend. Back in the day, the default config was:
1- Web client connects and apache process/thread is created
2- php interpreter is instanciated
3- database connection is opened
4- queries are done, some html is generated while being sent to client at the same time
5- wait for all data to be transferred to client
6- release php interpreter, database connection, and apache process/thread
This didn't work back then, and it still doesn't work now, because step 5 can take a while. Suppose you need 2 milliseconds of SQL queries and 10 milliseconds running php to generate the response, and 500ms for the HTTP transfer to complete.
The database connection is opened and lasts 500ms, using up valuable RAM and resources on the database server for the whole 500ms, but it is only being actually used for 2ms. That's an efficiency of 0.4%. This means: if the database connection was only used to perform the queries during 2ms, then released for other clients to use, it could perform 250 times more queries per second using the same amount of RAM on your database server. This also means the dumb config that doesn't scale will use hundreds of times more RAM precisely on the server where RAM is the most precious resource of all: the database. Which in practice means it will come down in flames.
The webserver process and php interpreter are only doing useful work for 10ms, but they will occupy valuable RAM on the webserver for 500ms. That's an efficiency of 2%. So, for the same reason as above, this setup would need 50x more webserver RAM than the smart setup.
The smart setup is very simple, it's basically a funnel.
On the wide side of the funnel, you have many slow incoming client connections.
On the small side of the funnel, you have a few webserver processes or database connections.
The funnel is software like g-wan or lighttpd, or a connection pool, which:

gathers input data while using minimum resources, especially NOT using one thread per client

when enough input data is available to generate a response, use a pre-spawned process in a pool to process it, buffer the response, and release the process as fast as possible

transfer data back to the client while using minimum resources

Using this setup, your valuable resources are always working instead of waiting for clients, so you need a lot less of them to produce the same amount of output. With few database connections open, ideally a few connections per core, there is much less contention, locks, and context switching on the database server. In particular, locks are held for a much shorter time. This all makes the database server much faster.
So you are asking the wrong question, it is not about how many concurrent connections you can have on your database server before it falls apart. It is about using tools that allow you to have the ideal number of database connections that make your database perform at maximum throughput, no matter the actual number of client connections on the other side of the funnel.
Postgres does tends to scale better especially when you have reads, writes, and long complicated queries at the same time.


How to optimized mysql query having large dataset I have two tables in MySql with the following schema,

CREATE TABLE `open_log` (
  `delivery_id` varchar(30) DEFAULT NULL,
  `email_id` varchar(50) DEFAULT NULL,
  `email_activity` varchar(30) DEFAULT NULL,
  `click_url` text,
  `email_code` varchar(30) DEFAULT NULL,
  `on_date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

CREATE TABLE `sent_log` (
  `email_id` varchar(50) DEFAULT NULL,
  `delivery_id` varchar(50) DEFAULT NULL,
  `email_code` varchar(50) DEFAULT NULL,
  `delivery_status` varchar(50) DEFAULT NULL,
  `tries` int(11) DEFAULT NULL,
  `creation_ts` varchar(50) DEFAULT NULL,
  `creation_dt` varchar(50) DEFAULT NULL,
  `on_date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB  DEFAULT CHARSET=latin1;

The email_id and delivery_id columns in both tables make up a unique key.

The open_log table have 2.5 million records where as sent_log table has 0.25 million records.

I want to filter out the records from open log table based on the unique key (email_id and delivery_id).

I'm writing the following query.

SELECT * FROM open_log
WHERE CONCAT(email_id,'^',delivery_id)
IN (
SELECT DISTINCT CONCAT(email_id,'^',delivery_id) FROM sent_log
)

The problem is the query is taking too much time to execute. I've waited for an hour for the query completion but didn't succeed.

I've tried to make the email_id and delivery_id as composite key but that didn't help.

Kindly, suggest what I can do to make it fast since, I have the big data size in the tables.

Thanks,
Faisal Nasir

First, if email_id and delivery_id together are a unique key, please add a primary -composite- key on both tables for (email_id, delivery_id)
Second, the concat is not necessary and will prevent the previous key from being used. Try:

SELECT ol.*
FROM open_log ol
JOIN sent_log sl
ON (ol.email_id, ol.delivery_id) = (sl.email_id, sl.delivery_id)


Shared Location Options in MS SQL Server Data Migration Assistant For studying purposes I want to upgrade AdventureWorks sample database from MS SQL Server 2017 Express edition to Developer. I am using DMA for that, and can't understand the meaning of Shared Location Option. What I have to specify in that field in order to continue my migration? What's the use of backupshare?

Thanks for your time!

That is for backups that can be accessed by both source and Target servers.

Think of it Just like logshipping has a backup share that is accessed by primary backup job and secondary copy jobs.


"lock request time out period exceeded" Error When Trying to See DB Hierarchies I'm having problems with a database.

I can run basic queries, albeit much slower than normal.
When I attempt to view the hierarchy trees for tables, views, or procedures in SSMS Object Explorer, I get lock request time out period exceeded.
My SSRS reports that run on objects in this database are no longer completing.
Jobs associated with procedures stored on this database also do not run.

I tried using sp_who2 to find and kill all connections on the database, however this has not solved the problem.

What is going on here? How can I resolve this?

It was being caused by a perpetual rollback of a transaction.  Had to eventually restart my server cluster


create sql server login for own domain account I have a working SQL Server instance to administer.  I do not have a SQL Server login and do not know any other logins. I do have a Windows domain login to the server as a local admin.  How can I add my Windows account as a login to SQL Server when I don't know any existing login to the instance?

I can connect using PowerShell:

cd sql\uk-blahblah\default\logins

but cannot retrieve any detail:

gci

SQL Server PowerShell provider error: Failed to read child items. [Failed to connect to server uk-blahblah. --> Login failed for user 'AUTOdomain\admin-account'.]

I can stop the SQL Service and restart from an elevated command prompt:

sqlservr.exe -m -c

but trying to run sqlcmd still fails:

sqlcmd -S UK-blahblah
Login failed for user 'AUTOdomain\admin-account'

I'm sure many of you must have found a way of adding your login to a server where you don't know any other logins, but I'm struggling a little here.

I'm using SQL Server 2012 SP2 Enterprise Edition.

This requires stopping and starting the SQL Service, but will get you access to the SQL Server Instance if you are a local admin on the server.

Run this from an elevated command prompt.

Substitute the service name for mssqlserver if you have a named instance or SQL Express i.e. mssql$sqlexpress

Substitute your domain and account name for BUILTIN\administrators if you do not want to grant sysadmin for all local administrators and want just your account.

Note: the /m SQLCMD switch is single user mode and only a SQLCMD connection will be accepted. This is to keep from being locked out by a service that connects before I can get connected.

I have put these commands in a .bat file and ran it from an elevated command prompt. That makes the outage very short.

As always, test on a dev instance before you go running against production.

net stop mssqlserver

net start mssqlserver /mSQLCMD

sqlcmd -Q "if not exists(select * from sys.server_principals where name='BUILTIN\administrators') CREATE LOGIN [BUILTIN\administrators] FROM WINDOWS;EXEC master..sp_addsrvrolemember @loginame = N'BUILTIN\administrators', @rolename = N'sysadmin'"

net stop mssqlserver

net start mssqlserver

sqlcmd -Q "if exists( select * from fn_my_permissions(NULL, 'SERVER') where permission_name = 'CONTROL SERVER') print 'You are a sysadmin.'"


SQL Server data partitioning This is for SQL Server:

I have a table that has been partitioned. Sometimes, I need to completely update an existing partition - the updates could be extensive enough to touch all rows of the partition.

. Can I..

Make a copy of the partition
Apply updates
Merge the updated partition back?

I am worried about speed of updates. Want to do all updates offline (users would be using the partition actively), and, if possible, switch the updated partion in, so that there would be minimal impact to users...

You could copy the data into another table which has the same schema and partitioning, then switch out the old data, then switch in the new data.

The better option would probably be to run the update in a loop against small numbers of rows using the TOP syntax.

SELECT NULL
WHILE @@ROWCOUNT <> 0
    UPDATE TOP (1000) YourTable
        SET Something = 'Something'
    WHERE SomeDate = '1/1/2012'
        AND Something <> 'Something'


MySQL disk scanning algorithm How does MySQL (InnoDB engine) read data from disk? What scanning algorithm does it use? Since the rows are stored in data pages will it read the entire data page to get the resulting rows?

The subject is quite broad and this could (actually does) fill a full book even. If you're interested in more details I recommend reading some. I'll try to summarise the baseline.

InnoDB is using pages to store data. Normally it is 16k but is configurable on some MySQL forks (Percona for example). This is the smallest item which is read from or written to disk (except the innodb_logs but that's a different story). 64 consecutive page make an extent (1MB) and extents are grouped in segments. The tablespace contains multiple segments.

Row are stored in B+Tree structure based on the primary key. Secondary keys are also stored in B+Tree having the primary key at the end.

Primary key lookup

InnoDB is doing a tree traversal on the PRIMARY KEY and return the row.

Secondary key lookup

either traversing the secondary key finding the primary key(s) fulfilling the requirements and then the PK traversal.
or using the adaptive_hash_buffer to get the primary key from the conditions and then do the PK traversal (this can be quite efficient: benchmarking secondary key updates in MySQL)

In case of any read if the page is not present in the buffer pool InnoDB fetch it from the disk (and sometimes other to for read ahead) and put it in the buffer pool. It may need to evict some other to have space for it. In case of innodb_flush_neighbors it can flush more.

Then the row or rows will be fetched and returned.

I hope this gave you something to start with.

Innodb_ruby is very useful tool to look into the physical structure of ibd files. Can give you a lot of insight into the internal behaviour.

5.5 doesn't have a feature to show the contents of the buffer pool unfortunately. In Percona Server 5.5 (not in Oracle MySQL) you can dump the contents of the buffer pool, which can be a way to look what's inside.

The best book I think is High Performance MySQL, 3rd Edition

A couple of years ago I wrote a blog post about how to learn to become a DBA. That might contain some useful information for you also.


Trace Flag 1222 Not Working? I have a customer site with two similarly configured 2008r2 SQL Servers "A" and "C".  On both servers the trace flags 1204 and 1222 are enabled and DBCC tracestatus shows the following on both servers:

TraceFlag   Status  Global  Session
1204        1       1      0
1222        1       1      0
3605        1       1      0

On A the trace flags work as expected, when a deadlock occurs, we get both the 1204 and 1222 deadlock reports in the error logs.  However, on C, only the 1204 report shows up, we never get the 1222 report.

For the life of me I cannot see any reason for this difference.  I have both googled this extensively, and read (and re-read) the MS doc on these trace flags, and I cannot find any reports of behavior like this, nor any hints as to what might cause it.  The only thing that comes close is the occasional claim that neither trace flag was working, but these all turned out to be cases were they had typos in the enabling commands. I know that this is not the case here because I have used DBCC TRACESTATUS to confirm it.

So any insights into what might be causing only trace flag 1222 to not work and/or how to fix it would be greatly appreciated.

Well, here's an interesting development.  Whenever I generate a deadlock myself (using this code: https://stackoverflow.com/questions/7813321/how-to-deliberately-cause-a-deadlock), then I get both trace reports in the error logs.  It's only the "natural" deadlocks that occur every couple of days from the applications that seem to only trigger one of the deadlock reports.  Not sure if this helps, is there any reason to believe that trace 1222 would not report on all of the same deadlock conditions that 1204 would?

I had a similar issue, not sure it will sort yours.

Try this:

EXEC master..sp_altermessage 1205, 'WITH_LOG', TRUE;
GO

Even though it was logging in the event log via the trace flag, this also needs to set in order to trigger the emails. You can see the table here:

select * from master.sys.messages
where text like '%deadlock%'

You can have more details here


MySQL intersection on one table with time type I've a table with 4 columns: id (int), name (varchar), timefrom (time), timeuntil (time)

Now I would like to have all combinations where they have an intersection on the timefrom to timeuntil.

My current solution is that I load all the records and loop it in php for every full hour... but this costs me much performance.

Any Ideas, is this possible to solve in a Select?

I want to know which presences are sharing the same timefrom, timeuntil As an example:

#1 / whole day / 06:00:00 / 18:00:00
#2 / morning   / 07:00:00 / 12:00:00
#3 / afternoon / 13:00:00 / 18:00:00

I would like to have the result:

#1, #2, #3   (the whole day shares the times with morning, afternoon)
#2, #1       (the morning shares the times with the whole day)
#3, #1       (the afternoon shares the times with the whole day)

It seems you need a self join with a non-equality condition that checks for overlaping ranges. The output can be in different formats. Depending on your needs, the easiest would be a 2-column output:

SELECT a.id AS a_id, b.id AS b_id
FROM calendar AS a
  JOIN calendar AS b
    ON  a.id <> b.id
    AND a.timefrom < b.timeuntil
    AND b.timefrom < a.timeuntil
ORDER BY a.id, b.id ;

Output would be (2 columns):

#1    #2   (the whole day shares the times with morning)
#1    #3   (the whole day shares the times with afternoon)
#2    #1   (the morning shares the times with the whole day)
#3    #1   (the afternoon shares the times with the whole day)

To get the rows combined, you can use GROUP_CONCAT():

SELECT a.id AS a_id,
       GROUP_CONCAT(b.id ORDER BY b.id SEPARATOR ' ') AS b_ids
FROM calendar AS a
  JOIN calendar AS b
    ON  a.id <> b.id
    AND a.timefrom < b.timeuntil
    AND b.timefrom < a.timeuntil
GROUP BY a.id
ORDER BY a.id ;

Output (2-columns):

#1    #2 #3   (the whole day shares the times with morning, afternoon)
#2    #1      (the morning shares the times with the whole day)
#3    #1      (the afternoon shares the times with the whole day)

If you want one row per a.id and multiple columns, you'll need dynamic SQL.


Keystroke duplicating in data grid The issue that I am experiencing is easy to reproduce.

When attempting to update the data of a row using the data tab of SQL Developer the cell will receive the first letter twice.

This happens "intermittently"; ~50% of the time when I go to enter fresh data into the cell.

This is most noticeable when you have ~10 columns of Y/N flags and want to simply Y Tab Y Tab Y and end up with Y YY YY and then Shift+Tab rolls you another ~50% chance of fixing it. See image at bottom of question.

Does anyone know of a solution?

Environment specs:

Windows 7 x64
SQL Developer 4.1.5.21.78
C:\Program Files\Oracle\SQL Developer\4.1.5.21.78\jdk\jre\release | JAVA_VERSION = 1.8.0_102

I've been using the latest versions for over 3 years now and this issue has been persistent in all of the versions.

Example:

Please try the following possible solution:
1. Go to the following directory in c:\ drive:

C:\Users\<your_user_id>\AppData\Roaming\

There you will find folder(s) named SQL Developerandsqldeveloper
Rename those folders (whose name is like SQL developer and sqldeveloper) to something like SQL Developer1 and sqldeveloper1 respectively or as you like.
Restart SQL Developer application.

Note: After performing the above operations  all your Connections data will get erased. So, you might need to first export all your connections into an .xml file.

Hope this solves your problem !!


Data driven subscription query parameters in SSRS I am looking at customizing my data driven subscription query in SSRS so that depending on the results of the original query it will notify the correct department.

For example the query returns:

ID Dept   Amount Name

1  Sales  $70    Shelly

Then the email only goes to the sales department and no other department such as marketing or HR

I am looking at using a data driven query and notifying users by email

When creating the dataset for the data-driven subscription, you can use those results to populate several email delivery options.  For each row returned by the data-driven subscription dataset, it will send one email.  Each of those rows should contain the necessary columns to feed the required parameters for each report to be unique to its recipients (i.e. Department), along with the email address(es) to which you want to send each report.  The data-driven subscription will only send out reports for the rows its dataset returns, so you will need to make sure the data-driven subscription dataset returns rows that filter the report parameters correctly.  If your report dataset will return more than one row, you will need to ensure data-driven subscription dataset results are grouped appropriately to ensure you send the reports in the desired granularity.

ID   Dept   Amount   Name     Email

1    Sales  $70      Shelly   shelly@email.com; sales@email.com


Database Design guidance required I have scenario where there is Table T with columns PK,A, B, C,D

TABLE t
(
PK,
A,
B,
C,
D
)

i have to insert data from different sources ie columns A,B,C,D are calculated separately..
and they same primary PK.

I would like to ask if it better to create indivdiaul tables for each column and then join them using PK and insert into final table T

pk|A  ,pk|B ,PK|C ,PK|D ----> JOIN AND INSERT INTO TABLE T(pk,a,b,c,d)

or

it is ok to update ie

first have some thing like T (PK,A,B,C,D)
DATA SOMETHING like (1,a1,NULL,NULL,NULL)

run update statement for all columns B,C,D  sequentially and get all the data

T(1,a1,b1,c1,d1)

I'm not sure either of these are really great options.  Here's my thoughts:

  pk|A ,pk|B ,PK|C ,PK|D ----> JOIN AND INSERT INTO TABLE T(pk,a,b,c,d)

With this scenario you have redundant data.  Unless your individual PK tables are just staging tables, I think you could run into some serious issues here, especially with different consumers pulling data from different tables.  I wouldn't chance that whatsoever with this design.

Not to mention that if PK:A, PK:B, PK:C, and PK:D are all 1:1 relationships, I don't see any normalization benefit out of this either.

  T (PK,A,B,C,D) DATA SOMETHING like (1,a1,NULL,NULL,NULL) run update statement for all columns B,C,D sequentially and get all the data T(1,a1,b1,c1,d1)

I'm not sure I like this design either, although I'd put it at a higher preference then the former.  The reason behind this is because what happens if business dictates that columns A, B, C, and/or D really aren't NULLable.  Now you can run into a big problem of data conflicting with business constraints.

I think the real solution here may be to take a step back and analyze your data modification.  Instead of having a step-based data insert, is there no way to do a single insert of all the required and necessary data into this table?  In the application, retrieve all of the required data before each item's insert.  That way you won't be defying any business rules, having redundant data, or additional operation overhead of the designs above.

In other words, if data columns A, B, and C are required, then you shouldn't be pushing that data out to this table until you have it.  Without all of those required columns you wouldn't have compliant data, and it shouldn't live in that table until it is.  And if D is nullable and optional data, then sure that's fine if it doesn't appear until a later time with a different DML operation.


Vague replication error code 2005 2015-04-07 13:49:53 23758 [ERROR] Slave I/O: error connecting to master 'replication'@master-host:3306' - retry-time: 60  retries: 1, Error_code: 2005

This was after attempting to do a change master to from previously running replication.  The only change was the master host.   The new guy I wanted to change master to is running from the same actual master.

This is using GTID so i was doing auto position=1 and not mucking with master_log_file master_log_position values.

I am able to connect to the new master using the replication credentials that show in mysql.slave_master_info via mysql client. Show grants confirms it has replication slave on .

I'm not seeing anything about denied connection attempts in the new master's error file, but I do see retry error attempts on the slave.

There's nothing more useful in show slave status\G

After getting frustrated I did a change master to back to the old master and it started running just fine.

I'm at a loss for how to continue trouble shooting this.

Once you change the master_host, you have to redo everything else

It says so in the MySQL Documentation on CHANGE MASTER TO

  If you specify the MASTER_HOST or MASTER_PORT option, the slave assumes that the master server is different from before (even if the option value is the same as its current value.) In this case, the old values for the master binary log file name and position are considered no longer applicable, so if you do not specify MASTER_LOG_FILE and MASTER_LOG_POS in the statement, MASTER_LOG_FILE='' and MASTER_LOG_POS=4 are silently appended to it.

Just specify everything again

STOP SLAVE;
CHANGE MASTER TO
    master_host='IP_of_new_host',
    master_port=3306,
    master_user='repluser',
    master_password='repluserpasswowrd',
    master_heartbeat_period=1,
    master_auto_position=1
;
START SLAVE;

GIVE IT A TRY !!!


SQL Server on Windows Server (VMware) - online storage swap I have a VM cluster designed to store multiple SQL Server instances. We use variety of storage providers (i.e. 3PAR, XIV) which have to be changed now (not relevant to what brand). On Oracle on Linux we use VLM / Oracle ASM to perform the storage swap while the Oracle instance remains online. I need to swap the storage of disks used as User_DB, User_DB_Logs and Backup partitions in Windows. Is there anything I can do in the SQL Server / Windows Server world to achieve it?

SQL Server Std & Enterprise 2012-2017

Windows Server 2012 R2 - 2019

EDIT 1:

I am looking for options like writing both data and log into two targets at the same time (using one MSSQL instance) or Windows Server / VMware level disk mirroring which would allow me to do a hot swap onece both disk are in synch.

My set up has well more than 500 individual SQL Server instances, many with 100+ DBs on it. Solution must be orchestrated and rather generic.

As far as I know there is nothing like that in Windows/MSSQL world, but this forum surprised me enough times to raise this question anyway.

EDIT 2:

I ended up with an idea to create second set of my databases on the same instance as they are primarily located. That would allow me to have a copy of my DB set located of freshly introduced disks (backed by fresh storage provider behind the VM). In my set up I need to migrate only user databases (including their logs) everything else stays on its current drive set (backups are easy to move).
Therefore what I need to achieve now is to force one of the available HA technologies to create a copy of my DB set into the same SQL Server instance.
Therefore the plan I have looks like that:

Spin up new disks for SQL DB data and DB logs
Introduce them to Windows and SQL Server
Set up a Transactional Replication of all user DBs into the same SQL Server instance, changing DB names and their physical location on drives
Once everything in synch, stop the SQL traffic, rename DB sets and fail-over into DB located on fresh drives
Clean up (old DBs, replication, buffer pools etc)

That would allow me to introduce new drives with minimal time off.

Can you think about anything faster than (not involving introducing of new SQL Server instances)?

Follow the process here: Move Database Files to move the databases to the new storage.  You can leave the insance online, but each database will need to be OFFLINE while its files are copied to the new location.

Alternatively you can present the new drives, shut down SQL Server, copy all the files to the new drives, unmount the old drives, mount the new drives in the same folders or drive letters as the old drives, and restart SQL Server.

In both cases take backups of your databases before you start DO NOT delete any files from the old drives until you have sucessfully migrated to the new drives and taken new backups of your databases.

  what I need to achieve now is to force one of the available HA technologies to create a copy of my DB set into the same SQL Server instance.

To do that, use Backup/Restore, not Transnational Replication.  Replication is more complicated, expensive, and does not support tables without a primary key, or all database objects.  So, for each database:

Place Database in Full recovery mode
Take a Full Backup and restore it with a new name and location without recovery
Take a Tail Log Backup of the source database (taking it offline).
Restore the Tail Log Backup on the new database with recovery.
Rename or drop the old database
Rename the new database (back online).
Restore the desired recovery mode of the new database.


How can I model a database structure to retain student information? I am completely new to databases and SQL.

I have generated a database in myphpAdmin consisting of a table with the columns (name, student number, grade). Basically this database will hold the grades for all the students in a school. In my web application program, the index.php displays all information in the database on the browser.

So basically each student will have a row in the database. Over time, the grades for each student will be added to the database once they sit an exam. How can I just add more information to the grade column of each student row without losing the grades that are already there? (As the UPDATE operation in SQL seems to override the old data).

Or am I going about this the wrong way and need to structure each student as a database in themselves and then add their grades into those individual databases, with grade1, grade2, grade3, etc.?

You need to normalize the design. A column can, under sane circumstances, only hold a datum (singular).

So something like this

CREATE TABLE student (
  studentid int AUTO_INCREMENT PRIMARY KEY
);
CREATE TABLE class (
  classid   int AUTO_INCREMENT PRIMARY KEY,
  classname text
);
CREATE TABLE student_class_grade (
  classid   int REFERENCES class,
  studentid int REFERENCES student,
  grade     float
);

This is super simple, so each student has a studentid, each class has a classid, and you can put as many entries as needed for stupid_class_grade. This doesn't account for the extra complexity needed for weighing tests and homework differently in the same class.


Exclusion constraint on ENUM errors: data type dow has no default operator class for access method "gist" Following in the style of this, I'm trying to create an exclusion constraint over an ENUM,

CREATE TYPE dow AS ENUM (
  'Monday', 'Tuesday', 'Wednesday', 'Thursday',
  'Friday', 'Saturday', 'Sunday'
);

CREATE TABLE available (
  dow      dow,
  tsrange  tsrange,
  EXCLUDE USING gist (tsrange WITH =, dow WITH &&)
);

Returns

ERROR:  data type dow has no default operator class for access method "gist"
HINT:  You must specify an operator class for the index or define a default operator class for the data type.

This is apparently a known issue, and it was fixed in PostgreSQL 10. The new version of btree_gist (10+) has in the docs,

  btree_gist provides GiST index operator classes that implement B-tree equivalent behavior for the data types int2, int4, int8, float4, float8, numeric, timestamp with time zone, timestamp without time zone, time with time zone, time without time zone, date, interval, oid, money, char, varchar, text, bytea, bit, varbit, macaddr, macaddr8, inet, cidr, uuid, and all enum types.


How can I copy the content of a cell in the output pane where the latter is detached from the query window in pgAdmin III? How can I copy the content of a cell in the output pane where the latter is detached from the query window in  pgAdmin III?

When the output pane is not detached from the query window, I can use   control  +  C, but it doesn't seem to work when it is detached.

Here is a screenshot showing the output pane detached from the query window:

I use pgAdmin III 1.20.0 with Windows 7 SP1 x64 Ultimate (English).

For reasons best known to the designers of PgAdmin3, it's apparently not possible to do this.

The Cut/Copy and Paste options become greyed out when the window is floating. The logic behind this escapes me (wracks brain.... no, no good).

The only thing that I can think of is to use the green write arrow with the floppy and save your data to a file and dig out your data element that way - cumbersome I know. Or, you could just re-dock the window which does work.

If you'd like to file a feature request on the PgAdmin site, I'll gladly file a "me-too" if you provide the link back here.


oracle 12c rac switch from mounted to open How to open an RAC database correctly(version 12.1) from "mounted" state?

Earlier, nodes #01 and #02 were opened manually by the command alter database open.

Now, the database in this state:

SQL> select instance_name,status from gv$instance;
INSTANCE_NAME    STATUS
node01          OPEN
node02          OPEN
node04          MOUNTED
node03          MOUNTED

~$ srvctl status database -d cldb -v
Instance node01 is running on node srv-n01. Instance status: Open,Readonly.
Instance node02 is running on node srv-n02. Instance status: Open,Readonly.
Instance node03 is running on node srv-n03. Instance status: Mounted (Closed).
Instance node04 is running on node srv-n04. Instance status: Mounted (Closed).

Connect to third instance and issue

SQL> alter database open;

then to fourth instance and do the same.

But if you want to open all instances at once, you need to use srvctl utility:

srvctl start database -db db_name

https://docs.oracle.com/database/121/RACAD/GUID-C9DB4DCB-34B9-42F0-8AB4-33199087CE98.htm#RACAD900


MySQL system date change impact I can't find an answer for this problem.

If I have in my database data of datatypes like date and timestamp. Can the change of system date affect this saved data ? I mean, can these existing timestamp and date values be changed after the change of system date ?

Thank you very much :)

Changing the system date or the system time does not change values stored in tables.

Existing values can be changed using an UPDATE statement, though.

Datetime and timestamp columns behave differently on retrieval.

create table tz_test (
  ts timestamp not null,
  dt datetime not null
);

insert into tz_test values (current_timestamp, current_timestamp);

select * from tz_test;

ts                   dt
--
2013-06-27 09:46:14  2013-06-27 09:46:14

Set a session timezone, and compare.

set time_zone = '-8:00';
select * from tz_test;

ts                   dt
--
2013-06-27 05:46:14  2013-06-27 09:46:14
           ^^

Timezone support in MySQL

Docs show you how to change both client and server timezones for testing. You have to read that.